Implementing a search with a room

Question

Implementing a search with a room

I recently fiddled with Android Architecture Components (more specifically Room ), but I hit the checkpoint a bit.

I have successfully created the Room database, which stores a list of departments and their staff. Previously, this data was retrieved from the server, but not stored locally. The search function was also processed remotely, so now I am looking for local work with search functions, but my knowledge of SQL is a bit absent.

If you look at the SQL code on the server, the search operator uses a group of functions REGEXPto search both databases based on the provided query. Which doesn't seem like the best way to handle a search, but it worked pretty well and responded quickly. So I tried to simulate this locally, but quickly found that it was REGEXPnot supported on Android (without using NDK).

As for the operators LIKEand GLOB, they seem very limited in what they can do. For example, I don’t see the way I can match multiple keywords at once; whereas REGEXPI can simply replace the space with the operator or( |) to achieve this functionality.

So, looking for an alternative, I came across a full-text search (FTS); which is the method demonstrated in the Android documentation for implementing search . Although it seems that the FTS is designed to search for complete documents, not simple data, as with my use case.

In any case, FTS is not supported by Room .

So naturally, I tried to get Room to create a virtual FTS table instead of the standard table, creating an implementation SupportSQLiteOpenHelper.Factorydoes just that. This implementation is almost a direct copy of the standard FrameworkSQLiteOpenHelperFactoryand related infrastructure classes. The required bit of code is in SupportSQLiteDatabasewhere I redefine execSQLto insert virtual table code if 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
        }
    }

}

, ! , ...

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)