Room is not updating schema of the database

Question

I have the following table:

@JsonClass(generateAdapter = true)
@Entity(tableName = "lexeme")
data class Lexeme(
    @PrimaryKey
    @ColumnInfo(typeAffinity = ColumnInfo.INTEGER)
    val id: Long,
    val form: String,
    val formNoAccent: String,
    val formUtf8General: String,
    val reverse: String,
    val number: Int?,
    val description: String?,
    val noAccent: String?,
    val consistentAccent: String?,
    val frequency: String?,
    val hyphenations: String?,
    val pronunciations: String?,
    val stopWord: String?,
    val compound: String?,
    val modelType: String?,
    val modelNumber: String?,
    val restriction: String?,
    val staleParadigm: String?,
    val notes: String?,
    val hasApheresis: String?,
    val hasApocope: String?,
    val createDate: String?,
    val modDate: String?
)

Whatever I am trying to do, id, form, formNoAccent and formUtf8General are not set as Not Null and the id is not set as affinity = 3, I have tried both Int and Long.

I am trying to preload a database into my app but I am getting the following error:

     Expected:
    TableInfo{name='lexeme', columns={hyphenations=Column{name='hyphenations', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, staleParadigm=Column{name='staleParadigm', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, modDate=Column{name='modDate', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, notes=Column{name='notes', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, hasApheresis=Column{name='hasApheresis', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, description=Column{name='description', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, consistentAccent=Column{name='consistentAccent', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, formNoAccent=Column{name='formNoAccent', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, noAccent=Column{name='noAccent', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, modelType=Column{name='modelType', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, reverse=Column{name='reverse', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, compound=Column{name='compound', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, hasApocope=Column{name='hasApocope', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, frequency=Column{name='frequency', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, number=Column{name='number', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, formUtf8General=Column{name='formUtf8General', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, form=Column{name='form', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, restriction=Column{name='restriction', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, modelNumber=Column{name='modelNumber', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, stopWord=Column{name='stopWord', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, pronunciations=Column{name='pronunciations', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, createDate=Column{name='createDate', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}
     Found:
TableInfo{name='lexeme', columns={hyphenations=Column{name='hyphenations', type='text', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, staleParadigm=Column{name='staleParadigm', type='text', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, modDate=Column{name='modDate', type='text', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, notes=Column{name='notes', type='text', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, hasApheresis=Column{name='hasApheresis', type='text', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, description=Column{name='description', type='text', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, consistentAccent=Column{name='consistentAccent', type='text', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, formNoAccent=Column{name='formNoAccent', type='text', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, noAccent=Column{name='noAccent', type='text', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, modelType=Column{name='modelType', type='text', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, reverse=Column{name='reverse', type='text', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, compound=Column{name='compound', type='text', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, hasApocope=Column{name='hasApocope', type='text', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, frequency=Column{name='frequency', type='text', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, number=Column{name='number', type='integer', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, formUtf8General=Column{name='formUtf8General', type='text', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, form=Column{name='form', type='text', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, restriction=Column{name='restriction', type='text', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, modelNumber=Column{name='modelNumber', type='text', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='text', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, stopWord=Column{name='stopWord', type='text', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, pronunciations=Column{name='pronunciations', type='text', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, createDate=Column{name='createDate', type='text', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}

Answer 1

The simplest way to ensure that found matches expected is to use the SQL that Room generates to create the tables in the tool in which you are using to create the pre-packaged database.

The SQL can be found by compiling the project after creating all the entities and adding them in the list of entities in the class that is annotated with @Database.

After compiling look at the java (generated) and find the class that is the same name as the class that is annotated with @Database suffixed with _Impl. Inside the class there will be a method called createAllTables this has the SQL for all the tables, views, indexes and for FTS tables the triggers that room expects.

• e.g. :-

Fix

You have to ensure that the tables in the pre-packaged database (the found) match those in the SQL ( the expected).

Assuming that you have spotted all the differences then you could try changing the Lexeme class to be:-

@JsonClass(generateAdapter = true)
@Entity(tableName = "lexeme")
data class Lexeme(
    @PrimaryKey
    //@ColumnInfo(typeAffinity = ColumnInfo.INTEGER) //<<<<< effectively removed 
    val id: String, //<<<<< changed to be TEXT
    val form: String?, //<<<<< changes to be NOT NULL
    val formNoAccent: String?, //<<<<< changes to be NOT NULL 
    val formUtf8General: String?, //<<<<< changes to be NOT NULL
    val reverse: String, //<<<<< changes to be NOT NULL (not found by yourself)
    val number: Int?,
    val description: String?,
    val noAccent: String?,
    val consistentAccent: String?,
    val frequency: String?,
    val hyphenations: String?,
    val pronunciations: String?,
    val stopWord: String?,
    val compound: String?,
    val modelType: String?,
    val modelNumber: String?,
    val restriction: String?,
    val staleParadigm: String?,
    val notes: String?,
    val hasApheresis: String?,
    val hasApocope: String?,
    val createDate: String?,
    val modDate: String?
)

• note I haven't tried the above (have to some extent but still no guarantee that I've spotted all the mismatches)
• you may have to make other changes in other code to suit, pay special attention to the id column as the id will not be generated as may have been expected.
• I personally do not recommend trying to match the Entity (the Expected) to an existing table ( the Found). So I would suggest considering the following to adjust the pre-packaged database to suit the Entity :-

The SQL generated after the above changes are made is :-

CREATE TABLE IF NOT EXISTS `lexeme` (
    `id` TEXT NOT NULL, 
    `form` TEXT, 
    `formNoAccent` TEXT, 
    `formUtf8General` TEXT, 
    `reverse` TEXT NOT NULL, /* <<<<< ANOTHER MISMATCH SPOTTED */
    `number` INTEGER, 
    `description` TEXT, 
    `noAccent` TEXT, 
    `consistentAccent` TEXT, 
    `frequency` TEXT, 
    `hyphenations` TEXT, 
    `pronunciations` TEXT, 
    `stopWord` TEXT, 
    `compound` TEXT, 
    `modelType` TEXT, 
    `modelNumber` TEXT, 
    `restriction` TEXT, 
    `staleParadigm` TEXT, 
    `notes` TEXT, 
    `hasApheresis` TEXT, 
    `hasApocope` TEXT, 
    `createDate` TEXT, 
    `modDate` TEXT, 
    PRIMARY KEY(`id`)
    )

• Note using the above, as the reverse column was the odd one out, checked the found and saw that it too doesn't match (so changed ). Hence why I suggest not trying to match Entity to pre-packaged but use Room's generated SQL to create/amend the pre-packaged.

If you have a lot of data in the pre-packaged database then what you could do is use SQL to (if you have no or little data then just drop the lexeme table and create as per step 2):-

1. Rename the lexeme table e.g.

   ALTER TABLE lexeme RENAME TO lexeme_old;

2. Create the lexeme using the SQL for the lexeme table as copied from the generated java.

3. Copy the data from the lexeme_old table to the lexeme table e.g.

   INSERT INTO lexeme (SELECT * FROM lexeme_old);

4. DROP the lexeme_old table using DROP TABLE IF EXISTS lexeme_old

5. Optionally use the SQL VACUUM;

6. Close the database, exit the tool, start the tool, open/connect the database check that the changes have been applied, close the database then copy the pre-packaged database to the app.

• note that as the id column is TEXT then if any of the values are not valid integers then you will get an SQLITE_MISMATCH error.

• note the above assumes that there are no other entities/tables or triggers which could complicate matters (especially if the Lexeme tables is a parent to other tables).

• note, the above is not necessarily, efficient (if you look at the example linked below it drops indexes, triggers and views and doesn't create them until the data has been copied)

If you do get the SQLITE_MISMATCH then you can do one of the following:-

1. Change the id column from val id: Long to val id: String AND remove the line @ColumnInfo(typeAffinity = ColumnInfo.INTEGER).
2. change the SQL used in step 3 to be

:-

INSERT INTO lexeme 
    (
        `form`,
        `formNoAccent`,
        `formUtf8General`,
        `reverse`,
        `number`,
        `description`,
        `noAccent`,
        `consistentAccent`,
        `frequency`,
        `hyphenations`,
        `pronunciations`,
        `stopWord`,
        `compound`,
        `modelType`,
        `modelNumber`,
        `restriction`,
        `staleParadigm`,
        `notes`,
        `hasApheresis`,
        `hasApocope`,
        `createDate`,
        `modDate`
    ) 
    SELECT 
        `form`,
        `formNoAccent`,
        `formUtf8General`,
        `reverse`,
        `number`,
        `description`,
        `noAccent`,
        `consistentAccent`,
        `frequency`,
        `hyphenations`,
        `pronunciations`,
        `stopWord`,
        `compound`,
        `modelType`,
        `modelNumber`,
        `restriction`,
        `staleParadigm`,
        `notes`,
        `hasApheresis`,
        `hasApocope`,
        `createDate`,
        `modDate` 
    FROM lexeme_old
;

• note the SQL has not been checked, consider it as in-principle.

The above steps could be undertaken in whatever tool you are using/did use to create the pre-packaged database.

Another option would be to make the changes on the fly using the prePackagedDarabaseCallback. This is an example of using the callback that actually does the steps above with just a few changes. However, it is written in java and it also requires at least Room version 2.4.0-beta02 (suggest using 2.4.0 as it's now been released).