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