Разработка и поддержка крупных информационных баз на устаревшей, но все еще активно используемой платформе 1С:Предприятие 7.7 в файловом режиме (DBF) часто упирается в ограничения производительности стандартного движка. Одним из наиболее эффективных решений для преодоления этого барьера является внедрение внешней компоненты 1sqlite (автор — Александр Орефков). Данное решение позволяет выполнять полноценные, сверхбыстрые SQL-запросы непосредственно к DBF-таблицам базы данных 1С или работать со структурами в оперативной памяти (режим :memory:).
Однако синтаксис построения запросов в 1sqlite имеет свои специфические особенности. Главная сложность для разработчиков, привыкших к классическому SQL или языку запросов «1С:Предприятия 8», заключается в правильной типизации результатов, обращении к метаданным и связывании таблиц. В этом руководстве мы подробно разберем, как устроен синтаксис 1sqlite, как правильно выбирать данные из справочников и как избежать распространенных ошибок.
Начнем с самого важного шага — обращения к таблицам баз данных. В ранних версиях компоненты разработчикам приходилось вручную объявлять виртуальные таблицы с помощью драйвера dbeng. Это выглядело следующим образом:
ЗагрузитьВнешнююКомпоненту("1sqlite.dll");
БазаДанных = СоздатьОбъект("SQLiteBase");
БазаДанных.Открыть(":memory:");
Запрос = БазаДанных.НовыйЗапрос();
Запрос.ВыполнитьЗапрос("CREATE VIRTUAL TABLE ФайлыПрайсов USING dbeng(Справочник.ФайлыПрайсов)");
В современных сборках компоненты ручное объявление таблиц больше не требуется. Драйвер автоподключения автоматически распознает обращения к метаданным 1С. Мы можем использовать два варианта синтаксиса для обращения к таблицам справочников и документов:
[Справочник.ИмяСправочника].Справочник_ИмяСправочника.Рассмотрим пример простого и лаконичного обращения к данным:
БазаДанных = СоздатьОбъект("SQLiteBase");
БазаДанных.Открыть(":memory:");
Запрос = БазаДанных.НовыйЗапрос();
ТЗ = Запрос.ВыполнитьЗапрос("SELECT * FROM [Справочник.Номенклатура] LIMIT 10");
ТЗ.ВыбратьСтроку();
Драйвер dbeng самостоятельно перехватит этот запрос, найдет соответствующий DBF-файл на диске, проанализирует его структуру и создаст виртуальную таблицу в СУБД SQLite в режиме «на лету».
Каждая таблица справочника в 1С 7.7 на физическом уровне содержит стандартный набор системных полей. Проанализируем их структуру:
ID — уникальный идентификатор элемента (хранится в виде 36-ричной строки длиной 9 символов).PARENTID — идентификатор родительского элемента (используется в иерархических справочниках).CODE — код элемента справочника.DESCR — наименование элемента.ISFOLDER — признак группы (1 — группа, 2 — элемент).ISMARK — пометка удаления (если элемент помечен на удаление, поле содержит символ *).PARENTEXT — идентификатор владельца (для подчиненных справочников).Если мы выполним стандартный SQL-запрос вида SELECT ID, DESCR FROM [Справочник.Номенклатура], то СУБД SQLite вернет нам «сырые» текстовые значения из DBF-файла. Поле ID вернется как строка вида " 1AG ". Платформа 1С не сможет распознать в ней ссылку на элемент справочника. Чтобы решить эту проблему, в 1sqlite реализован механизм типизации колонок через псевдонимы (алиасы) с использованием квадратных скобок.
Посмотрим на пример правильной типизации полей справочника:
ТекстЗапроса = "
|SELECT
| Номенклатура.ID as [Товар :Справочник.Номенклатура],
| Номенклатура.DESCR as [Наименование],
| Номенклатура.Артикул as [Артикул]
|FROM
| [Справочник.Номенклатура] as Номенклатура
|WHERE
| Номенклатура.ISMARK <> '*'
|";
Разберем, как работает конструкция [Товар :Справочник.Номенклатура]. Компонента перед отдачей результата в 1С анализирует псевдоним колонки. Обнаружив двоеточие : (или символ $), она понимает, что данную колонку нужно типизировать. Она преобразует сырой текстовый идентификатор в полноценную ссылку на элемент справочника 1С, а служебную часть из названия колонки вырезает. В результате в полученной Таблице Значений у нас будет колонка с именем Товар, содержащая полноценные объекты 1С.
Выясним, какие типы данных и конструкции для типизации мы можем использовать в псевдонимах колонок:
[Колонка :Справочник.ИмяСправочника] — типизация под конкретный справочник.[Колонка :Документ.ИмяДокумента] — ссылка на конкретный вид документа.[Колонка :Документ] — ссылка на документ неопределенного вида.[Колонка :Перечисление.ИмяПеречисления] — значение перечисления.[Колонка :Счет.ИмяПланаСчетов] — ссылка на счет бухгалтерского учета.[Колонка :Дата] — преобразование строки в дату формата 1С.[Колонка :Время] — преобразование во время.[Колонка :Число.Длина.Точность] — принудительное приведение к числу 1С (например, [Сумма :Число.15.2]).Разберем важную особенность СУБД SQLite, о которой необходимо знать каждому разработчику. SQLite нативно не поддерживает точный тип данных типа Numeric или Decimal с фиксированной точкой. Все дробные числа хранятся в формате чисел с плавающей точкой (тип Double). Из-за этого при математических операциях в запросах (особенно при группировках и суммировании) могут возникать микроскопические погрешности округления (например, вместо 45.10 вернется значение 45.0999999999999).
Хотя при типизации :Число.15.2 компонента выполнит округление при передаче данных в 1С, внутри самого SQL-запроса (в условиях WHERE или HAVING) обязательно нужно использовать встроенную математическую функцию round(). Рассмотрим пример:
ТекстЗапроса = "
|SELECT
| Заказ.ID as [Документ :Документ.Заказ],
| round(SUM(Заказ.Сумма), 2) as [Всего :Число.15.2]
|FROM
| [ДокументСтроки.Заказ] as Заказ
|GROUP BY
| Заказ.ID
|HAVING
| round(SUM(Заказ.Сумма), 2) > 5000.00
|";
Рассмотрим частую проблему: у нас есть подчиненный справочник (например, «ТоварыПоставщиков») и мы хотим получить его владельца (элемент справочника «ФайлыПрайсов»). За связь с владельцем отвечает системное поле PARENTEXT.
Если мы напишем запрос без явной типизации поля владельца, то получим числовое или строковое значение идентификатора, которое малоинформативно для пользователя:
// Неправильный вариант: вернет сырой ID владельца
SELECT PARENTEXT as [Прайс] FROM [Справочник.ТоварыПоставщиков]
Чтобы компонента корректно преобразовала это поле в элемент справочника-владельца, мы обязательно должны указать тип в квадратных скобках алиаса, используя символ $ или :. Проанализируем правильный вариант кода:
// Правильный вариант: вернет ссылку на элемент Справочник.ФайлыПрайсов
SELECT
Товары.CODE as Код,
Товары.DESCR as Наименование,
Товары.PARENTEXT as [Прайс $Справочник.ФайлыПрайсов]
FROM
[Справочник.ТоварыПоставщиков] as Товары
Важное примечание: Если вы забудете поставить символ $ или : (например, напишете [Прайс Справочник.ФайлыПрайсов]), то компонента проигнорирует типизацию и вернет необработанную строку. Символы типизации внутри квадратных скобок обязательны!
В системе 1С 7.7 периодические реквизиты справочников не хранятся в физической таблице самого справочника. Они вынесены в отдельную системную таблицу констант — 1SCONST (в терминах 1sqlite к ней можно обращаться напрямую как к _1С.Const или через автоподключение [Регистр.Имя] в зависимости от конфигурации).
Чтобы получить значение периодического реквизита на определенную дату, нам необходимо связать таблицу справочника с таблицей констант по условиям совпадения идентификатора объекта (OBJID = ID) и идентификатора самого периодического реквизита.
В современных версиях компоненты 1sqlite, базирующихся на новых версиях SQLite (3.25 и выше), стали доступны оконные функции. С их помощью можно очень быстро и элегантно получить срез последних (актуальные значения периодических реквизитов на заданную дату) без использования тяжелых подзапросов с группировками. Разберем этот подход на примере получения актуальных цен товаров:
ТекстЗапроса = "
|SELECT
| Номенклатура.DESCR as [Товар],
| Цены.Value as [Цена :Число.15.2]
|FROM
| [Справочник.Номенклатура] as Номенклатура
|LEFT JOIN (
| SELECT
| OBJID,
| Value,
| ROW_NUMBER() OVER (PARTITION BY OBJID ORDER BY DATE DESC, TIME DESC) as rn
| FROM
| _1С.Const
| WHERE
| ID = ' 2F ' -- Здесь указывается ID вашего периодического реквизита из метаданных
| AND DATE <= '20231026'
|) as Цены ON Цены.OBJID = Номенклатура.ID AND Цены.rn = 1
|";
В данном запросе оконная функция ROW_NUMBER() OVER (PARTITION BY OBJID ORDER BY DATE DESC, TIME DESC) нумерует строки истории изменения цен для каждого товара, начиная с самой свежей на указанную дату. Во внешнем запросе мы просто соединяем таблицы по условию Цены.rn = 1, тем самым забирая только актуальные значения.
Разберем популярную ошибку начинающих разработчиков при попытке вывести результат запроса в Таблицу Значений (ТЗ) на форме. Рассмотрим пример кода, который приводит к неожиданному поведению:
// Ошибочный подход
ТЗ = Запрос.ВыполнитьЗапрос(ТекстЗапроса);
ТабЗначНаФорме.КоличествоСтрок(ТЗ.КоличествоСтрок());
ТабЗначНаФорме.Заполнить(ТЗ);
При таком подходе на форме может отобразиться гораздо меньше колонок, чем было выбрано в SQL-запросе. Выясним причину этого поведения. Метод Заполнить() класса ТаблицаЗначений в 1С 7.7 не создает новые колонки автоматически. Он лишь заполняет данными те колонки, имена которых уже существуют в объекте-приемнике. Если ваша таблица на форме изначально пуста или содержит другие колонки, данные просто не отобразятся.
Чтобы избежать этой проблемы, используйте метод Выгрузить(), который полностью перестраивает структуру колонок приемника под структуру источника:
// Правильный подход
ТЗ = Запрос.ВыполнитьЗапрос(ТекстЗапроса);
ТЗ.Выгрузить(ТабЗначНаФорме);
В процессе работы с прямыми запросами в 1С 7.7 вы обязательно столкнетесь с классом ПрямойЗапрос из библиотеки 1С++. Проанализируем разницу между этими подходами.
Класс ПрямойЗапрос является надстройкой (парсером). Он позволяет писать запросы на псевдоязыке, очень похожем на язык запросов 1С 8.х (например, с использованием виртуальных таблиц Регистр.Остатки или конструкции СрезПоследних). Парсер автоматически транслирует этот код в низкоуровневый SQL.
Однако опытные разработчики рекомендуют сначала освоить именно «чистый» 1sqlite. И на это есть веские причины:
compress() / uncompress()), которые парсер ПрямойЗапрос может не поддерживать или транслировать неоптимально.Для эффективной работы никогда не пишите и не отлаживайте сложные SQL-запросы непосредственно в конфигураторе 1С. Любая синтаксическая ошибка приведет к падению программы, а процесс поиска опечатки займет много времени.
Используйте специализированные внешние консоли запросов (например, обработку 1sqlite.ert). Консоль позволяет писать SQL-код в интерактивном режиме, подсвечивает синтаксис, выводит результат в удобную таблицу на экране и показывает детальное время выполнения запроса в миллисекундах. Также с помощью команды EXPLAIN QUERY PLAN вы всегда сможете посмотреть, как СУБД SQLite планирует выполнять ваш запрос и используются ли при этом необходимые индексы DBF-файлов.