API Reference

HTTP is used as a communication protocol to send API commands to the database server.
Therefore, each request can be performed using a simple command line tool like curl or any other http client. In addition, SDK libraries in several languages that simplify the communication can be downloaded.

Connect to Database

To authenticate against the server, a connect command with all relevant information (server url, database, user, password) needs to be executed. This command returns, besides other information, a connection unique id that needs to be used in the authentication header of each later request. After that one can check if the connection works via the alive command.

Endpoints

There exist several that provide different ways of interacting with the database. If no endpoint is set, the rpc endpoint is used.

Endpoint	Description
.well-known	Documentation of the database commands in JSON Schema format.
api	Get and set data in OpenAPI format.
rpc	Get and set data in OpenRPC format.
file	Upload and download server files.

.Well-Known

The .well-known/openapi.json endpoint returns a documentation of the database commands according to the OpenAPI standard in a discovery document in json format. It uses JSON Schema to document the input parameters and response types.

curl http://127.0.0.1:8700/.well-known/openapi.json

From this OpenAPI specification custom interface libraries can be generated via tools like OpenAPI Generator.

API

The server uses http post for each database command on its api endpoints.

In this example, the connect command is used:

http://127.0.0.1/api/connect

Text Format

When content type is text/plain, the URL contains the command. The other lines are parameters in key/value format.

Example 1: Connect to the database.

URL:

http://127.0.0.1/connect

Body:

USER=Sys  
PASS=pass  
DATABASE=Demo

Example 2: Consolidate table data.
The Authorization header needs to provide the connection UID returned by the connect command.

URL:

http://127.0.0.1/api/consolidateRecords

Body:

TABLE=[Sales]  
DIMENSION=[Month]  
DIMENSION=[Customer]  
DIMENSION=[Product]  
SUM=[Amount]  
SUM=[Turnover]  
FILTER=[Month].[2022-10]  
FILTER=[Month].[2022-11]  
FILTER=[Product].[P1000]

Json Format

When content type is application/json, the method parameter contains the command.

Example 1: Connect to the database.

URL:

http://127.0.0.1/api/connect

Body:

{
  "user": "Sys",
  "pass": "pass",
  "database": "Demo"
}

Example 2: Consolidate table data.
The Authorization header needs to provide the connection UID returned by the connect command.

URL:

http://127.0.0.1/api/consolidateRecords

Body:

{
  "table": "[Sales]",
  "dimension": [
    "[Month]",
    "[Customer]",
    "[Product]"
  ],
  "sum": [
    "[Amount]",
    "[Turnover]"
  ],
  "filter": {
    "[Month]": [
      "[2022-10]",
      "[2022-11]"
    ],
    "[Product]": [
      "[P1000]"
    ]
  }
}

RPC

The server uses the json-rpc 2.0 protocol for communication via its rpc endpoints. These are documented according to the OpenRPC standard in a json discovery document. It uses JSON Schema to document the input parameters and response types.

Alternatively to using the json-rpc format, a simple text command format can be used in the http requests when using content-type header text/plain. This is the format used in the Manager and Console applications.

curl http://127.0.0.1:8700/rpc -d '{"method": "rpc.discover"}'

From this OpenRPC specification custom interface libraries can be generated via tools like OpenRPC Generator.

Text Format

When content type is text/plain, the first line contains the command. The other lines are parameters in key/value format.

Example 1: Connect to the database.

URL:

http://127.0.0.1
http://127.0.0.1/rpc

Body:

CONNECT  
USER=Sys  
PASS=pass  
DATABASE=Demo

Example 2: Consolidate table data.
In the header, the Authentication parameter is needed. It conations the UID returned by the connect command.

URL:

http://127.0.0.1
http://127.0.0.1/rpc

Body:

CONSOLIDATE RECORDS
TABLE=[Sales]  
DIMENSION=[Month]  
DIMENSION=[Customer]  
DIMENSION=[Product]  
SUM=[Amount]  
SUM=[Turnover]  
FILTER=[Month].[2022-10]  
FILTER=[Month].[2022-11]  
FILTER=[Product].[P1000]

Json Format

When content type is application/json, the method parameter contains the command.

Example 1: Connect to the database.

URL:

http://127.0.0.1
http://127.0.0.1/rpc

Body:

{
  "id": 1,
  "jsonrpc": "2.0",
  "method": "connect",
  "params": {
    "user": "Sys",
    "pass": "pass",
    "database": "Demo"
  }
}

Example 2: Consolidate table data.
In the header, the Authentication parameter is needed. It conations the UID returned by the connect command.

URL:

http://127.0.0.1
http://127.0.0.1/rpc

Body:

{
  "id": 1,
  "jsonrpc": "2.0",
  "method": "consolidate records",
  "params": {
    "table": "[Sales]",
    "dimension": [
      "[Month]",
      "[Customer]",
      "[Product]"
    ],
    "sum": [
      "[Amount]",
      "[Turnover]"
    ],
    "filter": {
      "[Month]": [
        "[2022-10]",
        "[2022-11]"
      ],
      "[Product]": [
        "[P1000]"
      ]
    }
  }
}

File

The file endpoint allows uploading and downloading files easily. These exist for convenience in addition to the setFile and getFile commands (api and rfc)for which the file content needs to be base64 encoded.

Get a File

The http get method loads a file from the server as a binary stream (application/octet-stream). The URL contains the File parameter. Percent values must be used for special characters in the URL (%20=space, %2F=slash, %3D=equal...).

This example loads the file Good Customers.pdf from the server (get method):

http://127.0.0.1/file:8700?File=Good%20Customers.pdf

Set a File

The http post or put method creates or updates a file on the server. The body contains the file as a binary stream (application/octet-stream). The URL can contain three parameters. Percent values must be used for special characters in the URL (%20=space, %2F=slash, %3D=equal...).

Parameter	Description
File	This parameter is needed and contains the file name.
CreateDirectory	This parameter is optional. If True, the target directory will be created if it does not exists. Only the last directory will be created and not the entire directory tree. The default value is True.
Overwrite	This parameter is optional. If True an existing file is overwritten. The default value is True.

This example saves the file Best Customers.pdf to the server (post or put method):

http://127.0.0.1/file:8700?File=Best%20Customers.pdf&CreateDirectory=False&Overwrite=True

Http Headers

For all the endpoints the same http headers are relevant which are described below.

Content-Type

With the content type header the client specifies what input format it will use.

Content-Type	Description
text/plain	The input is plain text.
application/json	The input is json.

Accept

The accept header determines what output format should be used.

Content-Type	Description
application/json	The result object contains three arrays: The fields, records and statistic.
application/formatted+json	Same as application/json but uses user-friendly formatting.
application/field+json	Same as application/json but every record is an object { field, value } that contains the field name.
application/field-formatted+json	Same as application/json but every record contains also the field name and uses user-friendly formatting.
application/octet-stream	The result is stored in a binary stream.
application/javascript+octet-stream	Same as application/octet-stream but the bit order of double fields is changed. This is required for JavaScript.

Auth

The Authorization header is needed for all requests after connecting to the database with the connect command. This command returns a connection UID which is used to identify the client. The format of the header value is: UID <UID from connect>

Example:

Authorization: 'UID 0C4E08F277834AFDA39ED4F437D20B234DEB6BDE3F014A8BBD0FAF'

Alternatively, a cookie based authentication can be used by setting the parameter SetCookie to true. In that case the response to the connect command contains the connection UID in a cookie, so no auth header needs to be set.