Использование СМЭВ4 ========================== .. _sql_syntax: SQL-синтаксис ----------------- Регламентированные SQL-запросы, регистрируемые в СМЭВ4, должны соответствовать следующему синтаксису. .. _tab_sql_syntax: .. table:: SQL-синтаксис +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+------------------------------+ | № | Описание | Пример запроса | Минимальная версия ПО | | | | +-------------+----------------+ | | | | Агент СМЭВ4 | Витрина данных | +=======+===============================================+=======================================================================================+=============+================+ | 1 | **Числовые типы данных** | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 1.1 | Типы данных INTEGER и SMALLINT | ``SELECT CAST(1 AS INT)`` | 3.0.0 | 1.12.0 | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 1.2 | Типы данных REAL, DOUBLE PRECISION и FLOAT | ``SELECT CAST(1 AS FLOAT)`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 1.3 | Типы данных DECIMAL и NUMERIC | ``SELECT CAST(1 AS NUMERIC)`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 1.4 | Арифметические операторы | ``SELECT 10+1, 9-2, 8*3, 7/2`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 1.5 | Числовые сравнения | ``SELECT 1 WHERE 1 < 2`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 1.6 | Неявные преобразования между числовыми типами | ``SELECT int_column FROM t WHERE int_column = 1.00`` | | | | | данных | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 2 | **Символьные типы данных** | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 2.1 | Тип данных CHARACTER | ``SELECT CAST(‘1111111111111111111111111111111111111111111’ AS CHAR)`` | 3.0.0 | 1.12.0 | | | | | | | | | Длина по умолчанию 30 | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 2.2 | Тип данных CHARACTER VARYING | ``SELECT CAST(‘1111111111111111111111111111111111111111111’ AS VARCHAR)`` | | | | | | | | | | | Длина по умолчанию 30 | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 2.3 | Символьные строки | ``SELECT ''`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 2.4 | Функция CHARACTER_LENGTH | ``SELECT character_length(char_column) FROM t`` | | | | | | | | | | | убирает завершающие пробелы из значений | | | | | | CHARACTER перед подсчётом символов | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 2.5 | Функция OCTET_LENGTH | ``SELECT octet_length(char_column) FROM t`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 2.6 | Функция SUBSTRING | ``SELECT substring(char_column FROM 1 FOR 1) FROM t`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 2.7 | Конкатенация символьных строк | ``SELECT ‘a’ || ‘b’ FROM t`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 2.8 | Функции UPPER и LOWER | ``SELECT upper(‘a’),lower(‘B’) FROM t`` | | | | | | | | | | | | ``SELECT int_column FROM t WHERE int_column > (SELECT DISTINCT (int_column) FROM t)`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 2.9 | Функция TRIM | ``SELECT trim(‘a ‘) FROM t`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 2.10 | Неявные преобразования между типами | ``SELECT char_column FROM t WHERE char_column > varchar_column`` | | | | | символьных строк | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 2.11 | Функция POSITION | ``SELECT position(‘A’ IN char_column) FROM t`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 2.12 | Сравнения символов | ``SELECT char_column FROM t WHERE char_column > ‘a’`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 2.13 | Функция LISTAGG для объединения значений из | ``SELECT department_id, LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY | 3.18.0 | 1.12.0 | | | нескольких строк в одну строку | employee_name) AS employees_list FROM employees GROUP BY department_id;`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 3 | **Идентификаторы** | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 3.1 | Идентификаторы с разделителями | ``SELECT 1 AS «t47»`` | 3.0.0 | 1.12.0 | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 3.2 | Идентификаторы в нижнем регистре | ``SELECT 1 AS t48`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 3.3 | Завершающее подчёркивание | ``SELECT 1 AS t49_`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 4 | **Базовое определение запросов** | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 4.1 | SELECT DISTINCT | ``SELECT DISTINCT int_column FROM t`` | 3.0.0 | 1.12.0 | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 4.2 | Предложение GROUP BY | ``SELECT DISTINCT int_column FROM t GROUP BY int_column`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 4.3 | GROUP BY может содержать колонки не из | ``SELECT DISTINCT char_column FROM t GROUP BY lower(char_column)`` | | | | | <списка выборки> | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 4.4 | Элементы списка выборки могут | ``SELECT int_column AS K FROM t ORDER BY K`` | | | | | переименовываться | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 4.5 | Предложение HAVING | ``SELECT count(*) FROM t HAVING count(*) > 0`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 4.6 | Корреляционные имена в предложении FROM | ``SELECT K.column FROM t AS K`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 4.7 | Переименование колонок в предложении FROM | ``SELECT column FROM t AS x(q, c)`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 5 | **Базовые предикаты и условия поиска** | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 5.1 | Предикат сравнения | ``SELECT column FROM t WHERE 0 = 0`` | 3.0.0 | 1.12.0 | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 5.2 | Предикат BETWEEN | ``SELECT column FROM t WHERE ‘ ‘ BETWEEN ‘’ AND ‘’`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 5.3 | Предикат IN со списком значений | ``SELECT column FROM t WHERE char_column IN (‘a’, upper(‘a’))`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 5.4 | Предикат LIKE | ``SELECT column FROM t WHERE char_column LIKE ‘_’`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 5.5 | Предложение ESCAPE в предикате LIKE | ``SELECT column FROM t WHERE ‘abc’ LIKE ‘abcX_’ ESCAPE ‘X’`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 5.6 | Предикат NULL | ``SELECT column FROM t WHERE char_column IS NOT NULL`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 5.7 | Предикаты количественного сравнения | ``SELECT column FROM t WHERE char_column = ANY (SELECT char_column FROM t)`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 5.8 | Предикат EXISTS | ``SELECT column FROM t WHERE NOT EXISTS (SELECT char_column FROM t)`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 5.9 | Подзапросы в предикате сравнения | ``SELECT column FROM t WHERE int_column > (SELECT max (int_column) FROM t)`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 5.10 | Подзапросы в предикате IN | ``SELECT column FROM t WHERE char_column IN (SELECT char_column FROM t)`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 5.11 | Подзапросы в предикате количественного | ``SELECT column FROM t WHERE char_column >= ALL(SELECT char_column FROM t)`` | | | | | сравнения | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 5.12 | Коррелирующие подзапросы | ``SELECT column FROM t WHERE int_column = (SELECT int_column FROM t2 WHERE t2.char_ | | | | | | column = t.char_column)`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 5.13 | Условие поиска | ``SELECT column FROM t WHERE 0 <> 0 OR ‘a’ < ‘b’ AND int_column IS NOT NULL`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 6 | **Простые выражения с запросами** | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 6.1 | Табличный оператор UNION DISTINCT | ``SELECT column FROM t UNION DISTINCT SELECT column1 FROM t`` | 3.0.0 | 1.12.0 | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 6.2 | Табличный оператор UNION ALL | ``SELECT column FROM t UNION ALL SELECT column1 FROM t`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 6.3 | Табличный оператор EXCEPT DISTINCT | ``SELECT column FROM t EXCEPT DISTINCT SELECT column1 FROM t`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 6.4 | Табличный оператор INTERSECT | ``SELECT column FROM t INTERSECT SELECT column1 FROM t`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 6.5 | Колонки, объединяемые табличными операторами, | ``SELECT char_column FROM t UNION SELECT 5`` | | | | | могут иметь разные типы данных | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 6.6 | Табличные операторы в подзапросах | ``SELECT column FROM t WHERE ‘a’ IN (SELECT char_column FROM t UNION SELECT | | | | | | char_column FROM t)`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 6.7 | C использованием обобщенных табличных | ``with cte as (SELECT shortname, regioncode, oktmo from fias.addrobj | | | | | выражений | WHERE formalname = ‘Москва’) SELECT * FROM cte`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 7 | **Функции множеств** | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 7.1 | AVG | ``SELECT avg(int_column) FROM t`` | 3.0.0 | 1.12.0 | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 7.2 | COUNT | ``SELECT count(int_column) FROM t`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 7.3 | MAX | ``SELECT max(int_column) FROM t`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 7.4 | MIN | ``SELECT min(int_column) FROM t`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 7.5 | SUM | ``SELECT sum(int_column) FROM t`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 7.6 | Дополнение ALL | ``SELECT sum(ALL int_column) FROM t`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 7.7 | Дополнение DISTINCT | ``SELECT sum(DISTINCT int_column) FROM t`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 7.8 | Оператор SELECT, возвращающий одну строку | ``SELECT count(*) FROM t`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 8 | **Базовая поддержка курсоров** | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 8.1 | Колонки ORDER BY, отсутствующие в списке | ``SELECT int_column FROM t ORDER BY char_column`` | 3.0.0 | 1.12.0 | | | выборки | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 8.2 | Выражения значений в предложении ORDER BY | ``SELECT int_column FROM t ORDER BY -int_column`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 8.3 | Поддержка NULL (NULL вместо значений) | ``SELECT int_column FROM t WHERE int_column IS NULL`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 9 | **Базовое соединение таблиц** | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 9.1 | Внутреннее соединение (но не обязательно с | ``SELECT a.int_column FROM t a JOIN t b ON a.int_column = b.int_column`` | 3.0.0 | 1.12.0 | | | ключевым словом INNER) | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 9.2 | Ключевое слово INNER | ``SELECT a.int_column FROM t a INNER JOIN t b ON a.int_column = b.int_column`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 9.3 | 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`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 9.4 | 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`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 9.5 | Внешние соединения могут быть вложенными | ``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`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 9.6 | Внутренняя таблица с левой или правой стороны | ``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`` | | | | | во внутреннем соединении | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 9.7 | Поддерживаются все операторы сравнения (а не | ``SELECT column FROM t WHERE 0 = 1 OR 0 > 1 OR 0 < 1 OR 0 <> 1`` | | | | | только =) | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 10 | **Базовая поддержка даты и времени** | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 10.1 | Тип данных DATE (включая поддержку строк | ``SELECT ‘2012-07-12’ AS "DATE"`` | 3.0.0 | 1.12.0 | | | DATE) | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 10.2 | Тип данных TIME (включая поддержку строк | ``SELECT ‘1:2:3’ AS "TIME"`` | | | | | TIME) с точностью до секунд как минимум с 0 | | | | | | знаков после запятой | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 10.3 | Тип данных TIMESTAMP (включая поддержку строк | ``SELECT ‘2012-07-12’ AS "TIMESTAMP"`` | | | | | TIMESTAMP) | | | | | | | | | | | | с точностью до секунд как минимум | | | | | | с 0 и 6 знаками после запятой | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 10.4 | Предикаты сравнения с типами данных DATE, | ``SELECT column FROM t3 WHERE date_column = date_column AND time_column = time_column | | | | | TIME и TIMESTAMP | AND timestamp_column = timestamp_column`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 10.5 | Явное приведение (CAST) между типами | ``SELECT cast(date_column AS VARCHAR(10)) FROM t3`` | | | | | даты/времени и типами символьных строк | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 10.6 | CURRENT_DATE | ``SELECT current_date FROM t`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 10.7 | LOCALTIME | ``SELECT localtime FROM t`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 10.8 | LOCALTIMESTAMP | ``SELECT localtimestamp FROM t`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 11 | **Расширенная поддержка даты и времени** | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 11.1 | Вычисление интервала | ``select ('YYYY-MM-DD hh:mm:ss' - 'YYYY-MM-DD hh:mm:ss') MONTH`` | 3.0.0 | 1.12.0 | | | (c указанием единиц времени: YEAR, MONTH, | | | | | | DAY, HOUR, MINUTE, SECOND) | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 11.2 | FOR SYSTEM_TIME AS OF TIMESTAMP | ``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'`` | | | | | Указание «TIMESTAMP» опционально | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 11.3 | FOR SYSTEM_TIME AS OF DELTA_NUM | ``SELECT column FROM t FOR SYSTEM_TIME AS OF DELTA_NUM `` | 3.18.0 | 1.12.0 | | | (запрос данных, актуальных на указанную | | | | | | дельту) | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 11.4 | FOR SYSTEM_TIME FINISHED IN | ``SELECT column FROM t FOR SYSTEM_TIME FINISHED IN (, )`` | | | | | (запрос данных, удаленных в диапазоне дельт) | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 11.5 | FOR SYSTEM_TIME FINISHED TS | ``SELECT column FROM t FOR SYSTEM_TIME FINISHED TS (, )`` | | | | | (запрос данных, удаленных в период времени) | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 11.6 | FOR SYSTEM_TIME STARTED IN | ``SELECT column FROM t FOR SYSTEM_TIME STARTED IN (, )`` | | | | | (запрос данных, добавленных и/или измененных | | | | | | в диапазоне дельт) | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 11.7 | FOR SYSTEM_TIME STARTED TS | ``SELECT column FROM t FOR SYSTEM_TIME STARTED TS (, )`` | | | | | (запрос данных, добавленных и/или измененных | | | | | | в период времени) | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 11.8 | FOR SYSTEM_TIME AS OF CN | ``SELECT column FROM t FOR SYSTEM_TIME AS OF CN `` | 3.18.0 | 2.0.0 | | | (запрос данных, актуальных на указанный | | | | | | номер операции записи) | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 11.9 | FOR SYSTEM_TIME STARTED CN | ``SELECT column FROM t FOR SYSTEM_TIME STARTED CN (, )`` | | | | | (запрос данных, добавленных и/или измененных | | | | | | в диапазоне операций) | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 11.10 | FOR SYSTEM_TIME FINISHED CN | ``SELECT column FROM t FOR SYSTEM_TIME FINISHED CN (, )`` | | | | | (запрос данных, удаленных в диапазоне | | | | | | операций) | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 11.11 | FOR SYSTEM_TIME | ``SELECT column FROM t FOR SYSTEM_TIME AS OF LATEST_UNCOMMITTED_DELTA`` | | | | | AS OF LATEST_UNCOMMITTED_DELTA | | | | | | (запрос актуальных данных, | | | | | | включая изменения открытой дельты) | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 11.12 | FOR SYSTEM_TIME RAW | ``SELECT column FROM t FOR SYSTEM_TIME RAW`` | 3.23.0 | 2.0.0 | | | (запрос данных, содержащиеся | | | | | | в физических таблицах логической сущности) | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 12 | **Функция CAST** | ``SELECT cast(int_column AS INT) FROM t`` | 3.0.0 | 1.12.0 | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 13 | **Выражение CASE** | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 13.1 | Простой оператор CASE | ``SELECT CASE WHEN 1 = 0 THEN 5 ELSE 7 END FROM t`` | 3.0.0 | 1.12.0 | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 13.2 | Оператор CASE с условиями | ``SELECT CASE 1 WHEN 0 THEN 5 ELSE 7 END FROM t`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 13.3 | NULLIF | ``SELECT nullif(int_column, 7) FROM t`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 13.4 | COALESCE | ``SELECT coalesce(int_column,7) FROM t`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 13.5 | Длинные идентификаторы | ``SELECT 1 AS A12345678901234567890123456789`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 13.6 | Спецсимволы Unicode в идентификаторах | ``SELECT 1 AS Я12345678901234567890123456789`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 13.7 | Спецсимволы Unicode в текстовых строках | ``SELECT U&'\6553'`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 13.8 | Национальные символы | ``SELECT 'Я'`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 13.9 | Скалярные значения подзапросов | ``SELECT int_column FROM t WHERE int_column = (SELECT count(*) FROM t)`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 13.10 | Расширенный предикат NULL | ``SELECT column FROM t WHERE row(int_column, int_column) IS NOT NULL`` | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 14 | **Функции по управлению текстовым поиском** | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ | 14.1 | Функция TO_TSVECTOR подготовки текстовых | ``SELECT TO_TSVECTOR('russian', 'иванов иван')`` | 3.16.0 | 1.16.0 | | | значения, выбранных запросом | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 14.2 | Функция PLAINTO_TSQUERY подготовки | ``SELECT PLAINTO_TSQUERY('russian', 'иванов иван')`` | | | | | неформатированного текста без сохранения | | | | | | порядка слов | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 14.3 | Функция TO_TSQUERY подготовки слов | ``SELECT TO_TSQUERY('russian', 'иванов & иван');`` | | | | | разделенных операторами & (AND), ``|`` | | | | | | (OR), ! (NOT), и (или) <-> (FOLLOWED BY) | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 14.4 | Функция PHRASETO_TSQUERY подготовки | ``SELECT PHRASETO_TSQUERY('russian', 'иванов иван')`` | | | | | неформатированного текста с сохранением | | | | | | порядка слов | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 14.5 | Функция WEBSEARCH_TO_TSQUERY подготовки | ``SELECT WEBSEARCH_TO_TSQUERY('russian', 'иванов иван OR петр')`` | | | | | неформатированного текста с сохранением | | | | | | порядка слов или без него. Поддерживает | | | | | | операторы OR и - (NOT) | | | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+ + + | 14.6 | Оператор @@ сравнения текстового вектора | ``SELECT column FROM t WHERE (TO_TSVECTOR('russian', lastname) @@ | | | | | с подготовленным или неподготовленным текстом | PLAINTO_TSQUERY('russian', 'иванов иван'))`` | | | | | | | | | | | | ``SELECT column FROM t WHERE (PLAINTO_TSQUERY('russian', 'иванов иван' @@ | | | | | | TO_TSVECTOR('russian', lastname)))`` | | | | | | | | | | | | ``SELECT column FROM t WHERE (lastname @@ PLAINTO_TSQUERY('russian', 'иванов иван'))``| | | +-------+-----------------------------------------------+---------------------------------------------------------------------------------------+-------------+----------------+ .. _executing_regulated_queries: Выполнение регламентированных SQL-запросов -------------------------------------------- .. _executing_rules: Правила выполнения ~~~~~~~~~~~~~~~~~~~~~ Вызов Регламентированного SQL-запроса может быть выполнен с использованием: - REST-интерфейса в соответствии с :numref:`consumer_sql_rest_interface`. - JDBC-интерфейса в соответствии с :numref:`jdbc_interface`. Возможность указания надстроек при вызове регламентированного SQL-запроса ограничена. Поэтому рекомендуется сразу использовать методы упрощенного вызова: - через REST-интерфейс в соответствии c :numref:`sync_broker_connect`; - через JDBC-интерфейс: .. code-block:: CALL <мнемоника Витрины>.<версия Регламентированного SQL-запроса>.<мнемоника Регламентированного SQL-запроса>(<параметры>) Методы чувствительны к регистру, необходимо использовать нижний регистр. При необходимости использования методов с возможностью использования надстроек необходимо составить SQL выражение, указав мнемонику Регламентированного SQL-запроса вместо таблицы. Формат Регламентированного SQL-запроса имеет вид: .. code-block:: select * from <мнемоника Витрины>.<версия Регламентированного SQL-запроса>.<мнемоника Регламентированного SQL-запроса>(<параметры>) <надстройки (опционально)> где: - <мнемоника Витрины> – для простых запросов (к одной Витрине) задается мнемоника соответствующей Витрины. Для распределенного запроса вместо мнемоники Витрины указывается префикс «podd»; - <версия Регламентированного SQL-запроса> – задается в формате «major.minor»; - <мнемоника Регламентированного SQL-запроса> – мнемоника вызываемого Регламентированного SQL-запроса; - <параметры> – указываются в скобках через запятую для Регламентированного SQL-запроса с параметрами, заданными в определении. Если выполняемый запрос не требует указания параметров, то скобки должны быть указаны пустыми. Если параметры не указаны в исходном запросе, но их значения по умолчанию присутствуют в загруженном определении, то они будут подставлены автоматически. - <надстройки (опционально)> – дополнительные условия фильтрации и операций над получаемыми данными (например, order by, limit, where, перечисление полей вместо * и любые другие запросы отличные от select * from <мнемоника Витрины>. <версия Регламентированного SQL-запроса (опционально)>.<мнемоника Регламентированного SQL-запроса>(<параметры>)) .. attention:: При наличии потребности использовать такие надстройки рекомендуется обратиться к поставщику данных для добавления их в SQL-выражение. Данный метод останется доступным только для выполнения произвольных SQL-запросов к собственным Витринам данных для тестирования. **Правила указания параметров:** 1. Если параметры заданы в SQL-выражении Регламентированного SQL-запроса в виде «?», то при вызове они должны быть указаны в порядке, соответствующем определению Регламентированного SQL-запроса. Количество параметров при вызове должно соответствовать количеству параметров в определении Регламентированного SQL-запроса. 2. Для запроса, в SQL-выражении которого параметры заданы как именованные, доступны: 1. Вызов с использованием мнемоники именованного параметра. Для этого необходимо задать значения всех именованных параметров, для которых не задано значение по умолчанию, в виде "<мнемоника параметра>=><значение параметра>" (:numref:`original_query_transform`). при вызове РЗ через /regulated-query также возможна передача параметров с мнемоникой: ``params={"name": "param1","type": "STRING", "value": ""}`` Мнемоника именованного параметра при вызове запроса должна соответствовать мнемонике именованного параметра в определении Регламентированного SQL-запроса. Использование именованных параметров позволяет при вызове задавать значения параметров в любом порядке и только один раз, независимо от количества мест, где этот параметр используется в определении Регламентированного SQL-запроса. 2. Вызов без использования мнемоники именованного параметра. Для этого необходимо задать значения всех именованных параметров в порядке, соответствующем определению Регламентированного SQL-запроса. 3. Совместное использование именованных и неименованных параметров не допускается. .. _original_query_transform: Примеры вызова и преобразования исходного запроса ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1. Регламентированный SQL-запрос с параметрами без имени: .. code-block:: // Исходный запрос от Потребителя данных СМЭВ4: 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-запрос с именованными параметрами: .. code-block:: // Исходный запрос от Потребителя данных СМЭВ4 (вызов через мнемонику параметра) select * from <мнемоника Витрины>.<версия Регламентированного SQL-запроса>.<мнемоника Регламентированного SQL-запроса>(param=>'000', lim=>20) // Исходный запрос от Потребителя данных СМЭВ4 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: .. code-block:: // Исходный запрос от Потребителя данных СМЭВ4 select * from fias.1.0.addrobj_view('001', '000', TIMESTAMP '1970-01-01 00:00:00') // Исходный запрос от Потребителя данных СМЭВ4 CALL fias.1.0.addrobj_view('001', '000', TIMESTAMP '1970-01-01 00:00:00') // Исходный запрос от Потребителя данных СМЭВ4 с отдельным блоком параметров 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: .. code-block:: // Исходный запрос от Потребителя данных СМЭВ4 select * from fias.1.0.addrobj_view('001', '000', '1970-01-01') // Исходный запрос от Потребителя данных СМЭВ4 CALL fias.1.0.addrobj_view('001', '000', '1970-01-01') // Исходный запрос от Потребителя данных СМЭВ4 с отдельным блоком параметров 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-запрос без параметров: .. code-block:: // Исходный запрос от Потребителя данных СМЭВ4: select * from egrul.1.1.legalentity_view() // Пример преобразования исходного запроса в соответствии с загруженным определением Регламентированного SQL-запроса: select ogrn, short_name, inn, kpp, region_code from egrul.2.legalentity 6. Распределенный Регламентированный SQL-запрос: .. code-block:: // Исходный запрос от Потребителя данных СМЭВ4: 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 .. _system_param_query: Выполнение запроса с системным параметром ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Системные параметры не описываются в определении Регламентированного SQL-запроса и могут быть заданы Потребителем для Регламентированного SQL-запроса при выполнении запроса. :numref:`tab_system_param_query` содержит поддерживаемые в СМЭВ4 параметры. .. _tab_system_param_query: .. table:: Поддерживаемые в СМЭВ4 параметры +---+------------------------------------------------+-------------------------------------------------------------------------------------+---------------------------------+ | № | Мнемоника | Назначение | Минимальная версия Агента СМЭВ4 | | | | | на стороне Потребителя данных | +===+================================================+=====================================================================================+=================================+ | 1 | settings_for_system_time | Системный параметр для получения актуальных на заданный момент данных. | 3.0.0 | | | | | | | | | Позволяет указывать при вызове запроса момент времени, на который требуется | | | | | получить актуальные данные из Витрины. | | +---+------------------------------------------------+-------------------------------------------------------------------------------------+---------------------------------+ | 2 | settings_for_system_time_started | Системный параметр для получения результата выполнения запроса с данными, | 3.18.0 | | | | добавленными за указанный диапазон времени. | | | | | | | | | | Позволяет указывать при вызове запроса диапазон номеров дельт, за который требуется | | | | | получить измененные данные | | +---+------------------------------------------------+-------------------------------------------------------------------------------------+ + | 3 | settings_for_system_time_finished | Системный параметр для получения результата выполнения запроса с данными, | | | | | удаленными за указанный диапазон времени. | | | | | | | | | | Позволяет указывать при вызове запроса диапазон номеров дельт, за который требуется | | | | | получить удаленные данные | | +---+------------------------------------------------+-------------------------------------------------------------------------------------+ + | 4 | settings_for_system_time_cn | Системный параметр для получения данных на указанный номер операции | | +---+------------------------------------------------+-------------------------------------------------------------------------------------+ + | 5 | settings_for_system_time_started_cn | Системный параметр для получения результата выполнения запроса с данными, | | | | | добавленными за указанный диапазон номеров операций. | | +---+------------------------------------------------+-------------------------------------------------------------------------------------+ + | 6 | settings_for_system_time_finished_cn | Системный параметр для получения результата выполнения запроса с данными, | | | | | удаленными за указанный диапазон номеров операций. | | +---+------------------------------------------------+-------------------------------------------------------------------------------------+---------------------------------+ Вызов Регламентированного SQL-запроса с системным параметром осуществляется аналогично вызову Регламентированного SQL-запроса с заданием значения для именованного параметра, в соответствии со следующими правилами (данные, подходящие под условия РЗ, которые были и добавлены, и удалены в переданном диапазоне ``settings_for_system_time_started``/ ``settings_for_system_time_finished``, не попадают в результат запроса): 1. Параметр задается в запросе только один раз в виде именованного параметра с соответствующей мнемоникой. 2. Значение параметра ``settings_for_system_time`` должно соответствовать формату ``YYYY-MM-DD hh:mm:ss``. 3. Значение параметров ``settings_for_system_time_started`` и ``settings_for_system_time_finished`` должно соответствовать формату: 3.1. При передаче диапазона дельт - (``int1``, ``int2``). Значение ``int1`` должно быть меньше, чем ``int2``. 3.2. При передаче диапазона timestamp – (``YYYY-MM-DD hh:mm:ss``, ``YYYY-MM-DD hh:mm:ss``)/. Значение1 в переданном диапазоне должно быть меньше значения2 4. Задание параметра недоступно при вызове Регламентированного SQL-запроса без SQL-выражения. 5. Для Витрин, к которым обращается Регламентированный SQL-запрос с данным параметром, должна быть задана настройка поддержки обработки данного параметра в соответствии с :numref:`datamart_profile`. 6. В подзапросе в блоке FROM должна отсутствовать конструкция «FOR SYSTEM_TIME». Пример вызова с параметром ``settings_for_system_time``: .. code-block:: // вызываемый 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 Пример вызова с параметрами ``settings_for_system_time_started`` и ``settings_for_system_time_finished``: .. code-block:: // вызываемый 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 .. _consumer_tp_query: Выполнение запросов с использованием табличных параметров, передаваемых Потребителем данных для обогащения ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Выполнение запроса, включающего табличный параметр, осуществляется аналогично выполнению любого запроса к Витрине Поставщика данных. Описание синтаксиса выполнения запроса приведено в :numref:`sql_syntax`. Для выполнения запроса с табличным параметром необходимо: 1. Определить структуру таблицы, указанной в табличном параметре. 2. Указать запрос, содержащий табличный параметр. 3. Указать источник данных для табличного параметра. Эти операции могут быть выполнены с использованием REST или JDBC-интерфейса. Данная функциональность доступна при выполнении следующих условий: - на стороне Потребителя установлен Агент СМЭВ4 версии не ниже 3.0.0 - на стороне Поставщика: - установлена Витрина версии 1.12.0-1.17.0 и Агент не ниже 3.0.0 - установлена Витрина версии не ниже 2.0.0 и Агент не ниже 3.20.0 .. _rest_interface_request: Запрос с использованием REST-интерфейса ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ При использовании REST-интерфейса данные собираются в CSV файл, который прикрепляется к запросу, содержащему информацию о структуре таблицы и SQL-выражение. Формат запроса приведен в :numref:`consumer_sql_async_request`. В теле запроса передаются следующие параметры: .. code-block:: 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: где: 1. sql – текст для вызова Регламентированного SQL-запроса, содержащего табличные параметры; 2. tableParams – описание передаваемого файла с данными для табличного параметра, где: - name – табличный параметр; - columns – перечень названий столбцов и их типов, содержащихся в файле с данными для табличного параметра. 3. inns – файл с данными для табличного параметра, где: - inns – табличный параметр (выступает в качестве названия параметра запроса); - <1.csv> – файл в формате CSV (поддерживаемый формат), передаваемый в параметре запроса. Пример CSV файла (разделитель – запятая): .. code-block:: 1, 4345310593 2, 4311003795 3, 4345336320 .. _jdbc_interface_query: Запрос с использованием JDBC-интерфейса ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ При использовании JDBC-интерфейса для описания передаваемой в качестве параметра таблицы и добавления данных расширяются функции драйвера JDBC. Для доступа к данным формируется итератор. После передачи данных выполняется запрос, включающий табличный параметр в формате ``@имя_параметра``. Пример использования представлен в :numref:`jdbc_driver`. .. _binary_objects_output: Получение двоичных объектов в результатах запроса ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ СМЭВ4 предоставляет возможность получения в качестве результата выполнения Регламентированных SQL-запросов: - скалярных параметров (значение которых представляет собой число, строку или дату); - двоичных объектов (соответствующих файлам, размещенным на Витринах Потребителей данных). Для получения в качестве ответа двоичных объектов необходимо выполнение следующих условий: - Витрина Поставщика данных поддерживает тип данных «двоичный объект»; - в метаданных Витрины Поставщика данных, зарегистрированной в СМЭВ4, для соответствующих атрибутов должен быть установлен тип данных «двоичный объект» (описание типов данных СМЭВ4 приведено в :numref:`respondent_rest_services`). Описание процесса выполнения такого SQL-запроса приведено далее. 1. ИС Потребителя передаёт SQL-запрос в Агент СМЭВ4. 2. Далее приём, обработка и передача запроса: Агент Потребителя данных → Ядро СМЭВ4 → Агент Поставщика данных → Витрина Поставщика данных осуществляется аналогично обычному информационному обмену с использованием Регламентированных SQL-запросов (:numref:`rz_exchange`). 3. Витрина данных осуществляет обработку запроса и возвращает ответ. В случае выполнения запроса, результат которого содержит атрибуты с типом данных «двоичный объект», в составе результата запроса атрибут может принимать одно из следующих возможных значений: - непосредственно сам двоичный объект; - уникальная ссылка на получение двоичного объекта с Витрины Поставщика данных (см. шаги :numref:`img_blob_output`). Решение о том, какое значение будет передано, (объект или ссылка на него) принимается Витриной данных. 4. Далее ответ доставляется до ИС Потребителя аналогично обычному обмену (:numref:`rz_exchange`). Пример возврата двоичного объекта и ссылки на двоичный объект приведен в :numref:`sync_response`. 5. Если в результате выполнения запроса передана ссылка, то для получения двоичного объекта ИС Потребителя направляет запрос на получение вложения по ссылке, которую получил в ответе. Запрос двоичного объекта по ссылке считается отдельным обменом, идентификатор обмена (requestId) запроса по ссылке уникальный (в случае нескольких ссылок - уникальный для каждой из ссылок). Описание запроса на получение двоичного объекта по ссылке: - через REST-интерфейс приведено в :numref:`consumer_tp_request`; - через JDBC-интерфейс приведено далее. .. note:: Ссылки для получения двоичного объекта имеют время жизни, которое определяется таймаутом запроса на получение ссылок (см. пункт 1) плюс значением настройки времени жизни Ядра СМЭВ4 (10 минут). 6. Далее приём, обработка и передача запроса Агент Потребителя данных → Ядро СМЭВ4 → Агент Поставщика данных → Витрина Поставщика данных осуществляется аналогично обычному обмену (:numref:`rz_exchange`). Для передачи запроса двоичного объекта на Витрину Поставщика используется специальный топик ``blob.rq``. 7. В случае если в теле запроса содержится ссылка на двоичный объект: - Адаптер PODD Витрины данных отправляет запрос в BLOB-адаптер на получение этого файла. - BLOB-адаптер считывает ссылку на двоичный объект и обращается в Хранилище BLOB-объектов на стороне Ведомства. - После получения двоичного объекта возвращает его в СМЭВ4. Для передачи ответа на запрос двоичного объекта от Витрины Поставщика данных используются специальные топики ``blob.rs`` и ``blob.err``. 8. СМЭВ4 передает ИС Потребителя данных двоичный объект (файл), организуя двоичный поток между Витриной Поставщика данных и ИС Потребителя данных. В процессе передачи Агент Поставщика данных разбивает поток данных на сегменты (чанки) и подписывает каждый сегмент подписью Поставщика данных. Перед передачей данных Потребителю данных подпись проверяется Ядром СМЭВ4. Пример программного кода для JDBC-драйвера, реализующего получение двоичного объекта: .. code-block:: 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)) } } } .. _img_blob_output: .. figure:: img/img_blob_output.png :align: center :alt: Информационный обмен при выполнении запроса с использованием СМЭВ4 Информационный обмен при выполнении запроса с использованием СМЭВ4 .. _printable_form_output: Получение печатной формы в результатах запроса ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Запросы для получения печатной формы в результатах запроса не содержат SQL-выражения в определении Регламентированного SQL-запроса и являются процедурами, выполняемыми на стороне Витрины. В запросе обязательно передается параметр с указанием типа или нескольких типов формируемых документов: - xml – формируется файл, содержащий отчет и присоединенную подпись; - pdf – формируется файл, содержащий отчет без подписи; - xml_detached_sig – формируется файл xml-отчета и файл с открепленной подписью; - pdf_sig - формируется файл pdf -отчета и файл с открепленной подписью. .. note:: Перечень поддерживаемых типов документов зависит от настроек Сервиса Формирования документов типового ПО Витрина данных, выполненных Поставщиком. Приём, обработка и передача запроса по пути Агент Потребителя данных → Ядро СМЭВ4 → Агент Поставщика данных осуществляется аналогично обычному информационному обмену с использованием Регламентированных SQL-запросов (:numref:`rz_exchange`). Пример вызова запроса печатной формы: .. code-block:: select * from <мнемоника Витрины>.<версия SQL-РЗ>.<мнемоника SQL-РЗ>('pdf_sig, xml', '<значение опционального параметра>') .. _full_text_search_output: Выполнение запроса с текстовым поиском ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Функция текстового поиска позволяет искать данные внутри текстовых полей таблиц по ключевым словам или фразам. Поиск выполняется на уровне Витрины данных. Описание поддерживаемых функций и операторов приведено в :numref:`sql_syntax`. Подробное описание данного функционала приведено в `Информационно-методическом справочнике ядра типового ПО витрин данных НСУД — Prostore `_. Для Витрин c версией компонента query-execution (Prostore) ниже 7.1, к которым обращается Регламентированный SQL-запрос, должна быть задана настройка subQueryParamSupport=NOT_SUPPORTED в соответствии с :numref:`datamart_profile`. Функция текстового поиска доступна для любых Регламентированных запросов типа «SQL-запрос». Пример вызова с текстовым поиском: .. code-block:: // Исходный запрос от Потребителя данных СМЭВ4: select * from demo_view_test.5.0.passenger(‘Иван’) // Пример преобразования исходного запроса в соответствии с загруженным определением Регламентированного SQL-запроса: select code, id, lastname, firstname, middlename, birthday from demo_view_test.5.0.passenger where (to_tsvector('russian', lastname) @@ plainto_tsquery('russian', 'Иван')) .. _rest_service_output: Выполнение запросов к REST-сервису ИС Ответчика --------------------------------------------------- Для выполнения запроса к REST-сервису ИС Ответчика необходимо: 1. Определить доступные REST-сервисы. 2. Добавить к запросу опциональные заголовки: - клиентский идентификатор, передаётся в опциональном заголовке ClientRequestID (тип string), см. :numref:`request_identification` данного документа; - для передачи мнемоники Инициатора в REST-сервис ответчика в спецификации OpenAPI REST-сервиса Ответчика должен присутствовать обязательный заголовок X-PODD-CLIENT-SYSTEM-MNEMONIC; - пользовательский таймаут (предельное время ожидания выполнения запроса), передается в опциональном заголовке Request-Timeout. Значение указывается в секундах, максимальное значение 24 часа. В случае отсутствия параметра в запросе используется таймаут, заданный в конфигурации Агента СМЭВ4 (по умолчанию 20 секунд). 3. Составить URI запроса в соответствии со спецификацией OpenAPI зарегистрированного REST-сервиса ИС Ответчика, загруженной в СМЭВ4. URI запроса формируется путём конкатенации мнемоники Агента Ответчика, префикса в URL (basePath) соответствующего REST-сервиса ИС Ответчика и path операции. Сформированный запрос должен совпадать с запросом из спецификации OpenAPI. Формат запроса для обмена с использованием REST-сервиса ИС Ответчика имеет вид: .. code-block:: <адрес>:<порт>/ где: - HTTP-метод – метод из поддерживаемых REST-сервисом; - <адрес> – IP-адрес Агента Инициатора; - <порт> – порт для обращения Агента Инициатора к Ядру СМЭВ4 в соответствии с «Руководством администратора СМЭВ4»; - systemMnemonic – мнемоника Агента Ответчика, на стороне которого развернут REST-сервис; - basePath – префикс в URL соответствующего REST-сервиса ИС Ответчика; - path – путь операции, указанный в спецификации OpenAPI соответствующего REST-сервиса ИС Ответчика. Пример URI запроса соответствует примеру спецификации OpenAPI, приведенному в :numref:`respondent_rest_services` данного документа: .. code-block:: GET 10.81.4.30:29164/agent-oktmo/region-service/api/v1.0/letters/{mailId}/{date}/pdf Выполнение запросов осуществляется через REST-интерфейс Агента СМЭВ4 (см. :numref:`respondent_rest_interface` данного документа). .. _request_identification: Сквозная идентификация запросов ----------------------------------- Для диагностики проблем, возникающих в ходе информационных обменов через СМЭВ4 в части отслеживания всей цепочки сообщений, возникающих в ходе информационного обмена, обеспечена сквозная идентификация запросов. Данная функциональность доступна для Потребителей, на стороне которых установлен Агент СМЭВ4 версии не ниже 3.5.0. Сквозная идентификация в СМЭВ4 основывается на идентификаторах, приведённых в :numref:`tab_identifiers_types`. На :numref:`img_sql_request_identification`, :numref:`img_rest_request_identification` и :numref:`img_distribution_request_identification` приведён порядок возникновения этих идентификаторов. .. _tab_identifiers_types: .. table:: Виды идентификаторов СМЭВ4 +---+------------------------------+--------------------------+------------------------------------------------+ | № | Вид | Код | Источник идентификатора | +===+==============================+==========================+================================================+ | 1 | Клиентский идентификатор | clientRequestId | Инициатор обмена | | | | | | | | (идентификатор клиента) | | | +---+------------------------------+--------------------------+------------------------------------------------+ | 2 | Идентификатор обмена | requestId | СМЭВ4 при получении запроса на: | | | | | | | | (идентификатор процесса) | | - информационный обмен (например, | | | | | выполнение регламентированного SQL-запроса) | | | | | | | | | | - процесс (например, регистрации подписки) | | | | | | | | | | Витрина данных: | | | | | | | | | | - если является инициатором обмена (например, | | | | | уведомление о наличии новой дельты) | | | | | | | | | | - если происходит манипуляции данными без | | | | | участия СМЭВ4 (например, загрузка в таблицу) | +---+------------------------------+--------------------------+------------------------------------------------+ | 3 | Идентификатор подзапроса | subRequestId | СМЭВ4 | | | СМЭВ4 – Витрина | | | +---+------------------------------+--------------------------+------------------------------------------------+ | 4 | Идентификатор подзапроса | dataRequestId | Адаптер Витрины | | | Адаптер – Prostore | | | +---+------------------------------+--------------------------+------------------------------------------------+ .. _img_sql_request_identification: .. figure:: img/img_sql_request_identification.png :align: center :alt: Сквозная идентификация запросов для информационного обмена с использованием регламентированных SQL-запросов Сквозная идентификация запросов для информационного обмена с использованием регламентированных SQL-запросов .. _img_rest_request_identification: .. figure:: img/img_rest_request_identification.png :align: center :alt: Сквозная идентификация запросов для информационного обмена с использованием запросов к REST-сервису ИС Ответчика Сквозная идентификация запросов для информационного обмена с использованием запросов к REST-сервису ИС Ответчика .. _img_distribution_request_identification: .. figure:: img/img_distribution_request_identification.png :align: center :alt: Сквозная идентификация запросов для информационного обмена с использованием Рассылок Сквозная идентификация запросов для информационного обмена с использованием Рассылок **Правила использования клиентского идентификатора** 1. Клиентский идентификатор опционален. *Исключение из правила* – УВ явно добавил обязательное поле ClientRequestID в спецификацию OpenAPI для информационного обмена с использованием Регламентированных REST-запросов. В данном случае валидация осуществляется в соответствии со спецификацией. 2. Клиентский идентификатор должен соответствовать стандарту UUID. 3. Ответственность за используемый формат несёт инициатор обмена. СМЭВ4 не осуществляет проверку по формату и версиям UUID. Исключение из правила – УВ явно добавил поле ClientRequestID в спецификацию OpenAPI для информационного обмена с использованием Регламентированных REST-запросов. В данном случае, валидация осуществляется в соответствии со спецификацией. 4. Допустимо использовать один клиентский идентификатор для связывания нескольких запросов. СМЭВ4 не осуществляет проверку на уникальность. 5. Способ передачи клиентского идентификатора приведён в :numref:`consumer_protocol`. .. _limiting_requests: Лимитирование регламентированных запросов Потребителя к информационным ресурсам Поставщика -------------------------------------------------------------------------------------------- .. _limiting: Лимиты ~~~~~~ Для защиты Поставщиков от избыточных запросов Потребителей обеспечена возможность лимитирования запросов к Поставщику данных от конкретного Потребителя данных для информационных обменов с использованием регламентированных запросов. Данная функциональность доступна для Поставщиков, на стороне которых установлен Агент СМЭВ4 версии не ниже 3.10.0. Лимит может быть установлен на следующие информационные ресурсы Поставщика: - на конкретный Регламентированный SQL-запрос; - на все Регламентированные SQL-запросы к конкретной Витрине; - на конкретный Регламентированный REST-запрос (спецификацию OpenAPI ИС Поставщика); - общий на все Регламентированные SQL и REST-запросы от ИС Потребителя к ИС Поставщика. Лимит может быть следующих типов: - на Количество запросов; - на Размер запросов; - на Размер ответов. При создании лимита указывается его значение (количество или размер) и период (у которого два назначения – период контроля соблюдения значения и период блокировки). .. _limiting_conditions: Условия для добавления лимитов ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1. В СМЭВ4 должны быть зарегистрированы ИС Поставщика и ИС Потребителя; 2. Если лимит задаётся на Витрину в СМЭВ4 должны быть выполнены условия: - зарегистрирована Витрина; - Витрина связана с ИС Поставщика; 3. Если лимит задаётся на конкретный Регламентированный SQL-запрос, в СМЭВ4 должны быть: - зарегистрирована Витрина; - Витрина связана с ИС Поставщика; - выданы права ИС Потребителя на Регламентированный SQL-запрос. 4. Если лимит задаётся на конкретную спецификацию OpenAPI в СМЭВ4 должны быть: - зарегистрирована спецификация OpenAPI; - выданы права ИС Инициатора на спецификацию OpenAPI. 5. При выставлении лимитов недопустимо совмещение: - общего на все Регламентированные SQL и REST-запросы ИС Потребителя к ИС Поставщика лимита и лимита на другие ресурсы; - лимита на Витрину и лимита на Регламентированный SQL-запрос к этой Витрине. 6. Лимит на конкретный Регламентированный SQL-запрос ограничивает запросы Потребителя по всем версиям этого запроса. 7. Лимит на все Регламентированные SQL-запросы к конкретной Витрине ограничивает запросы Потребителя по всем версиям модели этой Витрины. 8. При установке лимита на Витрину и ИС также могут учитываться произвольные SQL-запросы. Реализация исключения таких запросов из процесса лимитирования не предусмотрена, так как права доступа на выполнение произвольных SQL-запросов предоставляются автоматически и только владельцам Витрин на их собственные Витрины. .. _blocking: Блокировки ~~~~~~~~~~ В случае превышения лимитов, заданных Поставщиком, СМЭВ4 блокирует Потребителю доступ к соответствующему информационному ресурсу. Например, если для ИС Потребителя указано два лимита к двум Регламентированным SQL-запросам и заблокирован доступ к одному из них, она может продолжить пользоваться другим доступным запросом. При получении запроса от заблокированного Потребителя СМЭВ4 возвращает ответ с соответствующей ошибкой. Блокировка снимается: - при истечении времени блокировки; - при удалении соответствующего лимита; - при редактировании соответствующего лимита (любых атрибутов, кроме наименования); - при получении запроса на принудительное снятие блокировки. .. _blocking_limit_manage: Управление лимитами и блокировками ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Управление лимитами и блокировками доступно из ЛК УВ. .. _limiting_algorithm: Алгоритм лимитирования ~~~~~~~~~~~~~~~~~~~~~~ Лимитирование осуществляется в соответствии со следующим алгоритмом (:numref:`img_limiting_algorithm`): 1. СМЭВ4 формирует окна наблюдения от 1 января 1970 года 00:00:00.000 в соответствии с указанным значением периода лимита. 2. СМЭВ4 хранит в памяти значение счётчика предыдущего и текущего окна наблюдения. После разблокировки значения счётчиков обнуляются. 3. СМЭВ4 вычисляет счётчик, инкрементируя значение при каждом полученном запросе. 4. СМЭВ4 принимает решение о блокировке на основании имеющихся счётчиков: - Если значения предыдущего окна наблюдения нет, то берётся значение инкрементированного счётчика текущего окна. - Если полученное значение меньше значения лимита, то запрос будет обработан. - Если значение предыдущего окна наблюдения есть, то СМЭВ4 вычисляет коэффициенты наложения окна наблюдения на интервалы по формуле: .. code-block:: K = N + M * R где: - N - количество запросов в текущем интервале (с учетом полученного), - M - количество запросов на предыдущем интервале, - R - коэффициент наложения на предыдущий интервал (0...1). - Если полученное значение меньше значения лимита, то запрос будет обработан. Пример: .. code-block:: Установлен лимит: 4 запроса в минуту С 0 до 0:59 - пришло 5 запросов, В 1:30 - пришёл ещё один запрос результат вычисления: - N = 1, - M = 5, - R = (90 - 60) / 60 = 0.5 K = 1 + 5 * 0.5 = 3.5 меньше предельного, запрос будет обработан. .. _img_limiting_algorithm: .. figure:: img/img_limiting_algorithm.png :align: center :alt: Алгоритм лимитирования запросов Алгоритм лимитирования запросов