Room Database Migration didn't properly handle conversion

Question

I am trying to migrate from Sqlite to Room but I am getting a migration error, I believe it is due to the fact that I used VARCHAR() (I followed a guide, definitely wish I would have just used TEXT, but can't fix that now).

Here is my entity:

@Entity(tableName = "conversation")
class Conversation(@PrimaryKey
               @ColumnInfo(name = "key") val key: Int,
               @ColumnInfo(name = "message_id") val messageId: String?,
               @ColumnInfo(name = "thread_id") val threadId: String?,
               @ColumnInfo(name = "address") val address: String?,
               @ColumnInfo(name = "group_address") val groupAddress: String?,
               @ColumnInfo(name = "name") val name: String?,
               @ColumnInfo(name = "group_name") val groupName: String?,
               @ColumnInfo(name = "body") val body: String?)

Here is the error that I am getting:

Expected:
TableInfo{name='conversation', columns={date=Column{name='date', type='INTEGER', notNull=false, primaryKeyPosition=0}, address=Column{name='address', type='TEXT', notNull=false, primaryKeyPosition=0}.....
 Found:
TableInfo{name='conversation', columns={date=Column{name='date', type='INTEGER', notNull=false, primaryKeyPosition=0}, address=Column{name='address', type='VARCHAR(14)', notNull=false, primaryKeyPosition=0}....

And it is just a whole bunch of that, and I am not really sure of how to convert VARCHAR to text or anything like that

Edit: Migration Method

override fun migrate(database: SupportSQLiteDatabase) {
            database.execSQL(TexpertDatabase.CONVO_CREATE) //Creates table named conversation_temp with preferred schema
            database.execSQL("INSERT INTO conversation_temp SELECT ${TexpertDatabase.allCols} FROM conversation")
            database.execSQL("DROP TABLE conversation")
            database.execSQL("ALTER TABLE conversation_temp RENAME TO conversation")
        }

score 2 · Answer 1 · answered Apr 30 '18 at 08:50

2

Normally, relational databases provides ALTER TABLE command which allow you to change the data type of a column. But sadly, SQLite offers a very limited ALTER TABLE support, which involves only :

renaming a table
adding a new column.

So, the standard (and maybe only) way of doing this is to

create a temporary table with the desired schema
copy all of the data from old table to this new table
delete the old table
rename the new table to the desired name

All this code will come in the migrate function of the Migration that you'll be providing to room.

answered Apr 30 '18 at 08:50

Yash

5,225
4
32
65

Thank you for this, I have added my migration method above, but I now have a new issue which is the fact that I did not create a primary key in the original database, and now Room requires there to be one. I have tried a number of different methods of adding a primary key to the current database but I could not find a way to insert an incrementing number into the existing database without taking an incredibly long time manually inserting it. I am thinking now that the best bet is to stick with what I have now. – Nick Mowen Apr 30 '18 at 22:02
Did you try declaring an int primary key with autoincrement property? See this: https://commonsware.com/AndroidArch/previews/the-dao-of-entities. – Yash May 01 '18 at 06:56
No I did not, I am definitely going to try this as soon as possible – Nick Mowen May 01 '18 at 13:43
I tried using autoincrement but that did not work. The guide you shared had a tip below that which recommended creating a composite primary key from other values. This seems perfect for me but for some reason, astonishingly, the migration complains that there are not unique values (which makes no sense, they definitely should be) – Nick Mowen May 01 '18 at 15:33

score 2 · Accepted Answer · answered May 14 '18 at 16:14

Room 1.1 added support for Sqlite types that were not in Room so that fixed all issues except the primary key. Ultimately it was a migration that worked out to solve the problem, took a lot of working but here is the code that finally worked out, if anyone else has this problem:

override fun migrate(database: SupportSQLiteDatabase) {
        //Creates table named conversation_temp with copy of table from before, and adds a PRIMARY KEY AUTO INCREMENT to that
        database.execSQL(TexpertDatabase.CONVO_CREATE) 
        database.execSQL("INSERT (column, names, listed) INTO conversation_temp SELECT * FROM conversation")
        database.execSQL("DROP TABLE conversation")
        database.execSQL("ALTER TABLE conversation_temp RENAME TO conversation")
    }

Room Database Migration didn't properly handle conversion

2 Answers2

Linked