`"createIntegrationTable"` (jsonAction)

JSON hub `"createIntegrationTable` action creates a new integration table in FairCom Edge

The "createIntegrationTable" action creates an integration table.

Essential information

The "createIntegrationTable" action creates an integration table.
This means that it will have the automatically-added integration fields discussed in the Integration fields section, in addition to the fields you explicitly add yourself using the fields property.
If the table already exists, it returns or logs an error.
Integration tables are created with input and read privileges only. This enables them for audit trail purposes. Allowing updates and deletions would destroy audit integrity.

Request examples

Minimal

{
  "api":        "hub",
  "action":     "createIntegrationTable",
  "params":     {
    "tableName": "test1"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Create an integration table with one additional field

{
  "api":        "hub",
  "apiVersion": "1.0",
  "requestId":  "2",
  "action":     "createIntegrationTable",
  "params":     {
    "databaseName": "faircom",
    "tableName":    "test2",
    "fields": [
      {
        "name":     "t10",
        "type":     "VARCHAR",
        "length":   128,
        "nullable": true
      }
    ],
    "metadata":        { },
    "retentionPeriod": 4,
    "retentionUnit":   "week"
  },
  "apiVersion": "1.0",
  "debug": "max",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Response examples

Success

{
    "result": {},
    "requestId": "1",
    "errorCode": 0,
    "errorMessage": ""
}

Table already exists failure

{
    "result": {},
    "requestId": "2",
    "debugInfo": {
        "request": {
            "api": "hub",
            "action": "createIntegrationTable",
            "params": {
                "databaseName": "faircom",
                "tableName": "test1",
                "fields": [
                    {
                        "name": "t10",
                        "type": "VARCHAR",
                        "length": 128,
                        "nullable": true
                    }
                ],
                "metadata": {},
                "retentionPeriod": 4,
                "retentionUnit": "week"
            },
            "apiVersion": "1.0",
            "requestId": "2",
            "debug": "max",
            "authToken": "replaceWithAuthTokenFromCreateSession"
        }
    },
    "errorCode": 12020,
    "errorMessage": "Not able to create integration table [test1]. Integration table name already exists.",
    "authToken": "replaceWithAuthTokenFromCreateSession"
}

Use the createIntegrationTable JSON API action to create a new integration table in FairCom Edge

JSON hub APIAPI actionsjsonActionintegration tables actionscreate integration tablecreateIntegrationTable

`"params"`

The "params" property is an object that contains an action's parameters. Each action defines its own required and optional properties. See System limits for a comprehensive look at property requirements and limitations.

Table 1. "params" property summaries

Property

Description

Default

Type

Limits (inclusive)

databaseName

(optional) specifies the name of a database

Defaults to the "defaultDatabaseName" value that is set during "createSession". If no default is set during "createSession", then "faircom" is used.

string

1 to 64 bytes

fields

(optional) contains objects that specify the settings of a field in a table

[]

array

fields

.autoValue

(optional) controls when and how the server automatically sets the field value

"none"

string

"changeid"

"incrementOnInsert"

"none"

"timestampOnInsert"

"timestampOnUpdate"

"timestampOnUpdateAndInsert

fields

.defaultValue

(optional) specifies the default value of a field

""

string

0 to 65,500 bytes

fields

.length

(optional) specifies the length of a field's value in a record

null

integer

1 to 65500

fields

.name

specifies a new name of the field

Required - No default value

string

0 to 64 bytes

fields

.nullable

(optional) allows a field to contain a NULL value when true

true

Boolean

true

false

fields

.primaryKey

(optional) adds a field to the specified ordinal position of the table's primary key when > 0

null

integer

0 to 32

fields

.scale

(optional) specifies the number of places to the right of the decimal point

null

integer

0 to 32

fields

.type

specifies the type of field

Required - No default value

string

"bit"

"tinyint"

"smallint"

"integer"

"bigint"

"real"

"double"

"number"

"money"

"date"

"time"

"timestamp"

"char"

"varchar"

"lvarchar"

"binary"

"varbinary"

"lvarbinary"

"json"

metadata

(optional) exists primarily for the user interface to find integration information

{}

object

May contain any number and type of user-defined properties

retentionPolicy

(optional) controls how messages are persisted

"autopurge"

string

"autoPurge"

"neverPurge"

retentionPeriod

(optional) specifies the number of retention units, which controls how long data is retained – see "retentionUnit"

4

integer

1 to 100

retentionUnit

(optional) purges expired messages each time this unit cycles – see "retentionPeriod"

"week"

string

"minute"

"hour"

"day"

"week"

"month"

"year"

"forever"

sourcePayloadFormat

(optional) specifies the variant type format of the "source_payload" field

"binary"

string

"json"

"xml"

"binary"

"jpeg"

"siemensUdt"

"utf8"

tableName

specifies the name of a table

Required - No default value

string

1 to 64 bytes

transformName

(optional) specifies the name of a transform process you have created. See the "createTransform" and "alterTransform" actions

""

string

1 to 64 bytes

`"databaseName"`

The "databaseName" property is an optional string that specifies the database that contains the tables. It defaults to the database name supplied at login.

Note

In the API Explorer, "defaultDatabaseName" is set to "ctreeSQL" in the "createSession" action that happens at login.

A zero-length "databaseName" is invalid.
Its limits are from 0 to 64 bytes.
If the "databaseName" property is omitted or set to null, the server will use the default database name specified at login.
If no default database is specified during "createSession", "defaultDatabaseName" will be set to the "defaultDatabaseName" value that is specified in the services.json file.

`"fields"`

The "fields" property is a required array of field-type objects. There is one object for each top-level field in the array. Each object identifies the actual data type of the field as stored in the database.

A field-type object is used when creating a table. It contains one object for each field definition returned in the data.

Full request example

"fields": 
[
    {
      "name": "name",
      "type": "varchar",
      "length": 50,
      "scale": null,
      "defaultValue": null,
      "nullable": false,
      "primaryKey":1
   }
]

Full response example

"fields":
[
  {
      "name": "id",
      "type": "bigint",
      "length": null,
      "scale": null,
      "autoValue": "none",
      "defaultValue": null,
      "nullable": false,
      "primaryKey": 1
  }
]

`"metadata"`

The "metadata" property is an optional JSON object. It exists primarily for the user interface to find integration information. By default, it is an empty JSON object.

It contains a flexible set of properties.
It typically contains tags and description properties.

Example

{
  "description": "",
  "tags": [""],
  "yourOwnProperties": "usage, purpose, notes, location, etc.",
}

`"retentionPolicy"`

The "retentionPolicy" property controls how messages are persisted. This property is optional.

If not specified, the default found in the services.json file is used. Initially, it is "autoPurge".

retentionPolicy values:

"autoPurge"
This is the default. It is automatically applied when a new topic is created. It is preferred because it allows FairCom's servers to automatically remove messages that are older than the retention time. This helps ensure message data does not consume all storage space. It also minimizes storage costs and speeds up data access. The server partitions a table into multiple files so it can efficiently delete expired files.
"neverPurge"
This stores messages on disk and never removes them. This is useful when you need the entire history of the message stream. If message velocity is high, this can consume all storage space and cause an outage. The server creates a non-partitioned table, which is slightly faster than a partitioned table because it stores all records in one file.

`"retentionPeriod"`

The "retentionPeriod" property specifies how many units of data to retain. It must be an integer value from 1 to 100. It refers to the unit of time specified by the "retentionUnit" property — for example, if "retentionPeriod" is 14 and "retentionUnit" is "day", then message data is retained for 14 days. This property is optional.

Periodically, the system increments the age of each table partition.
- "Minute" units are incremented at the zero second of each minute, whether the table partition was created 1 second before or 59 seconds before.
- "Day" units are incremented at midnight GMT, not midnight of the local UTC time zone. A table partition becomes one day old at midnight GMT, whether it was created one second before or 23 hours and 59 seconds before.
- "Week" units are incremented at midnight GMT on the last day of each week, even if the table partition was created one second before. The last day of the week is Saturday GMT.
- "Month" units are incremented at midnight GMT on the last day of each month.
- "Year" units are incremented at midnight GMT on Dec 31.
"retentionPeriod" implicitly calculates an upper bound on how many partitions are able to accumulate on your hard drive. However, partitions are not deleted just because this calculated number of partitions is reached. The system also does not restrict the deletion of all partitions.
If the FairCom database is shut down for a month, when the database is started up again, all partitions that are retained for less than one month are immediately deleted.
If someone purposely or accidentally sets the date to a future date, all partitions immediately become older, and auto-deletion ensues accordingly.

When partitions are auto-purged, some data are maintained "forever" in a global index. Auto-purge does not prevent these files from growing without bounds and filling up your hard drive.

If not specified, the default found in the services.json file is used. Initially, it is 4 (weeks).

Automatically purging data is important to ensure that retained data does not consume all storage and shut down the computer. The default value of 4 weeks allows FairCom's servers to store 1 TB of messages when 200 topics send one 2K message per second.

Note

If the value is not an integer from 1 to 100, FairCom's servers set it to the default value.
Smaller numbers improve SQL performance.

Each time the "retentionPeriod" cycles, FairCom's servers automatically and efficiently delete expired data.
The "retentionUnit" and "retentionPeriod" properties are used only when the "retentionPolicy" is set to "autoPurge".
FairCom's servers only use the "retentionPeriod" property when the "retentionPolicy" is "autoPurge".
The "retentionPeriod" can be changed to retain fewer or more messages. Changing it does not necessarily destroy existing data, but data may expire more quickly or be retained longer.
The "retentionPeriod" and "retentionUnit" properties control data granularity as well as the retention time. In other words, "retentionPeriod" defines how many sets of data are stored, and "retentionUnit" defines how often data is purged.
For example, if "rententionPeriod" is set to 14 , the server stores 14 sets of data. At the beginning of the 15th cycle, the server automatically purges the oldest set of data. If "retentionUnit" is set to day, then data will be purged daily. If set to "week", then data will be purged weekly.
The current calendar date affects purging.
FairCom's servers automatically purge all retained data that has expired. This is noticeable when FairCom's servers come online after having been offline for a long time. When a server comes back online, it automatically purges all expired data.
For example, if a FairCom server is offline for four weeks when it comes online, it will completely purge all retained data that has a retention time of less than 4 weeks.

`"retentionUnit"`

Each time this unit cycles, FairCom purges expired messages. For example, if you want a week's worth of messages to be purged once a week, set "retentionUnit" to "week" . This property is optional.

If not specified, the default found in the services.json file is used. Initially, it is "week".

This property is used in concert with "retentionPeriod" to determine retention time.
"retentionUnit" values:
- "minute"
- "hour"
- "day"
- "week"
- "month"
- "year"
- "forever"

Note

For best performance, set the "retentionUnit" to a value that keeps "retentionPeriod" between 5 and 30.
When you set "retentionUnit" property to "forever" the server will not purge messages. This setting is the same as setting "retentionPolicy" to "neverPurge".
The "retentionUnit" and "retentionPeriod" properties are used only when the "retentionPolicy" is set to "autoPurge".

`"sourcePayloadFormat"`

The "sourcePayloadFormat" property is an optional string that specifies the expected data type of the MQTT payload. When omitted or set to null, it defaults to "binary". When the server receives an MQTT message, it always takes the binary value from the MQTT payload and stores it in the source_payload field. The source_payload field is a variant, which means it stores the type of data it contains. If there are no errors in validating the MQTT payload to match the type specified in "sourcePayloadFormat", the server assigns the value of "sourcePayloadFormat" as the variant field's type.

Depending on the values of the "sourcePayloadFormat" and "validateMqttPayload" properties, the server may validate the MQTT payload to ensure it matches the type. The default value for the "validateMqttPayload" property is true. The server may also convert the MQTT payload into another form before storing it. If the MQTT payload does not match the type or cannot be converted, the server stores the payload in the source_payload field, sets its variant type to "binary", and sets the error field to 1 to indicate there is an error. It also adds an error entry to the JSON array in the log field.

FairCom's MQTT server does the following when a topic receives an MQTT message and "sourcePayloadFormat" is set to one of the following values:

"sourcePayloadFormat" Value	Description
`"binary"`	The server stores the MQTT payload in the `source_payload` field with no validation or conversion and assigns the `"binary"` type to the field. It ignores the value of `"validateMqttPayload"`.
`"json"`	The server stores the MQTT payload in the `source_payload` field. If `"validateMqttPayload"` is `true`, it validates the MQTT payload as JSON. If successful, it assigns the `"json"` type to the `source_payload` field. If unsuccessful, the server assigns a `"binary"` type to the `source_payload` field and sets the error field to `1` to indicate there is an error. It also adds an error entry to the JSON array in the log field. If `"validateMqttPayload"` is `false`, it does not validate the MQTT payload and assigns the `"json"` type to the `source_payload` field. The publisher must send MQTT messages containing valid JSON, or database queries may return errors.
`"utf8"`	The server stores the MQTT payload in the `source_payload` field. If `"validateMqttPayload"` is `true`, it validates the MQTT payload as a UTF-8 string. If successful, it assigns the `"utf8"` type to the `source_payload` field. If unsuccessful, the server assigns a `"binary"` type to the field and sets the error field to `1` to indicate there is an error. It also adds an error entry to the JSON array in the log field. If `"validateMqttPayload"` is `false`, it does not validate the MQTT payload and assigns the `"utf8"` type to the `source_payload` field. The publisher must send MQTT messages containing valid UTF-8, or database queries may return errors.
`"variantObject"`	The server validates the payload as a variant object. If successful, the server extracts the value from the variant object’s `"value"` property and assigns it to the value of the `source_payload` field. It also assigns the variant object’s type to the type of the `source_payload` field. If unsuccessful, the server writes the MQTT payload to the `source_payload` field, assigns a "binary" type to the field, and sets the error field to 1, to indicate that it also adds an error entry to the JSON array in the log field. The server ignores the value of `"validateMqttPayload"`. The server always validates `"variantObject"`.
`"siemensUdt"`	The server stores the MQTT payload in the `source_payload` field without validation or conversion and assigns the `"binary"` type to the field. It ignores the value of `"validateMqttPayload"`.
`"jpeg"`	The server stores the MQTT payload in the `source_payload` field without validation or conversion and assigns the `"binary"` type to the field. It ignores the value of `"validateMqttPayload"`.
`"xml"`	The server stores the MQTT payload in the `source_payload` field without validation or conversion and assigns the `"binary"` type to the field. It ignores the value of `"validateMqttPayload"`.

This property is a hint to the server about the format and type of the MQTT message payload.
This property does not cause server to validate the MQTT payload to see if it matches the type you set. The server stores the payload as is without validation. For example, setting the type to "json", it does not stop the server from receiving and storing a non-JSON value or invalid JSON document in the source payload.
The FairCom Edge Explorer application may use the value of this property to determine the default way to display a topic's payload.
The transform engine may use the value of this property to help it transform the source payload.

`"tableName"`

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

See table name in System limits for table naming requirements and limitations.

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

`"autoValue"`

This property controls when and how the server automatically sets the field value.

Specify only one of these values per field.

"none" indicates the server does not automatically set the field's value.
"incrementOnInsert" indicates the server automatically increments a field’s value each time the server inserts a new record. It applies to fields that are of the type of decimal or one of the integer types, such as "bigint". Only one field per table can have this attribute. The server returns an error when assigning this attribute to multiple fields. The JSON DB API automatically creates the "id" field as an "incrementOnInsert" field. If you apply this attribute to another field, it becomes the only automatically incremented field in the table. If you want that field to be the primary key, assign "primaryKey": 1 to it.
"timestampOnInsert" indicates the server automatically sets a field’s value to the current date and time of an insert. It applies only to fields with a type of "timestamp".
"timestampOnUpdate" indicates the server automatically sets a field’s value to the current date and time of an update. It applies only to timestamp fields.
"timestampOnUpdateAndInsert" indicates the server automatically sets a field’s value to the current date and time of an insert and an update. It applies only to fields with a type of "timestamp".
"changeId" indicates the server uses the field for optimistic locking. The server automatically sets the field's value to the internal transaction number used during the last update of the record. This value changes each time the server updates the record. A table may only have one change tracking field. The field type must be "bigint".
The JSON DB API automatically creates a "changeid" field with change-tracking functionality.
Change tracking is optional in the CTDB and ISAM APIs. The application must create a 64-bit integer field and assign change-tracking functionality to it.

`"transformName"`

The "transformName" property is an optional string that contains the unique name of a transform process, which consists of one or more transform steps.

The value of "transformName" must be the name of a transform process you created. It cannot be the name of a FairCom-provided transform method.

You must create a transform before you can assign it to an integration table. See the "createTransform" and "alterTransform" actions.

A transform is a process that works like a pipeline where the output of one transformation can become the input for another transformation.
The following actions use the "transformName" property to assign a transform to an integration table:
- "configureTopic"
- "createInput"
- "alterInput"
- "createIntegrationTable"
- "alterIntegrationTable"

"createIntegrationTable" (jsonAction)

JSON hub "createIntegrationTable action creates a new integration table in FairCom Edge

Essential information

Request examples

Minimal

Create an integration table with one additional field

Response examples

Success

Table already exists failure

Use the createIntegrationTable JSON API action to create a new integration table in FairCom Edge

"params"

"databaseName"

Note

"fields"

Full request example

Full response example

"metadata"

Example

"retentionPolicy"

"retentionPeriod"

Note

"retentionUnit"

Note

"sourcePayloadFormat"

"tableName"

"autoValue"

"transformName"

`"createIntegrationTable"` (jsonAction)

JSON hub `"createIntegrationTable` action creates a new integration table in FairCom Edge

`"params"`

`"databaseName"`

`"fields"`

`"metadata"`

`"retentionPolicy"`

`"retentionPeriod"`

`"retentionUnit"`

`"sourcePayloadFormat"`

`"tableName"`

`"autoValue"`

`"transformName"`