...
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 when the table has more records.
For Example:
Code Block | ||
---|---|---|
| ||
explain query plan select * from mcaremotherhousehold where FWWOMFNAMEFWHOHFNAME LIKE '%abc%' |
Returns
selectid | order | from | detail |
---|---|---|---|
0 | 0 | 0 | SCAN TABLE mcaremotherhousehold |
Full table scans are really slow, especially with when querying a large data set. They need to be optimized Consider optimizing such queries.
Adopting FTS search
In your Application class, add *Application.java → Add the register table names that need to use FTS search
Code Block language java theme Eclipse private String[] getFtsTables(){ String[] ftsTables = { "householdtable1", "elco", "mcaremother", "mcarechild" ... }; return ftsTables; }
Add search and sort fields
Code Block language java theme Eclipse private String[] getFtsSearchFields(String tableName){ if(tableName.equals("householdtable1")){ String[] ftsSearchFiledsftsSearchFields = { "FWHOHFNAMESearchField1", "FWGOBHHID", "FWJIVHHID" ... }; return ftsSearchFiledsftsSearchFields; } ... } private String[] getFtsSortFields(String tableName){ if(tableName.equals("householdtable1")) { String[] sortFields = {"FWHOHFNAME", "FWGOBHHID", "FWJIVHHID"}SortField1" ... }; return sortFields; } ... return null; }
Add custom relational Ids if they exist.
These are foreign keys apart from relationalId or relational_id fields that store a unique identifier for the related table e.g. childId or parentId
Code Block language java theme Eclipse private String getFtsCustomRelationalId(String tableName){ if(tableName.equals("table1")){ String customRelationalId = "parentId1"; return sortFieldscustomRelationalId; } ... return null; }
If your Add condition fields if the query has a main condition, add the fields in this condition
For Example:
Code Block language java theme Eclipse queryBUilder.mainCondition(" FWHOHFNAMEField1 is not null ");
Code Block language java theme Eclipse private String[] getFtsMainConditions(String tableName){ if(tableName.equals("householdtable1")) { String[] sortFieldsmainConditions = {"FWHOHFNAMEField1"}; return sortFieldsmainConditions; } ... }
In onCreateIn *Application.onCreate(), update the global context instance to have the values specified above.i.e. update context.commonFtsObject
Code Block language java theme Eclipse 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)); commonFtsObject.updateCustomRelationalId(ftsTable, getFtsCustomRelationalId(ftsTable)); } return commonFtsObject; }
In org.ei.opensrp.commonregistry.CommonRepository#onCreate, id and relationalid indexes are created for each common repository table.
Code Block language java theme Eclipse if(StringUtils.isNotBlank(common_ID_INDEX_SQL)) { database.execSQL(common_ID_INDEX_SQL); } if(StringUtils.isNotBlank(common_Relational_ID_INDEX_SQL)) { database.execSQL(common_Relational_ID_INDEX_SQL); } if(StringUtils.isNotBlank(common_Custom_Relational_ID_INDEX_SQL)) { return commonFtsObject database.execSQL(common_Custom_Relational_ID_INDEX_SQL); }
In the Context classorg.ei.opensrp.Context.java, commonFtsObject is passed as an argument when initializing the Repository class.
Code Block language java theme Eclipse if(commonFtsObject != null){ repository = new Repository(this.applicationContext, session(), this.commonFtsObject, drishtireposotoryarray); }
Then the virtual tables are created using fts4, after creating the databasein org.ei.opensrp.repository.Repository#onCreate just after the database has been created.
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); } }
These virtual tables are updated after saving a form submission is saved.
For Example, In in FormSubmissionService class.java:
Code Block language java theme Eclipse ziggyService.saveForm(getParams(submission), submission.instance()); // Update FTS Tables updateFTSsearch(submission);
- Update filterandSortExecute() and CountExecute() methods to use FTS searchQueries perform much
Perform search/sort query in the virtual table(s) and return the reference ids
Virtual table scans are optimized and therefore faster than normal full table scans.
Facts
Code Block language sql title Explain query plan explain query plan select object_id from household_search where phrase match 'abc*'
Returns
selectid order from detail 0 0 0 SCAN TABLE household_search VIRTUAL TABLE INDEX 4: Use the ids returned in Step 9(a) above to filter the main query
Since id fields are indexed, Step 6, full table scans are replaced with search table using index.
Code Block language sql title Explain query plan explain query plan select * from household where id in (select object_id from household_search where phrase match 'abc*')
Returns
selectid order from detail 0 0 0 SEARCH TABLE household USING INDEX sqlite_autoindex_household_1 (id=?) 0 0 0 EXECUTE LIST SUBQUERY 1 0
0 0 SCAN TABLE household_search VIRTUAL TABLE INDEX 4:
Step 9 a) and b) in codeCode Block language java theme Eclipse title Using FTS // Get the virtual table query String sql = sqb.searchQueryFts(tablename, joinTable, mainCondition, filters, Sortqueries, currentlimit, currentoffset); // Get the reference ids List<String> ids = commonRepository.findSearchIds(sql); // Update the main query by adding the reference ids in the where clause currentquery = sqb.toStringFts(ids, tablename + "." + CommonRepository.ID_COLUMN, Sortqueries); query = sqb.Endquery(currentquery);
FTS Table Structure
Code Block | ||
---|---|---|
| ||
PRAGMA table_info('household_search'); |
Returns
cid | name | type | not null |
| pk |
---|---|---|---|---|---|
0 | object_id | 0 | 0 | ||
1 | object_relational_id | 0 | 0 | ||
2 | phrase | 0 | 0 | ||
3 | SortField1 | 0 | 0 | ||
4 | SortField2 | 0 | 0 | ||
5 | SortField3 | 0 | 0 |
object_id → reference id i.e. household.id
object_relational_id → reference relational id i.e. household.relationalid. Useful when query has a join between two register tables.
phrase → search term, contains all the searchable values separated by ' | ' e.g. " searchFieldValue1 | searchFieldValue2 | searchFieldValue3 "
sort field → sort value
Facts
- Queries are faster when indexes are used.
- Indexes are ignored when SQLite's LIKE-keyword based query is used.
- Virtual tables require more disk 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.
...