В работе с 1С 7.7 часто возникает необходимость выполнения сложных операций с данными, которые хранятся как в стандартных объектах конфигурации (справочниках, документах), так и во временных структурах, таких как Таблицы Значений (ТЗ). Встроенные средства 1С 7.7 для манипуляции данными в ТЗ ограничиваются циклами и простыми методами, что может быть недостаточно для решения комплексных задач, требующих объединения, фильтрации и группировки данных из множества источников.
В таких случаях на помощь приходит внешняя компонента 1sqlite, которая позволяет использовать мощный функционал SQL для работы как с ТЗ, так и с данными 1С напрямую. Рассмотрим, как мы можем объединять данные из двух Таблиц Значений, использовать справочники как виртуальные таблицы, строить сложные запросы с помощью CTE и представлений (VIEW), а также разберем критически важные нюансы типизации колонок в 1sqlite.
Первым шагом, если у нас есть данные в Таблицах Значений, которые мы хотим обработать с помощью SQL, является их "укладка" в базу данных 1sqlite. Для этого мы используем метод базаДанных.уложитьТЗ().
Разберем синтаксис этого метода:
УложитьТЗ(ТЗ, ИмяТаблицы, [КакПостоянную])
0 (по умолчанию) – создает временную таблицу, которая будет существовать только на время текущей сессии или до явного удаления.1 – создает обычную (постоянную) таблицу в файле базы данных SQLite.Состав полей созданной таблицы в SQLite будет соответствовать составу колонок исходной ТЗ, а имена полей – идентификаторам колонок ТЗ. 1sqlite автоматически преобразует значения 1С в формат базы данных и обратно.
Посмотрим на пример, как мы можем уложить две Таблицы Значений и затем выполнить SQL-запрос для их объединения:
базаДанных.уложитьТЗ(ИсходнаяТЗ1, "ПолучаемаяТаблицаДляSQL1", 0);
базаДанных.уложитьТЗ(ИсходнаяТЗ2, "ПолучаемаяТаблицаДляSQL2", 0);
запрос = "
|SELECT т1.*, т2.*
|FROM ПолучаемаяТаблицаДляSQL1 AS т1
|FULL JOIN ПолучаемаяТаблицаДляSQL2 AS т2
|ON TRUE
";
// Далее выполните запрос и обработайте результат
// Результат = Запрос.ВыполнитьЗапрос(запрос);
В этом примере мы создали две временные таблицы в памяти SQLite и объединили их полным внешним соединением. Обратите внимание, что FULL JOIN в SQLite может вести себя не совсем так, как в других СУБД; иногда LEFT JOIN с последующей фильтрацией в WHERE является более предсказуемым и эффективным подходом.
Одним из мощных преимуществ 1sqlite является возможность напрямую обращаться к данным 1С (например, справочникам) как к виртуальным таблицам без необходимости предварительной выгрузки в ТЗ или использования команды CREATE VIRTUAL TABLE. Компонента 1sqlite способна самостоятельно подключать таблицы 1С.
Мы можем обращаться к справочникам 1С, используя синтаксис [Справочник.ИмяСправочника] или Справочник_ИмяСправочника. Например, для справочника "ТМЦ" мы можем использовать [Справочник.ТМЦ].
Важный совет по производительности:
При работе с 1sqlite старайтесь следовать принципам эффективной работы:
WHERE будет иметь наименьшее количество записей.LEFT JOIN: Вместо INNER JOIN часто предпочтительнее использовать LEFT JOIN, а затем отфильтровывать ненужные данные в WHERE. Это связано с отсутствием полноценной статистики в 1sqlite для оптимизации INNER JOIN.Время на передачу данных между 1С и 1sqlite ничтожно, поскольку все происходит в оперативной памяти.
Для решения комплексных задач, таких как определение группы товара на основе множества характеристик, нам часто требуется выполнять многоэтапные SQL-запросы. 1sqlite поддерживает продвинутые возможности SQL, такие как Общие Табличные Выражения (CTE) и Представления (VIEW).
WITHОбщие Табличные Выражения (CTE), или WITH-выражения, позволяют нам создавать временные именованные наборы результатов, которые существуют только в рамках одного SQL-оператора. Это значительно улучшает читаемость и модульность сложных запросов, разбивая их на более мелкие, логически завершенные части.
Рассмотрим пример использования CTE:
запрос = "
|WITH НужноеИмя AS (
| SELECT
| спр.ID AS Элемент,
| спр.DESCR AS Наименование
| FROM
| [Справочник.Номенклатура] AS спр
|)
|SELECT
| товары.Элемент AS [Товар $Справочник.Номенклатура],
| товары.Наименование AS [Наименование $Строка]
|FROM
| НужноеИмя AS товары
|WHERE
| товары.Наименование LIKE '%товар%'
|";
// Результат = Запрос.ВыполнитьЗапрос(запрос);
В этом примере мы сначала определили CTE с именем НужноеИмя, которое выбирает ID и описание из справочника "Номенклатура". Затем в основном запросе мы обращаемся к НужноеИмя как к обычной таблице, что делает запрос более понятным.
Представления (VIEW) — это виртуальные таблицы, которые не хранят данные самостоятельно, а являются сохраненными запросами. Они позволяют нам инкапсулировать сложную логику выборки данных и обращаться к ней как к обычной таблице. В отличие от CTE, VIEW могут быть сохранены постоянно и использоваться в нескольких SQL-операторах.
Создание представления в 1sqlite происходит с помощью команды CREATE VIEW:
// Сначала удаляем представление, если оно уже существует, чтобы избежать ошибок при повторном запуске
ТекстЗапрос = "DROP VIEW IF EXISTS ВьюшкаТовары";
Запрос.ВыполнитьЗапрос(ТекстЗапрос);
// Создаем представление
Представление = "
|CREATE VIEW temp.ВьюшкаТовары AS
|SELECT
| ID AS ТоварID,
| DESCR AS НаименованиеТовара
|FROM
| [Справочник.Номенклатура]
|";
Запрос.ВыполнитьЗапрос(Представление);
// Теперь мы можем обращаться к ВьюшкаТовары как к обычной таблице
ТекстЗапрос = "SELECT ТоварID, НаименованиеТовара FROM ВьюшкаТовары WHERE НаименованиеТовара LIKE '%тест%'";
// Результат = Запрос.ВыполнитьЗапрос(ТекстЗапрос);
Использование VIEW особенно удобно, когда мы хотим сначала отладить промежуточный запрос, а затем использовать его результат в другом, более сложном запросе, не переписывая каждый раз всю логику.
Один из самых частых источников ошибок при работе с 1sqlite и 1С 7.7 возникает из-за неправильного понимания типизации колонок в SQL-запросах. Синтаксис [ИмяКолонки $Тип1С.Субтип] является специфическим для 1sqlite и предназначен исключительно для финального SELECT-запроса, который возвращает данные непосредственно в Таблицу Значений 1С или ТабличноеПоле.
Проблема:
Если мы используем типизацию внутри промежуточных запросов (таких как CTE или VIEW), например, так:
CREATE VIEW temp.ТЗГруппыТМЦ_2 AS
SELECT
Count(ТЗГруппыТМЦ_.Хар) AS Колич,
ТЗГруппыТМЦ_.ГруппаУр1 AS [ГруппаУр1_ $Справочник.ТМЦ], // НЕПРАВИЛЬНО!
ТЗГруппыТМЦ_.Группа AS [Группа $Справочник.ТМЦ] // НЕПРАВИЛЬНО!
FROM
ТЗГруппыТМЦ_
GROUP BY
ТЗГруппыТМЦ_.ГруппаУр1, ТЗГруппыТМЦ_.Группа;
Тогда фактические имена колонок в созданном представлении ТЗГруппыТМЦ_2 будут не Колич, ГруппаУр1_ и Группа, а Колич, ГруппаУр1_ $Справочник.ТМЦ и Группа $Справочник.ТМЦ. Если мы попытаемся обратиться к ним по сокращенному имени, например, SELECT ГруппаУр1_ FROM ТЗГруппыТМЦ_2;, мы получим ошибку "no such column: ГруппаУр1_".
Почему SELECT * работает?
SELECT * работает, потому что он выбирает все колонки по их полным, фактическим именам, включая те, что содержат типизацию. Когда 1С-компонента получает результат с колонкой "ГруппаУр1_ $Справочник.ТМЦ", она распознает "волшебный" суффикс, отрезает его и правильно типизирует данные в итоговой ТЗ 1С, присваивая колонке имя "ГруппаУр1_".
Правильное решение:
Внутри CTE или VIEW используйте простые псевдонимы без указания типа 1С. Типизацию применяйте только в самом последнем SELECT-запросе, который выгружает данные в 1С.
// Правильное создание VIEW без типизации
CREATE VIEW temp.ТЗГруппыТМЦ_2 AS
SELECT
Count(ТЗГруппыТМЦ_.Хар) AS Колич,
ТЗГруппыТМЦ_.ГруппаУр1 AS ГруппаУр1_ , // ПРАВИЛЬНО
ТЗГруппыТМЦ_.Группа AS Группа_ // ПРАВИЛЬНО
FROM
ТЗГруппыТМЦ_
GROUP BY
ТЗГруппыТМЦ_.ГруппаУр1, ТЗГруппыТМЦ_.Группа;
// Теперь вы можете обращаться к полям по их простым именам
SELECT Колич FROM ТЗГруппыТМЦ_2; // РАБОТАЕТ
SELECT ГруппаУр1_ FROM ТЗГруппыТМЦ_2; // РАБОТАЕТ
// И только в финальном запросе, выгружающем данные в 1С, добавляем типизацию
SELECT
Колич,
ГруппаУр1_ AS [ГруппаУр1_ $Справочник.ТМЦ],
Группа_ AS [Группа $Справочник.ТМЦ]
FROM
ТЗГруппыТМЦ_2;
Совет по отладке:
Чтобы понять фактические имена колонок в представлении или таблице SQLite, используйте команду pragma_table_info():
ТекстЗапрос = "SELECT name FROM pragma_table_info('ТЗГруппыТМЦ_2');";
// Результат = Запрос.ВыполнитьЗапрос(ТекстЗапрос);
// Выгрузив результат, вы увидите реальные имена колонок в вашей VIEW.
Это поможет вам избежать ошибок "no such column" и правильно формировать запросы.
При необходимости совместить в представлении как простое имя, так и типизированное для удобства отладки и использования, мы можем создать два поля:
DROP VIEW IF EXISTS ВьюшкаТовары;
CREATE VIEW temp.ВьюшкаТовары AS
SELECT
ID AS Товар, // Простое имя для использования в других запросах
ID AS [РасшифровкаТовара $Справочник.Номенклатура] // Типизированное имя для финальной выгрузки в 1С ТЗ
FROM
[Справочник.Номенклатура];
// Теперь можем использовать:
SELECT Товар FROM ВьюшкаТовары; // Для внутреннего использования
SELECT РасшифровкаТовара [РасшифровкаТовара :Справочник.Номенклатура] FROM ВьюшкаТовары; // Для выгрузки в 1С ТЗ
Важно помнить, что SQLite "проталкивает" условия WHERE из внешнего запроса внутрь VIEW, что способствует оптимизации. Однако пока SQLite не умеет отбрасывать лишние поля, если к ним нет обращения, поэтому создание двух колонок (одной обычной и одной типизированной) может немного замедлить работу на больших объемах данных, если типизированная колонка не нужна в финальном результате.
Помимо 1sqlite, существует еще одна мощная компонента для 1С 7.7 – 1С++ с ее объектом "Индексированная Таблица" (ИТЗ). ИТЗ также позволяет работать с Таблицами Значений в памяти с расширенными возможностями, такими как:
InnerJoin, LeftJoin, FullJoin) непосредственно между ТЗ.Если ваша задача сводится преимущественно к манипуляциям с уже имеющимися в памяти ТЗ, без необходимости обращаться к базе данных 1С напрямую через SQL, ИТЗ может быть более простым и быстрым решением. Выбор между 1sqlite и ИТЗ зависит от специфики задачи: 1sqlite предпочтительнее для SQL-ориентированных запросов к различным источникам, тогда как ИТЗ – для быстрых операций с ТЗ.
Мы рассмотрели, как с помощью компоненты 1sqlite в 1С 7.7 можно эффективно решать сложные задачи по работе с данными, объединяя Таблицы Значений и справочники 1С с использованием SQL-запросов. Автор темы успешно применил эти методы для автоматического определения групп товаров на основе их характеристик, что является отличным примером практического использования.
Основные выводы и рекомендации:
базаДанных.уложитьТЗ() для временного хранения данных из Таблиц Значений в SQLite.[Справочник.ИмяСправочника].WITH (CTE) или CREATE VIEW для улучшения читаемости и модульности.[ИмяКолонки $Тип1С.Субтип] применяйте только в финальном SELECT-запросе, который возвращает данные в 1С. В промежуточных CTE и VIEW используйте простые псевдонимы.SELECT name FROM pragma_table_info('ИмяТаблицы');.Внедрение этих подходов позволяет существенно расширить возможности 1С 7.7 по работе с данными, делая решение сложных аналитических и миграционных задач более эффективным и гибким.