Making pre-packed .db file for pre-populating a Room database

Question

Given a situation that a .db file was needed to pre-populate a Room database with a problem arising at the run-time saying Pre-packaged database has an invalid schema. Among many columns only two are not matching that are both defined as Boolean type in Kotlin.

Kotlin entity class:

@ColumnInfo(name = "xxx")
var xxx: Boolean = false ,

@ColumnInfo(name = "yyy")
var yyy: Boolean? = null

Error message:

Expected:
    TableInfo{name='zzz', columns={xxx=Column{name='xxx', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, yyy=Column{name='yyy', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, foreignKeys=[], indices=[]}
Found:
    TableInfo{name='zzz', columns={xxx=Column{name='xxx', type='NUMERIC', affinity='1', notNull=true, primaryKeyPosition=0, defaultValue='null'}, yyy=Column{name='yyy', type='NUMERIC', affinity='1', notNull=false, primaryKeyPosition=0, defaultValue='null'}, foreignKeys=[], indices=[]}

For creation of pre-packed .db file DB Browser for SQLite is being used and this is what comes as query for creating the actual zzz table:

CREATE TABLE "zzz" (
    "id"    INTEGER PRIMARY KEY AUTOINCREMENT,
    ...
    "xxx"   INTEGER NOT NULL DEFAULT 0,
    "yyy"   INTEGER DEFAULT NULL
);

Even though xxx and yyy columns are defined to be of INTEGER type they end up being of NUMERIC type.

Answer 1

The pre-packagaed database being opened read and inspected has a schema where the column types for xxx and yyy are not INTEGER but are NUMERIC (or considered to be NUMERIC).

Room does not handle a type of NUMERIC as basically it is the catchall type and therefore it cannot definitively determine the type that should be used for the member variable, it could only guess. The message is therefore saying that it considers the pre-packaged database as unsuitable in it's incarnation.

You need to either recreate the pre-packaged database using a schema that sets the xxx and zzz columns to be INTEGER e.g. using :-

CREATE TABLE "zzz" (
    "id"    INTEGER PRIMARY KEY AUTOINCREMENT,
    ...
    "xxx"   INTEGER NOT NULL DEFAULT 0,
    "yyy"   INTEGER DEFAULT NULL
);

or you need to ALTER the zzz table in pre-packaged database so that the schema is as above. In short Room will not handle a column that has been defined that has a derived type of NUMERIC as it doesn't know how to handle such a type as NUMERIC as a type is a catchall.

To ALTER the table you could use something along the lines of :-

DROP TABLE IF EXISTS zzz_amended;
CREATE TABLE IF NOT EXISTS "zzz_amended" (
    "id"    INTEGER PRIMARY KEY AUTOINCREMENT,
    /* ... */
    "xxx"   INTEGER NOT NULL DEFAULT 0,
    "yyy"   INTEGER DEFAULT NULL
);
INSERT INTO zzz_amended SELECT * FROM zzz;
DROP TABLE IF EXISTS zzz_old;
ALTER TABLE zzz RENAME TO zzz_old;
ALTER TABLE zzz_amended RENAME TO zzz;
DROP TABLE IF EXISTS zzz_old;

A simpler Alternative could be to use the RoomDatabaseBuilder's createFromAsset or createFromFile method, but this might then need changes to the enitity to suit what the methods determine the schema to be.

---

Additional Re Comment :-

Well the schema used for the table zzz is as shown where xxx and yyy are of type INTEGER. And createFromAsset() method is being used. But for some reason outcome of createFromAsset() is that xxx and yyy are of type NUMERIC instead of type INTEGER as defined when creating the zzz table.

1.

Consider the following Database with the table created as you say it has been created. e.g.

Populated with as per :-

The database being available at so59224033.db

2

Also consider the following Simple App :-

Zzz

@Entity(tableName = "zzz")
 data class Zzz (

    @PrimaryKey(autoGenerate = true)
    var id: Long? = null,

    @ColumnInfo(name = "xxx")
    var xxx: Boolean = false ,

    @ColumnInfo(name = "yyy")
    var yyy: Boolean? = null
)

ZzzDao

@Dao
interface ZzzDao {

    @Query("SELECT * FROM zzz")
    fun getAllFromzzz() :List<Zzz>
}

AppDatabase

@Database(version = 1,entities = [Zzz::class])
abstract class AppDatabase :RoomDatabase() { 
    abstract fun getZzzDao() :ZzzDao
}

MainActivity

class MainActivity : AppCompatActivity() {

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        val appDatabase :AppDatabase = Room.databaseBuilder(this,AppDatabase::class.java,"mydb")
            .allowMainThreadQueries()
            .createFromAsset("so59224033.db")
            .build()

        val listofZzz = appDatabase.getZzzDao().getAllFromzzz()
        for (z  in listofZzz) {
            Log.d("ZzzINFO","ID = " + z.id + " xxx = " + z.xxx + " yyy = " + z.yyy)
        }
    }
}

3

The result not being any conflict (thus eliminating a bug with Room, at least if using the latest Room libraries, with the available code) :-

kapt 'androidx.room:room-compiler:2.2.2'
implementation 'androidx.room:room-runtime:2.2.2'

Rather the result is as expected :-

2019-12-08 08:55:23.196 19370-19370/? D/ZzzINFO: ID = 1 xxx = false yyy = null
2019-12-08 08:55:23.196 19370-19370/? D/ZzzINFO: ID = 2 xxx = false yyy = null
2019-12-08 08:55:23.196 19370-19370/? D/ZzzINFO: ID = 3 xxx = false yyy = null
2019-12-08 08:55:23.196 19370-19370/? D/ZzzINFO: ID = 4 xxx = false yyy = null
2019-12-08 08:55:23.196 19370-19370/? D/ZzzINFO: ID = 5 xxx = false yyy = null
2019-12-08 08:55:23.196 19370-19370/? D/ZzzINFO: ID = 6 xxx = false yyy = null
2019-12-08 08:55:23.196 19370-19370/? D/ZzzINFO: ID = 7 xxx = false yyy = null
2019-12-08 08:55:23.196 19370-19370/? D/ZzzINFO: ID = 8 xxx = false yyy = null
2019-12-08 08:55:23.196 19370-19370/? D/ZzzINFO: ID = 9 xxx = false yyy = null
2019-12-08 08:55:23.196 19370-19370/? D/ZzzINFO: ID = 10 xxx = false yyy = null
2019-12-08 08:55:23.196 19370-19370/? D/ZzzINFO: ID = 11 xxx = false yyy = null
2019-12-08 08:55:23.196 19370-19370/? D/ZzzINFO: ID = 12 xxx = false yyy = null
2019-12-08 08:55:23.196 19370-19370/? D/ZzzINFO: ID = 13 xxx = false yyy = null
2019-12-08 08:55:23.196 19370-19370/? D/ZzzINFO: ID = 14 xxx = false yyy = null
2019-12-08 08:55:23.196 19370-19370/? D/ZzzINFO: ID = 15 xxx = false yyy = null
2019-12-08 08:55:23.196 19370-19370/? D/ZzzINFO: ID = 16 xxx = false yyy = null
2019-12-08 08:55:23.196 19370-19370/? D/ZzzINFO: ID = 17 xxx = false yyy = null
2019-12-08 08:55:23.197 19370-19370/? D/ZzzINFO: ID = 18 xxx = false yyy = null
2019-12-08 08:55:23.197 19370-19370/? D/ZzzINFO: ID = 19 xxx = false yyy = null
2019-12-08 08:55:23.197 19370-19370/? D/ZzzINFO: ID = 20 xxx = false yyy = null

4

If the source database is changed e.g. to use :-

Then the result is :-

 Expected:
TableInfo{name='zzz', columns={yyy=Column{name='yyy', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, xxx=Column{name='xxx', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}}, foreignKeys=[], indices=[]}
 Found:
TableInfo{name='zzz', columns={yyy=Column{name='yyy', type='NUMERIC', affinity='1', notNull=false, primaryKeyPosition=0, defaultValue='NULL'}, xxx=Column{name='xxx', type='typethatwillbe_N_U_M_E_R_I_C', affinity='1', notNull=true, primaryKeyPosition=0, defaultValue='0'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}}, foreignKeys=[], indices=[]}

i.e. Your issue has been replicated by using a database in the assets folder that does not conform to the expectations.

i.e. the cause is that the database in the assets folder is incorrect.

I would suggest deleting the file from the assets folder, double checking that the columns are defined as INTEGER and then copying the file into the assets folder.