Regression testing sqlite database migrations in Android

‹ 3D models from a single photo | Tantalus-Arboretum Trail ›

One strategy to test Android sqlite database upgrades is to make a copy of your SQLiteOpenHelper class each time you create a new database version. That way, you can use the older revisions of SQLiteOpenHelper to create legacy versions of your database schema and make sure that when you open the database with newer versions, the upgrade works properly.

For example, if your SQLiteOpenHelper subclass is called MyOpenHelper and is currently set to database version 1, you could copy it to MyOpenHelper1 before adding a new database version 2 to MyOpenHelper.onUpgrade(). Then you can test that v1 databases are properly updated to version 2 like this:

public class MyOpenHelperTest extends AndroidTestCase
{
  public void testUpgradeFromV1toV2()
  {
    // Delete the database which will be lying around from previous tests
    getContext().deleteDatabase(MyOpenHelper.DATABASE_NAME);
    // Set up a v1 database
    SQLiteOpenHelper helper = new MyOpenHelper1(getContext());
    SQLiteDatabase db = helper.getWritableDatabase();

    // Insert some test data
    final ContentValues values = new ContentValues();
    values.put("column1", "value1");
    values.put("column2", "value2");
    db.insertOrThrow("table", null, values);

    // Check that it was inserted
    Cursor cursor =
      db.query("table", new String[] {
        "column1", "column2" }, null, new String[] {}, null, null,
        null, "1");
    assertEquals(1, cursor.getCount());
    cursor.moveToFirst();
    int idxCol1 = cursor.getColumnIndexOrThrow("column1");
    assert idxCol1 >= 0 : idxCol1;
    assertEquals("value1", cursor.getString(idxCol1));
    int idxCol2 = cursor.getColumnIndexOrThrow("column2");
    assert idxCol2 >= 0 : idxCol2;
    assertEquals("value2", cursor.getString(idxCol2));
    cursor.close();
    db.close();

    // Migrate it to a V2 database
    helper = new MyOpenHelper(getContext());
    db = helper.getWritableDatabase();

    // Check that the original data is still there
    // and that the migration worked.
    cursor =
      db.query("table", new String[] {
        "column1", "column2", "newcolumn" },
        null, new String[] {}, null, null, null, "1");
    assertEquals(1, cursor.getCount());
    cursor.moveToFirst();
    final int idxNewColumn = cursor.getColumnIndexOrThrow("newcolumn");
    // Call this to make sure it doesn't crash
    cursor.getInt(idxNewColumn);
    int idxCol1 = cursor.getColumnIndexOrThrow("column1");
    assert idxCol1 >= 0 : idxCol1;
    assertEquals("value1", cursor.getString(idxCol1));
    int idxCol2 = cursor.getColumnIndexOrThrow("column2");
    assert idxCol2 >= 0 : idxCol2;
    assertEquals("value2", cursor.getString(idxCol2));
    cursor.close();
    db.close();
  }
}

Subscribe to All Posts - Wesley Tanaka