Dynamic Database (Runtime Conversion)¶
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:
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 |
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 |
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 |
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:
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 |
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 |
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
andcustomer
definitions;openScope
call;externalProcedure
block (its content is re-parented toexecute
);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!