SQLMEP META SQL generation - hudec/sql-processor GitHub Wiki

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.

Notice

All generated artefacts (POJO, META SQL, DAO) are created in a compatible fashion. For any generator to produce correct code, first the file definitions.meta and/or definitions.model should be opened in IDE. The best approach is to have definitions.model as a symbolic link to definitions.meta.

META SQL generation based on DB model

The next feature of the SQLMEP 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.meta/model, as is described in SQLMOP POJO generation II, to establish connection to the target database (this tutorial is based on the sample project https://github.com/hudec/sql-processor/blob/master/sql-samples/simple-jdbc-crud/src/main/resources/definitions.model):

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.meta/model
pojogen-generate-wrappers;
compress-meta-directives;

Next the template tables should be used to generate the following snippet in definitions.meta/model (it's described in SQLMEP Basic Tutorial):

is-table person PERSON;
is-table personLibrary PERSON_LIBRARY;
...

As a further step the POJOs should be generated. This step is described in SQLMOP POJO generation. Next the template pojos should be used to generate the following snippet in definitions.meta/model:

is-pojo Person org.sqlproc.sample.simple.model.Person;
is-pojo PersonLibrary org.sqlproc.sample.simple.model.PersonLibrary;
...

As a final step the META SQL statements can be generated. There are several control directives metagen-..., which are devoted to this process. Put the cursor at the start of file statements.meta 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.meta.

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;

metagen-table-sequence SEQ1 PERSON;

For not supported databases the sequence definition should be defined in the statements.meta, like in the following snippet (there should be sequence SIMPLE_SEQUENCE in DB)

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

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

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

For supported databases we can have the sequence name directly in META SQL

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

There's a new approach based on ID generators, which unify both sequences and identities. For more info please see SQLMEP META SQL generation II.

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 IDSEL1;

metagen-table-identity IDSEL1 PERSON;

For not supported databases the identity SELECT definition should be defined in the statements.meta, like in the following snippet

IDSEL1(OPT)=call identity();

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

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

For supported databases we can have the default identity name IDSEL directly in META SQL

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

There's a new approach based on ID generators, which unify both sequences and identities. For more info please see SQLMEP META SQL generation II.

META Types

META types are described in Input values or Output values. To force SQLMEP 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=,in=Contact,out=Contact,tab=contact)=
  insert into %%CONTACT (%ID, %PERSON_ID, %ADDRESS, %PHONE_NUMBER)
  {= values (:id(idsel=IDSEL), :person.id, :address, :phoneNumber(type=phone)) }
;

Final statements

The statement can be assigned using the final= in the META SQL statement header. This is an indicator for the META SQL generator to not overwrite this statement in the next metagen template usage. To force SQLMEP 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 SQLMOP POJO generation II.

Inheritance

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

GET_MOVIE(CRUD,final=,in=Movie,out=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 SQLMOP POJO generation for example for the table BANK_ACCOUNT the following GET statement is generated

GET_BANK_ACCOUNT(CRUD,final=,in=BankAccount,out=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 SQLMOP POJO generation II.

###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 META annotations #Index or #EnumIndex (described in SQLMOP POJO generation II)
all POJO classes have related constants or enumeration (described in SQLMOP POJO generation II)
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
for columns FIRST_NAME and LAST_NAME

In the case there is a control directive

pojogen-generate-methods index;

the generated POJO Person contains the following snippet

#Serializable(1) #Index(id) #Index(lastName) #Index(lastName,firstName) ...
#Pojo Person {
  #PrimaryKey
  #Attr Long id
  ...
}

otherwise the generated POJO Person contains the following snippet

#Serializable(1) ...
#Pojo Person {
  #PrimaryKey #Index
  #Attr Long id
  ...
}

The created class Person contains the following snippet

public class Person implements Serializable {
  public final static String ORDER_BY_ID = "ID";
  public final static String ORDER_BY_LAST_NAME = "LAST_NAME";
  public final static String ORDER_BY_LAST_NAME_FIRST_NAME = "LAST_NAME_FIRST_NAME";
  ...
}

public class Person implements Serializable {
  public enum Order {
    ID,
    LAST_NAME,
    LAST_NAME_FIRST_NAME
  }
}

The generated SELECT META SQL query contains the following snippet

SELECT_PERSON(QRY,final=,in=Person,out=Person,tab=person=p,tab=personLibrary=p1,...)=
  select %p.ID @id(id), %p.FIRST_NAME @firstName, %p.LAST_NAME @lastName, ...
  from %%PERSON p
  {= where
    ...
  }
  {#ID order by %p.ID }
  {#LAST_NAME_FIRST_NAME order by %p.LAST_NAME, %p.FIRST_NAME }
  {#LAST_NAME 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.meta 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=,in=Person,out=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(!empty) }
    {& %VERSION = :version(!empty) }
  }
;

DELETE_PERSON(CRUD,final=,in=Person,out=Person,tab=person)=
  delete from %%PERSON
  {= where
    {& %ID = :id(!empty) }
    {& %VERSION = :version(!empty) }
  }
;

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");