Querybuilder filter design document

From PTAGISWiki

Jump to: navigation, search

Contents

Overview

The custom-built querybuilder component of the PTAGIS website allows users to design their own reports against the database. There are a few constraints that we use to keep the database from being overwhelmed. Initially, the only constraint was that queries had to be limited to a single-year span of First Observation Date or Observation Date (depending on the dataset). We would like to allow broader queries as long as we can keep a safe load on the database.

Allowing different query constraints

Our database has been recently tuned to perform well given queries based on HUC Code and Migration Year. The goal is to allow queries that meet the original requirement of First Obs Date OR queries that specify (Migration Year or Release Date) and (HUC Code or Release Site) OR specify a Tag ID. At some point we will probably want to determine how many Migration Years or HUC Codes are allowed within a query, but for now, any non-null filters on those fields will be allowed. (A user could request all migration years and a very broad HUC definition and thereby cause significant load on the database.)

Required set 1: (bit 1024)

First observation date (1024 + 516 + 16 = 1552) field #16

Required set 2: (bit 2048)

HUC code (256 + 2048 = 2304) field #10
Release site (2048) field #6

Required set 3: (bit 4096)

Migration year (4096) field #7
Release date (32 + 4096 = 4128) field #5

Required set 4: (bit 8192)

Tag ID (65 + 8192 = 8257) field #1

A query must have a filter on required set 1 OR a filter on one field from required set 2 and one field from required set 3 OR a filter on required set 4.

Design details

The code that enforces the querybuilder constraints is contained in setFilters.jsp, setProperties.jsp, QueryBuilder.java, and the wqb_fields metadata table.

The First Observation Date and Observation Date fields have value of 1552 in wqb_fields for their "validation". The bit mask that indicates that the fields are required is 1024, as specified in QueryBuilder.java: 

  /**
   * The enumeration representing the different validations.
   * They can be OR'd together
   * These are stored in the "validation" field of the "wqb_fields" table.
   */
  public static final int VALIDATION_NONE = 0;
  public static final int VALIDATION_REGEX_TAGID = 1;
  public static final int VALIDATION_REGEX_TAGFILEID = 2;
  public static final int VALIDATION_REGEX_HUCCODE = 4;
  public static final int VALIDATION_REGEX_RIVERKM = 8;
  public static final int VALIDATION_DATE = 16;
  public static final int VALIDATION_DATETIME = 32;
  public static final int VALIDATION_MAKEUPPER = 64;
  public static final int VALIDATION_INT = 128;
  public static final int VALIDATION_LIST_SIZE = 256; //limit a list by the number specified in validation_data
  public static final int VALIDATION_DATE_DIFF1YEAR = 512;
  public static final int VALIDATION_REQUIRED = 1024;
  public static final int VALIDATION_REQUIRED2 = 2048;
  public static final int VALIDATION_REQUIRED3 = 4096;
  public static final int VALIDATION_REQUIRED4 = 8192;

The validation bit is checked for each field and if it is present, the field is colored to mark it as required.

Before the filters can be accepted on a query, the required fields are checked. Certain fields require a start and a stop value (sFiltValA and sFiltValB in the code below). Other fields can be filtered with a single value. Each required field is validated appropriately, based on the web form type. If any required field is null, the form is prevented from being submitted. This current design enforces the presence of filters on all required fields within a set. Changing this to allow a filter on one field within a set will require a new data structure to track the fulfillment of the required fields.

The following code snippet peforms this check and is from setFilters.jsp and setProperties.jsp: 

for(int i=0; i<nFldCnt; i++){
  String sFldName= QueryBuilder.getString(mFields[i],"name","");
  int nInputType= QueryBuilder.getInt(mFields[i],"input_type",QueryBuilder.INPUT_GUI_SINGLE_TEXTBOX);
  int nValidation= QueryBuilder.getInt(mFields[i],"validation",QueryBuilder.VALIDATION_NONE);
  boolean bRequired= ( (nValidation & QueryBuilder.VALIDATION_REQUIRED) != 0);
  if (bRequired){
    String sFiltValA= oQueryData.getFiltValA(i);
    String sFiltValB= oQueryData.getFiltValB(i);
    switch(nInputType){
      case QueryBuilder.INPUT_GUI_SINGLE_TEXTBOX:
      case QueryBuilder.INPUT_GUI_DOUBLE_TEXTBOX:
      case QueryBuilder.INPUT_GUI_SINGLESELECT:
      case QueryBuilder.INPUT_GUI_MULTISELECT:
      case QueryBuilder.INPUT_GUI_FILTERED_MULTISELECT:
        if (sFiltValA.equals("")){
          bRequiredValidated= false;
          sRequiredFields= sRequiredFields+"\""+sFldName+"\" ";
        }
      break;
      case QueryBuilder.INPUT_GUI_TAGCODE:
        if (sFiltValA.equals("") && sFiltValB.equals("")){
          bRequiredValidated= false;
          sRequiredFields= sRequiredFields+"\""+sFldName+"\" ";
        }
      break;
    }
  }
}

The above code simple walks through the list of fields, checks to see if each field is required, and if it is, checks for the presence of a filter on that field. Failure to have a filter on a required field causes the bRequiredValidated boolean to be set to false, which causes the query build to stop.

If one filter in a set is sufficient, then we must determine sufficiency after examining all the fields, but note the presence of filters on each field as we go. This requires a data structure for the number of fields with filters within each required set.

One way to implement this is to keep a running count of the number of filters within each required set: 

int nRequiredFilters1 = 0;
int nRequiredFilters2 = 0;
int nRequiredFilters3 = 0;
int nRequiredFilters4 = 0;

The following code snippet causes the required fields to be displayed in a distinct way: 

  boolean bRequired= ( (nValidation & QueryBuilder.VALIDATION_REQUIRED) != 0);
  String sFldReqText= ((bRequired)?"(required)": "");
  String sFldHighlightStart= ((bRequired)?"<span style='color: red'>": "");
  String sFldHighlightEnd= ((bRequired)?"</span>": "");

Change description

In order to allow more complex filter logic, we need more bits in the bitmask. 

VALIDATION_REQUIRED2 = 2048;
VALIDATION_REQUIRED3 = 4096;
VALIDATION_REQUIRED4 = 8192;

Each set of required fields should be painted a different color. The logic that sets the bRequiredValidated variable should allow the form to proceed if EITHER a filter is present on the original set of fields (First Observation Date) OR a filter is present on the new set of required fields ((HUC or Release Site) and (Migration Year or Release Date)) or a filter is present on Tag ID. To accomplish this, four new variables are needed to indicate the fulfillment of the sub-requirements for the four sets of required fields. The original bRequiredValidated variable still indicates whether or not the processing can move forward, but it is now set according to the state of bRequiredValidatedSub1, bRequiredValidatedSub2, bRequiredValidatedSub3, and bRequiredValidatedSub4 like so: 

if (bRequiredValidatedSub1 || (bRequiredValidatedSub2 && bRequiredValidatedSub3) || bRequiredValidatedSub4) {
  bRequiredValidated = true;
} else {
  bRequiredValidated = false;
}

The logic that sets each bRequiredValidatedSubx variable needs to change to allow it to be true if at least one member of the set has a filter, instead of requiring all members of the set to have filters. The new code for one set would look like this: 

  boolean bRequired3= ( (nValidation & QueryBuilder.VALIDATION_REQUIRED3) != 0);
  if (bRequired3){
    String sFiltValA= oQueryData.getFiltValA(i);
    String sFiltValB= oQueryData.getFiltValB(i);
    switch(nInputType){
      case QueryBuilder.INPUT_GUI_SINGLE_TEXTBOX:
      case QueryBuilder.INPUT_GUI_DOUBLE_TEXTBOX:
      case QueryBuilder.INPUT_GUI_SINGLESELECT:
      case QueryBuilder.INPUT_GUI_MULTISELECT:
      case QueryBuilder.INPUT_GUI_FILTERED_MULTISELECT:
        if (sFiltValA.equals("")){
          //bRequiredValidatedSub3= false;
          sRequiredFields= sRequiredFields+"\""+sFldName+"\" ";
        } else {
          nRequiredFilters3++;
        }
      break;
      case QueryBuilder.INPUT_GUI_TAGCODE:
        if (sFiltValA.equals("") && sFiltValB.equals("")){
          //bRequiredValidatedSub3= false;
          sRequiredFields= sRequiredFields+"\""+sFldName+"\" ";
        } else {
          nRequiredFilters3++;
        }
      break;
    }
  }

The new variable nRequiredFiltersx is an integer that counts the number of filters present within the given set. This variable directly relates to the bRequiredValidatedSubx variables like so: 

if (nRequiredFilters1 < 1) {
  bRequiredValidatedSub1 = false;
}
if (nRequiredFilters2 < 1) {
  bRequiredValidatedSub2 = false;
}
if (nRequiredFilters3 < 1) {
  bRequiredValidatedSub3 = false;
}
if (nRequiredFilters4 < 1) {
  bRequiredValidatedSub4 = false;
}

Each additional set of required fields would need another bit set in the mask to be able to uniquely identify it and an additional bRequiredValidatedSubx variable to track its state.

Changes to the instructions on how to construct a query need to be made in the JSP and in the wqb_fields table.

The following table could be part of the instructions:

First Observation Date
OR
Release Site Release Date
HUC Code Migration Year
OR
Tag ID

Moving left to right, either apply a filter to a red field or apply a filter to one blue field and one green field.

Retrieved from "http://php.ptagis.org/wiki/index.php/Querybuilder_filter_design_document"
Personal tools