Room 2.1 SQLite FTS: inserting new single FTS object in database

Question

I have been trying to use new Android's Room 2.1 feature of FTS table to enable search functionality.

Entity:

 @Entity
    class ChatMessageEntity(
            @PrimaryKey
            var messageId: String,
            var messageUser: String,
            var messageText: String,
            )

FTS entity:

@Fts4(contentEntity = ChatMessageEntity::class)
@Entity
class ChatMessageFts(
        var messageText: String //only want messageText searchable
)

Database:

@Database(entities = [ChatMessageEntity::class, ChatMessageFts::class], version = 1)
abstract class ChatMessageDatabase : RoomDatabase() {

    abstract fun chatMessageDatabase(): ChatMessageDao

    companion object {
        fun create(context: Context) = Room.databaseBuilder(context, ChatMessageDatabase::class.java, DB_NAME).build()
    }
}

DAO:

@Query("INSERT INTO ChatMessageFts(ChatMessageFts) VALUES('rebuild')") //works
    suspend fun ftsRebuild()

@Query("INSERT INTO ChatMessageFts(docid, messageText) VALUES(:id, :text)") //compiles but doesn't work
    suspend fun saveFtsMessage(id: Long, text: String)

I wish to add each row in FTS when I also add the row in the original table (ChatMessageEntity - not shown). When rebuilding the FTS table from scratch, it works perfectly (ftsRebuild() above).

However, I wish to add each input individually. I have tried many things (for example saveFtsMessage() shown above) - adding without docId, trying with rowId, ... I even tried adding a new object ChatMessageEntity into ChatMessageFts as input but I didn't manage to compile it due to errors.

Any suggestion on how I can add individual input in FTS table is most welcome!

You should not need either of those `INSERT INTO ChatMessageFts` statements. Inserting into `ChatMessage` will populate `ChatMessageFts` via triggers. — CommonsWare, Feb 17 '20 at 12:46
@CommonsWare Thank you sooo much. Don't know how I didn't realise this before. Do you think I should close this question or mark your answer as accepted (if so please add it below)? — Androidz, Feb 17 '20 at 12:53

score 3 · Accepted Answer · answered Feb 17 '20 at 12:58

@Fts4(contentEntity = ChatMessageEntity::class)

The magic of the contentEntity property of the annotation is that Room sets up triggers to keep your FTS table in sync with the corresponding entity table:

"contentSyncTriggers": [
  "CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_paragraphsFts_BEFORE_UPDATE BEFORE UPDATE ON `paragraphs` BEGIN DELETE FROM `paragraphsFts` WHERE `docid`=OLD.`rowid`; END",
  "CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_paragraphsFts_BEFORE_DELETE BEFORE DELETE ON `paragraphs` BEGIN DELETE FROM `paragraphsFts` WHERE `docid`=OLD.`rowid`; END",
  "CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_paragraphsFts_AFTER_UPDATE AFTER UPDATE ON `paragraphs` BEGIN INSERT INTO `paragraphsFts`(`docid`, `prose`) VALUES (NEW.`rowid`, NEW.`prose`); END",
  "CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_paragraphsFts_AFTER_INSERT AFTER INSERT ON `paragraphs` BEGIN INSERT INTO `paragraphsFts`(`docid`, `prose`) VALUES (NEW.`rowid`, NEW.`prose`); END"
]

(from this sample app from this book)

So long as you are inserting, updating, and deleting your entities, the FTS table will be maintained automatically. You just use the FTS table for querying purposes.

If you skip the contentEntity annotation — and assuming that works — then you might need to maintain your FTS table independently.

Room 2.1 SQLite FTS: inserting new single FTS object in database

1 Answers1