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

Довольно хорошо известно, что в общем случае, имея 2 версии структуры одной и той же БД, невозможно автоматически их синхронизировать между собой. Например, мы на тестовой базе переименовали некоторое поле. Как синхронизатор поймет, что произошло: переименование, лил удаление поля + создание нового с другим именем? Ситуация еще более усложняется при наличии связей с внешними ключами.

Для того чтобы "догнать" версию базы на реальном сервере до версии базы на тестовом, необходимо выполнить на первой те же самые ALTER-команды, что и на второй. Т.е. нужно тщательно хранить все ALTER-команды, выполняющиеся над базой разработчиками, и потом разом "накатывать" их на другие базы.

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

Очень часто приходится видеть такую схему: ALTER-команды каждый разработчик логирует самостоятельно, а затем складывает их в CVS и обеспечивает версионность. Но такая схема очень сильно подвержена различного рода ошибкам, она неустойчива: достаточно 1 раз ошибиться, чтобы структуры баз начали отличаться, и отличие это мы уже найти не сможем, если база большая (или сможем, но с трудом).

Я думаю, это происходит потому, что задача решается не на том слое абстракции: логирование и версионность должен обеспечивать тот слой, который имеет наибольшую информацию о производимых изменениях. Этот слой - вовсе даже не разработчик и CVS, а сама СУБД. Она ведь прекрасно знает, какие операции над ней выполняются, и может их складировать, параллельно отслеживая версии.

Собственно, от самой СУБД хотелось бы иметь 2 команды в API:

1. get_current_version() - возвращает текущую версию структуры БД. С каждым новом ALTER-ом текущая версия увеличивается на 1.

2. get_diff_alters(from_version) - возвращает список ALTER-команд, выполненных над базой от версии from_version до текущей

Причем эта штука должна работать не на уровне лог-файлов запросов, а на уровне самой БД (потому что логи, во-первых, сваливают в кучу запросы для различных БД в пределах СУБД, а во-вторых, не парсятся легко).

Спрашивается, новы ли эти мысли, и реализовано ли что-то подобное хотя бы для одной из популярных СУБД? Конечно, прежде всего интересуют PostgreSQL и MySQL.

-me-только DDL триггеры не очень помогут, например, в ситуации, когда создаем новый справочник, и меняем/добавляем поля в других таблицах - внешние ключи на данный справочник.
Т.е. кроме DDL, может возникнуть необходимость хранения и части DML, если изменения накатываются на рабочую базу, и в случае отката DROP COLUMN/TABLE.

Не проще ли записывать (разработчикам) все изменения схемы как последовательность SQL патчей , к примеру, можно использовать след. структуру каталогов/файлов:
т.е. каждое новое изменение сохраняется в новом каталоге с инкрементом названия(номера версии), старые скрипты остаются read-only.
??
Именно так и делается, однако кто должен генерировать up.sql и down.sql, если структуру БД меняютс сразу несколько разработчиков? В этом и заключается тема данного топика. Традиционно их пишет сам разработчик, либо вручную, либо же настроив свою IDE работы с БД на автоматическое логирование запросов. Однако данный метод, мне кажется, плох тем, что разработчиков и IDE много, а sql-скрипт - один. И гарантии, что каждое действие будет корректно залогировано, нет.

Вот вариант с "прокси запросов" для базы, который принимает запрос на изменение от разработчика, накатывает его на базу и тут же гарантировано логирует, уже гораздо лучше. Но - это, по сути, то же самое, что триггер на DDL.

С внешними ключами и уникальностью, действительно, проблемы, но - их можно решить, разрешив или даже заставив разработчикам вставлять команды на корректировку внешних ключей/уникальных полей при выполнении соответствующих alter-ов. Т.е. если система видит, что alter сужает домен, она заставляет выполнить update/delete перед этим.

На вопрос, почему проблема с версионностью базы, ответ простой: недостаточно просто пересоздать БД с нуля, нужно еще максимально сохранить данные, которые в ней имеются. Т.е. даже если мы в каждый момент времени имеем дампы структуры базы для любой версии, это нам ничего не дает - мы не можем автоматически перейти от одной версии к другой, сохранив данные.