Внутренняя механика работы СУБД в 1С:Предприятии 7.7 (особенно в DBF-версии) накладывает серьезные ограничения на скорость построения сложных отчетов. Для решения этой проблемы программисты часто используют внешнюю компоненту 1sqlite.dll и ее основной объект SQLiteBase. Он позволяет выполнять прямые SQL-запросы к таблицам базы данных на сверхвысокой скорости.
Однако при переходе от встроенного языка 1С или «черных» запросов к чистому SQL разработчики часто сталкиваются с трудностями. Проанализируем типичную задачу: необходимо выбрать элементы справочника БракПриемка, связать их со справочником Контрагенты и выполнить поиск по наименованию (поле descr) в обеих таблицах одновременно.
Разберем по шагам, почему возникают ошибки синтаксиса, как правильно связывать таблицы через JOIN и как оптимизировать такой запрос для максимального быстродействия.
Давайте подробно рассмотрим, какие ошибки совершают разработчики при написании подобных запросов, и как спроектировать идеальное решение.
---
В процессе разработки запроса к SQLiteBase часто возникают три ключевые ошибки:
WHERE должна находиться строго после всех объявлений связей JOIN. Попытка вставить условие фильтрации до оператора INNER JOIN или LEFT JOIN (как в первом сообщении темы) приведет к синтаксической ошибке парсера SQLite.СпрБрак.Контрагент.Наименование. Но в прямых SQL-запросах это не работает. Платформа 1С 7.7 в физической таблице базы данных хранит реквизит Контрагент как простой текстовый идентификатор (строку из 9 символов). Никаких "вложенных" свойств у этого поля на уровне базы данных нет. Чтобы получить реквизиты контрагента, необходимо явно объединить две таблицы по равенству идентификаторов. Попытка написать СпрБрак.Контрагент.id вызовет ошибку no such column.---
Раньше для работы с таблицами 1С в SQLiteBase требовалось вручную регистрировать виртуальные таблицы в процедуре ПриОткрытии:
запрос.ВыполнитьЗапрос("create virtual table СпрБрак using dbeng(Справочник.БракПриемка);");
запрос.ВыполнитьЗапрос("create virtual table СпрКонтр using dbeng(Справочник.Контрагенты);");
Современные версии компоненты 1sqlite поддерживают механизм автоподключения таблиц. Нам больше не нужно писать лишний код регистрации. Мы можем обращаться к метаданным напрямую двумя способами:
[Справочник.БракПриемка].Справочник_БракПриемка.Компонента сама «на лету» перехватит запрос, обратится к словарю метаданных 1С, создаст виртуальную таблицу через движок dbeng и вернет данные.
---
Чтобы связать справочник брака с контрагентами, мы должны использовать конструкцию LEFT JOIN или INNER JOIN. Связывание происходит по условию равенства идентификатора (ссылки) в одной таблице и уникального идентификатора id в другой:
FROM [Справочник.БракПриемка] AS Брак
LEFT JOIN [Справочник.Контрагенты] AS Контр ON Брак.Контрагент = Контр.id
Использование LEFT JOIN предпочтительнее, если в справочнике брака могут быть элементы с незаполненным реквизитом Контрагент, и мы всё равно хотим видеть их в результате.
---
Для поиска по подстроке в наименованиях обоих справочников используется оператор LIKE с групповыми символами процента %. Объединив условия через оператор OR, мы получим выборку, где искомая строка встречается либо в названии бракованного товара, либо в названии контрагента:
WHERE Брак.descr LIKE '%стрПоиска%' OR Контр.descr LIKE '%стрПоиска%'
---
При выгрузке результатов запроса в стандартную ТаблицуЗначений 1С мы рискуем получить простые текстовые строки вместо ссылок на объекты 1С. Чтобы этого избежать, используем встроенный в 1sqlite механизм типизации с помощью квадратных скобок: ИмяПоля [НовоеИмя :ТипДанных].
Также категорически не рекомендуется собирать текст запроса обычной склейкой строк (например, " ... LIKE '%" + СокрЛП(стрПоиска) + "%'"). Это может привести к синтаксическим ошибкам при наличии кавычек в строке поиска. Вместо этого задействуем безопасный метод Подставлять().
Проанализируем итоговый шаблон запроса:
ТекстЗапроса = "
|SELECT
| Брак.id [ЭлементБрака :Справочник.БракПриемка],
| Брак.descr [НаименованиеБрака],
| Контр.id [Контрагент :Справочник.Контрагенты]
|FROM [Справочник.БракПриемка] Брак
|LEFT JOIN [Справочник.Контрагенты] Контр ON Брак.Контрагент = Контр.id
|WHERE Брак.descr LIKE :СтрокаПоиска OR Контр.descr LIKE :СтрокаПоиска
|";
---
Сведем все наши шаги в готовую процедуру для модуля формы отчета или обработки. Обратите внимание на лаконичность инициализации и отсутствие необходимости ручной регистрации таблиц:
Перем БазаДанных, Запрос;
Процедура Сформировать()
// Подготовим строку поиска, обернув ее в символы % для LIKE
СтрПоиска = "%" + СокрЛП(СтрокаПоиска) + "%";
// Формируем безопасный типизированный запрос с автоподключением таблиц
Текст = "
|SELECT
| Брак.id [Брак :Справочник.БракПриемка],
| Контр.id [Контрагент :Справочник.Контрагенты]
|FROM [Справочник.БракПриемка] Брак
|LEFT JOIN [Справочник.Контрагенты] Контр ON Брак.Контрагент = Контр.id
|WHERE Брак.descr LIKE :ШаблонПоиска OR Контр.descr LIKE :ШаблонПоиска
|";
// Передаем параметр в запрос (защита от SQL-инъекций и спецсимволов)
Запрос.Подставлять("ШаблонПоиска", СтрПоиска);
// Выполняем запрос
ТЗ = СоздатьОбъект("ТаблицаЗначений");
Если Запрос.ВыполнитьЗапрос(Текст) = 1 Тогда
// Метод Выгрузить() автоматически преобразует ID в объекты 1С
Запрос.Выгрузить(ТЗ);
// Выведем результат в таблицу на форме
ТЗ.Выгрузить(ТаблицаРезультатов);
Иначе
Предупреждение("Ошибка выполнения SQL-запроса!");
КонецЕсли;
КонецПроцедуры
Процедура ПриОткрытии()
// Загрузка внешней компоненты и инициализация базы данных в памяти
Если ЗагрузитьВнешнююКомпоненту("1sqlite.dll") = 0 Тогда
Предупреждение("Не удалось загрузить компоненту 1sqlite.dll!");
СтатусВозврата(0);
Возврат;
КонецЕсли;
БазаДанных = СоздатьОбъект("SQLiteBase");
БазаДанных.Открыть(":memory:");
Запрос = БазаДанных.НовыйЗапрос();
КонецПроцедуры
Процедура ПриЗакрытии()
// Обязательно освобождаем ресурсы при закрытии формы
БазаДанных.Закрыть();
КонецПроцедуры
Используя этот подход, мы получаем безопасный, лаконичный и невероятно быстрый поиск по базе данных 1С 7.7, который корректно типизирует результаты в полноценные справочники платформы.