Symmetricds document Translation--"Chapter 3. Detailed configuration (config) [section C] "

3.6. Routers

The route implementations provided in the current implementation include:

1. Default Router: This Router sends all the data to all nodes in the group to which the target node defined in Router is a member.
2. Column Match Router: This Router can compare the old value of a column (the value of this column in the Data source table) or the new value (the value that will be set on the target node) to a constant value or to the value of external_id and node_id of the node.
3. Lookup Router: This Router can be configured to determine whether to perform a routing operation when routing data based on an existing table or subordinate table.
4. Subselect Router: This Router executes an SQL expression on the database to select the node to route to. This SQL expression can be passed to the old value of a column (the value before the data change) or the new value (the value after the data changes).
5. Scripted Router: This Router executes a bean script expression to select the node to route to. This script can use the old or new values of a column.
6. XML Publishing Router: This Router directly publishes data changes to a message solution instead of transmitting these changes to an already registered node. This router must be manually configured as an extensibility point in XML format. (Can be combined with Kafka, then data can be entered into the Hadoop ecosystem)
7. Audit Table Router: This Router inserts data into an automatically created audit table. The data captured by this router record changes to the table associated with it.

Trigger and router are many-to-many relationships. This means that a trigger can capture data changes and then route these changes to multiple locations. It also means that a router can be associated with a different trigger.

3.6.1. Default Router

The simplest router, which is a router that sends all the related triggers captured to the data to all the nodes in the group to which the target node defined in the router belongs. A router is represented by a row of data in the router table. Then connect to the Trigger_router table.

The following SQL statement defines a router that will send data from the "corp" group to the "store" group.

Insert into Sym_router (router_id,               source_node_group_id, target_node_group_id, Create_time,               Last_update_ Time) VALUES (' Corp-2-store ', ' Corp ', ' store ',               

The following SQL statement establishes a mapping of the top router to the item trigger.

INSERT INTO Sym_trigger_router               (trigger_id, router_id, Initial_load_order, Create_time,               last_update_time) VALUES (' Item ', ' Corp-2-store ', 1, Current_timestamp,               

3.6.2. Column Match Router

There may be times when the data needs to be routed based on the current value of the data (after the data changes have occurred) or on the previous value of a column (before the data changes occur). When you insert the Router system table, set Router_type to column to configure a column Router, and then set the router_expression as an equation that represents the expected value of a particular row.

The first part of an expression is always a column name. This column name should always be defined in uppercase letters. Uppercase column names are prefixed with old_ to compare the old values of this column. The second part of the expression can make a constant, a symbol that can represent another column, or a symbol that can represent other Symmetricds concepts. The value of the symbol always begins with a colon (:).

Data changes are routed to all nodes of the target node group when the Status column of a table is set to ' ready to SEND '. The following SQL statement will insert a column router in the router table to complete this requirement.

Insert into Sym_router (router_id,               source_node_group_id, Target_node_group_id,router_type,               router_ Expression,create_time, Last_update_time) values               (' Corp-2-store-ok ', ' Corp ', ' Store ', ' column ', ' Status=ready to SEND ',               current_timestamp,current_timestamp);

When the value of a table's status column is modified, the changed data is routed to all nodes of the target group. The following SQL statement will insert a column router in the router table to complete this requirement. Note the use of old_status, the old_ prefix can access the old value of this column.

Insert into Sym_router (router_id,               source_node_group_id,target_node_group_id, Router_type,               router_ Expression,create_time, Last_update_time) values               (' Corp-2-store-status ', ' Corp ', ' Store ', ' column ', ' Status!=:o Ld_status ',               current_timestamp,current_timestamp);

The data changes for a table are sent to the node in the target node group where the external ID of a node matches the value of the store_id column of this table. The following SQL statement will insert a column router in the router table to implement this requirement.

Insert into Sym_router (router_id,               source_node_group_id,target_node_group_id, Router_type,               router_ Expression,create_time, Last_update_time) values               (' Corp-2-store-id ', ' Corp ', ' Store ', ' column ', ' store_id=: external_id ',               current_timestamp,current_timestamp);

The following 3 properties of a node can be referenced:

1.: node_id

2.: external_id

3.: node_group_id

In the route process, you can always capture the value of External_data as a virtual column.

The data changes for a table need to be routed to a redirect node, and the external ID of the node is defined in the Registration_redirect table. The following SQL statement will insert a column router in the router table to implement this requirement.

Insert into Sym_router (router_id,               source_node_group_id,target_node_group_id, Router_type,               router_ expression, Create_time,last_update_time) values               (' Corp-2-store-redirect ', ' Corp ', ' Store ', ' column ',               ' Store_id=:redirect_node ', Current_timestamp, Current_timestamp);

Multiple columns can be configured in Router_expression. When more than one column is configured, all successful matching nodes will be added to the list of nodes to be routed. Here is a simple example of a table in which the value of the store_id column needs to be equal to the external_id of one node or all constant, equal to the all constant means that all nodes will receive data updates.

Insert into Sym_router (router_id,               source_node_group_id,target_node_group_id, Router_type,               router_ Expression,create_time, Last_update_time) values               (' corp-2-store-multiple-matches ', ' Corp ', ' Store ', ' column ',               ' Store_id=all orstore_id=:external_id ', Current_timestamp,               current_timestamp);

The NULL keyword can be used to check if a column is empty. If the column is empty, the data is routed to all nodes. In the following example, when the store_id column is used to route nodes external_id equals store_id, if store_id is empty, all nodes are routed.

Insert into Sym_router (router_id,               source_node_group_id,target_node_group_id, Router_type,               router_ Expression,create_time, Last_update_time) values               (' corp-2-store-multiple-matches ', ' Corp ', ' Store ', ' column ',               ' Store_id=null orstore_id=:external_id ', Current_timestamp,               current_timestamp);

3.6.3. Lookup Table Router

A lookup table may contain the ID of the node to which the data is to be routed. This could be an already existing table or an attached table specifically for routing data to be added. Lookup Table router is configured by specifying the value of Router_type when inserting a router into router, and you can set multiple configuration parameters in the LookupTable column.

Each of the following configuration parameters is required.

Lookup_table

The name of the Lookup table.

Key_column

The name of the column in the table that will be router. This is inserted as the primary key into the lookup table.

Lookup_key_column

The name of the column for the primary key in the lookup table.

External_id_column

This is the name of a column that contains the external_id of the node to be routed in the lookup table.

Note that the lookup table will be read into memory and cached in case the data is used during the route to a channel.

Consider that a table may be routed to a particular store, but the data for the changed table contains only the branding information. In this example, the store table can be used as a lookup table.

Insert into Sym_router (router_id,               source_node_group_id,target_node_group_id, Router_type,               router_ Expression,create_time, Last_update_time) values               (' Corp-2-store-ok ', ' Corp ', ' Store ', ' lookuptable ', ' Lookup_ Table=store               key_column=brand_idlookup_key_column=brand_id               external_id_column=store_id ', Current_ timestamp, current_timestamp);

3.6.4. Subselect Router

Sometimes it is necessary to determine whether the current data needs to be routed based on data that is not currently being synchronized. Subselect router can be used in this case. Subselect Router router_expression column to configure a SQL query statement that returns a list of the IDs of the nodes that need to be routed. The column symbol can be used in SQL expressions, which can be replaced by a specified column in a row of data. Using this router is expensive because the Subselect statement will run once for each row to be routed. This should not be used in tables that have many rows that need to be updated. This router also has a drawback that if the data used to determine the ID of the node to be routed has been deleted, the SQL statement will not return the result, and the routing operation will not occur.

The router_expression you specify can be added to the following SQL statement to select the node ID:

Select c.node_id from Sym_node C where               c.node_group_id=:node_group_idand c.sync_enabled=1 and ...

As you can see, you could use the alias "C" To access information about the node currently being processed, such as c.external_id. There are two node-related symbols you can use in your expression:

1.: node_group_id

2.: External_data

The column that represents your data requires a colon prefix, such as ": employ_id", or ": old_employee_id". Here, the "old_" prefix indicates that the value is the value before the data changes.

For example, consider the following situation where an "Order" table and a "orderlineitem" table need to be routed to a particular store. The "Order" table has a table called "order_id" and "store_id". A store node has a external_id equal to the store_id of the "Order" table. However, the "orderlineitem" table has only one foreign key to the "order_id" of the "Order" table. In order to route data from "Orderlineitem" to the node to which the "order" table is routed, we need to refer to the records of the "Order" table.

In Symmetricds, there are two possible ways to solve this problem. One is to configure a "subselect" type of router, as follows (another way is to use External_select to capture data through a trigger, and then use this data match in column router, described in detail in Section3.6.7, " Utilizing External Select when Routing ").

Our solution is to use Subselect to compare the external ID of the current node with the value of the store_id column in the Order table, and route this data when the order_id equals the order ID of the current row:

Insert into Sym_router (router_id,               source_node_group_id,target_node_group_id, Router_type,               router_ Expression,create_time, Last_update_time) values               (' Corp-2-store ', ' Corp ', ' Store ', ' subselect ', ' c.external_id in (Select               store_id from Order whereorder_id=:order_id) ', Current_timestamp,               current_timestamp);

Finally, note that in this example, the rows in the order table must exist when the row in the Routing Orderlineitem table is associated, because when the route occurs, the SELECT statement is running, and the SELECT statement runs not when the data changes are first captured.

3.6.5. Scripted Router

Scripted router may be used when you need more flexibility in choosing the logic of the nodes you want to route. The currently available scripting language is the Bean Shell. BeanShell is a Java-like scripting language. The documentation for the Bean Shell scripting language can be viewed in http://www.beanshell.org.

The router_type of the Bean shell type router is "BSH". Route_expression is a valid BeanShell script:

1. Add the ID of a node to the target node's collection

2. Returns a new collection that contains multiple node IDs

3. Returns the ID of a node

4. Returns true if all nodes in the target group are routed and returns false instead.

The script returns a list of nodes. These nodes are legitimate Org.jumpmind.symmetric.model.Node objects. The data column current value and the old value can be used again in scripting, using a Java object to represent the data for a single column. Specify that you want to use uppercase. References to old values need to be prefixed with "old_".

If you need access to the Symmetricds service, you can use the engine variable to access an instance of an Org.jumpmind.symmetric.ISymmetricEngine interface.

In the following example, NODE_ID is a combination of store_id and Workstation_number, both of which are columns in the table to be routed:

Insert into Sym_router (router_id,               source_node_group_id,target_node_group_id, Router_type,               router_ Expression,create_time, Last_update_time) values               (' Corp-2-store-bsh ', ' Corp ', ' Store ', ' bsh ', ' Targetnodes.add ' ( store_id +               "-" +workstation_number); ', Current_timestamp, Current_timestamp);

The same functionality can be done simply by returning the node ID. The last line of the Bah script returns this value.

Insert into Sym_router (router_id,               source_node_group_id,target_node_group_id, Router_type,               router_ Expression,create_time, Last_update_time) values               (' Corp-2-store-bsh ', ' Corp ', ' Store ', ' bsh ', ' store_id + '-"+               Workstation_number ', Current_timestamp, Current_timestamp);

In the following example, if the flag column is changed, the data is synchronized to all nodes, and flag is not changed and will not be synchronized to any node. Note that here we use the "old_" prefix to access the old values of the columns.

Insert into Sym_router (router_id,               source_node_group_id,target_node_group_id, Router_type,               router_ Expression,create_time, Last_update_time) values               (' corp-2-store-flag-changed ', ' Corp ', ' Store ', ' BSH ', ' flag! = ' Null               &&! Flag.equals (Old_flag) ', Current_timestamp,               current_timestamp);

In the following example, the script iterates through each of the legitimate nodes and checks if the column named station is stripped of the space at both ends to be equal to the value of external_id.

Insert into Sym_router (router_id,               source_node_group_id,target_node_group_id, Router_type,               router_ Expression,create_time, Last_update_time) values               (' corp-2-store-trimmed-station ', ' Corp ', ' Store ', ' BSH ', ' for               (org.jumpmind.symmetric.model.Nodenode:nodes) {if (station! = NULL               &&node.getexternalid (). Equals ( Station.trim ())) {               Targetnodes.add (Node.getnodeid ());} ', Current_timestamp,               current_timestamp);

3.6.6. Audit Table Router

Audit router captures data changes by recording data changes in an audit table created and updated by a router (as long as Auto.config.database is set to true). Router creates a table whose name is the table name of the data to be captured plus the "_audit" suffix. This table will have the same structure as the original table, and there are 3 additional columns.

Three additional audit columns are added to the table:

1. AUDIT_ID: primary key of Table

2. Audit_time: The time when data changes occur

3. Audit_event: DML types that occur in this row of data

Here is an example of creating a audit router:

Insert into Sym_router (router_id,               source_node_group_id,target_node_group_id, Router_type, Create_time,               Last_update_time) VALUES (' Audit_at_corp ', ' Corp ', ' local ', ' audit ',               current_timestamp,current_timestamp);

Audit router captures data for a group connection. In order for the above Auditroute to work, it must be connected to a node_group_link using the "R" action type. "R" stands for "only Route to". In the example above, we relate to a local group. Here, the local group is newly created for audit router. There is no node data for the local node group. If the corp node is connected to a audit router trigger, a new audit table will be created on the Corp node, and the newly changed data will be inserted into the audit table.

Symmetricds document Translation--"Chapter 3. Detailed configuration (config) [section C] "