-- -- Licensed to JumpMind Inc under one or more contributor -- license agreements. See the NOTICE file distributed -- with this work for additional information regarding -- copyright ownership. JumpMind Inc licenses this file -- to you under the GNU General Public License, version 3.0 (GPLv3) -- (the "License"); you may not use this file except in compliance -- with the License. -- -- You should have received a copy of the GNU General Public License, -- version 3.0 (GPLv3) along with this library; if not, see -- . -- -- Unless required by applicable law or agreed to in writing, -- software distributed under the License is distributed on an -- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY -- KIND, either express or implied. See the License for the -- specific language governing permissions and limitations -- under the License. ------------------------------------------------------------------------------ -- Sample Data ------------------------------------------------------------------------------ -- General Meet Setup INSERT INTO Meet (licence_number, meet_name, meet_type, start_date, end_date, location, course) VALUES ("3SE7357", "Test_Meet", "Age_Group_Open", "2021-03-27", "2021-02-28", "Prince Regent Swimming Complex", 0); -- Add Dummy Club insert into Clubs (short_club_name, club_code, long_club_name, club_rep_name, email_address) values ('Portsmouth N', 'PORS', 'Portsmouth Northsea SC', 'Name One', 'name.one@gmail.com'); -- Add Dummy Swimmers insert into Swimmers (asa_number, forename, surname, dob, age, sex, short_club_name) values (839484, 'Matthew', 'Whittenham', '1998-11-20', 22, 0, 'Portsmouth N'); insert into Swimmers (asa_number, forename, surname, dob, age, sex, short_club_name) values (999999, 'Emma', 'Whittenham', '2002-11-20', 18, 1, 'Portsmouth N'); ------------------------------------------------------------------------------ -- Clear and load SymmetricDS Configuration ------------------------------------------------------------------------------ delete from sym_trigger_router; delete from sym_trigger; delete from sym_router; delete from sym_channel where channel_id in ('sale_transaction', 'item'); 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; ------------------------------------------------------------------------------ -- Channels ------------------------------------------------------------------------------ -- Channel "meet" for meet & session tables insert into sym_channel (channel_id, processing_order, max_batch_size, enabled, description) values('meet', 1, 100000, 1, 'channel for meet and session tables'); insert into sym_channel (channel_id, processing_order, max_batch_size, enabled, description) values('events', 2, 100000, 1, 'channel for events & qualifying times tables'); insert into sym_channel (channel_id, processing_order, max_batch_size, enabled, description) values('swimmers', 3, 100000, 1, 'channel for swimmers and clubs'); -- -- Channel "item" for tables related to items for purchase -- insert into sym_channel -- (channel_id, processing_order, max_batch_size, enabled, description) -- values('item', 1, 100000, 1, 'Item and pricing data'); ------------------------------------------------------------------------------ -- Node Groups ------------------------------------------------------------------------------ insert into sym_node_group (node_group_id) values ('master'); insert into sym_node_group (node_group_id) values ('node'); ------------------------------------------------------------------------------ -- Node Group Links ------------------------------------------------------------------------------ -- Master sends changes to Nodes when Master pushes to Nodes. insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('master', 'node', 'P'); -- Nodes send changes to Master when Nodes push to Master insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('node', 'master', 'P'); ------------------------------------------------------------------------------ -- Triggers ------------------------------------------------------------------------------ -- Triggers for tables on "all" channel insert into sym_trigger (trigger_id,source_table_name,channel_id,last_update_time,create_time) values('Meet','Meet','meet',current_timestamp,current_timestamp); insert into sym_trigger (trigger_id,source_table_name,channel_id,last_update_time,create_time) values('Sessions','Sessions','meet',current_timestamp,current_timestamp); insert into sym_trigger (trigger_id,source_table_name,channel_id,last_update_time,create_time) values('Events','Events','events',current_timestamp,current_timestamp); insert into sym_trigger (trigger_id,source_table_name,channel_id,last_update_time,create_time) values('Qualifying_Times','Qualifying_Times','events',current_timestamp,current_timestamp); insert into sym_trigger (trigger_id,source_table_name,channel_id,last_update_time,create_time) values('Clubs','Clubs','swimmers',current_timestamp,current_timestamp); insert into sym_trigger (trigger_id,source_table_name,channel_id,last_update_time,create_time) values('Swimmers','Swimmers','swimmers',current_timestamp,current_timestamp); -- Triggers for tables on "all" channel - initial load only -- insert into sym_trigger -- (trigger_id,source_table_name,channel_id, sync_on_insert, sync_on_update, sync_on_delete,last_update_time,create_time) -- values('Meet_Initial','Meet','all',0,0,0,current_timestamp,current_timestamp); -- insert into sym_trigger -- (trigger_id,source_table_name,channel_id, sync_on_insert, sync_on_update, sync_on_delete,last_update_time,create_time) -- values('Clubs_Initial','Clubs','all',0,0,0,current_timestamp,current_timestamp); -- insert into sym_trigger -- (trigger_id,source_table_name,channel_id, sync_on_insert, sync_on_update, sync_on_delete,last_update_time,create_time) -- values('Events_Initial','Events','all',0,0,0,current_timestamp,current_timestamp); -- insert into sym_trigger -- (trigger_id,source_table_name,channel_id, sync_on_insert, sync_on_update, sync_on_delete,last_update_time,create_time) -- values('Qualifying_Times_Initial','Qualifying_Times','all',0,0,0,current_timestamp,current_timestamp); -- insert into sym_trigger -- (trigger_id,source_table_name,channel_id, sync_on_insert, sync_on_update, sync_on_delete,last_update_time,create_time) -- values('Sessions_Initial','Sessions','all',0,0,0,current_timestamp,current_timestamp); -- insert into sym_trigger -- (trigger_id,source_table_name,channel_id, sync_on_insert, sync_on_update, sync_on_delete,last_update_time,create_time) -- values('Swimmers_Initial','Swimmers','all',0,0,0,current_timestamp,current_timestamp); ------------------------------------------------------------------------------ -- Routers ------------------------------------------------------------------------------ -- Default router sends all data from master to nodes insert into sym_router (router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time) values('master_2_nodes', 'master', 'node', 'default',current_timestamp, current_timestamp); -- Default router sends all data from nodes to master insert into sym_router (router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time) values('nodes_2_master', 'node', 'master', 'default',current_timestamp, current_timestamp); -- -- Column match router will subset data from corp to specific store -- insert into sym_router -- (router_id,source_node_group_id,target_node_group_id,router_type,router_expression,create_time,last_update_time) -- values('corp_2_one_store', 'corp', 'store', 'column','STORE_ID=:EXTERNAL_ID or OLD_STORE_ID=:EXTERNAL_ID',current_timestamp, current_timestamp); ------------------------------------------------------------------------------ -- Trigger Routers ------------------------------------------------------------------------------ -- Sends Meet table data from master to nodes insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('Meet','master_2_nodes', 100, current_timestamp, current_timestamp); insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('Meet','nodes_2_master', 100, current_timestamp, current_timestamp); insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('Clubs','master_2_nodes', 100, current_timestamp, current_timestamp); insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('Clubs','nodes_2_master', 100, current_timestamp, current_timestamp); insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('Events','master_2_nodes', 100, current_timestamp, current_timestamp); insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('Events','nodes_2_master', 100, current_timestamp, current_timestamp); insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('Qualifying_Times','master_2_nodes', 100, current_timestamp, current_timestamp); insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('Qualifying_Times','nodes_2_master', 100, current_timestamp, current_timestamp); insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('Sessions','master_2_nodes', 100, current_timestamp, current_timestamp); insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('Sessions','nodes_2_master', 100, current_timestamp, current_timestamp); insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('Swimmers','master_2_nodes', 100, current_timestamp, current_timestamp); insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('Swimmers','nodes_2_master', 100, current_timestamp, current_timestamp); -- Initial Load: -- insert into sym_trigger_router -- (trigger_id,router_id,initial_load_order,last_update_time,create_time) -- values('Meet_Initial','master_2_nodes', 100, current_timestamp, current_timestamp); -- insert into sym_trigger_router -- (trigger_id,router_id,initial_load_order,last_update_time,create_time) -- values('Meet_Initial','nodes_2_master', 100, current_timestamp, current_timestamp); -- insert into sym_trigger_router -- (trigger_id,router_id,initial_load_order,last_update_time,create_time) -- values('Clubs_Initial','master_2_nodes', 100, current_timestamp, current_timestamp); -- insert into sym_trigger_router -- (trigger_id,router_id,initial_load_order,last_update_time,create_time) -- values('Clubs_Initial','nodes_2_master', 100, current_timestamp, current_timestamp); -- insert into sym_trigger_router -- (trigger_id,router_id,initial_load_order,last_update_time,create_time) -- values('Events_Initial','master_2_nodes', 100, current_timestamp, current_timestamp); -- insert into sym_trigger_router -- (trigger_id,router_id,initial_load_order,last_update_time,create_time) -- values('Events_Initial','nodes_2_master', 100, current_timestamp, current_timestamp); -- insert into sym_trigger_router -- (trigger_id,router_id,initial_load_order,last_update_time,create_time) -- values('Qualifying_Times_Initial','master_2_nodes', 100, current_timestamp, current_timestamp); -- insert into sym_trigger_router -- (trigger_id,router_id,initial_load_order,last_update_time,create_time) -- values('Qualifying_Times_Initial','nodes_2_master', 100, current_timestamp, current_timestamp); -- insert into sym_trigger_router -- (trigger_id,router_id,initial_load_order,last_update_time,create_time) -- values('Sessions_Initial','master_2_nodes', 100, current_timestamp, current_timestamp); -- insert into sym_trigger_router -- (trigger_id,router_id,initial_load_order,last_update_time,create_time) -- values('Sessions_Initial','nodes_2_master', 100, current_timestamp, current_timestamp); -- insert into sym_trigger_router -- (trigger_id,router_id,initial_load_order,last_update_time,create_time) -- values('Swimmers_Initial','master_2_nodes', 100, current_timestamp, current_timestamp); -- insert into sym_trigger_router -- (trigger_id,router_id,initial_load_order,last_update_time,create_time) -- values('Swimmers_Initial','nodes_2_master', 100, current_timestamp, current_timestamp); -- -- Send all items to all stores -- insert into sym_trigger_router -- (trigger_id,router_id,initial_load_order,last_update_time,create_time) -- values('item','corp_2_store', 100, current_timestamp, current_timestamp); -- -- Send item prices to associated stores -- insert into sym_trigger_router -- (trigger_id,router_id,initial_load_order,initial_load_select,last_update_time,create_time) -- values('item_selling_price','corp_2_one_store',100,'store_id=''$(externalId)''',current_timestamp,current_timestamp); -- -- Send all sales transactions to corp -- insert into sym_trigger_router -- (trigger_id,router_id,initial_load_order,last_update_time,create_time) -- values('sale_transaction','store_2_corp', 200, current_timestamp, current_timestamp); -- insert into sym_trigger_router -- (trigger_id,router_id,initial_load_order,last_update_time,create_time) -- values('sale_return_line_item','store_2_corp', 200, current_timestamp, current_timestamp); -- -- Send all sales transactions to store during initial load -- insert into sym_trigger_router -- (trigger_id,router_id,initial_load_order,last_update_time,create_time) -- values('sale_transaction_corp','corp_2_store', 200, current_timestamp, current_timestamp); -- insert into sym_trigger_router -- (trigger_id,router_id,initial_load_order,last_update_time,create_time) -- values('sale_return_line_item_corp','corp_2_store', 200, current_timestamp, current_timestamp);