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 to build modern digital experience apps with Crafter CMS. Download this eBook now. Brought to you in partnership with Crafter Software

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.

Crafter is a modern CMS platform for building modern websites and content-rich digital experiences. Download this eBook now. Brought to you in partnership with Crafter Software.

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 }}