Is there anyway to implement Full Text Search (FTS) in SQlite from Android platform?

Question

I am trying to create an application which collects a lot of notes from users. I want to implement full text search on the notes so that the user can get relevant notes from the whole array of notes. I am looking for a solution for this. Full-text-search(FTS) is actually supported by SQLite, but is it available for Android? Can anybody enlighten me on this?

Answer 1

Personally I don't think its a good idea to traverse all the db entries in code and do a lot of operations (like toLowerCase) repeatedly.

A better solution would be to create create another table in your SQL database with two columns one for the keys and one for strings.

Now lets assume we have a table persons so we create another table *fts_persons*. Each time a new Person is added to the persons table a new entry would be added to the *fts_persons* table as well. The key in the *fts_persons* would be the very same as it is in the persons table and the second column would contain all the stuff that is searchable for a person, separated by a separator character.

Example :

persons table:

---

1234 | Joe | Sutter | Kingston Road | 23 | worker

fts_persons table:

---

1234 | joe+sutter+kingston road+23+worker

Now when doing fulltext search you simply do a MATCH query for the keys on the *fts_persons* string column. If there are some matches you will get a list of keys for which you can do another query in the persons table. Or you can combine these two queries into one which would make things even faster.

Of course you have to keep the fts tables in sync with the tables they are made for , so each time a persons table is updated or deleted you have to update or delete the affected column in the *fts_persons* table as well. For this the best thing to do is to use triggers within the SQL database

Answer 2

Full text search in SQLite is supported in Android. You can see an example of it being used in my application here:

http://github.com/bpellin/keepassdroid/blob/master/src/com/keepassdroid/search/SearchDbHelper.java

Answer 3

Yes it is possible, you can make use of Sqlite's FTS3 and FTS4 extensions. They work great. FTS tables are created using a CREATE VIRTUAL TABLE statement. For more information on how FTS tables work and how you can search for terms please have a look here

I found this method very useful while searching through a customer database -

public static String[] negligibleTokens = {"a", "an", "the", "is", "am", "are", "to"};

public Cursor searchCustomer(String inputText) throws SQLException {
    Log.w(TAG, inputText);

    String[] searchQueryTokens = inputText.split(" ");
    StringBuilder searchQueryBuilder = new StringBuilder();

    for (int i = 0; i < searchQueryTokens.length; i++) {

        if (!Arrays.asList(negligibleTokens).contains(searchQueryTokens[i])) {

            if (i != 0 && searchQueryBuilder.length() > 0)
                searchQueryBuilder.append(" OR" +
                        " ");

            searchQueryBuilder.append(searchQueryTokens[i]);
        }
    }        

    String query = "SELECT docid as _id," +
            KEY_CUSTOMER + "," +
            KEY_NAME + "," +
            "(" + KEY_ADDRESS1 + "||" +
            "(case when " + KEY_ADDRESS2 + "> '' then '\n' || " + KEY_ADDRESS2 + " else '' end)) as " + KEY_ADDRESS + "," +
            KEY_ADDRESS1 + "," +
            KEY_ADDRESS2 + "," +
            KEY_CITY + "," +
            KEY_STATE + "," +
            KEY_ZIP +
            " from " + FTS_VIRTUAL_TABLE +
            " where " + KEY_SEARCH + " MATCH '" + searchQueryBuilder.toString() + "';";
    Log.w(TAG, query);
    Cursor mCursor = mDb.rawQuery(query, null);

    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;

}

This method uses the " MATCH" query in the table FTS_VIRTUAL_TABLE. The "OR" operator ensures every customer with even a single word matching from the whole search text gets included in the search results.

Hope it helps.