Подходы для версионирования баз данных
- modified:
- reading: 13 minutes
У нас достаточно молодая сейчас команда, времени особо нет на эксперименты, развитие проекта идет семимильными шагами. Попробовали несколько подходов для хранения/версионирования баз данных. Хотел бы с вами поделиться проблемами, которые мы встречали, а так же нашими решениями. Ну и с удовольствием выслушаю ваши комментарии, предложения, рекомендации.
Подход #0 неоправданный
Самый первый подход избранный нами – это совсем не хранить версии. То есть хранить в репозитории только скрипты создания базы данных с нуля. Один из вариантов – это использование Database Project, который существует в Visual Studio, еще можно использоватьSolution, который есть в Management Studio, благо у Management Studio также есть интеграция с Team Foundation Server 2010 (для этого нужно установить Team Foundation Server MSSCCI Provider 2010). Если не использовать эти проекты, есть еще и самый простой вариант, с которого мы начинали – это просто хранить самописные скрипты создания сущностей баз данных. Минусы самописных скриптов очевидны, сложно проверить работоспособность скриптов (хотя бы опечатки в названии таблиц) без выполнения скриптов, возможны случаи забывания “положить” последние изменения с базами данных в скрипты. Ведь все равно работа основная идет с Management Studio, по крайней мере, так делаем мы: изменяем все в Management Studio, потому что нам так проще, а потом уже обновляем скрипты.
Database project, который идет в составе Visual Studio 2010, немного облегчил нам жизнь. Легко, при помощи Schema Comparison можно обновить файлы Database Project сравнив ее с живой базой данных. Schema Comparison в Visual Studio позволяет сравнивать как две базы данных, так и базу данных с Database Project. В целом очень удобно. Вносишь изменения в базу данных, делаешь Comparison, потом чекинишь изменения в TFS.
Так же мы добавили в nant скрипт автоматический деплой последней версии базы данных на тестовую базу данных (она постоянно пересоздавалась с нуля), где у нас потом выполняются тесты. То есть проект так же постоянно билдится и тестируется. Вот как в nant выглядит этот target:
<target name="deploy-database">
<exec program="C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\msbuild.exe">
<arg>
<path>
<pathelement file="Database\Database.dbproj"/>
</path>
</arg>
<arg value="/t:Deploy" />
<arg value="/p:UserSandboxSettings=false" />
<arg value="/p:TargetDatabase=${database}" />
<arg value="/p:TargetConnectionString="Data Source=dbserver;User ID=dbuser;Password=dbuserpassword"" />
<arg value="/p:DeployToDatabase=true" />
<arg value="/logger:"C:\Program Files (x86)\CruiseControl.NET\server\ThoughtWorks.CruiseControl.MsBuild.dll"" />
</exec>
</target>
Кстати, кто не пользуется nant’ом – рекомендую, достаточно удобно в нем даже локально прописать какие-то действия, вроде запуск всех тестов или сборки проекта, чтобы быстренько запускать их из командной строки, даже не запуская студию.
Итак, жили мы с этим подходом очень долго. Пока темпы развития проекта не ускорились, и не прибавилось заказчиков (а следовательно и независимых инсталяций). У этого подхода есть значительный минус, во время обновления версии нужно делать постоянно одну и туже процедуру, нужно сохранить рабочую версию базы данных (сделать бекап), воссоздать из скрипта новую, потом сравнить версию старой и новой базы данных, например, при помощи RedGate SQL Compare и SQL Data Compare инструментов (они на порядок будут лучше дефолтных Schema Compare и Data Compare, которые идут с Visual Studio, но у них есть другой минус – они платные, причем стоят не дешево), ну и накатить скрипт, который приведет нашу базу данных из старой схемы в новую. А бывают и проблемы, причем очень часто, когда и эти чудо тулзы не справляются. Ну, например, разбили вы одну таблицу на две (произвели нормализацию), с данными-то нужно что-то делать, нужно писать скрипт, который заполнит новые таблицы по старой, а так же восстановит все связи с другими внешними таблицами. Вот тут-то и начинаются проблемы. Кажется, а в чем проблема – выполнил один раз и дело в шляпе. Даже можно сделать так – подготовил один раз скрипт преобразования версии 4 в 5 и сохранил где-нибудь. Но проблема чаще всего обширнее. На одной инсталяции у нас версия 4, и ее нужно обновить до 5, а на второй 4.5, а ее мы уже обновляем до 5.1. В общем-то, сложно дать гарантии, что сохраненный скрипт именно то, что для нас нужно в этот момент. Выполнять же процедуру сравнения схем баз данных каждый раз, во-первых накладно по времени, а во-вторых это может быть причиной возникновения ошибок (второй раз обычно программист будет делать все с большей уверенностью, а значит и с меньшей трепетностью).
Было еще время, когда мы просто сносили базу данных и создавали ее с нуля, не переносят никаких данных. Их было просто очень мало, пользователей мы создавали сами, в каком-то смысле – это был просто прототип. В общем, эх… были времена.
Подход #0.1 проплаченный
Здесь бы мне могла заплатить компания RedGate. В общем-то, пару слов о них. У них есть огромное количество тулзов для упрощенной работы с базами данных (MS SQL), из мои любимых – это SQL Promt (IntelliSense для SQL кода), SQL Comparer (сравнение схем баз данных), SQL Data Comparer (сравнение данных), SQL Data Generator (генерирование тестовых данных). С того момента, как я начал пользоваться их продуктами, их SQL Toolbelt вырос с 5 до 15 продуктов. Цена у него тоже не маленькая. Среди не пользованных мною продуктов есть так же SQL Source Control, который, вроде как, облегчает работу с версионированием баз данных. Чтобы пользоваться, нужно, чтобы у всех участников была лицензия и установлен SQL Source Control, в общем, мы просто этот вариант откинули. Интересно, спасибо, но нам не настолько нужно это. Кстати, да, интересно, кто-то пользуется? Как впечатления? Есть ли ветвления?
Подход #1
Этот подход можно назвать в честь моего бывшего коллеги Олега Аксенова. Взял я этот подход у него. Он описал его в свой статье Основы SQL - DDL и рефакторинг БД. Идея подхода в следующем. У нас как таковых нет версий у базы данных. Есть просто набор скриптов, которые можно упорядочить, которые и определяют для нас версионность. То есть, если за сегодня мы положили три скрипта в репозиторий, то они будут выглядеть примерно так:
001_add_table_Users.sql
002_add_column_phone_to_table_User.sql
003_add_sp_FindUser.sql
Дальше, чтобы привести базу данных к версии 3, нужно запустить последовательно три скрипта. Нумеровать скрипты можно и другими способами, например, временем yyyy_MM_dd_HH_mm (мне этот подход больше всего нравится), очень маловероятно, что два разработчикам добавят два скрипта с одним и тем же временем в репозиторий. А если такое случится, ну просто нужно следить за этим самим разработчикам, а можно написать плагин для SVN/TFS, который будет проверять, что нет повторений по версиям (если разработчики сами уследить не могут).
К тому подходу так же рекомендуется делать все скрипты перезапускаемыми (спорный тоже момент, а нужно ли). То есть, если вы добавляете таблицу, то делаете это так
if object_id('Users') is not null
begin
create table Users(
Id int primary key,
Name varchar(255)
)
end
В случае повторного запуска скрипта не будет никаких ошибок, так как таблица уже создана. Этот подход хорош, например, в случае, когда вы при помощи Management Studio делаете изменения, сохраняя их, а также генерируете скрипт, чтобы положить его в репозиторий (все же знают, что можно генерировать скипты изменений таблиц и т.п.?). В общем, вы добавите изменения в Management Studio, добавите разработку в проекте, чтобы это все работало, и потом одни чекином запишите это все в репозиторий. Хороший вариант, если при билде (на continuous integration сервере) эти скрипты накатятся еще раз на базу данных (или на тестовую базу данных), чтобы не забылось, а так же чтобы проверить работоспособность скрипта.
Плохо в этом подходе то, что нужно помнить какая версия БД у нас постоянно находится на сервере (инсталяциях). Можно представить такой вот крайний случай: еженедельные релизы, в один из моментов их сервер падает (железо, сгорает или еще чего), есть бекапы, на той стороне восстанавливают бекап, понятное дело, что нам не говорят, а просто тычут пальцем, говоря, что это не доделано, а это не работает (ну старый бекап какой-нибудь восстановили), как нам узнать версию БД? Только интуитивно. В общем, есть у этого подхода существенный минус, что не пишутся версии.
Конечно же, этот подход можно доработать, обновлять версию в каждом новом скрипте, но тогда нам уже не нужно будет выполнять повторно запускающиеся скрипты, так как будет просто проверка, что версия уже другая и этот скрипт накатывать не нужно. Ну и не писать же руками эти обновлений версий. В общем, это уже другой подход.
Подход #2
Чтобы познать второй подход, рекомендую прочитать статью Automation for the people: Hands-free database migration (у нее есть и переведенный русский вариант Автоматизация для программистов: Автоматизированная миграция баз данных, но он немного хромает). Суть этого подхода в использовании сторонней библиотеки LiquiBase. Написана она на Java, есть порт на .NET (непонятно зачем), но смысла его использовать нет, так как функционал не такой как у старшего собрата (есть смысл использовать .NET версию, только если захочется его дорабатывать). Я пробовал Java версию, без проблем подключал драйвер для MS SQL к LiquiBase, настраивал и все у меня работало. Я вам на пальцах покажу, что это такое, а вот если захотите проникнуться больше, то отправляю вас к приведенной статье, ну а если будут проблемы с настройкой или еще чем-то, то велком в комментарии.
Итак, давайте рассмотрим на примере, что дает нам LiquiBase.
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.7"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.7
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.7.xsd">
<changeSet id="1" author="betsey">
<createTable tableName="distributor">
<column name="id" type="int">
<constraints primaryKey="true" nullable="false"/>
</column>
<column name="name" type="varchar(255)">
<constraints nullable="false"/>
</column>
<column name="address" type="varchar(255)">
<constraints nullable="true"/>
</column>
<column name="active" type="boolean" defaultValue="1"/>
</createTable>
</changeSet>
<changeSet id="2" author="joe">
<addColumn tableName="distributor">
<column name="phonenumber" type="varchar(255)"/>
</addColumn>
</changeSet>
<changeSet id="3" author="betsey">
<code type="section" width="100%">
<insert tableName="distributor">
<column name="id" valueNumeric="3"/>
<column name="name" value="Manassas Beer Company"/>
</insert>
<insert tableName="distributor">
<column name="id" valueNumeric="4"/>
<column name="name" value="Harrisonburg Beer Distributors"/>
</insert>
</changeSet>
<changeSet id="4" author="joe">
<sqlFile path="insert-distributor-data.sql"/>
</changeSet>
</databaseChangeLog>
Идея LiquiBase в том, что существует один xml файл в котором друг за другом прописываются ChangeSet (в нем, конечно же, могут быть include других changeset файлов), у каждого ChangeSet (изменения) есть идентификатор. Для изменения схемы базы данных не нужно писать SQL код, а необходимо использовать XML описание изменения. Так, например, первое изменение у нас - это создание таблицы, второе - это добавление колонки к созданной таблице, третье изменение – это вставка данных, третье – это опять же вставка данных, но только она у нас описана стандартным SQL синтаксисом в файле insert-distributor-data.sql.
Для многих операций поддерживается так же rollback, если хочется откатиться до предыдущей версии. Есть возможность сравнения баз данных, и даже можно сравнивать с Hibernate (не NHibernate) маппингом. И, конечно же, есть возможность сгенерировать changelog по готовой базе данных.
Подход действительно интересный, но есть одно но, это не SQL. Нужно еще разбираться в их XML синтаксисе. На Java мы вряд ли будем ее дорабатывать, а .NET реализацию вряд ли можно назвать готовой к использованию. Конечно, можно совсем забыть про XML и писать все на SQL, обозначая начала и окончания версий при помощи комментариев Formatted SQL Changelogs.
В общем, мы уже даже думали ее использовать, но все-таки после нескольких проб и попыток, мы поняли, что пользы большой от этой утилиты нам не будет. Все равно приятно создавать таблицы в Management Studio, и просто по готовым объектам генерировать скрипты. Так и проще и нагляднее.
В итоге мы задумались о написании собственной утилиты, которая бы просто собирала скрипты из папки, выполняла их последовательно, выставляла версии, ну и следила бы за папкой, за появлением новых скриптов. И тут я решил сделать последний пробег по гуглу и попробовать найти готовую утилиту.
Подход #3
Мой поиск привел меня к очень простой утилите dotnetmigrations. Чтобы познакомиться достаточно близко с этой утилитой рекомендую прочитать документацию по версии 0.5 .NET Migrations Demo (в 2008 году написана). Данная утилита предоставляет 3 команды – generate, migrate и rollback. Первая создает новый скрипт в папке, прописанной в настройках, вторая выполняет обновление базы данных до определенной версии (или последней), последняя выполняет даунгрейт до определенной версии. У каждого созданного утилитой файла есть определенная версия, либо просто целочисленная последовательность от 0, либо выставляется число, которое получается из даты по формату yyyyMMddHHmm.
Понятное дело, что чтобы работал rollback, его нужно прописывать самим, чуда не будет. В каждом файле прописывать нужно и обновление, а так же процедуру возврата к предыдущей версии схемы (разбиваются они при помощи labels).
Утилита написана на C#, поэтому мы без проблем допилили ее, подправили ошибки. Помню, что валилась утилита при запуске из nant, там в коде используются как-то по хитрому свойства консоли, а в случае запуска из nant эти свойства не доступны, вылечил простым выкидыванием кода. Еще нам захотелось доработать утилиту, так как нам хотелось использовать версии, получаемые по времени, но в записи 201103232038 очень не просто распознать, например, день месяца. Хотелось бы визуального разделения составляющих. Код мне пришлось сильно переделать, так как там по умолчанию везде в коде прописаны целочисленные идентификаторы, я же решил сразу переделать их на строковые. В результате у нас теперь версии выглядят так 2011_03_23_20_38, что намного проще читается.
Так же связался с автором, попросил его о новых функциях, он был безумно рад, что кто-то еще собирается пользоваться его утилитой, и если посмотреть по последнему коду он сейчас активно разрабатывать и дорабатывает утилиту, за что ему, конечно же, огромное спасибо. Может быть он даже уже все реализовал, что я допиливал сам. Но времени нет проверить, у нас все работает и трогать не хочется :)
Остановились сейчас на этой утилите. Она делает для нас не так много: просто запускает скрипты, проверяет версию базы данных, и выставляет новую, но как оказалось больше и не нужно. Осталось только грамотно настроить билд сервер, чтобы он обновлял все базы: для разработчиков, тестировщиков, для автоматических тестов и т.п.
Другие подходы
Думаю, что еще существует огромное количество других подходов версионирования баз данных. Они зависят от команды, от инструментов, от среды, где вы разрабатываете. Хочу вспомнить еще один подход. Когда я работал в первой в своей девелоперской практике конторе IT-Co, мы писали полноценное клиентское приложение UTM с локальной базой данных на Access (многие удивленно или с сарказмом скажут, а это база данных? :)), в которой мы хранили нужную нам для приложения информацию (справочную), а так же сохраненные проекты пользователей. То приложение было написано на C++ (оно и сейчас активно разрабатывается). У приложения есть свой инсталяционный пакет, и мы понятно дело поддерживали возможность обновления приложения со старой версии до более новой. В коде самой программы были зашиты эти обновления, при запуске программы узнавалась текущая версия базы данных, затем, если необходимо, поочередно накатывались обновления для того, чтобы привести ее к необходимой версии. Соответственно мы без проблем могли обновить приложение с версии 1.0 до 1.3. У нас были свои бета тестеры, потому релизы у нас были, конечно, намного чаще для них. Поэтому хоть версия приложения и имеет статус 1.3, у базы данных версия может быть уже 40-я. Мы хранили практически все версии баз данных, начиная с 20 что ли. И перед отправкой бета тестерам всегда проверяли, что приложение без проблем может обновить любую версию базы данных. Но один раз произошло чудо (по моей вине), обновление удаляло все проекты (хорошо, что эту версию отдавали только бета тестерам, они то сами напросились), либо забыли проверить сами, либо случай уникальный, что удалял специальные проекты (не помню уже). В общем, я тут об одном хочу сказать, в любом случае делайте бекапы при обновлении. Очевидно, что нам тоже нужно было бы все равно делать бекап старой версии базы данных (это же всего access файл), класть его где-нибудь рядом на всякий случай, и удалять только тогда, когда пользователь нам об этом скажет.
И, на последок, расскажу еще одну историю. Работали как-то два Боба в компании, которая разрабатывала систему, вроде ebay для проведения аукциона. Только разрабатывала она эту систему для какой-то сторонней фирмы. И вот звонит Майкл из этой фирмы и просит мол, я вот зарегистрировал аукцион, а участники проспали торги, и они закончились ни с чем, давайте в БД поменяем просто статус у аукциона на рабочий и сместим дату начала аукциона, чтобы они могли нормально поторговаться, а то заполнять все эти поля заново очень не охота, да и платить за новый аукцион нужно. Бобы то конечно сначала отнекивались и говорили, что по живой системе такое делать нельзя, а Майкл предложил им хорошую компенсацию, да такую, что на все Бобовские доводы сразу же нашлись антидоводы самими Бобами. Сидит один Боб за монитором и пишет SQL:
update Auction set Status = 'Active' and StartDate = getdate()
Спрашивает у второго “ну как, все нормально, запускаем?”, второй Боб глянул пристально и говорит “вроде так, запускай.” После запуска сразу же в один голос слышится “Shiiit!!!”, как только увидели
(1678 row(s) affected)
В общем, хорошо что не было в тот момент других аукционов, а Бобы в этот раз сделали бекап. Но мысль этой истории в трех вещах: не правьте ничего в живой системе, делайте всегда бекапы, и даже code review может пропустить баг.