The new Validate Against DB functionality works in the following stages:
Stage | Operations | Configuration |
---|---|---|
Validate |
| Table/column information SQL Block Procedure. Configured on the Target Connection. |
Alter |
| Alter DDL Template. Configured the Table. The default is configured on the Target connection. |
Create DDL
The Create DDL for the table to be validated/altered MUST use any of the following tokens to refer to the name of the table: $OBJECT$, $TABLE$ or [TABLEOWNER].[table_name
].
Without these tokens, the temporary "expected" table cannot created reliably as only these tokens can be replaced with temporary table names for the temporary table creation.
The Create DDL is usually generated by a template and so these tokens need to be specified in the template itself unless using the Override Create DDL.
The generated Create DDL for a temporary table is validated to make sure it contains the unique temporary table name. If not for interactive validation the user is warned and given the chance to continue but for application deployment, the validation is skipped to avoid the potential dropping of a data warehouse table
Drop Table DDL
The Drop Table DDL statement MUST use any of the following tokens to refer to the name of the table: $OBJECT$, $TABLE$
. Otherwise, the dropping of the temporary table after the validation process may drop the actual data warehouse table.
Table/Column information SQL Block
These are some of the characteristics that this procedure needs to be created:
- This is a SQL Block Procedure object that is configured on the target connection.
- SQL Blocks are a sub-type of the Procedure object in RED.
- The SQL Block must contain exactly two (2) separated statements by the configured end-of the statement string (<EOS>, by default).
- Each statement must return one result set.
- Each result set must contain the specified columns below in the correct order.
- The data types shown below are the maximum sizes to be processed.
- Additional information is returned in triples of extra columns at the end of the result set. This additional information is compared during validation and is provided to the template used to generate alter DDL.
Statement 1 - Table information
Columm | Type | Description |
---|---|---|
1 | VARCHAR(128) | catalog |
2 | VARCHAR(128) | schema |
3 | VARCHAR(128) | table name |
4 + (i * 3) | VARCHAR(128) | additional information key |
5 + (i * 3) | VARCHAR(128) | additional information description |
6 + (i * 3) | VARCHAR(128) | additional information value |
Where i ranges from 0 to the number of additional information keys are exclusive.
Statement 2 - Column information
Columm | Type | Description |
---|---|---|
1 | VARCHAR(128) | catalog |
2 | VARCHAR(128) | schema |
3 | VARCHAR(128) | table name |
4 | INTEGER | ordinal position |
5 | VARCHAR(128) | column name |
6 | VARCHAR(128) | data type |
7 + (i * 3) | VARCHAR(128) | additional information key |
8 + (i * 3) | VARCHAR(4000) | additional information description |
9+ (i * 3) | VARCHAR(128) | additional information value |
Where i ranges from 0 to the number of additional information keys are exclusive.
The following example uses the standard information_schema
views. It is also the default if nothing is configured
SELECT table_catalog, table_schema, table_name FROM information_schema.tables WHERE UPPER(table_catalog) = UPPER('$DATABASE$') AND UPPER(table_schema) = UPPER('$SCHEMA$') AND UPPER(table_name) = UPPER('$TABLE$') ORDER BY table_catalog, table_schema, table_name <EOS> SELECT table_catalog, table_schema, table_name, ordinal_position, column_name, CONCAT(data_type, CASE WHEN COALESCE(character_maximum_length, numeric_precision, datetime_precision) IS NOT NULL THEN CONCAT('(', CONCAT(CAST(COALESCE(character_maximum_length, numeric_precision, datetime_precision) AS VARCHAR(20)), CONCAT(CASE WHEN numeric_scale IS NOT NULL THEN CONCAT(', ', CAST(numeric_scale AS VARCHAR(20))) ELSE '' END, ')'))) ELSE '' END) data_type, 'COLUMN_DEFAULT', 'Default Value', column_default, 'NULLABLE', 'Nullable', is_nullable FROM information_schema.columns WHERE UPPER(table_catalog) = UPPER('$DATABASE$') AND UPPER(table_schema) = UPPER('$SCHEMA$') AND UPPER(table_name) = UPPER('$TABLE$') ORDER BY table_catalog, table_schema, table_name, ordinal_position
Alter DDL Template
- This is a template that is configured on each table.
- The default for new tables is taken from the default template configured on the target connection.
- The template must be set as Type: 'Alter'
- The result of the template is interpreted as a sequence of SQL Blocks separated by two newlines.
- Each SQL Block is executed independently; the failure of one does not prevent the execution of the following.
- Within each SQL Block there can be multiple statements separated by "<EOS>" or the configured separator.
- A failure of a statement within an SQL Block does prevent the execution of the following statements in that block.
- The usual variables are available, e.g. table, options, env.
In addition, the following variables are available:
Variable | Description |
---|---|
changes | An array of objects describing how the actual table is different from the expected table. Each element has type Change. |
actualTable | An object containing the table/column information obtained for the actual table. Has type TableInformation. |
'Change' object type
Field | Description |
---|---|
changeType | One of the enum values in Types. ChangeType; determines the type of the object under data. |
data | An object containing the details of the specific change; the type depends on the value of changeType. |
Change types
Change type | Description | Fields in data | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
Types.ChangeType.TableCreation | Indicates that the actual table does not exist. | (none) | ||||||||||
Types.ChangeType.TableAdditionalInformationChange | Indicates that one of the additional information fields returned by the Table Information statement differs. |
| ||||||||||
Types.ChangeType.ColumnOrderChange | Indicates that the columns differ only in their order. | (none) | ||||||||||
Types.ChangeType.ColumnAddition | Indicates that one of the columns in the expected table is not present in the actual table. |
| ||||||||||
Types.ChangeType.ColumnDeletion | Indicates that one of the columns in the actual table is not present in the expected table. |
| ||||||||||
Types.ChangeType.ColumnNameChange | Indicates that the name of one of the columns differs. |
| ||||||||||
Types.ChangeType.ColumnDataTypeChange | Indicates that the data type of one of the columns differs. |
| ||||||||||
Types.ChangeType.ColumnAdditionalInformationChange | Indicates that one of the additional information fields returned by the Column Information statement differs for one column. |
|
'TableInformation' object type
Variable | Description |
---|---|
catalog | name of the catalog containing the table |
schema | name of the schema containing the table |
name | name of the table |
additionalInformation | array of AdditionalInformation objects |
columns | array of ColumnInformation objects |
'ColumnInformation' object type
Variable | Description |
---|---|
name | name of the column |
dataType | data type of the column |
additionalInformation | array of AdditionalInformation objects |
'AdditionalInformation' object type
Variable | Description |
---|---|
key | additional information "key" |
description | additional information "description" |
value | additional information "value" |