java - room migration using existing boolean column types

Question

What i found out so far

All the @entity annotated classes are processed during compiletime and an Implementation for Database class is generated. Then before accessing the db, validateMigration method of this generated class is called. This validateMigration method verifies with the existing db schema via raw query

PRAGMA table_info mytable name

(see L208 of android.arch.persistence.room.util.TableInfo.java)

Now the problem

My sqlite3 db has some columns with column type as BOOLEAN. (which slqite internally handles to int). Now when i create room entities say

public someEntity {
     @columnInfo(name="someName")
     public Boolean myValue;
}

The room's create table query will be

Create Table someEntity ( myValue INTEGER)

Where as when we query the existing db with PRAGMA table_info someEntity we get

1|myValue|BOOLEAN|0||0

As explained above room verifies the ( sqlite to room ) migration by comparing field name, column type etc. And since the column types dont match (BOOLEAN and INTEGER) it throws an error saying migration failed.

Can anyone suggest a workaround to this ? Can we make room create BOOLEAN column type in sqlite ? (Also afaik we can't change/alter column types of existing tables.)

PS: I also see a similar issue with VARCHAR - Using an existing VARCHAR column with Room

Answer 1

Define the migration for the new attribute newAttribute with both a DEFAULT value and as NOT NULL.

Code

database.execSQL("ALTER TABLE tableName ADD COLUMN newAttribute INTEGER DEFAULT 0 NOT NULL")

Full Code

@Database(entities = arrayOf(ModelName::class), version = 2)
@TypeConverters(Converters::class)
abstract class DatabaseName : RoomDatabase() {

    abstract fun daoName(): DaoName

    companion object {

        private var INSTANCE: DatabaseName? = null

        fun getAppDatabase(context: Context): DatabaseName {
            if (INSTANCE == null) {
                INSTANCE = Room.databaseBuilder(context.applicationContext,
                        DatabaseName::class.java, DATABASE_NAME)
                        .addMigrations(MIGRATION_1_2)
                        .build()
            }
            return INSTANCE as DatabaseName
        }

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

}