postfixadmin-devel

[Postfixadmin-devel] (fwd) SF.net SVN: postfixadmin: [170] trunk/upgrade.php

[David G. <da...@co...>]

Hi,

Below is a quick stab at something that could do the upgrade procedure
for us.

Namely, every time in the future there is an update to do to the db, it
goes as a new function in upgrade.php

So... if in 'version' 56 I decide to remove a column, I would do
something like :

function upgrade_56() {
	// probably need to switch between database types.
	// see below.
	db_query('alter table add column bar woof foo');
}

I've missed out the logic to update the config table after an upgrade,
but have included the logic not to include the same update twice.

Any comments/observations?

The only problems with the below is that it can't run on it's own
without the table already existing - I can't seem to use db_query($sql)
if the table doesn't exist and catch the error. Does this imply
db_query($sql) needs updating to return some sort of error code or call
trigger_error() instead?

(I've forwarded the svn ci message, so you can see the code as
necessary)


thanks
David
----- Forwarded message from Gin...@us... -----

Added: trunk/upgrade.php
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
--- trunk/upgrade.php	                        (rev 0)
+++ trunk/upgrade.php	2007-10-31 20:18:53 UTC (rev 170)
@@ -0,0 +1,68 @@
+<?php
+require_once('common.php');
+$sql =3D "SELECT * FROM config WHERE name =3D 'version'";
+
+// create table config (name varchar(20), value varchar(20));
+// insert into config('version', '01');
+// Should really query the db to see if the 'config' table exists first!
+
+$r =3D db_query($sql);
+
+if($r['rows'] =3D=3D 1) {
+    $rs =3D $r['result'];
+    $row =3D db_array($rs);
+    $version =3D $row['value'];
+    _do_upgrade($version);
+}
+
+
+function _do_upgrade($current_version) {
+    $all_functions =3D get_defined_functions();
+    $upgrade_functions =3D array();
+    foreach($all_functions['user'] as $function_name) {
+        if(preg_match('!upgrade_(\d+)!', $function_name, $matches)) {
+            $version =3D $matches[1];
+            if($version <=3D $current_version) {
+                continue;
+            }
+            $upgrade_functions[$matches[1]] =3D $function_name;
+        }
+    }
+
+    ksort($upgrade_functions);
+    foreach($upgrade_functions as $version =3D> $function) {
+        $function();
+    }
+}
+
+function upgrade_00() {
+    global $CONF;
+    if($CONF['database_type'] =3D=3D 'mysql') {
+        echo 'mysql 00';
+    }
+    if($CONF['database_type'] =3D=3D 'pgsql') {
+        echo 'pgsql 00';
+    }
+}
+
+function upgrade_01() {
+    global $CONF;
+    if($CONF['database_type'] =3D=3D 'mysql') {
+        echo 'mysql 01';
+    }
+    if($CONF['database_type'] =3D=3D 'pgsql') {
+        echo 'pgsql 01';
+    }
+}
+function upgrade_02() {
+    global $CONF;
+    if($CONF['database_type'] =3D=3D 'mysql') {
+        echo 'mysql 02';
+    }
+    if($CONF['database_type'] =3D=3D 'pgsql') {
+        echo 'pgsql 02';
+    }
+}
+function upgrade_03() {
+    echo 'woof 03';
+}


This was sent by the SourceForge.net collaborative development platform, th=
e world's largest Open Source development site.

-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems?  Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
_______________________________________________
Postfixadmin-svn mailing list
Pos...@li...
https://lists.sourceforge.net/lists/listinfo/postfixadmin-svn

----- End forwarded message -----

--=20
David Goodwin=20

[ david at codepoets dot co dot uk ]
[ http://www.codepoets.co.uk       ]

[Postfixadmin-devel] Database upgrade (was: (fwd) SF.net SVN: postfixadmin: [170] trunk/upgrade.php)

[Christian B. <pos...@cb...>]

Hello,

Am Mittwoch, 31. Oktober 2007 schrieb David Goodwin:
> Below is a quick stab at something that could do the upgrade
> procedure for us.
>
> Namely, every time in the future there is an update to do to the db,
> it goes as a new function in upgrade.php
>
> So... if in 'version' 56 I decide to remove a column, I would do
> something like :

We can use the SVN revision as version to make the update bulletproof 
(it's too easy to forget to change a $database_level variable ;-)

Sidenote: I just learned that svn does keyword substitution only if you 
tell it to do so. A good SVN documentation is on 
http://svnbook.red-bean.com/en/1.4/svn.advanced.props.special.keywords.html

In short:
    svn propset svn:keywords "Revision" upgrade.php

I have done this and hope my next commit will insert the revision 
number.

> function upgrade_56() {
> 	// probably need to switch between database types.

You already know that I like the S9Y database code. I have taken an idea 
from there: Placeholders like {AUTOINCREMENT}.

I added the function db_query_parsed to upgrade.php and inserted the 
placeholders S9Y uses. Please check if the PgSQL strings are valid, and 
correct them if they are not.

> 	// see below.
> 	db_query('alter table add column bar woof foo');
> }
>
> I've missed out the logic to update the config table after an
> upgrade, 

Simply use CREATE TABLE IF NOT EXISTS ;-)  (given PgSQL supports it).

Otherwise, we'll have to check if the table exists.

MySQL:   SHOW TABLES LIKE 'config'

I don't know if PgSQL supports the SHOW TABLES command or if we have to 
read from information schema 
http://www.postgresql.org/docs/8.2/interactive/infoschema-tables.html

Another option is to simply ignore the error ;-)

> but have included the logic not to include the same update 
> twice.
>
> Any comments/observations?

The basic idea is good.

However, I change the do_upgrade function to use a classical
    for ($i = 1; $i <= 10; $i++)
loop and function_exists(). This awoids the need to sort the upgrade 
functions (which will break when we have >99 functions) ;-)
Note that you may not use leading zeros!

I also added the option to create upgrade_<number>_mysql and 
upgrade_<number>_pgsql functions for database-specific changes.

> The only problems with the below is that it can't run on it's own
> without the table already existing - I can't seem to use
> db_query($sql) if the table doesn't exist and catch the error. Does
> this imply db_query($sql) needs updating to return some sort of error
> code or call trigger_error() instead?

I have added an optional $ignore_error parameter to db_query ;-)

I also dived into DOCUMENTATION/* and the SVN log and added all needed 
update commands to upgrade.php.

There are still several TODO notes in upgrade.php, especially related to 
PgSQL.

While talking about database upgrade:
I propose we should
- create the tables within upgrade.php (maybe we should rename it ;-)
- do all future database changes as ALTER statements within upgrade.php
- the function numbers should always be the SVN revision numbers of the 
  change
- drop DATABASE_*.TXT
- remaining question: where do we put the GRANT statements etc.?
  Or can we assume that people who run a mailserver know how to grant
  database permissions?
- drop DOCUMENTS/TABLE_BACKUP_MX.TXT and DOCUMENTS/TABLE_CHANGES.TXT and
  remove the references to these files in DOCUMENTS/UPGRADE.TXT


Regards,

Christian Boltz
-- 
Now I hope the best for my seven 1.44MB disks, oh yes, very old ...
and I feel about 8 years younger by copying files to disks.
[Thomas Porschberg in opensuse]

Re: [Postfixadmin-devel] Database upgrade (was: (fwd) SF.net SVN: postfixadmin: [170] trunk/upgrade.php)

[David G. <da...@co...>]

> We can use the SVN revision as version to make the update bulletproof 
> (it's too easy to forget to change a $database_level variable ;-)
> 
> Sidenote: I just learned that svn does keyword substitution only if you 
> tell it to do so. A good SVN documentation is on 
> http://svnbook.red-bean.com/en/1.4/svn.advanced.props.special.keywords.html
 
 Yes, that's an excellent manual ! 

> 
> You already know that I like the S9Y database code. I have taken an idea 
> from there: Placeholders like {AUTOINCREMENT}.
 
 Will look later

> 
> Simply use CREATE TABLE IF NOT EXISTS ;-)  (given PgSQL supports it).
 
 Yes, that was my problem - I don't think PostgreSQL does.

> I don't know if PgSQL supports the SHOW TABLES command or if we have to 
> read from information schema 
> http://www.postgresql.org/docs/8.2/interactive/infoschema-tables.html
 

Probably.

> Another option is to simply ignore the error ;-)

I couldn't - as db_query was calling 'die()' when it failed.

> The basic idea is good.
> 
> However, I change the do_upgrade function to use a classical
>     for ($i = 1; $i <= 10; $i++)
> loop and function_exists(). This awoids the need to sort the upgrade 
> functions (which will break when we have >99 functions) ;-)
> Note that you may not use leading zeros!

Can they not be ordered lexiographically (spelling?) which would fix
that issue; or alterantively, the script 'casts' the number into
something with enough leading zeros as required using printf?

> 
> I also added the option to create upgrade_<number>_mysql and 
> upgrade_<number>_pgsql functions for database-specific changes.
 
I thought of that - but decided to not bother splitting it into two
seperate bits - thinking that if all of a change was in one statement it
would make it easier to follow. Of course, I could be mistaken about
this.

> > The only problems with the below is that it can't run on it's own
> > without the table already existing - I can't seem to use
> > db_query($sql) if the table doesn't exist and catch the error. Does
> > this imply db_query($sql) needs updating to return some sort of error
> > code or call trigger_error() instead?
> 
> I have added an optional $ignore_error parameter to db_query ;-)

Cool, thanks.

> I also dived into DOCUMENTATION/* and the SVN log and added all needed 
> update commands to upgrade.php.
 
Excellent.

> There are still several TODO notes in upgrade.php, especially related to 
> PgSQL.
 
I'll try and resolve these soon.

> While talking about database upgrade:
> I propose we should
> - create the tables within upgrade.php (maybe we should rename it ;-)
> - do all future database changes as ALTER statements within upgrade.php
> - the function numbers should always be the SVN revision numbers of the 
>   change
> - drop DATABASE_*.TXT
> - remaining question: where do we put the GRANT statements etc.?
>   Or can we assume that people who run a mailserver know how to grant
>   database permissions?
> - drop DOCUMENTS/TABLE_BACKUP_MX.TXT and DOCUMENTS/TABLE_CHANGES.TXT and
>   remove the references to these files in DOCUMENTS/UPGRADE.TXT
> 


All good ideas to me. If it's easy enough to write, it does make more
sense to create the initial database structure from 'upgrade.php' as
well as handling anything else.

You've been busy!


thanks
David.
-- 
David Goodwin 

[ david at codepoets dot co dot uk ]
[ http://www.codepoets.co.uk       ]

Re: [Postfixadmin-devel] Database upgrade (was: (fwd) SF.net SVN: postfixadmin: [170] trunk/upgrade.php)

[Christian B. <pos...@cb...>]

Hello,

Am Freitag, 2. November 2007 schrieb David Goodwin:
> > Simply use CREATE TABLE IF NOT EXISTS ;-)  (given PgSQL supports
> > it).
>
>  Yes, that was my problem - I don't think PostgreSQL does.

:-(

> > I don't know if PgSQL supports the SHOW TABLES command or if we
> > have to read from information schema
> > http://www.postgresql.org/docs/8.2/interactive/infoschema-tables.ht
> >ml
>
> Probably.

You'll need to search for the details - I don't know much about PgSQL 
and had to do lots of searches in the PgSQL documemtation to make the 
SQL commands in upgrade.php as compatible as possibe.

> > Another option is to simply ignore the error ;-)
>
> I couldn't - as db_query was calling 'die()' when it failed.

Seems you used an old revision ;-)))

> > The basic idea is good.
> >
> > However, I change the do_upgrade function to use a classical
> >     for ($i = 1; $i <= 10; $i++)
> > loop and function_exists(). This awoids the need to sort the
> > upgrade functions (which will break when we have >99 functions) ;-)
> > Note that you may not use leading zeros!
>
> Can they not be ordered lexiographically (spelling?) which would fix
> that issue; or alterantively, the script 'casts' the number into
> something with enough leading zeros as required using printf?

Maybe we talk at cross purposes - there is no need to use leading zeros.
This avoids all problems with fixed length. Given we would use fixed 
length: what if we exceed the number the length allows? (Short answer: 
rename _all_ upgrade_* functions.)
Without leading zeroes, it's very easy - there is no limit that we could 
exceed.

The "sorting" of the functions is simply done when editing upgrade.php - 
and if someone adds upgrade_42 between upgrade_2 and upgrade_3, I 
promise to learn how to use the svn revert command ;-))

Running all functions in the correct order is also no problem. If you 
have doubts, try
    for ($i = 1, $i <= 100; $i++) { echo "$i\n"; }

> > I also added the option to create upgrade_<number>_mysql and
> > upgrade_<number>_pgsql functions for database-specific changes.
>
> I thought of that - but decided to not bother splitting it into two
> seperate bits - thinking that if all of a change was in one statement
> it would make it easier to follow. Of course, I could be mistaken
> about this.

This was also my first thought, but then I noticed that
- some changes only affect a specific database type (for example MySQL 
  engine and charset changes)
- some changes need a very different syntax, and it's not always 
  possible to merge into a single query

Therefore I added the _option_ to have database specific functions.
You can (and usually should) use upgrade_<number> for changes that 
affect all database types. 

The _mysql and _pgsql functions are just to avoid the need for checking 
the database type in each function. In this case, I recommend to add a 
comment "# MySQL only" or "# PgSQL only" at the "function ...()" line 
to make it more visible that the function only runs on one database.

[...]
> > There are still several TODO notes in upgrade.php, especially
> > related to PgSQL.
>
> I'll try and resolve these soon.

Have fun - writing the ALTER statements can sometimes be very 
interesting...

> > While talking about database upgrade:
> > I propose we should
> > - create the tables within upgrade.php (maybe we should rename it
> >   ;-) - do all future database changes as ALTER statements within
> >   upgrade.php - the function numbers should always be the SVN
> >   revision numbers of the change
> > - drop DATABASE_*.TXT
> > - remaining question: where do we put the GRANT statements etc.?
> >   Or can we assume that people who run a mailserver know how to
> >   grant database permissions?
> > - drop DOCUMENTS/TABLE_BACKUP_MX.TXT and
> >   DOCUMENTS/TABLE_CHANGES.TXT and remove the references to these
> >   files in DOCUMENTS/UPGRADE.TXT
>
> All good ideas to me. If it's easy enough to write, it does make more
> sense to create the initial database structure from 'upgrade.php' 

We will have to make the database dumps database independent (with 
{whatever} placeholders if needed), but this shouldn't be too hard.

This is the "hardest" part - actually creating the tables simply means 
adding another upgrade_* function. (Having a table_exists() function 
would be nice, but ignoring errors also works.)

While talking about ignoring errors: Maybe we could even check for 
the "usual" error messages and display everything else.

The list of error messages to ignore isn't too long. For MySQL, in RegEx 
format:

   Invalid query: Can't DROP .*; check that column/key exists
     -> Index already dropped

   Invalid query: Duplicate column name .*
     -> column already added

   Invalid query: Unknown column 'change_date' in .*
   Invalid query: Unknown column 'create_date' in .*
     -> renamed columns, listed with name because this shouldn't happen 
        too often

You'll have to find out the PgSQL error messages ;-)

> as well as handling anything else.

Tip: The developer doing a database change can simply paste his ALTER 
TABLE statement (he needs it anyway to change his local database)
to upgrade.php, which is even easier than hand-editing the DATABASE_* 
files.


Regards,

Christian Boltz
-- 
And as Novell has shown with the packager: it's never too late for 
a beta, to add new features ;-)     [Marcel Hilzinger in opensuse]