英文:
Android Room invalid schema only on Lollipop 5.1 SDK 22 with duplicated primaryKeyPosition
问题
我有以下的实体类：
@Entity(tableName = "game_regions",
primaryKeys = {"_game", "_region_area"},
foreignKeys = {
@ForeignKey(
entity = GameEntity.class,
parentColumns = "_id",
childColumns = "_game"
),
@ForeignKey(
entity = RegionAreaEntity.class,
parentColumns = "_id",
childColumns = "_region_area"
)})
public class GameRegionsEntity {
@NonNull
@ColumnInfo(name = "_game")
private String game;
@NonNull
@ColumnInfo(name = "_region_area")
private String regionArea;
public GameRegionsEntity(@NonNull String game, @NonNull String regionArea) {
this.game = game;
this.regionArea = regionArea;
}
@NonNull
public String getGame() {
return game;
}
public void setGame(@NonNull String game) {
this.game = game;
}
@NonNull
public String getRegionArea() {
return regionArea;
}
public void setRegionArea(@NonNull String regionArea) {
this.regionArea = regionArea;
}
}
在SQL中，它表示为：
CREATE TABLE "game_regions" (
"_game" TEXT NOT NULL,
"_region_area" TEXT NOT NULL,
PRIMARY KEY("_game","_region_area"),
FOREIGN KEY("_region_area") REFERENCES "region_areas"("_id"),
UNIQUE("_game","_region_area"),
FOREIGN KEY("_game") REFERENCES "games"("_id")
);
当使用Android 6或更高版本的设备时，一切都正常工作，没有任何错误。然而，当使用Android Lollipop 5.1时，在第一次验证模式时会引发以下异常：
java.lang.IllegalStateException: Pre-packaged database has an invalid schema: game_regions(GameRegionsEntity).
Expected:
TableInfo{name='game_regions', columns={_region_area=Column{name='_region_area', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=2, defaultValue='undefined'}, _game=Column{name='_game', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=1, defaultValue='undefined'}}, foreignKeys=[ForeignKey{referenceTable='games', onDelete='NO ACTION +', onUpdate='NO ACTION', columnNames=['_game'], referenceColumnNames=['_id']}, ForeignKey{referenceTable='region_areas', onDelete='NO ACTION +', onUpdate='NO ACTION', columnNames=['_region_area'], referenceColumnNames=['_id']}], indices=[]}
Found:
TableInfo{name='game_regions', columns={_game=Column{name='_game', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=1, defaultValue='undefined'}, _region_area=Column{name='_region_area', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=1, defaultValue='undefined'}}, foreignKeys=[ForeignKey{referenceTable='region_areas', onDelete='NO ACTION +', onUpdate='NO ACTION', columnNames=['_region_area'], referenceColumnNames=['_id']}, ForeignKey{referenceTable='games', onDelete='NO ACTION +', onUpdate='NO ACTION', columnNames=['_game'], referenceColumnNames=['_id']}], indices=null}
如果我们比较"EXPECTED"和"FOUND"模式的结果，我们注意到唯一变化的是两个主键的"primaryKeyPosition"都是1。
我不知道是什么原因导致这个错误，以及为什么它只在Android Lollipop 5.1 (SDK 22)上崩溃，而在23到33的每个版本上都能正常工作。有什么线索吗？
英文:
I have the following Entity:
@Entity(tableName = "game_regions",
primaryKeys = {"_game", "_region_area"},
foreignKeys = {
@ForeignKey(
entity = GameEntity.class,
parentColumns = "_id",
childColumns = "_game"
),
@ForeignKey(
entity = RegionAreaEntity.class,
parentColumns = "_id",
childColumns = "_region_area"
)})
public class GameRegionsEntity {
@NonNull
@ColumnInfo(name = "_game")
private String game;
@NonNull
@ColumnInfo(name = "_region_area")
private String regionArea;
public GameRegionsEntity(@NonNull String game, @NonNull String regionArea) {
this.game = game;
this.regionArea = regionArea;
}
@NonNull
public String getGame() {
return game;
}
public void setGame(@NonNull String game) {
this.game = game;
}
@NonNull
public String getRegionArea() {
return regionArea;
}
public void setRegionArea(@NonNull String regionArea) {
this.regionArea = regionArea;
}
}
Which is represented in this way in SQL:
CREATE TABLE "game_regions" (
"_game" TEXT NOT NULL,
"_region_area" TEXT NOT NULL,
PRIMARY KEY("_game","_region_area"),
FOREIGN KEY("_region_area") REFERENCES "region_areas"("_id"),
UNIQUE("_game","_region_area"),
FOREIGN KEY("_game") REFERENCES "games"("_id")
);
When using a device with Android 6 or higher, everything works without any error, however, when using Android Lollipop 5.1, it throws the following exception when the schema gets validated the first time:
java.lang.IllegalStateException: Pre-packaged database has an invalid schema: game_regions(GameRegionsEntity).
Expected:
TableInfo{name='game_regions', columns={_region_area=Column{name='_region_area', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=2, defaultValue='undefined'}, _game=Column{name='_game', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=1, defaultValue='undefined'}}, foreignKeys=[ForeignKey{referenceTable='games', onDelete='NO ACTION +', onUpdate='NO ACTION', columnNames=[_game], referenceColumnNames=[_id]}, ForeignKey{referenceTable='region_areas', onDelete='NO ACTION +', onUpdate='NO ACTION', columnNames=[_region_area], referenceColumnNames=[_id]}], indices=[]}
Found:
TableInfo{name='game_regions', columns={_game=Column{name='_game', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=1, defaultValue='undefined'}, _region_area=Column{name='_region_area', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=1, defaultValue='undefined'}}, foreignKeys=[ForeignKey{referenceTable='region_areas', onDelete='NO ACTION +', onUpdate='NO ACTION', columnNames=[_region_area], referenceColumnNames=[_id]}, ForeignKey{referenceTable='games', onDelete='NO ACTION +', onUpdate='NO ACTION', columnNames=[_game], referenceColumnNames=[_id]}], indices=null}
If we compare the EXPECTED and the FOUND schema results: we notice that the only thing that changes is that primaryKeyPosition for both primary keys are 1.
I don't know what is causing this error and why this is crashing ONLY in Android Lollipop 5.1 (SDK 22) and its working on every version from 23 until 33.
Any hint?
答案1
得分: 1
我建议不提供/使用您的SQL资产，而是使用Room创建的SQL。
然而，始终建议使用Room提供/生成的SQL来创建预先存在的数据库的SQLite组件。
不需要唯一索引，主键隐式唯一。
Room SQL如下（来自测试）：
CREATE TABLE IF NOT EXISTS `game_regions` (
`_game` TEXT NOT NULL,
`_region_area` TEXT NOT NULL,
PRIMARY KEY(`_game`, `_region_area`),
FOREIGN KEY(`_game`) REFERENCES `GameEntity`(`_id`) ON UPDATE NO ACTION ON DELETE NO ACTION ,
FOREIGN KEY(`_region_area`) REFERENCES `RegionAreaEntity`(`_id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
您可能希望在您喜欢的SQLite工具中执行以下操作来转换现有资产数据库：
- 重命名game_regions表。
- 使用上述SQL（最好使用Room生成的SQL）。
- 从重命名的原始表复制数据到新创建的表，例如
INSERT INTO game_regions SELECT * FROM game_regions_renamed（未经测试）。 - 删除重命名的原始games_regions表。
- 可能/可选地对更改后的数据库运行VACUUM。
- 保存数据库。
测试（建议答案的原因）：
最初创建了一个项目，使用了您的代码以及以下支持性代码（模拟您未提供的代码）：
GameEntity类
@Entity
class GameEntity {
@NonNull
@PrimaryKey
String _id;
String game_type;
}
RegionAreaEntity类
@Entity
class RegionAreaEntity {
@NonNull
@PrimaryKey
String _id;
}
TheDatabase抽象类（即带有@Database注释的类）
@Database(entities = {GameEntity.class, RegionAreaEntity.class, GameRegionsEntity.class}, exportSchema = false, version = 1)
abstract class TheDatabase extends RoomDatabase {
// ... 省略其他代码
}
MainActivity（用于测试）
public class MainActivity extends AppCompatActivity {
TheDatabase db;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
db = TheDatabase.getInstance(this);
SupportSQLiteDatabase suppdb = db.getOpenHelper().getWritableDatabase();
}
}
测试部分1：
删除了createFromAsset方法调用并在API 22设备上运行（在Android Studio模拟器中）。它运行良好，从而排除了API 22与Room不兼容的问题。
测试部分2：
卸载了应用程序，重新启用了createFromAsset。使用Navicat（一个SQLite工具）使用以下SQL创建了具有两个支持表（GameEntity和RegionAreaEntity，使用Room生成的SQL）的数据库：
DROP TABLE IF EXISTS game_regions;
DROP TABLE IF EXISTS gameentity;
DROP TABLE IF EXISTS regionareaentity;
CREATE TABLE IF NOT EXISTS `GameEntity` (`_id` TEXT NOT NULL, `game_type` TEXT, PRIMARY KEY(`_id`));
CREATE TABLE IF NOT EXISTS `RegionAreaEntity` (`_id` TEXT NOT NULL, PRIMARY KEY(`_id`));
CREATE TABLE "game_regions" (
"_game" TEXT NOT NULL,
"_region_area" TEXT NOT NULL,
PRIMARY KEY("_game","_region_area"),
FOREIGN KEY("_region_area") REFERENCES "region_areas"("_id"),
UNIQUE("_game","_region_area"),
FOREIGN KEY("_game") REFERENCES "games"("_id")
);
然后运行SQL：
DROP TABLE IF EXISTS regionareaentity;
CREATE TABLE IF NOT EXISTS `GameEntity` (`_id` TEXT NOT NULL, `game_type` TEXT, PRIMARY KEY(`_id`))
> OK
> Time: 0.024s
CREATE TABLE IF NOT EXISTS `RegionAreaEntity` (`_id` TEXT NOT NULL, PRIMARY KEY(`_id`))
> OK
> Time: 0.028s
CREATE TABLE "game_regions" (
"_game" TEXT NOT NULL,
"_region_area" TEXT NOT NULL,
PRIMARY KEY("_game","_region_area"),
FOREIGN KEY("_region_area") REFERENCES "region_areas"("_id"),
UNIQUE("_game","_region_area"),
FOREIGN KEY("_game") REFERENCES "games"("_id")
)
> OK
> Time: 0.024s
保存数据库（关闭数据库和连接，然后退出Navicat）。将文件复制到assets文件夹中（将文件重命名为the_database.db）。运行应用程序后：
发生了运行时异常，显示了不同的结果，因为主键位置颠倒（不确定具体原因，但怀疑是因为项目不同）。
测试部分3（使用Room的SQL）：
卸载了应用程序。在Navicat中使用以下SQL（Room的SQL）：
DROP TABLE IF EXISTS game_regions;
DROP TABLE IF EXISTS gameentity;
DROP TABLE IF EXISTS regionareaentity;
CREATE TABLE IF NOT EXISTS `GameEntity` (`_id` TEXT NOT NULL, `game_type` TEXT, PRIMARY KEY(`_id`));
CREATE TABLE IF NOT EXISTS `RegionAreaEntity` (`_id` TEXT NOT NULL, PRIMARY KEY(`_id`));
CREATE TABLE IF NOT EXISTS `game_regions` (`_game` TEXT NOT NULL, `_region_area` TEXT NOT NULL, PRIMARY KEY(`_game`, `_region_area`), FOREIGN KEY(`_game`) REFERENCES `GameEntity`(`_id`) ON UPDATE NO ACTION ON DELETE NO ACTION , FOREIGN KEY(`_region_area`) REFERENCES `RegionAreaEntity`(`_id`) ON UPDATE NO ACTION ON DELETE NO ACTION );
/*
CREATE TABLE "game_regions" (
"_game" TEXT NOT NULL,
"_region_area" TEXT NOT NULL,
PRIMARY KEY("_game","_region_area"),
FOREIGN KEY("_region_area") REFERENCES "region_areas"("_id"),
UNIQUE("_game","_region_area"),
FOREIGN KEY("_game") REFERENCES "games"("_id")
);
*/
然后运行了SQL：
DROP TABLE IF EXISTS game_regions
> OK
> Time: 0.517s
DROP TABLE IF EXISTS gameentity
> OK
> Time: 0.024s
DROP TABLE IF EXISTS regionareaentity
> OK
> Time: 0.024s
CREATE TABLE IF NOT EXISTS `GameEntity` (`_id` TEXT NOT NULL, `game_type` TEXT, PRIMARY KEY(`_id`))
> OK
> Time: 0.024s
CREATE TABLE IF NOT EXISTS `RegionAreaEntity` (`_id` TEXT NOT NULL, PRIMARY KEY(`_id`))
> OK
<details>
<summary>英文:</summary>
I would suggest not providing/using your SQL for the asset BUT instead using the SQL that room creates.
- (*suspect that the issue may be the UNIQUE index **as that is certainly different from what room expects***)
- At a guess the annotation processing is mixing up the UNIQUE index's positions with the primary keys positions and then the expected/found message is being issued.
- no idea why it doesn't complain post API 22.
----
**However,** it is **ALWAYS** suggested to **utilise the SQL that room provides/generates** for the creation of the SQLite components for a pre-existing database.
----
- There is no need for the UNIQUE index, a Primary Key is implicitly UNIQUE.
The Room SQL being *(from a test)*:-
CREATE TABLE IF NOT EXISTS `game_regions` (
`_game` TEXT NOT NULL,
`_region_area` TEXT NOT NULL,
PRIMARY KEY(`_game`, `_region_area`),
FOREIGN KEY(`_game`) REFERENCES `GameEntity`(`_id`) ON UPDATE NO ACTION ON DELETE NO ACTION ,
FOREIGN KEY(`_region_area`) REFERENCES `RegionAreaEntity`(`_id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
- you may wish, in your favourite SQLite Tool, to convert the existing asset database by:-
- 1. renaming the **game_regions** table
- 2. use the SQL above (or better still the SQL generated by Room)
- From the Android View, look for the Java(generated) directory, then
- open the class that is the same name as the class that is annotated with `@Database` but suffixed with **`_Impl`**
- locate the **`createAllTables`** method which includes the actual SQL for the expected tables *(noting that the UNIQUE constraint isn't directly supported by Room annotations)*.
- 3. copy the data from the renamed original table to the newly created table e.g. `INSERT INTO game_regions SELECT * FROM game_regions_renamed` ***(not tested)***
- 4. DROP the renamed original **games_regions** table.
- 5. Perhaps/Optionally run a VACUUM on the changed database.
- 6. Save the database.
**The test** (aka reason for the suggested answer)
Initially a project as created using your code plus the following supportive code (to mimic the code you have not supplied):-
**GameEntity class**
@Entity
class GameEntity {
@NonNull
@PrimaryKey
String _id;
String game_type;
}
**RegionAreaEntity class**
@Entity
class RegionAreaEntity {
@NonNull
@PrimaryKey
String _id;
}
**TheDatabase abstract class** (i.e. the @Database annotated class)
@Database(entities = {GameEntity.class,RegionAreaEntity.class,GameRegionsEntity.class},exportSchema = false,version = 1)
abstract class TheDatabase extends RoomDatabase {
private static volatile TheDatabase instance;
public static TheDatabase getInstance(Context context) {
if (instance==null) {
instance = Room.databaseBuilder(
context,TheDatabase.class,"the_database"
)
.allowMainThreadQueries()
.createFromAsset("the_database.db")
.build();
}
return instance;
}
}
- Note that .allowMainThreadQueries to cater for running on the main thread (for simplicity and brevity).
**Main Activity** (to test)
public class MainActivity extends AppCompatActivity {
TheDatabase db;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
db = TheDatabase.getInstance(this);
SupportSQLiteDatabase suppdb = db.getOpenHelper().getWritableDatabase();
}
}
- i.e. this will force an open of the database.
**Test Part 1**
The `createFromAsset` method call was removed and run on an API 22 device (emulator in Android Studio). It ran fine. Thus eliminating any issue that API 22 is blatantly not compatible with Room.
**Test Part 2**
The App was uninstalled, `createFromAsset` was reinstated.
Using Navicat (an SQLite tool) created the database with the two supportive tables (GameEntity and RegionAreaEnntity using the SQL generated by room) using:-
DROP TABLE IF EXISTS game_regions;
DROP TABLE IF EXISTS gameentity;
DROP TABLE IF EXISTS regionareaentity;
CREATE TABLE IF NOT EXISTS `GameEntity` (`_id` TEXT NOT NULL, `game_type` TEXT, PRIMARY KEY(`_id`));
CREATE TABLE IF NOT EXISTS `RegionAreaEntity` (`_id` TEXT NOT NULL, PRIMARY KEY(`_id`));
CREATE TABLE "game_regions" (
"_game" TEXT NOT NULL,
"_region_area" TEXT NOT NULL,
PRIMARY KEY("_game","_region_area"),
FOREIGN KEY("_region_area") REFERENCES "region_areas"("_id"),
UNIQUE("_game","_region_area"),
FOREIGN KEY("_game") REFERENCES "games"("_id")
);
Ran the SQL:-
DROP TABLE IF EXISTS regionareaentity
> OK
> Time: 0.024s
CREATE TABLE IF NOT EXISTS `GameEntity` (`_id` TEXT NOT NULL, `game_type` TEXT, PRIMARY KEY(`_id`))
> OK
> Time: 0.024s
CREATE TABLE IF NOT EXISTS `RegionAreaEntity` (`_id` TEXT NOT NULL, PRIMARY KEY(`_id`))
> OK
> Time: 0.028s
CREATE TABLE "game_regions" (
"_game" TEXT NOT NULL,
"_region_area" TEXT NOT NULL,
PRIMARY KEY("_game","_region_area"),
FOREIGN KEY("_region_area") REFERENCES "region_areas"("_id"),
UNIQUE("_game","_region_area"),
FOREIGN KEY("_game") REFERENCES "games"("_id")
)
> OK
> Time: 0.024s
Saved the database (closed the database and the connection and then quit Navicat).
Copied the file into the assets folder (renaming the file to the_database.db).
Ran the App and:-
2023-06-16 11:07:20.700 4744-4744/a.a.so76483436javaroomapi22issue E/AndroidRuntime: FATAL EXCEPTION: main
Process: a.a.so76483436javaroomapi22issue, PID: 4744
java.lang.RuntimeException: Unable to start activity ComponentInfo{a.a.so76483436javaroomapi22issue/a.a.so76483436javaroomapi22issue.MainActivity}: java.lang.IllegalStateException: Pre-packaged database has an invalid schema: game_regions(a.a.so76483436javaroomapi22issue.GameRegionsEntity).
Expected:
TableInfo{name='game_regions', columns={_region_area=Column{name='_region_area', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=2, defaultValue='undefined'}, _game=Column{name='_game', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=1, defaultValue='undefined'}}, foreignKeys=[ForeignKey{referenceTable='RegionAreaEntity', onDelete='NO ACTION +', onUpdate='NO ACTION', columnNames=[_region_area], referenceColumnNames=[_id]}, ForeignKey{referenceTable='GameEntity', onDelete='NO ACTION +', onUpdate='NO ACTION', columnNames=[_game], referenceColumnNames=[_id]}], indices=[]}
Found:
TableInfo{name='game_regions', columns={_game=Column{name='_game', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=1, defaultValue='undefined'}, _region_area=Column{name='_region_area', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=2, defaultValue='undefined'}}, foreignKeys=[ForeignKey{referenceTable='games', onDelete='NO ACTION +', onUpdate='NO ACTION', columnNames=[_game], referenceColumnNames=[_id]}, ForeignKey{referenceTable='region_areas', onDelete='NO ACTION +', onUpdate='NO ACTION', columnNames=[_region_area], referenceColumnNames=[_id]}], indices=null}
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2325)
- slightly different result as pkey positions are reversed (not sure exactly why but suspect due to the projects being different).
**Test Part 3** (using Room's SQL)
Uninstalled the App.
Used the following in Navicat (Room's SQL):-
DROP TABLE IF EXISTS game_regions;
DROP TABLE IF EXISTS gameentity;
DROP TABLE IF EXISTS regionareaentity;
CREATE TABLE IF NOT EXISTS `GameEntity` (`_id` TEXT NOT NULL, `game_type` TEXT, PRIMARY KEY(`_id`));
CREATE TABLE IF NOT EXISTS `RegionAreaEntity` (`_id` TEXT NOT NULL, PRIMARY KEY(`_id`));
CREATE TABLE IF NOT EXISTS `game_regions` (`_game` TEXT NOT NULL, `_region_area` TEXT NOT NULL, PRIMARY KEY(`_game`, `_region_area`), FOREIGN KEY(`_game`) REFERENCES `GameEntity`(`_id`) ON UPDATE NO ACTION ON DELETE NO ACTION , FOREIGN KEY(`_region_area`) REFERENCES `RegionAreaEntity`(`_id`) ON UPDATE NO ACTION ON DELETE NO ACTION );
/*
CREATE TABLE "game_regions" (
"_game" TEXT NOT NULL,
"_region_area" TEXT NOT NULL,
PRIMARY KEY("_game","_region_area"),
FOREIGN KEY("_region_area") REFERENCES "region_areas"("_id"),
UNIQUE("_game","_region_area"),
FOREIGN KEY("_game") REFERENCES "games"("_id")
);
*/
Resulting in:-
DROP TABLE IF EXISTS game_regions
> OK
> Time: 0.517s
DROP TABLE IF EXISTS gameentity
> OK
> Time: 0.024s
DROP TABLE IF EXISTS regionareaentity
> OK
> Time: 0.024s
CREATE TABLE IF NOT EXISTS `GameEntity` (`_id` TEXT NOT NULL, `game_type` TEXT, PRIMARY KEY(`_id`))
> OK
> Time: 0.024s
CREATE TABLE IF NOT EXISTS `RegionAreaEntity` (`_id` TEXT NOT NULL, PRIMARY KEY(`_id`))
> OK
> Time: 0.024s
CREATE TABLE IF NOT EXISTS `game_regions` (`_game` TEXT NOT NULL, `_region_area` TEXT NOT NULL, PRIMARY KEY(`_game`, `_region_area`), FOREIGN KEY(`_game`) REFERENCES `GameEntity`(`_id`) ON UPDATE NO ACTION ON DELETE NO ACTION , FOREIGN KEY(`_region_area`) REFERENCES `RegionAreaEntity`(`_id`) ON UPDATE NO ACTION ON DELETE NO ACTION )
> OK
> Time: 0.024s
/*
CREATE TABLE "game_regions" (
"_game" TEXT NOT NULL,
"_region_area" TEXT NOT NULL,
PRIMARY KEY("_game","_region_area"),
FOREIGN KEY("_region_area") REFERENCES "region_areas"("_id"),
UNIQUE("_game","_region_area"),
FOREIGN KEY("_game") REFERENCES "games"("_id")
);
*/
> not an error
> Time: 0s
Closed the database and connection and quit Navicat.
Copied the database into the assets directory (renaming the previous used db file).
Ran the App and it ran fine. Device Explorer showing (App Inspection only good for API 26+):-
[![enter image description here][1]][1]
----
Finally just to show the generated java (and also the assets) :-
----
[![enter image description here][2]][2]
[1]: https://i.stack.imgur.com/C4JW6.png
[2]: https://i.stack.imgur.com/orY8U.png
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论