Контроль версий структуры базы данных

Автор оригинала: Dave Marshall
  • Перевод
imageЭта статья — How To, которое поможет вам легко обеспечить миграцию между версиями БД ваших PHP приложений с помощью Phing и dbdeploy.

Установка Phing


Автор статьи признается в том что всегда пользуется бета и RC релизами Phing и если вы будете использовать материал статьи для совместимости поступайте так же. Самый простой способ установки phing — использование PEAR. Вы сможете сделать это на любой системе тремя командами:

 
> pear channel-discover pear.phing.info
> pear config-set preferred_state beta
> pear install phing/phing

Пример структуры приложения


В примере будет рассмотрено простое приложение со следующей структурой:

example/
 |-- db/ ← здесь хранятся sql файлы для управления БД
 |   `-- deltas/
 |-- deploy/ ← здесь хранятся скрипты обеспечивающие миграцию
 |   `-- scripts/
 |-- library/ ← здесь находится разрабатываемое приложение
 `-- public/ ← сюда указывает директива DOCUMENT ROOT 


Скрипты сборщика


В этом разделе мы рассмотрим как разрабатывать скрипты сборщика которые будут инициализировать миграцию базы данных. Для начала нам потребуется создатьпростой конфигурационный (ini) файл, комментарии к которому будут излишне. Разместим его здесь: deploy/build.properties.

# Property files contain key/value pairs
#key=value
 
# This dir must contain the local application
build.dir=../
 
# Credentials for the database migrations
db.host=localhost
db.user=user
db.pass=password
db.name=example
 
# paths to programs
progs.mysql=/usr/bin/mysql

Второй файл который нам необходимо создать это deploy/build.xml. Из него Phing узнает что мы от него хотим. Автор снабдил пример некоторыми комментариями, но если у вас возникнут более детальные вопросы — обратитесь к документации Phing.

<?xml version="1.0" ?> 
<project name="PurpleMonkey" basedir="." default="build"> 
 
    <!-- Sets the DSTAMP, TSTAMP and TODAY properties --> 
    <tstamp/> 
 
    <!-- Load our configuration --> 
    <property file="./build.properties" /> 
 
    <!-- create our migration task --> 
    <target name="migrate" description="Database Migrations">    
 
        <!-- load the dbdeploy task --> 
        <taskdef
            name="dbdeploy"
            classname="phing.tasks.ext.dbdeploy.DbDeployTask"/> 
 
        <!--
        these two filenames will contain the generated SQL
        to do the deploy and roll it back
        --> 
        <property
            name="build.dbdeploy.deployfile"
            value="deploy/scripts/deploy-${DSTAMP}${TSTAMP}.sql" /> 
        <property
            name="build.dbdeploy.undofile"
            value="deploy/scripts/undo-${DSTAMP}${TSTAMP}.sql" /> 
 
        <!-- generate the deployment scripts --> 
        <dbdeploy 
            url="mysql:host=${db.host};dbname=${db.name}" 
            userid="${db.user}" 
            password="${db.pass}" 
            dir="${build.dir}/db/deltas" 
            outputfile="${build.dir}/${build.dbdeploy.deployfile}" 
            undooutputfile="${build.dir}/${build.dbdeploy.undofile}" /> 
 
        <!--
        Execute the SQL
        Use mysql command line to avoid trouble with large files
        or many statements and PDO
        --> 
        <exec 
            command="${progs.mysql} -h${db.host} -u${db.user} -p${db.pass} ${db.name} < ${build.dbdeploy.deployfile}" 
            dir="${build.dir}" 
            checkreturn="true" /> 
    </target> 
</project> 


* This source code was highlighted with Source Code Highlighter.

В принципе это все что требуется сделать, осталось создать саму базу данных.

Работа с dbdeploy


Так как мы, в принципе, еще не создали нашу базу, так что вместо того чтобы сделать этого традиционным путем будем использовать миграции чтобы создать начальную структуру. Мы еще без понятия что будет делать наше приложение, но так как во многих примерах используют концепт блогов, то почему бы и нам не начать с этого же… начнем с одной таблицы «post», содержащей 3 поля:
Field Type Comment
title VARCHAR(255) The title of our post
time_created DATETIME The time we created our post
content MEDIUMTEXT The content of our post

Работа Dbdeploy основана на создании нумерованных файлов отличий, каждый файл содержит SQL чтобы применить изменения и откатить их, базовый файл имеет следующий вид:

--//
-- Run SQL to do the changes
--//@UNDO
-- RUN SQL to undo the changes
--//


* This source code was highlighted with Source Code Highlighter.

Мы создаем нашу первоначальную структуру, поэтому положим дамп в db/deltas/1-create_initial_schema.sql

--//

CREATE TABLE `post` (
    `title` VARCHAR(255),
    `time_created` DATETIME,
    `content` MEDIUMTEXT
);

--//@UNDO

DROP TABLE `post`;

--//


* This source code was highlighted with Source Code Highlighter.

Миграции


Мы в одном шаге от нашей первой миграции. Чтобы отслеживать текущую версию базы данных dbdeploy требует таблицу в БД для хранения служебной информации. Это единственный раз когда нам потребуется напрямую взаимодействовать с клиентом mysql напрямую.

 
> mysql -hlocalhost -uroot -ppassword example
> CREATE TABLE changelog (
  change_number BIGINT NOT NULL,
  delta_set VARCHAR(10) NOT NULL,
  start_dt TIMESTAMP NOT NULL,
  complete_dt TIMESTAMP NULL,
  applied_by VARCHAR(100) NOT NULL,
  description VARCHAR(500) NOT NULL
);
> ALTER TABLE changelog ADD CONSTRAINT Pkchangelog PRIMARY KEY (change_number, delta_set);

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

 
>cd deploy
>phing migrate

Теперь в нашей базе данных есть таблица с постами, но что насчет того чтобы добавить информацию об авторе? Нам потребуется создать еще одну таблицу и внешний ключ, чтобы это сделать создается еще один файл для dbdeploy и назовем его db/deltas/2-create_author_and_link_to_post.sql

--//

CREATE TABLE `author` (
    `author_id` INT(10) unsigned auto_increment,
    `name` VARCHAR(255),
    PRIMARY KEY (`author_id`)
);

ALTER TABLE `post` ADD `author_id` INT(10) unsigned NULL;

--//@UNDO

ALTER TABLE `post` DROP `author_id`;

DROP TABLE `author`;

--//


* This source code was highlighted with Source Code Highlighter.

Запустим миграцию повторно.

 
shell> cd deploy
shell> phing migrate

Заключение


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

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

Это руководство неполное и если вам кажется, есть что добавить, пожалуйста, оставьте свой комментарий ниже.

Progg it
P. S.
Ознакомительная статья Phing Is Not GNU для получения общего представления о Phing.
Поделиться публикацией

Похожие публикации

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

    0
    Очень интересно, спасибо
      0
      Это пока единственный автоматизированный вариант решения проблемы который я нашел, до этого приходилось все делать вручную, что было достаточно накладно.
        0
        Надо попробовать поюзать, чтобы оценить все преимущества. Но то, что вручную делать лень — это, конечно, правда.
          0
          на хабре ведь уже обсуждали LiquiBase
            0
            Поиск находит только 1 топик с поверхностным упоминанием.
              +1
              мир ведь не сошелся на LiquiBase, больше выбор — больше возможностей. Спасибо что упомянули LiquiBase, теперь можно почитать и про него и выбрать что лучше(раньше не слышал о LiquiBase).
                0
                Я как раз только посмотрел пару видео: в общем тоже самое только в профиль, пока из плюсов — автоматические генереривание дифов через плагин к Eclipse.
            +1
            Простите если ошибусь о сути поста, но это как было сказано ниже описание «Контроль версий для структуры баз данных». В нашей фирме мы пробовали Phing как средство автоматизации сборок билдов и проверки качества кода. Но на тот момент он был довольно сырой и поддерживал довольно мало плагинов.
            Остановились на CruiseControl cruisecontrol.sourceforge.net/ и на него сверху интерфейс
            phpundercontrol phpundercontrol.org/about.html. Преимущества в том что проект развивается довольно давно, имеет оч. много обзоров по настройке cruise (будет не лишне упаминуть что с непривычки много времени ушло на первоначальную настройку билдов). В том числе есть плагин для работы с базой. А именно после любого изменения в базе, девелопер который его сделал, создает дамп базы и кладет этот файл в свн. Круиз забирает все из СВН разворачивает каждый раз по новой базу и потом на этой базе выполняет все тесты. Таким образом мы каждый день имеем отчет все ли изменения были сделаны корректны и не внесли ли они проблемы в работы других узлов приложения.
            P.S. Если Вы имели ввиду не такого рода свойства Phing, звеняйте ):
              0
              Забыл дописать, этого решения нам достаточно чтоб не делать уйму файлов апдейтов базы. Так как каждый разработчик утром апдейтит СВН и разворачивает у себя базу, которая у нас готова к работе с необходимым набором данных.
              А если вам необходимо вычленить необходимые апдэйты для патча на рабочую базу, то для этих целей опять же можно написать скрипт-плагин(на том же PHP) который будет сверять рабочую базу и базу из SVN, генерируя код патча автоматом.
                0
                Вы таким образом и поступаете? Я про «скрипт плагин».
                0
                У СС есть несколько типов плагинов: паблишеры, билдеры и т. п. phing — один из возможных билдеров. Насчет сырости — слышал тожесамое про phpundercontrol, к тому же он отностительно староват: «Revision 94 by mapi at Sun, 21 Sep 2008 00:16:19 +0200»
                  0
                  phpundercontrol действительно давно не обновлялся, но он в данной связке выступает лишь как интерфейс к CC (на любителя).
                  Просто он больше заточен под проекты на PHP с выносом в отдельные вкладки результатов выполнения unittests, codesnifer, codecoverage, etc. Впринципе ничего не мешает самому написать интерфейс, для нас это было излишне.
                  по поводу билдеров, где-то видел в английском сегменте, что народ подключал phing к CC, но меня не хватило разобраться в теме.
                  Преимущество phing (на мой взгляд) значительно более простая установка и настройка конфигов. Плюс большой плюс для написания своих дополнений пхп-программистам.
                  CruiseControl муторен в первоначальном развертывании, особенно новичку, но зато большое сообщество и уже довольно много всяких плагинов на все случаи жизни.
            +1
            Я бы предложил переименовать статью в «Контроль версий для структуры баз данных», будет сразу понятно, что речь идет не о бэкапе данных или чем-то подобном.

            А за статью спасибо, весьма пригодится — а то тяжко постоянно вручную патчи c SQL делать :(
              +2
              озвученная статья не решает проблемы ручного создания патчей. статья — об автоматическом накатывании созданных вручную патчей на базу.

              ps: для автоматической гененрации диффов пользуемся EMS DB Comparer и EMS Data Comparer
                0
                Для pg из бесплатного есть apgdiff.
                  0
                  Альтернатив EMS не встречали еще?
                    +1
                    Пользуюсь уже больше года теперь DB Forge — проблему разниц схем решает, потому как при модификации отдаёт сразу разницу. Потом сохраняем в файл и накатываем куда нужно.
                      0
                      Спасибо, посмотрю ее.
                –2
                у нас в JAVA проекте senior написала что то похожее сама — используем. В личных нуждах надо попробовать ваш пример.
                  +1
                  уж в java-то это давно есть написанное
                  0
                  В свое время искали как такое можно сделать отдельными утилитами, нашли EMS Database Compare.
                  Позволяет по двум базам построить файл-мердж.
                  Конечно не то, что описано в статье, но лично нам этой утилиты хватило более чем.
                  0
                  Имхо некрасивый синтаксис в build.xml, и неудобно то что само приложение надо поместить в папку library и public, что ставит под вопрос использование миграции в фреймворке. А так немного напоминает миграцию рельс кроме генерации через консоль и своего синтаксиса определения стуктуры бд. И всё же наличие миграций лучше чем её отсутствие :)
                    +1
                    Library и public приведены для примера, раскидать вы можете как хотите, только укажите в xml соответствующие пути.
                    +2
                    Мы уже давно пришли к следующей структуре:
                    Есть файл database.sql, который создаёт базу с нуля — там всегда самая свежая структура.
                    Есть каталог alters, в котором лежат файлы поименованные как:
                    001_2008_05_15.sql
                    002_2008_06_18.sql
                    003_2008_06_19.sql

                    Преимущество такого подхода — три позиции под номер дают возможность всегда выстраивать альтреры в хронологическом порядке. Дата позволяет примерно увидеть, насколько обновлялась база. Да и вообще не люблю давать осмысленные названия подобным файлам.
                    В конце каждого альтера стоит
                    update zp_maintenance set file='121_2009-04-13.sql', modiftime=now();
                    Благодаря этому мы всегда знаем какой альтер был применён последним в текущей копии базы данных и можем дотащить новые альтеры, если они появятся.
                    Ещё один немаловажный аспект — дисциплина разработчиков. При изменении структуры базы данных разработчики должны _сначала_ писать альтер, а потом применять его на свою копию. Никаких добавлений колонок в phpmyadmin и прочих.
                      0
                      Способ описанный в статье по факту от вашего не отличается, единственное — автоматизировано накатывание обновлений и их откат.
                        +3
                        следует заметить, что «откат» на самом деле здесь полурешение.
                        попробуйте откатить удаление таблицы или столбца.
                          +3
                          Ну в принципе автонакатывание альтеров и есть основная фишка, которая сильно раздражает при ежедневной работе. Плюс после автонаката можно делать деплой дев-версии приложения по крону хоть каждый час.
                          откатить изменение на самом деле не так просто — для этого придётся для каждого альтера писать антиальтер.
                          Но в принципе — за все годы не возникало такой необходимости ни разу.
                          Ещё одно важдное правило — альтер должен быть целиком заключён в транзакцию — чтоб при провале одной строки весь альтер не применялся и деплой скрипт должен это учитывать и не применять остальные альтеры при провале одного из них.
                        0
                        может кому-то пригодится.
                        это я делал когда-то для синхронизации структуры бд.
                        без контроля версий, конечно, но умеет добавлять/изменять таблицы и поля.
                        в принципе, никто не мешает прикрутить туда логи, бекапы, создание альтеров/диффов в виде отдельных файлов и прочие вкусности, но на момент написания это было не нужно.
                        удаление таблиц и полей отсутствует намеренно, а не от лени, написано левой задней ногой в лучших традициях пхп-лапши :).

                        да, я на скорую руку убрал оттуда зависимости, а протестировать сейчас негде, потому мог слегка поломать.
                          0
                          Вопрос автору, Вы пытались провести сравнительный анализ данных php инструментов с компонентой фреймворка Rails — db:migration? cуществуют ли преимущества на решением в рельсах?
                            0
                            Материал статьи — по сути портирование концепта с rails, от этом есть в оригинале в первых строках, я сначала перевел и это, но потом убрал.
                            Ruby on Rails is a popular web application framework, that provides a method of migrating (upgrading) the applications database programatically, keeping the database schema essentially version controlled. This allows individual developers to update their working databases and the databases on testing, staging or production machines to be updated with new versions of applications. The CakePHP framework has recently developed a migrations library simliar to rails, but this article focuses on using seperate tools to run database migrations, a build tool called Phing, along with a method for creating database migrations, dbdeploy.
                            0
                            Если не хочется писать миграции руками и нужна конретно MySQL, то я делаю вот такой проект: code.google.com/p/mygrate/. Вы вносите изменения в БД через любой инструмент, и потом можете сделать коммит, который автоматически создает миграцию. Миграции можно просматривать («mygrate log»), откатывать («mygrate up», «revert») и т.п. В ситуациях синхронизации локальной дев-БД и продакшн-сервера работает замечательно. Если кого-то заинтересует, напишите, я постараюсь дать более подробный хелп.
                              0
                              Уже несколько часов изучаю тему.
                              Как ваш проект, забросили?

                              Идея очень интересная, как раз то, что надо.
                                0
                                о, вовсе нет, не забросил. просто у меня самого для разработки работает более-менее стабильно, и поэтому сразу меньше мотивации писать документации или какие-то новые фичи делать. поэтому если вы пользуетесь и вам нравится, я буду рад багрепортам (видел, что один уже пришел) или предложениям по тому, чего не хватает.
                              0
                              хм, это конечно вариант, но очень хотелось бы какую то автоматическую связку git — mysql, где в mysql необходим только версионность структуры БД. На самом деле это достаточно элементарно было бы и самому написать, но на все нужно время… а компания время потраченное на такие вещи к сожалению не оплачивает… Может быть кто-то сталкивался с подобными проектами?

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

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