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.
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 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:
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 may contain nuts.
The preview of this section is in the process of being translated from its native Klingon.
The preview of this section is being chased by zombies.