The following is the first few sections of a chapter from The Busy Coder's Guide to Android Development, plus headings for the remaining major sections, to give you an idea about the content of the chapter.


Advanced Database Techniques

This chapter offers tips and techniques for working with SQLite beyond what the previous chapters in the book have covered.

Prerequisites

This chapter assumes that you have read the core chapters, particularly the ones on databases and Internet access.

Also, please read the chapter on advanced action bar techniques, particularly the section on SearchView, as that is used in one of the sample apps.

Full-Text Indexing

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 section, we will review how you can add full-text indexing to your SQLite database and how you can let the user take advantage of that index using a SearchView.

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 this section 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.

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:

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.

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:

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

Some Notes About the Rest of the Sample App

As noted previously, this sample app is a revised version of the Stack Overflow questions list from the chapter on Internet access. It is specifically derived from the Picasso version of the sample. However, this version is designed to allow the user to full-text search the downloaded question data (e.g., title), above and beyond just seeing the list of latest questions.

This, in turn, requires a few more changes than those outlined so far. The following sections outline some of the highlights.

Adding a ModelFragment

The original sample had a very simple data model: a list of questions retrieved via Retrofit. Hence, the sample did not include much in the way of model management.

The FTS sample needs a database, which implies more local disk I/O that we are responsible for, which in turn leads us in the direction of implementing a model fragment (ModelFragment), much as the tutorials and a few other samples do:

package com.commonsware.android.fts;

import android.app.Activity;
import android.app.Fragment;
import android.content.Context;
import android.database.Cursor;
import android.os.Bundle;
import android.util.Log;
import org.greenrobot.eventbus.EventBus;
import org.greenrobot.eventbus.Subscribe;
import org.greenrobot.eventbus.ThreadMode;
import retrofit2.Retrofit;
import retrofit2.converter.gson.GsonConverterFactory;

public class ModelFragment extends Fragment {
  private Context app=null;

  @Override
  public void onCreate(Bundle state) {
    super.onCreate(state);

    setRetainInstance(true);
  }

  @Override
  public void onAttach(Activity host) {
    super.onAttach(host);

    EventBus.getDefault().register(this);

    if (app==null) {
      app=host.getApplicationContext();
      new FetchQuestionsThread().start();
    }
  }

  @Override
  public void onDetach() {
    EventBus.getDefault().unregister(this);

    super.onDetach();
  }

  @Subscribe(threadMode =ThreadMode.BACKGROUND)
  public void onSearchRequested(SearchRequestedEvent event) {
    try {
      Cursor results=DatabaseHelper.getInstance(app).loadQuestions(app, event.match);

      EventBus.getDefault().postSticky(new ModelLoadedEvent(results));
    }
    catch (Exception e) {
      Log.e(getClass().getSimpleName(),
          "Exception searching database", e);
    }
  }

  class FetchQuestionsThread extends Thread {
    @Override
    public void run() {
      Retrofit retrofit=
        new Retrofit.Builder()
          .baseUrl("https://api.stackexchange.com")
          .addConverterFactory(GsonConverterFactory.create())
          .build();
      StackOverflowInterface so=
        retrofit.create(StackOverflowInterface.class);

      try {
        SOQuestions questions=so.questions("android").execute().body();

        DatabaseHelper
            .getInstance(app)
            .insertQuestions(app, questions.items);
      }
      catch (Exception e) {
        Log.e(getClass().getSimpleName(),
            "Exception populating database", e);
      }

      try {
        Cursor results=DatabaseHelper.getInstance(app).loadQuestions(app, null);

        EventBus.getDefault().postSticky(new ModelLoadedEvent(results));
      }
      catch (Exception e) {
        Log.e(getClass().getSimpleName(),
            "Exception populating database", e);
      }
    }
  }
}

In onCreate(), we mark this fragment as retained, as that is key to the model fragment pattern, so the fragment retains the model data across configuration changes.

In onAttach(), we register for the greenrobot EventBus, plus kick off a FetchQuestionsThread if we have not done so already (i.e., this is the first onAttach() call we have received). onDetach() unregisters us from the event bus.

FetchQuestionsThread, in turn, uses Retrofit to download the questions from Stack Overflow, then uses DatabaseHelper to insert the questions into the FTS-enabled database table, then uses the DatabaseHelper again to retrieve all existing questions in the form of a Cursor, which it wraps in a ModelLoadedEvent and posts to the EventBus. This time, though, it posts it as a sticky event.

That sticky event is consumed by a revised version of the QuestionsFragment, in its onModelLoaded() method:

  @Subscribe(sticky = true, threadMode =ThreadMode.MAIN)
  public void onModelLoaded(ModelLoadedEvent event) {
    ((SimpleCursorAdapter)getListAdapter()).changeCursor(event.model);

    if (sv!=null) {
      sv.setEnabled(true);
    }
  }

But because this is a sticky event, we will get this event both when it is raised (because the data is loaded) and any time thereafter when the fragment registers with the EventBus. This allows QuestionsFragment to not be retained, as it will get back the bulk of its model data automatically from greenrobot’s EventBus.

QuestionsFragment also is modified from the Picasso sample to deal with the fact that its model data is now a Cursor, so it uses SimpleCursorAdapter to populate the list. To handle loading avatar images from the URLs, QuestionsFragment adds a QuestionBinder implementation of ViewBinder to the SimpleCursorAdapter, where QuestionBinder handles the Picasso logic from before:

  private class QuestionBinder implements SimpleCursorAdapter.ViewBinder {
    int size;

    QuestionBinder() {
      size=getActivity()
              .getResources()
              .getDimensionPixelSize(R.dimen.icon);
    }

    @Override
    public boolean setViewValue (View view, Cursor cursor, int columnIndex) {
      switch (view.getId()) {
        case R.id.title:
          ((TextView)view).setText(Html.fromHtml(cursor.getString(columnIndex)));

          return(true);

        case R.id.icon:
          Picasso.with(getActivity()).load(cursor.getString(columnIndex))
              .resize(size, size).centerCrop()
              .placeholder(R.drawable.owner_placeholder)
              .error(R.drawable.owner_error).into((ImageView)view);

          return(true);
      }

      return(false);
    }
  }

The main activity (MainActivity) sets up the ModelFragment in onCreate(), at least when one does not already exist due to a configuration change:

  @Override
  protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);

    if (getFragmentManager().findFragmentById(android.R.id.content) == null) {
      getFragmentManager().beginTransaction()
                          .add(android.R.id.content,
                               new QuestionsFragment()).commit();
    }

    model=(ModelFragment)getFragmentManager().findFragmentByTag(MODEL);

    if (model==null) {
      model=new ModelFragment();
      getFragmentManager().beginTransaction().add(model, MODEL).commit();
    }
  }

This description, though, has skipped over the onEventBackgroundThread() method on the ModelFragment, which we will get to later in this overview.

Adding a SearchView

As is covered in the chapter on advanced action bar techniques, a SearchView can be used to provide the standard “magnifying glass” search icon in the action bar. When tapped, the action bar item expands into a field where the user can type something, which our code can then receive and use to update the UI. In the SearchView sample from the action bar chapter, we saw using a SearchView for filtering. This time, we will use a SearchView for searching.

For a search, we need to know when the user is done typing, which is usually done by the user clicking a submit button. Hence, our code to configure the SearchView (a configureSearchView() method in QuestionsFragment) calls setSubmitButtonEnabled(true):

  private void configureSearchView(Menu menu) {
    MenuItem search=menu.findItem(R.id.search);

    search.setOnActionExpandListener(this);
    sv=(SearchView)search.getActionView();
    sv.setOnQueryTextListener(this);
    sv.setSubmitButtonEnabled(true);
    sv.setIconifiedByDefault(true);

    if (initialQuery != null) {
      sv.setIconified(false);
      search.expandActionView();
      sv.setQuery(initialQuery, true);
    }
  }

This, in turn, means that we need to pay attention to onQueryTextSubmit() in our SearchView.OnQueryTextListener implementation. That interface is implemented on QuestionsFragment itself, and delegates its work to a doSearch() method:

  @Override
  public boolean onQueryTextSubmit(String query) {
    doSearch(query);

    return(true);
  }

That method, in turn, confirms that the search is different than the last one we did (so we do not waste time running the search again), disables the SearchView, and posts a SearchRequestedEvent on the EventBus with the user’s search string:

  private void doSearch(String match) {
    if (!match.equals(lastQuery)) {
      lastQuery=match;

      if (sv != null) {
        sv.setEnabled(false);
      }

      EventBus.getDefault().post(new SearchRequestedEvent(match));
    }
  }

That event is picked up by onSearchRequested() on ModelFragment. The @Subscribe(threadMode =ThreadMode.BACKGROUND) annotation means that the event will be delivered to us on an EventBus-supplied background thread, so we can perform database I/O. In it, we call loadQuestions() on the DatabaseHelper to perform the search, and post another sticky ModelLoadedEvent to update the UI with the search results and re-enable the SearchView:

  @Subscribe(threadMode =ThreadMode.BACKGROUND)
  public void onSearchRequested(SearchRequestedEvent event) {
    try {
      Cursor results=DatabaseHelper.getInstance(app).loadQuestions(app, event.match);

      EventBus.getDefault().postSticky(new ModelLoadedEvent(results));
    }
    catch (Exception e) {
      Log.e(getClass().getSimpleName(),
          "Exception searching database", e);
    }
  }

When the user clears the SearchView, such as by pressing the BACK button a few times, the onMenuItemActionCollapse() method of QuestionsFragment calls a clearSearch() method:

  @Override
  public boolean onMenuItemActionCollapse(MenuItem item) {
    clearSearch();

    return(true);
  }

That clearSearch() method simply posts another SearchRequestedEvent, this time to load a fresh roster of all questions:

  private void clearSearch() {
    if (lastQuery!=null) {
      lastQuery=null;

      sv.setEnabled(false);
      EventBus.getDefault().post(new SearchRequestedEvent(null));
    }
  }

The Results

When you run the app, you are initially presented with the list of questions pulled from the Stack Exchange API:

FTS Demo, As Initially Launched
Figure 785: FTS Demo, As Initially Launched

Tapping on the SearchView opens it up, as normal, though this time with the “submit” button (the rightward-pointing arrowhead):

FTS Demo, with Open SearchView
Figure 786: FTS Demo, with Open SearchView

Typing in a search, then tapping the “submit” button, will reload the list with those questions that match the search criteria in the question title:

FTS Demo, Showing Basic Search
Figure 787: FTS Demo, Showing Basic Search

FTS Demo, Showing Boolean Search
Figure 788: FTS Demo, Showing Boolean Search

Using the BACK button to get out of the SearchView reloads the full list of questions.

Getting Snippets

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:

This Books Reader App, Showing Search Results
Figure 789: This Book’s Reader App, Showing Search Results

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.