I have the followings transformation columns
insert into SYM_TRANSFORM_COLUMN (
transform_id, include_on, target_column_name, source_column_name, pk,
transform_type, transform_expression, transform_order, last_update_time,
create_time
) values (
'send_source_node_id', '*', 'node_id', 'employee_id', 1,
'bsh', 'String node = targetNode.getNodeId(); String value = currentValue; return value + "-" + sourceNodeId;', 1, current_timestamp, current_timestamp
);
insert into SYM_TRANSFORM_COLUMN (
transform_id, include_on, target_column_name, source_column_name, pk,
transform_type, transform_expression, transform_order, last_update_time,
create_time
) values (
'copy_employee_id', '*', 'employee_id', 'employee_id', 0,
'copy', '', 10, current_timestamp, current_timestamp
);
insert into SYM_TRANSFORM_COLUMN (
transform_id, include_on, target_column_name, source_column_name, pk,
transform_type, transform_expression, transform_order, last_update_time,
create_time
) values (
'copy_employee_name', '*', 'name', 'name', 0,
'copy', '', 10, current_timestamp, current_timestamp
);
With its transform tables
insert into SYM_TRANSFORM_TABLE (
transform_id, source_node_group_id, target_node_group_id, transform_point, source_table_name,
target_table_name, update_action, delete_action, transform_order, column_policy, update_first,
last_update_time, create_time
) values (
'copy_employee_id', 'school', 'main-school', 'EXTRACT', 'employees',
'employees', 'UPDATE_COL', 'DEL_ROW', 1, 'IMPLIED', 0,
current_timestamp, current_timestamp
);
insert into SYM_TRANSFORM_TABLE (
transform_id, source_node_group_id, target_node_group_id, transform_point, source_table_name,
target_table_name, update_action, delete_action, transform_order, column_policy, update_first,
last_update_time, create_time
) values (
'copy_employee_name', 'school', 'main-school', 'EXTRACT', 'employees',
'employees', 'UPDATE_COL', 'DEL_ROW', 1, 'IMPLIED', 0,
current_timestamp, current_timestamp
);
insert into SYM_TRANSFORM_TABLE (
transform_id, source_node_group_id, target_node_group_id, transform_point, source_table_name,
target_table_name, update_action, delete_action, transform_order, column_policy, update_first,
last_update_time, create_time
) values (
'send_source_node_id', 'school', 'main-school', 'EXTRACT', 'employees',
'employees', 'UPDATE_COL', 'DEL_ROW', 1, 'IMPLIED', 0,
current_timestamp, current_timestamp
);
Structure of my source table
create table employees (
employee_id serial,
name varchar(50),
CONSTRAINT departments_pk PRIMARY KEY (employee_id)
);
Structure of my target table
create table employees (
master_employee_id uuid default uuid_generate_v1(),
employee_id integer,
name varchar(50),
node_id varchar(50),
CONSTRAINT employees_pk PRIMARY KEY (master_employee_id)
);
The synchronization works fine with other tables that are identical, but these tables do not work fine, because when a new employee is added in the source table two records will be added to the target table. I not idea about this behavior, can someone explain to me?
Each entry in sym_transform_table represents a table mapping that will process row changes for the source table. Since you have 3 table mappings for the same source table, each row change on that table will result in 3 transformed rows.
I think you wanted to use 1 table mapping in sym_transform_table, like this:
insert into SYM_TRANSFORM_TABLE (
transform_id, source_node_group_id, target_node_group_id, transform_point, source_table_name,
target_table_name, update_action, delete_action, transform_order, column_policy, update_first,
last_update_time, create_time
) values (
'employees_to_employees', 'school', 'main-school', 'EXTRACT', 'employees',
'employees', 'UPDATE_COL', 'DEL_ROW', 1, 'IMPLIED', 0,
current_timestamp, current_timestamp
);
Then change your entries in sym_transform_column to reference it by using the same "employees_to_employees" transform ID.