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?

Minstrel

Possible duplicate of How to migrate existing SQLite application to Room Persistance Library? – Womb

Thanks, but I do not see solution in the answers of the mentioned question. Obviously, implementing the empty migration does nothing because the scheme does not match. – Minstrel

Sometimes it's also good to look at other responses, except the approved answer. For your case indeed this response gives directions to the solution, as the migration is not so simple – Womb

Which version of Room are you using? – Rainey

@Rainey 2.2.0-alpha01 – Minstrel

@Minstrel I got the same error after upgrading from 2.1.0 to 2.2.0-alpha01. Everything was working fine before. – Rainey

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.

Wheeler

Thanks. That is probably the only way. The problem is I have more than 100 tables and several datatypes to change. So I am not going down this road. I will do my best so that these changes are made during database creation (although I won't be creating it). – Minstrel

@Minstrel perhaps another way would be to allow room to create the database (empty) close it and to then copy the pre-existing. The open both outside of room and copy the data using INSERT INTO room_table SELECT * FROM preexisting_table where tables are extracted from sqlite_master. When done the pre-existing could be deleted. – Wheeler

I'll try that as well. Thanks – Minstrel

@Minstrel you may find this question and answer of use. – Wheeler

Thanks. I went through it. I would really like to get around these migration issues, simply by having the same, exact scheme in both generated sqlite and my Room. And if that doesn't work, I'll have this attach method as plan B. – Minstrel

@Bromden you may be interested in [How do I use a Pre-Existing Database in Android Room? ](#57449872). I may expand upon the answer as I'm playing around with an App that both generates the Entities and converts the database. – Wheeler

Thanks Mike. I am with this currently, and I have a doubt about these methods createFromAsset() and createFromFile(). Let say that I use createFromFile() method. The documentation says that Room does not open the pre-packaged database, instead it copies it into the internal app database folder and then opens it. But if I save the database file in internal app database folder (assuming I'll be downloading the database on first run). Can I then avoid this coping and directly save it and open the database? If this isn't possible, should I delete the first database when Room copies it? – Minstrel

If downloading the DB AND it's converted/usable by Room you could either open it directly via Room from it's downloaded location or copy the file to the databases folder and then open it. The createFrom methods actually do the conversion (e.g. change NUMERIC types to TEXT and so on). – Wheeler

@Minstrel you may find RoomExistingSQLiteDBConverter of use. It's still a work-in-progress. It does most (perhaps all) of the conversion. – Wheeler

Thanks Mike, this is great. I was doing most of it manually and it is very time consuming and error prone when dealing with large databases. – Minstrel

@Bromden let us know if you have any issues with it, perhaps via Code Project as per RoomExistingSQLiteDBConverter – Wheeler

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
}

Devereux

Thanks for your answer, but I am afraid I am not sure how this is related to the opening database that contains NVARCHAR with Room. – Minstrel

Answer 3

For me the problem was Not-Null , for every column in the database with NOT NULL it should be reflected in you model with @NonNull.

if you LastName in the database is

"LastName"  TEXT NOT NULL,

in your code on your Model it should be

@NonNull
private String LastName;

Remittee

This solved it for me. There was no need to convert the types from varchar to String or something. Just make sure that all fields that are nullable in the prepackaged database file also are marked as nullable in room. Also, I needed to add the Integer type instead of primitive int, to make the field nullable. – Hue

Answer 4

In my case error was solved by changing the name parameter different from my database name. in this code, I set the name parameter to "my_db"

fun getInstance(context: Context): AppDatabase {
    if (INSTANCE == null) {
        INSTANCE = Room.databaseBuilder(
            context,
            AppDatabase::class.java,
            "my_db" //this parameter
        )
        .allowMainThreadQueries()
        .createFromAsset("database/harry_potter.db")
        .build()
    }
}

Corporeal

Answer 5

This should be resolvable with ColumnInfo.typeAffinity. Unfortunately, there's an open issue relating to typeAffinity and schema validation:

ColumnInfo type affinity is being suppressed by column adapters.

Maybe go there and hit the "+" so this issue gets some attention.

Tiffanytiffi

Answer 6

If you are using Kotlin, a common problem is that if your data class's field is declared as non-null, then your SQL must specify NOT NULL, and vice versa, if it is nullable, it cannot be declared as NOT NULL.

Scutt