Pre-packaged database has an invalid schema column order

Question

I am attempting to implement Room in my Android application, but am getting the error

java.lang.IllegalStateException: Pre-packaged database has an invalid schema: viewLog(myApp.ViewLogModel).
 
Expected:
TableInfo{name='viewLog', columns={vid=Column{name='vid', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, mid=Column{name='mid', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, uts=Column{name='uts', type='REAL', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}
 
Found:
TableInfo{name='viewLog', columns={VID=Column{name='VID', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, UTS=Column{name='UTS', type='REAL', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='null'}, MID=Column{name='MID', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}}, foreignKeys=[], indices=[]}

I do see the uts and mid columns are switched, however I have not been able to figure out how to change the column order in the TableInfo.

My Room Database code is

@Database(entities = [ViewLogModel::class, CacheInputModel::class, CachePageModel::class], version = 1)
    abstract class MyDatabase : RoomDatabase() {
    abstract fun cacheInputDao(): CacheInputDao
    abstract fun cachePageDao(): CachePageDao
    abstract fun viewLogDao(): ViewLogDao

    companion object {
        // For Singleton Instance
        @Volatile
        private var INSTANCE: MyDatabase? = null

        fun getAppDataBase(context: Context): MyDatabase {
            return INSTANCE ?: synchronized(this) {
                INSTANCE ?: Room.databaseBuilder(context.applicationContext, MyDatabase::class.java, "appDatabase")
                    .createFromAsset("app.db")
                    .build()
            }
        }

        fun destroyDataBase(){
            INSTANCE = null
        }
    }
}

The ViewLogModel is

@Entity(tableName = "viewLog")
data class ViewLogModel(
    @PrimaryKey val mid: Int,
    val vid: Int,
    val uts: Float)

The ViewLogDao is

@Dao
interface ViewLogDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertOrUpdateViewLog(viewLog: ViewLogModel)

    @Update
    suspend fun updateViewLog(viewLog: ViewLogModel)

    @Delete
    suspend fun deleteAllViewLog(viewLog: ViewLogModel)

    @Query("DELETE FROM viewlog WHERE VID= :vid OR UTS < :uts")
    suspend fun deleteViewLog(vid: Int, uts: Float)

    @Query("SELECT * FROM viewLog")
    suspend fun getAll(): List<ViewLogModel>

    @Query("SELECT * FROM viewLog WHERE vid = :vid")
    suspend fun getViewLog(vid: Int): List<ViewLogModel>
}

Can anyone help me with ordering the columns if that is the issue that I'm encountering?

Answer 1

I believe that your issue is not the order but rather the case of the column names.

For example the following database (save as asset appcase.db) defined with :-

CREATE TABLE "viewLog" (
  "MID" INTEGER NOT NULL,
  "UTS" real NOT NULL,
  "VID" INTEGER NOT NULL,
  PRIMARY KEY ("MID")
);

Fails with :-

 Caused by: java.lang.IllegalStateException: Pre-packaged database has an invalid schema: viewLog(a.a.so67711264kotlinroomprepackageddb.ViewLogModel).
 Expected:
TableInfo{name='viewLog', columns={vid=Column{name='vid', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, mid=Column{name='mid', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, uts=Column{name='uts', type='REAL', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}
 Found:
TableInfo{name='viewLog', columns={VID=Column{name='VID', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, UTS=Column{name='UTS', type='real', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='null'}, MID=Column{name='MID', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}}, foreignKeys=[], indices=[]}

However, using a database who's DDL is:-

CREATE TABLE "viewLog" (
  "mid" INTEGER NOT NULL,
  "uts" real NOT NULL,
  "vid" INTEGER NOT NULL,
  PRIMARY KEY ("mid")
)

works as expected.

So you either need to change the DDL as above or change the Entity to reflect the DDL e.g. the following works with the original (failed) DDL:-

@Entity(tableName = "viewLog")
data class ViewLogModel(
    @PrimaryKey val MID: Int,
    val VID: Int,
    val UTS: Float)

or alternately you could use @ColumnInfo's like:-

@Entity(tableName = "viewLog")
data class ViewLogModel(
    @PrimaryKey @ColumnInfo(name = "MID") val mid: Int,
    @ColumnInfo(name ="VID") val vid: Int,
    @ColumnInfo(name = "UTS") val uts: Float)