Android Development: Database Upgrades

This article is part 1 of 11 in the series Android Development

I’ve decied to add a new (occasional) series to this blog which is primarily intended to keep notes on development for the Android platform. I need to write this stuff down somewhere, so I might as well do it here.

In this first installment, I want to deal with upgrades to your app’s database. As you may be aware, the SQLiteOpenHelper class defines a convenient onUpgrade() function, which is called when your database scheme needs upgrading.

But how does that work, exactly? The documentation isn’t exactly clear on the details:

Called when the database needs to be upgraded. The implementation should use this method to drop tables, add tables, or do anything else it needs to upgrade to the new schema version.

Fair enough, that explains what to do, but not under what circumstances the function is called. Granted, there’s not much searching you need to do to find the SQLiteOpenHelper’s constructor, and notice the version parameter passed to it. That parameter’s documentation reads:

number of the database (starting at 1); if the database is older, onUpgrade(SQLiteDatabase, int, int) will be used to upgrade the database

Right, at first glance, that’s all we need to know.

If you’ve actually implemented onUpgrade() at least once, though, you might have realized that in order to test your upgrade code works, you need to crank up the constructor’s version parameter every time you try out a new version of your code.

Unless you’re perfect and hit the correct code at the first try, you’ll end up incrementing your database schema version by a value >1 for an upgrade which logcially should be represented by an increment of one exactly.

Yes, integers are cheap. Still, it’s somewhat annoying.

It’s especially annoying because in order to be a very well-behaved citizen, your app should never lose data. As a consequence of this, your app should upgrade it’s database scheme incrementally from it’s current version up to the newly installed upgrade’s requirements.

Here’s what your onUpgrade() might look like, stripped down to bare essentials:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
  if (newVersion > oldVersion) {
    db.beginTransaction();
 
    boolean success = true;
    for (int i = oldVersion ; i < newVersion ; ++i) {
      int nextVersion = i + 1;
      switch (nextVersion) {
        case 2:
          success = upgradeToVersion2(db);
          break;
        case 3:
          success = upgrateToVersion3(db);
          break;
        // etc. for later versions.
      }
 
      if (!success) {
        break;
      }
    }
 
    if (success) {
      db.setTransactionSuccessful();
    }
    db.endTransaction();
  }
  else {
    clearDatabase(db)
    onCreate(db);
  }
}

The code above is by no means perfect, but it demonstrates that there is some benefit in strictly incrementing the database scheme version by one for each logical upgrade. Granted, one could avoid the need for that by implementing onUpgrade() more cleverly.

That’s not exactly the point of this post, though. To reiterate, the point of this post is to do with the fact that few of us are perfect enough to write the code for each schema upgrade perfectly the first time. The above snippet just illustrates how painful testing can be if you have to crank up the schema version with each change you make to the code.

Wouldn’t it be awesome if you could prevent schema versions from being used up during development?

  • R George E B

    I don’t believe the new database version needs to be greater than the old version for ‘onUpgrade’ to work properly. In testing I simply changed the database version number (increment or decrement) and everything seemed to work fine.

    I’m more interested in error handling during database upgrades, and also handling corrupted databases. I’m searching for a robust way to keep user data alive if there is an error. I know this is an incredibly old blog post, but I would be interested to hear your thoughts.

    • http://www.unwesen.de/ unwesen

      Android doesn’t really enforce whether or not versions are incremented or decremented, no. But incrementing is the simplest scheme for figuring out what changes to a database you need to make, so it’s good practice. The code snippet should illustrate how/why to do that.

      Error handling during database upgrades is difficult; of course you’d start and commit or discard the changes in a transaction, but if the transaction fails, you pretty much have to quit the app. And you do that, the next time you start the app the same upgrade happens, and fails again. The only thing you can do is make your upgrade code smart enough to recover from errors you know can occur.

      Given that in the upgrade code you can do just about anything you want, you might do a range of things; rather than altering tables, create new tables and copy the data across, for example. That’ll allow older versions of the same app to run as before in case the migration fails, and the user wants to downgrade.

      ALso, you can catch errors and ask users whether they want to submit their database to you for tracking down the migration error and providing a fix.

      I think there’s no silver bullet, but a few things you can do to keep user’s data safe.

  • http://profile.typepad.com/6p010536ac8e55970c Bob Kerns

    Why not just copy the old version of the database, and restore it before each test?

    You could even automate this by doing it in your app: if the old version is there, replace the current one with it before opening. It won’t affect customers, which won’t have this saved copy, and you can rename it to something else when you don’t want to be testing upgrade. I haven’t tried that, but it seems like it should work out well.

    • http://www.unwesen.de/ unwesen

      The database path is inaccessible on a regular (unrooted) Android device.

      • http://profile.typepad.com/6p010536ac8e55970c Bob Kerns

        No, it’s not. It’s accessible to the application, or the application would never be able to open or modify the database.

        If you wanted to do it manually via adb, then you’d need a rooted device.

        • http://www.unwesen.de/ unwesen

          You’re right, this article was done about a rooted device, as the examples demonstrate.

          The whole point was to not copy the database. If you want to copy the database, feel free. This post represents an alternative.

          • http://profile.typepad.com/6p010536ac8e55970c Bob Kerns

            It’s always good to have alternatives!

            I like the idea of copying, because then you’re in a known state. Not every upgrade action can be properly tested by just resetting the version number.

            But for a quick test, your approach has the advantage.  It certainly beats bumping the version number up and up!

            • http://www.unwesen.de/ unwesen

              More importantly, you can test whether your upgrade actions are idempotent. That’s a rather useful trait for development, as well as production.