How to test database migration in Android?

Question

I have an Android app with a database with version 1.

Now I like to change the structure of the database and write all the migration code etc.

The problem I have is how to test this.

I need to repeatedly have the old version of the app with its old DB so I can test the update process to the new app with the new DB several times.

I thought to simplify things I make a copy of my project and just rename it new - leaving the manifest and everything unchanged!!!!

The idea is to run/install the old version of the app with the old DB structure using Eclipse so I can create the start situation of my update.

Now to simulate the user update to the new app version I install it just over the old version using Eclipse again) - but even without having changed the db I get an error that the DB already exists ???

I am confused (I have only changed the project name, not the manifest) I would have expected that I can just install the new version over the old version and hereby test the user update of the app.

But that does not work.

How would I best do this? (without having the old and the new database code in the very same project. since if I have the same project I have two different data base structures in there and need to build in a switch how it should start up, ie as old or new. I find it cleaner to just write the new version of the app with the new database structure in there)

Answer 1

Room is one of the Android Architecture Components for database stuff.

Room provides a testing Maven artifact to assist with this testing process.

First you need to export db scheme by set the room.schemaLocation annotation processor property in your build.gradle file

android {
    ...
    defaultConfig {
        ...
        javaCompileOptions {
            annotationProcessorOptions {
                arguments = ["room.schemaLocation":
                             "$projectDir/schemas".toString()]
            }
        }
    }
}


@RunWith(AndroidJUnit4.class)
public class MigrationTest {
    private static final String TEST_DB = "migration-test";

    @Rule
    public MigrationTestHelper helper;

    public MigrationTest() {
        helper = new MigrationTestHelper(InstrumentationRegistry.getInstrumentation(),
                MigrationDb.class.getCanonicalName(),
                new FrameworkSQLiteOpenHelperFactory());
    }

    @Test
    public void migrate1To2() throws IOException {
        SupportSQLiteDatabase db = helper.createDatabase(TEST_DB, 1);

        // db has schema version 1. insert some data using SQL queries.
        // You cannot use DAO classes because they expect the latest schema.
        db.execSQL(...);

        // Prepare for the next version.
        db.close();

        // Re-open the database with version 2 and provide
        // MIGRATION_1_2 as the migration process.
        db = helper.runMigrationsAndValidate(TEST_DB, 2, true, MIGRATION_1_2);

        // MigrationTestHelper automatically verifies the schema changes,
        // but you need to validate that the data was migrated properly.
    }
}

You can read an official document for more info

Answer 2

You can create the main file 'create.sql' and the migrations files called '1.sql, 2.sql,.., n.sql'. Store all these files in assets for example.

If the user has no database, apply create.sql If the user needs a database upgrade from A to Z, then apply a+1.sql, a+2.sql, …, z.sql.

You need implement applyUpgrades method that you'll use on upgrade version of DB and for migration tests. It's look like:

fun Context.applyUpgrades(db: SQLiteDatabase, from: Int, to: Int): Unit =
    (from+1..to).forEach { index ->
      val migrationSql = getMigrationFromAssets(index)
      db.execSQL(migrationSql)
    }

Your migration tests will look like:

@Test
fun upgrade10to11() { // when you upgrade from 10 to 11

    val db = SQLiteDatabase.create(null)

    // apply all previous migrations
    applyUpgrades(db, from = INITIAL, to = 10)  

    val valuesBefore = ..

    // our new migration
    applyUpgrades(db, from = 10, to = 11)

    val valuesAfter = ..

    // assert that valuesBefore and valuesAfter are correct
}

Also you can check that you did not forget write test for new migration:

@Test
public void hasAllNecessaryTests() {
  assertEquals(DB_VERSION, 11);
}

Trello bless