Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

room migration using existing boolean column types

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

like image 339
BruceWayne Avatar asked Jan 15 '18 11:01

BruceWayne


People also ask

How do I move a room DB?

You can use AutoMigrationSpec to give Room the additional information that it needs to correctly generate migration paths. Define a static class that implements AutoMigrationSpec in your RoomDatabase class and annotate it with one or more of the following: @DeleteTable. @RenameTable.

Why should I migrate from SQLite to room database in Android?

The Room persistence library provides a number of benefits over using the SQLite APIs directly: Compile-time verification of SQL queries. Convenience annotations that minimize repetitive and error-prone boilerplate code.

What is migration in Android?

android.arch.persistence.room.migration.Migration. Base class for a database migration. Each migration can move between 2 versions that are defined by startVersion and endVersion . A migration can handle more than 1 version (e.g. if you have a faster path to choose when going version 3 to 5 without going to version 4).


2 Answers

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")
            }
        }
    }

}
like image 139
Adam Hurwitz Avatar answered Oct 03 '22 16:10

Adam Hurwitz


SQLite does not have a boolean data type. Room maps it to an INTEGER column, mapping true to 1 and false to 0. I think below code would be work

database.execSQL("ALTER TABLE xyz_table ADD COLUMN abc INTEGER DEFAULT 0");

It is undocumented.

like image 37
Suman Avatar answered Oct 02 '22 16:10

Suman