HOWTO - Sashipa development

SelectQueryBuilder.

You can find here general information about the selectQueryBuilder element.

Skeleton

A selectQueryBuilder element creates SQL select queries when the application is runing. The same element can define a complete query builder, or can be used for completing another selectQueryBuilder already created by a component. So it isn't always complete. For example, the Select statement is never specified in a listForm query because the listForm defines it.

Here is the skeleton of the selectQueryBuilder element:

  <selectQueryBuilder distinctRequired='no' type='list'>
    <castFilterSet>
      ...
    </castFilterSet>
    <selectStatementBuilder>
      ...
    </selectStatementBuilder>
    <fromStatementBuilder>
      ...
    </fromStatementBuilder>
    <whereStatementBuilder>
      ...
    </whereStatementBuilder>
  </selectQueryBuilder>

There are two attributes.

distinctRequired: the default value is 'no'. It can be set to 'yes' for adding the "DISTINCT" keyword in the select statement or for generating a SQL "GROUP BY" statement if needed. NB: The query can be an agregate query even when distinctRequired is set to 'no'. For example by adding an agregate column in the Select statement.
type: There are three types of select query. list for queries that return several rows. card for queries that return one row for cardForm, and agregat for queries that return one row too but with behaviour of list queries (for default parameters of castFilter, ...). Most of time this attribute can be deducted when compiling and you haven't to specify it.

castFilters

A query is filtered (or not) with the filter of the current screen. A filter is a primary key value. A castFilter builds a criterion with the current filter, and adds it in the SQL Where statement. Built criterion are like 'pk_columns = filter', or 'fk_columns = filter' or a composition of sub-castFilters.

A list of castFilters is defined for the query. When the query receives a filter, it tries to match the first castFilter with the filter. If the first castFilter doesn't match, then query tries with the second, etc..

Here is an example:

  <castFilterSet filterSensitive='yes' autoCastFilter='yes'
                 stuckWhenNoFilter='no'>
    <fkCastFilter>
      <instanceFk schemaFk='fk_tblTravel_tblPerson' />
    </fkCastFilter>
    <pkCastFilter>
      <instanceTable schemaTable='tblTravel' />
    </pkCastFilter>
  </castFilterSet>

castFilterSet attributes are optional. In this example, they have all their default value.

filterSensitive: Can be set to 'no' for ignoring all castFilters (and then filters).
autoCastFilter: For each table in the from statement, list and agregat queries automatically add primary key castFilter and foreign keys castFilters (card queries just add the primary key castFilter of the main table). This effect can be disabled by setting autoCastFilter to 'no'.
stuckWhenNoFilter: Sometimes there isn't any available filter for the query (if screen hasn't any filter or if there isn't a castFilter for the current filter). By default, a query without filter is run without the criterion of the filter in the Where statement. If this attribute is set to 'yes', the query will be run only when there is a filter. Otherwise, the query will return an empty list.

Select statement

A Select statement contains column instances, group of column instances or SQL expressions. Here is a select statement that contains one element of each type. All work with a from statement contains 'tblPerson' as mainInstanceTable value.

  <selectStatementBuilder autoPrimaryKey='yes'>
    <instanceColumnList>
      <instanceColumn schemaColumn='tblPerson_Name' />
      <referencedUserKeyColumns join='inner'>
        <instanceFk schemaFk='fk_tblPerson_tblCountry' />
      </referencedUserKeyColumns>
      <agregatInstanceColumn type='count' schemaFk='fk_tblTravel_tblPerson'
                             letterCount='6' sort='desc'>
        <title>Travels</title>
      </agregatInstanceColumn>
      <instanceFk schemaFk='fk_tblPerson_tblSex' />
      <manualColumn>                 <!-- MySQL syntax -->
        <schemaColumnRef schemaColumn='tblPerson_NumeroInYear' />
        <contentOfExpression>ifnull(max(Numero_In_Year), 0)+1</contentOfExpression>
      </manualColumn>
    </instanceColumnList>
  </selectStatementBuilder>

In 'list' queries, primary key columns are automatically added in the Select statement. This can be disabled by setting the autoPrimaryKey attribute to 'no'.

An instanceColumn is a column of an instanceTable. So the element instanceColumn contains the instance table container, and the instance table must be into the from statement too. When the instanceTable element is omitted then the default instance of the schemaTable is used.

A referencedUserKeyColumns element adds column instances of a table userKey AND add a join in the From statement.

A agregatInstanceColumn element creates a computed column. Available types are:

'count': the foreign key has to be specified. The join will be automatically added in the from statement.
'countStar': for a SQL 'count(*)'.
'sum', 'avg', 'min', 'max': An instanceColumn has to be specified, and a join has to be manually added in the from statement (See an example here).

A instanceFk element adds instance columns contained in a foreign key.

A SQL expression can be manually written in element manualColumn. When the element is used in the query for a default value in field of cardForm, the schemaColumnRef element must be the same as the field one. Be carefull: using this element can make your Sashipa code specific for only one dbms...

From statement

A From statement contains an element mainInstanceTable and a list of fkJoin for adding other tables:

  <fromStatementBuilder>
    <mainInstanceTable schemaTable='tblPerson' />

    <fkJoin join='inner'>
      <instanceFk schemaFk='fk_tblPerson_tblSex' />
    </fkJoin>

    <fkJoin join='left'>
      <instanceFk schemaFk='fk_tblPerson_tblCountry_Live'>
        <startInstanceTable schemaTable='tblPerson' />
      </instanceFk>
      <referencedInstanceTable schemaTable='tblCountry' nickName='countryLive' />
      <XxxCriteriaXxx> ... </XxxCriteriaXxx>
    </fkJoin>

    <fkJoin join='inner'>
      <instanceFk schemaFk='fk_tblPerson_tblCountry_Originate'>
        <startInstanceTable schemaTable='tblPerson' />
      </instanceFk>
      <referencedInstanceTable schemaTable='tblCountry' nickName='countryOrig' />
    </fkJoin>
  </fromStatementBuilder>

Each instance of table (elements mainInstanceTable, startInstanceTable, referencedInstanceTable) has a required attribute schemaTable, and an optional nicname.

Joins (elements fkJoin) have an attribute for the type (inner, left, right, full, nojoin). 'left' is more often used when the foreign key can be null, and 'inner' is used when the foreign key has always a value.

In an element fkJoin, instances of tables are optional because they can be deducted from the foreign key. So we define tables that need to be renamed in the SQL query. So in the example we could remove the startInstanceTable.

The criterion is optional too. It is added to the join criterion with a SQL 'AND'. Criteria are described below in the Where section.

Where statement

A Where statement contains a set of constantCriteria, manualCriteria, and/or criteriaBuilders. The separator is always a SQL 'AND'. For adding criteria with SQL OR, just put them in a criteriaBuilder with mode='or'.

Where statement: constantCriteria

You can manually specify a SQL criterion based on a column, for example:

  <whereStatementBuilder>
    <constantCriteria>
      <instanceColumn schemaColumn='tblContactaddress_ContactAddressLabel' />
      <endOfCriteria>is not null</endOfCriteria>
    </constantCriteria>
  </whereStatementBuilder>

The generated criterion will be the concatenation of the column instance and the content of element endOfCriteria.

The endOfCriteria content is open. But be carefull if you need to change your dbms later...

Where statement: manualCriteria

You can manually define a SQL criterion, for example:

  <whereStatementBuilder>
    <manualCriteria>
      <contentOfCriteria>ContactAddressLabel is not null</contentOfCriteria>
      <instanceColumnList>
        <instanceColumn schemaColumn='tblContactaddress_ContactAddressLabel' />
      </instanceColumnList>
    </manualCriteria>
  </whereStatementBuilder>

Remarks :

Content of element contentOfCriteria will be put in the SQL query as it's written.
The endOfCriteria content is open. But be carefull if you need to change your dbms later...
The list of instanceColumns must contains all columns that are used in the criterion. This list allows the query to know if its result has to be refreshed when a data is updated.

Where statement: criteriaBuilders

A criteriaBuilder is a criterion that contains a set of sub-criteria.

  <whereStatementBuilder>
    <criteriaBuilder mode='and' emptyAction='stuck'
                     defaultSubEmptyAction='ignore'>

      ... set of sub-criteria ...

    </criteriaBuilder>
  </whereStatementBuilder>

There are three attributes:

mode can take 'or' or 'and' as value. It's the SQL separator between sub-criteria.
emptyAction can be set to 'ignore' or 'stuck'. When the criteriaBuilder is empty, the parent will be stuck or will ignore the criterion (the parent is the query or another criteriaBuilder).
defaultSubEmptyAction can be set to 'ignore', 'stuck' or 'accepte'. This attribute defines the default emptyAction for sub-researchCriteria. The value 'accepte' means that sub-researchCriteria will be built even from undefined values (for building criterion like 'my_column is null').

Where statement: researchCriteria

A researchCriteria is a dynamic criterion that will be built from a value of a field. Due to a limitation of Melba, a researchCriteria has to be contained by a criteriaBuilder. In the example are two researchCriteria: the first one takes value in a fkField, the second one takes value in a simple field:

  <whereStatementBuilder>
    <criteriaBuilder mode='and' emptyAction='stuck'
                     defaultSubEmptyAction='ignore'>

      <researchCriteria emptyAction='stuck'>
        <castSchemaValue>
          <fkCastFilter>
            <instanceFk schemaFk='fk_tblPerson_tblSex' />
          </fkCastFilter>
        </castSchemaValue>
        <fieldRef field='fieldFkSex' />
      </researchCriteria>

      <researchCriteria>
        <castSchemaValue>
          <instanceColumn schemaColumn='tblPerson_Name' />
        </castSchemaValue>
        <fieldRef field='fieldPersonName' />
      </researchCriteria>
      
    </criteriaBuilder>
  </whereStatementBuilder>

A researchCriteria element contains a castSchemaValue and a reference to a field defined in the same screen. This field has to be defined (in a cardForm or a researchForm for example) and named before to be referenced.

A castSchemaValue can work on primary keys or foreign keys (with a castFilter), or on a simple column (with a instanceColumn).

In this example, the first researchCriteria requires a value (emptyAction='stuck') and the criteriaBuilder will be stuck if 'fieldFkSex' doesn't return a value. The second researchCriteria will be ignored if the field 'fieldPersonName' is empty.

Top