sqlite - Full text search example in Android -

i'm having hard time understanding how use full text search (fts) android. i've read sqlite documentation on fts3 , fts4 extensions. , know it's possible on android. however, i'm having hard time finding examples can comprehend.

the basic database model

a sqlite database table (named example_table) has 4 columns. however, there 1 column (named text_column) needs indexed full text search. every row of text_column contains text varying in length 0 1000 words. total number of rows greater 10,000.

• how set table and/or fts virtual table?
• how perform fts query on text_column?

additional notes:

• because 1 column needs indexed, using fts table (and dropping example_table) inefficient non-fts queries.
• for such large table, storing duplicate entries of text_column in fts table undesirable. this post suggests using external content table.
• external content tables use fts4, fts4 not supported before android api 11. answer can assume api >= 11, commenting on options supporting lower versions helpful.
• changing data in original table not automatically update fts table (and vice versa). including triggers in answer not necessary basic example, helpful nonetheless.

most basic answer

i'm using plain sql below clear , readable possible. in project can use android convenience methods. db object used below instance of sqlitedatabase.

create fts table

db.execsql("create virtual table fts_table using fts3 ( col_1, col_2, text_column )"); 

this go in oncreate() method of extended sqliteopenhelper class.

populate fts table

db.execsql("insert fts_table values ('3', 'apple', 'hello. how you?')"); db.execsql("insert fts_table values ('24', 'car', 'fine. thank you.')"); db.execsql("insert fts_table values ('13', 'book', 'this example.')"); 

it better use sqlitedatabase#insert or prepared statements execsql.

query fts table

string[] selectionargs = { searchstring }; cursor cursor = db.rawquery("select * fts_table fts_table match ?", selectionargs); 

you use sqlitedatabase#query method. note match keyword.

fuller answer

the virtual fts table above has problem it. every column indexed, waste of space , resources if columns don't need indexed. column needs fts index text_column.

to solve problem use combination of regular table , virtual fts table. fts table contain index none of actual data regular table. instead have link content of regular table. called external content table.

create tables

db.execsql("create table example_table (_id integer primary key, col_1 integer, col_2 text, text_column text)"); db.execsql("create virtual table fts_example_table using fts4 (content='example_table', text_column)"); 

notice have use fts4 rather fts3. fts4 not supported in android before api version 11. either (1) provide search functionality api >= 11, or (2) use fts3 table (but means database larger because full text column exists in both databases).

populate tables

db.execsql("insert example_table (col_1, col_2, text_column) values ('3', 'apple', 'hello. how you?')"); db.execsql("insert example_table (col_1, col_2, text_column) values ('24', 'car', 'fine. thank you.')"); db.execsql("insert example_table (col_1, col_2, text_column) values ('13', 'book', 'this example.')"); 

(again, there better ways in inserts execsql. using readability.)

if tried fts query on fts_example_table no results. reason changing 1 table not automatically change other table. have manually update fts table:

db.execsql("insert fts_example_table (docid, text_column) select _id, text_column example_table"); 

(the docid rowid regular table.) have make sure update fts table (so can update index) every time make change (insert, delete, update) external content table. can cumbersome. if making prepopulated database, can

db.execsql("insert fts_example_table(fts_example_table) values('rebuild')"); 

which rebuild whole table. can slow, though, not want after every little change. after finishing inserts on external content table. if need keep databases in sync automatically, can use triggers. go here , scroll down little find directions.

query databases

string[] selectionargs = { searchstring }; cursor cursor = db.rawquery("select * fts_example_table fts_example_table match ?", selectionargs); 

this same before, except time have access text_column (and docid). if need data other columns in external content table? since docid of fts table matches rowid (and in case _id) of external content table, can use join. (thanks this answer that.)

string sql = "select * example_table _id in " +         "(select docid fts_example_table fts_example_table match ?)"; string[] selectionargs = { searchstring }; cursor cursor = db.rawquery(sql, selectionargs); 

further reading

go through these documents see other ways of using fts virtual tables:

• sqlite fts3 , fts4 extensions (sqlite docs)
• storing , searching data (android docs)

additional notes

• set operators (and, or, not) in sqlite fts queries have standard query syntax , enhanced query syntax. unfortunately, android apparently not support enhanced query syntax (see here, here, here, , here). means mixing , and or becomes difficult (requiring use of union or checking pragma compile_options seems). unfortunate. please add comment if there update in area.