RSS Feed

On Upgrading your Android App Database

Posted on Friday, February 14, 2014 in Coding

GnuCash Android has come a long way since it’s first release and in that time, lot of changes have been made to the database schema. Android has a special mechanism for this in the onUpgrade() method which is called when the database version number changes. Up until version 1.2.7, GnuCash had this method implemented as follows:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < newVersion){
        Log.i(TAG, "Upgrading database to version " + newVersion);
        if (oldVersion == 1 && newVersion == 2){
            //execute upgrade queries
        }
        if (oldVersion == 2 && newVersion == 3){
            //execute database upgrade queries
        }
    }
}

Can you spot the problem? Go ahead, take your time, I’ll wait. Most of it is the common boilerplate you’d expect: checking that the database operation is actually an upgrade, logging, etc.
But then come the important lines…let’s take another look:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < newVersion){
        Log.i(TAG, "Upgrading database to version " + newVersion);
        if (oldVersion == 1 &amp;&amp; newVersion == 2){
            //execute upgrade queries
        }
        if (oldVersion == 2 && newVersion == 3){
            //execute database upgrade queries
        }
    }
}

Whenever a user skips an upgrade and then tries to upgrade at a later version, then none of the if statements will ever be true. Hence no database upgrade will be performed. This can lead to all manner of headaches for the user due to an inconsistent database schema. Mea maxima culpa!

It has been possible for this error to go undetected for this long because, I guess, most users accept the updates they are proposed or even allow the Play Store to automatically upgrade the apps. But for the small percentage of users who skip an upgrade, well, that would be the beginning of trouble. The typical way to solve this would be to uninstall and reinstall the application, which is not ideal.

While this was going on, Navneet Karnani created a patch which was meant to add another feature to the app, but which included a fix for the database issue.
The database upgrade code should look like this:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < newVersion){
        Log.i(TAG, "Upgrading database to version " + newVersion);
        if (oldVersion == 1 && newVersion >= 2){
            //execute upgrade queries
            oldVersion = 2;
        }
        if (oldVersion == 2 && newVersion >= 3){
            //execute database upgrade queries
            oldVersion = 3;
        }
    }
}

There is a small but very significant difference in the code.
First, we check whether the new database version is greater than or less than what we expect. Also, and very crucially, we update the oldVersion variable to the next version after performing the upgrades to the old version. This ensures that if we are upgrading over several database versions, then all the upgrade steps for the intermediate versions will also be performed.

It is also possible to implement the database upgrade method as switch statements. I’ll leave that as an exercise to you.

The next version of GnuCash Android (v1.3.1) is currently available in the Google Play Store and includes these changes which should improve stability.

Share and Enjoy:
  • Twitter
  • Google Bookmarks
  • Digg
  • del.icio.us
  • Facebook

Bring on the comments

  1. John Ralls says:

    That’s going to get ugly as you the version gets large. I suggest:

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    Log.i(TAG, “Upgrading database to version ” + newVersion);
    while (oldVersion < newVersion){
    execute_upgrade_queries(db, oldVersion++);
    }
    }

    If possible I'd house the actual queries in an array indexed on oldVersion to keep execute_upgrade_queries simple and scalable.

    • Ngewi Fet says:

      Thanks John, I totally agree. That is definitely a better way to check the version for upgrade. I’ll use that.

      The execute_upgrade_queries() method will still grow very big over time though. I like the idea of the indexed queries. However, certain migrations are more than just simple queries and also require code for migrating old records to a newer format.

Leave a Reply