"Symmetricds" Implementation of new database dialect

2018-04-20 by quiet snowy days http://www.cnblogs.com/quiet-snowy-day/p/8890785.html

This article is translated from Symmetricds official documentation Implement a New Database dialect

Published:monday, February 2013 15:54

Written by Eric Long

The database dialect is the software layer in Symmetricds, which contains programs that are specific to the database platform. Symmetricds uses the universal architecture of the Kua platform for data replication. When a task requires specific details of the database, the database dialect is called to complete. Examples of database-specific tasks related to dialect processing are the installation of triggers, querying metadata, and changing the table structure. By writing the implementation for the dialect interface, support for the new database is added to the Symmetricds.

evaluating a New Database

Before implementing a new dialect, you need to evaluate the functionality of the database to confirm which features of Symmetricds will be supported.

Data Capture --Data capture

The data capture system requires a database trigger. If a database does not support triggers, it cannot be used as a data source, but it can still load data as a target. Triggers use CSV format to record data changes, that is, a function is required to concatenate strings, and to replace quotation marks with escape characters.

Transaction Identifier --Transaction identifier

When a row of data is captured, the transaction it belongs to is also logged. This allows Symmetricds to ensure that data that is in the same transaction is loaded together. This feature requires a transaction identifier from the database.

Conditional Sync --Conditional synchronization

The Sync_on_x_condition column of the table in which the trigger is located, allowing the user to specify an expression that is built into the trigger. This feature requires the database to support a procedural language that allows "if" statements and conditions.

Update Loop Prevention --Prevent cyclic updates

A data capture system with record changes and a data loading system for updating data also requires a mechanism to prevent cyclic updates. This feature requires a way to store the state so that it is limited to a login session or transaction, such as a session variable or a private temporary table.

CLOB Sync

Capturing character large objects (CLOB) as part of a transaction, the database needs to process CLOB functions to link them, and use escape quotes to replace the quotes. In addition, you can choose to extract CLOB data streams from the database when batching, instead of capturing Clob in a transaction.

BLOB Sync

Capturing binary Large objects (BLOBs) as part of a transaction, the database needs to process BLOB functions and encode them as varchar characters using known formats such as Base64 or Hex. In addition, you can choose to extract the Blob's data stream from the database when batching, instead of capturing the blob in the transaction.

Database dialect Classes

At the highest level, the database dialect in the Symmetric-client project can be used, which is the responsibility of linking the database platform. These classes can be found under package path org.jumpmind.symmetric.db.

Class	Interface	Responsibility
Abstractsymmetricdialect	Isymmetricdialect	Main database dialect class that handles calls and contains the trigger template and platform classes. Database dialect main class, processing calls and including trigger templates and platform classes.
Abstracttriggertemplate		Generate The Data definition language statements that CREATE DATABASE triggers on tables for data capture Generates a DDL statement that creates a database trigger on the table for data capture.
Jdbcsymmetricdialectfactory		Bootstraps the dialect by detecting the platform and instantiating the correct abstractsymmetricdialect Use the probing platform to guide the dialect in order to instantiate the abstract class correctly abstractsymmetricdialect

At a low level, the responsibility of the database dialect in the SYMMETRIC-DB project is to generate DDL and DML statements. These classes can be found under package path Org.jumpmind.db.sql.

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. Generate statements for creating and changing tables. It can handle changes on the table structure and determine the necessary change statements.
Dmlstatement		Generates the statements to inserts, update, and delete data in tables. Generate statements that add, modify, and delete table data.

At the lowest level, the responsibility of the database dialect in the SYMMETRIC-JDBC project is to use a database-oriented JDBC call to satisfy the service invocation. These classes can be found under package path Org.jumpmind.db.platform.

Class	Interface	Responsibility
Abstractjdbcdatabaseplatform		The platform class contains the SQL template, DDL Reader, and DLL Builder. platform classes, including SQL templates, DDL readers, and DDL creators.
Abstractjdbcddlreader	Iddlreader	Reads metadata about tables and columns Read the table and column metadata.
Abstractsqltemplate	Isqltemplate	Runs queries and updates on database Executes queries and change statements on the database.
Jdbcdatabaseplatformfactory		Bootstraps the platform by detecting the database and instantiating the correct abstractjdbcdatabaseplatform The platform is booted by probing the database to properly instantiate the abstract class Abstractjdbcdatabaseplatform

Database dialect Implementation

A typical database dialect will extend the core classes and provide implementations in their own packages. Extension classes follow the naming convention, which is to use the database name as a prefix. For example, the following classes are used in the MySQL 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, in order to guide new platforms and dialects, the Jdbcdatabaseplatformfactory and Jdbcsymmetricdialectfactory classes have been modified to probe databases, instantiate platforms, and dialect instances.

Testing

After the new dialect is instantiated, you can verify that it is available by executing the JUnit test case. This test only uses features that are supported by dialects. For example, if the dialect for the isblobsyncsupported () method returns False, then the BLOB-related test will be skipped.

Integration testing is applied between instances of two replication engines. When a client instance loads a change, the root instance installs the trigger and captures the change. The file db-test.properties defines which databases will be tested and how to connect. For example: If you want to test MySQL as the root cause, H2 as the client, the following is the paragraph 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= FalseH2.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

You can use MAVEN's "test" target to perform tests. Properties can be specified at the command line to overwrite the root cause and the client uses those databases. If no attribute is specified, the property settings are read using the from Db-test.properties file.

MVN-DTEST.CLIENT=MYSQL-DTEST.ROOT=H2 Test

If you use an integrated development environment like eclipse, you can run a separate test case class. In Eclipse, right-click the run As->junit Test. The run configuration for the integration test is as follows:

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

Use JRE6 carefully to run integration tests. The methods of the existing beta versions depend on the specific run order. If you use JRE7, the methods of the test class are executed in random order, which results in an error.

"Symmetricds" Implementation of new database dialect