Mapping datatypes from SQLite dump file to Room entities

I have a db.sqlite dump file. When I view it in SQLite Browser I can see that it contains many CREATE TABLE, CREATE INDEX statements. I'm using this dump file to create my Room Database by using createFromAsset("db.sqlite") function.

My question is, what is the correct way to map the datatypes in these tables from the dump file into the Kotlin datatypes in Room entities? Is there a rule for datatype mapping from SQLite dump file into Room entities? Is there an automated tool/library that can do it for me?

The types that SQLite Browser shows in these tables are quite broad:

date, datetime, mediumtext, varchar(255), varchar(32), tinyint(1), int(1), int(10), bigint(20), TEXT

> Is there an automated tool/library that can do it for me?

No (that I am aware of). I did have one BUT as Room started changing/adding features it became a nightmare AND its not that hard to convert. As will be shown below, Room can assist and make your life easier.

• Here's a link to the project in github, which has not been changed since December 2019

> My question is, what is the correct way to map the datatypes in these tables from the dump file into the Kotlin datatypes in Room entities? Is there a rule for datatype mapping from SQLite dump file into Room entities?

A column type will be one of four types INTEGER, TEXT, BLOB, or REAL. Only these types an be used. However, Room will generate these based upon the types when Room looks at the fields in the @Entity annotated classes.

• Room does not cater for the default/drop through NUMERIC type, IT CANNOT BE USED and must be converted.

1. A string will be converted to TEXT.
2. An integer type (e.g. Long, Int, Byte ....) will be converted to INTEGER.
3. A decimal type (e.g. Double, Float ....) will be converted to REAL
4. A byte stream, such as ByteArray will be converted to BLOB
5. more complex objects (e.g. Date) that are are not basically primitive types have to be converted to a primitive type or String (typically the latter as a JSON representation) OR the fields that make up the object can be added (possibly via an @Embedded annotation)
   1. by basically primitive type, this does not mean an actual primitive type as per Java int, long etc, but rather a type that Room knows will be one of the four column types above.

Now considering date, datetime, mediumtext, varchar(255), varchar(32), tinyint(1), int(1), int(10), bigint(20), TEXT

• date, you would need to look at how the date value is stored, is it a textual representation e.g. 2023-01-01 or is it an integer value? Respectively a field type of String or Long.

  • if you use a Date type object e.g. var mydate: Date then you need a TypeConverter to Convert the date into either a Long/Int or a String (or even a ByteArray if wanted).

• datetime likewise.

• mediumtext, probably String

• varchar would very likely be String

• all the others very likely Long or Int

• Note. With the exception of a column defined as INTEGER that is the sole column of a PRIMARY KEY, SQLite can store any type in any column BUT ROOM WILL NOT.

Now the real issue you could encounter, is that if using createFromAsset that you will need to convert the asset.

The easiest way to do this is to first create the @Entity annotated classes, one per table based upon the above i.e. deciding what type of data is actually stored, as column names are very flexible in SQLite and also that you can store any type in any column (exception above), so it is the actual data that can be important.

• perhaps refer to https://stackoverflow.com/questions/45557278/how-flexible-restricive-are-sqlite-column-types

When you have the proposed entities coded, then code an @Database annotated class with the entities parameter specifying the @Entity annotated classes. It could be as simple as, for example:-

@Database(entities = [Entity1::class,Entity2::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
}

• Obviously the @Entity annotated classes would likely not be Entity and Entity2

Now compile the project (successfully).

Room will then generate java that includes the SQL for creating the tables.

Example of The above (based upon an assumed approximation of your various types):-

Entity1 for want of better names:-

@Entity
data class Entity1(
    @PrimaryKey /* All entities MUST have a primary key */
    var datetime: Long=System.currentTimeMillis(), /* possible use */
    var date: String,
    var mediumtext: String,
    var varchar255: String?, /*<<<<<<<< nullable to demonstrate */
    var varchar32: String,
    var tinyint1: Short,
    var int1: Int,
    var int10: Int,
    var bigint20: Long /* Note BigInteger would need a type converter */
)

The @Database annotated class:-

@Database(entities = [Entity1::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
}

Compile Successfully

e.g.:-

BUILD SUCCESSFUL in 7s
34 actionable tasks: 8 executed, 26 up-to-date

From the Android View,

1. find the java(generated), and then
2. the class that is the same name as the @Database annotated class suffixed with Impl, and then
3. the method name createAllTables

e.g.:-

From the above the createAllTables method is:-

  public void createAllTables(@NonNull final SupportSQLiteDatabase db) {
    db.execSQL("CREATE TABLE IF NOT EXISTS `Entity1` (`datetime` INTEGER NOT NULL, `date` TEXT NOT NULL, `mediumtext` TEXT NOT NULL, `varchar255` TEXT, `varchar32` TEXT NOT NULL, `tinyint1` INTEGER NOT NULL, `int1` INTEGER NOT NULL, `int10` INTEGER NOT NULL, `bigint20` INTEGER NOT NULL, PRIMARY KEY(`datetime`))");
    db.execSQL("CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)");
    db.execSQL("INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, '142451a6629652e0eafe63b6637a32ff')");
  }

As can be seen Room converts types as described above BUT note that Room does more such as add NOT NULL constraints or not as in the case of the varchar255 column.

• NOT NULL constraints must be as expected and additionally DEFAULT values must be as expected (i.e. if, for example, @ColumnInfo(defaultValue = "whaetever") where used then DEFAULT 'whaetever' would be used)
  • if you need to convert data and no default value is specified the respective column definition in the asset must not have DEFAULT ...., this can complicate the copying of the data (step 3 below).

Room will only accept a database that has the definitions as per the generated SQL. If there are any discrepancies then the result will a be an exception that that lists what Room Expected and what was Found.

As such there is no doubt whatsoever, that the database copied from the assets needs to be changed (the only acceptable column type in your list is TEXT, every other column type would cause an exception).

You can either convert the database prior to it being copied into the assets folder or you can use the PrepackagedDatabaseCallback, example of its use can be found here https://stackoverflow.com/questions/70141979/how-do-i-use-rooms-prepackageddatabasecallback

I believe it is easier to use a tool such as SQLiteBrowser, in which case you could base the conversion upon:-

1. Use an ALTER TABLE to rename the original tables.
2. Create the tables using the generated SQL as obtained above.
   1. there is no need to bother with room_master_table, room will handle that.
3. Copy the data from the renamed original tables to their equivalents
   1. could be as simple as INSERT INTO entity1 SELECT * FROM renamed_entity1, it could be more complicated e.g. if you have nulls for a NOT NULL (perhaps add the ? to the field, recompile and so on)
4. Delete the renamed tables using DROP TABLE ....
5. Perhaps consider doing a VACUUM
6. Save the database and then copy it into the assets folder
7. You can then complete the Room code (add the @Dao annotated interfaces, amend the @Database annotated class, add POJO's as required). If all has been done well, then the database should be opened and used successfully.