Спускаемся в 1С 8.2 на уровень Базы Данных (Часть2)

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

Для решения каких задач нам могут понадобиться прямые запросы к базе данных

• Думаю данная возможность понадобиться при активной разработки своих собственных решений, либо при реструктуризации готовых решений. В тех случаях, когда в отладочных целях, либо ещё по каким-то причинам, нам придётся переносить большие объемы данных с одной таблицы в другую, либо разбивать данные на несколько таблиц
• Для интеграции 1С с другими, сторонними разработками. Например вывод данных из 1С, в какую-нибудь стороннюю программу анализа продаж или что-то похожее.
• Оптимизация массивных обработок данных. Когда нам необходимо обработать большое количество данных, при этом внося какие-то изменения, корректировки и т.п. Например копирование записей регистра сведений с изменением какого-либо поля средствами 1С, займет куда больше времени, чем выполнение операции T-SQL Update

Как во время исполнения приложения, подключиться к базе данных

Первым делом нам понадобится строка подключения к базе данных. Сначала я думал что в 1С такая строка будет выглядеть как-то по особенному, но поскольку мы используем ADO компоненты, то и строка подключения для него выглядит совершенно стандартно. Так же порадовала возможность формировать эту строку через стандартный интерфейс Windows (см. рис. 1).

Рисунок 1. Интерфейс настройки подключения к базе данных.

Давайте рассмотрим пример работы с ADO.

В данном фрагменте кода, мы создаем объект подключения к базе данных. А так же с помощью объекта DataLinks, получаем строку подключения к базе данных.

	Соединение = Новый COMОбъект("ADODB.Connection");
	ДатаЛ = Новый COMОбъект("DataLinks");
	Соединение.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;"; // Зададим первоначальные настройки, что бы не выбирать руками вид СУБД.
	ДатаЛ.PromptEdit(Соединение); // Отобразим диалог настройки подключения к базе (см. рис. 1)
	ConnectionString = Соединение.ConnectionString; // Полученную строку присвоим к глобальной переменной, для дальнейшей работы с ней. 
	Соединение = Неопределено;

Таким образом, мы получили строку подключения к базе. Теперь для удобства использования, нам наверняка хотелось бы сохранить параметры этой строки в некоторых элементах управления, на форме нашей обработки. Я пока нашел лишь один способ это сделать, и привожу его ниже. Если кто-то подскажет более элегантный способ парсинга строк, буду признателен.

//Данная функция парсит строку подключения и извлекает необходимое значение
Функция ЗначениеИзСтрокиПодключения(СтрокаПодключения,Значение)
	Результат = "";
	ПозицияСтарта = Найти(СтрокаПодключения,Значение+"=");
	Если ПозицияСтарта > 0 Тогда
		ПозицияСтарта = ПозицияСтарта + СтрДлина(Значение)+1;
	Иначе 
		Возврат Результат;
	КонецЕсли;
	К = ПозицияСтарта;
	Дл = СтрДлина(СтрокаПодключения);
	Пока (Сред(СтрокаПодключения,К,1) <> ";") И (К <= Дл) Цикл
		К = К + 1;
	КонецЦикла;
	ПозицияКонца = К;
	КолСимв = ПозицияКонца - ПозицияСтарта;
	Результат = Сред(СтрокаПодключения, ПозицияСтарта, КолСимв);
	Возврат Результат;
КонецФункции

...
//Фрагмент кода, события где присваиваем значения из строки в отображаемые контролы на форме.
	Если Найти(ConnectionString,"Integrated Security") = 0 Тогда
		флВинАутентификация = Ложь;
		ЭлементыФормы.Пароль.Значение = ЗначениеИзСтрокиПодключения(ConnectionString,"Password");
		ЭлементыФормы.Пользователь.Значение = ЗначениеИзСтрокиПодключения(ConnectionString,"User ID");
	Иначе
		флВинАутентификация = Истина;
		ЭлементыФормы.Пользователь.Значение = "";
		ЭлементыФормы.Пароль.Значение = "";
	КонецЕсли;
	
	ЭлементыФормы.ИмяБазыДанных.Значение = ЗначениеИзСтрокиПодключения(ConnectionString,"Initial Catalog");
	ЭлементыФормы.Сервер1С.Значение = ЗначениеИзСтрокиПодключения(ConnectionString,"Data Source");
...

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

И отмечаем нужные объекты формы флажками.

Далее надо предусмотреть вариант, когда пользователь (чаще всего мы сами), изменит наименование базы данных или тайм аут прямо в форме обработки, минуя форму редактирования строки подключения. На такой случай создадим такую функцию, которая будет формировать строку подключения собирая данные из визуальных контролов формы. Выглядит она примерно так:

Функция ПолучитьСтрокуПодключения()
	Строка = "Provider=SQLOLEDB.1;";
	Если ЭлементыФормы.флВинАутентификация.Значение Тогда
		Строка = Строка + "Integrated Security=SSPI;"
	Иначе
		Строка = Строка + "Password="+ЭлементыФормы.Пароль.Значение+";";
		Строка = Строка + "Persist Security Info=True;";
		Строка = Строка + "User ID="+ЭлементыФормы.Пользователь.Значение+";";
	КонецЕсли;
	Строка = Строка + "Initial Catalog="+ИмяБазыДанных+";Data Source="+Сервер1С;
	
	Возврат Строка;
КонецФункции

Теперь когда все (или почти все) вопросы со строкой подключения решены, давайте попытаемся воспользоваться ею, для обращения к базе данных.
В первую очередь, думаю нам понадобится функция для проверки подключения. Опишем её таким образом:

// Параметр "CS" - это строка подключения которую мы можем передать для проверки. Если не передаем, 
// то строка собирается из визуальных контролов
Функция ПроверкаПодключения(CS = "")
	
	Соединение = Новый COMОбъект("ADODB.Connection"); //Создаем подключение
	Если CS = "" Тогда 
		СтрокаКоннекта = ПолучитьСтрокуПодключения();
	Иначе
		СтрокаКоннекта = CS;
	КонецЕсли;
	Соединение.ConnectionTimeOut = 6; //Жестко зададим минимальный TimeOut что бы не ждать долго при неудачной попытке.
	Попытка
		Соединение.Open(СтрокаКоннекта);
	Исключение
		Сообщить(ОписаниеОшибки());
		Возврат Ложь;
	КонецПопытки;
	Возврат Истина;
	
КонецФункции

После выполнения данной функции нам станет ясно, можно ли работать дальше, или соединение с базой установить не удалось, и следовательно дальше что-либо делать с подключением — бесполезно. Кстати, для оптимизации функцию получения объекта ADODB.Connection можно разместить в общем модуле, в настройках которого выставлено «Повторное использование». Это позволит не создавать каждый раз новый объект подключения, а будет использоваться уже созданный объект. В теории это позволит сократить время вызова соединения, а так же совсем чуть-чуть сэкономит ресурсы системы.

Если тест подключения к базе проходит нормально, мы можем смело открывать подключение и используя его, обращаться к базе данных. Для того что бы выполнить запрос, нам понадобится объект ADODB.Command. Если наш запрос подразумевает возврат набора записей, или одного значения, в таком случае, нам так же понадобится объект ADODB.RecordSet. Каким образом мы можем работать с этими объектами в 1С? Давайте рассмотрим пример.
Первым делом нам необходимо создать и открыть подключение к базе данных. Делаем это так как показано в примере ниже.

	Соединение = Новый COMОбъект("ADODB.Connection");
	СтрокаКоннекта = ПолучитьСтрокуПодключения();
	Соединение.ConnectionTimeOut = Число(ЭлементыФормы.ТаймАут.Значение);
	Попытка
		Соединение.Open(СтрокаКоннекта);
	Исключение
		Сообщить(ОписаниеОшибки(),СтатусСообщения.ОченьВажное);
		Возврат Неопределено;
	КонецПопытки;

Следующим шагом нам необходимо используя данное подключение, выполнить T-SQL запрос. Следовательно необходимо создать объект ADODB.Command.

	ЗапросАДО = Новый COMОбъект("ADODB.Command");
	ЗапросАДО.CommandText = "Select * From SomeTable";
	ЗапросАДО.ActiveConnection = Соединение;

Причем заметьте, что свойству ActiveConnection мы присваиваем ранее созданное подключение к базе. Теперь когда объект у нас создан, нам остается лишь воспользоваться им. Если нам необходимо просто выполнить запрос, который не вернет никаких результатов, то будет достаточно одной простой команды, которая показана ниже.

ЗапросАДО.Execute();

После выполнения этой команды, в MS SQL будет выполнен T-SQL скрипт, который мы задали в свойстве CommandText у объекта ADODB.Command.
Если же нам необходимо получить какие-то данные из базы, в таком случае нам придется задействовать ещё один компонент: ADODB.RecordSet. Как понятно из названия — это набор записей. И результат выборки нам необходимо поместить именно туда, прежде чем мы сможем этот набор как-то обрабатывать.
Пример использования набора записей приведен ниже.

	Попытка
		НаборЗаписей = Новый COMОбъект("ADODB.ReсordSеt");
		НаборЗаписей = ЗапросАДО.Execute();
	Исключение
		Сообщить(ОписаниеОшибки(),СтатусСообщения.ОченьВажное);
		Возврат Неопределено;
	Конецпопытки;

	Попытка
		НаборЗаписей.MoveFirst();
	Исключение //нет записей в рекордсете
		Сообщить(ОписаниеОшибки(),СтатусСообщения.ОченьВажное);
		Возврат Неопределено;
	КонецПопытки;

	Пока НаборЗаписей.EOF() = 0 Цикл
		Сообщить(Строка(НаборЗаписей.Fields(0).Value));
		НаборЗаписей.MoveNext();
	КонецЦикла;
	
	Соединение.Close();
	ЗапросАДО = Неопределено;
	Соединение = Неопределено;

Как видно в примере, мы выполняем все туже команду ЗапросАДО.Execute(), только на этот раз результат этой команды присваивается объекту НаборЗаписей , который собственно и представляет из себя набор записей ADODB.RecordSet. Когда набор записей получает результат выборки, мы можем этот результат обрабатывать. Но первым делом нам надо сдвинуть курсор с места. Для этого мы выполняем первую команду НаборЗаписей.MoveFirst(). Мы оборачиваем её обработкой исключения потому что если набор записей не будет содержать ни одной строки, то данная команда приведет к ошибке. Поэтому мы намерено отлавливаем данное исключение и в случае если оно происходит, сообщаем об этом пользователю.
Если же записи имеются, то мы открываем цикл, который будет выполняться до тех пор, пока не встретиться конец набора записей. В цикле же мы описываем обработку данных. В своем примере я указал вывод через сообщение, значения нулевого поля запроса. Но при необходимости мы так же можем присваивать значения в структуры, массивы и списки значений.

Интеграция запросов T-SQL в конфигурацию 1С

Общие сведения

Когда мы пишем какой-то отладочный, разовый запрос, нам конечно же не составит труда написать его руками. Например воспользоваться готовой, либо самодельной обработкой для просмотра структуры базы данных, опираясь на данные этой обработки написать запрос, и выполнить его, причем выполнить его можно как средствами 1С, так через Managment Studio. Но как быть если мы собираемся использовать данный запрос в конфигурации постоянно, возможно на разных базах. А ведь у разных БД идентификаторы таблиц могут отличаться. Напишем мы например обращение к регистру сведений как к таблице _InfoRg7581, а на клиентской базе это может оказаться таблица _InfoRg10403. А такой как 7581 и вовсе нет. Это разумеется приведет к исключительной ситуации, и наша функция не отработает как положено.
А ещё можно рассмотреть ситуацию, когда у нас есть запрос внутри системы, написанный разумеется на языке 1С, и он допустим очень громоздкий и тяжелый. Писать с нуля такой же на T-SQL лень. Как быть?

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

Конечно мы довольно легко сможем заменить в тексте запроса такие служебные слова как Выбрать, Из, Где и другие. Но все таки в языке запросов 1С есть нюансы, которые так просто не обработать.
Во первых в 1С есть такое понятие как Виртуальная таблица. Например .Остатки() или ОстаткиИОбороты(). Если запрос содержит такого рода конструкции, без вмешательства человека обойтись не получится. Точнее можно, но для этого придется практически писать аналог 1С Сервера, который тем и занимается что полностью переводит 1С запросы, на T-SQL запросы.
Затем в T-SQL совершенно по другому выглядит запись данных во временные таблицы. Создание индексов. В 1С запросах есть специфичные для платформы функции которые могут использоваться в запросах, а в T-SQL таких функций нету, и наоборот. Поэтому просто заменить служебные слова 1С-запроса будет — недостаточно. И для нормальной трансляции потребуется наше активное участие.

Я не буду сейчас рассматривать всех нюансов трансляции, а обращу внимание лишь на самое важное и необходимое.

Если нам необходимо перевести в T-SQL запрос который уже написан в 1С представлении, мы можем воспользоваться самописной обработкой, либо вручную, перевести основные служебные слова запроса. Стоит помнить о том что SQL Server нормально воспринимает русскоязычные идентификаторы, поэтому если при формировании временной таблицы мы сформируем русские имена полей — это нормально. Транслит можно не применять.
Если в формате 1С, временные таблицы называются как и обычные, то в T-SQL перед именем временной таблицы необходимо поставить символ — #.
То есть выбор данных во временную таблицу будет выглядить примерно так:

Select 
*
INTO #BufTable
From _InfoRg14325

А для того что бы проиндексировать данную временную таблицу по каким-либо полям, то после окончания запроса, нам необходимо дописать следующую конструкцию:

CREATE INDEX i_vt_IndexName ON #VT_TableName(Field1,Field2..FieldN);

Если нам необходимо удалить временную таблицу, можно воспользоваться таким приемом:

IF OBJECT_ID('tempdb..#VT_TableName') IS NOT NULL
  DROP TABLE #VT_TableName

Это то, что чаще всего нам может пригодиться. Остальное же при необходимости можно узнать в справочниках по SQL.

Интегрируем T-SQL запрос в конфигурацию

Для интеграции в конфигурацию такого запроса, нам необходима универсальность. Для этого, после того как мы отладим сам запрос средствами MS SQL, и убедимся что он работает правильно, нам необходимо сделать из него заготовку. Что это значит? Это значит что мы берем наш запрос. Например он выглядит так:

Select 
_Period,
_Fld1232,
_Fld1233
From
_InfoRg0943

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

Select
Период,
Показатель,
Значение
From
РегистрСведений.ИнформацияСДатчиков

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

Для ещё большей оптимизации, мы можем подобные запросы формировать в виде хранимых процедур и функций в MS SQL. Создание данных функций включить в какой-нибудь модуль обновления базы. И однажды при обновлении данные функции будут созданы. А в коде 1С, мы будем просто вызывать хранимые процедуры и функции MS SQL, возможно передавая туда какие-то параметры.

Секрет полей ПланВидовХарактеристик раскрыт

Помню в прошлой статье я писал о том как устроены поля которые могут содержать ссылки на различные таблицы (справочники, документы). Но оставалось загадкой, что же все таки хранится в поле ТипСсылки. Так вот недавно данная тайна была раскрыта. В этом поле хранится индекс в 16-тиричной системе счисления. Если мы переведем этот индекс в 10-чную систему счисления, и затем посмотрим на список таблиц базы данных, то обязательно найдем таблицу, в имене которой содержится данный индекс. То есть по сути мы можем по этому индексу получить таблицу, в которой содержится элемент, на который ссылается ссылка в нашем поле.

Зная индекс, мы можем найти необходимую таблицу простым запросом:

Select [TABLE_NAME] From INFORMATION_SCHEMA.TABLES
where TABLE_NAME like '%[A-Z,a-z]1950'

Где 1950 — искомый индекс.

Пожалуй вопросы по созданию универсальных механизмов трансляции запросов и оптимизации T-SQL запросов потребуют отдельных статей. Поэтому продолжение следует. А на сегодня ограничимся этим.

Автор: ixilimuse

Источник