Updating a Database in a Deployed Mobile App

One of the issues I had to deal with in building my mobile app was the evolving database schema as more features were added and the app grew.  I needed a way to update the database scheme without blowing away the old one and forcing the app to redownload all the data.  While I could have done that without any loss of data (there is no user supplied data to be lost), the main data set is over 8 MB in size and that isn’t nice to users’ wireless plans.  What I needed was a way to incrementally change the schema as the app was updated.

First Attempt

My first attempts just used if() statements.  i.e. if the current app version was one value and the new app version was a different value, apply this update.  This worked well enough for my development versions where I was strictly controlling the upgrade versions.  But I quickly realized that if I were to try to do that with versions of the app out in the wild, where I had no control over when it was updated, I’d quickly have a mess of conditionals to deal with.  So I scrapped that idea and went looking for something new.

Final Solution

I don’t think this solution is new to me as I’m sure I saw it somewhere to give me the idea but I couldn’t tell you where I saw it off the top of my head.  The answer was a switch() statement, with each case() block having a bit of the upgrade path from old to new schemas.  In order for this to work, you need to track a version number for the database schema that increases each time you make a change.  Simply start at one and every time you change the schema increment the value.  This value, which I called current_version, is simply a coded variable.

Additionally, the app needs a second value that it stores as part of the application data which contains the database version that is currently installed (installed_version).  Then, each time the application is run, you check these values.  There are three possibilities^[1]:

1. There is no stored value for installed_version. This should only be true the very first time the application is run.  In this case, you simply need to create the entire database schema as it now stands and store the current_version as the installed_version.
2. The current_version equals the installed_version.  This is the simplest case of all.  Since the database schema is all up-to- date you don’t have to do anything.  Continue on to the main part of the application.
3. The current_version is greater than the installed_version.  In this case there is work to do and we now need to do the update.

To make the update we simply enter a switch() statement based on the installed_version.  Each case() block should have arguments that start at 1 and increase by one each time.  The first case() block (i.e. case 1:) would hold the commands that were necessary to upgrade from version 1 to version 2.  The second would hold the commands modify the schema from version 2 to version 3, and so forth.

None of the case statements should have the break command associated with them so that after execution they would fall through to the next one and cascade all the way to the end.  That way if a user skips one or more app updates that include database upgrades, each one will be applied in turn and none will be skipped.

Finally, the default() block at the end of the switch statement should contain the code up update the value of the stored installed_version so that it is also up-to-date for the next time the application is run.

Here is a bit of sample code that shows how this works.  It is lifted in modified (with additional comments added) from my application which is written in using Titanium Studio’s Mobile SDK.  It should be noted that the c object is a configuration object that holds a lot of information such as the database name, the table names, and column names among other things.

// Check that we have a stored value for the installed database version

if(!Ti.App.Properties.hasProperty('installed_db_version')){

// if not, the database has never been created

createDB();

} else {

var installed_version = Ti.App.Properties.getInt('installed_db_version');

if (c.current_version > installed_version){

Ti.API.info("Performing an upgrade from database version "+old_version+" to version "+c.version);

var db = Ti.Database.open(c.name);

// We need to do an upgrade to the new version based on the old version.

// We'll do this incrementally adding in all the changes since the installed

// version was created.

switch(installed_version) {

case 1:

Page 1 of 2 | Next page