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.
SQLite supports FTS virtual tables for full-text searching of content.
Room does not.
NOTE: The upcoming 2.1.0 version of Room will support FTS.
That edition of Room
is part of AndroidX; this book focuses on the original
of Room, which pre-dates this FTS support.
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.
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
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
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 a few full-text indexing options available in SQLite: FTS3, FTS4, and FTS5. Newer versions (higher numbers) are generally faster but take more disk space. FTS3 has been around since the beginning of Android. FTS4 arrived with the version of SQLite used in API Level 11. FTS5, by constrast, is only likely to be available on API Level 24 and higher devices.
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.
The preview of this section was stepped on by Godzilla.
The preview of this section was traded for a bag of magic beans.