Datasets¶
The dataset objects are constructs that extends the functionality of temp-tables and allows a set of these to be handled together, as a single object. Optionally, some relationships can be defined between these tables which impose a tree-like structure among them. The dataset provide means to:- populate the member tables from external sources;
- track changes while the data inside tables is changed;
- serialize and deserialize the whole construct in/from a single XML/JSON resource (not mandatory a file);
- validate data and store back to data sources.
From a simplified perspective, the datasets can be seen as "miniature", in-memory database objects. In consequence, they can be passed as parameters in a similar fashion to table parameters.
Typical usecase of datasets¶
Usually, the usage of datasets consist in:- definition of a
DataSourcewhere the data in 'imported'. This is a special object which allows a table of aDataSetto be populated. The used term for operation is "fill" and can be performed at table-level or for the full data-set. - definition of
DataSet. The component tables are defined and added to the collection and optional relations are defined; - the
filloperation is executed and the dataset is populated; - the tables can be used for various operations now, individually, or as a whole set. The modification in data is tracked automatically if the option is activated;
- data is validated and stored back to source based on several flags which can be set automatically or programmatically;
- the object is destroyed.
Types of DataSet objects¶
There are two main ways to create the dataset collection: static (created at conversion type) or dynamically (created at runtime). All related objects will have some logical constraints (for example you cannot create a static dataset using dynamically generated tables because the table do not exist at conversion time), but after the creation, all objects are equals, in the sense that they support the very same features.
We will create an example for the static case and describe how FWD handles it at conversion and runtime. Then we will only build the same construct in a dynamic fashion. The runtime part is really the very same.
Static approach¶
The static approach is used mainly when the tables/buffers are also defined statically. This could take advantage of the structures to be a bit pre-processed at conversion time and slightly improve performance of the code building the object. However, after the objects are fully constructed, there is no difference between the objects, until they are deleted. To check whether an object was defined or created, the ABL programmer can use the DYNAMIC attribute which is fully supported by FWD.
Defining the static DATASET¶
Consider the following 4GL code:
DEFINE TEMP-TABLE tt1 BEFORE-TABLE btt1
FIELD f11 AS INTEGER
FIELD f12 AS CHARACTER
FIELD f13 AS DATE
INDEX idx1 IS UNIQUE f11.
DEFINE TEMP-TABLE tt2 BEFORE-TABLE btt2
FIELD f21 AS INTEGER
FIELD f24 AS LOGICAL
FIELD f25 AS DECIMAL.
DEFINE TEMP-TABLE tt3
FIELD f36 AS DATETIME
FIELD f37 AS HANDLE
FIELD f38 AS DATETIME-TZ.
DEFINE DATASET my-dataset
FOR tt1, tt2, tt3
DATA-RELATION tt-link FOR tt1, tt2 RELATION-FIELDS (tt1.f11, tt2.f21).
We define three temporary tables. The first two of them define the optional BEFORE-TABLE which will track the changes from the main table (aka AFTER-TABLE). In the case of tt1, FWD will generate the DMO for the main table as usual (see href=?), but the @Table annotation will contain an extra attribute:
/** Data Model Object corresponding with a temporary table. */
@Table(name = "tt3", legacy = "tt1", beforeTable = "btt1")
@Indices({
@Index(name = "tt3_idx1", legacy = "idx1", unique = true, components = {
@IndexComponent(name = "f11", legacy = "f11")
})
})
public interface Tt1_1 {
[...]
Next to Tt1_1 interface, the Btt1_1 interface will be generated, with similar structure but annotated as:
@Table(name = "tt4", legacy = "btt1", afterTable = "tt1")
public interface Btt1_1 {
[...]
Another thing to notice here is the presence of the special properties injected at the top of the interface, properties which will store the attributes specific to dataset-operations (error-flag, error-string, row-state, etc):
/**
* Getter: __error-flag__
* <p>
* _errorFlag
*
* @return _errorFlag
*/
@Property(id = -9, name = "_errorFlag", column = "_errorFlag", legacy = "__error-flag__", format = "->,>>>,>>9", initialNull = true, order = 0, like = "tt1.f11")
public integer get_errorFlag();
Similar result will be obtained for tt2 and btt2 tables, but tt3 is kept single because of the absence of BEFORE-TABLE option.
For the DATASET declaration we will find in the procedure's Java source the generated code which looks as follows:
final DataSet myDataset = DataSetManager.define("my-dataset", false, false)
.forBuffers(tt1, tt2, tt3)
.addDataRelation("tt-link", false, false, false, false, false, tt1, tt2, new DataSet.FieldPair("tt1.f11", "tt2.f21"))
.generate();
- specifies the dataset's name, and the optional sharing flags in
definestatic method; - adds the list of buffers;
- defines the optional relations between the tables. Notice all the options have their implied values set and the fields already parsed to simplify the operations at runtime;
- forces the generation of the final object and stores it in a field to make it available procedure-wide.
Defining the static DATA-SOURCES¶
Let's consider the following ABL code:
DEFINE TEMP-TABLE src-1
FIELD e-11 AS INTEGER
FIELD e-12 AS CHARACTER
FIELD e-13 AS DATE
FIELD e-14 AS LOGICAL // not filled
FIELD e-15 AS DECIMAL // not filled
INDEX idx1 IS UNIQUE PRIMARY e-11.
DEFINE QUERY q-1 FOR src-1.
QUERY q-1:QUERY-PREPARE("FOR EACH src-1 WHERE e-14").
DEFINE DATA-SOURCE ds1 FOR QUERY q-1.
DEFINE TEMP-TABLE src-2
FIELD e-21 AS INTEGER
FIELD e-24 AS LOGICAL
FIELD f25 AS DECIMAL
FIELD f26 AS DATETIME // not filled
INDEX idx2 IS UNIQUE PRIMARY e-21.
DEFINE DATA-SOURCE ds2 FOR src-2 KEYS (e-21).
DEFINE TEMP-TABLE src-3
FIELD f36 AS DATETIME
FIELD f37 AS HANDLE
FIELD f38 AS DATETIME-TZ.
DEFINE DATA-SOURCE ds3 FOR src-3.
The above code defines three tables src-<k> which will be the sources for each of the tables in the dataset.
The first DATA-SOURCE uses a QUERY to filter only a specific set of rows to be copied to destination dataset table. The query itself is generated, as usual for this kind of object, as:
final QueryWrapper query0 = new QueryWrapper("q-1", false);
[...]
query0.addBuffer(src1, true);
[...]
query0.prepare("FOR EACH src-1 WHERE e-14");
What is new is the definition of the ds1 class member, which is built similar to DataSet objects from previous paragraph:
final DataSource ds1 = DataSource.define()
.setQuery(query0)
.generate();
There are different methods which can be chained during the build of the DataSource object and the FWD conversion picks the exact one which matches the syntax used in ABL code. Each of these three constructs use different methods. For example, the second DataSource is build using a set of buffers with a KEY, for reverse lookup:
final DataSource ds2 = DataSource.define()
.addBuffer(src2, "e-21")
.generate();
Once the value returned by builder method is assigned to final member of the procedure, the object is accessible to be used. However, this only happens at runtime.
Dynamic approach¶
Some applications prefer, or are forced by their architecture, to create the data-sources and data-sets partially or fully at runtime. When this is done dynamically, the objects we manipulate are always handle s. If we are to rewrite the construction of the static DATA-SOURCE -s from previous section with dynamic content, the ABL code will look like this:
DEFINE VARIABLE hd-ds1 AS HANDLE.
CREATE DATA-SOURCE hd-ds1.
hd-ds1:QUERY = QUERY q-1:HANDLE.
DEFINE VARIABLE hd-ds2 AS HANDLE.
CREATE DATA-SOURCE hd-ds2.
hd-ds2:ADD-SOURCE-BUFFER(BUFFER src-2:HANDLE, "e-21").
We decided here a mixed approach in which the QUERY and BUFFER objects remain defined statically and their handle is obtained at runtime. A more dynamic solution could have created these also dynamically, in which case they can only be accessed by the HANDLE s used in their creation. We leave this as a homework for the reader. After FWD conversion, the above code will look like this in Java:
@LegacySignature(type = Type.VARIABLE, name = "hd-ds1")
handle hdDs1 = UndoableFactory.handle();
@LegacySignature(type = Type.VARIABLE, name = "hd-ds2")
handle hdDs2 = UndoableFactory.handle();
[...]
DataSource.create(hdDs1);
hdDs1.unwrapQueryAssociable().setQueryAsHandle(query0.asHandle());
DataSource.create(hdDs2);
hdDs2.unwrapDataSource().addSourceBuffer(new handle(buffer(src2)), "e-21");
A similar paradigm will be encountered when we talk about the dynamic DATASET s. The code constructed the static object above can be rewritten with similar results as:
DEFINE VARIABLE hd-my-dataset AS HANDLE.
CREATE DATASET hd-my-dataset.
hd-my-dataset:SET-BUFFERS(BUFFER src-1:HANDLE, BUFFER src-2:HANDLE, BUFFER src-2:HANDLE).
hd-my-dataset:ADD-RELATION(BUFFER src-1:HANDLE, BUFFER src-2:HANDLE, "tt1.f11,tt2.f21").
and will be converted to Java as:
@LegacySignature(type = Type.VARIABLE, name = "hd-my-dataset")
handle hdMyDataset = UndoableFactory.handle();
[...]
DataSet.create(hdMyDataset);
hdMyDataset.unwrapBufferCollection().setBuffers(new handle(buffer(src1)), new handle(buffer(src2)), new handle(buffer(src2)));
hdMyDataset.unwrapDataSet().addRelation(new handle(buffer(src1)), new handle(buffer(src2)), new character("tt1.f11,tt2.f21"));
Operations at runtime¶
The first operation which happens at runtime is to attach DATA-SOURCE s to BUFFER s before the latter could be FILL -ed. These operations are put face-to-face below:
| Original OpenEdge Code | Converted Java Code |
|---|---|
|
|
|
|
Notice the unwrapping of the data-set and buffer to same interface, Fillable. Each of the DataSet and Buffer implements the interface, and the result is as expected: the whole dataset is populated recursively, for the former case, while only the tt3 buffer is (re-) populated in latter (that is, the destination table will contain the records from the source tabled doubled, since the default FILL mode is APPEND).
| Original OpenEdge Code | Converted Java Code |
|---|---|
|
|
At this moment, the programmer is free to use the tables from the dataset as he likes. The changes done on AFTER BUFFER will be reflected in the BEFORE BUFFER.
At the end of the operation the programmer may choose one of the simple outcome, that is to save the changes back to source for the tracked table, using:| Original OpenEdge Code | Converted Java Code |
|---|---|
|
|
Putting all together¶
We have now all the needed operations ready for a basic procedure using datasets. The source code in ABL can look like this:
DEFINE TEMP-TABLE src-1
FIELD e-11 AS INTEGER
FIELD e-12 AS CHARACTER
FIELD e-13 AS DATE
FIELD e-14 AS LOGICAL // not filled
FIELD e-15 AS DECIMAL // not filled
INDEX idx1 IS UNIQUE PRIMARY e-11.
// manually populate this source table
CREATE src-1. e-11 = 1. e-12 = "1". e-13 = TODAY. e-14 = YES. e-15 = 1.1.
CREATE src-1. e-11 = 2. e-12 = "22". e-13 = TODAY - 1. e-14 = YES. e-15 = 1.2.
CREATE src-1. e-11 = 3. e-12 = "333". e-13 = TODAY + 2. e-14 = NO. e-15 = 3.1.
CREATE src-1. e-11 = 4. e-12 = "4444". e-13 = TODAY - 3. e-14 = YES. e-15 = 1.5.
CREATE src-1. e-11 = 5. e-12 = "55555". e-13 = TODAY + 4. e-14 = NO. e-15 = 6.1.
CREATE src-1. e-11 = 6. e-12 = "666666". e-13 = TODAY - 5. e-14 = YES. e-15 = 1.9.
message "View source table #1:".
FOR EACH src-1:
MESSAGE RECID(src-1) src-1.e-11 src-1.e-12 src-1.e-13 src-1.e-14 src-1.e-15.
END.
DEFINE QUERY q-1 FOR src-1.
QUERY q-1:QUERY-PREPARE("FOR EACH src-1 WHERE e-14").
DEFINE DATA-SOURCE ds1 FOR QUERY q-1.
DEFINE TEMP-TABLE src-2
FIELD e-21 AS INTEGER
FIELD e-24 AS LOGICAL
FIELD f25 AS DECIMAL
FIELD f26 AS DATETIME // not filled
INDEX idx2 IS UNIQUE PRIMARY e-21 f25.
// manually populate this source table, too
CREATE src-2. src-2.e-21 = 2. src-2.e-24 = YES. src-2.f25 = 5.68. src-2.f26 = NOW.
CREATE src-2. src-2.e-21 = 2. src-2.e-24 = NO. src-2.f25 = 12.83. src-2.f26 = ?.
CREATE src-2. src-2.e-21 = 3. src-2.e-24 = NO. src-2.f25 = 17.31. src-2.f26 = NOW.
CREATE src-2. src-2.e-21 = 4. src-2.e-24 = YES. src-2.f25 = 1.01. src-2.f26 = NOW.
DEFINE DATA-SOURCE ds2 FOR src-2 KEYS (e-21).
DEFINE TEMP-TABLE src-3
FIELD f36 AS DATETIME
FIELD f37 AS HANDLE
FIELD f38 AS DATETIME-TZ.
CREATE src-3. src-3.f36 = NOW. src-3.f37 = THIS-PROCEDURE:HANDLE. src-3.f38 = NOW.
DEFINE DATA-SOURCE ds3 FOR src-3.
DEFINE TEMP-TABLE tt1 BEFORE-TABLE btt1
FIELD f11 AS INTEGER
FIELD f12 AS CHARACTER
FIELD f13 AS DATE
INDEX idx1 IS UNIQUE f11.
DEFINE TEMP-TABLE tt2 BEFORE-TABLE btt2
FIELD f21 AS INTEGER
FIELD f24 AS LOGICAL
FIELD f25 AS DECIMAL.
DEFINE TEMP-TABLE tt3
FIELD f36 AS DATETIME
FIELD f37 AS HANDLE
FIELD f38 AS DATETIME-TZ.
DEFINE DATASET my-dataset
FOR tt1, tt2, tt3
DATA-RELATION tt-link FOR tt1, tt2 RELATION-FIELDS (tt1.f11, tt2.f21).
BUFFER tt1:ATTACH-DATA-SOURCE(DATA-SOURCE ds1:HANDLE, "e-11,f11,e-12,f12,e-13,f13").
BUFFER tt2:ATTACH-DATA-SOURCE(DATA-SOURCE ds2:HANDLE, "e-21,f21", "f26"). // excluding f-26
BUFFER tt3:ATTACH-DATA-SOURCE(DATA-SOURCE ds3:HANDLE, "", ?, "f36,f37,f38"). // including f-36, f-37, and f-38
DATASET my-dataset:FILL().
BUFFER tt3:FILL().
MESSAGE "~nFilled dataset:".
FOR EACH tt1:
MESSAGE "tt1:" RECID(tt1) ROW-STATE(tt1) tt1.f11 tt1.f12 tt1.f13.
END.
FOR EACH tt2:
MESSAGE "tt2:" RECID(tt2) ROW-STATE(tt2) tt2.f21 tt2.f24 tt2.f25.
END.
FOR EACH tt3:
MESSAGE "tt3:" RECID(tt3) ROW-STATE(tt3) tt3.f36 tt3.f37 tt3.f38.
END.
// enable tracking changes on tt1:
TEMP-TABLE tt1:TRACKING-CHANGES = TRUE.
// create a record
CREATE tt1.
tt1.f11 = 100.
tt1.f12 = "one hundred".
// delete a record
FIND LAST tt1 WHERE f11 < 100.
DELETE tt1.
// modify a couple of records
FIND FIRST tt1.
tt1.f11 = -1.
tt1.f12 = "negative one".
BUFFER tt1:ERROR = TRUE.
FIND NEXT tt1.
tt1.f12 = "second".
MESSAGE "~nView tracked changes:".
FOR EACH btt1:
MESSAGE RECID(btt1) ROW-STATE(btt1) btt1.f11 btt1.f12 btt1.f13.
END.
// save these changes
FOR EACH btt1:
IF NOT BUFFER btt1:ERROR THEN
BUFFER btt1:SAVE-ROW-CHANGES().
END.
MESSAGE "~nView result:".
FOR EACH src-1:
MESSAGE RECID(src-1) src-1.e-11 src-1.e-12 src-1.e-13 src-1.e-14 src-1.e-15.
END.The converted Java counterpart is rather long and composed from multiple sources (procedure plus DMO interfaces for all tables used in the example) so it is not posted here.
After converting and executing the procedure the captured output should be something similar to:
View source table #1: 4352 1 1 08/02/22 yes 1.1 4353 2 22 08/01/22 yes 1.2 4354 3 333 08/04/22 no 3.1 4355 4 4444 07/30/22 yes 1.5 4356 5 55555 08/06/22 no 6.1 4357 6 666666 07/28/22 yes 1.9 Filled dataset: tt1: 16640 0 1 1 08/02/22 tt1: 16641 0 2 22 08/01/22 tt1: 16642 0 4 4444 07/30/22 tt1: 16643 0 6 666666 07/28/22 tt2: 24832 0 2 no 5.68 tt2: 24833 0 2 no 12.83 tt2: 24834 0 4 no 1.01 tt3: 33024 ? 08/02/2022 12:26:18.595 1000 08/02/2022 12:26:18.598+03:00 tt3: 33025 ? 08/02/2022 12:26:18.595 1000 08/02/2022 12:26:18.598+03:00 View tracked changes: 20737 1 6 666666 07/28/22 20738 2 1 1 08/02/22 20739 2 2 22 08/01/22 20736 3 0 ? View result: 4352 1 1 08/02/22 yes 1.1 4353 2 second 08/01/22 yes 1.2 4354 3 333 08/04/22 no 3.1 4355 4 4444 07/30/22 yes 1.5 4356 5 55555 08/06/22 no 6.1 4358 100 one hundred ? no 0