[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]