Over a million developers have joined DZone.

PHP Script for Database Patching at Deploy Time

· Web Dev Zone

Start coding today to experience the powerful engine that drives data application’s development, brought to you in partnership with Qlik.

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.

Create data driven applications in Qlik’s free and easy to use coding environment, brought to you in partnership with Qlik.

Topics:

Published at DZone with permission of Lorna Mitchell, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}