IRS And Query Builders
In order to help us learn how this system works better, lets follow it through with an example use case:
A QueryBuilder query can be constructed (and then executed) by invoking the construct() method. This action is broken down into three main steps:
This phase is dominated by the powerful ValueQueryParser, provided by Runway SDK, which reads the XML configuration file sent from the client to populate an initial ValueQuery. Here is the XML sent from the client for our example use case:
xml version="1.0" encoding="UTF-8"?> dss.vector.solutions.irs.InsecticideBrand dss.vector.solutions.irs.InsecticideBrand /> dss.vector.solutions.irs.AbstractSpray dss.vector.solutions.irs.AbstractSpray /> sprayteam_defaultLocale sprayteam_defaultLocale Spray team false activeIngredient_displayLabel activeIngredient_spray Active ingredient false DATEGROUP_EPIWEEK dategroup_epiweek Epi week false /> /> />
Aggregation Level
The ValueQuery WHERE criteria pertaining to aggregation level is set from the incoming XML via the <criteria> tag. The IRS codebase contains a class called CriteriaInterceptor which is used to detect the current aggregation level, and the IRSQB hasLevel1 will read it from the CriteriaInterceptor. These aggregation levels refer to whether or not the end query includes data from this level. If the user does not specify any levels, then all levels are included.
This is where the real magic happens and it all centers around the requiredAliases IRS variable. requiredAliases maintains a current list of all the columns to be included in the final query, and the columns are grouped depending on which view they are associated with. In our current example, this is what the requiredAliases variable gets set to:
{ resourceTargetView= [ ], sprayView= [ id, sprayteam_defaultLocale, dategroup_epiweek, brand, spray_date, disease, activeIngredient_spray ], plannedTeamRollup= [ ], insecticideView= [ ], plannedOperator= [ ], plannedArea= [ ], plannedTeamResults= [ ], spraySummaryView= [ ], geoTargetView= [ ], dateGroups= [ dategroup_epiweek, spray_date ], dateExtrapolationView= [ ], plannedTeam= [ ], allActuals= [ id, sprayteam_defaultLocale, dategroup_epiweek, brand, spray_date, disease, activeIngredient_spray ] }
This is the JSON representation of the in memory java HashMap that this variable gets set to. Each one of these arrays in the hash map is the representation of a View that will get generated and used to build our final SELECT statement.
About allActuals
In the system the recording phase (as opposed to planning) is often referred to actuals, so the The AllActuals View is calculated from the aggregates of spray data from levels 1, 2 and 3. When the user selects, for example, epi_week, the system builds a column (spray_date) in the AllActuals view which is the aggregated spray_date from all levels. This means, that in the final resultant SELECT statement, the spray_date is selected from the AllActuals view which includes the spray_date from all spray levels. This AllActuals table contains both spray activity (number of houses sprayed, spray cans used, etc.) and spray targets (targets set on the spray date).
ResourceTargetView
This view aggregates the resource_target table, converting the targets for each epi week into a view with the relevant data. The resource_target table contains planning targets. Planning targets are typically set at the beginning of a spray season.
DateGroup
This view aggregates dates from all 3 spray levels.
plannedTeamRollup
PostProcess
While the post processing phase is defined for all QueryBuilders, it is only overridden by the IRS query builder. The postProcess function is segregated into 3 different sections: operatorAggregation, teamAggregation, and areaAggregation. If the query contains selectables for one of these levels then a new IRSQB is instiantiated and used to aggregate the planning targets. This new IRSQB pulls from the planning table (resource_target, aggregated into ResourceTargetView). The initial IRSQB is then modified to only contain the actuals (targets and activity) and it is then joined with the initial IRSQB, effectively joining the planning with the actuals.
WITH RECURSIVE geo_displayLabel AS (
SELECT geo.id, geo_id, COALESCE( geoLabel.m_alaria_default_locale, geoLabel.default_locale) || ' (' ||
COALESCE( typeLabel.m_alaria_default_locale, typeLabel.default_locale) ||
COALESCE( ' : ' || termLabel.m_alaria_default_locale, ' : ' || termLabel.default_locale, '') || ')' AS label
FROM
geo_entity geo
INNER JOIN geo_entity_entity_label geoLabel ON geo.entity_label = geoLabel.id
INNER JOIN md_type md ON geo.type = (md.package_name || '.' || md.type_name)
INNER JOIN metadata_display_label typeLabel ON md.display_label = typeLabel.id
LEFT JOIN term AS term ON term.id = geo.term
LEFT JOIN term_term_display_label AS termLabel ON termLabel.id = term.term_display_label
),
insecticideView AS (
SELECT insecticide_brand.id,
COALESCE(start_date,'1900-01-01'::date) start_date,
insecticide_brand.disease disease,
COALESCE(end_Date,'2100-01-01'::date) end_date,
unit_quantifier*(concentration_quantifier/100.0) AS active_ingredient_per_can,
enum_name spray_unit,
(SELECT default_locale FROM metadata_display_label md WHERE enumeration_master.display_label = md.id) targetUnit_displayLabel,
(CASE WHEN enum_name = 'ROOM' THEN room WHEN enum_name = 'STRUCTURE' THEN structure_area WHEN enum_name = 'HOUSEHOLD' THEN household END ) AS unit_area,
unit_nozzle_area_coverage unit_nozzle_area_coverage,
((unit_quantifier*units_per_application*(concentration_quantifier/100.0)) / unit_nozzle_area_coverage ) AS standard_application_rate,
(1000.0 * (unit_quantifier*units_per_application*(concentration_quantifier/100.0)) / unit_nozzle_area_coverage ) AS standard_application_rate_mg,
unit_nozzle_area_coverage/(CASE WHEN enum_name = 'ROOM' THEN room WHEN enum_name = 'STRUCTURE' THEN structure_area WHEN enum_name = 'HOUSEHOLD' THEN household END ) AS units_per_can
FROM area_standards AS area_standards,
insecticide_brand AS insecticide_brand,
enumeration_master enumeration_master
WHERE
enumeration_master.id = target_unit_c
),
dateGroups AS (
-- GENERATED BY: DateGroups
SELECT
get_epiWeek_from_date(spray_date,0) ::varchar AS dategroup_epiweek,
spray_date ::date AS spray_date
FROM
(
SELECT DISTINCT spray_date, disease FROM abstract_spray a INNER JOIN
(
SELECT id, disease FROM
(
SELECT id, disease ::varchar AS disease FROM operator_spray
UNION ALL
SELECT id, disease ::varchar AS disease FROM zone_spray
UNION ALL
SELECT id, disease ::varchar AS disease FROM team_spray
) levels
) diseases ON diseases.id = a.id
) date_joined
),
allActuals AS (
-- GENERATED BY: ActualOperatorSprayTarget
-- LEVEL: 1
SELECT
operator_spray.id ::varchar AS id,
spray_team.team_id ::text AS sprayteam_defaultLocale,
brand ::varchar AS brand,
spray_date ::date AS spray_date,
operator_spray.disease ::varchar AS disease
FROM
operator_spray AS operator_spray
INNER JOIN abstract_spray AS abstract_spray ON abstract_spray.id = operator_spray.id
AND operator_spray.disease = '0kur3nsmxnij1l1v5kihis3irfbau8ooitvozp9y4b6h11bqy4ue95sdl815u410'
LEFT JOIN spray_team AS spray_team ON spray_team.id = operator_spray.spray_team
UNION ALL
-- GENERATED BY: ActualTeamSprayTarget
-- LEVEL: 2
SELECT
team_spray.id ::varchar AS id,
spray_team.team_id ::text AS sprayteam_defaultLocale,
brand ::varchar AS brand,
spray_date ::date AS spray_date,
team_spray.disease ::varchar AS disease
FROM
team_spray AS team_spray
INNER JOIN abstract_spray AS abstract_spray ON abstract_spray.id = team_spray.id
AND team_spray.disease = '0kur3nsmxnij1l1v5kihis3irfbau8ooitvozp9y4b6h11bqy4ue95sdl815u410'
INNER JOIN spray_team AS spray_team ON team_spray.spray_team = spray_team.id
UNION ALL
-- GENERATED BY: ActualZoneSprayTarget
-- LEVEL: 3
SELECT
zone_spray.id ::varchar AS id,
spray_team.team_id ::text AS sprayteam_defaultLocale,
brand ::varchar AS brand,
spray_date ::date AS spray_date,
zone_spray.disease ::varchar AS disease
FROM
zone_spray AS zone_spray
INNER JOIN abstract_spray AS abstract_spray ON abstract_spray.id = zone_spray.id
AND zone_spray.disease = '0kur3nsmxnij1l1v5kihis3irfbau8ooitvozp9y4b6h11bqy4ue95sdl815u410'
LEFT JOIN team_spray_status AS team_spray_status ON
zone_spray.id = team_spray_status.spray
INNER JOIN spray_team AS spray_team ON team_spray_status.spray_team = spray_team.id
),
sprayView AS (
-- GENERATED BY: ActualJoin
SELECT
a.id /*[regular]*/,
a.sprayteam_defaultLocale /*[regular]*/,
dateGroups.dategroup_epiweek ::varchar AS dategroup_epiweek /*[regular]*/,
a.brand ::varchar AS brand /*[regular]*/,
a.spray_date ::date AS spray_date /*[regular]*/,
a.disease /*[regular]*/
FROM
allActuals a LEFT JOIN dateGroups ON dateGroups.spray_date = a.spray_date
GROUP BY
id,sprayteam_defaultLocale,dategroup_epiweek,brand,a.spray_date,disease
)
SELECT
(sprayteam_defaultLocale) AS sprayteam_defaultLocale,
(activeIngredient_displayLabel) AS activeIngredient_displayLabel,
((get_epiWeek_from_date(abstract_spray_2.spray_date,0))) AS DATEGROUP_EPIWEEK,
(count(*) over()) AS dss_vector_solutions__window_count
FROM (SELECT NULL) spoofTable ,
sprayView abstract_spray_2 LEFT JOIN insecticideView insecticideView ON abstract_spray_2.brand = insecticideView.id AND
insecticideView.disease = abstract_spray_2.disease
AND ((abstract_spray_2.spray_date) >= (insecticideView.start_date)
AND (abstract_spray_2.spray_date) <= (insecticideView.end_date))
,
(
SELECT insecticide_brand.id AS id, term0.name AS activeIngredient_displayLabel
FROM insecticide_brand AS insecticide_brand
LEFT JOIN term AS term0 ON insecticide_brand.active_ingredient = term0.id
) insecticide_brand_3_active_ingredient,
insecticide_brand insecticide_brand_3
WHERE insecticide_brand_3.id = insecticide_brand_3_active_ingredient.id
AND insecticide_brand_3.id = (abstract_spray_2.brand)
Its important to note that when running these queries in PGAdmin, you need to set the search path before running them:
set search_path TO ddms;
This is because the data lives within the ddms schema.