Thread: SF.net SVN: postfixadmin: [170] trunk/upgrade.php
Brought to you by:
christian_boltz,
gingerdog
Menu
▾
▴
postfixadmin-svn
|
From: <Gin...@us...> - 2007-10-31 20:18:55
|
Revision: 170
http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=170&view=rev
Author: GingerDog
Date: 2007-10-31 13:18:53 -0700 (Wed, 31 Oct 2007)
Log Message:
-----------
upgrade.php: test edition of a db upgrade script, based loosely around what Drupal uses
Added Paths:
-----------
trunk/upgrade.php
Added: trunk/upgrade.php
===================================================================
--- trunk/upgrade.php (rev 0)
+++ trunk/upgrade.php 2007-10-31 20:18:53 UTC (rev 170)
@@ -0,0 +1,68 @@
+<?php
+require_once('common.php');
+$sql = "SELECT * FROM config WHERE name = 'version'";
+
+// create table config (name varchar(20), value varchar(20));
+// insert into config('version', '01');
+// Should really query the db to see if the 'config' table exists first!
+
+$r = db_query($sql);
+
+if($r['rows'] == 1) {
+ $rs = $r['result'];
+ $row = db_array($rs);
+ $version = $row['value'];
+ _do_upgrade($version);
+}
+
+
+function _do_upgrade($current_version) {
+ $all_functions = get_defined_functions();
+ $upgrade_functions = array();
+ foreach($all_functions['user'] as $function_name) {
+ if(preg_match('!upgrade_(\d+)!', $function_name, $matches)) {
+ $version = $matches[1];
+ if($version <= $current_version) {
+ continue;
+ }
+ $upgrade_functions[$matches[1]] = $function_name;
+ }
+ }
+
+ ksort($upgrade_functions);
+ foreach($upgrade_functions as $version => $function) {
+ $function();
+ }
+}
+
+function upgrade_00() {
+ global $CONF;
+ if($CONF['database_type'] == 'mysql') {
+ echo 'mysql 00';
+ }
+ if($CONF['database_type'] == 'pgsql') {
+ echo 'pgsql 00';
+ }
+}
+
+function upgrade_01() {
+ global $CONF;
+ if($CONF['database_type'] == 'mysql') {
+ echo 'mysql 01';
+ }
+ if($CONF['database_type'] == 'pgsql') {
+ echo 'pgsql 01';
+ }
+}
+function upgrade_02() {
+ global $CONF;
+ if($CONF['database_type'] == 'mysql') {
+ echo 'mysql 02';
+ }
+ if($CONF['database_type'] == 'pgsql') {
+ echo 'pgsql 02';
+ }
+}
+function upgrade_03() {
+ echo 'woof 03';
+}
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <chr...@us...> - 2007-11-02 00:24:32
|
Revision: 172
http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=172&view=rev
Author: christian_boltz
Date: 2007-11-01 17:24:36 -0700 (Thu, 01 Nov 2007)
Log Message:
-----------
upgrade.php:
- create config table at startup (not nice, but works)
- changed _do_upgrade to use "for ($i = $current_version +1;
$i <= $target_version; $i++)" loop and function_exists()
- added support for upgrade_<number>>_mysql() and
upgrade_<number>_pgsql() functions for database-specific changes
- added function db_query_parsed(), idea taken from serendipity's
include/db/*.php serendipity_db_schema_import()
- added function _drop_index() - MySQL and PgSQL syntax differs too
much on this
- added pre-2.1 database updates (DOCUMENTS/TABLE_BACKUP_MX.TXT and
DOCUMENTS/TABLE_CHANGES.TXT)
- added all database changes between 2.1 and current SVN.
Everything since the move to sourceforge has the revision number in
the function name.
(still several TODOs left)
Modified Paths:
--------------
trunk/upgrade.php
Property Changed:
----------------
trunk/upgrade.php
Modified: trunk/upgrade.php
===================================================================
--- trunk/upgrade.php 2007-11-01 23:58:12 UTC (rev 171)
+++ trunk/upgrade.php 2007-11-02 00:24:36 UTC (rev 172)
@@ -1,10 +1,27 @@
<?php
require_once('common.php');
+
+# Note: run with upgrade.php?debug=1 to see all SQL error messages
+
+
+# Not nice, but works:
+# We don't know if the config table exists, so we simply try to create it ;-)
+# Better solution (TODO): query the db to see if the 'config' table exists
+
+$sql = "
+CREATE TABLE {IF_NOT_EXISTS} " . table_by_key ('config') . "(
+ `id` {AUTOINCREMENT} {PRIMARY},
+ `name` VARCHAR(20) {LATIN1} NOT NULL DEFAULT '',
+ `value` VARCHAR(20) {LATIN1} NOT NULL DEFAULT '',
+ UNIQUE name ( `name` )
+)
+";
+
+db_query_parsed($sql, 0, " ENGINE = MYISAM COMMENT = 'PostfixAdmin settings'");
+
$sql = "SELECT * FROM config WHERE name = 'version'";
-// create table config (name varchar(20), value varchar(20));
// insert into config('version', '01');
-// Should really query the db to see if the 'config' table exists first!
$r = db_query($sql);
@@ -12,57 +29,334 @@
$rs = $r['result'];
$row = db_array($rs);
$version = $row['value'];
- _do_upgrade($version);
+} else {
+ $version = 0;
}
+_do_upgrade($version);
function _do_upgrade($current_version) {
- $all_functions = get_defined_functions();
- $upgrade_functions = array();
- foreach($all_functions['user'] as $function_name) {
- if(preg_match('!upgrade_(\d+)!', $function_name, $matches)) {
- $version = $matches[1];
- if($version <= $current_version) {
- continue;
- }
- $upgrade_functions[$matches[1]] = $function_name;
- }
+ global $CONF;
+ $target_version = preg_replace('/[^0-9]/', '', '$Revision:42$');
+
+ if ($current_version >= $target_version) {
+ # already up to date
+ echo "up to date";
+ return true;
}
- ksort($upgrade_functions);
- foreach($upgrade_functions as $version => $function) {
- $function();
- }
+ echo "<p>Updating database:<p>old version: $current_version; target version: $target_version";
+
+ 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";
+ }
+ }
+ # TODO: update version in config table after each change
+ # TODO: this avoids problems in case the script hits the max_execution_time,
+ # TODO: simply rerunning it will continue where it was stopped
+ };
}
-function upgrade_00() {
+/**
+ * Replaces database specific parts in a query
+ * @param String sql query with placeholders
+ * @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') {
- echo 'mysql 00';
+
+ 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}' => '`active` tinyint(1) NOT NULL',
+ '{UTF_8}' => '/*!40100 CHARACTER SET utf8 COLLATE utf8_unicode_ci */',
+ '{LATIN1}' => '/*!40100 CHARACTER SET latin1 COLLATE latin1_swedish_ci */',
+ '{IF_NOT_EXISTS}' => 'IF NOT EXISTS',
+ '{RENAME_COLUMN}' => 'CHANGE COLUMN',
+ );
+ $sql = "$sql $attach_mysql";
+
+ } elseif($CONF['database_type'] == 'pgsql') {
+ static $replace = array(
+ '{AUTOINCREMENT}' => 'SERIAL',
+ '{PRIMARY}' => 'primary key',
+ '{UNSIGNED}' => '',
+ '{FULLTEXT}' => '',
+ '{BOOLEAN}' => 'BOOLEAN NOT NULL',
+ '{UTF_8}' => '', # TODO: UTF_8 is simply ignored.
+ '{LATIN1}' => '', # TODO: same for latin1
+ '{IF_NOT_EXISTS}' => 'IF NOT EXISTS', # TODO: does this work with PgSQL?
+ '{RENAME_COLUMN}' => 'CHANGE COLUMN', # TODO: probably wrong
+ 'int(1)' => 'int2',
+ 'int(10)' => 'int4',
+ 'int(11)' => 'int4',
+ 'int(4)' => 'int4',
+ );
+
+ } else {
+ echo "Sorry, unsupported database type " . $conf['database_type'];
+ exit;
}
- if($CONF['database_type'] == 'pgsql') {
- echo 'pgsql 00';
+
+ $replace['{BOOL_TRUE}'] = db_get_boolean(True);
+ $replace['{BOOL_FALSE}'] = db_get_boolean(False);
+
+ $query = trim(str_replace(array_keys($replace), $replace, $sql));
+ $result = db_query($query, $ignore_errors);
+ if (safeget('debug') != "") {
+ print $result['error'];
}
+ return $result;
}
-function upgrade_01() {
+function _drop_index ($table, $index) {
global $CONF;
- if($CONF['database_type'] == 'mysql') {
- echo 'mysql 01';
+ $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"; # TODO: on which table?!
+ } else {
+ echo "Sorry, unsupported database type " . $conf['database_type'];
+ exit;
}
- if($CONF['database_type'] == 'pgsql') {
- echo 'pgsql 01';
- }
}
-function upgrade_02() {
- global $CONF;
- if($CONF['database_type'] == 'mysql') {
- echo 'mysql 02';
+
+function upgrade_1() {
+ # inserting the version number is a good start ;-)
+ db_insert(
+ 'config',
+ array(
+ 'name' => 'version',
+ 'value' => '1',
+ )
+ );
+ echo "upgrade_1";
+}
+
+function upgrade_2() {
+ # upgrade pre-2.1 database
+ # from TABLE_BACKUP_MX.TXT
+ $table_domain = table_by_key ('domain');
+ $result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN transport VARCHAR(255) AFTER maxquota;", TRUE);
+ $result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN backupmx {BOOLEAN} DEFAULT {BOOL_FALSE} AFTER transport;", TRUE);
+}
+
+function upgrade_3() {
+ # 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');
+
+ $all_sql = split("\n", trim("
+ ALTER TABLE $table_admin {RENAME_COLUMN} create_date created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
+ ALTER TABLE $table_admin {RENAME_COLUMN} change_date modified DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
+ ALTER TABLE $table_alias {RENAME_COLUMN} create_date created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
+ ALTER TABLE $table_alias {RENAME_COLUMN} change_date modified DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
+ ALTER TABLE $table_domain {RENAME_COLUMN} create_date created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
+ ALTER TABLE $table_domain {RENAME_COLUMN} change_date modified DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
+ ALTER TABLE $table_domain ADD COLUMN aliases INT(10) DEFAULT '-1' NOT NULL AFTER description;
+ ALTER TABLE $table_domain ADD COLUMN mailboxes INT(10) DEFAULT '-1' NOT NULL AFTER aliases;
+ ALTER TABLE $table_domain ADD COLUMN maxquota INT(10) DEFAULT '-1' NOT NULL AFTER mailboxes;
+ ALTER TABLE $table_domain ADD COLUMN transport VARCHAR(255) AFTER maxquota;
+ ALTER TABLE $table_domain ADD COLUMN backupmx TINYINT(1) DEFAULT '0' NOT NULL AFTER transport;
+ ALTER TABLE $table_mailbox {RENAME_COLUMN} create_date created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
+ ALTER TABLE $table_mailbox {RENAME_COLUMN} change_date modified DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
+ ALTER TABLE $table_mailbox ADD COLUMN quota INT(10) DEFAULT '-1' NOT NULL AFTER maildir;
+ ALTER TABLE $table_vacation ADD COLUMN domain VARCHAR(255) DEFAULT '' NOT NULL AFTER cache;
+ ALTER TABLE $table_vacation ADD COLUMN created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL AFTER domain;
+ ALTER TABLE $table_vacation ADD COLUMN active TINYINT(1) DEFAULT '1' NOT NULL AFTER created;
+ ALTER TABLE $table_vacation DROP PRIMARY KEY
+ ALTER TABLE $table_vacation ADD COLUMN PRIMARY KEY(email);
+ UPDATE $table_vacation SET domain=SUBSTRING_INDEX(email, '@', -1) WHERE email=email;
+ "));
+
+ foreach ($all_sql as $sql) {
+ $result = db_query_parsed($sql, TRUE);
}
- if($CONF['database_type'] == 'pgsql') {
- echo 'pgsql 02';
- }
}
-function upgrade_03() {
- echo 'woof 03';
+
+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);
}
+
+function upgrade_4_pgsql() { # PgSQL only
+ # changes between 2.1 and moving to sourceforge
+
+/* TODO
+
+Changes in DATABASE_PGSQL.TXT: (in diff format - "-" means removed, "+" means added)
+
+TABLE domain
+- domain character varying(255) NOT NULL default '',
++ domain character varying(255) NOT NULL,
++ quota integer NOT NULL default 0,
+
++CREATE INDEX domain_domain_active ON domain(domain,active);
+
+
+TABLE "admin"
+- "username" character varying(255) NOT NULL default '',
++ "username" character varying(255) NOT NULL,
+
+
+TABLE alias
+- address character varying(255) NOT NULL default '',
++ address character varying(255) NOT NULL,
+- domain character varying(255) NOT NULL default '',
++ domain character varying(255) NOT NULL REFERENCES domain,
+
++CREATE INDEX alias_address_active ON alias(address,active);
+
+TABLE domain_admins
+- username character varying(255) NOT NULL default '',
++ username character varying(255) NOT NULL,
+- domain character varying(255) NOT NULL default '',
++ domain character varying(255) NOT NULL REFERENCES domain,
+
+TABLE log
+- data character varying(255) NOT NULL default ''
++ data text NOT NULL default ''
+
+TABLE mailbox
+- username character varying(255) NOT NULL default '',
++ username character varying(255) NOT NULL,
+- domain character varying(255) NOT NULL default '',
++ domain character varying(255) NOT NULL REFERENCES domain,
+
++CREATE INDEX mailbox_username_active ON mailbox(username,active);
+
+TABLE vacation
+- email character varying(255) NOT NULL default '',
++ email character varying(255) PRIMARY KEY,
+- body text NOT NULL,
++ body text NOT NULL DEFAULT '',
+- cache text NOT NULL,
++ cache text NOT NULL DEFAULT '',
+- domain character varying(255) NOT NULL default '',
++ "domain" character varying(255) NOT NULL REFERENCES "domain",
+- active boolean NOT NULL default true,
++ active boolean DEFAULT true NOT NULL
+- Constraint "vacation_key" Primary Key ("email")
+
+-COMMENT ON TABLE vacation IS 'Postfix Admin - Virtual Vacation';
+
++CREATE INDEX vacation_email_active ON vacation(email,active);
+
++CREATE TABLE vacation_notification (
++ on_vacation character varying(255) NOT NULL REFERENCES 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)
++);
+
+
+*/
+}
+
+function upgrade_79_mysql() { # MySQL only
+ # drop useless indicies (already available as primary key)
+ $result = db_query_parsed(_drop_index('admin', 'username'));
+ $result = db_query_parsed(_drop_index('alias', 'address'));
+ $result = db_query_parsed(_drop_index('domain', 'domain'));
+ $result = db_query_parsed(_drop_index('mailbox', 'username'));
+}
+
+function upgrade_81_mysql() { # MySQL only
+/* TODO
+
+table vacation -
+- all varchar + text fields changed to utf8
+- active was tinyint (1), now (4)
+- ENGINE changed
+- DEFAULT CHARSET changed
+- COLLATE changed
+
+diff:
+ - `body` text NOT NULL default '',
+ - `cache` text NOT NULL default '',
+ + body text NOT NULL,
+ + cache text NOT NULL,
+ - `active` tinyint(1) NOT NULL default '1',
+ + active tinyint(4) NOT NULL default '1', -> boolean are usually tinyint(1)
+ -) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Vacation';
+ +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci TYPE=InnoDB COMMENT='Postfix Admin - Virtual Vacation' ;
+
+
+
+ */
+
+ db_query_parsed(
+ " CREATE TABLE {IF_NOT_EXISTS} vacation_notification (
+ on_vacation varchar(255) NOT NULL,
+ notified varchar(255) NOT NULL,
+ notified_at timestamp NOT NULL default now(),
+ CONSTRAINT vacation_notification_pkey PRIMARY KEY(on_vacation, notified),
+ FOREIGN KEY (on_vacation) REFERENCES vacation(email) ON DELETE CASCADE
+ )
+ ",
+ TRUE,
+ " ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci TYPE=InnoDB
+ COMMENT='Postfix Admin - Virtual Vacation Notifications'
+ "
+ );
+}
+
+function upgrade_169_mysql() { # MySQL only
+ # allow quota > 2 GB
+
+ $table_domain = table_by_key ('domain');
+ $table_mailbox = table_by_key ('mailbox');
+ $result = db_query_parsed("ALTER TABLE $table_domain ALTER COLUMN `quota` bigint(20) NOT NULL default '0'", TRUE);
+ $result = db_query_parsed("ALTER TABLE $table_mailbox ALTER COLUMN `maxquota` bigint(20) NOT NULL default '0'", TRUE);
+ $result = db_query_parsed("ALTER TABLE $table_mailbox ALTER COLUMN `quota` bigint(20) NOT NULL default '0'", TRUE);
+}
+
+
+/*
+TODO
+
+ Database changes that should be done:
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+MySQL:
+* vacation: DROP INDEX email
+* vacation_notification:
+ - DEFAULT CHARSET and COLLATE should be changed
+ - change all varchar fields to latin1 (email addresses don't contain utf8 characters)
+* remove all DROP TABLE statements from DATABASE_*
+
+*/
+
+
+/* vim: set expandtab softtabstop=4 tabstop=4 shiftwidth=4: */
Property changes on: trunk/upgrade.php
___________________________________________________________________
Name: svn:keywords
+ Revision
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <chr...@us...> - 2007-11-02 00:35:42
|
Revision: 174
http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=174&view=rev
Author: christian_boltz
Date: 2007-11-01 17:35:46 -0700 (Thu, 01 Nov 2007)
Log Message:
-----------
- next try to expand $Revision$ in upgrade.php :-/
Modified Paths:
--------------
trunk/upgrade.php
Modified: trunk/upgrade.php
===================================================================
--- trunk/upgrade.php 2007-11-02 00:27:32 UTC (rev 173)
+++ trunk/upgrade.php 2007-11-02 00:35:46 UTC (rev 174)
@@ -37,7 +37,7 @@
function _do_upgrade($current_version) {
global $CONF;
- $target_version = preg_replace('/[^0-9]/', '', '$Revision:42$');
+ $target_version = preg_replace('/[^0-9]/', '', '$Revision$');
if ($current_version >= $target_version) {
# already up to date
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <chr...@us...> - 2007-11-02 00:50:53
|
Revision: 175
http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=175&view=rev
Author: christian_boltz
Date: 2007-11-01 17:50:58 -0700 (Thu, 01 Nov 2007)
Log Message:
-----------
- fixed some SQL syntax errors
- debug output now includes the query
Modified Paths:
--------------
trunk/upgrade.php
Modified: trunk/upgrade.php
===================================================================
--- trunk/upgrade.php 2007-11-02 00:35:46 UTC (rev 174)
+++ trunk/upgrade.php 2007-11-02 00:50:58 UTC (rev 175)
@@ -92,7 +92,7 @@
'{PRIMARY}' => 'primary key',
'{UNSIGNED}' => 'unsigned' ,
'{FULLTEXT}' => 'FULLTEXT',
- '{BOOLEAN}' => '`active` tinyint(1) NOT NULL',
+ '{BOOLEAN}' => 'tinyint(1) NOT NULL',
'{UTF_8}' => '/*!40100 CHARACTER SET utf8 COLLATE utf8_unicode_ci */',
'{LATIN1}' => '/*!40100 CHARACTER SET latin1 COLLATE latin1_swedish_ci */',
'{IF_NOT_EXISTS}' => 'IF NOT EXISTS',
@@ -128,7 +128,8 @@
$query = trim(str_replace(array_keys($replace), $replace, $sql));
$result = db_query($query, $ignore_errors);
if (safeget('debug') != "") {
- print $result['error'];
+ print "<p style='color:#999'>$query";
+ print "<div style='color:#f00'>" . $result['error'] . "</div>";
}
return $result;
}
@@ -195,7 +196,7 @@
ALTER TABLE $table_vacation ADD COLUMN created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL AFTER domain;
ALTER TABLE $table_vacation ADD COLUMN active TINYINT(1) DEFAULT '1' NOT NULL AFTER created;
ALTER TABLE $table_vacation DROP PRIMARY KEY
- ALTER TABLE $table_vacation ADD COLUMN PRIMARY KEY(email);
+ ALTER TABLE $table_vacation ADD PRIMARY KEY(email)
UPDATE $table_vacation SET domain=SUBSTRING_INDEX(email, '@', -1) WHERE email=email;
"));
@@ -286,10 +287,10 @@
function upgrade_79_mysql() { # MySQL only
# drop useless indicies (already available as primary key)
- $result = db_query_parsed(_drop_index('admin', 'username'));
- $result = db_query_parsed(_drop_index('alias', 'address'));
- $result = db_query_parsed(_drop_index('domain', 'domain'));
- $result = db_query_parsed(_drop_index('mailbox', 'username'));
+ $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
@@ -337,9 +338,9 @@
$table_domain = table_by_key ('domain');
$table_mailbox = table_by_key ('mailbox');
- $result = db_query_parsed("ALTER TABLE $table_domain ALTER COLUMN `quota` bigint(20) NOT NULL default '0'", TRUE);
- $result = db_query_parsed("ALTER TABLE $table_mailbox ALTER COLUMN `maxquota` bigint(20) NOT NULL default '0'", TRUE);
- $result = db_query_parsed("ALTER TABLE $table_mailbox ALTER COLUMN `quota` bigint(20) NOT NULL default '0'", TRUE);
+ $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);
}
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <chr...@us...> - 2007-11-03 22:46:16
|
Revision: 182
http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=182&view=rev
Author: christian_boltz
Date: 2007-11-03 15:46:20 -0700 (Sat, 03 Nov 2007)
Log Message:
-----------
- upgrade_90(): replace spaces with underscore in log.action
(change was caused by translatable logging)
Modified Paths:
--------------
trunk/upgrade.php
Modified: trunk/upgrade.php
===================================================================
--- trunk/upgrade.php 2007-11-03 22:24:34 UTC (rev 181)
+++ trunk/upgrade.php 2007-11-03 22:46:20 UTC (rev 182)
@@ -333,6 +333,13 @@
);
}
+function upgrade_90() {
+ # translatable logging
+ # old format: "create alias"
+ # new format: "create_alias"
+ $result = db_query_parsed("UPDATE " . table_by_key ('log') . " SET action = REPLACE(action,' ','_')", TRUE);
+}
+
function upgrade_169_mysql() { # MySQL only
# allow quota > 2 GB
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <chr...@us...> - 2007-11-05 22:06:58
|
Revision: 201
http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=201&view=rev
Author: christian_boltz
Date: 2007-11-05 14:07:02 -0800 (Mon, 05 Nov 2007)
Log Message:
-----------
- completed upgrade_81_mysql() (several changes in vacation table)
Modified Paths:
--------------
trunk/upgrade.php
Modified: trunk/upgrade.php
===================================================================
--- trunk/upgrade.php 2007-11-05 21:23:37 UTC (rev 200)
+++ trunk/upgrade.php 2007-11-05 22:07:02 UTC (rev 201)
@@ -294,31 +294,26 @@
}
function upgrade_81_mysql() { # MySQL only
-/* TODO
+ $table_vacation = table_by_key ('vacation');
+ $table_vacation_notification = table_by_key('vacation_notification');
-table vacation -
-- all varchar + text fields changed to utf8
-- active was tinyint (1), now (4)
-- ENGINE changed
-- DEFAULT CHARSET changed
-- COLLATE changed
+ $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` ENGINE = INNODB
+ "));
-diff:
- - `body` text NOT NULL default '',
- - `cache` text NOT NULL default '',
- + body text NOT NULL,
- + cache text NOT NULL,
- - `active` tinyint(1) NOT NULL default '1',
- + active tinyint(4) NOT NULL default '1', -> boolean are usually tinyint(1)
- -) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Vacation';
- +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci TYPE=InnoDB COMMENT='Postfix Admin - Virtual Vacation' ;
+ foreach ($all_sql as $sql) {
+ $result = db_query_parsed($sql, TRUE);
+ }
-
-
- */
-
db_query_parsed(
- " CREATE TABLE {IF_NOT_EXISTS} vacation_notification (
+ " CREATE TABLE {IF_NOT_EXISTS} $table_vacation_notification (
on_vacation varchar(255) NOT NULL,
notified varchar(255) NOT NULL,
notified_at timestamp NOT NULL default now(),
@@ -358,11 +353,12 @@
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
MySQL:
-* vacation: DROP INDEX email
+* vacation:
+ - DROP INDEX email
+ - 'cache' field might be obsolete with vacation_notification - needs to be checked!
* vacation_notification:
- DEFAULT CHARSET and COLLATE should be changed
- change all varchar fields to latin1 (email addresses don't contain utf8 characters)
-* remove all DROP TABLE statements from DATABASE_*
*/
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <chr...@us...> - 2007-11-05 23:23:23
|
Revision: 202
http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=202&view=rev
Author: christian_boltz
Date: 2007-11-05 15:23:19 -0800 (Mon, 05 Nov 2007)
Log Message:
-----------
- rename placeholder {UTF_8} to the more obvious {UTF-8}
This also fixes my last commit ;-)
Modified Paths:
--------------
trunk/upgrade.php
Modified: trunk/upgrade.php
===================================================================
--- trunk/upgrade.php 2007-11-05 22:07:02 UTC (rev 201)
+++ trunk/upgrade.php 2007-11-05 23:23:19 UTC (rev 202)
@@ -93,7 +93,7 @@
'{UNSIGNED}' => 'unsigned' ,
'{FULLTEXT}' => 'FULLTEXT',
'{BOOLEAN}' => 'tinyint(1) NOT NULL',
- '{UTF_8}' => '/*!40100 CHARACTER SET utf8 COLLATE utf8_unicode_ci */',
+ '{UTF-8}' => '/*!40100 CHARACTER SET utf8 COLLATE utf8_unicode_ci */',
'{LATIN1}' => '/*!40100 CHARACTER SET latin1 COLLATE latin1_swedish_ci */',
'{IF_NOT_EXISTS}' => 'IF NOT EXISTS',
'{RENAME_COLUMN}' => 'CHANGE COLUMN',
@@ -107,7 +107,7 @@
'{UNSIGNED}' => '',
'{FULLTEXT}' => '',
'{BOOLEAN}' => 'BOOLEAN NOT NULL',
- '{UTF_8}' => '', # TODO: UTF_8 is simply ignored.
+ '{UTF-8}' => '', # TODO: UTF-8 is simply ignored.
'{LATIN1}' => '', # TODO: same for latin1
'{IF_NOT_EXISTS}' => 'IF NOT EXISTS', # TODO: does this work with PgSQL?
'{RENAME_COLUMN}' => 'CHANGE COLUMN', # TODO: probably wrong
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <chr...@us...> - 2007-11-15 20:55:05
|
Revision: 231
http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=231&view=rev
Author: christian_boltz
Date: 2007-11-15 12:55:01 -0800 (Thu, 15 Nov 2007)
Log Message:
-----------
upgrade.php:
- change "edit_alias_state" to "edit_alias_active" in log table (was changed in r90)
https://sourceforge.net/tracker/index.php?func=detail&aid=1830376&group_id=191583&atid=937964
Modified Paths:
--------------
trunk/upgrade.php
Modified: trunk/upgrade.php
===================================================================
--- trunk/upgrade.php 2007-11-15 20:47:42 UTC (rev 230)
+++ trunk/upgrade.php 2007-11-15 20:55:01 UTC (rev 231)
@@ -333,6 +333,8 @@
# old format: "create alias"
# new format: "create_alias"
$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);
}
function upgrade_169_mysql() { # MySQL only
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <Gin...@us...> - 2007-12-17 07:02:41
|
Revision: 271
http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=271&view=rev
Author: GingerDog
Date: 2007-12-16 23:02:45 -0800 (Sun, 16 Dec 2007)
Log Message:
-----------
upgrade.php: implemented some of the postgresql functions; have not tested it yet though
Modified Paths:
--------------
trunk/upgrade.php
Modified: trunk/upgrade.php
===================================================================
--- trunk/upgrade.php 2007-12-17 07:01:50 UTC (rev 270)
+++ trunk/upgrade.php 2007-12-17 07:02:45 UTC (rev 271)
@@ -101,20 +101,20 @@
$sql = "$sql $attach_mysql";
} elseif($CONF['database_type'] == 'pgsql') {
- static $replace = array(
+ $replace = array(
'{AUTOINCREMENT}' => 'SERIAL',
'{PRIMARY}' => 'primary key',
'{UNSIGNED}' => '',
'{FULLTEXT}' => '',
'{BOOLEAN}' => 'BOOLEAN NOT NULL',
'{UTF-8}' => '', # TODO: UTF-8 is simply ignored.
- '{LATIN1}' => '', # TODO: same for latin1
- '{IF_NOT_EXISTS}' => 'IF NOT EXISTS', # TODO: does this work with PgSQL?
- '{RENAME_COLUMN}' => 'CHANGE COLUMN', # TODO: probably wrong
- 'int(1)' => 'int2',
- 'int(10)' => 'int4',
- 'int(11)' => 'int4',
- 'int(4)' => 'int4',
+ '{LATIN1}' => '', # TODO: same for latin1
+ '{IF_NOT_EXISTS}' => '', # TODO: does this work with PgSQL? NO
+ '{RENAME_COLUMN}' => 'ALTER COLUMN', # PgSQL : ALTER TABLE x RENAME x TO y
+ 'int(1)' => 'int',
+ 'int(10)' => 'int',
+ 'int(11)' => 'int',
+ 'int(4)' => 'int',
);
} else {
@@ -141,7 +141,7 @@
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"; # TODO: on which table?!
+ return "DROP INDEX $index"; # Index names are unique with a DB for PostgreSQL
} else {
echo "Sorry, unsupported database type " . $conf['database_type'];
exit;
@@ -160,15 +160,23 @@
echo "upgrade_1";
}
-function upgrade_2() {
+function upgrade_2_mysql() {
# upgrade pre-2.1 database
# from TABLE_BACKUP_MX.TXT
$table_domain = table_by_key ('domain');
$result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN transport VARCHAR(255) AFTER maxquota;", TRUE);
+ // don't think PGSQL supports 'AFTER transport'
$result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN backupmx {BOOLEAN} DEFAULT {BOOL_FALSE} AFTER transport;", TRUE);
}
-function upgrade_3() {
+function upgrade_2_pgsql() {
+ $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');
@@ -177,6 +185,8 @@
$table_mailbox = table_by_key ('mailbox');
$table_vacation = table_by_key ('vacation');
+ // these will not work on PostgreSQL; syntax is :
+ // ALTER TABLE foo RENAME f1 TO f2
$all_sql = split("\n", trim("
ALTER TABLE $table_admin {RENAME_COLUMN} create_date created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
ALTER TABLE $table_admin {RENAME_COLUMN} change_date modified DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
@@ -213,76 +223,66 @@
function upgrade_4_pgsql() { # PgSQL only
# changes between 2.1 and moving to sourceforge
+ $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');
-/* TODO
+ $result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN quota int NOT NULL default '0'");
-Changes in DATABASE_PGSQL.TXT: (in diff format - "-" means removed, "+" means added)
+ $result = db_query_parsed("ALTER TABLE $table_domain ALTER COLUMN domain DROP DEFAULT");
+ $result = db_query_parsed("CREATE INDEX domain_domain_active ON domain(domain,active)");
-TABLE domain
-- domain character varying(255) NOT NULL default '',
-+ domain character varying(255) NOT NULL,
-+ quota integer NOT NULL default 0,
+ $result = db_query_parsed("ALTER TABLE $table_admin 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");
+ $result = db_query_parsed("CREATE INDEX alias_address_active ON alias(address,active)");
-+CREATE INDEX domain_domain_active ON domain(domain,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");
-TABLE "admin"
-- "username" character varying(255) NOT NULL default '',
-+ "username" character varying(255) NOT NULL,
+ $result = db_query_parsed("BEGIN;
+ ALTER TABLE $table_log RENAME COLUMN data TO data_old;
+ ALTER TABLE $table_log ADD COLUMN data TYPE text NOT NULL default '';
+ UPDATE $table_log SET data = CAST(data_old AS text);
+ ALTER TABLE $table_log DROP COLUMN data_old;
+ COMMIT;");
-
-TABLE alias
-- address character varying(255) NOT NULL default '',
-+ address character varying(255) NOT NULL,
-- domain character varying(255) NOT NULL default '',
-+ domain character varying(255) NOT NULL REFERENCES domain,
+ $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");
-+CREATE INDEX alias_address_active ON alias(address,active);
-
-TABLE domain_admins
-- username character varying(255) NOT NULL default '',
-+ username character varying(255) NOT NULL,
-- domain character varying(255) NOT NULL default '',
-+ domain character varying(255) NOT NULL REFERENCES domain,
+ $result = db_query_parsed("BEGIN;
+ ALTER TABLE $table_mailbox RENAME COLUMN domain TO domain_old;
+ ALTER TABLE $table_mailbox ADD COLUMN domain TYPE varchar(255) REFERENCES domain (domain);
+ UPDATE $table_mailbox SET domain = domain_old;
+ ALTER TABLE $table_mailbox DROP COLUMN domain_old;
+ CREATE INDEX mailbox_username_active ON mailbox(username,active);
+ COMMIT;");
-TABLE log
-- data character varying(255) NOT NULL default ''
-+ data text NOT NULL default ''
-
-TABLE mailbox
-- username character varying(255) NOT NULL default '',
-+ username character varying(255) NOT NULL,
-- domain character varying(255) NOT NULL default '',
-+ domain character varying(255) NOT NULL REFERENCES domain,
-+CREATE INDEX mailbox_username_active ON mailbox(username,active);
-
-TABLE vacation
-- email character varying(255) NOT NULL default '',
-+ email character varying(255) PRIMARY KEY,
-- body text NOT NULL,
-+ body text NOT NULL DEFAULT '',
-- cache text NOT NULL,
-+ cache text NOT NULL DEFAULT '',
-- domain character varying(255) NOT NULL default '',
-+ "domain" character varying(255) NOT NULL REFERENCES "domain",
-- active boolean NOT NULL default true,
-+ active boolean DEFAULT true NOT NULL
-- Constraint "vacation_key" Primary Key ("email")
+ $result = db_query_parsed("ALTER TABLE $table_vacation ALTER COLUMN body SET DEFAULT = ''");
+ $result = db_query_parsed("ALTER TABLE $table_vacation DROP COLUMN cache");
--COMMENT ON TABLE vacation IS 'Postfix Admin - Virtual Vacation';
+ $result = db_query_parsed("ALTER TABLE $table_vacation RENAME COLUMN domain to domain_old");
+ $result = db_query_parsed("ALTER TABLE $table_vacation ADD COLUMN domain TYPE character varying(255) REFERENCES domain ");
+ $result = db_query_parsed("UPDATE $table_vacation SET domain = domain_old");
+ $result = db_query_parsed("ALTER TABLE $table_vacation DROP COLUMN domain_old");
-+CREATE INDEX vacation_email_active ON vacation(email,active);
+ $result = db_query_parsed("CREATE INDEX vacation_email_active ON vacation(email,active)");
-+CREATE TABLE vacation_notification (
-+ on_vacation character varying(255) NOT NULL REFERENCES 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)
-+);
+ $result = db_query_parsed("
+CREATE TABLE vacation_notification (
+ on_vacation character varying(255) NOT NULL REFERENCES 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)
+);");
-*/
}
function upgrade_79_mysql() { # MySQL only
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <Gin...@us...> - 2007-12-29 13:51:16
|
Revision: 277
http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=277&view=rev
Author: GingerDog
Date: 2007-12-29 05:51:19 -0800 (Sat, 29 Dec 2007)
Log Message:
-----------
upgrade.php: should work for postgresql now
Modified Paths:
--------------
trunk/upgrade.php
Modified: trunk/upgrade.php
===================================================================
--- trunk/upgrade.php 2007-12-28 21:45:33 UTC (rev 276)
+++ trunk/upgrade.php 2007-12-29 13:51:19 UTC (rev 277)
@@ -4,20 +4,33 @@
# Note: run with upgrade.php?debug=1 to see all SQL error messages
-# Not nice, but works:
-# We don't know if the config table exists, so we simply try to create it ;-)
-# Better solution (TODO): query the db to see if the 'config' table exists
-$sql = "
-CREATE TABLE {IF_NOT_EXISTS} " . table_by_key ('config') . "(
- `id` {AUTOINCREMENT} {PRIMARY},
- `name` VARCHAR(20) {LATIN1} NOT NULL DEFAULT '',
- `value` VARCHAR(20) {LATIN1} NOT NULL DEFAULT '',
- UNIQUE name ( `name` )
-)
-";
-db_query_parsed($sql, 0, " ENGINE = MYISAM COMMENT = 'PostfixAdmin settings'");
+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 = 'config'");
+ if($r['rows'] == 0) {
+ $pgsql = "
+ CREATE TABLE config (
+ 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_by_key ('config') . "(
+ `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 config WHERE name = 'version'";
@@ -32,6 +45,7 @@
} else {
$version = 0;
}
+
_do_upgrade($version);
@@ -40,7 +54,7 @@
$target_version = preg_replace('/[^0-9]/', '', '$Revision$');
if ($current_version >= $target_version) {
- # already up to date
+# already up to date
echo "up to date";
return true;
}
@@ -69,15 +83,17 @@
echo " done";
}
}
- # TODO: update version in config table after each change
- # TODO: this avoids problems in case the script hits the max_execution_time,
- # TODO: simply rerunning it will continue where it was stopped
+ // Update config table so we don't run the same query twice in the future.
+ $i = (int) $i;
+ $sql = "UPDATE config 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
*/
@@ -88,34 +104,34 @@
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 COLLATE utf8_unicode_ci */',
- '{LATIN1}' => '/*!40100 CHARACTER SET latin1 COLLATE latin1_swedish_ci */',
- '{IF_NOT_EXISTS}' => 'IF NOT EXISTS',
- '{RENAME_COLUMN}' => 'CHANGE COLUMN',
- );
+ '{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 COLLATE utf8_unicode_ci */',
+ '{LATIN1}' => '/*!40100 CHARACTER SET latin1 COLLATE latin1_swedish_ci */',
+ '{IF_NOT_EXISTS}' => 'IF NOT EXISTS',
+ '{RENAME_COLUMN}' => 'CHANGE COLUMN',
+ );
$sql = "$sql $attach_mysql";
} elseif($CONF['database_type'] == 'pgsql') {
$replace = array(
- '{AUTOINCREMENT}' => 'SERIAL',
- '{PRIMARY}' => 'primary key',
- '{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
- '{RENAME_COLUMN}' => 'ALTER COLUMN', # PgSQL : ALTER TABLE x RENAME x TO y
- 'int(1)' => 'int',
- 'int(10)' => 'int',
- 'int(11)' => 'int',
- 'int(4)' => 'int',
- );
+ '{AUTOINCREMENT}' => 'SERIAL',
+ '{PRIMARY}' => 'primary key',
+ '{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
+ '{RENAME_COLUMN}' => 'ALTER COLUMN', # PgSQL : ALTER TABLE x RENAME x TO y
+ 'int(1)' => 'int',
+ 'int(10)' => 'int',
+ 'int(11)' => 'int',
+ 'int(4)' => 'int',
+ );
} else {
echo "Sorry, unsupported database type " . $conf['database_type'];
@@ -149,20 +165,20 @@
}
function upgrade_1() {
- # inserting the version number is a good start ;-)
+# inserting the version number is a good start ;-)
db_insert(
- 'config',
- array(
- 'name' => 'version',
- 'value' => '1',
- )
- );
+ 'config',
+ array(
+ 'name' => 'version',
+ 'value' => '1',
+ )
+ );
echo "upgrade_1";
}
function upgrade_2_mysql() {
- # upgrade pre-2.1 database
- # from TABLE_BACKUP_MX.TXT
+# upgrade pre-2.1 database
+# from TABLE_BACKUP_MX.TXT
$table_domain = table_by_key ('domain');
$result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN transport VARCHAR(255) AFTER maxquota;", TRUE);
// don't think PGSQL supports 'AFTER transport'
@@ -173,12 +189,11 @@
$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
+# 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');
@@ -188,41 +203,41 @@
// these will not work on PostgreSQL; syntax is :
// ALTER TABLE foo RENAME f1 TO f2
$all_sql = split("\n", trim("
- ALTER TABLE $table_admin {RENAME_COLUMN} create_date created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
- ALTER TABLE $table_admin {RENAME_COLUMN} change_date modified DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
- ALTER TABLE $table_alias {RENAME_COLUMN} create_date created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
- ALTER TABLE $table_alias {RENAME_COLUMN} change_date modified DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
- ALTER TABLE $table_domain {RENAME_COLUMN} create_date created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
- ALTER TABLE $table_domain {RENAME_COLUMN} change_date modified DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
- ALTER TABLE $table_domain ADD COLUMN aliases INT(10) DEFAULT '-1' NOT NULL AFTER description;
- ALTER TABLE $table_domain ADD COLUMN mailboxes INT(10) DEFAULT '-1' NOT NULL AFTER aliases;
- ALTER TABLE $table_domain ADD COLUMN maxquota INT(10) DEFAULT '-1' NOT NULL AFTER mailboxes;
- ALTER TABLE $table_domain ADD COLUMN transport VARCHAR(255) AFTER maxquota;
- ALTER TABLE $table_domain ADD COLUMN backupmx TINYINT(1) DEFAULT '0' NOT NULL AFTER transport;
- ALTER TABLE $table_mailbox {RENAME_COLUMN} create_date created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
- ALTER TABLE $table_mailbox {RENAME_COLUMN} change_date modified DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
- ALTER TABLE $table_mailbox ADD COLUMN quota INT(10) DEFAULT '-1' NOT NULL AFTER maildir;
- ALTER TABLE $table_vacation ADD COLUMN domain VARCHAR(255) DEFAULT '' NOT NULL AFTER cache;
- ALTER TABLE $table_vacation ADD COLUMN created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL AFTER domain;
- ALTER TABLE $table_vacation ADD COLUMN active TINYINT(1) DEFAULT '1' NOT NULL AFTER created;
- ALTER TABLE $table_vacation DROP PRIMARY KEY
- ALTER TABLE $table_vacation ADD PRIMARY KEY(email)
- UPDATE $table_vacation SET domain=SUBSTRING_INDEX(email, '@', -1) WHERE email=email;
- "));
+ ALTER TABLE $table_admin {RENAME_COLUMN} create_date created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
+ ALTER TABLE $table_admin {RENAME_COLUMN} change_date modified DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
+ ALTER TABLE $table_alias {RENAME_COLUMN} create_date created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
+ ALTER TABLE $table_alias {RENAME_COLUMN} change_date modified DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
+ ALTER TABLE $table_domain {RENAME_COLUMN} create_date created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
+ ALTER TABLE $table_domain {RENAME_COLUMN} change_date modified DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
+ ALTER TABLE $table_domain ADD COLUMN aliases INT(10) DEFAULT '-1' NOT NULL AFTER description;
+ ALTER TABLE $table_domain ADD COLUMN mailboxes INT(10) DEFAULT '-1' NOT NULL AFTER aliases;
+ ALTER TABLE $table_domain ADD COLUMN maxquota INT(10) DEFAULT '-1' NOT NULL AFTER mailboxes;
+ ALTER TABLE $table_domain ADD COLUMN transport VARCHAR(255) AFTER maxquota;
+ ALTER TABLE $table_domain ADD COLUMN backupmx TINYINT(1) DEFAULT '0' NOT NULL AFTER transport;
+ ALTER TABLE $table_mailbox {RENAME_COLUMN} create_date created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
+ ALTER TABLE $table_mailbox {RENAME_COLUMN} change_date modified DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
+ ALTER TABLE $table_mailbox ADD COLUMN quota INT(10) DEFAULT '-1' NOT NULL AFTER maildir;
+ ALTER TABLE $table_vacation ADD COLUMN domain VARCHAR(255) DEFAULT '' NOT NULL AFTER cache;
+ ALTER TABLE $table_vacation ADD COLUMN created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL AFTER domain;
+ ALTER TABLE $table_vacation ADD COLUMN active TINYINT(1) DEFAULT '1' NOT NULL AFTER created;
+ ALTER TABLE $table_vacation DROP PRIMARY KEY
+ ALTER TABLE $table_vacation ADD PRIMARY KEY(email)
+ UPDATE $table_vacation SET domain=SUBSTRING_INDEX(email, '@', -1) WHERE email=email;
+ "));
- foreach ($all_sql as $sql) {
- $result = db_query_parsed($sql, TRUE);
- }
+ foreach ($all_sql as $sql) {
+ $result = db_query_parsed($sql, TRUE);
+ }
}
function upgrade_4_mysql() { # MySQL only
- # changes between 2.1 and moving to sourceforge
+# 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);
}
function upgrade_4_pgsql() { # PgSQL only
- # changes between 2.1 and moving to sourceforge
+# changes between 2.1 and moving to sourceforge
$table_domain = table_by_key('domain');
$table_admin = table_by_key('admin');
$table_alias = table_by_key('alias');
@@ -247,98 +262,98 @@
$result = db_query_parsed("BEGIN;
ALTER TABLE $table_log RENAME COLUMN data TO data_old;
- ALTER TABLE $table_log ADD COLUMN data TYPE text NOT NULL default '';
+ 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("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 TYPE varchar(255) REFERENCES domain (domain);
- UPDATE $table_mailbox SET domain = domain_old;
- ALTER TABLE $table_mailbox DROP COLUMN domain_old;
- CREATE INDEX mailbox_username_active ON mailbox(username,active);
- COMMIT;");
+ $result = db_query_parsed("BEGIN;
+ ALTER TABLE $table_mailbox RENAME COLUMN domain TO domain_old;
+ ALTER TABLE $table_mailbox ADD COLUMN domain TYPE varchar(255) REFERENCES domain (domain);
+ UPDATE $table_mailbox SET domain = domain_old;
+ ALTER TABLE $table_mailbox DROP COLUMN domain_old;
+ CREATE INDEX mailbox_username_active ON mailbox(username,active);
+ COMMIT;");
- $result = db_query_parsed("ALTER TABLE $table_vacation ALTER COLUMN body SET DEFAULT = ''");
- $result = db_query_parsed("ALTER TABLE $table_vacation DROP COLUMN cache");
+ $result = db_query_parsed("ALTER TABLE $table_vacation ALTER COLUMN body SET DEFAULT ''");
+ $result = db_query_parsed("ALTER TABLE $table_vacation DROP COLUMN cache");
- $result = db_query_parsed("ALTER TABLE $table_vacation RENAME COLUMN domain to domain_old");
- $result = db_query_parsed("ALTER TABLE $table_vacation ADD COLUMN domain TYPE character varying(255) REFERENCES domain ");
- $result = db_query_parsed("UPDATE $table_vacation SET domain = domain_old");
- $result = db_query_parsed("ALTER TABLE $table_vacation DROP COLUMN domain_old");
+ $result = db_query_parsed("ALTER TABLE $table_vacation RENAME COLUMN domain to domain_old");
+ $result = db_query_parsed("ALTER TABLE $table_vacation ADD COLUMN domain TYPE character varying(255) REFERENCES domain ");
+ $result = db_query_parsed("UPDATE $table_vacation SET domain = domain_old");
+ $result = db_query_parsed("ALTER TABLE $table_vacation DROP COLUMN domain_old");
- $result = db_query_parsed("CREATE INDEX vacation_email_active ON vacation(email,active)");
+ $result = db_query_parsed("CREATE INDEX vacation_email_active ON vacation(email,active)");
- $result = db_query_parsed("
-CREATE TABLE vacation_notification (
- on_vacation character varying(255) NOT NULL REFERENCES 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)
-);");
+ $result = db_query_parsed("
+ CREATE TABLE vacation_notification (
+ on_vacation character varying(255) NOT NULL REFERENCES 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)
+ );");
}
-function upgrade_79_mysql() { # MySQL only
- # drop useless indicies (already available as primary key)
- $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_79_mysql() { # MySQL only
+# drop useless indicies (already available as primary key)
+ $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` ENGINE = INNODB
- "));
+ 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` ENGINE = INNODB
+ "));
foreach ($all_sql as $sql) {
$result = db_query_parsed($sql, TRUE);
}
db_query_parsed(
- " CREATE TABLE {IF_NOT_EXISTS} $table_vacation_notification (
- on_vacation varchar(255) NOT NULL,
- notified varchar(255) NOT NULL,
- notified_at timestamp NOT NULL default now(),
- CONSTRAINT vacation_notification_pkey PRIMARY KEY(on_vacation, notified),
- FOREIGN KEY (on_vacation) REFERENCES vacation(email) ON DELETE CASCADE
- )
- ",
- TRUE,
- " ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci TYPE=InnoDB
+ " CREATE TABLE {IF_NOT_EXISTS} $table_vacation_notification (
+ on_vacation varchar(255) NOT NULL,
+ notified varchar(255) NOT NULL,
+ notified_at timestamp NOT NULL default now(),
+ CONSTRAINT vacation_notification_pkey PRIMARY KEY(on_vacation, notified),
+ FOREIGN KEY (on_vacation) REFERENCES vacation(email) ON DELETE CASCADE
+ )
+ ",
+ TRUE,
+ " ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci TYPE=InnoDB
COMMENT='Postfix Admin - Virtual Vacation Notifications'
- "
- );
+ "
+ );
}
function upgrade_90() {
- # translatable logging
- # old format: "create alias"
- # new format: "create_alias"
+# translatable logging
+# old format: "create alias"
+# new format: "create_alias"
$result = db_query_parsed("UPDATE " . table_by_key ('log') . " SET action = REPLACE(action,' ','_')", TRUE);
- # change edit_alias_state to edit_alias_active
+# 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);
}
function upgrade_169_mysql() { # MySQL only
- # allow quota > 2 GB
+# allow quota > 2 GB
$table_domain = table_by_key ('domain');
$table_mailbox = table_by_key ('mailbox');
@@ -349,20 +364,19 @@
/*
-TODO
+ TODO
Database changes that should be done:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
MySQL:
-* vacation:
- - DROP INDEX email
- - 'cache' field might be obsolete with vacation_notification - needs to be checked!
-* vacation_notification:
- - DEFAULT CHARSET and COLLATE should be changed
- - change all varchar fields to latin1 (email addresses don't contain utf8 characters)
+ * vacation:
+ - DROP INDEX email
+ - 'cache' field might be obsolete with vacation_notification - needs to be checked!
+ * vacation_notification:
+ - DEFAULT CHARSET and COLLATE should be changed
+ - change all varchar fields to latin1 (email addresses don't contain utf8 characters)
-*/
+ */
-/* vim: set expandtab softtabstop=4 tabstop=4 shiftwidth=4: */
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <Gin...@us...> - 2007-12-29 14:51:54
|
Revision: 278
http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=278&view=rev
Author: GingerDog
Date: 2007-12-29 06:51:58 -0800 (Sat, 29 Dec 2007)
Log Message:
-----------
upgrade.php: fix pgsql isms - i..e check that fields exist before trying to remove them, likewise for tables; minor syntax fixes in upgrade etc; tested and works for me
Modified Paths:
--------------
trunk/upgrade.php
Modified: trunk/upgrade.php
===================================================================
--- trunk/upgrade.php 2007-12-29 13:51:19 UTC (rev 277)
+++ trunk/upgrade.php 2007-12-29 14:51:58 UTC (rev 278)
@@ -1,11 +1,53 @@
<?php
require_once('common.php');
+// vim ts=4:sw=4:et
# 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' ";
+ echo $sql;
+ $r = db_query($sql);
+ $row = db_row($r['result']);
+ if($row) {
+ return true;
+ }
+ return false;
+}
+
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 = 'config'");
@@ -236,8 +278,10 @@
$result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN quota int(10) NOT NULL default '0' AFTER maxquota", TRUE);
}
-function upgrade_4_pgsql() { # PgSQL only
-# changes between 2.1 and moving to sourceforge
+/**
+ * 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');
@@ -246,68 +290,89 @@
$table_mailbox = table_by_key('mailbox');
$table_vacation = table_by_key('vacation');
- $result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN quota int NOT NULL default '0'");
+ 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");
- $result = db_query_parsed("CREATE INDEX domain_domain_active ON domain(domain,active)");
-
- $result = db_query_parsed("ALTER TABLE $table_admin ALTER COLUMN domain DROP DEFAULT");
+ if(!_pgsql_object_exists('domain_domain_active')) {
+ $result = db_query_parsed("CREATE INDEX domain_domain_active ON 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");
- $result = db_query_parsed("CREATE INDEX alias_address_active ON alias(address,active)");
+ if(!_pgsql_object_exists('alias_address_active')) {
+ $result = db_query_parsed("CREATE INDEX alias_address_active ON 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;
+ $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;");
+ 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("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 TYPE varchar(255) REFERENCES domain (domain);
- UPDATE $table_mailbox SET domain = domain_old;
- ALTER TABLE $table_mailbox DROP COLUMN domain_old;
- CREATE INDEX mailbox_username_active ON mailbox(username,active);
- COMMIT;");
+ $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 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 mailbox(username,active)');
+ }
- $result = db_query_parsed("ALTER TABLE $table_vacation ALTER COLUMN body SET DEFAULT ''");
- $result = db_query_parsed("ALTER TABLE $table_vacation DROP COLUMN cache");
+ $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("ALTER TABLE $table_vacation RENAME COLUMN domain to domain_old");
- $result = db_query_parsed("ALTER TABLE $table_vacation ADD COLUMN domain TYPE character varying(255) REFERENCES domain ");
- $result = db_query_parsed("UPDATE $table_vacation SET domain = domain_old");
- $result = db_query_parsed("ALTER TABLE $table_vacation DROP COLUMN domain_old");
+ $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 domain;
+ UPDATE $table_vacation SET domain = domain_old;
+ ALTER TABLE $table_vacation DROP COLUMN domain_old;
+ COMMIT;
+ ");
- $result = db_query_parsed("CREATE INDEX vacation_email_active ON vacation(email,active)");
+ if(!_pgsql_object_exists('vacation_email_active')) {
+ $result = db_query_parsed("CREATE INDEX vacation_email_active ON vacation(email,active)");
+ }
- $result = db_query_parsed("
- CREATE TABLE vacation_notification (
- on_vacation character varying(255) NOT NULL REFERENCES 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)
- );");
+ if(!_pgsql_object_exists('vacation_notification')) {
+ $result = db_query_parsed("
+ CREATE TABLE vacation_notification (
+ on_vacation character varying(255) NOT NULL REFERENCES 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)
+ );");
+ }
+}
-
+/**
+ * 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_79_mysql() { # MySQL only
-# drop useless indicies (already available as primary key)
- $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');
@@ -343,17 +408,19 @@
);
}
+/**
+ * Make logging translatable - i.e. create alias => create_alias
+ */
function upgrade_90() {
-# translatable logging
-# old format: "create alias"
-# new format: "create_alias"
$result = db_query_parsed("UPDATE " . table_by_key ('log') . " SET action = REPLACE(action,' ','_')", TRUE);
-# change edit_alias_state to edit_alias_active
+ # 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);
}
-function upgrade_169_mysql() { # MySQL only
-# allow quota > 2 GB
+/**
+ * MySQL only allow quota > 2 GB
+ */
+function upgrade_169_mysql() {
$table_domain = table_by_key ('domain');
$table_mailbox = table_by_key ('mailbox');
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <Gin...@us...> - 2008-01-03 22:05:50
|
Revision: 286
http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=286&view=rev
Author: GingerDog
Date: 2008-01-03 13:58:02 -0800 (Thu, 03 Jan 2008)
Log Message:
-----------
upgrade.php: needless echo removed; tested on another PostgreSQL database - worked fine
Modified Paths:
--------------
trunk/upgrade.php
Modified: trunk/upgrade.php
===================================================================
--- trunk/upgrade.php 2008-01-03 21:51:08 UTC (rev 285)
+++ trunk/upgrade.php 2008-01-03 21:58:02 UTC (rev 286)
@@ -38,7 +38,7 @@
AND pg_catalog.pg_table_is_visible(c.oid)
)
AND a.attname = '$field' ";
- echo $sql;
+// echo $sql;
$r = db_query($sql);
$row = db_row($r['result']);
if($row) {
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <chr...@us...> - 2008-04-06 23:12:32
|
Revision: 318
http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=318&view=rev
Author: christian_boltz
Date: 2008-04-06 16:12:29 -0700 (Sun, 06 Apr 2008)
Log Message:
-----------
upgrade.php:
- creation of vacation_notification table moved to upgrade_318_mysql
because the query in upgrade_81_mysql was broken (key length vs. utf8
charset)
- added upgrade_318_mysql which finally creates a working
vacation_notification table
NOTE: Users who used workarounds to create this table before might
need to drop it manually before upgrade works.
This should fix
http://sourceforge.net/tracker/index.php?func=detail&aid=1828857&group_id=191583&atid=937964
and probably also
http://sourceforge.net/tracker/index.php?func=detail&aid=1875897&group_id=191583&atid=937964
additional minor fixes:
- "create table config" for pgsql now uses table_by_key()
- some whitespace changes
- set default charset (latin1) in upgrade_5_mysql (instead of using
the system default)
Modified Paths:
--------------
trunk/upgrade.php
Modified: trunk/upgrade.php
===================================================================
--- trunk/upgrade.php 2008-04-06 21:33:30 UTC (rev 317)
+++ trunk/upgrade.php 2008-04-06 23:12:29 UTC (rev 318)
@@ -53,7 +53,7 @@
$r = db_query("SELECT relname FROM pg_class WHERE relname = 'config'");
if($r['rows'] == 0) {
$pgsql = "
- CREATE TABLE config (
+ CREATE TABLE " . table_by_key ('config') . " (
id SERIAL,
name VARCHAR(20) NOT NULL UNIQUE,
value VARCHAR(20) NOT NULL,
@@ -69,8 +69,8 @@
`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'");
}
@@ -369,6 +369,13 @@
}
}
+
+# 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
@@ -387,7 +394,7 @@
`active` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`username`),
KEY username (`username`)
- ) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Admins';
+ ) TYPE=MyISAM DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Admins';
");
$result = db_query_parsed("
@@ -400,7 +407,7 @@
`active` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`address`),
KEY address (`address`)
- ) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Aliases';
+ ) TYPE=MyISAM DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Aliases';
");
$result = db_query_parsed("
@@ -418,7 +425,7 @@
`active` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`domain`),
KEY domain (`domain`)
- ) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Domains';
+ ) TYPE=MyISAM DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Domains';
");
$result = db_query_parsed("
@@ -428,7 +435,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';
+ ) TYPE=MyISAM DEFAULT {LATIN1} COMMENT='Postfix Admin - Domain Admins';
");
$result = db_query_parsed("
@@ -439,7 +446,7 @@
`action` varchar(255) NOT NULL default '',
`data` varchar(255) NOT NULL default '',
KEY timestamp (`timestamp`)
- ) TYPE=MyISAM COMMENT='Postfix Admin - Log';
+ ) TYPE=MyISAM DEFAULT {LATIN1} COMMENT='Postfix Admin - Log';
");
$result = db_query_parsed("
@@ -455,7 +462,7 @@
`active` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`username`),
KEY username (`username`)
- ) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Mailboxes';
+ ) TYPE=MyISAM DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Mailboxes';
");
$result = db_query_parsed("
@@ -469,10 +476,8 @@
`active` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`email`),
KEY email (`email`)
- ) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Vacation';
+ ) TYPE=MyISAM DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Vacation';
");
- # Possible errors that can be ignored:
- # - (none)
}
/**
@@ -498,26 +503,14 @@
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
- "));
+ "));
foreach ($all_sql as $sql) {
$result = db_query_parsed($sql, TRUE);
}
- db_query_parsed(
- " CREATE TABLE {IF_NOT_EXISTS} $table_vacation_notification (
- on_vacation varchar(255) NOT NULL,
- notified varchar(255) NOT NULL,
- notified_at timestamp NOT NULL default now(),
- CONSTRAINT vacation_notification_pkey PRIMARY KEY(on_vacation, notified),
- FOREIGN KEY (on_vacation) REFERENCES vacation(email) ON DELETE CASCADE
- )
- ",
- TRUE,
- " ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci TYPE=InnoDB
- COMMENT='Postfix Admin - Virtual Vacation Notifications'
- "
- );
+ # creation of vacation_notification table moved to upgrade_318_mysql because
+ # the query in this function was broken (key length vs. utf8 charset)
}
/**
@@ -542,6 +535,49 @@
}
+/**
+ * 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');
+
+ # create table without constraint for now...
+ db_query_parsed( "
+ CREATE TABLE {IF_NOT_EXISTS} $table_vacation_notification (
+ on_vacation varchar(255) NOT NULL,
+ notified varchar(255) NOT NULL,
+ notified_at timestamp NOT NULL default now(),
+ PRIMARY KEY on_vacation (`on_vacation`, `notified`)
+ )
+ ENGINE=InnoDB DEFAULT {LATIN1} TYPE=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` DROP FOREIGN KEY `vacation_notification_pkey`
+ ALTER TABLE `$table_vacation_notification` CHANGE `on_vacation` `on_vacation` VARCHAR( 255 ) {LATIN1} NOT NULL
+ ALTER TABLE `$table_vacation_notification` CHANGE `notified` `notified` VARCHAR( 255 ) {LATIN1} NOT NULL
+ ALTER TABLE `$table_vacation_notification` DEFAULT {LATIN1}
+ "));
+ # 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 constraint...
+ $result = db_query_parsed("
+ ALTER TABLE $table_vacation_notification ADD CONSTRAINT `vacation_notification_pkey`
+ FOREIGN KEY (`on_vacation`) REFERENCES vacation(`email`) ON DELETE CASCADE
+ ");
+}
+
/*
TODO
@@ -549,15 +585,10 @@
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
MySQL:
- * vacation:
+* vacation:
- DROP INDEX email
- 'cache' field might be obsolete with vacation_notification - needs to be checked!
- * vacation_notification:
- - DEFAULT CHARSET and COLLATE should be changed
- - change all varchar fields to latin1 (email addresses don't contain utf8 characters)
* charset of equal fields MUST be the same (see bugreport)
-* if necessary, delete the CONSTRAINT before changing field type and create it again
- http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
*/
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <chr...@us...> - 2008-04-06 23:28:36
|
Revision: 319
http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=319&view=rev
Author: christian_boltz
Date: 2008-04-06 16:28:40 -0700 (Sun, 06 Apr 2008)
Log Message:
-----------
upgrade.php:
- print query (debug mode) _before_ executing it
- upgrade_318_mysql: include CONSTRAINT in CREATE TABLE - otherwise
dropping it afterwards fails...
Modified Paths:
--------------
trunk/upgrade.php
Modified: trunk/upgrade.php
===================================================================
--- trunk/upgrade.php 2008-04-06 23:12:29 UTC (rev 318)
+++ trunk/upgrade.php 2008-04-06 23:28:40 UTC (rev 319)
@@ -184,9 +184,11 @@
$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 "<p style='color:#999'>$query";
print "<div style='color:#f00'>" . $result['error'] . "</div>";
}
return $result;
@@ -543,13 +545,14 @@
function upgrade_318_mysql() {
$table_vacation_notification = table_by_key('vacation_notification');
- # create table without constraint for now...
db_query_parsed( "
CREATE TABLE {IF_NOT_EXISTS} $table_vacation_notification (
on_vacation varchar(255) NOT NULL,
notified varchar(255) NOT NULL,
notified_at timestamp NOT NULL default now(),
- PRIMARY KEY on_vacation (`on_vacation`, `notified`)
+ PRIMARY KEY on_vacation (`on_vacation`, `notified`),
+ CONSTRAINT `vacation_notification_pkey`
+ FOREIGN KEY (`on_vacation`) REFERENCES vacation(`email`) ON DELETE CASCADE
)
ENGINE=InnoDB DEFAULT {LATIN1} TYPE=InnoDB
COMMENT='Postfix Admin - Virtual Vacation Notifications'
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <Gin...@us...> - 2008-04-22 23:05:23
|
Revision: 339
http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=339&view=rev
Author: GingerDog
Date: 2008-04-22 16:05:29 -0700 (Tue, 22 Apr 2008)
Log Message:
-----------
upgrade.php: fix bug in mysql vacation notification table as reported by gabbs on irc (thanks!)
Modified Paths:
--------------
trunk/upgrade.php
Modified: trunk/upgrade.php
===================================================================
--- trunk/upgrade.php 2008-04-20 19:59:06 UTC (rev 338)
+++ trunk/upgrade.php 2008-04-22 23:05:29 UTC (rev 339)
@@ -670,7 +670,7 @@
CREATE TABLE {IF_NOT_EXISTS} $table_vacation_notification (
on_vacation varchar(255) NOT NULL,
notified varchar(255) NOT NULL,
- notified_at timestamp NOT NULL default now(),
+ notified_at timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY on_vacation (`on_vacation`, `notified`),
CONSTRAINT `vacation_notification_pkey`
FOREIGN KEY (`on_vacation`) REFERENCES vacation(`email`) ON DELETE CASCADE
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <Gin...@us...> - 2008-04-23 15:25:38
|
Revision: 340
http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=340&view=rev
Author: GingerDog
Date: 2008-04-23 08:25:31 -0700 (Wed, 23 Apr 2008)
Log Message:
-----------
upgrade.php: fix issue of mysql tables not being created on initial install
Modified Paths:
--------------
trunk/upgrade.php
Modified: trunk/upgrade.php
===================================================================
--- trunk/upgrade.php 2008-04-22 23:05:29 UTC (rev 339)
+++ trunk/upgrade.php 2008-04-23 15:25:31 UTC (rev 340)
@@ -220,6 +220,105 @@
echo "upgrade_1";
}
+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`)
+ ) TYPE=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`)
+ ) TYPE=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`)
+ ) TYPE=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`)
+ ) TYPE=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`)
+ ) TYPE=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`)
+ ) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Mailboxes';";
+
+ $sql[] = "
+ CREATE TABLE {IF_NOT_EXISTS} $vacation (
+ email varchar(255) NOT NULL default '',
+ subject varchar(255) NOT NULL default '',
+ body text NOT NULL,
+ cache text NOT NULL,
+ domain varchar(255) NOT NULL default '',
+ created datetime NOT NULL default '0000-00-00 00:00:00',
+ active tinyint(4) NOT NULL default '1',
+ PRIMARY KEY (email),
+ KEY email (email)
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci TYPE=InnoDB 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
@@ -671,7 +770,7 @@
on_vacation varchar(255) NOT NULL,
notified varchar(255) NOT NULL,
notified_at timestamp NOT NULL default CURRENT_TIMESTAMP,
- PRIMARY KEY on_vacation (`on_vacation`, `notified`),
+ PRIMARY KEY on_vacation (`on_vacation`, `notified`),
CONSTRAINT `vacation_notification_pkey`
FOREIGN KEY (`on_vacation`) REFERENCES vacation(`email`) ON DELETE CASCADE
)
@@ -701,18 +800,3 @@
FOREIGN KEY (`on_vacation`) REFERENCES vacation(`email`) ON DELETE CASCADE
");
}
-
-/*
- TODO
-
- Database changes that should be done:
- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-
-MySQL:
-* vacation:
- - DROP INDEX email
- - 'cache' field might be obsolete with vacation_notification - needs to be checked!
-* charset of equal fields MUST be the same (see bugreport)
- */
-
-
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <Gin...@us...> - 2008-04-26 21:55:55
|
Revision: 352
http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=352&view=rev
Author: GingerDog
Date: 2008-04-26 14:56:02 -0700 (Sat, 26 Apr 2008)
Log Message:
-----------
upgrade.php: reindent; fix postgresql fetchmail stuff
Modified Paths:
--------------
trunk/upgrade.php
Modified: trunk/upgrade.php
===================================================================
--- trunk/upgrade.php 2008-04-26 21:55:16 UTC (rev 351)
+++ trunk/upgrade.php 2008-04-26 21:56:02 UTC (rev 352)
@@ -320,8 +320,8 @@
}
function upgrade_2_mysql() {
-# upgrade pre-2.1 database
-# from TABLE_BACKUP_MX.TXT
+ # upgrade pre-2.1 database
+ # from TABLE_BACKUP_MX.TXT
$table_domain = table_by_key ('domain');
$result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN transport VARCHAR(255) AFTER maxquota;", TRUE);
// don't think PGSQL supports 'AFTER transport'
@@ -469,7 +469,7 @@
// these will not work on PostgreSQL; syntax is :
// ALTER TABLE foo RENAME f1 TO f2
$all_sql = split("\n", trim("
- ALTER TABLE $table_admin {RENAME_COLUMN} create_date created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
+ ALTER TABLE $table_admin {RENAME_COLUMN} create_date created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
ALTER TABLE $table_admin {RENAME_COLUMN} change_date modified DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
ALTER TABLE $table_alias {RENAME_COLUMN} create_date created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
ALTER TABLE $table_alias {RENAME_COLUMN} change_date modified DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL;
@@ -487,8 +487,8 @@
ALTER TABLE $table_vacation ADD COLUMN created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL AFTER domain;
ALTER TABLE $table_vacation ADD COLUMN active TINYINT(1) DEFAULT '1' NOT NULL AFTER created;
ALTER TABLE $table_vacation DROP PRIMARY KEY
- ALTER TABLE $table_vacation ADD PRIMARY KEY(email)
- UPDATE $table_vacation SET domain=SUBSTRING_INDEX(email, '@', -1) WHERE email=email;
+ ALTER TABLE $table_vacation ADD PRIMARY KEY(email)
+ UPDATE $table_vacation SET domain=SUBSTRING_INDEX(email, '@', -1) WHERE email=email;
"));
foreach ($all_sql as $sql) {
@@ -539,57 +539,56 @@
$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;");
+ 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("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 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 mailbox(username,active)');
-}
+ $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 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 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("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 domain;
-UPDATE $table_vacation SET domain = domain_old;
-ALTER TABLE $table_vacation DROP COLUMN domain_old;
-COMMIT;
-");
+ $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 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 vacation(email,active)");
-}
+ if(!_pgsql_object_exists('vacation_email_active')) {
+ $result = db_query_parsed("CREATE INDEX vacation_email_active ON vacation(email,active)");
+ }
-if(!_pgsql_object_exists('vacation_notification')) {
- $result = db_query_parsed("
- CREATE TABLE vacation_notification (
- on_vacation character varying(255) NOT NULL REFERENCES 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)
-);");
+ if(!_pgsql_object_exists('vacation_notification')) {
+ $result = db_query_parsed("
+ CREATE TABLE vacation_notification (
+ on_vacation character varying(255) NOT NULL REFERENCES 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:
@@ -616,90 +615,89 @@
`active` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`username`),
KEY username (`username`)
-) TYPE=MyISAM DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Admins';
-");
+) TYPE=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`)
- ) TYPE=MyISAM DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Aliases';
-");
+ $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`)
+ ) TYPE=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`)
- ) TYPE=MyISAM DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Domains';
-");
+ $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`)
+ ) TYPE=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`)
- ) TYPE=MyISAM DEFAULT {LATIN1} COMMENT='Postfix Admin - Domain Admins';
-");
+ $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`)
+ ) TYPE=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`)
- ) TYPE=MyISAM DEFAULT {LATIN1} COMMENT='Postfix Admin - Log';
-");
+ $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`)
+ ) TYPE=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`)
- ) TYPE=MyISAM DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Mailboxes';
-");
+ $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`)
+ ) TYPE=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 default '',
- `subject` varchar(255) NOT NULL default '',
- `body` text NOT NULL default '',
- `cache` text 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',
- PRIMARY KEY (`email`),
-KEY email (`email`)
- ) TYPE=MyISAM DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Vacation';
-");
+ $result = db_query_parsed("
+ CREATE TABLE {IF_NOT_EXISTS} `" . table_by_key('vacation') . "` (
+ `email` varchar(255) NOT NULL default '',
+ `subject` varchar(255) NOT NULL default '',
+ `body` text NOT NULL default '',
+ `cache` text 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',
+ PRIMARY KEY (`email`),
+ KEY email (`email`)
+ ) TYPE=MyISAM DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Vacation';
+ ");
}
/**
@@ -724,7 +722,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` ENGINE = INNODB
"));
foreach ($all_sql as $sql) {
@@ -771,34 +769,33 @@
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 vacation(`email`) ON DELETE CASCADE
-)
-ENGINE=InnoDB DEFAULT {LATIN1} TYPE=InnoDB
-COMMENT='Postfix Admin - Virtual Vacation Notifications'
-");
+ CONSTRAINT `vacation_notification_pkey`
+ FOREIGN KEY (`on_vacation`) REFERENCES vacation(`email`) ON DELETE CASCADE
+ )
+ ENGINE=InnoDB DEFAULT {LATIN1} TYPE=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` DROP FOREIGN KEY `vacation_notification_pkey`
- ALTER TABLE `$table_vacation_notification` CHANGE `on_vacation` `on_vacation` VARCHAR( 255 ) {LATIN1} NOT NULL
- ALTER TABLE `$table_vacation_notification` CHANGE `notified` `notified` VARCHAR( 255 ) {LATIN1} NOT NULL
- ALTER TABLE `$table_vacation_notification` DEFAULT {LATIN1}
-"));
-# 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.
+ # in case someone has manually created the table with utf8 fields before:
+ $all_sql = split("\n", trim("
+ ALTER TABLE `$table_vacation_notification` DROP FOREIGN KEY `vacation_notification_pkey`
+ ALTER TABLE `$table_vacation_notification` CHANGE `on_vacation` `on_vacation` VARCHAR( 255 ) {LATIN1} NOT NULL
+ ALTER TABLE `$table_vacation_notification` CHANGE `notified` `notified` VARCHAR( 255 ) {LATIN1} NOT NULL
+ ALTER TABLE `$table_vacation_notification` DEFAULT {LATIN1}
+ "));
+ # 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);
-}
+ foreach ($all_sql as $sql) {
+ $result = db_query_parsed($sql);
+ }
-# create constraint...
-$result = db_query_parsed("
- ALTER TABLE $table_vacation_notification ADD CONSTRAINT `vacation_notification_pkey`
- FOREIGN KEY (`on_vacation`) REFERENCES vacation(`email`) ON DELETE CASCADE
- ");
+ # create constraint...
+ $result = db_query_parsed("
+ ALTER TABLE $table_vacation_notification ADD CONSTRAINT `vacation_notification_pkey`
+ FOREIGN KEY (`on_vacation`) REFERENCES vacation(`email`) ON DELETE CASCADE ");
}
@@ -807,10 +804,10 @@
*/
function upgrade_344_mysql() {
- $table_vacation_notification = table_by_key('vacation_notification');
+ $table_fetchmail = table_by_key('fetchmail');
db_query_parsed( "
- create table fetchmail(
+ create table $table_fetchmail(
id int(11) unsigned not null auto_increment,
mailbox varchar(255) not null default '',
src_server varchar(255) not null default '',
@@ -832,6 +829,38 @@
}
function upgrade_344_pgsql() {
- # TODO: create fetchmail table for 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.
+ }
}
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <chr...@us...> - 2008-05-26 21:05:47
|
Revision: 374
http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=374&view=rev
Author: christian_boltz
Date: 2008-05-26 14:05:51 -0700 (Mon, 26 May 2008)
Log Message:
-----------
upgrade.php
- upgrade_373_mysql: change domain.description and mailbox.name to utf-8
https://sourceforge.net/tracker/?func=detail&atid=937964&aid=1972703&group_id=191583
Modified Paths:
--------------
trunk/upgrade.php
Modified: trunk/upgrade.php
===================================================================
--- trunk/upgrade.php 2008-05-25 21:19:58 UTC (rev 373)
+++ trunk/upgrade.php 2008-05-26 21:05:51 UTC (rev 374)
@@ -953,3 +953,22 @@
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);
+ }
+}
+
+
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <chr...@us...> - 2008-05-26 21:12:44
|
Revision: 375
http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=375&view=rev
Author: christian_boltz
Date: 2008-05-26 14:12:47 -0700 (Mon, 26 May 2008)
Log Message:
-----------
upgrade.php:
- only include common.php if POSTFIXADMIN is not define()d
Avoids a "already defined" warning in setup.php
Modified Paths:
--------------
trunk/upgrade.php
Modified: trunk/upgrade.php
===================================================================
--- trunk/upgrade.php 2008-05-26 21:05:51 UTC (rev 374)
+++ trunk/upgrade.php 2008-05-26 21:12:47 UTC (rev 375)
@@ -1,5 +1,7 @@
<?php
-require_once('common.php');
+if(!defined('POSTFIXADMIN')) {
+ require_once('common.php');
+}
// vim ts=4:sw=4:et
# Note: run with upgrade.php?debug=1 to see all SQL error messages
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <Gin...@us...> - 2008-06-20 08:00:38
|
Revision: 392
http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=392&view=rev
Author: GingerDog
Date: 2008-06-20 01:00:44 -0700 (Fri, 20 Jun 2008)
Log Message:
-----------
update trunk also; this is a merge of r391 from branches/postfixadmin-2.2.1
Modified Paths:
--------------
trunk/upgrade.php
Modified: trunk/upgrade.php
===================================================================
--- trunk/upgrade.php 2008-06-20 07:54:14 UTC (rev 391)
+++ trunk/upgrade.php 2008-06-20 08:00:44 UTC (rev 392)
@@ -305,11 +305,11 @@
$sql[] = "
CREATE TABLE {IF_NOT_EXISTS} $vacation (
- email varchar(255) NOT NULL default '',
- subject varchar(255) NOT NULL default '',
+ email varchar(255) NOT NULL ,
+ subject varchar(255) NOT NULL,
body text NOT NULL,
cache text NOT NULL,
- domain varchar(255) NOT NULL default '',
+ 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),
@@ -432,9 +432,9 @@
db_query_parsed('
CREATE TABLE ' . table_by_key('vacation') . ' (
email character varying(255) PRIMARY KEY,
- subject character varying(255) NOT NULL DEFAULT \'\',
- body text NOT NULL DEFAULT \'\',
- cache text NOT NULL DEFAULT \'\',
+ subject character varying(255) NOT NULL,
+ body text NOT NULL ,
+ cache text NOT NULL ,
"domain" character varying(255) NOT NULL REFERENCES "domain",
created timestamp with time zone DEFAULT now(),
active boolean DEFAULT true NOT NULL
@@ -735,11 +735,11 @@
$result = db_query_parsed("
CREATE TABLE {IF_NOT_EXISTS} `" . table_by_key('vacation') . "` (
- `email` varchar(255) NOT NULL default '',
- `subject` varchar(255) NOT NULL default '',
- `body` text NOT NULL default '',
- `cache` text NOT NULL default '',
- `domain` varchar(255) NOT NULL default '',
+ `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`),
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X