Confusion: How does SQLiteOpenHelper onUpgrade() behave? And together with import of an old database backup?

Question

let's assume I have a database table test_table with 2 columns and a corresponding create script in the SQLiteOpenHelper:

DB_VERSION = 1:
public void onCreate(SQLiteDatabase db)
{
db.execSql("CREATE table test_table (COL_A, COL_B);
}

This is the initial app version 1, which is published in the Play Store.

After a while there's an update to the app and the utilized database. I guess the SQLiteOpenHelper class has to be adapted like this:

DB_VERSION = 2:
public void onCreate(SQLiteDatabase db)
{
db.execSql("CREATE table test_table (COL_A, COL_B, COL_C)");
}

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
db.execSql("ALTER TABLE test_table ADD Column COL_C");
}

After some time, another app update:

DB_VERSION = 3:
public void onCreate(SQLiteDatabase db)
{
db.execSql("CREATE table test_table (COL_A, COL_B, COL_C, COL_D)");
}

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
db.execSql("ALTER TABLE test_table ADD Column COL_D");
}

--> This is where I need advice. If the user installs app version 1, he has Columns A and B. If he then updates to version 2, onUpgrade fires and adds a column C. New users who install from scratch get the 3 columns via the create statement. If the user then updates to version 3, onUpgrade fires again and a column D is added. But WHAT IF the user installs app version 1, then skips the update of version 2 and updates version 3? Then he would have missed the

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)

    {
    db.execSql("ALTER TABLE test_table ADD Column COL_C");
    }

part and only

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)

    {
    db.execSql("ALTER TABLE test_table ADD Column COL_D");
    }

would be called, which leads to a table test_table(COL_A, COL_B, COL_D)??

What's the correct way of handling database upgrades of a live app, so the user doesn't lose his data? Do you have to check all possible (old) versions in the onUpgrade() method and execute different alter table statements based on that version?

I am asking because in my app, the user has the possibility to export and import the data, which is nothing more than export: copy the whole database away and import: replace the app database with the backup copy database.

What happens if the user has app version 1, exports the database, upgrades the app (new database structure) and imports the old version 1 backup? --> How will SQLiteOpenHelper behave? --> What is the correct way to handle db upgrades together with import/export functionality?

Menses

Answer 1

What's the correct way of handling database upgrades of a live app, so the user doesn't lose his data? Do you have to check all possible (old) versions in the onUpgrade() method and execute different alter table statements based on that version?

By and large, yes.

A common approach to this is to do pair-wise upgrades:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  if (oldVersion<2) {
    // do upgrade from 1 to 2
  }

  if (oldVersion<3) {
    // do upgrade from 2 to 3, which will also cover 1->3,
    // since you just upgraded 1->2
  }

  // and so on
}

This roughly equates to Rails migrations, for example.

What happens if the user has app version 1, exports the database, upgrades the app (new database structure) and imports the old version 1 backup? --> How will SQLiteOpenHelper behave?

If by "copy the whole database away", you literally mean a full file copy of the SQLite database file, then when SQLiteOpenHelper goes to open the restored backup, it will that the database has the old schema version and will go through onUpgrade() as normal.

What is the correct way to handle db upgrades together with import/export functionality?

I suspect the answer is: either make your backup by copying the entire file, or also arrange to backup and restore the schema version, which you can get by calling getVersion() on a SQLiteDatabase object. That being said, I haven't dealt with this scenario much, and there may be more issues that I am not thinking of.

Anissa

So you mean that when an older database is copied back, onUpgrade will notice and run? Nice and good to know! Thank you for your answers! – Menses

@ToniKanoni: "So you mean that when an older database is copied back, onUpgrade will notice and run?" -- it should, if it is a complete database copy. – Anissa

Yes, as you've written above, it's just the copy of the whole database file – Menses

Answer 2

below psuedo code shows increamental upgrade

@Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        switch(oldVersion) {

        case 2:
                //upgrade logic from version 2 to 3
        case 3:
                //upgrade logic from version 3 to 4
        case 4:
                //upgrade logic from version 4 to 5
                break;
        default:
                throw new IllegalStateException(
                "onUpgrade() with unknown oldVersion" + oldVersion));
        }
    }

By incremental upgrade i mean - Notice the missing break statement in case 2 and 3

say if the old version is 2 and new version is 4, then the logic will upgrade the database from 2 to 3 and then to 4

if old version is 3 and new version is 4, it will just run the upgrade logic for 3 to 4

keep adding new cases for every new database version upgrade that will do the increamental changes

Lands

that won't work. it will skip versions: installed version 2, upgrade to app version with db version 4 will skip version 3 and fail, the switch statement should use oldVersion – Ogham

if installed version is 2, the onUpgrade will get hit with oldVersion as 2, that means the switch case now will execute case 2 + case 3 + case 4 as the break statements are missing in all the older cases. Note that break statements should not be there in the older cases as we are doing incteamental upgrades – Lands

Even though the two answers are right (yours and the right one) this is easier to implement, has no duplicated code (by that I mean the queries) and it's cleaner. One might get confused by the missing breaks, but, c'mon, that's a way to use switch, so... Anyway, I liked your answer :) – Flexible

Answer 3

i suspect you can also create a new table with all the columns you need

CREATE TABLE new_test_table (COL_A, COL_B, COL_C,COL_D);

copy the data from the old table to the new

INSERT INTO new_test_table SELECT * FROM test_table;

drop the old table DROP TABLE test_table;

and rename the new table

ALTER TABLE new_test_table RENAME TO test_table;

so in short

public void onUpgrade(SQLiteDatabase db,int OldVersion,int NewVersion){
  db.execSQL("CREATE TABLE new_test_table (COL_A, COL_B, COL_C,COL_D);"+
             "INSERT INTO new_test_table SELECT * FROM test_table;"+
             "DROP TABLE test_table;"+
             "ALTER TABLE new_test_table RENAME TO test_table;");"
    }

that way you don't have to worry about dataloss or incremental changes

Dominant

Answer 4

I think its too late to answer this question but it may help others.

     DB_VERSION = 1:
        public void onCreate(SQLiteDatabase db)
        {
        db.execSql("CREATE table test_table (COL_A, COL_B, COL_C, COL_D, COL_E");
        }
    

      public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
        {
             switch (oldVersion) {
        case 1:                     db.execSql("ALTER TABLE test_table ADD Column COL_C");
                                    db.execSql("ALTER TABLE test_table ADD Column COL_D");
                                    db.execSql("ALTER TABLE test_table ADD Column COL_E");
                            break;
        case 2:                     db.execSql("ALTER TABLE test_table ADD Column COL_D");
                                    db.execSql("ALTER TABLE test_table ADD Column COL_E");
                            break;
        case 3:                     db.execSql("ALTER TABLE test_table ADD Column COL_E");
                                  
                            break;

and so on...
}

If the user installs app first time he will get all column from onCreate

If old version of database 2 then case 2 add column C and D.

If old version of database 3 then case 3 add only column E

In that way there is no confusion about database version.

Anjanetteanjela

In the beginning, it should be DB_VERSION = 4; – Belding