CODEWITHSUNDEEP
androiddatabasedatabase-migrationandroid-room
vtodorova
vtodorova

Reputation: 209

Migrating from SQLite to Room: Migration didn't properly handle table

So I was using the SQLite database and now I decided to migrate it to Room, without changing it. This is what my room model looks like

@Entity(tableName = "documents")
data class Document(
    @PrimaryKey(autoGenerate = true)
    val docID: Int,
    val path: String,
    val filename: String,
    val dateCreated: Long = MAX_VALUE,
)

and this is the query I was using to create the table BEFORE room:

"CREATE TABLE IF NOT EXISTS documents (docID INTEGER PRIMARY KEY ASC, path TEXT NOT NULL unique, filename TEXT NOT NULL, dateCreated LONG DEFAULT MAX_VALUE)"

also, I created a new migration to the final version of the database, which uses the room, which I left empty because I don't wanna change anything in the table.

private val MIGRATION_9_10: Migration = object : Migration(9, 10) {
    override fun migrate(database: SupportSQLiteDatabase) {

    }
}

so when I finally run it on the device, which had the previous (9) version of the database, an exception is thrown:

java.lang.IllegalStateException: Migration didn't properly handle: documents.

Expected: TableInfo{name='documents', columns={path=Column{name='path', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, filename=Column{name='filename', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, dataCreated=Column{name='dataCreated', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, docID=Column{name='docID', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}}, foreignKeys=[], indices=[]}

Found: TableInfo{name='documents', columns={path=Column{name='path', type='STRING', affinity='1', notNull=true, primaryKeyPosition=0, defaultValue='null'}, filename=Column{name='filename', type='STRING', affinity='1', notNull=true, primaryKeyPosition=0, defaultValue='null'}, dataCreated=Column{name='dataCreated', type='LONG', affinity='1', notNull=false, primaryKeyPosition=0, defaultValue='9223372036854775807'}, docID=Column{name='docID', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}}, foreignKeys=[], indices=[]}

there are differences between the types of the path and filename fields, which are expected to be TEXT, but actually are STRING and also the type of dateCreated, which is expected to be INTEGER but is actually LONG. The interesting part for me is that dateCreated was of type long before and is still of type long. I tried adding a migration, which is as follows:

private val MIGRATION_9_10: Migration = object : Migration(9, 10) {
    override fun migrate(database: SupportSQLiteDatabase) {
        val tableDocumentsTemp = "documents_temp"
        database.execSQL("ALTER TABLE documents RENAME TO $tableDocumentsTemp")
        database.execSQL("CREATE TABLE documents (docId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, path STRING NOT NULL, filename STRING NOT NULL, dateCreated LONG DEFAULT $MAX_VALUE)")
        database.execSQL("INSERT INTO documents (docId, path, filename, dateCreated) SELECT docId, path, filename, dateCreated FROM $tableDocumentsTemp")
        database.execSQL("DROP TABLE $tableDocumentsTemp")
        }
    }
}

but this makes no difference.

Anybody has any ideas on how I can fix it?

Upvotes: 0

Views: 333

Answers (1)

Amit Tiwary
Amit Tiwary

Reputation: 797

When you write room database migration code, you have to use TEXT for String and INTEGER for long data type.

private val MIGRATION_9_10: Migration = object : Migration(9, 10) {
override fun migrate(database: SupportSQLiteDatabase) {
    val tableDocumentsTemp = "documents_temp"
    database.execSQL("CREATE TABLE $tableDocumentsTemp (docId INTEGER NOT NULL, path TEXT NOT NULL, filename TEXT NOT NULL, dateCreated INTEGER DEFAULT $MAX_VALUE NOT NULL, PRIMARY KEY(docId))")
    database.execSQL("INSERT INTO $tableDocumentsTemp (docId, path, filename, dateCreated) SELECT docId, path, filename, dateCreated FROM documents")
    database.execSQL("DROP TABLE documents")
    database.execSQL("ALTER TABLE $tableDocumentsTemp RENAME TO documents")
    }
 }
}

Upvotes: 1

Related Questions