Room gives error: pre-packaged database has an invalid schema for FTS Table

Question

I'm developing a dictionary app, which provides search using FTS table. The definitions come from a pre-packaged database, which has following schema: (output of .schema from sqlite3 program on Linux)

sqlite> .open words.db
sqlite> .schema
CREATE TABLE entries
    (id INTEGER PRIMARY KEY NOT NULL,
    word TEXT NOT NULL COLLATE NOCASE,
    wordtype TEXT NOT NULL COLLATE NOCASE,
    definition TEXT NOT NULL COLLATE NOCASE);
CREATE INDEX words_index ON entries(id, word);
CREATE VIRTUAL TABLE entriesFts USING FTS4(content='entries', word)
/* entriesFts(word) */;
CREATE TABLE IF NOT EXISTS 'entriesFts_segments'(blockid INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE IF NOT EXISTS 'entriesFts_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx));
CREATE TABLE IF NOT EXISTS 'entriesFts_docsize'(docid INTEGER PRIMARY KEY, size BLOB);
CREATE TABLE IF NOT EXISTS 'entriesFts_stat'(id INTEGER PRIMARY KEY, value BLOB);

And my Entities definations look like these:

@Entity(tableName="entries",
    indices = [Index(name = "words_index", value = ["id", "word"])])
data class Word (
    @PrimaryKey val id :Int,
    @ColumnInfo val word :String,
    @ColumnInfo(name = "wordtype") val wordType :String,
    @ColumnInfo val definition :String
)

/* Used when displaying list of words */
@Fts4(contentEntity = Word::class)
@Entity(tableName = "entriesFts")
data class WordMinimal (
    @PrimaryKey @ColumnInfo(name = "rowid") val id :Int,
    @ColumnInfo val word :String
)

It looks to me like the schemas match perfectly, but Room has a different opinion :/
It throws this error in logcat:

java.lang.IllegalStateException: Pre-packaged database has an invalid schema: entriesFts(rawderm.dictionary.en.db.WordMinimal).
     Expected:
    FtsTableInfo{name='entriesFts', columns=[word], options=[content=`entries`]}
     Found:
    FtsTableInfo{name='entriesFts', columns=[word], options=[content='entries']}

Answer 1

Room is quite paricular about the characters used for enclosing names and basically only accepts grave accents ` (ASCII code 96) for enclosing column names.

The safest way to code columns is to use grave accents as this is what Room does. Saying that the easisest way of getting accurate schemas is to let Room generate the schema code on your behalf.

You can do this by creating the entity(ies) and also the Database class (i.e. class that extends Room Database) then compiling (Ctrl + F9) and afterwards to look in the generated code for the code that is the Database class name appended with _Impl. In that class there will be a createAllTables method. This contains the SQL for the tables that Room expects, which is according to the Entity.

As such Room conveniently generates :-

    _db.execSQL("CREATE TABLE IF NOT EXISTS `entries` (`id` INTEGER NOT NULL, `word` TEXT NOT NULL, `wordtype` TEXT NOT NULL, `definition` TEXT NOT NULL, PRIMARY KEY(`id`))");
    _db.execSQL("CREATE INDEX IF NOT EXISTS `words_index` ON `entries` (`id`, `word`)");
    _db.execSQL("CREATE VIRTUAL TABLE IF NOT EXISTS `entriesFts` USING FTS4(`word` TEXT NOT NULL, content=`entries`)");
    _db.execSQL("CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_entriesFts_BEFORE_UPDATE BEFORE UPDATE ON `entries` BEGIN DELETE FROM `entriesFts` WHERE `docid`=OLD.`rowid`; END");
    _db.execSQL("CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_entriesFts_BEFORE_DELETE BEFORE DELETE ON `entries` BEGIN DELETE FROM `entriesFts` WHERE `docid`=OLD.`rowid`; END");
    _db.execSQL("CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_entriesFts_AFTER_UPDATE AFTER UPDATE ON `entries` BEGIN INSERT INTO `entriesFts`(`docid`, `word`) VALUES (NEW.`rowid`, NEW.`word`); END");
    _db.execSQL("CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_entriesFts_AFTER_INSERT AFTER INSERT ON `entries` BEGIN INSERT INTO `entriesFts`(`docid`, `word`) VALUES (NEW.`rowid`, NEW.`word`); END");

• Notice how it additionally creates Triggers to maintain the FTS. And that these do not need to be specified as or in Entities (i.e. you can have SQLite components that don't exist as entities and Room doesn't detect a mismatch).