I am migrating from SQlite to room in my existing application. Therefore I created migration strategy to create new temporary tables, copy existing data into temporary tables and delete old tables and rename new tables.
After migrating, I checked my exported schema and it is exactly the same as expected schema but I still get the error of my old schema is found and receiving error below. Main differences on the schema errors are "type" and #notNull" but I already corrected them during the migration
Pre-packaged database has an invalid schema: User(com.afl.waterReminderDrinkAlarmMonitor.model.User)
My User Entity
@Entity(tableName = "User" )
data class User(
@PrimaryKey
var id: Int = 0,
var age: Int = 0,
var weight: Int = 0,
var gender: String = "",
var metric: String = "",
var water: Int = 0
)
AppDatabase
abstract class AppDatabase : RoomDatabase() {
abstract fun dao(): Dao
companion object {
@Volatile
private var INSTANCE: AppDatabase? = null
val migration_1_2 = object : Migration(1, 2) {
override fun migrate(database: SupportSQLiteDatabase) {
//COPY USER TABLE
// Create new table
database.execSQL("CREATE TABLE IF NOT EXISTS UserTmp ($COL_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, $COL_WEIGHT INTEGER NOT NULL, $COL_GENDER TEXT NOT NULL,$COL_METRIC TEXT NOT NULL, $COL_AGE INTEGER NOT NULL, $COL_WATER INTEGER NOT NULL)")
// Copy the data
database.execSQL("INSERT INTO UserTmp ($COL_ID, $COL_WEIGHT, $COL_GENDER, $COL_METRIC,$COL_AGE, $COL_WATER) SELECT $COL_ID, $COL_WEIGHT, $COL_GENDER, $COL_METRIC,$COL_AGE, $COL_WATER FROM $TABLE_NAME ")
// Remove the old table
database.execSQL("DROP TABLE $TABLE_NAME")
// Change the table name to the correct one
database.execSQL("ALTER TABLE UserTmp RENAME TO $TABLE_NAME")
//COPY DRUNK TABLE
// Create new table
database.execSQL("CREATE TABLE IF NOT EXISTS DrunkTmp ($COL_ID_DRUNK INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, $COL_DATE_DRUNK TEXT NOT NULL, $COL_TIME_DRUNK TEXT NOT NULL,$COL_DRINK_DRUNK TEXT NOT NULL, $COL_AMOUNT_DRUNK INTEGER NOT NULL ,$COL_METRIC_DRUNK TEXT NOT NULL)")
// Copy the data
database.execSQL("INSERT INTO DrunkTmp ($COL_ID_DRUNK, $COL_DATE_DRUNK, $COL_TIME_DRUNK, $COL_DRINK_DRUNK, $COL_AMOUNT_DRUNK, $COL_METRIC_DRUNK) SELECT $COL_ID_DRUNK, $COL_DATE_DRUNK, $COL_TIME_DRUNK, $COL_DRINK_DRUNK, $COL_AMOUNT_DRUNK, $COL_METRIC_DRUNK FROM $TABLE_NAME_DRUNK")
// Remove the old table
database.execSQL("DROP TABLE $TABLE_NAME_DRUNK")
// Change the table name to the correct one
database.execSQL("ALTER TABLE DrunkTmp RENAME TO $TABLE_NAME_DRUNK")
//COPY NOT TABLE
// Create new table
database.execSQL("CREATE TABLE IF NOT EXISTS NotificationTmp ($COL_ID_NOT INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, $COL_PREF_NOT INTEGER NOT NULL, $COL_START_NOT INTEGER NOT NULL, $COL_FINISH_NOT INTEGER NOT NULL,$COL_INTERVAL_NOT INTEGER NOT NULL)")
// Copy the data
database.execSQL("INSERT INTO NotificationTmp ($COL_ID_NOT, $COL_PREF_NOT, $COL_START_NOT, $COL_FINISH_NOT, $COL_INTERVAL_NOT) SELECT $COL_ID_NOT, $COL_PREF_NOT, $COL_START_NOT, $COL_FINISH_NOT, $COL_INTERVAL_NOT FROM $TABLE_NAME_NOT ")
// Remove the old table
database.execSQL("DROP TABLE $TABLE_NAME_NOT")
// Change the table name to the correct one
database.execSQL("ALTER TABLE NotificationTmp RENAME TO $TABLE_NAME_NOT")
}
}
fun getDatabase(context: Context?): AppDatabase {
val tempInstance = INSTANCE
if (tempInstance != null) {
return tempInstance
}
synchronized(this) {
val instance = Room.databaseBuilder(
context?.applicationContext!!,
AppDatabase::class.java, DATABASE_NAME
)
.addMigrations(migration_1_2)
.build()
INSTANCE = instance
return instance
}
}
}
}
Exported Schema for User
{
"tableName": "User",
"createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER NOT NULL, `age` INTEGER NOT NULL, `weight` INTEGER NOT NULL, `gender` TEXT NOT NULL, `metric` TEXT NOT NULL, `water` INTEGER NOT NULL, PRIMARY KEY(`id`))",
"fields": [
{
"fieldPath": "id",
"columnName": "id",
"affinity": "INTEGER",
"notNull": true
},
{
"fieldPath": "age",
"columnName": "age",
"affinity": "INTEGER",
"notNull": true
},
{
"fieldPath": "weight",
"columnName": "weight",
"affinity": "INTEGER",
"notNull": true
},
{
"fieldPath": "gender",
"columnName": "gender",
"affinity": "TEXT",
"notNull": true
},
{
"fieldPath": "metric",
"columnName": "metric",
"affinity": "TEXT",
"notNull": true
},
{
"fieldPath": "water",
"columnName": "water",
"affinity": "INTEGER",
"notNull": true
}
],
"primaryKey": {
"columnNames": [
"id"
],
"autoGenerate": false
},
"indices": [],
"foreignKeys": []
}
and finally the error I receive it
java.lang.IllegalStateException: Pre-packaged database has an invalid schema: User(com.afl.waterReminderDrinkAlarmMonitor.model.User). Expected: TableInfo{name='User', columns={weight=Column{name='weight', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, gender=Column{name='gender', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, metric=Column{name='metric', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, water=Column{name='water', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, age=Column{name='age', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]} Found: TableInfo{name='User', columns={gender=Column{name='gender', type='VARCHAR(256)', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, metric=Column{name='metric', type='VARCHAR(256)', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, weight=Column{name='weight', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, water=Column{name='water', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, age=Column{name='age', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}
The question misses the pre-populated database, which either resides in assets or elsewhere; as the mismatch is actually in that file and a migration would need to load it and then change the column. As one can see, table User has the wrong data-type for columns gender and metric:
Expected: TableInfo{name='User', columns={
id=Column{name='id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'},
age=Column{name='age', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null',
weight=Column{name='weight', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'},
gender=Column{name='gender', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'},
metric=Column{name='metric', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'},
water=Column{name='water', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}
}}, foreignKeys=[], indices=[]}
Found: TableInfo{name='User', columns={
id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'},
age=Column{name='age', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue ='null',
weight=Column{name='weight', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'},
gender=Column{name='gender', type='VARCHAR(256)', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'},
metric=Column{name='metric', type='VARCHAR(256)', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'},
water=Column{name='water', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}
}}, foreignKeys=[], indices=[]}
Editing the packaged database on a computer might be the least effort.