Версионирование базы данных на лету

[@JuraZ]

Для мелкомягких, в SQL Server есть DDL триггеры:

CREATE TRIGGER DB_SCHEMA_AUDIT 
ON  database 
FOR     CREATE_PROCEDURE, DROP_PROCEDURE, ALTER_PROCEDURE,
        CREATE_TABLE, DROP_TABLE, ALTER_TABLE,
        CREATE_VIEW, DROP_VIEW, ALTER_VIEW,
        CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION
        CREATE_INDEX, ALTER_INDEX, DROP_INDEX,
        CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER
AS
....

А для контроля версий можно использовать "Redgate SQL Compare". Софтик умеет сравнивать базу данных с например TFS.

[@AlanDenton]

Не только триггерами этом можно делать. Для SQL Server 2008+ использование XEvent более удачным решением было бы. А по поводу контроля версия постоянно использовать компаратор не комильфо. Ужа давно существуют сорс контролы, которые это все делают автоматически.

Для тех кто в поисках, вдруг будут полезны эти плагины для SSMS: dbForge Source Control for SQL Server и SQL Source Control.

[@ANTPro]

А не лучше ли у всех отобрать права на запись к базе у всех кроме сервера CI. И раздать всем доступ к репозиторию с определенной структурой. Сервер CI будет переодически проверять наличие коммитов и применять новые запросы к базе.

[@jehy]

Кажется, именно про это и написано под заголовком "идеальный мир". А в ссылках есть пример такой реализации. Конечно лучше!

[@qwazer]

Решал подобную задачу для Oracle и DB2 LUW
Утилиты для выгрузки DDL:
https://github.com/qwazer/scheme2ddl
https://github.com/qwazer/db2-scheme2ddl

scheme2ddl расползлась по узкому кругу заинтересованных лиц за 3 года, может и вам пригодится.

[@AlexLeonov]

Чтобы только не выдумать, лишь бы не использовать нормальные миграции и не заниматься настройкой workflow в команде...

А вы версионируете свою базу данных?

Разумеется. Любой проект начинается с этого. Более того, флоу построен так, что невозможно начать проект без нулевой миграции.

по факту?

Да упаси господи. То, что вы описали, это не версионирование БД, это карго-культ. Вы делаете вид, что у вас миграции. И ждете, что с неба спустятся белые бородатые люди и принесут вкусную огненную воду.

Может быть, есть реализации под другие СУБД — MySQL, Postgres?

А какая, собственно говоря, разница? Не понимаю.

Или есть какой-то фундаментально другой хороший подход, который я проглядел?

Разумеется есть.

[@jehy]

А хоть слово конкретики?

[@AlexLeonov]

А что конкретно вас интересует?
Слово "миграции" я упомянул два раза. Это паттерн такой, архитектурный.

1. На каждое изменение делаете два файлика. NNN.up.sql и NNN.down.sql, где NNN — последовательный номер миграции. Можете для удобства взять timestamp, например.
2. В файлик *.up.sql записываете DDL, которые приведут к требуемым изменениям в вашей БД
3. В файл же *.down.sql пишете команды, которые изменения откатят (внимание — это не всегда возможно!)
4. Пишете систему, которая:
   
   • умеет определять, какая миграция была применена последней
   • применять все последующие, по одной или до конца
   • откатывать миграции
5. Лучше всего пункт 4. взять готовым. Например на PHP в каждый фреймворк обязательно входит система миграций

А дальше радуетесь жизни
Коммит — пуш — сервер сборки запускает команду migrate up — вы получаете новое состояние БД. Желательно сначала на тестовом стенде. Виза QA — сборка на боевом.

[@ghost404]

+1 автор ищет проблемы на своё мягкое место. За изменение БД должен отвечать программист который делал это изменение и миграции должны хранится вместе с кодом проекта.
Я в PHP использую DoctrineMigrations

[@jehy]

Конкретно меня интересует реализация. Использование архитектурного паттерна — это недалёкий шаг от того, что просто решить, что надо использовать контроль версий на базе.

Использовать свой велосипед не хочется, поскольку задача довольно общая, и явно уже должна была быть решена много раз — да и требований довольно много, поскольку хочется понять правильное использование для большей системы, а не пет проджекта с одним разработчиком и двадцатью табличками.

Из требований навскидку могу выделить следующее:

1. Возможность централизованно мигрировать различные инстансы
2. Уметь определять ошибки миграции и править их в полуавтоматическом режиме
3. Иметь систему прав доступа и аппрува изменений
4. Желательно уметь связывать миграции с версией кода
5. Желательно уметь автоматически генерировать миграции на основании уже внесённых изменений (написать корректный и работающий DDL в блокноте и потом пытаться его накатить это сложная задача с малыми шансами на успех)
6. Комментировать и привязывать к задачам каждую миграцию
7. Сравнивать итоговый DDL произвольных моментов в жизненном цикле. Не в виде огромной пачки изменений, которые скорее всего будут один и тот же объект, а в виде конечной разницы.

Как пример готовых сервисов, которые установил и ими пользуешься — есть таск менеджеры, багтрекеры, есть тот же Gitlab. Просто берёшь и используешь. Так что да, я жду белых бородатых людей, которые хотят мне продать работающее решение.
Разницы в том, какую СУБД использовать, действительно нет — вопрос в наличии готовых реализаций.

К слову о миграциях в PHP фреймворках — видел реализацию в Yii. Для больших проектов это мало подходит, поскольку там обычно разработчики базы данных и самого приложения — разные люди. Заставлять тех, кто хорошо умеет работать с СУБД писать код миграции на PHP — некорректная практика. Получать у этих людей SQL миграции и отдавать разработчикам — тоже. Можно, конечно, написать простенький скрипт, который генерирует класс миграции на основании скормленного ему SQL — но это уже опять создание велосипедов. Да и миграции в Yii не решают большей части описанных выше задач.

[@AlexLeonov]

Конкретно меня интересует реализация.

Реализация описанного мной паттерна требует написания ровно одного небольшого bash-скрипта. Всё.

А всё что вы написали — это от непонимания того, что такое "миграция". Что доказывается, например, следующей фразой:

Заставлять тех, кто хорошо умеет работать с СУБД писать код миграции на PHP

[@jehy]

Вы зачем-то вырвали предложение из контекста, надеюсь что по невнимательности. Оно про реализацию миграции на Yii.
Ну и полностью проигнорировали список требований. Вам может быть достаточно одного bash скрипта. Мне — нет.

[@AlexLeonov]

Ваши требования не нужны. Вы не хотите никак этого понять...

Вот например:

Уметь определять ошибки миграции и править их в полуавтоматическом режиме

Полная ерунда. Что за ошибки? Как их править? Миграция либо накатилась, либо откатилась. Одно из двух. Ошибка? Откат транзакции. Запись о миграции не создается. Сборка целиком откатывается. В логе сборки мы видим подробности.

Иметь систему прав доступа и аппрува изменений

Это вопрос к вам, как к тимлиду и к тому, как вы строите свой wokflow.

Желательно уметь связывать миграции с версией кода

Миграции — это просто файлы. Они лежат в гите. Если вы используете какие-то другие "версии кода" — это вопрос к вам.

Комментировать и привязывать к задачам каждую миграцию

К задачам привязываются коммиты, а не миграции.

Желательно уметь автоматически генерировать миграции на основании уже внесённых изменений

Не может быть никаких изменений без миграций. Поэтому требование абсурдно.

Ну и так далее. Вы очень многое недопонимаете.

[@ghost404]

1. Возможность централизованно мигрировать различные инстансы

миграции применяются при деплое автоматически или вручную. Сначала на тестовом сервере с использованием CI, а потом на боевом.

2. Уметь определять ошибки миграции и править их в полуавтоматическом режиме

запихнул миграцию в транзакцию и если она не выполнилась, транзакцию откатываем. Это делается через систему мигрирования.
Править миграцию в автоматическом режиме невозможно и не нужно.

3. Иметь систему прав доступа и аппрува изменений

Проект имеет доступ к всей базе и менять может всю базу. На уровне миграции разделения прав доступа быть не может. Это задача другого порядка.

4. Желательно уметь связывать миграции с версией кода

миграции нужно хранить в репозитории с кодом

5. Желательно уметь автоматически генерировать миграции на основании уже внесённых изменений

Многие системы миграций умеют генерить код на основе БД и миграции на основе кода. Doctrine Migrations по крайней мере точно умеет

6. Комментировать и привязывать к задачам каждую миграцию

добавляя к коммиту с миграцией номер задачи мы автоматически связываем их. Так работает GitHub и GitLab

7. Сравнивать итоговый DDL произвольных моментов в жизненном цикле.

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

видел реализацию в Yii

Я лично считаю не правильным использовать PHP конструкции для описания миграции. Во всех проектах которые я видел миграции описывались как SQL. Где-то это был *.sql файл, где-то PHP класс в котором описывались изменения как SQL. Для Yii можно вызвать execute(), для Doctrine Migrations addSql()

Получать у этих людей SQL миграции и отдавать разработчикам

Если миграции пишет разработчик БД, а не разработчик приложения, он все равно должен сохранить миграцию в проекте. Для упрощения можно описывать миграции, как говорил AlexLeonov, в отдельный *.sql файлах. Любой нормальный редактор будет поддерживать подсветку SQL синтаксиса в таких файлах.

[@jehy]

Да, я много раз указал, что не считаю способ версионирования по факту корректным, и слёзы стыда наворачиваются на мои глаза каждый раз, когда я думаю об этом. Однако в реальных условиях мне (и многим другим) приходится работать в качестве DBA во внешних компаниях, где я никоим образом не могу повлиять на workflow. И проблемы, которые теоретически должны быть на совести разработчиков, как тут уже указали, в одночасье становятся моими при возникновении каких-либо ошибок. И версионировать по факту — это в данном случае единственный способ иметь возможность что-то исправить.

[@igor_suhorukov]

В случае с ораклом почти каждый проект пилит свои уникальные велосипедные тулы по дампу, дифу, накатке патчей и т.п.

В общем случае, если логики на уровне базы не много, то отлично используют liquibase, dbmaintain

[@jehy]

Про dbmaintain ещё не слышал, спасибо. Только их страничка не обновлялась с 2011 года — оно стабильно и не требует обновления, или тихо умерло?

Вот меня очень сильно смущает писать велосипеды. А впереди ещё большой проект на постгресе...

[@hanovruslan]

doctrine migrations для многих sql — для postgres точно есть. Базовые возможности — миграция схемы данных на основе маппинга и текущей схемы в БД

[@ghost404]

Doctrine поддерживает Oracle и многие другие

[@jehy]

Спасибо! Конечно, не полное решение, но как платформа выглядит очень симпатично. А есть возможность посмотреть итоговый DDL на момент выбранной миграции и смотреть diff между итоговыми же DDL от различных миграций?

[@hanovruslan]

Я полагаю, что под DDL вы подразумеваете схему БД и маппинг внутри приложения (какие поля в коде если отображаются и как на колонки в таблицах БД)

Если так, то этот diff — это две части

1 — файлик с sql патчем
2 — diff самого кода

Как было указано чуть выше, Doctrine migrations — это на PHP. Вполне может быть, что есть реализации на других языках.

[@igor_suhorukov]

dbmaintain похоже что мертв, но видел его в одном проекте года 3 назад. Успешно использовали, но там схема была достаточно простая.
liquibase видел на более сложных проектах, но и он сложнее.

[@eduard93]

СУБД InterSystems Caché. Из коробки есть контроль версий в файлы (а там уже прикручивайте любую систему котроля версий) и открыт код плагинов для удобной работы с Git и SVN. Из CI есть Jenkins и интеграция с GitHub. Удобно.