Adams Bros Blog

28Feb/1219

Upgrade Android SQLite Database

After fiddling with a few different styles of coding a database upgrade implementation for Android, I've settled on a fairly simple method.  Basically, I take the old version, and increment a counter until I get to the current version.  Through each iteration, I use switch on the value of the upgradeTo variable, and upgrade each cycle.  If things get too complex for your application, you can always create individual upgrade methods, for each version.

Of course this method will only work if your database version is incremented by one each time you change the database.  And, this really should be the way you version it anyhow.

    public void onUpgrade(
        final SQLiteDatabase db, final int oldVersion,
        final int newVersion)
    {
        int upgradeTo = oldVersion + 1;
        while (upgradeTo <= newVersion)
        {
            switch (upgradeTo)
            {
                case 5:
                    db.execSQL(SQLiteSet.V5_ADD_LAST_CARD);
                    db.execSQL(SQLiteCard.V5_ADD_FAILED);
                    break;
                case 6:
                    db.execSQL(SQLiteSet.V6_ADD_IMPORT_TYPE);
                    break;
                case 7:
                    db.execSQL(SQLiteSet.V7_ADD_SHORT_FNAME);
                    break;
            }
            upgradeTo++;
        }
    }

Someone asked in a comment, why we do the upgrade in a loop.  I do this because I do not know what version they will be converted from or to. So, I must either put a bunch of logic for every possible upgrade variation, such as 1 => 2, 1 => 3, 1=> 4, 3=> 4, etc, etc, or, I can loop through and do one upgrade at a time, and increment the version through each loop.  This GREATLY simplifies the database upgrade.

Filed under: Android, Java Leave a comment
Comments (19) Trackbacks (0)
  1. Why are you looping through each upgrade? If a user goes from version 5 to verion 7, then why not just put the contents of your case 6 and 7 all in case 7? For the example below, see how I added the case 6 execSQL ito case 7 also? This will allow users to go from DB v5 to v7 without having to go through v6 first.

    case 5:
    db.execSQL(SQLiteSet.V5_ADD_LAST_CARD);
    db.execSQL(SQLiteCard.V5_ADD_FAILED);
    break;
    case 6:
    db.execSQL(SQLiteSet.V6_ADD_IMPORT_TYPE);
    break;
    case 7:
    db.execSQL(SQLiteSet.V6_ADD_IMPORT_TYPE);
    db.execSQL(SQLiteSet.V7_ADD_SHORT_FNAME);
    break;

  2. I mainly do not do that, because I do not know what version they will be converted from. If they were on version 4, your case 7 would not work. So, I must either put a bunch of logic for every possible upgrade variation, or, I can loop through and do one upgrade at a time, and increment the version through each loop.

    Now, if you ended up doing a database upgrade, never releasing that version to the android market, or your users, then you could do that. For example, let’s say you did a version 6 database, but none of your users ever used that version, but then you released version 7 to them; it would make sense to do it the way you have suggested.

  3. It is much easier to use a switch and leave out the breaks. Like this
    switch (oldVersion)
    {
    case 4:
    db.execSQL(SQLiteSet.V5_ADD_LAST_CARD);
    db.execSQL(SQLiteCard.V5_ADD_FAILED);
    case 5:
    db.execSQL(SQLiteSet.V6_ADD_IMPORT_TYPE);
    case 6:
    db.execSQL(SQLiteSet.V7_ADD_SHORT_FNAME);
    }

  4. humanoid, interesting solution. Thanks.

  5. Don’t you need call db.setVersion(upgradeTo) after each update?

  6. I like this I call it the Adams Increment method. I was actually going to do each upgrade individually. The Adams increment method will be easier to maintain.

  7. What is the SQLiteSet and SQLiteCard ? Are these your own classes where you have constants defined for each query ?

    • I apologize, I did not see your question earlier. Yes, those are my own custom classes, which implement sets of cards for a flash card program for android. Not really relevant to this specific blog post though, which is why I didn’t include them.

  8. I am wondering if this works as well

    public void onUpgrade(
    final SQLiteDatabase db, final int oldVersion,
    final int newVersion)
    {
    int upgradeTo = oldVersion + 1;
    switch (upgradeTo)
    {
    case 5:
    db.execSQL(SQLiteSet.V5_ADD_LAST_CARD);
    db.execSQL(SQLiteCard.V5_ADD_FAILED);
    case 6:
    db.execSQL(SQLiteSet.V6_ADD_IMPORT_TYPE);
    case 7:
    db.execSQL(SQLiteSet.V7_ADD_SHORT_FNAME);
    break;
    }
    }

    So if upgradeTo is 6, it will do both case 6 & 7

    • 1. Switch/case without breaks is usually considered bad practise, as it can result in bugs.
      2. If you had a large number of database versions to upgrade through, you might want to display a progress bar. Doing it via a loop would make that easier.

  9. it can remove the while loop and all the breaks in the switch, then the upgrade can also work and looks simpler.

  10. Thanks for this — it’s a perfect template for onUpgrade.

    I’m having to deal with changing my schema for the first time.

    Do I just:
    1) Change this var: public static final int DB_VERSION = 1; which is fed into SQLiteOpenHelper’s constructor
    2) Add some code in onUpgrade() like you have here

    Do I also change my onCreate() to reflect the new schema OR do I leave it as is (the version 1 schema)?

    Thanks for any help!

  11. Thanks a lot Adams.

  12. I’ve used this pattern for years.
    Note that when the # of upgrades gets very large (I was up to 300+), the switch will fail because it can overtax the compiler.

    Instead, simply have a function for each upgrade step, ie
    current_version = upgrade_step_10( current_version );

    And one master function that calls ALL of those functions in order.
    The trick is that each function checks the current_version parameter to see if this step should be performed. If not, then it just returns current_version.

    Something like that.

    You can put all the functions in a nice big array of function pointers (yes it was in C++) and for loop through them.

    An additional trick is to ensure that after each step, you also write back to the DB what the new version number is.

    That way if the upgrade breaks half way through, it is possible to continue the upgrade later (although hand-fixing may be required if a step breaks halfway through).

  13. Been trying a couple of approaches but this one does the job efficiently for apps with relatively shorter database version changes.


Leave a comment

 

No trackbacks yet.