在单线程测试用例中，ALTER TABLE 之前的 SELECT 会隐藏后续 SELECT 中的新列
一步一步
Android Studio Bumblebee ->文件->新->新项目->空活动 ->完成（使用默认值）
打开app/src/androidTest/java/com/example/myapplication/ExampleInstrumentedTest.kt并添加以下测试，然后在例如API级别30模拟器上运行它：
@org.junit.Test
fun visibility_of_column_after_select_then_alter_then_select_again() {
val appContext = androidx.test.platform.app.InstrumentationRegistry.getInstrumentation().targetContext
// Use a fresh DB each time
val dbPath = appContext.getDatabasePath(java.util.UUID.randomUUID().toString())
val db = android.database.sqlite.SQLiteDatabase.openDatabase(
dbPath,
android.database.sqlite.SQLiteDatabase.OpenParams.Builder()
.addOpenFlags(android.database.sqlite.SQLiteDatabase.CREATE_IF_NECESSARY)
.build()
)
// Query 1: CREATE TABLE
db.execSQL("CREATE TABLE some_table (some_column TEXT)")
// Query 2: SELECT all columns (to assert on index of new_column)
assertColumnIndex(db, "new_column", -1)
// Query 3: ALTER TABLE to add column
db.execSQL("ALTER TABLE some_table ADD new_column TEXT")
// Query 4: SELECT all columns again (to assert on index of new_column)
assertColumnIndex(db, "new_column", 1)
}
private fun assertColumnIndex(db: android.database.sqlite.SQLiteDatabase, columnName: String, expectedIndex: Int) {
val cursor = db.query("some_table", null, null, null, null, null, null)
org.junit.Assert.assertEquals(expectedIndex, cursor.getColumnIndex(columnName))
cursor.close()
}
预期结果
测试通过。
实际结果
在查询 4 之后断言 "new_column" 索引为 1 时，测试失败。它是 -1，也称为“not成立”。这很奇怪，因为我们之前就添加了它。
但是，如果我只执行以下两件事之一，则测试通过：
A. 将
//放在查询 2 前面，即停止发出该SELECT查询。我觉得这很奇怪。B. 将
.addOpenFlags(SQLiteDatabase.ENABLE_WRITE_AHEAD_LOGGING)添加到OpenParams.Builder。这并不奇怪，但并不能让我们更容易理解为什么A.会产生影响。
问题
为什么A.能够通过测试？
Step-by-step
Android Studio Bumblebee -> File -> New -> New Project -> Empty Activity -> Finish (use defaults)
Open app/src/androidTest/java/com/example/myapplication/ExampleInstrumentedTest.kt and add the following test and then run it on e.g. an API level 30 emulator:
@org.junit.Test
fun visibility_of_column_after_select_then_alter_then_select_again() {
val appContext = androidx.test.platform.app.InstrumentationRegistry.getInstrumentation().targetContext
// Use a fresh DB each time
val dbPath = appContext.getDatabasePath(java.util.UUID.randomUUID().toString())
val db = android.database.sqlite.SQLiteDatabase.openDatabase(
dbPath,
android.database.sqlite.SQLiteDatabase.OpenParams.Builder()
.addOpenFlags(android.database.sqlite.SQLiteDatabase.CREATE_IF_NECESSARY)
.build()
)
// Query 1: CREATE TABLE
db.execSQL("CREATE TABLE some_table (some_column TEXT)")
// Query 2: SELECT all columns (to assert on index of new_column)
assertColumnIndex(db, "new_column", -1)
// Query 3: ALTER TABLE to add column
db.execSQL("ALTER TABLE some_table ADD new_column TEXT")
// Query 4: SELECT all columns again (to assert on index of new_column)
assertColumnIndex(db, "new_column", 1)
}
private fun assertColumnIndex(db: android.database.sqlite.SQLiteDatabase, columnName: String, expectedIndex: Int) {
val cursor = db.query("some_table", null, null, null, null, null, null)
org.junit.Assert.assertEquals(expectedIndex, cursor.getColumnIndex(columnName))
cursor.close()
}
Expected result
The test passes.
Actual result
The test fails when asserting on that "new_column" index is 1 after Query 4. It is -1, also known as "not found". Which is strange, because we added it right before.
However, the test PASS if I do just one of the following two things:
A. Put
//in front of Query 2, i.e. stop issuing thatSELECTquery. I find this very strange.B. Add
.addOpenFlags(SQLiteDatabase.ENABLE_WRITE_AHEAD_LOGGING)to theOpenParams.Builder. This is less strange, but does not make it easier to understand why A. make a difference.
Question
Why does A. make the test pass?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
在 Kotlin 和 Java 中对此进行了测试，结果是相同的，尽管添加了新列（根据架构又名 sqlite_master），但游标似乎不知道而是处于 WAL 模式。
不确定为什么，我怀疑可能是 SQLite API 的问题。
一种解决方案是不使用alter，而是删除表并重新创建它，例如
另一种选择是关闭然后重新打开数据库，但是这将相对资源昂贵。
以下用于测试各种场景：-
对于 Java 测试（表明 Java 代码也会发生同样的情况）：-
用于测试的活动：-
日志显示：-
其他
进一步测试，使用特定的列名称而不是 *（空），似乎表明有错误的代码。
显然不合适，因为如果指定列不存在，将导致异常。
但是，请考虑一下，下面的
如上所述，实际上并不可用，但这绕过了未找到列的异常，以证明 null 作为查询方法的第二个参数可能无法按预期工作
：-
这会导致：-
Tested this in Kotlin and Java and the result is the same, although the new column is added (according to the schema aka sqlite_master) the cursor doesn't appear to be aware but is in WAL mode.
Unsure as to why, I suspect it might be the SQLite API.
One solution would be to not use alter but to drop the table and recreate it e.g.
Another alternative is to close and then re-open the database, however this would be relatively resource expensive.
The following was used for testing various scenarios:-
For the Java testing (showing that the same happens with Java code) :-
The activity used to test :-
The Log shows :-
Additional
Further testing, using specific column names rather than * (null), seems to indicate buggy code.
Obviously not suitable, as specifying the column if non-existent, would result in an exception.
However, consider, the following
as said not really usable but this circumvents the column not found exception to prove the point that null as the 2nd parameter of the query method perhaps doesn't work as intended
:-
This results in :-