Changed data-type in room, how to do a migration?

Question

I have changed a column type from Float to Int, how can I migrate the change so I won't lost old entries, instead just to convert all of them to the Int.

Answer 1

You need to add a Migration that will create the table in it's new form and copy the data from it's old form.

The SQL for the new form can be ascertained by looking at the generated java (visible from the Android View of Android Studio). Look at the class that is named the same as the class that is annotated with @Database but suffixed with _Impl, and then find the SQL in the method named createAllTables

You could then use the following

:-

1. DROP, just in case the intermediate old table e.g. DROP TABLE IF EXISTS table_old;
2. RENAME the original table using SQL based upon ALTER TABLE the_table RENAME TO the_table_old;
3. Create the new table using the SQL as obtained above
4. Copy the data using SQL based upon INSERT INTO the_table SELECT * FROM the_table_old;
5. DROP the now defunct old table e.g. DROP TABLE IF EXISTS table_old;;

Demo

As an example where the entity is (was commented out) :-

@Entity(tableName = "jourTable")
class Note(
    @ColumnInfo(name = "title") val jourTitle:String,
    @ColumnInfo(name = "description") val jourDescription:String,
    @ColumnInfo(name = "date") val jourDate:String,
    @ColumnInfo(name = "image", typeAffinity = ColumnInfo.BLOB) val jourImage: Bitmap?, //<<<<< will use the TypeConverter
    //@ColumnInfo(name = "altImage") val jourAltImage: ByteArray //<<<<< will not use the TypeConverter
    @ColumnInfo(name = "altImage") val jourAltImage: Int
) {
    @PrimaryKey(autoGenerate = true)var id=0
}

• i.e. commented out jourAltImage was ByteArray now to be Int (INTEGER type in SQL)

and the generated java is obtained via :-

The the @Database annotated class (TheDatabase) has :-

@TypeConverters(ImageConverter::class)
@Database(entities = [Note::class], version = 2 /*<<<<<<<<<< INCREASE FROM 1 to 2 (or as required)*/, exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDao

    companion object {
        @Volatile
        private var instance: TheDatabase ? = null
        fun getInstance(context: Context): TheDatabase {
            if (instance == null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java,"the_database.db")
                    .allowMainThreadQueries()
                    .addMigrations(MIG_1_2) //<<<<<<<<<< ADD the migration
                    .build()
            }
            return instance as TheDatabase
        }
        /*<<<<<<<<<< The Migration >>>>>>>>>> */
        val MIG_1_2 = object: Migration(1,2){
            override fun migrate(db: SupportSQLiteDatabase) {
                db.execSQL("DROP TABLE IF EXISTS jourTable_old;")
                db.execSQL("ALTER TABLE jourTable RENAME TO jourTable_old ")
                /* SQL ON NEXT LINE COPIED FROM GENERATED JAVA */
                db.execSQL("CREATE TABLE IF NOT EXISTS `jourTable` (`title` TEXT NOT NULL, `description` TEXT NOT NULL, `date` TEXT NOT NULL, `image` BLOB, `altImage` INTEGER NOT NULL, `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL)")
                db.execSQL("INSERT INTO jourTable SELECT * FROM jourTable_old")
                db.execSQL("DROP TABLE IF EXISTS jourTable_old")
            }
        }
    }
}

The when run App Inspection shows (it had 1 row) and the app adds 1 new row when run :-

• as cab seen the altimage column is now INTEGER (was BLOB) and the 1 row has been retained.