Простой подход к версионированию баз данных 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, код из-за элементарности приводить не будем, а только посмотрим, что должно в итоге получиться:
    image
    Создание системы развертывания базы

    Теперь, когда все файлы и каталоги собраны в определенную структуру, можно приступить к созданию механизма, который будет развертывать нашу базу. Для 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
    .

    На этом, вся настройка по работе с первой версией завершена, и файловая структура должна принять вид, указанный на изображении:
    image

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

    Когда подходит время следующего релиза, все те изменения, которые накопились в процессе работы, должны быть собраны в подпапке основной ветки 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


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

    Заключение

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

    Средняя зарплата в IT

    110 000 ₽/мес.
    Средняя зарплата по всем IT-специализациям на основании 8 355 анкет, за 2-ое пол. 2020 года Узнать свою зарплату
    Реклама
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее

    Комментарии 19

      +7
      Хабракат бы 8)
        –1
        дааа
        вроде и статьи хорошие пишутся, но без хабраката как-то все портится)
          –1
          Как пользоваться?
            –1
            Поставьте <habracut> в месте, где должен быть разрыв. Это и многое другое можно прочесть в разделе помощь. Так-то.
              –1
              а с подстветкой sql синтаксиса статья была бы еще лучше.
                +1
                Согласен. Fixed!
            –1
            Я тут первый раз. Но ставил <habracut/> в начале текста.
              +2
              в самом-самом-самом начале?

              надо:
              ля-ля-ля
              <habracut/>
              ло-ло-ло
                –1
                Спасибо, теперь буду знать!
              –1
              А расскажите, чем ваш способ лучше использования Tarantino или Migrator.NET? А то вы во введении напедалили букв руками семь верст до небес, а сравнить с аналогами и не потрудились даже.
                –1
                Я не стал сравнивать, так как они используют совершенно разный механизм с описываемым. Тут чистый скриптинг без сторонних компонент, от разработчика требуется только дисциплинированность в формировании структуры каталогов и именования файлов. Это больше code convention, при котором еще возможна гибкая процедура миграции.
                  –1
                  В тарантино тоже чисто скриптинг, минимум внешней логики. Я так и понял, что вы не стали сравнивать. Наверное, даже и не заглядывали
                    –1
                    Вы правы, не смотрел, но проект скачан и ожидает рассмотрения:) Но я и не претендую на универсальность. В названии я так и обозначил «Простой подход». Спасибо за указание на Tarantino.
                0
                а проекты баз данных в VS 2008 никак не решают данный вопрос?
                или здесь вся фишка в том, чтобы отдать голый .bat на сторону?
                но тогда можно и правильно оформленный .sql отдать

                а если в проект типа setup свой код закатать, то и с интерактивностью проблем не будет, и с последовательностью установок обновлений
                отдаете админу 5 патчей в виде .exe, и он их накатывает по одному на системе, и еще видит, что было и что стало
                да, там код нужно написать один раз будет, зато потом как под копирку…

                а так, вариантов конечно много можно придумать…
                  –1
                  >>а проекты баз данных в VS 2008 никак не решают данный вопрос?
                  Я писал про «простой» способ. И не все используют VS.

                  >>но тогда можно и правильно оформленный .sql отдать
                  Вот я и написал о «правильной организации sql»

                  >> отдаете админу 5 патчей в виде .exe, и он их накатывает по одному на системе, и еще видит, что было и что стало
                  Это хорошо когда админ один, а если их много, и некоторым понадобились «непредусмотренные» фишки, как они будут править Ваш exe? Как раз админы больше приветствуют легко модифицируемые скрипты.

                  >>а так, вариантов конечно много можно придумать…
                  С нетерпением жду Ваш вариант.
                    –1
                    вы не путайте критику статьи с обсуждением тематических вопросов, которые она порождает
                    к варианту в статье с описанными условиями вопросов нет, общие вопросы есть, они были озвучены
                    может кто чего полезного скажет, потому и задал их

                    с «непредусмотренными» фишками можно бороться, зашивая одинаково полный функционал во все версии
                    потому что поддерживать потом отдельную ветку — это дополнительные затраты
                    разумеется, во всем нужен баланс

                    хорошо, вот вам вариант:
                    можно перевести систему в штатный maintenance, развернуть backup пустой свежей базы, перелить туда данные из предыдущей версии и убить старую базу
                    да, у такого решения есть свои минусы, но есть и свои плюсы:
                    — код для переливки зашивается прямо в процедуру БД
                    — при переливке можно дополнительно проверить целостность данных и внести коррективы
                    — поскольку старая база убивается, весь клиентский код, не попавший под обновление и не знающий про новую базу, автоматически отсеивается, что иногда мега-удобно

                    разумеется, по мелочам такое делать не очень правильно, но вот как service pack — очень хороший вариант
                    наверное, такой вариант можно сравнить с подходом XP, когда сначала пишутся тесты, а потом сам код
                    так что еще неизвестно, к чему мы в итоге придем со временем )))
                    –5
                    посмотрел на Tarantino и Migrator.NET, оба — свободные проекты, висящие на google code
                    вот мне было бы стремно использовать в работе стороннюю поделку, если уже есть какое-то свое работающее решение
                    хотя, если разобраться, то может быть все нормально прошло

                    было бы лучше, если бы был plug-in к VS с аналогичной функциональностью, дающий на выходе скрипт миграции, а еще лучше .exe

                    ну, может сделают со временем…
                      0
                      SELECT 1/0; — это честно говоря жесть какая-то, почему не сделать более интуитивно понятным с raiserror?
                        0
                        Если рассматривать MSSQL Server, то можно. Однако данный метод писался с претензией на универсальность (независимость от конкрентной базы данных и был портирован из Postgres), поэтому я и оставил 1/0. Но соглашусь, raiserror — выглядит более профессионально

                      Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                      Самое читаемое