OUTPUT SQL

The OUTPUT SQL operator allows data from the input data bus stream to be written to a selected SQL database. It is used to add new records or modify existing data in database tables.

The operator can handle relationships between tables, such as parent–child structures, allowing data to be inserted into multiple related tables within a single operation. This relationship is implemented by grouping data from the data bus based on the bits selected for the main (parent) table, ensuring that the data is properly linked and stored according to the hierarchy.

A built-in primary key standardization mechanism makes it possible to retain mappings between the original data keys and newly created records. Based on this, the operator decides whether to perform an INSERT operation (adding a new record) or an UPDATE operation (modifying an existing record). It supports auto-increment keys generated by the database and can also calculate new key values itself when required.

Mapping data bus bits to table columns is done using a convenient user interface that allows data mappings to be configured easily.

The OUTPUT SQL operator is key to integrating data with relational databases and enables comprehensive management of data persistence within a GRAVITY project.

Example of a GRAVITY project using the OUTPUT SQL operator:

DATABASE CONNECTION CONFIGURATION

SELECTING AND CREATING A DATABASE CONNECTION

Go to the Physical link tab in the operator properties.

If you already have a connection defined, select it from the Connection drop-down list; the connection fields will be populated according to the selected connection.

You can modify the connection parameters by clicking the Edit button.

Keep in mind that if the connection is already used by another operator, that operator will also use the modified connection.

If you do not yet have an appropriate database connection defined, you can always add one using the Add button.

After entering the details of the new connection, you can test it using the Test connection button and save it in the system by clicking Save.

After saving, the connection will be available in the system under the name specified in the Name field.

For more information on creating database connections, see New SQL Database Connection.

DECLARING ACTIONS WHEN A DUPLICATE IS DETECTED

Decide how the system should behave when a duplicate is detected in the target database.

You can choose between two possible behaviors when duplicates are detected while adding records to the target database:

SKIP → the operator skips records that have already been processed if the value of the primary key defined on the table (in the data write diagram) matches the value of the data bus bit used as the PK field value in the table definition. This option applies only to PK definitions that use standardization.
PASS → the operator processes all records from the input data bus. If you select the PASS option, the behavior of the process in duplicate cases depends on the actions specified in the SQL Actions option in the data write diagram.

Duplicate detection works only for tables whose primary key in the operator definition is set to standardization, standardization or get max or standardization or identifying. Otherwise, duplicate detection is ignored and all records from the data bus are passed on for processing.

DELETE OLD RECORD → the existing record is deleted and a new record is added.
UPDATE OLD RECORD → the existing record is overwritten with the new record.

DECLARING THE DATA STREAM INSERT SCHEMA

The data stream insert code is generated automatically based on the diagram you have configured (see the SHOW DIAGRAM TO DEFINE OUTPUT option).

The automatically generated code is in XML format and can also be modified manually.

If you want to revert to the template code generated from the diagram, simply use the REFRESH INSERT CODE option.

PRIMARY KEY STANDARDIZATION

WHAT IS STANDARDIZATION? Standardization is the process of replacing a field (column) value with a standard value based on a dictionary that maps the original value to a standardized one. In the context of the OUTPUT SQL, the standardization dictionary is created automatically—ad hoc—during processing performed by the OUTPUT SQL operator when a situation requiring replacement of PRIMARY KEY field (column) values is encountered. The standardization dictionary is created automatically by GRAVITY and stored in the target database.

WHY DO WE STANDARDIZE? The data stream stored in the target table may originate from multiple independent source databases. Because of this, in order to store all data in a single database, you must decide to change the values of unique (primary key) fields, as these values may be identical across different sources.

HOW DOES STANDARDIZATION WORK? Technically, this is an automatic process of creating a recoding dictionary. The recoding (standardization) dictionary is associated with the target table and stored in the target database in specially created tables named “ve_[target_table_name]”. Additionally, the target table is extended with two required fields, “__gravity_id__” and “__gravity_process_id__”, which make it possible to locate standardized records. The recoding links the source identifier (source bit) and the old primary key field values with the newly created unique identifiers in the target table.

If you want to view the records stored in the standardization dictionaries, select the SHOW CACHE TABLES option in the operator properties.

You will see a window containing all tables handled by the OUTPUT SQL operator. After selecting a table, the standardized records are displayed in the lower list.

You can delete standardization entries using the buttons below the list of supported tables in the target database.

You will most likely use the option to remove standardization entries during configuration and testing of the process (similar to removing data added to the target table). Keep in mind, however, that after deleting data from the standardization tables, records that have already been written to the target database will be added again when the process is executed.

SELECTING THE TARGET TABLE AND DEFINING DATA STREAM INSERTION RULES

STEP 1

On the right side of the window, you can see all tables located in the target database specified in PHYSICAL LINK.

The target table is selected by dragging the appropriate table and dropping it onto the workspace.

If you do not yet have a table in the database, you can create one by clicking Create table.

You can place multiple tables on the workspace as target tables, but you must link them by defining a parent–child relationship. If you place multiple tables independently (without defining a relationship), the process will be stopped during execution.

After selecting the target table, you can proceed to define the rules for inserting the data stream into the table.

STEP 2

Now indicate the PRIMARY KEY fields (a PRIMARY KEY is a set of columns that uniquely identify a record) by selecting the appropriate columns.

STEP 3

You should now specify how the process should behave with respect to the PRIMARY KEY fields when inserting a record (this option is available in the table header bar).

The choice of option depends on how the table’s primary key is defined in the database.

You can choose the following actions for the PRIMARY KEY fields:

NOTHING

Use this option when you want to write every data bus record to the target table in the database. Use when the table does not manage PK values automatically.

No standardization action is performed, and the value of the bit assigned to the primary key is not modified. Each record is written to the database exactly in the form in which it appeared on the data bus.

GET MAX

Use this option when you want to add new records to the table or modify existing ones (the primary key value on the data bus comes from the target table, for example when building API functions for the system), and the table does not have auto-increment enabled on the primary key field.

The operator behaves in two different ways depending on the value of the bit corresponding to the primary key field.

If the bit has a value of 0 or less, a new record is added to the database. The process calculates a new value for the last PRIMARY KEY field by incrementing the last record in the target table. The data bus bit corresponding to the primary key value is populated with the primary key value written to the database. The standardization dictionary is not built.
If the bit has a value greater than 0, the operator performs no action or—depending on the action selected in the SQL Actions area—updates the record (UPDATE OLD RECORD) or deletes the record (DELETE OLD RECORD).

IDENTITY

Use this option when you want to add new records to the table or modify existing ones (the primary key value on the data bus comes from the target table, for example when building API functions for the system), and the table has auto-increment enabled for the primary key field.

The operator behaves in two different ways depending on the value of the bit corresponding to the primary key field:

If the bit has a value of 0 or less, a new record is added to the database. The database calculates a new value for the primary key field. The data bus bit corresponding to the primary key value is populated with the primary key value written to the database. The standardization dictionary is not built.
If the bit has a value greater than 0, the operator performs no action or—depending on the action selected in the SQL Actions area—updates the record (UPDATE OLD RECORD) or deletes the record (DELETE OLD RECORD).

STANDARDIZATION OR IDENTIFYING

Select this action when you are transferring data between databases and want records that have already been written to the target database not to be written again. The primary key value from the source database does not match the value in the target database, and the target database table has auto-increment enabled on the primary key field.

If you select this action, you will need to specify a source identifier, either by selecting the appropriate input data bus bit (if such a bit exists) or by entering a custom name that will always be used for this data source.

The combination of the primary key and the data source identifier forms a unique standardization entry.

The system checks whether the given primary key value from the data bus and the source identifier already exist in the standardization table.

If not, a new record is added to the database, and the primary key value generated by the database together with the primary key value originating from the data bus are stored as a standardized pair in the standardization table. The generated primary key value is written to the bit assigned to the primary key field.
If so, the next step taken by the system depends on the selected action (the SQL Actions area).

→ if no action is selected, the system performs no operation.

→ if an action is selected, for example UPDATE OLD RECORD or DELETE OLD RECORD, the primary key values for the target table corresponding to the input primary key values from the data bus are retrieved from the standardization table. In the database, the record corresponding to the retrieved key is updated or deleted.

STANDARDIZATION OR GET MAX

Use this option when you are copying data between databases and want records that have already been written to the target database not to be written again. The primary key value from the source database does not match the value in the target database, and the target database table does not have auto-increment enabled on the primary key field.

If you select this action, you will need to specify a source identifier, either by indicating the appropriate input data bus bit (if such a bit exists) or by entering a custom name that will always be used for this data source.

The pair consisting of the primary key and the data source identifier forms a unique standardization entry.

The system checks whether the given primary key value from the data bus and the source identifier already exist in the standardization table.

If not, the value of the primary key bit is generated by the system based on the entries in the target table as MAX+1. Next, a new record is inserted into the database. The generated primary key value and the primary key value originating from the data bus are stored as a standardized pair in the standardization table. The generated primary key value is written to the bit assigned to the primary key field.
If so, the next step taken by the system depends on the selected action (the SQL Actions area).

→ if no action is selected, the system performs no operation.

→ if an action is selected, for example UPDATE OLD RECORD or DELETE OLD RECORD. The primary key values for the target table corresponding to the input primary key values from the data bus are retrieved from the standardization table. In the database, the record corresponding to the retrieved key is updated or deleted.

STANDARDIZATION

Use this option when you are transferring data between databases and want records that have already been written to the target database not to be written again, the primary key value from the source database matches the value in the target database, and the target database table does not have auto-increment enabled on the primary key field.

The system checks whether the given primary key value from the data bus and the source identifier already exist in the standardization table.

If not, a new record is inserted into the database with the primary key bit value taken from the data bus, and an entry is added to the standardization table.
If so, the next step taken by the system depends on the selected action (the SQL Actions area).

→ if no action is selected, the system performs no operation.

If you want to store the primary key from the source table—carried on the data bus—in the target table, first extend the data bus using the

WIDTH BUSBAR operator by adding an additional bit, for example old_pk, based on the primary key field, and add an old_pk column to the target table. Then link the bit and the column in the operator definition.

STEP 4

You should now indicate which bit on the input data bus maps to each field (column) of the target table. You can do this as follows:

Select a column from the input data bus (see the illustration below—the highlighted area on the left),
The system automatically indicates which fields can be matched by greying out the others (see the illustration below—the highlighted area on the right); the available fields are determined based on data type compatibility.
Select the table field by clicking the appropriate column in the target table.
Select the next input data bus column and repeat the steps.

STEP 5

If you want the input data bus stream to also handle tables that are subordinate to the main table, you can do this in the next step. Select the related table and place it on the workspace—this action is identical to the one used for the main table: grab the related table from the list of available tables (the list in the left-hand area) and drop it onto the workspace.

In the next step, select the column in the parent table—usually the primary key (PK)—that is related to a column in the child table. Then, by clicking, connect it to the corresponding column in the child table that defines this relationship (the foreign key, FK). A line representing the relationship will appear. Then repeat the configuration steps for the child table, starting from Step 2.

When standardizing fields that participate in a parent–child (primary key–foreign key) relationship, the system automatically ensures that the foreign key field in the child table receives the correct value. You can repeat this step any number of times, and the depth of nested child relationships is not limited.

ADDITIONAL ACTIONS You need to decide which actions you want to perform while inserting the data stream into the database. Actions are added in the SQL Actions area of the data write wizard window.

In the illustration above, you can see the NEW option selected, which allows you to add actions:

PRE type → actions performed before inserting the data stream into the database;
POST type → actions performed after inserting the data stream into the database;
IN type → actions performed during insertion, for example defining system behavior when an identical record (based on the PRIMARY KEY) is detected in the target database.

If you need detailed help regarding how individual actions work, enable the help option—you will see a window with explanations.

We explain each action in detail below.

DELETE ALL RECORDS

Before inserting the data stream (a PRE action), the target table will be cleared of all records.

DELETE ALL RECORDS FOR SOURCE

Before inserting the data stream as part of a PRE-type action, the system removes from the target table all records associated with the specified source. Records originating from other sources are not removed. The source is identified either by name (constant) or by the specified value of an input data bus bit (column).

DELETE DECLARED SPACE

Before inserting the data stream (a PRE action), the target table will be cleared of records within the declared scope.

There are two ways to specify which range of records should be deleted:

RANGE
CONSTANTS

The RANGE setting allows you to specify a range of values for the PRIMARY KEY column. Records that fall within this range will be deleted.

Use this option when you collect records from multiple sources in a single table, and only one column is available, unique identification is handled by defining numeric ranges for different sources (for example, source 1: range from 1 to 1,000,000; source 2: range from 1,000,001 to 2,000,000, etc.)

The CONSTANTS setting allows you to delete only the record whose PRIMARY KEY column value matches the value specified in the Constant value field. DELETE SPACE MIN MAX PK Before inserting the data stream (a PRE action), the target table will be cleared of records within the declared scope. The deletion scope will be determined by the range between the minimum and maximum values of the PRIMARY KEY (see the section SELECTING THE TARGET TABLE AND DEFINING DATA STREAM INSERTION RULES in this chapter for details on PK definition.)

Note: if the columns belonging to the primary key were subject to standardization—that is, their values were modified—GRAVITY will automatically detect this and correctly identify the appropriate record.

DELETE OLD RECORD

This action is a counterpart to UPDATE OLD RECORD and is executed for each record in the data stream (therefore an IN-type action). If you have set the input filter ACTION FOR THE SAME RECORD = PASS (see the description above), you can now specify how the GRAVITY process should handle a potential duplicate. If you select the DELETE OLD RECORD action, the existing record is deleted so that the new record (the potential duplicate) can be inserted without conflict.

Note: if the unique identifier of the data stream was subject to standardization—that is, its value was modified—GRAVITY will automatically detect this and correctly identify the appropriate record.

If you have set the input filter ACTION FOR THE SAME RECORD = SKIP, the DELETE OLD RECORD action will not be executed.

UPDATE OLD RECORD

A specific action (described as a counterpart to DELETE OLD RECORD) performed for each record in the data stream (and therefore an IN-type action). If you have set the input filter ACTION FOR THE SAME RECORD = PASS (see the description above), you can now specify how the GRAVITY process should handle a potential duplicate. If you select the UPDATE OLD RECORD action, the existing record will be overwritten by the new record (the potential duplicate).

If you have set the input filter ACTION FOR THE SAME RECORD = SKIP, the UPDATE OLD RECORD action will not be executed.

SETUP INPUT

An IN-type action is executed repeatedly—after each record is processed by the OUTPUT SQL operator. It allows you to modify, in the source database, a record that was previously loaded into the data bus by an INPUT SQL operator. This type of action is used, for example, when you want to set flags indicating that a record has been retrieved from the source.

Configuration scope:

INPUT → specify the INPUT-type operator from which data is loaded into the data bus
INPUT TABLE → specify the source table from which the INPUT operator retrieves data
COLUMN FOR UPDATE → specify the column in the source table that will be updated (for example, where a flag is set)
VALUE FOR UPDATE → specify the value to be written (for example, the flag value)
ID SOURCE RECORD → specify how the source record is identified (for example, when setting a flag); select a unique column from the source table and the corresponding column from the input data bus of the OUTPUT SQL operator

The SETUP INPUT provides extensive capabilities for interacting with source data. After records are retrieved, you can mark them as already retrieved. This allows you to perform data retrieval in a strictly incremental manner by defining a retrieval condition based on a selected flag value in the INPUT operator.

USER SQL

An action that gives you full flexibility. You can create any SQL code and specify when it should be executed (see the illustration below):

PRE → before the data stream insertion action
POST → after the data stream insertion action
IN → during data stream insertion—note: in this case, the code is executed repeatedly, once after each record in the data stream is inserted

When writing your own SQL code, you can use the values available on the input data bus of the OUTPUT SQL operator. You reference a data stream column value by using the leading @ symbol followed by the name of the bit (column) in the input data stream (for example, @id).

SAVING THE CONFIGURATION

Once you have configured all actions and declared the input tables for the data stream, you need to save your diagram.

When saving, GRAVITY automatically generates code in XML format. You can view this code on the INSERT CODE tab.

The sections above describe how to use the OUTPUT SQL operator. We are aware that this is one of the most complex components of GRAVITY; therefore, to better illustrate how the OUTPUT SQL operator works, we encourage you to review the examples (see the USE CASE EXAMPLES chapter.) In addition to standard contextual examples, the OUTPUT SQL operator is also covered in a dedicated section titled USING THE OUTPUT SQL OPERATOR.