データベース移行 - 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);