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.

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

// get list of patches
$patches = glob(APPLICATION_PATH . '/../db/patch-*.sql');
foreach($patches as $patch_file) {
    $pattern_matches = array();
    preg_match('/patch-([0-9]{3})\.sql$/',$patch_file, $pattern_matches);
    if($pattern_matches[1] > $last_patch) {
        echo "running $patch_file\n";
        $cmd = 'mysql -h ' . escapeshellarg($db_params['host'] )
            . ' -u ' . escapeshellarg($db_params['username'] )
            . ' -p' . escapeshellarg($db_params['password'] )
            . ' ' . escapeshellarg($db_params['dbname'] )
            . ' 2>&1 < ' . escapeshellarg($patch_file);
        exec($cmd, $output, $retval);
        if($retval != 0) {
            var_dump($output);
            exit;
        }
    }
}

This is actually a ZF application (as you might be able to guess) and I have one controller bootstrapped to run from the CLI. The code shown here resides in a action in the CliController, which makes it perfect for use in a scripted deployment - this project uses phing to deploy, which I am really enjoying using. I can also just call the script directly if I've updated a copy of the code in a development or staging area and there are new patches.