Home > PHP, software development > phing + dbdeploy website deployment

phing + dbdeploy website deployment

November 8th, 2010 Leave a comment Go to comments

I recently had a project with xs2theworld to help create the mobile websites for intel asia. Because this project was quite important and I wanted to step up my game I created a proper deployment strategy. No more sweaty palms while running custom scripts, pressing svn up or switching symlinks. I wanted a fully automated deployment. A deployment I could test, run and always get the same result.

Because I’ve been hearing about phing and dbdeploy from dragonbe and harrieverveer I looked into them. They ended up being excellent tools to reach my goal.

Phing is a deployment tool in which you can create a deployment “script” made up out of a ant like syntax using xml.

<copy todir="${buildDir}" >
  <fileset dir="${projRoot}">
    <include name="**" />
  </fileset>
</copy>

DBdeploy is a tool that compares your patches to your database and creates a forward SQL patch and a backwards SQL patch, aggregating your SQL patches in the forward file and the undo statements in the backwards file.

In this blog post I will highlight some of the things I did.

I created an actual ‘build’ stage, where all the website elements were processed and copied into a separate build directory. The reason for this was two-fold. Firstly I wanted to be able to check the result of a ‘build’ without it being deployed, especially the SQL patches that dbdeploy generated. Secondly, I wanted to only copy those files that were needed for the site to run. So no .git directory, no sql patches directory, etc..
This has really been a great choice, because of the separate build stage i’ve at least had two instances in which i caught a problem before deployment. Saving me from the embarrassment of having to make quick fixes while the site was in offline mode.

I created separate phing property files for different environments. (production, staging, development) this combined with a simple wrapper script that called phing resulted in a very pleasant way of deploying by just issuing a command like “./deploy build development” or “./deploy rollout production” and the inevitable “./deploy rollback production”. Much better then “phing -Denvironment=staging build”.  Property files are basically ini files that contain key/value pairs that can be referenced from within your phing build file.
Then in phing you can say “<property file=”deploy/${environment}.properties” />” and it will read the property file. Please note that “${environment}” refers to a variable. which in my case was set when calling phing. (the -Denvironment=)

Dbdeploy is a piece of software that can read your SQL patch files, compare them to the database and create a single SQL file you can run to update your database. Unfortunately dbdeploy is very fussy about the separator you use between your patch and your undo patch. Yes, undo patch. At some point you want to rollback a deployment and at that time you really do not want to find out that you can’t because the new table structure breaks the old code.
It only takes very little time to create undo statements and you will avoid excruciating minutes of frantically applying changes manually when things break.
Also when creating undo statements be sure to set them in the reverse sequence of your normal sql patch statements.
e.g.

ALTER TABLE `myrecords`  ADD `rank` int NOT NULL;
RENAME TABLE `myrecords`  TO `myrecord`;

-- //@UNDO

RENAME TABLE `myrecord` TO `myrecords`;
ALTER TABLE `myrecords` DROP `rank`;

Also, that is how you should write the undo separator. Exactly like that. If you don’t dbdeploy will simply add the undo section to your deployment SQL file as well. Which is very much unwanted.
Also be sure to always, ALWAYS, ALWAYS! run both your forward SQL patch as well as your backwards SQL patch to make sure it works. Preferably not on production.

That’s about it I guess. There are many wonderful guides that will explain how to use phing and dbdeploy in detail, which is the reason I didn’t. I just wanted to pass along some things I used and thought worked nicely.
I would like to point people who want to read more about phing to the following blog posts that helped me heaps:
– Diving into Phing I http://groups.drupal.org/node/4363
– Diving into Phing II http://groups.drupal.org/node/5400
– Phing Build File http://sean.gravener.net/blog/web-design/phing-build-file/134/
– How To: Simple database migrations with Phing and DbDeploy (*this was a bit outdated*) http://www.davedevelopment.co.uk/2008/04/14/how-to-simple-database-migrations-with-phing-and-dbdeploy/

most important though.
http://phing.info
http://dbdeploy.com/

  1. November 8th, 2010 at 22:19 | #1

    Interesting post Erik!
    Coincidentally i am currently working on a new deployment procedure for the projects i do at work.
    Phing is one of the solutions i was considering. Your explanation describes exactly one of the problems i was running into, and this really cleared things up.

    Dbdeploy is totally new to me, but this tool also seems very useful to the process. Definitely worth looking into.

    Thanks!

  1. No trackbacks yet.