Script for Database Patching at Deploy Time

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)

• Read the original article at Planet PHP