Basic META SQL

Table of Contents

META SQL
Dynamic input values
Static input values
Embedded mapping rules
META SQL statements
Conditional SQL fragment IF
Advanced conditional SQL fragment IF
Conditional SQL fragment AND
Advanced conditional SQL fragment AND
Conditional SQL fragment OR
Advanced conditional SQL fragment OR
Conditional SQL fragment OPT
Advanced conditional fragment OPT
Special fragment WHERE
Special fragment VALUES
Special fragment COLUMNS
Special fragment SET
Ordering SQL fragment

META SQL

META SQL is an ANSI SQL extension based on ANTLR defined grammar. Based on data provided by a user (or an application) the final SQL statement is generated. From the point of view of SQL Processor the SQL statement is a stream of text. In this text special patterns are identified and processed. A META SQL statement definition itself begins with a left curly bracket { and ends with a right curly bracket }. The input values are identified in a text stream with a colon character : or a dollar sign $. The output mapping rules are identified in a text stream with an at sign @. In the SQL Processor 1.6 there are two more control characters - % and %%. These are use by the SQL Processor Eclipse plugin. In the runtime they are ignored. In the SQL Processor 2.2 there are two more control characters - :: and $$. These are used for SQL operators.

The SQL Processor query definition as a stream of a text is shown here:

Special characters and tokens are the next ones:

• COLON is a colon character : - identifies a dynamic input value (=identifier)
• STRING is a dollar sign $ - identifies a static input value (=constant)
• COLON COLON - identifies a dynamic SQL operator (=identifierOperator)
• STRING STRING - identifies a static SQL operator (=constantOperator)
• AT is an at sign @ - identifies an output mapping rule (=column)
• PERCENT is a percent character % - identifies a database column (=dbcolumn)
• PERCENT PERCENT - identifies a database table (=dbtable)
• LBRACE is a left curly bracket { - identifies the beginning of a META SQL statement (=metaSql)
• RBRACE is a right curly bracket } - identifies the end of a META SQL statement (=metaSql)
• AND...WS are all ASCII characters except the characters defined above. In fact they form a fragment of the final SQL statement.

So the SQL Processor statement is an extended ANSI SQL statement. This enhancement consists of input values (dynamic or static ones), dynamic operators (dynamic or static ones), output mapping rules and META SQL statements. For example the query

QUERY(QRY)=
  select p.ID @id, $-name.first @firstName, :+name.last @lastName { , :ssn @ssnum} from PERSON p
;

• select p.ID id, is a query fragment, the at sign is deleted from this fragment
• $-name.first is a static input value
• @firstName, is a query fragment (and a mapping rule), the at sign is deleted from this fragment
• :+name.last is a dynamic input value
• @lastName is a query fragment (and a mapping rule), the at sign is deleted from this fragment
• { , :ssn @ssnum} is a META SQL statement (of type OPT)
• from PERSON p is a query fragment

Dynamic input values

A dynamic input value is simply a value of an attribute from a search form. This value is bounded to a JDBC prepared statement, which is created from the final SQL statement. The input value can have an influence, how the final SQL statement is generated. In the case its occurrence is outside of a META SQL statement (so outside of a text between curly brackets), it must be non-empty and

• it can be located in the SELECT part of the SQL query. In this case the value is counted into the result of an SQL query SELECT as a virtual column. For example in the previously quoted QUERY there's a virtual column :+name.last created from an input value.
• it can be located in the WHERE, SET or UPDATE part of the SQL statement. In this case the value is bounded to a JDBC prepared statement in a standard way.

The non-emptiness of a dynamic input value depends on the Java type of the related attribute

• for a String it must be not null and the length must be >0
• for a Collection it must be not null and the size must be >0
• for the rest Java types it must be not null
• an enumeration based attribute is treated in a special way, please see the advanced tutorial

The isNotEmpty() method in the SQLP plugins is responsible for the emptiness detection.

The schema of a dynamic input value (=identifier) is the next one:

Special characters and tokens are:

• PLUS is a plus character + - means the input value is converted to upper-case
• MINUS is a minus character - - means the input value is converted to lower-case
• IDENT is a Java identifier. It's the name of the attribute in a search form.
• IDENT_DOT is a complex Java identifier. It's the name of the embedded attribute in a search form.

Static input values

A static input value is simply a value of an attribute from a search form. This value is embedded directly into the final SQL statement. All the SQL Processor Engine main methods can have one search form for dynamic input values and one search form for static input values as parameters. Obviously a search form for static input values can't be filled by an end user (to protect a database against SQL query injection). So this form is used mainly by an application layer in a web application for example. In the case its occurrence is outside of a META SQL statement (so outside of a text between curly brackets), it must be non-empty and

• it can be located in the SELECT part of the SQL query. In this case the value is counted into the result of an SQL query SELECT as a virtual column. For example in the previously quoted QUERY there's a virtual column $-name.first created from an input value.
• it can be located in the WHERE, SET or UPDATE part of the SQL statement. In this case the value is embedded into the final SQL statement.

The non-emptiness of a static input value depends on the Java type of the related attribute

• for a String it must be not null and the length must be >0
• for a Collection it must be not null and the size must be >0
• for the rest Java types it must be not null
• an enumeration based attribute is treated in a special way, please see the advanced tutorial

The isNotEmpty() method in the SQLP plugins is responsible for the emptiness detection.

The schema of a static input value (=constant) is the next one:

Special characters and tokens are:

• PLUS is a plus character + - means the input value is converted to upper-case
• MINUS is a minus character - - means the input value is converted to lower-case
• IDENT is a Java identifier. It's the name of the attribute in a search form.
• IDENT_DOT is a complex Java identifier. It's the name of the embedded attribute in a search form.

Embedded mapping rules

The mapping rules are a prescription how to fill the instances of the result class with the output values from an SQL query execution. In fact, it's a list of database columns or aliases, values of which are seeded into the instances of the result class. These rules can be defined explicitly or implicitly. In the second case they are identified in the META SQL query with the prefix @ followed by the alias/attribute name. More on mapping rules is shown in the separate tutorial.

META SQL statements

A META SQL statement is a text between curly brackets, which is treated in a special way. There are several types of META SQL statements. The schema is the next one:

Special characters and tokens are the next ones:

• QUESTI is a question mark ? - identifies a conditional SQL fragment of type IF (logical expression)
• BAND is an ampersand & - identifies a conditional SQL fragment of type AND (conjunction)
• BOR is a vertical bar | - identifies a conditional SQL fragment of type OR (disjunction)
• EQUALS is an equals sign = - identifies a special SQL fragment. The following IDENT token begins the identification of this special SQL fragment and it can have one of the next values
  • WHERE
  • VALUES
  • SET
• HASH DIGIT is a hash sign # followed by a number - identifies an ordering SQL fragment with the ordering id, which is equal to this number
• all other ASCII characters except the characters defined above – in this case it's one of several possible conditional SQL fragments of type OPT (optional query). These alternative fragments can be separated with a BOR (a vertical bar).

Conditional SQL fragment IF

A logical expression consists of a boolean expression and one or two alternative SQL fragments separated with a BOR (a vertical bar).

A boolean expression is an expression of input values with the following operands between them:

• ! an exclamation mark - for a logical negation
• ( and ) left and right round brackets – for a logical grouping
• && two ampersands – for a logical conjunction
• || two vertical bars – for a logical disjunction

A logical value of an input value (dynamic or static) depends on the Java type of the related attribute. If the value is null, it is evaluated as false. Otherwise it is evaluated as true in the following cases

• Boolean or boolean – a logical value of the related attribute
• any type of Number – if the related attribute value is a positive one
• String - if the related attribute length is greater than zero. A special case is a token false, which is evaluated as false.
• an enumeration based attribute is treated in a special way, please see the advanced tutorial

The isTrue() method in the SQLP plugins is responsible for this detection.

The first alternative SQL fragment will become a part of the final SQL statement in the case that the boolean expression is evaluated as true. The optional second alternative SQL fragment will become part of the final SQL statement in the case that the boolean expression is evaluated as false.

Let's have a query

QUERY(QRY)=
  select m.id, m.TITLE, e.ROLE
  {? :fname | , p.NAME_FIRST first | , '' first}
  {? :lname | , p.NAME_LAST last | , '' last}
  from MEDIA m left join ENGAGEMENT e on e.MEDIA = m.ID
  {? :lname || :fname | left join PERSON p on e.PERSON = p.ID}
  {= where
   {? :lname | AND UPPER(p.NAME_LAST) = :+lname | AND UPPER(p.NAME_FIRST) = :+fname}
  }
;

• in the case the input value :fname is true, the column NAME_FIRST will become a part of the final SELECT (otherwise the value of the alias first is an empty text ``)
• in the case the input value :lname is true, the column NAME_LAST will become a part of the final SELECT (otherwise the value of the alias last is an empty text ``)
• in the case the input value :fname or the input value :lname are true, the table ENGAGEMENT will be joined to the table PERSON
• in the case the input value :lname is true, the condition AND UPPER(p.NAME_LAST) = :+lname will become a part of the final WHERE
• otherwise in the case the input value :lname is false, the condition AND UPPER(p.NAME_FIRST) = :+fname will become a part of the final WHERE

Advanced conditional SQL fragment IF

Every alternative SQL fragment is itself a META SQL conditional fragment (of type OPT). So it's treated based on input values in this META SQL fragment. In the previous example there was the next alternative SQL fragment AND UPPER(p.NAME_FIRST) = :+fname. It contains the input value identified by +fname. If the value is empty, this SQL fragment won't become a part of the final SQL query regardless the value of the related boolean expression. So the last WHERE fragment in this example has these conditions

• in the case the input value :lname is non empty text, the condition AND UPPER(p.NAME_LAST) = :+lname will become a part of the final WHERE
• otherwise in the case the input value :fname is non empty text, the condition AND UPPER(p.NAME_FIRST) = :+fname will become a part of the final WHERE

Conditional SQL fragment AND

An SQL fragment conjunction is an ANSI SQL fragment with embedded input values (static or dynamic ones). It's treated based on the emptiness of these input values. This fragment is divided into segments. Each segment is finished with one input value. If this input value is non-empty, the related segment will become a part of the final SQL statement. The conditional fragment itself is embedded into the final SQL statement with the prefix AND. So this fragment can only be embedded into the WHERE part of the final SQL statement. Let's have a query

QUERY(QRY)=
  select m.id, m.TITLE, e.ROLE, p.NAME_FIRST first, p.NAME_LAST last
  from MEDIA m left join ENGAGEMENT e on e.MEDIA = m.ID
  left join PERSON p on e.PERSON = p.ID
  {= where
   {& UPPER(p.NAME_LAST) = :+lname}
   {& UPPER(p.NAME_FIRST) = :+fname}
  }
;

• in the case the input value :lname is not empty, the condition AND UPPER(p.NAME_LAST) = :+lname will become a part of the final WHERE
• in the case the input value :fname is not empty, the condition AND UPPER(p.NAME_FIRST) = :+fname will become a part of the final WHERE

Advanced conditional SQL fragment AND

An SQL fragment conjunction can consists from several alternative fragments separated with a BOR (a vertical bar). In this case, the first fragment, which has at least one input value non-empty, will be embedded in the final SQL statement. Let's have a query

QUERY(QRY)=
  select m.id, m.TITLE, e.ROLE, p.NAME_FIRST first, p.NAME_LAST last
  from MEDIA m left join ENGAGEMENT e on e.MEDIA = m.ID
  left join PERSON p on e.PERSON = p.ID
  {= where
   {& UPPER(p.NAME_LAST) = :+lname | UPPER(p.NAME_FIRST) = :+fname}
  }
;

• in the case the input value :lname is not empty, the condition AND UPPER(p.NAME_LAST) = :+lname will become a part of the final WHERE
• otherwise in the case the input value :fname is not empty, the condition AND UPPER(p.NAME_FIRST) = :+fname will become a part of the final WHERE

Conditional SQL fragment OR

An SQL fragment disjunction is an ANSI SQL query fragment with embedded input values (static or dynamic ones). It's treated based on the emptiness of these input values. This fragment is divided into segments. Each segment is finished with one input value. If this input value is non-empty, the related segment will become a part of the final SQL statement. The conditional fragment itself is embedded into the final SQL statement with the prefix OR. So this fragment can only be embedded into the WHERE part of the final SQL statement.

Let's have a query

QUERY(QRY)=
  select m.id, m.TITLE, e.ROLE, p.NAME_FIRST first, p.NAME_LAST last
  from MEDIA m left join ENGAGEMENT e on e.MEDIA = m.ID
  left join PERSON p on e.PERSON = p.ID
  {= where
   {| UPPER(p.NAME_LAST) = :+lname}
   {| UPPER(p.NAME_FIRST) = :+fname}
  }
;

• in the case the input value :lname is not empty, the condition OR UPPER(p.NAME_LAST) = :+lname will become a part of the final WHERE
• in the case the input value :fname is not empty, the condition OR UPPER(p.NAME_FIRST) = :+fname will become a part of the final WHERE

Advanced conditional SQL fragment OR

A SQL fragment disjunction can consists from several alternative fragments separated with a BOR (a vertical bar). In this case, the first fragment, which has at least one input value non-empty, will be embedded in the final SQL statement.

Let's have a query

QUERY(QRY)=
  select m.id, m.TITLE, e.ROLE, p.NAME_FIRST first, p.NAME_LAST last
  from MEDIA m left join ENGAGEMENT e on e.MEDIA = m.ID
  left join PERSON p on e.PERSON = p.ID
  {= where
   {| UPPER(p.NAME_LAST) = :+lname | UPPER(p.NAME_FIRST) = :+fname}
  }
;

• in the case the input value :lname is not empty, the condition OR UPPER(p.NAME_LAST) = :+lname will become a part of the final WHERE
• otherwise in the case the input value :fname is not empty, the condition OR UPPER(p.NAME_FIRST) = :+fname will become a part of the final WHERE

Conditional SQL fragment OPT

An optional SQL fragment is an ANSI SQL fragment with embedded input values (static or dynamic ones). It's treated based on the emptiness of these input values. This fragment is divided into segments. Each segment is finished with one input value. If this input value is non-empty, the related segment will become a part of the final SQL statement. This fragment can be embedded into the SELECT, WHERE or any other part of the final SQL statement.

Let's have a query

QUERY(QRY)=
  select m.id, m.TITLE, e.ROLE, p.NAME_FIRST first, p.NAME_LAST last
  from MEDIA m left join ENGAGEMENT e on e.MEDIA = m.ID
  left join PERSON p on e.PERSON = p.ID
  {=where { UPPER(p.NAME_LAST) = :+lname}}
;

• in the case the input value :lname is not empty, the WHERE part of the final query will become UPPER(p.NAME_LAST) = :+lname

Advanced conditional fragment OPT

An optional SQL fragment can consists from several alternative fragments separated with a BOR (a vertical bar). In this case, the first fragment, which has at least one input value non-empty, will be embedded in the final SQL statement.

Let's have a query

QUERY(QRY)=
  select m.id, m.TITLE, e.ROLE, p.NAME_FIRST first, p.NAME_LAST last
  from MEDIA m left join ENGAGEMENT e on e.MEDIA = m.ID
  left join PERSON p on e.PERSON = p.ID
  {= where { UPPER(p.NAME_LAST) = :+lname | UPPER(p.NAME_FIRST) = :+fname}}
;

• in the case the input value :lname is not empty, the WHERE part of the final query will become UPPER(p.NAME_LAST) = :+lname
• otherwise in the case the input value :fname is not empty, the WHERE part of the final query will become UPPER(p.NAME_FIRST) = :+fname
• otherwise there's no WHERE fragment in the final query

Special fragment WHERE

Special fragment WHERE is used to delimit an SQL command WHERE and switch a special handling of input values in it. At the same time the keyword WHERE will become a prefix of this fragment, and potential leading AND/OR keywords will be stripped. This can help for example with the embedded conditional fragments of type AND and OR.

Let's have a query

QUERY(QRY)=
  select m.id, m.TITLE, e.ROLE, p.NAME_FIRST first, p.NAME_LAST last
  from MEDIA m left join ENGAGEMENT e on e.MEDIA = m.ID
  left join PERSON p on e.PERSON = p.ID
  {= where
   {& UPPER(p.NAME_LAST) = :+lname}
   {& UPPER(p.NAME_FIRST) = :+fname}
  }
;

• in the case the input value :lname is not empty, the condition AND UPPER(p.NAME_LAST) = :+lname will become a part of the final WHERE. Without embedding in {= where … }, the AND follows immediately after the command WHERE.
• in the case the input value :fname is not empty, the condition AND UPPER(p.NAME_FIRST) = :+fname will become a part of the final WHERE. In the case :lname is empty and without being embedding in {= where … }, the AND follows immediately after the command WHERE.

Special fragment VALUES

Special fragment VALUES is used to delimit an SQL command VALUES for the INSERT SQL command and switches a special handling of the input values in it. At the same time the keyword VALUES will become a prefix of this fragment, and potential leading and trailing commas will be stripped. The special handling of the input values means the emptiness of the input values is suppressed. In this way also null input values can be bounded to the final SQL prepared statement.

Let's have a statement

INSERT_PERSON(CRUD)=
  insert into PERSON (id, name)
  {= values (:id(seq=SEQ1), :name) }
;

• the generated input value :id is inserted into column id. The sequence SEQ1 is used to generate this value. This sequence has to be defined as an optional feature SEQ1(OPT)=….
• the input value :name is inserted into column name regardless if it's empty or not.

Special fragment COLUMNS

Special fragment COLUMNS is used for the INSERT SQL command and switches a special handling of the input values in it. A typical usage is the next one

INSERT_PERSON(CRUD)=
  insert into %%PERSON {= columns (
  , %ID
  {? :name | ,%NAME}
  ) }
  {= values (
  ,:id(seq=SIMPLE_SEQUENCE)
  {? :name | ,:name}
  ) }
  ;

• the generated input value :id is inserted into column id. The sequence SEQ1 is used to generate this value. This sequence has to be defined as an optional feature SEQ1(OPT)=….
• the input value :name is inserted into column name only in the case it's not null.

Special fragment SET

Special fragment SET is used to delimit an SQL command SET for the UPDATE SQL command and switches a special handling of the input values in it. At the same time the keyword SET will become a prefix of this fragment, and potential leading and trailing commas will be stripped. The special handling of the input values means the emptiness of the input values is suppressed. In this way also null input values can be bounded to the final SQL prepared statement.

Let's have a statement

UPDATE_PERSON(CRUD)=
  update PERSON
  {= set name = :name}
  {= where {& id = :id(notnull)}}
;

• the input value :name is updated the column name' regardless if it's empty or not.

Ordering SQL fragment

An ordering query fragment is identified with the number, which is at the begin of it. If this number is used in ordering directives (as a parameter to the SQL Processor Engine), the related ordering fragment will become a part of the final SQL query. The ordering directives can be

• SqlOrder.getAscOrder(number) – the related ordering query fragment will be embedded into the final SQL query with a suffix ASC
• SqlOrder.getDescOrder(number) – the related ordering query fragment will be embedded into the final SQL query with a suffix DESC

Let's have a query

QUERY(QRY)=
  select p.ID, p.NAME_FIRST first, p.NAME_LAST last, p.SSN_NUMBER ssn
  from PERSON p
  {#1 order by p.ID}
  {#2 order by p.NAME_LAST}
;

• in the case the ordering directive is (for example) SqlOrder.getAscOrder(1), the ordering command order by p.ID ASC will become a part of the final SQL query
• in the case the ordering directive is (for example) SqlOrder.getDescOrder(2), the ordering command order by p. NAME_LAST DESC will become a part of the final SQL query
• in the case the ordering directives are combined (for example) SqlOrder.getDescOrder(2).addAscOrder(1), the ordering command order by p. NAME_LAST DESC, p.ID ASC will become a part of the final SQL query

The tutorial is updated for SQLP 2.2