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?

Mica

We finally got it and starting from version 2.1.0-alpha01 Room supports entities with a mapping FTS3 or FTS4 table. For more information and example usage, you can go to their documentation: @Fts3 @Fts4 – Dutton

@MichałBaran can i get some sample or example using fts3 or fts4 using room. – Item

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.

Uptotheminute

Sorry, I don't get this part "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." The FTS is for searching in one of the tables using searchable component. Also, could you elaborate a bit more with a simple example how it could be done? – Mica

@Eylen: Any table created by Room via @Entity cannot have FTS, at least through Room 1.0.0 (see the issues linked to from the question). Any table created by you, via a CREATE TABLE statement, can have FTS, but then Room won't know anything about it. So, if you need Room to be aware of FTS, this approach will not work. This sample app demonstrates using getOpenHelper(), in this case to execute some PRAGMA statements. You're welcome to define your own tables that way too, AFAIK. – Uptotheminute

so... I could create my table with @ Entity and create the virtual table and the triggers in RoomDatabase with getOpenHelper()? Or it won't work because the "main" table is not created manually? – Mica

@Eylen: "I could create..." -- presumably that would work, though I have not tried it. Others have gotten CREATE TRIGGER working, albeit with Room-defined tables. – Uptotheminute

Answer 2

This feature will be available in Room 2.1.0

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

Tetartohedral