Создание и получение большого количества элементов приложений напрямую из БД 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»
на верхней панели. Ждем пока все элементы приложения прогрузятся.
![[IMG]](https://community.elma365.com/ru/assets/c2NyZWVuMTE3MjE5ODg0NTE=.png)
После загрузки видим сам запрос в верхней части и найденные записи в нижней. Обращаем внимание на то, что все поля приложения хранятся в столбце «body» в формате json.
Для получения структуры хранения элементов приложения необходимо двойным нажатием раскрыть одну из ячеек с json.
![[IMG]](https://community.elma365.com/ru/assets/c2NyZWVuMjE3MjE5ODg1NjE=.png)
Далее составим запрос для массового добавления элементов:
Код:
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'
Результат выборки:
![[IMG]](https://community.elma365.com/ru/assets/c2NyZWVuMzE3MjE5ODg2Mzk=.png)
Теперь получим документы всех типов по всем регионам, но с определенными статусами. Запрос примет следующий вид:
Код:
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'
Результат выборки:
![[IMG]](https://community.elma365.com/ru/assets/c2NyZWVuNDE3MjE5ODg3MDE=.png)
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.
![[IMG]](https://community.elma365.com/ru/assets/c2NyZWVuNTE3MjE5ODg4MjA=.png)
Более подробно с синтаксисом можно ознакомиться в статье.