7

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?

Phantômaxx
  • 37,901
  • 21
  • 84
  • 115
Eylen
  • 2,617
  • 4
  • 27
  • 42
  • 2
    We finally got it and starting from version [2.1.0-alpha01](https://developer.android.com/jetpack/docs/release-notes#october_8_2018) Room supports entities with a mapping FTS3 or FTS4 table. For more information and example usage, you can go to their documentation: [@Fts3](https://developer.android.com/reference/androidx/room/Fts3) [@Fts4](https://developer.android.com/reference/androidx/room/Fts4) – Michał Pawlik Oct 10 '18 at 08:47
  • @MichałBaran can i get some sample or example using fts3 or fts4 using room. – P Vartak Oct 23 '18 at 05:06

2 Answers2

5

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.

CommonsWare
  • 986,068
  • 189
  • 2,389
  • 2,491
  • 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? – Eylen Nov 29 '17 at 13:54
  • 1
    @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](https://github.com/commonsguy/cw-androidarch/tree/v0.6/CityPop/RoomPragma) demonstrates using `getOpenHelper()`, in this case to execute some `PRAGMA` statements. You're welcome to define your own tables that way too, AFAIK. – CommonsWare Nov 29 '17 at 13:59
  • 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? – Eylen Nov 29 '17 at 14:48
  • @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. – CommonsWare Nov 29 '17 at 14:50
2

This feature will be available in Room 2.1.0

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

davidhodges86
  • 301
  • 2
  • 5