Android Room - Verify the correctness of DB indices during migration unit test

Question

We just begin expose to migration unit test code. We love it as it helps us to identify few bugs.

It is useful to help us verify the expected table structure.

But, I notice that the migration code, unable to check against whether indices are being created correctly. If I purposely left out the index creation code from the migration, the unit test is still pass.

I notice in the generated schema json files, they contain indices information. Hence, I expect the migration test code should able to capture the missing indices.

Here's our migration test code.

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

    // Array of all migrations
    private static final Migration[] ALL_MIGRATIONS = new Migration[]{
            new Migration_1_2(),
            new Migration_2_3(),
            new Migration_3_4(),
            new Migration_4_5(),
            new Migration_5_6(),
            new Migration_6_7(),
            new Migration_7_8(),
            new Migration_8_9(),
            new Migration_9_10(),
            new Migration_10_11(),
            new Migration_11_12(),
            new Migration_12_13(),
            new Migration_13_14(),
            new Migration_14_15(),
            new Migration_15_16(),
            new Migration_16_17(),
            new Migration_17_18(),
            new Migration_18_19(),
            new Migration_19_20(),
            new Migration_20_21(),
            new Migration_21_22(),
            new Migration_22_23(),
            new Migration_23_24()
    };

    @Rule
    public MigrationTestHelper helper;

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

    @Test
    public void migrateAll() throws IOException {
        // Create earliest version of the database.
        SupportSQLiteDatabase db = helper.createDatabase(TEST_DB, 1);
        db.close();

        // Open latest version of the database. Room will validate the schema
        // once all migrations execute.
        MyAppRoomDatabaseappDb = Room.databaseBuilder(
                InstrumentationRegistry.getInstrumentation().getTargetContext(),
                MyAppRoomDatabase.class,
                TEST_DB)
                .addMigrations(ALL_MIGRATIONS).build();
        appDb.getOpenHelper().getWritableDatabase();
        appDb.close();
    }
}

Is there any way we can do, to verify the correctness of the DB indices?

Answer 1

You could either query table sqlite_master directly - or use the PRAGMA extension.

This would be PRAGMA index_list(tablename) & PRAGMA index_info(indexname); there's also PRAGMA schema.index_xinfo(indexname). I'm not aware of any less verbose way to check for that, as androidx.room:room-testing doesn't seem to support that. However, when wrapping such a query into a test-method (eg. taking table-name and index-name as arguments), this still might be convenient enough to consider it as acceptable. The SQLite FAQ also describe that.

---

The source-code of class MigrationTestHelper confirms that method onValidateSchema() (line 430) does not query for WHERE type='index'. You still could file a feature request on the issue tracker: https://issuetracker.google.com/issues/new?component=413107&template=1096568

They'd be in the position to provide an authoritative answer.