Thread: SF.net SVN: postfixadmin:[514] trunk/upgrade.php (Page 2)
Brought to you by:
christian_boltz,
gingerdog
From: <Gin...@us...> - 2009-01-14 13:13:33
|
Revision: 514 http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=514&view=rev Author: GingerDog Date: 2009-01-14 13:13:25 +0000 (Wed, 14 Jan 2009) Log Message: ----------- upgrade.php: see tdiehl on irc.. perhaps this fixes his problem (i hate mysql) Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2009-01-13 14:22:00 UTC (rev 513) +++ trunk/upgrade.php 2009-01-14 13:13:25 UTC (rev 514) @@ -756,8 +756,6 @@ $result = db_query_parsed($sql, TRUE); } - # creation of vacation_notification table moved to upgrade_318_mysql because - # the query in this function was broken (key length vs. utf8 charset) } /** @@ -793,7 +791,7 @@ db_query_parsed( " CREATE TABLE {IF_NOT_EXISTS} $table_vacation_notification ( - on_vacation varchar(255) NOT NULL, + on_vacation varchar(255) {LATIN1} NOT NULL, notified varchar(255) NOT NULL, notified_at timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY on_vacation (`on_vacation`, `notified`), This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <Gin...@us...> - 2009-05-28 19:48:08
|
Revision: 667 http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=667&view=rev Author: GingerDog Date: 2009-05-28 19:47:52 +0000 (Thu, 28 May 2009) Log Message: ----------- try and avoid naming conflicts so put _idx on the end of all postgres indexes Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2009-05-24 11:12:34 UTC (rev 666) +++ trunk/upgrade.php 2009-05-28 19:47:52 UTC (rev 667) @@ -229,7 +229,7 @@ if ($CONF['database_type'] == 'mysql' || $CONF['database_type'] == 'mysqli' ) { return "ALTER TABLE $table ADD INDEX `$indexname` ( `$fieldlist` )"; } elseif($CONF['database_type'] == 'pgsql') { - $pgindexname = $table . "_" . $indexname; + $pgindexname = $table . "_" . $indexname . '_idx'; return "CREATE INDEX $pgindexname ON $table($fieldlist);"; # Index names are unique with a DB for PostgreSQL } else { echo "Sorry, unsupported database type " . $conf['database_type']; This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <Gin...@us...> - 2009-09-14 19:52:34
|
Revision: 719 http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=719&view=rev Author: GingerDog Date: 2009-09-14 19:52:26 +0000 (Mon, 14 Sep 2009) Log Message: ----------- quotation fix; use double quotes with $ silly (thanks to ksb4ever - #2858717 ) Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2009-09-06 22:03:14 UTC (rev 718) +++ trunk/upgrade.php 2009-09-14 19:52:26 UTC (rev 719) @@ -600,7 +600,7 @@ COMMIT;" ); if(!_pgsql_object_exists('mailbox_username_active')) { - db_query_parsed('CREATE INDEX mailbox_username_active ON $table_mailbox(username,active)'); + db_query_parsed("CREATE INDEX mailbox_username_active ON $table_mailbox(username,active)"); } This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <chr...@us...> - 2009-10-06 21:49:07
|
Revision: 723 http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=723&view=rev Author: christian_boltz Date: 2009-10-06 21:48:59 +0000 (Tue, 06 Oct 2009) Log Message: ----------- upgrade.php: - replaced TYPE=... in CREATE/ALTER TABLE statements with ENGINE= to fix compability with MySQL 6.x (TYPE= is no longer supported) https://sourceforge.net/projects/postfixadmin/forums/forum/676076/topic/3377035 - new placeholders {INNODB} and {MYISAM} - I don't hope that this needs to be changed again, but you never know ;-) Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2009-09-30 09:31:00 UTC (rev 722) +++ trunk/upgrade.php 2009-10-06 21:48:59 UTC (rev 723) @@ -169,6 +169,8 @@ '{LATIN1}' => '/*!40100 CHARACTER SET latin1 */', '{IF_NOT_EXISTS}' => 'IF NOT EXISTS', '{RENAME_COLUMN}' => 'CHANGE COLUMN', + '{MYISAM}' => 'ENGINE=MyISAM', + '{INNODB}' => 'ENGINE=InnoDB', ); $sql = "$sql $attach_mysql"; @@ -179,10 +181,12 @@ '{UNSIGNED}' => '', '{FULLTEXT}' => '', '{BOOLEAN}' => 'BOOLEAN NOT NULL', - '{UTF-8}' => '', # TODO: UTF-8 is simply ignored. - '{LATIN1}' => '', # TODO: same for latin1 - '{IF_NOT_EXISTS}' => '', # TODO: does this work with PgSQL? NO + '{UTF-8}' => '', # UTF-8 is simply ignored. + '{LATIN1}' => '', # same for latin1 + '{IF_NOT_EXISTS}' => '', # does not work with PgSQL '{RENAME_COLUMN}' => 'ALTER COLUMN', # PgSQL : ALTER TABLE x RENAME x TO y + '{MYISAM}' => '', + '{INNODB}' => '', 'int(1)' => 'int', 'int(10)' => 'int', 'int(11)' => 'int', @@ -257,7 +261,7 @@ `modified` datetime NOT NULL default '0000-00-00 00:00:00', `active` tinyint(1) NOT NULL default '1', PRIMARY KEY (`username`) - ) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Admins';"; + ) {MYISAM} COMMENT='Postfix Admin - Virtual Admins';"; $sql[] = " CREATE TABLE {IF_NOT_EXISTS} $alias ( @@ -268,7 +272,7 @@ `modified` datetime NOT NULL default '0000-00-00 00:00:00', `active` tinyint(1) NOT NULL default '1', PRIMARY KEY (`address`) - ) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Aliases'; "; + ) {MYISAM} COMMENT='Postfix Admin - Virtual Aliases'; "; $sql[] = " CREATE TABLE {IF_NOT_EXISTS} $domain ( @@ -284,7 +288,7 @@ `modified` datetime NOT NULL default '0000-00-00 00:00:00', `active` tinyint(1) NOT NULL default '1', PRIMARY KEY (`domain`) - ) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Domains'; "; + ) {MYISAM} COMMENT='Postfix Admin - Virtual Domains'; "; $sql[] = " CREATE TABLE {IF_NOT_EXISTS} $domain_admins ( @@ -293,7 +297,7 @@ `created` datetime NOT NULL default '0000-00-00 00:00:00', `active` tinyint(1) NOT NULL default '1', KEY username (`username`) - ) TYPE=MyISAM COMMENT='Postfix Admin - Domain Admins';"; + ) {MYISAM} COMMENT='Postfix Admin - Domain Admins';"; $sql[] = " CREATE TABLE {IF_NOT_EXISTS} $log ( @@ -303,7 +307,7 @@ `action` varchar(255) NOT NULL default '', `data` varchar(255) NOT NULL default '', KEY timestamp (`timestamp`) - ) TYPE=MyISAM COMMENT='Postfix Admin - Log';"; + ) {MYISAM} COMMENT='Postfix Admin - Log';"; $sql[] = " CREATE TABLE {IF_NOT_EXISTS} $mailbox ( @@ -317,7 +321,7 @@ `modified` datetime NOT NULL default '0000-00-00 00:00:00', `active` tinyint(1) NOT NULL default '1', PRIMARY KEY (`username`) - ) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Mailboxes';"; + ) {MYISAM} COMMENT='Postfix Admin - Virtual Mailboxes';"; $sql[] = " CREATE TABLE {IF_NOT_EXISTS} $vacation ( @@ -330,7 +334,7 @@ active tinyint(4) NOT NULL default '1', PRIMARY KEY (email), KEY email (email) - ) ENGINE=InnoDB DEFAULT CHARSET=utf8 TYPE=InnoDB COMMENT='Postfix Admin - Virtual Vacation' ;"; + ) {INNODB} DEFAULT CHARSET=utf8 COMMENT='Postfix Admin - Virtual Vacation' ;"; foreach($sql as $query) { db_query_parsed($query); @@ -657,7 +661,7 @@ `active` tinyint(1) NOT NULL default '1', PRIMARY KEY (`username`), KEY username (`username`) -) TYPE=MyISAM DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Admins'; "); +) {MYISAM} DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Admins'; "); $result = db_query_parsed(" CREATE TABLE {IF_NOT_EXISTS} `" . table_by_key('alias') . "` ( @@ -669,7 +673,7 @@ `active` tinyint(1) NOT NULL default '1', PRIMARY KEY (`address`), KEY address (`address`) - ) TYPE=MyISAM DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Aliases'; + ) {MYISAM} DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Aliases'; "); $result = db_query_parsed(" @@ -687,7 +691,7 @@ `active` tinyint(1) NOT NULL default '1', PRIMARY KEY (`domain`), KEY domain (`domain`) - ) TYPE=MyISAM DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Domains'; + ) {MYISAM} DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Domains'; "); $result = db_query_parsed(" @@ -697,7 +701,7 @@ `created` datetime NOT NULL default '0000-00-00 00:00:00', `active` tinyint(1) NOT NULL default '1', KEY username (`username`) - ) TYPE=MyISAM DEFAULT {LATIN1} COMMENT='Postfix Admin - Domain Admins'; + ) {MYISAM} DEFAULT {LATIN1} COMMENT='Postfix Admin - Domain Admins'; "); $result = db_query_parsed(" @@ -708,7 +712,7 @@ `action` varchar(255) NOT NULL default '', `data` varchar(255) NOT NULL default '', KEY timestamp (`timestamp`) - ) TYPE=MyISAM DEFAULT {LATIN1} COMMENT='Postfix Admin - Log'; + ) {MYISAM} DEFAULT {LATIN1} COMMENT='Postfix Admin - Log'; "); $result = db_query_parsed(" @@ -724,7 +728,7 @@ `active` tinyint(1) NOT NULL default '1', PRIMARY KEY (`username`), KEY username (`username`) - ) TYPE=MyISAM DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Mailboxes'; + ) {MYISAM} DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Mailboxes'; "); $result = db_query_parsed(" @@ -738,7 +742,7 @@ `active` tinyint(1) NOT NULL default '1', PRIMARY KEY (`email`), KEY email (`email`) - ) TYPE=MyISAM DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Vacation'; + ) {MYISAM} DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Vacation'; "); } @@ -764,7 +768,7 @@ ALTER TABLE `$table_vacation` CHANGE `domain` `domain` VARCHAR( 255 ) {LATIN1} NOT NULL ALTER TABLE `$table_vacation` CHANGE `active` `active` TINYINT( 1 ) NOT NULL DEFAULT '1' ALTER TABLE `$table_vacation` DEFAULT {LATIN1} - ALTER TABLE `$table_vacation` ENGINE = INNODB + ALTER TABLE `$table_vacation` {INNODB} ")); foreach ($all_sql as $sql) { @@ -813,7 +817,7 @@ CONSTRAINT `vacation_notification_pkey` FOREIGN KEY (`on_vacation`) REFERENCES $table_vacation(`email`) ON DELETE CASCADE ) - ENGINE=InnoDB TYPE=InnoDB + {INNODB} COMMENT='Postfix Admin - Virtual Vacation Notifications' "); @@ -917,7 +921,7 @@ PRIMARY KEY (`alias_domain`), KEY `active` (`active`), KEY `target_domain` (`target_domain`) - ) TYPE=MyISAM COMMENT='Postfix Admin - Domain Aliases' + ) {MYISAM} COMMENT='Postfix Admin - Domain Aliases' "); } This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <Gin...@us...> - 2009-10-24 19:44:42
|
Revision: 738 http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=738&view=rev Author: GingerDog Date: 2009-10-24 19:44:32 +0000 (Sat, 24 Oct 2009) Log Message: ----------- create the plpgsql language if it is not already there; will error if already enabled, but we will just ignore this - this may cause issues if the db user does not have priviledges to create language ... if this is the case the next query will fail Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2009-10-24 00:13:39 UTC (rev 737) +++ trunk/upgrade.php 2009-10-24 19:44:32 UTC (rev 738) @@ -1118,6 +1118,7 @@ $table_quota = table_by_key('quota'); $table_quota2 = table_by_key('quota2'); + db_query_parsed('CREATE LANGUAGE plpgsql', 1); /* will error if plpgsql is already installed */ # trigger for dovecot v1.0 & 1.1 quota table # taken from http://wiki.dovecot.org/Quota/Dict db_query_parsed(" This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <chr...@us...> - 2009-10-28 00:17:17
|
Revision: 745 http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=745&view=rev Author: christian_boltz Date: 2009-10-28 00:14:22 +0000 (Wed, 28 Oct 2009) Log Message: ----------- upgrade.php - upgrade_344_mysql(): fix MySQL 6.0 compatibility reported by ant77191 on https://sourceforge.net/projects/postfixadmin/forums/forum/676076/topic/3435180 Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2009-10-26 16:20:13 UTC (rev 744) +++ trunk/upgrade.php 2009-10-28 00:14:22 UTC (rev 745) @@ -863,7 +863,7 @@ extra_options text, returned_text text, mda varchar(255) not null default '', - date timestamp(14), + date timestamp, primary key(id) ); "); This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <chr...@us...> - 2009-12-05 00:08:56
|
Revision: 784 http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=784&view=rev Author: christian_boltz Date: 2009-12-05 00:08:47 +0000 (Sat, 05 Dec 2009) Log Message: ----------- upgrade.php: - fix issues from smarty merge: - re-add {BIGINT} replacement for pgsql - convert DOS linebreaks to unix linebreaks Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2009-12-02 13:24:45 UTC (rev 783) +++ trunk/upgrade.php 2009-12-05 00:08:47 UTC (rev 784) @@ -1,1233 +1,1234 @@ -<?php -if(!defined('POSTFIXADMIN')) { - require_once('common.php'); -} - -/* vim: set expandtab softtabstop=4 tabstop=4 shiftwidth=4: */ - -# Note: run with upgrade.php?debug=1 to see all SQL error messages - - -/** - * Use this to check whether an object (Table, index etc) exists within a - * PostgreSQL database. - * @param String the object name - * @return boolean true if it exists - */ -function _pgsql_object_exists($name) { - $sql = "select relname from pg_class where relname = '$name'"; - $r = db_query($sql); - if($r['rows'] == 1) { - return true; - } - return false; -} - -function _pgsql_field_exists($table, $field) { - $sql = ' - SELECT - a.attname, - pg_catalog.format_type(a.atttypid, a.atttypmod) AS "Datatype" - FROM - pg_catalog.pg_attribute a - WHERE - a.attnum > 0 - AND NOT a.attisdropped - AND a.attrelid = ( - SELECT c.oid - FROM pg_catalog.pg_class c - LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace - WHERE c.relname ~ ' . "'^($table)\$' - AND pg_catalog.pg_table_is_visible(c.oid) - ) - AND a.attname = '$field' "; - $r = db_query($sql); - $row = db_row($r['result']); - if($row) { - return true; - } - return false; -} - -function _mysql_field_exists($table, $field) { - $sql = "SHOW COLUMNS FROM $table LIKE '$field'"; - $r = db_query($sql); - $row = db_row($r['result']); - if($row) { - return true; - } - return false; -} - -$table = table_by_key('config'); -if($CONF['database_type'] == 'pgsql') { - // check if table already exists, if so, don't recreate it - $r = db_query("SELECT relname FROM pg_class WHERE relname = '$table'"); - if($r['rows'] == 0) { - $pgsql = " - CREATE TABLE $table ( - id SERIAL, - name VARCHAR(20) NOT NULL UNIQUE, - value VARCHAR(20) NOT NULL, - PRIMARY KEY(id) - )"; - db_query_parsed($pgsql); - } -} -else { - $mysql = " - CREATE TABLE {IF_NOT_EXISTS} $table ( - `id` {AUTOINCREMENT} {PRIMARY}, - `name` VARCHAR(20) {LATIN1} NOT NULL DEFAULT '', - `value` VARCHAR(20) {LATIN1} NOT NULL DEFAULT '', - UNIQUE name ( `name` ) - ) - "; - db_query_parsed($mysql, 0, " ENGINE = MYISAM COMMENT = 'PostfixAdmin settings'"); -} - -$sql = "SELECT * FROM $table WHERE name = 'version'"; - -// insert into config('version', '01'); - -$r = db_query($sql); - -if($r['rows'] == 1) { - $rs = $r['result']; - $row = db_array($rs); - $version = $row['value']; -} else { - db_query_parsed("INSERT INTO $table (name, value) VALUES ('version', '0')", 0, ''); - $version = 0; -} - -_do_upgrade($version); - - -function _do_upgrade($current_version) { - global $CONF; - $target_version = preg_replace('/[^0-9]/', '', '$Revision$'); - - if ($current_version >= $target_version) { - # already up to date - echo "<p>Database is up to date</p>"; - return true; - } - - echo "<p>Updating database:</p><p>- old version: $current_version; target version: $target_version</p>"; - - for ($i = $current_version +1; $i <= $target_version; $i++) { - $function = "upgrade_$i"; - $function_mysql = $function . "_mysql"; - $function_pgsql = $function . "_pgsql"; - if (function_exists($function)) { - echo "<p>updating to version $i (all databases)..."; - $function(); - echo " done"; - } - if ($CONF['database_type'] == 'mysql' || $CONF['database_type'] == 'mysqli' ) { - if (function_exists($function_mysql)) { - echo "<p>updating to version $i (MySQL)..."; - $function_mysql(); - echo " done"; - } - } elseif($CONF['database_type'] == 'pgsql') { - if (function_exists($function_pgsql)) { - echo "<p>updating to version $i (PgSQL)..."; - $function_pgsql(); - echo " done"; - } - } - // Update config table so we don't run the same query twice in the future. - $i = (int) $i; - $table = table_by_key('config'); - $sql = "UPDATE $table SET value = $i WHERE name = 'version'"; - db_query($sql); - }; -} - -/** - * Replaces database specific parts in a query - * @param String sql query with placeholders - * @param int (optional) whether errors should be ignored (0=false) - * @param String (optional) MySQL specific code to attach, useful for COMMENT= on CREATE TABLE - * @return String sql query - */ - -function db_query_parsed($sql, $ignore_errors = 0, $attach_mysql = "") { - global $CONF; - - if ($CONF['database_type'] == 'mysql' || $CONF['database_type'] == 'mysqli' ) { - - $replace = array( - '{AUTOINCREMENT}' => 'int(11) not null auto_increment', - '{PRIMARY}' => 'primary key', - '{UNSIGNED}' => 'unsigned' , - '{FULLTEXT}' => 'FULLTEXT', - '{BOOLEAN}' => 'tinyint(1) NOT NULL', - '{UTF-8}' => '/*!40100 CHARACTER SET utf8 */', - '{LATIN1}' => '/*!40100 CHARACTER SET latin1 */', - '{IF_NOT_EXISTS}' => 'IF NOT EXISTS', - '{RENAME_COLUMN}' => 'CHANGE COLUMN', - '{MYISAM}' => 'ENGINE=MyISAM', - '{INNODB}' => 'ENGINE=InnoDB', - '{BIGINT}' => 'bigint', - ); - $sql = "$sql $attach_mysql"; - - } elseif($CONF['database_type'] == 'pgsql') { - $replace = array( - '{AUTOINCREMENT}' => 'SERIAL', - '{PRIMARY}' => 'primary key', - '{UNSIGNED}' => '', - '{FULLTEXT}' => '', - '{BOOLEAN}' => 'BOOLEAN NOT NULL', - '{UTF-8}' => '', # UTF-8 is simply ignored. - '{LATIN1}' => '', # same for latin1 - '{IF_NOT_EXISTS}' => '', # does not work with PgSQL - '{RENAME_COLUMN}' => 'ALTER COLUMN', # PgSQL : ALTER TABLE x RENAME x TO y - '{MYISAM}' => '', - '{INNODB}' => '', - 'int(1)' => 'int', - 'int(10)' => 'int', - 'int(11)' => 'int', - 'int(4)' => 'int', - ); - - } else { - echo "Sorry, unsupported database type " . $conf['database_type']; - exit; - } - - $replace['{BOOL_TRUE}'] = db_get_boolean(True); - $replace['{BOOL_FALSE}'] = db_get_boolean(False); - - $query = trim(str_replace(array_keys($replace), $replace, $sql)); - if (safeget('debug') != "") { - print "<p style='color:#999'>$query"; - } - $result = db_query($query, $ignore_errors); - if (safeget('debug') != "") { - print "<div style='color:#f00'>" . $result['error'] . "</div>"; - } - return $result; -} - -function _drop_index ($table, $index) { - global $CONF; - $tabe = table_by_key ($table); - - if ($CONF['database_type'] == 'mysql' || $CONF['database_type'] == 'mysqli' ) { - return "ALTER TABLE $table DROP INDEX $index"; - } elseif($CONF['database_type'] == 'pgsql') { - return "DROP INDEX $index"; # Index names are unique with a DB for PostgreSQL - } else { - echo "Sorry, unsupported database type " . $conf['database_type']; - exit; - } -} - -function _add_index($table, $indexname, $fieldlist) { - global $CONF; - $tabe = table_by_key ($table); - - if ($CONF['database_type'] == 'mysql' || $CONF['database_type'] == 'mysqli' ) { - return "ALTER TABLE $table ADD INDEX `$indexname` ( `$fieldlist` )"; - } elseif($CONF['database_type'] == 'pgsql') { - $pgindexname = $table . "_" . $indexname . '_idx'; - return "CREATE INDEX $pgindexname ON $table($fieldlist);"; # Index names are unique with a DB for PostgreSQL - } else { - echo "Sorry, unsupported database type " . $conf['database_type']; - exit; - } - -} - -function upgrade_1_mysql() { - // CREATE MYSQL DATABASE TABLES. - $admin = table_by_key('admin'); - $alias = table_by_key('alias'); - $domain = table_by_key('domain'); - $domain_admins = table_by_key('domain_admins'); - $log = table_by_key('log'); - $mailbox = table_by_key('mailbox'); - $vacation = table_by_key('vacation'); - - $sql = array(); - $sql[] = " - CREATE TABLE {IF_NOT_EXISTS} $admin ( - `username` varchar(255) NOT NULL default '', - `password` varchar(255) NOT NULL default '', - `created` datetime NOT NULL default '0000-00-00 00:00:00', - `modified` datetime NOT NULL default '0000-00-00 00:00:00', - `active` tinyint(1) NOT NULL default '1', - PRIMARY KEY (`username`) - ) {MYISAM} COMMENT='Postfix Admin - Virtual Admins';"; - - $sql[] = " - CREATE TABLE {IF_NOT_EXISTS} $alias ( - `address` varchar(255) NOT NULL default '', - `goto` text NOT NULL, - `domain` varchar(255) NOT NULL default '', - `created` datetime NOT NULL default '0000-00-00 00:00:00', - `modified` datetime NOT NULL default '0000-00-00 00:00:00', - `active` tinyint(1) NOT NULL default '1', - PRIMARY KEY (`address`) - ) {MYISAM} COMMENT='Postfix Admin - Virtual Aliases'; "; - - $sql[] = " - CREATE TABLE {IF_NOT_EXISTS} $domain ( - `domain` varchar(255) NOT NULL default '', - `description` varchar(255) NOT NULL default '', - `aliases` int(10) NOT NULL default '0', - `mailboxes` int(10) NOT NULL default '0', - `maxquota` bigint(20) NOT NULL default '0', - `quota` bigint(20) NOT NULL default '0', - `transport` varchar(255) default NULL, - `backupmx` tinyint(1) NOT NULL default '0', - `created` datetime NOT NULL default '0000-00-00 00:00:00', - `modified` datetime NOT NULL default '0000-00-00 00:00:00', - `active` tinyint(1) NOT NULL default '1', - PRIMARY KEY (`domain`) - ) {MYISAM} COMMENT='Postfix Admin - Virtual Domains'; "; - - $sql[] = " - CREATE TABLE {IF_NOT_EXISTS} $domain_admins ( - `username` varchar(255) NOT NULL default '', - `domain` varchar(255) NOT NULL default '', - `created` datetime NOT NULL default '0000-00-00 00:00:00', - `active` tinyint(1) NOT NULL default '1', - KEY username (`username`) - ) {MYISAM} COMMENT='Postfix Admin - Domain Admins';"; - - $sql[] = " - CREATE TABLE {IF_NOT_EXISTS} $log ( - `timestamp` datetime NOT NULL default '0000-00-00 00:00:00', - `username` varchar(255) NOT NULL default '', - `domain` varchar(255) NOT NULL default '', - `action` varchar(255) NOT NULL default '', - `data` varchar(255) NOT NULL default '', - KEY timestamp (`timestamp`) - ) {MYISAM} COMMENT='Postfix Admin - Log';"; - - $sql[] = " - CREATE TABLE {IF_NOT_EXISTS} $mailbox ( - `username` varchar(255) NOT NULL default '', - `password` varchar(255) NOT NULL default '', - `name` varchar(255) NOT NULL default '', - `maildir` varchar(255) NOT NULL default '', - `quota` bigint(20) NOT NULL default '0', - `domain` varchar(255) NOT NULL default '', - `created` datetime NOT NULL default '0000-00-00 00:00:00', - `modified` datetime NOT NULL default '0000-00-00 00:00:00', - `active` tinyint(1) NOT NULL default '1', - PRIMARY KEY (`username`) - ) {MYISAM} COMMENT='Postfix Admin - Virtual Mailboxes';"; - - $sql[] = " - CREATE TABLE {IF_NOT_EXISTS} $vacation ( - email varchar(255) NOT NULL , - subject varchar(255) NOT NULL, - body text NOT NULL, - cache text NOT NULL, - domain varchar(255) NOT NULL , - created datetime NOT NULL default '0000-00-00 00:00:00', - active tinyint(4) NOT NULL default '1', - PRIMARY KEY (email), - KEY email (email) - ) {INNODB} DEFAULT CHARSET=utf8 COMMENT='Postfix Admin - Virtual Vacation' ;"; - - foreach($sql as $query) { - db_query_parsed($query); - } -} - -function upgrade_2_mysql() { - # upgrade pre-2.1 database - # from TABLE_BACKUP_MX.TXT - $table_domain = table_by_key ('domain'); - if(!_mysql_field_exists($table_domain, 'transport')) { - $result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN transport VARCHAR(255) AFTER maxquota;", TRUE); - } - if(!_mysql_field_exists($table_domain, 'backupmx')) { - $result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN backupmx {BOOLEAN} DEFAULT {BOOL_FALSE} AFTER transport;", TRUE); - } -} - -function upgrade_2_pgsql() { - - if(!_pgsql_object_exists(table_by_key('domain'))) { - db_query_parsed(" - CREATE TABLE " . table_by_key('domain') . " ( - domain character varying(255) NOT NULL, - description character varying(255) NOT NULL default '', - aliases integer NOT NULL default 0, - mailboxes integer NOT NULL default 0, - maxquota integer NOT NULL default 0, - quota integer NOT NULL default 0, - transport character varying(255) default NULL, - backupmx boolean NOT NULL default false, - created timestamp with time zone default now(), - modified timestamp with time zone default now(), - active boolean NOT NULL default true, - Constraint \"domain_key\" Primary Key (\"domain\") - ); - CREATE INDEX domain_domain_active ON " . table_by_key('domain') . "(domain,active); - COMMENT ON TABLE " . table_by_key('domain') . " IS 'Postfix Admin - Virtual Domains'; - "); - } - if(!_pgsql_object_exists(table_by_key('admin'))) { - db_query_parsed(' - CREATE TABLE ' . table_by_key("admin") . ' ( - "username" character varying(255) NOT NULL, - "password" character varying(255) NOT NULL default \'\', - "created" timestamp with time zone default now(), - "modified" timestamp with time zone default now(), - "active" boolean NOT NULL default true, - Constraint "admin_key" Primary Key ("username") - );' . " - COMMENT ON TABLE " . table_by_key('admin') . " IS 'Postfix Admin - Virtual Admins'; - "); - } - - if(!_pgsql_object_exists(table_by_key('alias'))) { - db_query_parsed(' - CREATE TABLE ' . table_by_key("alias") . ' ( - address character varying(255) NOT NULL, - goto text NOT NULL, - domain character varying(255) NOT NULL REFERENCES "' . table_by_key("domain") . '", - created timestamp with time zone default now(), - modified timestamp with time zone default now(), - active boolean NOT NULL default true, - Constraint "alias_key" Primary Key ("address") - ); - CREATE INDEX alias_address_active ON ' . table_by_key("alias") . '(address,active); - COMMENT ON TABLE ' . table_by_key("alias") . ' IS \'Postfix Admin - Virtual Aliases\'; - '); - } - - if(!_pgsql_object_exists(table_by_key('domain_admins'))) { - db_query_parsed(' - CREATE TABLE ' . table_by_key('domain_admins') . ' ( - username character varying(255) NOT NULL, - domain character varying(255) NOT NULL REFERENCES "' . table_by_key('domain') . '", - created timestamp with time zone default now(), - active boolean NOT NULL default true - ); - COMMENT ON TABLE ' . table_by_key('domain_admins') . ' IS \'Postfix Admin - Domain Admins\'; - '); - } - - if(!_pgsql_object_exists(table_by_key('log'))) { - db_query_parsed(' - CREATE TABLE ' . table_by_key('log') . ' ( - timestamp timestamp with time zone default now(), - username character varying(255) NOT NULL default \'\', - domain character varying(255) NOT NULL default \'\', - action character varying(255) NOT NULL default \'\', - data text NOT NULL default \'\' - ); - COMMENT ON TABLE ' . table_by_key('log') . ' IS \'Postfix Admin - Log\'; - '); - } - if(!_pgsql_object_exists(table_by_key('mailbox'))) { - db_query_parsed(' - CREATE TABLE ' . table_by_key('mailbox') . ' ( - username character varying(255) NOT NULL, - password character varying(255) NOT NULL default \'\', - name character varying(255) NOT NULL default \'\', - maildir character varying(255) NOT NULL default \'\', - quota integer NOT NULL default 0, - domain character varying(255) NOT NULL REFERENCES "' . table_by_key('domain') . '", - created timestamp with time zone default now(), - modified timestamp with time zone default now(), - active boolean NOT NULL default true, - Constraint "mailbox_key" Primary Key ("username") - ); - CREATE INDEX mailbox_username_active ON ' . table_by_key('mailbox') . '(username,active); - COMMENT ON TABLE ' . table_by_key('mailbox') . ' IS \'Postfix Admin - Virtual Mailboxes\'; - '); - } - - if(!_pgsql_object_exists(table_by_key('vacation'))) { - db_query_parsed(' - CREATE TABLE ' . table_by_key('vacation') . ' ( - email character varying(255) PRIMARY KEY, - subject character varying(255) NOT NULL, - body text NOT NULL , - cache text NOT NULL , - "domain" character varying(255) NOT NULL REFERENCES "' . table_by_key('domain') . '", - created timestamp with time zone DEFAULT now(), - active boolean DEFAULT true NOT NULL - ); - CREATE INDEX vacation_email_active ON ' . table_by_key('vacation') . '(email,active);'); - } - - if(!_pgsql_object_exists(table_by_key('vacation_notification'))) { - db_query_parsed(' - CREATE TABLE ' . table_by_key('vacation_notification') . ' ( - on_vacation character varying(255) NOT NULL REFERENCES ' . table_by_key('vacation') . '(email) ON DELETE CASCADE, - notified character varying(255) NOT NULL, - notified_at timestamp with time zone NOT NULL DEFAULT now(), - CONSTRAINT vacation_notification_pkey primary key(on_vacation,notified) - ); - '); - } - - // this handles anyone who is upgrading... (and should have no impact on new installees) - $table_domain = table_by_key ('domain'); - $result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN transport VARCHAR(255)", TRUE); - $result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN backupmx BOOLEAN DEFAULT false", TRUE); -} - -function upgrade_3_mysql() { - # upgrade pre-2.1 database - # from TABLE_CHANGES.TXT - $table_admin = table_by_key ('admin'); - $table_alias = table_by_key ('alias'); - $table_domain = table_by_key ('domain'); - $table_mailbox = table_by_key ('mailbox'); - $table_vacation = table_by_key ('vacation'); - - if(!_mysql_field_exists($table_admin, 'created')) { - db_query_parsed("ALTER TABLE $table_admin {RENAME_COLUMN} create_date created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;"); - } - if(!_mysql_field_exists($table_admin, 'modified')) { - db_query_parsed("ALTER TABLE $table_admin {RENAME_COLUMN} change_date modified DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;"); - } - if(!_mysql_field_exists($table_alias, 'created')) { - db_query_parsed("ALTER TABLE $table_alias {RENAME_COLUMN} create_date created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;"); - } - if(!_mysql_field_exists($table_alias, 'modified')) { - db_query_parsed("ALTER TABLE $table_alias {RENAME_COLUMN} change_date modified DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;"); - } - if(!_mysql_field_exists($table_domain, 'created')) { - db_query_parsed("ALTER TABLE $table_domain {RENAME_COLUMN} create_date created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;"); - } - if(!_mysql_field_exists($table_domain, 'modified')) { - db_query_parsed("ALTER TABLE $table_domain {RENAME_COLUMN} change_date modified DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;"); - } - if(!_mysql_field_exists($table_domain, 'aliases')) { - db_query_parsed("ALTER TABLE $table_domain ADD COLUMN aliases INT(10) DEFAULT '-1' NOT NULL AFTER description;"); - } - if(!_mysql_field_exists($table_domain, 'mailboxes')) { - db_query_parsed("ALTER TABLE $table_domain ADD COLUMN mailboxes INT(10) DEFAULT '-1' NOT NULL AFTER aliases;"); - } - if(!_mysql_field_exists($table_domain, 'maxquota')) { - db_query_parsed("ALTER TABLE $table_domain ADD COLUMN maxquota INT(10) DEFAULT '-1' NOT NULL AFTER mailboxes;"); - } - if(!_mysql_field_exists($table_domain, 'transport')) { - db_query_parsed("ALTER TABLE $table_domain ADD COLUMN transport VARCHAR(255) AFTER maxquota;"); - } - if(!_mysql_field_exists($table_domain, 'backupmx')) { - db_query_parsed("ALTER TABLE $table_domain ADD COLUMN backupmx TINYINT(1) DEFAULT '0' NOT NULL AFTER transport;"); - } - if(!_mysql_field_exists($table_mailbox, 'created')) { - db_query_parsed("ALTER TABLE $table_mailbox {RENAME_COLUMN} create_date created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;"); - } - if(!_mysql_field_exists($table_mailbox, 'modified')) { - db_query_parsed("ALTER TABLE $table_mailbox {RENAME_COLUMN} change_date modified DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;"); - } - if(!_mysql_field_exists($table_mailbox, 'quota')) { - db_query_parsed("ALTER TABLE $table_mailbox ADD COLUMN quota INT(10) DEFAULT '-1' NOT NULL AFTER maildir;"); - } - if(!_mysql_field_exists($table_vacation, 'domain')) { - db_query_parsed("ALTER TABLE $table_vacation ADD COLUMN domain VARCHAR(255) DEFAULT '' NOT NULL AFTER cache;"); - } - if(!_mysql_field_exists($table_vacation, 'created')) { - db_query_parsed("ALTER TABLE $table_vacation ADD COLUMN created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL AFTER domain;"); - } - if(!_mysql_field_exists($table_vacation, 'active')) { - db_query_parsed("ALTER TABLE $table_vacation ADD COLUMN active TINYINT(1) DEFAULT '1' NOT NULL AFTER created;"); - } - db_query_parsed("ALTER TABLE $table_vacation DROP PRIMARY KEY"); - db_query_parsed("ALTER TABLE $table_vacation ADD PRIMARY KEY(email)"); - db_query_parsed("UPDATE $table_vacation SET domain=SUBSTRING_INDEX(email, '@', -1) WHERE email=email;"); -} - -function upgrade_4_mysql() { # MySQL only - # changes between 2.1 and moving to sourceforge - $table_domain = table_by_key ('domain'); - $result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN quota int(10) NOT NULL default '0' AFTER maxquota", TRUE); - # Possible errors that can be ignored: - # - Invalid query: Table 'postfix.domain' doesn't exist -} - -/** - * Changes between 2.1 and moving to sf.net - */ -function upgrade_4_pgsql() { - $table_domain = table_by_key('domain'); - $table_admin = table_by_key('admin'); - $table_alias = table_by_key('alias'); - $table_domain_admins = table_by_key('domain_admins'); - $table_log = table_by_key('log'); - $table_mailbox = table_by_key('mailbox'); - $table_vacation = table_by_key('vacation'); - $table_vacation_notification = table_by_key('vacation_notification'); - - if(!_pgsql_field_exists($table_domain, 'quota')) { - $result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN quota int NOT NULL default '0'"); - } - - $result = db_query_parsed("ALTER TABLE $table_domain ALTER COLUMN domain DROP DEFAULT"); - if(!_pgsql_object_exists('domain_domain_active')) { - $result = db_query_parsed("CREATE INDEX domain_domain_active ON $table_domain(domain,active)"); - } - - $result = db_query_parsed("ALTER TABLE $table_domain_admins ALTER COLUMN domain DROP DEFAULT"); - $result = db_query_parsed("ALTER TABLE $table_alias ALTER COLUMN address DROP DEFAULT"); - $result = db_query_parsed("ALTER TABLE $table_alias ALTER COLUMN domain DROP DEFAULT"); - if(!_pgsql_object_exists('alias_address_active')) { - $result = db_query_parsed("CREATE INDEX alias_address_active ON $table_alias(address,active)"); - } - - $result = db_query_parsed("ALTER TABLE $table_domain_admins ALTER COLUMN username DROP DEFAULT"); - $result = db_query_parsed("ALTER TABLE $table_domain_admins ALTER COLUMN domain DROP DEFAULT"); - - $result = db_query_parsed(" - BEGIN; - ALTER TABLE $table_log RENAME COLUMN data TO data_old; - ALTER TABLE $table_log ADD COLUMN data text NOT NULL default ''; - UPDATE $table_log SET data = CAST(data_old AS text); - ALTER TABLE $table_log DROP COLUMN data_old; - COMMIT;"); - - $result = db_query_parsed("ALTER TABLE $table_mailbox ALTER COLUMN username DROP DEFAULT"); - $result = db_query_parsed("ALTER TABLE $table_mailbox ALTER COLUMN domain DROP DEFAULT"); - - $result = db_query_parsed(" - BEGIN; - ALTER TABLE $table_mailbox RENAME COLUMN domain TO domain_old; - ALTER TABLE $table_mailbox ADD COLUMN domain varchar(255) REFERENCES $table_domain (domain); - UPDATE $table_mailbox SET domain = domain_old; - ALTER TABLE $table_mailbox DROP COLUMN domain_old; - COMMIT;" - ); - if(!_pgsql_object_exists('mailbox_username_active')) { - db_query_parsed("CREATE INDEX mailbox_username_active ON $table_mailbox(username,active)"); - } - - - $result = db_query_parsed("ALTER TABLE $table_vacation ALTER COLUMN body SET DEFAULT ''"); - if(_pgsql_field_exists($table_vacation, 'cache')) { - $result = db_query_parsed("ALTER TABLE $table_vacation DROP COLUMN cache"); - } - - $result = db_query_parsed(" - BEGIN; - ALTER TABLE $table_vacation RENAME COLUMN domain to domain_old; - ALTER TABLE $table_vacation ADD COLUMN domain varchar(255) REFERENCES $table_domain; - UPDATE $table_vacation SET domain = domain_old; - ALTER TABLE $table_vacation DROP COLUMN domain_old; - COMMIT; - "); - - if(!_pgsql_object_exists('vacation_email_active')) { - $result = db_query_parsed("CREATE INDEX vacation_email_active ON $table_vacation(email,active)"); - } - - if(!_pgsql_object_exists($table_vacation_notification)) { - $result = db_query_parsed(" - CREATE TABLE $table_vacation_notification ( - on_vacation character varying(255) NOT NULL REFERENCES $table_vacation(email) ON DELETE CASCADE, - notified character varying(255) NOT NULL, - notified_at timestamp with time zone NOT NULL DEFAULT now(), - CONSTRAINT vacation_notification_pkey primary key(on_vacation,notified));"); - } -} - - -# Possible errors that can be ignored: -# -# NO MySQL errors should be ignored below this line! - - - -/** - * create tables - * version: Sourceforge SVN r1 of DATABASE_MYSQL.txt - * changes compared to DATABASE_MYSQL.txt: - * - removed MySQL user and database creation - * - removed creation of default superadmin - */ -function upgrade_5_mysql() { - - $result = db_query_parsed(" - CREATE TABLE {IF_NOT_EXISTS} `" . table_by_key('admin') . "` ( - `username` varchar(255) NOT NULL default '', - `password` varchar(255) NOT NULL default '', - `created` datetime NOT NULL default '0000-00-00 00:00:00', - `modified` datetime NOT NULL default '0000-00-00 00:00:00', - `active` tinyint(1) NOT NULL default '1', - PRIMARY KEY (`username`), - KEY username (`username`) -) {MYISAM} DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Admins'; "); - - $result = db_query_parsed(" - CREATE TABLE {IF_NOT_EXISTS} `" . table_by_key('alias') . "` ( - `address` varchar(255) NOT NULL default '', - `goto` text NOT NULL, - `domain` varchar(255) NOT NULL default '', - `created` datetime NOT NULL default '0000-00-00 00:00:00', - `modified` datetime NOT NULL default '0000-00-00 00:00:00', - `active` tinyint(1) NOT NULL default '1', - PRIMARY KEY (`address`), - KEY address (`address`) - ) {MYISAM} DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Aliases'; - "); - - $result = db_query_parsed(" - CREATE TABLE {IF_NOT_EXISTS} `" . table_by_key('domain') . "` ( - `domain` varchar(255) NOT NULL default '', - `description` varchar(255) NOT NULL default '', - `aliases` int(10) NOT NULL default '0', - `mailboxes` int(10) NOT NULL default '0', - `maxquota` int(10) NOT NULL default '0', - `quota` int(10) NOT NULL default '0', - `transport` varchar(255) default NULL, - `backupmx` tinyint(1) NOT NULL default '0', - `created` datetime NOT NULL default '0000-00-00 00:00:00', - `modified` datetime NOT NULL default '0000-00-00 00:00:00', - `active` tinyint(1) NOT NULL default '1', - PRIMARY KEY (`domain`), - KEY domain (`domain`) - ) {MYISAM} DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Domains'; - "); - - $result = db_query_parsed(" - CREATE TABLE {IF_NOT_EXISTS} `" . table_by_key('domain_admins') . "` ( - `username` varchar(255) NOT NULL default '', - `domain` varchar(255) NOT NULL default '', - `created` datetime NOT NULL default '0000-00-00 00:00:00', - `active` tinyint(1) NOT NULL default '1', - KEY username (`username`) - ) {MYISAM} DEFAULT {LATIN1} COMMENT='Postfix Admin - Domain Admins'; - "); - - $result = db_query_parsed(" - CREATE TABLE {IF_NOT_EXISTS} `" . table_by_key('log') . "` ( - `timestamp` datetime NOT NULL default '0000-00-00 00:00:00', - `username` varchar(255) NOT NULL default '', - `domain` varchar(255) NOT NULL default '', - `action` varchar(255) NOT NULL default '', - `data` varchar(255) NOT NULL default '', - KEY timestamp (`timestamp`) - ) {MYISAM} DEFAULT {LATIN1} COMMENT='Postfix Admin - Log'; - "); - - $result = db_query_parsed(" - CREATE TABLE {IF_NOT_EXISTS} `" . table_by_key('mailbox') . "` ( - `username` varchar(255) NOT NULL default '', - `password` varchar(255) NOT NULL default '', - `name` varchar(255) NOT NULL default '', - `maildir` varchar(255) NOT NULL default '', - `quota` int(10) NOT NULL default '0', - `domain` varchar(255) NOT NULL default '', - `created` datetime NOT NULL default '0000-00-00 00:00:00', - `modified` datetime NOT NULL default '0000-00-00 00:00:00', - `active` tinyint(1) NOT NULL default '1', - PRIMARY KEY (`username`), - KEY username (`username`) - ) {MYISAM} DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Mailboxes'; - "); - - $result = db_query_parsed(" - CREATE TABLE {IF_NOT_EXISTS} `" . table_by_key('vacation') . "` ( - `email` varchar(255) NOT NULL , - `subject` varchar(255) NOT NULL, - `body` text NOT NULL, - `cache` text NOT NULL, - `domain` varchar(255) NOT NULL, - `created` datetime NOT NULL default '0000-00-00 00:00:00', - `active` tinyint(1) NOT NULL default '1', - PRIMARY KEY (`email`), - KEY email (`email`) - ) {MYISAM} DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Vacation'; - "); -} - -/** - * drop useless indicies (already available as primary key) - */ -function upgrade_79_mysql() { # MySQL only - $result = db_query_parsed(_drop_index('admin', 'username'), True); - $result = db_query_parsed(_drop_index('alias', 'address'), True); - $result = db_query_parsed(_drop_index('domain', 'domain'), True); - $result = db_query_parsed(_drop_index('mailbox', 'username'), True); -} - -function upgrade_81_mysql() { # MySQL only - $table_vacation = table_by_key ('vacation'); - $table_vacation_notification = table_by_key('vacation_notification'); - - $all_sql = split("\n", trim(" - ALTER TABLE `$table_vacation` CHANGE `email` `email` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_vacation` CHANGE `subject` `subject` VARCHAR( 255 ) {UTF-8} NOT NULL - ALTER TABLE `$table_vacation` CHANGE `body` `body` TEXT {UTF-8} NOT NULL - ALTER TABLE `$table_vacation` CHANGE `cache` `cache` TEXT {LATIN1} NOT NULL - ALTER TABLE `$table_vacation` CHANGE `domain` `domain` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_vacation` CHANGE `active` `active` TINYINT( 1 ) NOT NULL DEFAULT '1' - ALTER TABLE `$table_vacation` DEFAULT {LATIN1} - ALTER TABLE `$table_vacation` {INNODB} - ")); - - foreach ($all_sql as $sql) { - $result = db_query_parsed($sql, TRUE); - } - -} - -/** - * Make logging translatable - i.e. create alias => create_alias - */ -function upgrade_90() { - $result = db_query_parsed("UPDATE " . table_by_key ('log') . " SET action = REPLACE(action,' ','_')", TRUE); - # change edit_alias_state to edit_alias_active - $result = db_query_parsed("UPDATE " . table_by_key ('log') . " SET action = 'edit_alias_state' WHERE action = 'edit_alias_active'", TRUE); -} - -/** - * MySQL only allow quota > 2 GB - */ -function upgrade_169_mysql() { - - $table_domain = table_by_key ('domain'); - $table_mailbox = table_by_key ('mailbox'); - $result = db_query_parsed("ALTER TABLE $table_domain MODIFY COLUMN `quota` bigint(20) NOT NULL default '0'", TRUE); - $result = db_query_parsed("ALTER TABLE $table_domain MODIFY COLUMN `maxquota` bigint(20) NOT NULL default '0'", TRUE); - $result = db_query_parsed("ALTER TABLE $table_mailbox MODIFY COLUMN `quota` bigint(20) NOT NULL default '0'", TRUE); -} - - -/** - * Create / modify vacation_notification table. - * Note: This might not work if users used workarounds to create the table before. - * In this case, dropping the table is the easiest solution. - */ -function upgrade_318_mysql() { - $table_vacation_notification = table_by_key('vacation_notification'); - $table_vacation = table_by_key('vacation'); - - db_query_parsed( " - CREATE TABLE {IF_NOT_EXISTS} $table_vacation_notification ( - on_vacation varchar(255) {LATIN1} NOT NULL, - notified varchar(255) NOT NULL, - notified_at timestamp NOT NULL default CURRENT_TIMESTAMP, - PRIMARY KEY on_vacation (`on_vacation`, `notified`), - CONSTRAINT `vacation_notification_pkey` - FOREIGN KEY (`on_vacation`) REFERENCES $table_vacation(`email`) ON DELETE CASCADE - ) - {INNODB} - COMMENT='Postfix Admin - Virtual Vacation Notifications' - "); - - # in case someone has manually created the table with utf8 fields before: - $all_sql = split("\n", trim(" - ALTER TABLE `$table_vacation_notification` CHANGE `notified` `notified` VARCHAR( 255 ) NOT NULL - ALTER TABLE `$table_vacation_notification` DEFAULT CHARACTER SET utf8 - ")); - # Possible errors that can be ignored: - # None. - # If something goes wrong, the user should drop the vacation_notification table - # (not a great loss) and re-create it using this function. - - foreach ($all_sql as $sql) { - $result = db_query_parsed($sql); - } - -} - - -/** - * Create fetchmail table - */ -function upgrade_344_mysql() { - - $table_fetchmail = table_by_key('fetchmail'); - - db_query_parsed( " - CREATE TABLE IF NOT EXISTS $table_fetchmail( - id int(11) unsigned not null auto_increment, - mailbox varchar(255) not null default '', - src_server varchar(255) not null default '', - src_auth enum('password','kerberos_v5','kerberos','kerberos_v4','gssapi','cram-md5','otp','ntlm','msn','ssh','any'), - src_user varchar(255) not null default '', - src_password varchar(255) not null default '', - src_folder varchar(255) not null default '', - poll_time int(11) unsigned not null default 10, - fetchall tinyint(1) unsigned not null default 0, - keep tinyint(1) unsigned not null default 0, - protocol enum('POP3','IMAP','POP2','ETRN','AUTO'), - extra_options text, - returned_text text, - mda varchar(255) not null default '', - date timestamp, - primary key(id) - ); - "); -} - -function upgrade_344_pgsql() { - $fetchmail = table_by_key('fetchmail'); - // a field name called 'date' is probably a bad idea. - if(!_pgsql_object_exists('fetchmail')) { - db_query_parsed( " - create table $fetchmail( - id serial, - mailbox varchar(255) not null default '', - src_server varchar(255) not null default '', - src_auth varchar(15) NOT NULL, - src_user varchar(255) not null default '', - src_password varchar(255) not null default '', - src_folder varchar(255) not null default '', - poll_time integer not null default 10, - fetchall boolean not null default false, - keep boolean not null default false, - protocol varchar(15) NOT NULL, - extra_options text, - returned_text text, - mda varchar(255) not null default '', - date timestamp with time zone default now(), - primary key(id), - CHECK (src_auth IN ('password','kerberos_v5','kerberos','kerberos_v4','gssapi','cram-md5','otp','ntlm','msn','ssh','any')), - CHECK (protocol IN ('POP3', 'IMAP', 'POP2', 'ETRN', 'AUTO')) - ); - "); - } - // MySQL expects sequences to start at 1. Stupid database. - // fetchmail.php requires id parameters to be > 0, as it does if($id) like logic... hence if we don't - // fudge the sequence starting point, you cannot delete/edit the first entry if using PostgreSQL. - // I'm sure there's a more elegant way of fixing it properly.... but this should work for now. - if(_pgsql_object_exists('fetchmail_id_seq')) { - db_query_parsed("SELECT nextval('{$fetchmail}_id_seq')"); // I don't care about number waste. - } -} - -/** - * Create alias_domain table - MySQL - */ -# function upgrade_362_mysql() { # renamed to _438 to make sure it runs after an upgrade from 2.2.x -function upgrade_438_mysql() { - # Table structure for table alias_domain - # - $table_alias_domain = table_by_key('alias_domain'); - db_query_parsed(" - CREATE TABLE IF NOT EXISTS $table_alias_domain ( - `alias_domain` varchar(255) NOT NULL default '', - `target_domain` varchar(255) NOT NULL default '', - `created` datetime NOT NULL default '0000-00-00 00:00:00', - `modified` datetime NOT NULL default '0000-00-00 00:00:00', - `active` tinyint(1) NOT NULL default '1', - PRIMARY KEY (`alias_domain`), - KEY `active` (`active`), - KEY `target_domain` (`target_domain`) - ) {MYISAM} COMMENT='Postfix Admin - Domain Aliases' - "); -} - -/** - * Create alias_domain table - PgSQL - */ -# function upgrade_362_pgsql() { # renamed to _438 to make sure it runs after an upgrade from 2.2.x -function upgrade_438_pgsql() { - # Table structure for table alias_domain - $table_alias_domain = table_by_key('alias_domain'); - $table_domain = table_by_key('domain'); - if(_pgsql_object_exists($table_alias_domain)) { - return; - } - db_query_parsed(" - CREATE TABLE $table_alias_domain ( - alias_domain character varying(255) NOT NULL REFERENCES $table_domain(domain) ON DELETE CASCADE, - target_domain character varying(255) NOT NULL REFERENCES $table_domain(domain) ON DELETE CASCADE, - created timestamp with time zone default now(), - modified timestamp with time zone default now(), - active boolean NOT NULL default true, - PRIMARY KEY(alias_domain))"); - db_query_parsed("CREATE INDEX alias_domain_active ON $table_alias_domain(alias_domain,active)"); - db_query_parsed("COMMENT ON TABLE $table_alias_domain IS 'Postfix Admin - Domain Aliases'"); -} - -/** - * Change description fields to UTF-8 - */ -function upgrade_373_mysql() { # MySQL only - $table_domain = table_by_key ('domain'); - $table_mailbox = table_by_key('mailbox'); - - $all_sql = split("\n", trim(" - ALTER TABLE `$table_domain` CHANGE `description` `description` VARCHAR( 255 ) {UTF-8} NOT NULL - ALTER TABLE `$table_mailbox` CHANGE `name` `name` VARCHAR( 255 ) {UTF-8} NOT NULL - ")); - - foreach ($all_sql as $sql) { - $result = db_query_parsed($sql); - } -} - - -/** - * add ssl option for fetchmail - */ -function upgrade_439_mysql() { - $table_fetchmail = table_by_key('fetchmail'); - if(!_mysql_field_exists($table_fetchmail, 'ssl')) { - db_query_parsed("ALTER TABLE `$table_fetchmail` ADD `ssl` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0' AFTER `protocol` ; "); - } -} -function upgrade_439_pgsql() { - $table_fetchmail = table_by_key('fetchmail'); - if(!_pgsql_field_exists($table_fetchmail, 'ssl')) { - db_query_parsed("ALTER TABLE $table_fetchmail ADD COLUMN ssl BOOLEAN NOT NULL DEFAULT false"); - } -} - -function upgrade_473_mysql() { - $table_admin = table_by_key('admin'); - $table_alias = table_by_key('alias'); - $table_al_dom = table_by_key('alias_domain'); - $table_domain = table_by_key('domain'); - $table_dom_adm = table_by_key('domain_admins'); - $table_fmail = table_by_key('fetchmail'); - $table_mailbox = table_by_key('mailbox'); - $table_log = table_by_key('log'); - - # tables were created without explicit charset before :-( - $all_sql = split("\n", trim(" - ALTER TABLE `$table_admin` CHANGE `username` `username` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_admin` CHANGE `password` `password` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_admin` DEFAULT {LATIN1} - ALTER TABLE `$table_alias` CHANGE `address` `address` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_alias` CHANGE `goto` `goto` TEXT {LATIN1} NOT NULL - ALTER TABLE `$table_alias` CHANGE `domain` `domain` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_alias` DEFAULT {LATIN1} - ALTER TABLE `$table_al_dom` CHANGE `alias_domain` `alias_domain` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_al_dom` CHANGE `target_domain` `target_domain` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_al_dom` DEFAULT {LATIN1} - ALTER TABLE `$table_domain` CHANGE `domain` `domain` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_domain` CHANGE `transport` `transport` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_domain` DEFAULT {LATIN1} - ALTER TABLE `$table_dom_adm` CHANGE `username` `username` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_dom_adm` CHANGE `domain` `domain` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_dom_adm` DEFAULT {LATIN1} - ALTER TABLE `$table_log` CHANGE `username` `username` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_log` CHANGE `domain` `domain` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_log` CHANGE `action` `action` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_log` CHANGE `data` `data` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_log` DEFAULT {LATIN1} - ALTER TABLE `$table_mailbox` CHANGE `username` `username` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_mailbox` CHANGE `password` `password` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_mailbox` CHANGE `maildir` `maildir` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_mailbox` CHANGE `domain` `domain` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_mailbox` DEFAULT {LATIN1} - ALTER TABLE `$table_fmail` CHANGE `mailbox` `mailbox` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_fmail` CHANGE `src_server` `src_server` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_fmail` CHANGE `src_user` `src_user` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_fmail` CHANGE `src_password` `src_password` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_fmail` CHANGE `src_folder` `src_folder` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_fmail` CHANGE `mda` `mda` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_fmail` CHANGE `mailbox` `mailbox` VARCHAR( 255 ) {LATIN1} NOT NULL - ALTER TABLE `$table_fmail` CHANGE `extra_options` `extra_options` TEXT {LATIN1} NULL DEFAULT NULL - ALTER TABLE `$table_fmail` CHANGE `returned_text` `returned_text` TEXT {LATIN1} NULL DEFAULT NULL - ALTER TABLE `$table_fmail` DEFAULT {LATIN1} - ")); - - foreach ($all_sql as $sql) { - $result = db_query_parsed($sql); - } -} - -function upgrade_479_mysql () { - # ssl is a reserved word in MySQL and causes several problems. Renaming the field... - $table_fmail = table_by_key('fetchmail'); - if(!_mysql_field_exists($table_fmail, 'usessl')) { - db_query_parsed("ALTER TABLE `$table_fmail` CHANGE `ssl` `usessl` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0'"); - } -} -function upgrade_479_pgsql () { - $table_fmail = table_by_key('fetchmail'); - if(!_pgsql_field_exists($table_fmail, 'usessl')) { - db_query_parsed("alter table $table_fmail rename column ssl to usessl"); - } -} - -function upgrade_483_mysql () { - $table_log = table_by_key('log'); - db_query_parsed("ALTER TABLE $table_log CHANGE `data` `data` TEXT {LATIN1} NOT NULL"); -} - -# Add a local_part field to the mailbox table, and populate it with the local part of the user's address. -# This is to make it easier (hopefully) to change the filesystem location of a mailbox in the future -# See https://sourceforge.net/forum/message.php?msg_id=5394663 -function upgrade_495_pgsql() { - $table_mailbox = table_by_key('mailbox'); - if(!_pgsql_field_exists($table_mailbox, 'local_part')) { - db_query_parsed("ALTER TABLE $table_mailbox add column local_part varchar(255) "); - db_query_parsed("UPDATE $table_mailbox SET local_part = substring(username from '^(.*)@')"); - db_query_parsed("ALTER TABLE $table_mailbox alter column local_part SET NOT NULL"); - } -} -# See https://sourceforge.net/forum/message.php?msg_id=5394663 -function upgrade_495_mysql() { - $table_mailbox = table_by_key('mailbox'); - if(!_mysql_field_exists($table_mailbox, 'local_part')) { - db_query_parsed("ALTER TABLE $table_mailbox add local_part varchar(255) AFTER quota"); // allow to be null - db_query_parsed("UPDATE $table_mailbox SET local_part = substring_index(username, '@', 1)"); - db_query_parsed("ALTER TABLE $table_mailbox change local_part local_part varchar(255) NOT NULL"); // remove null-ness... - } -} - -function upgrade_504_mysql() { - $table_mailbox = table_by_key('mailbox'); - db_query_parsed("ALTER TABLE `$table_mailbox` CHANGE `local_part` `local_part` VARCHAR( 255 ) {LATIN1} NOT NULL"); -} - -function upgrade_655() { - db_query_parsed(_add_index('mailbox', 'domain', 'domain')); - db_query_parsed(_add_index('alias', 'domain', 'domain')); -} - -function upgrade_727_mysql() { - $table_vacation = table_by_key('vacation'); - if(!_mysql_field_exists($table_vacation, 'activefrom')) { - db_query_parsed("ALTER TABLE $table_vacation add activefrom datetime default NULL"); - } - if(!_mysql_field_exists($table_vacation, 'activeuntil')) { - db_query_parsed("ALTER TABLE $table_vacation add activeuntil datetime default NULL"); - } - - $table_client_access = table_by_key('client_access'); - db_query_parsed(" - CREATE TABLE IF NOT EXISTS $table_client_access ( - `client` char(50) NOT NULL, - `action` char(50) NOT NULL default 'REJECT', - UNIQUE KEY `client` (`client`) - ) {MYISAM} COMMENT='Postfix Admin - Client Access' - "); - $table_from_access = table_by_key('from_access'); - db_query_parsed(" - CREATE TABLE IF NOT EXISTS $table_from_access ( - `from_access` char(50) NOT NULL, - `action` char(50) NOT NULL default 'REJECT', - UNIQUE KEY `from_access` (`from_access`) - ) {MYISAM} COMMENT='Postfix Admin - From Access' - "); - $table_helo_access = table_by_key('helo_access'); - db_query_parsed(" - CREATE TABLE IF NOT EXISTS $table_helo_access ( - `helo` char(50) NOT NULL, - `action` char(50) NOT NULL default 'REJECT', - UNIQUE KEY `helo` (`helo`) - ) {MYISAM} COMMENT='Postfix Admin - Helo Access' - "); - $table_rcpt_access = table_by_key('rcpt_access'); - db_query_parsed(" - CREATE TABLE IF NOT EXISTS $table_rcpt_access ( - `rcpt` char(50) NOT NULL, - `action` char(50) NOT NULL default 'REJECT', - UNIQUE KEY `rcpt` (`rcpt`) - ) {MYISAM} COMMENT='Postfix Admin - Recipient Access' - "); - $table_user_whitelist = table_by_key('user_whitelist'); - db_query_parsed(" - CREATE TABLE IF NOT EXISTS $table_user_whitelist ( - `recipient` char(50) NOT NULL, - `action` char(50) NOT NULL default 'REJECT', - UNIQUE KEY `recipient` (`recipient`) - ) {MYISAM} COMMENT='Postfix Admin - User whitelist' - "); -} - -function upgrade_729() { - $table_quota = table_by_key('quota'); - $table_quota2 = table_by_key('quota2'); - - # table for dovecot v1.0 & 1.1 - db_query_parsed(" - CREATE TABLE {IF_NOT_EXISTS} $table_quota ( - username VARCHAR(255) {LATIN1} NOT NULL, - path VARCHAR(100) {LATIN1} NOT NULL, - current {BIGINT}, - PRIMARY KEY (username, path) - ) {MYISAM} ; - "); - - # table for dovecot >= 1.2 - db_query_parsed(" - CREATE TABLE {IF_NOT_EXISTS} $table_quota2 ( - username VARCHAR(100) {LATIN1} NOT NULL, - bytes {BIGINT} NOT NULL DEFAULT 0, - messages integer NOT NULL DEFAULT 0, - PRIMARY KEY (username) - ) {MYISAM} ; - "); -} - -function upgrade_730_pgsql() { - $table_quota = table_by_key('quota'); - $table_quota2 = table_by_key('quota2'); - - db_query_parsed('CREATE LANGUAGE plpgsql', 1); /* will error if plpgsql is already installed */ - - # trigger for dovecot v1.0 & 1.1 quota table - # taken from http://wiki.dovecot.org/Quota/Dict - db_query_parsed(" - CREATE OR REPLACE FUNCTION merge_quota() RETURNS TRIGGER AS \$merge_quota\$ - BEGIN - UPDATE $table_quota SET current = NEW.current + current WHERE username = NEW.username AND path = NEW.path; - IF found THEN - RETURN NULL; - ELSE - RETURN NEW; - END IF; - END; - \$merge_quota\$ LANGUAGE plpgsql; - "); - db_query_parsed(" - CREATE TRIGGER mergequota BEFORE INSERT ON $table_quota FOR EACH ROW EXECUTE PROCEDURE merge_quota(); - "); - - # trigger for dovecot >= 1.2 quota table - # taken from http://wiki.dovecot.org/Quota/Dict, table/trigger name changed to quota2 naming - db_query_parsed(" - CREATE OR REPLACE FUNCTION merge_quota2() RETURNS TRIGGER AS \$\$ - BEGIN - IF NEW.messages < 0 OR NEW.messages IS NULL THEN - -- ugly kludge: we came here from this function, really do try to insert - IF NEW.messages IS NULL THEN - NEW.messages = 0; - ELSE - NEW.messages = -NEW.messages; - END IF; - return NEW; - END IF; - - LOOP - UPDATE $table_quota2 SET bytes = bytes + NEW.bytes, - messages = messages + NEW.messages - WHERE username = NEW.username; - IF found THEN - RETURN NULL; - END IF; - - BEGIN - IF NEW.messages = 0 THEN - INSERT INTO $table_quota2 (bytes, messages, username) VALUES (NEW.bytes, NULL, NEW.username); - ELSE - INSERT INTO $table_quota2 (bytes, messages, username) VALUES (NEW.bytes, -NEW.messages, NEW.username); - END IF; - return NULL; - EXCEPTION WHEN unique_violation THEN - -- someone just inserted the record, update it - END; - END LOOP; - END; - \$\$ LANGUAGE plpgsql; -"); - - db_query_parsed(" - CREATE TRIGGER mergequota2 BEFORE INSERT ON $table_quota2 - FOR EACH ROW EXECUTE PROCEDURE merge_quota2(); - "); -} +<?php +if(!defined('POSTFIXADMIN')) { + require_once('common.php'); +} + +/* vim: set expandtab softtabstop=4 tabstop=4 shiftwidth=4: */ + +# Note: run with upgrade.php?debug=1 to see all SQL error messages + + +/** + * Use this to check whether an object (Table, index etc) exists within a + * PostgreSQL database. + * @param String the object name + * @return boolean true if it exists + */ +function _pgsql_object_exists($name) { + $sql = "select relname from pg_class where relname = '$name'"; + $r = db_query($sql); + if($r['rows'] == 1) { + return true; + } + return false; +} + +function _pgsql_field_exists($table, $field) { + $sql = ' + SELECT + a.attname, + pg_catalog.format_type(a.atttypid, a.atttypmod) AS "Datatype" + FROM + pg_catalog.pg_attribute a + WHERE + a.attnum > 0 + AND NOT a.attisdropped + AND a.attrelid = ( + SELECT c.oid + FROM pg_catalog.pg_class c + LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace + WHERE c.relname ~ ' . "'^($table)\$' + AND pg_catalog.pg_table_is_visible(c.oid) + ) + AND a.attname = '$field' "; + $r = db_query($sql); + $row = db_row($r['result']); + if($row) { + return true; + } + return false; +} + +function _mysql_field_exists($table, $field) { + $sql = "SHOW COLUMNS FROM $table LIKE '$field'"; + $r = db_query($sql); + $row = db_row($r['result']); + if($row) { + return true; + } + return false; +} + +$table = table_by_key('config'); +if($CONF['database_type'] == 'pgsql') { + // check if table already exists, if so, don't recreate it + $r = db_query("SELECT relname FROM pg_class WHERE relname = '$table'"); + if($r['rows'] == 0) { + $pgsql = " + CREATE TABLE $table ( + id SERIAL, + name VARCHAR(20) NOT NULL UNIQUE, + value VARCHAR(20) NOT NULL, + PRIMARY KEY(id) + )"; + db_query_parsed($pgsql); + } +} +else { + $mysql = " + CREATE TABLE {IF_NOT_EXISTS} $table ( + `id` {AUTOINCREMENT} {PRIMARY}, + `name` VARCHAR(20) {LATIN1} NOT NULL DEFAULT '', + `value` VARCHAR(20) {LATIN1} NOT NULL DEFAULT '', + UNIQUE name ( `name` ) + ) + "; + db_query_parsed($mysql, 0, " ENGINE = MYISAM COMMENT = 'PostfixAdmin settings'"); +} + +$sql = "SELECT * FROM $table WHERE name = 'version'"; + +// insert into config('version', '01'); + +$r = db_query($sql); + +if($r['rows'] == 1) { + $rs = $r['result']; + $row = db_array($rs); + $version = $row['value']; +} else { + db_query_parsed("INSERT INTO $table (name, value) VALUES ('version', '0')", 0, ''); + $version = 0; +} + +_do_upgrade($version); + + +function _do_upgrade($current_version) { + global $CONF; + $target_version = preg_replace('/[^0-9]/', '', '$Revision$'); + + if ($current_version >= $target_version) { + # already up to date + echo "<p>Database is up to date</p>"; + return true; + } + + echo "<p>Updating database:</p><p>- old version: $current_version; target version: $target_version</p>"; + + for ($i = $current_version +1; $i <= $target_version; $i++) { + $function = "upgrade_$i"; + $function_mysql = $function . "_mysql"; + $function_pgsql = $function . "_pgsql"; + if (function_exists($function)) { + echo "<p>updating to version $i (all databases)..."; + $function(); + echo " done"; + } + if ($CONF['database_type'] == 'mysql' || $CONF['database_type'] == 'mysqli' ) { + if (function_exists($function_mysql)) { + echo "<p>updating to version $i (MySQL)..."; + $function_mysql(); + echo " done"; + } + } elseif($CONF['database_type'] == 'pgsql') { + if (function_exists($function_pgsql)) { + echo "<p>updating to version $i (PgSQL)..."; + $function_pgsql(); + echo " done"; + } + } + // Update config table so we don't run the same query twice in the future. + $i = (int) $i; + $table = table_by_key('config'); + $sql = "UPDATE $table SET value = $i WHERE name = 'version'"; + db_query($sql); + }; +} + +/** + * Replaces database specific parts in a query + * @param String sql query with placeholders + * @param int (optional) whether errors should be ignored (0=false) + * @param String (optional) MySQL specific code to attach, useful for COMMENT= on CREATE TABLE + * @return String sql query + */ + +function db_query_parsed($sql, $ignore_errors = 0, $attach_mysql = "") { + global $CONF; + + if ($CONF['database_type'] == 'mysql' || $CONF['database_type'] == 'mysqli' ) { + + $replace = array( + '{AUTOINCREMENT}' => 'int(11) not null auto_increment', + '{PRIMARY}' => 'primary key', + '{UNSIGNED}' ... [truncated message content] |
From: <chr...@us...> - 2009-12-15 17:21:19
|
Revision: 790 http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=790&view=rev Author: christian_boltz Date: 2009-12-15 17:21:10 +0000 (Tue, 15 Dec 2009) Log Message: ----------- upgrade.php - function upgrade_1_mysql(): change default charset of vacation table to latin1. Otherwise table creation breaks with MySQL 6. Fields that need to be utf-8 are changed to utf-8 later anyways. (Found by mechno on #postfixadmin) Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2009-12-14 00:12:43 UTC (rev 789) +++ trunk/upgrade.php 2009-12-15 17:21:10 UTC (rev 790) @@ -336,7 +336,7 @@ active tinyint(4) NOT NULL default '1', PRIMARY KEY (email), KEY email (email) - ) {INNODB} DEFAULT CHARSET=utf8 COMMENT='Postfix Admin - Virtual Vacation' ;"; + ) {INNODB} DEFAULT CHARSET=latin1 COMMENT='Postfix Admin - Virtual Vacation' ;"; foreach($sql as $query) { db_query_parsed($query); This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <chr...@us...> - 2010-01-13 22:25:31
|
Revision: 794 http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=794&view=rev Author: christian_boltz Date: 2010-01-13 22:25:22 +0000 (Wed, 13 Jan 2010) Log Message: ----------- upgrade.php: - fix typo in _drop_index and _add_index ($tabe -> $table) that broke changing table names via $CONF Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2009-12-28 21:34:08 UTC (rev 793) +++ trunk/upgrade.php 2010-01-13 22:25:22 UTC (rev 794) @@ -216,7 +216,7 @@ function _drop_index ($table, $index) { global $CONF; - $tabe = table_by_key ($table); + $table = table_by_key ($table); if ($CONF['database_type'] == 'mysql' || $CONF['database_type'] == 'mysqli' ) { return "ALTER TABLE $table DROP INDEX $index"; @@ -230,7 +230,7 @@ function _add_index($table, $indexname, $fieldlist) { global $CONF; - $tabe = table_by_key ($table); + $table = table_by_key ($table); if ($CONF['database_type'] == 'mysql' || $CONF['database_type'] == 'mysqli' ) { return "ALTER TABLE $table ADD INDEX `$indexname` ( `$fieldlist` )"; This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <chr...@us...> - 2010-12-16 00:30:28
|
Revision: 894 http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=894&view=rev Author: christian_boltz Date: 2010-12-16 00:30:21 +0000 (Thu, 16 Dec 2010) Log Message: ----------- upgrade.php: - replaced deprecated split() with explode() - added SVN $Id header Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2010-12-15 23:41:40 UTC (rev 893) +++ trunk/upgrade.php 2010-12-16 00:30:21 UTC (rev 894) @@ -4,6 +4,7 @@ } /* vim: set expandtab softtabstop=4 tabstop=4 shiftwidth=4: */ +# @version $Id$ # Note: run with upgrade.php?debug=1 to see all SQL error messages @@ -762,7 +763,7 @@ $table_vacation = table_by_key ('vacation'); $table_vacation_notification = table_by_key('vacation_notification'); - $all_sql = split("\n", trim(" + $all_sql = explode("\n", trim(" ALTER TABLE `$table_vacation` CHANGE `email` `email` VARCHAR( 255 ) {LATIN1} NOT NULL ALTER TABLE `$table_vacation` CHANGE `subject` `subject` VARCHAR( 255 ) {UTF-8} NOT NULL ALTER TABLE `$table_vacation` CHANGE `body` `body` TEXT {UTF-8} NOT NULL @@ -824,7 +825,7 @@ "); # in case someone has manually created the table with utf8 fields before: - $all_sql = split("\n", trim(" + $all_sql = explode("\n", trim(" ALTER TABLE `$table_vacation_notification` CHANGE `notified` `notified` VARCHAR( 255 ) NOT NULL ALTER TABLE `$table_vacation_notification` DEFAULT CHARACTER SET utf8 ")); @@ -957,7 +958,7 @@ $table_domain = table_by_key ('domain'); $table_mailbox = table_by_key('mailbox'); - $all_sql = split("\n", trim(" + $all_sql = explode("\n", trim(" ALTER TABLE `$table_domain` CHANGE `description` `description` VARCHAR( 255 ) {UTF-8} NOT NULL ALTER TABLE `$table_mailbox` CHANGE `name` `name` VARCHAR( 255 ) {UTF-8} NOT NULL ")); @@ -995,7 +996,7 @@ $table_log = table_by_key('log'); # tables were created without explicit charset before :-( - $all_sql = split("\n", trim(" + $all_sql = explode("\n", trim(" ALTER TABLE `$table_admin` CHANGE `username` `username` VARCHAR( 255 ) {LATIN1} NOT NULL ALTER TABLE `$table_admin` CHANGE `password` `password` VARCHAR( 255 ) {LATIN1} NOT NULL ALTER TABLE `$table_admin` DEFAULT {LATIN1} This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <chr...@us...> - 2011-02-06 17:42:53
|
Revision: 945 http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=945&view=rev Author: christian_boltz Date: 2011-02-06 17:42:47 +0000 (Sun, 06 Feb 2011) Log Message: ----------- upgrade.php: add "modified" column to vacation table, various fixes and new helper functions - fix _pgsql_field_exists and _mysql_field_exists to work with non-default table names - new function _db_field_exists as database-independent wrapper for _*_field_exists - new function _db_add_field to add a field to a table - new function printdebug for debug output (grey text) - define {DATECURRENT} for timestamp fields (avoids lots of duplication) -> @GingerDog: please check if the PostgreSQL statement is correct! - upgrade_945: add a 'modified' column to the vacation table - various small changes (whitespace, comments, TODO notes etc. Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2011-02-05 10:54:59 UTC (rev 944) +++ trunk/upgrade.php 2011-02-06 17:42:47 UTC (rev 945) @@ -25,6 +25,7 @@ } function _pgsql_field_exists($table, $field) { + $table = table_by_key($table); $sql = ' SELECT a.attname, @@ -51,15 +52,45 @@ } function _mysql_field_exists($table, $field) { + $table = table_by_key($table); $sql = "SHOW COLUMNS FROM $table LIKE '$field'"; $r = db_query($sql); $row = db_row($r['result']); + if($row) { return true; } return false; } +function _db_field_exists($table, $field) { + global $CONF; + if($CONF['database_type'] == 'pgsql') { + return _pgsql_field_exists($table, $field); + } else { + return _mysql_field_exists($table, $field); + } +} + +function _db_add_field($table, $field, $fieldtype, $after) { + global $CONF; + + $query = "ALTER TABLE " . table_by_key($table) . " ADD COLUMN $field $fieldtype"; + if($CONF['database_type'] != 'pgsql') { + $query .= " AFTER $after "; # PgSQL does not support to specify where to add the column, MySQL does + } + + if(! _db_field_exists($table, $field)) { + $result = db_query_parsed($query); + } else { + printdebug ("field already exists: $table.$field"); + } +} + +function printdebug($text) { + if (safeget('debug') != "") print "<p style='color:#999'>$text</p>"; +} + $table = table_by_key('config'); if($CONF['database_type'] == 'pgsql') { // check if table already exists, if so, don't recreate it @@ -173,7 +204,8 @@ '{MYISAM}' => 'ENGINE=MyISAM', '{INNODB}' => 'ENGINE=InnoDB', '{BIGINT}' => 'bigint', - ); + '{DATECURRENT}' => 'timestamp NOT NULL default CURRENT_TIMESTAMP', + ); $sql = "$sql $attach_mysql"; } elseif($CONF['database_type'] == 'pgsql') { @@ -194,7 +226,8 @@ 'int(10)' => 'int', 'int(11)' => 'int', 'int(4)' => 'int', - ); + '{DATECURRENT}' => 'timestamp with time zone default now()', + ); } else { echo "Sorry, unsupported database type " . $conf['database_type']; @@ -205,8 +238,9 @@ $replace['{BOOL_FALSE}'] = db_get_boolean(False); $query = trim(str_replace(array_keys($replace), $replace, $sql)); + if (safeget('debug') != "") { - print "<p style='color:#999'>$query"; + printdebug ($query); } $result = db_query($query, $ignore_errors); if (safeget('debug') != "") { @@ -909,7 +943,7 @@ /** * Create alias_domain table - MySQL */ -# function upgrade_362_mysql() { # renamed to _438 to make sure it runs after an upgrade from 2.2.x +# function upgrade_362_mysql() # renamed to _438 to make sure it runs after an upgrade from 2.2.x function upgrade_438_mysql() { # Table structure for table alias_domain # @@ -931,7 +965,7 @@ /** * Create alias_domain table - PgSQL */ -# function upgrade_362_pgsql() { # renamed to _438 to make sure it runs after an upgrade from 2.2.x +# function upgrade_362_pgsql() # renamed to _438 to make sure it runs after an upgrade from 2.2.x function upgrade_438_pgsql() { # Table structure for table alias_domain $table_alias_domain = table_by_key('alias_domain'); @@ -1091,6 +1125,7 @@ } function upgrade_727_mysql() { +# TODO: do the same for PostgreSQL - if possible without different queries $table_vacation = table_by_key('vacation'); if(!_mysql_field_exists($table_vacation, 'activefrom')) { db_query_parsed("ALTER TABLE $table_vacation add activefrom datetime default NULL"); @@ -1099,6 +1134,7 @@ db_query_parsed("ALTER TABLE $table_vacation add activeuntil datetime default NULL"); } +# TODO: the following tables are not used - remove them from upgrade_727_mysql() ??? $table_client_access = table_by_key('client_access'); db_query_parsed(" CREATE TABLE IF NOT EXISTS $table_client_access ( @@ -1233,3 +1269,20 @@ FOR EACH ROW EXECUTE PROCEDURE merge_quota2(); "); } + +function upgrade_945() { + _db_add_field('vacation', 'modified', '{DATECURRENT}', 'created'); +} + + +# TODO MySQL: +# - various varchar fields do not have a default value +# https://sourceforge.net/projects/postfixadmin/forums/forum/676076/topic/3419725 +# - change default of all timestamp fields to {DATECURRENT} (CURRENT_TIMESTAMP} +# https://sourceforge.net/tracker/?func=detail&aid=1699218&group_id=191583&atid=937964 +# +# TODO all: +# drop function upgrade_727_mysql() (won't run for upgrades from 2.3.x) and +# - replace it with a function that works for all databases (_add_field) +# - ignore the unused tables (client_access etc.) + This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <chr...@us...> - 2011-02-06 19:07:50
|
Revision: 946 http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=946&view=rev Author: christian_boltz Date: 2011-02-06 19:07:44 +0000 (Sun, 06 Feb 2011) Log Message: ----------- upgrade.php: add vacation.activefrom/activeuntil even for updates from 2.3.x - MySQL only allows one column with DEFAULT CURRENT_TIMESTAMP per table -> introduce {DATE} with DEFAULT 2000-01-01 as workaround -> GingerDog, please check if the PostgreSQL variant of {DATE} is valid - disable upgrade_727_mysql because - function number is too small for upgrades from 2.3.x - MySQL only - it creates some tables PostfixAdmin doesn't use - new function upgrade_946 to add activefrom/activeuntil fields to the vacation table (previously done in upgrade_727_mysql) Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2011-02-06 17:42:47 UTC (rev 945) +++ trunk/upgrade.php 2011-02-06 19:07:44 UTC (rev 946) @@ -204,7 +204,8 @@ '{MYISAM}' => 'ENGINE=MyISAM', '{INNODB}' => 'ENGINE=InnoDB', '{BIGINT}' => 'bigint', - '{DATECURRENT}' => 'timestamp NOT NULL default CURRENT_TIMESTAMP', + '{DATE}' => "timestamp NOT NULL default '2000-01-01'", # MySQL needs a sane default (no default is interpreted as CURRENT_TIMESTAMP, which is ... + '{DATECURRENT}' => 'timestamp NOT NULL default CURRENT_TIMESTAMP', # only allowed once per table in MySQL ); $sql = "$sql $attach_mysql"; @@ -226,6 +227,7 @@ 'int(10)' => 'int', 'int(11)' => 'int', 'int(4)' => 'int', + '{DATE}' => "timestamp with time zone default '2000-01-01'", # stay in sync with MySQL '{DATECURRENT}' => 'timestamp with time zone default now()', ); @@ -1124,6 +1126,12 @@ db_query_parsed(_add_index('alias', 'domain', 'domain')); } +/* + function number too small for upgrades from 2.3.x + -> adding activefrom and activeuntil to vacation table is now upgrade_964 + -> the tables client_access, from_access, helo_access, rcpt_access, user_whitelist + are not used by PostfixAdmin - no replacement function needed + Note: Please never remove this function, even if it is disabled - it might be needed in case we have to debug a broken database upgrade etc. function upgrade_727_mysql() { # TODO: do the same for PostgreSQL - if possible without different queries $table_vacation = table_by_key('vacation'); @@ -1134,7 +1142,8 @@ db_query_parsed("ALTER TABLE $table_vacation add activeuntil datetime default NULL"); } -# TODO: the following tables are not used - remove them from upgrade_727_mysql() ??? + # the following tables are not used by postfixadmin + $table_client_access = table_by_key('client_access'); db_query_parsed(" CREATE TABLE IF NOT EXISTS $table_client_access ( @@ -1176,6 +1185,7 @@ ) {MYISAM} COMMENT='Postfix Admin - User whitelist' "); } +*/ function upgrade_729() { $table_quota = table_by_key('quota'); @@ -1274,15 +1284,16 @@ _db_add_field('vacation', 'modified', '{DATECURRENT}', 'created'); } +function upgrade_946() { + # taken from upgrade_727_mysql, needs to be done for all databases + _db_add_field('vacation', 'activefrom', '{DATE}', 'body'); + _db_add_field('vacation', 'activeuntil', '{DATE}', 'activefrom'); +} + # TODO MySQL: # - various varchar fields do not have a default value # https://sourceforge.net/projects/postfixadmin/forums/forum/676076/topic/3419725 -# - change default of all timestamp fields to {DATECURRENT} (CURRENT_TIMESTAMP} +# - change default of all timestamp fields to {DATECURRENT} (CURRENT_TIMESTAMP} or {DATE} +# including vacation.activefrom/activeuntil (might have a different default as leftover from upgrade_727_mysql) # https://sourceforge.net/tracker/?func=detail&aid=1699218&group_id=191583&atid=937964 -# -# TODO all: -# drop function upgrade_727_mysql() (won't run for upgrades from 2.3.x) and -# - replace it with a function that works for all databases (_add_field) -# - ignore the unused tables (client_access etc.) - This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <chr...@us...> - 2011-02-18 21:59:49
|
Revision: 968 http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=968&view=rev Author: christian_boltz Date: 2011-02-18 21:59:42 +0000 (Fri, 18 Feb 2011) Log Message: ----------- upgrade.php: - PostgreSQL: change domain.quota, domain.maxquota and mailbox.quota to bigint reported by oliver (n0d3 @SF), https://sourceforge.net/tracker/?func=detail&aid=3057081&group_id=191583&atid=937964 Note: MySQL has bigint field since r169. Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2011-02-18 20:32:14 UTC (rev 967) +++ trunk/upgrade.php 2011-02-18 21:59:42 UTC (rev 968) @@ -1289,11 +1289,19 @@ _db_add_field('vacation', 'activefrom', '{DATE}', 'body'); _db_add_field('vacation', 'activeuntil', '{DATE}', 'activefrom'); } +function upgrade_968_pgsql() { + # pgsql counterpart for upgrade_169_mysql() - allow really big quota + $table_domain = table_by_key ('domain'); + $table_mailbox = table_by_key('mailbox'); + db_query_parsed("ALTER TABLE $table_domain ALTER COLUMN quota type bigint"); + db_query_parsed("ALTER TABLE $table_domain ALTER COLUMN maxquota type bigint"); + db_query_parsed("ALTER TABLE $table_mailbox ALTER COLUMN quota type bigint"); +} - # TODO MySQL: # - various varchar fields do not have a default value # https://sourceforge.net/projects/postfixadmin/forums/forum/676076/topic/3419725 # - change default of all timestamp fields to {DATECURRENT} (CURRENT_TIMESTAMP} or {DATE} # including vacation.activefrom/activeuntil (might have a different default as leftover from upgrade_727_mysql) +# including vacation.modified - should be {DATE}, not {DATECURRENT} # https://sourceforge.net/tracker/?func=detail&aid=1699218&group_id=191583&atid=937964 This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <chr...@us...> - 2011-03-14 18:38:13
|
Revision: 1001 http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=1001&view=rev Author: christian_boltz Date: 2011-03-14 18:38:07 +0000 (Mon, 14 Mar 2011) Log Message: ----------- upgrade.php: - fix syntax error Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2011-03-13 22:22:13 UTC (rev 1000) +++ trunk/upgrade.php 2011-03-14 18:38:07 UTC (rev 1001) @@ -1305,4 +1305,4 @@ # including vacation.activefrom/activeuntil (might have a different default as leftover from upgrade_727_mysql) # including vacation.modified - should be {DATE}, not {DATECURRENT} # https://sourceforge.net/tracker/?func=detail&aid=1699218&group_id=191583&atid=937964 -@todo vacation.email has 2 indizes \ No newline at end of file +# @todo vacation.email has 2 indizes This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <chr...@us...> - 2011-04-23 18:38:38
|
Revision: 1050 http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=1050&view=rev Author: christian_boltz Date: 2011-04-23 18:38:31 +0000 (Sat, 23 Apr 2011) Log Message: ----------- upgrade_1050(): - add index on (domain,timestamp) in log table to make viewlog faster _add_index(): - fix handling of multi-column aliases in MySQL Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2011-04-19 22:59:00 UTC (rev 1049) +++ trunk/upgrade.php 2011-04-23 18:38:31 UTC (rev 1050) @@ -270,6 +270,7 @@ $table = table_by_key ($table); if ($CONF['database_type'] == 'mysql' || $CONF['database_type'] == 'mysqli' ) { + $fieldlist = str_replace(',', '`,`', $fieldlist); # fix quoting if index contains multiple fields return "ALTER TABLE $table ADD INDEX `$indexname` ( `$fieldlist` )"; } elseif($CONF['database_type'] == 'pgsql') { $pgindexname = $table . "_" . $indexname . '_idx'; @@ -1298,6 +1299,10 @@ db_query_parsed("ALTER TABLE $table_mailbox ALTER COLUMN quota type bigint"); } +function upgrade_1050() { + db_query_parsed(_add_index('log', 'domain_timestamp', 'domain,timestamp')); +} + # TODO MySQL: # - various varchar fields do not have a default value # https://sourceforge.net/projects/postfixadmin/forums/forum/676076/topic/3419725 This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <chr...@us...> - 2011-07-25 23:28:38
|
Revision: 1130 http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=1130&view=rev Author: christian_boltz Date: 2011-07-25 23:28:32 +0000 (Mon, 25 Jul 2011) Log Message: ----------- upgrade.php: - add hint about ?debug=1 Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2011-07-25 23:18:52 UTC (rev 1129) +++ trunk/upgrade.php 2011-07-25 23:28:32 UTC (rev 1130) @@ -147,6 +147,7 @@ } echo "<p>Updating database:</p><p>- old version: $current_version; target version: $target_version</p>"; + echo "<div style='color:#999'> (If the update doesn't work, run setup.php?debug=1 to see the detailed error messages and SQL queries.)</div>"; for ($i = $current_version +1; $i <= $target_version; $i++) { $function = "upgrade_$i"; This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <chr...@us...> - 2011-11-27 20:22:37
|
Revision: 1283 http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=1283&view=rev Author: christian_boltz Date: 2011-11-27 20:22:31 +0000 (Sun, 27 Nov 2011) Log Message: ----------- upgrade.php - upgrade_1283(): add a "superadmin" column to the admin table This is the first step to get rid of the "ALL" dummy domain. Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2011-11-26 01:09:38 UTC (rev 1282) +++ trunk/upgrade.php 2011-11-27 20:22:31 UTC (rev 1283) @@ -1304,6 +1304,12 @@ db_query_parsed(_add_index('log', 'domain_timestamp', 'domain,timestamp')); } +function upgrade_1283() { + _db_add_field('admin', 'superadmin', '{BOOLEAN}', 'password'); +} + + + # TODO MySQL: # - various varchar fields do not have a default value # https://sourceforge.net/projects/postfixadmin/forums/forum/676076/topic/3419725 This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <chr...@us...> - 2011-11-27 21:23:42
|
Revision: 1284 http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=1284&view=rev Author: christian_boltz Date: 2011-11-27 21:23:36 +0000 (Sun, 27 Nov 2011) Log Message: ----------- upgrade.php: - upgrade_1284(): migrate the ALL domain to the superadmin column Note: The ALL domain is not (yet) deleted to stay backwards-compatible for now (will be done in a later upgrade function) Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2011-11-27 20:22:31 UTC (rev 1283) +++ trunk/upgrade.php 2011-11-27 21:23:36 UTC (rev 1284) @@ -1308,8 +1308,21 @@ _db_add_field('admin', 'superadmin', '{BOOLEAN}', 'password'); } +function upgrade_1284() { + # migrate the ALL domain to the superadmin column + # Note: The ALL domain is not (yet) deleted to stay backwards-compatible for now (will be done in a later upgrade function) + $result = db_query("SELECT username FROM " . table_by_key('domain_admins') . " where domain='ALL'"); + if ($result['rows'] > 0) { + while ($row = db_array ($result['result'])) { + printdebug ("Setting superadmin flag for " . $row['username']); + db_update('admin', 'username', $row['username'], array('superadmin' => db_get_boolean(true)) ); + } + } +} + + # TODO MySQL: # - various varchar fields do not have a default value # https://sourceforge.net/projects/postfixadmin/forums/forum/676076/topic/3419725 This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <chr...@us...> - 2012-03-27 19:56:48
|
Revision: 1345 http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=1345&view=rev Author: christian_boltz Date: 2012-03-27 19:56:42 +0000 (Tue, 27 Mar 2012) Log Message: ----------- upgrade.php: - add \n after "Upgrading database" lines - that makes the output readable if setup.php is called in a console instead a webbrowser Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2012-02-03 23:53:58 UTC (rev 1344) +++ trunk/upgrade.php 2012-03-27 19:56:42 UTC (rev 1345) @@ -146,7 +146,7 @@ return true; } - echo "<p>Updating database:</p><p>- old version: $current_version; target version: $target_version</p>"; + echo "<p>Updating database:</p><p>- old version: $current_version; target version: $target_version</p>\n"; echo "<div style='color:#999'> (If the update doesn't work, run setup.php?debug=1 to see the detailed error messages and SQL queries.)</div>"; for ($i = $current_version +1; $i <= $target_version; $i++) { This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <chr...@us...> - 2012-05-28 15:50:31
|
Revision: 1391 http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=1391&view=rev Author: christian_boltz Date: 2012-05-28 15:50:25 +0000 (Mon, 28 May 2012) Log Message: ----------- upgrade.php: - _pgsql_field_exists(), _mysql_field_exists(): Those functions are always called with the expanded table name - don't expand it twice. (The better solution would be to change all calling code to provide non-expanded tablenames, but that's more work.) Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2012-05-27 19:06:37 UTC (rev 1390) +++ trunk/upgrade.php 2012-05-28 15:50:25 UTC (rev 1391) @@ -25,7 +25,7 @@ } function _pgsql_field_exists($table, $field) { - $table = table_by_key($table); + # $table = table_by_key($table); # _pgsql_field_exists is always called with the expanded table name - don't expand it twice $sql = ' SELECT a.attname, @@ -52,7 +52,7 @@ } function _mysql_field_exists($table, $field) { - $table = table_by_key($table); + # $table = table_by_key($table); # _mysql_field_exists is always called with the expanded table name - don't expand it twice $sql = "SHOW COLUMNS FROM $table LIKE '$field'"; $r = db_query($sql); $row = db_row($r['result']); This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <chr...@us...> - 2013-11-16 20:52:04
|
Revision: 1591 http://sourceforge.net/p/postfixadmin/code/1591 Author: christian_boltz Date: 2013-11-16 20:52:01 +0000 (Sat, 16 Nov 2013) Log Message: ----------- upgrade.php: - convert TODO to a note Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2013-11-16 20:38:15 UTC (rev 1590) +++ trunk/upgrade.php 2013-11-16 20:52:01 UTC (rev 1591) @@ -1134,8 +1134,9 @@ -> the tables client_access, from_access, helo_access, rcpt_access, user_whitelist are not used by PostfixAdmin - no replacement function needed Note: Please never remove this function, even if it is disabled - it might be needed in case we have to debug a broken database upgrade etc. + Note: there never was a function upgrade_727_pgsql() + function upgrade_727_mysql() { -# TODO: do the same for PostgreSQL - if possible without different queries $table_vacation = table_by_key('vacation'); if(!_mysql_field_exists($table_vacation, 'activefrom')) { db_query_parsed("ALTER TABLE $table_vacation add activefrom datetime default NULL"); This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <chr...@us...> - 2013-12-08 22:12:27
|
Revision: 1605 http://sourceforge.net/p/postfixadmin/code/1605 Author: christian_boltz Date: 2013-12-08 22:12:24 +0000 (Sun, 08 Dec 2013) Log Message: ----------- upgrade.php: - change {BIGINT} to include "NOT NULL DEFAULT 0" - add {INT} (not used anywhere yet) - upgrade_729: - quota2 table: change "{BIGINT} NOT NULL DEFAULT 0" to "{BIGINT}" to match the above change - note: quota table created with old versions of upgrade.php will not have explicit "NOT NULL DEFAULT 0" for the "current" field (shouldn't hurt) Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2013-12-08 21:30:30 UTC (rev 1604) +++ trunk/upgrade.php 2013-12-08 22:12:24 UTC (rev 1605) @@ -204,7 +204,8 @@ '{RENAME_COLUMN}' => 'CHANGE COLUMN', '{MYISAM}' => 'ENGINE=MyISAM', '{INNODB}' => 'ENGINE=InnoDB', - '{BIGINT}' => 'bigint', + '{INT}' => 'integer NOT NULL DEFAULT 0', + '{BIGINT}' => 'bigint NOT NULL DEFAULT 0', '{DATE}' => "timestamp NOT NULL default '2000-01-01'", # MySQL needs a sane default (no default is interpreted as CURRENT_TIMESTAMP, which is ... '{DATECURRENT}' => 'timestamp NOT NULL default CURRENT_TIMESTAMP', # only allowed once per table in MySQL ); @@ -223,7 +224,8 @@ '{RENAME_COLUMN}' => 'ALTER COLUMN', # PgSQL : ALTER TABLE x RENAME x TO y '{MYISAM}' => '', '{INNODB}' => '', - '{BIGINT}' => 'bigint', + '{INT}' => 'integer NOT NULL DEFAULT 0', + '{BIGINT}' => 'bigint NOT NULL DEFAULT 0', 'int(1)' => 'int', 'int(10)' => 'int', 'int(11)' => 'int', @@ -1208,7 +1210,7 @@ db_query_parsed(" CREATE TABLE {IF_NOT_EXISTS} $table_quota2 ( username VARCHAR(100) {LATIN1} NOT NULL, - bytes {BIGINT} NOT NULL DEFAULT 0, + bytes {BIGINT}, messages integer NOT NULL DEFAULT 0, PRIMARY KEY (username) ) {MYISAM} ; This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <chr...@us...> - 2013-12-09 22:10:58
|
Revision: 1606 http://sourceforge.net/p/postfixadmin/code/1606 Author: christian_boltz Date: 2013-12-09 22:10:55 +0000 (Mon, 09 Dec 2013) Log Message: ----------- upgrade.php: - upgrade_729 - add comment about r1605 changes Revision Links: -------------- http://sourceforge.net/p/postfixadmin/code/1605 Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2013-12-08 22:12:24 UTC (rev 1605) +++ trunk/upgrade.php 2013-12-09 22:10:55 UTC (rev 1606) @@ -1197,6 +1197,9 @@ $table_quota2 = table_by_key('quota2'); # table for dovecot v1.0 & 1.1 + # note: quota table created with old versions of upgrade.php (before r1605) + # will not have explicit "NOT NULL DEFAULT 0" for the "current" field + # (shouldn't hurt) db_query_parsed(" CREATE TABLE {IF_NOT_EXISTS} $table_quota ( username VARCHAR(255) {LATIN1} NOT NULL, This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <gin...@us...> - 2014-01-14 16:03:39
|
Revision: 1626 http://sourceforge.net/p/postfixadmin/code/1626 Author: gingerdog Date: 2014-01-14 16:03:36 +0000 (Tue, 14 Jan 2014) Log Message: ----------- upgrade.php : fix postgres complaining about addition of boolean not null field when existing data exists and no default is specified Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2014-01-14 16:02:03 UTC (rev 1625) +++ trunk/upgrade.php 2014-01-14 16:03:36 UTC (rev 1626) @@ -1310,10 +1310,14 @@ db_query_parsed(_add_index('log', 'domain_timestamp', 'domain,timestamp')); } -function upgrade_1283() { +function upgrade_1283_mysql() { _db_add_field('admin', 'superadmin', '{BOOLEAN}', 'password'); } +function upgrade_1283_pgsql() { /* postgresql doesn't like adding columns which can't be null when there is data already there. */ + _db_add_field('admin', 'superadmin', "{BOOLEAN} DEFAULT '{BOOL_TRUE}'", 'password'); +} + function upgrade_1284() { # migrate the ALL domain to the superadmin column # Note: The ALL domain is not (yet) deleted to stay backwards-compatible for now (will be done in a later upgrade function) This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <chr...@us...> - 2014-01-21 19:17:11
|
Revision: 1632 http://sourceforge.net/p/postfixadmin/code/1632 Author: christian_boltz Date: 2014-01-21 19:17:09 +0000 (Tue, 21 Jan 2014) Log Message: ----------- upgrade.php: - change {BOOLEAN} to include "default false" - revert the r1626 changes in upgrade_1283 (BTW: "by default, every admin is a superadmin" is not a good idea ;-) Revision Links: -------------- http://sourceforge.net/p/postfixadmin/code/1626 Modified Paths: -------------- trunk/upgrade.php Modified: trunk/upgrade.php =================================================================== --- trunk/upgrade.php 2014-01-21 19:04:56 UTC (rev 1631) +++ trunk/upgrade.php 2014-01-21 19:17:09 UTC (rev 1632) @@ -197,7 +197,7 @@ '{PRIMARY}' => 'primary key', '{UNSIGNED}' => 'unsigned' , '{FULLTEXT}' => 'FULLTEXT', - '{BOOLEAN}' => 'tinyint(1) NOT NULL', + '{BOOLEAN}' => "tinyint(1) NOT NULL DEFAULT '" . db_get_boolean(False) . "'", '{UTF-8}' => '/*!40100 CHARACTER SET utf8 */', '{LATIN1}' => '/*!40100 CHARACTER SET latin1 */', '{IF_NOT_EXISTS}' => 'IF NOT EXISTS', @@ -217,7 +217,7 @@ '{PRIMARY}' => 'primary key', '{UNSIGNED}' => '', '{FULLTEXT}' => '', - '{BOOLEAN}' => 'BOOLEAN NOT NULL', + '{BOOLEAN}' => "BOOLEAN NOT NULL DEFAULT '" . db_get_boolean(False) . "'", '{UTF-8}' => '', # UTF-8 is simply ignored. '{LATIN1}' => '', # same for latin1 '{IF_NOT_EXISTS}' => '', # does not work with PgSQL @@ -392,7 +392,7 @@ $result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN transport VARCHAR(255) AFTER maxquota;", TRUE); } if(!_mysql_field_exists($table_domain, 'backupmx')) { - $result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN backupmx {BOOLEAN} DEFAULT {BOOL_FALSE} AFTER transport;", TRUE); + $result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN backupmx {BOOLEAN} AFTER transport;", TRUE); } } @@ -1310,14 +1310,10 @@ db_query_parsed(_add_index('log', 'domain_timestamp', 'domain,timestamp')); } -function upgrade_1283_mysql() { +function upgrade_1283() { _db_add_field('admin', 'superadmin', '{BOOLEAN}', 'password'); } -function upgrade_1283_pgsql() { /* postgresql doesn't like adding columns which can't be null when there is data already there. */ - _db_add_field('admin', 'superadmin', "{BOOLEAN} DEFAULT '{BOOL_TRUE}'", 'password'); -} - function upgrade_1284() { # migrate the ALL domain to the superadmin column # Note: The ALL domain is not (yet) deleted to stay backwards-compatible for now (will be done in a later upgrade function) This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |