logo

You have published the android app and it already have thousands of user. Now you want to upgrade the app database and launch the new version of the application.

To upgrade the database, we use SQLLiteOpenHelper‘s onUpgrade() method. This method is called when database version represented by DATABASE_VERSION is changed. The bare implementation of this method is to simply drop the table and call onCreate() method which creates the tables. The method is shown below:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    ...
    db.execSQL("DROP TABLE IF EXISTS albums");
    onCreate(db);
}

This solution is very simple but user will loose all data and ultimately user will become unhappy. The solution is to fire the queries in following order:

1) "alter table TABLE_TO_UPDATE RENAME TO temp;" – Renames the table to update to temporary table name.

2) "create table TABLE_TO_UPDATE (_id integer primary key autoincrement, old_column_1 integer not null, old_column_2 integer not null, new_column integer not null);"; – Here new_column is added to table

3) "insert into TABLE_TO_UPDATE select *, 0 from temp;"; – insert all rows from temp table to TABLE_TO_UPDATE. 0 is default value of new column.

4) DROP TABLE temp; – Drops the temporary table.

Following is the onUpgrade() method:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    String alter_query1="alter table TABLE_TO_UPDATE RENAME TO temp;";
    String alter_query2="create table TABLE_TO_UPDATE (_id integer primary key autoincrement, old_column_1 integer not null, old_column_2 integer not null, new_column integer not null);";
    String alter_query3="insert into TABLE_TO_UPDATE select *, 0 from temp;";
    String alter_query4="DROP TABLE temp;";

    db.execSQL(alter_query1);
    db.execSQL(alter_query2);
    
db.execSQL(alter_query3); db.execSQL(alter_query4); }
AUTHOR: Mahavir Jain

Founder @CodeToArt, Leads Android Development at CodeToArt.

No Comments

Leave a Comment

Your email address will not be published.