Room cannot verify data integraty of database built from asset with indices

Question

Summary

I'm pre-populating a database with createFromAsset() It works, now I need to add indices to the DB, however Room is not able to handle this change.

Quik note

This db is not in production, I re-installed the app after making the changes, my issue is not the common problem on how to migrate from version X to Y.

Code

This is my entity

@Entity(tableName = "myTable", indices = [Index(value = ["col1","col2"], name = "myIndex")])
...

I ran the following query to also add the indice to the pre-populated DB

CREATE INDEX myIndex ON myTable (col1,col2)

After these changes were made I re-installed the app to avoid db version issues.

The problem is that Room always fails to verify the data integrity, incrementing the version is not an option, it does fix the problem if (and only if) I install version 1 first and then increment the version.

The migration must also be empty for it to work, executing the following query fails because myIndex already exists:

db.execSQL("CREATE INDEX myIndex ON myTable (col1,col2)")

What I tried

Running the query locally in onCreate function from RoomDatabase.Callback() instead of importing it directly from assets.

Wondering...

I can't seem to understand why Room complains about the indice, it refuses to add it because it already exists, running an empty migration fixes the problem, not running any migration gives me a verification exception.

The thing is that I can't run an empty migration just to fix it because the migration won't run at all since this DB is not in prod yet.

I feel lost.

user_4744514 · Answer

it refuses to add it because it already exists,

Use CREATE INDEX IF NOT EXISTS myIndex ON myTable (col1,col2)

You should use it in the onOpen function.

Alternately if using Room 2.4.0-beta2 or greater then there is a prePackagedDatabase callback which you could use to do the same BUT only when the Pre-Packaged database is copied.

The onOpen function will be invoked every time the database is built/opened (not an issue as the index existing will result in minimal overheads).
Whilst the prePackagedDatabase callback is only invoked when the database is created/copied from the asset.
The onCreate function only runs when Room creates the database. It does not create the database when createFromAsset (or File) is used. However, if you use fallbackToDestructiveMigration AND there is no Migration covering the from/to versions then onCreate is called.

The correct solution would be to include the index in the Pre-Packaged database, it would then always exist.

I can't seem to understand why Room complains about the indice, it refuses to add it because it already exists, running an empty migration fixes the problem, not running any migration gives me a verification exception.

If you run a fresh installation, then as the index is defined in the Entity it will create the index. Hence why it already exists (IF NOT EXISTS doesn't create it if it already exists).

Not running a migration fails because room generates an identity_hash based upon the Entities. Change the entity and room sees that the identity hash has changed.

You may find looking at the generated java (visible from the Android View in Android Studio) looking for the class that is the class annotated with @Database but with the _Impl suffix and looking in that class for the createAllTables method. This includes all the tables and indexes that Room will create.

You would see the room_master table that holds the identity_hash that is compared against the one in the package. You would also see that Room includes IF NOT EXISTS on all CREATE statements.