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.
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.
NUMERIC
type, IT CANNOT BE USED and must be converted.Long
, Int
, Byte
....) will be converted to INTEGER.Double
, Float
....) will be converted to REALByteArray
will be converted to BLOB@Embedded
annotation)
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
.
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.
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() {
}
@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.
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,
java(generated)
, and then@Database
annotated class suffixed with Impl, and thene.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.
@ColumnInfo(defaultValue = "whaetever")
where used then DEFAULT 'whaetever'
would be used)
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 How do I use Room's prepackagedDatabaseCallback?
I believe it is easier to use a tool such as SQLiteBrowser, in which case you could base the conversion upon:-
ALTER TABLE
to rename the original tables.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)DROP TABLE ....
VACUUM
@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.