This sample shows how to set up an environment for data replication using the solidDB Advanced Replication component, see Advanced Replication.
The files for the sample are located in the soliddb-installdir\samples\smartflow directory.
For general information about running the samples, see Samples.
The sample includes the following scenarios:
▪ Basic sample: Demonstrates how to configure, connect, and synchronize a master and replica database. For detailed information about the steps, see Running Basic sample.
▪ Intelligent Transaction sample: Demonstrates how to manage replication conflicts. For detailed information about the steps, see Running Intelligent Transaction sample.
▪ Sync Pull Notify sample: Demonstrates how to get the replica database to initiate a refresh whenever data is updated in the master database. For detailed information about the steps, see Running Sync Pull Notify sample.
Running Basic sample
Run the runme script (runme.bat in Windows) in the samples\smartflow\eval_setup folder. The script completes the following actions:
Prepare servers
1 Start two solidDB servers.
2 Specify one server to be the master database, create the table (SYNCDEMO) that will be synchronized with the replica database, and define a publication (PUB_DEMO) that publishes all data that is in the SYNCDEMO table (master1.sql).
3 Specify the other server to be the replica database, create the table (SYNCDEMO) with same definition as in master database, and register the publication (PUB_DEMO) that was created in the master database (replica1.sql).
4 (both databases) Create procedures for inserting and updating data into SYNCDEMO (proced1.sql, proced2.sql).
5 (both databases) Run a SELECT statement to show the status of the SYNCDEMO table (the table has no data in either database).
Insert records
1 (replica database) Register to publication PUB_DEMO, insert two rows in SYNCDEMO table and save the transaction to be propagated to the master database (replica2.sql).
2 (replica database) Create a new message, append tasks to the message, and send message to propagate and refresh transactions with master database (replica3.sql).
3 (master database) Delete a row from the SYNCDEMO table (master2.sql).
4 (replica database) Insert two more rows in the SYNCDEMO table, and save the transaction to be propagated to the master database (replica4.sql).
5 (replica database) Update a row in the SYNCDEMO table, and save the transaction to be propagated to the master database (replica5.sql).
6 (master database) Update a different row in SYNCDEMO table (master3.sql).
7 (replica database) Update the same row in the SYNCDEMO table that was updated in the master database, and save the transaction to be propagated to the master database (replica6.sql).
8 (both databases) Run a SELECT statement to show the status of the SYNCDEMO table.
Result
Each table contains all the changes that were made before the refresh (from both databases). However, the rows that were deleted or changed in the master database after the refresh are reflected only in the master database, and the rows that were added or changed in the replica database after the refresh are reflected only in the replica database. This includes one row that has different content in each database. In the replica database, the status field indicates which changes are provisional (that is, have not been accepted by the master database yet).
Running Intelligent Transaction sample
Run the runme script (runme.bat in Windows) in the samples\smartflow\intelligent_transaction folder. The script completes the following actions:
Prepare servers
1 Initialize the master database and create user IDs for the system.
2 Initialize the replica database and registers it with master database.
3 (both databases) Create CUSTOMER, ORDER, and ACCOUNT tables and procedures for creating orders, updating customer credit, and updating account balances. Also create an ERR_LOG table to hold details of any orders that fail.
4 (master database) Define a publication that publishes all data that is in the CUSTOMER table and that matches a given area code.
Insert data
1 (master database) Populate the CUSTOMER and ACCOUNT tables with data.
2 (replica database) Create procedures that do the data synchronization.
3 (replica database) Add two orders for the same customer to the database and also updates associated customer credit and account balances.
4 (replica database) Verify that the replica has two orders and that the account balance and credit limit are updated.
5 (master database) Add an order to the database that conflicts with the orders added to replica database such that the customer credit limit is exceeded.
6 (master database) Verify that the master database has one order and that the account balance and credit limit are updated.
7 Synchronize the databases, propagating the two transactions from the replica database to the master database.
8 (replica database) Verify the status of the orders. The order that was created in the master database, and the first order that was created in the replica database have succeeded, but the second order that was created in the replica database has a status of INV (invalid) and the ERR_LOG table has an entry detailing that the order failed.
Running Sync Pull Notify sample
Run the sample by running the runme script (runme.bat in Windows) in the samples\smartflow\sync_pull_notify folder. The script completes the following actions:
1 Initialize the master database, create a table (CUSTOMERS), define a publication (PUBLICATION1) that publishes all data that is in the CUSTOMERS table, and define a trigger that, when a transaction is committed, initiates the replica to request a refresh of the updated data.
2 Initialize the replica database and register it with master database, register with the publication, and define a procedure to request a refresh of updated data from the master database.
3 (master database) Execute the synchronization connection, and insert data into the CUSTOMERS table. The insertion automatically initiates the replica to request the refresh.
4 (replica database) Verify that the newly inserted data is in the CUSTOMER table.