Android/SQLite FTS (Full Text Search)

First thing we need to do is create an index table using following sql statement. This virtual table can be treated as an ordinary table. But there are few rules for FTS3 such as, all the columns must be string/text.

CREATE VIRTUAL TABLE  t_doc_fts USING FTS3(doc_id, text)

Insert Data as any other table. Put all searchable strings comma separated in 'text' column. And reference id in 'doc_id' column. Here reference id is the primary key of the actual search table. If 'doc_id' column is still confusing please see the search method below.

public void populateFTS(long docId, String[] data){
   db.execSQL("INSERT INTO t_doc_fts(doc_id, text) values( "+docId
         +", '"+data[1]+","+data[2]+","
         +data[3]+","+data[4]+","+data[5]+","+data[6]+","+data[7]+"')");
}
  
public long insert(String[] data) throws RuntimeException{
    long id = db.insert(...);
    /* populate FTS table */
    populateFTS(id, data);
    return id;
}

Search Method:

public Cursor fetchDocumentFTS(String code) throws RuntimeException {
       key = code.replace(' ', '*');
       key = key+"*";

       System.out.println("FTS KEY="+key);
       Cursor cur0 = dbr.query(true, "t_doc_fts",
                       new String[]{"doc_id","text"},
                       "text MATCH '"+key+"'",
                       null, null, null, null, null);
       String ids = "";
       if(cur0 != null){
               cur0.moveToFirst();
               for(int i=0; i < cur0.getCount(); i++){
                       if(i == 0)
                              ids = cur0.getString(0);
                       else 
                               ids = ids+","+cur0.getString(0);
                       cur0.moveToNext();
                }
                cur0.close();
        }
        System.out.println("FTS IDS="+ids);
        Cursor cur = dbr.query(true, "t_document", new String[]{...}, 
                        "_id IN ("+ids+")",
                        null, null, null, null, null);
        if(cur!=null)
                cur.moveToFirst();
        return cur;
    }