FreeMarker Variables
The datasqill module DsModTemplateSQL passes variables to the FreeMarker interpreter (in addition to the FreeMarker script) so that the script can generate the code based on the tables and columns involved and the configured parameters.
The script can now generate the effective SQL based on the variables.
If you want to generate the INSERT INTO with the correct table (and optional schema name), it looks like this in FreeMarker:
INSERT INTO ${target}
If the target object in the datasqill model is now the table holiday in schema SOFTQUADRAT, then the code
INSERT INTO SOFTQUADRAT.holiday
is generated.
The variable target is provided by DsModTemplateSQL.
The module always provides the following variables:
| Name | Data Type | Meaning |
|---|---|---|
| database_product_name | String | Database name according to response from JDBC driver |
| action | String | The transformation instruction (SQL) |
| action_id | Number | ID of the action (transformation) |
| batch_instance_id | Number | ID of the batch instance (running batch), or -1 during Validate |
| request_id | Number | ID of the unique request (see request table) |
| schedule_date | String | Start date in UTC time of the batch in format yyyy-MM-ddTHH:mm:ss.SSSSSSSSSZ |
| module_start_timestamp | String | Module execution start in local time in format yyyy-MM-dd HH:mm:ss.SSS |
| module_start_datetime | String | Module execution start in local time in format yyyy-MM-dd HH:mm:ss |
| module_start_date | String | Module execution start in local time in format yyyy-MM-dd |
| module_utc_start_timestamp | String | Module execution start in UTC time in format yyyy-MM-dd HH:mm:ss.SSS |
| module_utc_start_datetime | String | Module execution start in UTC time in format yyyy-MM-dd HH:mm:ss |
| module_utc_start_date | String | Module execution start in UTC time in format yyyy-MM-dd |
| connection_id | Number | Database ID (Connection ID) of the database connection |
| target_connection_id | Number | Database ID (Connection ID) of the database connection |
| target_schema | String | Schema name of the target table. May be empty in the future |
| target_schema_quoted | String | Schema name of the target table of the target database in identifier quotes, if necessary |
| target_table | String | Table name of the target table |
| target_table_quoted | String | Table name of the target table of the target database in identifier quotes, if necessary |
| target | String | Schema name with "." (if given) and table name of the target table each in quotes, if necessary |
| target_partition | String | Partition name of the target table |
| sources | Object | A list of source objects of data type ObjectDs |
| iteration | Number | For Loop Queries there is a sequential number per loop result. Otherwise 1 |
| all_tgt | TemplateColumn | List of all columns in the target table |
| all_src | TemplateColumn | List of all columns in the source query |
| other_tgt | TemplateColumn | List of all columns without those defined by parameters in the target table |
| other_src | TemplateColumn | List of all columns without those defined by parameters in the source table |
| loopVariable | Object | A map of loop results consisting of name and value e.g. loopVariable.anzahl |
Variables when the transformation has exactly one source table:
| Name | Data Type | Meaning |
|---|---|---|
| source_schema | String | Schema name of the source table |
| source_table | String | Table name of the source table |
| source_partition | String | Partition name of the source table |
Variables from attributes:
In datasqill you can store module attributes that are then displayed and can be edited in the datasqill GUI.
For template purposes you can define these attributes. They can then be used in the template. For example, in the Historize module you can specify whether deleted data should be considered during comparison (Boolean), or which keys define the primary key of the historization (TemplateColumn). Individual columns (TemplateColumn) are also stored there (invisible to the user). These defined attributes are made available to the FreeMarker interpreter as variables with the attribute name.
Attributes of type BOOLEAN are passed as boolean variables. When attributes are of type CHAR and begin with "RC:", "RL:", "OC:" or "OL:", they are converted into a list of TemplateColumn. Otherwise they are passed directly as String.
Optional and Required Columns, or Column Lists
| Attribute value beginning | Data Type | Meaning |
|---|---|---|
| OC: | Optional Column | The user specifies one or no column |
| OL: | Optional List | A comma-separated list of columns. It may be empty |
| RC: | Required Column | The user must specify exactly one column |
| RL: | Required List | A comma-separated list of columns. It must not be empty |
Examples from the Historize module:
| Name | Value | Comment |
|---|---|---|
| is_deleted | OC:is_deleted | The column is optional. If not given, the template logic changes |
| key | RL:${tgt_pk_list} | The column list is required. In this example the primary keys of the target database are used |
| key | RL:spalte1,spalte2 | The column list is required. In this example they are specified manually |
| ignore_columns | OL: | The column list is not required. All specified columns are ignored during Historize |
The ObjectDs Data Type
The ObjectDs data type is derived directly from the vv_sqts_object table in the repository database:
| Name | Data Type | Comment |
|---|---|---|
| sqtsObjectId | Number | Unique ID of the object (table) |
| sqtsObjectTypeId | Number | ID of the object type. |
| serverId | Number | ID of the connection (database_id) |
| objectName | Number | Name of the object (table name) |
| objectOwner | Number | Name of the owner (schema name) |
| objectPartition | Number | Partition name |
The TemplateColumn Data Type
Usually the column variables mentioned above are a list of data type TemplateColumn. The TemplateColumn data type looks like this:
| Name | Data Type | Comment |
|---|---|---|
| name | Number | Name of the column, possibly quoted |
| unquotedName | Number | Name of the column |
| nullable | Boolean | Indicates whether the column can accept the value NULL |
Some examples for using variables in FreeMarker
Using a boolean variable
[#if with_delete!false]Text1[#else]Text2[/#if]
If with_delete is true, it results in:
Text1
If with_delete is false (or not set, specified with !false), it results in:
Text2
A module runs only on Oracle or Postgres. Abort for an unsupported database
[#if !["Oracle","PostgreSQL"]?seq_contains("${database_product_name}")]
[#stop 'This Module does not support ' + database_product_name + " databases"]
[/#if]
Building a WHERE condition where 2 tables are joined on the same primary key (specified via the key attribute):
WHERE [#list key as col]src.${col.name} = tgt.${col.name} [#sep]
AND [/#list]
[#sep] causes the following part not to be generated after the last column.
If the key consists of spalte1 and spalte2, the following code is generated:
WHERE src.spalte1 = tgt.spalte1
AND src.spalte2 = tgt.spalte2
If the key consists of spalte1 and spalte-2, the following code is generated for MySQL database:
WHERE src.spalte1 = tgt.spalte1
AND src.spalte2 = tgt.`spalte-2`
For most other databases the following code is generated:
WHERE src.spalte1 = tgt.spalte1
AND src.spalte2 = tgt."spalte-2"
Output all database sources
[#list sources as src][#if "${src.sqtsObjectTypeId}" == "1"]${src.objectOwner}.${src.objectName}
[/#if][/#list]
If the two tables tab1 and tab2 in schema SOFTQUADRAT were specified as database sources (there can also be other sources), the output would be:
SOFTQUADRAT.tab1
SOFTQUADRAT.tab2