Objects and Data Transformation

A transformation rule changes what is replicated: the names of schemas, tables and columns, the type or the data of a column. It can also leave a table or a column out. This page shows the rules that are used most. The full syntax is in Objects Mapping and Possible Transforms.

How the rules work

• A rule of a Capture Process is global: it changes the objects for all the Apply Processes that read from it. A rule of an Apply Process is local: it affects only that process. The rules of an Apply Process see the names that the rules of the Capture Process have set.
• The rules are tried in the order of their ids. Write the rules for tables before the rules for columns (see the order of the rules in Objects Mapping and Possible Transforms).
• An Apply Process reads its rules when it starts. After alter, stop the process and run it again.
• ddlprocessing=map applies the rules to the DDL statements too.
• A rule is typed as one argument, without a space after a semicolon. A text constant needs special quotes, see “Add a column” below.

The example replicates the tables of the schema dbo of a SQL Server database to the schema public of a PostgreSQL database and changes them on the way. The commands are typed in repcli, as in Basic Replication Configuration.

Rename the schema

Prepare the Source and the Target Databases and create the Capture Process MAPCAP1 and the Apply Process. The rule map=1,rule=(dbo.%:public.%) of the Capture Process changes the schema of every table of dbo to public, and the tables keep their names. All the Apply Processes that read from the Capture Process get the new names. ddlprocessing=map applies the rule to the DDL statements too (see PostgreSQL Apply Parameters):

Rename a table

The following rules change the Apply Process 8. Stop the process, add a rule that renames the table public.Customer to public.Clients, and run the process again. The numbers of the rules belong to the process: the rule 1 of the Apply Process is not the rule 1 of the Capture Process (see Alter Process):

Rename a column

Rename the column public.Customer.ID to CLIENT_ID. The rule is written after the rule that renames the table, and its right side has the new name of the table, public.Clients. Stop the Apply Process, run the command and run the process again:

Remove a column

Ignore the column public.Customer.SQL_ID in all the SQL statements. NULL on the right side of a rule leaves the column out:

Change the type of a column

Change the type of all the UNIQUEIDENTIFIER columns of the schema public to VARCHAR(64). The attributes are written without spaces:

Add a column

Add the column SOURCE_SYS VARCHAR(64) to all the tables of the schema public and fill it with the constant text REPLICATED FROM SQL SERVER. A text constant is written between single quotes inside the double quotes of DATA, and the double quotes are doubled, \""'text'\"": then repcli keeps the single quotes and the spaces of the text (see the quotes in Objects Mapping and Possible Transforms):

Check the rules before you save them

Add preview=1 to the alter command to see what the rules do without saving them. The answer lists the tables of the Apply Process and, under Map Tables, what each rule does to them. The example is from a test installation where the Apply Process 1 reads the tables dbo.DimCustomer and dbo.DimCurrency of a sample database: the table is renamed, a column is renamed and the column Title is left out.

The same check works for the rules of a Capture Process (see Alter Process). If a rule is wrong, the answer is Failed with the reason, and nothing is saved.