Syncing Data with Conditional Parent

A change data capture system like SymmetricDS will extract changes from the source and apply them at the target database. With logical replication, the user can conditionally sync data and create subsets to sync. But what if you want to hold back changes until a condition is met, then sync the change, plus all its dependent data? With some clever usage of features available in SymmetricDS 3.5, I’ll show you how.

Conditional Parent Data Scenario

Let’s say our users enter inventory documents into an onsite database to keep track of products, such as receiving, counting, and transferring them. The data is synced to a central office and other locations in real time. But we don’t want a document synced until the user is done applying all their updates and finally mark the document as active. We want this behavior because it:

• Prevents users at other locations from seeing the data before it’s ready
• Avoids lots of updates being applied to other locations that don’t need it
• Minimizes network usage by syncing only when necessary

The inventory document is stored in a parent table called INV_DOC with multiple child rows in a table called INV_LINE. When the parent status changes to active, we want to sync all the child rows also.

Routing Based on Parent Status

Using a Subselect Router

After setting up a Trigger (in Configure->Triggers) for INV_LINE, we next setup a Router (in Configure->Routers), but we only want to send data to other locations if the status is active. The status is stored on another table, the parent INV_DOC table, so we use a Subselect Router to access the status. This router runs a query against the SYM_NODE table to retrieve a list of locations where the data will be sent. We click the “New” button to create a router (see inset screenshot). In the router expression, we can append the query to limit the rows returned. Our SQL expression joins to INV_DOC using the foreign key column from the row being routed, which is a variable written in upper case and prefixed with a colon:

exists (select 1 from inv_doc where inv_doc_id = :INV_DOC_ID and status = ‘A’)

If you are paying close attention, then you may realize that this router will only send changes to INV_LINE made after the status of INV_DOC is active. In the next step, we’ll send the INV_LINE rows as soon as INV_DOC becomes active.

Sync Children Data

Using Custom Trigger Text

The Trigger for INV_DOC already has the row with the status, so it’s a simple matter to add “status = ‘A’” to the insert, update, and delete conditions to prevent it from syncing otherwise. To send the child rows, we use a Custom Update Trigger Text to append our own text to the trigger. When the status is active, we update the child rows, which causes them to be captured as well. Our table has a last_modified_time column that can be updated to the current timestamp, but most databases will allow you to set a column to itself and the trigger will still fire. (One notable exception is newer versions of MySQL, which will not fire a trigger unless data is actually changed.) You could also install your own custom database trigger on the table to perform this operation, but it’s both consistent and convenient to use configuration. (Some databases like MySQL will only allow one trigger on a table, so you may be forced to use configuration.) Here is the custom trigger text:

if (new.status = ‘A’) then update inv_line set last_modified_time = timestamp where inv_doc_id = new.inv_doc_id; end if;

Conclusion

We can prevent too many updates from sending and applying until a condition is met. Using SymmetricDS, we configure a conditional Trigger for the parent table and a subselect Router for the child table. The conditional Trigger waits for the status column to change to active and uses a custom update text to capture the child rows. The Router ensures the status is active before sending the child rows. Using this technique, we make efficient use of the network and only apply updates when needed.