'dmsquery' capability for WebSocket Token API

Table of Contents

Overview

Overview

Provides an interface for executing arbitrary Select SQL queries on a postgresql database that hosts model data of a specified class (or explicitly specified in stores).

Is a project tool for building specific reports with intersections (JOIN).

This mechanism is an adaptation mechanism, not a general one. Because in general case different classes can place data in different databases, and for building samples with intersections we need data placed in different storages. Besides, the tables with class data have specific names. Therefore, the use of this mechanism creates problems of further modifiability and replicability of packages.

Uses storage access with an identical name and the postfix '_readonly', assuming access to the database from a read-only user account. If no such storage is found, the system attempts to create a read-only user account, for which the primary account needs full access. If this fails, the request is terminated with an error.

If the data of different classes necessary for building a data sample are distributed over several different databases, it is necessary to ensure their presence in one database by design means. ETL (copies of collections), dblink or other methods may be useful for this purpose.

Given that in large distributed systems, archived data is most likely hosted in Clickhouse, such queries to postgresql will be prevented from accessing the archives.

In the future, the method will be extended to Clickhouse, and then the preprocessing and ETL tools will need to place the final category tables, the values of which will be needed to build the samples, there. And the syntax of the SQL query should be as follows clickhouse.

General view of the request:

[
  "dmsquery",
  {
    "qid": 0.1234,
    "classpath": "/rest/v1/model/SomeClass",
    "timeout": 20000,
    "storageInstance": "auto",
    "sqlText": "Select id as i, name From SomeSchema.SomeTable"
  }
]

General view of the answer:

[
  "dmsquery_result",
  {
    "qid": 0.1234,
    "result": ok,
    "content": [
      {
        "i": ...,
        "name": ...
      },
      {
        "i": ...,
        "name": ...
      },
      ...
    ]
  }
]