2009-09-07

Android Development: Database Upgrades

This article is part 1 of 11 in the series Android Development
  1. Android Development: Database Upgrades
  2. Android Development: Missing API
  3. (Not) Integrating Application with Intents
  4. Android Activity Lifecycle
  5. URI Matching in Android’s IntentFilters
  6. Android Intent Sender Verification
  7. Android Jittery Scrolling Gallery
  8. Encryption on Android & BouncyCastle
  9. Android Permission Handling
  10. ContentProvider Curses Cursor
  11. ContentProvider Wasted Potential

Turns out that you can. Or rather, you can reset your database’s schema manually, which lets you re-use one schema version over and over during development, and ship with schema version increments of exactly one.

The first and obvious choice for that is a function in the SQLiteDatabase class that’s called setVersion()1. You could call that… well, when exactly?

One obvious candiate to use this in is the onOpen() function in SQLiteOpenHelper. The thing is, that function is called after onUpgrade() has completed, and is less than ideal.

Granted you could let onUpgrade() finish, then crank down the stored schema version so the next time you open the database the upgrade is attempted again.

Maybe it’s just me, but I find such code slightly distasteful. The biggest issue with it I have is that I’m forgetful… I might accidentally ship that code, and my app might re-try the database upgrade over and over again.

I much prefer modifying the database file itself whenever I want to test the upgrade code. That’s also possible, and here’s how you do it.

  1. First, restart adb as root:
    $ adb root
  2. Then, log in to your device:
    $ adb shell
#
  3. Next, open the database file. You know the file name because you passed it to SQLiteOpenHelper‘s constructor. The path to that file, on the other hand, includes your app’s package name. We’ll use de.unwesen.example for the package name, and my.db for the database file name here:

    # sqlite3 /datat/data/de.unwesen.example/databases/my.db
SQLite version 3.5.9
Enter ".help" for instructions
sqlite>
  4. Now, all you need to do is set the schema version you want. Let's use 5 here, because I haven't mentioned that before:
    sqlite> PRAGMA user_version = 5;
sqlite>

And that's it! The next time your app opens the database, any upgrades from version 5 to the version you're opening the database with will be performed. You can force the app to open the database by reinstalling it2.

All of this may not sound exactly like rocket science, but you might be surprised by the suggestions I've found on the intarwebs. I'll let you search for that yourself, if you're that curious.

Reblog this post [with Zemanta]
  1. There’s a corresponding getVersion() as well. [↩]
  2. Don't uninstall and install it, or your data will be lost. Use the reinstall target from the build system. [↩]
Pages: 1 2
« Previous page
18:13 | Filed under: programming |

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

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