Enabling FTS4 on an Android + Room project

android_room_fts4.md

Enabling FTS4 on an Android project with Room

You can do a SQL text query by using the LIKE operator. The issue is that using it requires a lot of computation, as a complete string query is done. Also if you want to have more search options (more fields), your query will grow a lot in complexity. To solve this issue, there's a concept of virtual tables for full text search (FTS).

We will build our solution using Room (already set in the project). We're using version 2.2.0-rc01 for that.

Step 1 - Create new Virtual Table

With Room, the only thing we need is to create the new class with @FTS4 notation. By specifying contentEntity to be the Route class, it means that it will reuse the values from the Route table instead of populating this one with copies. The fields in question should match the ones from the Route table. In this example we only need the title.

@Fts4(contentEntity = Route::class)
@Entity(tableName = "routesFts")
class RoutesFts(val id: String, val title: String)

Step 2 - Create table DAO

We can now specify how we are actually getting the data. In this case we have a query that searches for all titles in the routesFts table that match a given string. This constraint is then applied to the Route table to get the routes that matches that constraint. Note that to use Full Text Search, we use the MATCH operator, instead of the LIKE.

@Dao
abstract class RouteFtsDao {
    @Query("SELECT * FROM route JOIN routesFts ON route.id == routesFts.id WHERE routesFts.title MATCH :text GROUP BY route.id" )
    abstract fun routesWithText(text: String): List<Route>
}

Step 3 - Update the database class

Now we need to update our Database class with the new table and Dao. Note the addition of the RoutesFts::class in the entities section, the routesFtsDao() method and an increase of database version.

@Database(
        entities = [Routes::class, Route::class, FavoriteRoute::class, UncheckedWaypoint::class, RoutesFts::class],
        version = 6,
        exportSchema = false
)
@TypeConverters(Converters::class)
abstract class MarvelroadDb : RoomDatabase() {
    abstract fun routeDao(): RouteDao
    abstract fun favoriteRoutesDao(): FavoriteRoutesDao
    abstract fun waypointsDao(): WaypointsDao
    abstract fun routesFtsDao(): RouteFtsDao
}

Step 4 - Search!

Now you should be able to just call the routesWithText(text) with the text you want to search. Note that using this approach will make full words search. If you want to have partial results, you can search by *{text}* instead of justn {text}.

routesWithText("*{myTextToSearch}*") 

The previous version of my room database was very low. Now if I upgrade and want to support Fts4, I would like to ask: Do you have any good suggestions?

@TheMelody
I haven't been in touch with this topic for quite some time, but supporting FTS was done by the means of adding an additional table which becomes the one to support FTS.
In the example I gave, I already had a table for "Route". In order to support FTS, I created a new table with the proper annotations, and in the queries, I linked the new table route field with the old table one.

In your case I assume to be the same. You already have your table, and you would need to create this new table and link it with the older one.

But I would also recommend to check documentation, as this can be outdated already.

OK