SQLEP 1.2.x META SQL generation

###The tutorial is updated for SQLP 2 and SQLEP 1.3

###Samples All new features are presented in the samples Advances Samples.

###Important All generated artefacts (POJO, META SQL, DAO) are created in a compatible fashion. For any generator to produce correct code, first the file definitions.qry should be opened in IDE.

##META SQL generation based on DB model The next new feature of the SQLEP is the ability to generate the META SQL statements based on DB layout. It can significantly help to improve coding efficiency. The generated SQL can be taken as the first step in a new project, mainly for CRUD statements. Later the generated statements can be manually overwritten and improved.

The usage is rather simple. Let's have the following control directives in the definitions.qry, as is described in SQLEP 1.2.x Changes Tutorial, to establish connection to the target database (this tutorial is based on the sample project https://github.com/hudec/sql-processor/tree/master/sql-samples/simple-jdbc-crud):

database-is-online;
database-jdbc-driver org.hsqldb.jdbcDriver;
database-has-url jdbc:hsqldb:mem:sqlproc;
database-login-username sa;
database-login-password "";
database-ddl-create hsqldb.ddl; // should be located in the same directory as definitions.qry

Next the template tables should be used to generate the following snippet in definitions.qry (it's described in SQLEP 1.1.x Basic Tutorial):

table person PERSON;
table personLibrary PERSON_LIBRARY;
...

As a further step the POJOs should be generated. The steps are described in SQLEP 1.1.x POJO generation.

As a final step the META SQL statements can be generated. There are several control directives metagen-..., which are devoted to this process. The initial content of the statements.qry can be for example

SEQ1(OPT)=call NEXT VALUE FOR HIBERNATE_SEQUENCE;
SEL1(OPT)=call identity(); 

Put the cursor at the end of this file and press a Ctrl-Space. A content assist is activated and in the popup menu a couple of templates is offered. Select the new advanced template metagen - Meta SQL generator. A block of META SQL statements is generated based on the DB layout. For every database table the following artifacts are created

• one INSERT statement
• one GET statement
• one UPDATE statement
• one DELETE statement
• one SELECT query

The sample of generated META SQL statements can be seen in the https://github.com/hudec/sql-processor/blob/master/sql-samples/simple-jdbc-crud/src/main/resources/statements.qry.

The process of the META SQL statements generation is controlled by the next control directives

• pojogen-*
• metagen-*

Sequences

In some databases like Oracle, the sequences should/can be utilized in the INSERT statements. We must tell to SQLEP to use the sequences, like in the following snippet

metagen-global-sequence SEQ1;

or

metagen-table-sequence SEQ1 PERSON;

The sequence definition should be of course defined in the statements.qry, like in the following snippet

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

In this case the following statement is generated for the table PERSON:

INSERT_PERSON(CRUD,final=,inx=Person,outx=Person,tab=person)=
  insert into %%PERSON (%ID, %FIRST_NAME, %LAST_NAME, %DATE_OF_BIRTH, %SSN)
  {= values (:id(seq=SEQ1), :firstName, :lastName, :dateOfBirth, :ssn) }
;

###Identities In some databases like HSQLDB, the identities (auto-generated identity columns) should/can be utilized in the INSERT statements. We must tell to SQLEP to use the identities, like in the following snippet

metagen-global-identity SEL1;

or

metagen-table-identity SEL1 PERSON;

The identity SELECT definition should be of course defined in the statements.qry, like in the following snippet

SEL1(OPT)=call identity();

In this case the following statement is generated for the table PERSON:

INSERT_PERSON(CRUD,final=,inx=Person,outx=Person,tab=person)=
  insert into %%PERSON (%FIRST_NAME, %LAST_NAME, %DATE_OF_BIRTH, %SSN)
  {= values (:id(idsel=SEL1), :firstName, :lastName, :dateOfBirth, :ssn) }
;

###META Types META types are described in Input values or Output values. To force SQLEP to use META type for some table and column, we can use

metagen-column-meta-type CONTACT PHONE_NUMBER->phone;

This can be described also for the statement and column, like in the following snippet

metagen-statement-meta-type INSERT_CONTACT PHONE_NUMBER->phone;

In this case one of the following statement is generated for the table CONTACT:

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

###Final statements The statement can be assigned using the final= in the META SQL statement header (described in SQLEP 1.2.x Changes Tutorial). This is an indicator for the META SQL generator to not overwrite this statement in the next metagen template usage. To force SQLEP to mark all generated statements as final, we can use

metagen-make-it-final;

###Associations The generated META SQL statements (namely GET and SELECT) automatically contain first level JOINs for all one-to-many, many-to-one and many-to-many associations. These JOINs can be activated in the runtime using the methods enumInit and toInit. More details can be found in SQLEP 1.2.x Changes Tutorial.

###Inheritance The generated META SQL statements automatically reflect supported types of inheritance. For the inheritance table per subclass described in SQLEP 1.1.x POJO generation for example for the table MOVIE the following GET statement is generated

GET_MOVIE(CRUD,final=,inx=Movie,outx=Movie,tab=movie=m,tab=media=m1,tab=performer=p)=
  select %m.MEDIA_ID @id(id), %m.URLIMDB @urlimdb, %m.PLAYLENGTH @playlength, %m1.TITLE @title, %m1.AUTHOR @author.id(id)
  ...
  from %%MOVIE m
  join %%MEDIA m1 on %m.MEDIA_ID = %m1.ID
  ...

For the inheritance table per class hierarchy utilizing the discriminator described in SQLEP 1.1.x POJO generation for example for the table BANK_ACCOUNT the following GET statement is generated

GET_BANK_ACCOUNT(CRUD,final=,inx=BankAccount,outx=BankAccount,tab=billingDetails=b,tab=subscriber=s)=
  select %b.BA_ACCOUNT @baAccount, %b.ID @id(id), %b.SUBSCRIBER @subscriber.id(id), %b.TYPE @type
  from %%BILLING_DETAILS b
  ...

###NULL values update The generated META SQL statements (namely UPDATE) contain code to enable update DB columns with NULL values. This feature can be activated in the runtime using the methods enumDef and isDef. More details can be found in SQLEP 1.2.x Changes Tutorial.

###Ordering in SELECT Ordering is described in the Tutorial Basic META SQL. In the case the POJOs are generated based on DB model, the next artefacts are generated for all one column based indexes (including primary keys)

• all POJO attributes have assigned indexes postfix (described in SQLEP 1.2.x Changes Tutorial) or methods index=NNN
• all POJO classes have related constants (described in SQLEP 1.2.x Changes Tutorial)
• all SELECT META SQL queries have ordering directives

For example there are the next indexes in table PERSON

• primary key ID
• for column LAST_NAME

In the case there is a control directive

pojogen-generate-methods index=;

the generated POJO Person contains the following snippet

final pojo Person serializable 1  {
  id : java.lang.Long primaryKey
  firstName : java.lang.String required
  lastName : java.lang.String required
  ...
  index=1 ::: id
  index=2 ::: lastName firstName
  index=3 ::: lastName
}

otherwise the generated POJO Person contains the following snippet

final pojo Person serializable 1  {
  id : java.lang.Long primaryKey index 1
  firstName : java.lang.String required
  lastName : java.lang.String required index 2
  dateOfBirth : java.util.Date
  ...
}

The created class Person contains the following snippet

public class Person extends BaseModelImpl implements Serializable {
  public static final int ORDER_BY_ID = 1;
  public static final int ORDER_BY_LAST_NAME_FIRST_NAME = 2;
  public static final int ORDER_BY_LAST_NAME = 3;
  ...
}

or

public class Person extends BaseModelImpl implements Serializable {
  public static final int ORDER_BY_ID = 1;
  public static final int ORDER_BY_LAST_NAME = 2;
  ...
}

The generated SELECT META SQL query contains the following snippet

SELECT_PERSON(QRY,final=,inx=Person,outx=Person,tab=person=p,tab=personLibrary=p1,...)=
  select %p.ID @id(id), %p.FIRST_NAME @firstName, %p.LAST_NAME @lastName, ...
  from %%PERSON p
  {= where
    ...
  }
  {#1 order by %p.ID }
  {#2 order by %p.LAST_NAME, %p.FIRST_NAME }
  {#3 order by %p.LAST_NAME }
;

or

SELECT_PERSON(QRY,final=,inx=Person,outx=Person,tab=person=p,tab=personLibrary=p1,...)=
  select %p.ID @id(id), %p.FIRST_NAME @firstName, %p.LAST_NAME @lastName, ...
  from %%PERSON p
  {= where
    ...
  }
  {#1 order by %p.ID }
  {#2 order by %p.LAST_NAME }
;

To obtain the list of people sorted in descending order based on the last name, we can use

  person = new Person(); // no WHERE conditions
  list = personDao.list(person, new SqlStandardControl().setDescOrder(Person.ORDER_BY_ID));

###Optimistic locking In the case in the definitions.qry there is a control directive

pojogen-version-column VERSION;

where VERSION is the DB column name, the generated UPDATE and DELETE META SQL statements are built in a special fashion. For example in the case the table PERSON contains the column VERSION, the META SQLs are

UPDATE_PERSON(CRUD,final=,inx=Person,outx=Person,tab=person)=
  update %%PERSON
  {= set
    { ,%FIRST_NAME = :firstName(call=isDef) }
    { ,%LAST_NAME = :lastName(call=isDef) }
    { ,%DATE_OF_BIRTH = :dateOfBirth(call=isDef) }
    { ,%SSN = :ssn(call=isDef) }
    { ,%VERSION = %VERSION + 1 }
  }
  {= where
    {& %ID = :id(notnull) }
    {& %VERSION = :version(notnull) }
  }
;

DELETE_PERSON(CRUD,final=,inx=Person,outx=Person,tab=person)=
  delete from %%PERSON
  {= where
    {& %ID = :id(notnull) }
    {& %VERSION = :version(notnull) }
  }
;

Controlling the number of updated/deleted rows, we can force the optimistic locking mechanism

  Person person = new Person();
  person.setId(andrej.getId());
  person.setVersion(andrej.getVersion());
  person.setFirstName("Andrioša");
  person.setNull(Person.Attribute.ssn);
  count = personDao.update(person);
  if (count != 1)
    throw new RuntimeException("The row with ID "+person.getId()+" has been meanwhile changed");