Pre-packaged database has an invalid schema error

Question

I'm building an Android application based on an old Android project. In my new application I'm using Room. I have to use the same database that is used in the first project. Furthermore, I've extracted the database from the first project using com.amitshekhar.android:debug-db library. After obtaining the database file I would like to open it with the Room.

I am building database like this:

Room.databaseBuilder(
            androidContext(),
            Database::class.java, "database.db"
        ).createFromAsset("database.db")
            .build()

Currently I'm using this createFromAsset() method, although later I would use the createFromFile() method, as my database should be downloaded from the server.

But I'm getting the java.lang.IllegalStateException: Pre-packaged database has an invalid schema This happens because there are several datatypes in the database that are not supported in Room such as NVARCHAR(200), DATE or bit.

I'm aware that Room is using only five Sql types, but I do not know how to change this so that Room can open this kind of database using above mentioned methods.

The problem is how to convert NVARCHAR(200), DATE or bit into datatypes that are supported by Room?

Answer 1

You have to convert the database to use specific column type affinities that are supported by Room and that match the entities.

For NVARCHAR(200) you need to have TEXT replace NVARCHAR(200) with the Entity defining the column as a String.

For DATE it depends upon the Entity definition if you are using String based dates e.g. YYYY-MM-DD hh:mm:ss then the Entity should be String and the column affinity TEXT. If storing the date as a timestamp then the Entity should be long and the column affinity INTEGER.

The answer here Can't migrate a table to Room do to an error with the way booleans are saved in Sqlite does a conversion to change BOOL's to INTEGER.

You could adapt this (although I would be cautious with DATE) to suit.

Additional

You may find the following to be of use. You run it against the pre-existing database in your favourite SQLite Manager tool.

WITH potentialRoomChanges AS (
    SELECT sm.name AS tablename, pti.name AS columnname, pti.type, dflt_value, pk,
        CASE 
            WHEN instr(upper(pti.type),'INT') THEN 'INTEGER'
            WHEN instr(upper(pti.type),'CHAR') OR instr(upper(pti.type),'CLOB') OR instr(upper(pti.type),'TEXT') THEN 'TEXT'
            WHEN instr(upper(pti.type),'BLOB') THEN 'BLOB'
            WHEN instr(upper(pti.type),'REAL') OR instr(upper(pti.type),'FLOA') OR instr(upper(pti.type),'DOUB') THEN 'REAL'
            ELSE 'NUMERIC'
        END AS roomtype ,
        CASE WHEN pti.[notnull] THEN 'Investigate NOT NULL USE' END AS nnindicator,
        sql
    FROM sqlite_master AS sm JOIN pragma_table_info(sm.name) AS pti
    WHERE 
        sm.type = 'table' 
        AND sm.name NOT LIKE 'sqlite_%' 
        AND sm.name <> 'android_metadata' 
        AND (
            upper(pti.type) <> roomtype 
            OR instr(roomtype,'NUMERIC') 
            OR nnindicator IS NOT NULL
            OR dflt_value IS NOT NULL
            OR pk > 0
        )
    ORDER BY sm.name,pti.cid
)
SELECT tablename, columnname, type, roomtype, 
CASE WHEN upper(type) <> upper(roomtype) THEN 'Investigate TYPE should be ' ||roomtype END AS typechange_notes,
CASE WHEN roomtype = 'NUMERIC' THEN 'Investigate NUMERIC' END AS numeric_notes, 
CASE WHEN dflt_value IS NOT NULL THEN 'Investigate DEFAULT VALUE of '||dflt_value END AS default_notes,
CASE WHEN pk > 0 THEN 'Investigate PRIMARY KEY inclusion' END AS primarykey_notes,
nnindicator AS notnull_notes 
FROM potentialRoomChanges
;

Example output :-

Hopefully the columns/text are self-explanatory. This is based upon the column types defined (which may differ from the type used). e.g. FLOATING POINT (5th row shown) you would think would be REAL. However according to the derived type affinity the first rule (if the type includes INT it is INTEGER) has been applied.

Rules as per Datatypes In SQLite Version 3 - 3.1. Determination Of Column Affinity.

NUMERIC from my limited experience with room isn't a type that it uses, so it should always be changed to one of the other types.

Answer 2

Use @NonNull before every field of you Pojo (entity) class.
there is no need to add @NonNull to primary key field.
an example is below

@Entity(tableName = "station")
public class Station {
    @PrimaryKey
    private int id;
    @NonNull
    private String name;
    @NonNull
    private int line;
    @NonNull
    private double lat;
    @NonNull
    private double lon;

    ... constructor, getters and setters
}