The new Validate Against DB functionality works in the following stages:

Stage

Operations

Configuration

Validate

  1. Create the expected table, under a different name, based on the metadata.
  2. Fetch information about the expected and actual tables.
  3. Compare the actual and expected information.

Table/column information SQL Block Procedure.
Configured on the Target Connection.

Alter

  1. Generate a SQL Block procedure using a template.
  2. Execute the SQL Block.

Alter DDL Template.
Configured the Table.
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.

Note

  • 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 validate 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 the ways in which the actual table is different to 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.

changedKey
The "key" of the additional information.
changedDescription
The "description" of the additional information.
actualValue
The "value" of the additional information in the actual table.
expectedValue
The "value" of the additional information in the expected table.

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.

columnName

The name of the column in the expected table.

Types.ChangeType.ColumnDeletion

Indicates that one of the columns in the actual table is not present in the expected table.

columnName

The name of the column in the actual table.

Types.ChangeType.ColumnNameChange

Indicates that the name of the one of the columns differs.

columnName

The name of the column in the expected table.

actualColumnName

The name of the corresponding column in the actual table.

Types.ChangeType.ColumnDataTypeChange

Indicates that the data type of one of the columns differs.

columnName

The name of the column (in the expected table).

dataType

The data type of the column in the expected table.

Types.ChangeType.ColumnAdditionalInformationChange

Indicates that one of additional information fields returned by the Column Information statement differs for one column.

columnName

The name of the column whose additional information differs.

changedKey

The "key" of the additional information.

changedDescription

The "description" of the additional information.

actualValue

The "value" of the additional information in the actual table.

expectedValue

The "value" of the additional information in the expected table.

'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"

  • No labels