Android Room Migration How to read existing data in migration method?

Question

This is my actual use case.

To change an Entity like this,

data class Student(
    val enum: EnumType,
    // ...
)

to

data class Student(
    val enum: EnumType,
    val ordinal: Int,
    // ...
)

In the Android Room database.

In other words, to add a new column to have the ordinal value of enum from an existing column data.

There is an existing answer here to create a table with default values and then read database data and modify it accordingly.

I can create the column with a default value.
But how do I read and modify data in the room migration function?

override fun migrate(database: SupportSQLiteDatabase) 

---

For background on why, I am moving the data from a simple enum to a new table and reference the id here as the enum list is growing and I want to dynamically add more to it.

Answer 1

Within the migrate function after the ALTER you would use an UPDATE to apply the value.

A simple example you be to use something like:-

database.execSQL("UPDATE student SET ordinal = 100")

Or you could use (for the same result):-

var cv = ContentValues()
cv.put("ordinal",100)
val result = database.update("Student",cv,null,null)

• Both of the above are probably of no use for your scenario but show how you could update the values (noting that ALL rows will be updated).
• The advantage of the convenience update method/function is that it returns the number of rows that have been updated. However, it does have the disadvantage that it is limited.

You probably want 100 to be a value determined according to other data. Say for example enumType were an Int and you wanted ordinal to be 100 * enumType then you could use

database.execSQL("UPDATAE student SET ordinal = enumType * 100")

So now you are using an expression to determine the value according to the value of an existing row.

You appear to want to update according to whatever value is held in the enumType from another table. Again an expression could facilitate this. For example. Assuming that the other table is called enum_table and it has 2 columns id (which you want extract and then store as the reference) and another named the_value (the value you want to extract/obtain the id for) then you could use:-

database.execSQL("UPDATE student SET ordinal = (SELECT id FROM enum_table WHERE the_value = student.enumType)")

An example of the last cab be found at http://sqlfiddle.com/#!7/9eecb7/10386

It uses the following SQL :-

DROP TABLE IF EXISTS enum_table;
DROP TABLE IF EXISTS student;
CREATE TABLE IF NOT EXISTS student (enumType TEXT);
INSERT INTO student VALUES ('test'),('notest'),('other');
CREATE TABLE IF NOT EXISTS enum_table (id INTEGER PRIMARY KEY, enum_value TEXT UNIQUE);
INSERT OR IGNORE INTO enum_table VALUES (1,'test'),(99,'notest'),(666,'other');
ALTER TABLE student ADD COLUMN ordinal INTEGER DEFAULT 9999999999;
SELECT * FROM student;
UPDATE student SET ordinal = (SELECT id FROM enum_table WHERE enum_value = student.enumType);
SELECT * FROM student;
DROP TABLE IF EXISTS enum_table;
DROP TABLE IF EXISTS student;

So this

1. creates the student table and also the new enum_table that has an id/value pairing.

2. Loads both tables with some testing data with the enumType in the student table holding values that are now setup in the enum_table that can be referenced via the id (the id's being set to noticably different values).

3. ALTERs the student table to add the new ordinal column, the default value being set to 9999999999.

4. To demonstrate the before state all rows are selected from the student table (the ordinal column has the default value)

5. The UPDATE is action followed by selecting all rows from the updated student table; showing that the ordinal column contains the respective id

Answer 2

1. Creating column with a default value

database.execSQL("ALTER TABLE table_name ADD COLUMN `new_column_name` INTEGER  DEFAULT -1 NOT NULL")

2. Updating the data using UPDATE and CASE in SQL

database.execSQL("UPDATE table_name SET `new_column_name` = CASE old_column_name WHEN 'VALUE_1' THEN 1 WHEN 'VALUE_2' THEN 2 WHEN 'VALUE_3' THEN 3 END")

Intentionally omitting ELSE in the SQL CASE since the source of the data will be an enum and the number of options will be known.