TMSSQL — работа с базами данных MS SQL Server в скриптах на OneScript и из командной строки

Подробное описание библиотеки и примеры использования в вебинаре ниже.

Из видео вы также узнаете о том как:

• Развернуть и настроить среду Visual Studio Code для разработки и отладки скриптов на OneScript
• Разрабатывать простые скрипты
• Устанавливать и подключать различные имеющиеся библиотеки
• Разрабатывать свои библиотеки и приложения на OneScript
• Использовать библиотеку TMSSQL для решения различных повседневных задач

Ссылки из вебинара:

• Создаем свою библиотеку для OneScript
• Обзор имеющихся библиотек OneScript

Предыдущие вебинары на данную тему:

• С чего начать разработку скриптов на OneScript? Первые шаги
• Сервер администрирования кластера серверов 1C:Предприятия и deployka

Пример использования библиотеки в приложении:

• "Перезаливатор" — приложение для автоматизации "перезаливки" баз

Вебинары по SQL-скриптам:

• Автоматизируем "перезаливку" баз (Скрипты для SQL-Server — Часть 1)
• Регламентные операции с индексами в MS SQL Server (Скрипты для SQL-Server — Часть 2)
• Еще немного полезных SQL-скриптов (Скрипты для SQL-Server — Часть 3)

Описание библиотеки TMSSQL

Приложение для работы с базами данных на MS SQL Server. Реализовано на OneScript. Работает в режиме:

• Консольного приложения
• Библиотеки для разработки скриптов на OneScript

Официальная страница репозитория: https://github.com/Tavalik/TMSSQL

Для подключения к MS SQL-серверу используется COM-Объект ADODB.Connection:

Соединение  = Новый COMОбъект("ADODB.Connection");
Соединение.ConnectionString =
"driver={SQL Server};" +
"server="+ПараметрыПодключения.АдресСервераSQL+";"+
"uid="+ПараметрыПодключения.ИмяПользователяSQL+";"+
"pwd="+ПараметрыПодключения.ПарольПользователяSQL+";" +
"database="+БазаДанных+";";
Соединение.ConnectionTimeout = ПараметрыПодключения.ConnectionTimeout;
Соединение.CommandTimeout = ПараметрыПодключения.CommandTimeout;
Соединение.Open();

Соответственно, приложение работает только в ОС семейства Windows.

Установка

Установка через пакетный менеджер opm командой:

opm install tmssql

Или копированием всего репозитория по прямой ссылке: https://github.com/Tavalik/TMSSQL/archive/master.zip

Работа в режиме приложения

Исполняемый файл: C:Program Files (x86)OneScriptinTMSSQL.bat

Команды:

• help — Вывод справки по параметрам
• createdatabase — Создание базы данных
• dropdatabase — Удаление базы данных
• setrecovery — Изменение модели восстановления
• backupdatabase — Создание резервной копии
• restoredatabase — Восстановление базы данных
• shrinkfile — Сжатие файлов базы данных
• shrinkdatabase — Сжатие базы данных
• deletefile — Удаление файлов на сервере

Пример использования (bat-файл):

@echo off

setlocal

set server="10.1.1.40"
set uid="sa"
set pwd="pass"
set database="Test_OS_TMSSQL"
set connectionstring=-server %server% -uid %uid% -pwd %pwd% -database %database%

rem Вывод справки
echo ----------------------------------------------
echo help:
call TMSSQL help

rem Создание базы данных
echo ----------------------------------------------
echo createdatabase:
call TMSSQL createdatabase %connectionstring%

rem Изменение модели восстановления
echo ----------------------------------------------
echo setrecovery:
call TMSSQL setrecovery FULL %connectionstring%

rem Создание резервных копий
echo ----------------------------------------------
echo backupdatabase:
set file_FULL=%database%_FILE_FULL.bak
set file_DIFF=%database%_FILE_DIFF.bak
set file_LOG=%database%_FILE_LOG.trn
call TMSSQL backupdatabase "" %file_FULL% FULL %connectionstring%
TIMEOUT 1 /NOBREAK
call TMSSQL backupdatabase "" %file_DIFF% DIFFERENTIAL %connectionstring%
TIMEOUT 1 /NOBREAK
call TMSSQL backupdatabase "" %file_LOG% LOG %connectionstring%
TIMEOUT 1 /NOBREAK

rem Восстановление базы данных
echo ----------------------------------------------
echo restoredatabase:
call TMSSQL restoredatabase %connectionstring%

rem Удаление файлов на сервере
echo ----------------------------------------------
echo deletefile:
call TMSSQL deletefile %file_FULL% %connectionstring%
call TMSSQL deletefile %file_DIFF% %connectionstring%
call TMSSQL deletefile %file_LOG% %connectionstring%

rem Сжатие файлов базы данных
echo ----------------------------------------------
echo shrinkfile:
call TMSSQL shrinkfile LOG %connectionstring%

rem Сжатие базы данных
echo ----------------------------------------------
echo shrinkdatabase:
call TMSSQL shrinkdatabase %connectionstring%

rem Удаление базы данных
echo ----------------------------------------------
echo dropdatabase:
call TMSSQL dropdatabase %connectionstring%

Использование в качестве библиотеки

Библиотека подключается как отдельный класс. Экземпляр класса используется для работы с базами на конкретном SQL-Сервере. Может также работать в качестве модуля.

Подключение библиотеки:

#Использовать TMSSQL

Создание класса:

УправлениеMSSQL = Новый УправлениеMSSQL();

Параметры класса:

• ПараметрыПодключения — Структура параметров подключения. Содержание структуры:
  • АдресСервераSQL — Сетевой адрес MS SQL Server
  • ИмяПользователяSQL — Имя пользователя для подключения к MS SQL Server
  • ПарольПользователяSQL — Пароль пользователя для подключения MS SQL Server
  • ИмяБазыДанныхSQL — Имя базы данных, в которой по умолчанию будут выполняться все запросы
  • ConnectionTimeout — Количество секунд для ожидания подключения, по умолчанию — 30
  • CommandTimeout — Количество секунд для выполнения команды, по умолчанию — 600
• ТекстОшибки — Переменная для возврата ошибки, если таковая имела место 
• РежимОтладки — Переменная для включения отладки, по умолчанию — Ложь

Процедуры и функции (описание параметров смотрите к описанию процедур и функций в файле TРаботаСMSSQL.os):

• ВыполнитьЗапрос() — Выполняет произвольный запрос в указанной базе данных
• СоздатьБД() — Создает на сервере MS SQL новую базу данных. Если база данных с данным именем уже существует, ничего не происходит
• УдалитьБД() — Удаляет базу данных с сервера MS SQL с указанным именем. Если база данных не найдена на сервере, ничего не происходит
• УдалитьФайлНаСервере() — Удаляет файл на сервере MS SQL
• ПолучитьСтруктуруФайловБД() — Получает данные файлов базы данных из параметров подключения
• СделатьРезервнуюКопиюБД() — Создает резервную копию базы данных из параметров подключения
• ПолучитьСписокФайловДляВосстановленияБД() — Получает последовательность файлов для восстановления базы данных из параметров подключения на указанную дату
• ВосстановитьИзРезервнойКопииБД() — Восстанавливает базу данных из параметров подключения по переданным именам файлов
• ВосстановитьБД() — Восстанавливает базу данных из параметров подключения на указанную дату
• ИзменитьМодельВосстановленияБД() — Изменяет модель восстановления базы данных из параметров подключения
• СжатьБД() — Сжимает базу данных из параметров подключения
• СжатьФайлыБД() — Сжимает файлы базы данных из параметров подключения

Пример использования (os-файл):

// Подключение библиотеки
#Использовать TMSSQL

// Создадим объект
УправлениеMSSQL = Новый УправлениеMSSQL();

// Введем параметры
ПараметрыПодключения = УправлениеMSSQL.ПараметрыПодключения;
ПараметрыПодключения.АдресСервераSQL = "10.1.1.40";
ПараметрыПодключения.ИмяПользователяSQL = "sa";
ПараметрыПодключения.ПарольПользователяSQL = "pass";
ПараметрыПодключения.ИмяБазыДанныхSQL = "Test_OS_TMSSQL";

БылиОшибки = Ложь;

// Создадим базу данных
Если УправлениеMSSQL.СоздатьБД() Тогда
Сообщить("СоздатьБД: УСПЕШНО");
Иначе
Сообщить("СоздатьБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;

// Получим список файлов базы данных
ТаблицаФайловБД = УправлениеMSSQL.ПолучитьСтруктуруФайловБД();
Если ТаблицаФайловБД <> Неопределено Тогда
Сообщить("ПолучитьСтруктуруФайловБД: УСПЕШНО");
Для Каждого ФайлБД Из ТаблицаФайловБД Цикл
Сообщить(" Имя: " + ФайлБД.ЛогическоеИмя + ", Путь: " + ФайлБД.ФизическоеИмя + ", тип: " + ФайлБД.Тип + ", размер: " + ФайлБД.Размер + " Мб.");
КонецЦикла;
Иначе
Сообщить("ПолучитьСтруктуруФайловБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;

// Сменим модель восстановления базы на полную
Если УправлениеMSSQL.ИзменитьМодельВосстановленияБД("FULL") Тогда
Сообщить("ИзменитьМодельВосстановленияБД: УСПЕШНО");
Иначе
Сообщить("ИзменитьМодельВосстановленияБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;

// Сделаем полную резервную копию
ПолноеИмяФайла = УправлениеMSSQL.СделатьРезервнуюКопиюБД(,,"FULL");
Если ПолноеИмяФайла <> Неопределено Тогда
Сообщить("СделатьРезервнуюКопиюБД: УСПЕШНО в " + ПолноеИмяФайла);
Иначе
Сообщить("СделатьРезервнуюКопиюБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;
Приостановить(6000);
ДатаПолнойКопии = ТекущаяДата();

// Сделаем разностную резервную копию
ПолноеИмяФайла = УправлениеMSSQL.СделатьРезервнуюКопиюБД(,,"DIFFERENTIAL");
Если ПолноеИмяФайла <> Неопределено Тогда
Сообщить("СделатьРезервнуюКопиюБД: УСПЕШНО в " + ПолноеИмяФайла);
Иначе
Сообщить("СделатьРезервнуюКопиюБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;
Приостановить(6000);

// Сделем копию журнала транзакций
ПолноеИмяФайла = УправлениеMSSQL.СделатьРезервнуюКопиюБД(,,"LOG");
Если ПолноеИмяФайла <> Неопределено Тогда
Сообщить("СделатьРезервнуюКопиюБД: УСПЕШНО в " + ПолноеИмяФайла);
Иначе
Сообщить("СделатьРезервнуюКопиюБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;
Приостановить(6000);

// Сделем копию журнала транзакций
ПолноеИмяФайла = УправлениеMSSQL.СделатьРезервнуюКопиюБД(,,"LOG");
Если ПолноеИмяФайла <> Неопределено Тогда
Сообщить("СделатьРезервнуюКопиюБД: УСПЕШНО в " + ПолноеИмяФайла);
Иначе
Сообщить("СделатьРезервнуюКопиюБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;
Приостановить(6000);

// Восстаноим базу на дату создания полной копии
Если УправлениеMSSQL.ВосстановитьБД(ДатаПолнойКопии) Тогда
Сообщить("ВосстановитьБД: УСПЕШНО на дату " + ДатаПолнойКопии);
Иначе
Сообщить("ВосстановитьБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;

// Получим список файлов для восстановления на текущую дату
МассивФайлов = УправлениеMSSQL.ПолучитьСписокФайловДляВосстановленияБД();
Если МассивФайлов <> Неопределено Тогда
Сообщить("ПолучитьСписокФайловДляВосстановленияБД: УСПЕШНО");
Для Сч = 0 По МассивФайлов.Количество()-1 Цикл
Сообщить(" Файл: " + МассивФайлов.Получить(Сч));
КонецЦикла;
Иначе
Сообщить("ПолучитьСписокФайловДляВосстановленияБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;

// Восстановим базу данных по полученнуму ранее массиву файлов
Если УправлениеMSSQL.ВосстановитьИзРезервнойКопииБД(МассивФайлов) Тогда
Сообщить("ВосстановитьИзРезервнойКопииБД: УСПЕШНО");
Иначе
Сообщить("ВосстановитьИзРезервнойКопииБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;

// Удалим файлы резервных копий
Для Сч = 0 По МассивФайлов.Количество()-1 Цикл
ИмяФайлаДляУдаления = МассивФайлов.Получить(Сч);
Если УправлениеMSSQL.УдалитьФайлНаСервере(ИмяФайлаДляУдаления) Тогда
Сообщить("УдалитьФайлНаСервере: УСПЕШНО для " + ИмяФайлаДляУдаления);
Иначе
Сообщить("УдалитьФайлНаСервере: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;
КонецЦикла;

// Переведем базу в простую модель восстановления
Если УправлениеMSSQL.ИзменитьМодельВосстановленияБД("SIMPLE") Тогда
Сообщить("ИзменитьМодельВосстановленияБД: УСПЕШНО");
Иначе
Сообщить("ИзменитьМодельВосстановленияБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;

// Запустим сжатие файлов лога
Если УправлениеMSSQL.СжатьФайлыБД("LOG") Тогда
Сообщить("СжатьФайлыБД: УСПЕШНО");
Иначе
Сообщить("СжатьФайлыБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;

// Запустим сжатие базы данных
Если УправлениеMSSQL.СжатьБД() Тогда
Сообщить("СжатьБД: УСПЕШНО");
Иначе
Сообщить("СжатьБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;

// Удалим базу данных
Если УправлениеMSSQL.УдалитьБД() Тогда
Сообщить("УдалитьБД: УСПЕШНО");
Иначе
Сообщить("УдалитьБД: " + УправлениеMSSQL.ТекстОшибки);
БылиОшибки = Истина;
КонецЕсли;

// Отчет о работе
Сообщить("");
Сообщить("-----------------------------------------------");
Сообщить("Результат работы модуля: " + ?(БылиОшибки,"БЫЛИ ОШИБКИ","УСПЕШНО"));
Сообщить("-----------------------------------------------");
Сообщить("");

Пишите, если у вас будут вопросы или пожелания по использованию библиотеки.