Page Comparison

...

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 Always consider explain query plan to determine whether your queries will be slow.

...

*Application.java → Add the register table names

Code Block

language	java
theme	Eclipse

private String[] getFtsTables(){
    String[] ftsTables = { "household" ... };
    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;
    } ...
}

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

Add condition fields 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;
    } ...
}

In onCreateIn *Application.onCreate(), update the global context instance to have the values specified above.i.e. update context.commonFtsObject

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 classorg.ei.opensrp.Context.java, 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 databasein org.ei.opensrp.repository.Repository#onCreate, 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.
For Example, In FormSubmissionService class:
Code Block
language java
ziggyService.saveForm(getParams(submission), submission.instance()); // Update FTS Tables updateFTSsearch(submission);
s

...

Versions Compared

Old Version 5

New Version 6

Key