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

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.

Recommended Posts

If you are using Android Room and want to change the column definition
If you want to change the Java development environment from Eclipse
If you want to recreate the instance in cloud9
When you want to change the MySQL password of docker-compose
[PostgreSQL] If you want to delete the Rails app, delete the database first!
If you want to include the parent class in Lombok's @builder
If you are new to Rails and want to make your own validation, stop by this finger.
If you want to mock a method in RSpec, you should use the allow method for mock and the singleton method.
[Android] How to turn the Notification panel on and off using StatusBarManager
[Rails] How to create a table, add a column, and change the column type
If you want to satisfy the test coverage of private methods in JUnit
If you want to modify database columns etc.
When you want to use the method outside
Introduce docker to the application you are creating
[Java] What to do if the contents saved in the DB and the name of the enum are different in the enum that reflects the DB definition
If you want to know the options when configuring Ruby, see `RbConfig :: CONFIG ["configure_args "]`
If you want to separate Spring Boot + Thymeleaf processing
[Ruby on Rails] How to change the column name
Change the database (MySQL) primary key to any column.
I want to judge the range using the monthly degree
[Rails] How to change the column name of the table
If you want to use Mockito with Kotlin, use mockito-kotlin
I want to call the main method using reflection
Are you using the default method of the interface properly?
I want to simplify the log output on Android
What to do if you change the Listen Address from the settings screen with Mattermost docker
When you want to change the wording to be displayed when making a select box from enum
What to do if you installed Ruby with rbenv but the version does not change
[Swift5] What to do if you want to commit files to github but there are too many
Memo that transitions to the login screen if you are not logged in with devise
When you want to reflect the Master Branch information in the Current Branch you are currently working on
What if the results of sum and inject (: +) are different?
[Rails] [bootstrap] I want to change the font size responsively
If you want to study programming at university, go to Australia
I want to bring Tomcat to the server and start the application
[# 3 Java] Read this if you want to study Java! ~ Carefully selected ~
I want to change the log output settings of UtilLoggingJdbcLogger
Android app to select and display images from the gallery
You are currently using Java 6. Solution in Android Studio Gradle
When you want to explicitly write OR or AND with ransack
[Android] Change the app name and app icon for each Flavor
What to do if you can't use the rails command
[Swift] When you want to know if the number of characters in a String matches a certain number ...
[For super beginners] The minimum knowledge you want to keep in mind with hashes and symbols
If you just want to run your containers in the cloud, Azure Container Instances is easy