android sqlite android-sqlite android-room android-room-relation

Android Room invalid schema only on Lollipop 5.1 SDK 22 with duplicated primaryKeyPosition

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?

Solution

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
- 1. 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).
- 1. 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)
- 1. DROP the renamed original games_regions table.
- 1. Perhaps/Optionally run a VACUUM on the changed database.
- 1. 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) :-