7.4.6 Objects Mapping and Possible Transforms

Transforms are primarily used where there is a need to reformat any statements. The transformation can only be configured for a Capture Process, as the Apply function is inherent in the use of transformation. Therefore, it is not possible nor is it necessary to construct an Apply Process for a Transform.

Repstance supports transformation rules, which can be applied to the following objects:

• schema name
• table name
• column name
• “identity” attribute (valid only for SQL Server DB)
• “primary key” attribute
• data type
• data type length
• data

Transformation rules can be triggered based on:

• schema name mask
• table name mask
• column name mask
• “identity” attribute (valid only for SQL Server DB)
• reference “identity” column (valid only for SQL Server DB)
• “primary key” attribute
• data type mask
• data type length

Transformation rules can be used to exclude the following objects:

• table
• column
• “identity” attribute (valid only for SQL Server DB)
• reference “identity” column (valid only for SQL Server DB)
• “primary key” attribute

Transformation rules can be used to add the following objects:

• column
• “identity” attribute (valid only for SQL Server DB)
• “primary key” attribute

Transformation rules can be used to change the following objects:

• schema name
• table name
• column name
• “identity” attribute (valid only for SQL Server DB)
• “primary key” attribute
• data type
• data type length
• data

Note – Data can be changed to the :

• constant value
• original value of another column from this table
• result of SQL Server function execution. The original values of any columns from this table can be passed to the Function as parameters. The Function must exist in the Target Database, where the Apply Process is to be run.

There are normally three separate parts that comprise a Transformation. They are:

• id – the id, which is unique to each Transform has the purpose of determining the order in which the Transform is executed by the Process
• rule – specifies the object to be transformed and specifies how it is to be transformed
• description – a meaningful description of this Transformation

The syntax used is:

where:

The CaptureCriteria consists of:

• schema_mask – the mask is used to specify schema name
• table_mask – the mask is used to specify table name
• column_mask – the mask is used to specify column name

The attr_spec is:

• PK – the specification of PK attribute, it can accept the following values:
• N– to determine column without PK
• U – to include PK column
• ID – determine if the column should or should not be handled as an “identity_insert”, the possible values are:
• 0 – to process as “non identity_insert” column
• 1 – to process as “identity_insert” column
• 2 – to process as reference “identity” column
• TYPE – name of the data type
• LEN – length of data type if applicable

TransformCriteria=schema_mask.table_mask.column_mask.[attr_spec]

data_specification=DATA="SQL_function_or_operation"

The TransformCriteria consists of:

• schema_mask – the mask is used to specify schema name
• table_mask – the mask is used to specify table name
• column_mask – the mask is used to specify column name

The attr_spec is:

• PK – the specification of PK attribute, it can accept the following values:
• N – to determine column without PK
• U – to include PK column
• ID – determine if the column should or should not be handled as an “identity_insert”, the possible values are:
• 0 – to process as “non identity_insert” column
• 1 – to process as “identity_insert” column
• 2 – to process as reference “identity” column
• TYPE – name of the data type
• LEN – length of data type if applicable
• DATA – this is the data that will result from the transformation specified on the captured data. This can be:
• predefined value in format "predefined_value"
• any operations based on the values of this column. In order to use the values found in the column the % needs to be used
• any operations based on the values of any columns in this table. In order to use the values of any column the following format is used "C:number_of_column". Table name can be passed as parameter as well. The format is "T:0".

To exclude columns the following syntax is used:

where:

• schema_mask – the mask is used to specify schema name
• table_mask – the mask is used to specify table name

To include any columns that were not represented in the source table the following syntax is used:

where:

• schema_mask – the mask is used to specify schema name
• table_mask – the mask is used to specify table name
• column_name – column name (this must be included and can not use the % symbol)
• TYPE – data type definition of the column to be created (this must be included and can not use the % symbol)
• LEN – length of data type, where necessary must be specified here
• PK – the specification of PK attribute. It accepts the following values:
• N – to determine column without PK
• U – to include PK column
• ID – determine if the column should or should not be handled as an “identity_insert”. The possible values are:
• 0 – to process as “non identity_insert” column
• 1 – to process as “identity_insert” column
• DATA – this is the data that will result from the transformation specified on the captured data. This can be:
• predefined value in format "predefined_value"
• any operations based on the values of this column. In order to use the values found in the column the % needs to be used
• any operations based on the values of any columns in this table. In order to use the values of any column the following format is used "C:number_of_column". Table name can be passed as parameter as well. The format is "T:0".

The following examples show how to use the Transformation Rules:

• For all tables located in the dbo schema change the schema name to report:

• Rename dbo.emp table to report.employees table:

• Exclude info column in the dbo.customer table from the replication:

• For any tables in the dbo schema having name starting with rep_ replace the prefix to the report_:

• For all tables in the dbo schema having ident column change the column name to id:

• Exclude from the replication any columns having name id and defined as primary key:

• For any tables having column id of int type handle this column as primary key. Any update and delete statements will be built to use id column as primary key or the part of composite primary key:

• Put the value of the desc column in the branch table into the uppercase:

• Add new column named tag of the nvarchar(100) type into the customer table contains predefined value rep:

• Add new column named name of the nvarchar(255) type to the person table contains concatenation of the firstname and lastname columns (in the table firstname has the position 2 and lastname has the position 3):

• For any tables in the dbo schema having name starting with rep_ and column id, which is defined as primary key and identity_insert, rename the table into the report schema and without rep_ prefix and use custom function report.generateID to reproduce the value of this column. This column will still be used as a primary key, but not as identity_insert: