The CommonsBlog

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

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 yesterday.

Creating a Full-Text Indexed Table

A full-text indexed table, using FTS3 or FTS4, uses SQLite’s CREATE VIRTUAL TABLE syntax. This indicates that you are opting into some special table-storage behavior, rather than the stock stuff.

In the Database/FTS sample project, the onCreate() method of our SQLiteOpenHelper subclass (DatabaseHelper) creates such a virtual table, using FTS4 for full-text indexing:

public void onCreate(SQLiteDatabase db) {
  db.execSQL("CREATE VIRTUAL TABLE questions USING fts4("
                +"_id INTEGER PRIMARY KEY, title TEXT, "
                +"link TEXT, profileImage TEXT, creationDate INTEGER, "

There are a few differences here from a typical CREATE TABLE statement, beyond the introduction of the VIRTUAL keyword:

  • The USING fts4 indicates that the virtual table is employing the FTS4 full-text indexing engine. To use FTS3, just replace fts4 with fts3.

  • You can have key-value pairs in the column list, separated by equals signs, to provide options for configuring the virtual table. In this case, it will provide options for configuring the FTS4 indexing behavior. In this case, we are providing order=DESC, to indicate that the full-text index should be optimized for returning items in descending order. Note that these options only exist for FTS4, not FTS3. The full roster of available options is covered in the SQLite documentation.

This gives us a table that supports normal table operations but also has a full-text index for its columns. However, there are some limitations, notably that these tables ignore constraints. So, for example, the PRIMARY KEY constraint applied to the _id column is ignored.

Populating a Full-Text Indexed Table

Adding content to an FTS3 or FTS4 table uses the same INSERT statements that you might use for a regular table. For example, the DatabaseHelper in the sample app has an insertQuestions() method that deletes all existing rows in the questions table, then inserts a bunch of rows based on a supplied List of Item objects:

void insertQuestions(Context app, List<Item> items) {
  SQLiteDatabase db=getDb(app);


  db.delete("questions", null, null);

  try {
    for (Item item : items) {
      Object[] args={, item.title,,
                      item.owner.profileImage, item.creationDate};

      db.execSQL("INSERT INTO questions (_id, title, "
                    +"link, profileImage, creationDate) "
                    +"VALUES (?, ?, ?, ?, ?)",

  finally {

If those Item objects look familiar, that is because this app is a modified version of the Stack Overflow questions apps profiled in the chapter on Internet access in the book.

The reason why we are deleting everything before inserting is just to keep the sample simple. The database table will hold all of the questions pulled from the Stack Exchange API. Each time we run the app, we get the latest questions from that API. The vision was to use INSERT OR REPLACE or INSERT OR IGNORE statements to be able to merge content into the table. However, FTS3 and FTS4 tables ignore all constraints, as noted above, which prevents the conflict resolution options (e.g., OR REPLACE) from working. Hence, rather than manually sifting through to find if there is an existing row or not for a given ID value, this sample simply gets rid of all existing rows. A production-grade app would likely apply a more sophisticated algorithm.

Querying a Full-Text Indexed Table

While you can query a full-text indexed table using normal SELECT statements, usually the point is to apply the MATCH operator, as is seen in the loadQuestions() method from DatabaseHelper:

Cursor loadQuestions(Context app, String match) {
  SQLiteDatabase db=getDb(app);

  if (TextUtils.isEmpty(match)) {
    return(db.rawQuery("SELECT * FROM questions ORDER BY creationDate DESC",

  String[] args={ match };

  return(db.rawQuery("SELECT * FROM questions WHERE title "
                      +"MATCH ? ORDER BY creationDate DESC", args));

The MATCH operator supports a wide range of query structures, including:

  • Keyword matches (e.g., Android)

  • Prefix matches (e.g., SQL*)

  • Phrase matches (e.g., "open source")

  • NEAR, AND, OR, and NOT operators (e.g., sqlite AND database)

The result is the same sort of Cursor that you would get from a regular SELECT statement against a non-full-text-indexed table.

In tomorrow’s post, we will explore a bit more about the demo app, such as the model fragment being used for holding onto the results of the query.

Dec 01, 2015

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

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. Due to the length of the material, it will be split over a few posts.

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, 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.

For example, if you are reading this book using the Android APK edition (instead of the PDF, EPUB, or Kindle/MOBI editions), tap on the SearchView action bar item and search for FTS4. You will get a list of matches back almost instantaneously, despite the fact that you are searching a multi-megabyte book. That is because this book ships a SQLite-powered full-text index of the book’s contents, specifically to power your use of SearchView.

In this series of blog posts, we will review how you can add full-text indexing to your SQLite database.

First, a Word About SQLite Versions

SQLite has evolved since Android’s initial production release in 2008.

In many cases, Android does not incorporate updates to third-party code, for backwards-compatibility reasons (e.g., Apache’s HttpClient). In the case of SQLite, newer Android versions do take on newer versions of SQLite… but the exact version of SQLite that a given version of Android uses is undocumented. Worse, some device manufacturers replace the stock SQLite for a version of Android with a different one.

This Stack Overflow answer contains a mapping of Android OS releases to SQLite versions, including various “anomalies” where manufacturers have elected to ship something else.

In many cases, the SQLite version does not matter. Core SQLite capabilities will have existed since the earliest days of Android. However, full-text indexing did not exist in the first SQLite used by Android, meaning that you will have to pay attention to your minSdkVersion and aim high enough that devices should support the full-text indexing option you choose.

Note that you could use an external SQLite implementation, one that gives you a newer SQLite engine than what might be on the device. For example, SQLCipher for Android ships its own copy of SQLite (with the SQLCipher extensions compiled in), one that is often newer than the one that is baked into the firmware of any given device.

FTS3 and FTS4

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.

The sample app for these posts will demonstrate FTS4, as that is available on most Android devices.

Note that the Android developer documentation does not cover FTS3 or FTS4 full-text indexing. The details for the SQL syntax to support these options can be found in the SQLite documentation.

In tomorrow’s post, we will see how to create, populate, and query an FTS3 or FTS4 table.

Nov 30, 2015

SecurityExceptions, Runtime Permissions, and "Reset app preferences"

Maksim Dmitriev, in a Stack Overflow question, pointed out an obscure but unpleasant bug in Android 6.0 and its runtime permission system.

Most of the time, you will handle permissions yourself within your app: you call requestPermissions(), dialogs appear, users grant permissions, and everyone walks away happy.

Users can go into Settings > Apps > (your app name) > Permissions and revoke permissions that the user previously granted. However, when this happens, Android terminates your process. This will force you to call checkSelfPermission() again, when your code next runs, and you will find out about the lost permission.

However, there is also Settings > Apps > “Reset app preferences”, where “Reset app preferences” is found in the action bar overflow. Tapping that brings up a dialog that tells the user about wide-ranging effects of resetting app preferences, such as re-enabling disabled apps. One of those effects is to revoke all granted permissions. If the user proceeds with the operation, your app loses its permissions.

However, in this case, Android does not terminate your process.

As a result, if you call some protected method after “Reset app preferences”, relying on some previous call to checkSelfPermission(), you will fail with a SecurityException or similar sort of error.

This is not good.

The one saving grace is that “Reset app preferences” is obscure and comes with a fairly scary-looking warning dialog. Few, if any, of your users will elect to reset those preferences. And, while Android will not terminate your process due to those reset permissions, it’s entirely possible that your process will die of “natural causes” while it is in the background anyway.

Personally, while this particular problem should be addressed in Android, it’s not the sort of thing that will keep me awake nights worrying about at the SDK level. However, it is something you should keep in the back of your mind.

If you really want to try to minimize the risk, use checkSelfPermission() at two levels in your app:

  1. Check if you hold the permission at the point in time where you would need to call requestPermissions() to get the permission from the user. This could be anywhere from on first run of your app to when the user taps some action bar item that triggers work that will need a dangerous permission.

  2. Check right before you call APIs that require that dangerous permission. Or, wrap those APIs in try/catch blocks to catch SecurityException, though it’s not guaranteed that SecurityException will be the specific exception thrown from all such APIs. In these cases, you know that your permission was revoked behind your back, and you can treat it as you would other sorts of error cases (e.g., IOException when you can’t reach the REST server) that your app encounters.

Nov 24, 2015

Why The Play Store Thinks Your App Has Ads... When It Doesn't

The Play Store is rolling out mandatory declarations of whether or not your app has ads. As part of this, they are running some automatic ad-SDK detection algorithms on your app. According to this Stack Overflow question, those algorithms may be a bit over-aggressive, reporting that you have AdMob when you are not using AdMob.

As a tiger-striped rabbit named Tanis.7x points out in an answer, the problem in this case comes from Play Services and transitive dependencies.

If your app depends upon, you are pulling in vast swaths of the Play Services SDK, including play-services-ads, the portion of the SDK that ties into AdMob. Switching to more granular dependencies may help here. In the case of the person who wrote the original Stack Overflow question, they really only needed play-services-maps, and by switching to depend upon that instead of play-services, the AdMob detection problem cleared up.

However, bear in mind that even some finer-grained Play Services artifacts will depend on play-services-ads. play-services-all-wear definitely depends upon play-services-ads. Tanis.7x reports that play-services-analytics depends upon play-services-ads, though that seems to be limited to version 8.1.0. Others may crop up in the future.

(and if you think that play-services-analytics case was just some random bug, play-services-location even today depends upon play-services-maps, which is a clear case of the tail wagging the dog)

It is also possible that the right ProGuard and other minification settings for your release build would help here, as perhaps something is being left behind that the Play Store algorithm is detecting. Alas, we have no great way of telling exactly what the Play Store is looking for.

Another Stack Overflow user, in a separate answer on the question, indicates that Play Store support staff said to leave the ads switch on “no”, despite the detected SDK. That makes sense, as you are declaring whether your app shows ads, not whether your app has code that might relate to ads. However, I still encourage you to try to clean this up, as future automated takedowns might sweep up your app, if they don’t improve their algorithms by then.

Nov 19, 2015

Another Doze Edge Case: Foreground vs. Foreground

Petr Nalevka has done a lot of work trying to make sense of some odd Doze mode behaviors on Android 6.0. In particular, he has nailed down one source of problems when trying to use a foreground service to bypass Doze mode.

A foreground service with a partial WakeLock is supposed to block Doze mode, presumably with an eye towards music players and the like. However, Mr. Nalevka discovered that if the process with the foreground service also has the foreground activity, Doze mode takes over again.

Dianne Hackborn’s recommended workaround is to split the foreground service out into a separate process. Since there doesn’t seem to be a way to link to specific G+ comments, you’ll need to scroll way down the comments list on this post to get to their exchange.

Nov 18, 2015

Older Posts