Chapter 3. Concepts

Table of Contents

3.1. Configuration Data Model
3.1.1. Node Table
3.1.2. Node Security Table
3.1.3. Node Identity Table
3.1.4. Node Group Table
3.1.5. Node Group Link Table
3.1.6. Channel Table
3.1.7. Node Channel Control Table
3.1.8. Trigger Table
3.1.9. Parameter Table
3.1.10. Registration Redirect Table
3.2. Runtime Data Model
3.2.1. Trigger History Table
3.2.2. Data Table
3.2.3. Data Event Table
3.2.4. Outgoing Batch Table
3.2.5. Outgoing Batch History Table
3.2.6. Incoming Batch Table
3.2.7. Incoming Batch History Table

Many of the data synchronization concepts can be understood by examining the data model. The configuration data model is stored in a set of tables updated by the user as needed to configure the system. In contrast, the set of tables for the runtime data model change constantly as the system captures data changes and records the activity to deliver them.

Note that all tables are prepended with a configurable prefix so that multiple instances of SymmetricDS may coexist in the same database. The default prefix is sym_.

3.1. Configuration Data Model

The configuration is entered by the user into the data model to control the behavior of what data is synchronized to which nodes. To ease management of several databases, nodes are put into groups, and groups are linked together for synchronization. A trigger captures data for a table, which can include conditions and criteria for subsets. Triggers are grouped into channels for prioritization and control of data flow.

Configuration Data Model

Figure 3.1. Configuration Data Model


3.1.1. Node Table

An instance of SymmetricDS that synchronizes data with one or more nodes. Each node has a unique identifier (nodeId) that is used when communicating, as well as a domain-specific identifier (externalId) that provides context within the local system.

Column NameTypeNullKeyDefaultDescription
node_idvarchar(50)NPK Unique identifier for a node
node_group_idvarchar(50)NFK The node group that this node belongs to, such as "store"
external_idvarchar(50)N   A domain-specific identifier for context within the local system. For example, the retail store number.
sync_enabledbooleanintY 0 Indicates whether this node should be sent synchronization. Disabled nodes are ignored by the triggers, so no entries are made in DataEvent for the node.
sync_urlvarchar(2000)Y  The URL to contact the node for synchronization.
schema_versionvarchar(50)Y   The version of the database schema this node manages. Useful for specifying synchronization by version.
symmetric_versionvarchar(50)Y  The version of SymmetricDS running at this node.
database_typevarchar(50)Y   The database product name at this node as reported by JDBC.
database_versionvarchar(50)Y   The database product version at this node as reported by JDBC.
heartbeat_timetimestampY   The last timestamp when the node sent a heartbeat, which is attempted every ten minutes by default.
timezone_offsetvarchar(6)Y   The timezone offset in RFC822 format at the time of the last heartbeat.
created_at_node_idvarchar(50)Y   The id of the node where this node was created. This is filled with the id found in node_identity where registration was opened for the node.

Table 3.1. node


3.1.2. Node Security Table

Security features like node passwords and open registration flag are stored in the NodeSecurity table.

Column NameTypeNullKeyDefaultDescription
node_idvarchar(50)NPK FK Unique identifier for a node
passwordvarchar(50)N   The password used by the node to prove its identity during synchronization.
registration_enabledbooleanintN 0Indicates whether registration is open for this node.
registration_timetimestampY  The timestamp when this node was registered.
initial_load_enabledbooleanintN 0Indicates whether an initial load will be sent to this node.
initial_load_timetimestampY  The timestamp when this node started the initial load.
created_at_node_idvarchar(50)Y   The id of the node where this node was created. This is filled with the id found in node_identity where registration was opened for the node.

Table 3.2. node_security


3.1.3. Node Identity Table

After registration, this table will have one row representing the identity of the node. For a root node, the row is entered by the user.

Column NameTypeNullKeyDefaultDescription
node_idvarchar(50)NPK FK Unique identifier for a node

Table 3.3. node_identity


3.1.4. Node Group Table

A category of Nodes that synchronizes data with one or more NodeGroups. A common use of NodeGroup is to describe a level in a hierarchy of data synchronization.

Column NameTypeNullKeyDefaultDescription
node_group_idvarchar(50)NPK  Unique identifier for a node group, usually named something meaningful, like "store" or "warehouse".
descriptionvarchar(50)Y  A description of this node group.

Table 3.4. node_group


3.1.5. Node Group Link Table

A source NodeGroup sends its data updates to a target NodeGroup using a pull, push, or custom technique.

Column NameTypeNullKeyDefaultDescription
source_node_group_idvarchar(50)NPK FK The node group where data changes should be captured.
target_node_group_idvarchar(50)NPK FK The node group where data changes will be sent.
data_event_actionchar(1)N W The notification scheme used to send data changes to the target node group. (P = Push, W = Wait for Pull)

Table 3.5. node_group_link


3.1.6. Channel Table

A category of data that can be synchronized independently of other Channels. Channels allow control over the type of data flowing and prevents one type of synchronization from contending with another.

Column NameTypeNullKeyDefaultDescription
channel_idvarchar(50)NPK  A unique identifer, usually named something meaningful, like "sales" or "inventory".
processing_orderintegerN 1Order of sequence to process channel data.
max_batch_sizeintegerN 1000 The maximum number of Data Events to process within a batch for this channel.
max_batch_to_sendintegerN 60 The maximum number of batches to send during a synchronization between two nodes.
enabledbooleanintN 1Indicates whether channel is enabled or not.
descriptionvarchar(1000)Y  Description on the type of data carried in this channel.

Table 3.6. channel


3.1.7. Node Channel Control Table

The Node Channel Control is used to ignore or suspend a channel. A channel that is ignored will have its Data Events batched and they will immediately be marked as "OK" without sending them. A channel that is suspended is skipped when batching Data Events.

Column NameTypeNullKeyDefaultDescription
node_idvarchar(50)NPK FK Unique identifier for a node
channel_idvarchar(50)NPK FK  A unique identifer, usually named something meaningful, like "sales" or "inventory".
suspend_enabledbooleanintY 0 Indicates if this channel is suspended, which prevents its Data Events from being batched.
ignore_enabledbooleanintY 0 Indicates if this channel is ignored, which marks its Data Events as if they were actually processed.

Table 3.7. node_channel_ctl


3.1.8. Trigger Table

The database triggers that capture changes in the database are automatically generated by SymmetricDS. Configuration of which triggers are generated and how they will behave is stored in the Trigger table.

Column NameTypeNullKeyDefaultDescription
trigger_idintegerNPK Unique identifier for a trigger.
source_schema_namevarchar(50)Y   The schema name where the source table resides. The database trigger will be created for the source_table_name in this schema.
source_table_namevarchar(50)N   The name of the source table that will have a trigger installed to watch for data changes.
target_schema_namevarchar(50)Y   The schema name where the target table resides. When data is sent to the remote database, the SQL statements used to load the data will qualify the target_table_name with this schema.
target_table_namevarchar(50)Y   The name of the target table that will have data changes synchronized to it.
source_node_group_idvarchar(50)N   The node group that will install this trigger to watch for data changes,
target_node_group_idvarchar(50)N   The node group that will have data changes synchronized to it.
channel_idvarchar(50)N  The channel that data changes will flow through.
sync_on_updatebooleanintN 1Whether or not to install an update trigger.
sync_on_insertbooleanintN 1Whether or not to install an insert trigger.
sync_on_deletebooleanintN 1Whether or not to install a delete trigger.
sync_on_incoming_batchbooleanintN 0 Whether or not an incoming batch that loads data into this table should cause the triggers to capture Data Events. Be careful turning this on, because an update loop is possible.
sync_column_levelbooleanintN 0 Perform column level synchronization for updates, so only the fields that changed are updated on the target database. When updates come from multiple sources at the same time, this enables merging of changes that do not conflict. The entire change row is still sent, which preserves the ability to fallback to an insert if the row does not exist.
name_for_update_triggervarchar(30)Y   Override the default generated name for the update trigger.
name_for_insert_triggervarchar(30)Y   Override the default generated name for the insert trigger.
name_for_delete_triggervarchar(30)Y   Override the default generated name for the delete trigger.
sync_on_update_conditionvarchar(1000)Y   Specify a condition for the update trigger firing using an expression specific to the database.
sync_on_insert_conditionvarchar(1000)Y   Specify a condition for the insert trigger firing using an expression specific to the database.
sync_on_delete_conditionvarchar(1000)Y   Specify a condition for the delete trigger firing using an expression specific to the database.
initial_load_selectvarchar(1000)Y   Specify a where-clause for an initial load of this table. The table is aliased as "t". Replacement variables are $(nodeId), $(groupId), and $(externalId).
node_selectvarchar(1000)Y   Specify a where-clause for selecting the nodes that will receive data changes. The node table is aliased as "c".
tx_id_expressionvarchar(1000)Y   Override the default expression for the transaction identifier that groups the data changes that were committed together.
excluded_column_namesvarchar(1000)Y   Specify a comma-delimited list of columns that should not be synchronized from this table.
initial_load_orderintegerN 1 Order sequence of this table when an initial load is sent to a node.
create_timetimestampN  Timestamp when this entry was created.
inactive_timetimestampY   Timestamp when this entry was inactivated, which stops capturing of data changes.
last_updated_byvarchar(50)Y  The user who last updated this entry.
last_updated_timetimestampN  Timestamp when a user last updated this entry.

Table 3.8. trigger


3.1.9. Parameter Table

The Parameter table provides a way to manage most SymmetricDS settings in the database.

Column NameTypeNullKeyDefaultDescription
external_idvarchar(50)NPK Target the parameter at a specific external id. To target all nodes, use the value of 'ALL.'
node_group_idvarchar(50)NPK FK  Target the parameter at a specific node group id. To target all groups, use the value of 'ALL.'
param_keyvarchar(100)NPK  The name of the parameter.
param_valuevarchar(1000)N   The value of the parameter.

Table 3.9. parameter


3.1.10. Registration Redirect Table

The Registration redirect table provides a way for a centralized registration server to redirect registering Nodes to their prospective parent Node in a multi-tiered deployment.

Column NameTypeNullKeyDefaultDescription
registrant_external_idvarchar(50)NPK Maps the external id of a registration request to a different parent node.
registration_node_idvarchar(50)NPK The id of the Node that a registration request should be redirected to.

Table 3.10. registration_redirect


3.2. Runtime Data Model

At runtime, the configuration is used to capture data changes and route them to nodes. The data changes are placed together in a single unit called a batch that can be loaded by another node. Outgoing batches are delivered to nodes and acknowledged. Incoming batches are received and loaded. History is recorded for batch status changes and statistics.

Runtime Data Model

Figure 3.2. Runtime Data Model


3.2.1. Trigger History Table

A history of a table's definition and the Trigger used to capture data from the table. When a database trigger captures a data change, it references a Trigger Hist entry so it is possible to know which columns the data represents. Trigger Hist entries are made during the sync trigger process, which runs at each startup, each night in the SyncTriggersJob, or any time the syncTriggers() JMX method is manually invoked. A new entry is made when a table definition or a Trigger definition is changed, which causes a database trigger to be created or rebuilt.

Column NameTypeNullKeyDefaultDescription
trigger_hist_idintegerNPK Unique identifier for a Trigger Hist.
trigger_idintegerNFK Unique identifier for a Trigger.
source_table_namevarchar(50)N   The name of the source table that will have a trigger installed to watch for data changes.
source_catalog_namevarchar(50)Y  The catalog name where the source table resides.
source_schema_namevarchar(50)Y  The schema name where the source table resides.
name_for_insert_triggervarchar(50)N  The name used when the insert trigger was created.
name_for_update_triggervarchar(50)N  The name used when the update trigger was created.
name_for_delete_triggervarchar(50)N  The name used when the delete trigger was created.
table_hashintegerN  A hash of the table definition, used to detect changes in the definition.
column_nameslongvarcharN   The column names defined on the table. The column names are stored in comma-separated values (CSV) format.
pk_column_nameslongvarcharN   The primary key column names defined on the table. The column names are stored in comma-separated values (CSV) format.
last_trigger_build_reasonchar(1)N   The following reasons for a change are possible: New trigger that has not been created before (N); Schema changes in the table were detected (S); Configuration changes in Trigger (C); Trigger was missing (T).
create_timetimestampN  The date and time when this entry was recorded.
inactive_timetimestampY  The date and time when a Trigger was inactivated.

Table 3.11. trigger_hist


3.2.2. Data Table

The captured data change that occurred to a row in the database. Entries in Data are created by database triggers.

Column NameTypeNullKeyDefaultDescription
data_idintegerNPK Unique identifier for a data
table_namevarchar(50)N   The name of the table in which a change occurred that this entry records.
event_typechar(1)Y   The type of event captured by this entry. For triggers, this is the change that occurred, which is "I" for insert, "U" for update, or "D" for delete. Other events include: "R" for reloading the entire table (or subset of the table) to the node; "S" for running dynamic SQL at the node, which is used for adhoc administration.
row_datalongvarcharY   The captured data change from the synchronized table. The column values are stored in comma-separated values (CSV) format.
pk_datalongvarcharY   The primary key values of the captured data change from the synchronized table. This data is captured for updates and deletes. The primary key values are stored in comma-separated values (CSV) format.
trigger_hist_idintegerNFK  The foreign key to the Trigger Hist entry that contains the primary key and column names for the table being synchronized.
create_timetimestampY  The timestamp when the data change was captured.

Table 3.12. data


3.2.3. Data Event Table

The Data Event represents routing of a Data to one or more Nodes. Entries in Data Event are created by database triggers.

Column NameTypeNullKeyDefaultDescription
data_idintegerNPK FK The Data that will be routed.
node_idvarchar(50)NPK FK The Node that will receive the Data.
channel_idvarchar(50)NFK The channel that this data belongs to, such as "prices"
transaction_idvarchar(1000)Y   An optional transaction identifier that links multiple data changes together as the same transaction.
batch_idintegerYFK  A unique identifier for a batch that will be a unit of delivery for multiple Data Events.
batchedchar(1)N 0 Whether or not this Data Event is prepared for a batch.

Table 3.13. data_event


3.2.4. Outgoing Batch Table

The Outgoing Batch is used for tracking the sending a collection of Data to a Node in the system. A new Outgoing Batch is created by the Outgoing Batch Service and given a status of "NE". After sending the Outgoing Batch to its target Node, the status becomes "SE". The Node responds with either a success status of "OK" or an error status of "ER". An error while sending to the Node also results in an error status of "ER" regardless of whether the Node sends that acknowledgement.

Column NameTypeNullKeyDefaultDescription
batch_idintegerNPK A unique ID for the Batch.
node_idvarchar(50)Y  The Node that will be sent this Batch.
channel_idvarchar(50)Y  The Channel that categorizes the Data in this Batch.
batch_typechar(2)N EV Batch types include events from triggers when rows change (EV) and initial loads of data that send an entire table (IL).
statuschar(2)Y   The current status of the Batch can be newly created (NE), sent to a Node (SE), acknowledged as successful (OK), and error (ER).
create_timetimestampY  The date and time when the Batch was created.

Table 3.14. outgoing_batch


3.2.5. Outgoing Batch History Table

A history of status changes to the Outgoing Batch, along with statistics of the work performed.

Column NameTypeNullKeyDefaultDescription
batch_idintegerNPK A unique ID for the Batch.
node_idvarchar(50)Y  The Node that will be sent this Batch.
statuschar(2)Y   The current status of the Batch can be newly created (NE), sent to a Node (SE), acknowledged as successful (OK), or error (ER).
start_timetimestampY  The date and time when the process for this entry was started. (1.4)
end_timetimestampY  The date and time when the process for this entry was ended. (1.4)
data_event_countintegerY   The number of Data Events in the Batch. This is only populated for a new status (NE).
failed_data_idintegerY   For a status of error (ER), this is the Data entry that was being processed when the Batch failed.
hostnamevarchar(50)Y   The name of the machine processing the Batch, which is meaningful when running a cluster of SymmetricDS instances. (1.4)
network_millisintegerY   The amount of time in milliseconds spent using the network to send the Batch. This is currently unimplemented. (1.4)
filter_millisintegerY   The amount of time in milliseconds spent in filters. This is currently unimplemented. (1.4)
database_millisintegerY   The amount of time in milliseconds spent in the database. This is only populated for a status of new (NE). (1.4)
sql_statevarchar(10)Y   For a status of error (ER), this is the XOPEN or SQL 99 SQL State. (1.4)
sql_codeintegerY   For a status of error (ER), this is the error code from the database that is specific to the vendor. (1.4)
sql_messagevarchar(50)Y   For a status of error (ER), this is the error message that describes the error. (1.4)

Table 3.15. outgoing_batch_hist


3.2.6. Incoming Batch Table

The Incoming Batch is used for tracking the status of loading an Outgoing Batch from another Node. Data is loaded and commited at the batch level. The status of the Incoming Batch is either successful (OK) or error (ER).

Column NameTypeNullKeyDefaultDescription
batch_idintegerNPK A unique ID for the Batch.
node_idvarchar(50)NPK The Node that sent this Batch.
statuschar(2)Y   The current status of the Batch can be successfully loaded (OK) or error (ER).
create_timetimestampY  The date and time when the Batch was first received.

Table 3.16. incoming_batch


3.2.7. Incoming Batch History Table

A history of status changes to the Incoming Batch, along with statistics of the work performed.

Column NameTypeNullKeyDefaultDescription
batch_idintegerNPK A unique ID for the Batch.
node_idvarchar(50)Y  The Node that sent this Batch to be loaded.
statuschar(2)Y   The current status of the Batch can be loaded successfully (OK), skipped because previously loaded (SK), or error (ER).
start_timetimestampY  The date and time when the load for this entry was started.
end_timetimestampY  The date and time when the load for this entry was ended.
failed_row_numberintegerY   For a status of error (ER), this is the row number (starting with row one) that was being processed when the Batch failed.
hostnamevarchar(50)Y   The name of the machine processing the Batch, which is meaningful when running a cluster of SymmetricDS instances.
network_millisintegerY   The amount of time in milliseconds spent using the network to receive the Batch. This is currently unimplemented.
filter_millisintegerY   The amount of time in milliseconds spent in filters. This currently includes time from IDataLoaderFilters.
database_millisintegerY   The amount of time in milliseconds spent in the database.
sql_statevarchar(10)Y   For a status of error (ER), this is the XOPEN or SQL 99 SQL State. (1.4)
sql_codeintegerY   For a status of error (ER), this is the error code from the database that is specific to the vendor. (1.4)
sql_messagevarchar(50)Y   For a status of error (ER), this is the error message that describes the error. (1.4)

Table 3.17. incoming_batch_hist