|
From: Christian B. <pos...@cb...> - 2007-11-02 00:30:45
|
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] |
