News Archive
PhpRiot Newsletter
Your Email Address:

More information

Script for Database Patching at Deploy Time

Note: This article was originally published at Planet PHP on 15 April 2011.
Planet PHP
I've written before about a simple way of patching database versions and there's a much more comprehensive article from Harrie on TechPortal as well. I often find though that projects with patching strategies are missing the scripts to apply these automatically when the code is deployed, so I thought I'd share mine.

My current project (BiteStats, a simple report of your google analytics data) uses a basic system where there are numbered patches, and a patch_history table with a row for every patch that was run, showing the version number and a timestamp. When I deploy the code to production, I have a script that runs automatically to apply the patches.

A A A A // get current patch level
A A A A $stmt = $db-query('select max(patch_number) as last from patch_history');
A A A A $result = $stmt-fetch();
A A A A $last_patch = $result['last'];
A A A A echo "current db patch level: $last_patch\n";

A A A A // get list of patches
A A A A $patches = glob(APPLICATION_PATH . '/../db/patch-*.sql');
A A A A foreach($patches as $patch_file) {
A A A A A A $pattern_matches = array();
A A A A A A preg_match('/patch-([0-9]{3})\.sql$/',$patch_file, $pattern_matches);
A A A A A A if($pattern_matches[1] $last_patch) {
A A A A A A A A echo

Truncated by Planet PHP, read more at the original (another 5096 bytes)