How do I perform the Android Room Database migration by changing existing data types?

Question

I use the Room Database in my Android app that provides a table of Car as follows:

@Entity(tableName = "Cars")
data class Car(
    @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "Id") val id: Int? = null,
    @ColumnInfo(name = "Manufacture") var manufacture: String? = null, 
    @ColumnInfo(name = "Model") var model: String? = null,
    @ColumnInfo(name = "Date_created") val dateCreated: String? = null,
    @ColumnInfo(name = "Date_modified") var dateModified: String? = null,
    @ColumnInfo(name = "Color") var color: String? = null,
    @ColumnInfo(name = "Tags") var tags: String? = null
)

Now I need to perform some changes with that table and upgrade it as follows: and now I need to apply some changes within table:

@Entity(tableName = "Cars")
data class Car(
    @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "Id") var id: Long = 0,
    @ColumnInfo(name = "Manufacture") var manufacture: String = "", 
    @ColumnInfo(name = "Model") var model: String = "",
    @ColumnInfo(name = "Labels") var labels: List<String> = listOf(),
    @ColumnInfo(name = "Color") var color: String? = null,
    @ColumnInfo(name = "Timestamp") var timestamp: Long = 0L,
    @ColumnInfo(name = "Type") var type: Type = Type.Sedan,
    @ColumnInfo(name = "Folder") var folder: Folder = Folder.Used
)

That upgrade brings the following changes of existing table:

• Column Id: val id: Int? = null changed to val id: Long = 0.
• Column Manufacture: var manufacture: String? = null changed to var manufacture: String = "".
• Column Date_created: var dateCreated: String? = null has been removed.
• Column Date_modified renamed to Timestamp and its data type also changed from nullable String to a non-nullable Long. Is it possible to convert String to Long while migrating? Those String values are timestamps converted to strings - timestampe.toString().
• Column Tags of data type var tags: String? = null (tags in one string separated by comma: e.g. "Gas,Diesel,Oil") changed to Labels of a list var labels: List<String> = listOf(). Is it possible to convert the string of tags into List while migration?
• Columns Type and Folder are new Columns of data type enum.

I am asking questions to some data types, which must be changed to another data type, because I could not find any infos to this topic. Nevertheless, I do a small a workaround based on Migrate your Room database and Understanding migrations with Room, but not sure about syntax and data type converts:

@Database(entities = [Car::class], version = 2, exportSchema = false)
abstract class CarsDatabase : RoomDatabase() {

    abstract val carDao: CarDao

    companion object {

        @Volatile
        private var INSTANCE: CarsDatabase? = null

        private val migration = object : Migration(1, 2) {
            override fun migrate(database: SupportSQLiteDatabase) {
                database.apply {
                    execSQL(
                        "CREATE TABLE cars_tmp (" +
                                "Id INTEGER, " +
                                "Manufacture TEXT, " +
                                "Model TEXT, " +
                                "Timestamp LONG, " + // Not sure about syntax for Long
                                "Labels LIST," + // Not sure about syntax for List<String>
                                "PRIMARY KEY(Id)" +
                                ")"
                    )
                    execSQL("INSERT INTO cars_tmp (" +
                            "Id, Manufacture, Model, Timestamp, Labels, Color) SELECT " +
                            "Id, Manufacture, Model, Date_modified, Tags, Color" + // String of "Date_modified" being converted to Long of "Timestamp" and String of "Tags" being converted to List<String>
                            "FROM Cars"
                    )
                    execSQL("DROP TABLE Car")
                    execSQL("ALTER TABLE cars_tmp RENAME TO Car")
                    execSQL("ALTER TABLE Car ADD COLUMN Type TEXT")
                    execSQL("ALTER TABLE Car ADD COLUMN Folder TEXT")
                }
            }
        }
        
        fun getInstance(context: Context): CarsDatabase = INSTANCE ?: synchronized(this) {
            val instance = Room.databaseBuilder(
                context.applicationContext,
                CarsDatabase::class.java,
                "Cars.db"
            )
                .addMigrations(migration)
                .build()
            INSTANCE = instance
            return instance
        }
    }
}

I am pretty new to SQL syntax and therefore, my migration code is faulty.

Edit

The answer can be found here: How to correctly get the path of Room database on os versions >= 26 sdk?

Answer 1

Assuming that you are making changes and need to preserve data (if you don't need to preserve any data then simply make the changes and uninstall the App).

The simplest way without having a need to workout/guess what Room expects is to use SQL that Room generates.

1. First make the changes to the table(s) i.e. the @Entity annotated classes.
2. Successfully compile the Project.
3. In Android View you will see in the project explorer of Android Studio and there will be a java(generated) expand this until you find a class that is the same name as the @Database annotated class but suffixed with _Impl.
   1. in your case CarsDatabase_Impl
4. In the class there will be a method (java equivalent of a function) that is called createAllTables. There will be a _db.execSQL("...."); for each component (table, index etc).
   1. ignore room_master_table, Room will manage this table
5. use the SQL (represented by ....) as the basis for the Migration. It is the EXACT SQL that Room would use and thus conforms to the expected (actually required) Schema.
   1. i.e. the exact data types and importantly the exact constraints are determined on your behalf.