Transformations

Transformations are captured in worksheets (Sheets). These are organized in directories.

A single transformation requires sources and targets and uses a specific module.

Directories

Worksheets

Transformations are grouped in worksheets.

Worksheets are the smallest deployment unit.

Objects

An object is defined by the object type and additional properties such as Connection, name, schema, path, etc.

Furthermore, at the object level, you can determine whether the connection to other sheets should be considered as a dependency or not.

This is configured in the GUI in the Dependency field. In this field, you set Wait = "Wait for transformations from other sheets" or Ignore = "Do not wait".

Additionally, you can refresh the metadata from the specified connection in the object editor.

Furthermore, for database objects, you can have an (export) script generated that describes the object. This script can be used to generate the DDL for creating the object. You can execute excerpts from the script directly on the target database, or use them for artifact definitions.

The code generated is based on a Freemarker template. This template is defined in the table vv_sqts_config_global:

Type	config_key1	config_key2	config_key3	config_key4
Export	VARIABLE	Template	TABLE_SCRIPT	EXPORT

There are two sets of variables. The first set provides the variables listed below. Another set is for the base table if it is an object that is based on exactly one other object (for views that have exactly one table as source). In this set, all variables have the prefix BASE_. If there is exactly one object that the object is based on, this set is filled with the information of the base table. Otherwise, this set corresponds to the first set.

The following variables are made available in Freemarker:

Variable	Data Type	Meaning
DATABASE_ID	Number	The connection of the object
SCHEMA_NAME	String	The schema name
OBJECT_NAME	String	The object name
OBJECT_TYPE	String	The object type. Currently either TABLE or VIEW
PK_NAME	String	The primary key name
OBJECT_COMMENT	String	The object comment
SCHEMA_NAME_LOWER	String	Currently not supported
OBJECT_NAME_LOWER	String	Currently not supported
columns	List	List of columns of the object

Each object has a list of names and their values. The following names are available:

Name	Data Type	Meaning
DATABASE_ID	Number	The connection of the object
SCHEMA_NAME	String	The schema name
OBJECT_NAME	String	The object name
COLUMN_NAME	String	The column name
COLUMN_POSITION	Number	Position of the column within the object
COLUMN_TYPE	Number	The data type
COLUMN_PK_POSITION	Number	If not null, position in primary key
COLUMN_COMMENT	String	The column comment
COLUMN_IS_NULLABLE	CHAR	"Y" nulls allowed, "N" not
COLUMN_SOURCE_TYPE	String	The data type as specified in the source system
SCHEMA_NAME_LOWER	String	Currently not supported
OBJECT_NAME_LOWER	String	Currently not supported
COLUMN_NAME_LOWER	String	Currently not supported

Using variables in Freemarker

An example to provide the full name of the object name for further use:

[#if SCHEMA_NAME?has_content]
  [#assign schema=SCHEMA_NAME?lower_case + "."]
[#else]
  [#assign schema=""]
[/#if]
[#assign object=OBJECT_NAME?lower_case]
[#assign name=schema+object]

Analogous for the base object:

[#if BASE_SCHEMA_NAME?has_content]
  [#assign base_schema=BASE_SCHEMA_NAME?lower_case + "."]
[#else]
  [#assign base_schema=""]
[/#if]
[#assign base_object=BASE_OBJECT_NAME?lower_case]
[#assign base_name=base_schema + base_object]

Recognize versioned tables:

[#-- base_tv and base_nv are placeholder for the versioned / non versioned table name with schema --]
[#assign base_tv=base_schema +base_object]
[#assign base_nv=base_schema +base_object]
[#if base_name?starts_with("tv_")]
  [#assign base_nv=base_schema + base_object[3..]]
[#else]
  [#assign base_tv=base_schema + "tv_" + base_object]
[/#if]

Recognize columns that do not belong to historization / versioning

[#-- list of historize columns --]
[#assign hist_cols = ["upd_by", "ins_by", "is_latest_period","last_changed_dt", "valid_from_dt", "invalid_from_dt", "is_current_and_active", "is_deleted"]]
[#-- generate the list data_cols with all columns which are not sqdv columns --]
[#assign data_cols = []]
[#list baseColumns as col]
  [#if !(col["COLUMN_NAME"]?lower_case?starts_with("sqdv_")) && !hist_cols?seq_contains(col["COLUMN_NAME"]?lower_case)]
    [#assign data_cols += [col] ]
  [/#if]
[/#list]

Filter out columns from primary key columns that are not part of versioning

[#-- generate the list pkCols with all primary key columns which are not sqdv columns --]
[#assign pkCols = []]
[#list baseColumns as col]
  [#if col["COLUMN_PK_POSITION"]?? && !(col["COLUMN_NAME"]?lower_case?starts_with("sqdv_")) && !hist_cols?seq_contains(col["COLUMN_NAME"]?lower_case)]
    [#assign pkCols += [col] ]
  [/#if]
[/#list]
[#assign pkCols = pkCols?sort_by("COLUMN_PK_POSITION")]

A function to prepare column types for the target environment

[#function toTargetType columType]
  [#local colType = columType?upper_case]
  [#local pos = colType?index_of("(")]
  [#if pos != -1]
    [#local colTypeFirst = colType?substring(0, pos)?trim]
    [#local colTypeLast = colType?substring(pos)?trim]
    [#local colTypeLast = colTypeLast?replace(" CHAR", "")?replace(" BYTE", "")]
  [#else]
    [#local colTypeFirst = colType?trim]
    [#local colTypeLast = ""]
  [/#if]
  [#if ["BIGINT", "INTEGER", "SMALLINT", "TINYINT"]?seq_contains(colTypeFirst)]
    [#local colTypeFirst = "INT"]
  [#elseif ["TIMESTAMP", "TIMESTAMP WITHOUT TIME ZONE"]?seq_contains(colTypeFirst)]
    [#local colTypeFirst = "DATETIME"]
  [#elseif ["VARCHAR2", "CHARACTER VARYING"]?seq_contains(colTypeFirst)]
    [#local colTypeFirst = "VARCHAR"]
  [#elseif ["CHARACTER"]?seq_contains(colTypeFirst)]
    [#local colTypeFirst = "CHAR"]
  [#elseif ["NUMBER", "NUMERIC"]?seq_contains(colTypeFirst) && colTypeLast=""]
    [#local colTypeFirst = "DECIMAL(15,2)"]
  [#elseif ["NUMBER", "NUMERIC"]?seq_contains(colTypeFirst)]
    [#local colTypeFirst = "DECIMAL"]
  [#elseif ["CLOB", "TEXT"]?seq_contains(colTypeFirst)]
    [#local colTypeFirst = "VARCHAR(MAX)"]
    [#local colTypeLast = ""]
  [/#if]
  [#return colTypeFirst + colTypeLast]
[/#function]

Output of the versioned table for non-SQDV versioned tables

DROP TABLE IF EXISTS ${base_tv};

-- Versioned table with data types of target database
CREATE TABLE ${base_tv} (
    [#list data_cols as col]${col["COLUMN_NAME"]?lower_case} ${toTargetType(col["COLUMN_TYPE"])}[#if col["COLUMN_IS_NULLABLE"] == 'N'] NOT NULL[/#if][#sep]
  , [/#list]
  , is_latest_period CHAR(1) NOT NULL
  , is_current_and_active CHAR(1) NOT NULL
  , is_deleted CHAR(1) NOT NULL
  , last_changed_dt DATETIME NOT NULL
  , valid_from_dt DATETIME NOT NULL
  , invalid_from_dt DATETIME NOT NULL [#if pkCols?size > 0]
  , PRIMARY KEY([#list pkCols as pk]${pk["COLUMN_NAME"]?lower_case}, [/#list]invalid_from_dt)[/#if]
);

Transformation

Module

Validation