Full Text Search in Room Tutorial: Getting Started

Fixing the Return Type

Navigate back to the LaunchDao.kt file in the db package and find the search method. Next, replace the search method along with the complete SQL query defined in the @Query annotation with one as below:

@Query("""
  SELECT *
  FROM launches
  JOIN launches_fts ON launches.name = launches_fts.name
  WHERE launches_fts MATCH :query
""")
suspend fun search(query: String): List<Launch>

Here, notice that you have updated the @Query annotation as well as updated the return type of search method to List<Launch>.

Much better! Here the launches table joins with the launches_fts table on the name of a launch. This way, the query matches against the launches_fts table but returns the columns of the launches table.

With this change, the app now uses FTS instead of pattern matching. Build and run the app and try searching for something.

You should see … no search results?

To understand why, you must learn how the FTS index is maintained. Head over to the next section to learn about it.

Maintaining the Full Text Search Index

In a newly created database, the FTS index is empty. It must be updated every time its content table is modified, but SQLite doesn’t perform this task automatically. Instead, it provides a feature to automate such operations: Triggers.

You don’t need to write these triggers by hand, because Room Jetpack Library generates them automatically. As a result, you almost never need to maintain the FTS index manually.

However, Find My Launch ships with a database asset to pre-populate the application database when it is first created. The pre-population process doesn’t trigger an update to the FTS index. Consequently, the index remains empty and returns zero results for every query.

To fix this, you must trigger an FTS rebuild after the application database creation completes.

Triggering FTS Rebuilds

As mentioned earlier, FTS tables have a hidden column with the same name as the table itself. This column supports some special commands, one of which is rebuild. Writing this command to the hidden column triggers a rebuild of the full-text index.

Navigate to the DatabaseModule.kt file in the db package. In the launchesDB method, you need to add a callback to the database builder to rebuild the FTS index, right after calling createFromAsset. Replace the existing launchesDB method with the below implementation:

@Provides
@Singleton
fun launchesDB(context: Context): LaunchesDatabase {
  return Room.databaseBuilder(context, LaunchesDatabase::class.java, "findmylaunch.db")
      // 1
      .createFromAsset("launches.db")
      // 2
      .addCallback(object : RoomDatabase.Callback() {
        override fun onCreate(db: SupportSQLiteDatabase) {
          super.onCreate(db)
          // 3
          db.execSQL("INSERT INTO launches_fts(launches_fts) VALUES ('rebuild')")
        }
      })
      .fallbackToDestructiveMigration()
      .build()
}

Here in this code snippet, you are:

1. Creating/Pre-populating the database using the launches.db file from assets
2. Adding the callback that is triggered after the database is pre-populated
3. Executing the SQL query that will use the rebuild trigger

Make sure to add missing imports using the IDE.

Build and run the app. Search should be fully functional again!

Once you have verified the search functionality, try searching for the following query: -9. You will notice that the application crashes with the following error in the logcat:

android.database.sqlite.SQLiteException: malformed MATCH expression: [*-9*] (code 1 SQLITE_ERROR)

To fix this error, you need to properly escape the search queries received from the UI.

Escaping FTS Queries

Full Text Search in SQLite supports a myriad of features, one of which is Set Operations.

Set Operations allow you to manipulate the results of an FTS query in various ways using logical operators. One such operator is the NOT operator, which returns the complement result set of the given query (i.e., all results minus the results of the given query). SQLite treats every minus sign (-) in the search-query as the NOT operator, and forbids any queries that contain - as a prefix. Therefore, searching for a seemingly harmless query like -9 crashes the app as it contains - as a prefix.

To solve this problem, you need to treat the user’s search queries as verbatim.

Navigate to the SearchViewModel.kt file in the search package, and add the following method to it:

private fun sanitizeSearchQuery(query: Editable?): String {
  return "\"$query\""
}

The sanitizeSearchQuery method surrounds the search query with quotes, which ensures that SQLite treats this string literally.

Next, modify the search method to sanitize the search-query before using it to fetch search results:

fun search(query: Editable?) {
  viewModelScope.launch {
    if (query.isNullOrBlank()) {
      launchDao.all().let {
        _searchResults.postValue(it)
      }
    } else {
      val sanitizedQuery = sanitizeSearchQuery(query)
      launchDao.search(sanitizedQuery).let {
        _searchResults.postValue(it)
      }
    }
  }
}

Build and run the app. You should now be able to search for queries that contain - as a prefix.

Unfortunately, this solution isn’t complete yet because a double-quote symbol in the search query still crashes the app. Try searching for "-9. The app should crash again with the following error:

android.database.sqlite.SQLiteException: malformed MATCH expression: [""-9"] (code 1 SQLITE_ERROR)

To fix this, you must correctly escape double-quotes too.

Escaping double-quotes

The method to escape double-quotes in SQLite is to replace them with… double double-quotes! :]

In the SearchViewModel.kt file, replace the implementation of the sanitizeSearchQuery method with this:

private fun sanitizeSearchQuery(query: Editable?): String {
  if (query == null) {
    return "";
  }
  val queryWithEscapedQuotes = query.replace(Regex.fromLiteral("\""), "\"\"")
  return "*\"$queryWithEscapedQuotes\"*"
}

This code uses a regular expression to replace all occurrences of " in the query with "". It then wraps the query with double quotes, adds asterisks on either ends, and finally returns the result.

Build and run the app. It should no longer crash when you search for queries such as "-9.

This change completes the basic implementation of the search functionality in the app. However, you can still do better by ordering the results according to relevance. Head over to the next section to learn how to do so.

Ranking Search Results

The current search implementation doesn’t order results based on their relevance. This means that less-relevant results could appear in the list before more-relevant ones.

To fix this issue, you must assign a rank to each result and order the results by their ranks. FTS4 has no built-in feature to handle this, but it provides the handy matchinfo function to build one.

The matchinfo function returns a BLOB containing metadata about each match. You can parse this metadata to calculate a relevance score, which can be used to assign a rank to each match. This function supports a lot of configuration options, each of which affects how its value must be parsed.

In the interest of keeping things simple, this tutorial uses only the default variant of this function. The default variant takes only the hidden FTS column name as its parameter.