How to change room db expected column order? Pre-packaged database has an invalid schema error

Question

I need to prepopulate db. I have two problems, one is: roomdb not exporting schema, even though exportSchema is set to true. second is: room db reordering columns and giving me error of Pre-packaged database has an invalid schema. Now the first problem can be omitted, but second problem must be resolved to move forward. I have no idea why this is happening. I have created database in sqlite browser and saw the same issue. The error is:

Expected:
TableInfo{name='words', columns={en=Column{name='en', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='undefined'}, pl=Column{name='pl', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, speech=Column{name='speech', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}}, foreignKeys=[], indices=[]}
 Found:
TableInfo{name='words', columns={id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='undefined'}, en=Column{name='en', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, pl=Column{name='pl', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, speech=Column{name='speech', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}}, foreignKeys=[], indices=[]}

as seen above the 'en' column and 'id' column in different position. Not sure but I suspect this is the reason of the error.

table creation statement:

CREATE TABLE words (id INTEGER PRIMARY KEY, en TEXT, pl TEXT, speech TEXT)

Entity:

@Entity(tableName = "words")
data class MyEntity (
    @PrimaryKey
    @ColumnInfo(name = "id")val id: Int,
    @ColumnInfo(name = "en") val englishWord: String?,
    @ColumnInfo(name = "pl") val polishWord: String?,
    @ColumnInfo(name = "speech") val speech: String?
)

Answer 1

How to change room db expected column order?

The order of the columns, between expected (the schema that Room expects according to the @Entity annotated classes that have been specified in the entities parameter of the respective @Database annotated class(es)) and the found (the pre-populated database) is irrelevant.

What matters is that the column definitions in the found table(s) match the expected definitions.

In other words, after having a quick look at the expected v found, you will see that the id column is expected to have a NOT NULL constraint, the table creation statement, as per the pre-populated database* does not include the NOT NULL constraint. i.e.

expected is ===> id=Column{name='id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='undefined'}

found is ==> id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='undefined'}

To overcome this issue then you could have:-

`CREATE TABLE words (id INTEGER PRIMARY KEY NOT NULL, en TEXT, pl TEXT, speech TEXT)`

Comparing and trying to ascertain how to interpret the expected/found discrepancies is not the easiest task. It is easier to always ensure that what Room expects is used for the schema of the pre-populated database. This is as simple as:-

1. Creating the @Entity annotated class(es) and including them in the entities parameter of the @Database class(es).

2. Successfully compiling the project.

3. Using the Android Studio's Android View locating the class that is the same name as the @Database annotated class, but suffixed with _Impl in the java(generated) directories (there will typically be at least two sub directories)

4. Locating the createAllTables method, in which the expected SQL for creating the tables (as Room would use if it was left to create them).

   4.1 Note that the room_master_table need not be created as Room will create and maintain this as required.

You may need to convert the existing data which can be done easily in an SQLite tool by:-

1. Renaming the original table

2. Creating the table as per the Room SQL from the generated java (this has the original name)

3. Using SQL to copy the data from the renamed original

   3.1 This in the simplest scenario could be as simple as INSERT INTO the_table SELECT * FROM the_renamed_original_table;

4. Dropping the renamed original table

Alternative Approach

In your case an alternative could be to use @ColumnInfo(name = "id")val id: Int?,. However, you may wish to refer to Why does @PrimaryKey val id: Int? = null works when creating a Room entity? as using a nullable id is treated differently by room when inserting (i.e. 0 is considered to be the value 0 rather than a generated value and thus if used could result in the row not being inserted or failing if another row exists with the value 0)

• note that in some cases you cannot tailor the @Entity to suit a pre-populated/existing schema.

Example of Generated Java for the Alternative Approach

Consider your and the suggested nullable id as per:-

@Entity(tableName = "words")
data class MyEntity (
    @PrimaryKey
    @ColumnInfo(name = "id")val id: Int,
    @ColumnInfo(name = "en") val englishWord: String?,
    @ColumnInfo(name = "pl") val polishWord: String?,
    @ColumnInfo(name = "speech") val speech: String?
)

@Entity(tableName = "wordsV2")
data class MyEntityV2 (
    @PrimaryKey
    @ColumnInfo(name = "id")val id: Int?,
    @ColumnInfo(name = "en") val englishWord: String?,
    @ColumnInfo(name = "pl") val polishWord: String?,
    @ColumnInfo(name = "speech") val speech: String?
)

And for the sake of demonstrating the following:-

@Database(entities = [AutoGenTrueTable::class,AutoGenFalseTable::class,AutoGenFalseNullableTable::class,MyEntity::class,MyEntityV2::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() { ....

• ignore all but MyEntity and MyEntityV2 (used an existing project for the demostration)

Then finding the generated java:-

• as can be seen the words table has the NOT NULL constraint, the wordsv2 does not, so may suit.
  • see link above re difference of how Room handles inserts (generation of the value for the id column).