Meta SQL Statements

Table of Contents

Introduction
The meta statements definition
Filters to support multiple databases

Introduction

The SQL Processor works with a set of artifacts:

• the Meta SQL statements
• the explicit mapping rules
• the optional features

The syntax and semantics of these artifacts are defined in a grammar. In the version 1.5 the SQL Processor has a new ANTLR grammar defined in SqlProcessor.g. This grammar is used in the runtime and is fully backward compatible. In the version 1.6 the SQL Processor has a new Xtext grammar defined in ProcessorDsl.xtext. This grammar offers a little bit more features and it's used for editing purposes (please see the SQLEP 1.1.x Basic Tutorial).

All the SQL Processor artifacts can be persisted in the meta statements file. This strategy has several advantages:

• all SQL statements are outside of any Java code, so they can be optimized without a need to rebuild/redeploy any application
• it is possible to have one meta statements file for every type of target database (Oracle, MySQL, ...), so SQL statements can be optimized for this database
• in the case this meta statements file can be reloaded in the runtime, all SQL statements can also be changed/optimized in the runtime

There is a loader. which can read and process (= pre-compile) these artifacts:

• SqlProcessorLoader is a new loader. It supports the new style of the SQL Processor artifacts definition.

The meta statements definition

All the SQL Processor artifacts are defined in one file with the suffix qry by default.

Every artifact is described in one of the the following way:

• IDENT is the name of the artifact
• LPAREN is the left parenthesis
• STATEMENT / MAPPING / OPTION is the indicator of the artifact type, which can be
  • QRY for the META SQL query
  • CRUD for the META SQL CRUD statement
  • CALL for the META SQL CALL statement
  • OUT for the Explicit mapping rule
  • OPT for the Optional feature based on String
  • LOPT for the Optional feature based on Long
  • IOPT for the Optional feature based on Integer
  • SOPT for the Optional feature based on Short
  • BOPT for the Optional feature based on Boolean
  • MOPT for the Optional feature based on Map
• COMMA IDENT is the optional sequence of filters
• RPAREN is the right parenthesis
• meta / mapping / option is the artifact content itself
• SEMICOLON is the character semicolon

For example the the META SQL query with the name QUERY1 and the implicit (embedded) mapping rule can be

QUERY1(QRY)=
  select p.ID @id, p.SSN_NUMBER @ssn, p.SSN_COUNTRY @country, p.SEX @sex
  from PERSON p
  {= where
   {& p.SEX = :sex(type=estring)}
   {& p.COUNTRY = $ssn.country(type=estring)}
  }
  {#1 order by id}
;

The META SQL query with the name QUERY2 and the explicit mapping rule with the same name can be

QUERY2(QRY)=
  select p.ID, p.SSN_NUMBER ssn, p.SSN_COUNTRY country, p.SEX
  from PERSON p
  {= where
   {& p.SEX = :sex(type=estring)}
   {& p.COUNTRY = $ssn.country(type=estring)}
  }
  {#1 order by id}
;

QUERY2(OUT)=id$id ssn$ssn.number country$ssn.country(type=eint) sex$sex(type=estring);

The optional feature describing the ORACLE sequence with the name SEQ1:

SEQ1(OPT)=select HIBERNATE_SEQUENCE.nextval from dual;

Filters to support multiple databases

The SQL Processor artifacts in the meta statements file can be filtered based on the value in the header of the statement. This can help to have one meta statements file for several databases at the same time. In the runtime, based on the filter value, the correct subset of statements is selected.

This is presented in the following example using the meta statements file statements.qry:

SEQ1(OPT,HSQLDB)=call NEXT VALUE FOR HIBERNATE_SEQUENCE;
SEQ1(OPT,ORACLE)=select HIBERNATE_SEQUENCE.nextval from dual;

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

In the case the next code is used to load the statements definition

JdbcEngineFactory factory = new JdbcEngineFactory();
sqlFactory.setMetaFilesNames("statements.qry");
factory.setFilter("HSQLDB");
SqlQueryEngine sqlEngine = sqlFactory.getQueryEngine("xxx");

the following artifacts are loaded

• the standard artifacts, which have no filter value in the header of the statement
• the artifacts which have filter value HSQLDB in the header of the statement

In the case the following code is used to load the statements definition

JdbcEngineFactory factory = new JdbcEngineFactory();
sqlFactory.setMetaFilesNames("statements.qry");
factory.setFilter("ORACLE");
SqlQueryEngine sqlEngine = sqlFactory.getQueryEngine("xxx");

the next artifacts are loaded

• the standard artifacts, which have no filter value in the header of the statement
• the artifacts which have filter value HSQLDB in the header of the statement

So, we can pick up for example the correct sequence definition using the correct filter based on the database in the runtime. This can also help to have HSQLDB for the Junit tests and Oracle for the runtime, for example.

The tutorial is updated for SQLP 2.2.6