Implement a New Database Dialect

The database dialect is the software layer in SymmetricDS that contains routines specific to a database platform. SymmetricDS implements data replication using a general architecture that works across platforms. When a task needs specific details for a database, it calls the database dialect to perform them. Examples of database-specific tasks handled by the dialect are installing triggers, querying metadata, and altering tables. By writing an implementation for the dialect interfaces, support for a new database can be added to SymmetricDS.

Evaluating a New Database

Before implementing a new dialect, evaluate the database for its capabilities to see which features of SymmetricDS it will support.

Data Capture

The data capture system requires database triggers. If the database doesn’t support triggers, then it can’t be a source of data, but it can still be a target to load data. The triggers record data changes in comma separated value (CSV) format, which requires functions to concatenate strings and replace quotes with escaped quotes.

Transaction Identifier

When a row of data is captured, the transaction it belongs to is also recorded. This enables SymmetricDS to ensure that all the data for the same transaction is loaded together. This feature requires a transaction identifier from the database.

Conditional Sync

The sync_on_x_condition columns of the Trigger table allow a user to specify an expression that is built into the trigger. This feature requires the database to support a procedural language that allows an “if” statement and condition.

Update Loop Prevention

With a data capture system that records changes and a data loader system that updates data, a mechanism to prevent an update loop is needed. This feature requires a way to store state that is scoped to the login session or transaction, such as a session variable or private temporary table.

CLOB Sync

To capture a character large object (CLOB) as part of a transaction, the database needs functions for handling CLOBs to concatenate them and replace quotes with escaped quotes. Instead of capturing CLOBs within the transaction, there is an option to stream them from the database when batching instead.

BLOB Sync

To capture a binary large object (CLOB) as part of a transaction, the database needs functions for encoding BLOBs as a varchar using known format such as base64 or hex. Instead of capturing BLOBs within the transaction, there is an option to stream them from the database when batching instead.

Database Dialect Classes

At the highest level, the database dialect is available in the symmetric-client project that is responsible for interfacing with a database platform. The classes are found in the org.jumpmind.symmetric.db package.

At a lower level, the database dialect in the symmetric-db project is responsible for generating data definition language (DDL) and data manipulation language (DML) statements. The classes are found in the org.jumpmind.db.sql package.

At the lowest level, the database dialect in the symmetric-jdbc project is responsible for using JDBC calls to the database to satisfy service calls. The classes are found in the org.jumpmind.db.platform package.

Database Dialect Implementation

A typical database dialect would extend the core classes, providing an implementation in its own package. The extended classes follow a naming convention with a prefix for the database name. For example, here are the classes used for the MySQL database dialect:

Finally, to bootstrap the new platform and dialect, the JdbcDatabasePlatformFactory and JdbcSymmetricDialectFactory classes are modified to detect the database and instantiate the platform and dialect instances.

Testing

After implementing a new dialect, you can run the JUnit test cases to verify it is working. The tests will only exercise functions that the dialect says it supports. For example, if the dialect returns false for isBlobSyncSupported(), then the BLOB tests are skipped.

Integration tests are performed between two instances of the replication engine. A root instance installs triggers and captures changes, while the client instance loads the changes. The db-test.properties file defines which databases will be tested and how to connect to them. For example, if you want to test MySQL as the root and H2 as the client, here are the sections of the file:

test.root=mysql
test.client=h2

mysql.db.driver=com.mysql.jdbc.Driver
mysql.db.user=root
mysql.db.password=admin
mysql.client.db.url=jdbc:mysql://localhost/SymmetricClient?tinyInt1isBit=false
mysql.root.db.url=jdbc:mysql://localhost/SymmetricRoot?tinyInt1isBit=false

h2.db.driver=org.h2.Driver
h2.db.user=sa
h2.db.password=
h2.client.db.url=jdbc:h2:file:target/clientdbs/client
h2.root.db.url=jdbc:h2:file:target/rootdbs/root

The tests will run from Maven with the “test” goal. Properties can be specified on the command line to override which database to use for the root and client. If no properties are specified, then the settings from db-test.properties is used.

mvn -Dtest.client=mysql -Dtest.root=h2 test

If you using an integrated developer environment like Eclipse, you can run individual test case classes. From Eclipse, right click on the class and select Run As->JUnit Test. The run configuration for the integration test looks like this: