...
The basic idea is to have a virtual FTS table along a for each register table.
The virtual table holds the values that are used when 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 when the table has more records.
For Example:
Code Block | ||
---|---|---|
| ||
explain query plan select * from mcaremother where FWWOMFNAME LIKE '%abc%' |
Returns
selectid | order | from | detail |
---|---|---|---|
0 | 0 | 0 | SCAN TABLE mcaremother |
Full table scans are really slow, especially with a large data set. They need to be optimized
Adopting FTS search
In your Application class, add the register table names that need to use FTS search
Code Block language java theme Eclipse private String[] getFtsTables(){ String[] ftsTables = { "household", "elco", "mcaremother", "mcarechild" }; return ftsTables; }
Add search and sort fields
Code Block language java theme Eclipse private String[] getFtsSearchFields(String tableName){ if(tableName.equals("household")){ String[] ftsSearchFileds = { "FWHOHFNAME", "FWGOBHHID", "FWJIVHHID" }; return ftsSearchFileds; } else if(tableName.equals("elco")){ String[] ftsSearchFileds = { "FWWOMFNAME", "GOBHHID", "JiVitAHHID" }; return ftsSearchFileds; } else if (tableName.equals("mcaremother")){ String[] ftsSearchFileds = { "FWWOMFNAME", "GOBHHID", "JiVitAHHID", "Is_PNC" }; return ftsSearchFileds; } else if (tableName.equals("mcarechild")){ String[] ftsSearchFileds = { "FWWOMFNAME", "GOBHHID", "JiVitAHHID" }; return ftsSearchFileds; } return null; } private String[] getFtsSortFields(String tableName){ if(tableName.equals("household")) { String[] sortFields = {"FWHOHFNAME", "FWGOBHHID", "FWJIVHHID"}; return sortFields; } else if(tableName.equals("elco")){ String[] sortFields = {"FWWOMFNAME", "GOBHHID", "JiVitAHHID"}; return sortFields; } else if(tableName.equals("mcaremother")){ String[] sortFields = {"FWWOMFNAME", "GOBHHID", "JiVitAHHID", "FWPSRLMP", "FWBNFDTOO", "FWSORTVALUE"}; return sortFields; } else if(tableName.equals("mcarechild")){ String[] sortFields = {"FWWOMFNAME", "GOBHHID", "JiVitAHHID", "FWSORTVALUE"}; return sortFields; } return null; }
If your query has a main condition, add the fields in this condition
For Example:
Code Block language java queryBUilder.mainCondition(" FWHOHFNAME is not null ");
Code Block language java theme Eclipse private String[] getFtsMainConditions(String tableName){ if(tableName.equals("household")) { String[] sortFields = {"FWHOHFNAME"}; return sortFields; } else if(tableName.equals("elco")){ String[] sortFields = {"FWWOMFNAME", "details"}; return sortFields; } else if(tableName.equals("mcaremother")){ String[] sortFields = {"FWWOMFNAME", "Is_PNC", "details"}; return sortFields; } else if(tableName.equals("mcarechild")){ String[] sortFields = {"FWBNFGEN"}; return sortFields; } return null; }
In onCreate() update the global context instance to have the values specified above.
Code Block language java context.updateCommonFtsObject(createCommonFtsObject());
Code Block language java theme Eclipse 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; }
In the Context class, commonFtsObject is passed as an argument when initializing the Repository class.
Code Block language java 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
Code Block language java theme Eclipse 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); } }
- s
Facts
- Queries perform much faster when indexes are used.
- Indexes are ignored when SQLite LIKE operator 's LIKE-keyword based query is used.
- Virtual tables require more space compared to regular tables.
- It takes more time to insert a record in a virtual table.
- Always consider
explain query plan
to determine whether your queries aren’t going to work slowly. - FTS does not support suffix search, only prefix.
...