How to deal with multiple database version changes when Android Application update

Question

thanks for reading my question here.

Actually i have some confusion for how to deal with SQLite tables when i need to add some rows in table for multiple sq-lite versions.

I Google things and found something like this we have to do alter table in onUpgrade method. This would be work sequential update.

Suppose i have 3 devices they have below versions of database
1) 1st device = database version 1
2) 2nd device = database version 2
3) 3rd device = application not installed.

database version 1 = 2 columns
database version 2 = 2+2 columns
database version 3 = 4+2 columns.

So here if i need to give update to my android application users then what i have to do ? I have following scenarios.

1) Updated application would be install in 3rd device where still application is not installed.
2) Application should be update from version 1 to version 3 (Device 1st).
3) Application should be update from version 2 to version 3 (Device 2nd).

Here i can got idea about how to handle scenario number 3 but i do not know how can i handle scenarios 1st and 2nd.

How can i write code so it would be work for all scenarios.

onUpgrade method would be call only database version changes and onCreate call when database is created.

Thanks Any help and Suggestion would be appreciated.

Answer 1

From what you have mentioned (e.g. the onUpgrade method) I assume you are using a SQLiteOpenHelper.

First update your database version to 3.

In onCreate(SQLiteDatabase db), update the code so that it creates all the tables as per the latest version 3 schema (e.g the table has col1, col2 and col3). A new install will trigger this code creating a brand new v3 table.

In onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion), refer to the old and new version parameters and update the tables that already exist accordingly, e.g.

if (oldVersion == 1 && newVersion = 2)
{
    //Going from v1 to v2
    db.execSQL("ALTER TABLE mytable ADD COLUMN col2 INTEGER");
}

if (oldVersion == 1 && newVersion = 3)
{
    //Going from v1 to v3
    db.execSQL("ALTER TABLE mytable ADD COLUMN col2 INTEGER");
    db.execSQL("ALTER TABLE mytable ADD COLUMN col3 INTEGER");
}

if (oldVersion == 2 && newVersion = 3)
{
    //Going from v2 to v3
    db.execSQL("ALTER TABLE mytable ADD COLUMN col3 INTEGER");
}

This code is triggered when the database version doesn't match what the user currently has. You know what version they do have so can amend the existing tables accordingly to bring them up to the same definition as found in onCreate. You can of course do anything you like within onUpgrade such as creating new tables and inserting/updating/deleting data.