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

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 DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

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

X

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

{{ parent.tldr }}

{{ parent.urlSource.name }}