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.

Class Interface Responsibility
AbstractSymmetricDialect ISymmetricDialect Main database dialect class that handles calls and contains the trigger template and platform classes.
AbstractTriggerTemplate Generate the data definition language statements that create database triggers on tables for data capture
JdbcSymmetricDialectFactory Bootstraps the dialect by detecting the platform and instantiating the correct AbstractSymmetricDialect

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.

Class Interface Responsibility
AbstractDdlBuilder IDdlBuilder Generates statements to create and alter tables. It can process schema changes for a table and determine the alter statements needed.
DmlStatement Generates the statements to insert, update, and delete data in tables.

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.

Class Interface Responsibility
AbstractJdbcDatabasePlatform The platform class contains the SQL template, DDL reader, and DLL builder.
AbstractJdbcDdlReader IDdlReader Reads metadata about tables and columns
AbstractSqlTemplate ISqlTemplate Runs queries and updates on database
JdbcDatabasePlatformFactory Bootstraps the platform by detecting the database and instantiating the correct AbstractJdbcDatabasePlatform

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:

Project Package Implementation
symmetric-client org.jumpmind.symmetric.db.mysql MySqlSymmetricDialect
MySqlTriggerTemplate
symmetric-db org.jumpmind.db.platform.mysql MySqlDdlBuilder
MySqlDmlStatement
symmetric-jdbc org.jumpmind.db.platform.mysql MySqlDatabasePlatform
MySqlDdlReader
MySqlJdbcSqlTemplate
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:

Project: symmetric-server
Test Class: org.jumpmind.symmetric.test.SimpleIntegrationTest
Test Runner: JUnit 4

Be careful to use JRE version 6 for running the integration test. The current version of the test has methods that depend on being run in a specific order. If you use JRE 7, the methods of the test class are run in a random order, which causes errors.

 

Copyright © 2014 JumpMind, Inc. All rights reserved. Privacy Policy / Terms of Service