SQL-запросы к базе 1С: таблицы, структура хранения и внешние источники

Как устроены таблицы 1С в СУБД

Когда вы создаёте объект метаданных, платформа раскладывает его по таблицам СУБД с автоматически сгенерированными именами. Имя состоит из префикса типа объекта и порядкового номера: нужной таблицы вы не угадаете по названию справочника. Основные префиксы такие:

Объект метаданных	Таблица в СУБД	Примеры полей
Справочник	_Reference123	_IDRRef, _Code, _Description, _Marked
Документ	_Document123	_IDRRef, _Date_Time, _Number, _Posted
Регистр сведений	_InfoRg123	_Period, _Recorder…, измерения и ресурсы
Регистр накопления	_AccumRg123, итоги _AccumRgT…	_Period, _RecordKind, _Active
Табличная часть	_Document123_VT456	номер строки, поля реквизитов

Реквизиты, измерения и ресурсы хранятся в полях вида _Fld789, где число — внутренний номер поля. Если реквизит имеет ссылочный тип, к имени добавляется суффикс RRef (например, _Fld789RRef) и поле содержит идентификатор записи. Реквизит составного типа разворачивается в несколько полей: для типа, значения и самой ссылки.

Получаем соответствие метаданных и таблиц

Платформа сама подскажет, как называется таблица. Функция ПолучитьСтруктуруХраненияБазыДанных() возвращает таблицу значений с описанием: имя объекта в терминах 1С, имя таблицы хранения, назначение таблицы (основная, табличная часть и т. д.) и поля. Вызывайте её от администратора, на сервере или в толстом клиенте. Без параметров она вернёт данные по всей базе, а можно передать массив нужных объектов метаданных. Подробнее о работе с результатом — в статье про таблицу значений.

&НаСервере
Процедура ВывестиСтруктуруХранения()

    Объекты = Новый Массив;
    Объекты.Добавить(Метаданные.Справочники.Номенклатура);
    Объекты.Добавить(Метаданные.Документы.РасходнаяНакладная);

    Структура = ПолучитьСтруктуруХраненияБазыДанных(Объекты, Истина);

    Для Каждого Таблица Из Структура Цикл
        Сообщить(Таблица.ИмяТаблицы + " -> " + Таблица.ИмяТаблицыХранения
            + " (" + Таблица.Назначение + ")");
        Для Каждого Поле Из Таблица.Поля Цикл
            Сообщить("    " + Поле.ИмяПоля + " = " + Поле.ИмяПоляХранения);
        КонецЦикла;
    КонецЦикла;

КонецПроцедуры

Для разового анализа удобнее выгрузить результат в табличный документ или таблицу на форме и отфильтровать по имени. Второй параметр определяет, как именовать поля — в терминах базы данных или в более удобном виде; назначение параметров и состав колонок результата уточните в синтаксис-помощнике своего релиза.

Примеры SELECT для чтения данных

Первый пример — выбрать неудалённые элементы справочника из MS SQL Server. Флаг пометки удаления в MS SQL хранится как бинарное значение, а в PostgreSQL — как логическое.

-- MS SQL Server
SELECT TOP 20
    _IDRRef,
    _Code,
    _Description
FROM dbo._Reference123 WITH (NOLOCK)
WHERE _Marked = 0x00
ORDER BY _Description;

-- PostgreSQL (имена приводятся к нижнему регистру)
SELECT _idrref, _code, _description
FROM _reference123
WHERE _marked = false
ORDER BY _description
LIMIT 20;

Второй пример — документы с соединением со справочником контрагентов. Колонка ссылки сравнивается с колонкой _IDRRef другой таблицы.

SELECT TOP 50
    d._Number,
    DATEADD(year, -2000, d._Date_Time) AS ДатаДокумента,
    k._Description AS Контрагент
FROM dbo._Document123 d WITH (NOLOCK)
    INNER JOIN dbo._Reference45 k WITH (NOLOCK) ON d._Fld789RRef = k._IDRRef
WHERE d._Posted = 0x01
ORDER BY d._Date_Time DESC;

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

Чтение регистров накопления

Записи регистра накопления лежат в таблице вида _AccumRg500. Поле _RecordKind показывает вид движения (приход — 0, расход — 1), а поле _Active — признак активности записи: неактивные движения, например от непроведённого документа, в расчёт не берутся. Остаток по номенклатуре проще и надёжнее получить суммированием движений, чем читать таблицу итогов _AccumRgT…: структура итогов зависит от настроек их использования и периода расчёта.

SELECT
    SUM(CASE WHEN _RecordKind = 0 THEN _Fld901 ELSE -_Fld901 END) AS Остаток
FROM dbo._AccumRg500 WITH (NOLOCK)
WHERE _Active = 0x01
  AND _Fld899RRef = @Номенклатура;

Если данные нужны другой системе регулярно, прямой доступ к таблицам — не лучший путь: надёжнее отдать их через HTTP-сервис в формате JSON, где 1С сама формирует выборку и отвечает за целостность.

Ссылки и даты

Идентификатор записи хранится в поле _IDRRef типа binary(16) (в PostgreSQL — bytea). Порядок байтов в нём не совпадает с текстовым представлением GUID в 1С, поэтому сравнивать ссылку со строкой напрямую нельзя: используйте связку по полям таблиц или преобразуйте значение отдельно.

Даты в MS SQL хранятся в типе datetime, у которого минимальная дата — 1753 год. Чтобы в нём помещалась пустая дата 1С (01.01.0001), платформа сдвигает все даты вперёд на «смещение дат» (2000 лет), которое задаётся при создании базы. В SQL-запросе смещение нужно вычитать, а при отборе — прибавлять, как это сделано в примере выше. Для PostgreSQL смещение обычно равно нулю, но конкретное значение зависит от настроек вашей базы.

Чтобы увидеть, какой SQL платформа генерирует из запроса на языке 1С (запросы в 1С), включите технологический журнал с событием DBMSSQL (для MS SQL) или DBPOSTGRS (для PostgreSQL): в записи будет полный текст SQL и время выполнения.

Почему нельзя писать в базу SQL-ом

• Кэш платформы. Рабочие процессы кэшируют данные и метаданные; изменение «мимо» платформы им неизвестно, и пользователи видят старые значения.
• Целостность. У записей есть служебные поля, версии, итоги регистров, индексы и ссылки на другие таблицы. Пропустите хотя бы одно, и база окажется в несогласованном состоянии.
• Блокировки. Платформа управляет блокировками на своём уровне, а прямое изменение их обходит.
• Поддержка. Условия использования платформы и поддержки не предполагают прямого изменения структуры и данных; при проблемах разбираться придётся с последствиями.
• Обновления. Номера таблиц и полей могут измениться после изменения конфигурации, и ваш скрипт молча повредит чужие данные.

Допустимо читать, при желании на реплике или копии базы. Менять данные нужно средствами платформы: объектами, наборами записей и обработками.

Читать внешнюю SQL-базу из 1С: внешние источники данных

Обратная ситуация: в 1С нужно прочитать данные другой системы — складской базы, сайта, ERP. Самый «родной» вариант — объект конфигурации «Внешние источники данных». В Конфигураторе вы создаёте источник, задаёте строку соединения через драйвер ODBC (при необходимости установите драйвер на сервер 1С), добавляете нужные таблицы и поля. Пример строки для PostgreSQL:

Driver={PostgreSQL Unicode};Server=db.example.local;Port=5432;Database=shop;

Логин и пароль не хранят в конфигурации: их задают при запуске программно. Затем к таблицам внешнего источника обращаются из запроса как к обычным таблицам:

&НаСервере
Процедура ПрочитатьТоварыСклада()

    ВнешниеИсточникиДанных.СкладскаяБаза.УстановитьОбщиеПараметрыАвторизации("reader", ПарольИзХранилища());
    ВнешниеИсточникиДанных.СкладскаяБаза.УстановитьСоединение();

    Запрос = Новый Запрос;
    Запрос.Текст =
    "ВЫБРАТЬ
    |    Товары.Артикул КАК Артикул,
    |    Товары.Остаток КАК Остаток
    |ИЗ
    |    ВнешнийИсточникДанных.СкладскаяБаза.Таблица.Товары КАК Товары
    |ГДЕ
    |    Товары.Остаток > 0";

    Выборка = Запрос.Выполнить().Выбрать();
    Пока Выборка.Следующий() Цикл
        Сообщить(Выборка.Артикул + ": " + Выборка.Остаток);
    КонецЦикла;

КонецПроцедуры

Преимущества этого способа: данные соединяются с таблицами 1С прямо в запросе, права настраиваются штатно, работает на всех платформах сервера, а не только на Windows. Имена источника, таблицы и функции получения пароля подставьте свои.

ADODB.Connection через COM

Если внешних источников данных мало, а базу нужно прочитать один раз, остаётся COM-объект ADODB. Способ работает только на Windows (на Linux-сервере не сработает) и требует драйвера нужной разрядности: 64-разрядному серверу 1С нужен 64-разрядный драйвер. Закрывайте соединение в любом случае и всегда оборачивайте работу в Попытка:

Процедура ПрочитатьИзВнешнейБазы()

    Соединение = Неопределено;
    Попытка
        Соединение = Новый COMОбъект("ADODB.Connection");
        Соединение.ConnectionString =
            "Driver={SQL Server};Server=SQLSRV;Database=Shop;Uid=reader;Pwd=" + Пароль + ";";
        Соединение.Open();

        Набор = Новый COMОбъект("ADODB.Recordset");
        Набор.Open("SELECT TOP 100 Code, Name FROM Goods", Соединение);

        Пока Не Набор.EOF Цикл
            Сообщить(Строка(Набор.Fields("Code").Value) + " " + Строка(Набор.Fields("Name").Value));
            Набор.MoveNext();
        КонецЦикла;
        Набор.Close();
    Исключение
        Сообщить("Ошибка чтения: " + ОписаниеОшибки());
    КонецПопытки;

    Если Соединение <> Неопределено Тогда
        Соединение.Close();
    КонецЕсли;

КонецПроцедуры

Свойство EOF в ADO возвращает логическое значение, поэтому условие цикла записано как Не Набор.EOF; значения полей могут быть Null, поэтому приводите их через Строка(). Если в запрос нужно подставить пользовательские значения, используйте команду ADODB с параметрами, а не склейку строки: так вы избежите SQL-инъекции.

Типичные ошибки

• Хранить в скрипте номера таблиц, которые «подсмотрели» в другой базе: в каждой базе они свои.
• Сравнивать даты без учёта смещения и получать пустые или пересекающиеся периоды.
• Выполнять прямые UPDATE и DELETE «временно», «только для исправления».
• Запускать тяжёлые SELECT по большим таблицам рабочей базы в рабочее время без ограничения выборки.
• Встраивать пароль к внешней базе в текст конфигурации.