How to use Date Field from existing SQLite DB in Room with Kotlin

Question

I am trying to use an existing SQLite database that my Kotlin app downloads and then opens with Room. However, I think I am having an issue with a field that has a Date type in the original SQLite.

The SQLite DB:

CREATE TABLE `sets` (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    baseSetSize INTEGER,
    block TEXT,
    booster TEXT,
    cardsphereSetId INTEGER,
    code TEXT(8) UNIQUE NOT NULL,
    isFoilOnly INTEGER NOT NULL DEFAULT 0,
    isForeignOnly INTEGER NOT NULL DEFAULT 0,
    isNonFoilOnly INTEGER NOT NULL DEFAULT 0,
    isOnlineOnly INTEGER NOT NULL DEFAULT 0,
    isPartialPreview INTEGER NOT NULL DEFAULT 0,
    keyruneCode TEXT,
    mcmId INTEGER,
    mcmIdExtras INTEGER,
    mcmName TEXT,
    mtgoCode TEXT,
    name TEXT,
    parentCode TEXT,
    releaseDate DATE,
    sealedProduct TEXT,
    tcgplayerGroupId INTEGER,
    totalSetSize INTEGER,
    type TEXT
)

My Room entity class:

@Entity(tableName = "sets", indices = [Index(value = ["code"], unique = true)])
data class Sets(
    @PrimaryKey @ColumnInfo(name = "id") val id: Int?,
    @ColumnInfo(name = "baseSetSize") val baseSetSize: Int?,
    @ColumnInfo(name = "block") val block: String?,
    @ColumnInfo(name = "booster") val booster: String?,
    @ColumnInfo(name = "cardsphereSetId") val cardsphereSetId: Int?,
    @ColumnInfo(name = "code") val code: String,
    @ColumnInfo(name = "isFoilOnly", defaultValue = "0") val isFoilOnly: Int,
    @ColumnInfo(name = "isForeignOnly", defaultValue = "0") val isForeignOnly: Int,
    @ColumnInfo(name = "isNonFoilOnly", defaultValue = "0") val isNonFoilOnly: Int,
    @ColumnInfo(name = "isOnlineOnly", defaultValue = "0") val isOnlineOnly: Int,
    @ColumnInfo(name = "isPartialPreview", defaultValue = "0") val isPartialPreview: Int,
    @ColumnInfo(name = "keyruneCode") val keyruneCode: String?,
    @ColumnInfo(name = "mcmId") val mcmId: Int?,
    @ColumnInfo(name = "mcmIdExtras") val mcmIdExtras: Int?,
    @ColumnInfo(name = "mcmName") val mcmName: String?,
    @ColumnInfo(name = "mtgoCode") val mtgoCode: String?,
    @ColumnInfo(name = "name") val name: String?,
    @ColumnInfo(name = "parentCode") val parentCode: String?,
    @ColumnInfo(name = "releaseDate") val releaseDate: Date?,
    @ColumnInfo(name = "sealedProduct") val sealedProduct: String?,
    @ColumnInfo(name = "tcgplayerGroupId") val tcgplayerGroupId: Int?,
    @ColumnInfo(name = "totalSetSize") val totalSetSize: Int?,
    @ColumnInfo(name = "type") val type: String?
)

And my database class:

@Database(
    version = 1,
    exportSchema = false,
    entities = [
        Cards::class,
        Sets::class]
)
@TypeConverters(Converters::class)
abstract class PrintingDatabase : RoomDatabase() {
    abstract fun cardsDAO(): CardsDAO
    abstract fun setsDAO(): SetsDAO

    companion object {
        // Singleton prevents multiple instances of database opening at the same time.
        @Volatile
        private var INSTANCE: PrintingDatabase? = null

        fun getInstance(context: Context): PrintingDatabase {
            val dbfile = File(context.dataDir.absolutePath + "/" + MTGJsonWorker.MTGJSON_PRINTING_FILE)
            return INSTANCE ?: synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    PrintingDatabase::class.java,
                    "printing_database"
                )
                    .createFromFile(dbfile)
                    .fallbackToDestructiveMigration()
                    .build()
                INSTANCE = instance
                instance
            }
        }
    }
}

And lastly the Converters class:

object Converters {
    @TypeConverter
    fun fromTimestamp(value: Long): Date? {
        return value?.let { Date(it) }
    }

    @TypeConverter
    fun dateToTimestamp(date: Date?): Long? {
        return date?.time
    }
}

In a CoroutineWorker, I have a method that creates an instance of the DB, and it is here that I get a failure:

java.util.concurrent.ExecutionException: java.lang.IllegalStateException: Pre-packaged database has an invalid schema: sets(net.redlightning.mtgdeck.thirdparty.mtgjson.database.printing.schema.Sets).
                                                                                                                                                                                                              Expected:
TableInfo{name='sets', columns={sealedProduct=Column{name='sealedProduct', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, cardsphereSetId=Column{name='cardsphereSetId', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, code=Column{name='code', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, isFoilOnly=Column{name='isFoilOnly', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='0'}, releaseDate=Column{name='releaseDate', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, isOnlineOnly=Column{name='isOnlineOnly', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='0'}, keyruneCode=Column{name='keyruneCode', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, mcmIdExtras=Column{name='mcmIdExtras', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, tcgplayerGroupId=Column{name='tcgplayerGroupId', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, type=Column{name='type', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, booster=Column{name='booster', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, mtgoCode=Column{name='mtgoCode', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, baseSetSize=Column{name='baseSetSize', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, isPartialPreview=Column{name='isPartialPreview', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='0'}, parentCode=Column{name='parentCode', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, isForeignOnly=Column{name='isForeignOnly', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='0'}, isNonFoilOnly=Column{name='isNonFoilOnly', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='0'}, name=Column{name='name', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, mcmName=Column{name='mcmName', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, block=Column{name='block', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, totalSetSize=Column{name='totalSetSize', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, mcmId=Column{name='mcmId', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[Index{name='index_sets_code', unique=true, columns=[code], orders=[ASC]}]}
                                                                                                     Found:
TableInfo{name='sets', columns={sealedProduct=Column{name='sealedProduct', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, cardsphereSetId=Column{name='cardsphereSetId', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, code=Column{name='code', type='TEXT(8)', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, isFoilOnly=Column{name='isFoilOnly', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='0'}, releaseDate=Column{name='releaseDate', type='DATE', affinity='1', notNull=false, primaryKeyPosition=0, defaultValue='null'}, isOnlineOnly=Column{name='isOnlineOnly', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='0'}, keyruneCode=Column{name='keyruneCode', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, mcmIdExtras=Column{name='mcmIdExtras', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, tcgplayerGroupId=Column{name='tcgplayerGroupId', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, type=Column{name='type', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, booster=Column{name='booster', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, mtgoCode=Column{name='mtgoCode', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, baseSetSize=Column{name='baseSetSize', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, isPartialPreview=Column{name='isPartialPreview', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='0'}, parentCode=Column{name='parentCode', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, isForeignOnly=Column{name='isForeignOnly', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='0'}, isNonFoilOnly=Column{name='isNonFoilOnly', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='0'}, name=Column{name='name', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, mcmName=Column{name='mcmName', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, block=Column{name='block', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, totalSetSize=Column{name='totalSetSize', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, mcmId=Column{name='mcmId', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[Index{name='index_sets_code', unique=true, columns=[code], orders=[ASC]}]}

When I do a text compare to see what the differences are, I find only: In Expected, the column code has a type of 'TEXT' while Found has a type of 'TEXT(8)', which I assume is fine.

Otherwise Expected has column releaseDate has type INTEGER and affinity 3, while in Found it has DATE with affinity 1, and I am thinking this is where the problem is:

releaseDate=Column{name='releaseDate', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}

vs

releaseDate=Column{name='releaseDate', type='DATE', affinity='1', notNull=false, primaryKeyPosition=0, defaultValue='null'}

I have tried messing around with the Converter class, changing the typeAffinity property of the field in the Room Entity, switching it to a String? or Int?, etc and so far no luck.

Am I missing something in my Converters class to properly handle the DATE from the imported SQLite DB? Is there a better way of declaring it in my Entity? How can I get Room to open it correctly?

Answer 1

When I do a text compare to see what the differences are, I find only: In Expected, the column code has a type of 'TEXT' while Found has a type of 'TEXT(8)', which I assume is fine.

I believe that it would not be fine as Room will see TEXT(8) as not being TEXT.

Otherwise Expected has column releaseDate has type INTEGER and affinity 3, while in Found it has DATE with affinity 1, and I am thinking this is where the problem is:

This, I believe, will be the second issue but certainly an issue.

Am I missing something in my Converters class to properly handle the DATE from the imported SQLite DB?

• No the converters are for when inserting or extracting data, they are not involved in updating data from a pre-packaged database. So no change to the converters will overcome the issue(s)

Is there a better way of declaring it in my Entity?

• Room determines the column types (see next)

How can I get Room to open it correctly?

Room is very specific in what it expects, and the original (pre-packaged) database MUST meet those expectations. It expects column types to be one of:-

• TEXT, or
• INTEGER,or
• REAL, or
• BLOB

As such you need to convert the pre-packaged database accordingly. So both columns code and releaseDate need to be changed.

The code column type needs to be changed to a text type of TEXT, and the column releaseDate needs to be changed to a type of INTEGER.

You could apply the changes to the pre-packaged database via an SQLite tool (e.g. Navicate for SQlite, DBeaver, DB Browser(SQLite), SQLiteStudio).

Or you could use the prePackagedDatabaseCallback.

You could make the changes by executing the following:-

DROP TABLE IF EXISTS original_sets; 
ALTER TABLE sets RENAME to original_sets;
CREATE TABLE IF NOT EXISTS sets .... <<<<<<<<<< see notes
INSERT INTO sets SELECT * FROM original_sets;
DROP TABLE IF EXISTS original_sets;
VACUUM; /* Optional */

• If using an SQLite tool then you should then save the resultant database and then use this new file to replace the asset.

• Using an SQLite tool would be the more efficient in regards to the App, as otherwise the conversion has to be undertaken within the App for every install.

Notes

Room, after compiling the project, generates java code. This code can be found from the Android View within Android Studio in the java(generated).

One of the classes will be the same as the @Database annotated class suffixed with _Impl (PrintingDatabase_Impl in your case).

Within that class there will be a method called createAllTables, this method includes a statement per table (@Entity annotated class) to create the table. Copying the and pasting the respective SQL statement(s) will ensure that the table is created according to Room's expectations.

The pre-packagedDatabaseCallback is explained here How do I use Room's prepackagedDatabaseCallback?