SQLP 2.0 and SQLEP 1.3 Tutorial

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

###Introduction The new features of the SQL Processor (SQLP) version 2.0.0 and the SQL Processor Eclipse Plugin (SQLEP) version 1.3.0 are

• the unified grammar
• the simplified terminology

Unfortunately, the side effect is that the backward compatibility is broken.

###Control directives The control directives for POJOs and DB tables identification have been renamed

• in for dynamic input values identification - it refers POJO class
• inx for dynamic input values identification - it refers POJO entity
• const for static input values identification - it refers POJO class
• constx for static input values identification - it refers POJO entity
• out for output values identification - it refers POJO class
• outx for output values identification - it refers POJO entity
• tab for database tables/columns identification

This can be seen in the following snippet in statements.qry

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, %b.VERSION @version
         {? :subscriber(call=toInit) | , %s.LIBRARY @subscriber.library.id, %s.CONTACT @subscriber.contact.id, %s.NAME @subscriber.name, %s.VERSION @subscriber.version }
  from %%BILLING_DETAILS b
  {? :subscriber(call=toInit) | left join %%SUBSCRIBER s on %b.SUBSCRIBER = %s.ID }
  {= where
    {& %b.BA_ACCOUNT = :baAccount }
    {& %b.ID = :id }
    {& %b.SUBSCRIBER = :subscriber.id }
    {& %b.TYPE = :type }
  }
;

All these control directives in turn switch on the validation and content assist. The content assist can also help to establish the control directives itself (using Ctrl+Space).

###Modifiers Up to the previous release of the SQLP the META types and other supplement values were used like in the following example presenting optimistic locking

UPDATE_PERSON(CRUD,final=,identx=Person,colx=Person,dbcol=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 }
  }
;

In the new grammar for any input/output value there can be a list of modifiers enclosed in parenthesis, like in the following META SQL statement

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

Also the syntax colouring is utilized

In the case there are several modifiers for the same input/output value, they are separated using the comma, without any space.

To summarize all modifiers, they can be

• Meta types (type=eint)
• custom types (type=phone)
• sequences usage (seq=SEQ1)
• identities usage (idsel=SEL1)
• processing directives
  • any
  • null
  • notnull
  • id
• associations handling (call=toInit)
• null attributes handling (call=isDef)

Some of these modifiers can be used only for the input values, some of them only for the output values. For the output values there can be used also partial modifiers

• dynamic types (dtype=alist)
• generic types (gtype=movie)
• discriminators (discr)

Again, the syntax colouring can help

The content assist can also help to establish the modifiers (using Ctrl+Space).