Home / Repstance User Guide / PROCESS CONFIGURATION COMMANDS / Objects Mapping and Possible Transforms

Objects Mapping and Possible Transforms

Transformation rules change what is replicated: the names of the schemas, tables and columns, the attributes and the types of the columns and the data. A rule can be configured for a Capture Process or for an Apply Process. The rules of a Capture Process are global: they affect all the Apply Processes that read from it. The rules of an Apply Process are local: they affect only that process. In the Web UI the rules are built in the Transformation (Mapping) Rules window; this page describes the rules themselves.

What a rule can do

A rule can	Objects
Be triggered by	The schema name mask, the table name mask, the column name mask, the “identity” attribute and the reference “identity” column (SQL Server only), the “primary key” attribute, the data type mask, the data type length.
Exclude	A table, a column, the “identity” attribute, the reference “identity” column (SQL Server only), the “primary key” attribute.
Add	A column, the “identity” attribute (SQL Server only), the “primary key” attribute.
Change	The schema name, the table name, the column name, the “identity” attribute (SQL Server only), the “primary key” attribute, the data type, the data type length, the data.

The data of a column can be changed to a constant value, to the original value of another column of the table, or to the result of a function of the Target Database. The original values of the columns of the table can be passed to the function as parameters. The function has to exist in the Target Database where the Apply Process runs.

The syntax

A rule has three parts:

id – the number of the rule. It is unique, and it gives the order in which the rules are tried.
rule – the object that is searched for and what it becomes.
description – a text that describes the rule. It is optional.

map=id,rule=(SearchCriteria:TransformCriteria),description="desc"SearchCriteria=schema_mask.table_mask.column_mask.[attr_spec]TransformCriteria=schema_mask.table_mask.column_mask.[attr_spec]

The schema_mask, table_mask and column_mask select the schemas, the tables and the columns, the % symbol matches any number of characters. The columns can be selected by attributes too:

attr_spec=PK=PK;TYPE=type;ID=id;LEN=len;DATA="data_specification"

Attribute	Values	Used in
PK	P – the column is a part of the primary key; U – the column is a part of a unique index; N – the column is not a part of the primary key or of a unique index.	The search criteria and the transform criteria.
ID	Whether the column is handled as an “identity_insert” column: 0 – as a column that is not, 1 – as an “identity_insert” column, 2 – as the reference “identity” column. A new column takes 0 or 1.	The search criteria and the transform criteria. SQL Server only.
TYPE	The name of the data type.	The search criteria and the transform criteria. A new column needs it.
LEN	The length of the data type, where it has one.	The search criteria and the transform criteria.
DATA	The data that results from the transformation: a predefined value written as "predefined_value"; an operation on the value of the column (the % stands for it); or an operation on the values of the columns of the table, written as "C:number_of_column" (the table name is "T:0").	The transform criteria and the new columns.

To exclude a column, write NULL in the place of the column on the right side of the rule. The table is named there as well:

schema_mask.table_mask.NULL

To exclude a table, write NULL as the whole right side of the rule. The % symbol selects several tables, for example all the tables of a schema:

schema_mask.table_mask:NULL

Take care with a rule that has a column on the left side: dbo.emp.salary:NULL has only NULL on the right side and leaves out the whole table dbo.emp. To leave out only the column write dbo.emp.salary:dbo.emp.NULL.

To add a column that the source table does not have, the right side of the rule names the new column and its type. The name and the type are required, the % symbol is not allowed in them:

schema_mask.table_mask.column_name.[TYPE=type;LEN=len;PK=pk;ID=id;DATA="data_specification"]

Setting and removing rules

A rule is a parameter of the process. In repcli the id is part of the name, and in the REST API it is one [name, value] pair (see Prepare Capture Process and Prepare Apply Process). Rules are added and replaced with Alter Process. map=mapID,default removes one rule, and map=default removes all the rules of a Capture Process. preview=1 lists the tables with the rules that apply to them without saving anything.

A rule is typed at the repcli prompt as one argument, and repcli reads the line the way a shell reads it:

A space starts a new argument, unless it is between double quotes. Write the attributes without spaces, [TYPE=nvarchar;LEN=100]: the server refuses a space after a semicolon.
A backslash keeps the double quote that follows it: DATA=\"upper(%)\" is sent as DATA="upper(%)".
Single quotes are removed unless they are between double quotes. A text constant needs both, and the double quotes are doubled: DATA=\""'rep'\"" is sent as DATA="'rep'", and the spaces of the text are kept.

In Command Mode the shell reads the line before repcli does, and it needs quotes for the parentheses. Put the whole argument between double quotes and write the double quotes inside it as \":

repcli alter process=apply id=1 "map=9,rule=(dbo.customer.NULL:dbo.customer.tag.[TYPE=nvarchar;LEN=100;DATA=\"'rep'\"])"

Examples

The following examples show how to use the transformation rules. They are written as they are typed at the repcli prompt (see above for the quotes):

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

map=1,rule=(dbo.%:report.%),description="sample 1"

Rename the dbo.emp table to report.employees:

map=2,rule=(dbo.emp:report.employees),description="sample 2"

Exclude the info column of the dbo.customer table from the replication:

map=3,rule=(dbo.customer.info:dbo.customer.NULL),description="sample 3"

For any tables in the dbo schema having a name that starts with rep_ replace the prefix with report_:

map=4,rule=(dbo.rep_%:dbo.report_%),description="sample 4"

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

map=5,rule=(dbo.%.ident:dbo.%.id),description="sample 5"

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

map=6,rule=(dbo.%.id.[PK=P]:dbo.%.NULL),description="sample 6"

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

map=7,rule=(dbo.%.id.[TYPE=int]:dbo.%.id.[PK=P]),description="sample 7"

Put the value of the desc column of the branch table into upper case:

map=8,rule=(dbo.branch.desc:dbo.branch.desc.[DATA=\"upper(%)\"])

Add a new column named tag of the nvarchar(100) type with the predefined value rep to the customer table:

map=9,rule=(dbo.customer.NULL:dbo.customer.tag.[TYPE=nvarchar;LEN=100;DATA=\""'rep'\""])

Add a new column named name of the nvarchar(255) type to the person table. It contains the concatenation of the firstname and lastname columns (in the table firstname is the column 2 and lastname the column 3):

map=10,rule=(dbo.person.NULL:dbo.person.name.[TYPE=nvarchar;LEN=255;DATA=\"C:2+C:3\"])

For any tables in the dbo schema having a name that starts with rep_ and a column id that is the primary key and an identity_insert column, rename the table into the report schema without the rep_ prefix and use the custom function report.generateID to produce the value of this column. The column is still the primary key, but not an identity_insert column:

map=11,rule=(dbo.rep_%.id.[PK=P;ID=1]:report.%.id.[PK=P;ID=0;DATA=\"report.generateID()\"])

Transformation Rules and Triggering Order

The process tries the rules in the order of their ids. There are two kinds of rules, and they work in different ways:

A table rule renames or excludes a schema or a table, and it has no column part. A table takes the first table rule that fits it. The later table rules are ignored for that table.
A column rule has a column part. Every column takes the first column rule that fits it. The column rules of a table are added to what a table rule has set for the table.

A table rule that comes after a column rule of the same table is ignored, because the column rule has already set the name of the table. This is also true for a rule that excludes the table. So write the rule that affects the least data first, among the table rules and among the column rules, and write the table rules before the column rules.

The right side of a column rule names the table too. When no table rule came before it, the table gets that name: write there the schema and the table of the left side, unless the table is to be renamed.

The following examples show it. All the tables of the dbo schema whose name starts with emp are to be renamed into the schema hr and keep their names. The ident columns of all the tables of the dbo schema are to be renamed to id. An employee table that has an ident column matches both rules.

The table rule comes first. The schema of the employee table is changed into hr and the ident column is renamed to id:

map=1,rule=(dbo.emp%:hr.emp%),description="sample 1"
map=2,rule=(dbo.%.ident:dbo.%.id),description="sample 2"

The column rule comes first. The ident column is renamed to id, but the schema of the employee table is not changed:

map=1,rule=(dbo.%.ident:dbo.%.id),description="sample 3"
map=2,rule=(dbo.emp%:hr.emp%),description="sample 4"

Two table rules fit the emp_archive table, and the first one is used. Here emp_archive goes to the arch schema and the other tables whose name starts with emp go to hr. If the two rules were written in the other order, emp_archive would go to hr too:

map=1,rule=(dbo.emp_archive:arch.emp_archive)
map=2,rule=(dbo.emp%:hr.emp%)

Remove Process

PROCESS CONTROL COMMANDS