Создание SQL регламентированного запроса

В статье «Обмен в СМЭВ4 с использованием SQL запросов» рассматривается сценарий работы в СМЭВ4, предполагающий обмен с использованием SQL запросов.

Важным шагом для реализации такого обмена является создание и регистрация регламентированного запроса (РЗ) в СМЭВ4 посредствам системы ЕИП НСУД.

РЗ нужно спроектировать, создать, согласовать и отправить в СМЭВ4 (ПОДД). Только в случае успешной отправки в тестовую и/или продуктивную среду СМЭВ4 РЗ считается зарегистрированным, и Потребитель сможет получить к нему доступ для совершения обменов.

Важно!

Для создания РЗ, Витрина или Витрины, параметры которых будут использоваться в РЗ, должны быть согласованы.

Интерфейс системы ЕИП НСУД детально описан в Инструкции ЕИП НСУД. Создание РЗ, работа с конструктором SQL-запроса, формами согласования и интеграции со СМЭВ4 (ПОДД) в разделе 4.7.

После создания РЗ с помощью SQL-конструктора, его можно модифицировать. Конструктор упрощает работу с формированием типовых РЗ, но возможности СМЭВ4 несколько шире и позволяют использовать больше функций, чем реализовано в конструкторе.

Синтаксис возможных операций описан в методических рекомендациях СМЭВ4 (МР СМЭВ4. В версии 3.19.0 это раздел 3.1).

Важно понимать, что запрос в ходе создания и выполнения несколько раз преобразовывается. Это понимание важно для правильного проектирования РЗ.

Пример формирования и преобразования РЗ

Рассмотрим на примере.

Реализуем РЗ типа SQL, который будет возвращать список из 10 отсортированных по наименованию аккредитованных ИТ организаций, наименования которых начинаются с выражения, указанного во входном параметре без учёта регистра.

С помощью конструктора создаём РЗ, выбрав поля, которые вернутся при выполнении РЗ (рисунок 1), входной параметр, а также задав условие WHERE (рисунок 2) и операторы ORDER BY и LIMIT (рисунок 3).

Рисунок 1. Создание РЗ типа SQL. Выбор атрибутов

Входным параметром выбираем Название организации.

Рисунок 2. Создание РЗ типа SQL. Выходной параметр

В условии WHERE так же выберем Название организации, пока на данном шаге, операцию равно (=) и значение знак вопроса (?), что будет означать, что сюда подставится входной параметр.

Сортировка (ORDER BY) будет так же по названию и ограничение количества возвращаемых данных (LIMIT) – 10.

Рисунок 3. Создание РЗ типа SQL. Условие и операторы.

Далее, после нажатия на кнопку «Применить запрос» сформируется первая версия нашего запроса:

SELECT 
it_organization.1.0.organizations."id" AS "organizations_id",
it_organization.1.0.organizations."date_record" AS "organizations_date_record",
it_organization.1.0.organizations."name_organization" AS "organizations_name_organization",
it_organization.1.0.organizations."ogrn" AS "organizations_ogrn",
it_organization.1.0.organizations."inn" AS "organizations_inn",
it_organization.1.0.organizations."date_decision" AS "organizations_date_decision",
it_organization.1.0.organizations."number_decision" AS "organizations_number_decision",
it_organization.1.0.organizations."date_change" AS "organizations_date_change",
it_organization.1.0.organizations."change_description" AS "organizations_change_description",
it_organization.1.0.organizations."status" AS "organizations_status"

FROM it_organization.1.0.organizations
WHERE it_organization.1.0.organizations."name_organization" = ?
ORDER BY it_organization.1.0.organizations."name_organization"
LIMIT 10;

Для того, чтобы понять синтаксис SQL при редактировании запроса, можно обращаться к разделу 3.1 Методических рекомендаций.

Так, например, в нашем запросе мы используем:

Конкатенация символьных строк	SELECT ‘a’ || ‘b’ FROM t
Тип данных CHARACTER VARYING	SELECT CAST(‘111’ AS VARCHAR)
Предикат LIKE	SELECT column FROM t WHERE char_column LIKE ‘_’
Функция UPPER	SELECT upper(‘a’) FROM t

Заменим в условии равно (=) на LIKE, используем конкатенацию (||) и приведение входного параметра к типу VARCHAR, и добавим приведение параметра и значения к верхнему регистру (для того, чтобы регистр входного параметра не учитывался).

В итоге получается конечная версия РЗ в ЕИП НСУД:

SELECT 
it_organization.1.0.organizations."id" AS "organizations_id",
it_organization.1.0.organizations."date_record" AS "organizations_date_record",
it_organization.1.0.organizations."name_organization" AS "organizations_name_organization",
it_organization.1.0.organizations."ogrn" AS "organizations_ogrn",
it_organization.1.0.organizations."inn" AS "organizations_inn",
it_organization.1.0.organizations."date_decision" AS "organizations_date_decision",
it_organization.1.0.organizations."number_decision" AS "organizations_number_decision",
it_organization.1.0.organizations."date_change" AS "organizations_date_change",
it_organization.1.0.organizations."change_description" AS "organizations_change_description",
it_organization.1.0.organizations."status" AS "organizations_status"

FROM it_organization.1.0.organizations
WHERE UPPER(it_organization.1.0.organizations."name_organization") 
like (UPPER(cast(? as varchar)) || '%') 
ORDER BY it_organization.1.0.organizations."name_organization" 
LIMIT 10;

Примечание

Для РЗ можно создать также табличные параметры. Табличный – это параметр, значение которого представляет собой двумерный массив с именованными колонками и неупорядоченными строками. Далее табличный параметр может использоваться в инструкциях FROM, JOIN как источник данных. Подробнее о работе с табличными параметрами в Методических рекомендациях СМЭВ4.

Сохраним этот запрос, отправим в тестовую среду СМЭВ4 и получим доступ в ЛК УВ (ссылка на РП ЛК УВ) к нему от своей же системы.

Теперь его можно выполнить. Для этого используется следующий формат:

SELECT * FROM it_organization.1.0.it_org_rz('name_organization')

Примечание

Пример вызова запроса всегда присутствует на странице РЗ в ЕИП НСУД

Вызываем РЗ с помощью CURL:

curl -X POST -H "Accept-Version:1" -H "Content-Type: application/json" -d 
'{"sql": {"sql": "Select * from it_organization.it_org_rz('\''общество'\'')"}}' 
http://localhost:8192/query --silent -m 30

Этот запрос на стороне СМЭВ4, преобразуется в подзапрос к Витрине данных. В Витрине Поставщика подзапрос через компонент "Адаптер" передаётся в ядро – Prostore и далее в СУБД.

Важно!

Важно понимать, что синтаксис SQL запроса изначального SQL-выражения, должен поддерживаться всеми системами и компонентами, через которые он проходит. Ошибочно считать, что если у Поставщика на Витрине используется, например, СУБД Postgres, то в РЗ можно использовать все её функции.

В ответ вернется массив с 10 записями (рисунок 4).

Рисунок 4. Выполнение РЗ через CURL.

Для оптимизации созданного РЗ рекомендуем использовать статью «Оптимизация работы РЗ».