Skip to main content

Configure tables for FairCom DB Notify

Configure tables to track data changes

Abstract

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

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_install_folder>/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 your management of 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.

Example configuration file

This JSON document shows the table configuration structure.

{
  "databaseName": "a database in the FairCom server",
  "tableName": "someTableName",
  "ownerName": "admin",
  "publishMqttMessages": [
    {
      "brokerConnectionName": "a broker in dbnotifyconnections.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":        "tagEachField"
    }
  ]
}

Property summary

Table 1. Table configuration object

Property

Description

Default

Type

Limits (inclusive)

"databaseName"

contains the name of a database

Required - No default value

string

Minimum length: 1
Maximum length: 64

"ownerName"

contains the unique name of a table's owner name

""

string

Minimum length: 1
Maximum length: 64

"publishMqttMessages"

contains the settings needed to send events

Required - No default value

array of objects

"tableName"

contains the name of a table

Required - No default value

string

Minimum length: 1
Maximum length: 64


The "databaseName" property is a required string that specifies the database that contains the tables.

Things to know:
  • A zero-length "databaseName" is invalid.

  • Its limits are from 1 to 64 bytes.

  • If no default database is specified during login or "databaseName" property is set to "", the action returns the error:

    Invalid parameter 'databaseName': is not a string or it is empty.

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

Things to know:
  • The "ownerName" property is optional and has a dynamic default value.

  • If the "ownerName" property is omitted or set to null, the server uses the value of the "defaultOwnerName" property supplied during the "createSession" action.

  • If the "defaultOwnerName" property is not defined, the server uses the "admin" as the owner name.

  • The owner of an object has administrative rights over that object.

  • The "ownerName" property is a namespace for an object. You can think of it as a container of objects.

    The "ownerName" allows users to use any name for the objects they create — for example, a QA engineer may copy tables into their owner space to run a set of tests.

    It is common for a user to create their own copies of objects from other accounts for testing, troubleshooting, and fixing data. The copied objects can retain the same name because the "ownerName" distinguishes between them.

  • The fully qualified name of an object is the "databaseName", "ownerName", and the object's name, such as "tableName" meaning a FairCom server may contain many tables with the name "mytable" as long as each one is in a different database or in a different owner space.

    For example, an organization often creates different databases for different phases of the development lifecycle, such as dev, test, stage, ua, and prod. Each of these databases contains the same set of objects with the same names. Applications leave the "databaseName" out of their JSON actions and use the "defaultDatabaseName" property to specify the target database.

  • Queries and scripts are often written without specifying "databaseName" and/or "ownerName", allowing queries and scripts to work properly when run in different databases or in different schemas.

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":        "tagEachField"

    }
  ]

Property summary

Table 2. "publishMqttMessage" property summaries

Property

Description

Default

Type

Limits (inclusive)

"brokerConnectionName"

defines a user-defined unique name for a broker integration

Required - No default value

string

Minimum length: 1
Maximum length: 64

"fixedOutput"

forces the JSON in the notification to contain all JSON fields when true

false

Boolean

true
false

"includedFields"

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

[ ]

array of strings

"includeMetadata"

contains information to identify input sources

[ ]

array of objects

"Qos"

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

2

int8

0
1
2

"recordFilter"

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

""

string

Minimum length: 0
Maximum length: 65,000

"revealAfterValueOnFilteredDelete"

displays the "afterValue" property in the notification message when true

false

Boolean

true
false

"revealBeforeValueOnFilteredInsert"

displays the "beforeValue" property in the notification message when true

false

Boolean

true
false

"topic"

contains a unique user-defined name for the topic

Required - No default value

string

Minimum length: 1
Maximum length: 65,500

"triggers"

contains a list of events on a table that trigger notifications.

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

array of enum strings

One or more of:

"insert"
"update"
"delete"

"includePrimaryKey"

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

"forPrimaryKeyFields"

string

"forEachField"
"never"
"forPrimaryKeyFields"

"tagChanges"

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

"tagEachField"

string

"tagEachField"
"neverTag"
"tagPrimaryKeyFields"


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.

Things to know:
  • 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.

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.

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.

Things to know:
  • 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.

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.

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.

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.

Things to know:
  • 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.

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.

Things to know:
  • 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".

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.

Things to know:
  • 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".

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.

Things to know:
  • Table primaryKey settings override global default primaryKey settings.

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":

  • "tagEachField" adds the "change" property to each field object in each change message.

  • "neverTag" does not add the "change" property to any field object.

  • "tagPrimaryKeyFields" adds the "change" property only to primary key field objects in each change message.

Things to know:
  • Table tagChanges settings override global default tagChanges settings.

The "tableName" property is an optional string that contains the name of an integration table or MQTT topic that holds the topic's messages. It defaults to an empty string.

Things to know:
  • You can use a topic name for an MQTT table because FairCom automatically generates a table name for each topic.

  • A "tableName" cannot begin with a number.

  • It refers to the name of the integration table that stores a topic’s messages and is used to rename that table or assign a new topic to an existing integration table.

  • MQTT automatically creates an integration table for each new topic it receives. Thus, when a message is sent to a topic, FairCom Edge automatically creates a table to hold it.

  • As you refine your integration processes, you may want to rename an integration table to better label the data it holds. You can use the "tableName" property of the "configureTopic" action to rename an integration table.

  • The "alterIntegrationTable" action can also be used to rename an integration table, but it is less convenient because you must know the original name of the integration table.