Is there a way to implement full-text search (FTS) in SQlite from the Android platform?

Question

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

Answer 1

SQLite full text search is supported on Android. You can see an example of using this application in my application:

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

Answer 2

Personally, I don’t think it’s a good idea to traverse all the db entries in the code and perform operations many times (e.g. toLowerCase).

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

Now suppose we have table faces, so we create another table * fts_persons *. Each time a new face is added to the face table, a new record is added to the * fts_persons * table. The key in * fts_persons * will be the same as in the face table, and the second column will contain all the materials that can be found for a person, separated by a separator character.

Example:

face table:

---

1234 | Joe | Sutter | Kingston Road | 23 | Working

Fts_persons table:

---

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

Now that you are doing a full-text search, you just do the MATCH query for the keys in the * fts_persons * row column. If there are several matches, you will get a list of keys for which you can make another request in the persons table. Or you can combine these two queries into one, which will make things even faster.

Of course, you must synchronize the fts tables with the tables for which they are created, so each time the face table is updated or deleted, you must update or delete the affected column in the * fts_persons * table. For this, it is best to use triggers in an SQL database.

Answer 3

Yes, perhaps you can use the Sqlite FTS3 and FTS4 extensions. They work great. FTS tables are created using the CREATE VIRTUAL TABLE statement. For more information on how FTS tables work and how you can search for conditions, please see here.

I found this method very useful when searching the 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 table FTS_VIRTUAL_TABLE. The OR operator ensures that each client with one single word match from the entire text of the search will be included in the search results.

Hope this helps.