SQLite数据库迁移似乎仅部分适用于Espresso测试 [英] SQLite database migration appears to only partially apply in Espresso test

问题描述

我们有一个SQLite数据库和一个相应的SQLiteOpenHelper子类.这个助手有一个onDowngrade实现，我想为此编写一个Espresso测试.

We have an SQLite database and a corresponding SQLiteOpenHelper subclass. This helper has an onDowngrade implementation that I would like to write an Espresso test for.

完整的onDowngrade实现在此处中可用.这是它的简化版本:

The full onDowngrade implementation is available here. This is a simplified version of it:

@Override
public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("CREATE TABLE IF NOT EXISTS foo_tmp (_id integer primary key, bar text not null, baz text not null);");
    db.execSQL("INSERT INTO foo_tmp(_id,bar,baz) SELECT _id,bar,baz FROM foo;");
    db.execSQL("DROP TABLE IF EXISTS foo;");
    db.execSQL("RENAME TABLE foo_tmp TO foo;");
}

该测试将加载具有很高版本号以及已添加或已删除列的数据库转储.然后，它将获得一个可读的数据库，并确保已将版本降级为当前的预期版本，并且确保列名是预期的列名.完整的资源可在此处获得.看起来是这样的:

The test loads a database dump with a very high version number and added or removed columns. It then gets a readable database and ensures that the version has been downgraded to the current expected version and that the column names are the expected column names. The full source is available here. This is what it looks like:

@Test
public void testMigration() throws IOException {
    writeDatabaseFile("database" + File.separator + dbFilename);
    InstancesDatabaseHelper databaseHelper = new InstancesDatabaseHelper();

    SQLiteDatabase db = databaseHelper.getReadableDatabase();
    assertThat(db.getVersion(), is(InstancesDatabaseHelper.DATABASE_VERSION));

    List<String> newColumnNames = InstancesDatabaseHelper.getInstancesColumnNames(db);

    assertThat(newColumnNames, contains(InstancesDatabaseHelper.CURRENT_VERSION_COLUMN_NAMES));
}

如果我将相同的数据库转储手动加载到应用程序中，那么一切都会按预期进行.但是，当我运行此测试时，似乎未执行迁移中的最后一个RENAME.如果我注释掉迁移中的最后两个SQL语句(删除原始表并将临时表重命名为原始表名)，则可以断言该临时表具有预期的内容(

Everything works as intended if I manually load the same database dumps into the app. However, when I run this test, it looks like the last RENAME in the migration is not executed. If I comment out the last two SQL statements in the migration (dropping the original table and renaming the temporary table to the original table name), I can assert that the temporary table has the expected contents (here is a commit that shows this).

通过一些实验，我们发现在实例化SQLiteOpenHelper之后在测试中添加databaseHelper.getReadableDatabase().close();会使测试通过.鉴于onDowngrade调用包含在事务中，所以我不知道这怎么可能.

With some experimentation, we have found that adding databaseHelper.getReadableDatabase().close(); in the test after instantiating the SQLiteOpenHelper makes the tests pass. Given that the onDowngrade call is wrapped in a transaction, I don't understand how this is possible.

这是否表明我们的onDowngrade实现中存在错误?在Espresso测试中触发迁移是否有所不同?

Could this point to a bug in our onDowngrade implementation? Is triggering migrations in Espresso tests different in some way?

推荐答案

可能存在竞争条件，因为SQLite是共享资源.

There probably is a race condition, because SQLite is a shared resource.

例如当测试在发出最后一个COMMIT语句之前运行.

eg. when the test runs before the last one COMMIT statement was issued.

将其包装为交易(另请参见

Wrap it into a transaction (also see Isolation In SQLite):

if(! BuildConfig.DEBUG) { 
    db.beginTransaction();
} else {
    db.beginTransactionWithListener(new SQLiteTransactionListener() {
        @Override public void onBegin()    {Log.d(LOG_TAG, "onBegin()");}
        @Override public void onCommit()   {Log.d(LOG_TAG, "onCommit()");}
        @Override public void onRollback() {Log.d(LOG_TAG, "onRollback()");}
    });
}

try {

    db.execSQL("CREATE TABLE IF NOT EXISTS foo_tmp (_id integer primary key, bar text not null, baz text not null);");
    db.execSQL("INSERT INTO foo_tmp(_id,bar,baz) SELECT _id,bar,baz FROM foo;");
    db.execSQL("DROP TABLE IF EXISTS foo;");
    db.execSQL("RENAME TABLE foo_tmp TO foo;");
    db.setTransactionSuccessful();

} catch(SQLException e){
    Log.d(LOG_TAG, "" + e.getMessage());
} finally {
    db.endTransaction();
}
db.close();

这篇关于SQLite数据库迁移似乎仅部分适用于Espresso测试的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！