Pre-packaged database has an invalid schema when prepopulating my room

Question

I have a Database (.db) that I want to use to pre-populate my Android Room Database. But even with many tries and researches, I have the same problem. Here is the error :

Caused by: java.lang.IllegalStateException: Pre-packaged database has an invalid schema: countries(com.example.pays.Pays_DB).
     Expected:
    TableInfo{name='countries', columns={name=Column{name='name', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, phonecode=Column{name='phonecode', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, shortname=Column{name='shortname', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}
     Found:
    TableInfo{name='countries', columns={}, foreignKeys=[], indices=[]}

For me, the problem is because my database is empty, but it is not. What is the issue?

Here is my code:

Pays_DB.java

@Entity(tableName = "countries")
    public class Pays_DB {
        @PrimaryKey(autoGenerate = true)
        public int id;
    
        @NonNull
        public String shortname;
    
        @NonNull
        public String name;
    
        @NonNull
        public int phonecode;
    }

Database.java

@androidx.room.Database(entities = {Pays_DB.class}, version = 2)
public abstract class Database extends RoomDatabase {
    public abstract Pays_DAO dao();
}

MainActivity.java

...
public void DBCreate(){
        if (db == null){
            synchronized (Database.class){
                if (db == null){
                    db = Room.databaseBuilder(this,
                            Database.class, "countries.db")
                            .allowMainThreadQueries()
                            .fallbackToDestructiveMigration()
                            .createFromAsset("countries.db")
                            .build();
                    SupportSQLiteDatabase sdb = db.getOpenHelper().getWritableDatabase();
                }
            }
        }
    }

And here is my Database : https://drive.google.com/file/d/1LPB07TQaDuwggs_DJ5QIfYdy6H4Ph-_6/view?usp=sharing

Answer 1

Your issue is that the table name in the pre-packaged database is named tbl_country. Room is expecting to find a table called countries.

• Really (TableInfo{name='countries', columns={}, foreignKeys=[], indices=[]} is saying that it didn't find a matching table at all as a table MUST have at least one column))

You could use:-

@Entity(tableName = "tbl_country")

to overcome this issue (you would have to change Pays_DAO accordingly).

HOWEVER, you would then encounter ongoing issues as Room would still not find what it expects as then column names do not match what Room expects.

That is Room expects a table to exist in the pre-packaged database according to:-

CREATE TABLE IF NOT EXISTS `countries` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `shortname` TEXT NOT NULL, `name` TEXT NOT NULL, `phonecode` INTEGER NOT NULL)

The tbl_country table in your database is defined using :-

CREATE TABLE tbl_country (ID INTEGER PRIMARY KEY AUTOINCREMENT, NOM_PAYS TEXT, NOM_PAYS_SHORT TEXT, POPULATION INTEGER, CAPITALE TEXT)

They are worlds apart.

What you need to do is create a table in the pre-packaged database that matches what room expects (at present the first SQL statement above) and populate that with suitable data. The tbl_country would then be ignored as far as room is concerned.

If you want to actually use the tbl_countries then you additionally need to have a class, annotated with @Entity, that defines the columns as per the tbl_countries table.

e.g.

@Entity(tableName = "tbl_country")
class Country {
    @PrimaryKey(autoGenerate = true)
    Long ID=null;
    String NOM_PAYS;
    String NOM_PAYS_SHORT;
    Integer POPULATION;
    String CAPITALE;
}

• this isn't the only option, but others would depend upon what you are trying to achieve. You may wish to amend you question to further explain your requirements.

Note before rerunning, it would be best to uninstall the App (noting that this deletes the database and thus any data is lost).

Example Fix (just getting it to run successfully)

1. In the tool that you are using to create the pre-packaged database run the SQL CREATE TABLE IF NOT EXISTS countries (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, shortname TEXT NOT NULL, name TEXT NOT NULL, phonecode INTEGER NOT NULL);

• Note that this is extracted from the generated Java from the class Database_Impl in the method createAllTables (i.e. what Room expects (not the room_master_table)).

2. Save the changes and copy the changed pre-packaged database file to replace the asset.

Here's a screen shot that highlights some of the above

3. Run the App (after uninstalling) and it will run successfully.

• The countries table will be empty unless you populated it
• The tbl_countries table will exist but not be accessible via room (see part two)

The database via App Inspection is:-

Stage Two - accessing tbl_countries

1. Create the Class Country (or whatever class name you deem fit) to be

:-

@Entity(tableName = "tbl_country")
class Country {
    @PrimaryKey(autoGenerate = true)
    Long ID=null;
    String NOM_PAYS;
    String NOM_PAYS_SHORT;
    Integer POPULATION;
    String CAPITALE;
}

• Note that Room is quite pernickety so case matters (even though it doesn't to SQLite)
• Note the use of Long (or Integer could be used) for the ID column. If you use long or int (primitives) then Room interprets them as being NotNull (as they cannot be null). However, objects can be null and hence their use so as to match what is in the tbl_country table (i.e. ID INTEGER PRIMARY KEY AUTOINCREMENT i.e. NOT NULL hasn't been specified). When it comes to integer types for the primary key the rules/interpretations are more convoluted.

2. Amend the class annotated with @Database, namely Database to include the Country class (or whatever name you have given the class) in the list of entities in the @Database annotation e.g.

:-

@androidx.room.Database(entities = {Pays_DB.class,Country.class}, version = 2)

3. Optionally add a Dao to the Pays_DAO class to extract from the tbl_country table e.g.

:-

@Query("SELECT * FROM tbl_country")
abstract List<Country> getAllCountry();

• note if Pays_DAO is an interface then remove abstract

4. To test actually extracting from the tbl_country table (assuming the above) then amend MainActivity to include

:-

    DBCreate();
    Pays_DAO dao = db.dao();
    for(Country c: dao.getAllCountry()) {
        Log.d("DBINFO","Country is " + c.NOM_PAYS /* etc */);
    }

5. Uninstall the App and Run and the log contains

:-

D/DBINFO: Country is Canada
D/DBINFO: Country is France
D/DBINFO: Country is Islande
D/DBINFO: Country is Japon
D/DBINFO: Country is Sample
D/DBINFO: Country is Sampl988