Как получить понятное название и определить тип документа через прямой SQL-запрос к базе 1С

Разработчики 1С часто сталкиваются с необходимостью выполнять прямые SQL-запросы к базе данных под управлением MS SQL Server или PostgreSQL. Это требуется для оптимизации тяжелых отчетов, интеграции со сторонними BI-системами или быстрой выгрузки больших массивов данных. Однако структура таблиц 1С в СУБД спроектирована так, что в ней отсутствуют привычные и понятные названия объектов метаданных. Вместо таблицы СписаниеТоваров мы видим системное имя вида _Document191, а вместо названий типов документов — зашифрованные бинарные идентификаторы.

В этой статье мы подробно разберем, как устроена связь между физическими таблицами SQL и логической структурой 1С, как расшифровать тип документа по его ссылке из составного типа данных и как получить понятное человеку название объекта метаданных.

Почему 1С хранит данные в скрытом виде?

Платформа 1С:Предприятие абстрагирует разработчика от физической структуры базы данных. При создании нового документа или справочника в конфигураторе платформа автоматически генерирует таблицы в СУБД, присваивая им внутренние индексы. Это обеспечивает переносимость конфигурации между различными СУБД и независимость бизнес-логики от особенностей SQL. В самих транзакционных таблицах СУБД (например, в таблицах документов или регистров накопления) текстовые названия типов документов не хранятся из соображений производительности и экономии дискового пространства.

Разбор составных типов данных: TRef и RRef

Когда мы обращаемся к регистрам (например, к регистру накопления «Товары на складах»), поле Регистратор (ссылка на документ, сделавший движение) может принимать значения разных типов документов: «Поступление товаров», «Списание товаров», «Перемещение» и так далее. В SQL-базе такое составное поле разбивается на несколько колонок. Проанализируем основные из них:

1. _RecorderTRef (Type Reference) — это уникальный внутренний идентификатор типа объекта (метаданных) в системе 1С. Это поле указывает на конкретную таблицу документа в базе данных.
2. _RecorderRRef (Row Reference) — это уникальный 16-байтовый бинарный идентификатор (GUID) самой записи (документа) в соответствующей таблице.

Именно значение в колонке _TRef позволяет нам однозначно понять, к какой физической таблице относится документ, не прибегая к медленному перебору всех существующих таблиц документов в базе данных.

Как сопоставить шестнадцатеричный TRef с именем таблицы

Рассмотрим конкретный пример. Допустим, при выполнении SQL-запроса к таблице движений регистра накопления в колонке _RecorderTRef мы получили бинарное значение 0x000000BF. Чтобы понять, какая таблица хранит этот документ, нам нужно выполнить перевод числа из шестнадцатеричной системы счисления в десятичную.

Переведем значение BF в десятичный вид:

B в шестнадцатеричной системе соответствует числу 11. Вычисляем: 11 * 16 + 15 (F) = 191.

Таким образом, десятичный идентификатор типа равен 191. Это означает, что сам документ и все его реквизиты хранятся в физической таблице с именем _Document191. Зная имя таблицы, мы можем легко получить номер, дату и любые другие реквизиты документа, сделав к ней соответствующий запрос.

Решение 1: Использование метода ПолучитьСтруктуруХраненияБазыДанных()

Самый надежный и поддерживаемый способ сопоставить физические таблицы SQL с метаданными 1С — это использование встроенного языка платформы. Метод ПолучитьСтруктуруХраненияБазыДанных() возвращает полную информацию о соответствии всех объектов конфигурации таблицам в СУБД.

Выясним, как мы можем автоматизировать этот процесс и использовать полученную схему в прямых запросах. Для этого напишем обработку в 1С, которая выгрузит структуру во вспомогательную таблицу в базе данных SQL. Создадим в нашей СУБД служебную таблицу, например, dbo.MetadataMapping с полями TableTypeID (индекс таблицы), SQLTableName (имя таблицы в SQL) и PresentationName (человекочитаемое имя на русском языке).

Рассмотрим пример кода на стороне 1С для заполнения такой таблицы:

ТаблицаСтруктуры = ПолучитьСтруктуруХраненияБазыДанных(, Истина);
Для Каждого СтрокаТаблицы Из ТаблицаСтруктуры Цикл
    // Фильтруем только таблицы документов
    Если Найти(СтрокаТаблицы.ИмяТаблицыХранения, "_Document") > 0 Тогда
        // Получаем текстовое имя метаданных
        ИмяМетаданных = СтрокаТаблицы.Метаданные;
        // Выделяем числовой индекс таблицы из ее SQL-имени
        ИндексТаблицыSQL = Сред(СтрокаТаблицы.ИмяТаблицыХранения, 10); 
        
        // Здесь выполняется запись данных во внешнюю SQL-таблицу dbo.MetadataMapping
        // с помощью ADO или внешнего источника данных
    КонецЕсли;
КонецЦикла;

После того как вспомогательная таблица заполнена, мы можем легко связывать ее в наших прямых SQL-запросах через оператор JOIN, мгновенно получая понятные названия документов прямо в результате выборки.

Решение 2: Чтение структуры напрямую из системных таблиц СУБД (Params и dbnames)

Если у нас нет возможности запустить сеанс 1С для выгрузки структуры (например, мы пишем полностью внешнюю утилиту интеграции), структуру метаданных можно извлечь напрямую из СУБД.

Вся информация о структуре таблиц хранится в служебной таблице базы данных 1С, которая называется Params (или в таблице Config в зависимости от версии платформы). Нам нужна запись, у которой в поле FileName указано значение dbnames. Данная запись представляет собой бинарный файл (Blob), сжатый по алгоритму Deflate (без стандартного заголовка zlib).

Разберем по шагам алгоритм извлечения этих данных:

1. Выполняем SQL-запрос для получения бинарного содержимого поля из таблицы Params по условию соответствия имени dbnames.
2. Передаем полученный бинарный поток в декомпрессор, работающий по алгоритму Deflate. Для этого в языках программирования вроде C# или Python существуют готовые библиотеки работы со сжатыми потоками.
3. Распакованный поток представляет собой текстовый файл в специальном внутреннем формате 1С (похожем на сериализованный JSON/XML). В этом файле содержится строгое соответствие внутренних UUID объектов метаданных (например, документов, справочников, реквизитов) и имен физических таблиц СУБД.
4. Пропарсив полученный файл соответствий, мы строим карту метаданных, которую можно использовать для расшифровки любых прямых запросов.

Решение практической задачи: Поиск последней операции над номенклатурой

Проанализируем конкретную задачу, упомянутую автором обсуждения: необходимо получить последнюю операцию по определенной номенклатуре (продажа, списание или перемещение) и определить тип этого события. Для решения этой задачи не нужно делать ресурсоемкий глобальный поиск по всем таблицам документов. Достаточно обратиться к таблице движений соответствующего регистра накопления (например, регистра «Товары на складах» — _AccumRg123).

Посмотрим на пример эффективного SQL-запроса, который решает эту задачу с использованием полей _Period (дата движения) и _RecorderTRef (идентификатор типа документа):

WITH LastMovements AS (
    SELECT 
        _Fld123_RRRef AS NomenclatureRef,
        _Period AS Period,
        _RecorderTRef AS RecorderType,
        _RecorderRRef AS RecorderUID,
        ROW_NUMBER() OVER (PARTITION BY _Fld123_RRRef ORDER BY _Period DESC) AS RowNum
    FROM 
        _AccumRg123
    WHERE 
        _Fld123_RRRef = 0x80E100155D010A0311E3B8789C1FDE6C -- Ищем по конкретной номенклатуре
)
SELECT 
    NomenclatureRef,
    Period,
    RecorderType,
    RecorderUID
FROM 
    LastMovements
WHERE 
    RowNum = 1;

В результате этого запроса мы получаем точную дату последней операции и идентификатор типа документа в поле RecorderType. Если в поле RecorderType вернулось значение 0x000000BF, мы сопоставляем его с таблицей метаданных и понимаем, что последней операцией было «Списание товаров» из таблицы _Document191. После этого мы можем точечно обратиться к этой таблице по значению RecorderUID, чтобы забрать номер и дату документа для вывода пользователю.

Особенности работы с датами и пустыми ссылками в SQL 1С

При написании прямых SQL-запросов к базам 1С обязательно учитывайте две важные технические особенности:

1. Смещение дат на 2000 лет. Платформа 1С хранит даты в СУБД со смещением в 2000 лет (для предотвращения ошибок выхода за границы минимальной даты в различных базах данных). Чтобы получить реальную дату в SQL-запросе, используйте функцию смещения дат: DATEADD(year, -2000, _Period).
2. Пустые ссылки в составных полях. Если поле регистратора или документа не заполнено, в SQL-таблице оно будет представлено как набор нулевых байтов (например, 0x00000000000000000000000000000000). Всегда делайте проверку на равенство нулевому значению при фильтрации и левых соединениях, чтобы избежать неверных результатов.

Таким образом, зная принципы хранения составных типов данных и понимая логику формирования имен физических таблиц СУБД, мы можем строить быстрые, стабильные и производительные интеграционные решения, работающие напрямую с базой данных 1С.