Синтаксис sql запросов к БД postgreSQL ELMA365

Создание и получение большого количества элементов приложений напрямую из БД PostgreSQL ELMA365.


Инструменты
Docker, pgAdmin, библиотека pg.


Реализация
1. Создание большого количества элементов приложений
Задача заключается в возможности создания большого количества экземпляров приложений (несколько тысяч, миллионов) по средствам прямых sql-запросов с целью сокращения времени и нагрузки на систему.
Для формирования sql запросов на добавление элементов необходимо узнать структуру хранения приложения в БД. Для этого можно воспользоваться pgAdmin.


1.1. Получение учетных данных от БД
Для подключения к БД через pgAdmin нужны учетные данные - креды. Способ их получения может отличаться в зависимости от типа поставки и типа БД (внутренние или внешние). Чтобы узнать креды следует обратиться к девопсам, которые разворачивали стенд.
Рассмотрим варианты ниже:
· С внешними БД креды должны быть известны без дополнительных манипуляций.
· Установка Helm-чартами с внутренними БД, поставка onPremise.
Необходимо поставить jq, затем выполнить следующую команду:

Код:

kubectl get secrets/elma365-db-connections -o json | jq '.data | map_values(@base64d)' | grep PSQL_URL

Затем необходимо пробросить порт:

Код:

kubectl port-forward --address 0.0.0.0 pod/postgres-0 5432

· Установка Docker с внутренними БД, поставка onPremise.
Следует проверить файл config-elma365.txt и раскомментировать следующие строки:

Код:

#ELMA365_PORT_FORWARD_PSQL=5432
#ELMA365_PORT_FORWARD_MONGO=27017
#ELMA365_PORT_FORWARD_AMQP=15672
#ELMA365_PORT_FORWARD_REDIS=6379
#ELMA365_PORT_FORWARD_S3=9000

Затем следует запустить установку и проверить подключение:

Код:

telnet elma365ha-app8.elewise.local 5432

После чего необходимо прописать следующие команды:

Код:

docker exec -it elma365 /bin/bash
kubectl edit secret elma365-db-connections

Далее необходимо найти PSQL_URL и расшифровать данные:

Код:

echo'cG9zdGdyZXM6Ly9wb3N0Z3JlczoxZnNneklFYkRCZ2VNTlJWQHBvc3RncmVzLmRlZmF1bHQuc3ZjLmNsdXN0ZXIubG9jYWw6NBQzMi9lbG1hMzY0ZGIxeG53P3NzbG7vZGU9ZGlzYWJsZQ=='| base64 --decode

Длинную зашифрованную строку необходимо поменять на свою PSQL_URL.
После любого из способов в ответ получаем строку такого вида:

Код:

postgres://postgres:H7ceACYE4bthSqAX@postgres.default.svc.cluster.local:5432/elma365dbrlxk?sslmode=disable

где пользователь: postgres, пароль: H7ceACYE4bthSqAX.
Креды получены, переходим к pgAdmin.


1.2. Настройка pgAdmin
Запускаем pgAdmin, нажимаем «Add New Server» и задаем название сервера. Во вкладке «Сonnection» заполняем необходимые для подключения поля:
«Host name/address» - указываем ip адрес стенда без «http://»,
«port» - 5432,
«Maintenance database» - elma365dbrlxk,
«Username» и «Password» из кред.


1.3. Формирование запроса
После успешного подключения проваливаемся по такому пути: Servers -> «Название сервера» -> Databases -> elma365dbrlxk -> Schemas -> head -> Tables. Здесь хранятся все приложения вашего стенда в виде таблиц. Ищем в этой ветке интересующую таблицу, которая имеет название, идентичное коду раздела и приложения на вашем стенде (код_раздела:код_приложения). Нажимаем по данной таблице в списке, а затем на кнопку «All rows» на верхней панели. Ждем пока все элементы приложения прогрузятся.


После загрузки видим сам запрос в верхней части и найденные записи в нижней. Обращаем внимание на то, что все поля приложения хранятся в столбце «body» в формате json.
Для получения структуры хранения элементов приложения необходимо двойным нажатием раскрыть одну из ячеек с json.

Далее составим запрос для массового добавления элементов:

Код:

DO $$
DECLARE
  op_uuid uuid;
BEGIN
  FOR i IN  0..4999999 LOOP
    op_uuid = uuid_generate_v4();
    INSERT INTO head."document_accounting:dossier_fl"(id, body, permissions, read, inherit)
    VALUES (
      op_uuid,
      (
        '{
          "__id": "' || op_uuid || '",
          "__name": "№' || i ||'",
          "region": "Тюменская область",
          "__debug": false,
          "__index": 1,
          "__status": {
            "order": 0,
            "status": 10
          },
          "__version": 1716898746,
          "case_date": "2024-01-01T00:00:00Z",
          "__createdAt": "2024-05-28T12:19:06Z",
          "__createdBy": "6ea6eee4-e3c9-4c43-b78d-25d66cbc792e",
          "__deletedAt": null,
          "__directory": null,
          "__updatedAt": "2024-05-28T12:19:06Z",
          "__updatedBy": "6ea6eee4-e3c9-4c43-b78d-25d66cbc792e",
          "__externalId": null,
          "__subscribers": [
            "6ea6eee4-e3c9-4c43-b78d-25d66cbc792e"
          ],
          "kind_of_document": [
            {
              "code": "credit",
              "name": "Кредиты частным лицам",
              "checked": false
            }
          ],
          "__externalProcessMeta": null,
          "loan_agreement_number": "111"
        }'
      )::jsonb,
      '{
        "values": [],
        "timestamp": 0,
        "inheritParent": true
      }',
      null,
    true);
  END LOOP;
END
$$;

Данный запрос создаст 5 000 000 элементов в таблице document_accounting:dossier_fl. В запросе структуру body необходимо поменять на свою. Создание 100 000 элементов таким способом занимает порядка 20 минут.

2. Получение большого количества элементов
Для наглядности продолжим работу в pgAdmin, а затем перейдем к скриптам в Elma и библиотеке pg.
Кейс: приложение хранит в себе множество документов разных типов по разным регионам. Предположим, что нам необходимо получить количество всех документов – депозитов, оформленных в Иркутской области, и дата оформления которых 01.01.2024 и позднее. Запрос будет выглядеть следующим образом:

Код:

SELECT body ->> 'region' AS Region, body->'kind_of_document'->0->>'code' AS kind_of_document, COUNT(body->'__id') AS amount
  FROM head."document_accounting:dossier_fl"
  WHERE body ->> 'region' = 'Иркутская область'
  AND body ->> 'case_date' >= '2024-01-01T00:00:00Z'
  AND body->'kind_of_document'->0->>'code' = 'deposit'
  AND (body ->> '__deletedAt' = NULL OR body ->> '__deletedAt' IS NULL)
  GROUP BY body ->> 'region', body->'kind_of_document'->0->>'code'

Результат выборки:

Теперь получим документы всех типов по всем регионам, но с определенными статусами. Запрос примет следующий вид:

Код:

SELECT body ->> 'region' AS Region, body->'kind_of_document'->0->>'code' AS kind_of_document,
    body->'__status'->'status' AS status, COUNT(body->'__id') AS amount
  FROM head."document_accounting:dossier_fl"
  WHERE body ->> 'case_date' >= '2024-01-01T00:00:00Z'
  AND body->'__status'->'status' in ('1', '2', '4', '7')
  AND (body ->> '__deletedAt' = NULL OR body ->> '__deletedAt' IS NULL)
  GROUP BY body ->> 'region', body->'kind_of_document'->0->>'code', body->'__status'->'status'

Результат выборки:


3. ELMA + библиотека pg
Такие запросы могут быть полезными внутри Элмы, так как при большом количестве элементов они отрабатывают быстрее и не имеют ограничений на количество при добавлении и получении.
Для начала следует найти npm пакет библиотеки pg. Далее необходимо установить docker и воспользоваться скриптом, приведенном в справке для создания архива.
После получения архива, его необходимо использовать внутри виджета или метода АПИ. Подгружаем архив на вкладке Файлы и в сценариях на стороне сервера объявляем импорт пакета:

Код:

import * as pg from 'pg'

Оформим запрос в виде функции:

Код:

async function receivingDocuments(req:FetchRequest): Promise<HttpResponse | void> {
    try {
        const response = new HttpResponse();
        const body = JSON.parse(req.body!.toString());

        //Выполняем подключение к БД
        const client = new pg.Client({
            connectionString: "postgres://postgres: H7ceACYE4bthSqAX@postgres.default.svc.cluster.local:5432/elma365dbrlxk?sslmode=disable",
})

        await client.connect();

        //Выполняем запрос к БД
        const res = await client.query(`

        SELECT body ->> 'region' AS Region, body->'kind_of_document'->0->>'code' AS kind_of_document,
          body->'__status'->'status' AS status, COUNT(body->'__id') AS amount
        FROM head."document_accounting:dossier_fl"
        WHERE body ->> 'case_date' >= '2024-01-01T00:00:00Z'
        AND body->'__status'->'status' in ('1', '2', '4', '7')
        AND (body ->> '__deletedAt' = NULL OR body ->> '__deletedAt' IS NULL)
        GROUP BY body ->> 'region', body->'kind_of_document'->0->>'code',
  body->'__status'->'status'
`)

        //Закрываем подключение к БД
        await client.end();

        if (res) {
            return response.status(200).json({
                "response": JSON.stringify(res.rows)
            })
        }
        else {
            return response.status(500).json({
                "response": " no response received "
            })
        }
    }
    catch (e) {
        const response = new HttpResponse();
        return response.status(400).json({
            "response": e.message
        })
    }
}

Результат выборки будет доступен в массиве res.rows.

Более подробно с синтаксисом можно ознакомиться в статье.

Замечательная статья!