FTS Search

FTS3 and FTS4 are SQLite virtual table modules that allows users to perform full-text searches on a set of documents.

The basic idea is to have a virtual FTS table for each register table.

The virtual table holds the values that are used for searching and sorting as well as an identifier to link the register table.

Prerequisite

Before adding FTS search, always consider explain query plan to determine whether your queries will be slow.

For Example:

explain query plan select * from mcaremother where FWWOMFNAME LIKE '%abc%'

Returns

Full table scans are really slow, especially when querying a large data set.

Consider optimizing

Adopting FTS search

1. *Application.java →  Add the register table names

   private String[] getFtsTables(){
       String[] ftsTables = { "household" ... };
       return ftsTables;
   }

2. Add search and sort fields

   private String[] getFtsSearchFields(String tableName){
       if(tableName.equals("household")){
           String[] ftsSearchFileds =  { "FWHOHFNAME", "FWGOBHHID", "FWJIVHHID" };
           return ftsSearchFileds;
       } ...
   }
   
   private String[] getFtsSortFields(String tableName){
       if(tableName.equals("household")) {
           String[] sortFields = {"FWHOHFNAME", "FWGOBHHID", "FWJIVHHID"};
           return sortFields;
       } ...
       return null;
   }

3. If your query has a main condition, add the fields in this condition

   For Example:

   queryBUilder.mainCondition(" FWHOHFNAME is not null ");

   private String[] getFtsMainConditions(String tableName){
       if(tableName.equals("household")) {
           String[] sortFields = {"FWHOHFNAME"};
           return sortFields;
       } ...
   }

4. In onCreate() update the global context instance to have the values specified above.

   context.updateCommonFtsObject(createCommonFtsObject());

   private CommonFtsObject createCommonFtsObject(){
       CommonFtsObject commonFtsObject = new CommonFtsObject(getFtsTables());
       for(String ftsTable: commonFtsObject.getTables()){
           commonFtsObject.updateSearchFields(ftsTable, getFtsSearchFields(ftsTable));
           commonFtsObject.updateSortFields(ftsTable, getFtsSortFields(ftsTable));
           commonFtsObject.updateMainConditions(ftsTable, getFtsMainConditions(ftsTable));
       }
       return commonFtsObject;
   }

5. In the Context class, commonFtsObject is passed as an argument when initializing the Repository class.

   if(commonFtsObject != null){
       repository = new Repository(this.applicationContext, session(), this.commonFtsObject, drishtireposotoryarray);
   }

   Then the virtual tables are created using fts4, after creating the database

   org.ei.opensrp.repository.Repository#onCreate

   if(this.commonFtsObject != null) {
       for (String ftsTable: commonFtsObject.getTables()) {
           Set<String> searchColumns = new LinkedHashSet<String>();
           searchColumns.add(CommonFtsObject.idColumn);
           searchColumns.add(CommonFtsObject.relationalIdColumn);
           searchColumns.add(CommonFtsObject.phraseColumnName);
   
           String[] mainConditions = this.commonFtsObject.getMainConditions(ftsTable);
           if(mainConditions != null)
               for (String mainCondition : mainConditions) {
                   searchColumns.add(mainCondition);
               }
   
           String[] sortFields = this.commonFtsObject.getSortFields(ftsTable);
           if(sortFields != null)
               for (String sortValue : sortFields) {
                   searchColumns.add(sortValue);
               }
   
           String joinedSearchColumns = StringUtils.join(searchColumns, ",");
   
           String searchSql = "create virtual table " + CommonFtsObject.searchTableName(ftsTable) + " using fts4 (" + joinedSearchColumns + ");";
           database.execSQL(searchSql);
   
       }
   }

6. These virtual tables are updated after saving a form submission.

   For Example, In FormSubmissionService class:

   ziggyService.saveForm(getParams(submission), submission.instance());
   
   // Update FTS Tables
   updateFTSsearch(submission);

7. s

Facts

1. Queries perform much faster when indexes are used.
2. Indexes are ignored when SQLite's LIKE-keyword based query is used.
3. Virtual tables require more space compared to regular tables.
4. It takes more time to insert a record in a virtual table.
5. Always consider explain query plan to determine whether your queries aren’t going to work slowly.
6. FTS does not support suffix search, only prefix.

Links

https://www.sqlite.org/fts3.html

https://lab.getbase.com/killer-search-on-android-part-1

http://stackoverflow.com/questions/29815248/full-text-search-example-in-android