Book Excerpt: Full-Text Indexing and Searching (Part 5)

The following sections are excerpts from Version 6.9 of “The Busy Coder’s Guide to Android Development”, with slight modifications to fit the blog format. It continues the blog post series begun Monday.

Usually, the content that is being indexed is a lot longer than Stack Overflow question titles. For example, it might be chapters in a book on Android application development. In that case, it would be useful to not only find out what chapters match the search expression, but what the prose is around the search expression, to help the user determine which search results are likely to be useful.

The APK edition of this book stores each paragraph and bullet as a separate entry in a SQLite database in an FTS3-enabled table. The query used when the reader types in a search expression in the app’s SearchView is:

SELECT ROWID as _id, file, node, snippet(booksearch) AS snippet FROM booksearch WHERE prose MATCH ?

Here, file and node are used to identify where this passage came from within the book, so when the user taps on a search result in the list, the book reader can jump to that particular location.

The snippet() auxiliary function will return, as the name suggests, a snippet of the indexed text, with the search match highlighted. It takes the name of the table booksearch as a mandatory parameter. It also supports optional parameters for what to bracket the search match with (defaults to <b> and </b>) and what to use for an ellipsis for extended prose segments (defaults to <b>...</b>). In the case of this query, the default formatting of the result is used. The resulting text can then be fed into Html.fromHtml() to generate the text for the ListView row, showing the search match within the snippet highlighted in bold:

The app also shows the name of the chapter in the lower-right corner of each row, to help provide larger context for where this snippet comes from.