`"createIntegrationTable"`

Abstract

createIntegrationTable creates an integration table.

The "createIntegrationTable" action creates an integration table.

Things to know

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 request example

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

Create an integration table with one additional field request example

{
  "api":        "hub",
  "apiVersion": "1.0",
  "requestId":  "2",
  "authToken":  "anAuthorizationTokenFromTheServer",
  "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"
}

Response examples

Successful response example

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

Table already exists failure response example

{
    "authToken": "anAuthorizationTokenFromTheServer",
    "result": {},
    "requestId": "2",
    "debugInfo": {
        "request": {
            "authToken": "anAuthorizationTokenFromTheServer",
            "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"
        }
    },
    "errorCode": 12020,
    "errorMessage": "Not able to create integration table [test1]. Integration table name already exists."
}

`"params"`

The "params" property is an object that contains an action's parameters. Each action defines its own required and optional properties.

Property summary

Table 1. "params" property summaries

Property

Description

Default

Type

Limits (inclusive)

"databaseName"

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

Minimum length: 1

Maximum length: 64

"fields"

contains objects that specify the settings of a field in a table

Required - No default value

array

"metadata"

exists primarily for the user interface to find integration information

{}

object

May contain any number and type of user-defined properties

"retentionPolicy"

controls how messages are persisted

"autopurge"

string

"autoPurge"

"neverPurge"

"doNotPersist"

"retentionPeriod"

sets the number of retention units, which controls how long data is retained – see "retentionUnit"

4

integer

1 - 100

"retentionUnit"

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

"week"

string

"minute"

"hour"

"day"

"week"

"month"

"year"

"forever"

"tableName"

specifies the name of a table

Required - No default value

string

Minimum length: 1

Maximum length 64

`"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.

Things to know:

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
  }
]

Property summary

Table 2. "fields" property summaries

Property

Description

Default

Type

Limits (inclusive)

"autoValue"

controls when and how the server automatically sets the field value

"none"

string

"none"

"incrementOnInsert"

"timestampOnInsert"

"timestampOnUpdate"

"timestampOnUpdateAndInsert"

"changeid"

"defaultValue"

specifies the default value of a field

""

string

Minimum length: 0

Maximum length: 65500

"length"

defines the length of a field's value in a record

null

integer

1 - 65500

"name"

defines a new name of the field

required

string

Minimum length: 0

Maximum length: 64

"nullable"

allows a field to contain a NULL value when true

true

Boolean

true

false

"primaryKey"

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

null

integer

0 - 32

"scale"

defines the number of places to the right of the decimal point

null

integer

0 - 32

"type"

defines the type of field

required

string

"bit"

"tinyint"

"smallint"

"integer"

"bigint"

"real"

"double"

"number"

"money"

"date"

"time"

"timestamp"

"char"

"varchar"

"lvarchar"

"binary"

"varbinary"

"lvarbinary"

"json"

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

`"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.

Things to know:

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.
"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.
"doNotPersist"
This stores messages only in RAM, where they are transmitted more quickly. Undelivered messages are lost when FairCom's servers stop and restart. No transforms can be applied to the payload. Message data cannot be bridged across protocols.

Important

Changing the "retentionPolicy" property value to "doNotPersist" removes all existing message history for the topic because it changes how messages are stored on disk.

`"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.

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.

Things to know:

Each time the "retentionPeriod" cycles, FairCom's servers automatically and efficiently delete expired data.
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".

Things to know:

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 the "retentionUnit" is set to "forever" the messages will never be purged.
FairCom MQ only uses the "retentionUnit" property when the "retentionPolicy" is "autoPurge".

`"tableName"`

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.