Templates

A template determines how DataSync should replicate the information of an application in particular. It defines how to connect to the databases as well as the rules that should be used in the replication process.

To work with templates you first need to configure the directory where the templates will be taken from in the Templates options, then you only need to create a new .json file for each one of your Application templates.

Application template

An Application template has the following format:

{
    "ApplicationId": string,
    "ApplicationVersion": number,
    "IgnoreSequenceNumber": boolean,
    "AutoHealingEnabled": boolean,
    "Rules": ExportRuleTemplate[]
}

Where:

• ApplicationId: is the reserved Id of the application being configured.
• ApplicationVersion: a number that indicates the version of this template.
• AutoHealingEnabled: a boolean value that indicates whether auto-healing is allowed when executing this application's rules.
• ExportRules: a list of Export rule templates.

Export rule template

Warning

Currently rules only work with tables which have a single primary key. For more information, read Primary key columns.

Each export rule template must follow this format:

{
    "Code": string,
    "Table": string,
    "ExecutableOnSites": string[],
    "Order": number,
    "Condition": string,
    "ColumnSelection": string[],
    "ColumnSelectionType": string,
    "ImportRules": ImportRuleTemplate[],
    "ColumnsToUpdate" : {
        [string]: StampData
    }
}

Where:

• Code: is a unique code to identify the rule. It must be kebab cased.
• Table: is the name of the Table to which this rule applies to.
• Direction: is a string value that indicates the rule's permitted execution directions. "ToHub" only allows exports in satellites and imports in hub sites, while "ToSatellite" only allows exports in hubs and imports in satellites. Finally, "Bidirectional" allows both ToHub and ToSatellite directions.
• ExecutableOnSites: is an array with the codes of the Sites where the Rule can be executed.
• Order: is a number of the order of priority of the rule.
• ColumnSelection: is a list of column names that will be used to filter the table columns. This names are limited to a subset of the table's columns. No mather the selection, the table's primary key will always be included.
• ColumnSelectionType: is the type of filter that will be applied with the ColumnSelection. It can be set to OnlySelected or ExcludeSelected. If ColumnSelection has elements, it can not be null. If neither ColumnSelection and ColumnSelectionType are defined, all table columns will be used instead.
• Condition: is the filter expression for rows to meet to execute the rule.
• ColumnsToUpdate: is a dictionary whose keys are the name of the columns to stamp, and the values are the ones to set in those columns.
• ImportRules: a list of Import rule templates that are associated to this export rule.
• ColumnsToUpdate: is a dictionary whose keys are the name of the columns and whose values are the stamp to perform (see stamps)

Rules best practices

When working with rules, there are some best practices we should keep in mind.

Global conditions

If a table we want to replicate represents an entity that should be shared across all sites, the rule should include the following condition:

LastUpdatedUTC > ?LAST_EXPORT_TIME AND (?SOURCE_SITE = ?HUB_SITE OR (?SOURCE_SITE <> ?HUB_SITE AND ExportMarker = 1))

Furthermore, this condition (and the following) prevents data from "bouncing", namely, being replicated back and forth among sites. Global conditions do not prevent the source satellite from receiving the emitted data back (once), though. To avoid this, three requirements must be fulfilled:

• A column named UpdateSite must be added to the desired table. It will contain a string with the code of the site that updated that row.
• A trigger must be declared for that table that, on a row insert or update, sets the current site as UpdateSite.
• A condition has to be added to that table's rules, as follows: UpdateSite <> ?DESTINATION_SITE

Installation conditions

On the other hand, there are some entities that belong only to the site that created them. In those cases, the table should only be shared between that site and its Hub. To accomplish that, the rule's condition must include the following:

LastUpdatedUTC > ?LAST_EXPORT_TIME AND ((?SOURCE_SITE = ?HUB_SITE AND ?DESTINATION_SITE = OwnerSiteCode) OR (?SOURCE_SITE <> ?HUB_SITE AND ExportMarker = 1))

CLOBs replication

When a table contains CLOBs, it is recommended to lighten the replication work by replicating those columns separately. This can be achieved by:

• Defining CLOBLastUpdatedUTC and CLOBExportMarker columns, as parallel versions of LastUpdatedUTC and ExportMarker.
• Excluding these columns, along with the CLOB column from the table's rules, creating parallel rules that work only with these columns. This last rules should implement the suggested global/installation conditions, replacing the corresponding columns.

Import rule template

Each import rule template follows a similar format to the export ones:

{
    "Code": string,
    "Order": number,
    "ColumnSelection": string[],
    "ColumnSelectionType": string,
    "OperationType": string,
    "ColumnsToUpdate" : {
        [string]: StampData
    },
    "BatchSize": number
}

Where:

• Code: is an unique code to identify the rule. It must be kebab cased.
• Order: is a number of the order of priority of the rule.
• OperationType: is the operation type that will be done by the rule, this operation could be: Insert, Update or Upsert.
• ColumnSelection: is a list of column names that will be used to filter the table columns. This names are limited to a subset of the export rule's columns. No mather the selection, the table's primary key will always be included.
• ColumnSelectionType: is the type of filter that will be applied with the ColumnSelection. It can be set to OnlySelected or ExcludeSelected. If ColumnSelection has elements, it can not be null. If neither ColumnSelection and ColumnSelectionType are defined, all table columns will be used instead.
• ColumnsToUpdate: is a dictionary whose keys are the name of the columns and whose values are the stamp to perform (see stamps).
• BatchSize: is the maximum number of rows to be send to the database at the same time.

Template versioning

As explained above, the Application template has a field to set its ApplicationVersion, this field serves the purpose of allowing to have different configurations for the same application depending on their databases, apart from not losing information about previous replication processes performed when new versions are created.

Every time a change on the database schema of the application is made you will need to define a new template with a different ApplicationVersion. This is due to how DataSync works with the databases: any change to a column or table in the schema will not be detected by DataSync once the application setup has been performed.

Important

You could have just one file with your template for certain application and update it when needed, changing its version when the schema changes, but it is advised to create a new file for each version instead.

Stamps

Each stamp data has the following shape:

{
    "Value": object,
    "Placeholder" string
}

Where:

• Value: is the value to use to stamp the column.
• Placeholder: is the name of a placeholder to use to stamp the column, which does not need to be formatted (see information below).

Important

You can use either the value or placeholder option, but not both at the same time. For now the supported types are boolean, DateTime and string.

Important

Stamp value types are not validated by DataSync, so be sure to use values that match the column type.

Available placeholders

DataSync has the following common placeholders:

• DESTINATION_SITE: has the code of the destination Site of the replication process.
• SOURCE_SITE: has the code of the source Site of the replication process.
• HUB_SITE: has the code of the Site configured as Hub in DataSync.
• LAST_EXPORT_TIME: has the date and time in which the last export was executed.
• UTC_NOW: has the date and time in which the replication process is being executed.

Important

To use the placeholders in the condition of your rules, you have to format them according to the database engine being used. I.E: when using SqlServer you must use @DESTINATION_SITE, but in Oracle it would be :DESTINATION_SITE. Placeholder names in the StampData do not need to be formatted.

Example

The final template file must be similar to the following example:

{
    "ApplicationId": "10001",
    "ApplicationVersion": 1,
    "IgnoreSequenceNumber": false,
    "AutoHealingEnabled": true,
    "ExportRules": [
        {
            "Code": "export-users",
            "Table": "Users",
            "ExecutableOnSites": ["10001", "10002", "10003"],
            "Condition": "ExportMarker = 1",
            "ColumnSelection": ["Id", "Name", "Issue"],
            "ColumnSelectionType": "OnlySelected",
            "ColumnsToUpdate": {
                "ExportMarker": { "Value": 0 }
            },
            "Order": 1,
            "ImportRules": [
                {
                    "Code": "import-users",
                    "Order": 1,
                    "ColumnSelection": ["Issue"],
                    "ColumnSelectionType": "ExcludeSelection",
                    "OperationType": "Insert",
                    "ColumnsToUpdate": {
                        "ExportMarker": { "Value": 1 },
                        "LastUpdatedUTC": { "Placeholder": "UTC_NOW" }
                    },
                    "BatchSize": 500
                }
            ]
        }
    ]
}