Triggers

datasqill enables freely definable triggers for certain functionalities. These are used to trigger specific activities in the database at certain points in time (e.g. after establishing a connection, after writing, etc.) or to enable non-standardized functionalities (e.g. Truncate Table). Multiple commands can be executed for a trigger. These are grouped in statement sets. Triggers can be defined at different levels:

• Per connection/database type in the entity vv_datasqill_database_type_trigger
• Per connection/database in the entity vv_datasqill_database_trigger
• Per transformation in the entity vv_datasqill_action_trigger

Each table contains the ID of the referenced object (connection type, connection or transformation), as well as the trigger name trigger_name and the statement set name statement_set_name.

Trigger definitions per transformation override triggers per connection.

Trigger definitions per connection override triggers per connection type.

Triggers from a previous hierarchy can be deleted by specifying empty triggers (statement_set_name empty or NULL).

Each module defines in the entity vv_datasqill_module_trigger which triggers it can execute.

In addition to the triggers that are to be triggered before or after an event, "Instead of Triggers" can also be defined. These are executed when a certain functionality is required. Currently only the Instead of Truncate trigger is used. This is called when a complete table is to be cleared. If the trigger is not present, the table is completely cleared with the SQL DELETE command.

Trigger definitions per connection or connection type are saved with "Export of Connections" in the GUI and transferred to the datasqill target environment during deployment.

Trigger definitions per action are saved with the respective worksheet (Sheet) and transferred to the datasqill target environment during deployment of the sheet.

Statement Sets

A statement set contains one or more individual commands that are executed in the database. It is stored in the repository in the entity vv_datasqill_statement_set. The combination of the name statement_set_name and the order sorting results in the complete set that can be executed for a trigger.

For the execution of the statement command, 3 different methods method can be selected. In the ignore_errors column, it is specified per executing command whether occurring errors should be ignored.

Method execute

The specified command statement is executed as a Java Statement with the executeUpdate method.

Method call

The specified command statement is executed as a Java CallableStatement with the execute method. A call corresponding to the database must be used. Many databases support the escape syntax:

{call procedure_xy()}

Otherwise, the native procedure of the target database must be used.

Method select statistics

The specified command statement is a Select statement. For each row returned from this statement, an entry is inserted in sqts_request_result. This method is used to obtain statistical information about the database or the executed commands. The following column names from the Select statement must be used:

• stat_name: Name of a statistic value. If the name is contained in the entity vv_sqts_result_type and the log_enabled flag is set to 'Y', then this value is transferred to sqts_request_result.
• stat_value_numeric: For numeric values, the corresponding value (h2 data type BIGINT).
• stat_value_character: For character strings, the corresponding value (h2 data type VARCHAR). Not yet stored.

Using Variables

The following variables can be used in the specified commands:

• batch_instance_id: Sequence number of the current "Run"
• schedule_date: Start date and time of the batch in format yyyy-MM-ddTHH:mm:ss:SSSZ
• action_id: ID of the current transformation
• request_id: System-unique ID of this execution
• connection_id: Connection in which this statement is executed
• module_start_timestamp: Start date and time in local time of the transformation in format yyyy-MM-dd HH:mm:ss.SSS
• module_start_datetime: Start date and time in local time of the transformation in format yyyy-MM-dd HH:mm:ss
• module_start_date: Start date in local time of the transformation in format yyyy-MM-dd
• module_utc_start_timestamp: Start date and time in UTC of the transformation in format yyyy-MM-dd HH:mm:ss.SSS
• module_utc_start_datetime: Start date and time in UTC of the transformation in format yyyy-MM-dd HH:mm:ss
• module_utc_start_date: Start date in UTC of the transformation in format yyyy-MM-dd
• target_connection_id: Connection of the target table
• target_schema: Schema name of the target table
• target_schema_quoted: Schema name of the target table in quotes if it contains special characters
• target_table: Name of the target table
• target_table_quoted: Name of the target table in quotes if it contains special characters
• target_partition: Name of the target partition or empty string if none specified
• target: Name of the target table if target schema is empty. Otherwise target schema dot and target table (schema.table)
• sources: List of all sources. Each element has columns corresponding to a datasqill object:
  • sqtsObjectId = sqts_object_id
  • sqtsObjectTypeId = 1 for database objects
  • serverId = Connection of the source table
  • objectOwner = Schema
  • objectName = Table name
  • objectPartition = Partition
• source_schema: Only set when there is exactly one source of type database object. Then schema name of the source table
• source_table: Only set when there is exactly one source of type database object. Then name of the source table
• source_partition: Only set when there is exactly one source of type database object. Then partition of the source table
• iteration: Sequence number of the iteration when there is a loop query. Otherwise 1

datasqill uses Freemarker for variable substitution. Freemarker is configured such that symbols are specified in square brackets rather than angle brackets. Variables are specified in curly brackets with a leading dollar sign.

Examples of commands:

Truncate a table for Postgres

TRUNCATE TABLE ONLY ${target} RESTART IDENTITY

Vacuum a table for Postgres

BEGIN; END WORK; VACUUM ANALYSE ${target}; END

Call a grant function with read rights for each source database object (sqtsObjectTypeId=1) in an Oracle database and a grant function with write rights for the target table

BEGIN
[#list sources as src][#if "${src.sqtsObjectTypeId}" == "1" && "${src.serverId}" == "${connection_id}"]
  dsrt_grant_object('${src.objectOwner}', '${src.objectName}', 'SELECT');[/#if][/#list][#if target_table?has_content && "${target_connection_id}" == "${connection_id}"]
  dsrt_grant_object('${target_schema}', '${target_table}', 'SELECT,INSERT,UPDATE,DELETE');[#else]NULL;[/#if]
END;

Reading the statistics of the used temp space and the unique session number for Oracle (Method select statistics)

SELECT 'tempspace' stat_name
     , MAX(ses_hist.temp_space_allocated) stat_value_numeric
     , NULL stat_value_character
  FROM (SELECT NVL(qc_session_id, session_id) session_id
             , NVL(qc_session_serial#, session_serial#) session_serial#
             , sample_id
             , SUM(temp_space_allocated) temp_space_allocated
          FROM v$active_session_history
         GROUP BY NVL(qc_session_id, session_id)
                , NVL(qc_session_serial#, session_serial#)
                , sample_id
       ) ses_hist
     , v$session ses
 WHERE ses.sid = ses_hist.session_id(+)
   AND ses.serial# = ses_hist.session_serial#(+)
   AND ses.audsid = SYS_CONTEXT('USERENV', 'SESSIONID')
UNION ALL
SELECT 'session_id' stat_name
     , TO_NUMBER(SYS_CONTEXT('USERENV', 'SESSIONID')) stat_value_numeric
     , NULL stat_value_character
  FROM dual

Reading the unique session number for Exasol (Method select statistics)

SELECT 'session_id' stat_name
     , CURRENT_SESSION stat_value_numeric
     , NULL stat_value_character

Logging of the statistic in sqts_request_result must be enabled for this

INSERT INTO vv_sqts_result_type(result_type_id, result_type_name, result_type_desc, data_type, log_enabled)
SELECT NVL(result_type_id, max_result_type_id + 1) result_type_id
     , 'session_id' result_type_name
     , 'Database Session or Transaction ID' result_type_desc
     , 'N' data_type
     , 'Y' log_enabled
  FROM (SELECT COUNT(*) cnt
             , MAX(result_type_id) result_type_id
          FROM vv_sqts_result_type
         WHERE result_type_name = 'session_id'
       )
     , (SELECT MAX(result_type_id) max_result_type_id
          FROM vd_sqts_result_type
         WHERE result_type_id < 1000
       )