Using Wildcards to Sync Database Tables

Quickly select and dynamically add tables to database synchronization using wildcard characters. This powerful feature can save time during configuration and even eliminate the need to make changes to configuration in the future. Since data capture triggers can be created from a schedule or on-demand programmatically, wildcard characters open up the possibility of new applications, such as using SymmetricDS database replication in Platform As A Service (PaaS) where new tables created by a client are automatically replicated between high availability servers. This article will demonstrate using wildcard characters to sync some tables while ignoring others.

Replication Setup

A common scenario is to sync a set of tables while excluding some tables from being synced. If tables are named with common conventions, a wildcard character can match a set of tables. For example, a set of customer tables might be prefixed with “CUSTOMER”.

create table customer ( id integer primary key, first_name varchar(50), last_name varchar(50) ); create table customer_address ( id integer primary key, customer_id integer, street varchar(50), city varchar(50), state varchar(2), zip varchar(10) );

At the same time, the database might have tables that should be excluded from replication. For example, a set of employee tables might be prefixed with “EMPLOYEE”.

create table employee ( id integer primary key, first_name varchar(50), last_name varchar(50) ); create table employee_address ( id integer primary key, customer_id integer, street varchar(50), city varchar(50), state varchar(2), zip varchar(10) );

Replicating Tables with a Wildcard

Using the 2-tier profile in SymmetricDS Pro, the synchronization configuration is quickly setup. To sync customer tables between nodes, a new trigger is created using the Configure->Triggers screen. The wildcard character is an asterisks that means “match any characters.” So, to match the set of customer tables, the Source Table is specified as “CUSTOMER*”.

Configure Trigger with Wildcard

From the Manage->Logging screen, or directly from the symmetric.log file, it will output the triggers that are created. The new triggers can also be verified from the Manage->Installed Triggers screen. Data capture triggers are created for CUSTOMER and CUSTOMER_ADDRESS tables, while ignoring the employee tables.

Creating SYM_ON_I_FOR_CSTMR_DDRSS_SRVR trigger for CUSTOMER_ADDRESS Creating SYM_ON_U_FOR_CSTMR_DDRSS_SRVR trigger for CUSTOMER_ADDRESS Creating SYM_ON_D_FOR_CSTMR_DDRSS_SRVR trigger for CUSTOMER_ADDRESS Creating SYM_ON_I_FOR_CSTMR_SRVR trigger for CUSTOMER Creating SYM_ON_U_FOR_CSTMR_SRVR trigger for CUSTOMER Creating SYM_ON_D_FOR_CSTMR_SRVR trigger for CUSTOMER

Dynamic Replication

Now that the synchronization configuration is in place, when new tables are created that match the wildcard expression, they will be automatically included in the database replication. For example, a new customer table is created.

create table customer_phone ( id integer primary key, customer_id integer, type char(1), phone_num varchar(50), phone_ext varchar(10) );

Using the Manage->Installed Triggers screen, the “Sync Triggers” button will cause the new data capture triggers to be created immediately.

Installed Triggers Screen

The Sync Triggers process is scheduled as a job that runs every night, but the schedule can be changed to run at any frequency using a cron expression. The expression has fields for the second, minute, hour, day of month, month, and day of week that are separated by a space. The fields are numeric, although month and day of week can also be specified by their name. For example, a cron expression of “0 0/5 9-17 * * MON-FRI” would run the job every 5 minutes between 9 AM and 5 PM on Monday through Friday.

Scheduling the Sync Triggers Job

Java Management Extensions (JMX) are available to programmatically execute the Sync Triggers process from external clients. An application could alter the database or add new tables, then call Sync Triggers over JMX to detect the changes and setup replication. SymmetricDS also includes a web frontend to access JMX operations using the MX4J HTTP Adapter. The Sync Triggers operation is found in the management bean named “org.jumpmind.symmetric.server:name=Node” under the “org.jumpmind.symmetric.server” domain.

Running Sync Triggers from JMX

Conclusion

When configuring synchronization, consider using a wildcard character to match the set of tables. If all tables in the catalog or schema are required to be replicated, a single Trigger entry with a wildcard is a quick and easy configuration. Adding a configuration for a specific table can be used to override any wildcard Trigger entries. SymmetricDS will use the most specific Trigger configuration. Replicating new tables automatically as they are created can be used to build unique solutions that require the system to assemble itself in response to change.