Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

PHP Script for Database Patching at Deploy Time

DZone's Guide to

PHP Script for Database Patching at Deploy Time

· Web Dev Zone ·
Free Resource

Learn how error monitoring with Sentry closes the gap between the product team and your customers. With Sentry, you can focus on what you do best: building and scaling software that makes your users’ lives better.

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.

What’s the best way to boost the efficiency of your product team and ship with confidence? Check out this ebook to learn how Sentry's real-time error monitoring helps developers stay in their workflow to fix bugs before the user even knows there’s a problem.

Topics:

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}