打造通用 Room 数据库迁移工具

在使用 Android Architecture Components 时对于其中的数据库 Room 的升级想到了一个较为通用的迁移方案，使用了一段时间，在此分享一下。

转载请注明出处：https://blog.csdn.net/y1551812297/article/details/86749963

1. 准备

数据库需要升级，通常是因为其中的表或表中的字段、约束等发生了变化，比如增减表、增减字段、重命名等等。通常我们提取新旧数据库的差异，将差异应用到旧数据库中，就完成了升级。如果要做一个通用易用的迁移方案，那么第一步，我们需要能够较为简单的获取这个差异。我们很自然的想到，获取新旧数据库中的表结构，然后做比对就可以了，那么在 Room 中如何获取这个表结构？直接查询数据库可以吗？显然是不行的，旧数据库的表结构可以查到，但是新数据库此时还不存在。那提供接口，在使用时通过接口返回差异信息可以吗？这样的确可以，但是在使用时会非常麻烦，和直接使用 Migration 差别不大，就毫无意义了。

那么有没有一种简单好用的方案？当然有。

我们查看 Room 官方文档中 Migrating Room databases 这一章，其中 Export schemas 这一节中提到了：

Upon compilation, Room exports your database’s schema information into a JSON file. To export the schema, set the room.schemaLocation annotation processor property in your build.gradle file.

在编译完成后，Room 将数据库的概要信息导出到了一个 JSON 文件中，你可以在 gradle 文件中指定这个 JSON 文件的存放位置：

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

文档中同样也提到了：

You should store the exported JSON files—which represent your database’s schema history—in your version control system, as it allows Room to create older versions of your database for testing purposes.

必须把这个 JSON 文件存放在版本控制系统中，划重点：Room 可以根据它创建旧版本的数据库。 当时看到这里，想到既然 Room 可以通过该文件建表，那么它必定存放了表信息，上面提到该文件在编译后就会生成，那么我们更改数据库版本号之后重新编译，它就存在了，这意味着我们可以根据该文件获取新数据库的表信息。查看这个文件：

{
  "formatVersion": 1,
  "database": {
    "version": 1,
    "identityHash": "7678a65999c66329a09502ad53354586",
    "entities": [
      {
        "tableName": "User",
        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` TEXT NOT NULL)",
        "fields": [
          {
            "fieldPath": "id",
            "columnName": "id",
            "affinity": "INTEGER",
            "notNull": false
          },
          {
            "fieldPath": "name",
            "columnName": "name",
            "affinity": "TEXT",
            "notNull": true
          }
        ],
        "primaryKey": {
          "columnNames": [
            "id"
          ],
          "autoGenerate": true
        },
        "indices": [],
        "foreignKeys": []
      }
    ],
    "views": [],
    "setupQueries": [
      "CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)",
      "INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, \"7678a65999c66329a09502ad53354586\")"
    ]
  }
}

果然和我们所想的一致，所有我们需要的信息都在里面了。根据上面 gradle 中的配置，我们把这个文件导入到了项目根目录 下 schemas 文件夹中，而该文件的命名方式为 数据库版本号.json。我们只需要读取并解析新旧数据库对应的结构文件就可以得到它们的表结构，从而可以对比出差异部分。我们修改一下 gradle 文件，在打包时将 schemas 文件夹中的文件放入到 assets 目录下，这样就可以在 app 中读取到了：

android {
	sourceSets {
        main {
            assets.srcDirs += files("$projectDir/schemas".toString())
        }
    }
}

2. 迁移方案

Room 只是 Sqlite 数据库的上层包装，而 Sqlite 不支持删除字段、修改字段类型或修改字段名，当有这些变更时，较为常用的做法是创建新表然后整表复制，另外，以前使用 GreenDao 时在 stackoverflow 上看到一个使用较多的迁移方案也是整表复制，有鉴于此，决定也使用整表复制，主要步骤如下：

1. 找出新旧数据库中有变更的表，以下几步都只操作有变更的表
2. 备份旧表的数据到临时表中
3. 删除旧表
4. 创建新表
5. 将备份数据插入到新表
6. 删除备份数据

3. Talk is cheap

import android.app.Application
import androidx.core.util.ObjectsCompat
import androidx.room.Room
import androidx.room.RoomDatabase
import androidx.room.migration.Migration
import androidx.sqlite.db.SupportSQLiteDatabase
import com.alibaba.fastjson.JSON
import java.util.*

/**
 * A room-database-migration simple implementation.
 *
 * Migrate [databaseClass] from [fromVersion] to [toVersion], use [entityComparator] to determine
 * which tables and columns should be migrated from or migrate to, the [databaseClass] should
 * same as the second argument of [Room.databaseBuilder].
 *
 * **Note! By default, it works only if the NAME of the tables and columns NOT CHANGED.**
 * It means that you can add and remove some tables or columns,
 * you can add and remove some constraints of them,
 * such as foreign keys or indexes, etc, but **YOU CAN'T CHANGE IT'S NAME!**
 * If you want to change this behavior, implements your own [IEntityComparator] and set to it.
 *
 * It relies on the automatically generated schema file of room,
 * so the following code need to be added to the gradle file:
 *
 * ```
 * android {
 *  ...
 *  sourceSets {
 *      main {
 *          assets.srcDirs += files("$projectDir/schemas".toString())
 *      }
 *   }
 *  }
 * ```
 * **This class can only be used when the data being migrated is small.**
 *
 * During the migration, it will:
 * 1. Copy all the data in the changed table into an **in-memory** temporary table
 * 2. Delete the old table and create a new table
 * 3. Copy data from the temporary table to the new table and delete the temporary table
 *
 * So, if the migrated data is too large, it may cause oom.
 *
 * @since 1.0
 * @author Wing-Hawk
 */
open class DatabaseMigration<in T : RoomDatabase>(
        private val context: Context,
        private val databaseClass: Class<T>,
        private val fromVersion: Int,
        private val toVersion: Int,
        private val entityComparator: IEntityComparator = DefaultEntityComparator()
) : Migration(fromVersion, toVersion) {

    companion object {
        private const val SQLITE_MASTER = "sqlite_master"
        private const val SQLITE_TEMP_MASTER = "sqlite_temp_master"
        private const val BACKUP_SUFFIX = "_BACKUP"
    }

    override fun migrate(database: SupportSQLiteDatabase) {
		// parse schema file
        val oldEntities = parseSchemaFile(fromVersion)
        val newEntities = parseSchemaFile(toVersion)
		
        val oldChangedEntities = oldEntities.toMutableSet()
        val newChangedEntities = newEntities.toMutableSet()

        // remove unchanged entities
        oldChangedEntities.removeAll { oldEntity ->
            newEntities.any { newEntity ->
                entityComparator.equals(oldEntity, newEntity)
            }
        }

        newChangedEntities.removeAll { newEntity ->
            oldEntities.any { oldEntity ->
                entityComparator.equals(oldEntity, newEntity)
            }
        }

        if (oldChangedEntities.isEmpty() && newChangedEntities.isEmpty()) {
            // no table changed, skip migrate
            return
        }

        // certain old table changed/deleted or certain new table added
        database.runInTx {
            backupData(database, oldChangedEntities, newChangedEntities)
            dropOldTables(database, oldChangedEntities)
            createNewTables(database, newChangedEntities)
            restoreData(database, oldChangedEntities, newChangedEntities)
        }
    }

    open fun backupData(
            database: SupportSQLiteDatabase,
            oldEntities: Set<Entity>,
            newEntities: Set<Entity>) {
        // make temp table stored in memory.
        // if skipped or set to other value, androidx.room.InvalidationTracker#internalInit method
        // will throw an exception later for trying to change the temp_store value after the
        // temporary table is created.
        // shit! It took me half a day reading room's source code to find and solve this problem.
        database.execSQL("PRAGMA temp_store = MEMORY;")

        oldEntities.filter { oldEntity ->
            // only the table which exists and retained in new version need to backup
            isTableExists(database, oldEntity.tableName, false) &&
                    newEntities.any { newEntity -> entityComparator.isEntitySame(oldEntity, newEntity) }
        }.forEach { oldEntity ->
            // drop to make sure the backup-table is new
            database.execSQL("DROP TABLE IF EXISTS `${oldEntity.tableName}$BACKUP_SUFFIX`;")
            // backup data
            database.execSQL(
                    """CREATE TEMPORARY TABLE `${oldEntity.tableName}$BACKUP_SUFFIX`
                        AS SELECT * FROM `${oldEntity.tableName}`;"""
            )
        }
    }

    open fun dropOldTables(database: SupportSQLiteDatabase, oldEntities: Set<Entity>) {
        oldEntities.forEach { database.execSQL("DROP TABLE IF EXISTS `${it.tableName}`;") }
    }

    open fun createNewTables(database: SupportSQLiteDatabase, newEntities: Set<Entity>) {
        newEntities.forEach { newEntity ->
            database.execSQL("${newEntity.createSql.replace("\${TABLE_NAME}", newEntity.tableName)};")
            newEntity.indices.forEach { index ->
                database.execSQL("${index.createSql.replace("\${TABLE_NAME}", newEntity.tableName)};")
            }
        }
    }

    open fun restoreData(
            database: SupportSQLiteDatabase,
            oldEntities: Set<Entity>,
            newEntities: Set<Entity>) {
        oldEntities.forEach { oldEntity ->
            // check to see whether a backup table is exists and has backup data, if not, skip restore data.
            if (isTableExists(database, "${oldEntity.tableName}$BACKUP_SUFFIX", true) &&
                    !oldEntity.fields.isEmpty()) {
                val newEntity = newEntities.find { newEntity -> entityComparator.isEntitySame(oldEntity, newEntity) }
                // columns to insert into new table
                val restoreColumns = ArrayList<String>()
                // columns to select from backup table
                val backupColumns = ArrayList<String>()
                // table in new version is exists, find which columns need to be restored
                newEntity?.fields?.forEach { newField ->
                    val oldField = oldEntity.fields.find { oldField ->
                        entityComparator.isFieldSame(oldEntity, newEntity, oldField, newField)
                    }
                    if (oldField != null) {
                        // a column needs to restore data
                        backupColumns.add(oldField.columnName)
                        restoreColumns.add(newField.columnName)
                        // if old column is nullable but the new is non-null, set default value to it
                        if (newField.notNull && !oldField.notNull) {
                            val replaceNullSql =
                                    """UPDATE `${oldEntity.tableName}$BACKUP_SUFFIX`
                                        SET `${oldField.columnName}` = ${oldField.getDefaultValue()}
                                        WHERE `${oldField.columnName}` IS NULL;"""
                            database.execSQL(replaceNullSql)
                        }
                    } else {
                        if (newField.notNull) {
                            // non-null field added in new version
                            restoreColumns.add(newField.columnName)
                            // set a default value to it
                            backupColumns.add("${newField.getDefaultValue()}")
                        } else {
                            // do nothing...
                        }
                    }
                }
                val restoreColumnsStr = restoreColumns.joinToString(",") { "`$it`" }
                val backupColumnsStr = backupColumns.joinToString(",") { "`$it`" }

                if (!restoreColumns.isEmpty() && !backupColumns.isEmpty()) {
                    database.execSQL(
                            """REPLACE INTO `${newEntity?.tableName}` ($restoreColumnsStr)
                                SELECT $backupColumnsStr FROM `${oldEntity.tableName}$BACKUP_SUFFIX`;"""
                    )
                }
            }
            // restore finish, delete temp table
            database.execSQL("DROP TABLE IF EXISTS `${oldEntity.tableName}$BACKUP_SUFFIX`;")
        }
    }

    private fun isTableExists(
            database: SupportSQLiteDatabase,
            tableName: String,
            isTemp: Boolean): Boolean {
        val masterTable = if (isTemp) SQLITE_TEMP_MASTER else SQLITE_MASTER
        val sql = "SELECT COUNT(*) FROM `$masterTable` WHERE type = 'table' AND name = ?;"
        return database.query(sql, arrayOf(tableName)).use { cursor ->
            cursor != null && cursor.moveToFirst() && cursor.getInt(0) > 0
        }
    }

    private fun Field.getDefaultValue(): Any {
        return when (affinity) {
            "INTEGER" -> 0
            "REAL" -> 0
            "NUMERIC" -> 0
            else -> "''"
        }
    }

    // get table info from database schema file
    private fun parseSchemaFile(version: Int): List<Entity> {
        return context.assets.open("${databaseClass.name}/$version.json").use {
            JSON.parseObject(String(it.readBytes()), DatabaseSchema::class.java)
                    ?.database
                    ?.entities
                    ?: ArrayList()
        }
    }

    // run in transaction
    private fun SupportSQLiteDatabase.runInTx(block: () -> Unit) {
        beginTransaction()
        try {
            block()
            setTransactionSuccessful()
        } finally {
            endTransaction()
        }
    }
}

/**
 * This interface is used to determine which tables and columns should be migrated from or migrate to.
 */
interface IEntityComparator {

    /**
     * Whether the [oldEntity] is logically the same as the [newEntity].
     * You must ensure that each old table has at most one new table logically the same to it.
     */
    fun isEntitySame(oldEntity: Entity, newEntity: Entity): Boolean

    /**
     * Whether the [oldEntity]'s [oldField] is logically the same as the [newEntity]'s [newField].
     * You must ensure that each old field has at most one new field logically the same as it
     * in it's logically the same table.
     */
    fun isFieldSame(oldEntity: Entity, newEntity: Entity, oldField: Field, newField: Field): Boolean

    /**
     * Compare whether the [oldEntity] and [newEntity] are structurally identical.
     * This method is used to determine if certain entities has any changed.
     * Only changed entities need to be migrated.
     */
    fun equals(oldEntity: Entity, newEntity: Entity): Boolean
}

open class DefaultEntityComparator : IEntityComparator {

    override fun isEntitySame(oldEntity: Entity, newEntity: Entity): Boolean {
        return oldEntity.tableName == newEntity.tableName
    }

    override fun isFieldSame(oldEntity: Entity, newEntity: Entity, oldField: Field, newField: Field): Boolean {
        return isEntitySame(oldEntity, newEntity) && oldField.columnName == newField.columnName
    }

    override fun equals(oldEntity: Entity, newEntity: Entity): Boolean {
        return oldEntity == newEntity
    }
}

data class DatabaseSchema(
        val formatVersion: Int,
        val database: Database)

data class Database(
        val version: Int,
        val identityHash: String,
        val entities: List<Entity>,
        val setupQueries: List<String>)

data class Entity(
        val tableName: String,
        val createSql: String,
        val fields: List<Field>,
        val primaryKey: PrimaryKey,
        val indices: List<Index>,
        val foreignKeys: List<ForeignKey>) {

    override fun equals(other: Any?): Boolean {
        if (this === other) {
            return true
        }
        if (other == null || javaClass != other.javaClass) {
            return false
        }
        other as Entity
        return ObjectsCompat.equals(createSql, other.createSql)
                && ObjectsCompat.equals(indices, other.indices)
    }

    override fun hashCode(): Int {
        return ObjectsCompat.hash(createSql, indices)
    }
}

data class Field(
        val fieldPath: String,
        val columnName: String,
        val affinity: String,
        val notNull: Boolean)

data class PrimaryKey(
        val columnNames: List<String>,
        val autoGenerate: Boolean)

data class Index(
        val name: String,
        val unique: Boolean,
        val columnNames: List<String>,
        val createSql: String) {

    override fun equals(other: Any?): Boolean {
        if (this === other) {
            return true
        }
        if (other == null || javaClass != other.javaClass) {
            return false
        }
        other as Index
        return ObjectsCompat.equals(createSql, other.createSql)
    }

    override fun hashCode(): Int {
        return ObjectsCompat.hash(createSql)
    }
}

data class ForeignKey(
        val table: String,
        val onDelete: String,
        val onUpdate: String,
        val columns: List<String>,
        val referencedColumns: List<String>)

4. 使用

如果数据库中的表名和字段名没有发生变化，可以直接创建它的对象：

DatabaseMigration(
	context = context,
	databaseClass = YourRoomDatabase::class.java,
	fromVersion = fromVersion,
	toVersion = toVersion)

否则，你需要自己实现 IEntityComparator 接口或继承 DefaultEntityComparator 类，它是用来指定迁移的原表和目标表以及原字段和目标字段。

假设原数据库中有表：

@Entity
data class User constructor(
    @PrimaryKey(autoGenerate = true)
    var id: Long? = null,
    var name: String
)

因为某些原因，该表变成了这样：

@Entity
data class AUser constructor(
    @PrimaryKey(autoGenerate = true)
    var id: Long? = null,
    var aName: String
)

你可以这么做：

class MyEntityComparator : DefaultEntityComparator() {
    override fun isEntitySame(oldEntity: Entity, newEntity: Entity): Boolean {
        // If the old table name is "User" and the new is "AUser",
        // we say they are the same.
        if (oldEntity.tableName == "User" && newEntity.tableName == "AUser") {
            return true
        }
        /*
           If you change a lot of entity names, you can put them in a map, and do this:
           // key is old entity name, value is new entity name
           val map = mapOf(
                   Pair("A", "B"),
                   Pair("C", "D")
                   Pair("E", "F")
                   ...
           )
           map.forEach { (oldTableName, newTableName) ->
               if (oldEntity.tableName == oldTableName && newEntity.tableName == newTableName) {
                   return true
               }
           }
         */
        return super.isEntitySame(oldEntity, newEntity)
    }
    override fun isFieldSame(oldEntity: Entity, newEntity: Entity, oldField: Field, newField: Field): Boolean {
        // If the old field name is "name" and the new field name
        // is "aName" in table "User", we say they are the same.
        if (isEntitySame(oldEntity, newEntity)) {
        	if (oldField.columnName == "name" && newField.columnName == "aName") {
 				return true
			}
        }
        return super.isFieldSame(oldEntity, newEntity, oldField, newField)
    }
}

5. 问题

因为迁移方案采用的是整表复制，所以在表比较大的时候，可能会耗时严重，另外，临时表创建在内存中，也可能会引发 oom，因此该方案仅适合在数据量不大的情况下使用。
如果有其它办法在字段删除、字段重命名等情况下可以不用复制整表，或者可以绕开 Room 临时表必须在内存中创建的限制，请告诉我，不胜感激。

— The End —