Updating the SQLite database for a production Android application, is this correct?

Question

As the name says, I have an Android app with about 1000 installations. I had to make changes to SQL in SQLite, until that moment the version of SQLite DB was installed on version "1".

Hopefully I will explain the code below enough in the comments, this code is in my SQLiteOpenHelper class, so the onUpgrade method is part of the class:

// Provides an upgrade path for the DB when the apps version is updated. @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // First version of the DB was 1. Logic: each if statement will // alter the DB cumulatively based on the version code. So, if the // newVersion was version 3, there would be two if statements, one // for oldVersion 1 and one for oldVersion 2. oldVersion 2 will // contain the logic for upgrading from version 2 to 3, while // oldVersion 1 will contain a combination of alter statements // allowing the database to upgrade from version 1 directly to // version 3. if (oldVersion == 1) { db.execSQL("ALTER TABLE plans ADD COLUMN " + App.CURRENCYCODE + " TEXT"); Locale locale = Locale.getDefault(); ContentValues content_values = new ContentValues(); content_values.put(App.CURRENCYCODE, locale.toString()); db.update(App.DBPLANS, content_values, App.ID + " > ?", new String[] { "0" }); } if (oldVersion == 2) { // Placeholder for next database upgrade instructions. } } 

Please let me know if there are any pitfalls here. So far, it went perfectly, although I am very concerned about the launch of my first database update. I have 1000 users or so, I would not want to lose them.

Thanks again!

Answer 1

When I need to update a database like this, I usually do it with a switch statement, where the cases fail to each other, for example:

 switch (oldVersion) { case 1: // update to version 2 // do _not_ break; -- fall through! case 2: // update to version 3 // again, do not break; case 3: // you're already up to date 

The advantages of this are that you do not repeat your update statements in multiple if statements while continuing to modify the database, and to add a database update you only need to add a new case statement, rather than updating a few blocks of code.

Sometimes there are exceptions, such as a column added to one version, but then deleted in the future, so you need to pay attention when you go.