Android SQLite Upgrade without losing data

Ever worrying about changing structure of database when Android application is already in the production? Yes then you are at right place.

SQLite is lightweight database provided by the Android system to store and manipulate the content. There are several callback when we are creating/modifying structures, insert/update/delete of data.

Here is the best way to change/modify structure of database without loosing data.

CustomDatabaseHandler is the custom database handler implementation class which extends the SQLiteOpenHelper  which in turn implements OnCreate(), OnUpgrade(), OnDestroy(), etc mehtods.

DATABASE_VERSION is the current version of the database which is in use it starts with 1. In least case it remains as 1 it start increasing as further structural changes occurs by the time.

public CustomDatabaseHandler(Context context) {

super(context,DATABASE_NAME,null,DATABASE_VERSION)

}

OnCreate() will be called when you installing app for the first time and creating object for the database handler

public void onCreate(SQLiteDatabase db) {
String CREATE_TABLE_NAME = “CREATE TABLE ” +TABLE_TABLE_NAME+ “(“+ KEY_ID + ” INTEGER PRIMARY KEY,” + KEY_PARAM + ” TYPE_PARAM”)”;

db.execSQL(CREATE_TABLE_NAME );
}

OnUpgrade() will be called whenever you change the Database Version. Increase in number indicates table structure is  being altered.

public void onUpgrade(SQLIteDatabase db , int oldVersion, int newVersion){

        //here you can alter database structure

         //this will be calledback when new Database Version is changed(increased)    i.e. newVersion > oldVersion

}

Example :

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

        case 2:
                //db.execute(ALTER TABLE 'TABLE_NAME' ADD COLUMN 'COLUMN_NAME' 'COLUMN_TYPE')
                //other alter table(s) logic from version 1 to 2
        case 3:
                //alter table(s) logic from version 2 to 3
      
        default:
                throw new IllegalStateException(
                "onUpgrade() with unknown oldVersion " + oldVersion);
        }
    }

Here you can alter your table structures when you increase database version number by 1. But you should follow best practice to incremental database changes as one never knows whats the previous version number when app was publish. There might chance that during development several versions have increase. Best incremental alter strategy can be as follows

public void onUpgrade(
        final SQLiteDatabase db, final int oldVersion,
        final int newVersion)
    {
        int upgradeTo = oldVersion + 1;
        while (upgradeTo <= newVersion)
        {
            switch (upgradeTo)
            {
                case 2:
                 break;
                    .
                    .
                    .
                    .
                case 5:
                    db.execSQL("SOME ALTER COMMAND FOR VERSION 5");
                    db.execSQL("SOME ALTER COMMAND FOR VERSION 5");
                    break;
                case 6:
                    db.execSQL("SOME ALTER COMMAND FOR VERSION 6");
                    break;
                case 7:
                    db.execSQL("SOME ALTER COMMANDS FOR VERSION 7");
                    break;
            }
            upgradeTo++;
        }
    }

While loop takes care of all the previous versions of database structure changes,  one can directly upgrade to version number 7 from version number 1. Then incremental changes will be from 1->2 , 2->3 ….. 6->7 through while loop. Without while loop one have to write several lines of code to get the same results.