Querybuilder filter rules

From PTAGISWiki

Currently the validation of Querybuilder filters is done without regard to the datasource being used. However, different datasources cause different loads on the database. For instance, Interrogation Detail needs to be tightly constrained because it can return very large result sets. On the other hand, Mort Details returns relatively small result sets.

How many records are in each datasource?

There should be a way to tailor the validation constraints to the requirements of each datasource.

Datasource	Constraint
Interrogation Detail	1 site, 1 year
Tagging Detail	no constraint
Interrogation Summary	existing rules
Recapture Details	inner join against tag details
Mort Details	no constraint
Recapture Information	no constraint

Giving certain datasources an exemption from any constraints is the easiest way forward. This could be done in setFilters.jsp and setProperties.jsp. The current datasource can be determined from this variable:

int nDsId= oQueryData.getDsId();^M

The addition of a new variable could track which datasource requires constraints:

boolean bDsConstrained = (nDsId == 1 || nDsId == 3);

The validation calculation then becomes:

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

As we add more datasource-specific constraints, the validation calculation will become more like a switch statement like this:

switch (nDsId) {
  case 1 :
    if (bRequiredValidatedSub1 || bRequiredValidatedSub4) {
       bRequiredValidated = true;
    } else {
       bRequiredValidated = false;
    }
  case 2 :
    bRequiredValidated = true;
  case 3 :
    bRequiredValidated = true;
  ...

The highlighting of fields and the instructions can be masked if the datasource is not constrained like so:

<% if (bDsConstrained) { %>
Do one or more of the following:
<ul>
<li>Apply a filter to a red field.
<li>Apply a filter to at least one blue field and at least one green field.
<li>Apply a filter to an orange field.
</ul>

The datasources are kept in the database in the table wqb_datasources which looks like this:

ID	Name	Description	db_view
1	Interrogation Detail
2	Tagging Detail
3	Interrogation Summary
4	Recapture Details
5	Mort Details
6	Recapture Information

Allowing a filter to be applied on a field

Some fields such as Recapture Release Site (field id #62) do not allow filtering to be applied. This is determined by the Input_type which is set to 0 for Recapture Release Site.

To make the Recapture Release Site behave like the Recapture Site (field id #65) the following changes to the record for Recapture Release Site should be made:

Field	Current value	New value
instructions		Select one or more sites to restrict your query.
input_type	0	4
value_list	0	1
valuelist_data		SELECT trim(site) AS value, trim(site)+' - '+trim(loc_name) AS text FROM rkm