android database sqlite android-sqlite android-room

Android Room Pre-packaged database has an invalid schema error

I tried room for the first time and it is not working as expected , and i can't figure out what the problem as i don't have any experience with room. Any help would be really appreciated. I am using prepackaged database , This is the Query i used to create database.

CREATE TABLE quotes_table (
id          INTEGER PRIMARY KEY AUTOINCREMENT,
quote       TEXT    UNIQUE,
category_id TEXT    NOT NULL
);

And this is how i am creating database in my app

Room.databaseBuilder(context.getApplicationContext(),
                        AppDatabase.class, "quotes_database.db")
                        .allowMainThreadQueries()
                        .createFromAsset("database/quotes_database.db")
                        .fallbackToDestructiveMigration()
                        .build();

and this is how i am creating my data class

@Entity(tableName = "quotes_table" ,
    indices = { @Index(value = {"quote"}, unique = true) } )
public class Quote {

@PrimaryKey(autoGenerate = true)
@ColumnInfo(name = "id")
private int id;

@ColumnInfo(name = "quote")
private String quote;

@NonNull
@ColumnInfo(name = "category_id")
private String categoryId;

But my app crashes when i launch the app and here is my logcat

2020-11-17 20:27:14.866 1588-1588/com.example.test E/AndroidRuntime: FATAL EXCEPTION: main
Process: com.example.test, PID: 1588
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.test/com.example.test.MainActivity}: java.lang.IllegalStateException: Pre-packaged database has an invalid schema: quotes_table(com.example.test.Quote).
 Expected:
TableInfo{name='quotes_table', columns={category_id=Column{name='category_id', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, quote=Column{name='quote', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[Index{name='index_quotes_table_quote', unique=true, columns=[quote]}]}
 Found:
TableInfo{name='quotes_table', columns={quote=Column{name='quote', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, category_id=Column{name='category_id', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}}, foreignKeys=[], indices=[Index{name='index_quotes_table_quote', unique=true, columns=[quote]}]}
    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2817)
    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2895)
    at android.app.ActivityThread.-wrap11(Unknown Source:0)
    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1616)
    at android.os.Handler.dispatchMessage(Handler.java:106)
    at android.os.Looper.loop(Looper.java:176)
    at android.app.ActivityThread.main(ActivityThread.java:6651)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:547)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:824)
 Caused by: java.lang.IllegalStateException: Pre-packaged database has an invalid schema: quotes_table(com.example.test.Quote).
 Expected:
TableInfo{name='quotes_table', columns={category_id=Column{name='category_id', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, quote=Column{name='quote', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[Index{name='index_quotes_table_quote', unique=true, columns=[quote]}]}
 Found:
TableInfo{name='quotes_table', columns={quote=Column{name='quote', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, category_id=Column{name='category_id', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}}, foreignKeys=[], indices=[Index{name='index_quotes_table_quote', unique=true, columns=[quote]}]}
    at androidx.room.RoomOpenHelper.onCreate(RoomOpenHelper.java:82)
    at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper$OpenHelper.onCreate(FrameworkSQLiteOpenHelper.java:118)
    at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:333)
    at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:238)
    at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper$OpenHelper.getWritableSupportDatabase(FrameworkSQLiteOpenHelper.java:92)
    at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper.getWritableDatabase(FrameworkSQLiteOpenHelper.java:53)
    at androidx.room.SQLiteCopyOpenHelper.getWritableDatabase(SQLiteCopyOpenHelper.java:90)
    at androidx.room.RoomDatabase.inTransaction(RoomDatabase.java:476)
    at androidx.room.RoomDatabase.assertNotSuspendingTransaction(RoomDatabase.java:281)
    at com.example.test.QuoteDao_Impl.getAllQuotes(QuoteDao_Impl.java:63)
2020-11-17 20:27:14.867 1588-1588/com.example.test E/AndroidRuntime:     at com.example.test.MainActivity.onCreate(MainActivity.java:44)
        at android.app.Activity.performCreate(Activity.java:7088)
        at android.app.Activity.performCreate(Activity.java:7079)
        at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1215)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2770)
            ... 9 more

I searched a lot on the internet but i couldn't anything that would help me. So any help here would be really appreciated. Thanks

Solution

The error message contains the "Expected" schema (what your Room Entity defines) and the "Found" schema - what your pre-packaged database has as its schema. Reordering the columns to match gives you:

 Expected:
TableInfo{name='quotes_table', columns={category_id=Column{name='category_id', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=true,  primaryKeyPosition=1, defaultValue='null'}, quote=Column{name='quote', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[Index{name='index_quotes_table_quote', unique=true, columns=[quote]}]}
 Found:
TableInfo{name='quotes_table', columns={category_id=Column{name='category_id', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, quote=Column{name='quote', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[Index{name='index_quotes_table_quote', unique=true, columns=[quote]}]}

These need to match exactly. However, your expected schema has a non-null id column:

id=Column{name='id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}

But your pre-packaged database has a nullable id column:

id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}

You need to change your pre-packaged database to ensure that the id column is NOT NULL.