Migrating to FTS

If you have already shipped your app, and you want to add an FTS index to an existing table or add a new table with FTS, you will need to implement a migration, as we discussed in a previous chapter.

In this case, your migration will be a bit more complicated. Not only will you need to create your FTS table, but you will also need to define some triggers to keep your main table and the FTS shadow table in sync.

If you set up Gradle to export your schemas, you will have access to the required SQL. Partly, that will be in the createSql JSON property for your entity:

"createSql": "CREATE VIRTUAL TABLE IF NOT EXISTS `${TABLE_NAME}` USING FTS4(`prose` TEXT NOT NULL, content=`paragraphs`)"

And, partly, that will be in the contentSyncTriggers JSON property, which holds a list of SQL statements to be executed:

"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"
]

Your migration will need to include all of these, so your migrated database schema matches the database schema that new users will use.