[Phpwm] mysql deployment

[elliot at townx.org]

Hello,

On Tue, 25 Aug 2009 12:14:09 +0100, David Goodwin <david at codepoets.co.uk>
wrote:
> -----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
> 

I like David's solution here - it's pretty much the Drupal model for
upgrades, which has worked well for me in the past.

If you find yourself needing rollbacks, it might be worth investigating the
migrations feature of ActiveRecord (part of Rails), or maybe any PHP
variants of it (maybe something like it in symfony or CakePHP). This
enables scripting of upgrading/downgrading in a reasonably elegant fashion.
Failing that, you could implement your own take on it.

The issue with using dump files is that you don't want to overwrite any
changes to live data (e.g. if customers are adding content or config. to
the db). You might also need data updates (e.g. adding a value for a new
field to existing records). If those aren't issues for you (e.g. if the db
is read-only), I'd say that could work pretty well too.

Elliot





> 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 "   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-----
> 
> _______________________________________________
> Phpwm mailing list
> Phpwm at mailman.lug.org.uk
> https://mailman.lug.org.uk/mailman/listinfo/phpwm