You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643
  1. <?php
  2. if(!defined('POSTFIXADMIN')) {
  3. require_once('common.php');
  4. }
  5. /* vim: set expandtab softtabstop=4 tabstop=4 shiftwidth=4: */
  6. # @version $Id: upgrade.php 1853 2016-05-22 19:58:54Z christian_boltz $
  7. # Note: run with upgrade.php?debug=1 to see all SQL error messages
  8. /**
  9. * Use this to check whether an object (Table, index etc) exists within a
  10. * PostgreSQL database.
  11. * @param String the object name
  12. * @return boolean true if it exists
  13. */
  14. function _pgsql_object_exists($name) {
  15. $sql = "select relname from pg_class where relname = '$name'";
  16. $r = db_query($sql);
  17. if($r['rows'] == 1) {
  18. return true;
  19. }
  20. return false;
  21. }
  22. function _pgsql_field_exists($table, $field) {
  23. # $table = table_by_key($table); # _pgsql_field_exists is always called with the expanded table name - don't expand it twice
  24. $sql = '
  25. SELECT
  26. a.attname,
  27. pg_catalog.format_type(a.atttypid, a.atttypmod) AS "Datatype"
  28. FROM
  29. pg_catalog.pg_attribute a
  30. WHERE
  31. a.attnum > 0
  32. AND NOT a.attisdropped
  33. AND a.attrelid = (
  34. SELECT c.oid
  35. FROM pg_catalog.pg_class c
  36. LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  37. WHERE c.relname ~ ' . "'^($table)\$'
  38. AND pg_catalog.pg_table_is_visible(c.oid)
  39. )
  40. AND a.attname = '$field' ";
  41. $r = db_query($sql);
  42. $row = db_row($r['result']);
  43. if($row) {
  44. return true;
  45. }
  46. return false;
  47. }
  48. function _mysql_field_exists($table, $field) {
  49. # $table = table_by_key($table); # _mysql_field_exists is always called with the expanded table name - don't expand it twice
  50. $sql = "SHOW COLUMNS FROM $table LIKE '$field'";
  51. $r = db_query($sql);
  52. $row = db_row($r['result']);
  53. if($row) {
  54. return true;
  55. }
  56. return false;
  57. }
  58. function _db_field_exists($table, $field) {
  59. global $CONF;
  60. if($CONF['database_type'] == 'pgsql') {
  61. return _pgsql_field_exists($table, $field);
  62. } else {
  63. return _mysql_field_exists($table, $field);
  64. }
  65. }
  66. function _upgrade_filter_function($name) {
  67. return preg_match('/upgrade_[\d]+(_mysql|_pgsql|_sqlite|_mysql_pgsql)?$/', $name) == 1;
  68. }
  69. function _db_add_field($table, $field, $fieldtype, $after) {
  70. global $CONF;
  71. $query = "ALTER TABLE " . table_by_key($table) . " ADD COLUMN $field $fieldtype";
  72. if($CONF['database_type'] != 'pgsql') {
  73. $query .= " AFTER $after "; # PgSQL does not support to specify where to add the column, MySQL does
  74. }
  75. if(! _db_field_exists(table_by_key($table), $field)) {
  76. $result = db_query_parsed($query);
  77. } else {
  78. printdebug ("field already exists: $table.$field");
  79. }
  80. }
  81. function printdebug($text) {
  82. if (safeget('debug') != "") print "<p style='color:#999'>$text</p>";
  83. }
  84. $table = table_by_key('config');
  85. if($CONF['database_type'] == 'pgsql') {
  86. // check if table already exists, if so, don't recreate it
  87. $r = db_query("SELECT relname FROM pg_class WHERE relname = '$table'");
  88. if($r['rows'] == 0) {
  89. $pgsql = "
  90. CREATE TABLE $table (
  91. id SERIAL,
  92. name VARCHAR(20) NOT NULL UNIQUE,
  93. value VARCHAR(20) NOT NULL,
  94. PRIMARY KEY(id)
  95. )";
  96. db_query_parsed($pgsql);
  97. }
  98. } elseif(db_sqlite()) {
  99. $enc = 'PRAGMA encoding = "UTF-8"';
  100. db_query_parsed($enc);
  101. $sql = "
  102. CREATE TABLE {IF_NOT_EXISTS} $table (
  103. `id` {AUTOINCREMENT},
  104. `name` TEXT NOT NULL UNIQUE DEFAULT '',
  105. `value` TEXT NOT NULL DEFAULT ''
  106. )
  107. ";
  108. db_query_parsed($sql);
  109. } else {
  110. $mysql = "
  111. CREATE TABLE {IF_NOT_EXISTS} $table (
  112. `id` {AUTOINCREMENT} {PRIMARY},
  113. `name` VARCHAR(20) {LATIN1} NOT NULL DEFAULT '',
  114. `value` VARCHAR(20) {LATIN1} NOT NULL DEFAULT '',
  115. UNIQUE name ( `name` )
  116. )
  117. ";
  118. db_query_parsed($mysql, 0, " ENGINE = MYISAM COMMENT = 'PostfixAdmin settings'");
  119. }
  120. $version = check_db_version(False);
  121. _do_upgrade($version);
  122. function _do_upgrade($current_version) {
  123. global $CONF;
  124. $target_version = 0;
  125. // Rather than being bound to an svn revision number, just look for the largest function name that matches upgrade_\d+...
  126. // $target_version = preg_replace('/[^0-9]/', '', '$Revision: 1853 $');
  127. $funclist = get_defined_functions();
  128. $our_upgrade_functions = array_filter($funclist['user'], '_upgrade_filter_function');
  129. foreach($our_upgrade_functions as $function_name) {
  130. $bits = explode("_", $function_name);
  131. $function_number = $bits[1];
  132. if($function_number > $current_version && $function_number > $target_version) {
  133. $target_version = $function_number;
  134. }
  135. }
  136. if ($current_version >= $target_version) {
  137. # already up to date
  138. echo "<p>Database is up to date</p>";
  139. return true;
  140. }
  141. echo "<p>Updating database:</p><p>- old version: $current_version; target version: $target_version</p>\n";
  142. echo "<div style='color:#999'>&nbsp;&nbsp;(If the update doesn't work, run setup.php?debug=1 to see the detailed error messages and SQL queries.)</div>";
  143. if (db_sqlite() && $current_version < 1824) {
  144. // Fast forward to the first revision supporting SQLite
  145. $current_version = 1823;
  146. }
  147. for ($i = $current_version +1; $i <= $target_version; $i++) {
  148. $function = "upgrade_$i";
  149. $function_mysql_pgsql = $function . "_mysql_pgsql";
  150. $function_mysql = $function . "_mysql";
  151. $function_pgsql = $function . "_pgsql";
  152. $function_sqlite = $function . "_sqlite";
  153. if (function_exists($function)) {
  154. echo "<p>updating to version $i (all databases)...";
  155. $function();
  156. echo " &nbsp; done";
  157. }
  158. if ($CONF['database_type'] == 'mysql' || $CONF['database_type'] == 'mysqli' || $CONF['database_type'] == 'pgsql') {
  159. if (function_exists($function_mysql_pgsql)) {
  160. echo "<p>updating to version $i (MySQL and PgSQL)...";
  161. $function_mysql_pgsql();
  162. echo " &nbsp; done";
  163. }
  164. }
  165. if ($CONF['database_type'] == 'mysql' || $CONF['database_type'] == 'mysqli' ) {
  166. if (function_exists($function_mysql)) {
  167. echo "<p>updating to version $i (MySQL)...";
  168. $function_mysql();
  169. echo " &nbsp; done";
  170. }
  171. } elseif(db_sqlite()) {
  172. if (function_exists($function_sqlite)) {
  173. echo "<p>updating to version $i (SQLite)...";
  174. $function_sqlite();
  175. echo " &nbsp; done";
  176. }
  177. } elseif($CONF['database_type'] == 'pgsql') {
  178. if (function_exists($function_pgsql)) {
  179. echo "<p>updating to version $i (PgSQL)...";
  180. $function_pgsql();
  181. echo " &nbsp; done";
  182. }
  183. }
  184. // Update config table so we don't run the same query twice in the future.
  185. $i = (int) $i;
  186. $table = table_by_key('config');
  187. $sql = "UPDATE $table SET value = $i WHERE name = 'version'";
  188. db_query($sql);
  189. };
  190. }
  191. /**
  192. * Replaces database specific parts in a query
  193. * @param String sql query with placeholders
  194. * @param int (optional) whether errors should be ignored (0=false)
  195. * @param String (optional) MySQL specific code to attach, useful for COMMENT= on CREATE TABLE
  196. * @return String sql query
  197. */
  198. function db_query_parsed($sql, $ignore_errors = 0, $attach_mysql = "") {
  199. global $CONF;
  200. if ($CONF['database_type'] == 'mysql' || $CONF['database_type'] == 'mysqli' ) {
  201. $replace = array(
  202. '{AUTOINCREMENT}' => 'int(11) not null auto_increment',
  203. '{PRIMARY}' => 'primary key',
  204. '{UNSIGNED}' => 'unsigned' ,
  205. '{FULLTEXT}' => 'FULLTEXT',
  206. '{BOOLEAN}' => "tinyint(1) NOT NULL DEFAULT '" . db_get_boolean(False) . "'",
  207. '{UTF-8}' => '/*!40100 CHARACTER SET utf8 */',
  208. '{LATIN1}' => '/*!40100 CHARACTER SET latin1 */',
  209. '{IF_NOT_EXISTS}' => 'IF NOT EXISTS',
  210. '{RENAME_COLUMN}' => 'CHANGE COLUMN',
  211. '{MYISAM}' => 'ENGINE=MyISAM',
  212. '{INNODB}' => 'ENGINE=InnoDB',
  213. '{INT}' => 'integer NOT NULL DEFAULT 0',
  214. '{BIGINT}' => 'bigint NOT NULL DEFAULT 0',
  215. '{DATETIME}' => "datetime NOT NULL default '2000-01-01 00:00:00'", # different from {DATE} only for MySQL
  216. '{DATE}' => "timestamp NOT NULL default '2000-01-01'", # MySQL needs a sane default (no default is interpreted as CURRENT_TIMESTAMP, which is ...
  217. '{DATECURRENT}' => 'timestamp NOT NULL default CURRENT_TIMESTAMP', # only allowed once per table in MySQL
  218. );
  219. $sql = "$sql $attach_mysql";
  220. } elseif(db_sqlite()) {
  221. $replace = array(
  222. '{AUTOINCREMENT}' => 'integer PRIMARY KEY AUTOINCREMENT NOT NULL',
  223. '{PRIMARY}' => 'PRIMARY KEY',
  224. '{UNSIGNED}' => 'unsigned',
  225. '{FULLTEXT}' => 'text',
  226. '{BOOLEAN}' => "tinyint(1) NOT NULL DEFAULT '" . db_get_boolean(False) . "'",
  227. '{BOOLEAN_TRUE}' => "tinyint(1) NOT NULL DEFAULT '" . db_get_boolean(True) . "'",
  228. '{UTF-8}' => '',
  229. '{LATIN1}' => '',
  230. '{IF_NOT_EXISTS}' => 'IF NOT EXISTS',
  231. '{RENAME_COLUMN}' => 'CHANGE COLUMN',
  232. '{MYISAM}' => '',
  233. '{INNODB}' => '',
  234. '{INT}' => 'int(11) NOT NULL DEFAULT 0',
  235. '{BIGINT}' => 'bigint(20) NOT NULL DEFAULT 0',
  236. '{DATETIME}' => "datetime NOT NULL default '2000-01-01'",
  237. '{DATE}' => "datetime NOT NULL default '2000-01-01'",
  238. '{DATECURRENT}' => 'datetime NOT NULL default CURRENT_TIMESTAMP',
  239. );
  240. } elseif($CONF['database_type'] == 'pgsql') {
  241. $replace = array(
  242. '{AUTOINCREMENT}' => 'SERIAL',
  243. '{PRIMARY}' => 'primary key',
  244. '{UNSIGNED}' => '',
  245. '{FULLTEXT}' => '',
  246. '{BOOLEAN}' => "BOOLEAN NOT NULL DEFAULT '" . db_get_boolean(False) . "'",
  247. '{UTF-8}' => '', # UTF-8 is simply ignored.
  248. '{LATIN1}' => '', # same for latin1
  249. '{IF_NOT_EXISTS}' => '', # does not work with PgSQL
  250. '{RENAME_COLUMN}' => 'ALTER COLUMN', # PgSQL : ALTER TABLE x RENAME x TO y
  251. '{MYISAM}' => '',
  252. '{INNODB}' => '',
  253. '{INT}' => 'integer NOT NULL DEFAULT 0',
  254. '{BIGINT}' => 'bigint NOT NULL DEFAULT 0',
  255. 'int(1)' => 'int',
  256. 'int(10)' => 'int',
  257. 'int(11)' => 'int',
  258. 'int(4)' => 'int',
  259. '{DATETIME}' => "timestamp with time zone default '2000-01-01'", # stay in sync with MySQL
  260. '{DATE}' => "timestamp with time zone default '2000-01-01'", # stay in sync with MySQL
  261. '{DATECURRENT}' => 'timestamp with time zone default now()',
  262. );
  263. } else {
  264. echo "Sorry, unsupported database type " . $conf['database_type'];
  265. exit;
  266. }
  267. $replace['{BOOL_TRUE}'] = db_get_boolean(True);
  268. $replace['{BOOL_FALSE}'] = db_get_boolean(False);
  269. $query = trim(str_replace(array_keys($replace), $replace, $sql));
  270. if (safeget('debug') != "") {
  271. printdebug ($query);
  272. }
  273. $result = db_query($query, $ignore_errors);
  274. if (safeget('debug') != "") {
  275. print "<div style='color:#f00'>" . $result['error'] . "</div>";
  276. }
  277. return $result;
  278. }
  279. function _drop_index ($table, $index) {
  280. global $CONF;
  281. $table = table_by_key ($table);
  282. if ($CONF['database_type'] == 'mysql' || $CONF['database_type'] == 'mysqli' ) {
  283. return "ALTER TABLE $table DROP INDEX $index";
  284. } elseif($CONF['database_type'] == 'pgsql' || db_sqlite()) {
  285. return "DROP INDEX $index"; # Index names are unique with a DB for PostgreSQL
  286. } else {
  287. echo "Sorry, unsupported database type " . $conf['database_type'];
  288. exit;
  289. }
  290. }
  291. function _add_index($table, $indexname, $fieldlist) {
  292. global $CONF;
  293. $table = table_by_key ($table);
  294. if ($CONF['database_type'] == 'mysql' || $CONF['database_type'] == 'mysqli' ) {
  295. $fieldlist = str_replace(',', '`,`', $fieldlist); # fix quoting if index contains multiple fields
  296. return "ALTER TABLE $table ADD INDEX `$indexname` ( `$fieldlist` )";
  297. } elseif($CONF['database_type'] == 'pgsql') {
  298. $pgindexname = $table . "_" . $indexname . '_idx';
  299. return "CREATE INDEX $pgindexname ON $table($fieldlist);"; # Index names are unique with a DB for PostgreSQL
  300. } else {
  301. echo "Sorry, unsupported database type " . $conf['database_type'];
  302. exit;
  303. }
  304. }
  305. function upgrade_1_mysql() {
  306. // CREATE MYSQL DATABASE TABLES.
  307. $admin = table_by_key('admin');
  308. $alias = table_by_key('alias');
  309. $domain = table_by_key('domain');
  310. $domain_admins = table_by_key('domain_admins');
  311. $log = table_by_key('log');
  312. $mailbox = table_by_key('mailbox');
  313. $vacation = table_by_key('vacation');
  314. $sql = array();
  315. $sql[] = "
  316. CREATE TABLE {IF_NOT_EXISTS} $admin (
  317. `username` varchar(255) NOT NULL default '',
  318. `password` varchar(255) NOT NULL default '',
  319. `created` {DATETIME},
  320. `modified` {DATETIME},
  321. `active` tinyint(1) NOT NULL default '1',
  322. PRIMARY KEY (`username`)
  323. ) {MYISAM} COMMENT='Postfix Admin - Virtual Admins';";
  324. $sql[] = "
  325. CREATE TABLE {IF_NOT_EXISTS} $alias (
  326. `address` varchar(255) NOT NULL default '',
  327. `goto` text NOT NULL,
  328. `domain` varchar(255) NOT NULL default '',
  329. `created` {DATETIME},
  330. `modified` {DATETIME},
  331. `active` tinyint(1) NOT NULL default '1',
  332. PRIMARY KEY (`address`)
  333. ) {MYISAM} COMMENT='Postfix Admin - Virtual Aliases'; ";
  334. $sql[] = "
  335. CREATE TABLE {IF_NOT_EXISTS} $domain (
  336. `domain` varchar(255) NOT NULL default '',
  337. `description` varchar(255) NOT NULL default '',
  338. `aliases` int(10) NOT NULL default '0',
  339. `mailboxes` int(10) NOT NULL default '0',
  340. `maxquota` bigint(20) NOT NULL default '0',
  341. `quota` bigint(20) NOT NULL default '0',
  342. `transport` varchar(255) default NULL,
  343. `backupmx` tinyint(1) NOT NULL default '0',
  344. `created` {DATETIME},
  345. `modified` {DATETIME},
  346. `active` tinyint(1) NOT NULL default '1',
  347. PRIMARY KEY (`domain`)
  348. ) {MYISAM} COMMENT='Postfix Admin - Virtual Domains'; ";
  349. $sql[] = "
  350. CREATE TABLE {IF_NOT_EXISTS} $domain_admins (
  351. `username` varchar(255) NOT NULL default '',
  352. `domain` varchar(255) NOT NULL default '',
  353. `created` {DATETIME},
  354. `active` tinyint(1) NOT NULL default '1',
  355. KEY username (`username`)
  356. ) {MYISAM} COMMENT='Postfix Admin - Domain Admins';";
  357. $sql[] = "
  358. CREATE TABLE {IF_NOT_EXISTS} $log (
  359. `timestamp` {DATETIME},
  360. `username` varchar(255) NOT NULL default '',
  361. `domain` varchar(255) NOT NULL default '',
  362. `action` varchar(255) NOT NULL default '',
  363. `data` varchar(255) NOT NULL default '',
  364. KEY timestamp (`timestamp`)
  365. ) {MYISAM} COMMENT='Postfix Admin - Log';";
  366. $sql[] = "
  367. CREATE TABLE {IF_NOT_EXISTS} $mailbox (
  368. `username` varchar(255) NOT NULL default '',
  369. `password` varchar(255) NOT NULL default '',
  370. `name` varchar(255) NOT NULL default '',
  371. `maildir` varchar(255) NOT NULL default '',
  372. `quota` bigint(20) NOT NULL default '0',
  373. `domain` varchar(255) NOT NULL default '',
  374. `created` {DATETIME},
  375. `modified` {DATETIME},
  376. `active` tinyint(1) NOT NULL default '1',
  377. PRIMARY KEY (`username`)
  378. ) {MYISAM} COMMENT='Postfix Admin - Virtual Mailboxes';";
  379. $sql[] = "
  380. CREATE TABLE {IF_NOT_EXISTS} $vacation (
  381. email varchar(255) NOT NULL ,
  382. subject varchar(255) NOT NULL,
  383. body text NOT NULL,
  384. cache text NOT NULL,
  385. domain varchar(255) NOT NULL ,
  386. created {DATETIME},
  387. active tinyint(4) NOT NULL default '1',
  388. PRIMARY KEY (email),
  389. KEY email (email)
  390. ) {INNODB} DEFAULT CHARSET=latin1 COMMENT='Postfix Admin - Virtual Vacation' ;";
  391. foreach($sql as $query) {
  392. db_query_parsed($query);
  393. }
  394. }
  395. function upgrade_2_mysql() {
  396. # upgrade pre-2.1 database
  397. # from TABLE_BACKUP_MX.TXT
  398. $table_domain = table_by_key ('domain');
  399. if(!_mysql_field_exists($table_domain, 'transport')) {
  400. $result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN transport VARCHAR(255) AFTER maxquota;", TRUE);
  401. }
  402. if(!_mysql_field_exists($table_domain, 'backupmx')) {
  403. $result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN backupmx {BOOLEAN} AFTER transport;", TRUE);
  404. }
  405. }
  406. function upgrade_2_pgsql() {
  407. if(!_pgsql_object_exists(table_by_key('domain'))) {
  408. db_query_parsed("
  409. CREATE TABLE " . table_by_key('domain') . " (
  410. domain character varying(255) NOT NULL,
  411. description character varying(255) NOT NULL default '',
  412. aliases integer NOT NULL default 0,
  413. mailboxes integer NOT NULL default 0,
  414. maxquota integer NOT NULL default 0,
  415. quota integer NOT NULL default 0,
  416. transport character varying(255) default NULL,
  417. backupmx boolean NOT NULL default false,
  418. created timestamp with time zone default now(),
  419. modified timestamp with time zone default now(),
  420. active boolean NOT NULL default true,
  421. Constraint \"domain_key\" Primary Key (\"domain\")
  422. );
  423. CREATE INDEX domain_domain_active ON " . table_by_key('domain') . "(domain,active);
  424. COMMENT ON TABLE " . table_by_key('domain') . " IS 'Postfix Admin - Virtual Domains';
  425. ");
  426. }
  427. if(!_pgsql_object_exists(table_by_key('admin'))) {
  428. db_query_parsed('
  429. CREATE TABLE ' . table_by_key("admin") . ' (
  430. "username" character varying(255) NOT NULL,
  431. "password" character varying(255) NOT NULL default \'\',
  432. "created" timestamp with time zone default now(),
  433. "modified" timestamp with time zone default now(),
  434. "active" boolean NOT NULL default true,
  435. Constraint "admin_key" Primary Key ("username")
  436. );' . "
  437. COMMENT ON TABLE " . table_by_key('admin') . " IS 'Postfix Admin - Virtual Admins';
  438. ");
  439. }
  440. if(!_pgsql_object_exists(table_by_key('alias'))) {
  441. db_query_parsed('
  442. CREATE TABLE ' . table_by_key("alias") . ' (
  443. address character varying(255) NOT NULL,
  444. goto text NOT NULL,
  445. domain character varying(255) NOT NULL REFERENCES "' . table_by_key("domain") . '",
  446. created timestamp with time zone default now(),
  447. modified timestamp with time zone default now(),
  448. active boolean NOT NULL default true,
  449. Constraint "alias_key" Primary Key ("address")
  450. );
  451. CREATE INDEX alias_address_active ON ' . table_by_key("alias") . '(address,active);
  452. COMMENT ON TABLE ' . table_by_key("alias") . ' IS \'Postfix Admin - Virtual Aliases\';
  453. ');
  454. }
  455. if(!_pgsql_object_exists(table_by_key('domain_admins'))) {
  456. db_query_parsed('
  457. CREATE TABLE ' . table_by_key('domain_admins') . ' (
  458. username character varying(255) NOT NULL,
  459. domain character varying(255) NOT NULL REFERENCES "' . table_by_key('domain') . '",
  460. created timestamp with time zone default now(),
  461. active boolean NOT NULL default true
  462. );
  463. COMMENT ON TABLE ' . table_by_key('domain_admins') . ' IS \'Postfix Admin - Domain Admins\';
  464. ');
  465. }
  466. if(!_pgsql_object_exists(table_by_key('log'))) {
  467. db_query_parsed('
  468. CREATE TABLE ' . table_by_key('log') . ' (
  469. timestamp timestamp with time zone default now(),
  470. username character varying(255) NOT NULL default \'\',
  471. domain character varying(255) NOT NULL default \'\',
  472. action character varying(255) NOT NULL default \'\',
  473. data text NOT NULL default \'\'
  474. );
  475. COMMENT ON TABLE ' . table_by_key('log') . ' IS \'Postfix Admin - Log\';
  476. ');
  477. }
  478. if(!_pgsql_object_exists(table_by_key('mailbox'))) {
  479. db_query_parsed('
  480. CREATE TABLE ' . table_by_key('mailbox') . ' (
  481. username character varying(255) NOT NULL,
  482. password character varying(255) NOT NULL default \'\',
  483. name character varying(255) NOT NULL default \'\',
  484. maildir character varying(255) NOT NULL default \'\',
  485. quota integer NOT NULL default 0,
  486. domain character varying(255) NOT NULL REFERENCES "' . table_by_key('domain') . '",
  487. created timestamp with time zone default now(),
  488. modified timestamp with time zone default now(),
  489. active boolean NOT NULL default true,
  490. Constraint "mailbox_key" Primary Key ("username")
  491. );
  492. CREATE INDEX mailbox_username_active ON ' . table_by_key('mailbox') . '(username,active);
  493. COMMENT ON TABLE ' . table_by_key('mailbox') . ' IS \'Postfix Admin - Virtual Mailboxes\';
  494. ');
  495. }
  496. if(!_pgsql_object_exists(table_by_key('vacation'))) {
  497. db_query_parsed('
  498. CREATE TABLE ' . table_by_key('vacation') . ' (
  499. email character varying(255) PRIMARY KEY,
  500. subject character varying(255) NOT NULL,
  501. body text NOT NULL ,
  502. cache text NOT NULL ,
  503. "domain" character varying(255) NOT NULL REFERENCES "' . table_by_key('domain') . '",
  504. created timestamp with time zone DEFAULT now(),
  505. active boolean DEFAULT true NOT NULL
  506. );
  507. CREATE INDEX vacation_email_active ON ' . table_by_key('vacation') . '(email,active);');
  508. }
  509. if(!_pgsql_object_exists(table_by_key('vacation_notification'))) {
  510. db_query_parsed('
  511. CREATE TABLE ' . table_by_key('vacation_notification') . ' (
  512. on_vacation character varying(255) NOT NULL REFERENCES ' . table_by_key('vacation') . '(email) ON DELETE CASCADE,
  513. notified character varying(255) NOT NULL,
  514. notified_at timestamp with time zone NOT NULL DEFAULT now(),
  515. CONSTRAINT vacation_notification_pkey primary key(on_vacation,notified)
  516. );
  517. ');
  518. }
  519. // this handles anyone who is upgrading... (and should have no impact on new installees)
  520. $table_domain = table_by_key ('domain');
  521. $result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN transport VARCHAR(255)", TRUE);
  522. $result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN backupmx BOOLEAN DEFAULT false", TRUE);
  523. }
  524. function upgrade_3_mysql() {
  525. # upgrade pre-2.1 database
  526. # from TABLE_CHANGES.TXT
  527. $table_admin = table_by_key ('admin');
  528. $table_alias = table_by_key ('alias');
  529. $table_domain = table_by_key ('domain');
  530. $table_mailbox = table_by_key ('mailbox');
  531. $table_vacation = table_by_key ('vacation');
  532. if(!_mysql_field_exists($table_admin, 'created')) {
  533. db_query_parsed("ALTER TABLE $table_admin {RENAME_COLUMN} create_date created {DATETIME};");
  534. }
  535. if(!_mysql_field_exists($table_admin, 'modified')) {
  536. db_query_parsed("ALTER TABLE $table_admin {RENAME_COLUMN} change_date modified {DATETIME};");
  537. }
  538. if(!_mysql_field_exists($table_alias, 'created')) {
  539. db_query_parsed("ALTER TABLE $table_alias {RENAME_COLUMN} create_date created {DATETIME};");
  540. }
  541. if(!_mysql_field_exists($table_alias, 'modified')) {
  542. db_query_parsed("ALTER TABLE $table_alias {RENAME_COLUMN} change_date modified {DATETIME};");
  543. }
  544. if(!_mysql_field_exists($table_domain, 'created')) {
  545. db_query_parsed("ALTER TABLE $table_domain {RENAME_COLUMN} create_date created {DATETIME};");
  546. }
  547. if(!_mysql_field_exists($table_domain, 'modified')) {
  548. db_query_parsed("ALTER TABLE $table_domain {RENAME_COLUMN} change_date modified {DATETIME};");
  549. }
  550. if(!_mysql_field_exists($table_domain, 'aliases')) {
  551. db_query_parsed("ALTER TABLE $table_domain ADD COLUMN aliases INT(10) DEFAULT '-1' NOT NULL AFTER description;");
  552. }
  553. if(!_mysql_field_exists($table_domain, 'mailboxes')) {
  554. db_query_parsed("ALTER TABLE $table_domain ADD COLUMN mailboxes INT(10) DEFAULT '-1' NOT NULL AFTER aliases;");
  555. }
  556. if(!_mysql_field_exists($table_domain, 'maxquota')) {
  557. db_query_parsed("ALTER TABLE $table_domain ADD COLUMN maxquota INT(10) DEFAULT '-1' NOT NULL AFTER mailboxes;");
  558. }
  559. if(!_mysql_field_exists($table_domain, 'transport')) {
  560. db_query_parsed("ALTER TABLE $table_domain ADD COLUMN transport VARCHAR(255) AFTER maxquota;");
  561. }
  562. if(!_mysql_field_exists($table_domain, 'backupmx')) {
  563. db_query_parsed("ALTER TABLE $table_domain ADD COLUMN backupmx TINYINT(1) DEFAULT '0' NOT NULL AFTER transport;");
  564. }
  565. if(!_mysql_field_exists($table_mailbox, 'created')) {
  566. db_query_parsed("ALTER TABLE $table_mailbox {RENAME_COLUMN} create_date created {DATETIME};");
  567. }
  568. if(!_mysql_field_exists($table_mailbox, 'modified')) {
  569. db_query_parsed("ALTER TABLE $table_mailbox {RENAME_COLUMN} change_date modified {DATETIME};");
  570. }
  571. if(!_mysql_field_exists($table_mailbox, 'quota')) {
  572. db_query_parsed("ALTER TABLE $table_mailbox ADD COLUMN quota INT(10) DEFAULT '-1' NOT NULL AFTER maildir;");
  573. }
  574. if(!_mysql_field_exists($table_vacation, 'domain')) {
  575. db_query_parsed("ALTER TABLE $table_vacation ADD COLUMN domain VARCHAR(255) DEFAULT '' NOT NULL AFTER cache;");
  576. }
  577. if(!_mysql_field_exists($table_vacation, 'created')) {
  578. db_query_parsed("ALTER TABLE $table_vacation ADD COLUMN created {DATETIME} AFTER domain;");
  579. }
  580. if(!_mysql_field_exists($table_vacation, 'active')) {
  581. db_query_parsed("ALTER TABLE $table_vacation ADD COLUMN active TINYINT(1) DEFAULT '1' NOT NULL AFTER created;");
  582. }
  583. db_query_parsed("ALTER TABLE $table_vacation DROP PRIMARY KEY");
  584. db_query_parsed("ALTER TABLE $table_vacation ADD PRIMARY KEY(email)");
  585. db_query_parsed("UPDATE $table_vacation SET domain=SUBSTRING_INDEX(email, '@', -1) WHERE email=email;");
  586. }
  587. function upgrade_4_mysql() { # MySQL only
  588. # changes between 2.1 and moving to sourceforge
  589. $table_domain = table_by_key ('domain');
  590. $result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN quota int(10) NOT NULL default '0' AFTER maxquota", TRUE);
  591. # Possible errors that can be ignored:
  592. # - Invalid query: Table 'postfix.domain' doesn't exist
  593. }
  594. /**
  595. * Changes between 2.1 and moving to sf.net
  596. */
  597. function upgrade_4_pgsql() {
  598. $table_domain = table_by_key('domain');
  599. $table_admin = table_by_key('admin');
  600. $table_alias = table_by_key('alias');
  601. $table_domain_admins = table_by_key('domain_admins');
  602. $table_log = table_by_key('log');
  603. $table_mailbox = table_by_key('mailbox');
  604. $table_vacation = table_by_key('vacation');
  605. $table_vacation_notification = table_by_key('vacation_notification');
  606. if(!_pgsql_field_exists($table_domain, 'quota')) {
  607. $result = db_query_parsed("ALTER TABLE $table_domain ADD COLUMN quota int NOT NULL default '0'");
  608. }
  609. $result = db_query_parsed("ALTER TABLE $table_domain ALTER COLUMN domain DROP DEFAULT");
  610. if(!_pgsql_object_exists('domain_domain_active')) {
  611. $result = db_query_parsed("CREATE INDEX domain_domain_active ON $table_domain(domain,active)");
  612. }
  613. $result = db_query_parsed("ALTER TABLE $table_domain_admins ALTER COLUMN domain DROP DEFAULT");
  614. $result = db_query_parsed("ALTER TABLE $table_alias ALTER COLUMN address DROP DEFAULT");
  615. $result = db_query_parsed("ALTER TABLE $table_alias ALTER COLUMN domain DROP DEFAULT");
  616. if(!_pgsql_object_exists('alias_address_active')) {
  617. $result = db_query_parsed("CREATE INDEX alias_address_active ON $table_alias(address,active)");
  618. }
  619. $result = db_query_parsed("ALTER TABLE $table_domain_admins ALTER COLUMN username DROP DEFAULT");
  620. $result = db_query_parsed("ALTER TABLE $table_domain_admins ALTER COLUMN domain DROP DEFAULT");
  621. $result = db_query_parsed("
  622. BEGIN;
  623. ALTER TABLE $table_log RENAME COLUMN data TO data_old;
  624. ALTER TABLE $table_log ADD COLUMN data text NOT NULL default '';
  625. UPDATE $table_log SET data = CAST(data_old AS text);
  626. ALTER TABLE $table_log DROP COLUMN data_old;
  627. COMMIT;");
  628. $result = db_query_parsed("ALTER TABLE $table_mailbox ALTER COLUMN username DROP DEFAULT");
  629. $result = db_query_parsed("ALTER TABLE $table_mailbox ALTER COLUMN domain DROP DEFAULT");
  630. $result = db_query_parsed("
  631. BEGIN;
  632. ALTER TABLE $table_mailbox RENAME COLUMN domain TO domain_old;
  633. ALTER TABLE $table_mailbox ADD COLUMN domain varchar(255) REFERENCES $table_domain (domain);
  634. UPDATE $table_mailbox SET domain = domain_old;
  635. ALTER TABLE $table_mailbox DROP COLUMN domain_old;
  636. COMMIT;"
  637. );
  638. if(!_pgsql_object_exists('mailbox_username_active')) {
  639. db_query_parsed("CREATE INDEX mailbox_username_active ON $table_mailbox(username,active)");
  640. }
  641. $result = db_query_parsed("ALTER TABLE $table_vacation ALTER COLUMN body SET DEFAULT ''");
  642. if(_pgsql_field_exists($table_vacation, 'cache')) {
  643. $result = db_query_parsed("ALTER TABLE $table_vacation DROP COLUMN cache");
  644. }
  645. $result = db_query_parsed("
  646. BEGIN;
  647. ALTER TABLE $table_vacation RENAME COLUMN domain to domain_old;
  648. ALTER TABLE $table_vacation ADD COLUMN domain varchar(255) REFERENCES $table_domain;
  649. UPDATE $table_vacation SET domain = domain_old;
  650. ALTER TABLE $table_vacation DROP COLUMN domain_old;
  651. COMMIT;
  652. ");
  653. if(!_pgsql_object_exists('vacation_email_active')) {
  654. $result = db_query_parsed("CREATE INDEX vacation_email_active ON $table_vacation(email,active)");
  655. }
  656. if(!_pgsql_object_exists($table_vacation_notification)) {
  657. $result = db_query_parsed("
  658. CREATE TABLE $table_vacation_notification (
  659. on_vacation character varying(255) NOT NULL REFERENCES $table_vacation(email) ON DELETE CASCADE,
  660. notified character varying(255) NOT NULL,
  661. notified_at timestamp with time zone NOT NULL DEFAULT now(),
  662. CONSTRAINT vacation_notification_pkey primary key(on_vacation,notified));");
  663. }
  664. }
  665. # Possible errors that can be ignored:
  666. #
  667. # NO MySQL errors should be ignored below this line!
  668. /**
  669. * create tables
  670. * version: Sourceforge SVN r1 of DATABASE_MYSQL.txt
  671. * changes compared to DATABASE_MYSQL.txt:
  672. * - removed MySQL user and database creation
  673. * - removed creation of default superadmin
  674. */
  675. function upgrade_5_mysql() {
  676. $result = db_query_parsed("
  677. CREATE TABLE {IF_NOT_EXISTS} `" . table_by_key('admin') . "` (
  678. `username` varchar(255) NOT NULL default '',
  679. `password` varchar(255) NOT NULL default '',
  680. `created` {DATETIME},
  681. `modified` {DATETIME},
  682. `active` tinyint(1) NOT NULL default '1',
  683. PRIMARY KEY (`username`),
  684. KEY username (`username`)
  685. ) {MYISAM} DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Admins'; ");
  686. $result = db_query_parsed("
  687. CREATE TABLE {IF_NOT_EXISTS} `" . table_by_key('alias') . "` (
  688. `address` varchar(255) NOT NULL default '',
  689. `goto` text NOT NULL,
  690. `domain` varchar(255) NOT NULL default '',
  691. `created` {DATETIME},
  692. `modified` {DATETIME},
  693. `active` tinyint(1) NOT NULL default '1',
  694. PRIMARY KEY (`address`),
  695. KEY address (`address`)
  696. ) {MYISAM} DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Aliases';
  697. ");
  698. $result = db_query_parsed("
  699. CREATE TABLE {IF_NOT_EXISTS} `" . table_by_key('domain') . "` (
  700. `domain` varchar(255) NOT NULL default '',
  701. `description` varchar(255) NOT NULL default '',
  702. `aliases` int(10) NOT NULL default '0',
  703. `mailboxes` int(10) NOT NULL default '0',
  704. `maxquota` int(10) NOT NULL default '0',
  705. `quota` int(10) NOT NULL default '0',
  706. `transport` varchar(255) default NULL,
  707. `backupmx` tinyint(1) NOT NULL default '0',
  708. `created` {DATETIME},
  709. `modified` {DATETIME},
  710. `active` tinyint(1) NOT NULL default '1',
  711. PRIMARY KEY (`domain`),
  712. KEY domain (`domain`)
  713. ) {MYISAM} DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Domains';
  714. ");
  715. $result = db_query_parsed("
  716. CREATE TABLE {IF_NOT_EXISTS} `" . table_by_key('domain_admins') . "` (
  717. `username` varchar(255) NOT NULL default '',
  718. `domain` varchar(255) NOT NULL default '',
  719. `created` {DATETIME},
  720. `active` tinyint(1) NOT NULL default '1',
  721. KEY username (`username`)
  722. ) {MYISAM} DEFAULT {LATIN1} COMMENT='Postfix Admin - Domain Admins';
  723. ");
  724. $result = db_query_parsed("
  725. CREATE TABLE {IF_NOT_EXISTS} `" . table_by_key('log') . "` (
  726. `timestamp` {DATETIME},
  727. `username` varchar(255) NOT NULL default '',
  728. `domain` varchar(255) NOT NULL default '',
  729. `action` varchar(255) NOT NULL default '',
  730. `data` varchar(255) NOT NULL default '',
  731. KEY timestamp (`timestamp`)
  732. ) {MYISAM} DEFAULT {LATIN1} COMMENT='Postfix Admin - Log';
  733. ");
  734. $result = db_query_parsed("
  735. CREATE TABLE {IF_NOT_EXISTS} `" . table_by_key('mailbox') . "` (
  736. `username` varchar(255) NOT NULL default '',
  737. `password` varchar(255) NOT NULL default '',
  738. `name` varchar(255) NOT NULL default '',
  739. `maildir` varchar(255) NOT NULL default '',
  740. `quota` int(10) NOT NULL default '0',
  741. `domain` varchar(255) NOT NULL default '',
  742. `created` {DATETIME},
  743. `modified` {DATETIME},
  744. `active` tinyint(1) NOT NULL default '1',
  745. PRIMARY KEY (`username`),
  746. KEY username (`username`)
  747. ) {MYISAM} DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Mailboxes';
  748. ");
  749. $result = db_query_parsed("
  750. CREATE TABLE {IF_NOT_EXISTS} `" . table_by_key('vacation') . "` (
  751. `email` varchar(255) NOT NULL ,
  752. `subject` varchar(255) NOT NULL,
  753. `body` text NOT NULL,
  754. `cache` text NOT NULL,
  755. `domain` varchar(255) NOT NULL,
  756. `created` {DATETIME},
  757. `active` tinyint(1) NOT NULL default '1',
  758. PRIMARY KEY (`email`),
  759. KEY email (`email`)
  760. ) {MYISAM} DEFAULT {LATIN1} COMMENT='Postfix Admin - Virtual Vacation';
  761. ");
  762. }
  763. /**
  764. * drop useless indicies (already available as primary key)
  765. */
  766. function upgrade_79_mysql() { # MySQL only
  767. $result = db_query_parsed(_drop_index('admin', 'username'), True);
  768. $result = db_query_parsed(_drop_index('alias', 'address'), True);
  769. $result = db_query_parsed(_drop_index('domain', 'domain'), True);
  770. $result = db_query_parsed(_drop_index('mailbox', 'username'), True);
  771. }
  772. function upgrade_81_mysql() { # MySQL only
  773. $table_vacation = table_by_key ('vacation');
  774. $table_vacation_notification = table_by_key('vacation_notification');
  775. $all_sql = explode("\n", trim("
  776. ALTER TABLE `$table_vacation` CHANGE `email` `email` VARCHAR( 255 ) {LATIN1} NOT NULL
  777. ALTER TABLE `$table_vacation` CHANGE `subject` `subject` VARCHAR( 255 ) {UTF-8} NOT NULL
  778. ALTER TABLE `$table_vacation` CHANGE `body` `body` TEXT {UTF-8} NOT NULL
  779. ALTER TABLE `$table_vacation` CHANGE `cache` `cache` TEXT {LATIN1} NOT NULL
  780. ALTER TABLE `$table_vacation` CHANGE `domain` `domain` VARCHAR( 255 ) {LATIN1} NOT NULL
  781. ALTER TABLE `$table_vacation` CHANGE `active` `active` TINYINT( 1 ) NOT NULL DEFAULT '1'
  782. ALTER TABLE `$table_vacation` DEFAULT {LATIN1}
  783. ALTER TABLE `$table_vacation` {INNODB}
  784. "));
  785. foreach ($all_sql as $sql) {
  786. $result = db_query_parsed($sql, TRUE);
  787. }
  788. }
  789. /**
  790. * Make logging translatable - i.e. create alias => create_alias
  791. */
  792. function upgrade_90_mysql_pgsql() {
  793. $result = db_query_parsed("UPDATE " . table_by_key ('log') . " SET action = REPLACE(action,' ','_')", TRUE);
  794. # change edit_alias_state to edit_alias_active
  795. $result = db_query_parsed("UPDATE " . table_by_key ('log') . " SET action = 'edit_alias_state' WHERE action = 'edit_alias_active'", TRUE);
  796. }
  797. /**
  798. * MySQL only allow quota > 2 GB
  799. */
  800. function upgrade_169_mysql() {
  801. $table_domain = table_by_key ('domain');
  802. $table_mailbox = table_by_key ('mailbox');
  803. $result = db_query_parsed("ALTER TABLE $table_domain MODIFY COLUMN `quota` bigint(20) NOT NULL default '0'", TRUE);
  804. $result = db_query_parsed("ALTER TABLE $table_domain MODIFY COLUMN `maxquota` bigint(20) NOT NULL default '0'", TRUE);
  805. $result = db_query_parsed("ALTER TABLE $table_mailbox MODIFY COLUMN `quota` bigint(20) NOT NULL default '0'", TRUE);
  806. }
  807. /**
  808. * Create / modify vacation_notification table.
  809. * Note: This might not work if users used workarounds to create the table before.
  810. * In this case, dropping the table is the easiest solution.
  811. */
  812. function upgrade_318_mysql() {
  813. $table_vacation_notification = table_by_key('vacation_notification');
  814. $table_vacation = table_by_key('vacation');
  815. db_query_parsed( "
  816. CREATE TABLE {IF_NOT_EXISTS} $table_vacation_notification (
  817. on_vacation varchar(255) {LATIN1} NOT NULL,
  818. notified varchar(255) NOT NULL,
  819. notified_at timestamp NOT NULL default CURRENT_TIMESTAMP,
  820. PRIMARY KEY on_vacation (`on_vacation`, `notified`),
  821. CONSTRAINT `vacation_notification_pkey`
  822. FOREIGN KEY (`on_vacation`) REFERENCES $table_vacation(`email`) ON DELETE CASCADE
  823. )
  824. {INNODB}
  825. COMMENT='Postfix Admin - Virtual Vacation Notifications'
  826. ");
  827. # in case someone has manually created the table with utf8 fields before:
  828. $all_sql = explode("\n", trim("
  829. ALTER TABLE `$table_vacation_notification` CHANGE `notified` `notified` VARCHAR( 255 ) NOT NULL
  830. ALTER TABLE `$table_vacation_notification` DEFAULT CHARACTER SET utf8
  831. "));
  832. # Possible errors that can be ignored:
  833. # None.
  834. # If something goes wrong, the user should drop the vacation_notification table
  835. # (not a great loss) and re-create it using this function.
  836. foreach ($all_sql as $sql) {
  837. $result = db_query_parsed($sql);
  838. }
  839. }
  840. /**
  841. * Create fetchmail table
  842. */
  843. function upgrade_344_mysql() {
  844. $table_fetchmail = table_by_key('fetchmail');
  845. db_query_parsed( "
  846. CREATE TABLE IF NOT EXISTS $table_fetchmail(
  847. id int(11) unsigned not null auto_increment,
  848. mailbox varchar(255) not null default '',
  849. src_server varchar(255) not null default '',
  850. src_auth enum('password','kerberos_v5','kerberos','kerberos_v4','gssapi','cram-md5','otp','ntlm','msn','ssh','any'),
  851. src_user varchar(255) not null default '',
  852. src_password varchar(255) not null default '',
  853. src_folder varchar(255) not null default '',
  854. poll_time int(11) unsigned not null default 10,
  855. fetchall tinyint(1) unsigned not null default 0,
  856. keep tinyint(1) unsigned not null default 0,
  857. protocol enum('POP3','IMAP','POP2','ETRN','AUTO'),
  858. extra_options text,
  859. returned_text text,
  860. mda varchar(255) not null default '',
  861. date timestamp,
  862. primary key(id)
  863. );
  864. ");
  865. }
  866. function upgrade_344_pgsql() {
  867. $fetchmail = table_by_key('fetchmail');
  868. // a field name called 'date' is probably a bad idea.
  869. if(!_pgsql_object_exists('fetchmail')) {
  870. db_query_parsed( "
  871. create table $fetchmail(
  872. id serial,
  873. mailbox varchar(255) not null default '',
  874. src_server varchar(255) not null default '',
  875. src_auth varchar(15) NOT NULL,
  876. src_user varchar(255) not null default '',
  877. src_password varchar(255) not null default '',
  878. src_folder varchar(255) not null default '',
  879. poll_time integer not null default 10,
  880. fetchall boolean not null default false,
  881. keep boolean not null default false,
  882. protocol varchar(15) NOT NULL,
  883. extra_options text,
  884. returned_text text,
  885. mda varchar(255) not null default '',
  886. date timestamp with time zone default now(),
  887. primary key(id),
  888. CHECK (src_auth IN ('password','kerberos_v5','kerberos','kerberos_v4','gssapi','cram-md5','otp','ntlm','msn','ssh','any')),
  889. CHECK (protocol IN ('POP3', 'IMAP', 'POP2', 'ETRN', 'AUTO'))
  890. );
  891. ");
  892. }
  893. // MySQL expects sequences to start at 1. Stupid database.
  894. // fetchmail.php requires id parameters to be > 0, as it does if($id) like logic... hence if we don't
  895. // fudge the sequence starting point, you cannot delete/edit the first entry if using PostgreSQL.
  896. // I'm sure there's a more elegant way of fixing it properly.... but this should work for now.
  897. if(_pgsql_object_exists('fetchmail_id_seq')) {
  898. db_query_parsed("SELECT nextval('{$fetchmail}_id_seq')"); // I don't care about number waste.
  899. }
  900. }
  901. /**
  902. * Create alias_domain table - MySQL
  903. */
  904. # function upgrade_362_mysql() # renamed to _438 to make sure it runs after an upgrade from 2.2.x
  905. function upgrade_438_mysql() {
  906. # Table structure for table alias_domain
  907. #
  908. $table_alias_domain = table_by_key('alias_domain');
  909. db_query_parsed("
  910. CREATE TABLE IF NOT EXISTS $table_alias_domain (
  911. `alias_domain` varchar(255) NOT NULL default '',
  912. `target_domain` varchar(255) NOT NULL default '',
  913. `created` {DATETIME},
  914. `modified` {DATETIME},
  915. `active` tinyint(1) NOT NULL default '1',
  916. PRIMARY KEY (`alias_domain`),
  917. KEY `active` (`active`),
  918. KEY `target_domain` (`target_domain`)
  919. ) {MYISAM} COMMENT='Postfix Admin - Domain Aliases'
  920. ");
  921. }
  922. /**
  923. * Create alias_domain table - PgSQL
  924. */
  925. # function upgrade_362_pgsql() # renamed to _438 to make sure it runs after an upgrade from 2.2.x
  926. function upgrade_438_pgsql() {
  927. # Table structure for table alias_domain
  928. $table_alias_domain = table_by_key('alias_domain');
  929. $table_domain = table_by_key('domain');
  930. if(_pgsql_object_exists($table_alias_domain)) {
  931. return;
  932. }
  933. db_query_parsed("
  934. CREATE TABLE $table_alias_domain (
  935. alias_domain character varying(255) NOT NULL REFERENCES $table_domain(domain) ON DELETE CASCADE,
  936. target_domain character varying(255) NOT NULL REFERENCES $table_domain(domain) ON DELETE CASCADE,
  937. created timestamp with time zone default now(),
  938. modified timestamp with time zone default now(),
  939. active boolean NOT NULL default true,
  940. PRIMARY KEY(alias_domain))");
  941. db_query_parsed("CREATE INDEX alias_domain_active ON $table_alias_domain(alias_domain,active)");
  942. db_query_parsed("COMMENT ON TABLE $table_alias_domain IS 'Postfix Admin - Domain Aliases'");
  943. }
  944. /**
  945. * Change description fields to UTF-8
  946. */
  947. function upgrade_373_mysql() { # MySQL only
  948. $table_domain = table_by_key ('domain');
  949. $table_mailbox = table_by_key('mailbox');
  950. $all_sql = explode("\n", trim("
  951. ALTER TABLE `$table_domain` CHANGE `description` `description` VARCHAR( 255 ) {UTF-8} NOT NULL
  952. ALTER TABLE `$table_mailbox` CHANGE `name` `name` VARCHAR( 255 ) {UTF-8} NOT NULL
  953. "));
  954. foreach ($all_sql as $sql) {
  955. $result = db_query_parsed($sql);
  956. }
  957. }
  958. /**
  959. * add ssl option for fetchmail
  960. */
  961. function upgrade_439_mysql() {
  962. $table_fetchmail = table_by_key('fetchmail');
  963. if(!_mysql_field_exists($table_fetchmail, 'ssl')) {
  964. db_query_parsed("ALTER TABLE `$table_fetchmail` ADD `ssl` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0' AFTER `protocol` ; ");
  965. }
  966. }
  967. function upgrade_439_pgsql() {
  968. $table_fetchmail = table_by_key('fetchmail');
  969. if(!_pgsql_field_exists($table_fetchmail, 'ssl')) {
  970. db_query_parsed("ALTER TABLE $table_fetchmail ADD COLUMN ssl BOOLEAN NOT NULL DEFAULT false");
  971. }
  972. }
  973. function upgrade_473_mysql() {
  974. $table_admin = table_by_key('admin');
  975. $table_alias = table_by_key('alias');
  976. $table_al_dom = table_by_key('alias_domain');
  977. $table_domain = table_by_key('domain');
  978. $table_dom_adm = table_by_key('domain_admins');
  979. $table_fmail = table_by_key('fetchmail');
  980. $table_mailbox = table_by_key('mailbox');
  981. $table_log = table_by_key('log');
  982. # tables were created without explicit charset before :-(
  983. $all_sql = explode("\n", trim("
  984. ALTER TABLE `$table_admin` CHANGE `username` `username` VARCHAR( 255 ) {LATIN1} NOT NULL
  985. ALTER TABLE `$table_admin` CHANGE `password` `password` VARCHAR( 255 ) {LATIN1} NOT NULL
  986. ALTER TABLE `$table_admin` DEFAULT {LATIN1}
  987. ALTER TABLE `$table_alias` CHANGE `address` `address` VARCHAR( 255 ) {LATIN1} NOT NULL
  988. ALTER TABLE `$table_alias` CHANGE `goto` `goto` TEXT {LATIN1} NOT NULL
  989. ALTER TABLE `$table_alias` CHANGE `domain` `domain` VARCHAR( 255 ) {LATIN1} NOT NULL
  990. ALTER TABLE `$table_alias` DEFAULT {LATIN1}
  991. ALTER TABLE `$table_al_dom` CHANGE `alias_domain` `alias_domain` VARCHAR( 255 ) {LATIN1} NOT NULL
  992. ALTER TABLE `$table_al_dom` CHANGE `target_domain` `target_domain` VARCHAR( 255 ) {LATIN1} NOT NULL
  993. ALTER TABLE `$table_al_dom` DEFAULT {LATIN1}
  994. ALTER TABLE `$table_domain` CHANGE `domain` `domain` VARCHAR( 255 ) {LATIN1} NOT NULL
  995. ALTER TABLE `$table_domain` CHANGE `transport` `transport` VARCHAR( 255 ) {LATIN1} NOT NULL
  996. ALTER TABLE `$table_domain` DEFAULT {LATIN1}
  997. ALTER TABLE `$table_dom_adm` CHANGE `username` `username` VARCHAR( 255 ) {LATIN1} NOT NULL
  998. ALTER TABLE `$table_dom_adm` CHANGE `domain` `domain` VARCHAR( 255 ) {LATIN1} NOT NULL
  999. ALTER TABLE `$table_dom_adm` DEFAULT {LATIN1}
  1000. ALTER TABLE `$table_log` CHANGE `username` `username` VARCHAR( 255 ) {LATIN1} NOT NULL
  1001. ALTER TABLE `$table_log` CHANGE `domain` `domain` VARCHAR( 255 ) {LATIN1} NOT NULL
  1002. ALTER TABLE `$table_log` CHANGE `action` `action` VARCHAR( 255 ) {LATIN1} NOT NULL
  1003. ALTER TABLE `$table_log` CHANGE `data` `data` VARCHAR( 255 ) {LATIN1} NOT NULL
  1004. ALTER TABLE `$table_log` DEFAULT {LATIN1}
  1005. ALTER TABLE `$table_mailbox` CHANGE `username` `username` VARCHAR( 255 ) {LATIN1} NOT NULL
  1006. ALTER TABLE `$table_mailbox` CHANGE `password` `password` VARCHAR( 255 ) {LATIN1} NOT NULL
  1007. ALTER TABLE `$table_mailbox` CHANGE `maildir` `maildir` VARCHAR( 255 ) {LATIN1} NOT NULL
  1008. ALTER TABLE `$table_mailbox` CHANGE `domain` `domain` VARCHAR( 255 ) {LATIN1} NOT NULL
  1009. ALTER TABLE `$table_mailbox` DEFAULT {LATIN1}
  1010. ALTER TABLE `$table_fmail` CHANGE `mailbox` `mailbox` VARCHAR( 255 ) {LATIN1} NOT NULL
  1011. ALTER TABLE `$table_fmail` CHANGE `src_server` `src_server` VARCHAR( 255 ) {LATIN1} NOT NULL
  1012. ALTER TABLE `$table_fmail` CHANGE `src_user` `src_user` VARCHAR( 255 ) {LATIN1} NOT NULL
  1013. ALTER TABLE `$table_fmail` CHANGE `src_password` `src_password` VARCHAR( 255 ) {LATIN1} NOT NULL
  1014. ALTER TABLE `$table_fmail` CHANGE `src_folder` `src_folder` VARCHAR( 255 ) {LATIN1} NOT NULL
  1015. ALTER TABLE `$table_fmail` CHANGE `mda` `mda` VARCHAR( 255 ) {LATIN1} NOT NULL
  1016. ALTER TABLE `$table_fmail` CHANGE `mailbox` `mailbox` VARCHAR( 255 ) {LATIN1} NOT NULL
  1017. ALTER TABLE `$table_fmail` CHANGE `extra_options` `extra_options` TEXT {LATIN1} NULL DEFAULT NULL
  1018. ALTER TABLE `$table_fmail` CHANGE `returned_text` `returned_text` TEXT {LATIN1} NULL DEFAULT NULL
  1019. ALTER TABLE `$table_fmail` DEFAULT {LATIN1}
  1020. "));
  1021. foreach ($all_sql as $sql) {
  1022. $result = db_query_parsed($sql);
  1023. }
  1024. }
  1025. function upgrade_479_mysql () {
  1026. # ssl is a reserved word in MySQL and causes several problems. Renaming the field...
  1027. $table_fmail = table_by_key('fetchmail');
  1028. if(!_mysql_field_exists($table_fmail, 'usessl')) {
  1029. db_query_parsed("ALTER TABLE `$table_fmail` CHANGE `ssl` `usessl` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0'");
  1030. }
  1031. }
  1032. function upgrade_479_pgsql () {
  1033. $table_fmail = table_by_key('fetchmail');
  1034. if(!_pgsql_field_exists($table_fmail, 'usessl')) {
  1035. db_query_parsed("alter table $table_fmail rename column ssl to usessl");
  1036. }
  1037. }
  1038. function upgrade_483_mysql () {
  1039. $table_log = table_by_key('log');
  1040. db_query_parsed("ALTER TABLE $table_log CHANGE `data` `data` TEXT {LATIN1} NOT NULL");
  1041. }
  1042. # Add a local_part field to the mailbox table, and populate it with the local part of the user's address.
  1043. # This is to make it easier (hopefully) to change the filesystem location of a mailbox in the future
  1044. # See https://sourceforge.net/forum/message.php?msg_id=5394663
  1045. function upgrade_495_pgsql() {
  1046. $table_mailbox = table_by_key('mailbox');
  1047. if(!_pgsql_field_exists($table_mailbox, 'local_part')) {
  1048. db_query_parsed("ALTER TABLE $table_mailbox add column local_part varchar(255) ");
  1049. db_query_parsed("UPDATE $table_mailbox SET local_part = substring(username from '^(.*)@')");
  1050. db_query_parsed("ALTER TABLE $table_mailbox alter column local_part SET NOT NULL");
  1051. }
  1052. }
  1053. # See https://sourceforge.net/forum/message.php?msg_id=5394663
  1054. function upgrade_495_mysql() {
  1055. $table_mailbox = table_by_key('mailbox');
  1056. if(!_mysql_field_exists($table_mailbox, 'local_part')) {
  1057. db_query_parsed("ALTER TABLE $table_mailbox add local_part varchar(255) AFTER quota"); // allow to be null
  1058. db_query_parsed("UPDATE $table_mailbox SET local_part = substring_index(username, '@', 1)");
  1059. db_query_parsed("ALTER TABLE $table_mailbox change local_part local_part varchar(255) NOT NULL"); // remove null-ness...
  1060. }
  1061. }
  1062. function upgrade_504_mysql() {
  1063. $table_mailbox = table_by_key('mailbox');
  1064. db_query_parsed("ALTER TABLE `$table_mailbox` CHANGE `local_part` `local_part` VARCHAR( 255 ) {LATIN1} NOT NULL");
  1065. }
  1066. function upgrade_655_mysql_pgsql() {
  1067. db_query_parsed(_add_index('mailbox', 'domain', 'domain'));
  1068. db_query_parsed(_add_index('alias', 'domain', 'domain'));
  1069. }
  1070. /*
  1071. function number too small for upgrades from 2.3.x
  1072. -> adding activefrom and activeuntil to vacation table is now upgrade_964
  1073. -> the tables client_access, from_access, helo_access, rcpt_access, user_whitelist
  1074. are not used by PostfixAdmin - no replacement function needed
  1075. 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.
  1076. Note: there never was a function upgrade_727_pgsql()
  1077. function upgrade_727_mysql() {
  1078. $table_vacation = table_by_key('vacation');
  1079. if(!_mysql_field_exists($table_vacation, 'activefrom')) {
  1080. db_query_parsed("ALTER TABLE $table_vacation add activefrom datetime default NULL");
  1081. }
  1082. if(!_mysql_field_exists($table_vacation, 'activeuntil')) {
  1083. db_query_parsed("ALTER TABLE $table_vacation add activeuntil datetime default NULL");
  1084. }
  1085. # the following tables are not used by postfixadmin
  1086. $table_client_access = table_by_key('client_access');
  1087. db_query_parsed("
  1088. CREATE TABLE IF NOT EXISTS $table_client_access (
  1089. `client` char(50) NOT NULL,
  1090. `action` char(50) NOT NULL default 'REJECT',
  1091. UNIQUE KEY `client` (`client`)
  1092. ) {MYISAM} COMMENT='Postfix Admin - Client Access'
  1093. ");
  1094. $table_from_access = table_by_key('from_access');
  1095. db_query_parsed("
  1096. CREATE TABLE IF NOT EXISTS $table_from_access (
  1097. `from_access` char(50) NOT NULL,
  1098. `action` char(50) NOT NULL default 'REJECT',
  1099. UNIQUE KEY `from_access` (`from_access`)
  1100. ) {MYISAM} COMMENT='Postfix Admin - From Access'
  1101. ");
  1102. $table_helo_access = table_by_key('helo_access');
  1103. db_query_parsed("
  1104. CREATE TABLE IF NOT EXISTS $table_helo_access (
  1105. `helo` char(50) NOT NULL,
  1106. `action` char(50) NOT NULL default 'REJECT',
  1107. UNIQUE KEY `helo` (`helo`)
  1108. ) {MYISAM} COMMENT='Postfix Admin - Helo Access'
  1109. ");
  1110. $table_rcpt_access = table_by_key('rcpt_access');
  1111. db_query_parsed("
  1112. CREATE TABLE IF NOT EXISTS $table_rcpt_access (
  1113. `rcpt` char(50) NOT NULL,
  1114. `action` char(50) NOT NULL default 'REJECT',
  1115. UNIQUE KEY `rcpt` (`rcpt`)
  1116. ) {MYISAM} COMMENT='Postfix Admin - Recipient Access'
  1117. ");
  1118. $table_user_whitelist = table_by_key('user_whitelist');
  1119. db_query_parsed("
  1120. CREATE TABLE IF NOT EXISTS $table_user_whitelist (
  1121. `recipient` char(50) NOT NULL,
  1122. `action` char(50) NOT NULL default 'REJECT',
  1123. UNIQUE KEY `recipient` (`recipient`)
  1124. ) {MYISAM} COMMENT='Postfix Admin - User whitelist'
  1125. ");
  1126. }
  1127. */
  1128. function upgrade_729_mysql_pgsql() {
  1129. $table_quota = table_by_key('quota');
  1130. $table_quota2 = table_by_key('quota2');
  1131. # table for dovecot v1.0 & 1.1
  1132. # note: quota table created with old versions of upgrade.php (before r1605)
  1133. # will not have explicit "NOT NULL DEFAULT 0" for the "current" field
  1134. # (shouldn't hurt)
  1135. db_query_parsed("
  1136. CREATE TABLE {IF_NOT_EXISTS} $table_quota (
  1137. username VARCHAR(255) {LATIN1} NOT NULL,
  1138. path VARCHAR(100) {LATIN1} NOT NULL,
  1139. current {BIGINT},
  1140. PRIMARY KEY (username, path)
  1141. ) {MYISAM} ;
  1142. ");
  1143. # table for dovecot >= 1.2
  1144. db_query_parsed("
  1145. CREATE TABLE {IF_NOT_EXISTS} $table_quota2 (
  1146. username VARCHAR(100) {LATIN1} NOT NULL,
  1147. bytes {BIGINT},
  1148. messages integer NOT NULL DEFAULT 0,
  1149. PRIMARY KEY (username)
  1150. ) {MYISAM} ;
  1151. ");
  1152. }
  1153. function upgrade_730_pgsql() {
  1154. $table_quota = table_by_key('quota');
  1155. $table_quota2 = table_by_key('quota2');
  1156. db_query_parsed('CREATE LANGUAGE plpgsql', 1); /* will error if plpgsql is already installed */
  1157. # trigger for dovecot v1.0 & 1.1 quota table
  1158. # taken from http://wiki.dovecot.org/Quota/Dict
  1159. db_query_parsed("
  1160. CREATE OR REPLACE FUNCTION merge_quota() RETURNS TRIGGER AS \$merge_quota\$
  1161. BEGIN
  1162. UPDATE $table_quota SET current = NEW.current + current WHERE username = NEW.username AND path = NEW.path;
  1163. IF found THEN
  1164. RETURN NULL;
  1165. ELSE
  1166. RETURN NEW;
  1167. END IF;
  1168. END;
  1169. \$merge_quota\$ LANGUAGE plpgsql;
  1170. ");
  1171. db_query_parsed("
  1172. CREATE TRIGGER mergequota BEFORE INSERT ON $table_quota FOR EACH ROW EXECUTE PROCEDURE merge_quota();
  1173. ");
  1174. # trigger for dovecot >= 1.2 quota table
  1175. # taken from http://wiki.dovecot.org/Quota/Dict, table/trigger name changed to quota2 naming
  1176. db_query_parsed("
  1177. CREATE OR REPLACE FUNCTION merge_quota2() RETURNS TRIGGER AS \$\$
  1178. BEGIN
  1179. IF NEW.messages < 0 OR NEW.messages IS NULL THEN
  1180. -- ugly kludge: we came here from this function, really do try to insert
  1181. IF NEW.messages IS NULL THEN
  1182. NEW.messages = 0;
  1183. ELSE
  1184. NEW.messages = -NEW.messages;
  1185. END IF;
  1186. return NEW;
  1187. END IF;
  1188. LOOP
  1189. UPDATE $table_quota2 SET bytes = bytes + NEW.bytes,
  1190. messages = messages + NEW.messages
  1191. WHERE username = NEW.username;
  1192. IF found THEN
  1193. RETURN NULL;
  1194. END IF;
  1195. BEGIN
  1196. IF NEW.messages = 0 THEN
  1197. INSERT INTO $table_quota2 (bytes, messages, username) VALUES (NEW.bytes, NULL, NEW.username);
  1198. ELSE
  1199. INSERT INTO $table_quota2 (bytes, messages, username) VALUES (NEW.bytes, -NEW.messages, NEW.username);
  1200. END IF;
  1201. return NULL;
  1202. EXCEPTION WHEN unique_violation THEN
  1203. -- someone just inserted the record, update it
  1204. END;
  1205. END LOOP;
  1206. END;
  1207. \$\$ LANGUAGE plpgsql;
  1208. ");
  1209. db_query_parsed("
  1210. CREATE TRIGGER mergequota2 BEFORE INSERT ON $table_quota2
  1211. FOR EACH ROW EXECUTE PROCEDURE merge_quota2();
  1212. ");
  1213. }
  1214. function upgrade_945_mysql_pgsql() {
  1215. _db_add_field('vacation', 'modified', '{DATECURRENT}', 'created');
  1216. }
  1217. function upgrade_946_mysql_pgsql() {
  1218. # taken from upgrade_727_mysql, needs to be done for all databases
  1219. _db_add_field('vacation', 'activefrom', '{DATE}', 'body');
  1220. _db_add_field('vacation', 'activeuntil', '{DATE}', 'activefrom');
  1221. }
  1222. function upgrade_968_pgsql() {
  1223. # pgsql counterpart for upgrade_169_mysql() - allow really big quota
  1224. $table_domain = table_by_key ('domain');
  1225. $table_mailbox = table_by_key('mailbox');
  1226. db_query_parsed("ALTER TABLE $table_domain ALTER COLUMN quota type bigint");
  1227. db_query_parsed("ALTER TABLE $table_domain ALTER COLUMN maxquota type bigint");
  1228. db_query_parsed("ALTER TABLE $table_mailbox ALTER COLUMN quota type bigint");
  1229. }
  1230. function upgrade_1050_mysql_pgsql() {
  1231. db_query_parsed(_add_index('log', 'domain_timestamp', 'domain,timestamp'));
  1232. }
  1233. function upgrade_1283_mysql_pgsql() {
  1234. _db_add_field('admin', 'superadmin', '{BOOLEAN}', 'password');
  1235. }
  1236. function upgrade_1284_mysql_pgsql() {
  1237. # migrate the ALL domain to the superadmin column
  1238. # Note: The ALL domain is not (yet) deleted to stay backwards-compatible for now (will be done in a later upgrade function)
  1239. $result = db_query("SELECT username FROM " . table_by_key('domain_admins') . " where domain='ALL'");
  1240. if ($result['rows'] > 0) {
  1241. while ($row = db_array ($result['result'])) {
  1242. printdebug ("Setting superadmin flag for " . $row['username']);
  1243. db_update('admin', 'username', $row['username'], array('superadmin' => db_get_boolean(true)) );
  1244. }
  1245. }
  1246. }
  1247. function upgrade_1345_mysql() {
  1248. # $table_vacation = table_by_key('vacation');
  1249. # adding and usage of reply_type field removed in r1610
  1250. # db_query_parsed("ALTER TABLE `$table_vacation` ADD `reply_type` VARCHAR( 20 ) NOT NULL AFTER `domain` ");
  1251. # obsoleted by upgrade_1610()
  1252. # db_query_parsed("ALTER TABLE `$table_vacation` ADD `interval_time` INT NOT NULL DEFAULT '0' AFTER `reply_type` ");
  1253. }
  1254. function upgrade_1519_mysql_pgsql() {
  1255. _db_add_field('fetchmail', 'sslcertck', '{BOOLEAN}', 'usessl' );
  1256. _db_add_field('fetchmail', 'sslcertpath', "VARCHAR(255) {UTF-8} DEFAULT ''", 'sslcertck' );
  1257. _db_add_field('fetchmail', 'sslfingerprint', "VARCHAR(255) {LATIN1} DEFAULT ''", 'sslcertpath');
  1258. }
  1259. function upgrade_1610_mysql_pgsql() {
  1260. # obsoletes upgrade_1345_mysql() - which means debug mode could print "field already exists"
  1261. _db_add_field('vacation', 'interval_time', '{INT}', 'domain');
  1262. }
  1263. function upgrade_1685_mysql() {
  1264. # Fix existing log entries broken by https://sourceforge.net/p/postfixadmin/bugs/317/
  1265. $table = table_by_key('log');
  1266. db_query_parsed("UPDATE $table SET data = domain WHERE data = '' AND domain LIKE '%@%'");
  1267. db_query_parsed("UPDATE $table SET domain=SUBSTRING_INDEX(domain, '@', -1) WHERE domain=data;");
  1268. }
  1269. function upgrade_1685_pgsql() {
  1270. $table = table_by_key('log');
  1271. db_query_parsed("UPDATE $table SET data = domain WHERE data = '' AND domain LIKE '%@%'");
  1272. db_query_parsed("UPDATE $table SET domain=SPLIT_PART(domain, '@', 2) WHERE domain=data;");
  1273. }
  1274. function upgrade_1761_mysql() {
  1275. # upgrade_1762 adds the 'modified' column as {DATECURRENT}, therefore we first need to change
  1276. # 'date' to {DATE} (mysql only allows one {DATECURRENT} column per table)
  1277. $table_fetchmail = table_by_key('fetchmail');
  1278. db_query_parsed("ALTER TABLE `$table_fetchmail` CHANGE `date` `date` {DATE}");
  1279. }
  1280. function upgrade_1762_mysql_pgsql() {
  1281. _db_add_field('fetchmail', 'domain', "VARCHAR(255) {LATIN1} DEFAULT ''", 'id');
  1282. _db_add_field('fetchmail', 'active', '{BOOLEAN}', 'date');
  1283. _db_add_field('fetchmail', 'created', '{DATE}', 'date');
  1284. # If you followed SVN and got upgrade failures here, you might need to
  1285. # UPDATE config SET value=1760 WHERE name='version';
  1286. # and run setup.php again (upgrade_1761_mysql was added later).
  1287. _db_add_field('fetchmail', 'modified', '{DATECURRENT}', 'created');
  1288. }
  1289. function upgrade_1763_mysql() {
  1290. $table = table_by_key('fetchmail');
  1291. db_query_parsed("UPDATE $table SET domain=SUBSTRING_INDEX(mailbox, '@', -1) WHERE domain='';");
  1292. }
  1293. function upgrade_1763_pgsql() {
  1294. $table = table_by_key('fetchmail');
  1295. db_query_parsed("UPDATE $table SET domain=SPLIT_PART(mailbox, '@', 2) WHERE domain='';");
  1296. }
  1297. function upgrade_1767_mysql_pgsql() {
  1298. # 'active' was just added, so make sure all existing jobs stay active
  1299. $table = table_by_key('fetchmail');
  1300. db_query_parsed("UPDATE $table SET active='{BOOL_TRUE}'");
  1301. }
  1302. function upgrade_1795_mysql() {
  1303. # upgrade_1761_mysql() was added later (in r1795) - make sure it runs for everybody
  1304. # (running it twice doesn't hurt)
  1305. upgrade_1761_mysql();
  1306. }
  1307. function upgrade_1824_sqlite() {
  1308. $admin_table = table_by_key('admin');
  1309. $alias_table = table_by_key('alias');
  1310. $alias_domain_table = table_by_key('alias_domain');
  1311. $domain_table = table_by_key('domain');
  1312. $domain_admins_table = table_by_key('domain_admins');
  1313. $fetchmail_table = table_by_key('fetchmail');
  1314. $log_table = table_by_key('log');
  1315. $mailbox_table = table_by_key('mailbox');
  1316. $quota_table = table_by_key('quota');
  1317. $quota2_table = table_by_key('quota2');
  1318. $vacation_table = table_by_key('vacation');
  1319. $vacation_notification_table = table_by_key('vacation_notification');
  1320. db_query_parsed("
  1321. CREATE TABLE $admin_table (
  1322. `username` varchar(255) NOT NULL,
  1323. `password` varchar(255) NOT NULL,
  1324. `superadmin` {BOOLEAN},
  1325. `created` {DATE},
  1326. `modified` {DATE},
  1327. `active` {BOOLEAN_TRUE},
  1328. {PRIMARY} (`username`));
  1329. ");
  1330. db_query_parsed("
  1331. CREATE TABLE $alias_table (
  1332. `address` varchar(255) NOT NULL,
  1333. `goto` {FULLTEXT} NOT NULL,
  1334. `domain` varchar(255) NOT NULL,
  1335. `created` {DATE},
  1336. `modified` {DATE},
  1337. `active` {BOOLEAN_TRUE},
  1338. {PRIMARY} (`address`));
  1339. ");
  1340. db_query_parsed("
  1341. CREATE TABLE $alias_domain_table (
  1342. `alias_domain` varchar(255) NOT NULL,
  1343. `target_domain` varchar(255) NOT NULL,
  1344. `created` {DATE},
  1345. `modified` {DATE},
  1346. `active` {BOOLEAN_TRUE},
  1347. {PRIMARY} (`alias_domain`));
  1348. ");
  1349. db_query_parsed("
  1350. CREATE TABLE $domain_table (
  1351. `domain` varchar(255) NOT NULL,
  1352. `description` varchar(255) NOT NULL,
  1353. `aliases` {INT},
  1354. `mailboxes` {INT},
  1355. `maxquota` {BIGINT},
  1356. `quota` {BIGINT},
  1357. `transport` varchar(255) NOT NULL,
  1358. `backupmx` {BOOLEAN},
  1359. `created` {DATE},
  1360. `modified` {DATE},
  1361. `active` {BOOLEAN_TRUE},
  1362. {PRIMARY} (`domain`));
  1363. ");
  1364. db_query_parsed("
  1365. CREATE TABLE $domain_admins_table (
  1366. `username` varchar(255) NOT NULL,
  1367. `domain` varchar(255) NOT NULL,
  1368. `created` {DATE},
  1369. `active` {BOOLEAN_TRUE});
  1370. ");
  1371. db_query_parsed("
  1372. CREATE TABLE $fetchmail_table (
  1373. `id` {AUTOINCREMENT},
  1374. `domain` varchar(255) DEFAULT '',
  1375. `mailbox` varchar(255) NOT NULL,
  1376. `src_server` varchar(255) NOT NULL,
  1377. `src_auth` varchar(255) DEFAULT NULL,
  1378. `src_user` varchar(255) NOT NULL,
  1379. `src_password` varchar(255) NOT NULL,
  1380. `src_folder` varchar(255) NOT NULL,
  1381. `poll_time` int(11) NOT NULL DEFAULT '10',
  1382. `fetchall` {BOOLEAN},
  1383. `keep` {BOOLEAN},
  1384. `protocol` {FULLTEXT} DEFAULT NULL,
  1385. `usessl` {BOOLEAN},
  1386. `sslcertck` {BOOLEAN},
  1387. `sslcertpath` varchar(255) DEFAULT '',
  1388. `sslfingerprint` varchar(255) DEFAULT '',
  1389. `extra_options` {FULLTEXT},
  1390. `returned_text` {FULLTEXT},
  1391. `mda` varchar(255) NOT NULL,
  1392. `date` {DATE},
  1393. `created` {DATE},
  1394. `modified` {DATECURRENT},
  1395. `active` {BOOLEAN});
  1396. ");
  1397. db_query_parsed("
  1398. CREATE TABLE $log_table (
  1399. `timestamp` {DATE},
  1400. `username` varchar(255) NOT NULL,
  1401. `domain` varchar(255) NOT NULL,
  1402. `action` varchar(255) NOT NULL,
  1403. `data` {FULLTEXT} NOT NULL);
  1404. ");
  1405. db_query_parsed("
  1406. CREATE TABLE $mailbox_table (
  1407. `username` varchar(255) NOT NULL,
  1408. `password` varchar(255) NOT NULL,
  1409. `name` varchar(255) NOT NULL,
  1410. `maildir` varchar(255) NOT NULL,
  1411. `quota` {BIGINT},
  1412. `local_part` varchar(255) NOT NULL,
  1413. `domain` varchar(255) NOT NULL,
  1414. `created` {DATE},
  1415. `modified` {DATE},
  1416. `active` {BOOLEAN_TRUE},
  1417. {PRIMARY} (`username`));
  1418. ");
  1419. db_query_parsed("
  1420. CREATE TABLE $quota_table (
  1421. `username` varchar(255) NOT NULL,
  1422. `path` varchar(100) NOT NULL,
  1423. `current` {BIGINT},
  1424. {PRIMARY} (`username`,`path`));
  1425. ");
  1426. db_query_parsed("
  1427. CREATE TABLE $quota2_table (
  1428. `username` varchar(255) NOT NULL,
  1429. `bytes` {BIGINT},
  1430. `messages` {INT},
  1431. {PRIMARY} (`username`));
  1432. ");
  1433. db_query_parsed("
  1434. CREATE TABLE $vacation_table (
  1435. `email` varchar(255) NOT NULL,
  1436. `subject` varchar(255) NOT NULL,
  1437. `body` {FULLTEXT} NOT NULL,
  1438. `activefrom` {DATE},
  1439. `activeuntil` {DATE},
  1440. `cache` {FULLTEXT} NOT NULL DEFAULT '',
  1441. `domain` varchar(255) NOT NULL,
  1442. `interval_time` {INT},
  1443. `created` {DATE},
  1444. `modified` {DATECURRENT},
  1445. `active` {BOOLEAN_TRUE},
  1446. {PRIMARY} (`email`));
  1447. ");
  1448. db_query_parsed("
  1449. CREATE TABLE $vacation_notification_table (
  1450. `on_vacation` varchar(255) NOT NULL,
  1451. `notified` varchar(255) NOT NULL,
  1452. `notified_at` {DATECURRENT},
  1453. {PRIMARY} (`on_vacation`,`notified`),
  1454. CONSTRAINT `vacation_notification_pkey` FOREIGN KEY (`on_vacation`) REFERENCES `vacation` (`email`) ON DELETE CASCADE);
  1455. ");
  1456. }
  1457. function upgrade_1835_mysql() {
  1458. # change default values for existing datetime fields with a 0000-00-00 default to {DATETIME}
  1459. foreach (array('admin', 'alias', 'alias_domain', 'domain', 'mailbox', 'domain_admins', 'vacation') as $table_to_change) {
  1460. $table = table_by_key($table_to_change);
  1461. db_query_parsed("ALTER TABLE `$table` CHANGE `created` `created` {DATETIME}");
  1462. }
  1463. foreach (array('admin', 'alias', 'alias_domain', 'domain', 'mailbox') as $table_to_change) {
  1464. $table = table_by_key($table_to_change);
  1465. db_query_parsed("ALTER TABLE `$table` CHANGE `modified` `modified` {DATETIME}");
  1466. }
  1467. $table = table_by_key('log');
  1468. db_query_parsed("ALTER TABLE `$table` CHANGE `timestamp` `timestamp` {DATETIME}");
  1469. }
  1470. # TODO MySQL:
  1471. # - various varchar fields do not have a default value
  1472. # https://sourceforge.net/projects/postfixadmin/forums/forum/676076/topic/3419725
  1473. # - change default of all timestamp fields to {DATECURRENT} (CURRENT_TIMESTAMP} or {DATE}
  1474. # including vacation.activefrom/activeuntil (might have a different default as leftover from upgrade_727_mysql)
  1475. # including vacation.modified - should be {DATE}, not {DATECURRENT}
  1476. # https://sourceforge.net/tracker/?func=detail&aid=1699218&group_id=191583&atid=937964
  1477. # @todo vacation.email has 2 indizes