- Published on Monday, 25 February 2013 15:54
- Written by Eric Long
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.
|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.
|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.
|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:
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:
|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.