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?

Mousetail

Perhaps the problem is how you are going about creating the table. You don't want Room thinking that it has much to do with the table. My interpretation is that you still have an @Entity and are hacking the Room-created CREATE TABLE statement. We just received the go-ahead to do a workaround (like, in the past 20 minutes or so), but I wouldn't go about it this way. Instead, I'd use RoomDatabase.Callback and migrations, manually creating and modifying the table on the SupportSQLiteDatabase. – Ezar

"is there some other method that is more suitable for my use-case?" -- it's not completely clear what your use case is. You have keywords somewhere. Is this list in a single column in the table? If so, perhaps the answer instead is that there is an M:N relationship between department and keyword. – Ezar

@Ezar Yes, sorry for not expanding more on my use-case. The department database, for example, has six columns (code, title, prefix, location, phone and fax). The title, prefix and location columns can have multiple words, and I want to be able to match any of the keywords provided with any of the words in all columns (though I am not too worried about the phone and fax fields). So yes, and M:N relationship. – Mousetail

Ah, OK, I thought perhaps "keywords" meant like tags or something. FTS isn't a bad choice for that, IMHO. – Ezar

Oh, no I say "keywords" as in the query provided by the user (whatever words they type into the search field); which may well be incorrect terminology. And the workaround you mentioned looks very promising! Working on implementing it now. – Mousetail

Room doesn't support regex? What a shame ! – Gyatt

Answer 1

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 and @Fts4

Fulmer

Answer 2

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.

Ezar

This works great! Though FTS doesn't seem to be as robust as I had hoped it would be; but I can manage. Also, it's a bit strange that your insert() function works, considering the documentation explicitly states "RawQuery methods can only be used for read queries. For write queries, use RoomDatabase.getOpenHelper().getWritableDatabase()." – Mousetail

@Bryan: OK, I just filed an issue for documentation clarity around the @RawQuery write scenario. I'm not sure what you feel is lacking in the SQLite FTS support, so I cannot provide much advice there. – Ezar

@Ezar My RawQuery is returning an empty list. db.execSQL("CREATE VIRTUAL TABLE NameSearch USING fts4(Name)"); For creating the virtual database. SimpleSQLiteQuery simpleQuery = new SimpleSQLiteQuery("INSERT INTO NameSearch (Name) VALUES ?", new Object[]{entity.getName()}); For populating the database. SimpleSQLiteQuery query = new SimpleSQLiteQuery("SELECT * FROM NameSearch WHERE Name MATCH ? ORDER BY Name ASC", new Object[]{name}); For querying the database. – Boles

@AyokunlePaul: Perhaps there are issues with the value of name. Here is a complete sample project demonstrating the use of FTS with Room. It's a little odd, in that I have to package the data to be searched so that the sample is self-contained, but it may be of help as you try to determine what is going on with your own code. – Ezar

Thanks. It's working now. The problem I was facing was quite tricky. – Boles

This solution must be outdated because your statement of using raw queries doesn't jive with the documentation: developer.android.com/training/data-storage/room/defining-data – Holcman

@AndroidDev: I am uncertain of your specific concern, as that page does not discuss @RawQuery. If you are in position to use Room 2.1.0 or higher, definitely use the native stuff described in the other answer. However, as of June 2019, Room 2.1.0 is not final, and not everybody is in position to use pre-release libraries. – Ezar