При интеграции 1С 7.7 со сторонними системами (BI, веб-сервисы, внешние отчеты или сторонние СУБД) сторонние разработчики часто сталкиваются с техническими названиями таблиц и колонок в Microsoft SQL Server. Вместо понятных имен в базе 1С 7.7 используются системные идентификаторы: справочники хранятся в таблицах вида SC1234, шапки документов — в DH5678, табличные части — в DT5678, регистры — в RA9012 и RG9012, а поля именуются как sp2345.
Разберем, как при помощи внешней компоненты 1С++ (объектов MetaDataWork и ODBCRecordSet) автоматически сгенерировать набор SQL-представлений (CREATE VIEW), которые преобразуют внутреннюю структуру базы в привычные имена объектов и реквизитов на русском языке.
Прежде чем формировать скрипты представлений, проанализируем базовые правила и форматы хранения данных платформы 1С 7.7:
ID, IDDOC): сохраняются в виде 9-символьной строки в 36-ричной системе счисления (символы 0-9, A-Z). Первые 6 символов кодируют порядковый номер объекта, последние 3 символа — код узла базы (актуально при распределенной ИБ / УРИБ).DATE_TIME_IDDOC): имеет длину 23 символа. Первые 8 символов — дата документа в формате ГГГГММДД, следующие 6 символов — время в 36-ричном представлении (число десятых долей миллисекунды от начала суток), а завершающие 9 символов — идентификатор IDDOC.CHAR и дополняются пробелами справа. При внешних соединениях рекомендуется применять функцию RTRIM().SP<ИД> и маркера типа TSP<ИД>._1SCONST с привязкой по OBJID.Для корректного отображения поля DATE_TIME_IDDOC в виде стандартного типа DATETIME в SQL Server создадим вспомогательную скалярную функцию datetime_1s. Она переводит 36-ричное время платформы в стандартные часы, минуты и секунды.
CREATE FUNCTION [dbo].[datetime_1s]
(
@val varchar(23)
)
RETURNS datetime
AS
BEGIN
DECLARE @i bigint, @h int, @m int, @s int;
DECLARE @result datetime;
DECLARE @t char(8);
-- Декодирование 36-ричной строки времени (символы с 9 по 14)
DECLARE @tm varchar(6);
SET @tm = LTRIM(RTRIM(SUBSTRING(@val, 9, 6)));
DECLARE @abc char(36);
SET @abc = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE @n int;
DECLARE @ms bigint;
DECLARE @c char(1);
SET @n = 1;
SET @ms = 0;
WHILE @n <= LEN(@tm)
BEGIN
SET @c = SUBSTRING(@tm, @n, 1);
SET @ms = @ms * 36 + (CHARINDEX(@c, @abc) - 1);
SET @n = @n + 1;
END;
SET @ms = @ms / 10000; -- перевод в секунды
SET @h = CAST(@ms / 3600 AS int);
SET @ms = @ms - @h * 3600;
SET @m = CAST(@ms / 60 AS int);
SET @s = CAST(@ms - @m * 60 AS int);
SET @t = RIGHT('0' + CAST(@h AS varchar(2)), 2) + ':' +
RIGHT('0' + CAST(@m AS varchar(2)), 2) + ':' +
RIGHT('0' + CAST(@s AS varchar(2)), 2);
SET @result = CONVERT(datetime, (LEFT(@val, 4) + '-' + SUBSTRING(@val, 5, 2) + '-' + SUBSTRING(@val, 7, 2) + ' ' + @t), 120);
RETURN @result;
END
Рассмотрим готовую процедуру на встроенном языке 1С 7.7. С помощью объекта MetaDataWork мы получаем физические идентификаторы объектов и реквизитов, а через ODBCRecordSet отправляем DDL-запросы на создание представлений с хинтами (NOLOCK) для предотвращения блокировок при чтении.
Процедура Сформировать()
Перем МД, СКЛ, Префикс, ИмяОбъекта, Стр, инд, рек, тСпр, чРек, тРек, тКонст, пер, инд1, флЕстьДатаТайм;
МД = СоздатьОбъект("MetaDataWork");
СКЛ = СоздатьОбъект("ODBCRecordSet");
Префикс = "";
// 1. Журнал документов
ИмяОбъекта = Префикс + "Журнал";
Попытка
СКЛ.Выполнить("DROP VIEW dbo." + ИмяОбъекта);
Исключение
КонецПопытки;
Стр = "CREATE VIEW dbo." + ИмяОбъекта + "
|AS
|SELECT DATE_TIME_IDDOC ДатаДок, IDDOC Ссылка, IDDOCDEF Вид, DOCNO Номер, Closed Проведен, ISMARK ПометкаУдаления";
Для инд = 1 По Метаданные.ОбщийРеквизитДокумента() Цикл
Если Метаданные.ОбщийРеквизитДокумента(инд).Сортировка = 1 Тогда
Стр = Стр + ", sp" + МД.ИДОбъекта(Метаданные.ОбщийРеквизитДокумента(инд)) + " " +
Метаданные.ОбщийРеквизитДокумента(инд).Идентификатор;
КонецЕсли;
КонецЦикла;
Стр = Стр + "
|FROM dbo._1SJOURN (NOLOCK)";
Попытка
СКЛ.Выполнить(Стр);
Исключение
Сообщить("Не создан журнал документов...", "!");
КонецПопытки;
// 2. Документы (Шапки и Табличные части)
Для инд = 1 По Метаданные.Документ() Цикл
// Шапка документа
ИмяОбъекта = Префикс + "Док_" + Метаданные.Документ(инд).Идентификатор;
Попытка
СКЛ.Выполнить("DROP VIEW dbo." + ИмяОбъекта);
Исключение
КонецПопытки;
Стр = "CREATE VIEW dbo." + ИмяОбъекта + "
|AS
|SELECT IDDOC Ссылка, " + МД.ИДОбъекта(Метаданные.Документ(инд)) + " Вид";
Для рек = 1 По Метаданные.ОбщийРеквизитДокумента() Цикл
Если Метаданные.ОбщийРеквизитДокумента(рек).Сортировка = 0 Тогда
Стр = Стр + ", sp" + МД.ИДОбъекта(Метаданные.ОбщийРеквизитДокумента(рек)) + " " +
Метаданные.ОбщийРеквизитДокумента(рек).Идентификатор;
КонецЕсли;
КонецЦикла;
Для рек = 1 По Метаданные.Документ(инд).РеквизитШапки() Цикл
Стр = Стр + "
|, sp" + МД.ИДОбъекта(Метаданные.Документ(инд).РеквизитШапки(рек)) + " " +
Метаданные.Документ(инд).РеквизитШапки(рек).Идентификатор;
КонецЦикла;
Стр = Стр + "
|FROM dbo.DH" + МД.ИДОбъекта(Метаданные.Документ(инд)) + " (NOLOCK)";
Попытка
СКЛ.Выполнить(Стр);
Исключение
Сообщить("Не создана шапка документа: " + Метаданные.Документ(инд).Идентификатор, "!");
КонецПопытки;
// Табличная часть документа
Если Метаданные.Документ(инд).РеквизитТабличнойЧасти() <> 0 Тогда
ИмяОбъекта = Префикс + "Док_" + Метаданные.Документ(инд).Идентификатор + "_ТЧ";
Попытка
СКЛ.Выполнить("DROP VIEW dbo." + ИмяОбъекта);
Исключение
КонецПопытки;
Стр = "CREATE VIEW dbo." + ИмяОбъекта + "
|AS
|SELECT IDDOC Ссылка, " + МД.ИДОбъекта(Метаданные.Документ(инд)) + " Вид, LINENO_ НомерСтроки";
Для рек = 1 По Метаданные.Документ(инд).РеквизитТабличнойЧасти() Цикл
Стр = Стр + "
|, sp" + МД.ИДОбъекта(Метаданные.Документ(инд).РеквизитТабличнойЧасти(рек)) + " " +
Метаданные.Документ(инд).РеквизитТабличнойЧасти(рек).Идентификатор;
КонецЦикла;
Стр = Стр + "
|FROM dbo.DT" + МД.ИДОбъекта(Метаданные.Документ(инд)) + " (NOLOCK)";
Попытка
СКЛ.Выполнить(Стр);
Исключение
Сообщить("Не создана ТЧ документа: " + Метаданные.Документ(инд).Идентификатор, "!");
КонецПопытки;
КонецЕсли;
КонецЦикла;
// 3. Справочники
Для инд = 1 По Метаданные.Справочник() Цикл
ИмяОбъекта = Префикс + "Спр_" + Метаданные.Справочник(инд).Идентификатор;
Попытка
СКЛ.Выполнить("DROP VIEW dbo." + ИмяОбъекта);
Исключение
КонецПопытки;
Стр = "CREATE VIEW dbo." + ИмяОбъекта + "
|AS
|SELECT ID Ссылка, ISMARK ПометкаУдаления" +
?(Метаданные.Справочник(инд).ДлинаКода <> 0, ", CODE Код", "") +
?(Метаданные.Справочник(инд).ДлинаНаименования <> 0, ", DESCR Наименование", "") +
", " + МД.ИДОбъекта(Метаданные.Справочник(инд)) + " Вид";
Если СокрЛП(Метаданные.Справочник(инд).Владелец) <> "Метаданные" Тогда
Стр = Стр + ", PARENTEXT Владелец";
КонецЕсли;
Если Метаданные.Справочник(инд).КоличествоУровней <> 1 Тогда
Стр = Стр + ", PARENTID Родитель, ISFOLDER ЭтоГруппа";
КонецЕсли;
Для рек = 1 По Метаданные.Справочник(инд).Реквизит() Цикл
Если Метаданные.Справочник(инд).Реквизит(рек).Периодический = 1 Тогда
Продолжить;
КонецЕсли;
Стр = Стр + "
|, sp" + МД.ИДОбъекта(Метаданные.Справочник(инд).Реквизит(рек)) + " " +
Метаданные.Справочник(инд).Реквизит(рек).Идентификатор;
КонецЦикла;
Стр = Стр + "
|FROM dbo.SC" + МД.ИДОбъекта(Метаданные.Справочник(инд)) + " (NOLOCK)";
Попытка
СКЛ.Выполнить(Стр);
Исключение
Сообщить("Не создан справочник: " + Метаданные.Справочник(инд).Идентификатор, "!");
КонецПопытки;
КонецЦикла;
// 4. Регистры (Движения и Итоги)
Для инд = 1 По Метаданные.Регистр() Цикл
// Таблица движений
ИмяОбъекта = Префикс + "Рег_" + Метаданные.Регистр(инд).Идентификатор;
Попытка
СКЛ.Выполнить("DROP VIEW dbo." + ИмяОбъекта);
Исключение
КонецПопытки;
флЕстьДатаТайм = Метаданные.Регистр(инд).БыстраяОбработкаДвижений;
Если флЕстьДатаТайм = 0 Тогда
Для рек = 1 По Метаданные.Регистр(инд).Измерение() Цикл
Если Метаданные.Регистр(инд).Измерение(рек).ОтборДвижений = 1 Тогда
флЕстьДатаТайм = 1;
Прервать;
КонецЕсли;
КонецЦикла;
КонецЕсли;
Стр = "CREATE VIEW dbo." + ИмяОбъекта + "
|AS
|SELECT IDDOC Ссылка, " + МД.ИДОбъекта(Метаданные.Регистр(инд)) + " Вид
|, LINENO_ НомерСтроки
|, ACTNO НомерДвижения
|, DEBKRED Движение
|" + ?(флЕстьДатаТайм = 1, ", IDDOCDEF ВидДокумента, DATE_TIME_IDDOC ДатаВремяИдДок", "") + "
|" + ?(флЕстьДатаТайм = 1, ", dbo.datetime_1s(DATE_TIME_IDDOC) ДатаВремя", "") + "
|";
// Измерения
Для рек = 1 По Метаданные.Регистр(инд).Измерение() Цикл
Стр = Стр + ", sp" + МД.ИДОбъекта(Метаданные.Регистр(инд).Измерение(рек)) + " " +
Метаданные.Регистр(инд).Измерение(рек).Идентификатор;
Если Метаданные.Регистр(инд).Измерение(рек).Тип = "Неопределенный" Тогда
Стр = Стр + ", tsp" + МД.ИДОбъекта(Метаданные.Регистр(инд).Измерение(рек)) + " " +
Метаданные.Регистр(инд).Измерение(рек).Идентификатор + "_Доп";
КонецЕсли;
КонецЦикла;
// Ресурсы
Для рек = 1 По Метаданные.Регистр(инд).Ресурс() Цикл
Стр = Стр + ", sp" + МД.ИДОбъекта(Метаданные.Регистр(инд).Ресурс(рек)) + " " +
Метаданные.Регистр(инд).Ресурс(рек).Идентификатор;
Если Метаданные.Регистр(инд).Ресурс(рек).Тип = "Неопределенный" Тогда
Стр = Стр + ", tsp" + МД.ИДОбъекта(Метаданные.Регистр(инд).Ресурс(рек)) + " " +
Метаданные.Регистр(инд).Ресурс(рек).Идентификатор + "_Доп";
КонецЕсли;
КонецЦикла;
// Реквизиты
Для рек = 1 По Метаданные.Регистр(инд).Реквизит() Цикл
Стр = Стр + ", sp" + МД.ИДОбъекта(Метаданные.Регистр(инд).Реквизит(рек)) + " " +
Метаданные.Регистр(инд).Реквизит(рек).Идентификатор;
Если Метаданные.Регистр(инд).Реквизит(рек).Тип = "Неопределенный" Тогда
Стр = Стр + ", tsp" + МД.ИДОбъекта(Метаданные.Регистр(инд).Реквизит(рек)) + " " +
Метаданные.Регистр(инд).Реквизит(рек).Идентификатор + "_Доп";
КонецЕсли;
КонецЦикла;
Стр = Стр + "
|FROM dbo.RA" + МД.ИДОбъекта(Метаданные.Регистр(инд)) + " (NOLOCK)";
Попытка
СКЛ.Выполнить(Стр);
Исключение
Сообщить("Не созданы движения регистра: " + Метаданные.Регистр(инд).Идентификатор, "!");
КонецПопытки;
// Таблица итогов
ИмяОбъекта = Префикс + "Рег_" + Метаданные.Регистр(инд).Идентификатор + "_Итоги";
Попытка
СКЛ.Выполнить("DROP VIEW dbo." + ИмяОбъекта);
Исключение
КонецПопытки;
Стр = "CREATE VIEW dbo." + ИмяОбъекта + "
|AS
|SELECT " + МД.ИДОбъекта(Метаданные.Регистр(инд)) + " Вид, PERIOD ДатаИтогов";
Для рек = 1 По Метаданные.Регистр(инд).Измерение() Цикл
Стр = Стр + ", sp" + МД.ИДОбъекта(Метаданные.Регистр(инд).Измерение(рек)) + " " +
Метаданные.Регистр(инд).Измерение(рек).Идентификатор;
КонецЦикла;
Для рек = 1 По Метаданные.Регистр(инд).Ресурс() Цикл
Стр = Стр + ", sp" + МД.ИДОбъекта(Метаданные.Регистр(инд).Ресурс(рек)) + " " +
Метаданные.Регистр(инд).Ресурс(рек).Идентификатор;
КонецЦикла;
Стр = Стр + "
|FROM dbo.RG" + МД.ИДОбъекта(Метаданные.Регистр(инд)) + " (NOLOCK)";
Попытка
СКЛ.Выполнить(Стр);
Исключение
Сообщить("Не созданы итоги регистра: " + Метаданные.Регистр(инд).Идентификатор, "!");
КонецПопытки;
КонецЦикла;
Сообщить("Создание SQL-представлений успешно завершено!");
КонецПроцедуры
При использовании SQL-представлений для чтения данных 1С 7.7 необходимо учитывать следующие архитектурные особенности:
spXXXX, DHXXXX) могут сместиться. Процедуру пересоздания представлений необходимо запускать повторно после каждой реструктуризации базы.SELECT). Выполнять операции вставки (INSERT), обновления (UPDATE) или удаления (DELETE) напрямую в базу 1С 7.7 строго не рекомендуется. В платформе 1С 7.7 нет внешних ключей и триггеров на уровне SQL — вся целостность данных, блокировки и актуализация итогов контролируются ядром клиентского приложения.UNION ALL по всем справочникам обращается к общей таблице _1SCONST. При работе с большими базами такие запросы требуют обязательной фильтрации по OBJID и DATE во избежание полного сканирования таблицы (Table Scan).