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 ()
                {
                }
        }
}

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();
		}
	}
}

<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>