If you are using Android Room and want to change the column definition

Room migration is not allowed

I am developing an Android application using "Room" which is a persistence library made by Google. This Room Because it is a relatively new library, the migration function is not good. .. .. What I wanted to do was "change a REAL type column to an INTEGER type", but sqlite's specification that "only adding columns can be done with the ALTER statement" and exquisite mariage were created and realized. I had a hard time doing it ... This article is that memo.

Solution procedure

I will write from the solution procedure first.

1. Change the property type of the entity (in my case, change from Double? To Integer?)
2. Correspond to the build error caused by 1.
3. Increment the version of @ Database
4. Write the migration code
   1. CREATE TABLE
   2. INSERT INTO ... SELECT
   3. DROP TABLE
   4. ALTER TABLE ... RENAME TO ...
5. CREATE INDEX (if there is an index)

I don't think you can understand it very well at this rate, so let's take a look at each detail.

1. Change the property type of an entity

For example, if you have the following Product entity

Product.kt

@Entity(indices = [Index(value = ["name"])])
data class Product(
    @PrimaryKey(autoGenerate = true) var id: Int = 0,
    var name: String? = null,
    var price: Double? = null,
    ...

Change the Double? Of this price to ʻInt?`, And so on. Well, this is natural.

2. Respond to build errors

The change in 1. should (usually) cause a build error, so crush it one by one. How to fix it depends on the implementation, so I will omit it here.

3. Increment the version of @ Database

Maybe you're defining a sub-abstract class for RoomDatabase like this:

AppDatabase.kt

@Database(entities = [Product::class], version = 1)
abstract class AppDatabase : RoomDatabase() {

    companion object {

        @Volatile private var instance: AppDatabase? = null

        fun getInstance(context: Context): AppDatabase {
            return instance ?: synchronized(this) {
                instance ?: buildDatabase(context).also { instance = it }
            }
        }

        private fun buildDatabase(context: Context): AppDatabase {
            return Room.databaseBuilder(context.applicationContext, AppDatabase::class.java, "hogehoge-database")
                .build()
        }
    }
}

Increase the version here by one. In this example, it is set to 2. You're going to set the schema version of the database to 2.

4. Write the migration code

This was the most annoying smell. Room does almost nothing about migration. As a result, you need to write raw SQL. Since the specification of sqlite is "columns cannot be changed or deleted", the method is to define a new table, transfer the current data to it, delete the old table, and then rename the new table to its original name. I will do it. Lastly, this is a pitfall, but the index that was set on the old table when it was deleted is also deleted, so you have to create this after creating the new table.

First of all, the CREATE TABLE statement, but I think it is very troublesome to write from scratch, so let's use the schema output json of Room. If there is the following description in ʻapp / build.gradle`, the schema information json will be spit out at build time.

app/build.gradle

android {
        ...
        javaCompileOptions {
            annotationProcessorOptions {
                arguments = ["room.schemaLocation": "$projectDir/schemas".toString()]
            }
        }

When building with this, schema information will be output as json under ʻapp / schema`. The file name is * schema version * .json.

Looking at this json, I think that the CREATE TABLE statement is defined in the key createSql. It's easy to copy it and make the necessary changes.

So, the actual migration code is as follows. Taking the example of ʻAppDatabase.kt` earlier,

AppDatabase.kt

...
        private fun buildDatabase(context: Context): AppDatabase {
            val migration1to2 = object : Migration(1, 2) {
                override fun migrate(database: SupportSQLiteDatabase) {
                    database.execSQL("CREATE TABLE IF NOT EXISTS `Appropriate table name` ...")
                }
            }

            return Room.databaseBuilder(context.applicationContext, AppDatabase::class.java, "onieasy-database")
                .addMigrations(migration1to2)
                .build()
        }
...

Replace the schema version with the actual one.

You can rename it later, so you can use the appropriate table name. Next to CREATE TABLE

• INSERT INTO ... SELECT
• DROP TABLE
• ALTER TABLE ... RENAME TO

is not it.

            val migration1to2 = object : Migration(1, 2) {
                override fun migrate(database: SupportSQLiteDatabase) {
                    database.execSQL("CREATE TABLE IF NOT EXISTS Appropriate table name...")
                    database.execSQL("INSERT INTO Appropriate table name SELECT*FROM The table name you want to change")
                    database.execSQL("DROP TABLE The name of the table you want to change")
                    database.execSQL("ALTER TABLE Appropriate table name RENAME TO Table name you want to change")
                }
            }

Finally, if it was originally indexed, copy it from within the schema json. There should be createSql in ʻindices`.

                    database.execSQL("CREATE INDEX index_Product_name` ON `Product` (`name`)")

It's like that.

Let's do it all at once, build it, and run the app. If you can change it correctly, it should work normally.

Why is it so complicated

Room is still developing, so I think there are quite a lot of things to do. The first thing I came up with was to define a new entity in Kotlin and transfer the data to that new entity. I tried it, but the result is that the app does not start ... How, ** Room creates a table from an entity only in the case of schema version 1 (that is, the first time), but after that, the table is generated. It doesn't generate automatically! ** What the hell ...

That's why when using Room, if you don't design the table exactly from the beginning, it will be troublesome, yes, I learned.