Однопальцевое преобразование "одинэсного" запроса в запрос на SQL v 1.0

Описание работы

Внешний вид обработки достаточно прост: в левой части помещается текст на встроенном языке 1С. В правой части результат. В центре управляющие кнопки.

Требования к тексту на языке 1С:

 - Он обязательно должен открываться конструктором запросов. Без этого преобразование невозможно т.к парсинг текста происходит средствами этого механизма.

-  Запрос обязательно должен быть отформатирован при помощи конструктора запросов. 

Описания команд:

1. При нажатии кнопки происходит непосредственное преобразование запроса на SQL

2. Открывается конструктор запросов

3. Открывается форма задания параметров запроса

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

Возможности и "фишки" обработки.

1. Преобразование параметров 

Обработка имеет возможность задать параметры запроса и преобразовать их в SQL. Внешний вид формы задания представлен на рисунке. После указания значений можно нажать кнопку "Заполнить параметры SQL". В таком случае они подставятся в результат. 

Например, для запроса 

ВЫБРАТЬ ПЕРВЫЕ 50
    Контрагенты.Наименование КАК Наименование,
    ВложенныйЗапрос.РУ КАК РУ
ИЗ
    Справочник.Контрагенты КАК Контрагенты
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБРАТЬ ПЕРВЫЕ 150
            РеализацияУслуг.Ссылка КАК РУ,
            РеализацияУслуг.Контрагент КАК Контрагент
        ИЗ
            Документ.РеализацияУслуг КАК РеализацияУслуг
        ГДЕ
            РеализацияУслуг.Дата < &Дата) КАК ВложенныйЗапрос
        ПО (Контрагенты.Ссылка = ВложенныйЗапрос.Контрагент)
ГДЕ
    Контрагенты.Ссылка = &Ссылка

СГРУППИРОВАТЬ ПО
    Контрагенты.Наименование,
    ВложенныйЗапрос.РУ

Результат преобразования будет:

SELECT TOP 50
Контрагенты._Description Наименование,
ВложенныйЗапрос.РУ РУ
FROM
_Reference35 Контрагенты
INNER JOIN (SELECT TOP 150
РеализацияУслуг._IDRRef РУ,
РеализацияУслуг._Fld1114RRef Контрагент
FROM
_Document1109 РеализацияУслуг

WHERE
РеализацияУслуг._Date_Time < {ts '2021-04-16 12:00:00'}) ВложенныйЗапрос
ON Контрагенты._IDRRef = ВложенныйЗапрос.Контрагент
WHERE
Контрагенты._IDRRef = CAST(0xA277CD71A26B4BEA4F311172C8623727  AS binary(16))

GROUP BY
Контрагенты._Description,
ВложенныйЗапрос.РУ

2. Преобразование вложенных запросов

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

ВЫБРАТЬ
    ВложенныйЗапрос1.Подразделение КАК Подразделение,
    ВложенныйЗапрос1.Период КАК Период,
    ВложенныйЗапрос1.Должность КАК Должность
ИЗ
    (ВЫБРАТЬ
        ВложенныйЗапрос.Подразделение КАК Подразделение,
        ВложенныйЗапрос.Период КАК Период,
        ОтветственныеЛица.Должность КАК Должность
    ИЗ
        (ВЫБРАТЬ
            ОтветственныеЛица.Подразделение КАК Подразделение,
            МАКСИМУМ(ОтветственныеЛица.Период) КАК Период
        ИЗ
            РегистрСведений.ОтветственныеЛица КАК ОтветственныеЛица
        ГДЕ
            ОтветственныеЛица.Период <= &Период
        
        СГРУППИРОВАТЬ ПО
            ОтветственныеЛица.Подразделение) КАК ВложенныйЗапрос
            ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ОтветственныеЛица КАК ОтветственныеЛица
            ПО ВложенныйЗапрос.Период = ОтветственныеЛица.Период
                И ВложенныйЗапрос.Подразделение = ОтветственныеЛица.Подразделение) КАК ВложенныйЗапрос1

будет преобразован в 
 

SELECT
ВложенныйЗапрос1.Подразделение Подразделение,
ВложенныйЗапрос1.Период Период,
ВложенныйЗапрос1.Должность Должность

(SELECT
ВложенныйЗапрос.Подразделение Подразделение,
ВложенныйЗапрос.Период Период,
ОтветственныеЛица._Fld7656RRef Должность
FROM
(SELECT
ОтветственныеЛица._Fld7654RRef Подразделение,
MAX(ОтветственныеЛица._Period) Период
FROM
_InfoRg7653 ОтветственныеЛица

WHERE
ОтветственныеЛица._Period <= {ts '2021-04-15 12:00:00'}

GROUP BY
ОтветственныеЛица._Fld7654RRef) ВложенныйЗапрос
INNER JOIN _InfoRg7653 ОтветственныеЛица
ON ВложенныйЗапрос.Период = ОтветственныеЛица._Period
AND ВложенныйЗапрос.Подразделение = ОтветственныеЛица._Fld7654RRef) ВложенныйЗапрос1

2. Разыменование полей "через точку"

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

ВЫБРАТЬ
    Контрагенты.Родитель.Автор.Код КАК РодительАвторКод,
    Контрагенты.Наименование КАК Наименование
ИЗ
    Справочник.Контрагенты КАК Контрагенты

Преобразуется сначала в 

ВЫБРАТЬ
    КонтрагентыРодительКонтрагенты.Автор.Код КАК РодительАвторКод,
    Контрагенты.Наименование КАК Наименование
ИЗ
    Справочник.Контрагенты КАК Контрагенты
        ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Контрагенты КАК КонтрагентыРодительКонтрагенты
        ПО (Контрагенты.Родитель = КонтрагентыРодительКонтрагенты.Ссылка)

А при повторном нажатии в 

ВЫБРАТЬ
    КонтрагентыРодительКонтрагентыАвторПользователи.Код КАК РодительАвторКод,
    Контрагенты.Наименование КАК Наименование
ИЗ
    Справочник.Контрагенты КАК Контрагенты
        ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Контрагенты КАК КонтрагентыРодительКонтрагенты
            ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Пользователи КАК КонтрагентыРодительКонтрагентыАвторПользователи
            ПО (КонтрагентыРодительКонтрагенты.Автор = КонтрагентыРодительКонтрагентыАвторПользователи.Ссылка)
        ПО Контрагенты.Родитель = КонтрагентыРодительКонтрагенты.Ссылка

После того, как все обращения "через точку" будут ликвидированы, можно преобразовать в текст на SQL

SELECT
КонтрагентыРодительКонтрагентыАвторПользователи._Code РодительАвторКод,
Контрагенты._Description Наименование
FROM
_Reference35 Контрагенты
LEFT OUTER JOIN _Reference35 КонтрагентыРодительКонтрагенты
ON Контрагенты._ParentIDRRef = КонтрагентыРодительКонтрагенты._IDRRef
LEFT OUTER JOIN _Reference169 КонтрагентыРодительКонтрагентыАвторПользователи
ON КонтрагентыРодительКонтрагенты._Fld10297RRef = КонтрагентыРодительКонтрагентыАвторПользователи._IDRRef

3. Временные таблицы

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

ВЫБРАТЬ ПЕРВЫЕ 100
    Контрагенты.Ссылка КАК Ссылка,
    Контрагенты.ИНН КАК ИНН
ПОМЕСТИТЬ ВТ_Контрагенты
ИЗ
    Справочник.Контрагенты КАК Контрагенты

;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    РеализацияУслуг.Номер КАК Номер,
    РеализацияУслуг.Дата КАК Дата,
    РеализацияУслуг.ДокументОснование КАК ДокументОснование,
    ВТ_Контрагенты.ИНН КАК ИНН
ИЗ
    ВТ_Контрагенты КАК ВТ_Контрагенты
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.РеализацияУслуг КАК РеализацияУслуг
        ПО ВТ_Контрагенты.Ссылка = РеализацияУслуг.Контрагент

Будет выглядеть как

 If not exists (select * from tempdb..sysobjects where name =  '##ВТ_Контрагенты') Create table ##ВТ_Контрагенты(Ссылка  binary(16),ИНН  nvarchar(150))
INSERT INTO ##ВТ_Контрагенты WITH(TABLOCK) (Ссылка,ИНН)SELECT TOP 100
Контрагенты._IDRRef Ссылка,
Контрагенты._Fld2119 ИНН
FROM
_Reference35 Контрагенты

SELECT
РеализацияУслуг._Number Номер,
РеализацияУслуг._Date_Time Дата,
РеализацияУслуг._Fld3984_RRRef ДокументОснование,
ВТ_Контрагенты.ИНН ИНН
FROM
##ВТ_Контрагенты ВТ_Контрагенты
INNER JOIN _Document1109 РеализацияУслуг
ON ВТ_Контрагенты.Ссылка = РеализацияУслуг._Fld1114RRef
TRUNCATE TABLE  ##ВТ_Контрагенты

4. Виртуальные таблицы.

В текущей версии можно задать только "СрезПоследних" и "Обороты" для регистра накопления. Список планируется расширить в дальнейшем. 

Например, запрос

ВЫБРАТЬ
    ОтветственныеЛицаСрезПоследних.Период КАК Период,
    ОтветственныеЛицаСрезПоследних.Подразделение КАК Подразделение,
    ОтветственныеЛицаСрезПоследних.Должность КАК Должность
ИЗ
    РегистрСведений.ОтветственныеЛица.СрезПоследних(&Период, Подразделение = &Подразделение) КАК ОтветственныеЛицаСрезПоследних

Будет преобразован в 

SELECT
ОтветственныеЛицаСрезПоследних.Период Период,
ОтветственныеЛицаСрезПоследних.Подразделение Подразделение,
ОтветственныеЛицаСрезПоследних.Должность Должность
FROM
(SELECT
ВложенныйЗапросМаксПериод.Подразделение Подразделение,
ОтветственныеЛица._Fld7656RRef Должность,
ОтветственныеЛица._Period Период
FROM
(SELECT
ОтветственныеЛица._Fld7654RRef Подразделение,
MAX(ОтветственныеЛица._Period) Период
FROM
_InfoRg7653 ОтветственныеЛица

WHERE
ОтветственныеЛица._Period <= {ts '2021-04-15 12:00:00'} AND 
ОтветственныеЛица._Fld7654RRef = CAST(0x831A001A64963CBD11E06B2D4EAA2070  AS binary(16))

GROUP BY
ОтветственныеЛица._Fld7654RRef
) ВложенныйЗапросМаксПериод
INNER JOIN _InfoRg7653 ОтветственныеЛица
ON ВложенныйЗапросМаксПериод.Подразделение = ОтветственныеЛица._Fld7654RRef
AND ВложенныйЗапросМаксПериод.Период = ОтветственныеЛица._Period) ОтветственныеЛицаСрезПоследних

Ограничения обработки:

-Не поддерживается большая часть функций языка запросов (ССЫЛКА, Выразить, ТипЗначения, НачалоПериода и тд)

-Не поддерживается большая часть виртуальных таблиц, за исключением парочки наиболее часто используемых

-Не поддерживаются таблицы изменений

-Пока встречаются ошибки

Проблемы, которые мешают разработке:

-В первую очередь это работа с составными типами. Особенно теми, где сочетаются примитивные и ссылочные типы значений. Загадочная 1Сная система из 3 полей пока не поддерживается

-Нет разделения данных

-Нет РЛС

-Нет разделения итогов (Spliter) и агрегатов.

Изменения в версии 0.9

-Добавлена возможность работы с параметрами - списками значений

-Исправлены некоторые ошибки

-Добавлена возможность подключиться к SQL и выполнить запрос прямо из обработки

-Добавлена возможность сохранять запросы и настройки

Изменения в версии 1.0

-исправлена ошибка с обращением к общим модулям