3. Использование ПОДД СМЭВ

3.1. SQL-синтаксис

Регламентированные SQL-запросы, регистрируемые в ПОДД СМЭВ должны соответствовать следующему синтаксису.

Таблица 3.3 SQL-синтаксис

№	Описание	Пример запроса
	Числовые типы данных
1	Типы данных INTEGER и SMALLINT	SELECT CAST(1 AS INT)
2	Типы данных REAL, DOUBLE PRECISION и FLOAT	SELECT CAST(1 AS FLOAT)
3	Типы данных DECIMAL и NUMERIC	SELECT CAST(1 AS NUMERIC)
4	Арифметические операторы	SELECT 10+1, 9-2, 8*3, 7/2
5	Числовые сравнения	SELECT 1 WHERE 1 < 2
6	Неявные преобразования между числовыми типами данных	SELECT int_column FROM t WHERE int_column = 1.00
Символьные типы данных
7	Тип данных CHARACTER Длина по умолчанию 30	SELECT CAST(‘1111111111111111111111111111111111111111111’ AS CHAR)
8	Тип данных CHARACTER VARYING Длина по умолчанию 30	SELECT CAST(‘1111111111111111111111111111111111111111111’ AS VARCHAR)
9	Символьные строки	SELECT ''
10	Функция CHARACTER_LENGTH убирает завершающие пробелы из значений CHARACTER перед подсчётом символов	SELECT character_length(char_column) FROM t
11	Функция OCTET_LENGTH	SELECT octet_length(char_column) FROM t
12	Функция SUBSTRING	SELECT substring(char_column FROM 1 FOR 1) FROM t
13	Конкатенация символьных строк	SELECT ‘a’ || ‘b’ FROM t
14	Функции UPPER и LOWER	SELECT upper(‘a’),lower(‘B’) FROM t SELECT int_column FROM t WHERE int_column > (SELECT DISTINCT (int_column) FROM t)
15	Функция TRIM	SELECT trim(‘a ‘) FROM t
16	Неявные преобразования между типами символьных строк	SELECT char_column FROM t WHERE char_column > varchar_column
17	Функция POSITION	SELECT position(‘A’ IN char_column) FROM t
18	Сравнения символов	SELECT char_column FROM t WHERE char_column > ‘a’
Идентификаторы
19	Идентификаторы с разделителями	SELECT 1 AS «t47»
20	Идентификаторы в нижнем регистре	SELECT 1 AS t48
21	Завершающее подчёркивание	SELECT 1 AS t49_
Базовое определение запросов
22	SELECT DISTINCT	SELECT DISTINCT int_column FROM t
23	Предложение GROUP BY	SELECT DISTINCT int_column FROM t GROUP BY int_column
24	GROUP BY может содержать колонки не из <списка выборки>	SELECT DISTINCT char_column FROM t GROUP BY lower(char_column)
25	Элементы списка выборки могут переименовываться	SELECT int_column AS K FROM t ORDER BY K
26	Предложение HAVING	SELECT count(*) FROM t HAVING count(*) > 0
27	Корреляционные имена в предложении FROM	SELECT K.column FROM t AS K
28	Переименование колонок в предложении FROM	SELECT column FROM t AS x(q, c)
Базовые предикаты и условия поиска
29	Предикат сравнения	SELECT column FROM t WHERE 0 = 0
30	Предикат BETWEEN	SELECT column FROM t WHERE ‘ ‘ BETWEEN ‘’ AND ‘’
31	Предикат IN со списком значений	SELECT column FROM t WHERE char_column IN (‘a’, upper(‘a’))
32	Предикат LIKE	SELECT column FROM t WHERE char_column LIKE ‘_’
33	Предложение ESCAPE в предикате LIKE	SELECT column FROM t WHERE ‘abc’ LIKE ‘abcX_’ ESCAPE ‘X’
34	Предикат NULL	SELECT column FROM t WHERE char_column IS NOT NULL
35	Предикаты количественного сравнения	SELECT column FROM t WHERE char_column = ANY (SELECT char_column FROM t)
36	Предикат EXISTS	SELECT column FROM t WHERE NOT EXISTS (SELECT char_column FROM t)
37	Подзапросы в предикате сравнения	SELECT column FROM t WHERE int_column > (SELECT max (int_column) FROM t)
38	Подзапросы в предикате IN	SELECT column FROM t WHERE char_column IN (SELECT char_column FROM t)
39	Подзапросы в предикате количественного сравнения	SELECT column FROM t WHERE char_column >= ALL(SELECT char_column FROM t)
40	Коррелирующие подзапросы	SELECT column FROM t WHERE int_column = (SELECT int_column FROM t2 WHERE t2.char_ column = t.char_column)
41	Условие поиска	SELECT column FROM t WHERE 0 <> 0 OR ‘a’ < ‘b’ AND int_column IS NOT NULL
Простые выражения с запросами
42	Табличный оператор UNION DISTINCT	SELECT column FROM t UNION DISTINCT SELECT column1 FROM t
43	Табличный оператор UNION ALL	SELECT column FROM t UNION ALL SELECT column1 FROM t
44	Табличный оператор EXCEPT DISTINCT	SELECT column FROM t EXCEPT DISTINCT SELECT column1 FROM t
45	Колонки, объединяемые табличными операторами, могут иметь разные типы данных	SELECT char_column FROM t UNION SELECT 5
46	Табличные операторы в подзапросах	SELECT column FROM t WHERE ‘a’ IN (SELECT char_column FROM t UNION SELECT char_column FROM t)
Функции множеств
47	AVG	SELECT avg(int_column) FROM t
48	COUNT	SELECT count(int_column) FROM t
49	MAX	SELECT max(int_column) FROM t
50	MIN	SELECT min(int_column) FROM t
51	SUM	SELECT sum(int_column) FROM t
52	Дополнение ALL	SELECT sum(ALL int_column) FROM t
53	Дополнение DISTINCT	SELECT sum(DISTINCT int_column) FROM t
54	Оператор SELECT, возвращающий одну строку	SELECT count(*) FROM t
Базовая поддержка курсоров
55	Колонки ORDER BY, отсутствующие в списке выборки	SELECT int_column FROM t ORDER BY char_column
56	Выражения значений в предложении ORDER BY	SELECT int_column FROM t ORDER BY -int_column
57	Поддержка NULL (NULL вместо значений)	SELECT int_column FROM t WHERE int_column IS NULL
Базовое соединение таблиц
58	Внутреннее соединение (но не обязательно с ключевым словом INNER)	SELECT a.int_column FROM t a JOIN t b ON a.int_column = b.int_column
59	Ключевое слово INNER	SELECT a.int_column FROM t a JOIN t b ON a.int_column = b.int_column
60	LEFT OUTER JOIN	SELECT a.int_column, b.int_column FROM t a LEFT OUTER JOIN t b ON a.int_column = b.int_column
61	RIGHT OUTER JOIN	SELECT a.int_column, b.int_column FROM t a RIGHT OUTER JOIN t b ON a.int_column = b.int_column
62	Внешние соединения могут быть вложенными	SELECT a.int_column FROM t a LEFT OUTER JOIN t b ON a.int_column = b.int_column LEFT OUTER JOIN t2 c ON a.int_column = c.int_column
63	Внутренняя таблица с левой или правой стороны внешнего соединения может также участвовать во внутреннем соединении	SELECT t2.int_column FROM (t2 LEFT OUTER JOIN t ON t.int_column = t2.int_column) j INNER JOIN t2 ON j.int_column = t2.int_column
64	Поддерживаются все операторы сравнения (а не только =)	SELECT column FROM t WHERE 0 = 1 OR 0 > 1 OR 0 < 1 OR 0 <> 1
Базовая поддержка даты и времени
65	Тип данных DATE (включая поддержку строк DATE)	SELECT ‘2012-07-12’ AS "DATE"
66	Тип данных TIME (включая поддержку строк TIME) с точностью до секунд как минимум с 0 знаков после запятой	SELECT ‘1:2:3’ AS "TIME"
67	Тип данных TIMESTAMP (включая поддержку строк TIMESTAMP) с точностью до секунд как минимум с 0 и 6 знаками после запятой	SELECT ‘2012-07-12’ AS "TIMESTAMP"
68	Предикаты сравнения с типами данных DATE, TIME и TIMESTAMP	SELECT column FROM t3 WHERE date_column = date_column AND time_column = time_column AND timestamp_column = timestamp_column
69	Явное приведение (CAST) между типами даты/времени и типами символьных строк	SELECT cast(date_column AS VARCHAR(10)) FROM t3
70	CURRENT_DATE	SELECT current_date FROM t
71	LOCALTIME	SELECT localtime FROM t
72	LOCALTIMESTAMP	SELECT localtimestamp FROM t
Расширенная поддержка даты и времени
73	FOR SYSTEM_TIME (запрос данных, актуальных на указанную дату и время)	SELECT column FROM t FOR SYSTEM_TIME AS OF 'YYYY-MM-DD hh:mm:ss' SELECT column FROM t FOR SYSTEM_TIME AS OF TIMESTAMP 'YYYY-MM-DD hh:mm:ss'
74	Указание «TIMESTAMP» опционально Вычисление интервала (c указанием единиц времени: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND)	select ('YYYY-MM-DD hh:mm:ss' - 'YYYY-MM-DD hh:mm:ss') MONTH
75	Функция CAST	SELECT cast(int_column AS INT) FROM t
Выражение CASE
76	Простой оператор CASE	SELECT CASE WHEN 1 = 0 THEN 5 ELSE 7 END FROM t
77	Оператор CASE с условиями	SELECT CASE 1 WHEN 0 THEN 5 ELSE 7 END FROM t
78	NULLIF	SELECT nullif(int_column, 7) FROM t
79	COALESCE	SELECT coalesce(int_column,7) FROM t
80	Длинные идентификаторы	SELECT 1 AS A12345678901234567890123456789
81	Спецсимволы Unicode в идентификаторах	SELECT 1 AS Я12345678901234567890123456789
82	Спецсимволы Unicode в текстовых строках	SELECT U&'\6553'
83	Национальные символы	SELECT 'Я'
84	Скалярные значения подзапросов	SELECT int_column  FROM t WHERE int_column = (SELECT count(*) FROM t)
85	Расширенный предикат NULL	SELECT column FROM t WHERE row(int_column, int_column) IS NOT NULL

3.2. Примеры запросов с использованием SQL-синтаксиса, поддерживаемого ПОДД СМЭВ

Далее приведены примеры SQL-выражений Регламентированных SQL-запросов.

Необходимо учитывать, что возможность выполнения соответствующих синтаксических конструкций зависит от их поддержки со стороны ПО Витрины данных.

3.2.1. Запрос с использованием табличных выражений

with cte as (
select shortname, regioncode, oktmo from fias.addrobj
where formalname = ‘Москва’
)
select * from cte

Запросы с использованием базовых операторов

1. Запрос с использованием EXCEPT:

select oktmo from fias.addrobj
EXCEPT
select kod2 from oktmo.oktmo

2. Запрос с использованием INTERSECT:

select oktmo from fias.addrobj
INTERSECT
select kod2 from oktmo.oktmo

3. Запросы с использованием UNION:

select oktmo from fias.addrobj
UNION
select kod2 from oktmo.oktmo

4. Запросы с использованием ORDER BY:

select shortname, formalname from fias.addrobj
where aolevel = 1
order by formalname

select shortname, formalname from fias.addrobj
where aolevel = 1
order by formalname desc

5. Запрос использованием GROUP BY, HAVING:

select ao.regioncode, count(*) N from fias.addrobj ao
left join oktmo.oktmo o on o.kod2 = ao.oktmo
where o.kod2 is null
group by ao.regioncode
having COUNT(*) > 3000
order by N desc

3.2.2. Указание в запросе версии Витрины

select * from egrul.1.0.legalentity
select * from egrul.2.0.legalentity

Результат может содержать разное количество возвращаемых атрибутов в соответствии со структурой таблицы «legalentity» Витрины «egrul» версий 1.0 и 2.0.

3.2.3. Запросы с использованием JOIN

select le.short_name, rc.region_full_name from egrul.legalentity le
join egrul.region_codes rc on le.region_code = rc.region_code

SELECT ao.oktmo, o.name, o.kod
from fias.addrobj ao
JOIN oktmo.oktmo o on ao.oktmo = o.kod2
WHERE ao.offname = ‘Москва’ AND o.regionname = ‘город Москва’

select le.short_name, le.region_code, o.regionid, o.name, o.regionname from egrul.legalentity le
left join oktmo.oktmo o on cast(o.regionid as varchar) = le.region_code
where o.kod2 is null

select le.short_name, le.region_code, o.regionid, o.name, o.regionname from egrul.legalentity le
full join oktmo.oktmo o on cast(o.regionid as varchar) = le.region_code
where le.ogrn is null or o.kod2 is null

select le.short_name, le.region_code, o.regionid, o.name, o.regionname from egrul.legalentity le
right join oktmo.oktmo o on cast(o.regionid as varchar) = le.region_code
where le.ogrn is null

select le.short_name, rc.region_full_name from egrul.legalentity le
cross join egrul.region_codes rc
where rc.region_code = le.region_code

3.3. Выполнение регламентированных SQL-запросов

3.3.1. Правила выполнения

Регламентированный SQL-запрос выполняется аналогично выполнению любого запроса к Витрине Поставщика данных. Описание синтаксиса выполнения запроса приведено в Раздел 3.1 .

Эта операция может быть выполнена:

• с использованием REST-интерфейса в соответствии с Раздел 2.3.1.

• с использованием JDBC-интерфейса в соответствии с Раздел 2.3.3 .

Для выполнения необходимо составить SQL выражение, указав мнемонику Регламентированного SQL-запроса вместо таблицы. Формат Регламентированного SQL-запроса имеет вид:

select * from <мнемоника Витрины>.<версия Регламентированного SQL-запроса>.<мнемоника Регламентированного SQL-запроса>(<параметры>) <дополнительные функции (опционально)>

где:

• <мнемоника Витрины> – для простых запросов (к одной Витрине) задается мнемоника соответствующей Витрины. Для распределенного запроса вместо мнемоники Витрины указывается префикс «podd»;

• <версия Регламентированного SQL-запроса> – задается в формате «major.minor»;

• <мнемоника Регламентированного SQL-запроса> – мнемоника вызываемого Регламентированного SQL-запроса;

• <параметры> – указываются в скобках через запятую для Регламентированного SQL-запроса с параметрами, заданными в определении.

  Если выполняемый запрос не требует указания параметров, то скобки должны быть указаны пустыми. Если параметры не указаны в исходном запросе, но их значения по умолчанию присутствуют в загруженном определении, то они будут подставлены автоматически.

• <дополнительные функции (опционально)> – дополнительные функции для обработки вызываемого Регламентированного SQL-запроса (например, группировка или лимит).

Внимание

если запрос не является аналитическим и необходимо получение результата выполнения в реальном времени (запросы граждан на портале ЕПГУ, предоставление услуг в МФЦ и т.п.), то обязательно указание лимита (limit) с минимально возможным значением (максимально допустимое значение зависит от конфигурации и настроек Витрины Поставщика, по умолчанию равно 1000):

POST «https://<host>:<port>/query?async=false»

Accept-Version:1
Content-Type:application/x-www-form-urlencoded; encoding=utf-8

priority:NORMAL
timeout:60
sql: select * from fias.1.0.addrobj_view('001') limit 10

Для упрощенного вызова Регламентированного SQL-запроса без использования дополнительных функций обработки (без надстроек) доступен формат вызова:

• через REST-интерфейс в соответствии c Раздел 2.3.1.1.2 с соответствующей пометкой;

• через JDBC-интерфейс:

CALL <мнемоника Витрины>.<версия Регламентированного SQL-запроса>.<мнемоника Регламентированного SQL-запроса>(<параметры>)

Правила указания параметров:

1. Если параметры заданы в SQL-выражении Регламентированного SQL-запроса в виде «?», то при вызове они должны быть указаны в порядке, соответствующем определению Регламентированного SQL-запроса. Количество параметров при вызове должно соответствовать количеству параметров в определении Регламентированного SQL-запроса.

2. Для запроса, в SQL-выражении которого параметры заданы как именованные, доступен:

   1. Вызов с использованием мнемоники именованного параметра.

      Для этого необходимо задать значения всех именованных параметров, для которых не задано значение по умолчанию, в виде «<мнемоника параметра>=><значение параметра>» (Раздел 3.3.2).

      Мнемоника именованного параметра при вызове запроса должна соответствовать мнемонике именованного параметра в определении Регламентированного SQL-запроса.

      Использование именованных параметров позволяет при вызове задавать значения параметров в любом порядке и только один раз, независимо от количества мест, где этот параметр используется в определении Регламентированного SQL-запроса.

   2. Вызов без использования мнемоники именованного параметра.

      Для этого необходимо задать значения всех именованных параметров в порядке, соответствующем определению Регламентированного SQL-запроса.

3. Совместное использование именованных и неименованных параметров не допускается.

3.3.2. Примеры вызова и преобразования исходного запроса

1. Регламентированный SQL-запрос с параметрами без имени:

// Исходный запрос от Потребителя данных ПОДД СМЭВ:
select * from oktmo.1.0.oktmo_view(‘Московская область’,7)

// Определение Регламентированного SQL-запроса
select id, whenadd, name, regionname, settlementtypename FROM oktmo.1.0.oktmo where regionname = ? AND settlementtypeid = ?

// Пример преобразования исходного запроса в соответствии с загруженным определением Регламентированного SQL-запроса:
select id, whenadd, name, regionname, settlementtypename FROM oktmo.1.0.oktmo where regionname = ‘Московская область’ AND settlementtypeid = 7

2. Регламентированный SQL-запрос с именованными параметрами:

// Исходный запрос от Потребителя данных ПОДД СМЭВ (вызов через мнемонику параметра)
select * from <мнемоника Витрины>.<версия Регламентированного SQL-запроса>.<мнемоника Регламентированного SQL-запроса>(param=>'000', lim=>20)

// Исходный запрос от Потребителя данных ПОДД СМЭВ
select * from <мнемоника Витрины>.<версия Регламентированного SQL-запроса>.<мнемоника Регламентированного SQL-запроса>('000',20)

// Определение Регламентированного SQL-запроса
SELECT name, code FROM datamart.1.0.regions WHERE code=:param and id in (select id from :param) limit :lim

// Пример преобразования исходного запроса в соответствии с загруженным определением Регламентированного SQL-запроса:
SELECT name, code FROM datamart.1.0.regions WHERE code='000' and id in (select id from '000') limit 20

3. Регламентированный SQL-запрос с параметром типа TIMESTAMP:

// Исходный запрос от Потребителя данных ПОДД СМЭВ при подключении к Pulsar
select * from fias.1.0.addrobj_view('001', '000', TIMESTAMP '1970-01-01 00:00:00')

// Исходный запрос от Потребителя данных ПОДД СМЭВ при подключении к брокеру
CALL fias.1.0.addrobj_view('001', '000', TIMESTAMP '1970-01-01 00:00:00')

// Исходный запрос от Потребителя данных ПОДД СМЭВ при подключении к брокеру с отдельным блоком параметров
datamart: fias
mnemonic: addrobj_view
majorVersion: 1
minorVersion: 0
params:{ “type”: “STRING”, “value”:”001”},{ “type”: “STRING”, “value”:“002”},{ “type”: “TIMESTAMP”, “value”:“2007-12-03Т10:00:00.000Z”}

// Определение РЗ
SELECT oktmo, formalname, startdate, enddate FROM datamart.9.1.addrobj WHERE areacode=? and citycode=? and startdate<=?

4. Регламентированный SQL-запрос с параметром типа DATE:

// Исходный запрос от Потребителя данных ПОДД СМЭВ при подключении к Pulsar
select * from fias.1.0.addrobj_view('001', '000', '1970-01-01')

// Исходный запрос от Потребителя данных ПОДД СМЭВ при подключении к брокеру
CALL fias.1.0.addrobj_view('001', '000', '1970-01-01')

// Исходный запрос от Потребителя данных ПОДД СМЭВ при подключении к брокеру с отдельным блоком параметров
datamart: fias
mnemonic: addrobj_view
majorVersion: 1
minorVersion: 0
params:{ “type”: “STRING”, “value”:”001”},{ “type”: “STRING”, “value”:“002”},{ “type”: “DATE”, “value”:“2007-12-03”}

// Определение РЗ
SELECT oktmo, formalname, startdate, enddate FROM datamart.9.1.addrobj WHERE areacode=? and citycode=? and startdate>=?

5. Регламентированный SQL-запрос без параметров:

// Исходный запрос от Потребителя данных ПОДД СМЭВ:
select * from egrul.1.1.legalentity_view()

// Пример преобразования исходного запроса в соответствии с загруженным определением Регламентированного SQL-запроса:
select ogrn, short_name, inn, kpp, region_code from egrul.2.legalentity

6. Распределенный Регламентированный SQL-запрос:

// Исходный запрос от Потребителя данных ПОДД СМЭВ:
select * from podd.1.1.r_query(‘Москва’, 18)

// Пример преобразования исходного запроса в соответствии с загруженным определением Регламентированного SQL-запроса:
select ao.oktmo, o.name, o.kod from fias.1.0.addrobj ao LEFT JOIN oktmo.1.0.oktmo o on ao.oktmo = o.kod2 WHERE ao.offname= ‘Москва’ AND o.regionid = 18

3.3.3. Выполнение запроса с системным параметром

Системные параметры не описываются в определении Регламентированного SQL-запроса и могут быть заданы Потребителем для любого Регламентированного SQL-запроса при выполнении запроса.

Таблица 3.4 содержит поддерживаемые в ПОДД параметры.

Таблица 3.4 Поддерживаемые в ПОДД параметры

№	Мнемоника	Назначение
1	settings_for_system_time	Системный параметр для получения актуальных на заданный момент данных. Позволяет указывать при вызове запроса момент времени, на который требуется получить актуальные данные из Витрины.
2	settings_for_system_time_started	Системный параметр для получения результата выполнения запроса с данными, добавленными за указанный диапазон времени. Позволяет указывать при вызове запроса диапазон номеров дельт, за который требуется получить измененные данные
3	settings_for_system_time_finished	Системный параметр для получения результата выполнения запроса с данными, удаленными за указанный диапазон времени. Позволяет указывать при вызове запроса диапазон номеров дельт, за который требуется получить удаленные данные

Вызов Регламентированного SQL-запроса с системным параметром осуществляется аналогично вызову Регламентированного SQL-запроса с заданием значения для именованного параметра, в соответствии со следующими правилами:

1. Параметр задается в запросе только один раз в виде именованного параметра с соответствующей мнемоникой.

2. Значение параметра settings_for_system_time должно соответствовать формату YYYY-MM-DD hh:mm:ss.

3. Значение параметров settings_for_system_time_started и settings_for_system_time_finished должно соответствовать формату („int1,int2“). Значение int1 должно быть меньше, чем int2.

4. Задание параметра недоступно при вызове Регламентированного SQL-запроса без SQL-выражения.

5. Для простого запроса: для Витрины, к которой обращается Регламентированный SQL-запрос с данным параметром, должна быть задана настройка поддержки обработки данного параметра в соответствии с «Руководством администратора ПОДД СМЭВ[26]_».

6. Для распределенного запроса: для всех Витрин, к которым обращается Регламентированный SQL-запрос с данным параметром, должна быть задана настройка поддержки обработки данного параметра в соответствии с «Руководством администратора ПОДД СМЭВ[26]_».

7. В подзапросе в блоке FROM должна отсутствовать конструкция «FOR SYSTEM_TIME».

Пример вызова с параметром settings_for_system_time:

// вызываемый SQL-запрос (вариант 1)
select * from fias.1.0.addrobj_func(p1=>'000', p2=>'001', settings_for_system_time=>'YYYY-MM-DD hh:mm:ss')

// вызываемый SQL-запрос (вариант 2)
select * from fias.1.0.addrobj_func(settings_for_system_time=>?, p1=>?, p2=>?)
params: {"type": "TIMESTAMP", "value": "YYYY-MM-DD hh:mm:ss"},{"type": "STRING", "value": "001"},{"type": "STRING", "value": "002"}

// SQL-выражение Регламентированного SQL-запроса
SELECT <атрибуты> FROM <витрина>.<таблица> WHERE id=:p1 AND region=:p2

[26]

Размещено на портале ЕСКС – https://info.gosuslugi.ru/

Пример вызова с параметрами settings_for_system_time_started» и «settings_for_system_time_finished:

// вызываемый SQL-запрос (вариант 1)
select * from fias.1.0.addrobj_func(p1=>'000', p2=>'001', settings_for_system_time_started=>'122,124')
select * from fias.1.0.addrobj_func(p1=>'000', p2=>'001', settings_for_system_time_finished=>'122,124')

// вызываемый SQL-запрос без настроек через JDBC-интерфейс
call fias.1.0.addrobj_func(p1=>'000', p2=>'001', settings_for_system_time_started=>'122,124')

// вызываемый SQL-запрос с параметрами, передаваемыми отдельным блоком
select * from fias.1.0.addrobj_func(settings_for_system_time_finished=>?, p1=>?, p2=>?)
params: {"type": "STRING", "value": "122,124"},{"type": "STRING", "value": "001"},{"type": "STRING", "value": "002"}

// SQL-выражение РЗ
SELECT <атрибуты> FROM <витрина>.<таблица> WHERE id=:p1 AND region=:p2

3.3.4. Выполнение запросов с использованием табличных параметров, передаваемых Потребителем данных для обогащения

Выполнение запроса, включающего табличный параметр, осуществляется аналогично выполнению любого запроса к Витрине Поставщика данных. Описание синтаксиса выполнения запроса приведено в Раздел 3.1.

Для выполнения запроса с табличным параметром необходимо:

1. Определить структуру таблицы, указанной в табличном параметре.

2. Указать запрос, содержащий табличный параметр.

3. Указать источник данных для табличного параметра.

Эти операции могут быть выполнены с использованием REST или JDBC-интерфейса.

3.3.4.1. Запрос с использованием REST-интерфейса

При использовании REST-интерфейса данные собираются в CSV файл, который прикрепляется к запросу, содержащему информацию о структуре таблицы и SQL-выражение. Формат запроса приведен в Раздел 2.3.1.2.

В теле запроса передаются следующие параметры:

   sql:SELECT el.inn, er.region_name FROM @inns el LEFT JOIN egrul.2.region_codes er ON SUBSTRING(el.inn,1,2) = er.region_code
   priority:NORMAL
   tableParams:{“name”: “inns”, “columns”:[{“name”: “id”, “type”: “INTEGER”},{“name”: “inn”, “type”: “STRING”}]}
   inns:<l.csv>

где:

1. sql – текст для вызова Регламентированного SQL-запроса, содержащего табличные параметры;

2. tableParams – описание передаваемого файла с данными для табличного параметра, где:

   • name – табличный параметр;

   • columns – перечень названий столбцов и их типов, содержащихся в файле с данными для табличного параметра.

3. inns – файл с данными для табличного параметра, где:

   • inns – табличный параметр (выступает в качестве названия параметра запроса);

   • <1.csv> – файл в формате CSV (поддерживаемый формат), передаваемый в параметре запроса.

Пример CSV файла (разделитель – запятая):

1, 4345310593
2, 4311003795
3, 4345336320

3.3.4.2. Запрос с использованием JDBC-интерфейса

При использовании JDBC-интерфейса для описание передаваемой в качестве параметра таблицы и добавления данных расширяются функции драйвера JDBC. Для доступа к данным формируется итератор.

После передачи данных выполняется запрос, включающий табличный параметр в формате @имя_параметра.

Пример использования представлен в Раздел 2.3.3.1.

3.3.5. Получение двоичных объектов в результатах запроса

ПОДД СМЭВ предоставляет возможность получения в качестве результата выполнения Регламентированных SQL-запросов:

• скалярных параметров (значение которых представляет собой число, строку или дату);

• двоичных объектов (соответствующих файлам, размещенным на Витринах Потребителей данных).

Для получения в качестве ответа двоичных объектов необходимо выполнение следующих условий:

• Витрина Поставщика данных поддерживает тип данных «двоичный объект»;

• в метаданных Витрины Поставщика данных, зарегистрированной в ПОДД СМЭВ, для соответствующих атрибутов должен быть установлен тип данных «двоичный объект» (описание типов данных ПОДД СМЭВ приведено в Раздел 1.5.5).

Процесс выполнения такого SQL-запроса приведён на рисунке 14, описание процесса приведено далее.

1. ИС Потребителя передаёт SQL-запрос в Агент.

2. Далее приём, обработка и передача запроса:

   Агент Потребителя → Ядро ПОДД → Агент Поставщика → Витрина Поставщика осуществляется аналогично обычному информационному обмену с использованием Регламентированных SQL-запросов (Раздел 1.4.1).

3. Витрина данных осуществляет обработку запроса и возвращает ответ.

   В случае выполнения запроса, результат которого содержит атрибуты с типом данных «двоичный объект», в составе результата запроса атрибут может принимать одно из следующих возможных значений:

   • непосредственно сам двоичный объект;

   • уникальная ссылка на получение двоичного объекта с Витрины Поставщика данных (см. шаги Рисунок - 3.1).

   Решение о том какое значение будет передано (объект или ссылка на него принимается Витриной данных).

4. Далее ответ доставляется до ИС Потребителя аналогично обычному обмену (Раздел 1.4.1).

   Пример возврата двоичного объекта и ссылки на двоичный объект приведен в Раздел 2.3.1.1.3.

5. Если в результате выполнения запроса передана ссылка, то для получения двоичного объекта ИС Потребителя направляет запрос на получение вложения по ссылке, которую получил в ответе.

   Запрос двоичного объекта по ссылке считается отдельным обменом, идентификатор обмена (requestId) запроса по ссылке уникальный (в случае нескольких ссылок - уникальный для каждой из ссылок).

   Описание запроса на получение двоичного объекта по ссылке:

   • через REST-интерфейс приведено п Раздел 2.3.1.3;

   • через JDBC-интерфейс приведено далее.

6. Далее приём, обработка и передача запроса Агент Потребителя → Ядро ПОДД → Агент Поставщика → Витрина Поставщика осуществляется аналогично обычному обмену (Раздел 1.4.1). Для передачи запроса двоичного объекта на Витрину Поставщика используется специальный топик blob.rq.

7. В случае, если в теле запроса содержится ссылка на двоичный объект:

   • Адаптер PODD Витрины данных отправляет запрос в BLOB-адаптер на получение этого файла.

   • BLOB-адаптер, считывает ссылку на двоичный объект и обращается в Хранилище BLOB-объектов на стороне Ведомства.

   • После получения двоичного объекта, возвращает его в ПОДД.

   Для передачи ответа на запрос двоичного объекта от Витрины Поставщика используется специальные топики blob.rs и blob.err.

8. ПОДД СМЭВ передает ИС Потребителя данных двоичный объект (файл), организуя двоичный поток между Витриной Поставщика данных и ИС Потребителя данных.

   В процессе передачи Агент Поставщика данных разбивает поток данных на сегменты (чанки) и подписывает каждый сегмент подписью Поставщика данных. Перед передачей данных Потребителю данных подпись проверяется Ядром ПОДД СМЭВ.

Пример программного кода для JDBC-драйвера, реализующего получение двоичного объекта:

package dev.nsud.jdbc
import org.junit.jupiter.api.Test
import java.sql.Connection
import java.sql.DriverManager
import org.junit.jupiter.api.Assertions
import java.sql.SQLException

class Features {
    private getConnectionURI() {
        val host = System.getProperty(“agent.host”, “localhost”)
        val port = System.getProperty(“agent.port”, “8182”)
        return “jdbc:podd://$host:$port”
    }
    @Test
    fun `ожидается успешное соединение с базой данных`() {
        Assertions.assertDoesNotThrow { DriverManager.getConnection(getConnectionURI()) }
    }
    @Test
    fun `ожидается успешное получение бинарных данных `() {
        Assertions.assertDoesNotThrow {
            val expect = getExpectedBytes()
            val con = DriverManager.getConnection(getConnectionURI())
            val statement = con.createStatement()
            statement.executeQuery(“select binaryColumn from datamart.table where id=1”)
            val resultSet = statement.resultSet
            Assertions.assertEquals(expect, resultSet.getBlob(0))
        }
    }
}

Рисунок - 3.1 Информационный обмен при выполнении запроса с использованием ПОДД СМЭВ

3.3.6. Получение печатной формы в результатах поиска

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

Приём, обработка и передача запроса по пути Агент Потребителя → Ядро ПОДД → Агент Поставщика осуществляется аналогично обычному информационному обмену с использованием Регламентированных SQL-запросов (Раздел 1.4.1). Особенностью обработки запросов является добавление Агентом Поставщика алиаса сертификата в запрос, согласно настройкам, заданным в соответствии с Руководством администратора ПОДД СМЭВ [27], для последующей подписи печатной формы.

[27]

Размещен на портале ЕСКС – https://info.gosuslugi.ru/

3.4. Выполнение запросов к REST-сервису ИС Ответчика

Для выполнения запроса к REST-сервису ИС Ответчика необходимо:

1. Определить доступные REST-сервисы.

2. Составить URI запроса в соответствии со спецификацией OpenAPI зарегистрированного REST-сервиса ИС Ответчика, загруженной в ПОДД.

URI запроса формируется путём конкатенации мнемоники Агента ПОДД Ответчика, префикса в URL (basePath) соответствующего REST-сервиса ИС Ответчика и path операции. Сформированный запрос должен совпадать с запросом из спецификации OpenAPI.

Формат запроса для обмена с использованиям REST-сервиса ИС Ответчика имеет вид:

<HTTP-метод> <адрес>:<порт>/<systemMnemonic><basePath><path>

где:

• HTTP-метод – метод из поддерживаемых REST-сервисом;

• <адрес> – IP-адрес Агента ПОДД Инициатора;

• <порт> – порт для обращения Агента ПОДД Инициатора к Ядру ПОДД в соответствии с Руководством администратора ПОДД СМЭВ26;

• systemMnemonic – мнемоника Агента ПОДД Ответчика, на стороне которого развернут REST-сервис;

• basePath – префикс в URL соответствующего REST-сервиса ИС Ответчика;

• path – путь операции, указанный в спецификации OpenAPI соответствующего REST-сервиса ИС Ответчика.

Пример URI запроса соответствует примеру спецификации OpenAPI, приведенному в Раздел 1.5.5 данного документа:

GET 10.81.4.30:29164/agent-oktmo/region-service/region/123

Выполнение запросов осуществляется через REST-интерфейс Агента ПОДД СМЭВ (см. Раздел 2.3.1.3 данного документа).

3.5. Сквозная идентификация запросов

Для диагностики проблем возникающих в ходе информационных обменов через СМЭВ 4 в части отслеживания всей цепочки сообщений, возникающих в ходе информационного обмена обеспечена сквозная идентификация запросов.

Сквозная идентификация в СМЭВ 4 основывается на идентификаторах, приведённых в Таблица 3.5.

На Рисунок - 3.2, Рисунок - 3.3 и Рисунок - 3.4 приведён порядок возникновения этих идентификаторов.

Таблица 3.5 Виды идентификаторов СМЭВ4

№	Вид	Код	Источник идентификатора
1	Клиентский идентификатор (идентификатор клиента)	clientRequestId	Инициатор обмена
2	Идентификатор обмена (идентификатор процесса)	requestId	ПОДД при получении запроса на: информационный обмен (например, выполнение регламентированного SQL-запроса) процесс (например, регистрации подписки)
3	Идентификатор подзапроса ПОДД – Витрина	subRequestId	ПОДД
4	Идентификатор подзапроса Адаптер – Prostore	dataRequestId	Адаптер Витрины данных

Рисунок - 3.2 Сквозная идентификация запросов для информационного обмена с использованием регламентированных SQL-запросов

Рисунок - 3.3 Сквозная идентификация запросов для информационного обмена с использованием запросов к REST-сервису ИС Ответчика

Рисунок - 3.4 Сквозная идентификация запросов для информационного обмена с использованием Рассылок

Правила использования клиентского идентификатора

1. Клиентский идентификатор опционален.

   Исключение из правила – УВ явно добавил обязательное поле ClientRequestID в спецификацию OpenAPI для информационного обмена с использованием Регламентированных REST-запросов. В данном случае, валидация осуществляется в соответствии со спецификацией.

2. Клиентский идентификатор должен соответствовать стандарту UUID.

3. Ответственность за используемый формат несёт инициатор обмена. ПОДД не осуществляет проверку по формату и версиям UUID.

   Исключение из правила – УВ явно добавил поле ClientRequestID в спецификацию OpenAPI для информационного обмена с использованием Регламентированных REST-запросов. В данном случае, валидация осуществляется в соответствии со спецификацией.

4. Допустимо использовать один клиентский идентификатор для связывания нескольких запросов. ПОДД не осуществляет проверку на уникальность.

5. Способ передачи клиентского идентификатора приведён в Раздел 2.3.