Версионная миграция структуры базы данных: основные подходы

Убил кучу времени, пока нашёл версионирование БД для Yii. Причём сначала нашёлся экстеншн, который потом стал частью фреймворка :) Короче, вот ссылка на детальное описание: www.yiiframework.com/doc/guide/1.1/en/database.migration

И кстати, это версионирование в Yii появилось совсем недавно, буквально год назад ещё ничего не было :)

Не совсем понятно, можете уточнить? От какого основания отталкиваются ваши инкрементные изменения?

В некотором смысле тот же SQLyog подходит под ваше описание. Он может отображать диаграммы связей, и умеет генерировать diff'ы. Только делает это на реальных копиях БД. И проблема состоит в том, что он не заточен под именно эту функциональность, поэтому будет не слишком удобно.
Я уверен, что есть и другие инструменты со схожим функционалом, но у меня пока не было времени для их обзора.

В Oracle чего только нет. Например, отчасти по данной теме, в версии 11gR2 появилась интересная фича, называемая Edition Based Redefinition.

Она не касается вопроса, как поддерживать версионирование структуры базы, но позволяет без проблем применять миграции на живую базу в режиме реального времени без остановок. Можно также одновременно держать две версии объектов базы данных (например, если один модуль еще не переписан под новую структуру) без их рассогласования.

Спасибо, посмотрю. Там есть что-то по схожей теме?

Имхо — править и выпускать версии

2.3.1

Так же как нужно поступать с исходным кодом в двух разных branch-ах — мержить.

смотрите habrahabr.ru/blogs/sql/121265/#comment_3983506

Да, вы правы, удобство работы с бранчами в системах контроля версий может быть еще одним критерием при выборе подхода. Мне представляется, что лучше всего с этим обстоит у третьего метода (так и не придумал, как его кратко назвать). Чуть хуже — у метода идемпотентных изменений, и совсем нехорошо у инкрементного подхода.

Наверное, позже добавлю это в статью.

Немного дополнил статью своими мыслями по поводу слияния веток.

Можете посмотреть это: www.xt-r.com/2010/11/oracle-svn-git-etc.html

Спасибо за замечание, позже отредактирую статью.

Мне кажется, это зависит от уровня вашего доверия к автоматическим diff-утилитам. И потом, никто не отменяет тестирования сгенерированных скриптов на копии продакшн базы.

Я так понимаю, каждое конкретное изменение у вас не идемпотентно само по себе? Просто ваша хранимая процедура принимает решение о применении каждого изменения, в зависимости от текущей версии БД? А каким образом она определяет, в какой версии было добавлено каждое изменение?

Примерно так работает:

CREATE OR REPLACE FUNCTION update_1_0_2() RETURNS INTEGER AS $PROC$
BEGIN
IF validate_update_script( '1.0.1', '1.0.2' ) = 1 THEN RETURN 1; END IF;

----- Собственно изменения

RETURN 1;
END;
$PROC$ LANGUAGE plpgsql VOLATILE;

SELECT * FROM update_1_0_2();



Завязано на особенности PostgreSQL в работе с хранимыми процедурами (я-бы предпочел без них, но не получается по ряду причин).

А это собственно, проверка версии:

-- service function - no need to copy it to the next update script (it will be saved)
-- this function returns 1 if the updated was already applied, and 0 if the update must be applied
-- an exception is thrown if the update cannot be applied due to wrong database version
CREATE OR REPLACE FUNCTION validate_update_script( old_version TEXT, new_version TEXT ) RETURNS INTEGER AS $PROC$
BEGIN
IF EXISTS( SELECT id FROM version WHERE id = new_version ) THEN
RAISE INFO 'Already upgraded to version %, skipping.', new_version;
RETURN 1; -- means already done - skip the update
END IF;
RAISE INFO 'Upgrading from version % to version %...', old_version, new_version;
IF NOT EXISTS( SELECT id FROM version WHERE id = old_version ) THEN
RAISE EXCEPTION 'Error: version % not found - cannot upgrade', old_version;
END IF;
-- set the version here, assuming that if a transaction fails, it will be rolled back
INSERT INTO version( id, modified, update_number ) --VALUES( new_version, NOW() );
SELECT new_version, NOW(), MAX(update_number) + 1 FROM version;
IF NOT EXISTS( SELECT id FROM version WHERE id = new_version ) THEN
RAISE EXCEPTION 'Error: version % not found - upgrade failed', new_version;
END IF;
RETURN 0; -- means continue with the update
END;
$PROC$ LANGUAGE plpgsql VOLATILE;


Да, в таком случае вам, действительно, может подойти третий способ. Но в потенциале проблемы с данными есть всегда.

С другой стороны, настолько ли часто вам нужно обновляться с очень старой версии до новой? Если же речь об очень старой версии baseline-файла, то проблема решается созданием нового baseline для каждой новой версии, или же подверсии.

т.е. вы предлагаете, для каждой версии писать отдельный (прямой) скрипт для старых версий?
например, у нас имеется 4 версии:
1,2,3,4
При первом подходе, мы пишем инкрементальные скрипты для последовательного обновления:
1->2, 2->3, 3->4
а вы предлагаете поддерживать миграцию типа:
1->2
1->3
1->4
2->3
2->4
3->4
Не слишком ли накладно получается?

:) Рад, что статья полезна.
Интересно будет посмотреть на новую версию вашего мигратора. Сам я им не пользуюсь, но есть знакомые, которые используют и довольны.

Вот это очень интересно! Не хотите написать статью на эту тему?

Судя по наречию SQL и коннекшен стрингам — это T SQL для ms SQL Server. А чем кроме цены не устроили существующие инструменты (кроме цены), такие как SQL Examiner www.sqlaccessories.com/SQL_Examiner/
?

Для Interbase/Firebird есть отличный инструмент Database Comparer. он же включен в IDE IBExpert. пользуемся-ненарадуемся.

Да, удобство отката может быть еще одним критерием при выборе подхода. Спасибо, позже добавлю в статью.

Это был простой пример для того, чтобы наглядно показать, зачем держать версии БД и приложения синхронизированными. Вам можно было пропустить этот раздел :)

Это интересный подход. Только, по-моему, он ближе всего как раз к третьему методу, потому что, как я понял, вы используете декларативное описание всей структуры в виде XML. Возникли вот такие вопросы:
1. Скажите, вы храните XML-описание в системе контроля версий?
2. Каким образом вы получаете патч в виде идемпотентных изменений при помощи XSLT? Ведь у вас есть два XML-описания разных версий, и нужно сгенерировать патч как разницу между ними Не совсем представляю, как это сделать при помощи XSLT.
3. Еще интереснее было бы узнать про описание этим файлом мета-модели.

И вообще, на словах ваше решение довольно-таки красиво звучит :) Не расскажете ли поподробнее? Мне кажется, и статья на эту тему была бы хорошо воспринята.

> Должна быть общая для всех дев-база, на нее после каждого удачного релиза целиком копируется БД с продакшена вместе со всеми данными
Хе-хе, со всеми данными. К счастью, в большинстве случаев это невыполнимо. Хотя такая жесткая синхронизация всех баз сразу решает много проблем.

Спасибо за развернутое описание вашего процесса, и отдельное спасибо за diff-утилиту для PostgreSQL, добавил ссылку в статью.

По поводу единой центральной БД для разработки — здесь, думаю, все зависит от проекта и команды. К примеру, когда мы использовали центральную dev-БД, время от времени это приводило к ошибкам, т.к. в процессе разработки какой-либо фичи не всегда сразу понятно, какими именно должны быть изменения в БД. Иногда, после нескольких экспериментов с разной структурой таблиц, человек забывал откатить все ненужные изменения, и в итоге оставлял как те, что нужны для работы фичи, так и те, что остались от экспериментов. Конечно же, при использовании diff-подхода, все эти изменения в итоге попадут на продакшн.
Второй негативный момент центральной dev DB — временное несоответствие структуры БД версии приложения. Ведь когда один разработчик создает фичу и при этом меняет структуру общей БД, у других разработчиков что-то может сломаться в приложении, т.к. код, который работает с измененной структурой, еще не выложен в общий репозиторий кода.

Поэтому мы пришли к следующей схеме:
1. Центральной базы данных нет. Каждый разработчик работает с локальной базой данных.
2. Скрипты для создания текущей версии БД с нуля хранятся в репозитории.
3. Эти же скрипты умеют заливать во вновь созданную БД и один из наборов тестовых данных: легкий набор с минимально необходимыми для тестирования данными, и большой — для тестирования тяжелых запросов на больших объемах данных.
4. При обновлении кода из репозитория, разработчик запускает скрипт, который обновляет и базу данных до последней версии. При этом с нуля она не создается, применяются только изменения по сравнению с прошлым обновлением. Так что много времени это не занимает.
5. Когда человек заканчивает разработку фичи или баг-фикса, он отправляет в общий репозиторий кода как изменения в, собственно, коде, так и изменения в БД. Причем на этом этапе его изменения проходят через общую процедуру code review.
6. Как и вы, мы практикуем «репетицию релиза» на копии продакшн базы данных.

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