You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1935 lines
75 KiB
PHP
1935 lines
75 KiB
PHP
<?php
|
|
/* vim: set expandtab softtabstop=4 tabstop=4 shiftwidth=4: */
|
|
|
|
# Note: run with upgrade.php?debug=1 to see all SQL error messages
|
|
|
|
if (!defined('POSTFIXADMIN')) {
|
|
require_once('common.php');
|
|
}
|
|
|
|
if (empty($CONF)) {
|
|
die("config.inc.php seems invalid");
|
|
}
|
|
|
|
/**
|
|
* 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_one($sql);
|
|
return !empty($r);
|
|
}
|
|
|
|
/**
|
|
* @param string $table
|
|
* @param string $field
|
|
* @return bool
|
|
*/
|
|
function _pgsql_field_exists($table, $field) {
|
|
# $table = table_by_key($table); # _pgsql_field_exists is always called with the expanded table name - don't expand it twice
|
|
$sql = '
|
|
SELECT
|
|
a.attname,
|
|
pg_catalog.format_type(a.atttypid, a.atttypmod) AS "Datatype"
|
|
FROM
|
|
pg_catalog.pg_attribute a
|
|
WHERE
|
|
a.attnum > 0
|
|
AND NOT a.attisdropped
|
|
AND a.attrelid = (
|
|
SELECT c.oid
|
|
FROM pg_catalog.pg_class c
|
|
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
|
|
WHERE c.relname ~ ' . "'^($table)\$'
|
|
AND pg_catalog.pg_table_is_visible(c.oid)
|
|
)
|
|
AND a.attname = '$field' ";
|
|
$r = db_query_all($sql);
|
|
|
|
return !empty($r);
|
|
}
|
|
|
|
function _mysql_field_exists($table, $field) {
|
|
# $table = table_by_key($table); # _mysql_field_exists is always called with the expanded table name - don't expand it twice
|
|
$sql = "SHOW COLUMNS FROM $table LIKE ?";
|
|
$r = db_query_all($sql, array( $field));
|
|
|
|
return !empty($r);
|
|
}
|
|
|
|
function _sqlite_field_exists($table, $field) {
|
|
$sql = "PRAGMA table_info($table)";
|
|
$r = db_query_all($sql);
|
|
|
|
foreach ($r as $row) {
|
|
if ($row['name'] == $field) {
|
|
return true;
|
|
}
|
|
}
|
|
return false;
|
|
}
|
|
|
|
function _db_field_exists($table, $field) {
|
|
global $CONF;
|
|
if ($CONF['database_type'] == 'pgsql') {
|
|
return _pgsql_field_exists($table, $field);
|
|
} elseif ($CONF['database_type'] == 'sqlite') {
|
|
return _sqlite_field_exists($table, $field);
|
|
} else {
|
|
return _mysql_field_exists($table, $field);
|
|
}
|
|
}
|
|
function _upgrade_filter_function($name) {
|
|
return preg_match('/upgrade_[\d]+(_mysql|_pgsql|_sqlite|_mysql_pgsql)?$/', $name) == 1;
|
|
}
|
|
|
|
function _db_add_field($table, $field, $fieldtype, $after = '') {
|
|
global $CONF;
|
|
|
|
$query = "ALTER TABLE " . table_by_key($table) . " ADD COLUMN $field $fieldtype";
|
|
if ($CONF['database_type'] == 'mysql' && !empty($after)) {
|
|
$query .= " AFTER $after "; # PgSQL does not support to specify where to add the column, MySQL does
|
|
}
|
|
|
|
if (! _db_field_exists(table_by_key($table), $field)) {
|
|
db_query_parsed($query);
|
|
} else {
|
|
printdebug("field already exists: $table.$field");
|
|
}
|
|
}
|
|
|
|
function echo_out($text) {
|
|
if (defined('PHPUNIT_TEST')) {
|
|
//error_log("" . $text);
|
|
} else {
|
|
echo $text . "\n";
|
|
}
|
|
}
|
|
|
|
function printdebug($text) {
|
|
if (safeget('debug') != "") {
|
|
echo_out("<p style='color:#999'>$text</p>");
|
|
}
|
|
}
|
|
|
|
$table = table_by_key('config');
|
|
if ($CONF['database_type'] == 'pgsql') {
|
|
// check if table already exists, if so, don't recreate it
|
|
if (!_pgsql_object_exists($table)) {
|
|
$pgsql = "
|
|
CREATE TABLE $table (
|
|
id SERIAL,
|
|
name VARCHAR(20) NOT NULL UNIQUE,
|
|
value VARCHAR(20) NOT NULL,
|
|
PRIMARY KEY(id)
|
|
)";
|
|
db_query_parsed($pgsql);
|
|
}
|
|
} elseif (db_sqlite()) {
|
|
$enc = 'PRAGMA encoding = "UTF-8"';
|
|
db_query_parsed($enc);
|
|
$sql = "
|
|
CREATE TABLE {IF_NOT_EXISTS} $table (
|
|
`id` {AUTOINCREMENT},
|
|
`name` TEXT NOT NULL UNIQUE DEFAULT '',
|
|
`value` TEXT NOT NULL DEFAULT ''
|
|
)
|
|
";
|
|
db_query_parsed($sql);
|
|
} else {
|
|
$mysql = "
|
|
CREATE TABLE {IF_NOT_EXISTS} $table (
|
|
`id` {AUTOINCREMENT} {PRIMARY},
|
|
`name` VARCHAR(20) {LATIN1} NOT NULL DEFAULT '',
|
|
`value` VARCHAR(20) {LATIN1} NOT NULL DEFAULT '',
|
|
UNIQUE name ( `name` )
|
|
)
|
|
";
|
|
db_query_parsed($mysql, 0, " COMMENT = 'PostfixAdmin settings'");
|
|
}
|
|
|
|
$version = check_db_version(false);
|
|
_do_upgrade($version);
|
|
|
|
function _do_upgrade($current_version) {
|
|
global $CONF;
|
|
|
|
$target_version = 0;
|
|
// Rather than being bound to an svn revision number, just look for the largest function name that matches upgrade_\d+...
|
|
// $target_version = preg_replace('/[^0-9]/', '', '$Revision$');
|
|
$funclist = get_defined_functions();
|
|
$our_upgrade_functions = array_filter($funclist['user'], '_upgrade_filter_function');
|
|
foreach ($our_upgrade_functions as $function_name) {
|
|
$bits = explode("_", $function_name);
|
|
$function_number = $bits[1];
|
|
if ($function_number > $current_version && $function_number > $target_version) {
|
|
$target_version = $function_number;
|
|
}
|
|
}
|
|
|
|
if ($current_version >= $target_version) {
|
|
# already up to date
|
|
echo_out("<p>Database is up to date: $current_version/$target_version </p>");
|
|
return true;
|
|
}
|
|
|
|
echo_out("<p>Updating database:</p><p>- old version: $current_version; target version: $target_version</p>\n");
|
|
echo_out("<div style='color:#999'> (If the update doesn't work, run setup.php?debug=1 to see the detailed error messages and SQL queries.)</div>");
|
|
|
|
if (db_sqlite() && $current_version < 1824) {
|
|
// Fast forward to the first revision supporting SQLite
|
|
$current_version = 1823;
|
|
}
|
|
|
|
for ($i = $current_version +1; $i <= $target_version; $i++) {
|
|
$function = "upgrade_$i";
|
|
$function_mysql_pgsql = $function . "_mysql_pgsql";
|
|
$function_mysql = $function . "_mysql";
|
|
$function_pgsql = $function . "_pgsql";
|
|
$function_sqlite = $function . "_sqlite";
|
|
|
|
if (function_exists($function)) {
|
|
echo_out("<p>updating to version $i (all databases)...");
|
|
$function();
|
|
echo_out(" done");
|
|
}
|
|
if ($CONF['database_type'] == 'mysql' || $CONF['database_type'] == 'mysqli' || $CONF['database_type'] == 'pgsql') {
|
|
if (function_exists($function_mysql_pgsql)) {
|
|
echo_out("<p>updating to version $i (MySQL and PgSQL)...");
|
|
$function_mysql_pgsql();
|
|
echo_out(" done");
|
|
}
|
|
}
|
|
if ($CONF['database_type'] == 'mysql' || $CONF['database_type'] == 'mysqli') {
|
|
if (function_exists($function_mysql)) {
|
|
echo_out("<p>updating to version $i (MySQL)...");
|
|
$function_mysql();
|
|
echo_out(" done");
|
|
}
|
|
} elseif (db_sqlite()) {
|
|
if (function_exists($function_sqlite)) {
|
|
echo_out("<p>updating to version $i (SQLite)...");
|
|
$function_sqlite();
|
|
echo_out(" done");
|
|
}
|
|
} elseif ($CONF['database_type'] == 'pgsql') {
|
|
if (function_exists($function_pgsql)) {
|
|
echo_out("<p>updating to version $i (PgSQL)...");
|
|
$function_pgsql();
|
|
echo_out(" done");
|
|
}
|
|
}
|
|
// Update config table so we don't run the same query twice in the future.
|
|
$table = table_by_key('config');
|
|
$sql = "UPDATE $table SET value = :value WHERE name = 'version'";
|
|
db_execute($sql, array('value' => $i));
|
|
};
|
|
}
|
|
|
|
/**
|
|
* 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 void
|
|
*/
|
|
|
|
function db_query_parsed($sql, $ignore_errors = 0, $attach_mysql = "") {
|
|
global $CONF;
|
|
|
|
if (db_mysql()) {
|
|
$replace = array(
|
|
'{AUTOINCREMENT}' => 'int(11) not null auto_increment',
|
|
'{PRIMARY}' => 'primary key',
|
|
'{UNSIGNED}' => 'unsigned' ,
|
|
'{FULLTEXT}' => 'FULLTEXT',
|
|
'{BOOLEAN}' => "tinyint(1) NOT NULL DEFAULT '" . db_get_boolean(false) . "'",
|
|
'{UTF-8}' => '/*!40100 CHARACTER SET utf8 */',
|
|
'{LATIN1}' => '/*!40100 CHARACTER SET latin1 COLLATE latin1_general_ci */',
|
|
'{IF_NOT_EXISTS}' => 'IF NOT EXISTS',
|
|
'{RENAME_COLUMN}' => 'CHANGE COLUMN',
|
|
'{MYISAM}' => '',
|
|
'{INNODB}' => 'ENGINE=InnoDB',
|
|
'{INT}' => 'integer NOT NULL DEFAULT 0',
|
|
'{BIGINT}' => 'bigint NOT NULL DEFAULT 0',
|
|
'{DATETIME}' => "datetime NOT NULL default '2000-01-01 00:00:00'", # different from {DATE} only for MySQL
|
|
'{DATE}' => "timestamp NOT NULL default '2000-01-01'", # MySQL needs a sane default (no default is interpreted as CURRENT_TIMESTAMP, which is ...
|
|
'{DATEFUTURE}' => "timestamp NOT NULL default '2038-01-18'", # different default timestamp for vacation.activeuntil
|
|
'{DATECURRENT}' => 'timestamp NOT NULL default CURRENT_TIMESTAMP', # only allowed once per table in MySQL
|
|
'{COLLATE}' => "CHARACTER SET latin1 COLLATE latin1_general_ci", # just incase someone has a unicode collation set.
|
|
|
|
);
|
|
$sql = "$sql $attach_mysql";
|
|
} elseif (db_sqlite()) {
|
|
$replace = array(
|
|
'{AUTOINCREMENT}' => 'integer PRIMARY KEY AUTOINCREMENT NOT NULL',
|
|
'{PRIMARY}' => 'PRIMARY KEY',
|
|
'{UNSIGNED}' => 'unsigned',
|
|
'{FULLTEXT}' => 'text',
|
|
'{BOOLEAN}' => "tinyint(1) NOT NULL DEFAULT '" . db_get_boolean(false) . "'",
|
|
'{BOOLEAN_TRUE}' => "tinyint(1) NOT NULL DEFAULT '" . db_get_boolean(true) . "'",
|
|
'{UTF-8}' => '',
|
|
'{LATIN1}' => '',
|
|
'{IF_NOT_EXISTS}' => 'IF NOT EXISTS',
|
|
'{RENAME_COLUMN}' => 'CHANGE COLUMN',
|
|
'{MYISAM}' => '',
|
|
'{INNODB}' => '',
|
|
'{INT}' => 'int(11) NOT NULL DEFAULT 0',
|
|
'{BIGINT}' => 'bigint(20) NOT NULL DEFAULT 0',
|
|
'{DATETIME}' => "datetime NOT NULL default '2000-01-01'",
|
|
'{DATE}' => "datetime NOT NULL default '2000-01-01'",
|
|
'{DATEFUTURE}' => "datetime NOT NULL default '2038-01-18'", # different default timestamp for vacation.activeuntil
|
|
'{DATECURRENT}' => 'datetime NOT NULL default CURRENT_TIMESTAMP',
|
|
'{COLLATE}' => ''
|
|
);
|
|
} elseif ($CONF['database_type'] == 'pgsql') {
|
|
$replace = array(
|
|
'{AUTOINCREMENT}' => 'SERIAL',
|
|
'{PRIMARY}' => 'primary key',
|
|
'{UNSIGNED}' => '',
|
|
'{FULLTEXT}' => '',
|
|
'{BOOLEAN}' => "BOOLEAN NOT NULL DEFAULT '" . db_get_boolean(false) . "'",
|
|
'{UTF-8}' => '', # UTF-8 is simply ignored.
|
|
'{LATIN1}' => '', # same for latin1
|
|
'{IF_NOT_EXISTS}' => '', # does not work with PgSQL
|
|
'{RENAME_COLUMN}' => 'ALTER COLUMN', # PgSQL : ALTER TABLE x RENAME x TO y
|
|
'{MYISAM}' => '',
|
|
'{INNODB}' => '',
|
|
'{INT}' => 'integer NOT NULL DEFAULT 0',
|
|
'{BIGINT}' => 'bigint NOT NULL DEFAULT 0',
|
|
'int(1)' => 'int',
|
|
'int(10)' => 'int',
|
|
'int(11)' => 'int',
|
|
'int(4)' => 'int',
|
|
'{DATETIME}' => "timestamp with time zone default '2000-01-01'", # stay in sync with MySQL
|
|
'{DATE}' => "timestamp with time zone default '2000-01-01'", # stay in sync with MySQL
|
|
'{DATEFUTURE}' => "timestamp with time zone default '2038-01-18'", # stay in sync with MySQL
|
|
'{DATECURRENT}' => 'timestamp with time zone default now()',
|
|
'{COLLATE}' => '',
|
|
);
|
|
} else {
|
|
echo_out("Sorry, unsupported database type " . $CONF['database_type']);
|
|
exit;
|
|
}
|
|
|
|
$replace['{BOOL_TRUE}'] = db_get_boolean(true);
|
|
$replace['{BOOL_FALSE}'] = db_get_boolean(false);
|
|
|
|
$query = trim(str_replace(array_keys($replace), $replace, $sql));
|
|
|
|
$debug = safeget('debug', '') != '';
|
|
|
|
if ($debug) {
|
|
printdebug($query);
|
|
}
|
|
|
|
try {
|
|
$result = db_execute($query, array(), true);
|
|
} catch (PDOException $e) {
|
|
if ($debug) {
|
|
echo_out("<div style='color:#f00'>" . $e->getMessage() . "</div>");
|
|
}
|
|
}
|
|
}
|
|
/**
|
|
* @param string $table
|
|
* @param string $index
|
|
* @return string
|
|
*/
|
|
function _drop_index($table, $index) {
|
|
global $CONF;
|
|
$table = 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' || db_sqlite()) {
|
|
return "DROP INDEX $index"; # Index names are unique with a DB for PostgreSQL
|
|
} else {
|
|
echo_out("Sorry, unsupported database type " . $CONF['database_type']);
|
|
exit;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @return string
|
|
* @param string $table
|
|
* @param string $indexname
|
|
* @param string $fieldlist
|
|
*/
|
|
function _add_index($table, $indexname, $fieldlist) {
|
|
global $CONF;
|
|
$table = table_by_key($table);
|
|
|
|
if ($CONF['database_type'] == 'mysql' || $CONF['database_type'] == 'mysqli') {
|
|
$fieldlist = str_replace(',', '`,`', $fieldlist); # fix quoting if index contains multiple fields
|
|
return "ALTER TABLE $table ADD INDEX `$indexname` ( `$fieldlist` )";
|
|
} elseif ($CONF['database_type'] == 'pgsql') {
|
|
$pgindexname = $table . "_" . $indexname . '_idx';
|
|
return "CREATE INDEX $pgindexname ON $table($fieldlist);"; # Index names are unique with a DB for PostgreSQL
|
|
} else {
|
|
echo_out("Sorry, unsupported database type " . $CONF['database_type']);
|
|
exit;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_1_mysql() {
|
|
#
|
|
# creating the tables in this very old layout (pre 2.1) causes trouble if the MySQL charset is not latin1 (multibyte vs. index length)
|
|
# therefore:
|
|
|
|
return; # <-- skip running this function at all.
|
|
|
|
# (remove the above "return" if you really want to start with a pre-2.1 database layout)
|
|
|
|
// 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},
|
|
`modified` {DATETIME},
|
|
`active` tinyint(1) NOT NULL default '1',
|
|
PRIMARY KEY (`username`)
|
|
) {COLLATE} 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},
|
|
`modified` {DATETIME},
|
|
`active` tinyint(1) NOT NULL default '1',
|
|
PRIMARY KEY (`address`)
|
|
) {COLLATE} 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},
|
|
`modified` {DATETIME},
|
|
`active` tinyint(1) NOT NULL default '1',
|
|
PRIMARY KEY (`domain`)
|
|
) {COLLATE} 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},
|
|
`active` tinyint(1) NOT NULL default '1',
|
|
KEY username (`username`)
|
|
) {COLLATE} COMMENT='Postfix Admin - Domain Admins';";
|
|
|
|
$sql[] = "
|
|
CREATE TABLE {IF_NOT_EXISTS} $log (
|
|
`timestamp` {DATETIME},
|
|
`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`)
|
|
) {COLLATE} 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},
|
|
`modified` {DATETIME},
|
|
`active` tinyint(1) NOT NULL default '1',
|
|
PRIMARY KEY (`username`)
|
|
) {COLLATE} COMMENT='Postfix Admin - Virtual Mailboxes';";
|
|
|
|
$sql[] = "
|
|
CREATE TABLE {IF_NOT_EXISTS} $vacation (
|
|
email varchar(255) NOT NULL ,
|
|
subject varchar(255) NOT NULL,
|
|
body text NOT NULL,
|
|
cache text NOT NULL,
|
|
domain varchar(255) NOT NULL ,
|
|
created {DATETIME},
|
|
active tinyint(4) NOT NULL default '1',
|
|
PRIMARY KEY (email),
|
|
KEY email (email)
|
|
) {INNODB} {COLLATE} COMMENT='Postfix Admin - Virtual Vacation' ;";
|
|
|
|
foreach ($sql as $query) {
|
|
db_query_parsed($query);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_2_mysql() {
|
|
#
|
|
# updating the tables in this very old layout (pre 2.1) causes trouble if the MySQL charset is not latin1 (multibyte vs. index length)
|
|
# therefore:
|
|
|
|
return; # <-- skip running this function at all.
|
|
|
|
# (remove the above "return" if you really want to update a pre-2.1 database)
|
|
|
|
# upgrade pre-2.1 database
|
|
# from TABLE_BACKUP_MX.TXT
|
|
$table_domain = table_by_key('domain');
|
|
if (!_mysql_field_exists($table_domain, 'transport')) {
|
|
db_query_parsed("ALTER TABLE $table_domain ADD COLUMN transport VARCHAR(255) AFTER maxquota;", true);
|
|
}
|
|
if (!_mysql_field_exists($table_domain, 'backupmx')) {
|
|
db_query_parsed("ALTER TABLE $table_domain ADD COLUMN backupmx {BOOLEAN} AFTER transport;", true);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_2_pgsql() {
|
|
if (!_pgsql_object_exists(table_by_key('domain'))) {
|
|
db_query_parsed("
|
|
CREATE TABLE " . table_by_key('domain') . " (
|
|
domain character varying(255) NOT NULL,
|
|
description character varying(255) NOT NULL default '',
|
|
aliases integer NOT NULL default 0,
|
|
mailboxes integer NOT NULL default 0,
|
|
maxquota integer NOT NULL default 0,
|
|
quota integer NOT NULL default 0,
|
|
transport character varying(255) default NULL,
|
|
backupmx boolean NOT NULL default false,
|
|
created timestamp with time zone default now(),
|
|
modified timestamp with time zone default now(),
|
|
active boolean NOT NULL default true,
|
|
Constraint \"domain_key\" Primary Key (\"domain\")
|
|
); ");
|
|
db_query_parsed("CREATE INDEX domain_domain_active ON " . table_by_key('domain') . "(domain,active);");
|
|
db_query_parsed("COMMENT ON TABLE " . table_by_key('domain') . " IS 'Postfix Admin - Virtual Domains'");
|
|
}
|
|
if (!_pgsql_object_exists(table_by_key('admin'))) {
|
|
db_query_parsed('
|
|
CREATE TABLE ' . table_by_key("admin") . ' (
|
|
"username" character varying(255) NOT NULL,
|
|
"password" character varying(255) NOT NULL default \'\',
|
|
"created" timestamp with time zone default now(),
|
|
"modified" timestamp with time zone default now(),
|
|
"active" boolean NOT NULL default true,
|
|
Constraint "admin_key" Primary Key ("username")
|
|
)');
|
|
db_query_parsed("COMMENT ON TABLE " . table_by_key('admin') . " IS 'Postfix Admin - Virtual Admins'");
|
|
}
|
|
|
|
if (!_pgsql_object_exists(table_by_key('alias'))) {
|
|
db_query_parsed('
|
|
CREATE TABLE ' . table_by_key("alias") . ' (
|
|
address character varying(255) NOT NULL,
|
|
goto text NOT NULL,
|
|
domain character varying(255) NOT NULL REFERENCES "' . table_by_key("domain") . '",
|
|
created timestamp with time zone default now(),
|
|
modified timestamp with time zone default now(),
|
|
active boolean NOT NULL default true,
|
|
Constraint "alias_key" Primary Key ("address")
|
|
);');
|
|
db_query_parsed('CREATE INDEX alias_address_active ON ' . table_by_key("alias") . '(address,active)');
|
|
db_query_parsed('COMMENT ON TABLE ' . table_by_key("alias") . ' IS \'Postfix Admin - Virtual Aliases\'');
|
|
}
|
|
|
|
if (!_pgsql_object_exists(table_by_key('domain_admins'))) {
|
|
db_query_parsed('
|
|
CREATE TABLE ' . table_by_key('domain_admins') . ' (
|
|
username character varying(255) NOT NULL,
|
|
domain character varying(255) NOT NULL REFERENCES "' . table_by_key('domain') . '",
|
|
created timestamp with time zone default now(),
|
|
active boolean NOT NULL default true
|
|
);');
|
|
db_query_parsed('COMMENT ON TABLE ' . table_by_key('domain_admins') . ' IS \'Postfix Admin - Domain Admins\'');
|
|
}
|
|
|
|
if (!_pgsql_object_exists(table_by_key('log'))) {
|
|
db_query_parsed('
|
|
CREATE TABLE ' . table_by_key('log') . ' (
|
|
timestamp timestamp with time zone default now(),
|
|
username character varying(255) NOT NULL default \'\',
|
|
domain character varying(255) NOT NULL default \'\',
|
|
action character varying(255) NOT NULL default \'\',
|
|
data text NOT NULL default \'\'
|
|
);');
|
|
db_query_parsed('COMMENT ON TABLE ' . table_by_key('log') . ' IS \'Postfix Admin - Log\'');
|
|
}
|
|
if (!_pgsql_object_exists(table_by_key('mailbox'))) {
|
|
db_query_parsed('
|
|
CREATE TABLE ' . table_by_key('mailbox') . ' (
|
|
username character varying(255) NOT NULL,
|
|
password character varying(255) NOT NULL default \'\',
|
|
name character varying(255) NOT NULL default \'\',
|
|
maildir character varying(255) NOT NULL default \'\',
|
|
quota integer NOT NULL default 0,
|
|
domain character varying(255) NOT NULL REFERENCES "' . table_by_key('domain') . '",
|
|
created timestamp with time zone default now(),
|
|
modified timestamp with time zone default now(),
|
|
active boolean NOT NULL default true,
|
|
Constraint "mailbox_key" Primary Key ("username")
|
|
);');
|
|
db_query_parsed('CREATE INDEX mailbox_username_active ON ' . table_by_key('mailbox') . '(username,active);');
|
|
db_query_parsed('COMMENT ON TABLE ' . table_by_key('mailbox') . ' IS \'Postfix Admin - Virtual Mailboxes\'');
|
|
}
|
|
|
|
if (!_pgsql_object_exists(table_by_key('vacation'))) {
|
|
db_query_parsed('
|
|
CREATE TABLE ' . table_by_key('vacation') . ' (
|
|
email character varying(255) PRIMARY KEY,
|
|
subject character varying(255) NOT NULL,
|
|
body text NOT NULL ,
|
|
cache text NOT NULL ,
|
|
"domain" character varying(255) NOT NULL REFERENCES "' . table_by_key('domain') . '",
|
|
created timestamp with time zone DEFAULT now(),
|
|
active boolean DEFAULT true NOT NULL
|
|
);');
|
|
db_query_parsed('CREATE INDEX vacation_email_active ON ' . table_by_key('vacation') . '(email,active);');
|
|
}
|
|
|
|
if (!_pgsql_object_exists(table_by_key('vacation_notification'))) {
|
|
db_query_parsed('
|
|
CREATE TABLE ' . table_by_key('vacation_notification') . ' (
|
|
on_vacation character varying(255) NOT NULL REFERENCES ' . table_by_key('vacation') . '(email) ON DELETE CASCADE,
|
|
notified character varying(255) NOT NULL,
|
|
notified_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT vacation_notification_pkey primary key(on_vacation,notified)
|
|
);
|
|
');
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_3_mysql() {
|
|
#
|
|
# updating the tables in this very old layout (pre 2.1) causes trouble if the MySQL charset is not latin1 (multibyte vs. index length)
|
|
# therefore:
|
|
|
|
return; # <-- skip running this function at all.
|
|
|
|
# (remove the above "return" if you really want to update a pre-2.1 database)
|
|
|
|
# upgrade pre-2.1 database
|
|
# from TABLE_CHANGES.TXT
|
|
$table_admin = table_by_key('admin');
|
|
$table_alias = table_by_key('alias');
|
|
$table_domain = table_by_key('domain');
|
|
$table_mailbox = table_by_key('mailbox');
|
|
$table_vacation = table_by_key('vacation');
|
|
|
|
if (!_mysql_field_exists($table_admin, 'created')) {
|
|
db_query_parsed("ALTER TABLE $table_admin {RENAME_COLUMN} create_date created {DATETIME};");
|
|
}
|
|
if (!_mysql_field_exists($table_admin, 'modified')) {
|
|
db_query_parsed("ALTER TABLE $table_admin {RENAME_COLUMN} change_date modified {DATETIME};");
|
|
}
|
|
if (!_mysql_field_exists($table_alias, 'created')) {
|
|
db_query_parsed("ALTER TABLE $table_alias {RENAME_COLUMN} create_date created {DATETIME};");
|
|
}
|
|
if (!_mysql_field_exists($table_alias, 'modified')) {
|
|
db_query_parsed("ALTER TABLE $table_alias {RENAME_COLUMN} change_date modified {DATETIME};");
|
|
}
|
|
if (!_mysql_field_exists($table_domain, 'created')) {
|
|
db_query_parsed("ALTER TABLE $table_domain {RENAME_COLUMN} create_date created {DATETIME};");
|
|
}
|
|
if (!_mysql_field_exists($table_domain, 'modified')) {
|
|
db_query_parsed("ALTER TABLE $table_domain {RENAME_COLUMN} change_date modified {DATETIME};");
|
|
}
|
|
if (!_mysql_field_exists($table_domain, 'aliases')) {
|
|
db_query_parsed("ALTER TABLE $table_domain ADD COLUMN aliases INT(10) DEFAULT '-1' NOT NULL AFTER description;");
|
|
}
|
|
if (!_mysql_field_exists($table_domain, 'mailboxes')) {
|
|
db_query_parsed("ALTER TABLE $table_domain ADD COLUMN mailboxes INT(10) DEFAULT '-1' NOT NULL AFTER aliases;");
|
|
}
|
|
if (!_mysql_field_exists($table_domain, 'maxquota')) {
|
|
db_query_parsed("ALTER TABLE $table_domain ADD COLUMN maxquota INT(10) DEFAULT '-1' NOT NULL AFTER mailboxes;");
|
|
}
|
|
if (!_mysql_field_exists($table_domain, 'transport')) {
|
|
db_query_parsed("ALTER TABLE $table_domain ADD COLUMN transport VARCHAR(255) AFTER maxquota;");
|
|
}
|
|
if (!_mysql_field_exists($table_domain, 'backupmx')) {
|
|
db_query_parsed("ALTER TABLE $table_domain ADD COLUMN backupmx TINYINT(1) DEFAULT '0' NOT NULL AFTER transport;");
|
|
}
|
|
if (!_mysql_field_exists($table_mailbox, 'created')) {
|
|
db_query_parsed("ALTER TABLE $table_mailbox {RENAME_COLUMN} create_date created {DATETIME};");
|
|
}
|
|
if (!_mysql_field_exists($table_mailbox, 'modified')) {
|
|
db_query_parsed("ALTER TABLE $table_mailbox {RENAME_COLUMN} change_date modified {DATETIME};");
|
|
}
|
|
if (!_mysql_field_exists($table_mailbox, 'quota')) {
|
|
db_query_parsed("ALTER TABLE $table_mailbox ADD COLUMN quota INT(10) DEFAULT '-1' NOT NULL AFTER maildir;");
|
|
}
|
|
if (!_mysql_field_exists($table_vacation, 'domain')) {
|
|
db_query_parsed("ALTER TABLE $table_vacation ADD COLUMN domain VARCHAR(255) DEFAULT '' NOT NULL AFTER cache;");
|
|
}
|
|
if (!_mysql_field_exists($table_vacation, 'created')) {
|
|
db_query_parsed("ALTER TABLE $table_vacation ADD COLUMN created {DATETIME} AFTER domain;");
|
|
}
|
|
if (!_mysql_field_exists($table_vacation, 'active')) {
|
|
db_query_parsed("ALTER TABLE $table_vacation ADD COLUMN active TINYINT(1) DEFAULT '1' NOT NULL AFTER created;");
|
|
}
|
|
db_query_parsed("ALTER TABLE $table_vacation DROP PRIMARY KEY");
|
|
db_query_parsed("ALTER TABLE $table_vacation ADD PRIMARY KEY(email)");
|
|
db_query_parsed("UPDATE $table_vacation SET domain=SUBSTRING_INDEX(email, '@', -1) WHERE email=email;");
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_4_mysql() { # MySQL only
|
|
# changes between 2.1 and moving to sourceforge
|
|
|
|
return; // as the above _mysql functions are disabled; this one will just error for a new db.
|
|
$table_domain = table_by_key('domain');
|
|
|
|
db_query_parsed("ALTER TABLE $table_domain ADD COLUMN quota int(10) NOT NULL default '0' AFTER maxquota", true);
|
|
# Possible errors that can be ignored:
|
|
# - Invalid query: Table 'postfix.domain' doesn't exist
|
|
}
|
|
|
|
/**
|
|
* Changes between 2.1 and moving to sf.net
|
|
* @return void
|
|
*/
|
|
function upgrade_4_pgsql() {
|
|
$table_domain = table_by_key('domain');
|
|
$table_admin = table_by_key('admin');
|
|
$table_alias = table_by_key('alias');
|
|
$table_domain_admins = table_by_key('domain_admins');
|
|
$table_log = table_by_key('log');
|
|
$table_mailbox = table_by_key('mailbox');
|
|
$table_vacation = table_by_key('vacation');
|
|
$table_vacation_notification = table_by_key('vacation_notification');
|
|
|
|
if (!_pgsql_field_exists($table_domain, 'quota')) {
|
|
db_query_parsed("ALTER TABLE $table_domain ADD COLUMN quota int NOT NULL default '0'");
|
|
}
|
|
|
|
db_query_parsed("ALTER TABLE $table_domain ALTER COLUMN domain DROP DEFAULT");
|
|
if (!_pgsql_object_exists('domain_domain_active')) {
|
|
db_query_parsed("CREATE INDEX domain_domain_active ON $table_domain(domain,active)");
|
|
}
|
|
|
|
db_query_parsed("ALTER TABLE $table_domain_admins ALTER COLUMN domain DROP DEFAULT");
|
|
db_query_parsed("ALTER TABLE $table_alias ALTER COLUMN address DROP DEFAULT");
|
|
db_query_parsed("ALTER TABLE $table_alias ALTER COLUMN domain DROP DEFAULT");
|
|
if (!_pgsql_object_exists('alias_address_active')) {
|
|
db_query_parsed("CREATE INDEX alias_address_active ON $table_alias(address,active)");
|
|
}
|
|
|
|
db_query_parsed("ALTER TABLE $table_domain_admins ALTER COLUMN username DROP DEFAULT");
|
|
db_query_parsed("ALTER TABLE $table_domain_admins ALTER COLUMN domain DROP DEFAULT");
|
|
|
|
db_query_parsed("BEGIN ");
|
|
db_query_parsed("ALTER TABLE $table_log RENAME COLUMN data TO data_old;");
|
|
db_query_parsed("ALTER TABLE $table_log ADD COLUMN data text NOT NULL default '';");
|
|
db_query_parsed("UPDATE $table_log SET data = CAST(data_old AS text);");
|
|
db_query_parsed("ALTER TABLE $table_log DROP COLUMN data_old;");
|
|
db_query_parsed("COMMIT");
|
|
|
|
db_query_parsed("ALTER TABLE $table_mailbox ALTER COLUMN username DROP DEFAULT");
|
|
db_query_parsed("ALTER TABLE $table_mailbox ALTER COLUMN domain DROP DEFAULT");
|
|
|
|
db_query_parsed("BEGIN;");
|
|
db_query_parsed("ALTER TABLE $table_mailbox RENAME COLUMN domain TO domain_old;");
|
|
db_query_parsed("ALTER TABLE $table_mailbox ADD COLUMN domain varchar(255) REFERENCES $table_domain (domain);");
|
|
db_query_parsed("UPDATE $table_mailbox SET domain = domain_old;");
|
|
db_query_parsed("ALTER TABLE $table_mailbox DROP COLUMN domain_old;");
|
|
db_query_parsed("COMMIT;");
|
|
|
|
if (!_pgsql_object_exists('mailbox_username_active')) {
|
|
db_query_parsed("CREATE INDEX mailbox_username_active ON $table_mailbox(username,active)");
|
|
}
|
|
|
|
|
|
db_query_parsed("ALTER TABLE $table_vacation ALTER COLUMN body SET DEFAULT ''");
|
|
if (_pgsql_field_exists($table_vacation, 'cache')) {
|
|
db_query_parsed("ALTER TABLE $table_vacation DROP COLUMN cache");
|
|
}
|
|
|
|
db_query_parsed(" BEGIN; ");
|
|
db_query_parsed("ALTER TABLE $table_vacation RENAME COLUMN domain to domain_old;");
|
|
db_query_parsed("ALTER TABLE $table_vacation ADD COLUMN domain varchar(255) REFERENCES $table_domain;");
|
|
db_query_parsed("UPDATE $table_vacation SET domain = domain_old;");
|
|
db_query_parsed("ALTER TABLE $table_vacation DROP COLUMN domain_old;");
|
|
db_query_parsed("COMMIT;");
|
|
|
|
if (!_pgsql_object_exists('vacation_email_active')) {
|
|
db_query_parsed("CREATE INDEX vacation_email_active ON $table_vacation(email,active)");
|
|
}
|
|
|
|
if (!_pgsql_object_exists($table_vacation_notification)) {
|
|
db_query_parsed("
|
|
CREATE TABLE $table_vacation_notification (
|
|
on_vacation character varying(255) NOT NULL REFERENCES $table_vacation(email) ON DELETE CASCADE,
|
|
notified character varying(255) NOT NULL,
|
|
notified_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT vacation_notification_pkey primary key(on_vacation,notified));");
|
|
}
|
|
}
|
|
|
|
|
|
# Possible errors that can be ignored:
|
|
#
|
|
# NO MySQL errors should be ignored below this line!
|
|
|
|
|
|
|
|
/**
|
|
* create tables
|
|
* version: Sourceforge SVN r1 of DATABASE_MYSQL.txt
|
|
* changes compared to DATABASE_MYSQL.txt:
|
|
* - removed MySQL user and database creation
|
|
* - removed creation of default superadmin
|
|
* @return void
|
|
*/
|
|
function upgrade_5_mysql() {
|
|
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},
|
|
`modified` {DATETIME},
|
|
`active` tinyint(1) NOT NULL default '1',
|
|
PRIMARY KEY (`username`),
|
|
KEY username (`username`)
|
|
) {COLLATE} COMMENT='Postfix Admin - Virtual Admins'; ");
|
|
|
|
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},
|
|
`modified` {DATETIME},
|
|
`active` tinyint(1) NOT NULL default '1',
|
|
PRIMARY KEY (`address`),
|
|
KEY address (`address`)
|
|
) {COLLATE} COMMENT='Postfix Admin - Virtual Aliases';
|
|
");
|
|
|
|
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},
|
|
`modified` {DATETIME},
|
|
`active` tinyint(1) NOT NULL default '1',
|
|
PRIMARY KEY (`domain`),
|
|
KEY domain (`domain`)
|
|
) {COLLATE} COMMENT='Postfix Admin - Virtual Domains';
|
|
");
|
|
|
|
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},
|
|
`active` tinyint(1) NOT NULL default '1',
|
|
KEY username (`username`)
|
|
) {COLLATE} COMMENT='Postfix Admin - Domain Admins';
|
|
");
|
|
|
|
db_query_parsed("
|
|
CREATE TABLE {IF_NOT_EXISTS} " . table_by_key('log') . " (
|
|
`timestamp` {DATETIME},
|
|
`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`)
|
|
) {COLLATE} COMMENT='Postfix Admin - Log';
|
|
");
|
|
|
|
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},
|
|
`modified` {DATETIME},
|
|
`active` tinyint(1) NOT NULL default '1',
|
|
PRIMARY KEY (`username`),
|
|
KEY username (`username`)
|
|
) {COLLATE} COMMENT='Postfix Admin - Virtual Mailboxes';
|
|
");
|
|
|
|
db_query_parsed("
|
|
CREATE TABLE {IF_NOT_EXISTS} " . table_by_key('vacation') . " (
|
|
`email` varchar(255) NOT NULL ,
|
|
`subject` varchar(255) NOT NULL,
|
|
`body` text NOT NULL,
|
|
`cache` text NOT NULL,
|
|
`domain` varchar(255) NOT NULL,
|
|
`created` {DATETIME},
|
|
`active` tinyint(1) NOT NULL default '1',
|
|
PRIMARY KEY (`email`),
|
|
KEY email (`email`)
|
|
) {COLLATE} COMMENT='Postfix Admin - Virtual Vacation';
|
|
");
|
|
}
|
|
|
|
/**
|
|
* drop useless indicies (already available as primary key)
|
|
* @return void
|
|
*/
|
|
function upgrade_79_mysql() { # MySQL only
|
|
db_query_parsed(_drop_index('admin', 'username'), true);
|
|
db_query_parsed(_drop_index('alias', 'address'), true);
|
|
db_query_parsed(_drop_index('domain', 'domain'), true);
|
|
db_query_parsed(_drop_index('mailbox', 'username'), true);
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_81_mysql() { # MySQL only
|
|
$table_vacation = table_by_key('vacation');
|
|
$table_vacation_notification = table_by_key('vacation_notification');
|
|
|
|
$all_sql = explode("\n", trim("
|
|
ALTER TABLE $table_vacation CHANGE `email` `email` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_vacation CHANGE `subject` `subject` VARCHAR( 255 ) {UTF-8} NOT NULL
|
|
ALTER TABLE $table_vacation CHANGE `body` `body` TEXT {UTF-8} NOT NULL
|
|
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 {COLLATE}
|
|
ALTER TABLE $table_vacation {INNODB}
|
|
"));
|
|
|
|
foreach ($all_sql as $sql) {
|
|
db_query_parsed($sql, true);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Make logging translatable - i.e. create alias => create_alias
|
|
* @return void
|
|
*/
|
|
function upgrade_90_mysql_pgsql() {
|
|
db_query_parsed("UPDATE " . table_by_key('log') . " SET action = REPLACE(action,' ','_')", true);
|
|
# change edit_alias_state to edit_alias_active
|
|
db_query_parsed("UPDATE " . table_by_key('log') . " SET action = 'edit_alias_state' WHERE action = 'edit_alias_active'", true);
|
|
}
|
|
|
|
/**
|
|
* MySQL only allow quota > 2 GB
|
|
* @return void
|
|
*/
|
|
function upgrade_169_mysql() {
|
|
$table_domain = table_by_key('domain');
|
|
$table_mailbox = table_by_key('mailbox');
|
|
db_query_parsed("ALTER TABLE $table_domain MODIFY COLUMN `quota` bigint(20) NOT NULL default '0'", true);
|
|
db_query_parsed("ALTER TABLE $table_domain MODIFY COLUMN `maxquota` bigint(20) NOT NULL default '0'", true);
|
|
db_query_parsed("ALTER TABLE $table_mailbox MODIFY COLUMN `quota` bigint(20) NOT NULL default '0'", true);
|
|
}
|
|
|
|
|
|
/**
|
|
* Create / modify vacation_notification table.
|
|
* Note: This might not work if users used workarounds to create the table before.
|
|
* In this case, dropping the table is the easiest solution.
|
|
* @return void
|
|
*/
|
|
function upgrade_318_mysql() {
|
|
$table_vacation_notification = table_by_key('vacation_notification');
|
|
$table_vacation = table_by_key('vacation');
|
|
|
|
db_query_parsed("
|
|
CREATE TABLE {IF_NOT_EXISTS} $table_vacation_notification (
|
|
on_vacation varchar(255) NOT NULL,
|
|
notified varchar(255) NOT NULL,
|
|
notified_at timestamp NOT NULL default CURRENT_TIMESTAMP,
|
|
PRIMARY KEY on_vacation (`on_vacation`, `notified`),
|
|
CONSTRAINT `vacation_notification_pkey`
|
|
FOREIGN KEY (`on_vacation`) REFERENCES $table_vacation(`email`) ON DELETE CASCADE
|
|
) {INNODB} {COLLATE} COMMENT='Postfix Admin - Virtual Vacation Notifications'
|
|
");
|
|
|
|
# in case someone has manually created the table with utf8 fields before:
|
|
$all_sql = explode("\n", trim("
|
|
ALTER TABLE $table_vacation_notification CHANGE `notified` `notified` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_vacation_notification DEFAULT CHARACTER SET utf8
|
|
"));
|
|
# Possible errors that can be ignored:
|
|
# None.
|
|
# If something goes wrong, the user should drop the vacation_notification table
|
|
# (not a great loss) and re-create it using this function.
|
|
|
|
foreach ($all_sql as $sql) {
|
|
db_query_parsed($sql);
|
|
}
|
|
}
|
|
|
|
|
|
/**
|
|
* Create fetchmail table
|
|
* @return void
|
|
*/
|
|
function upgrade_344_mysql() {
|
|
$table_fetchmail = table_by_key('fetchmail');
|
|
|
|
db_query_parsed("
|
|
CREATE TABLE IF NOT EXISTS $table_fetchmail(
|
|
id int(11) unsigned not null auto_increment,
|
|
mailbox varchar(255) not null default '',
|
|
src_server varchar(255) not null default '',
|
|
src_auth enum('password','kerberos_v5','kerberos','kerberos_v4','gssapi','cram-md5','otp','ntlm','msn','ssh','any'),
|
|
src_user varchar(255) not null default '',
|
|
src_password varchar(255) not null default '',
|
|
src_folder varchar(255) not null default '',
|
|
poll_time int(11) unsigned not null default 10,
|
|
fetchall tinyint(1) unsigned not null default 0,
|
|
keep tinyint(1) unsigned not null default 0,
|
|
protocol enum('POP3','IMAP','POP2','ETRN','AUTO'),
|
|
extra_options text,
|
|
returned_text text,
|
|
mda varchar(255) not null default '',
|
|
date timestamp,
|
|
primary key(id)
|
|
);
|
|
");
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_344_pgsql() {
|
|
$fetchmail = table_by_key('fetchmail');
|
|
// a field name called 'date' is probably a bad idea.
|
|
if (!_pgsql_object_exists('fetchmail')) {
|
|
db_query_parsed("
|
|
create table $fetchmail(
|
|
id serial,
|
|
mailbox varchar(255) not null default '',
|
|
src_server varchar(255) not null default '',
|
|
src_auth varchar(15) NOT NULL,
|
|
src_user varchar(255) not null default '',
|
|
src_password varchar(255) not null default '',
|
|
src_folder varchar(255) not null default '',
|
|
poll_time integer not null default 10,
|
|
fetchall boolean not null default false,
|
|
keep boolean not null default false,
|
|
protocol varchar(15) NOT NULL,
|
|
extra_options text,
|
|
returned_text text,
|
|
mda varchar(255) not null default '',
|
|
date timestamp with time zone default now(),
|
|
primary key(id),
|
|
CHECK (src_auth IN ('password','kerberos_v5','kerberos','kerberos_v4','gssapi','cram-md5','otp','ntlm','msn','ssh','any')),
|
|
CHECK (protocol IN ('POP3', 'IMAP', 'POP2', 'ETRN', 'AUTO'))
|
|
);
|
|
");
|
|
}
|
|
// MySQL expects sequences to start at 1. Stupid database.
|
|
// fetchmail.php requires id parameters to be > 0, as it does if($id) like logic... hence if we don't
|
|
// fudge the sequence starting point, you cannot delete/edit the first entry if using PostgreSQL.
|
|
// I'm sure there's a more elegant way of fixing it properly.... but this should work for now.
|
|
if (_pgsql_object_exists('fetchmail_id_seq')) {
|
|
db_query_parsed("SELECT nextval('{$fetchmail}_id_seq')"); // I don't care about number waste.
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Create alias_domain table - MySQL
|
|
* function upgrade_362_mysql() # renamed to _438 to make sure it runs after an upgrade from 2.2.x
|
|
*
|
|
* @return void
|
|
*/
|
|
function upgrade_438_mysql() {
|
|
# Table structure for table alias_domain
|
|
#
|
|
$table_alias_domain = table_by_key('alias_domain');
|
|
db_query_parsed("
|
|
CREATE TABLE IF NOT EXISTS $table_alias_domain (
|
|
`alias_domain` varchar(255) {LATIN1} NOT NULL default '',
|
|
`target_domain` varchar(255) {LATIN1} NOT NULL default '',
|
|
`created` {DATETIME},
|
|
`modified` {DATETIME},
|
|
`active` tinyint(1) NOT NULL default '1',
|
|
PRIMARY KEY (`alias_domain`),
|
|
KEY `active` (`active`),
|
|
KEY `target_domain` (`target_domain`)
|
|
) {COLLATE} COMMENT='Postfix Admin - Domain Aliases'
|
|
");
|
|
}
|
|
|
|
/**
|
|
* Create alias_domain table - PgSQL
|
|
* function upgrade_362_pgsql() # renamed to _438 to make sure it runs after an upgrade from 2.2.x
|
|
* @return void
|
|
*/
|
|
function upgrade_438_pgsql() {
|
|
# Table structure for table alias_domain
|
|
$table_alias_domain = table_by_key('alias_domain');
|
|
$table_domain = table_by_key('domain');
|
|
if (_pgsql_object_exists($table_alias_domain)) {
|
|
return;
|
|
}
|
|
db_query_parsed("
|
|
CREATE TABLE $table_alias_domain (
|
|
alias_domain character varying(255) NOT NULL REFERENCES $table_domain(domain) ON DELETE CASCADE,
|
|
target_domain character varying(255) NOT NULL REFERENCES $table_domain(domain) ON DELETE CASCADE,
|
|
created timestamp with time zone default now(),
|
|
modified timestamp with time zone default now(),
|
|
active boolean NOT NULL default true,
|
|
PRIMARY KEY(alias_domain))");
|
|
db_query_parsed("CREATE INDEX alias_domain_active ON $table_alias_domain(alias_domain,active)");
|
|
db_query_parsed("COMMENT ON TABLE $table_alias_domain IS 'Postfix Admin - Domain Aliases'");
|
|
}
|
|
|
|
/**
|
|
* Change description fields to UTF-8
|
|
* @return void
|
|
*/
|
|
function upgrade_373_mysql() { # MySQL only
|
|
$table_domain = table_by_key('domain');
|
|
$table_mailbox = table_by_key('mailbox');
|
|
|
|
$all_sql = explode("\n", trim("
|
|
ALTER TABLE $table_domain CHANGE `description` `description` VARCHAR( 255 ) {UTF-8} NOT NULL
|
|
ALTER TABLE $table_mailbox CHANGE `name` `name` VARCHAR( 255 ) {UTF-8} NOT NULL
|
|
"));
|
|
|
|
foreach ($all_sql as $sql) {
|
|
db_query_parsed($sql);
|
|
}
|
|
}
|
|
|
|
|
|
/**
|
|
* add ssl option for fetchmail
|
|
* @return void
|
|
*/
|
|
function upgrade_439_mysql() {
|
|
$table_fetchmail = table_by_key('fetchmail');
|
|
if (!_mysql_field_exists($table_fetchmail, 'ssl')) {
|
|
db_query_parsed("ALTER TABLE $table_fetchmail ADD `ssl` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0' AFTER `protocol` ; ");
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_439_pgsql() {
|
|
$table_fetchmail = table_by_key('fetchmail');
|
|
if (!_pgsql_field_exists($table_fetchmail, 'ssl')) {
|
|
db_query_parsed("ALTER TABLE $table_fetchmail ADD COLUMN ssl BOOLEAN NOT NULL DEFAULT false");
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_473_mysql() {
|
|
$table_admin = table_by_key('admin');
|
|
$table_alias = table_by_key('alias');
|
|
$table_al_dom = table_by_key('alias_domain');
|
|
$table_domain = table_by_key('domain');
|
|
$table_dom_adm = table_by_key('domain_admins');
|
|
$table_fmail = table_by_key('fetchmail');
|
|
$table_mailbox = table_by_key('mailbox');
|
|
$table_log = table_by_key('log');
|
|
|
|
# tables were created without explicit charset before :-(
|
|
$all_sql = explode("\n", trim("
|
|
ALTER TABLE $table_admin CHANGE `username` `username` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_admin CHANGE `password` `password` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_admin DEFAULT {LATIN1}
|
|
ALTER TABLE $table_alias CHANGE `address` `address` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_alias CHANGE `goto` `goto` TEXT {LATIN1} NOT NULL
|
|
ALTER TABLE $table_alias CHANGE `domain` `domain` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_alias DEFAULT {LATIN1}
|
|
ALTER TABLE $table_al_dom CHANGE `alias_domain` `alias_domain` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_al_dom CHANGE `target_domain` `target_domain` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_al_dom DEFAULT {LATIN1}
|
|
ALTER TABLE $table_domain CHANGE `domain` `domain` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_domain CHANGE `transport` `transport` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_domain DEFAULT {LATIN1}
|
|
ALTER TABLE $table_dom_adm CHANGE `username` `username` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_dom_adm CHANGE `domain` `domain` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_dom_adm DEFAULT {LATIN1}
|
|
ALTER TABLE $table_log CHANGE `username` `username` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_log CHANGE `domain` `domain` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_log CHANGE `action` `action` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_log CHANGE `data` `data` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_log DEFAULT {LATIN1}
|
|
ALTER TABLE $table_mailbox CHANGE `username` `username` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_mailbox CHANGE `password` `password` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_mailbox CHANGE `maildir` `maildir` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_mailbox CHANGE `domain` `domain` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_mailbox DEFAULT {LATIN1}
|
|
ALTER TABLE $table_fmail CHANGE `mailbox` `mailbox` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_fmail CHANGE `src_server` `src_server` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_fmail CHANGE `src_user` `src_user` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_fmail CHANGE `src_password` `src_password` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_fmail CHANGE `src_folder` `src_folder` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_fmail CHANGE `mda` `mda` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_fmail CHANGE `mailbox` `mailbox` VARCHAR( 255 ) {LATIN1} NOT NULL
|
|
ALTER TABLE $table_fmail CHANGE `extra_options` `extra_options` TEXT {LATIN1} NULL DEFAULT NULL
|
|
ALTER TABLE $table_fmail CHANGE `returned_text` `returned_text` TEXT {LATIN1} NULL DEFAULT NULL
|
|
ALTER TABLE $table_fmail DEFAULT {LATIN1}
|
|
"));
|
|
|
|
foreach ($all_sql as $sql) {
|
|
db_query_parsed($sql);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_479_mysql() {
|
|
# ssl is a reserved word in MySQL and causes several problems. Renaming the field...
|
|
$table_fmail = table_by_key('fetchmail');
|
|
if (!_mysql_field_exists($table_fmail, 'usessl')) {
|
|
db_query_parsed("ALTER TABLE $table_fmail CHANGE `ssl` `usessl` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0'");
|
|
}
|
|
}
|
|
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_479_pgsql() {
|
|
$table_fmail = table_by_key('fetchmail');
|
|
if (!_pgsql_field_exists($table_fmail, 'usessl')) {
|
|
db_query_parsed("alter table $table_fmail rename column ssl to usessl");
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_483_mysql() {
|
|
$table_log = table_by_key('log');
|
|
db_query_parsed("ALTER TABLE $table_log CHANGE `data` `data` TEXT {LATIN1} NOT NULL");
|
|
}
|
|
|
|
/**
|
|
* Add a local_part field to the mailbox table, and populate it with the local part of the user's address.
|
|
* This is to make it easier (hopefully) to change the filesystem location of a mailbox in the future
|
|
* See https://sourceforge.net/forum/message.php?msg_id=5394663
|
|
* @return void
|
|
*/
|
|
function upgrade_495_pgsql() {
|
|
$table_mailbox = table_by_key('mailbox');
|
|
if (!_pgsql_field_exists($table_mailbox, 'local_part')) {
|
|
db_query_parsed("ALTER TABLE $table_mailbox add column local_part varchar(255) ");
|
|
db_query_parsed("UPDATE $table_mailbox SET local_part = substring(username from '^(.*)@')");
|
|
db_query_parsed("ALTER TABLE $table_mailbox alter column local_part SET NOT NULL");
|
|
}
|
|
}
|
|
/**
|
|
* See https://sourceforge.net/forum/message.php?msg_id=5394663
|
|
* @return void
|
|
*/
|
|
function upgrade_495_mysql() {
|
|
$table_mailbox = table_by_key('mailbox');
|
|
if (!_mysql_field_exists($table_mailbox, 'local_part')) {
|
|
db_query_parsed("ALTER TABLE $table_mailbox add local_part varchar(255) AFTER quota"); // allow to be null
|
|
db_query_parsed("UPDATE $table_mailbox SET local_part = substring_index(username, '@', 1)");
|
|
db_query_parsed("ALTER TABLE $table_mailbox change local_part local_part varchar(255) NOT NULL"); // remove null-ness...
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_504_mysql() {
|
|
$table_mailbox = table_by_key('mailbox');
|
|
db_query_parsed("ALTER TABLE $table_mailbox CHANGE `local_part` `local_part` VARCHAR( 255 ) {LATIN1} NOT NULL");
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_655_mysql_pgsql() {
|
|
db_query_parsed(_add_index('mailbox', 'domain', 'domain'));
|
|
db_query_parsed(_add_index('alias', 'domain', 'domain'));
|
|
}
|
|
|
|
/*
|
|
function number too small for upgrades from 2.3.x
|
|
-> adding activefrom and activeuntil to vacation table is now upgrade_964
|
|
-> the tables client_access, from_access, helo_access, rcpt_access, user_whitelist
|
|
are not used by PostfixAdmin - no replacement function needed
|
|
Note: Please never remove this function, even if it is disabled - it might be needed in case we have to debug a broken database upgrade etc.
|
|
Note: there never was a function upgrade_727_pgsql()
|
|
|
|
function upgrade_727_mysql() {
|
|
$table_vacation = table_by_key('vacation');
|
|
if(!_mysql_field_exists($table_vacation, 'activefrom')) {
|
|
db_query_parsed("ALTER TABLE $table_vacation add activefrom datetime default NULL");
|
|
}
|
|
if(!_mysql_field_exists($table_vacation, 'activeuntil')) {
|
|
db_query_parsed("ALTER TABLE $table_vacation add activeuntil datetime default NULL");
|
|
}
|
|
|
|
# the following tables are not used by postfixadmin
|
|
|
|
$table_client_access = table_by_key('client_access');
|
|
db_query_parsed("
|
|
CREATE TABLE IF NOT EXISTS $table_client_access (
|
|
`client` char(50) NOT NULL,
|
|
`action` char(50) NOT NULL default 'REJECT',
|
|
UNIQUE KEY `client` (`client`)
|
|
) COMMENT='Postfix Admin - Client Access'
|
|
");
|
|
$table_from_access = table_by_key('from_access');
|
|
db_query_parsed("
|
|
CREATE TABLE IF NOT EXISTS $table_from_access (
|
|
`from_access` char(50) NOT NULL,
|
|
`action` char(50) NOT NULL default 'REJECT',
|
|
UNIQUE KEY `from_access` (`from_access`)
|
|
) COMMENT='Postfix Admin - From Access'
|
|
");
|
|
$table_helo_access = table_by_key('helo_access');
|
|
db_query_parsed("
|
|
CREATE TABLE IF NOT EXISTS $table_helo_access (
|
|
`helo` char(50) NOT NULL,
|
|
`action` char(50) NOT NULL default 'REJECT',
|
|
UNIQUE KEY `helo` (`helo`)
|
|
) COMMENT='Postfix Admin - Helo Access'
|
|
");
|
|
$table_rcpt_access = table_by_key('rcpt_access');
|
|
db_query_parsed("
|
|
CREATE TABLE IF NOT EXISTS $table_rcpt_access (
|
|
`rcpt` char(50) NOT NULL,
|
|
`action` char(50) NOT NULL default 'REJECT',
|
|
UNIQUE KEY `rcpt` (`rcpt`)
|
|
) COMMENT='Postfix Admin - Recipient Access'
|
|
");
|
|
$table_user_whitelist = table_by_key('user_whitelist');
|
|
db_query_parsed("
|
|
CREATE TABLE IF NOT EXISTS $table_user_whitelist (
|
|
`recipient` char(50) NOT NULL,
|
|
`action` char(50) NOT NULL default 'REJECT',
|
|
UNIQUE KEY `recipient` (`recipient`)
|
|
) COMMENT='Postfix Admin - User whitelist'
|
|
");
|
|
}
|
|
*/
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_729_mysql_pgsql() {
|
|
$table_quota = table_by_key('quota');
|
|
$table_quota2 = table_by_key('quota2');
|
|
|
|
# table for dovecot v1.0 & 1.1
|
|
# note: quota table created with old versions of upgrade.php (before r1605)
|
|
# will not have explicit "NOT NULL DEFAULT 0" for the "current" field
|
|
# (shouldn't hurt)
|
|
db_query_parsed("
|
|
CREATE TABLE {IF_NOT_EXISTS} $table_quota (
|
|
username VARCHAR(255) {LATIN1} NOT NULL,
|
|
path VARCHAR(100) {LATIN1} NOT NULL,
|
|
current {BIGINT},
|
|
PRIMARY KEY (username, path)
|
|
) {COLLATE} ;
|
|
");
|
|
|
|
# table for dovecot >= 1.2
|
|
db_query_parsed("
|
|
CREATE TABLE {IF_NOT_EXISTS} $table_quota2 (
|
|
username VARCHAR(100) {LATIN1} NOT NULL,
|
|
bytes {BIGINT},
|
|
messages integer NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (username)
|
|
) ;
|
|
");
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_730_pgsql() {
|
|
$table_quota = table_by_key('quota');
|
|
$table_quota2 = table_by_key('quota2');
|
|
|
|
db_query_parsed('CREATE LANGUAGE plpgsql', 1); /* will error if plpgsql is already installed */
|
|
|
|
# trigger for dovecot v1.0 & 1.1 quota table
|
|
# taken from http://wiki.dovecot.org/Quota/Dict
|
|
db_query_parsed("
|
|
CREATE OR REPLACE FUNCTION merge_quota() RETURNS TRIGGER AS \$merge_quota\$
|
|
BEGIN
|
|
UPDATE $table_quota SET current = NEW.current + current WHERE username = NEW.username AND path = NEW.path;
|
|
IF found THEN
|
|
RETURN NULL;
|
|
ELSE
|
|
RETURN NEW;
|
|
END IF;
|
|
END;
|
|
\$merge_quota\$ LANGUAGE plpgsql;
|
|
");
|
|
db_query_parsed("
|
|
CREATE TRIGGER mergequota BEFORE INSERT ON $table_quota FOR EACH ROW EXECUTE PROCEDURE merge_quota();
|
|
");
|
|
|
|
# trigger for dovecot >= 1.2 quota table
|
|
# taken from http://wiki.dovecot.org/Quota/Dict, table/trigger name changed to quota2 naming
|
|
db_query_parsed("
|
|
CREATE OR REPLACE FUNCTION merge_quota2() RETURNS TRIGGER AS \$\$
|
|
BEGIN
|
|
IF NEW.messages < 0 OR NEW.messages IS NULL THEN
|
|
-- ugly kludge: we came here from this function, really do try to insert
|
|
IF NEW.messages IS NULL THEN
|
|
NEW.messages = 0;
|
|
ELSE
|
|
NEW.messages = -NEW.messages;
|
|
END IF;
|
|
return NEW;
|
|
END IF;
|
|
|
|
LOOP
|
|
UPDATE $table_quota2 SET bytes = bytes + NEW.bytes,
|
|
messages = messages + NEW.messages
|
|
WHERE username = NEW.username;
|
|
IF found THEN
|
|
RETURN NULL;
|
|
END IF;
|
|
|
|
BEGIN
|
|
IF NEW.messages = 0 THEN
|
|
INSERT INTO $table_quota2 (bytes, messages, username) VALUES (NEW.bytes, NULL, NEW.username);
|
|
ELSE
|
|
INSERT INTO $table_quota2 (bytes, messages, username) VALUES (NEW.bytes, -NEW.messages, NEW.username);
|
|
END IF;
|
|
return NULL;
|
|
EXCEPTION WHEN unique_violation THEN
|
|
-- someone just inserted the record, update it
|
|
END;
|
|
END LOOP;
|
|
END;
|
|
\$\$ LANGUAGE plpgsql;
|
|
");
|
|
|
|
db_query_parsed("
|
|
CREATE TRIGGER mergequota2 BEFORE INSERT ON $table_quota2
|
|
FOR EACH ROW EXECUTE PROCEDURE merge_quota2();
|
|
");
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_945_mysql_pgsql() {
|
|
_db_add_field('vacation', 'modified', '{DATECURRENT}', 'created');
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_946_mysql_pgsql() {
|
|
# taken from upgrade_727_mysql, needs to be done for all databases
|
|
_db_add_field('vacation', 'activefrom', '{DATE}', 'body');
|
|
_db_add_field('vacation', 'activeuntil', '{DATEFUTURE}', 'activefrom');
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_968_pgsql() {
|
|
# pgsql counterpart for upgrade_169_mysql() - allow really big quota
|
|
$table_domain = table_by_key('domain');
|
|
$table_mailbox = table_by_key('mailbox');
|
|
db_query_parsed("ALTER TABLE $table_domain ALTER COLUMN quota type bigint");
|
|
db_query_parsed("ALTER TABLE $table_domain ALTER COLUMN maxquota type bigint");
|
|
db_query_parsed("ALTER TABLE $table_mailbox ALTER COLUMN quota type bigint");
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_1050_mysql_pgsql() {
|
|
db_query_parsed(_add_index('log', 'domain_timestamp', 'domain,timestamp'));
|
|
}
|
|
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_1283_mysql_pgsql() {
|
|
_db_add_field('admin', 'superadmin', '{BOOLEAN}', 'password');
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_1284_mysql_pgsql() {
|
|
# migrate the ALL domain to the superadmin column
|
|
# Note: The ALL domain is not (yet) deleted to stay backwards-compatible for now (will be done in a later upgrade function)
|
|
|
|
$result = db_query_all("SELECT username FROM " . table_by_key('domain_admins') . " where domain='ALL'");
|
|
|
|
foreach ($result as $row) {
|
|
printdebug("Setting superadmin flag for " . $row['username']);
|
|
db_update('admin', 'username', $row['username'], array('superadmin' => db_get_boolean(true)));
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_1345_mysql() {
|
|
# $table_vacation = table_by_key('vacation');
|
|
# adding and usage of reply_type field removed in r1610
|
|
# db_query_parsed("ALTER TABLE `$table_vacation` ADD `reply_type` VARCHAR( 20 ) NOT NULL AFTER `domain` ");
|
|
# obsoleted by upgrade_1610()
|
|
# db_query_parsed("ALTER TABLE `$table_vacation` ADD `interval_time` INT NOT NULL DEFAULT '0' AFTER `reply_type` ");
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_1519_mysql_pgsql() {
|
|
_db_add_field('fetchmail', 'sslcertck', '{BOOLEAN}', 'usessl');
|
|
_db_add_field('fetchmail', 'sslcertpath', "VARCHAR(255) {UTF-8} DEFAULT ''", 'sslcertck');
|
|
_db_add_field('fetchmail', 'sslfingerprint', "VARCHAR(255) {LATIN1} DEFAULT ''", 'sslcertpath');
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_1610_mysql_pgsql() {
|
|
# obsoletes upgrade_1345_mysql() - which means debug mode could print "field already exists"
|
|
_db_add_field('vacation', 'interval_time', '{INT}', 'domain');
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_1685_mysql() {
|
|
# Fix existing log entries broken by https://sourceforge.net/p/postfixadmin/bugs/317/
|
|
$table = table_by_key('log');
|
|
db_query_parsed("UPDATE $table SET data = domain WHERE data = '' AND domain LIKE '%@%'");
|
|
db_query_parsed("UPDATE $table SET domain=SUBSTRING_INDEX(domain, '@', -1) WHERE domain=data;");
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_1685_pgsql() {
|
|
$table = table_by_key('log');
|
|
db_query_parsed("UPDATE $table SET data = domain WHERE data = '' AND domain LIKE '%@%'");
|
|
db_query_parsed("UPDATE $table SET domain=SPLIT_PART(domain, '@', 2) WHERE domain=data;");
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_1761_mysql() {
|
|
# upgrade_1762 adds the 'modified' column as {DATECURRENT}, therefore we first need to change
|
|
# 'date' to {DATE} (mysql only allows one {DATECURRENT} column per table)
|
|
$table_fetchmail = table_by_key('fetchmail');
|
|
db_query_parsed("ALTER TABLE $table_fetchmail CHANGE `date` `date` {DATE}");
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_1762_mysql_pgsql() {
|
|
_db_add_field('fetchmail', 'domain', "VARCHAR(255) {LATIN1} DEFAULT ''", 'id');
|
|
_db_add_field('fetchmail', 'active', '{BOOLEAN}', 'date');
|
|
_db_add_field('fetchmail', 'created', '{DATE}', 'date');
|
|
|
|
# If you followed SVN and got upgrade failures here, you might need to
|
|
# UPDATE config SET value=1760 WHERE name='version';
|
|
# and run setup.php again (upgrade_1761_mysql was added later).
|
|
_db_add_field('fetchmail', 'modified', '{DATECURRENT}', 'created');
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_1763_mysql() {
|
|
$table = table_by_key('fetchmail');
|
|
db_query_parsed("UPDATE $table SET domain=SUBSTRING_INDEX(mailbox, '@', -1) WHERE domain='';");
|
|
}
|
|
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_1763_pgsql() {
|
|
$table = table_by_key('fetchmail');
|
|
db_query_parsed("UPDATE $table SET domain=SPLIT_PART(mailbox, '@', 2) WHERE domain='';");
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_1767_mysql_pgsql() {
|
|
# 'active' was just added, so make sure all existing jobs stay active
|
|
$table = table_by_key('fetchmail');
|
|
db_query_parsed("UPDATE $table SET active='{BOOL_TRUE}'");
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_1795_mysql() {
|
|
# upgrade_1761_mysql() was added later (in r1795) - make sure it runs for everybody
|
|
# (running it twice doesn't hurt)
|
|
upgrade_1761_mysql();
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_1824_sqlite() {
|
|
$admin_table = table_by_key('admin');
|
|
$alias_table = table_by_key('alias');
|
|
$alias_domain_table = table_by_key('alias_domain');
|
|
$domain_table = table_by_key('domain');
|
|
$domain_admins_table = table_by_key('domain_admins');
|
|
$fetchmail_table = table_by_key('fetchmail');
|
|
$log_table = table_by_key('log');
|
|
$mailbox_table = table_by_key('mailbox');
|
|
$quota_table = table_by_key('quota');
|
|
$quota2_table = table_by_key('quota2');
|
|
$vacation_table = table_by_key('vacation');
|
|
$vacation_notification_table = table_by_key('vacation_notification');
|
|
|
|
db_query_parsed("
|
|
CREATE TABLE {IF_NOT_EXISTS} $admin_table (
|
|
`username` varchar(255) NOT NULL,
|
|
`password` varchar(255) NOT NULL,
|
|
`superadmin` {BOOLEAN},
|
|
`created` {DATE},
|
|
`modified` {DATE},
|
|
`active` {BOOLEAN_TRUE},
|
|
{PRIMARY} (`username`));
|
|
");
|
|
|
|
db_query_parsed("
|
|
CREATE TABLE {IF_NOT_EXISTS} $alias_table (
|
|
`address` varchar(255) NOT NULL,
|
|
`goto` {FULLTEXT} NOT NULL,
|
|
`domain` varchar(255) NOT NULL,
|
|
`created` {DATE},
|
|
`modified` {DATE},
|
|
`active` {BOOLEAN_TRUE},
|
|
{PRIMARY} (`address`));
|
|
");
|
|
|
|
db_query_parsed("
|
|
CREATE TABLE {IF_NOT_EXISTS} $alias_domain_table (
|
|
`alias_domain` varchar(255) NOT NULL,
|
|
`target_domain` varchar(255) NOT NULL,
|
|
`created` {DATE},
|
|
`modified` {DATE},
|
|
`active` {BOOLEAN_TRUE},
|
|
{PRIMARY} (`alias_domain`));
|
|
");
|
|
|
|
db_query_parsed("
|
|
CREATE TABLE {IF_NOT_EXISTS} $domain_table (
|
|
`domain` varchar(255) NOT NULL,
|
|
`description` varchar(255) NOT NULL,
|
|
`aliases` {INT},
|
|
`mailboxes` {INT},
|
|
`maxquota` {BIGINT},
|
|
`quota` {BIGINT},
|
|
`transport` varchar(255) NOT NULL,
|
|
`backupmx` {BOOLEAN},
|
|
`created` {DATE},
|
|
`modified` {DATE},
|
|
`active` {BOOLEAN_TRUE},
|
|
{PRIMARY} (`domain`));
|
|
");
|
|
|
|
db_query_parsed("
|
|
CREATE TABLE {IF_NOT_EXISTS} $domain_admins_table (
|
|
`username` varchar(255) NOT NULL,
|
|
`domain` varchar(255) NOT NULL,
|
|
`created` {DATE},
|
|
`active` {BOOLEAN_TRUE});
|
|
");
|
|
|
|
db_query_parsed("
|
|
CREATE TABLE {IF_NOT_EXISTS} $fetchmail_table (
|
|
`id` {AUTOINCREMENT},
|
|
`domain` varchar(255) DEFAULT '',
|
|
`mailbox` varchar(255) NOT NULL,
|
|
`src_server` varchar(255) NOT NULL,
|
|
`src_auth` varchar(255) DEFAULT NULL,
|
|
`src_user` varchar(255) NOT NULL,
|
|
`src_password` varchar(255) NOT NULL,
|
|
`src_folder` varchar(255) NOT NULL,
|
|
`poll_time` int(11) NOT NULL DEFAULT '10',
|
|
`fetchall` {BOOLEAN},
|
|
`keep` {BOOLEAN},
|
|
`protocol` {FULLTEXT} DEFAULT NULL,
|
|
`usessl` {BOOLEAN},
|
|
`sslcertck` {BOOLEAN},
|
|
`sslcertpath` varchar(255) DEFAULT '',
|
|
`sslfingerprint` varchar(255) DEFAULT '',
|
|
`extra_options` {FULLTEXT},
|
|
`returned_text` {FULLTEXT},
|
|
`mda` varchar(255) NOT NULL,
|
|
`date` {DATE},
|
|
`created` {DATE},
|
|
`modified` {DATECURRENT},
|
|
`active` {BOOLEAN});
|
|
");
|
|
|
|
db_query_parsed("
|
|
CREATE TABLE {IF_NOT_EXISTS} $log_table (
|
|
`timestamp` {DATE},
|
|
`username` varchar(255) NOT NULL,
|
|
`domain` varchar(255) NOT NULL,
|
|
`action` varchar(255) NOT NULL,
|
|
`data` {FULLTEXT} NOT NULL);
|
|
");
|
|
|
|
db_query_parsed("
|
|
CREATE TABLE {IF_NOT_EXISTS} $mailbox_table (
|
|
`username` varchar(255) NOT NULL,
|
|
`password` varchar(255) NOT NULL,
|
|
`name` varchar(255) NOT NULL,
|
|
`maildir` varchar(255) NOT NULL,
|
|
`quota` {BIGINT},
|
|
`local_part` varchar(255) NOT NULL,
|
|
`domain` varchar(255) NOT NULL,
|
|
`created` {DATE},
|
|
`modified` {DATE},
|
|
`active` {BOOLEAN_TRUE},
|
|
{PRIMARY} (`username`));
|
|
");
|
|
|
|
db_query_parsed("
|
|
CREATE TABLE {IF_NOT_EXISTS} $quota_table (
|
|
`username` varchar(255) NOT NULL,
|
|
`path` varchar(100) NOT NULL,
|
|
`current` {BIGINT},
|
|
{PRIMARY} (`username`,`path`));
|
|
");
|
|
|
|
db_query_parsed("
|
|
CREATE TABLE {IF_NOT_EXISTS} $quota2_table (
|
|
`username` varchar(255) NOT NULL,
|
|
`bytes` {BIGINT},
|
|
`messages` {INT},
|
|
{PRIMARY} (`username`));
|
|
");
|
|
|
|
db_query_parsed("
|
|
CREATE TABLE {IF_NOT_EXISTS} $vacation_table (
|
|
`email` varchar(255) NOT NULL,
|
|
`subject` varchar(255) NOT NULL,
|
|
`body` {FULLTEXT} NOT NULL,
|
|
`activefrom` {DATE},
|
|
`activeuntil` {DATEFUTURE},
|
|
`cache` {FULLTEXT} NOT NULL DEFAULT '',
|
|
`domain` varchar(255) NOT NULL,
|
|
`interval_time` {INT},
|
|
`created` {DATE},
|
|
`modified` {DATECURRENT},
|
|
`active` {BOOLEAN_TRUE},
|
|
{PRIMARY} (`email`));
|
|
");
|
|
|
|
db_query_parsed("
|
|
CREATE TABLE {IF_NOT_EXISTS} $vacation_notification_table (
|
|
`on_vacation` varchar(255) NOT NULL,
|
|
`notified` varchar(255) NOT NULL,
|
|
`notified_at` {DATECURRENT},
|
|
{PRIMARY} (`on_vacation`,`notified`),
|
|
CONSTRAINT `vacation_notification_pkey` FOREIGN KEY (`on_vacation`) REFERENCES `vacation` (`email`) ON DELETE CASCADE);
|
|
");
|
|
}
|
|
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_1835_mysql() {
|
|
# change default values for existing datetime fields with a 0000-00-00 default to {DATETIME}
|
|
|
|
foreach (array('domain_admins', 'vacation') as $table_to_change) {
|
|
$table = table_by_key($table_to_change);
|
|
db_query_parsed("ALTER TABLE $table CHANGE `created` `created` {DATETIME}");
|
|
}
|
|
|
|
foreach (array('admin', 'alias', 'alias_domain', 'domain', 'mailbox') as $table_to_change) {
|
|
$table = table_by_key($table_to_change);
|
|
db_query_parsed("ALTER TABLE $table CHANGE `created` `created` {DATETIME}, CHANGE `modified` `modified` {DATETIME}");
|
|
}
|
|
|
|
$table = table_by_key('log');
|
|
db_query_parsed("ALTER TABLE $table CHANGE `timestamp` `timestamp` {DATETIME}");
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_1836_mysql() {
|
|
$table_alias_domain = table_by_key('alias_domain');
|
|
$table_vacation_notification = table_by_key('vacation_notification');
|
|
|
|
$all_sql = explode("\n", trim("
|
|
ALTER TABLE $table_alias_domain CHANGE `alias_domain` `alias_domain` VARCHAR(255) {LATIN1} NOT NULL default ''
|
|
ALTER TABLE $table_alias_domain CHANGE `target_domain` `target_domain` VARCHAR(255) {LATIN1} NOT NULL default ''
|
|
ALTER TABLE $table_vacation_notification CHANGE `notified` `notified` VARCHAR(255) {LATIN1} NOT NULL default ''
|
|
"));
|
|
|
|
foreach ($all_sql as $sql) {
|
|
db_query_parsed($sql, true);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_1837() {
|
|
global $CONF;
|
|
|
|
if ($CONF['database_type'] == 'sqlite') {
|
|
return;
|
|
}
|
|
# alternative contact means to reset a forgotten password
|
|
foreach (array('admin', 'mailbox') as $table) {
|
|
_db_add_field($table, 'phone', "varchar(30) {UTF-8} NOT NULL DEFAULT ''", 'active');
|
|
_db_add_field($table, 'email_other', "varchar(255) {UTF-8} NOT NULL DEFAULT ''", 'phone');
|
|
_db_add_field($table, 'token', "varchar(255) {UTF-8} NOT NULL DEFAULT ''", 'email_other');
|
|
_db_add_field($table, 'token_validity', '{DATETIME}', 'token');
|
|
}
|
|
}
|
|
# TODO MySQL:
|
|
# - various varchar fields do not have a default value
|
|
# https://sourceforge.net/projects/postfixadmin/forums/forum/676076/topic/3419725
|
|
# - change default of all timestamp fields to {DATECURRENT} (CURRENT_TIMESTAMP} or {DATE}
|
|
# including vacation.activefrom/activeuntil (might have a different default as leftover from upgrade_727_mysql)
|
|
# including vacation.modified - should be {DATE}, not {DATECURRENT}
|
|
# https://sourceforge.net/tracker/?func=detail&aid=1699218&group_id=191583&atid=937964
|
|
# @todo vacation.email has 2 indizes
|
|
|
|
|
|
# Upgrading to v1835 & v1836 in sqlite have a couple of peculiarities:
|
|
# - DATE and DATETIME are the same type internally (NUMERIC)
|
|
# - SQLite does not support ALTER COLUMN. At all.
|
|
# TODO: Rename/create anew/migrate/drop tables for v1836... If it matters?
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_1837_sqlite() {
|
|
# Add columns for the alternative contact to reset a forgotten password.
|
|
foreach (array('admin', 'mailbox') as $table_to_change) {
|
|
$table = table_by_key($table_to_change);
|
|
if (!_sqlite_field_exists($table, 'phone')) {
|
|
db_query_parsed("ALTER TABLE `$table` ADD COLUMN `phone` varchar(30) NOT NULL DEFAULT ''");
|
|
}
|
|
if (!_sqlite_field_exists($table, 'email_other')) {
|
|
db_query_parsed("ALTER TABLE `$table` ADD COLUMN `email_other` varchar(255) NOT NULL DEFAULT ''");
|
|
}
|
|
}
|
|
}
|
|
|
|
/**
|
|
* https://github.com/postfixadmin/postfixadmin/issues/89
|
|
* upgrade_1838_mysql() renamed to upgrade_1839() to keep all databases in sync
|
|
* @return void
|
|
*/
|
|
function upgrade_1839() {
|
|
if (!db_sqlite()) {
|
|
_db_add_field('log', 'id', '{AUTOINCREMENT} {PRIMARY}', 'data');
|
|
return;
|
|
}
|
|
|
|
/* ONLY FOR Sqlite */
|
|
// probably didn't have a working log table, so drop it and recreate with an id field.
|
|
$log_table = table_by_key('log');
|
|
db_query_parsed("DROP TABLE IF EXISTS $log_table");
|
|
|
|
db_query_parsed("
|
|
CREATE TABLE $log_table (
|
|
`id` {AUTOINCREMENT},
|
|
`timestamp` {DATE},
|
|
`username` varchar(255) NOT NULL,
|
|
`domain` varchar(255) NOT NULL,
|
|
`action` varchar(255) NOT NULL,
|
|
`data` {FULLTEXT} NOT NULL);
|
|
");
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_1840_mysql_pgsql() {
|
|
# sqlite doesn't support changing the default value
|
|
$vacation = table_by_key('vacation');
|
|
db_query_parsed("ALTER TABLE $vacation ALTER COLUMN activeuntil SET DEFAULT '2038-01-18'");
|
|
}
|
|
|
|
/**
|
|
* try and fix: https://github.com/postfixadmin/postfixadmin/issues/177 - sqlite missing columns
|
|
* @return void
|
|
*/
|
|
function upgrade_1841_sqlite() {
|
|
foreach (array('admin', 'mailbox') as $table) {
|
|
_db_add_field($table, 'phone', "varchar(30) {UTF-8} NOT NULL DEFAULT ''", 'active');
|
|
_db_add_field($table, 'email_other', "varchar(255) {UTF-8} NOT NULL DEFAULT ''", 'phone');
|
|
_db_add_field($table, 'token', "varchar(255) {UTF-8} NOT NULL DEFAULT ''", 'email_other');
|
|
_db_add_field($table, 'token_validity', '{DATETIME}', 'token');
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_1842() {
|
|
_db_add_field('mailbox', 'password_expiry', "{DATETIME}"); // when a specific mailbox password expires
|
|
_db_add_field('domain', 'password_expiry', 'int DEFAULT 0'); // expiry applied to mailboxes within that domain
|
|
}
|
|
|
|
/**
|
|
* @return void
|
|
*/
|
|
function upgrade_1843() {
|
|
# Additional field for fetchmail to allow server with non-standard port number
|
|
_db_add_field('fetchmail', 'src_port', "{INT}", 'src_server');
|
|
}
|