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:-
-
- renaming the game_regions table
-
- 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).
-
- 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)
-
- DROP the renamed original games_regions table.
-
- Perhaps/Optionally run a VACUUM on the changed database.
-
- 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+):-
Finally just to show the generated java (and also the assets) :-
