[Phpwm] mysql deployment

[David Goodwin]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Jujhar Singh wrote:
> Morning All,
> 
>  
> 
> I was wondering what best practice was for deployment of mysql databases?
> 
>  


We have a simple script that...
a) Creates a config table in the database if it doesn't already exist.
b) Queries the config table to find the latest version number
c) Runs all functions it can find within the file with a number >
$version_number
d) Updates the config table - so it knows to not repeat the updates

Alternatively (or as well) you can write your DB queries / code so that
it only runs if (for instance) a db field doesn't already exist.

I've attached a stripped down version of the file (upgrade.php) which
you are free to use.

Note - it doesn't allow you to go 'down' (i.e. revert a change); we've
never had to do this... our problem has always been ensuring updates are
applied as the code/site is updated.


thanks
David.



<?php
// <snip>

$table = 'config';
$mysql = "CREATE TABLE IF NOT EXISTS $table (
        `id` int(11) not null auto_increment primary key,
        `name`  VARCHAR(20) NOT NULL DEFAULT '',
        `value` VARCHAR(20) NOT NULL DEFAULT '',
        UNIQUE name ( `name` )
        )
    ";
$db->query($mysql);

$sql = "SELECT * FROM $table WHERE name = 'version'";

$res = $db->query($sql);
$rows = $res->getRows();
if(sizeof($rows) > 0) {
    $version = $rows[0]['value'];
}
else {
    $db->query("INSERT INTO $table (name, value) VALUES ('version', '0')");
    $version = 1;
}

if(!empty($_GET['force'])) {
    $version = 1;
}
_do_upgrade($version);

function _mysql_field_exists($table, $field) {
    global $db;
    $sql = "SHOW COLUMNS FROM $table LIKE ?";
    $res = $db->query($sql, array($field));
    $rows = $res->getRows();
    if(sizeof($rows) > 0) {
        return true;
    }
    return false;
}
function _do_upgrade($current_version) {
    global $db;
    $i = $current_version;
    while(true) {
        $function = "upgrade_$i";
        if(function_exists($function)) {
            echo "<hr/><br/>";
            echo "<p>updating to version $i ...";
            $output = $function();
            if(!empty($output)) {
                echo " $output - done";
            }
            else {
                echo " &nbsp; done";
            }
        }
        else {
            break;
        }
        $i++;
    }
    echo "<br/><br/>Database up to date (revision $i)";
    // Update config table so we don't run the same query twice in the
future.
    $table = 'config';
    $sql = "UPDATE $table SET value = $i WHERE name = 'version'";
    $db->query($sql);
}


function upgrade_1() {
    $db->query('CREATE TABLE IF NOT EXISTS .........');
}
function upgrade_2() {
    if(!_mysql_field_Exists('foo', 'bar')) {
        $db->query('ALTER TABLE foo ADD COLUMN bar .......');
    }
}


- --
David Goodwin

[ david at codepoets dot co dot uk ]
[ http://www.codepoets.co.uk       ]
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkqTx4AACgkQ/ISo3RF5V6aK6wCg6E1yKfbsFlKg5IYTmI2rMRa7
xcQAn1//FSVirhf4q93UH/hxCxNb7V8b
=+XJ5
-----END PGP SIGNATURE-----