Android: SQLite Database Upgrade

Note: I haven't edited this since writing it, or fully tested the code that I present (that said, it's heavily based on one of my apps, it's just I've reduced the number of tables being made.) I will update the tutorial later on when I get a chance.

There are quite a few tutorials out on the web of how to make a SQLite database within an Android application, but not so many that deal with proper upgrading of the database.

Over time the requirements of the database within your application may change, this is almost inevitable if your application is in active development and you're constantly adding new features. Properly upgrading the database in your app is important, because if something goes wrong your application will have unexpected behaviour (such as crashing) or you may loose all your user data and have to start over.



Version 1 of your database
If you have followed one of the numerous tutorials online on how to setup a SQLite database in your app, you most likely have some form of DatabaseHelper class that extends SQLiteOpenHelper. It may look something like this:

package com.example.sampledb;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.text.SimpleDateFormat;

public class DbHelper extends SQLiteOpenHelper
{
    private static final String DATABASE_NAME = "mysampledb";
    private static final int DATABASE_VERSION = 1;

    private static final String DATABASE_CREATE_SAMPLE_TABLE = "CREATE TABLE tblSample" +
        "(" +
        "   _id integer primary key autoincrement," +
        "   name varchar(32)" +
        ");";

    public static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss.SSSS";
    public static final SimpleDateFormat SIMPLE_DATE_FORMAT = new SimpleDateFormat(DATE_FORMAT);

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

    @Override
    public void onCreate(SQLiteDatabase database)
    {
        database.execSQL(DATABASE_CREATE_SAMPLE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        // Do nothing for now
    }
}

Each time it is called, it will check to see if this database and version already exist. If the database doesn't exist, it will create the database by calling onCreate(), and it will store the database name and version number with it. If it does exist, but the version of the current version is lower than what is defined in DATABASE_VERSION, the onUpgrade() method will be called.

Version 2 of your database
The question is, what is the best way to handle an upgrade? I had a think about the way it worked, and I decided the best way would be to loop through all the different versions and apply the required changes.

Let's say you wanted to add a field to "tblSample" called "address"? The new statement to create the database table would look like:

CREATE TABLE tblSample
(
    _id integer primary key autoincrement,
    name varchar(32),
    address varchar(128)
);

Which you will obviously what you want to change your "DATABASE_CREATE_SAMPLE_TABLE" variable to, since you want all new creations of the database to be up to date. You also want to use the onUpgarade method(). The way I have implemented the onUpgrade method is as follows.

package com.example.sampledb;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.text.SimpleDateFormat;

public class DbHelper extends SQLiteOpenHelper
{
    private static final String DATABASE_NAME = "mysampledb";
    private static final int DATABASE_VERSION = 2;

    private static final String DATABASE_CREATE_SAMPLE_TABLE = "CREATE TABLE tblSample" +
        "(" +
        "   _id integer primary key autoincrement," +
        "   name varchar(32)," +
        "   address varchar(128)" +
        ");";

    public static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss.SSSS";
    public static final SimpleDateFormat SIMPLE_DATE_FORMAT = new SimpleDateFormat(DATE_FORMAT);

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

    @Override
    public void onCreate(SQLiteDatabase database)
    {
        database.execSQL(DATABASE_CREATE_SAMPLE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        for (int i = oldVersion; i < newVersion; i++)
        {
            switch(i)
            {
                case 1:
                    db.execSQL("ALTER TABLE tblSample ADD address varchar(128)");
                    break;
            }
        }
    }
}


How will this work? Well, quite well from what I've found, since it will work for any upgrade... if you always use this method, it will work for upgrading from version 1 to 9, or from version 4 to 5. Basically, it will loop through all the previous versions starting at the current version.

So, if my phone is on version 1, and the app needs to upgrade to version 2, this loop will iterate one time, with the value of "i = 1." The switch case statement should, for each version number, execute the required statements to upgrade from that version to the next. So, in this case, "case 1:" will execute the required statements to upgrade from version 1 to version 2.

Potential Problems
The biggest problem that you may face is if your "fresh" database creation statements get out of sync with all the update statements. What should always be the case is, if you start with version 1, and run all the update statements to get it to the newest version, the schema of the database that has been upgraded should match exactly the schema of the database if it were created from the newest version.

In short, the best way around it is only make small changes to your database at a time, and make sure you apply the changes to both the creation statements and the update statements such that they match. I would also suggest always using string literals in the update statements rather than refer to any variables. This would definitely be the case if you need to create a new table. It would be tempting to just refer to the DATABASE_CREATE_NAME_TABLE variable in the upgrade section, but remember that this variable should be creating the newest version of the database, and in your upgrade statement, you need to be aiming to move to the version after what is specified in the case statement. So whilst it will work at first, when you change that table, it may mess up.

A potential solution
One potential solution I have thought of that may work is to keep the create table statements the same such as they were in version 1. Then, when you create a fresh database in the onCreate method, you create all the tables as they were in version 1, you call the onUpgrade method with the variables (database, 1, DATABASE_VERSION). It would look something like the following:

package com.example.sampledb;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.text.SimpleDateFormat;

public class DbHelper extends SQLiteOpenHelper
{
    private static final String DATABASE_NAME = "mysampledb";
    private static final int DATABASE_VERSION = 2;

    private static final String DATABASE_CREATE_SAMPLE_TABLE = "CREATE TABLE tblSample" +
        "(" +
        "   _id integer primary key autoincrement," +
        "   name varchar(32)" +
        ");";

    public static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss.SSSS";
    public static final SimpleDateFormat SIMPLE_DATE_FORMAT = new SimpleDateFormat(DATE_FORMAT);

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

    @Override
    public void onCreate(SQLiteDatabase database)
    {
        database.execSQL(DATABASE_CREATE_SAMPLE_TABLE);
        onUpgrade(database, 1, DATABASE_VERSION);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        for (int i = oldVersion; i < newVersion; i++)
        {
            switch(i)
            {
                case 1:
                    db.execSQL("ALTER TABLE tblSample ADD address varchar(128)");
                    break;
            }
        }
    }
}


I haven't tried this, and I don't think it's the best way to go. If it does work, it would reduce the potential of a miss match between the same version if it were created fresh or upgraded, since it's just creating the first version and upgrading it to the newest version.

The main problem with this I think is that it doesn't force you to think about the structure of your database, and could easily turn you into a lazy programmer (like having a catch clause that doesn't do anything.) The other problem I can see is that it's hard to see at a glance the structure of your database in the newest version, since you will need to process all the upgrade statements to see what it really looks like.

It's your choice. This way should work, and will pretty much eliminate any possibility of a miss match between a fresh and upgraded database, but at the same time you lose the ability to see the structure of your database, and it has the potential to make you lazy.