使用更新的预填充数据库进行Android Room迁移

Question

我在使用Room和预填充数据库时遇到了一些问题，需要进行迁移。

解释

目前我正在使用Room和一个预填充的数据库。第一个版本（版本1）可以正常加载数据库，并且一切都能正确地工作。

问题在于，此时我需要向数据库添加三个新表和其中的数据。所以我开始更新我拥有的版本1数据库，创建了所有需要的表和行及其中的数据。

第一次尝试时，我直接将新的.sqlite数据库推送到资源文件夹中，并将版本号更改为2，但是当然，Room会提示它需要知道如何处理迁移1_2，因此我添加了迁移规则。

    .addMigrations(new Migration(1,2) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            database.execSQL("CREATE TABLE ...);
            database.execSQL("CREATE TABLE ...);
            database.execSQL("CREATE TABLE ...);
        }
    ...

我认为如果让Room创建这些表格，它将连接到assets中的新数据库，并相应地填充这些表格。但这显然行不通，在查看数据库检查器时，可以发现这些表格存在，但为空。

一个我不太喜欢的解决方案

经过一番尝试，最终我发现可行的方法是先复制更新后的数据库，在其中导航（我目前正在使用DB Browser for SQLite），获取新填充行的SQL查询，并格式化一个database.execSQL语句来将新数据插入表格中：

    .addMigrations(new Migration(1,2) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            database.execSQL("CREATE TABLE ...);
            database.execSQL("CREATE TABLE ...);
            database.execSQL("CREATE TABLE ...);
    
            database.execSQL("INSERT INTO ...");
            database.execSQL("INSERT INTO ...");
            database.execSQL("INSERT INTO ...");
            database.execSQL("INSERT INTO ...");

        }
    ...

我认为这是一种“可接受”的解决方案，适用于处理包含少量数据的行的情况。但在我的情况下，我正在处理包含非常长字符串的行，这会产生一系列不便之处：

• 需要良好格式化从数据库数据提取的SQL语句：需要处理'符号，以及可能存在于长字符串中的"和换行符；
• 必须保持数据库和行的插入语句之间的一致性；

问题

请注意，fallbackToDestructiveMigration（）不是可接受的选项，因为版本1中的数据库具有用户创建的数据，并且需要在迁移之间保留该数据。

那么，是否有一种解决方案，允许我直接将新的.sqlite数据库推送到资源文件夹中，而不编写大量的INSERT和CREATE TABLE语句，并且让Room自动处理其中的新数据以及同时保留旧表的数据？

---

感谢您花费时间阅读！

- x-devel

---

感谢您提供了一个非常清晰的场景说明，我也是这样认为。 - Hashir Labs

Answer 1

也许可以考虑：

1. 将新数据库放入适合应用程序新安装的资源文件夹中，以便createFromAsset会复制此版本2数据库进行新安装。

2. 在迁移过程中，将资产复制到具有不同数据库名称的数据库文件夹中。

3. 在迁移中创建新表。

4. 仍在迁移中，对于每个新表，从命名不同的新数据库中提取所有数据，然后使用Cursor将数据插入现有数据库。

5. 仍在迁移中，关闭不同名称的数据库并删除该文件。

这是类似这些内容（没有模式更改，只有新的预填充数据）的迁移代码，它是Kotlin而不是Java，来自最近的回答：

    val migration1_2 = object: Migration(1,2) {
        val assetFileName = "appdatabase.db" 
        val tempDBName = "temp_" + assetFileName
        val bufferSize = 1024 * 4
        @SuppressLint("Range")
        override fun migrate(database: SupportSQLiteDatabase) {
            val asset = contextPassed?.assets?.open(assetFileName) /* Get the asset as an InputStream */
            val tempDBPath = contextPassed?.getDatabasePath(tempDBName) /* Deduce the file name to copy the database to */
            val os = tempDBPath?.outputStream() /* and get an OutputStream for the new version database */

            /* Copy the asset to the respective file (OutputStream) */
            val buffer = ByteArray(bufferSize)
            while (asset!!.read(buffer,0,bufferSize) > 0) {
                os!!.write(buffer)
            }
            /* Flush and close the newly created database file */
            os!!.flush()
            os.close()
            /* Close the asset inputStream */
            asset.close()
            /* Open the new database */
            val version2db = SQLiteDatabase.openDatabase(tempDBPath.path,null,SQLiteDatabase.OPEN_READONLY)
            /* Grab all of the supplied rows */
            val v2csr = version2db.rawQuery("SELECT * FROM user WHERE userId < ${User.USER_DEMARCATION}",null)
            /* Insert into the actual database ignoring duplicates (by userId) */
            while (v2csr.moveToNext()) {
                database.execSQL("INSERT OR IGNORE INTO user VALUES(${v2csr.getLong(v2csr.getColumnIndex("userId"))},'${v2csr.getString(v2csr.getColumnIndex("userName"))}')",)
            }
            /* close cursor and the newly created database */
            v2csr.close()
            version2db.close()
            tempDBPath.delete() /* Delete the temporary database file */
        }
    }

• 在测试上述代码时请注意。我最初尝试ATTACH新的（temp）数据库。这起初是可行的并且复制了数据，但是ATTACH或DETACH（或两者都是）过早结束了迁移所在的事务，导致Room无法打开数据库并引发异常。

  • 如果不是这样的话，那么可以使用简单的INSERT INTO main.the_table SELECT * FROM the_attached_schema_name.the_table;而不是使用游标作为中间层。

不用写大量的INSERT和CREATE TABLE语句

以上已经处理了插入操作。

类似地，可以通过使用以下方式从新资产数据库中提取CREATE SQL：

`SELECT name,sql FROM sqlite_master WHERE type = 'table' AND name in (a_csv_of_the_table_names (enclosed in single quotes))`

例如：SELECT name,sql FROM sqlite_master WHERE type = 'table' AND name IN ('viewLog','message');; 结果为（用于演示的任意数据库）：-

• name是表的名称，sql是用于创建表的SQL语句。
• 或者，在编译后生成的Java代码中（从Android视图中可见），可以在带有@Database注释的类的名称相同但后缀为_Impl的类中找到创建表的SQL。将有一个名为createAlltables的方法，其中包含创建所有表格（和其他项目）的SQL语句，例如（仅为任意示例）：

注意红色删除线是针对room_master表的，ROOM会创建它，但在资产中不需要（这是room用来检查模式是否已更改的内容）。

工作示例

版本1（准备迁移到版本2）

以下是一个工作示例。在版本1中，使用预填充数据库中的单个名为original（实体OriginalEnity）的表和数据（5行），然后添加一行以反映用户提供/输入的数据。当应用程序运行时，将提取表的内容并写入日志：

D/DBINFOoriginal: Name is name1 ID is 1 - DB Version is 1
D/DBINFOoriginal: Name is name2 ID is 2 - DB Version is 1
D/DBINFOoriginal: Name is name3 ID is 3 - DB Version is 1
D/DBINFOoriginal: Name is name4 ID is 4 - DB Version is 1
D/DBINFOoriginal: Name is name5 ID is 5 - DB Version is 1
D/DBINFOoriginal: Name is App User Data ID is 6 - DB Version is 1

数据库检查器显示：-

版本2：

第二版

新增了3个实体/表（分别命名为newEntity1、newEntity2和newEntity3，表名分别为new1、new2和new3），具有相同的基本结构。

在创建实体并编译SQL后，按照从TheDatabase_Impl类提取的createAlltables方法（包括3个额外的索引）执行：

然后使用SQLite工具使用此SQL创建新表并填充一些数据。

/* FOR VERSION 2 */
/* Create statments copied from TheDatabase_Impl */
DROP TABLE IF EXISTS new1;
DROP TABLE IF EXISTS new2;
DROP TABLE IF EXISTS new3;
CREATE TABLE IF NOT EXISTS `new1` (`new1_id` INTEGER, `new1_name` TEXT, PRIMARY KEY(`new1_id`));
CREATE INDEX IF NOT EXISTS `index_new1_new1_name` ON `new1` (`new1_name`);
CREATE TABLE IF NOT EXISTS `new2` (`new2_id` INTEGER, `new2_name` TEXT, PRIMARY KEY(`new2_id`));
CREATE INDEX IF NOT EXISTS `index_new2_new2_name` ON `new2` (`new2_name`);
CREATE TABLE IF NOT EXISTS `new3` (`new3_id` INTEGER, `new3_name` TEXT, PRIMARY KEY(`new3_id`));
CREATE INDEX IF NOT EXISTS `index_new3_new3_name` ON `new3` (`new3_name`);

INSERT OR IGNORE INTO new1 (new1_name) VALUES ('new1_name1'),('new1_name2');
INSERT OR IGNORE INTO new2 (new2_name) VALUES ('new2_name1'),('new2_name2');
INSERT OR IGNORE INTO new3 (new3_name) VALUES ('new3_name1'),('new3_name2');

数据库已保存并复制到资产文件夹中（原始文件已重命名）：

然后迁移代码（完整的数据库助手），它： - 仅由表名的 String[] 驱动 - 复制资源文件（新数据库）并通过 SQLite API 打开它 - 根据资源文件创建表、索引和触发器（必须与 Room 生成的模式匹配（因此从之前生成的 Java 中复制 SQL）） - 它通过从 sqlite_master 表中提取相应的 SQL 来实现这一点 - 通过从资产数据库中提取数据到 Cursor，然后插入到 Room 数据库中来填充新创建的 Room 表（不是最有效的方式，但 Room 在事务中运行迁移）。

@Database(entities = {
        OriginalEntity.class, /* on it's own for V1 */
        /* ADDED NEW TABLES FOR V2 */NewEntity1.class,NewEntity2.class,NewEntity3.class
        },
        version = TheDatabase.DATABASE_VERSION,
        exportSchema = false
)
abstract class TheDatabase extends RoomDatabase {
    public static final String DATABASE_NAME = "thedatabase.db";
    public static final int DATABASE_VERSION = 2; //<<<<<<<<<< changed */
    abstract AllDao getAllDao();

    private static volatile TheDatabase instance = null;
    private static Context currentContext;

    public static TheDatabase getInstance(Context context) {
        currentContext = context;
        if (instance == null) {
            instance = Room.databaseBuilder(context, TheDatabase.class, DATABASE_NAME)
                    .allowMainThreadQueries() /* for convenience run on main thread */
                    .createFromAsset(DATABASE_NAME)
                    .addMigrations(migration1_2)
                    .build();
        }
        return instance;
    }

    static Migration migration1_2 = new Migration(1, 2) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            /* Copy the asset into the database folder (with different name) */
            File assetDBFile = getNewAssetDatabase(currentContext,DATABASE_NAME);
            /* Open the assetdatabase */
            SQLiteDatabase assetDB = SQLiteDatabase.openDatabase(assetDBFile.getPath(),null,SQLiteDatabase.OPEN_READWRITE);
            /* Build (create and populate) the new ROOM tables and indexes from the asset database  */
            buildNewTables(
                    new String[]{
                            NewEntity1.TABLE_NAME,
                            NewEntity2.TABLE_NAME,
                            NewEntity3.TABLE_NAME},
                    database /* ROOM DATABASE */,
                    assetDB /* The copied and opened asset database as an SQliteDatabase */
            );
            /* done with the asset database */
            assetDB.close();
            assetDBFile.delete();
        }
    };

    private static void buildNewTables(String[] tablesToBuild, SupportSQLiteDatabase actualDB, SQLiteDatabase assetDB) {
        StringBuilder args = new StringBuilder();
        boolean afterFirst = false;
        for (String tableName: tablesToBuild) {
            if (afterFirst) {
                args.append(",");
            }
            afterFirst = true;
            args.append("'").append(tableName).append("'");
        }
        /* Get SQL for anything related to the table (table, index, trigger) to the tables and build it */
        /* !!!!WARNING!!!! NOT TESTED VIEWS */
        /* !!!!WARNING!!!! may not cope with Foreign keys as conflicts could occur */
        Cursor csr = assetDB.query(
                "sqlite_master",
                new String[]{"name","sql", "CASE WHEN type = 'table' THEN 1 WHEN type = 'index' THEN 3 ELSE 2 END AS sort"},
                "tbl_name IN (" + args.toString() + ")",
                null,
                null,null, "sort"
        );
        while (csr.moveToNext()) {
            Log.d("CREATEINFO","executing SQL:- " + csr.getString(csr.getColumnIndex("sql")));
            actualDB.execSQL(csr.getString(csr.getColumnIndex("sql")));
        }
        /* Populate the tables */
        /* !!!!WARNING!!!! may not cope with Foreign keys as conflicts could occur */
        /*      no set order for the tables so a child table may not be loaded before it's parent(s) */
        ContentValues cv = new ContentValues();
        for (String tableName: tablesToBuild) {
            csr = assetDB.query(tableName,null,null,null,null,null,null);
            while (csr.moveToNext()) {
                cv.clear();
                for (String columnName: csr.getColumnNames()) {
                    cv.put(columnName,csr.getString(csr.getColumnIndex(columnName)));
                    actualDB.insert(tableName, OnConflictStrategy.IGNORE,cv);
                }
            }
        }
        csr.close();
    }

    private static File getNewAssetDatabase(Context context, String assetDatabaseFileName) {
        String tempDBPrefix = "temp_";
        int bufferSize = 1024 * 8;
        byte[] buffer = new byte[bufferSize];
        File assetDatabase = context.getDatabasePath(tempDBPrefix+DATABASE_NAME);
        InputStream assetIn;
        OutputStream assetOut;
        /* Delete the AssetDatabase (temp DB) if it exists */
        if (assetDatabase.exists()) {
            assetDatabase.delete(); /* should not exist but just in case */
        }
        /* Just in case the databases folder (data/data/packagename/databases)
            doesn't exist create it
            This should never be the case as Room DB uses it
         */
        if (!assetDatabase.getParentFile().exists()) {
            assetDatabase.mkdirs();
        }
        try {
            assetIn = context.getAssets().open(assetDatabaseFileName);
            assetOut = new FileOutputStream(assetDatabase);
            while(assetIn.read(buffer) > 0) {
                assetOut.write(buffer);
            }
            assetOut.flush();
            assetOut.close();
            assetIn.close();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("Error retrieving Asset Database from asset " + assetDatabaseFileName);
        }
        return assetDatabase;
    }
}

活动中的代码是：-

public class MainActivity extends AppCompatActivity {

    TheDatabase db;
    AllDao dao;
    private static final String TAG = "DBINFO";

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        /* Original */
        db = TheDatabase.getInstance(this);
        dao = db.getAllDao();
        OriginalEntity newOE = new OriginalEntity();
        newOE.name = "App User Data";
        dao.insert(newOE);
        for(OriginalEntity o: dao.getAll()) {
            Log.d(TAG+OriginalEntity.TABLE_NAME,"Name is " + o.name + " ID is " + o.id + " - DB Version is " + TheDatabase.DATABASE_VERSION);
        }

        /* Added for V2 */
        for (NewEntity1 n: dao.getAllNewEntity1s()) {
            Log.d(TAG+NewEntity1.TABLE_NAME,"Names is " + n.name + " ID is " + n.id + " - DB Version is " + TheDatabase.DATABASE_VERSION);
        }
        for (NewEntity2 n: dao.getAllNewEntity2s()) {
            Log.d(TAG+NewEntity2.TABLE_NAME,"Names is " + n.name + " ID is " + n.id + " - DB Version is " + TheDatabase.DATABASE_VERSION);
        }
        for (NewEntity3 n: dao.getAllNewEntity3s()) {
            Log.d(TAG+NewEntity3.TABLE_NAME,"Names is " + n.name + " ID is " + n.id + " - DB Version is " + TheDatabase.DATABASE_VERSION);
        }
    }
}

请查看代码中的版本1部分和注释以运行V1。
V2运行（初始）的结果输出到日志中为：
:-

2021-10-11 13:02:50.939 D/CREATEINFO: executing SQL:- CREATE TABLE `new1` (`new1_id` INTEGER, `new1_name` TEXT, PRIMARY KEY(`new1_id`))
2021-10-11 13:02:50.941 D/CREATEINFO: executing SQL:- CREATE TABLE `new2` (`new2_id` INTEGER, `new2_name` TEXT, PRIMARY KEY(`new2_id`))
2021-10-11 13:02:50.942 D/CREATEINFO: executing SQL:- CREATE TABLE `new3` (`new3_id` INTEGER, `new3_name` TEXT, PRIMARY KEY(`new3_id`))
2021-10-11 13:02:50.942 D/CREATEINFO: executing SQL:- CREATE INDEX `index_new1_new1_name` ON `new1` (`new1_name`)
2021-10-11 13:02:50.943 D/CREATEINFO: executing SQL:- CREATE INDEX `index_new2_new2_name` ON `new2` (`new2_name`)
2021-10-11 13:02:50.944 D/CREATEINFO: executing SQL:- CREATE INDEX `index_new3_new3_name` ON `new3` (`new3_name`)
2021-10-11 13:02:51.006 D/DBINFOoriginal: Name is name1 ID is 1 - DB Version is 2
2021-10-11 13:02:51.006 D/DBINFOoriginal: Name is name2 ID is 2 - DB Version is 2
2021-10-11 13:02:51.006 D/DBINFOoriginal: Name is name3 ID is 3 - DB Version is 2
2021-10-11 13:02:51.006 D/DBINFOoriginal: Name is name4 ID is 4 - DB Version is 2
2021-10-11 13:02:51.006 D/DBINFOoriginal: Name is name5 ID is 5 - DB Version is 2
2021-10-11 13:02:51.006 D/DBINFOoriginal: Name is App User Data ID is 6 - DB Version is 2
2021-10-11 13:02:51.006 D/DBINFOoriginal: Name is App User Data ID is 7 - DB Version is 2
2021-10-11 13:02:51.010 D/DBINFOnew1: Names is new1_name1 ID is 1 - DB Version is 2
2021-10-11 13:02:51.010 D/DBINFOnew1: Names is new1_name2 ID is 2 - DB Version is 2
2021-10-11 13:02:51.012 D/DBINFOnew2: Names is new2_name1 ID is 1 - DB Version is 2
2021-10-11 13:02:51.012 D/DBINFOnew2: Names is new2_name2 ID is 2 - DB Version is 2
2021-10-11 13:02:51.013 D/DBINFOnew3: Names is new3_name1 ID is 1 - DB Version is 2
2021-10-11 13:02:51.013 D/DBINFOnew3: Names is new3_name2 ID is 2 - DB Version is 2

请注意，用户数据已被保留（第一个应用程序用户数据...，第二个在运行活动时添加）。

Dao（AllDao）是：-

@Dao
abstract class AllDao {
    /* Original Version 1 Dao's */
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract long insert(OriginalEntity originalEntity);
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract long[] insert(OriginalEntity ... originalEntities);
    @Query("SELECT * FROM original")
    abstract List<OriginalEntity> getAll();

    /* New Version 2 Dao's */
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract long insert(NewEntity1 newEntity1);
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract long insert(NewEntity2 newEntity2);
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract long insert(NewEntity3 newEntity3);
    @Query("SELECT * FROM " + NewEntity1.TABLE_NAME)
    abstract List<NewEntity1> getAllNewEntity1s();
    @Query("SELECT * FROM " + NewEntity2.TABLE_NAME)
    abstract List<NewEntity2> getAllNewEntity2s();
    @Query("SELECT * FROM " + NewEntity3.TABLE_NAME)
    abstract List<NewEntity3> getAllNewEntity3s();
}

实体包括：

@Entity(tableName = OriginalEntity.TABLE_NAME)
class OriginalEntity {
    public static final String TABLE_NAME = "original";
    public static final String COL_ID = TABLE_NAME +"_id";
    public static final String COL_NAME = TABLE_NAME + "_name";

    @PrimaryKey
    @ColumnInfo(name = COL_ID)
    Long id = null;
    @ColumnInfo(name = COL_NAME, index = true)
    String name;
}

and for V2 :-

@Entity(tableName = NewEntity1.TABLE_NAME)
class NewEntity1 {

    public static final String TABLE_NAME = "new1";
    public static final String COl_ID = TABLE_NAME + "_id";
    public static final String COL_NAME = TABLE_NAME + "_name";

    @PrimaryKey
    @ColumnInfo(name = COl_ID)
    Long id = null;
    @ColumnInfo(name = COL_NAME, index = true)
    String name;
}

这句话的意思是“并且：-”

@Entity(tableName = NewEntity2.TABLE_NAME)
class NewEntity2 {

    public static final String TABLE_NAME = "new2";
    public static final String COl_ID = TABLE_NAME + "_id";
    public static final String COL_NAME = TABLE_NAME + "_name";

    @PrimaryKey
    @ColumnInfo(name = COl_ID)
    Long id = null;
    @ColumnInfo(name = COL_NAME, index = true)
    String name;
}

and :-

@Entity(tableName = NewEntity3.TABLE_NAME)
class NewEntity3 {

    public static final String TABLE_NAME = "new3";
    public static final String COl_ID = TABLE_NAME + "_id";
    public static final String COL_NAME = TABLE_NAME + "_name";

    @PrimaryKey
    @ColumnInfo(name = COl_ID)
    Long id = null;
    @ColumnInfo(name = COL_NAME, index = true)
    String name;
}

最终测试新应用程序安装（即无迁移，但从资产创建）。

运行时，输出到日志的内容为（没有用户提供/输入的数据）：

2021-10-11 13:42:48.272 D/DBINFOoriginal: Name is name1 ID is 1 - DB Version is 2
2021-10-11 13:42:48.272 D/DBINFOoriginal: Name is name2 ID is 2 - DB Version is 2
2021-10-11 13:42:48.272 D/DBINFOoriginal: Name is name3 ID is 3 - DB Version is 2
2021-10-11 13:42:48.272 D/DBINFOoriginal: Name is name4 ID is 4 - DB Version is 2
2021-10-11 13:42:48.272 D/DBINFOoriginal: Name is name5 ID is 5 - DB Version is 2
2021-10-11 13:42:48.272 D/DBINFOoriginal: Name is App User Data ID is 6 - DB Version is 2
2021-10-11 13:42:48.275 D/DBINFOnew1: Names is new1_name1 ID is 1 - DB Version is 2
2021-10-11 13:42:48.275 D/DBINFOnew1: Names is new1_name2 ID is 2 - DB Version is 2
2021-10-11 13:42:48.276 D/DBINFOnew2: Names is new2_name1 ID is 1 - DB Version is 2
2021-10-11 13:42:48.276 D/DBINFOnew2: Names is new2_name2 ID is 2 - DB Version is 2
2021-10-11 13:42:48.277 D/DBINFOnew3: Names is new3_name1 ID is 1 - DB Version is 2
2021-10-11 13:42:48.277 D/DBINFOnew3: Names is new3_name2 ID is 2 - DB Version is 2

---

注意：

Room将项目（表格、列）的名称用重音符号括起来。这使得无效的列名变为有效的，例如未加引号的1是无效的，而加了引号的1是有效的。尽管我怀疑不会出现问题，但使用其他无效名称可能会导致问题（我尚未测试此方面）。SQLite本身在存储名称时会去掉重音符号。

CREATE TABLE IF NOT EXISTS `testit` (`1`);
SELECT * FROM sqlite_master WHERE name = 'testit';
SELECT * FROM testit;

结果是：

• 即，当SQL被存储时，重音符号会保留，因此生成的CREATE是安全的。

和：

即，重音符号已被删除，该列仅命名为1，这可能会在遍历光标中的列时引起问题（但很可能不会）。

- MikeT