Выпущена новая версия языка запросов 1QL.
Скачать дистрибутив и документацию можно здесь.
Данная статья является продолжением моего выступления на Infostart 2019 Inception. В этой статье я расскажу о развитии проекта и его новых возможностях. Для того, чтобы не нарушать права фирмы 1С на её торговый знак, проект переименован на DaJet.
Название DaJet (даджет) образовано от слов data (данные) и jet (реактивный).
Кроме этого является модификацией слова gadget (гаджет) и может быть интерпретировано как "приспособление для работы с данными".
Новые возможности.
1. Добавлена возможность обращаться в одном запросе не только к разным базам данным, расположенным на одном экземпляре SQL Server, но и к связанным (linked) серверам.
2. Реализован сервер web api, позволяющий переводить запросы в терминах 1С в запросы на T-SQL, а также выполнять эти запросы при помощи любого web api клиента.
3. Используется новый парсер языка запросов, поддерживающий практически полный синтаксис SQL Server 2005-2016. Это означает, что поддерживаются как DDL, так и DML инструкции. Также возможно использование DMV, табличных переменных, оконных функций, табличных указаний (хинтов) и т.д. и т.п.
4. Использование нового парсера в перспективе позволяет добавлять в синтаксис языка запросов собственные функции, собственную логику выполнения запроса, а также выполнять программный анализ запроса по заданным критериям.
Как это работает.
1. Выгружаем метаданные 1С при помощи специальной обработки в XML.
2. Настраиваем web сервер DaJet QL, который реализован на базе web сервера Kestrel (в основном описываем структуру каталогов сервера СУБД и указываем URL).
3. Выполняем запросы к web серверу DaJet QL при помощи web api.
В целях ознакомления с функционалом DaJet QL web api в качестве клиента можно использовать Postman. Коллекция вызовов web api для импорта в Postman прилагается.
Кроме этого, к данной статье прилагается демонстрационный web api клиент в виде обработки 1С.
Репозиторий DaJet QL на GitHub
Продолжение: "JSON в запросах DaJet QL".
Описание web api сервера DaJet QL.
GET metadata/use
Пример:
http://localhost:5000/metadata/use
Показывает текущий сервер и базу данных, которые используются web сервером DaJet QL в данный момент.
POST metadata/use/{server}/{database}
Пример:
http://localhost:5000/metadata/use/mysrv/mydb
Устанавливает текущий сервер и базу данных для web сервера DaJet QL, в контексте которых выполняются последующие запросы к СУБД. Аналог команды USE [database] SQL Server.
Вызов этого метода обязателен до того, как выполнять запросы.
POST script/translate
Пример:
http://localhost:5000/script/translate
Тело запроса:
{
"script": "SELECT Ссылка FROM Документ.ПоступлениеТоваровУслуг"
}
Тело ответа:
{
"script" : "SELECT _IDRRef FROM _Document123"
}
Этот метод позволяет посмотреть какой запрос SQL формирует web сервис DaJet QL перед его выполнением.
POST script/execute
Пример:
http://localhost:5000/script/execute
Тело запроса:
{
"script": "SELECT Ссылка FROM Документ.ПоступлениеТоваровУслуг"
}
Тело ответа:
[
{ "_IDRRef" : "{123:da142d98-b1e9-489a-11e5-cfdd69e2ce6c}" },
{ "_IDRRef" : "{123:da142d98-b1e9-489a-11e5-cfdd69e2ce73}" }
]
Метод выполняет запрос и возвращает результат его выполнения в формате JSON (массив структур записей).
Ограничения языка запросов.
1. Работа с составными типами данных 1С, содержащими простые типы данных, не поддерживается. Поддерживается только работа с составными ссылочными типами данных.
2. Обращение к реквизитам объектов через точку (операция разыменования) с автоматической генерацией LEFT JOIN в запросах SQL не поддерживается. Подробнее о работе с реквизитами ссылочного типа будет рассказано ниже.
Обращение к объектам в запросах.
1. Обращение к объекту в контексте текущей базы данных:
Документ.ПоступлениеТоваровУслуг
2. Обращение к табличной части объекта в контексте текущей базы данных:
Документ.ПоступлениеТоваровУслуг.Товары
3. Обращение к объекту из другой базы данных:
[torg_db].Документ.ПоступлениеТоваровУслуг
4. Обращение к табличной части объекта из другой базы данных:
[torg_db].Документ.ПоступлениеТоваровУслуг.Товары
5. Обращение к объекту связанного сервера:
[bi_server].[torg_db].Документ.ПоступлениеТоваровУслуг
6. Обращение к табличной части объекта связанного сервера:
[bi_server].[torg_db].Документ.[ПоступлениеТоваровУслуг+Товары]
В последнем случае использование шаблона вида [{Объект}+{ТЧ}] обязательно, так как парсер языка запросов умеет работать только с идентификаторами таблиц, имеющими 4 составных части. Использование квадратных скобок и символа "+" также обязательно.
При этом можно также обращаться к таблицам СУБД по их настоящим именам, заданным на уровне СУБД, например, так: SELECT _IDRRef FROM _Document123.
Это даёт возможность обращения в одном запросе к таблицам других СУБД, выгрузки данных во внешние источники или их загрузки из них. Например, это может выглядеть так:
SELECT
РТУ.СуммаДокумента AS СуммаРеализации,
CO.OrderSum AS СуммаЗаказа
FROM
Документ.РеализацияТоваровУслуг AS РТУ
LEFT JOIN [orders_db].CustomerOrders AS CO
ON РТУ.Номер = CO.DocNumber
Обращение к ссылочным реквизитам в запросах.
Язык запросов DaJet QL работает с ссылочными типами данных не так как 1С. Для работы с такими внутренними значениями ссылки как код типа данных объекта (TRef) и уникальным идентификатором объекта (RRef)в языке запросов DaJet QL на уровне парсера реализована работа с такими специализированными свойствами ссылки как uuid, type и TYPE.
Например выполним следующий запрос:
SELECT TOP 1
Ссылка,
Ссылка.uuid,
Ссылка.type
FROM
Документ.ПоступлениеТоваровУслуг
В результате мы получим следующий SQL запрос, используя web метод script/translate:
SELECT TOP 1
(0x0000007B + _IDRRef), /* binary(20) */
_IDRRef, /* binary(16) */
0x0000007B /* binary(4) */
FROM
_Document123
Таким образом мы видим, что свойство uuid возвращает уникальный идентификатор ссылки, что соответствует полю таблицы СУБД, которое хранит это значение.
Свойство type возвращает код типа объекта, который, в том числе, можно найти в названии таблицы объекта. В данном случае это число 123. Для однозначных реквизитов парсер подставит константу, а для составных ссылочных типов наименование поля таблицы СУБД, в котором хранится это значение.
Специальное свойство TYPE ссылки всегда содержит константу 0x08, что соответствует значению ссылочного типа данных в структуре хранения значений составных типов в таблицах СУБД. Это свойство необходимо исключительно для выполнения соединений между таблицами, так как поле, имеющее суффикс _TYPE, на уровне СУБД может входить в индекс. Более подробно об этом рассказано ниже.
Функция TYPEOF.
Язык запросов DaJet QL реализует специальную функцию TYPEOF для получения кода типа объекта аналогично функции ССЫЛКА в языке запросов 1С.
Выполним следующий запрос:
SELECT TOP 1
Ссылка.type,
TYPEOF(Документ.ПоступлениеТоваровУслуг)
FROM
Документ.ПоступлениеТоваровУслуг
Получим следующий код SQL:
SELECT TOP 1
0x0000007B,
0x0000007B
FROM
_Document123
Эта функция нужна также, как и специальное свойство TYPE (см. выше), для получения кода типа объекта при соединении с полями составного типа или таблицами других баз данных. Чтобы понять зачем это нужно, предлагаю рассмотреть два практических примера ниже.
Соединение по реквизиту составного типа.
Вариант № 1 (используем специальное свойство type).
-- Запрос на языке DaJet QL
SELECT TOP 1
ФЛ.Наименование AS ФИО,
БСК.НомерСчета AS НомерСчета
FROM
Справочник.ФизическиеЛица AS ФЛ
INNER JOIN Справочник.БанковскиеСчетаКонтрагентов AS БСК
ON ФЛ.Ссылка.uuid = БСК.Владелец.uuid
AND ФЛ.Ссылка.type = БСК.Владелец.type
AND ФЛ.Ссылка.TYPE = БСК.Владелец.TYPE
-- Запрос на T_SQL
SELECT TOP 1
ФЛ._Description AS ФИО,
БСК._Fld2205 AS НомерСчета
FROM
_Reference477 AS ФЛ
INNER JOIN _Reference41 AS БСК
ON ФЛ._IDRRef = БСК._OwnerID_RRRef
AND 0x000001DD = БСК._OwnerID_RTRef
AND 0x08 = БСК._OwnerID_TYPE;
Вариант № 2 (используем функцию TYPEOF).
-- Запрос на DaJet QL
SELECT TOP 1
ФЛ.Наименование AS ФИО,
БСК.НомерСчета AS НомерСчета
FROM
Справочник.ФизическиеЛица AS ФЛ
INNER JOIN Справочник.БанковскиеСчетаКонтрагентов AS БСК
ON ФЛ.Ссылка.uuid = БСК.Владелец.uuid
AND TYPEOF(Справочник.ФизическиеЛица) = БСК.Владелец.type
AND 0x08 = БСК.Владелец.TYPE
-- Запрос на T_SQL
SELECT TOP 1
ФЛ._Description AS ФИО,
БСК._Fld2205 AS НомерСчета
FROM
_Reference477 AS ФЛ
INNER JOIN _Reference41 AS БСК
ON ФЛ._IDRRef = БСК._OwnerID_RRRef
AND 0x000001DD = БСК._OwnerID_RTRef
AND 0x08 = БСК._OwnerID_TYPE;
В данном случае справочник "БанковскиеСчетаКонтрагентов" имеет два справочника владельца: "Контрагенты" и "ФизическиеЛица". На уровне СУБД реквизит "Владелец" состоит из трёх полей. Чтобы использовать индекс по всем этим полям, необходимо соединяться вышеуказанным способом. В таких случаях можно использовать оба варианта: с использованием специального свойства type и функции TYPEOF.
Сверка движений документов из разных баз данных.
Предположим, что у нас есть две базы данных: торговля и бухгалтерия. Торговля периодически выгружает в бухгалтерию документы установки цен. Нам необходимо выполнить сверку наличия выгруженных из торговли в бухгалтерию документов, а также соответствие цен в обеих базах. Предположим, что документы синхронизируются по ссылкам. Базы данных называются trade и accounting. Запрос может выглядеть так:
SELECT
УТДок.Дата AS ТоргДата,
УТДок.Номер AS ТоргНомер,
УТЦН.Цена AS ТоргЦена,
БПДок.Дата AS БухДата,
БПДок.Номер AS БухНомер,
БПЦН.Цена AS БухЦена
FROM
trade.РегистрСведений.ЦеныНоменклатуры AS УТЦН
LEFT JOIN accounting.РегистрСведений.ЦеныНоменклатуры AS БПЦН
ON УТЦН.Регистратор.uuid = БПЦН.Регистратор.uuid
AND УТЦН.Регистратор.type = TYPEOF(trade.Документ.УстановкаЦенНоменклатуры)
AND БПЦН.Регистратор.type = TYPEOF(accounting.Документ.УстановкаЦенНоменклатуры)
LEFT JOIN trade.Документ.УстановкаЦенНоменклатуры AS УТДок
ON УТЦН.Регистратор.uuid = УТДок.Ссылка.uuid
AND УТЦН.Регистратор.type = УТДок.Ссылка.type
LEFT JOIN accounting.Документ.УстановкаЦенНоменклатуры AS БПДок
ON БПЦН.Регистратор.uuid = БПДок.Ссылка.uuid
AND БПЦН.Регистратор.type = БПДок.Ссылка.type
В данном запросе предикат первого JOIN’а выглядит следующим образом:
ON УТЦН.Регистратор.uuid = БПЦН.Регистратор.uuid
AND УТЦН.Регистратор.type = TYPEOF(trade.Документ.УстановкаЦенНоменклатуры)
AND БПЦН.Регистратор.type = TYPEOF(accounting.Документ.УстановкаЦенНоменклатуры)
Соединение по полю _RecorderRRef (Регистратор.uuid) не вызывает вопросов, так как синхронизация документов выполняется как раз таки по внутреннему идентификатору объектов. Однако, соединение по полю _RecorderTRef (Регистратор.type) "в лоб" невозможно, так как документы "УстановкаЦенНоменклатуры" в разных базах имеют разные значения кодов типов объектов. Именно в таком случае пригождается функция TYPEOF. Она помогает установить соответствие между документами из разных баз данных. Эдакая конвертация данных "на лету".
Ещё несколько примеров использования в статье "JSON в запросах DaJet QL".
Настройка web сервера DaJet QL.
1. Распаковать установочный архив DaJet QL в любой каталог.
2. Создать в этом каталоге каталог metadata.
3. Внутри каталога metadata создать каталог c именем сервера СУБД.
4. Выгрузить метаданные 1С обработкой ConfigurationExporter83.epf
в файл xml. Назвать этот файл также как называется база данных
СУБД. Положить этот файл в созданный ранее каталог сервера.
5. В файле настроек web сервера DaJet QL appsettings.json в секции
MetadataSettings повторить структуру каталогов и баз данных
СУБД. Файл настроек, чтобы долго не искать, можно открыть при
помощи show-web-server-settings.bat.
6. При необходимости настроить URL, по которому будет запускаться
web сервер DaJet QL, в том же файле настроек appsettings.json.
7. Web cервер DaJet QL готов к работе. Его можно запустить при помощи
файла run-web-server.bat.
Для облегчения понимания как настроить web сервер архив установки содержит уже настроенный каталог для тестового окружения автора статьи. То же самое касается файла appsettings.json.
Системные требования.
1. Web сервер DaJet QL скомпилирован для платформы win-x64 и linux-x64.
2. Для подключения к SQL Server web сервер DaJet QL использует
встроенную аутентификацию Windows. Следовательно запускать его
нужно под учётной записью, которая имеет доступ к серверу СУБД
и нужным базам данных.
Для linux версии необходимо в Линуксе подключить протокол kerberos для аутентификации на SQL Server. Как это сделать я сам не знаю, но судя по отзывам пользователей DaJet QL, которые пробовали запускаться на Линукс, им это удалось.
3. Требуется установка .NET Core 3.1.3
.NET качаем здесь: https://dotnet.microsoft.com/download
Вместо заключения.
Использовать web api сервера DaJet QL можно при помощи Postman. Для этого в каталоге утилит лежит файл "OneCSharp Scripting.postman_collection.json". Просто импортируйте эту коллекцию в свой Postman и всё — можно гонять запросы.
Кроме этого для 1С есть обработка OneCSharpUI.epf. Это реализация клиента web api DaJet QL. Для работы обработки требуется платформа 1С:Предприятие 8.3.6 и выше.
Обработка платная: приобретая её, вы помогаете развитию проекта. Заранее благодарю всех за эту помощь!
