SQLEP 1.0.0 Basic Tutorial - hudec/sql-processor GitHub Wiki

##Introduction The SQL Processor Eclipse plugin (SQLEP) enables smart edition of the SQL Processor (SQLP) artifacts (the META SQL statements and so on). It works in a very simple fashion. After the installation every file with the extension qry is associated with this smart editor. The basic picture of this editor activated in STS is the next one:

plugin-overview.png

It is based on the Xtext and offers all its advanced features, like syntax coloring, code completion, outline, validation and so on. The Xtext based grammar is more advanced compared to the ANTLR based grammar used in the runtime. To prevent the runtime errors due to this restriction, the rule of thumb is to put all advanced artifacts into a separate file. For example, you can use the next two files

statements.qry contains the standard SQL Processor artifacts (runtime artifacts), as they are described in Meta SQL Statements. This file is used in the runtime, as it's described in Simple Tutorial for example.
definitions.qry contains only artifacts (control artifacts), which are used by the SQL Processor plugin itself. In fact they are control directives, which help the Eclipse plugin to model the relationships between the runtime artifacts.

##Installation The simplest way to install the SQL Processor plugin is to copy the following jars into dropins directory:

The dropins directory is located in the STS/Eclipse main directory. For example is can be ~/springsource/sts-2.7.0.RELEASE/dropins/ for STS. After that, it's necessary to restart the IDE. (Also the modeling plugins like XText should be installed).

##POJOs The POJO beans (input forms or result classes) used in the runtime artifacts can be processed in a couple of ways:

the dynamic input values validation
the dynamic input values content assist
the static input values validation
the static input values content assist
the implicit output values validation
the implicit output values content assist
the explicit output values validation
the explicit output values content assist

To enable these features, the following directive should be declared

resolve references ON;

Next, all input forms and result classes should be defined. For example, let's take the sample used in the Wiki tutorials (https://github.com/hudec/sql-processor/tree/master/sql-samples/simple-jdbc). The following snippet lists all POJO beans used in this sample:

pojo person org.sqlproc.sample.simple.model.Person;
pojo contact org.sqlproc.sample.simple.model.Contact;
pojo media org.sqlproc.sample.simple.model.Media;
...

These definitions contain 3 columns

pojo is a keyword
the symbolic name of the POJO used in the next definitions
the full Java class name of the POJO. All the classes should be on the classpath of any project opened in the Eclipse/STS IDE.

Now, to enable the dynamic input values (=identifiers) processing, they should be defined, like in the following snippet:

ident ALL_PEOPLE person;
ident LIKE_PEOPLE person;
ident INSERT_PERSON person;
...

These definitions contain 3 columns

ident is a keyword
the name of the META SQL statement (the runtime artifact)
the symbolic name of the POJO (previously defined)

As a result, the validation of all dynamic input values in the META SQL statements (ALL_PEOPLE, LIKE_PEOPLE and INSERT_PERSON) is activated. It can be seen in the following picture, where the dynamic input value id1 is not an attribute in the POJO org.sqlproc.sample.simple.model.Person:

plugin-ident-validation.png

Similarly the content assist is activated. When you press a Ctrl-Space after a : control character, a list of all attributes in the POJO org.sqlproc.sample.simple.model.Person is offered:

plugin-ident-assist.png

To enable the static input values (=constants) processing, the keyword const instead of ident should be used. To enable the explicit output values (=output mapping rules) processing, the keyword out instead of ident should be used. Similarly to enable the output values (=columns) processing, the keyword col instead of ident should be used, like in the following snippet:

col ALL_PEOPLE person;
col LIKE_PEOPLE person;
col GET_PERSON person;
...

The following picture presents the output value validation in progress - the id2 isn't an attribute in the POJO org.sqlproc.sample.simple.model.Person:

plugin-col-validation.png

The next picture presents the content assist in action. When you press a Ctrl-Space after a @ control character, a list of all attributes in the POJO org.sqlproc.sample.simple.model.Person is offered.

plugin-col-assist.png

In fact, everywhere, where the POJO attribute is used, the content assist can help to quickly pick up the correct one. Also the validation helps to identify all incorrect usage of them. This can speed up the process of the SQL Processor artifacts definition significantly. Also it prevents the exceptions in the runtime due to incorrect database objects usage.

##Database objects The database columns and tables used in the runtime artifacts can be processed in a couple of ways:

the database tables validation
the database tables content assist
the database columns validation
the database columns content assist

To enable these features, the following directive should be declared

database online ON;

Moreover, the connection to the database should be established, like in the following snippet:

database driver oracle.jdbc.OracleDriver;
database url jdbc:oracle:thin:@127.0.0.1:1521:xe;
database username sqlsample;
database password sqlsample;
database schema SQLSAMPLE;

The database driver|url|username|password|schema are keywords. Of course, the declared JDBC driver has to be on the classpath. Next, all used tables should be defined. For example, let's take the sample used in the Wiki tutorials (https://github.com/hudec/sql-processor/tree/master/sql-samples/simple-jdbc). The following snippet lists all database tables used in this sample:

table person PERSON;
table contact CONTACT;
table media MEDIA;
...

These definitions contain 3 columns

table is a keyword
the symbolic name of the table
the table name in the database

In the case the database connection is in progress, you can use the content assist to pick up the correct table name. It can be seen in the following picture

plugin-table-assist.png

The following picture presents the table name validation in progress - the CONTACT1 table doesn't exist in the database under the schema SQLSAMPLE:

plugin-table-validation.png

In the META SQL statements all database tables names can be declared with the prefix %%. For example in the statement ALL_PEOPLE there's a usage %%PERSON:

ALL_PEOPLE(QRY)=
  select %ID @id, %NAME @name
  from %%PERSON
  {= where
   {& %ID = :id}
   {& UPPER(%NAME) = :+name}
  }
  {#1 order by %ID}
  {#2 order by %NAME}
;

In the runtime, the control characters %% are ignored. They are only recognized by the Eclipse plugin to activate the validation and content assist in the process of META SQL statement definition. This can be seen in the following picture:

plugin-table-validation-2.png

To enable these features, the usage of the database table in this META SQL statement has to be defined, like in the following snippet:

dbcol ALL_PEOPLE person;
dbcol LIKE_PEOPLE person;
dbcol INSERT_PERSON person;
...
dbcol ALL_PEOPLE_AND_CONTACTS person prefix p;
dbcol ALL_PEOPLE_AND_CONTACTS contact prefix c;
dbcol ALL_PEOPLE_AND_CONTACTS2 person prefix p;
dbcol ALL_PEOPLE_AND_CONTACTS2 contact prefix c;
...

These definitions contain 3 or 5 columns

dbcol is a keyword
the META SQL statement name
the symbolic name of the table (defined previously)
prefix is a keyword
the prefix value used in the META SQL statement

The next effect is all the database columns usage is validated and the content assist can be utilized. In the META SQL statements all database columns names can be declared with the prefix %. For example in the statement ALL_ALL_PEOPLE_AND_CONTACTS there's a usage %c.ID:

ALL_PEOPLE_AND_CONTACTS(QRY)=
  select %p.ID @id^long^id, %p.NAME @name, %c.ID @contacts.id^long^id, %p.ID @contacts.personId, %c.ADDRESS @contacts.address
  from %%PERSON p left join %%CONTACT c on %p.ID = %c.PERSON_ID
  {= where
   {& %p.ID = :id}
   {& UPPER(%p.NAME) like :+name}
  }
  {#1 order by %p.NAME, %c.ADDRESS}
;

In the runtime, the control character % is ignored. They are only recognized by the Eclipse plugin to activate the validation and content assist in the process of META SQL statement definition. This can be seen in the following picture:

plugin-column-assist.png

The %p.NAME1 is not a valid column in the table PERSON identified with the prefix p:

plugin-column-validation.png

In fact, everywhere, where the database table (view) or column is used, the content assist can help to quickly pick up the correct one. Also the validation helps to identify all incorrect usage of them. This can speed up the process of the SQL Processor artifacts definition significantly. Also it prevents the exceptions in the runtime due to incorrect database objects usage.

##Outline Plugin provides an outline view to help you navigate your models. By default, it provides a hierarchical view on your SQL Processor runtime artifacts and allows you to sort tree elements alphabetically. Selecting an element in the outline will highlight the corresponding element in the text editor. You can choose to synchronize the outline with the editor selection by clicking the Link with Editor button.

plugin-outline.png

There are 3 types of the SQL Processor runtime artifacts - optional features (OPT), meta statements (SQL) and mapping rules (OUT). In outline view there's also a possibility to filter these artifacts by types (OPT, SQL, OUT).

##General content assist The Content assist can help with the code completion not only for the SQL columns and POJO attributes. It covers all Xtext grammar keywords and a couple of enumerations.

For example, it can help with the SQL Processor artifacts types proposing their possible values:

plugin-metatype.png

Next example, it can help with the META types proposing their possible values:

plugin-sqltype.png

Some types are synonyms for the basic types, for example bool is synonym to boolean.

##CRUD templates SQL Processor plugin has defined 5 CRUD templates for basic CRUD operations - select, insert, update, optimistic update and delete.

Here is a simple CRUD example – let's have a database table PERSON with the (ORACLE) layout.

CREATE TABLE PERSON (
  ID NUMBER(19) NOT NULL,
  BIRTHDATE DATE NOT NULL,
  CREATEDDATE TIMESTAMP,
  CREATEDBY VARCHAR2(50),
  LASTUPDATED TIMESTAMP,
  LASTUPDATEDBY VARCHAR2(50),
  VERSION NUMBER(19) NOT NULL,
  CONTACT NUMBER(19),
  SSN_NUMBER VARCHAR2(20) NOT NULL,
  SSN_COUNTRY VARCHAR2(100) NOT NULL,
  NAME_FIRST VARCHAR2(100) NOT NULL,
  NAME_LAST VARCHAR2(100) NOT NULL,
  SEX VARCHAR2(100) NOT NULL,
  CLOTHES_SIZE NUMBER(10)
);

The related model is the Java class Person, which can be used also as a search form:

package org.sqlproc.engine.model;

import java.math.BigInteger;
import java.util.Date;

public class Person {

    private Long id;
    private java.sql.Date birthDate;
    private Date createdDate;
    private String createdBy;
    private Date lastUpdated;
    private String lastUpdatedBy;
    private Long version;
    private String first;
    private String last;

    // getters and setters
    ...
}

We'd like to have a person data in this table (=one database row). The result class is again the Java class Person. The META SQL query is going to be named GET_PERSON. First we write an empty statement in the meta statements file statements.qry:

GET_PERSON(CRUD)=
;

Now we define attributes in definitions.qry (attribute database online can be OFF before all database attributes are set):

resolve references ON; 
database online ON;
database driver oracle.jdbc.OracleDriver;
database url jdbc:oracle:thin:@127.0.0.1:1521:xe;
database username sql;
database password sql;
//database schema sql;

pojo person org.sqlproc.engine.model.Person;

ident GET_PERSON person;

col GET_PERSON person;

table person PERSON;

dbcol GET_PERSON person;

When the definition file is actually finished, we can changed the meta statement file. We put the cursor after the term GET_PERSON(CRUD)= and press Ctrl+Space. The list of basic CRUD templates is shown and we can pick up the select CRUD statement. The result is:

plugin-template.png

Some POJO attributes names can be invalid as they are derived from the the names of the database columns using the camel case algorithm. We can utilize again the content assist to fix them based on the real attributes names. The result statemets.qry is the next one:

GET_PERSON(CRUD)=
    select %ID @id, %BIRTHDATE @birthDate, %CREATEDDATE @createdDate, %CREATEDBY @createdBy, %LASTUPDATED @lastUpdated, %LASTUPDATEDBY @lastUpdatedBy, %VERSION @version, %NAME_FIRST @first, %NAME_LAST @last
    from %%PERSON
    {= where
  {& %ID = :id}
  {& %BIRTHDATE = :birthDate}
  {& %CREATEDDATE = :createdDate}
  {& %CREATEDBY = :createdDy}
  {& %LASTUPDATED = :lastUpdated}
  {& %LASTUPDATEDBY = :lastUpdatedBy}
  {& %VERSION = :version}
  {& %NAME_FIRST = :first}
  {& %NAME_LAST = :last}
    }
;

Optimistic update is a special case, where it plays an important role VERSION column (number). When you update the record, the VERSION value will be increased by 1.

UPDATE_PERSON(CRUD)=
  update PERSON
  {= set (VERSION = :version + 1, BIRTHDATE = :birthDate, CREATEDDATE = :createdDate, CREATEDBY = :createdBy, LASTUPDATED = :lastUpdated, LASTUPDATEDBY = :lastUpdatedBy, CONTACT = :contact, SSN_NUMBER = :ssnNumber, SSN_COUNTRY = :ssnCountry, NAME_FIRST = :nameFirst, NAME_LAST = :nameLast, SEX = :sex, CLOTHES_SIZE = :clothesSize)}
  {= where
  {& ID = :id }
  {& VERSION = :version }
  }
;

##Advanced templates SQL Processor plugin has defined 2 more advanced templates to improve the coding efficiency:

pojos - it loads the definitions of all POJO classes (the output classes or search forms) on the classpath. To enable the POJOs detection, these classes must be annotated with org.sqlproc.engine.annotation.Pojo.
tables - it loads the definitions of all database tables in the current schema

Problems

Remember - the definitions and statements file must be in same directory.

Plugin doesn't resolve POJO's:

check the Java pojo is in actual project or it's class is in dependency jar
check resolve references attribute in definition file
change to resolve references OFF and back to resolve references ON

Plugin doesn't resolve DB usages:

check the DB is online and the account isn't locked
check the JDBC driver is in the classpath
check the database attributes are correct in the definition file
check the database online attribute in definition file
change to database online OFF and back to database online ON