2009-12-05 01:08:47 +01:00
< ? php
2022-06-28 14:31:37 +02:00
2019-01-28 00:47:24 +01:00
/* vim: set expandtab softtabstop=4 tabstop=4 shiftwidth=4: */
# Note: run with upgrade.php?debug=1 to see all SQL error messages
2020-11-09 22:50:17 +01:00
require_once ( 'common.php' );
2009-12-05 01:08:47 +01:00
2019-10-19 21:51:05 +02:00
if ( empty ( $CONF )) {
2018-12-27 22:43:11 +01:00
die ( " config.inc.php seems invalid " );
}
2009-12-05 01:08:47 +01:00
2018-01-26 15:45:57 +01:00
/**
2019-01-06 21:25:24 +01:00
* Use this to check whether an object ( table , index etc ) exists within a
2009-12-05 01:08:47 +01:00
* PostgreSQL database .
2018-12-27 22:43:11 +01:00
* @ param string the object name
2009-12-05 01:08:47 +01:00
* @ return boolean true if it exists
*/
function _pgsql_object_exists ( $name ) {
$sql = " select relname from pg_class where relname = ' $name ' " ;
2019-01-06 22:27:08 +01:00
$r = db_query_one ( $sql );
2019-01-06 21:25:24 +01:00
return ! empty ( $r );
2009-12-05 01:08:47 +01:00
}
2018-12-27 22:43:11 +01:00
/**
* @ param string $table
* @ param string $field
* @ return bool
*/
2009-12-05 01:08:47 +01:00
function _pgsql_field_exists ( $table , $field ) {
2012-05-28 17:50:25 +02:00
# $table = table_by_key($table); # _pgsql_field_exists is always called with the expanded table name - don't expand it twice
2009-12-05 01:08:47 +01:00
$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' " ;
2019-01-06 22:27:08 +01:00
$r = db_query_all ( $sql );
2019-01-06 21:25:24 +01:00
return ! empty ( $r );
2009-12-05 01:08:47 +01:00
}
function _mysql_field_exists ( $table , $field ) {
2012-05-28 17:50:25 +02:00
# $table = table_by_key($table); # _mysql_field_exists is always called with the expanded table name - don't expand it twice
2019-01-06 21:25:24 +01:00
$sql = " SHOW COLUMNS FROM $table LIKE ? " ;
2019-01-06 22:27:08 +01:00
$r = db_query_all ( $sql , array ( $field ));
2011-02-06 18:42:47 +01:00
2019-01-06 21:25:24 +01:00
return ! empty ( $r );
2009-12-05 01:08:47 +01:00
}
2017-10-06 03:46:34 +02:00
function _sqlite_field_exists ( $table , $field ) {
$sql = " PRAGMA table_info( $table ) " ;
2019-01-06 22:27:08 +01:00
$r = db_query_all ( $sql );
2019-01-06 21:25:24 +01:00
2019-01-06 22:03:43 +01:00
foreach ( $r as $row ) {
2019-01-06 22:39:54 +01:00
if ( $row [ 'name' ] == $field ) {
2017-10-06 03:46:34 +02:00
return true ;
}
}
return false ;
}
2011-02-06 18:42:47 +01:00
function _db_field_exists ( $table , $field ) {
global $CONF ;
2018-01-26 15:45:57 +01:00
if ( $CONF [ 'database_type' ] == 'pgsql' ) {
return _pgsql_field_exists ( $table , $field );
2018-05-23 23:10:12 +02:00
} elseif ( $CONF [ 'database_type' ] == 'sqlite' ) {
return _sqlite_field_exists ( $table , $field );
2011-02-06 18:42:47 +01:00
} else {
return _mysql_field_exists ( $table , $field );
}
}
2015-09-18 07:10:13 +02:00
function _upgrade_filter_function ( $name ) {
2018-01-26 15:45:57 +01:00
return preg_match ( '/upgrade_[\d]+(_mysql|_pgsql|_sqlite|_mysql_pgsql)?$/' , $name ) == 1 ;
2015-09-18 07:10:13 +02:00
}
2011-02-06 18:42:47 +01:00
2018-08-28 22:15:45 +02:00
function _db_add_field ( $table , $field , $fieldtype , $after = '' ) {
2011-02-06 18:42:47 +01:00
global $CONF ;
$query = " ALTER TABLE " . table_by_key ( $table ) . " ADD COLUMN $field $fieldtype " ;
2018-08-28 22:15:45 +02:00
if ( $CONF [ 'database_type' ] == 'mysql' && ! empty ( $after )) {
2011-02-06 18:42:47 +01:00
$query .= " AFTER $after " ; # PgSQL does not support to specify where to add the column, MySQL does
}
2018-01-26 15:45:57 +01:00
if ( ! _db_field_exists ( table_by_key ( $table ), $field )) {
2018-12-27 22:43:11 +01:00
db_query_parsed ( $query );
2018-01-26 15:45:57 +01:00
} else {
printdebug ( " field already exists: $table . $field " );
2011-02-06 18:42:47 +01:00
}
}
2018-12-29 22:45:12 +01:00
function echo_out ( $text ) {
if ( defined ( 'PHPUNIT_TEST' )) {
2020-04-14 22:03:10 +02:00
//error_log("" . $text);
2018-12-29 22:45:12 +01:00
} else {
2019-01-03 20:36:40 +01:00
echo $text . " \n " ;
2018-12-29 22:45:12 +01:00
}
}
2011-02-06 18:42:47 +01:00
function printdebug ( $text ) {
2018-01-26 15:45:57 +01:00
if ( safeget ( 'debug' ) != " " ) {
2018-12-29 22:45:12 +01:00
echo_out ( " <p style='color:#999'> $text </p> " );
2018-01-26 15:45:57 +01:00
}
2011-02-06 18:42:47 +01:00
}
2009-12-05 01:08:47 +01:00
$table = table_by_key ( 'config' );
2018-01-26 15:45:57 +01:00
if ( $CONF [ 'database_type' ] == 'pgsql' ) {
2009-12-05 01:08:47 +01:00
// check if table already exists, if so, don't recreate it
2019-01-06 22:03:43 +01:00
if ( ! _pgsql_object_exists ( $table )) {
2009-12-05 01:08:47 +01:00
$pgsql = "
CREATE TABLE $table (
id SERIAL ,
name VARCHAR ( 20 ) NOT NULL UNIQUE ,
value VARCHAR ( 20 ) NOT NULL ,
PRIMARY KEY ( id )
) " ;
db_query_parsed ( $pgsql );
}
2018-01-26 15:45:57 +01:00
} elseif ( db_sqlite ()) {
2016-02-04 23:30:06 +01:00
$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 {
2009-12-05 01:08:47 +01:00
$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` )
)
" ;
2018-01-25 22:08:26 +01:00
db_query_parsed ( $mysql , 0 , " COMMENT = 'PostfixAdmin settings' " );
2009-12-05 01:08:47 +01:00
}
2018-01-26 15:45:57 +01:00
$version = check_db_version ( false );
2009-12-05 01:08:47 +01:00
_do_upgrade ( $version );
function _do_upgrade ( $current_version ) {
global $CONF ;
2015-02-17 12:03:16 +01:00
$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$');
2015-09-18 07:10:13 +02:00
$funclist = get_defined_functions ();
$our_upgrade_functions = array_filter ( $funclist [ 'user' ], '_upgrade_filter_function' );
2018-01-26 15:45:57 +01:00
foreach ( $our_upgrade_functions as $function_name ) {
2015-02-17 12:03:16 +01:00
$bits = explode ( " _ " , $function_name );
$function_number = $bits [ 1 ];
2022-12-06 09:56:23 +01:00
if ( is_numeric ( $function_number )) {
$target_version = max ( $target_version , $function_number );
2015-02-17 12:03:16 +01:00
}
}
2009-12-05 01:08:47 +01:00
if ( $current_version >= $target_version ) {
# already up to date
2018-12-29 22:45:12 +01:00
echo_out ( " <p>Database is up to date: $current_version / $target_version </p> " );
2009-12-05 01:08:47 +01:00
return true ;
}
2018-12-29 22:45:12 +01:00
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> " );
2009-12-05 01:08:47 +01:00
2016-02-04 23:30:06 +01:00
if ( db_sqlite () && $current_version < 1824 ) {
// Fast forward to the first revision supporting SQLite
$current_version = 1823 ;
}
2009-12-05 01:08:47 +01:00
for ( $i = $current_version + 1 ; $i <= $target_version ; $i ++ ) {
$function = " upgrade_ $i " ;
2016-02-04 23:30:06 +01:00
$function_mysql_pgsql = $function . " _mysql_pgsql " ;
2009-12-05 01:08:47 +01:00
$function_mysql = $function . " _mysql " ;
$function_pgsql = $function . " _pgsql " ;
2016-02-04 23:30:06 +01:00
$function_sqlite = $function . " _sqlite " ;
2009-12-05 01:08:47 +01:00
if ( function_exists ( $function )) {
2018-12-29 22:45:12 +01:00
echo_out ( " <p>updating to version $i (all databases)... " );
2009-12-05 01:08:47 +01:00
$function ();
2018-12-29 22:45:12 +01:00
echo_out ( " done " );
2009-12-05 01:08:47 +01:00
}
2016-02-04 23:30:06 +01:00
if ( $CONF [ 'database_type' ] == 'mysql' || $CONF [ 'database_type' ] == 'mysqli' || $CONF [ 'database_type' ] == 'pgsql' ) {
if ( function_exists ( $function_mysql_pgsql )) {
2018-12-29 22:45:12 +01:00
echo_out ( " <p>updating to version $i (MySQL and PgSQL)... " );
2016-02-04 23:30:06 +01:00
$function_mysql_pgsql ();
2018-12-29 22:45:12 +01:00
echo_out ( " done " );
2016-02-04 23:30:06 +01:00
}
}
2018-01-26 15:45:57 +01:00
if ( $CONF [ 'database_type' ] == 'mysql' || $CONF [ 'database_type' ] == 'mysqli' ) {
2009-12-05 01:08:47 +01:00
if ( function_exists ( $function_mysql )) {
2018-12-29 22:45:12 +01:00
echo_out ( " <p>updating to version $i (MySQL)... " );
2009-12-05 01:08:47 +01:00
$function_mysql ();
2018-12-29 22:45:12 +01:00
echo_out ( " done " );
2009-12-05 01:08:47 +01:00
}
2018-01-26 15:45:57 +01:00
} elseif ( db_sqlite ()) {
2016-02-04 23:30:06 +01:00
if ( function_exists ( $function_sqlite )) {
2018-12-29 22:45:12 +01:00
echo_out ( " <p>updating to version $i (SQLite)... " );
2016-02-04 23:30:06 +01:00
$function_sqlite ();
2018-12-29 22:45:12 +01:00
echo_out ( " done " );
2016-02-04 23:30:06 +01:00
}
2018-01-26 15:45:57 +01:00
} elseif ( $CONF [ 'database_type' ] == 'pgsql' ) {
2009-12-05 01:08:47 +01:00
if ( function_exists ( $function_pgsql )) {
2018-12-29 22:45:12 +01:00
echo_out ( " <p>updating to version $i (PgSQL)... " );
2009-12-05 01:08:47 +01:00
$function_pgsql ();
2018-12-29 22:45:12 +01:00
echo_out ( " done " );
2009-12-05 01:08:47 +01:00
}
2018-01-26 15:45:57 +01:00
}
2009-12-05 01:08:47 +01:00
// Update config table so we don't run the same query twice in the future.
$table = table_by_key ( 'config' );
2019-01-06 22:27:08 +01:00
$sql = " UPDATE $table SET value = :value WHERE name = 'version' " ;
db_execute ( $sql , array ( 'value' => $i ));
2009-12-05 01:08:47 +01:00
};
}
/**
* Replaces database specific parts in a query
2018-12-27 22:43:11 +01:00
* @ param string sql query with placeholders
2009-12-05 01:08:47 +01:00
* @ param int ( optional ) whether errors should be ignored ( 0 = false )
2018-12-27 22:43:11 +01:00
* @ param string ( optional ) MySQL specific code to attach , useful for COMMENT = on CREATE TABLE
* @ return void
2018-01-26 15:45:57 +01:00
*/
2009-12-05 01:08:47 +01:00
function db_query_parsed ( $sql , $ignore_errors = 0 , $attach_mysql = " " ) {
global $CONF ;
2019-01-03 20:36:40 +01:00
if ( db_mysql ()) {
2009-12-05 01:08:47 +01:00
$replace = array (
2018-01-26 15:45:57 +01:00
'{AUTOINCREMENT}' => 'int(11) not null auto_increment' ,
2009-12-05 01:08:47 +01:00
'{PRIMARY}' => 'primary key' ,
2018-01-26 15:45:57 +01:00
'{UNSIGNED}' => 'unsigned' ,
'{FULLTEXT}' => 'FULLTEXT' ,
'{BOOLEAN}' => " tinyint(1) NOT NULL DEFAULT ' " . db_get_boolean ( false ) . " ' " ,
2009-12-05 01:08:47 +01:00
'{UTF-8}' => '/*!40100 CHARACTER SET utf8 */' ,
2020-02-12 22:15:04 +01:00
'{LATIN1}' => '/*!40100 CHARACTER SET latin1 COLLATE latin1_general_ci */' ,
2009-12-05 01:08:47 +01:00
'{IF_NOT_EXISTS}' => 'IF NOT EXISTS' ,
'{RENAME_COLUMN}' => 'CHANGE COLUMN' ,
2018-01-25 22:08:26 +01:00
'{MYISAM}' => '' ,
2009-12-05 01:08:47 +01:00
'{INNODB}' => 'ENGINE=InnoDB' ,
2013-12-08 23:12:24 +01:00
'{INT}' => 'integer NOT NULL DEFAULT 0' ,
'{BIGINT}' => 'bigint NOT NULL DEFAULT 0' ,
2016-04-25 23:20:25 +02:00
'{DATETIME}' => " datetime NOT NULL default '2000-01-01 00:00:00' " , # different from {DATE} only for MySQL
2011-02-06 20:07:44 +01:00
'{DATE}' => " timestamp NOT NULL default '2000-01-01' " , # MySQL needs a sane default (no default is interpreted as CURRENT_TIMESTAMP, which is ...
2018-01-25 21:02:13 +01:00
'{DATEFUTURE}' => " timestamp NOT NULL default '2038-01-18' " , # different default timestamp for vacation.activeuntil
2011-02-06 20:07:44 +01:00
'{DATECURRENT}' => 'timestamp NOT NULL default CURRENT_TIMESTAMP' , # only allowed once per table in MySQL
2020-02-11 21:01:59 +01:00
'{COLLATE}' => " CHARACTER SET latin1 COLLATE latin1_general_ci " , # just incase someone has a unicode collation set.
2011-02-06 18:42:47 +01:00
);
2009-12-05 01:08:47 +01:00
$sql = " $sql $attach_mysql " ;
2018-01-26 15:45:57 +01:00
} elseif ( db_sqlite ()) {
2016-02-04 23:30:06 +01:00
$replace = array (
'{AUTOINCREMENT}' => 'integer PRIMARY KEY AUTOINCREMENT NOT NULL' ,
'{PRIMARY}' => 'PRIMARY KEY' ,
'{UNSIGNED}' => 'unsigned' ,
'{FULLTEXT}' => 'text' ,
2018-01-26 15:45:57 +01:00
'{BOOLEAN}' => " tinyint(1) NOT NULL DEFAULT ' " . db_get_boolean ( false ) . " ' " ,
'{BOOLEAN_TRUE}' => " tinyint(1) NOT NULL DEFAULT ' " . db_get_boolean ( true ) . " ' " ,
2016-02-04 23:30:06 +01:00
'{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' ,
2016-04-25 23:20:25 +02:00
'{DATETIME}' => " datetime NOT NULL default '2000-01-01' " ,
2016-02-04 23:30:06 +01:00
'{DATE}' => " datetime NOT NULL default '2000-01-01' " ,
2018-01-25 21:02:13 +01:00
'{DATEFUTURE}' => " datetime NOT NULL default '2038-01-18' " , # different default timestamp for vacation.activeuntil
2016-02-04 23:30:06 +01:00
'{DATECURRENT}' => 'datetime NOT NULL default CURRENT_TIMESTAMP' ,
2020-02-11 21:01:59 +01:00
'{COLLATE}' => ''
2016-02-04 23:30:06 +01:00
);
2018-01-26 15:45:57 +01:00
} elseif ( $CONF [ 'database_type' ] == 'pgsql' ) {
2009-12-05 01:08:47 +01:00
$replace = array (
2018-01-26 15:45:57 +01:00
'{AUTOINCREMENT}' => 'SERIAL' ,
'{PRIMARY}' => 'primary key' ,
'{UNSIGNED}' => '' ,
'{FULLTEXT}' => '' ,
'{BOOLEAN}' => " BOOLEAN NOT NULL DEFAULT ' " . db_get_boolean ( false ) . " ' " ,
2009-12-05 01:08:47 +01:00
'{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}' => '' ,
2013-12-08 23:12:24 +01:00
'{INT}' => 'integer NOT NULL DEFAULT 0' ,
'{BIGINT}' => 'bigint NOT NULL DEFAULT 0' ,
2009-12-05 01:08:47 +01:00
'int(1)' => 'int' ,
2018-01-26 15:45:57 +01:00
'int(10)' => 'int' ,
'int(11)' => 'int' ,
'int(4)' => 'int' ,
2016-04-25 23:20:25 +02:00
'{DATETIME}' => " timestamp with time zone default '2000-01-01' " , # stay in sync with MySQL
2011-02-06 20:07:44 +01:00
'{DATE}' => " timestamp with time zone default '2000-01-01' " , # stay in sync with MySQL
2018-01-25 21:02:13 +01:00
'{DATEFUTURE}' => " timestamp with time zone default '2038-01-18' " , # stay in sync with MySQL
2011-02-06 18:42:47 +01:00
'{DATECURRENT}' => 'timestamp with time zone default now()' ,
2020-02-11 21:01:59 +01:00
'{COLLATE}' => '' ,
2011-02-06 18:42:47 +01:00
);
2009-12-05 01:08:47 +01:00
} else {
2018-12-29 22:45:12 +01:00
echo_out ( " Sorry, unsupported database type " . $CONF [ 'database_type' ]);
2009-12-05 01:08:47 +01:00
exit ;
}
2018-01-26 15:45:57 +01:00
$replace [ '{BOOL_TRUE}' ] = db_get_boolean ( true );
$replace [ '{BOOL_FALSE}' ] = db_get_boolean ( false );
2009-12-05 01:08:47 +01:00
$query = trim ( str_replace ( array_keys ( $replace ), $replace , $sql ));
2011-02-06 18:42:47 +01:00
2018-12-27 22:43:11 +01:00
$debug = safeget ( 'debug' , '' ) != '' ;
if ( $debug ) {
2018-01-26 15:45:57 +01:00
printdebug ( $query );
2009-12-05 01:08:47 +01:00
}
2019-01-06 22:27:08 +01:00
try {
$result = db_execute ( $query , array (), true );
} catch ( PDOException $e ) {
2020-06-15 18:12:42 +02:00
error_log ( " Exception running PostfixAdmin query: $query " . $e );
2019-01-06 22:27:08 +01:00
if ( $debug ) {
echo_out ( " <div style='color:#f00'> " . $e -> getMessage () . " </div> " );
}
2021-05-08 21:11:58 +02:00
throw new \Exception ( " Postfixadmin DB update failed. Please check your PHP error_log " );
2009-12-05 01:08:47 +01:00
}
}
2019-01-16 10:23:09 +01:00
/**
* @ param string $table
* @ param string $index
* @ return string
*/
2018-01-26 15:45:57 +01:00
function _drop_index ( $table , $index ) {
2009-12-05 01:08:47 +01:00
global $CONF ;
2018-01-26 15:45:57 +01:00
$table = table_by_key ( $table );
2009-12-05 01:08:47 +01:00
2018-01-26 15:45:57 +01:00
if ( $CONF [ 'database_type' ] == 'mysql' || $CONF [ 'database_type' ] == 'mysqli' ) {
2009-12-05 01:08:47 +01:00
return " ALTER TABLE $table DROP INDEX $index " ;
2018-01-26 15:45:57 +01:00
} elseif ( $CONF [ 'database_type' ] == 'pgsql' || db_sqlite ()) {
2009-12-05 01:08:47 +01:00
return " DROP INDEX $index " ; # Index names are unique with a DB for PostgreSQL
} else {
2018-12-29 22:45:12 +01:00
echo_out ( " Sorry, unsupported database type " . $CONF [ 'database_type' ]);
2009-12-05 01:08:47 +01:00
exit ;
}
}
2019-01-16 10:23:09 +01:00
/**
* @ return string
* @ param string $table
* @ param string $indexname
2019-01-16 11:31:16 +01:00
* @ param string $fieldlist
2019-01-16 10:23:09 +01:00
*/
2009-12-05 01:08:47 +01:00
function _add_index ( $table , $indexname , $fieldlist ) {
global $CONF ;
2018-01-26 15:45:57 +01:00
$table = table_by_key ( $table );
2009-12-05 01:08:47 +01:00
2018-01-26 15:45:57 +01:00
if ( $CONF [ 'database_type' ] == 'mysql' || $CONF [ 'database_type' ] == 'mysqli' ) {
2011-04-23 20:38:31 +02:00
$fieldlist = str_replace ( ',' , '`,`' , $fieldlist ); # fix quoting if index contains multiple fields
2009-12-05 01:08:47 +01:00
return " ALTER TABLE $table ADD INDEX ` $indexname ` ( ` $fieldlist ` ) " ;
2018-01-26 15:45:57 +01:00
} elseif ( $CONF [ 'database_type' ] == 'pgsql' ) {
2009-12-05 01:08:47 +01:00
$pgindexname = $table . " _ " . $indexname . '_idx' ;
return " CREATE INDEX $pgindexname ON $table ( $fieldlist ); " ; # Index names are unique with a DB for PostgreSQL
} else {
2018-12-29 22:45:12 +01:00
echo_out ( " Sorry, unsupported database type " . $CONF [ 'database_type' ]);
2009-12-05 01:08:47 +01:00
exit ;
}
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2009-12-05 01:08:47 +01:00
function upgrade_1_mysql () {
2017-02-25 22:14:21 +01:00
#
# 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:
2018-01-26 15:45:57 +01:00
return ; # <-- skip running this function at all.
2017-02-25 22:14:21 +01:00
# (remove the above "return" if you really want to start with a pre-2.1 database layout)
2009-12-05 01:08:47 +01:00
// 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 '' ,
2016-04-25 23:20:25 +02:00
`created` { DATETIME },
`modified` { DATETIME },
2009-12-05 01:08:47 +01:00
`active` tinyint ( 1 ) NOT NULL default '1' ,
PRIMARY KEY ( `username` )
2020-02-11 21:01:59 +01:00
) { COLLATE } COMMENT = 'Postfix Admin - Virtual Admins' ; " ;
2009-12-05 01:08:47 +01:00
$sql [] = "
CREATE TABLE { IF_NOT_EXISTS } $alias (
`address` varchar ( 255 ) NOT NULL default '' ,
`goto` text NOT NULL ,
`domain` varchar ( 255 ) NOT NULL default '' ,
2016-04-25 23:20:25 +02:00
`created` { DATETIME },
`modified` { DATETIME },
2009-12-05 01:08:47 +01:00
`active` tinyint ( 1 ) NOT NULL default '1' ,
PRIMARY KEY ( `address` )
2020-02-11 21:01:59 +01:00
) { COLLATE } COMMENT = 'Postfix Admin - Virtual Aliases' ; " ;
2009-12-05 01:08:47 +01:00
$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' ,
2016-04-25 23:20:25 +02:00
`created` { DATETIME },
`modified` { DATETIME },
2009-12-05 01:08:47 +01:00
`active` tinyint ( 1 ) NOT NULL default '1' ,
PRIMARY KEY ( `domain` )
2020-02-11 21:01:59 +01:00
) { COLLATE } COMMENT = 'Postfix Admin - Virtual Domains' ; " ;
2009-12-05 01:08:47 +01:00
$sql [] = "
CREATE TABLE { IF_NOT_EXISTS } $domain_admins (
`username` varchar ( 255 ) NOT NULL default '' ,
`domain` varchar ( 255 ) NOT NULL default '' ,
2016-04-25 23:20:25 +02:00
`created` { DATETIME },
2009-12-05 01:08:47 +01:00
`active` tinyint ( 1 ) NOT NULL default '1' ,
KEY username ( `username` )
2020-02-11 21:01:59 +01:00
) { COLLATE } COMMENT = 'Postfix Admin - Domain Admins' ; " ;
2009-12-05 01:08:47 +01:00
$sql [] = "
CREATE TABLE { IF_NOT_EXISTS } $log (
2016-04-25 23:20:25 +02:00
`timestamp` { DATETIME },
2009-12-05 01:08:47 +01: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` )
2020-02-11 21:01:59 +01:00
) { COLLATE } COMMENT = 'Postfix Admin - Log' ; " ;
2009-12-05 01:08:47 +01:00
$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 '' ,
2016-04-25 23:20:25 +02:00
`created` { DATETIME },
`modified` { DATETIME },
2009-12-05 01:08:47 +01:00
`active` tinyint ( 1 ) NOT NULL default '1' ,
PRIMARY KEY ( `username` )
2020-02-11 21:01:59 +01:00
) { COLLATE } COMMENT = 'Postfix Admin - Virtual Mailboxes' ; " ;
2009-12-05 01:08:47 +01:00
$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 ,
2016-04-25 23:20:25 +02:00
created { DATETIME },
2009-12-05 01:08:47 +01:00
active tinyint ( 4 ) NOT NULL default '1' ,
PRIMARY KEY ( email ),
KEY email ( email )
2020-02-11 21:01:59 +01:00
) { INNODB } { COLLATE } COMMENT = 'Postfix Admin - Virtual Vacation' ; " ;
2009-12-05 01:08:47 +01:00
2018-01-26 15:45:57 +01:00
foreach ( $sql as $query ) {
2009-12-05 01:08:47 +01:00
db_query_parsed ( $query );
}
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2009-12-05 01:08:47 +01:00
function upgrade_2_mysql () {
2017-02-25 22:14:21 +01:00
#
# 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:
2018-01-26 15:45:57 +01:00
return ; # <-- skip running this function at all.
2017-02-25 22:14:21 +01:00
# (remove the above "return" if you really want to update a pre-2.1 database)
2009-12-05 01:08:47 +01:00
# upgrade pre-2.1 database
# from TABLE_BACKUP_MX.TXT
2018-01-26 15:45:57 +01:00
$table_domain = table_by_key ( 'domain' );
if ( ! _mysql_field_exists ( $table_domain , 'transport' )) {
2018-12-27 22:43:11 +01:00
db_query_parsed ( " ALTER TABLE $table_domain ADD COLUMN transport VARCHAR(255) AFTER maxquota; " , true );
2009-12-05 01:08:47 +01:00
}
2018-01-26 15:45:57 +01:00
if ( ! _mysql_field_exists ( $table_domain , 'backupmx' )) {
2018-12-27 22:43:11 +01:00
db_query_parsed ( " ALTER TABLE $table_domain ADD COLUMN backupmx { BOOLEAN} AFTER transport; " , true );
2009-12-05 01:08:47 +01:00
}
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2009-12-05 01:08:47 +01:00
function upgrade_2_pgsql () {
2018-01-26 15:45:57 +01:00
if ( ! _pgsql_object_exists ( table_by_key ( 'domain' ))) {
2009-12-05 01:08:47 +01:00
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 \" )
2019-02-15 22:59:11 +01:00
); " );
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' " );
2009-12-05 01:08:47 +01:00
}
2018-01-26 15:45:57 +01:00
if ( ! _pgsql_object_exists ( table_by_key ( 'admin' ))) {
2009-12-05 01:08:47 +01:00
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 " )
2019-02-15 22:59:11 +01:00
) ' );
db_query_parsed ( " COMMENT ON TABLE " . table_by_key ( 'admin' ) . " IS 'Postfix Admin - Virtual Admins' " );
2009-12-05 01:08:47 +01:00
}
2018-01-26 15:45:57 +01:00
if ( ! _pgsql_object_exists ( table_by_key ( 'alias' ))) {
2009-12-05 01:08:47 +01:00
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 " )
2019-02-15 22:59:11 +01:00
); ' );
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\'' );
2009-12-05 01:08:47 +01:00
}
2018-01-26 15:45:57 +01:00
if ( ! _pgsql_object_exists ( table_by_key ( 'domain_admins' ))) {
2009-12-05 01:08:47 +01:00
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
2019-02-15 22:59:11 +01:00
); ' );
db_query_parsed ( 'COMMENT ON TABLE ' . table_by_key ( 'domain_admins' ) . ' IS \'Postfix Admin - Domain Admins\'' );
2009-12-05 01:08:47 +01:00
}
2018-12-28 14:08:59 +01:00
2018-01-26 15:45:57 +01:00
if ( ! _pgsql_object_exists ( table_by_key ( 'log' ))) {
2009-12-05 01:08:47 +01:00
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 \ ' \ '
2019-02-15 22:59:11 +01:00
); ' );
db_query_parsed ( 'COMMENT ON TABLE ' . table_by_key ( 'log' ) . ' IS \'Postfix Admin - Log\'' );
2009-12-05 01:08:47 +01:00
}
2018-01-26 15:45:57 +01:00
if ( ! _pgsql_object_exists ( table_by_key ( 'mailbox' ))) {
2009-12-05 01:08:47 +01:00
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 " )
2019-02-15 22:59:11 +01:00
); ' );
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\'' );
2009-12-05 01:08:47 +01:00
}
2018-01-26 15:45:57 +01:00
if ( ! _pgsql_object_exists ( table_by_key ( 'vacation' ))) {
2009-12-05 01:08:47 +01:00
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
2019-02-15 22:59:11 +01:00
); ' );
db_query_parsed ( 'CREATE INDEX vacation_email_active ON ' . table_by_key ( 'vacation' ) . '(email,active);' );
2009-12-05 01:08:47 +01:00
}
2018-01-26 15:45:57 +01:00
if ( ! _pgsql_object_exists ( table_by_key ( 'vacation_notification' ))) {
2009-12-05 01:08:47 +01:00
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 )
);
' );
}
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2009-12-05 01:08:47 +01:00
function upgrade_3_mysql () {
2017-02-25 22:14:21 +01:00
#
# 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:
2018-01-26 15:45:57 +01:00
return ; # <-- skip running this function at all.
2017-02-25 22:14:21 +01:00
# (remove the above "return" if you really want to update a pre-2.1 database)
2009-12-05 01:08:47 +01:00
# upgrade pre-2.1 database
# from TABLE_CHANGES.TXT
2018-01-26 15:45:57 +01:00
$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' );
2009-12-05 01:08:47 +01:00
2018-01-26 15:45:57 +01:00
if ( ! _mysql_field_exists ( $table_admin , 'created' )) {
2016-04-25 23:20:25 +02:00
db_query_parsed ( " ALTER TABLE $table_admin { RENAME_COLUMN} create_date created { DATETIME}; " );
2009-12-05 01:08:47 +01:00
}
2018-01-26 15:45:57 +01:00
if ( ! _mysql_field_exists ( $table_admin , 'modified' )) {
2016-04-25 23:20:25 +02:00
db_query_parsed ( " ALTER TABLE $table_admin { RENAME_COLUMN} change_date modified { DATETIME}; " );
2009-12-05 01:08:47 +01:00
}
2018-01-26 15:45:57 +01:00
if ( ! _mysql_field_exists ( $table_alias , 'created' )) {
2016-04-25 23:20:25 +02:00
db_query_parsed ( " ALTER TABLE $table_alias { RENAME_COLUMN} create_date created { DATETIME}; " );
2009-12-05 01:08:47 +01:00
}
2018-01-26 15:45:57 +01:00
if ( ! _mysql_field_exists ( $table_alias , 'modified' )) {
2016-04-25 23:20:25 +02:00
db_query_parsed ( " ALTER TABLE $table_alias { RENAME_COLUMN} change_date modified { DATETIME}; " );
2009-12-05 01:08:47 +01:00
}
2018-01-26 15:45:57 +01:00
if ( ! _mysql_field_exists ( $table_domain , 'created' )) {
2016-04-25 23:20:25 +02:00
db_query_parsed ( " ALTER TABLE $table_domain { RENAME_COLUMN} create_date created { DATETIME}; " );
2009-12-05 01:08:47 +01:00
}
2018-01-26 15:45:57 +01:00
if ( ! _mysql_field_exists ( $table_domain , 'modified' )) {
2016-04-25 23:20:25 +02:00
db_query_parsed ( " ALTER TABLE $table_domain { RENAME_COLUMN} change_date modified { DATETIME}; " );
2009-12-05 01:08:47 +01:00
}
2018-01-26 15:45:57 +01:00
if ( ! _mysql_field_exists ( $table_domain , 'aliases' )) {
2009-12-05 01:08:47 +01:00
db_query_parsed ( " ALTER TABLE $table_domain ADD COLUMN aliases INT(10) DEFAULT '-1' NOT NULL AFTER description; " );
}
2018-01-26 15:45:57 +01:00
if ( ! _mysql_field_exists ( $table_domain , 'mailboxes' )) {
2009-12-05 01:08:47 +01:00
db_query_parsed ( " ALTER TABLE $table_domain ADD COLUMN mailboxes INT(10) DEFAULT '-1' NOT NULL AFTER aliases; " );
}
2018-01-26 15:45:57 +01:00
if ( ! _mysql_field_exists ( $table_domain , 'maxquota' )) {
2009-12-05 01:08:47 +01:00
db_query_parsed ( " ALTER TABLE $table_domain ADD COLUMN maxquota INT(10) DEFAULT '-1' NOT NULL AFTER mailboxes; " );
}
2018-01-26 15:45:57 +01:00
if ( ! _mysql_field_exists ( $table_domain , 'transport' )) {
2009-12-05 01:08:47 +01:00
db_query_parsed ( " ALTER TABLE $table_domain ADD COLUMN transport VARCHAR(255) AFTER maxquota; " );
}
2018-01-26 15:45:57 +01:00
if ( ! _mysql_field_exists ( $table_domain , 'backupmx' )) {
2009-12-05 01:08:47 +01:00
db_query_parsed ( " ALTER TABLE $table_domain ADD COLUMN backupmx TINYINT(1) DEFAULT '0' NOT NULL AFTER transport; " );
}
2018-01-26 15:45:57 +01:00
if ( ! _mysql_field_exists ( $table_mailbox , 'created' )) {
2016-04-25 23:20:25 +02:00
db_query_parsed ( " ALTER TABLE $table_mailbox { RENAME_COLUMN} create_date created { DATETIME}; " );
2009-12-05 01:08:47 +01:00
}
2018-01-26 15:45:57 +01:00
if ( ! _mysql_field_exists ( $table_mailbox , 'modified' )) {
2016-04-25 23:20:25 +02:00
db_query_parsed ( " ALTER TABLE $table_mailbox { RENAME_COLUMN} change_date modified { DATETIME}; " );
2009-12-05 01:08:47 +01:00
}
2018-01-26 15:45:57 +01:00
if ( ! _mysql_field_exists ( $table_mailbox , 'quota' )) {
2009-12-05 01:08:47 +01:00
db_query_parsed ( " ALTER TABLE $table_mailbox ADD COLUMN quota INT(10) DEFAULT '-1' NOT NULL AFTER maildir; " );
}
2018-01-26 15:45:57 +01:00
if ( ! _mysql_field_exists ( $table_vacation , 'domain' )) {
2009-12-05 01:08:47 +01:00
db_query_parsed ( " ALTER TABLE $table_vacation ADD COLUMN domain VARCHAR(255) DEFAULT '' NOT NULL AFTER cache; " );
}
2018-01-26 15:45:57 +01:00
if ( ! _mysql_field_exists ( $table_vacation , 'created' )) {
2016-04-25 23:20:25 +02:00
db_query_parsed ( " ALTER TABLE $table_vacation ADD COLUMN created { DATETIME} AFTER domain; " );
2009-12-05 01:08:47 +01:00
}
2018-01-26 15:45:57 +01:00
if ( ! _mysql_field_exists ( $table_vacation , 'active' )) {
2009-12-05 01:08:47 +01:00
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; " );
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2009-12-05 01:08:47 +01:00
function upgrade_4_mysql () { # MySQL only
# changes between 2.1 and moving to sourceforge
2020-02-12 22:19:49 +01:00
return ; // as the above _mysql functions are disabled; this one will just error for a new db.
2018-01-26 15:45:57 +01:00
$table_domain = table_by_key ( 'domain' );
2020-02-12 22:19:49 +01:00
2018-12-27 22:43:11 +01:00
db_query_parsed ( " ALTER TABLE $table_domain ADD COLUMN quota int(10) NOT NULL default '0' AFTER maxquota " , true );
2009-12-05 01:08:47 +01:00
# Possible errors that can be ignored:
# - Invalid query: Table 'postfix.domain' doesn't exist
}
/**
* Changes between 2.1 and moving to sf . net
2019-01-16 10:17:56 +01:00
* @ return void
2009-12-05 01:08:47 +01:00
*/
2018-01-26 15:45:57 +01:00
function upgrade_4_pgsql () {
2009-12-05 01:08:47 +01:00
$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' );
2018-01-26 15:45:57 +01:00
if ( ! _pgsql_field_exists ( $table_domain , 'quota' )) {
2018-12-27 22:43:11 +01:00
db_query_parsed ( " ALTER TABLE $table_domain ADD COLUMN quota int NOT NULL default '0' " );
2009-12-05 01:08:47 +01:00
}
2018-12-27 22:43:11 +01:00
db_query_parsed ( " ALTER TABLE $table_domain ALTER COLUMN domain DROP DEFAULT " );
2018-01-26 15:45:57 +01:00
if ( ! _pgsql_object_exists ( 'domain_domain_active' )) {
2018-12-27 22:43:11 +01:00
db_query_parsed ( " CREATE INDEX domain_domain_active ON $table_domain (domain,active) " );
2009-12-05 01:08:47 +01:00
}
2018-12-27 22:43:11 +01:00
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 " );
2018-01-26 15:45:57 +01:00
if ( ! _pgsql_object_exists ( 'alias_address_active' )) {
2018-12-27 22:43:11 +01:00
db_query_parsed ( " CREATE INDEX alias_address_active ON $table_alias (address,active) " );
2009-12-05 01:08:47 +01:00
}
2018-12-27 22:43:11 +01:00
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 " );
2009-12-05 01:08:47 +01:00
2019-02-15 22:59:11 +01:00
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 " );
2009-12-05 01:08:47 +01:00
2018-12-27 22:43:11 +01:00
db_query_parsed ( " ALTER TABLE $table_mailbox ALTER COLUMN username DROP DEFAULT " );
db_query_parsed ( " ALTER TABLE $table_mailbox ALTER COLUMN domain DROP DEFAULT " );
2009-12-05 01:08:47 +01:00
2019-02-15 22:59:11 +01:00
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; " );
2018-01-26 15:45:57 +01:00
if ( ! _pgsql_object_exists ( 'mailbox_username_active' )) {
2009-12-05 01:08:47 +01:00
db_query_parsed ( " CREATE INDEX mailbox_username_active ON $table_mailbox (username,active) " );
}
2018-12-27 22:43:11 +01:00
db_query_parsed ( " ALTER TABLE $table_vacation ALTER COLUMN body SET DEFAULT '' " );
2018-01-26 15:45:57 +01:00
if ( _pgsql_field_exists ( $table_vacation , 'cache' )) {
2018-12-27 22:43:11 +01:00
db_query_parsed ( " ALTER TABLE $table_vacation DROP COLUMN cache " );
2009-12-05 01:08:47 +01:00
}
2019-02-15 22:59:11 +01:00
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; " );
2009-12-05 01:08:47 +01:00
2018-01-26 15:45:57 +01:00
if ( ! _pgsql_object_exists ( 'vacation_email_active' )) {
2018-12-27 22:43:11 +01:00
db_query_parsed ( " CREATE INDEX vacation_email_active ON $table_vacation (email,active) " );
2009-12-05 01:08:47 +01:00
}
2018-01-26 15:45:57 +01:00
if ( ! _pgsql_object_exists ( $table_vacation_notification )) {
2018-12-27 22:43:11 +01:00
db_query_parsed ( "
2009-12-05 01:08:47 +01:00
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:
2018-01-26 15:45:57 +01:00
#
2009-12-05 01:08:47 +01:00
# 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
2019-01-16 10:17:56 +01:00
* @ return void
2009-12-05 01:08:47 +01:00
*/
function upgrade_5_mysql () {
2018-12-27 22:43:11 +01:00
db_query_parsed ( "
2017-12-24 16:27:24 +01:00
CREATE TABLE { IF_NOT_EXISTS } " . table_by_key('admin') . " (
2009-12-05 01:08:47 +01:00
`username` varchar ( 255 ) NOT NULL default '' ,
`password` varchar ( 255 ) NOT NULL default '' ,
2016-04-25 23:20:25 +02:00
`created` { DATETIME },
`modified` { DATETIME },
2009-12-05 01:08:47 +01:00
`active` tinyint ( 1 ) NOT NULL default '1' ,
PRIMARY KEY ( `username` ),
KEY username ( `username` )
2020-02-11 21:01:59 +01:00
) { COLLATE } COMMENT = 'Postfix Admin - Virtual Admins' ; " );
2009-12-05 01:08:47 +01:00
2018-12-27 22:43:11 +01:00
db_query_parsed ( "
2017-12-24 16:27:24 +01:00
CREATE TABLE { IF_NOT_EXISTS } " . table_by_key('alias') . " (
2009-12-05 01:08:47 +01:00
`address` varchar ( 255 ) NOT NULL default '' ,
`goto` text NOT NULL ,
`domain` varchar ( 255 ) NOT NULL default '' ,
2016-04-25 23:20:25 +02:00
`created` { DATETIME },
`modified` { DATETIME },
2009-12-05 01:08:47 +01:00
`active` tinyint ( 1 ) NOT NULL default '1' ,
PRIMARY KEY ( `address` ),
KEY address ( `address` )
2020-02-11 21:01:59 +01:00
) { COLLATE } COMMENT = 'Postfix Admin - Virtual Aliases' ;
2009-12-05 01:08:47 +01:00
" );
2018-12-27 22:43:11 +01:00
db_query_parsed ( "
2017-12-24 16:27:24 +01:00
CREATE TABLE { IF_NOT_EXISTS } " . table_by_key('domain') . " (
2009-12-05 01:08:47 +01:00
`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' ,
2016-04-25 23:20:25 +02:00
`created` { DATETIME },
`modified` { DATETIME },
2009-12-05 01:08:47 +01:00
`active` tinyint ( 1 ) NOT NULL default '1' ,
PRIMARY KEY ( `domain` ),
KEY domain ( `domain` )
2020-02-11 21:01:59 +01:00
) { COLLATE } COMMENT = 'Postfix Admin - Virtual Domains' ;
2009-12-05 01:08:47 +01:00
" );
2018-12-27 22:43:11 +01:00
db_query_parsed ( "
2017-12-24 16:27:24 +01:00
CREATE TABLE { IF_NOT_EXISTS } " . table_by_key('domain_admins') . " (
2009-12-05 01:08:47 +01:00
`username` varchar ( 255 ) NOT NULL default '' ,
`domain` varchar ( 255 ) NOT NULL default '' ,
2016-04-25 23:20:25 +02:00
`created` { DATETIME },
2009-12-05 01:08:47 +01:00
`active` tinyint ( 1 ) NOT NULL default '1' ,
KEY username ( `username` )
2020-02-11 21:01:59 +01:00
) { COLLATE } COMMENT = 'Postfix Admin - Domain Admins' ;
2009-12-05 01:08:47 +01:00
" );
2018-12-27 22:43:11 +01:00
db_query_parsed ( "
2017-12-24 16:27:24 +01:00
CREATE TABLE { IF_NOT_EXISTS } " . table_by_key('log') . " (
2016-04-25 23:20:25 +02:00
`timestamp` { DATETIME },
2009-12-05 01:08:47 +01: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` )
2020-02-11 21:01:59 +01:00
) { COLLATE } COMMENT = 'Postfix Admin - Log' ;
2009-12-05 01:08:47 +01:00
" );
2018-12-27 22:43:11 +01:00
db_query_parsed ( "
2017-12-24 16:27:24 +01:00
CREATE TABLE { IF_NOT_EXISTS } " . table_by_key('mailbox') . " (
2009-12-05 01:08:47 +01:00
`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 '' ,
2016-04-25 23:20:25 +02:00
`created` { DATETIME },
`modified` { DATETIME },
2009-12-05 01:08:47 +01:00
`active` tinyint ( 1 ) NOT NULL default '1' ,
PRIMARY KEY ( `username` ),
2020-02-11 21:01:59 +01:00
KEY username ( `username` )
) { COLLATE } COMMENT = 'Postfix Admin - Virtual Mailboxes' ;
2009-12-05 01:08:47 +01:00
" );
2018-12-27 22:43:11 +01:00
db_query_parsed ( "
2017-12-24 16:27:24 +01:00
CREATE TABLE { IF_NOT_EXISTS } " . table_by_key('vacation') . " (
2009-12-05 01:08:47 +01:00
`email` varchar ( 255 ) NOT NULL ,
`subject` varchar ( 255 ) NOT NULL ,
`body` text NOT NULL ,
`cache` text NOT NULL ,
`domain` varchar ( 255 ) NOT NULL ,
2016-04-25 23:20:25 +02:00
`created` { DATETIME },
2009-12-05 01:08:47 +01:00
`active` tinyint ( 1 ) NOT NULL default '1' ,
PRIMARY KEY ( `email` ),
KEY email ( `email` )
2020-02-11 21:01:59 +01:00
) { COLLATE } COMMENT = 'Postfix Admin - Virtual Vacation' ;
2009-12-05 01:08:47 +01:00
" );
}
/**
* drop useless indicies ( already available as primary key )
2019-01-16 10:17:56 +01:00
* @ return void
2009-12-05 01:08:47 +01:00
*/
function upgrade_79_mysql () { # MySQL only
2018-12-27 22:43:11 +01:00
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 );
2009-12-05 01:08:47 +01:00
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2009-12-05 01:08:47 +01:00
function upgrade_81_mysql () { # MySQL only
2018-01-26 15:45:57 +01:00
$table_vacation = table_by_key ( 'vacation' );
2009-12-05 01:08:47 +01:00
$table_vacation_notification = table_by_key ( 'vacation_notification' );
2010-12-16 01:30:21 +01:00
$all_sql = explode ( " \n " , trim ( "
2019-02-16 20:46:31 +01:00
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'
2020-02-12 22:15:04 +01:00
ALTER TABLE $table_vacation DEFAULT { COLLATE }
2019-02-16 20:46:31 +01:00
ALTER TABLE $table_vacation { INNODB }
2009-12-05 01:08:47 +01:00
" ));
foreach ( $all_sql as $sql ) {
2018-12-27 22:43:11 +01:00
db_query_parsed ( $sql , true );
2009-12-05 01:08:47 +01:00
}
}
/**
* Make logging translatable - i . e . create alias => create_alias
2019-01-16 10:17:56 +01:00
* @ return void
2009-12-05 01:08:47 +01:00
*/
2016-02-04 23:30:06 +01:00
function upgrade_90_mysql_pgsql () {
2018-12-27 22:43:11 +01:00
db_query_parsed ( " UPDATE " . table_by_key ( 'log' ) . " SET action = REPLACE(action,' ','_') " , true );
2009-12-05 01:08:47 +01:00
# change edit_alias_state to edit_alias_active
2018-12-27 22:43:11 +01:00
db_query_parsed ( " UPDATE " . table_by_key ( 'log' ) . " SET action = 'edit_alias_state' WHERE action = 'edit_alias_active' " , true );
2009-12-05 01:08:47 +01:00
}
/**
* MySQL only allow quota > 2 GB
2019-01-16 10:17:56 +01:00
* @ return void
2009-12-05 01:08:47 +01:00
*/
2018-01-26 15:45:57 +01:00
function upgrade_169_mysql () {
$table_domain = table_by_key ( 'domain' );
$table_mailbox = table_by_key ( 'mailbox' );
2018-12-27 22:43:11 +01:00
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 );
2009-12-05 01:08:47 +01:00
}
/**
* 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 .
2019-01-16 10:17:56 +01:00
* @ return void
2009-12-05 01:08:47 +01:00
*/
function upgrade_318_mysql () {
$table_vacation_notification = table_by_key ( 'vacation_notification' );
$table_vacation = table_by_key ( 'vacation' );
2018-01-26 15:45:57 +01:00
db_query_parsed ( "
2009-12-05 01:08:47 +01:00
CREATE TABLE { IF_NOT_EXISTS } $table_vacation_notification (
2017-12-24 16:27:24 +01:00
on_vacation varchar ( 255 ) NOT NULL ,
notified varchar ( 255 ) NOT NULL ,
2009-12-05 01:08:47 +01:00
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
2020-02-12 22:15:04 +01:00
) { INNODB } { COLLATE } COMMENT = 'Postfix Admin - Virtual Vacation Notifications'
2009-12-05 01:08:47 +01:00
" );
# in case someone has manually created the table with utf8 fields before:
2010-12-16 01:30:21 +01:00
$all_sql = explode ( " \n " , trim ( "
2017-12-24 16:27:24 +01:00
ALTER TABLE $table_vacation_notification CHANGE `notified` `notified` VARCHAR ( 255 ) { LATIN1 } NOT NULL
ALTER TABLE $table_vacation_notification DEFAULT CHARACTER SET utf8
2009-12-05 01:08:47 +01:00
" ));
# Possible errors that can be ignored:
# None.
2018-01-26 15:45:57 +01:00
# If something goes wrong, the user should drop the vacation_notification table
2009-12-05 01:08:47 +01:00
# (not a great loss) and re-create it using this function.
foreach ( $all_sql as $sql ) {
2018-12-27 22:43:11 +01:00
db_query_parsed ( $sql );
2009-12-05 01:08:47 +01:00
}
}
/**
* Create fetchmail table
2019-01-16 10:17:56 +01:00
* @ return void
2009-12-05 01:08:47 +01:00
*/
function upgrade_344_mysql () {
$table_fetchmail = table_by_key ( 'fetchmail' );
2018-01-26 15:45:57 +01:00
db_query_parsed ( "
2009-12-05 01:08:47 +01:00
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 )
);
" );
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2009-12-05 01:08:47 +01:00
function upgrade_344_pgsql () {
$fetchmail = table_by_key ( 'fetchmail' );
2018-01-26 15:45:57 +01:00
// a field name called 'date' is probably a bad idea.
if ( ! _pgsql_object_exists ( 'fetchmail' )) {
db_query_parsed ( "
2009-12-05 01:08:47 +01:00
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.
2018-01-26 15:45:57 +01:00
if ( _pgsql_object_exists ( 'fetchmail_id_seq' )) {
db_query_parsed ( " SELECT nextval(' { $fetchmail } _id_seq') " ); // I don't care about number waste.
2009-12-05 01:08:47 +01:00
}
}
2018-01-26 15:45:57 +01:00
/**
2009-12-05 01:08:47 +01:00
* Create alias_domain table - MySQL
2019-01-16 10:17:56 +01:00
* function upgrade_362_mysql () # renamed to _438 to make sure it runs after an upgrade from 2.2.x
*
* @ return void
2009-12-05 01:08:47 +01:00
*/
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 (
2017-02-25 22:14:21 +01:00
`alias_domain` varchar ( 255 ) { LATIN1 } NOT NULL default '' ,
`target_domain` varchar ( 255 ) { LATIN1 } NOT NULL default '' ,
2016-04-25 23:20:25 +02:00
`created` { DATETIME },
`modified` { DATETIME },
2009-12-05 01:08:47 +01:00
`active` tinyint ( 1 ) NOT NULL default '1' ,
PRIMARY KEY ( `alias_domain` ),
KEY `active` ( `active` ),
KEY `target_domain` ( `target_domain` )
2020-02-11 21:01:59 +01:00
) { COLLATE } COMMENT = 'Postfix Admin - Domain Aliases'
2009-12-05 01:08:47 +01:00
" );
}
2018-01-26 15:45:57 +01:00
/**
2009-12-05 01:08:47 +01:00
* Create alias_domain table - PgSQL
2019-01-16 10:17:56 +01:00
* function upgrade_362_pgsql () # renamed to _438 to make sure it runs after an upgrade from 2.2.x
* @ return void
2009-12-05 01:08:47 +01:00
*/
function upgrade_438_pgsql () {
# Table structure for table alias_domain
$table_alias_domain = table_by_key ( 'alias_domain' );
$table_domain = table_by_key ( 'domain' );
2018-01-26 15:45:57 +01:00
if ( _pgsql_object_exists ( $table_alias_domain )) {
2009-12-05 01:08:47 +01:00
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
2019-01-16 10:17:56 +01:00
* @ return void
2009-12-05 01:08:47 +01:00
*/
function upgrade_373_mysql () { # MySQL only
2018-01-26 15:45:57 +01:00
$table_domain = table_by_key ( 'domain' );
2009-12-05 01:08:47 +01:00
$table_mailbox = table_by_key ( 'mailbox' );
2010-12-16 01:30:21 +01:00
$all_sql = explode ( " \n " , trim ( "
2017-12-24 16:27:24 +01:00
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
2009-12-05 01:08:47 +01:00
" ));
foreach ( $all_sql as $sql ) {
2018-12-27 22:43:11 +01:00
db_query_parsed ( $sql );
2009-12-05 01:08:47 +01:00
}
}
/**
* add ssl option for fetchmail
2019-01-16 10:17:56 +01:00
* @ return void
2009-12-05 01:08:47 +01:00
*/
function upgrade_439_mysql () {
$table_fetchmail = table_by_key ( 'fetchmail' );
2018-01-26 15:45:57 +01:00
if ( ! _mysql_field_exists ( $table_fetchmail , 'ssl' )) {
2017-12-24 16:27:24 +01:00
db_query_parsed ( " ALTER TABLE $table_fetchmail ADD `ssl` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0' AFTER `protocol` ; " );
2009-12-05 01:08:47 +01:00
}
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2009-12-05 01:08:47 +01:00
function upgrade_439_pgsql () {
$table_fetchmail = table_by_key ( 'fetchmail' );
2018-01-26 15:45:57 +01:00
if ( ! _pgsql_field_exists ( $table_fetchmail , 'ssl' )) {
2009-12-05 01:08:47 +01:00
db_query_parsed ( " ALTER TABLE $table_fetchmail ADD COLUMN ssl BOOLEAN NOT NULL DEFAULT false " );
}
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2009-12-05 01:08:47 +01:00
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 :-(
2010-12-16 01:30:21 +01:00
$all_sql = explode ( " \n " , trim ( "
2017-12-24 16:27:24 +01:00
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 }
2009-12-05 01:08:47 +01:00
" ));
foreach ( $all_sql as $sql ) {
2018-12-27 22:43:11 +01:00
db_query_parsed ( $sql );
2009-12-05 01:08:47 +01:00
}
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2018-01-26 15:45:57 +01:00
function upgrade_479_mysql () {
2009-12-05 01:08:47 +01:00
# ssl is a reserved word in MySQL and causes several problems. Renaming the field...
$table_fmail = table_by_key ( 'fetchmail' );
2018-01-26 15:45:57 +01:00
if ( ! _mysql_field_exists ( $table_fmail , 'usessl' )) {
2017-12-24 16:27:24 +01:00
db_query_parsed ( " ALTER TABLE $table_fmail CHANGE `ssl` `usessl` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0' " );
2009-12-05 01:08:47 +01:00
}
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2018-01-26 15:45:57 +01:00
function upgrade_479_pgsql () {
2009-12-05 01:08:47 +01:00
$table_fmail = table_by_key ( 'fetchmail' );
2018-01-26 15:45:57 +01:00
if ( ! _pgsql_field_exists ( $table_fmail , 'usessl' )) {
2009-12-05 01:08:47 +01:00
db_query_parsed ( " alter table $table_fmail rename column ssl to usessl " );
}
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2018-01-26 15:45:57 +01:00
function upgrade_483_mysql () {
2009-12-05 01:08:47 +01:00
$table_log = table_by_key ( 'log' );
db_query_parsed ( " ALTER TABLE $table_log CHANGE `data` `data` TEXT { LATIN1} NOT NULL " );
}
2019-01-16 11:31:16 +01:00
/**
2019-01-16 10:17:56 +01:00
* 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
*/
2009-12-05 01:08:47 +01:00
function upgrade_495_pgsql () {
$table_mailbox = table_by_key ( 'mailbox' );
2018-01-26 15:45:57 +01:00
if ( ! _pgsql_field_exists ( $table_mailbox , 'local_part' )) {
2009-12-05 01:08:47 +01:00
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 " );
}
}
2019-01-16 10:17:56 +01:00
/**
* See https :// sourceforge . net / forum / message . php ? msg_id = 5394663
* @ return void
*/
2009-12-05 01:08:47 +01:00
function upgrade_495_mysql () {
$table_mailbox = table_by_key ( 'mailbox' );
2018-01-26 15:45:57 +01:00
if ( ! _mysql_field_exists ( $table_mailbox , 'local_part' )) {
2009-12-05 01:08:47 +01:00
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...
}
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2009-12-05 01:08:47 +01:00
function upgrade_504_mysql () {
$table_mailbox = table_by_key ( 'mailbox' );
2017-12-24 16:27:24 +01:00
db_query_parsed ( " ALTER TABLE $table_mailbox CHANGE `local_part` `local_part` VARCHAR( 255 ) { LATIN1} NOT NULL " );
2009-12-05 01:08:47 +01:00
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2016-02-04 23:30:06 +01:00
function upgrade_655_mysql_pgsql () {
2009-12-05 01:08:47 +01:00
db_query_parsed ( _add_index ( 'mailbox' , 'domain' , 'domain' ));
2018-01-26 15:45:57 +01:00
db_query_parsed ( _add_index ( 'alias' , 'domain' , 'domain' ));
2009-12-05 01:08:47 +01:00
}
2018-01-26 15:45:57 +01:00
/*
2011-02-06 20:07:44 +01:00
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 .
2013-11-16 21:52:01 +01:00
Note : there never was a function upgrade_727_pgsql ()
2009-12-05 01:08:47 +01:00
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 " );
}
2011-02-06 20:07:44 +01:00
# the following tables are not used by postfixadmin
2009-12-05 01:08:47 +01:00
$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` )
2018-01-25 22:08:26 +01:00
) COMMENT = 'Postfix Admin - Client Access'
2009-12-05 01:08:47 +01:00
" );
$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` )
2018-01-25 22:08:26 +01:00
) COMMENT = 'Postfix Admin - From Access'
2009-12-05 01:08:47 +01:00
" );
$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` )
2018-01-25 22:08:26 +01:00
) COMMENT = 'Postfix Admin - Helo Access'
2009-12-05 01:08:47 +01:00
" );
$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` )
2018-01-25 22:08:26 +01:00
) COMMENT = 'Postfix Admin - Recipient Access'
2009-12-05 01:08:47 +01:00
" );
$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` )
2018-01-25 22:08:26 +01:00
) COMMENT = 'Postfix Admin - User whitelist'
2009-12-05 01:08:47 +01:00
" );
}
2011-02-06 20:07:44 +01:00
*/
2009-12-05 01:08:47 +01:00
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2016-02-04 23:30:06 +01:00
function upgrade_729_mysql_pgsql () {
2009-12-05 01:08:47 +01:00
$table_quota = table_by_key ( 'quota' );
$table_quota2 = table_by_key ( 'quota2' );
# table for dovecot v1.0 & 1.1
2013-12-09 23:10:55 +01:00
# note: quota table created with old versions of upgrade.php (before r1605)
# will not have explicit "NOT NULL DEFAULT 0" for the "current" field
2018-01-26 15:45:57 +01:00
# (shouldn't hurt)
2009-12-05 01:08:47 +01:00
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 )
2020-02-11 21:01:59 +01:00
) { COLLATE } ;
2009-12-05 01:08:47 +01:00
" );
# table for dovecot >= 1.2
db_query_parsed ( "
CREATE TABLE { IF_NOT_EXISTS } $table_quota2 (
username VARCHAR ( 100 ) { LATIN1 } NOT NULL ,
2013-12-08 23:12:24 +01:00
bytes { BIGINT },
2009-12-05 01:08:47 +01:00
messages integer NOT NULL DEFAULT 0 ,
PRIMARY KEY ( username )
2018-01-25 22:08:26 +01:00
) ;
2009-12-05 01:08:47 +01:00
" );
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2009-12-05 01:08:47 +01:00
function upgrade_730_pgsql () {
$table_quota = table_by_key ( 'quota' );
$table_quota2 = table_by_key ( 'quota2' );
2021-01-19 17:50:56 +01:00
try {
db_query_parsed ( 'CREATE LANGUAGE plpgsql' , 1 ); /* will error if plpgsql is already installed */
} catch ( \Exception $e ) {
error_log ( " ignoring exception that's probably : plpgsql is probably already installed; " . $e );
}
2009-12-05 01:08:47 +01:00
# 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 ();
" );
}
2011-02-06 18:42:47 +01:00
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2016-02-04 23:30:06 +01:00
function upgrade_945_mysql_pgsql () {
2011-02-06 18:42:47 +01:00
_db_add_field ( 'vacation' , 'modified' , '{DATECURRENT}' , 'created' );
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2016-02-04 23:30:06 +01:00
function upgrade_946_mysql_pgsql () {
2011-02-06 20:07:44 +01:00
# taken from upgrade_727_mysql, needs to be done for all databases
2018-01-26 15:45:57 +01:00
_db_add_field ( 'vacation' , 'activefrom' , '{DATE}' , 'body' );
2017-12-30 12:39:09 +01:00
_db_add_field ( 'vacation' , 'activeuntil' , '{DATEFUTURE}' , 'activefrom' );
2011-02-06 20:07:44 +01:00
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2011-02-18 22:59:42 +01:00
function upgrade_968_pgsql () {
# pgsql counterpart for upgrade_169_mysql() - allow really big quota
2018-01-26 15:45:57 +01:00
$table_domain = table_by_key ( 'domain' );
2011-02-18 22:59:42 +01:00
$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 " );
}
2011-02-06 18:42:47 +01:00
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2016-02-04 23:30:06 +01:00
function upgrade_1050_mysql_pgsql () {
2011-04-23 20:38:31 +02:00
db_query_parsed ( _add_index ( 'log' , 'domain_timestamp' , 'domain,timestamp' ));
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2016-02-04 23:30:06 +01:00
function upgrade_1283_mysql_pgsql () {
2011-11-27 21:22:31 +01:00
_db_add_field ( 'admin' , 'superadmin' , '{BOOLEAN}' , 'password' );
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2016-02-04 23:30:06 +01:00
function upgrade_1284_mysql_pgsql () {
2011-11-27 22:23:36 +01:00
# 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)
2019-01-06 22:27:08 +01:00
$result = db_query_all ( " SELECT username FROM " . table_by_key ( 'domain_admins' ) . " where domain='ALL' " );
2011-11-27 22:23:36 +01:00
2019-01-06 22:03:43 +01:00
foreach ( $result as $row ) {
2019-01-06 21:25:24 +01:00
printdebug ( " Setting superadmin flag for " . $row [ 'username' ]);
db_update ( 'admin' , 'username' , $row [ 'username' ], array ( 'superadmin' => db_get_boolean ( true )));
2011-11-27 22:23:36 +01:00
}
}
2011-11-27 21:22:31 +01:00
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2012-04-19 23:55:36 +02:00
function upgrade_1345_mysql () {
2013-12-22 17:50:55 +01:00
# $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` ");
2012-04-19 23:55:36 +02:00
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2016-02-04 23:30:06 +01:00
function upgrade_1519_mysql_pgsql () {
2018-01-26 15:45:57 +01:00
_db_add_field ( 'fetchmail' , 'sslcertck' , '{BOOLEAN}' , 'usessl' );
_db_add_field ( 'fetchmail' , 'sslcertpath' , " VARCHAR(255) { UTF-8} DEFAULT '' " , 'sslcertck' );
2013-08-18 20:40:57 +02:00
_db_add_field ( 'fetchmail' , 'sslfingerprint' , " VARCHAR(255) { LATIN1} DEFAULT '' " , 'sslcertpath' );
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2016-02-04 23:30:06 +01:00
function upgrade_1610_mysql_pgsql () {
2013-12-22 17:50:55 +01:00
# obsoletes upgrade_1345_mysql() - which means debug mode could print "field already exists"
_db_add_field ( 'vacation' , 'interval_time' , '{INT}' , 'domain' );
}
2011-11-27 21:22:31 +01:00
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2014-09-12 11:47:22 +02:00
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; " );
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2014-09-12 11:47:22 +02:00
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; " );
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2015-09-07 22:46:31 +02:00
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' );
2017-12-24 16:27:24 +01:00
db_query_parsed ( " ALTER TABLE $table_fetchmail CHANGE `date` `date` { DATE} " );
2015-09-07 22:46:31 +02:00
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2016-02-04 23:30:06 +01:00
function upgrade_1762_mysql_pgsql () {
2018-01-26 15:45:57 +01:00
_db_add_field ( 'fetchmail' , 'domain' , " VARCHAR(255) { LATIN1} DEFAULT '' " , 'id' );
_db_add_field ( 'fetchmail' , 'active' , '{BOOLEAN}' , 'date' );
_db_add_field ( 'fetchmail' , 'created' , '{DATE}' , 'date' );
2015-09-07 22:46:31 +02:00
# 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).
2018-01-26 15:45:57 +01:00
_db_add_field ( 'fetchmail' , 'modified' , '{DATECURRENT}' , 'created' );
2015-04-04 16:20:57 +02:00
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2015-05-06 23:34:14 +02:00
function upgrade_1763_mysql () {
2015-04-04 16:20:57 +02:00
$table = table_by_key ( 'fetchmail' );
db_query_parsed ( " UPDATE $table SET domain=SUBSTRING_INDEX(mailbox, '@', -1) WHERE domain=''; " );
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2015-05-06 23:34:14 +02:00
function upgrade_1763_pgsql () {
$table = table_by_key ( 'fetchmail' );
db_query_parsed ( " UPDATE $table SET domain=SPLIT_PART(mailbox, '@', 2) WHERE domain=''; " );
}
2015-04-04 16:20:57 +02:00
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2016-02-04 23:30:06 +01:00
function upgrade_1767_mysql_pgsql () {
2015-04-04 16:32:58 +02:00
# '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}' " );
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2015-09-07 22:46:31 +02:00
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 ();
}
2014-09-12 11:47:22 +02:00
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2016-02-04 23:30:06 +01:00
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 ( "
2018-12-28 14:08:59 +01:00
CREATE TABLE { IF_NOT_EXISTS } $admin_table (
2016-02-04 23:30:06 +01:00
`username` varchar ( 255 ) NOT NULL ,
`password` varchar ( 255 ) NOT NULL ,
`superadmin` { BOOLEAN },
`created` { DATE },
`modified` { DATE },
`active` { BOOLEAN_TRUE },
{ PRIMARY } ( `username` ));
" );
db_query_parsed ( "
2018-12-28 14:08:59 +01:00
CREATE TABLE { IF_NOT_EXISTS } $alias_table (
2016-02-04 23:30:06 +01:00
`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 ( "
2018-12-28 14:08:59 +01:00
CREATE TABLE { IF_NOT_EXISTS } $alias_domain_table (
2016-02-04 23:30:06 +01:00
`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 ( "
2018-12-28 14:08:59 +01:00
CREATE TABLE { IF_NOT_EXISTS } $domain_table (
2016-02-04 23:30:06 +01:00
`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 ( "
2018-12-28 14:08:59 +01:00
CREATE TABLE { IF_NOT_EXISTS } $domain_admins_table (
2016-02-04 23:30:06 +01:00
`username` varchar ( 255 ) NOT NULL ,
`domain` varchar ( 255 ) NOT NULL ,
`created` { DATE },
`active` { BOOLEAN_TRUE });
" );
db_query_parsed ( "
2018-12-28 14:08:59 +01:00
CREATE TABLE { IF_NOT_EXISTS } $fetchmail_table (
2016-02-04 23:30:06 +01:00
`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 ( "
2018-12-28 14:08:59 +01:00
CREATE TABLE { IF_NOT_EXISTS } $log_table (
2016-02-04 23:30:06 +01:00
`timestamp` { DATE },
`username` varchar ( 255 ) NOT NULL ,
`domain` varchar ( 255 ) NOT NULL ,
`action` varchar ( 255 ) NOT NULL ,
`data` { FULLTEXT } NOT NULL );
" );
db_query_parsed ( "
2018-12-28 14:08:59 +01:00
CREATE TABLE { IF_NOT_EXISTS } $mailbox_table (
2016-02-04 23:30:06 +01:00
`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 ( "
2018-12-28 14:08:59 +01:00
CREATE TABLE { IF_NOT_EXISTS } $quota_table (
2016-02-04 23:30:06 +01:00
`username` varchar ( 255 ) NOT NULL ,
`path` varchar ( 100 ) NOT NULL ,
`current` { BIGINT },
{ PRIMARY } ( `username` , `path` ));
" );
db_query_parsed ( "
2018-12-28 14:08:59 +01:00
CREATE TABLE { IF_NOT_EXISTS } $quota2_table (
2016-02-04 23:30:06 +01:00
`username` varchar ( 255 ) NOT NULL ,
`bytes` { BIGINT },
`messages` { INT },
{ PRIMARY } ( `username` ));
" );
db_query_parsed ( "
2018-12-28 14:08:59 +01:00
CREATE TABLE { IF_NOT_EXISTS } $vacation_table (
2016-02-04 23:30:06 +01:00
`email` varchar ( 255 ) NOT NULL ,
`subject` varchar ( 255 ) NOT NULL ,
`body` { FULLTEXT } NOT NULL ,
`activefrom` { DATE },
2017-12-30 12:39:09 +01:00
`activeuntil` { DATEFUTURE },
2016-02-04 23:30:06 +01:00
`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 ( "
2018-12-28 14:08:59 +01:00
CREATE TABLE { IF_NOT_EXISTS } $vacation_notification_table (
2016-02-04 23:30:06 +01:00
`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 );
" );
}
2016-04-25 23:20:25 +02:00
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2016-04-25 23:20:25 +02:00
function upgrade_1835_mysql () {
# change default values for existing datetime fields with a 0000-00-00 default to {DATETIME}
2016-11-01 21:31:52 +01:00
foreach ( array ( 'domain_admins' , 'vacation' ) as $table_to_change ) {
2016-04-25 23:20:25 +02:00
$table = table_by_key ( $table_to_change );
2017-12-24 16:27:24 +01:00
db_query_parsed ( " ALTER TABLE $table CHANGE `created` `created` { DATETIME} " );
2016-04-25 23:20:25 +02:00
}
foreach ( array ( 'admin' , 'alias' , 'alias_domain' , 'domain' , 'mailbox' ) as $table_to_change ) {
$table = table_by_key ( $table_to_change );
2017-12-24 16:27:24 +01:00
db_query_parsed ( " ALTER TABLE $table CHANGE `created` `created` { DATETIME}, CHANGE `modified` `modified` { DATETIME} " );
2016-04-25 23:20:25 +02:00
}
$table = table_by_key ( 'log' );
2017-12-24 16:27:24 +01:00
db_query_parsed ( " ALTER TABLE $table CHANGE `timestamp` `timestamp` { DATETIME} " );
2016-04-25 23:20:25 +02:00
}
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2017-02-25 23:22:47 +01:00
function upgrade_1836_mysql () {
2018-01-26 15:45:57 +01:00
$table_alias_domain = table_by_key ( 'alias_domain' );
2017-02-25 23:22:47 +01:00
$table_vacation_notification = table_by_key ( 'vacation_notification' );
$all_sql = explode ( " \n " , trim ( "
2017-12-24 16:27:24 +01:00
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 ''
2017-02-25 23:22:47 +01:00
" ));
foreach ( $all_sql as $sql ) {
2018-12-27 22:43:11 +01:00
db_query_parsed ( $sql , true );
2017-02-25 23:22:47 +01:00
}
}
2016-10-17 18:12:14 +02:00
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2016-10-19 13:06:10 +02:00
function upgrade_1837 () {
2021-04-13 22:26:11 +02:00
if ( db_sqlite ()) {
2018-05-23 23:10:12 +02:00
return ;
}
2016-10-17 18:12:14 +02:00
# alternative contact means to reset a forgotten password
2018-01-26 15:45:57 +01:00
foreach ( array ( 'admin' , 'mailbox' ) as $table ) {
2016-10-19 13:06:10 +02:00
_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' );
2016-10-17 18:12:14 +02:00
}
}
2011-02-06 18:42:47 +01:00
# TODO MySQL:
# - various varchar fields do not have a default value
# https://sourceforge.net/projects/postfixadmin/forums/forum/676076/topic/3419725
2011-02-06 20:07:44 +01:00
# - 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)
2011-02-18 22:59:42 +01:00
# including vacation.modified - should be {DATE}, not {DATECURRENT}
2011-02-06 18:42:47 +01:00
# https://sourceforge.net/tracker/?func=detail&aid=1699218&group_id=191583&atid=937964
2011-03-14 19:38:07 +01:00
# @todo vacation.email has 2 indizes
2017-10-06 03:46:34 +02:00
# 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?
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2017-10-06 03:46:34 +02:00
function upgrade_1837_sqlite () {
# Add columns for the alternative contact to reset a forgotten password.
2018-01-26 15:45:57 +01:00
foreach ( array ( 'admin' , 'mailbox' ) as $table_to_change ) {
2017-10-06 03:46:34 +02:00
$table = table_by_key ( $table_to_change );
2018-01-26 15:45:57 +01:00
if ( ! _sqlite_field_exists ( $table , 'phone' )) {
2017-10-06 03:46:34 +02:00
db_query_parsed ( " ALTER TABLE ` $table ` ADD COLUMN `phone` varchar(30) NOT NULL DEFAULT '' " );
}
2018-01-26 15:45:57 +01:00
if ( ! _sqlite_field_exists ( $table , 'email_other' )) {
2017-10-06 03:46:34 +02:00
db_query_parsed ( " ALTER TABLE ` $table ` ADD COLUMN `email_other` varchar(255) NOT NULL DEFAULT '' " );
}
}
}
2017-11-11 13:52:15 +01:00
2019-01-16 10:17:56 +01:00
/**
2019-01-16 11:31:16 +01:00
* https :// github . com / postfixadmin / postfixadmin / issues / 89
2019-01-16 10:17:56 +01:00
* upgrade_1838_mysql () renamed to upgrade_1839 () to keep all databases in sync
* @ return void
*/
2017-12-30 12:37:25 +01:00
function upgrade_1839 () {
2018-12-28 20:31:43 +01:00
if ( ! db_sqlite ()) {
2019-01-16 10:19:04 +01:00
_db_add_field ( 'log' , 'id' , '{AUTOINCREMENT} {PRIMARY}' , 'data' );
return ;
2018-05-23 23:10:12 +02:00
}
/* 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 );
" );
2017-11-11 13:52:15 +01:00
}
2017-12-30 12:39:09 +01:00
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2017-12-30 12:39:09 +01:00
function upgrade_1840_mysql_pgsql () {
# sqlite doesn't support changing the default value
$vacation = table_by_key ( 'vacation' );
2018-01-30 16:53:42 +01:00
db_query_parsed ( " ALTER TABLE $vacation ALTER COLUMN activeuntil SET DEFAULT '2038-01-18' " );
2017-12-30 12:39:09 +01:00
}
2018-05-25 10:58:18 +02:00
2019-01-16 10:17:56 +01:00
/**
2019-01-16 11:31:16 +01:00
* try and fix : https :// github . com / postfixadmin / postfixadmin / issues / 177 - sqlite missing columns
2019-01-16 10:17:56 +01:00
* @ return void
*/
2018-05-25 10:58:18 +02:00
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' );
}
}
2018-08-28 22:15:45 +02:00
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2018-08-28 22:15:45 +02:00
function upgrade_1842 () {
2021-05-08 21:11:58 +02:00
$domain = table_by_key ( 'domain' );
// See: https://github.com/postfixadmin/postfixadmin/issues/489
// Avoid : ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'created' at row 1
if ( db_mysql ()) {
db_execute ( " UPDATE $domain SET created='2000-01-01 00:00:00', modified='2000-01-01 00:00:00' WHERE domain='ALL' " , [], true );
}
2018-08-28 22:15:45 +02:00
_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
2018-12-28 20:31:43 +01:00
}
2018-12-17 12:10:25 +01:00
2019-01-16 10:17:56 +01:00
/**
* @ return void
*/
2018-12-17 12:10:25 +01:00
function upgrade_1843 () {
2019-01-07 23:02:04 +01:00
# Additional field for fetchmail to allow server with non-standard port number
2018-12-17 12:10:25 +01:00
_db_add_field ( 'fetchmail' , 'src_port' , " { INT} " , 'src_server' );
}
2021-04-13 22:08:49 +02:00
/**
* @ return void
*/
function upgrade_1844 () {
# See:https://github.com/postfixadmin/postfixadmin/issues/475 - add pkey to domain_admins.
2021-04-13 22:26:11 +02:00
if ( db_sqlite ()) {
return ;
}
2021-05-08 21:11:58 +02:00
2021-04-13 22:08:49 +02:00
_db_add_field ( 'domain_admins' , 'id' , '{AUTOINCREMENT} {PRIMARY}' );
}
2021-12-07 20:42:49 +01:00
2022-03-14 15:48:34 +01:00
function upgrade_1845 () {
2021-12-07 20:42:49 +01:00
# See: https://github.com/postfixadmin/postfixadmin/pull/484
#
if ( ! db_mysql ()) {
return ;
}
$vacation = table_by_key ( 'vacation' );
$mailbox = table_by_key ( 'mailbox' );
db_query ( " alter table $vacation change body body text charset utf8mb4 not null " );
db_query ( " alter table $vacation change subject subject varchar(255) charset utf8mb4 not null " );
db_query ( " alter table $mailbox change name name varchar(255) charset utf8mb4 not null " );
}
2022-03-14 15:48:34 +01:00
function upgrade_1846_mysql () {
2021-12-07 20:42:49 +01:00
# See https://github.com/postfixadmin/postfixadmin/issues/327
$alias = table_by_key ( 'alias' );
$domain = table_by_key ( 'domain' );
$mailbox = table_by_key ( 'mailbox' );
$vacation = table_by_key ( 'vacation' );
$vacation_notification = table_by_key ( 'vacation_notification' );
$alias_domain = table_by_key ( 'alias_domain' );
$domain_admins = table_by_key ( 'domain_admins' );
db_query ( " ALTER TABLE $alias MODIFY address varchar(255) COLLATE latin1_general_ci NOT NULL " );
db_query ( " ALTER TABLE $alias MODIFY goto text COLLATE latin1_general_ci NOT NULL " );
db_query ( " ALTER TABLE $alias MODIFY domain varchar(255) COLLATE latin1_general_ci NOT NULL " );
db_query ( " ALTER TABLE $domain MODIFY domain varchar(255) COLLATE latin1_general_ci NOT NULL " );
db_query ( " ALTER TABLE $domain MODIFY transport varchar(255) COLLATE latin1_general_ci NOT NULL " );
db_query ( " ALTER TABLE $mailbox MODIFY username varchar(255) COLLATE latin1_general_ci NOT NULL " );
db_query ( " ALTER TABLE $mailbox MODIFY password varchar(255) COLLATE latin1_general_ci NOT NULL " );
db_query ( " ALTER TABLE $mailbox MODIFY maildir varchar(255) COLLATE latin1_general_ci NOT NULL " );
db_query ( " ALTER TABLE $mailbox MODIFY local_part varchar(255) COLLATE latin1_general_ci NOT NULL " );
db_query ( " ALTER TABLE $mailbox MODIFY domain varchar(255) COLLATE latin1_general_ci NOT NULL " );
2022-01-25 17:58:35 +01:00
db_query ( " ALTER TABLE $vacation_notification DROP FOREIGN KEY vacation_notification_pkey " );
2021-12-07 20:42:49 +01:00
db_query ( " ALTER TABLE $vacation MODIFY domain varchar(255) COLLATE latin1_general_ci NOT NULL " );
db_query ( " ALTER TABLE $vacation MODIFY email varchar(255) COLLATE latin1_general_ci NOT NULL " );
db_query ( " ALTER TABLE $alias_domain MODIFY alias_domain varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '' " );
db_query ( " ALTER TABLE $alias_domain MODIFY target_domain varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '' " );
db_query ( " ALTER TABLE $vacation_notification MODIFY on_vacation VARCHAR(255) COLLATE latin1_general_ci NOT NULL " );
db_query ( " ALTER TABLE $vacation_notification ADD CONSTRAINT vacation_notification_pkey FOREIGN KEY (`on_vacation`) REFERENCES $vacation (email) ON DELETE CASCADE " );
db_query ( " ALTER TABLE $domain_admins MODIFY `domain` varchar(255) COLLATE latin1_general_ci NOT NULL " );
}
2022-12-06 09:55:40 +01:00
function upgrade_1847_mysql () {
2022-12-06 09:56:28 +01:00
# See https://github.com/postfixadmin/postfixadmin/issues/327
2022-12-06 09:55:40 +01:00
# See https://github.com/postfixadmin/postfixadmin/issues/690
#
#
2022-12-06 09:56:28 +01:00
foreach ([ 'quota' , 'quota2' ] as $table ) {
2022-12-06 09:55:40 +01:00
$table = table_by_key ( $table );
db_query ( " ALTER TABLE $table MODIFY username varchar(255) COLLATE latin1_general_ci NOT NULL " );
}
}