Is there a way to generate SQLite file from iOS platform, which is compatible with Android Room database library?

Question

We have an Android app, which is using read/ write data using Android Room database library, and then download/ upload to cloud storage.

Now, we are developing an iOS app, which is suppose able to read/ write the data.

There is no issue for our iOS app to read SQLite file written by Android Room database, because we are the one who define database schema.

However, there are issue, for iOS app to write an Android Room database library compatible SQLite file. We notice Android Room database library is expecting the following 3 additional tables.

CREATE TABLE room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT);
CREATE TABLE android_metadata (locale TEXT);
CREATE TABLE sqlite_sequence(name,seq);

The sample data contain in the 3 additional tables are as follow.

INSERT INTO "main"."room_master_table" ("id", "identity_hash") VALUES ('42', '5471e2f102feee2750d42986836b0c42');

INSERT INTO "main"."android_metadata" ("locale") VALUES ('en_US');

INSERT INTO "main"."sqlite_sequence" ("name", "seq") VALUES ('plain_note', '62');
INSERT INTO "main"."sqlite_sequence" ("name", "seq") VALUES ('attachment', '6');
INSERT INTO "main"."sqlite_sequence" ("name", "seq") VALUES ('tab_info', '5');

I think I am able to generate data for android_metadata & sqlite_sequence manually.

But, I am clueless in generating data for room_master_table.

I tested a SQLite file without room_master_table, it will cause the following error during reading via Android Room.

Caused by: java.lang.IllegalStateException: Room cannot verify the data integrity. Looks like you've changed schema but forgot to update the version number. You can simply fix this by increasing the version number.

I was wondering, is there a way to generate SQLite file from iOS platform, which is compatible with Android Room database library? Or, it is simply not possible?

Answer 1

In short don't try to create the said tables, they are all system tables you should let them be created accordingly.

android_metadata just stores the locale and is created by the android SQLiteOpenHelper.

room_master_table stores a hash of the schema, if the hash doesn't match then you get Looks like you've changed schema but forgot to update the version number.. The hash is created at compile time.

sqlite_sequence exists if the AUTOINCREMENT keyword is used in a PRIMARY KEY definition. There is rarely any need to use AUTOINCREMENT. It is recommended to not use AUTOINCREMENT (Room's autogenerate = true). The table hold's a row per table that has AUTOINCREMENT. It stores the highest ever used rowid value. When a new row is inserted instead of just using the highest rowid to determine the next ( max(rowid) + 1), it instead uses the greater of the max(rowid) and the stored max ever allocated value and then adds 1. Thus it has to access the sqlite_sequence table to get the value and thus unless there is a need to always have a rowid greater than any ever allocated it is a waste.

If you use an SQLite database for both, and the IOS doesn't have sqlite_sequence then you should not have autogenerate coded anywhere in the @PrimaryKey annotation, as Room will then hit the Expected .... Found .... errors. As room is very particular about the schema.

I was wondering, is there a way to generate SQLite file from iOS platform, which is compatible with Android Room database library? Or, it is simply not possible?

Yes it is possible. In short don't create any of the tables, let Room create them. Then (assuming that both schemas are similar enough (data wise) have the same columns and column order). You can then load the Room tables from the SQLite file along the lines of

`INSERT INTO room_table_? SELECT * FROM ios_table_?;`

When and how frequently you do this will change exactly how you get to the stage when you can do the copy. If the schemas are identical and it's a one of then you could perhaps utilise the .createFromFile or if the schemas are close .createFromFile where you can modify the schema accordingly (if so then you might find this useful).