Configure tables for FairCom DB Notify

Configure tables to track data changes

Create a JSON configuration file for each table you want to send data change messages to FairCom MQ.

Place the file in the folder <faircom>/config/dbnotify.
Configure any combination of insert, update, or delete change events to send messages to the same or different topics.
Optionally, add a record filter to limit which records cause change messages to be sent.
Configure which fields are included in change messages.
The name of the configuration file does not matter but must have the .json extension. To aid in managing these files, you may want to use a descriptive name that follows a pattern similar to database-table.json.
Optionally, create multiple configuration files for each table.

Complete table file configuration

{
  "databaseName": "ctreeSQL",
  "tableName": "custmast",
  "ownerName": "admin",
  "publishMqttMessages": [
    {
      "topic": "topic1",
      "brokerConnectionName": "brokerCtree",
      "triggers": [
        "delete"
      ],
      "recordFilter": "!stricmp(cm_custstat,\"CA\")",
      "includedFields": [
        "cm_custnumb",
        "cm_custzipc",
        "cm_custcity"
      ],
      "recordFormat": "fields",
      "includePrimaryKey": "never"
    },
    {
      "topic": "topic2",
      "brokerConnectionName": "brokerCtree",
      "triggers": [
        "insert",
        "update"
      ],
      "recordFilter": "atoi(cm_custnumb) >= 1000",
      "recordFormat": "buffer",
      "includePrimaryKey": "never",
      "tagChanges": "forEachField"
    }
  ]
}

Simple table file configuration with "databaseName"

{
  "databaseName": "ctreeSQL",
  "tableName": "custmast",
  "ownerName": "admin",
  "publishMqttMessages": [
    {
      "topic": "topic1",
      "brokerConnectionName": "brokerCtree",
      "triggers": [
        "delete"
      ]
    },
    {
      "topic": "topic2",
      "brokerConnectionName": "brokerCtree",
      "triggers": [
        "insert",
        "update"
      ]
    }
  ]
}

Simple table file configuration with "dataFilePath"

{
  "dataFilePath": "c:\\mydata\\custmast.dat",
  "publishMqttMessages": [
    {
      "topic": "topic1",
      "brokerConnectionName": "brokerCtree",
      "triggers": [
        "delete"
      ],
      "recordFilter": "!stricmp(cm_custstat,\"CA\")",
      "includePrimaryKey": "never"
    },
    {
      "topic": "topic1",
      "brokerConnectionName": "brokerCtree",
      "triggers": [
        "insert",
        "update"
      ],
      "QoS": 0,
      "recordFilter": "atoi(cm_custnumb) >= 1000"
    }
  ]
}

Properties summary

Table 1. Table configuration object properties summary

Property

Description

Default

Type

Limits (inclusive)

databaseName

specifies the name of the database where the event occurred

Required - No default value if the dataFilePath is not specified

string

1 to 64 bytes

dataFilePath

specifies the name of the table with a path. It can be a full path or a relative path from the local directory

Required - No default value if tableName is not specified

string

ownerName

specifies the unique name of the table's owner

""

string

1 to 64 bytes

publishMqttMessages

specifies the settings needed to send events

Required - No default value

array of objects

publishMqttMessages

.brokerConnectionName

specifies a user-defined unique name for a broker integration

Required - No default value

string

1 to 64 bytes

publishMqttMessages

.fixedOutput

specifies to include all the JSON fields in the notification when true

false

boolean

true

false

publishMqttMessages

.includedFields

specifies a list of fields to include in the notification or returns all fields when empty

[ ]

array of strings

publishMqttMessages

.includeMetadata

specifies information to identify input sources

[ ]

array of objects

publishMqttMessages

.includePrimaryKey

specifies when to show in responses, the primary key designation of fields.

"forPrimaryKeyFields"

string

"forEachField"

"never"

"forPrimaryKeyFields"

publishMqttMessages

.Qos

specifies the MQTT quality of service for publishing messages that will be sent to the MQTT broker

2

int8

0

1

2

publishMqttMessages

.recordFilter

specifies a FairCom expression that evaluates a record's field values to determine if and how it should trigger an event

string

0 to 65,000 bytes

publishMqttMessages

.revealAfterValueOnFilteredDelete

specifies to include the "afterValue" property in the notification message when true

false

boolean

true

false

publishMqttMessages

.revealBeforeValueOnFilteredInsert

specifies to include the "beforeValue" property in the notification message when true

false

boolean

true

false

publishMqttMessages

.tagChanges

specifies when to show in responses, the designation that fields have changed.

"forEachField"

string

"forEachField"

"never"

"forPrimaryKeyFields"

publishMqttMessages

.topic

contains a unique user-defined name for the topic

Required - No default value

string

1 to 65,5000 bytes

publishMqttMessages

.triggers

specifies a list of events on a table that trigger notifications

[ "insert", "update", "delete" ]

array of enum strings

One or more of:

"insert"

"update"

"delete"

publishMqttMessages

.recordFormat

specifies the format of the record changes that are returned

"fields"

string

"fields"

"buffer"

tableName

specifies the name of the table in the database where the event occurred

Required - No default value if the dataFilePath is not specified

string

1 to 64 bytes

Configure data change capture for a table for FairCom DB Notify.

Data change capture configuration file for FairCom DB NotifyFairCom DB NotifyFairCom MQFairCom RTGFairCom DB

`"databaseName"`

The "databaseName" property is a required string (if the "dataFilePath" is not defined) that specifies the database where the event occurred.

Essential information

A zero-length "databaseName" is invalid.
Its limits are from 1 to 64 bytes.

`"dataFilePath"`

The "dataFilePath" property identifies a data file by path (relative to the server or absolute).

This property replaces the "databaseName", "ownerName", and "tableName" properties to uniquely identify a data file by path.

If both "tableName" and "dataFilePath" are defined, an error is generated.

Examples of "dataFilePath" values

"dataFilePath": "/faircom/server/data/ctreeSQL.dbs/admin_custmast.dat"
"dataFilePath": "D:\\mydata\\custmast.dat"
"dataFilePath": "./ctreeSQL.dbs/admin_custmast.dat"

`"ownerName"`

The "ownerName" property is an optional string from 1 to 64 bytes that specifies the account that owns an object.

`"publishMqttMessage"`

Each "publishMqttMessage" object contains the settings needed to send events to one topic on one MQTT broker. Create a different "publishMqttMessage" object for each topic that you want to publish on each broker.

Example

"publishMqttMessages": 
  [
    {
      "brokerConnectionName": "a broker in dbnotifyonnections.json",
      "topic": "an MQTT Topic",
      "QoS": 1,
      
      "triggers": [  "insert", "update", "delete" ],
      
      "recordFilter": "optional FairCom Filter Expression to filter records",
      
      "includedFields": [ "field1" ],

      "includeMetadata": [
        {
          "propertyPath": "customerUUID",
          "propertyValue": "22f0bf86-f7d5-4899-a93d-e6cbcbbcf34a"
        }
      ],
      "fixedOutput": false,

      "revealBeforeValueOnFilteredInsert": true,
      "revealAfterValueOnFilteredDelete": true,
      "includePrimaryKey": "forEachField",
      "tagChanges": "forEachField"

    }
  ]

`"fixedOutput"`

The "fixedOutput" property is an option Boolean where, when true, the JSON output messages generated by FairCom DB Notify contain every JSON property. It defaults to false.

Essential information

It is useful to contain every JSON property if the consuming system needs to receive the same JSON structure in each message, but it makes messages larger and typically slower to transmit over the network and slower to process.
When "fixedOutput" is true, all properties are always present and if a property does not have a value, it is set to null.
When "fixedOutput" is false, these properties are omitted when they are not needed:
- "error", unless there is an error.
- "beforeValue", for an insert operation.
- "afterValue", for a delete operation.

`"includedFields"`

The "includedFields" property is an optional array of strings where each string is the name of a field in a table. Only the specified fields are included in the results. It defaults to an empty array which includes all fields.

Be sure to include all the primary key fields in the list; otherwise, a subscriber cannot properly identify the records it receives.
The "includedFields" property is useful to limit which fields are included in the published message.
Insert and delete events are always reported when the "triggers" property monitors for them, but these messages will not be meaningful if the primary key fields of the record are not included in the "includedFields" property.

`"includeMetadata"`

The "includeMetadata" property adds JSON properties to a target JSON document. It is an array of objects that each contain the "propertyPath" and "propertyValue" properties. The "propertyPath" property specifies the path of the JSON property in the target JSON document, and the "propertyValue" property specifies the JSON value assigned to that property path.

"includeMetadata" example

"includeMetadata": 
[
  {
    "propertyPath": "device.name",
    "propertyValue": "any JSON value: string, number, true, false, null, {} [], [{}]"
  }
]

The string assigned to the "propertyPath" property is the path where the target property is located in the target JSON document. A JSON path containing a single property name, such as "device", creates a property with that name in the target JSON document.

When a JSON path contains multiple property names, separate each property name with a period, such as "device.name". Each property name in a path represents a nested JSON object. When a JSON path specifies the name of a property that does not exist in the target JSON document, the server creates the property.

You can use a JSON path to assign a value to a specific array element. Use the square brackets to specify the array position, such as "device.sensors[0]".

Tip

Assigning values to specific array positions is useful when an array exists in the output and you want to include additional elements in the array.

Use "includeMetadata" to add properties

You can add properties to any location in a JSON document. You can assign any valid JSON value to a property, including a string, number, true, false, null, object, or array.

Source JSON

You can start with any JSON document, such as an empty object.

{}

Configuration

You can use the "includeMetadata" property to add a "device" property to the target JSON document. Its value can be as simple or complex as you want.

{
  "includeMetadata": [
    {
      "propertyPath": "device",
      "propertyValue": {
        "name": "stamper1",
        "sensors": [ "humidity", "temperature", "pressure" ]
      }
    }
  ]
}

Resulting JSON

The server updates the JSON document to include a "device" property containing the JSON object.

{
  "device": {
    "name": "stamper1",
    "sensors": [ "humidity", "temperature", "pressure" ]
  }
}

Use "includeMetadata" to add nested properties

You may want to add properties to specific locations in a JSON document.

For example, you can use JSON path notation to add nested properties to a JSON document, such as adding a "name" property to the "device" object.

Source JSON

You can start with any JSON document.

{
  "device": 
  {
    "active": true
  }
}

Configuration

{
  "includeMetadata": [
    {
      "propertyPath": "device.name",
      "propertyValue": "stamper1"
    }
  ]
}

Resulting JSON

The server adds the "name" property with a value of "stamper1" to the "device" object.

{
  "device": 
  {
    "active": true,
    "name": "stamper1"
  }
}

The server creates properties as needed. For example, if the "device" property does not exist in the source JSON document, the server creates it and adds the "name" property to it.

Use "includeMetadata" to add array values

You may want to enrich a source JSON document by adding properties to specific elements to arrays.

Source JSON

You can start with any JSON document.

{
  "device": 
  {
    "name": "stamper1",
    "sensors": [ "humidity"]
  }
}

Configuration

For example, you can use JSON path notation to assign a "temperature" value to the second element of the "sensors" property and assign a "pressure" value to the third element. The server creates properties and array elements as needed.

Note

The first element in an array is the position [0].

{
  "includeMetadata": [
    {
      "propertyPath": "device.sensors[1]",
      "propertyValue": "temperature"
    },
    {
      "propertyPath": "device.sensors[2]",
      "propertyValue": "pressure"
    }
  ]
}

Resulting JSON

The server adds the "temperature" and "pressure" values to the "sensors" array.

{
  "device": 
  {
    "name": "stamper1",
    "sensors": [ "humidity", "temperature", "pressure" ]
  }
}

The server creates properties as needed. For example, if the "device" property does not exist in the source JSON document, the server creates it and adds the "sensors" property to it.

When a specified element already exists in the array, the server replaces it with the specified value. When a specified element does not exist in the array, the server appends the value to the end of the array.

`"includePrimaryKey"`

The "includePrimaryKey" property is a string that designates when to include a field's primary key status in messages. When the "includePrimaryKey" feature is enabled, the server adds to each DB Notify message, a "pk" property to the appropriate "fields" object. The "pk" property is set to an integer value of 0 if the field is not part of a primary key, or a value greater than 0 if the field is part of a primary key. A value of 1 indicates this is the first field in a primary key, 2 is the second and so forth. Depending on the "primaryKey" settings, some field objects may have a "pk" property and others may not.

The following are valid values for "includePrimaryKey":

"forEachField" includes the "pk" property on each field.
"never" does not include the "pk" property on any field.
"forPrimaryKeyFields" includes the "pk" property only on primary key fields.

Essential information

The includePrimaryKey property overrides the global defaultIncludePrimaryKey property.

`"QoS"`

The "QoS" property is an optional integer containing the MQTT quality of service for publishing the message that will be sent to the MQTT broker.

Essential information

The higher the quality of service, the slower the message delivery because MQTT provides extra packet handshakes to guarantee the quality of service.
A value of 0 does not guarantee the delivery of a message.
A value of 1 provides the best balance of performance and guaranteed delivery.
A value of 2 guarantees the delivery of the message once and only once.

`"recordFilter"`

The "recordFilter" property is an optional string that contains a FairCom expression that evaluates a record’s field values to determine if and how it should trigger an event. It is similar to a WHERE clause in a SQL SELECT statement, but uses a C-style syntax.

Essential information

It is similar to a WHERE clause in a SQL SELECT statement, but uses a C-style syntax.
A message is published only if the record’s field values satisfy the conditions of the filter expression. In other words, a record filter is like a "view" on a table that represents a subset of its records. Records coming into and leaving the view trigger which messages are sent.
- An insert record operation:
  If the record’s new values match the filter, an insert operation message is sent. Otherwise, no message is sent.
- A delete record operation:
  If the record’s old values match the filter, a delete operation message is sent. Otherwise, no message is sent.
- An update record operation:
  The filter is applied to both the old and new versions of the record.
  - An update operation message is sent if both the old and new versions of the record match the filter.
  - No message is sent if neither the old or new versions of the record match the filter.
  - A delete operation message is sent if the old version of the record matches the filter but not the new.
  - An insert operation message is sent if the new version of the record matches the filter but not the old.

`"revealAfterValueOnFilteredDelete"`

The "revealAfterValueOnFilteredDelete" property is an optional Boolean that, when true, the "afterValue" contains the field's value after it was updated and triggered the filtered delete operation. It defaults to false.

Essential information

When false it omits the "afterValue" in the notification message or sets it to null when "fixedOutput" is true.
For more information on how a record update can trigger a delete notification, see "recordFilter".

`"revealBeforeValueOnFilteredInsert"`

The "revealBeforeValueOnFilteredInsert" property is an optional Boolean that, when true, the "beforeValue" contains the field's value before it was updated and triggered the filtered insert option. It defaults to false.

Essential information

When false it omits the "beforeValue" in the notification message or sets it to null when "fixedOutput" is true. This is the secure option because it does not leak information to the client that the client does not need to see.
For more information on how a record update can trigger an insert notification, see "recordFilter".

`"tagChanges"`

The "tagChanges" property is a string that designates when to include in messages, the change status of fields. When the "tagChanges" feature is enabled, the server adds to each DB Notify message, a "changed" property in the appropriate "fields" object. The "changed" property value is set to true when the value in the field has changed; otherwise, it is set to false. Depending on the "tagChanges" settings, some field objects may have a "changed" property and others may not.

The following are valid values for "tagChanges":

"forEachField" adds the "change" property to each field object in each change message.
"never" does not add the "change" property to any field object.
"forPrimaryKeyFields" adds the "change" property only to primary key field objects in each change message.

Essential information

The tagChanges property overrides the global defaultTagChanges property.

`"triggers"`

The "triggers" property is an optional array of enum strings. It defaults to [ "insert", "update", "delete" ]. This means, one, two, or all three triggers can be used to trigger the publishing of MQTT messages. Also, each configuration file may define at most three triggers.

Tip

Multiple configuration files can be used with the same table to define different sets of triggers to send different messages to different topics with different record filters.

Each string must be one of the following values: "insert", "update", "delete". Any combination of these strings is allowed.
Each configuration file may define at most three triggers.

`"recordFormat"`

The "recordFormat" property is used to specify the format of the message that reports record changes.

The value “fields” causes DB Notify to return record changes using field data, which is its default behavior.

The value "buffer" causes DB Notify to return record changes as a binary record buffer. It causes the "beforeValue" and "afterValue" properties to contain a base64-encoded binary value string of the record before and after the change.

{
  "databaseName":"ctreeSQL",
  "tableName":"custmast",
  "ownerName":"admin",
  "publishMqttMessages":[
    {
       "topic":"topic1",
       "brokerConnectionName":"brokerCtree",
       "recordFilter":"!stricmp(cm_custstat,\"CA\")",
       "triggers":["delete"],
       "recordFormat":"buffer"
    }
  ]
}

`"tableName"`

The "tableName" property contains the name of the table in the database where the event occurred. It is a non-zero-length string.

A table name may contain up to 64 ASCII characters and must not start with a number.

A table in DBnotify is defined by "databaseName", "ownerName" and "tableName" or by "dataFilePath".