Versions Compared

Old Version 13

changes.mady.by.user Ephraim Muhia

Saved on

compared with

New Version 14

changes.mady.by.user Ephraim Muhia

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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

    Code Block
    languagejava
    themeEclipse
    private String[] getFtsTables(){
    String[] ftsTables = { "table1" ... };
    return ftsTables;
}

  2. Add search and sort fields

    Code Block
    languagejava
    themeEclipse
    private String[] getFtsSearchFields(String tableName){
    if(tableName.equals("table1")){
        String[] ftsSearchFields =  { "SearchField1" ... };
        return ftsSearchFields;
    } ...
}

private String[] getFtsSortFields(String tableName){
    if(tableName.equals("table1")) {
        String[] sortFields = {"SortField1" ... };
        return sortFields;
    } ...
    return null;
}

  3. 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
    languagejava
    themeEclipse
    private String getFtsCustomRelationalId(String tableName){
    if(tableName.equals("table1")){
        String customRelationalId = "parentId1";
        return customRelationalId;
    } ...
    return null;
}

  4. Add condition fields if the query has a main condition

    For Example:

    Code Block
    languagejava
    themeEclipse
    queryBUilder.mainCondition(" Field1 is not null ");
    Code Block
    languagejava
    themeEclipse
    private String[] getFtsMainConditions(String tableName){
    if(tableName.equals("table1")) {
        String[] mainConditions = {"Field1"};
        return mainConditions;
    } ...
}

  5. In *Application.onCreate(), update the global context instance i.e. update context.commonFtsObject

    Code Block
    languagejava
    themeEclipse
    context.updateCommonFtsObject(createCommonFtsObject());
    Code Block
    languagejava
    themeEclipse
    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;
}

  6. In org.ei.opensrp.commonregistry.CommonRepository#onCreate, id and relationalid indexes are created for each common repository table.

    Code Block
    languagejava
    themeEclipse
    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)) {
    database.execSQL(common_Custom_Relational_ID_INDEX_SQL);
}

  7. In org.ei.opensrp.Context.java, commonFtsObject is passed as an argument when initializing the Repository class.

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

    Then the virtual tables are created in org.ei.opensrp.repository.Repository#onCreate just after the database has been created.

    Code Block
    languagejava
    themeEclipse
    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);

    }
}

  8. These virtual tables are updated after a form submission is saved.

    For Example, in FormSubmissionService.java:

    Code Block
    languagejava
    themeEclipse
    ziggyService.saveForm(getParams(submission), submission.instance());

// Update FTS Tables
updateFTSsearch(submission);
  9. Update filterandSortExecute() and CountExecute() methods to use FTS search

    1. 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.

      Code Block
      languagesql
      titleExplain query plan
      explain query plan select object_id from household_search where phrase match 'abc*'

      Returns

      selectidorderfromdetail
      000SCAN TABLE household_search VIRTUAL TABLE INDEX 4:


    2. Use the ids returned in Step 79(a) above to filter the main query

      Since id fields are indexed, Step 56, full table scans are replaced with search table using index.

      Code Block
      languagesql
      titleExplain query plan
      explain query plan select * from household where id in (select object_id from household_search where phrase match 'abc*')

      Returns

      selectidorderfromdetail
      000SEARCH TABLE household USING INDEX sqlite_autoindex_household_1 (id=?)
      000EXECUTE LIST SUBQUERY 1

      0

      		00SCAN TABLE household_search VIRTUAL TABLE INDEX 4:


    Step 8 9 a) and b) in code

    Code Block
    languagejava
    themeEclipse
    titleUsing 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);

...