Implementing Search with Room

Question

Recently I have been messing around with Android Architecture Components (more specifically Room) but I have hit a bit of a roadblock.

I have successfully built a Room database that stores a list of departments and their personnel. Previously this data was being pulled from the server, yet not stored locally. The search functionality was also handled remotely so now I am looking to handle the search functionality locally as well, but my knowledge of SQL is a bit lacking.

Looking at the SQL code on the server, the search statement uses a bunch of REGEXP functions to search both databases based on the query provided. Which doesn't seem like the best way to go about handling search, but it worked fairly well and gave a quick response. So I tried to mimic this locally, but found out quickly that REGEXP is not supported on Android (without the use of the NDK).

As for the LIKE and GLOB operators, they seem very limited in what they can do. For example, I don't see a way that I can match against multiple keywords at once; whereas with REGEXP I can just replace whitespace with an or (|) operator to achieve this functionality.

So, looking for an alternative I came across full-text search (FTS); which is the method demonstrated in the Android documentation on implementing search. Though it seems like FTS is meant for searching full documents, not simple data as with my use-case.

In any case, FTS isn't supported by Room.

So, naturally, I tried to force Room to create an FTS virtual table instead of a standard table by creating an implementation of the SupportSQLiteOpenHelper.Factory that does just that. This implementation is almost a direct copy of the default FrameworkSQLiteOpenHelperFactory, and the related framework classes. The necessary bit of code is in the SupportSQLiteDatabase, where I override execSQL to inject the virtual table code where necessary.

class FTSSQLiteDatabase(
    private val delegate: SQLiteDatabase,
    private val ftsOverrides: Array<out String>
) : SupportSQLiteDatabase {

    // Omitted code...

    override fun execSQL(sql: String) {
        delegate.execSQL(injectVirtualTable(sql))
    }

    override fun execSQL(sql: String, bindArgs: Array<out Any>) {
        delegate.execSQL(injectVirtualTable(sql), bindArgs)
    }

    private fun injectVirtualTable(sql: String): String {
        if (!shouldOverride(sql)) return sql

        var newSql = sql

        val tableIndex = sql.indexOf("TABLE")
        if (tableIndex != -1) {
            sql = sql.substring(0..(tableIndex - 1)) + "VIRTUAL " + sql.substring(tableIndex)

            val argumentIndex = sql.indexOf('(')
            if (argumentIndex != -1) {
                sql = sql.substring(0..(argumentIndex - 1) + "USING fts4" + sql.substring(argumentIndex)
            }
        }

        return newSql
    }

    private fun shouldOverride(sql: String): Boolean {
        if (!sql.startsWith("CREATE TABLE")) return false

        val split = sql.split('`')
        if (split.size >= 2) {
            val tableName = split[1]
            return ftsOverrides.contains(tableName)
        } else {
            return false
        }
    }

}

It's a little messy, but it works! Well, it creates the virtual table…

But then I get the following SQLiteException:

04-04 10:54:12.146 20289-20386/com.example.app E/SQLiteLog: (1) cannot create triggers on virtual tables
04-04 10:54:12.148 20289-20386/com.example.app E/ROOM: Cannot run invalidation tracker. Is the db closed?
    android.database.sqlite.SQLiteException: cannot create triggers on virtual tables (code 1): , while compiling: CREATE TEMP TRIGGER IF NOT EXISTS `room_table_modification_trigger_departments_UPDATE` AFTER UPDATE ON `departments` BEGIN INSERT OR REPLACE INTO room_table_modification_log VALUES(null, 0); END
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:890)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:501)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
        at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
        at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1752)
        at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1682)
        at com.example.app.data.FTSSQLiteDatabase.execSQL(FTSSQLiteDatabase.kt:164)
        at android.arch.persistence.room.InvalidationTracker.startTrackingTable(InvalidationTracker.java:204)
        at android.arch.persistence.room.InvalidationTracker.access$300(InvalidationTracker.java:62)
        at android.arch.persistence.room.InvalidationTracker$1.run(InvalidationTracker.java:306)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1162)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:636)
        at java.lang.Thread.run(Thread.java:764)

Room creates the table, but then tries to create a trigger on the virtual table, which is apparently not allowed. If I try to override the the triggers (i.e. just prevent them from executing) I'm guessing that will break a lot of the functionality of Room. Which, I am assuming, is the reason Room doesn't support FTS in the first place.

TLDR

So if Room doesn't support FTS (and I cannot force it to), and REGEXP is not supported (unless I use the NDK); is there another way for me to implement search while using Room? Is FTS even the right way to go (it seems like overkill), or is there some other method that is more suitable for my use-case?

Answer 1

I can confirm that this works. It is aggravating, but it works.

First, you will need to create the table. For initial database creation, you can use a RoomDatabase.Callback for this:

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();

(also: remember this table if you need to make changes to it in migrations!)

You can then set up a @Dao for this. All of your actual database-manipulating DAO methods will need to be annotated with @RawQuery, as everything else expects to work with entities. And, since @RawQuery methods only accept a SupportSQLiteQuery parameter, you'll probably want to wrap those in other methods that create the SupportSQLiteQuery object.

So, for example, to insert data into the virtual table, you can have:

  @RawQuery
  protected abstract long insert(SupportSQLiteQuery queryish);

  void insert(ParagraphEntity entity) {
    insert(new SimpleSQLiteQuery("INSERT INTO booksearch (sequence, prose) VALUES (?, ?)",
      new Object[] {entity.sequence, entity.prose}));
  }

and to do a search, you can do:

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

  List<BookSearchResult> search(String expr) {
    return _search(query(expr));
  }

  private SimpleSQLiteQuery query(String expr) {
    return new SimpleSQLiteQuery("SELECT sequence, snippet(booksearch) AS snippet FROM booksearch WHERE prose MATCH ? ORDER BY sequence ASC",
      new Object[] {expr});
  }

In both cases, my @RawQuery methods are protected and use a leading _ to emphasize that "these would be private, but you cannot have private abstract methods, so please don't use them, m'kay?".

Note that your FTS search expressions need to follow the SQLite FTS documentation.