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?

Answer 1

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

Answer 2

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

Answer 3

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.