Migrating to Room: How to do Full Text Search?

Question

I was looking how to migrate an existing application to Room, from plain Sqlite, and I haven't been able to find how could I migrate a part that uses FTS.

Right now, I have a virtual table that's filled by a trigger every time a row is inserted or updated:

private static final String CREATE_VIRTUAL_TABLE = "CREATE VIRTUAL TABLE " + FTS_VIRTUAL_TABLE +
            " using fts4 (content='" + TABLE_NOTIFICATION + "', " + COLUMN_TITLE + ")";

private static void createVirtualTriggers(SQLiteDatabase database){
      database.execSQL("CREATE TRIGGER virtual_bu BEFORE UPDATE ON " + TABLE_NOTIFICATION + " BEGIN\n" +  "  DELETE FROM " + FTS_VIRTUAL_TABLE + " WHERE docid=old.rowid;\n" +    "END;");
      database.execSQL("CREATE TRIGGER virtual_bd BEFORE DELETE ON " + TABLE_NOTIFICATION + " BEGIN\n" +  "  DELETE FROM " + FTS_VIRTUAL_TABLE + " WHERE docid=old.rowid;\n" +    "END;");
      database.execSQL("CREATE TRIGGER virtual_au AFTER UPDATE ON " + TABLE_NOTIFICATION + " BEGIN\n" +  "  INSERT INTO " + FTS_VIRTUAL_TABLE + "(docid, " + COLUMN_TITLE + ") VALUES(new.rowid, new." + COLUMN_TITLE +");\n" + "END;");
      database.execSQL("CREATE TRIGGER virtual_ai AFTER INSERT ON " + TABLE_NOTIFICATION + " BEGIN\n" +  "  INSERT INTO " + FTS_VIRTUAL_TABLE + "(docid, " + COLUMN_TITLE + ") VALUES(new.rowid, new." + COLUMN_TITLE +");\n" +                   "END;");
  }

Is there any way to achieve this same functionality with Room?

Answer 1

Not really. See this issue and this issue where this request is being tracked.

You can always work with the database directly for FTS scenarios, such as creating the table and triggers in your code snippet in the question, by calling getOpenHelper() on the RoomDatabase and using it similar to SQLiteOpenHelper. Room will ignore new tables and stuff that you create behind its back. So, if your FTS work happens to be fairly separate from the rest of your database work, you might be able to go that route.

Answer 2

This feature will be available in Room 2.1.0

As per https://issuetracker.google.com/issues/62356416