How to Android SQLite onUpgrade()

You are currently viewing How to Android SQLite onUpgrade()

If you are an Android app developer and have written an Android App code or moderate size. You have likely implemented and utilized an SQLite Database. There are lots of examples over the internet on how to write the code which is needed for Android SQLite onUpgrade().

Many of the guides will get developers up and running. Although many of these code guides will get developers up and running. They can leave them stranded when it comes to time to upgrade the app and by extension the application database.

Sample App

For this tutorial guide we will imagine that we have an application that needs a single SQLite table. We will call this table Team and it will contain the columns:  Id (int), Name (string), City (string), Mascot (string).

Standard OnUpgrade() Guide

When I first started making Android apps in the starting days, I found a guide that looked something like this (Warning: this is not the code you want to copy!):

public class SQLiteHelper extends SQLiteOpenHelper {

    public SQLiteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    //database values
    private static final String DATABASE_NAME      = "demoApp.db";
    private static final int DATABASE_VERSION      = 1;
    public static final String COLUMN_ID           = "_id";

    //team table
    public static final String TABLE_TEAM       = "team";
    public static final String COLUMN_MASCOT    = "mascot";
    public static final String COLUMN_CITY      = "city";

    public static final String DATABASE_CREATE_TEAM = "create table "
            + TABLE_TEAM + "(" + COLUMN_ID + " integer primary key autoincrement, "
            + COLUMN_NAME + " string, "
            + COLUMN_MASCOT + " string, "
            + COLUMN_CITY + " string);";

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(DATABASE_CREATE_TEAM);
    }

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

This code works great when you are in the early development. You can change the tables by simply altering the create statement and incrementing the version number and that’s it. The only problem with this method is that this technique will wipe out the table every time it is updated. This technique is used very rarely in app production. You don’t want to be calling:

db.execSQL("DROP TABLE IF EXISTS " + TABLE_TEAM);

for each table every time a new version of the app is released.

Marginally Better Examples

Developers who think that this is going to be a problem might Google something like “android onupgrade add column.” Unfortunately, today when I’m writing this article, 5 out of 6 have no good solutions. To view some of these bad solutions.

The guide suggests altering the OnUpdate() function to better leverage the oldVersion parameter. But they all have a problem. Below are some of the suggested improvements. See if you can spot the problem with each:

Bad Example 1

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
{
   db.execSQL(ALTER_USER_TABLE_ADD_USER_SOCIETY);
   db.execSQL(ALTER_USER_TABLE_ADD_USER_STREET1);
}

The problem

These alter statements will be run every time you update the app! If you go from version 1 to 2, they will be run and add the columns. Then, if you update from 2 to 3 (and do not change this code), you will have an error when attempting to add these columns again! You might be tempted to just alter this section every time you change the app, but this is error prone and will expose you to the same bug as example 3.

Bad Example 2

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

    // If you need to add a column
    if (newVersion > oldVersion) {
        db.execSQL("ALTER TABLE foo ADD COLUMN new_column INTEGER DEFAULT 0");
    }
}

The Problem

I might give this very common answer the benefit of the doubt. But, this is a dangerous example, because many developers will simply copy this code. The problem is that every time an upgrade occurs, the if statement is true and we are left with the exact same problem as the previous code snippet.

Bad Example 3

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    String upgradeQuery = "ALTER TABLE mytable ADD COLUMN mycolumn TEXT";
    if (oldVersion == 1 && newVersion == 2)
         db.execSQL(upgradeQuery);
}

The Problem

This one is a bit tougher to spot. Consider what happens if a user goes from version 1 to 3. They will miss the upgrade query completely! These users will be in an awkward limbo where they are missing a few of the intermediate updates and do not have the expected SQL schema. This would likely be a major issue for your app.

The Right Way

public SQLiteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    //database values
    private static final String DATABASE_NAME      = "demoApp.db";
    private static final int DATABASE_VERSION      = 3;
    public static final String COLUMN_ID           = "_id";

    //team table
    public static final String TABLE_TEAM       = "team";
    public static final String COLUMN_MASCOT    = "mascot";
    public static final String COLUMN_CITY      = "city";
    public static final String COLUMN_COACH     = "coach";
    public static final String COLUMN_STADIUM   = "stadium";

    private static final String DATABASE_CREATE_TEAM = "create table "
            + TABLE_TEAM + "(" + COLUMN_ID + " integer primary key autoincrement, "
            + COLUMN_NAME + " string, "
            + COLUMN_MASCOT + " string, "
            + COLUMN_COACH + " string, "
            + COLUMN_STADIUM + " string, "
            + COLUMN_CITY + " string);";

    private static final String DATABASE_ALTER_TEAM_1 = "ALTER TABLE "
        + TABLE_TEAM + " ADD COLUMN " + COLUMN_COACH + " string;";

    private static final String DATABASE_ALTER_TEAM_2 = "ALTER TABLE "
        + TABLE_TEAM + " ADD COLUMN " + COLUMN_STADIUM + " string;";

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(DATABASE_CREATE_TEAM);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 2) {
             db.execSQL(DATABASE_ALTER_TEAM_1);
        }
        if (oldVersion < 3) {
             db.execSQL(DATABASE_ALTER_TEAM_2);
        }
    }
}

Leave a Reply