The Room 1.x FTS Recipe

Room 1.x cannot create an FTS table for us. However, Room can work with an FTS table, at least for data retrieval, using @RawQuery.

As a result, we can get partial support for FTS from Room, if we are willing to do the rest ourselves, using the support database API, such as what we use with migrations.

Manually Create the Table

First, we will need to manually create our FTS virtual table.

At minimum, we need to do this when we create the database. We can use a RoomDatabase.Callback object for that, as part of setting up our RoomDatabase.Builder:

    RoomDatabase.Builder<BookDatabase> b=
      Room.databaseBuilder(ctxt.getApplicationContext(), BookDatabase.class,
        DB_NAME);

    b.addCallback(new Callback() {
      @Override
      public void onCreate(@NonNull SupportSQLiteDatabase db) {
        super.onCreate(db);

        db.execSQL("CREATE VIRTUAL TABLE booksearch USING fts4(sequence, prose)");
      }
    });

    BookDatabase books=b.build();

RoomDatabase.Callback was covered in the chapter on the support database API.

Here, as part of setting up an instance of a BookDatabase, we request to get control when the database is created and add our own booksearch virtual table.

If you are adding the FTS table to an already-existing app, you will also need to create your FTS virtual table in the appropriate Migration object(s).

RawQuery the Table

To get data out of the virtual table, we can use a @RawQuery-annotated method on some @Dao class that references the table that we created manually. While @RawQuery can work with tables defined via Room entities, that is not a requirement. @RawQuery can work with any table, so long as Room can figure out how to map the columns that you request to the POJO that you want to return.

So, given some sort of BookSearchResult POJO that matches the booksearch table structure used above, we could have:

  @RawQuery
  protected abstract List<BookSearchResult> _searchSynchronous(SupportSQLiteQuery query);

Things get a bit tricky if we want a reactive response (e.g., Observable) or if we want to use paging, as we will see later in this chapter.

To put data into the virtual table, the official solution is to use a SupportSQLiteDatabase. You get one by calling getOpenHelper().getWritableDatabase() on your RoomDatabase. Then, just as we used one of those for a CREATE VIRTUAL TABLE statement (see above), we can use one for INSERT/UPDATE/DELETE statements or the corresponding insert(), update(), or delete() methods.

Unofficially, @RawQuery works for these as well:

  @RawQuery
  protected abstract long _insert(SupportSQLiteQuery queryish);