Database structure version control

This article is How To, which helps you easily provide migration between the database versions of your PHP applications using Phing and dbdeploy .

Phing installation

The author of the article admits that he always uses beta and RC releases of Phing and if you will use the material of the article for compatibility, do the same. The easiest way to install phing is to use PEAR . You can do this on any system with three commands:

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

Application structure example

In the example, a simple application with the following structure will be considered:

example /
 | - db / ← sql files for database management are stored here
 | `- deltas /
 | - deploy / ← scripts that provide migration are stored here
 | `- scripts /
 | - library / ← here is the application under development
 `- public / ← the DOCUMENT ROOT directive indicates here 

Collector scripts

In this section, we will look at how to develop collector scripts that will initialize database migration. First, we need to create a simple configuration (ini) file, comments on which will be unnecessary. Place it here: 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

The second file we need to create is deploy / build.xml. From him, Phing learns what we want from him. The author provided the example with some comments, but if you have more detailed questions, see the Phing documentation .

 
 
 
     
     
 
     
     
 
     
        
 
         
                     name="dbdeploy"
            classname="phing.tasks.ext.dbdeploy.DbDeployTask"/> 
 
                 these two filenames will contain the generated SQL
        to do the deploy and roll it back
        --> 
                     name="build.dbdeploy.deployfile"
            value="deploy/scripts/deploy-${DSTAMP}${TSTAMP}.sql" /> 
                     name="build.dbdeploy.undofile"
            value="deploy/scripts/undo-${DSTAMP}${TSTAMP}.sql" /> 
 
         
                     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
        --> 
                     command="${progs.mysql} -h${db.host} -u${db.user} -p${db.pass} ${db.name} < ${build.dbdeploy.deployfile}" 
            dir="${build.dir}" 
            checkreturn="true" /> 
     
 

* This source code was highlighted with Source Code Highlighter.

In principle, this is all that needs to be done, it remains to create the database itself.

Work with dbdeploy

Since we, in principle, have not yet created our base, so instead of doing it the traditional way, we will use migrations to create the initial structure. We still have no idea what our application will do, but since many examples use the concept of blogs, then why don’t we start from the same ... let's start with one “post” table containing 3 fields:

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

The work of Dbdeploy is based on the creation of numbered differences files, each file contains SQL to apply the changes and roll them back, the base file has the following form:

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

* This source code was highlighted with Source Code Highlighter.

We create our initial structure, so put the dump in 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.

Migrations

We are one step away from our first migration. To track the current version of the database, dbdeploy requires a table in the database to store service information. This is the only time we need to interact directly with the mysql client directly.

 
> 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);

Now we are ready to launch our first migration and create the initial structure for the application.

 
> cd deploy
> phing migrate

Now our database has a table with posts, but what about adding information about the author? We need to create another table and a foreign key, to do this, create another file for dbdeploy and call it 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.

Run the migration again.

 
shell> cd deploy
shell> phing migrate

Conclusion

That's all, now we know how to easily and effortlessly provide migration between versions of the database. If you do not want to paste the code to get a closer look, you can download the application archive .

There are many points when it comes to version control of a database, especially if you branch and merge your application code, some of which are described in detail in the dbdeploy documentation.

This guide is incomplete and if you think you have something to add, please leave a comment below. PS Phing Is Not GNU introductory article for a general overview of Phing.