SF.net SVN: postfixadmin: [317] trunk

[<chr...@us...>]

Revision: 317
          http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=317&view=rev
Author:   christian_boltz
Date:     2008-04-06 14:33:30 -0700 (Sun, 06 Apr 2008)

Log Message:
-----------
upgrade.php:
- integrated table creation, based on SVN r1 DATABASE_MYSQL.txt
- added some comments about possible errors that can be ignored
  (I'm dreaming of having an error "whitelist" and displaying 
  all other errors - but not for 2.2 ;-)

DATABASE_MYSQL.TXT
- added up-to-date database setup instructions
- disabled all SQL code (will be deleted later)

Modified Paths:
--------------
    trunk/DATABASE_MYSQL.TXT
    trunk/upgrade.php

Modified: trunk/DATABASE_MYSQL.TXT
===================================================================
--- trunk/DATABASE_MYSQL.TXT	2008-04-03 22:16:15 UTC (rev 316)
+++ trunk/DATABASE_MYSQL.TXT	2008-04-06 21:33:30 UTC (rev 317)
@@ -2,7 +2,34 @@
 # Postfix Admin
 # Licensed under GPL for more info check LICENSE.TXT
 #
+#
+# To setup the MySQL database, do the following steps:
+# - create a MySQL database 
+#       CREATE DATABASE postfix;
+# - create a MySQL user that has all permissions on this database:
+#       CREATE USER 'postfix'@'localhost' IDENTIFIED BY 'choose_a_password';
+#       GRANT ALL PRIVILEGES ON `postfix` . * TO 'postfix'@'localhost';
+# - edit config.inc.php to suit your needs
+# - run upgrade.php to create the needed tables (yes, the filename is chosen 
+#   badly in this case ;-)
+# - run setup.php
 
+
+
+
+
+/*
+
+ ***********************************************************************
+ * everything below is probably outdated and should no longer be used. *
+ * Therefore it is commented out ;-)                                   *
+ ***********************************************************************
+
+
+
+
+
+
 # This is the complete MySQL database structure for Postfix Admin.
 # If you are installing from scratch you can use this file otherwise you
 # need to use the TABLE_CHANGES.TXT or TABLE_BACKUP_MX.TXT that comes with Postfix Admin.

Modified: trunk/upgrade.php
===================================================================
--- trunk/upgrade.php	2008-04-03 22:16:15 UTC (rev 316)
+++ trunk/upgrade.php	2008-04-06 21:33:30 UTC (rev 317)
@@ -225,6 +225,8 @@
     $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);
+    # possible errors that can be ignored:
+    # - Invalid query: Table 'postfix.domain' doesn't exist
 }
 
 function upgrade_2_pgsql() {
@@ -265,17 +267,21 @@
                 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);
+    }
+    # Possible errors that can be ignored:
+    # - Invalid query: Table 'postfix.*' doesn't exist
 }
 
 function upgrade_4_mysql() { # MySQL only
 # changes between 2.1 and moving to sourceforge
     $table_domain = table_by_key ('domain');
     $result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN quota int(10) NOT NULL default '0' AFTER maxquota", TRUE);
+    # Possible errors that can be ignored:
+    # - Invalid query: Table 'postfix.domain' doesn't exist
 }
 
 /**
@@ -364,6 +370,112 @@
 }
 
 /**
+ * create tables
+ * version: Sourceforge SVN r1 of DATABASE_MYSQL.txt
+ * changes compared to DATABASE_MYSQL.txt:
+ * - removed MySQL user and database creation
+ * - removed creation of default superadmin
+ */
+function upgrade_5_mysql() {
+
+    $result = db_query_parsed("
+        CREATE TABLE {IF_NOT_EXISTS} `" . table_by_key('admin') . "` (
+          `username` varchar(255) NOT NULL default '',
+          `password` varchar(255) NOT NULL default '',
+          `created` datetime NOT NULL default '0000-00-00 00:00:00',
+          `modified` datetime NOT NULL default '0000-00-00 00:00:00',
+          `active` tinyint(1) NOT NULL default '1',
+          PRIMARY KEY  (`username`),
+          KEY username (`username`)
+        ) TYPE=MyISAM 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 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 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 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 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 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 COMMENT='Postfix Admin - Virtual Vacation';
+    ");
+    # Possible errors that can be ignored:
+    # - (none)
+}
+
+/**
  * drop useless indicies (already available as primary key)
  */
 function upgrade_79_mysql() { # MySQL only
@@ -443,7 +555,9 @@
  * 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.