Logo Icon Logo
A Crowd-sourced Cookbook on Writing Great Android® Apps
GitHub logo Twitter logo OReilly Book Cover Art

Upgrading an Existing SQLite Database

Author: Ian Darwin
Published? true
FormatLanguage: WikiFormat

Problem:

You want to provide an orderly upgrade to the SQLite database schema on your database, without losing data or causing errors on users who upgrade to a newer version of your app.

Solution:

Implement the onUpgrade method in your SQLiteHelper subclass.

Discussion:

The SQLiteHelper code keeps the VERSION field you pass into the constructor, and saves it in the database. When you need to change your schema, you increment this integer. After a user (or you, as developer) upgrade the application such that the version number in the app is higher than the version number in the database on "disk", the SQLiteHelper code notices, and calls the +onUpgrade()+ method with the old and new version numbers. You are expected to know what changed between each version, and update the database schema accordingly. In this example, we added a single table between version one and version two. This also shows creating the SQLiteOpenHelper as an inner class, which is a more concise way of writing it.

protected final SQLiteOpenHelper sqliteOpenHelper = new SQLiteOpenHelper(
        MainActivity.this, "music.db", null, SCHEMA_VERSION) {
    // onCreate() method not shown, same as previous recipes
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.d(TAG, String.format("IansSQLiteDatabase.onUpgrade(%d -> %d)",
                oldVersion, newVersion));
        switch(newVersion) {
        case 2:
                Log.d(TAG, "Adding Symphony Table");
                db.execSQL(SQL_CREATE_SYMPHONY_TABLE);
                break;
        default:
                throw new IllegalStateException(
                "onUpgrade() with unknown newVersion" + newVersion));
        }
    }
}

When run the first time after an application upgrade, sure enough, we see the following in the +logcat+ output:

10-18 20:01:18.900: D/IansApp.IansSQLiteDatabase(26003): IansSQLiteDatabase.onUpgrade(1 -> 2)
10-18 20:01:18.900: D/IansApp.IansSQLiteDatabase(26003): Adding Symphony Table
No records found.