The following is the first few sections of a chapter from Android's Architecture Components, plus headings for the remaining major sections, to give you an idea about the content of the chapter.


Room and Full-Text Searching

SQLite supports FTS virtual tables for full-text searching of content.

Room does not.

However, there are ways to get FTS support in a Room-managed database, but it requires you to do more of the work yourself. In this chapter, we will explore how to make this work, while also traveling in time. Or perhaps just reading about somebody who travels in time.

What Is FTS?

Standard SQL databases are great for ordinary queries. In particular, when it comes to text, SQL databases are great for finding rows where a certain column value matches a particular string. They are usually pretty good about finding when a column value matches a particular string prefix, if there is an index on that column. Things start to break down when you want to search for an occurrence of a string in a column — “find all rows where the column prose contains the word vague” – as this usually requires a “table scan” (i.e., iteratively examining each row to see if this matches). And getting more complex than that is often impossible, or at least rather difficult.

SQLite, in its stock form, inherits all those capabilities and limitations. However, SQLite also offers full-text indexing, where we can search our database much like how we use a search engine (e.g., “find all rows where this column has both foo and bar in it somewhere”). While a full-text index takes up additional disk space, the speed of the full-text searching is quite impressive.

There are two full-text indexing options available in SQLite: FTS3 and FTS4. FTS4 can be much faster on certain queries, though overall the speed of the two implementations should be similar. FTS4 has two key limitations:

  1. It may take a bit more disk space for its indexes.
  2. It was added to SQLite 3.7.4, which was only introduced into standard Android in API Level 11.

This chapter does not cover all of the details of using FTS with SQLite. For that, please see the “Advanced Database Techniques” chapter in The Busy Coder’s Guide to Android Development and the SQLite FTS documentation. This chapter is focused solely on enabling this stuff from Room, though you will see a bit of how FTS works along the way.

What You Can’t Do

The preview of this section may contain nuts.

The FTS Recipe

The preview of this section is in the process of being translated from its native Klingon.

Searching a Book

The preview of this section is being chased by zombies.