Database Upgrades and Migrations with Adobe AIR


This post will show you how to update your SQLite application when you release a new version of your AIR application (Android or otherwise). For those of you who are impatient, just copy the two classes provided and use them as demonstrated at the end of the post (or roll your own, your choice).

When releasing a new version of an AIR application that makes use of SQLite, it’s often necessary to upgrade the schema for that database as well. While taking a Hail Mary approach to the problem and just deleting the old database may work for some, it’s not an elegant option and will ensure that you can never persist data between versions. Instead, creating a way for your application to gracefully migrate its own database to the most recent version will ensure that your local data is not compromised, providing a smooth and seamless experience for the end user.

The Concept

We know that a database is the accumulation of all the CREATE, UPDATE and DELETE statements that have come before it. This means it may be easily represented and reconstructed by an ordered sequence of SQL files. It then becomes our task to manage which SQL files have already been executed in a given application version, and to execute (in order) those files which have not.

To accomplish this, we do the following:

  1. We create a migration table within your database in which we store which scripts have been executed.
  2. We check each script against this table before we run it.

There are other optimizations we can perform, however for this case we’re going to keep the idea simple.

Part 1: The Migration Class

The Migration class is a simple value container that contains your SQL Script as well as a Unique ID. Both of these are derivatives, since we want to give options on how to include the SQL file, and we don’t want to make our users have to think too much about how to set up a migration.

package com.fancybrandname.core.db
{
        import com.adobe.crypto.MD5;
        import com.fancybrandname.core.utils.LogUtil;

        import flash.data.SQLConnection;
        import flash.data.SQLStatement;
        import flash.errors.IllegalOperationError;

        import mx.logging.ILogger;

        /**
         * A specific database migration instance. It should contain both a version
         * and an embedded SQL source file that will be executed against the database.
         *
         * @author Michael Krotscheck
         */
        public class Migration
        {
                /**
                 * Logger
                 */
                private static const LOG : ILogger = LogUtil.getLogger( Migration );

                /**
                 * The SQL Source to use for this migration.
                 */
                private var _source : *;

                /**
                 * The SQL Statement extracted from the SQL Source object.
                 */
                private var _sqlStatement : String;

                /**
                 * This is the unique version key we use to identify this particular migration.
                 */
                private var _versionKey : String;

                /**
                 * The SQL Source to use for this migration.
                 */
                public function get source () : *
                {
                        return _source;
                }

                /**
                 * @private
                 */
                public function set source ( value : * ) : void
                {
                        _source = value;

                        // Here we attempt to extract the SQL statement from the passed object. If 
                        // the object is a Class (as with the @Embed directive), we instantiate and convert it.
                        // If it's anything else we try to invoke the toString method, and throw an error if we
                        // can't find it.
                        if ( _source is Class )
                        {
                                _sqlStatement = ( new _source() ).toString();
                        }
                        else
                        {
                                try
                                {
                                        _sqlStatement = _source.toString();
                                }
                                catch ( e : Error )
                                {
                                        throw new IllegalOperationError( 'Unrecognized type for SQL Migration. Please use Embeds or Strings' );
                                }
                        }

                        // Now we generate a unique ID from this string.
                        // You could request this key manually so that you don't have a dependency
                        // on the adobe crypto library, but I prefer doing it this way.
                        _versionKey = MD5.hash( sqlStatement );
                }

                /**
                 * This is the unique version key we use to identify this particular migration.
                 *
                 * @read-only
                 */
                public function get versionKey () : String
                {
                        return _versionKey;
                }

                /**
                 * This is the unique version key we use to identify this particular migration.
                 *
                 * @read-only
                 */
                public function get sqlStatement () : String
                {
                        return _sqlStatement;
                }

                /**
                 * Constructor.
                 */
                public function Migration ()
                {
                }
        }
}

Step 2: The Migration Manager

Our migration manager does the heavy lifting for this class, by checking the database for the last applied migration key and only applying those keys that come after it. It assumes several things. Firstly, that the migrations are in order of execution. Secondly, that the SqlConnection instance passed to it is a synchronous, not asynchronous connection. This method would definitely work for asynchronous connections, however you’d have to adjust the class to iterate using eventListeners. I leave this as an exercise to the reader.

package com.fancybrandname.core.db
{
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.errors.IllegalOperationError;
	import flash.errors.SQLError;
	
	/**
	 * This metadata sets the default property for child nodes used in MXML. This
	 * makes MXML markup a lot easier to understand.
	 */
	[DefaultProperty( "migrations" )]
	/**
	 * A simple class that assists in managing multiple SQL files that may need
	 * to be run against a locally stored database. It makes the assumption
	 * that the migrations must be executed in the order in which they are provided,
	 * and that the database starts "clean" and untouched.
	 *
	 * @author Michael Krotscheck
	 */
	public class MigrationManager
	{
		
		/**
		 * The SQL Connection to use to connect to the database and check for migration.
		 */
		public var sqlConnection : SQLConnection;
		
		/**
		 * The migrations to run against the database. These must be in order of execution.
		 */
		public var migrations : Vector.<Migration> = new Vector.<Migration>();
		
		/**
		 * The name of the table within the database to store the migrations in.
		 */
		public var tableName : String = 'migration';
		
		/**
		 * Constructor
		 */
		public function MigrationManager ( sqlConnection : SQLConnection = null )
		{
			this.sqlConnection = sqlConnection;
		}
		
		/**
		 * This method runs the database migrations.
		 */
		public function migrate () : void
		{
			// Can we migrate?
			if ( !sqlConnection )
			{
				throw new IllegalOperationError( 'No SQLConnection provided' );
				return;
			}
			
			try
			{
				// Make sure the migration table exists.
				createMigrationTable();
				
				// Get the last version key applied to the database.
				var lastVersionKey : String = getLastVersionKey();
				
				// Iterate through our collection and execute every script AFTER the version key we found.
				var len : int = migrations.length;
				
				// Switch to let us know whether the last version key was found.
				// If the last version key is empty, apply all of them.
				var lastKeyFound : Boolean = lastVersionKey == '' ? true : false;
				
				for ( var i : int = 0; i < len; i++ )
				{
					var currentMigration : Migration = migrations[ i ];
					
					if ( lastKeyFound )
					{
						apply( currentMigration );
					}
					else if ( currentMigration.versionKey == lastVersionKey )
					{
						lastKeyFound = true;
					}
				}
			}
			catch ( e : SQLError )
			{
				throw( e );
			}
		}
		
		/**
		 * This method creates the migration table if it doesn't exist yet.
		 */
		private function createMigrationTable () : void
		{
			var sql : String = 'CREATE TABLE IF NOT EXISTS ' + tableName + ' (version varchar(32) NOT NULL PRIMARY KEY, applied_on timestamp NOT NULL);';
			
			var statement : SQLStatement = new SQLStatement();
			statement.sqlConnection = sqlConnection;
			statement.text = sql;
			statement.execute();
		}
		
		/**
		 * This method gets the last version key recorded in the database.
		 */
		private function getLastVersionKey () : String
		{
			var statement : SQLStatement = new SQLStatement();
			statement.text = 'SELECT version FROM ' + tableName + ' ORDER BY applied_on DESC LIMIT 0,1';
			statement.sqlConnection = sqlConnection;
			statement.execute();
			
			var sqlResult : SQLResult = statement.getResult();
			
			if ( sqlResult == null || !sqlResult.data || sqlResult.data.length == 0 )
			{
				// No migrations found.
				return '';
			}
			else
			{
				// We found one, return it.
				return sqlResult.data[ 0 ].version;
			}
		}
		
		/**
		 * This method applies the database migration to the database passed to this function.
		 * Please note that this version of the class only works with Synchronous SQLConnections.
		 */
		private function apply ( migration : Migration ) : void
		{
			// Apply the migration
			var updateStatement : SQLStatement = new SQLStatement();
			updateStatement.text = migration.sqlStatement;
			updateStatement.sqlConnection = sqlConnection;
			updateStatement.execute();
			
			// Update the version key
			var versionStatement : SQLStatement = new SQLStatement();
			versionStatement.text = 'INSERT INTO ' + tableName + ' (version, applied_on) VALUES ( :version , :timestamp )';
			versionStatement.parameters[ ':version' ] = migration.versionKey;
			versionStatement.parameters[ ':timestamp' ] = ( new Date() ).getTime();
			versionStatement.sqlConnection = sqlConnection;
			versionStatement.execute();
		}
	}
}

Step 3: Usage

Finally it’s time to use our class. As you can see the MXML markup is pretty straightforwar, and usage in ActionScript is similarly easy. Note that I’m using both @Embed directives and raw text, and pay particular attention to the embed mime-type. Without it, this doesn’t really work.

<s:Application 
	xmlns:fx="http://ns.adobe.com/mxml/2009"
	xmlns:parsley="http://www.spicefactory.org/parsley"
	xmlns:s="library://ns.adobe.com/flex/spark"
	xmlns:db="com.fancybrandname.core.db.*"
	>
	<fx:Script>
		<![CDATA[
			override protected function initializationComplete():void
			{
				super.initializationComplete();
				
				var dbFile :File = File.applicationStorageDirectory.resolvePath('myDb.db');
				var connection :SQLConnection = new SQLConnection();
				connection.open( dbFile );
				
				migrationManager.sqlConnection = connection;
				migrationManager.migrate();
			}
		]]>
	</fx:Script>

	<fx:Declarations>
		<db:MigrationManager id="migrationManager" tableName="migrations">
			<db:Migration source="@Embed(source='/sql/CreateSomeTable.sql',mimeType='application/octet-stream')"/>
			<db:Migration source="DROP TABLE IF EXISTS someTable;"/>
			<db:Migration source="@Embed(source='/sql/CreateSomeOtherTable.sql',mimeType='application/octet-stream')"/>
		</db:MigrationManager>
	</fx:Declarations>
</s:Application>

3 Comments

  • Derrick Anderson

    Excellent work!

  • Andreas Rami

    Thanks for sharing this awesome code. Exactly what I was looking for. THANK YOU!!!!

  • Jason Ash

    Thank you so much..! This has helped me in my application. Awesome.

Leave a Reply