Room database Pre-packaged database has an invalid schema

Question

I have a problem with pre-packages room database. I have a database found on external storage. I have copied this database to be placed on the database directory. After copying the database, I should use Room database, but unfortunately, java.lang.IllegalStateException Pre-packaged database has an invalid schema thrown.

Expected:

 TableInfo{name='glyphs', columns={max_x=Column{name='max_x', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, glyph_id=Column{name='glyph_id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, page_number=Column{name='page_number', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, max_y=Column{name='max_y', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, line_number=Column{name='line_number', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, sura_number=Column{name='sura_number', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, ayah_number=Column{name='ayah_number', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, min_x=Column{name='min_x', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, position=Column{name='position', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, min_y=Column{name='min_y', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}

Found:

TableInfo{name='glyphs', columns={max_x=Column{name='max_x', type='int', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, glyph_id=Column{name='glyph_id', type='int', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, page_number=Column{name='page_number', type='int', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, max_y=Column{name='max_y', type='int', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, line_number=Column{name='line_number', type='int', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, sura_number=Column{name='sura_number', type='int', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, ayah_number=Column{name='ayah_number', type='int', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, min_x=Column{name='min_x', type='int', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, position=Column{name='position', type='int', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, min_y=Column{name='min_y', type='int', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[Index{name='sura_ayah_idx', unique=false, columns=[sura_number, ayah_number]}, Index{name='page_idx', unique=false, columns=[page_number]}]}

Does any help please?

Answer 1

Remember to reinstall the application after correcting schema

Answer 2

I find out the problem. Indices of the scheme were missing. I have added indices to @Entity class and it works fine.

Index in the database is used for speeding up the queries, check official documentation for more details

Example of indices in Room database

@Entity(indices = arrayOf(Index(value = ["last_name", "address"])))
data class User(
    @PrimaryKey val id: Int,
    val firstName: String?,
    val address: String?,
    @ColumnInfo(name = "last_name") val lastName: String?,
    @Ignore val picture: Bitmap?
)

Answer 3

Looks like your export doesn't have column types supported by Room. By the looks of the schema you shared, Room is expecting INTEGER instead of 'int'. You should try and stick with the datatypes expected by SQLite as mentioned here: https://www.sqlite.org/datatype3.html