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:

@Override
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, "
                +"order=DESC);");
}

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.beginTransaction();

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

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

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

    db.setTransactionSuccessful();
  }
  finally {
    db.endTransaction();
  }
}

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",
                        null));
  }

  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.

 

Meta


Creative Commons License