kotlin android-room android-room-prepackageddatabase

Room Pre-packaged database has an invalid schema ERROR of Expect type=Integer Found=Boolean

I'm a bit lost with this issue.

The Pre-packaged database has an invalid schema error has the following output:

Expected

TableInfo{name='account', columns={client_alt_phone_on_route_sheets=Column{name='client_alt_phone_on_route_sheets', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='0'}, client_titles_on_address_labels=Column{name='client_titles_on_address_labels', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='0'}, client_titles_on_invoices=Column{name='client_titles_on_invoices', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='0'}}, foreignKeys=[], indices=[]}

Found

TableInfo{name='account', columns={client_alt_phone_on_route_sheets=Column{name='client_alt_phone_on_route_sheets', type='BOOLEAN', affinity='1', notNull=true, primaryKeyPosition=0, defaultValue='FALSE'}, client_titles_on_address_labels=Column{name='client_titles_on_address_labels', type='BOOLEAN', affinity='1', notNull=false, primaryKeyPosition=0, defaultValue='FALSE'}, client_titles_on_invoices=Column{name='client_titles_on_invoices', type='BOOLEAN', affinity='1', notNull=false, primaryKeyPosition=0, defaultValue='FALSE'}}, foreignKeys=[], indices=[]}

I'm ommitting some other columns because they are not the problem and their output matches. The problem is with the columns that expected INTEGER but found BOOLEAN.

The database schema is as follows:

CREATE TABLE account
(
    client_alt_phone_on_route_sheets  BOOLEAN DEFAULT FALSE NOT NULL,
    client_titles_on_address_labels   BOOLEAN DEFAULT FALSE,
    client_titles_on_invoices         BOOLEAN DEFAULT FALSE,
    // Omitted rows
);

Initially, I did created the account Room @Entity with the BOOLEAN columns of type Boolean:

@Entity(tableName = "account")
data class Account(
    // Omitted data
    // @FIXME
    @ColumnInfo(name = "client_alt_phone_on_route_sheets", defaultValue = "FALSE") val routeSheetsClientAltPhone: Boolean,

    // @FIXME
    @ColumnInfo(name = "client_titles_on_address_labels", defaultValue = "FALSE") val clientTitlesOnAddressLabels: Boolean?,

    // @FIXME
    @ColumnInfo(name = "client_titles_on_invoices", defaultValue = "FALSE") val clientTitlesOnInvoices: Boolean?,
)

Then, the first time the error was thrown I did changed the Boolean type columns to Int type:

@Entity(tableName = "account")
data class Account(
    // Omitted data
    // @FIXME
    @ColumnInfo(name = "client_alt_phone_on_route_sheets", defaultValue = "0") val routeSheetsClientAltPhone: Int,

    // @FIXME
    @ColumnInfo(name = "client_titles_on_address_labels", defaultValue = "0") val clientTitlesOnAddressLabels: Int?,

    // @FIXME
    @ColumnInfo(name = "client_titles_on_invoices", defaultValue = "0") val clientTitlesOnInvoices: Int?,
)

However, the error kept happening. I tried with this answer migrating a single column to see if it then matches in the expected/found output.

        private val MIGRATION_1_2 = object : Migration(1,2) {
            override fun migrate(database: SupportSQLiteDatabase) {
                database.execSQL(
                    "ALTER TABLE account ADD COLUMN client_alt_phone_on_route_sheets INTEGER NOT NULL DEFAULT(0)"
                )
            }
        }

        private fun buildDatabase(context: Context) = Room.databaseBuilder(
            context.applicationContext,
            Database::class.java,
            Constants.DATABASE_NAME
        ).addMigrations(MIGRATION_1_2).createFromAsset("database.db").build()

But apparently migration did not work, nor changed anything in the output because the error is still the same. Also, I'm not sure which one is the Room database and which one is the database from assets. My hunch is that the 'Found' output is the one to match from assets because of the BOOLEAN type, but that's not completely clear. Why does it seems like changing the Room @ColumnInfo value type from Boolean to Int doesn't seem to take effect? If migration above needs to be implemented for every column of type BOOLEAN on my database, what is the proper way to apply migration for multiple tables? (since I have more tables that has this BOOLEAN type, though they're not in the error)

Solution

Why does it seems like changing the Room @ColumnInfo value type from Boolean to Int doesn't seem to take effect?

Because to Room they are the same type that is any type Long/long -> Boolean that is an integer (as opposed to decimal) is given a column type of INTEGER in the create table SQL.

Room will only create column types of INTEGER, TEXT, REAL or BLOB.

SQLite however is flexible in that you can create columns with virtually any type (as long s the type doesn't break parser rules such as being a keyword). SQLite then uses a set of rules to assign a type affinity (one of the 4 listed above or the catch-all NUMERIC (which room does not support/allow)).

So from the above the issue is with the pre-packaged database. That is the pre-packaged database has a column defined with BOOLEAN room expects INTEGER and therefore you MUST change the column type from BOOLEAN to INTEGER.

You can make this change by using:-

ALTER TABLE account RENAME TO account_old;
CREATE TABLE IF NOT EXISTS account (client_alt_phone_on_route_sheets INTEGER NOT NULL DEFAULT VALUE false,  .....);
INSERT INTO account SELECT * FROM account_old;
DROP TABLE IF EXISTS account_old;

Note you can obtain the CREATE TABLE SQL from room if you compile the project after creating the entities and the @Database annotated class with the entities defined in the list of entities.
After a successful compilation Room will have generated some java code in the class named the same as the @Database annotated class but suffixed with _Impl. In this class there will be a method createAllTables the SQL for the creation of the tables (indexes and views). This SQL is what room expects

You have various places where you can alter the table(s), the simplest are to:-

Make the changes (as above) in the pre-packaged database, then copy the changed file into the assets folder, or
Use the prePackagedDatabaseCallback call back see How do I use Room's prepackagedDatabaseCallback? 2. The callback is invoked after copying the file but before the validation (Expected v Found) is performed.