DaveDevelopment::Dave Marshall

I had a problem today at work, I’ve been coding exclusively in PHP5.2 since it was available and most of the servers I’ve been working for are Debian or Ubuntu based, so I didn’t have any problems until this afternoon. We’ve recently bought a SAN solution from Dell and to gain support we bought two new servers, both with SUSE Enterprise Linux installed, which only comes with PHP 5.1.2. That particular version came out in January 2006. Since then I’ve been using the new DateTime object, the filter functions, memory_get_peak_usage() and sys_get_temp_dir(). And they’re only the problems I noticed. We could install from source, but then we lose the subtle benefits of package management.

So, this lead to me wondering what LAMP versions the popular distros are using, with the help of DistroWatch, I compiled this table. It only shows the community/open source distributions, the commercials counterparts for each are usually at least a year behind, guaranteeing support but only for out of date versions.

Introduction

This How To will introduce some simple database migrations to your PHP application. 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.

Install Phing

I always use the beta or release candidate of phing and for the purposes of this article I suggest you do too. The best way to download and install phing is using PEAR. This can be done on Linux or Windows assuming you have the pear script in your PATH with three shell commands.

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

Example Application structure

As an example, we’re going to develop a simple application with the following directory structure.

example/
 |-- db/
 |   `-- deltas/
 |-- deploy/
 |   `-- scripts/
 |-- library/
 `-- public/

The db directory contains sql files for using and manipulating our database and
the deploy directory contains our build scripts that set the migrations in motion. The library directory contains our application code and the public folder will contain scripts and files accessible directly from the web, but will not be the focus of this article.

Build scripts

This section shows you how to develop the build scripts that will run the database migrations. The first file we need to create is a simple configuration file and should be fairly self explanatory. The file is written as key=value, lines beginning with a # are comments. Open your editor and save the following text as 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 next file we are going to create is the deploy/build.xml file. This is the file that tells Phing what we want it to do. I’m not going to go into too much detail describing each part of the build file, there are some comments, but you should consult the Phing Documentation for further details and enhancements.

<?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} &lt; ${build.dbdeploy.deployfile}"
            dir="${build.dir}"
            checkreturn="true" />
    </target>
</project>

That’s essentially all the magic we need. Now we just need to create our database.

Writing dbdeploy delta scripts

We haven’t actually created our database, so rather than create it the traditional way, we will actually use the migrations to create the initial schema. We’ve not actually decided what our example application does yet, but seeing as most tutorials make blogs, why don’t we give that a bash. We’ll start simple, one table with three columns called post.

Dbdeploy works by creating numbered delta files. Each delta files contains simple SQL to both deploy the change and roll it back. The basic layout of a delta file is like so.

--//

-- Run SQL to do the changes

--//@UNDO

-- RUN SQL to undo the changes

--//

We are creating our initial schema, so put the following content in db/deltas/1-create_initial_schema.sql

--//

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

--//@UNDO

DROP TABLE `post`;

--//

Migrating the database

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

shell> mysql -hlocalhost -uroot -ppassword example
mysql> 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
);
mysql> ALTER TABLE changelog ADD CONSTRAINT Pkchangelog PRIMARY KEY (change_number, delta_set);

We are now ready to run our first migration and create the initial schema for our application.

shell>cd deploy
shell>phing migrate

All being well, we now have a posts table in our database. But what about an author for our blog posts? We’ll have to add another table and a foreign key from the post table to author table. To do this we create another delta, we call this one 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`;

--//

Run our migrations again.

shell> cd deploy
shell> phing migrate

Conclusion

That’s pretty much it, you’ve seen how to create database deltas and use them to migrate your database, if you can’t be bothered to copy and paste things to try for yourself, download the example application.

There are plenty of caveats when it comes to version controlling databases, especially if you branch and merge your application code, some are detailed in the dbdeploy documentation

This tutorial is probably incomplete or wrong in plenty of ways, if you think you have something to point out, please leave your comments below

Here’s a simple one liner you can use to syntax check all php files in your working directory.

find . -type f -name "*.php" -exec php -l {} \; | grep -v 'No syntax errors'

For those not familiar with the programs used, it basically reads as…. Find all files that end in ‘.php’ and with each of those files run php -l. This is then put through a pipe to the grep -v, which filters out all files that are syntactically correct.

Just a quick note, I’ve recently moved from Gentoo to Ubuntu on my desktop machine at home. I had a few problems with my Gentoo install and while I find Gentoo great for learning the ins and outs of things, as I do more and more freelance work, I need a OS environment that’s not going to let me down. My point being environment, Gentoo is more than capable of being extremely stable, but it takes a lot more knowledge to get it and keep it stable. Whilst I was happy learning and taking my time before, things need to happen a little more urgently now.

I chose Ubuntu mainly because I run Debian on my dedicated servers and am already familiar with the package management, but also because I wanted to see what the fuss is a bout. I’m very pleased so far. I’m running Mozilla Firefox, Mozilla Thunderbird, GNUCash, OpenOffice.org, Amarok and KTorrent day to day, the latter two despite using GNOME for my desktop.

Thanks to this mailing list post I now have a ‘Bash here’ option in my windows explorer context menu for folders and drives. Simply add this registry script to the windows registry.

Thanks to my self and this little script, I can open a ‘windows explorer window here’ from within cygwin.

#! /bin/bash
PWD=`pwd`
PATH=`cygpath -w $PWD`
/cygdrive/c/WINDOWS/explorer.exe $PATH