Отладка запросов
Artem GrimutaРазработчикам на платформе 1С:Предприятие 8 часто приходится писать и отлаживать запросы к базе данных.
Однако отлаживать их непосредственно в конфигураторе/1С:EDT не всегда удобно. Особенно если после каждого исправления сохранять изменения информационной базы и перезапускать в режиме 1С:Предприятие. Все таки даже простые конфигурации запускаются не 2 секунды, а за время запуска 1С:ERP успеваешь попить кофе.
Консоли запросов
Для ускорения процесса отладки запросов на помощь приходят различные внешние обработки: от консолей запросов до исполнителей кода. Готовых консолей запросов на рынке довольно много, но во многих из них отсутствует возможность переноса значений параметров вместе с текстом запроса.
Т.е. подразумевается сценарий, когда пользователь копирует текст запроса. Затем нажимает на кнопку "Заполнить список параметров" и далее по очереди заполняет все параметры.
Для упрощения данного сценария разработчики фирмы 1С реализовали консоль запросов, которая позволяет избежать этого с помощью загрузки текста запроса со всеми параметрами из строки в формате XML.
Чтобы получить заветную строку необходимо в режиме отладки вычислить выражение функции ОбщегоНазначения.ЗапросВСтрокуXML, которую разработчики 1С внедрили в библиотеку стандартных подсистем.
// Выгружает запрос в строку XML, которую можно вставить в Консоль запросов.
// Для переноса запроса и всех его параметров в Консоль запросов, необходимо вызвать функцию в окне.
// "Вычислить выражение" (Shift + F9), скопировать полученный XML в поле "Текст запроса"
// консоли запросов и выполнить команду "Заполнить из XML" из меню "Еще".
// Подробнее об использование функции смотрите в справке к консоли запросов.
//
// Параметры:
// Запрос - Запрос - запрос, который необходимо выгрузить в формат строки XML.
//
// Возвращаемое значение:
// Строка - строка в формате XML, которую можно извлечь при помощи метода "ОбщегоНазначения.ЗначениеИзСтрокиXML".
// После извлечения получится объект типа "Структура" с полями:
// * Текст - Строка - текст запроса.
// * Параметры - Структура - параметры запроса.
//
Функция ЗапросВСтрокуXML(Запрос) Экспорт // АПК:299 - используется при отладке запросов, см. описание функции.
Структура = Новый Структура("Текст, Параметры");
ЗаполнитьЗначенияСвойств(Структура, Запрос);
Возврат ЗначениеВСтрокуXML(Структура);
КонецФункции
// Преобразует (сериализует) любое значение в XML-строку.
// Преобразованы в могут быть только те объекты, для которых в синтакс-помощнике указано, что они сериализуются.
// См. также ЗначениеИзСтрокиXML.
//
// Параметры:
// Значение - Произвольный - значение, которое необходимо сериализовать в XML-строку.
//
// Возвращаемое значение:
// Строка - XML-строка.
//
Функция ЗначениеВСтрокуXML(Значение) Экспорт
ЗаписьXML = Новый ЗаписьXML;
ЗаписьXML.УстановитьСтроку();
СериализаторXDTO.ЗаписатьXML(ЗаписьXML, Значение, НазначениеТипаXML.Явное);
Возврат ЗаписьXML.Закрыть();
КонецФункции
Таким образом, чтобы из отладчика перенести запрос с параметрами достаточно вызвать данную функцию, скопировать текст, вставить в консоль запросов и все.
Улучшаем функцию ЗапросВСтрокуXML
Данная функция полезная и удобная, но давайте рассмотрим пару доработок, которые позволят прокачать ее до 100%.
В типовом коде весьма часто используется менеджер временных таблиц, который позволяет переиспользовать временные таблицы выполненных запросов в других запросах. Например, при обработке проведения данные табличной части дополняются полями шапки и помещаются во временную таблицу, а уже во всех других запросах используется именно данная временная таблица.
Однако функция ЗапросВСтрокуXML в первую очередь выполняет обычное преобразование в XML. Потому данные менеджера не используются, и в результате запрос будет падать.
Чтобы этого избежать, можно выгружать используемые временные таблицы в параметры, а также дополнять исходный текст запроса на их формирование.
Второй особенностью является ограничение в количестве выводимых символов в окне "Просмотр значения выражения", после которого текст обрезается.
При этом значение этого показателя не является константой и скорее рассчитывается от используемых символов. Например, если вывести значение строки состоящий из повторяющихся //, то вместится 409 603 символа, если вывести кириллические буквы Аа, то вместится - 614 403 символа, а вот если вывести латинские Aa, то получим снова 409 603 символа.
Соответственно для получения единого запроса потребуется обрезать текст запроса со смещением с помощью функции Сред, а дальше собирать ее в блокноте. На практике превысить данный лимит весьма легко и часто результат преобразования из-за параметров будет состоять более пяти склеек.
Потому для сохранения нервов достаточно фиксировать результат во временный файл, а после просто открывать его в блокноте.
Стоит отметить, что данное ограничение разработчики платформы отключили в версии 8.3.21. Однако теперь можно наткнуться на фразу "Расчет значения", которая может длиться продолжительное время. Потому пока предложенный вариант не теряет своей актуальности.
И пожалуй вишенкой на торте будет отказ от слова XML в названии функции, что позволит в поле вычисления выражения проще вводить название без переключения языковой раскладки.
В результате можно получить следующую обёртку для типовой функции.
// Выгружает запрос в строку XML, которую можно вставить в Консоль запросов.
// Для переноса запроса и всех его параметров в Консоль запросов, необходимо вызвать функцию в окне.
// «Вычислить выражение»(Shift + F9), скопировать полученный XML в поле "Текст запроса"
// консоли запросов и выполнить команду "Заполнить из XML" из меню "Еще".
// Подробнее об использование функции смотрите в справке к консоли запросов.
//
// Параметры:
// Запрос - Запрос - Запрос, который необходимо выгрузить в формат строки XML.
// ПутьКФайлу - Строка - Если запрос больше 400к символов, то размещается в файле по указанному пути или во временном.
//
// Возвращаемое значение:
// Строка - строка в формате XML, которую можно извлечь при помощи метода "ОбщегоНазначения.ЗначениеИзСтрокиXML".
// После извлечения получится объект типа "Структура" с полями:
// * Текст - Строка - текст запроса.
// * Параметры - Структура - параметры запроса.
//
Функция ЗапросВСтроку(Запрос, ПутьКФайлу = "") Экспорт
// Подтягивание временных таблиц из МВТ.
Если НЕ Запрос.МенеджерВременныхТаблиц = Неопределено
И ЗначениеЗаполнено(Запрос.МенеджерВременныхТаблиц.Таблицы) Тогда
// Если запрос уже выполнили, то МВТ будет содержать ВТ текущего запроса - будем исключать.
ТаблицыМВТДляИсключения = Новый Массив;
Попытка
ТекСхемаЗапроса = Новый СхемаЗапроса;
ТекСхемаЗапроса.УстановитьТекстЗапроса(Запрос.Текст);
Для Каждого ТекСхемаЗапроса Из ТекСхемаЗапроса.ПакетЗапросов Цикл
Если НЕ ТипЗнч(ТекСхемаЗапроса) = Тип("ЗапросВыбораСхемыЗапроса")
ИЛИ НЕ ЗначениеЗаполнено(ТекСхемаЗапроса.ТаблицаДляПомещения) Тогда
Продолжить;
КонецЕсли;
ТаблицыМВТДляИсключения.Добавить(ТекСхемаЗапроса.ТаблицаДляПомещения);
КонецЦикла;
Исключение
ТекстОшибки = ПодробноеПредставлениеОшибки(ИнформацияОбОшибке());
КонецПопытки;
МассивПакетовНовогоЗапроса = Новый Массив;
ВременныйЗапрос = Новый Запрос;
ВременныйЗапрос.МенеджерВременныхТаблиц = Запрос.МенеджерВременныхТаблиц;
// В результате выгрузки могут оказаться пустой тип, который потом нельзя загрузить
// Потому будем менять на указанный.
ОписаниеТипаДляПустыхТипов = ОбщегоНазначения.ОписаниеТипаСтрока(10);
Для Каждого ВремТаблица Из Запрос.МенеджерВременныхТаблиц.Таблицы Цикл
ИмяВременнойТаблицы = ВремТаблица.ПолноеИмя;
// Если временная таблица не используется, то нет смысла ее выгружать.
Если СтрНайти(Запрос.Текст, ИмяВременнойТаблицы) = 0
ИЛИ НЕ ТаблицыМВТДляИсключения.Найти(ИмяВременнойТаблицы) = Неопределено Тогда
Продолжить;
КонецЕсли;
МассивПолейВыборкиМенеджера = Новый Массив;
МассивПолейВыборкиВЗапрос = Новый Массив;
Для Каждого ТекКолонка Из ВремТаблица.Колонки Цикл
ИмяКолонки = ТекКолонка.Имя;
ПредставлениеКолонки = ИмяКолонки;
ТипКолонки = ТекКолонка.ТипЗначения;
// Платформа сама добавляет колонку НомерСтроки, но потом не позволяет ее грузить.
// Потому будем менять псевдоним.
Если ПредставлениеКолонки = "НомерСтроки" Тогда
ПредставлениеКолонки = "НомерСтрокиНомерСтроки";
КонецЕсли;
МассивПолейВыборкиМенеджера.Добавить(СтрШаблон("Таблица.%1 КАК %2", ИмяКолонки, ПредставлениеКолонки));
МассивПолейВыборкиВЗапрос.Добавить(СтрШаблон("Таблица.%1 КАК %2", ПредставлениеКолонки, ИмяКолонки));
КонецЦикла;
ТекстПолейВыборки = СтрСоединить(МассивПолейВыборкиМенеджера, ",
| ");
ВременныйЗапрос.Текст = СтрШаблон("ВЫБРАТЬ
| %1
|ИЗ
| %2 КАК Таблица", ТекстПолейВыборки, ИмяВременнойТаблицы);
РезультатЗапроса = ВременныйЗапрос.Выполнить();
Выборка = РезультатЗапроса.Выбрать();
ВыгрузкаВременнойТаблицы = Новый ТаблицаЗначений;
Для Каждого ТекКолонка Из РезультатЗапроса.Колонки Цикл
ТипКолонки = ТекКолонка.ТипЗначения;
Если НЕ ЗначениеЗаполнено(ТипКолонки.Типы()) Тогда
ТипКолонки = ОписаниеТипаДляПустыхТипов;
КонецЕсли;
ВыгрузкаВременнойТаблицы.Колонки.Добавить(ТекКолонка.Имя, ТипКолонки,,ТекКолонка.Ширина);
КонецЦикла;
Пока Выборка.Следующий() Цикл
НоваяСтрока = ВыгрузкаВременнойТаблицы.Добавить();
ЗаполнитьЗначенияСвойств(НоваяСтрока, Выборка);
КонецЦикла;
Запрос.УстановитьПараметр(ИмяВременнойТаблицы, ВыгрузкаВременнойТаблицы);
ТекстПолейВыборки = СтрСоединить(МассивПолейВыборкиВЗапрос, ",
| ");
МассивПакетовНовогоЗапроса.Добавить(СтрШаблон("ВЫБРАТЬ
| %1
|ПОМЕСТИТЬ %2
|ИЗ
| &%2 КАК Таблица", ТекстПолейВыборки, ИмяВременнойТаблицы));
КонецЦикла;
МассивПакетовНовогоЗапроса.Добавить(Запрос.Текст);
ИтоговыйЗапрос = Новый Запрос;
ЗаполнитьЗначенияСвойств(ИтоговыйЗапрос, Запрос,, "МенеджерВременныхТаблиц");
ИтоговыйЗапрос.Текст = СтрСоединить(МассивПакетовНовогоЗапроса, "
|;
|
|////////////////////////////////////////////////////////////////////////////////
|");
Для Каждого ТекПараметр Из Запрос.Параметры Цикл
Если СтрНайти(Запрос.Текст, ТекПараметр.Ключ) = 0 Тогда
Продолжить;
КонецЕсли;
ИтоговыйЗапрос.УстановитьПараметр(ТекПараметр.Ключ, ТекПараметр.Значение);
КонецЦикла;
Иначе
ИтоговыйЗапрос = Запрос;
КонецЕсли;
// Вызов типовой функции ЗапросВСтрокуXML.
ПреобразованныйТекстЗапроса = ОбщегоНазначения.ЗапросВСтрокуXML(ИтоговыйЗапрос);
МаксимальноеКоличествоСимволовДляОтладки = 400000;
Если СтрДлина(ПреобразованныйТекстЗапроса) <= МаксимальноеКоличествоСимволовДляОтладки Тогда
Возврат ПреобразованныйТекстЗапроса;
КонецЕсли;
Если НЕ ЗначениеЗаполнено(ПутьКФайлу) Тогда
ПутьКФайлу = ПолучитьИмяВременногоФайла("txt");
КонецЕсли;
Попытка
Запись = Новый ЗаписьТекста(ПутьКФайлу,КодировкаТекста.UTF8,,Ложь);
Запись.Записать(ПреобразованныйТекстЗапроса);
Запись.Закрыть();
Исключение
ПутьКФайлу = ПодробноеПредставлениеОшибки(ИнформацияОбОшибке());
КонецПопытки;
Возврат ПутьКФайлу;
КонецФункции
Отладка через файл
В решении "1С:УНФ 8. Управление предприятием общепита" есть механизм, который решает задачу с помощью запроса на 6000 строк кода (107 пакетов).
Причем функционал механизма постоянно развивается, и запрос дописывается, переписывается. Потому раз в период к запросу приходится возвращаться, вспоминать его устройство и вносить исправления.
Чтобы вспомнить устройство запроса помогает документация, а вот для его исправления проверить только результирующие таблицы чаще всего не достаточно.
Все-таки ошибиться в одном из 107 пакетов слишком легко.
В связи с этим нам потребовался механизм, который бы позволил одновременно видеть текст каждого пакета с результатом его выполнения.
Для этого мы решили выгружать такую информацию в файл с форматом xlsx (Microsoft Excel, LibreOffice и т.п.), что позволило видеть сразу несколько таблиц и небольшим перемещением по странице пробегать от пакета к пакету.
Однако первое на чем мы споткнулись - неудобство анализа при работе с базами клиентов. На тестовых данных все запросы располагаются рядом и применять особые фильтры для поиска нужных строк не нужно. С реальными же данными все со всем наоборот и если в пакете хотя бы 100 строк, то перемещение между пакетами и их анализ очень затрудняется.
Частичным решением данной проблемы вышла возможность вывода одного пакета на один лист файла excel.
Второй момент оказался ограничением максимального количества символов в ячейке. Согласно документации Excel максимальное количество составляет 32 767 символов. С одной стороны много, но для текста пакетов объединяющих несколько запросов и еще с соединениями оказалось недостаточно. А т.к. для внесения правок в пакеты нам приходилось искать его в общем тексте запроса, то мы остановились на обрезке текста при логировании.
Третьей особенностью оказалась нехватка представления для анализа. Во первых потому что значение представления NULL, неопределено, пустой строки и пустой ссылки совпадает. И визуально их идентифицировать весьма трудно.
Во вторых наименования объектов могут пересекаться как в рамках одного типа, так и разных. Например, представление элемента справочника типов оплат "Наличные" и представления значение перечисления видов оплат "Наличные".
Для решения данной особенности была добавлена выдача информации о типе, а также идентификаторе ссылочных объектов.
В результате получился весьма удобный механизм, который сильно облегчает отладку такого запроса.
А чтобы воспользоваться его аналогом, достаточно использовать следующую функцию.
// Функция - Выполняет запрос и помещает результат в файл формата XLSX.
//
// Параметры:
// Запрос - Запрос - Запрос к выполнению.
// ПутьКФайлу - Строка - Путь к файлу xlsx на сервере. При пустом значении поместит во временный файл.
// РазмещатьПакетыНаРазныеЛисты - Булево - Размещать пакеты на разные листы.
//
// Возвращаемое значение:
// Структура:
// * ПутьКФайлу - Строка - Путь к файлу на сервере. Если параметр был пустой, то вернет путь к временному файлу.
// * ТекстОшибки - Строка - Текст ошибки при выполнении запроса или сохранении.
//
Функция ВыполнитьЗапросВФайл(Запрос, ПутьКФайлу = "", РазмещатьПакетыНаРазныеЛисты = Ложь)
РезультатыПакетов = Запрос.ВыполнитьПакетСПромежуточнымиДанными();
СхемаТекЗапроса = Новый СхемаЗапроса;
СхемаТекЗапроса.УстановитьТекстЗапроса(Запрос.Текст);
Если НЕ ЗначениеЗаполнено(ПутьКФайлу) Тогда
ПутьКФайлу = ПолучитьИмяВременногоФайла("xlsx");
КонецЕсли;
ТабДок = Новый ТабличныйДокумент;
ФайлОтладки = Новый ПакетОтображаемыхДокументов;
Если НЕ РазмещатьПакетыНаРазныеЛисты Тогда
ТекЛист = ФайлОтладки.Состав.Добавить();
ТекЛист.Данные = ПоместитьВоВременноеХранилище(ТабДок);
ТекЛист.Наименование = НСтр("ru='Результат'", "ru");
КонецЕсли;
ГраницаТабличногоДокумента = Новый Линия(ТипЛинииЯчейкиТабличногоДокумента.Сплошная);
МаксимальноеКоличествоСимволовВЯчейки = 32767;
ШрифтВыделения = Новый Шрифт(,,10, Истина);
Для СчетчикПакетов = 0 По РезультатыПакетов.ВГраница() Цикл
ДанныеПакета = СхемаТекЗапроса.ПакетЗапросов[СчетчикПакетов];
ИмяПакета = СтрШаблон(НСтр("ru='Результат пакета %1'"), Формат(СчетчикПакетов, "ЧН=0; ЧГ="));
Если ТипЗнч(ДанныеПакета) = Тип("ЗапросУничтоженияТаблицыСхемыЗапроса") Тогда
ТекстПакета = СтрШаблон("УНИЧТОЖИТЬ %1", ДанныеПакета.ИмяТаблицы);
ТаблицаПакета = Неопределено;
Иначе
Если ЗначениеЗаполнено(ИмяПакета) Тогда
ИмяПакета = ДанныеПакета.ТаблицаДляПомещения;
КонецЕсли;
ТекстПакета = ДанныеПакета.ПолучитьТекстЗапроса();
ТаблицаПакета = РезультатыПакетов[СчетчикПакетов].Выгрузить();
КонецЕсли;
Если РазмещатьПакетыНаРазныеЛисты Тогда
ТабДок = Новый ТабличныйДокумент;
ТекЛист = ФайлОтладки.Состав.Добавить();
ТекЛист.Данные = ПоместитьВоВременноеХранилище(ТабДок);
ТекЛист.Наименование = ИмяПакета;
КонецЕсли;
// Выводим шапочную информацию.
Секция = ТабДок.ПолучитьОбласть("R1");
Секция.Область("R2C1").Текст = ИмяПакета;
Секция.Область("R2C1").Шрифт = ШрифтВыделения;
ТекстПакета = СтрЗаменить(СтрЗаменить(СтрЗаменить(ТекстПакета," ","·")," ","····"), "//","");
Если СтрДлина(ТекстПакета) >= МаксимальноеКоличествоСимволовВЯчейки Тогда
ТекстПакета = Лев(ТекстПакета, МаксимальноеКоличествоСимволовВЯчейки);
КонецЕсли;
Секция.Область("R3C1").Текст = СокрЛП(ТекстПакета);
Секция.Область("R3C1").ВысотаСтроки = 11.24;
Если ТаблицаПакета = Неопределено Тогда
Продолжить;
КонецЕсли;
// Выводим заголовки колонок
МаксимальныйИндексКолонок = ТаблицаПакета.Колонки.Количество() - 1;
Для СчетчикКолонок = 0 По МаксимальныйИндексКолонок Цикл
ТекОбласть = Секция.Область(4, СчетчикКолонок + 1);
ТекОбласть.Текст = ТаблицаПакета.Колонки[СчетчикКолонок];
ТекОбласть.Шрифт = ШрифтВыделения;
ТекОбласть.ШиринаКолонки = 15;
ТекОбласть.ГраницаСверху = ГраницаТабличногоДокумента;
ТекОбласть.ГраницаСлева = ГраницаТабличногоДокумента;
ТекОбласть.ГраницаСнизу = ГраницаТабличногоДокумента;
ТекОбласть.ГраницаСправа = ГраницаТабличногоДокумента;
КонецЦикла;
ТабДок.Вывести(Секция);
// Выводим таблицу
Секция = ТабДок.ПолучитьОбласть("R1");
Для СчетчикСтрок = 0 По ТаблицаПакета.Количество() -1 Цикл
СтрокаТаблицы = ТаблицаПакета[СчетчикСтрок];
Для СчетчикКолонок = 0 По МаксимальныйИндексКолонок Цикл
КолонкаТаблицы = ТаблицаПакета.Колонки[СчетчикКолонок];
ЗначениеТекущейЯчейки = СтрокаТаблицы[КолонкаТаблицы.Имя];
ТипЗначенияЯчейки = ТипЗнч(ЗначениеТекущейЯчейки);
Если ЗначениеТекущейЯчейки = Неопределено Тогда
ЗначениеТекущейЯчейки = "Неопределено";
ИначеЕсли ЗначениеТекущейЯчейки = Null Тогда
ЗначениеТекущейЯчейки = "Null";
ИначеЕсли ЗначениеТекущейЯчейки = 0 Тогда
ЗначениеТекущейЯчейки = "0";
ИначеЕсли НЕ ЗначениеЗаполнено(ЗначениеТекущейЯчейки) Тогда
ЗначениеТекущейЯчейки = СтрШаблон(НСтр("ru='ПустаяСсылка. %1'", "ru"),ТипЗначенияЯчейки);
Иначе
Попытка
Если НЕ ТипЗначенияЯчейки = Тип("Число")
И НЕ ТипЗначенияЯчейки = Тип("Дата")
И НЕ ТипЗначенияЯчейки = Тип("Булево")
И НЕ ТипЗначенияЯчейки = Тип("Строка")
И НЕ ЗначениеТекущейЯчейки = XMLСтрока(ЗначениеТекущейЯчейки) Тогда
ЗначениеТекущейЯчейки = СтрШаблон("%1
|%2
|<%3>", ЗначениеТекущейЯчейки, XMLСтрока(ЗначениеТекущейЯчейки), ТипЗначенияЯчейки);
КонецЕсли;
Исключение
// Не преобразовываемое значение.
КонецПопытки;
КонецЕсли;
ТекОбласть = Секция.Область(СчетчикСтрок + 1, СчетчикКолонок + 1);
ТекОбласть.Текст = ЗначениеТекущейЯчейки;
ТекОбласть.ШиринаКолонки = 15;
ТекОбласть.ГраницаСверху = ГраницаТабличногоДокумента;
ТекОбласть.ГраницаСлева = ГраницаТабличногоДокумента;
ТекОбласть.ГраницаСнизу = ГраницаТабличногоДокумента;
ТекОбласть.ГраницаСправа = ГраницаТабличногоДокумента;
КонецЦикла;
КонецЦикла;
ТабДок.Вывести(Секция);
КонецЦикла;
Результат = Новый Структура;
Результат.Вставить("ПутьКФайлу", ПутьКФайлу);
Результат.Вставить("ТекстОшибки", "");
Попытка
ФайлОтладки.Записать(ПутьКФайлу, ТипФайлаПакетаОтображаемыхДокументов.XLSX);
Исключение
Результат.ТекстОшибки = СтрШаблон(НСтр("ru='Ошибка записи файла <%1>: %2'"), ПутьКФайлу,
ПодробноеПредставлениеОшибки(ИнформацияОбОшибке()));
КонецПопытки;
Возврат Результат;
КонецФункции
Вместо выводов
Основным инструментом отладки запросов к базе данных являются консоли запросов.
Однако наиболее удобными являются консоли поддерживающие загрузку текста запроса со значениями параметров полученных при отладке.
Одной из таких является консоль запросов от фирмы 1С, которая позволяет осуществлять экспорт с помощью функции ЗапросВСтрокуXML библиотеки стандартных подсистем.
В типовом варианте она имеет ряд ограничений, чтобы их обойти достаточно воспользоваться функцией предложенной в данной статье.
Больше полезных материалов на официальном канале 1С и точка.