Dynamic Database (Runtime Conversion)¶

Dynamic Database (Runtime Conversion)
- Temp-Tables
- Queries

Temp-Tables¶

Creation of Dynamic Temp-Tables¶

4GL allows to create a temp-table dynamically at run time using CREATE TEMP-TABLE statement (compared to static temp-table definition using DEFINE TEMP-TABLE). To create a dynamic temp-table, a user should perform the following steps:

1. Create a temp-table using CREATE TEMP-TABLE statement. In FWD, a TempTableBuilder object is created and a reference to it is stored in the resulting handle. This object, along with StaticTempTable, implements TempTable interface and represents "temp-table object" in 4GL terms.

Progress keyword	FWD equivalent
`CREATE TEMP-TABLE` Create a temp-table.	`TempTableBuilder.create`

2. Add fields and indexes to the table using the methods below. In FWD these methods add field/index definitions to the TempTableBuilder object.

Progress keyword	FWD equivalent
`ADD-NEW-FIELD` Add a field to the temp-table.	`TempTable.addNewField`
`ADD-NEW-INDEX` Add an index to the temp-table.	`TempTable.addNewIndex`
`ADD-INDEX-FIELD` Add a field to the temp-table index.	`TempTable.addFieldToIndex`
`ADD-LIKE-FIELD` Add a field, like the specified source field, to the temp-table.	`TempTable.addFieldLike`
`ADD-LIKE-INDEX` Add an index, like the specified source index, to the temp-table.	`TempTable.addIndexLike`
`ADD-FIELDS-FROM` Copy the field definitions from the specified source table to the temp-table. No index definitions are copied.	`TempTable.addFieldsFrom`
`CREATE-LIKE` Copy the field and index definitions from the specified source table to the temp-table.	`TempTable.createLike`

3. Prepare a temp-table using TEMP-TABLE-PREPARE statement. After that the table is ready to use. In FWD this function performs generation of required table artifacts.

Progress keyword	FWD equivalent
`TEMP-TABLE-PREPARE` Prepare a temp-table.	`TempTableBuilder.tempTablePrepare`

Also a user can use COPY-TEMP-TABLE to create identical copy of the source table. Effectively it does the same as CREATE-TABLE + PREPARE-TEMP-TABLE.

Progress keyword	FWD equivalent
`COPY-TEMP-TABLE` Copies a source temp-table object to a target temp-table object.	`TempTableBuilder.copyTempTable`

Sample Testcase¶

Here's a sample testcase that illustrates creation and usage of a dynamic temp-table. Using this example we'll learn how creation of dynamic tables work.

def var th as handle.
def var bh as handle.

// create temp-table
create temp-table th.
th:add-new-field("customer-id", "integer").
th:add-new-field("customer-name", "char").
th:add-new-index("main", true, true).
th:add-index-field("main", "customer-id", "asc").
th:temp-table-prepare("customer").

// create a test record
bh = th:default-buffer-handle.
bh:buffer-create.
bh:buffer-field("customer-id"):buffer-value = 1.
bh:buffer-field("customer-name"):buffer-value = "Test Customer".

Converted code:

//  create temp-table
TempTableBuilder.create(th);
th.unwrapTempTable().addNewField(new character("customer-id"), new character("integer"));
th.unwrapTempTable().addNewField(new character("customer-name"), new character("char"));
th.unwrapTempTable().addNewIndex(new character("main"), new logical(true), new logical(true));
th.unwrapTempTable().addFieldToIndex("main", "customer-id", "asc");
th.unwrapTempTable().tempTablePrepare("customer");         

//  create a test record
bh.assign(th.unwrapTempTable().defaultBufferHandle());
bh.unwrapBuffer().bufferCreate();
bh.unwrapBuffer().bufferField("customer-id").unwrapBufferField().changeValue(new integer(1));
bh.unwrapBuffer().bufferField("customer-name").unwrapBufferField().changeValue(new character("Test Customer"));

Dynamic Temp-Table Generation Under The Hood¶

The main part of dynamic table generation happens in DynamicTablesHelper.createDynamicDMO which is called by tempTablePrepare. It takes table definitions from TempTableBuilder, generates a DMO (Data Model Object) interface, and registers the new DMO type. After that, tempTablePrepare creates the default buffer for the new temp-table, based upon the new DMO interface.

The basic idea of createDynamicDMO is to create a form of code snippet with 4GL temp-table definitions and convert it into DMO classes, like we do during 4GL -> Java conversion, but with some intermediate steps dropped and optimized to increase runtime conversion speed.

Here're the steps createDynamicDMO utilizes to create a dynamic DMO for the testcase above:

1. A unique DMO interface name and table name is generated. In our case it'll be DynamicRecord1 and dtt1.
2. Generate AST (Abstract Syntax Tree) that represents the target table and has the same structure as .p2o files from the conversion stage. This tree is in-memory representation of 4GL table definitions well suitable for future processing. Representation format of a tree node is

node-name [node-type]:node-id
           (node-annotations)

You can print an AST using Aast.dumpTree(true) function.

Our AST:

_temp [DATABASE]:1 
   dtt1 [TEMP_TABLE]:2 
         (class-name=DynamicRecord1, table-name=dtt1, historical=customer)
      field1 [FIELD_INT]:3 
            (format=null, label=null, col_lab=null, historical=customer-id)
      field2 [FIELD_CHAR]:4 
            (format=null, label=null, col_lab=null, historical=customer-name)
      main [INDEX]:5 
            (primary=true, unique=true)
         customer-id [INDEX_FIELD]:6 
               (refid=3)

As you can see, there's a TEMP_TABLE node for the table dtt1, two FIELD_* nodes, and an INDEX node with an INDEX_FIELD node. These are the entities that we've previously added to TempTableBuilder.

3. Multiple sets of TRPL (tree processing) rules stored in .xml files under rules/ folder are sequentially applied onto this AST using ConversionPool.runTask to generate Java AST (tree representation of Java code) for the DMO interface. Here's Java code that could be generated from this Java AST. Note that this code is only for demonstration and it's NOT actually generated from the Java AST in DynamicTablesHelper.createDynamicDMO, instead the Java AST just goes to the next processing step skipping this code form.

package com.goldencode.p2j.persist.dynamic._temp;

import com.goldencode.p2j.persist.annotation.*;

@Table(name = "dtt1", legacy = "customer")
@Indices(
{
   @Index(name = "dtt1_main", legacy = "main", primary = true, unique = true, components = 
   {
      @IndexComponent(name = "field1", legacy = "customer-id")
   })
})
public interface DynamicRecord1
extends Temporary
{
   public com.goldencode.p2j.util.integer getField1();

   @Property(id = 1, name = "field1", column = "field1", legacy = "customer-id", format = "->,>>>,>>9", initial = "0", order = 10)
   public void setField1(com.goldencode.p2j.util.NumberType field1);

   public com.goldencode.p2j.util.character getField2();

   @Property(id = 2, name = "field2", column = "field2", legacy = "customer-name", format = "x(8)", order = 20)
   public void setField2(com.goldencode.p2j.util.Text field2);

   public interface Buf
   extends DynamicRecord1, TempTableBuffer
   {
   }
}

You can see that this code is exactly the same as DMO code generated for temp-tables during a FWD project conversion.

4. The Java AST from the previous step bypassing its Java code from is directly translated to Java bytecode using ASM (Java bytecode manipulation framework). The following classes are created:

com.goldencode.p2j.persist.dynamic._temp.DynamicRecord1.class
com.goldencode.p2j.persist.dynamic._temp.DynamicRecord1$Buf.class

5. The classes from the previous step are loaded in memory using a class loader. After that DMO class goes through standard registration process and it is ready to use!

Queries¶

Creation of Dynamic Queries¶

4GL allows to create a query dynamically at run time using CREATE QUERY statement (compared to static query definition, e.g. in FOR statement). To create a dynamic query, a user should perform the following steps:

1. Create a query using CREATE QUERY statement. In FWD, a QueryWrapper object is created and a reference to it is stored in the resulting handle.

Progress keyword	FWD equivalent
`CREATE QUERY` Create a dynamic query.	`QueryWrapper.createQuery`

2. Set buffers for a dynamic query using the methods below. In FWD these methods add buffers to the QueryWrapper object.

Progress keyword	FWD equivalent
`ADD-BUFFER` Add a buffer to the query object.	`QueryWrapper.addBuffer`
`SET-BUFFERS` Set all buffers for the query object. Any buffers previously added or set are removed.	`QueryWrapper.setBuffers`

3. Prepare a query using QUERY-PREPARE statement. After that the query is ready to use and can be opened. In FWD this function compiles a predicate (query condition). Note that dynamic predicates has some limitations. E.g. it can only contain literals and references to buffers/fields for buffers known to the query (no references to variables!).

Progress keyword	FWD equivalent
`QUERY-PREPARE` Prepare a query.	`QueryWrapper.prepare`

Sample Testcase¶

Here's a sample testcase that illustrates creation and usage of a dynamic query. Using this example we'll learn how creation of dynamic queries work. This testcase uses a permanent table named customer in a database named fwd.

// prepare data
def temp-table tt field f1 as int 
                  field f2 as char.
create tt.
tt.f1 = 1.
tt.f2 = "Test1". 

create tt.
tt.f1 = 2.
tt.f2 = "Test2".

find first customer.

// create dynamic query
def var q as handle.
create query q.
q:set-buffers(buffer tt:handle, buffer customer:handle).
q:query-prepare("for each tt, each customer where tt.f1 = customer.customer-id and tt.f2 <> 'Test'").

// iterate query
q:query-open().
repeat:
    q:get-next().
    if not avail tt then leave.
    message tt.f1 tt.f2 customer-id customer-name.
end.

Converted code:

Tt_1_1.Buf tt = TemporaryBuffer.define(Tt_1_1.Buf.class, "tt", "tt", false);
Customer.Buf customer = RecordBuffer.define(Customer.Buf.class, "hotel", "customer", "customer");
...

// prepare data
tt.create();
tt.setF1(new integer(1));
tt.setF2(new character("Test1"));
tt.create();
tt.setF1(new integer(2));
tt.setF2(new character("Test2"));
new FindQuery(customer, (String) null, null, "customer.recid asc").first();

// create dynamic query
QueryWrapper.createQuery(q);
q.unwrapBufferCollection().setBuffers(new handle(buffer(tt)), new handle(buffer(customer)));
q.unwrapQuery().prepare("for each tt, each customer where tt.f1 = customer.customer-id and tt.f2 <> 'Test'");         

// iterate query
q.unwrapQuery().queryOpen();

repeat("loopLabel0", new Block((Body) () -> 
{
   q.unwrapQuery().getNext();

   if (_not(tt.available()))
   {
      leave("loopLabel0");
   }

   message(new Object[]
   {
      (integer) new FieldReference(tt, "f1").getValue(),
      (character) new FieldReference(tt, "f2").getValue(),
      (integer) new FieldReference(customer, "customerId").getValue(),
      (character) new FieldReference(customer, "customerName").getValue()
   });
}));

Dynamic Query Generation Under The Hood¶

The main part of dynamic query generation happens in DynamicQueryHelper.parse which is called by QueryWrapper.prepare. It takes a predicate and set of buffers from a QueryWrapper and returns P2JQuery instance that can be used a normal query.

The basic idea of DynamicQueryHelper.parse is to create a form of code snippet with 4GL OPEN QUERY statement and convert it into a Java class, like we do during 4GL -> Java conversion, but with some intermediate steps dropped and optimized to increase runtime conversion speed. In that class, we run execute method of the generated class to create the corresponding P2JQuery object inside that class, call P2JQuery.queryOpen for it and use it externally, like a normal query in converted 4GL code.

Here're the steps DynamicQueryHelper.parse utilizes to parse the query from the testcase above:

1. The temporary buffers associated with the query are processed to create the associated dictionary and p2o ASTs (Abstract Syntax Trees).

_temp [DATA_MODEL]
   Tt_1 [CLASS]
      ...
      f1 [PROPERTY]
      f2 [PROPERTY]

In our case we have a single temp-table tt mapped in FWD to Tt_1 DMO interface. This tree is used by the SchemaDictionary to expose database information to the conversion rules.

2. In DynamicQueryHelper.preparePredicate create a Progress syntax correct OPEN QUERY statement that uses the given predicate. It will be later converted to Java form. In our case the generated predicate is

OPEN QUERY DynGenQuery for each tt, each customer where tt.f1 = customer.customer-id and tt.f2 <> 'Test'

3. The OPEN QUERY statement is parsed to create an AST:

block [BLOCK]
   statement [STATEMENT]
      OPEN [OPEN_QUERY]
         DynGenQuery [QUERY]
         for [KW_FOR]
            each [KW_EACH]
            record phrase [RECORD_PHRASE]
               tt [TEMP_TABLE]
            each [KW_EACH]
            record phrase [RECORD_PHRASE]
               customer [TABLE]
               where [KW_WHERE]
                  expression [EXPRESSION]
                     and [KW_AND]
                        = [EQUALS]
                           tt.f1 [FIELD_INT]
                           customer.customer-id [FIELD_INT]
                        <> [NOT_EQ]
                           tt.f2 [FIELD_CHAR]
                           'Test' [STRING]

4. Prepare the OPEN QUERY tree by placing the appropriate annotations with the converted java names and create DEFINE TEMP-TABLE nodes (with no fields), which is needed to allow proper conversion of the default buffer name. Prepared AST:

block [BLOCK]
   statement [STATEMENT]
      DEFINE [DEFINE_TEMP_TABLE]
         TEMP-TABLE [KW_TEMP_TAB]
            tt [SYMBOL]
   statement [STATEMENT]
      OPEN [OPEN_QUERY]
         ...

Notice the added DEFINE_TEMP_TABLE node.

5. Extract literals hardcoded in the predicate and replace them with parameters. This allows to improve query caching for the case of similar predicates.

6. Multiple sets of TRPL (tree processing) rules stored in .xml files under rules/ folder are sequentially applied onto this AST using ConversionPool.runTask to generate corresponding Java AST (tree representation of Java code) for 4GL code snippet with OPEN QUERY we've generated. Here's Java code that could be generated from this Java AST. Note that this code is only for demonstration and it's NOT actually generated from the Java AST in DynamicQueryHelper.parse, instead the Java AST just goes to the next processing step skipping this code form.

package com.goldencode.p2j.persist.dynquery;
...
@DatabaseReferences(aliases = 
{
   "fwd" 
})
public class DynGenQuery
{
   Tt_1.Buf tt = TemporaryBuffer.define(Tt_1.Buf.class, "tt", "tt", false);

   Customer.Buf customer = RecordBuffer.define(Customer.Buf.class, "fwd", "customer", "customer");

   public void execute()
   {
      CompoundQuery query0 = new CompoundQuery();

      externalProcedure(DynGenQuery.this, new Block((Body) () -> 
      {
         RecordBuffer.openScope(tt, customer);
         query0.initialize(true, false);
         query0.addComponent(new AdaptiveQuery().initialize(tt, ((String) null), null, "tt.recid asc"));
         query0.addComponent(new AdaptiveQuery().initialize(customer, "? = customer.customerId", () -> isNotEqual(tt.getF2(), hqlParam1dq), "customer.recid asc", new Object[]
         {
            new FieldReference(tt, "f1")
         }));
         query0.open();
      }));
   }
}

7. The Java AST above is prepared for future interpretation by a RuntimeJastInterpreter. Some nodes are removed, for example:

tt and customer definitions;
openScope call;
externalProcedure block (its content is re-parented to execute);
P2JQuery.open call (because the presented code will be called from this function).

The resulting tree represents the following code:

...
@DatabaseReferences(aliases = 
{
   "fwd" 
})
public class DynGenQuery
{
   CompoundQuery query0 = new CompoundQuery();

   public void execute()
   {
      query0.initialize(true, false);
      query0.addComponent(new AdaptiveQuery().initialize(tt, ((String) null), null, "tt.recid asc"));
      query0.addComponent(new AdaptiveQuery().initialize(customer, "? = customer.customerId", () -> isNotEqual(tt.getF2(), hqlParam1dq), "customer.recid asc", new Object[]
      {
         new FieldReference(tt, "f1")
      }));
   }
}

8. A RuntimeJastInterpreter is created for the generated Java AST. It allows to execute Java methods represented by the nodes of the Java AST and have full access to class internals. When a tree is being walked by the interpreter, it is analyzed to get the method name, the underlying object and method parameters and then Java reflection is used to call the method.

The interpreter initializes class field query0 and assigns to the resulting instance (which implements P2JQuery) a query open listener which is executed before the main part of P2JQuery.open.

When P2JQuery.open is called, this listener runs execute which contains the query initialization code, using the RuntimeJastInterpreter. After that the query can be used like a normal query!

Project

General

Profile

FWD

Wiki

Dynamic Database (Runtime Conversion)¶

Temp-Tables¶

Creation of Dynamic Temp-Tables¶

Sample Testcase¶

Dynamic Temp-Table Generation Under The Hood¶

Queries¶

Creation of Dynamic Queries¶

Sample Testcase¶

Dynamic Query Generation Under The Hood¶