Data change message in FairCom DB Notify

JSON format of data change messages

Abstract

JSON message format for the messages sent by FairCom DB Notify to FairCom MQ.

This topic defines the JSON format for the data change messages that FairCom DB Notify sends to FairCom MQ. These messages include the before and after data for each data change event.

Example

The following JSON document is sent to an MQTT topic for each trigger event. FairCom DB Notify sends one message for each operation, which can be an insert, update, or delete event. The message is designed to minimize its size for maximum throughput when transmitting over the network and when parsing.

{
  "operation": "insert | update | delete | error",
  "transactionTimestamp": "2022-03-04T08:40:38.256Z",
  "transactionID": "322551223",
  "serverName": "nameOfFairComServer",
  "databaseName": "someDatabaseName",
  "ownerName": "admin",
  "tableName": "someTableName",
  "fields": [
    {
      "fieldName": "fieldName",
      "beforeValue": "field value before an update or delete",
      "afterValue": "field value after an update or insert",
      "pk":0,
      "changed":false
    }
  ],
  "error": {
    "code": 0,
    "message": "",
    "data": {
    }
  }
}

Things to know

Unless the "fixedOutput" property in a Table Configuration file is true, the JSON notification message omits unnecessary properties to improve network and parsing throughput.
Omitted properties vary with the type of operation it follows.
- The "beforeValue" property is omitted on an insert record operation.
- The "afterValue" property is omitted on a delete record operation.
- The "changed" property is omitted if there are no "beforeValue" and "afterValue" properties.

Properties summary

Table 1. DB Notify message properties summary

Property

Description

Type

Limits (inclusive)

databaseName

specifies the name of the database where the event occurred.

string

1 to 64 characters

error

specifies error information

object

Omitted when there is no error

error

.code

specifies an error number. It is 0 when there is no error.

int32

-2147483648 to 2147483647

error

.data

specifies data about the error. Each error message may provide a different set of data properties. When there is no data, it is an empty object.

object

Zero or more properties

error

.message

specifies a human-readable message that describes the error. When there is no error message, it is an empty string.

string

0 to 511 characters

fields

specifies objects with information about each field

array of objects

Minimum: 0

Maximum: 2,500

fields

.afterValue

specifies the field value after the event occurred.

null,boolean, string

null: NULL

boolean: true | false

string: 0 to 65,000 chars

fields

.beforeValue

specifies the field value before the event occurred.

null,boolean, string

null: NULL

boolean: true | false

string: 0 to 65,000 chars

fields

.changed

informs if the field value has changed with the event.

boolean

true | false

fields

.fieldName

specifies the name of a field in the table.

string

1 to 64 characters

fields

.pk

specifies the field number on the primary key. Zero means it is not a field from the primary key.

int16

0 to 65535

operation

specifies the name of the event that triggered the sending of the notification

enum string

One of the following:

"insert"

"update"

"delete"

"error"

"notificationPluginStartup"

ownerName

specifies the name of the owner of the database table in which the event occurred.

string

1 to 64 characters

serverName

specifies the name of the Faircom server where the event occurred, such as "FAIRCOMS".

string

1 to 64 characters

tableName

specifies the name of the database table in which the event occurred.

string

1 to 64 characters

transactionTimestamp

specifies the timestamp of when the operation occurred

string

YYYY-MM-DDTHH:MM:SS.MMMZ

"fields"."afterValue"

The "afterValue" property contains the field value after the event has occurred, such as after a record was modified or inserted.

Numbers are represented as strings to avoid potential rounding and precision problems.

A null value in JSON represents a NULL value in the database.

Unless the "fixedOutput" property is true, this property is omitted on a delete operation to minimize document size.

"fields"."beforeValue"

The "beforeValue" property contains the field value before the event has occurred, such as before a record was modified or deleted. A null value in JSON represents a NULL value in the database.

Numbers are represented as strings to avoid potential rounding and precision problems.

Unless the "fixedOutput" property is true, this property is omitted on an insert operation to minimize document size.

"fields"."changed"

The "changed" property indicates if the field value was changed with the event.

When the tagChanges feature is enabled, the server adds to each DBNotify message a "changed" property in the appropriate "fields" object.

The "changed" property value is set to true when the value in a field has changed, otherwise it is set to false.

Depending on the "tagChanges" settings, some field objects have a "changed" property and others do not.

"fields"."pk"

The "pk" property contains the field number on the primary key. It is set to an integer value greater than 0. All fields that are not part of the primary key are set to 0.

The first field in a primary key is set to 1, the second is set to 2, and so forth.

Depending on the "primaryKey" settings, some field objects have a "pk" property, and others do not.

`"operation"`

The "operation" property is a string that contains the name of the event that triggered the sending of an operation document.

Things to know:

Events include:
Note
Additional events may be added in the future.
- "insert"
- "update"
- "delete"
- "error"
- "notificationPluginStartup"
If an error occurs, the operation will be set to "error" and the error properties will be set to the appropriate values. The following are examples of errors that may occur:
- "Cannot generate some notifications because unable to open table <tablename> as configured by notification table configuration file: <filename>."
- "Cannot generate some notifications because the source table is invalid in the notification table configuration file: <filename>. This prevents the record callbacks from being set."
- "Cannot generate some notifications because cannot connect to the source database as configured in notification table configuration file: <filename>."
- "Cannot generate some notifications because cannot connect to the FairCom MQ target database because of <error reason>."
- "Cannot generate some notifications because cannot create or update topic table in FairCom MQ because of <error reason>."
- "Not able to open table [<tableName>] - Can't open table"
- "Not able to connect to the local database [<dbname>] - Can't connect to database"
- "Not able to compile filters for table [<tableName>] from database [<dbname>] - CndxCompile failed"
All errors are sent to the FairComServer/NotificationPlugin/Errors/ topic.
The JSON for these errors includes the following properties that identify the context of the error.
```
{
  "operation": "notificationPluginStartup",
  "transactionTimestamp": "2022-03-04T08:40:38.256Z",
  "serverName": "nameOfFairComServer",
  "databaseName": "includedIfApplicableToError",
  "ownerName": "includedIfApplicableToError",
  "tableName": "includedIfApplicableToError",
  "error": {
    "code": 0,
    "message": "",
    "data": {
    }
  }
}
```
Note
Certain errors can only be logged to CTSTATUS. These include errors that prevent FairCom DB Notify from connecting to an MQTT broker. Thus, it is important to look at CTSTATUS regularly when setting up FairCom DB Notify.

`"transactionTimestamp"`

The "transactionTimestamp" property specifies the UTC date time in full-length ISO 8601 format. It is the timestamp when the notification event is generated.

Things to know:

It is typically close to the timestamp of the transaction.
On a busy system, the notification event may be delayed.
Tip
When the exact timing of a transaction is important, it is best to use a timestamp field in the record.
It has the format of YYYY-MM-DDTHH:MM:SS.MMMZ.
- YYYY represents a four digit year — for example, 2023.
- MM (the first set) represents a two-digit month from 01 to 12.
- DD represents a two-digit day from 01 to 3.
- T is always the character "T" that separates the date from the time.
- HH represents a two-digit hour from 00 to 23.
- MM (the second set) represents a two-digit minute from 00 to 59.
- SS represents a two-digit second from 00 to 59.
- MMM represents a three-digit millisecond from 000 to 999.
- Z is always the character "Z" that signifies that the time is Greenwich Zulu time, which is the UTC time zone.

Data change message in FairCom DB Notify

JSON format of data change messages

Example

Things to know

Properties summary

"fields"."afterValue"

"fields"."beforeValue"

"fields"."changed"

"fields"."pk"

"operation"

Note

Note

"transactionTimestamp"

Tip

`"operation"`

`"transactionTimestamp"`