July 14, 2012 2 Comments
While working with our newly discovered SymmetricDS tool, I realized that their manual does not clearly state how to synchronize two databases if their table names do not match. That might happen while matching tables between databases of two different companies, each one with its own table name policies. Blame those Database Administrators!
Here is how I accomplished synchronized two tables with different names:
First, I did the usual configuration: I defined two node groups A and B, defined a link between those node groups, defined a channel and a router for each direction (A to B and B to A).
The description of triggers requires more configuration effort: Since the table names are different on each database, and since each trigger definition is bound to a table name, I had to define one trigger for each table name. Here, I suppose to databases: A and B, where the equivalent tables are called TableA and TableB.
insert into sym_trigger (trigger_id,source_table_name,channel_id,last_update_time,create_time) values ('TriggerA','TableA','channel',current_timestamp,current_timestamp), ('TriggerB','TableB','channel',current_timestamp,current_timestamp);
As usual, the trigger needs to be associated with a router. Additional care is required: TriggerA is applicable only on database A and the reported changes are meaningful to be routed to database A to B. TriggerB is handled similarly.
insert into sym_trigger_router (trigger_id, router_id, initial_load_order, create_time, last_update_time) values ('TriggerA', 'routerAtoB', 1, current_timestamp, current_timestamp), ('TriggerB', 'routerBtoA', 1, current_timestamp, current_timestamp);
Then, a table transformation must be defined: From node A to node B, table is to renamed from TableA to TableB. And from node B to node A, table is to renamed from TableB to TableA. The
'IMPLED' column policy means that all columns are to be copied without changes. I assume that both tables have the same column names and types. The delete action is ‘DEL_ROW’ and is not the main subject of this article. I prefered to run the transformation during the ‘Load’ transformation point.
insert into sym_transform_table (transform_id,source_node_group_id,target_node_group_id,transform_point,source_table_name,target_table_name,delete_action,column_policy) values ('TransfAtoB', 'nodeA', 'nodeB', 'Load', 'TableA', 'TableB', 'DEL_ROW', 'IMPLIED'), ('TransfBtoA', 'nodeB', 'nodeA', 'Load', 'TableB', 'TableA', 'DEL_ROW', 'IMPLIED');