SQLMEP META SQL generation II

The tutorial is updated for SQLP 3.1 and SQLMEP 2.6

The next control directives have been changed: pojo to is-pojo, table to is-table, procedure to is-procedure, function to is-function.

Introduction

The next new features of the SQLMEP are new control directives devoted to

• The catalog filter
• The database type
• The database meta info
• The driver meta info
• The DatabaseMetaData methods invocation
• Improved sequences and identities handling
• Combine both sequences and identities
• Generate LIKE in QUERY statemets

The catalog filter

Alongside the control directive database-active-schema there's a new control directive database-in-catalog. It enables the determination of the catalog as the filter for the database objects selection (for more info please see http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html).

he database type

The POJO/DAO/META SQL generation is influenced by the type of the database. It is derived from the database meta information. In any case, it can be manually defined using the new control directive, as in the following code snippet

database-is-of-type HSQLDB; 

Pressing the CTRL-Space after the control directive, the popup menu with the supported database is presented.

The database meta info

We can ask for the database meta info like in the following code

database-show-database-info HSQL Database Engine,2.2.9,2,2;

Simply put the cursor after the control control directive and press a Ctrl-Space.

The driver meta info

We can ask for the JDBC driver meta info like in the following code

database-show-driver-info HSQL Database Engine Driver,2.2.9,2,2;

Simply put the cursor after the control control directive and press a Ctrl-Space.

The DatabaseMetaData methods invocation

We can ask for the any DatabaseMetaData methods invocation output using the new control directive like in the following code snippet

database-show-driver-output getSystemFunctions->DATABASE,IFNULL,USER;

After the control directive there's a method name like getSystemFunctions in the previous example. Pressing the CTRL-Space after the -> the required information is presented.

Improved sequences and identities handling

The sequences and identities usage is described in https://github.com/hudec/sql-processor/wiki/CRUD-Tutorial#insert-statement. The updated SQLP is able to handle the sequences and identities automatically, without the necessity to specify the related statements. The main requirement is to define the database type in the runtime, like in the following example

JdbcEngineFactory factory = new JdbcEngineFactory();
factory.setMetaFilesNames("statements.meta");
factory.setFilter(SqlFeature.HSQLDB);

In this case it's enough to use the real sequence name in the META SQL INSERT statement, like in the next HSQLDB sample

INSERT_CONTACT(CRUD,final=,inx=Contact,outx=Contact,tab=contact)=
  insert into %%CONTACT (%ID, %PERSON_ID, %ADDRESS, %PHONE_NUMBER)
  {= values (:id(seq=SIMPLE_SEQUENCE), :person.id, :address, :phoneNumber) }
;

or the identity abbreviation IDSEL, like in the next MySQL sample

INSERT_CONTACT(CRUD,final=,inx=Contact,outx=Contact,tab=contact)=
  insert into %%CONTACT (%PERSON_ID, %ADDRESS, %PHONE_NUMBER)
  {= values (:id(idsel), :person.id, :address, :phoneNumber) }
;

The supported databases are

• DB2
• HSQLDB
• H2
• Informix
• MS SQL (partially)
• MySQL
• Oracle
• PostgreSQL

The excellent examples presenting these features are located at https://github.com/hudec/sql-processor/tree/master/simple-samples.

Improved sequences and identities statements generation

The SQLMEP is able to generate the sequences statements or identities statements (described in SQLMEP META SQL generation). There's a new way, how to force these artifacts generation using the control directive

metagen-global-sequence SIMPLE_SEQUENCE;
metagen-generate-sequences;

the result is

SIMPLE_SEQUENCE(OPT)=call next value for SIMPLE_SEQUENCE;

INSERT_CONTACT(CRUD,in=Contact,out=Contact,tab=contact)=
  insert into %%CONTACT (%ID, %PERSON_ID, %TYPE, %ADDRESS, %PHONE_NUMBER, %X_NOTE)
  {= values (:id(seq=SIMPLE_SEQUENCE), :person.id, :type, :address, :phoneNumber, :xNote) }
;

or

metagen-global-identity SIMPLE_IDSEL;
metagen-generate-identities;

the result is

SIMPLE_IDSEL(OPT)=call identity();

INSERT_CONTACT(CRUD,in=Contact,out=Contact,tab=contact)=
  insert into %%CONTACT (%PERSON_ID, %TYPE, %ADDRESS, %PHONE_NUMBER, %X_NOTE)
  {= values (:id(idsel=SIMPLE_IDSEL), :person.id, :type, :address, :phoneNumber, :xNote) }
;

We can unify META SQL statements for both the sequences or identities, only the modifier is different - idgen= instead of seq= or idsel=

metagen-global-sequence SIMPLE_SEQUENCE;
metagen-generate-default-idgenerators;

the result is

IDGEN=SIMPLE_SEQUENCE(OPT)=seq=SIMPLE_SEQUENCE;

INSERT_CONTACT(CRUD,in=Contact,out=Contact,tab=contact)=
  insert into %%CONTACT (%ID, %PERSON_ID, %TYPE, %ADDRESS, %PHONE_NUMBER, %X_NOTE)
  {= values (:id(idgen=SIMPLE_SEQUENCE,id=ID), :person.id, :type, :address, :phoneNumber, :xNote) }
;

or

metagen-global-identity SIMPLE_IDSEL;
metagen-generate-default-idgenerators;

the result is

IDGEN=SIMPLE_IDSEL(OPT)=idsel;

INSERT_CONTACT(CRUD,in=Contact,out=Contact,tab=contact)=
  insert into %%CONTACT (%ID, %PERSON_ID, %TYPE, %ADDRESS, %PHONE_NUMBER, %X_NOTE)
  {= values (:id(idgen=SIMPLE_IDSEL,id=ID), :person.id, :type, :address, :phoneNumber, :xNote) }
;

Combine both sequences and identities

Using positive list (starts with character +) and negative list (starts with character -) we can combine both sequences and identities, like in the next sample

metagen-global-identity SIMPLE_IDSEL - PERSON_DETAIL;
metagen-table-sequence CONTACT SIMPLE_SEQUENCE;
metagen-generate-idgenerators;

In this way, for all tables the identity generator with the name SIMPLE_IDSEL is used. Only for the table CONTACT the sequence with the name SIMPLE_SEQUENCE is used. For the table PERSON_DETAIL nor identities nor sequences are used (Naturally, this table is one-to-one to the table PERSON).

Generate LIKE in QUERY statemets

At https://github.com/hudec/sql-processor/wiki/Optional-features#search-based-on-text-fragments there's described the usage of LIKE in the META SQL statements. In the standard fashion, how the SQLMEP generates the META SQL statements is the next one

• for all String base values the WHERE part of the generated statement contains the SQL fragment like in the next sample

  {& UPPER(%p.FIRST_NAME) like :+firstName }

• for all not-String base values the WHERE part of the generated statement contains the SQL fragment like in the next sample

  {& %p.DATE_OF_BIRTH = :dateOfBirth }

This behaviour can be changed using the control directives

metagen-like-columns PERSON DATE_OF_BIRTH;
metagen-not-like-columns PERSON FIRST_NAME;