Простой подход к версионированию баз данных MS SQL Server

Простой подход к версионированию баз данных MS SQL Server

Предисловие.

Очень часто в прикладных программах возникает необходимость использовать современные базы данных, которые предоставляют разработчику очень большой функционал, основанный не только на уровне данных, но также и созданию собственного API для предоставления доступа к этим данным, посредством хранимых процедур, триггеров, функций. Очевидно, что во всей этой структуре в зависимости от обстоятельств, могут понадобятся какие-либо изменения. И в самом безобидном случае, когда разработчик имеет дело с одним клиентом и одной изменяющейся базой данных (предположим на небольшом предприятии), процесс обновления выглядит просто – мы делаем необходимые изменения в структуре, сравниваем с помощью специальных утилит, например SQL Examiner, старую и новую версию и накатываем сгенерированный sql-скрипт на существующую базу. Как видно в описанном случае, миграция данных происходит каждый раз при обновлении структуры базы. Но к сожалению, описанная ситуация встречается крайне редко, чаще — клиенты и соответствующих для них базы данных для какого-либо продукта у разработчиков исчисляется сотнями, если не больше. Таким образом, для нормального жизненного цикла базы данных необходима система версионирования (не путать с системами версионирования исходников типа Subversion).

Подход

Чтобы лучше представлять назначение данной системы, рассмотрим следующий пример: имеется некое десктопное приложение – список сотрудников офиса, по требованию заказчика, эта программа должна хранить следующие данные: «Фамилия», «Имя», «Отчество», название должности и эта программа будет установлена в каждом из офисов фирмы, которые разбросаны по всему городу. Последний факт, говорит о том, что в будущем возможны ситуации, когда обновление версий ПО, будет происходить не синхронно, например возможен такой случай, что для программы выйдет уже 10 версия, а в каком-либо офисе все еще будет установлена наша первая версия, но при обновлении сразу до 10-ой, администратору, выполняющем данное обновление, не нужно будет устанавливать все 10 обновлений, а ему нужно предоставить сразу одно 10 обновление, которое самостоятельно обновит всю структуру и мигрирует данные для нового приложения. Таким образом, сформулируем главные требования к нашей системе версионирования баз данных:

1. Должна обеспечиваться сохранность данных в момент миграции
2. Данные должны мигрироваться независимо от их версии
3. Инструмент миграции должен быть как можно проще

Наиболее легкой версией задания команд миграции, является разработка скрипта миграции, на каком – либо языке, в этом примере будет использоваться стандартные для windows bat-файлы.
Теперь необходимо создать правила для сохранения сущностей.

1. Скрипт с DML определениями, создающий базу данных или изменяющий существующую структуру, должен находиться в одном файле, имя которому будет alter.sql .
2. Для каждой хранимой процедуры должен быть свой файл, как по созданию (для первой версии), так и по изменению(для последующих).
3. Для каждой сущности в базе данных максимально возможно пользоваться следующим правилом: создавать по четыре процедуры, для выборки, добавления, обновления, удаления сущности, которые должны быть именованы по правилу [ДействиеСущность]. Например, если у нас есть сущность книга – Book, то для доступа к ней мы создаем четыре процедуры – SelectBook, InsertBook, UpdateBook,DeleteBook. Введение новых правил допускается, но они должны быть максимально унифицированы.
4. Процедуры по работе с одной и той же сущностью должны быть сгруппированы в одной папке, носящей имя сущности.
5. Для каждой версии должны создаваться папки с номером реализуемой версии, а в них должны содержаться папки из шага 4, а также скрипт инициализации или изменения структуры базы данных из пункта 1.

Выполняя все вышеперечисленные правила, можно добиться жесткой каталогизации, с помощью которой мы придем к требуемой версионности.
Вернемся к нашему примеру. В первой версии у нас будет одна таблица Person, с тремя полями: FirstName, MiddleName, LastName. Для начала создадим папку Version – Она будет содержать версии, сейчас это версия 1, а под ней папка Person и скрипт инициализации, который представлен ниже:

CREATE TABLE Person
(
FirstName varchar(50),
MiddleName varchar(50),
LastName varchar(50)
)

* This source code was highlighted with Source Code Highlighter.Далее в папке персон создаем четыре хранимых процедуры, по правилу из пункта (3), называем их соответственно SelectPerson,InsertPerson,UpdatePerson,DeletePerson и помещаем внутрь папки Person, код из-за элементарности приводить не будем, а только посмотрим, что должно в итоге получиться:

Создание системы развертывания базы

Теперь, когда все файлы и каталоги собраны в определенную структуру, можно приступить к созданию механизма, который будет развертывать нашу базу. Для MS SQL сервер есть утилита по доступу с командной строки – sqlcmd.exe именно ее мы и возьмем как процессор команд sql. На момент исполнения предполагается, что путь к этой утилите прописан в переменной окружения Path. Для начала немного сведений про sqlcmd.exe:

1. Данная утилита может исполнять файлы, содержащие SQL команды, делается это с помощью ключа –i filename
2. Также она умеет определять переменные, которые можно использовать в sql коде, определение переменной происходит с помощью ключа –v param=value, а уже в коде к ним можно обращаться по синтаксису $(param), причем, исполняемый процессор просто заменяет эти токены на значение, которое было определено в вызове параметром -v
3. Утилита позволяет в sql скрипте вызывать другие sql скрипты, делается это с помощью директивы :r filename

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

1. Определяем, существует ли база данных, если нет, тогда создаем новую.
2. Делаем бекап данных.
3. Определяем, создана ли первоначальная настройка базы для версионирования, если нет, тогда создаем ее.
4. Определяем текущую версию и вызываем скрипт миграции.

Чтобы база могла хранить свою текущую версию (для шага 3), для этого необходимо создать таблицу с полем, где будет храниться эта версия:

CREATE TABLE Settings
(
  DbVersion int
)
go
INSERT INTO Settings(DbVersion) VALUES(1)
go

* This source code was highlighted with Source Code Highlighter.

При изменении версии базы, будет менятся соответствующие значение в поле DbVersion в созданной таблице.
Теперь определим какие параметры должен получать скрипт развертывания:

1. Имя базы данных — переменная workdbname .
2. Имя инстанса для служб SQL .
3. Путь к файлам базы – переменная databasepath.
4. Путь к файлу бекапа – переменная backuppath.

Этап подготовки

Здесь нам нужно будет написать логику проверки существования базы и создания новой. Сразу стоит оговориться, что утилита sqlcmd.exe при возникновении критической ошибки в выполнении сценария, например такой как деление на ноль, возвращает в среду код ошибки – 1. Этим можно пользоваться при исполнении bat-скрипта. Для упомянутой выше проверки, создадим следующие команды sql в файле CheckDbExists.sql:

use master;
IF NOT EXISTS (SELECT * FROM sysdatabases WHERE [Name] = $(workdbname)')
BEGIN
  SELECT 1/0;
END

* This source code was highlighted with Source Code Highlighter.
Теперь организуем первую проверку в файле deploy.bat:

"sqlcmd.exe" -S %2 -V 1 -v workdbname=%1 -i CheckDbExists.sql
IF ERRORLEVEL 1 GOTO :CREATENEW

Если у нас база не существует, тогда утилита вернет в среду исполнения код завершения 1 и логика перейдет к исполнению физического создания базы. Для этого напишем скрипт sql следующего содержания:

use master;
go
CREATE DATABASE $(workdbname) ON PRIMARY
(Name = N'$(workdbname)', FILENAME = N'$(databasepath)');
go
use $(workdbname);
go

CREATE TABLE Settings
(
  DbVersion int
)
go
INSERT INTO Settings(DbVersion) VALUES(1)
Go

* This source code was highlighted with Source Code Highlighter.
Как видно, в сценарии используются две переменные, которые нужно будет передать при вызове sqlcmd:

"sqlcmd.exe" -S %2 -v workdbname=%1 -v databasepath=%3 -i InitDatabase.sql

В шаге проверки существования базы данных могло случиться так, что база уже существует, поэтому необходимо подготовить ее к обновлению, а именно – принудительно создать бекап данных, сделать это можно вызвав следующий скрипт:

BACKUP DATABASE $(workdbname) TO DISK = N'$(backuppath)' WITH INIT,
SKIP, NOREWIND, NOUNLOAD

* This source code was highlighted with Source Code Highlighter.

Из создаваемого bat файла:
"sqlcmd.exe" -S %2 -v workdbname=%1 -v backuppath=%4 -i BackupDatabase.sql

Теперь подошло время для самого главного в нашей системе – проверки текущей версии, делаться это будет простой проверкой на заданную версию. Так как необходимо как-то оповестить «внешний мир» о номере версии, то здесь опять мы применим ошибку деления на ноль и оформим этот код в файл CheckVersion.sql:

use $(workdbname);
go
SELECT [DbVersion]/($(checkedVersion) - [DbVersion]) FROM Settings
Go

* This source code was highlighted with Source Code Highlighter.

И напишем вызов с проверкой первой версии:
"sqlcmd.exe" -S %2 -V 1 -v workdbname=%1 -v checkedVersion=1 -i CheckVersion.sql

При первом вызове, как и ожидалось, произойдет ошибка деления на ноль, и в среду исполнения вернется код 1. Тем самым мы можем вызвать скрипт, который должен внести все необходимые изменения, чтобы инициализировать структуру для первой версии базы:
IF ERRORLEVEL 1 "sqlcmd.exe" -S %2 -v workdbname=%1 -i Version\1\alter.sql
Вызываемый файл alter.sql для каждой версии должен работать по следующему принципу:

1. Должен устанавливать контекст той базы данных, название которой ему передали в переменной workdbname.
2. Вызывать с помощью директивы :r все необходимые файлы содержащие sql DML код.

Для первой версии нашего примера сценарий в данном файле состоит из нижеприведенных строк, плюс, в конце, обязательное обновление версии:

use $(workdbname);
go
:r Version\1\script.sql
go
:r Version\1\Person\DeletePerson.sql
go
:r Version\1\Person\InsertPerson.sql
go
:r Version\1\Person\SelectPerson.sql
go
:r Version\1\Person\UpdatePerson.sql
go
UPDATE Settings SET DbVersion = 2
Go

* This source code was highlighted with Source Code Highlighter.
На этом, вся настройка по работе с первой версией завершена, и файловая структура должна принять вид, указанный на изображении:

Создание новых версий

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

CREATE TABLE City
(
  CityId int identity NOT NULL,
  Title varchar(255) NOT NULL,
PRIMARY KEY (CityID)
)
go
ALTER TABLE Person ADD CityId int
go
ALTER TABLE Person ADD FOREIGN KEY(CityID) REFERENCES City (CityID)
go

* This source code was highlighted with Source Code Highlighter.

Одновременно необходимо будет создать четыре процедуры для работы с сущностью City и внести изменения в хранимки для сущности Person. После чего написать сценарий по обновлению в файл alter.sql:

use $(workdbname);
go
:r Version\2\script.sql
go
:r Version\2\Person\InsertPerson.sql
go
:r Version\2\Person\SelectPerson.sql
go
:r Version\2\Person\UpdatePerson.sql
go
:r Version\2\City\SelectCity.sql
go
:r Version\2\City\InsertCity.sql
go
:r Version\2\City\UpdateCity.sql
go
:r Version\2\City\DeleteCity.sql
go
UPDATE Settings SET DbVersion = 3
Go

* This source code was highlighted with Source Code Highlighter.

По завершению мы добавим в секцию проверки версий основного deploy.bat такие строки:
"sqlcmd.exe" -S %2 -V 1 -v workdbname=%1 -v checkedVersion=2 -i CheckVersion.sql
IF ERRORLEVEL 1 "sqlcmd.exe" -S %2 -v workdbname=%1 -i Version\2\alter.sql

Для второй версии структура файлов и папок примет вид:

Заключение

Описанный подход применим не только для баз MS SQL, но может быть адаптирован и в другие СУБД. Например, данный метод был разработан в процессе создания системы версионирования схемы данных для проекта, где СУБД был Postgres. Все исходные скрипты доступны по ссылке Тут.