データベース移行 - SymmetricDS

テーブル名が同じ、該当条件で派生する新テーブルを作成

org.customers → org.customers    # テーブル名が同じ
org.plans     → org.plans        # テーブル名が同じ
                 → mig.contract  # 該当条件で派生する新テーブル(plansのcontract_code=2の場合にcontractを作成する

customersとplansをコピーし、コピーしたplansからcontract_code=2の場合だけcontractを生成します。

truncate_table.sql

truncate table org.customers
truncate table org.plans
truncate table mig.contract

insert_migration.sql

#symテーブルを毎回作り直していればdeleteは不要
delete from sym_load_filter;
delete from sym_transform_column;
delete from sym_transform_table;
delete from sym_trigger_router;
delete from sym_trigger;
delete from syu_router;
delete from sym_channel where channel_id in ('channel');
delete from sym_node_group_link;
delete from sym_node_group;
delete from sym_node_host;
delete from sym_node_identity;
delete from sym_node_security;
delete from sym_node;

insert into sym_channel
(channel_id, processing_order, max_batch_size, enabled, description)
values
('to_sqlserver', 1, 100000, 1, 'to_sqlserver channel data');

insert into sym_node_group
(node_group_id)
values
('postgres'),
('sqlserver');

insert into sym_node_group_link
(source_node_group_id, target_node_group_id, data_event_action)
values
('postgres', 'sqlserver', 'W');

insert into sym_trigger
(trigger_id, source_schema_name, source_table_name, channel_id, last_update_time, create_time)
values
('customers', 'org', 'customers', 'to_sqlserver', current_timestamp, current_timestamp),
('plans', 'org', 'plans', 'to_sqlserver', current_timestamp, current_timestamp);

insert into sym_router
(router_id, target_schema_name, target_table_name, source_node_group_id, target_node_group_id, router_type, last_update_time, create_time)
values
('customers_postgres_2_sqlserver', 'org', 'customers', 'postgres', 'sqlserver', 'default', current_timestamp, current_timestamp),
('plans_postgres_2_sqlserver', 'org', 'plans', 'postgres', 'sqlserver', 'default', current_timestamp, current_timestamp);

insert into sym_trigger_router
(trigger_id, router_id, initial_load_order, create_time, last_update_time)
values
('customers', 'customers_postgres_2_sqlserver', 100, current_timestamp, current_timestamp),
('plans', 'plans_postgres_2_sqlserver', 100, current_timestamp, current_timestamp);

insert into sym_node
(node_id, node_group_id, external_id, sync_enabled, sync_url, schema_version, symmetric_version, database_type, database_version, hertbeat_time, timezone_offset, batch_to_send_count, batch_in_error_count, create_at_node_id)
values
('000', 'postgres', '000', null, null, null, null, null, current_timestamp, null, 0, 0, '000'),
('001', 'sqlserver', '001', null, null, null, null, null, current_timestamp, null, 0, 0, '000');

insert into sym_node_security
(node_id, node_password, registration_enabled, registration_time, initial_load_enabled, initial_load_time, created_at_node_id)
values
('000', '5d1c92bbache2edb9e1ca5dbb0e481', 0, current_timestamp, 0, current_timestamp, '000'),
('001', '5d1c92bbache2edb9e1ca5dbb0e481', 1, null, 1, null, '000');

insert into sym_node_identity
values
('000');

insert into sym_transform_table
(transform_id, source_node_group_id, target_node_group_id, transform_point,
source_schema_name, source_table_name,
target_schema_name, target_table_name,
delete_action, transform_order, column_policy, update_first,
last_update_by, last_update_time, create_time)
values
('plans_to_plans', 'postgres', 'sqlserver', 'LOAD',
'org', 'plans',
'org', 'plans',
'DEL_ROW', 1, 'IMPLIED', 1,
'sample' current_timestamp, current_timestamp),
('plans_to_contract','postgres', 'sqlserver', 'LOAD',
'org', 'plans',
'mig', 'constract',
'DEL_ROW', 2, 'SPECIFIED', 1,
'sample' current_timestamp, current_timestamp);

# 派生する新テーブルの構成からの変更あり
insert into sym_transform_column
(transform_id, include_on, source_column_name, target_column_name, pk,
transform_type, transform_expression, transform_order,
last_update_time, last_update_by, create_time)
values
('plans_to_contract', '*', '', 'id', 1,
'identity', null, 1,
current_timestamp, 'sample'
current_timestamp),
('plans_to_contract', '*', '', 'created', 0,
'const', 'init', 1,
current_timestamp, 'sample'
current_timestamp),
('plans_to_contract', '*', 'id', 'plan_id', 1,
'copy', null, 1,
current_timestamp, 'sample'
current_timestamp),
('plans_to_contract', '*', 'contract_status', 'status', 1,
'valueMap', '2=1 *=2', 1,
current_timestamp, 'sample'
current_timestamp);

# 派生する新テーブルの構成からの変更あり
insert into sym_load_filter
(load_filter_id, load_filter_type,
source_node_group_id, target_node_group_id,
target_schema_name, target_table_name,
filter_on_update, filter_on_insert, filter_on_delete,
before_write_script, after_write_script, batch_complete_script,
batch_commit_script, batch_rollback_script, handle_error_script,
create_time, last_update_by, last_update_time, load_filter_order, fail_on_error)
values
('contract_filter', 'BSH',
'postgres', 'sqlserver',
'mig', 'contract',
1, 1, 1, '
if (Long.parseLong(STATUS) == 1) {
    return true;
} else {
    return false;
}
', null, null, null, null,, null,
current_timestamp, 'sample', current_timestamp, 2, 1);

SymmetricDSパターン別構築メモ(異種データベース))