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:

We create a migration table within your database in which we store which scripts have been executed.

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.

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.

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.