Android: upgrading DB version and adding new table

Your code looks correct. My suggestion is that the database already thinks it's upgraded. If you executed the project after incrementing the version number, but before adding the execSQL call, the database on your test device/emulator may already believe it's at version 2.

A quick way to verify this would be to change the version number to 3 -- if it upgrades after that, you know it was just because your device believed it was already upgraded.

You can use SQLiteOpenHelper's onUpgrade method. In the onUpgrade method, you get the oldVersion as one of the parameters.

In the onUpgrade use a switch and in each of the cases use the version number to keep track of the current version of database.

It's best that you loop over from oldVersion to newVersion, incrementing version by 1 at a time and then upgrade the database step by step. This is very helpful when someone with database version 1 upgrades the app after a long time, to a version using database version 7 and the app starts crashing because of certain incompatible changes.

Then the updates in the database will be done step-wise, covering all possible cases, i.e. incorporating the changes in the database done for each new version and thereby preventing your application from crashing.

For example:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    switch (oldVersion) {
    case 1:
        String sql = "ALTER TABLE " + TABLE_SECRET + " ADD COLUMN " + "name_of_column_to_be_added" + " INTEGER";
        db.execSQL(sql);
        break;

    case 2:
        String sql = "SOME_QUERY";
        db.execSQL(sql);
        break;
    }

}

1. About onCreate() and onUpgrade()

onCreate(..) is called whenever the app is freshly installed. onUpgrade is called whenever the app is upgraded and launched and the database version is not the same.

2. Incrementing the db version

You need a constructor like:

MyOpenHelper(Context context) {
   super(context, "dbname", null, 2); // 2 is the database version
}

IMPORTANT: Incrementing the app version alone is not enough for onUpgrade to be called!

3. Don't forget your new users!

Don't forget to add

database.execSQL(DATABASE_CREATE_color);

to your onCreate() method as well or newly installed apps will lack the table.

4. How to deal with multiple database changes over time

When you have successive app upgrades, several of which have database upgrades, you want to be sure to check oldVersion:

onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
   switch(oldVersion) {
   case 1:
       db.execSQL(DATABASE_CREATE_color);
       // we want both updates, so no break statement here...
   case 2:
       db.execSQL(DATABASE_CREATE_someothertable); 
   }
}

This way when a user upgrades from version 1 to version 3, they get both updates. When a user upgrades from version 2 to 3, they just get the revision 3 update... After all, you can't count on 100% of your user base to upgrade each time you release an update. Sometimes they skip an update or 12 :)

5. Keeping your revision numbers under control while developing

And finally... calling

adb uninstall <yourpackagename>

totally uninstalls the app. When you install again, you are guaranteed to hit onCreate which keeps you from having to keep incrementing the database version into the stratosphere as you develop...