Gitiles
Code Review Sign In
gerrit.benkard.de / kubeia / d1f5b68b557869443f559de96824b2d2aee3b3e7 / . / mailcow / src / mailcow-dockerized / data / web / inc / init_db.inc.php
blob: e286ab553a40d1c015ba47d7bf113e1887efe1fb [file] [log] [blame]
Matthias Andreas Benkardd1f5b682023-11-18 13:18:30 +0100[diff] [blame^]1<?php
2function init_db_schema() {
3 try {
4 global $pdo;
5
6 $db_version = "14022023_1000";
7
8 $stmt = $pdo->query("SHOW TABLES LIKE 'versions'");
9 $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
10 if ($num_results != 0) {
11 $stmt = $pdo->query("SELECT `version` FROM `versions` WHERE `application` = 'db_schema'");
12 if ($stmt->fetch(PDO::FETCH_ASSOC)['version'] == $db_version) {
13 return true;
14 }
15 if (!preg_match('/y|yes/i', getenv('MASTER'))) {
16 $_SESSION['return'][] = array(
17 'type' => 'warning',
18 'log' => array(__FUNCTION__),
19 'msg' => 'Database not initialized: not running db_init on slave.'
20 );
21 return true;
22 }
23 }
24
25 $views = array(
26 "grouped_mail_aliases" => "CREATE VIEW grouped_mail_aliases (username, aliases) AS
27 SELECT goto, IFNULL(GROUP_CONCAT(address ORDER BY address SEPARATOR ' '), '') AS address FROM alias
28 WHERE address!=goto
29 AND active = '1'
30 AND sogo_visible = '1'
31 AND address NOT LIKE '@%'
32 GROUP BY goto;",
33 // START
34 // Unused at the moment - we cannot allow to show a foreign mailbox as sender address in SOGo, as SOGo does not like this
35 // We need to create delegation in SOGo AND set a sender_acl in mailcow to allow to send as user X
36 "grouped_sender_acl" => "CREATE VIEW grouped_sender_acl (username, send_as_acl) AS
37 SELECT logged_in_as, IFNULL(GROUP_CONCAT(send_as SEPARATOR ' '), '') AS send_as_acl FROM sender_acl
38 WHERE send_as NOT LIKE '@%'
39 GROUP BY logged_in_as;",
40 // END
41 "grouped_sender_acl_external" => "CREATE VIEW grouped_sender_acl_external (username, send_as_acl) AS
42 SELECT logged_in_as, IFNULL(GROUP_CONCAT(send_as SEPARATOR ' '), '') AS send_as_acl FROM sender_acl
43 WHERE send_as NOT LIKE '@%' AND external = '1'
44 GROUP BY logged_in_as;",
45 "grouped_domain_alias_address" => "CREATE VIEW grouped_domain_alias_address (username, ad_alias) AS
46 SELECT username, IFNULL(GROUP_CONCAT(local_part, '@', alias_domain SEPARATOR ' '), '') AS ad_alias FROM mailbox
47 LEFT OUTER JOIN alias_domain ON target_domain=domain
48 GROUP BY username;",
49 "sieve_before" => "CREATE VIEW sieve_before (id, username, script_name, script_data) AS
50 SELECT md5(script_data), username, script_name, script_data FROM sieve_filters
51 WHERE filter_type = 'prefilter';",
52 "sieve_after" => "CREATE VIEW sieve_after (id, username, script_name, script_data) AS
53 SELECT md5(script_data), username, script_name, script_data FROM sieve_filters
54 WHERE filter_type = 'postfilter';"
55 );
56
57 $tables = array(
58 "versions" => array(
59 "cols" => array(
60 "application" => "VARCHAR(255) NOT NULL",
61 "version" => "VARCHAR(100) NOT NULL",
62 "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
63 ),
64 "keys" => array(
65 "primary" => array(
66 "" => array("application")
67 )
68 ),
69 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
70 ),
71 "admin" => array(
72 "cols" => array(
73 "username" => "VARCHAR(255) NOT NULL",
74 "password" => "VARCHAR(255) NOT NULL",
75 "superadmin" => "TINYINT(1) NOT NULL DEFAULT '0'",
76 "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
77 "modified" => "DATETIME ON UPDATE NOW(0)",
78 "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
79 ),
80 "keys" => array(
81 "primary" => array(
82 "" => array("username")
83 )
84 ),
85 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
86 ),
87 "fido2" => array(
88 "cols" => array(
89 "username" => "VARCHAR(255) NOT NULL",
90 "friendlyName" => "VARCHAR(255)",
91 "rpId" => "VARCHAR(255) NOT NULL",
92 "credentialPublicKey" => "TEXT NOT NULL",
93 "certificateChain" => "TEXT",
94 // Can be null for format "none"
95 "certificate" => "TEXT",
96 "certificateIssuer" => "VARCHAR(255)",
97 "certificateSubject" => "VARCHAR(255)",
98 "signatureCounter" => "INT",
99 "AAGUID" => "BLOB",
100 "credentialId" => "BLOB NOT NULL",
101 "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
102 "modified" => "DATETIME ON UPDATE NOW(0)",
103 "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
104 ),
105 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
106 ),
107 "_sogo_static_view" => array(
108 "cols" => array(
109 "c_uid" => "VARCHAR(255) NOT NULL",
110 "domain" => "VARCHAR(255) NOT NULL",
111 "c_name" => "VARCHAR(255) NOT NULL",
112 "c_password" => "VARCHAR(255) NOT NULL DEFAULT ''",
113 "c_cn" => "VARCHAR(255)",
114 "mail" => "VARCHAR(255) NOT NULL",
115 // TODO -> use TEXT and check if SOGo login breaks on empty aliases
116 "aliases" => "TEXT NOT NULL",
117 "ad_aliases" => "VARCHAR(6144) NOT NULL DEFAULT ''",
118 "ext_acl" => "VARCHAR(6144) NOT NULL DEFAULT ''",
119 "kind" => "VARCHAR(100) NOT NULL DEFAULT ''",
120 "multiple_bookings" => "INT NOT NULL DEFAULT -1"
121 ),
122 "keys" => array(
123 "primary" => array(
124 "" => array("c_uid")
125 ),
126 "key" => array(
127 "domain" => array("domain")
128 )
129 ),
130 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
131 ),
132 "relayhosts" => array(
133 "cols" => array(
134 "id" => "INT NOT NULL AUTO_INCREMENT",
135 "hostname" => "VARCHAR(255) NOT NULL",
136 "username" => "VARCHAR(255) NOT NULL",
137 "password" => "VARCHAR(255) NOT NULL",
138 "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
139 ),
140 "keys" => array(
141 "primary" => array(
142 "" => array("id")
143 ),
144 "key" => array(
145 "hostname" => array("hostname")
146 )
147 ),
148 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
149 ),
150 "transports" => array(
151 "cols" => array(
152 "id" => "INT NOT NULL AUTO_INCREMENT",
153 "destination" => "VARCHAR(255) NOT NULL",
154 "nexthop" => "VARCHAR(255) NOT NULL",
155 "username" => "VARCHAR(255) NOT NULL DEFAULT ''",
156 "password" => "VARCHAR(255) NOT NULL DEFAULT ''",
157 "is_mx_based" => "TINYINT(1) NOT NULL DEFAULT '0'",
158 "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
159 ),
160 "keys" => array(
161 "primary" => array(
162 "" => array("id")
163 ),
164 "key" => array(
165 "destination" => array("destination"),
166 "nexthop" => array("nexthop"),
167 )
168 ),
169 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
170 ),
171 "alias" => array(
172 "cols" => array(
173 "id" => "INT NOT NULL AUTO_INCREMENT",
174 "address" => "VARCHAR(255) NOT NULL",
175 "goto" => "TEXT NOT NULL",
176 "domain" => "VARCHAR(255) NOT NULL",
177 "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
178 "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
179 "private_comment" => "TEXT",
180 "public_comment" => "TEXT",
181 "sogo_visible" => "TINYINT(1) NOT NULL DEFAULT '1'",
182 "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
183 ),
184 "keys" => array(
185 "primary" => array(
186 "" => array("id")
187 ),
188 "unique" => array(
189 "address" => array("address")
190 ),
191 "key" => array(
192 "domain" => array("domain")
193 )
194 ),
195 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
196 ),
197 "api" => array(
198 "cols" => array(
199 "api_key" => "VARCHAR(255) NOT NULL",
200 "allow_from" => "VARCHAR(512) NOT NULL",
201 "skip_ip_check" => "TINYINT(1) NOT NULL DEFAULT '0'",
202 "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
203 "modified" => "DATETIME ON UPDATE NOW(0)",
204 "access" => "ENUM('ro', 'rw') NOT NULL DEFAULT 'rw'",
205 "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
206 ),
207 "keys" => array(
208 "primary" => array(
209 "" => array("api_key")
210 ),
211 ),
212 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
213 ),
214 "sender_acl" => array(
215 "cols" => array(
216 "id" => "INT NOT NULL AUTO_INCREMENT",
217 "logged_in_as" => "VARCHAR(255) NOT NULL",
218 "send_as" => "VARCHAR(255) NOT NULL",
219 "external" => "TINYINT(1) NOT NULL DEFAULT '0'"
220 ),
221 "keys" => array(
222 "primary" => array(
223 "" => array("id")
224 )
225 ),
226 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
227 ),
228 "templates" => array(
229 "cols" => array(
230 "id" => "INT NOT NULL AUTO_INCREMENT",
231 "template" => "VARCHAR(255) NOT NULL",
232 "type" => "VARCHAR(255) NOT NULL",
233 "attributes" => "JSON",
234 "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
235 "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP"
236 ),
237 "keys" => array(
238 "primary" => array(
239 "" => array("id")
240 )
241 ),
242 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
243 ),
244 "domain" => array(
245 // Todo: Move some attributes to json
246 "cols" => array(
247 "domain" => "VARCHAR(255) NOT NULL",
248 "description" => "VARCHAR(255)",
249 "aliases" => "INT(10) NOT NULL DEFAULT '0'",
250 "mailboxes" => "INT(10) NOT NULL DEFAULT '0'",
251 "defquota" => "BIGINT(20) NOT NULL DEFAULT '3072'",
252 "maxquota" => "BIGINT(20) NOT NULL DEFAULT '102400'",
253 "quota" => "BIGINT(20) NOT NULL DEFAULT '102400'",
254 "relayhost" => "VARCHAR(255) NOT NULL DEFAULT '0'",
255 "backupmx" => "TINYINT(1) NOT NULL DEFAULT '0'",
256 "gal" => "TINYINT(1) NOT NULL DEFAULT '1'",
257 "relay_all_recipients" => "TINYINT(1) NOT NULL DEFAULT '0'",
258 "relay_unknown_only" => "TINYINT(1) NOT NULL DEFAULT '0'",
259 "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
260 "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
261 "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
262 ),
263 "keys" => array(
264 "primary" => array(
265 "" => array("domain")
266 )
267 ),
268 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
269 ),
270 "tags_domain" => array(
271 "cols" => array(
272 "tag_name" => "VARCHAR(255) NOT NULL",
273 "domain" => "VARCHAR(255) NOT NULL"
274 ),
275 "keys" => array(
276 "fkey" => array(
277 "fk_tags_domain" => array(
278 "col" => "domain",
279 "ref" => "domain.domain",
280 "delete" => "CASCADE",
281 "update" => "NO ACTION"
282 )
283 ),
284 "unique" => array(
285 "tag_name" => array("tag_name", "domain")
286 )
287 ),
288 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
289 ),
290 "tls_policy_override" => array(
291 "cols" => array(
292 "id" => "INT NOT NULL AUTO_INCREMENT",
293 "dest" => "VARCHAR(255) NOT NULL",
294 "policy" => "ENUM('none', 'may', 'encrypt', 'dane', 'dane-only', 'fingerprint', 'verify', 'secure') NOT NULL",
295 "parameters" => "VARCHAR(255) DEFAULT ''",
296 "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
297 "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
298 "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
299 ),
300 "keys" => array(
301 "primary" => array(
302 "" => array("id")
303 ),
304 "unique" => array(
305 "dest" => array("dest")
306 ),
307 ),
308 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
309 ),
310 "quarantine" => array(
311 "cols" => array(
312 "id" => "INT NOT NULL AUTO_INCREMENT",
313 "qid" => "VARCHAR(30) NOT NULL",
314 "subject" => "VARCHAR(500)",
315 "score" => "FLOAT(8,2)",
316 "ip" => "VARCHAR(50)",
317 "action" => "CHAR(20) NOT NULL DEFAULT 'unknown'",
318 "symbols" => "JSON",
319 "fuzzy_hashes" => "JSON",
320 "sender" => "VARCHAR(255) NOT NULL DEFAULT 'unknown'",
321 "rcpt" => "VARCHAR(255)",
322 "msg" => "LONGTEXT",
323 "domain" => "VARCHAR(255)",
324 "notified" => "TINYINT(1) NOT NULL DEFAULT '0'",
325 "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
326 "user" => "VARCHAR(255) NOT NULL DEFAULT 'unknown'",
327 ),
328 "keys" => array(
329 "primary" => array(
330 "" => array("id")
331 )
332 ),
333 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
334 ),
335 "mailbox" => array(
336 "cols" => array(
337 "username" => "VARCHAR(255) NOT NULL",
338 "password" => "VARCHAR(255) NOT NULL",
339 "name" => "VARCHAR(255)",
340 "description" => "VARCHAR(255)",
341 // mailbox_path_prefix is followed by domain/local_part/
342 "mailbox_path_prefix" => "VARCHAR(150) DEFAULT '/var/vmail/'",
343 "quota" => "BIGINT(20) NOT NULL DEFAULT '102400'",
344 "local_part" => "VARCHAR(255) NOT NULL",
345 "domain" => "VARCHAR(255) NOT NULL",
346 "attributes" => "JSON",
347 "kind" => "VARCHAR(100) NOT NULL DEFAULT ''",
348 "multiple_bookings" => "INT NOT NULL DEFAULT -1",
349 "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
350 "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
351 "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
352 ),
353 "keys" => array(
354 "primary" => array(
355 "" => array("username")
356 ),
357 "key" => array(
358 "domain" => array("domain"),
359 "kind" => array("kind")
360 )
361 ),
362 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
363 ),
364 "tags_mailbox" => array(
365 "cols" => array(
366 "tag_name" => "VARCHAR(255) NOT NULL",
367 "username" => "VARCHAR(255) NOT NULL"
368 ),
369 "keys" => array(
370 "fkey" => array(
371 "fk_tags_mailbox" => array(
372 "col" => "username",
373 "ref" => "mailbox.username",
374 "delete" => "CASCADE",
375 "update" => "NO ACTION"
376 )
377 ),
378 "unique" => array(
379 "tag_name" => array("tag_name", "username")
380 )
381 ),
382 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
383 ),
384 "sieve_filters" => array(
385 "cols" => array(
386 "id" => "INT NOT NULL AUTO_INCREMENT",
387 "username" => "VARCHAR(255) NOT NULL",
388 "script_desc" => "VARCHAR(255) NOT NULL",
389 "script_name" => "ENUM('active','inactive')",
390 "script_data" => "TEXT NOT NULL",
391 "filter_type" => "ENUM('postfilter','prefilter')",
392 "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
393 "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP"
394 ),
395 "keys" => array(
396 "primary" => array(
397 "" => array("id")
398 ),
399 "key" => array(
400 "username" => array("username"),
401 "script_desc" => array("script_desc")
402 ),
403 "fkey" => array(
404 "fk_username_sieve_global_before" => array(
405 "col" => "username",
406 "ref" => "mailbox.username",
407 "delete" => "CASCADE",
408 "update" => "NO ACTION"
409 )
410 )
411 ),
412 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
413 ),
414 "app_passwd" => array(
415 "cols" => array(
416 "id" => "INT NOT NULL AUTO_INCREMENT",
417 "name" => "VARCHAR(255) NOT NULL",
418 "mailbox" => "VARCHAR(255) NOT NULL",
419 "domain" => "VARCHAR(255) NOT NULL",
420 "password" => "VARCHAR(255) NOT NULL",
421 "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
422 "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
423 "imap_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
424 "smtp_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
425 "dav_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
426 "eas_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
427 "pop3_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
428 "sieve_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
429 "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
430 ),
431 "keys" => array(
432 "primary" => array(
433 "" => array("id")
434 ),
435 "key" => array(
436 "mailbox" => array("mailbox"),
437 "password" => array("password"),
438 "domain" => array("domain"),
439 ),
440 "fkey" => array(
441 "fk_username_app_passwd" => array(
442 "col" => "mailbox",
443 "ref" => "mailbox.username",
444 "delete" => "CASCADE",
445 "update" => "NO ACTION"
446 )
447 )
448 ),
449 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
450 ),
451 "user_acl" => array(
452 "cols" => array(
453 "username" => "VARCHAR(255) NOT NULL",
454 "spam_alias" => "TINYINT(1) NOT NULL DEFAULT '1'",
455 "tls_policy" => "TINYINT(1) NOT NULL DEFAULT '1'",
456 "spam_score" => "TINYINT(1) NOT NULL DEFAULT '1'",
457 "spam_policy" => "TINYINT(1) NOT NULL DEFAULT '1'",
458 "delimiter_action" => "TINYINT(1) NOT NULL DEFAULT '1'",
459 "syncjobs" => "TINYINT(1) NOT NULL DEFAULT '0'",
460 "eas_reset" => "TINYINT(1) NOT NULL DEFAULT '1'",
461 "sogo_profile_reset" => "TINYINT(1) NOT NULL DEFAULT '0'",
462 "pushover" => "TINYINT(1) NOT NULL DEFAULT '1'",
463 // quarantine is for quarantine actions, todo: rename
464 "quarantine" => "TINYINT(1) NOT NULL DEFAULT '1'",
465 "quarantine_attachments" => "TINYINT(1) NOT NULL DEFAULT '1'",
466 "quarantine_notification" => "TINYINT(1) NOT NULL DEFAULT '1'",
467 "quarantine_category" => "TINYINT(1) NOT NULL DEFAULT '1'",
468 "app_passwds" => "TINYINT(1) NOT NULL DEFAULT '1'",
469 ),
470 "keys" => array(
471 "primary" => array(
472 "" => array("username")
473 ),
474 "fkey" => array(
475 "fk_username" => array(
476 "col" => "username",
477 "ref" => "mailbox.username",
478 "delete" => "CASCADE",
479 "update" => "NO ACTION"
480 )
481 )
482 ),
483 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
484 ),
485 "alias_domain" => array(
486 "cols" => array(
487 "alias_domain" => "VARCHAR(255) NOT NULL",
488 "target_domain" => "VARCHAR(255) NOT NULL",
489 "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
490 "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
491 "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
492 ),
493 "keys" => array(
494 "primary" => array(
495 "" => array("alias_domain")
496 ),
497 "key" => array(
498 "active" => array("active"),
499 "target_domain" => array("target_domain")
500 )
501 ),
502 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
503 ),
504 "spamalias" => array(
505 "cols" => array(
506 "address" => "VARCHAR(255) NOT NULL",
507 "goto" => "TEXT NOT NULL",
508 "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
509 "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
510 "validity" => "INT(11)"
511 ),
512 "keys" => array(
513 "primary" => array(
514 "" => array("address")
515 ),
516 ),
517 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
518 ),
519 "filterconf" => array(
520 "cols" => array(
521 "object" => "VARCHAR(255) NOT NULL DEFAULT ''",
522 "option" => "VARCHAR(50) NOT NULL DEFAULT ''",
523 "value" => "VARCHAR(100) NOT NULL DEFAULT ''",
524 "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
525 "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
526 "prefid" => "INT(11) NOT NULL AUTO_INCREMENT"
527 ),
528 "keys" => array(
529 "primary" => array(
530 "" => array("prefid")
531 ),
532 "key" => array(
533 "object" => array("object")
534 )
535 ),
536 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
537 ),
538 "settingsmap" => array(
539 "cols" => array(
540 "id" => "INT NOT NULL AUTO_INCREMENT",
541 "desc" => "VARCHAR(255) NOT NULL",
542 "content" => "LONGTEXT NOT NULL",
543 "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
544 "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
545 "active" => "TINYINT(1) NOT NULL DEFAULT '0'"
546 ),
547 "keys" => array(
548 "primary" => array(
549 "" => array("id")
550 )
551 ),
552 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
553 ),
554 "logs" => array(
555 "cols" => array(
556 "id" => "INT NOT NULL AUTO_INCREMENT",
557 "task" => "CHAR(32) NOT NULL DEFAULT '000000'",
558 "type" => "VARCHAR(32) DEFAULT ''",
559 "msg" => "TEXT",
560 "call" => "TEXT",
561 "user" => "VARCHAR(64) NOT NULL",
562 "role" => "VARCHAR(32) NOT NULL",
563 "remote" => "VARCHAR(39) NOT NULL",
564 "time" => "INT(11) NOT NULL"
565 ),
566 "keys" => array(
567 "primary" => array(
568 "" => array("id")
569 )
570 ),
571 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
572 ),
573 "sasl_log" => array(
574 "cols" => array(
575 "service" => "VARCHAR(32) NOT NULL DEFAULT ''",
576 "app_password" => "INT",
577 "username" => "VARCHAR(255) NOT NULL",
578 "real_rip" => "VARCHAR(64) NOT NULL",
579 "datetime" => "DATETIME(0) NOT NULL DEFAULT NOW(0)"
580 ),
581 "keys" => array(
582 "primary" => array(
583 "" => array("service", "real_rip", "username")
584 ),
585 "key" => array(
586 "username" => array("username"),
587 "service" => array("service"),
588 "datetime" => array("datetime"),
589 "real_rip" => array("real_rip")
590 )
591 ),
592 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
593 ),
594 "quota2" => array(
595 "cols" => array(
596 "username" => "VARCHAR(255) NOT NULL",
597 "bytes" => "BIGINT(20) NOT NULL DEFAULT '0'",
598 "messages" => "BIGINT(20) NOT NULL DEFAULT '0'"
599 ),
600 "keys" => array(
601 "primary" => array(
602 "" => array("username")
603 )
604 ),
605 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
606 ),
607 "quota2replica" => array(
608 "cols" => array(
609 "username" => "VARCHAR(255) NOT NULL",
610 "bytes" => "BIGINT(20) NOT NULL DEFAULT '0'",
611 "messages" => "BIGINT(20) NOT NULL DEFAULT '0'"
612 ),
613 "keys" => array(
614 "primary" => array(
615 "" => array("username")
616 )
617 ),
618 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
619 ),
620 "domain_admins" => array(
621 "cols" => array(
622 "id" => "INT NOT NULL AUTO_INCREMENT",
623 "username" => "VARCHAR(255) NOT NULL",
624 "domain" => "VARCHAR(255) NOT NULL",
625 "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
626 "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
627 ),
628 "keys" => array(
629 "primary" => array(
630 "" => array("id")
631 ),
632 "key" => array(
633 "username" => array("username")
634 )
635 ),
636 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
637 ),
638 "da_acl" => array(
639 "cols" => array(
640 "username" => "VARCHAR(255) NOT NULL",
641 "syncjobs" => "TINYINT(1) NOT NULL DEFAULT '1'",
642 "quarantine" => "TINYINT(1) NOT NULL DEFAULT '1'",
643 "login_as" => "TINYINT(1) NOT NULL DEFAULT '1'",
644 "sogo_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
645 "app_passwds" => "TINYINT(1) NOT NULL DEFAULT '1'",
646 "bcc_maps" => "TINYINT(1) NOT NULL DEFAULT '1'",
647 "pushover" => "TINYINT(1) NOT NULL DEFAULT '0'",
648 "filters" => "TINYINT(1) NOT NULL DEFAULT '1'",
649 "ratelimit" => "TINYINT(1) NOT NULL DEFAULT '1'",
650 "spam_policy" => "TINYINT(1) NOT NULL DEFAULT '1'",
651 "extend_sender_acl" => "TINYINT(1) NOT NULL DEFAULT '0'",
652 "unlimited_quota" => "TINYINT(1) NOT NULL DEFAULT '0'",
653 "protocol_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
654 "smtp_ip_access" => "TINYINT(1) NOT NULL DEFAULT '1'",
655 "alias_domains" => "TINYINT(1) NOT NULL DEFAULT '0'",
656 "mailbox_relayhost" => "TINYINT(1) NOT NULL DEFAULT '1'",
657 "domain_relayhost" => "TINYINT(1) NOT NULL DEFAULT '1'",
658 "domain_desc" => "TINYINT(1) NOT NULL DEFAULT '0'"
659 ),
660 "keys" => array(
661 "primary" => array(
662 "" => array("username")
663 )
664 ),
665 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
666 ),
667 "da_sso" => array(
668 "cols" => array(
669 "username" => "VARCHAR(255) NOT NULL",
670 "token" => "VARCHAR(255) NOT NULL",
671 "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
672 ),
673 "keys" => array(
674 "primary" => array(
675 "" => array("token", "created")
676 ),
677 ),
678 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
679 ),
680 "imapsync" => array(
681 "cols" => array(
682 "id" => "INT NOT NULL AUTO_INCREMENT",
683 "user2" => "VARCHAR(255) NOT NULL",
684 "host1" => "VARCHAR(255) NOT NULL",
685 "authmech1" => "ENUM('PLAIN','LOGIN','CRAM-MD5') DEFAULT 'PLAIN'",
686 "regextrans2" => "VARCHAR(255) DEFAULT ''",
687 "authmd51" => "TINYINT(1) NOT NULL DEFAULT 0",
688 "domain2" => "VARCHAR(255) NOT NULL DEFAULT ''",
689 "subfolder2" => "VARCHAR(255) NOT NULL DEFAULT ''",
690 "user1" => "VARCHAR(255) NOT NULL",
691 "password1" => "VARCHAR(255) NOT NULL",
692 "exclude" => "VARCHAR(500) NOT NULL DEFAULT ''",
693 "maxage" => "SMALLINT NOT NULL DEFAULT '0'",
694 "mins_interval" => "SMALLINT UNSIGNED NOT NULL DEFAULT '0'",
695 "maxbytespersecond" => "VARCHAR(50) NOT NULL DEFAULT '0'",
696 "port1" => "SMALLINT UNSIGNED NOT NULL",
697 "enc1" => "ENUM('TLS','SSL','PLAIN') DEFAULT 'TLS'",
698 "delete2duplicates" => "TINYINT(1) NOT NULL DEFAULT '1'",
699 "delete1" => "TINYINT(1) NOT NULL DEFAULT '0'",
700 "delete2" => "TINYINT(1) NOT NULL DEFAULT '0'",
701 "automap" => "TINYINT(1) NOT NULL DEFAULT '0'",
702 "skipcrossduplicates" => "TINYINT(1) NOT NULL DEFAULT '0'",
703 "custom_params" => "VARCHAR(512) NOT NULL DEFAULT ''",
704 "timeout1" => "SMALLINT NOT NULL DEFAULT '600'",
705 "timeout2" => "SMALLINT NOT NULL DEFAULT '600'",
706 "subscribeall" => "TINYINT(1) NOT NULL DEFAULT '1'",
707 "is_running" => "TINYINT(1) NOT NULL DEFAULT '0'",
708 "returned_text" => "LONGTEXT",
709 "last_run" => "TIMESTAMP NULL DEFAULT NULL",
710 "success" => "TINYINT(1) UNSIGNED DEFAULT NULL",
711 "exit_status" => "VARCHAR(50) DEFAULT NULL",
712 "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
713 "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
714 "active" => "TINYINT(1) NOT NULL DEFAULT '0'"
715 ),
716 "keys" => array(
717 "primary" => array(
718 "" => array("id")
719 )
720 ),
721 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
722 ),
723 "bcc_maps" => array(
724 "cols" => array(
725 "id" => "INT NOT NULL AUTO_INCREMENT",
726 "local_dest" => "VARCHAR(255) NOT NULL",
727 "bcc_dest" => "VARCHAR(255) NOT NULL",
728 "domain" => "VARCHAR(255) NOT NULL",
729 "type" => "ENUM('sender','rcpt')",
730 "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
731 "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
732 "active" => "TINYINT(1) NOT NULL DEFAULT '0'"
733 ),
734 "keys" => array(
735 "primary" => array(
736 "" => array("id")
737 ),
738 "key" => array(
739 "local_dest" => array("local_dest"),
740 )
741 ),
742 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
743 ),
744 "recipient_maps" => array(
745 "cols" => array(
746 "id" => "INT NOT NULL AUTO_INCREMENT",
747 "old_dest" => "VARCHAR(255) NOT NULL",
748 "new_dest" => "VARCHAR(255) NOT NULL",
749 "created" => "DATETIME(0) NOT NULL DEFAULT NOW(0)",
750 "modified" => "DATETIME ON UPDATE CURRENT_TIMESTAMP",
751 "active" => "TINYINT(1) NOT NULL DEFAULT '0'"
752 ),
753 "keys" => array(
754 "primary" => array(
755 "" => array("id")
756 ),
757 "key" => array(
758 "local_dest" => array("old_dest"),
759 )
760 ),
761 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
762 ),
763 "tfa" => array(
764 "cols" => array(
765 "id" => "INT NOT NULL AUTO_INCREMENT",
766 "key_id" => "VARCHAR(255) NOT NULL",
767 "username" => "VARCHAR(255) NOT NULL",
768 "authmech" => "ENUM('yubi_otp', 'u2f', 'hotp', 'totp', 'webauthn')",
769 "secret" => "VARCHAR(255) DEFAULT NULL",
770 "keyHandle" => "VARCHAR(1023) DEFAULT NULL",
771 "publicKey" => "VARCHAR(4096) DEFAULT NULL",
772 "counter" => "INT NOT NULL DEFAULT '0'",
773 "certificate" => "TEXT",
774 "active" => "TINYINT(1) NOT NULL DEFAULT '0'"
775 ),
776 "keys" => array(
777 "primary" => array(
778 "" => array("id")
779 )
780 ),
781 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
782 ),
783 "forwarding_hosts" => array(
784 "cols" => array(
785 "host" => "VARCHAR(255) NOT NULL",
786 "source" => "VARCHAR(255) NOT NULL",
787 "filter_spam" => "TINYINT(1) NOT NULL DEFAULT '0'"
788 ),
789 "keys" => array(
790 "primary" => array(
791 "" => array("host")
792 ),
793 ),
794 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
795 ),
796 "sogo_acl" => array(
797 "cols" => array(
798 "id" => "INT NOT NULL AUTO_INCREMENT",
799 "c_folder_id" => "INT NOT NULL",
800 "c_object" => "VARCHAR(255) NOT NULL",
801 "c_uid" => "VARCHAR(255) NOT NULL",
802 "c_role" => "VARCHAR(80) NOT NULL"
803 ),
804 "keys" => array(
805 "primary" => array(
806 "" => array("id")
807 ),
808 "key" => array(
809 "sogo_acl_c_folder_id_idx" => array("c_folder_id"),
810 "sogo_acl_c_uid_idx" => array("c_uid")
811 )
812 ),
813 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
814 ),
815 "sogo_alarms_folder" => array(
816 "cols" => array(
817 "id" => "INT NOT NULL AUTO_INCREMENT",
818 "c_path" => "VARCHAR(255) NOT NULL",
819 "c_name" => "VARCHAR(255) NOT NULL",
820 "c_uid" => "VARCHAR(255) NOT NULL",
821 "c_recurrence_id" => "INT(11) DEFAULT NULL",
822 "c_alarm_number" => "INT(11) NOT NULL",
823 "c_alarm_date" => "INT(11) NOT NULL"
824 ),
825 "keys" => array(
826 "primary" => array(
827 "" => array("id")
828 )
829 ),
830 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
831 ),
832 "sogo_cache_folder" => array(
833 "cols" => array(
834 "c_uid" => "VARCHAR(255) NOT NULL",
835 "c_path" => "VARCHAR(255) NOT NULL",
836 "c_parent_path" => "VARCHAR(255) DEFAULT NULL",
837 "c_type" => "TINYINT(3) unsigned NOT NULL",
838 "c_creationdate" => "INT(11) NOT NULL",
839 "c_lastmodified" => "INT(11) NOT NULL",
840 "c_version" => "INT(11) NOT NULL DEFAULT '0'",
841 "c_deleted" => "TINYINT(4) NOT NULL DEFAULT '0'",
842 "c_content" => "LONGTEXT"
843 ),
844 "keys" => array(
845 "primary" => array(
846 "" => array("c_uid", "c_path")
847 ),
848 ),
849 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
850 ),
851 "sogo_folder_info" => array(
852 "cols" => array(
853 "c_folder_id" => "BIGINT(20) unsigned NOT NULL AUTO_INCREMENT",
854 "c_path" => "VARCHAR(255) NOT NULL",
855 "c_path1" => "VARCHAR(255) NOT NULL",
856 "c_path2" => "VARCHAR(255) DEFAULT NULL",
857 "c_path3" => "VARCHAR(255) DEFAULT NULL",
858 "c_path4" => "VARCHAR(255) DEFAULT NULL",
859 "c_foldername" => "VARCHAR(255) NOT NULL",
860 "c_location" => "VARCHAR(2048) DEFAULT NULL",
861 "c_quick_location" => "VARCHAR(2048) DEFAULT NULL",
862 "c_acl_location" => "VARCHAR(2048) DEFAULT NULL",
863 "c_folder_type" => "VARCHAR(255) NOT NULL"
864 ),
865 "keys" => array(
866 "primary" => array(
867 "" => array("c_path")
868 ),
869 "unique" => array(
870 "c_folder_id" => array("c_folder_id")
871 )
872 ),
873 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
874 ),
875 "sogo_quick_appointment" => array(
876 "cols" => array(
877 "c_folder_id" => "INT NOT NULL",
878 "c_name" => "VARCHAR(255) NOT NULL",
879 "c_uid" => "VARCHAR(1000) NOT NULL",
880 "c_startdate" => "INT",
881 "c_enddate" => "INT",
882 "c_cycleenddate" => "INT",
883 "c_title" => "VARCHAR(1000) NOT NULL",
884 "c_participants" => "TEXT",
885 "c_isallday" => "INT",
886 "c_iscycle" => "INT",
887 "c_cycleinfo" => "TEXT",
888 "c_classification" => "INT NOT NULL",
889 "c_isopaque" => "INT NOT NULL",
890 "c_status" => "INT NOT NULL",
891 "c_priority" => "INT",
892 "c_location" => "VARCHAR(255)",
893 "c_orgmail" => "VARCHAR(255)",
894 "c_partmails" => "TEXT",
895 "c_partstates" => "TEXT",
896 "c_category" => "VARCHAR(255)",
897 "c_sequence" => "INT",
898 "c_component" => "VARCHAR(10) NOT NULL",
899 "c_nextalarm" => "INT",
900 "c_description" => "TEXT"
901 ),
902 "keys" => array(
903 "primary" => array(
904 "" => array("c_folder_id", "c_name")
905 )
906 ),
907 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
908 ),
909 "sogo_quick_contact" => array(
910 "cols" => array(
911 "c_folder_id" => "INT NOT NULL",
912 "c_name" => "VARCHAR(255) NOT NULL",
913 "c_givenname" => "VARCHAR(255)",
914 "c_cn" => "VARCHAR(255)",
915 "c_sn" => "VARCHAR(255)",
916 "c_screenname" => "VARCHAR(255)",
917 "c_l" => "VARCHAR(255)",
918 "c_mail" => "TEXT",
919 "c_o" => "VARCHAR(500)",
920 "c_ou" => "VARCHAR(255)",
921 "c_telephonenumber" => "VARCHAR(255)",
922 "c_categories" => "VARCHAR(255)",
923 "c_component" => "VARCHAR(10) NOT NULL",
924 "c_hascertificate" => "INT4 DEFAULT 0"
925 ),
926 "keys" => array(
927 "primary" => array(
928 "" => array("c_folder_id", "c_name")
929 )
930 ),
931 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
932 ),
933 "sogo_sessions_folder" => array(
934 "cols" => array(
935 "c_id" => "VARCHAR(255) NOT NULL",
936 "c_value" => "VARCHAR(4096) NOT NULL",
937 "c_creationdate" => "INT(11) NOT NULL",
938 "c_lastseen" => "INT(11) NOT NULL"
939 ),
940 "keys" => array(
941 "primary" => array(
942 "" => array("c_id")
943 )
944 ),
945 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
946 ),
947 "sogo_store" => array(
948 "cols" => array(
949 "c_folder_id" => "INT NOT NULL",
950 "c_name" => "VARCHAR(255) NOT NULL",
951 "c_content" => "MEDIUMTEXT NOT NULL",
952 "c_creationdate" => "INT NOT NULL",
953 "c_lastmodified" => "INT NOT NULL",
954 "c_version" => "INT NOT NULL",
955 "c_deleted" => "INT"
956 ),
957 "keys" => array(
958 "primary" => array(
959 "" => array("c_folder_id", "c_name")
960 )
961 ),
962 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
963 ),
964 "pushover" => array(
965 "cols" => array(
966 "username" => "VARCHAR(255) NOT NULL",
967 "key" => "VARCHAR(255) NOT NULL",
968 "token" => "VARCHAR(255) NOT NULL",
969 "attributes" => "JSON",
970 "title" => "TEXT",
971 "text" => "TEXT",
972 "senders" => "TEXT",
973 "senders_regex" => "TEXT",
974 "active" => "TINYINT(1) NOT NULL DEFAULT '1'"
975 ),
976 "keys" => array(
977 "primary" => array(
978 "" => array("username")
979 )
980 ),
981 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
982 ),
983 "sogo_user_profile" => array(
984 "cols" => array(
985 "c_uid" => "VARCHAR(255) NOT NULL",
986 "c_defaults" => "LONGTEXT",
987 "c_settings" => "LONGTEXT"
988 ),
989 "keys" => array(
990 "primary" => array(
991 "" => array("c_uid")
992 )
993 ),
994 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
995 ),
996 "oauth_clients" => array(
997 "cols" => array(
998 "id" => "INT NOT NULL AUTO_INCREMENT",
999 "client_id" => "VARCHAR(80) NOT NULL",
1000 "client_secret" => "VARCHAR(80)",
1001 "redirect_uri" => "VARCHAR(2000)",
1002 "grant_types" => "VARCHAR(80)",
1003 "scope" => "VARCHAR(4000)",
1004 "user_id" => "VARCHAR(80)"
1005 ),
1006 "keys" => array(
1007 "primary" => array(
1008 "" => array("client_id")
1009 ),
1010 "unique" => array(
1011 "id" => array("id")
1012 )
1013 ),
1014 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
1015 ),
1016 "oauth_access_tokens" => array(
1017 "cols" => array(
1018 "access_token" => "VARCHAR(40) NOT NULL",
1019 "client_id" => "VARCHAR(80) NOT NULL",
1020 "user_id" => "VARCHAR(80)",
1021 "expires" => "TIMESTAMP NOT NULL",
1022 "scope" => "VARCHAR(4000)"
1023 ),
1024 "keys" => array(
1025 "primary" => array(
1026 "" => array("access_token")
1027 )
1028 ),
1029 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
1030 ),
1031 "oauth_authorization_codes" => array(
1032 "cols" => array(
1033 "authorization_code" => "VARCHAR(40) NOT NULL",
1034 "client_id" => "VARCHAR(80) NOT NULL",
1035 "user_id" => "VARCHAR(80)",
1036 "redirect_uri" => "VARCHAR(2000)",
1037 "expires" => "TIMESTAMP NOT NULL",
1038 "scope" => "VARCHAR(4000)",
1039 "id_token" => "VARCHAR(1000)"
1040 ),
1041 "keys" => array(
1042 "primary" => array(
1043 "" => array("authorization_code")
1044 )
1045 ),
1046 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
1047 ),
1048 "oauth_refresh_tokens" => array(
1049 "cols" => array(
1050 "refresh_token" => "VARCHAR(40) NOT NULL",
1051 "client_id" => "VARCHAR(80) NOT NULL",
1052 "user_id" => "VARCHAR(80)",
1053 "expires" => "TIMESTAMP NOT NULL",
1054 "scope" => "VARCHAR(4000)"
1055 ),
1056 "keys" => array(
1057 "primary" => array(
1058 "" => array("refresh_token")
1059 )
1060 ),
1061 "attr" => "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC"
1062 )
1063 );
1064
1065 foreach ($tables as $table => $properties) {
1066 // Migrate to quarantine
1067 if ($table == 'quarantine') {
1068 $stmt = $pdo->query("SHOW TABLES LIKE 'quarantaine'");
1069 $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
1070 if ($num_results != 0) {
1071 $stmt = $pdo->query("SHOW TABLES LIKE 'quarantine'");
1072 $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
1073 if ($num_results == 0) {
1074 $pdo->query("RENAME TABLE `quarantaine` TO `quarantine`");
1075 }
1076 }
1077 }
1078
1079 // Migrate tls_enforce_* options
1080 if ($table == 'mailbox') {
1081 $stmt = $pdo->query("SHOW TABLES LIKE 'mailbox'");
1082 $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
1083 if ($num_results != 0) {
1084 $stmt = $pdo->query("SHOW COLUMNS FROM `mailbox` LIKE '%tls_enforce%'");
1085 $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
1086 if ($num_results != 0) {
1087 $stmt = $pdo->query("SELECT `username`, `tls_enforce_in`, `tls_enforce_out` FROM `mailbox`");
1088 $tls_options_rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
1089 while ($row = array_shift($tls_options_rows)) {
1090 $tls_options[$row['username']] = array('tls_enforce_in' => $row['tls_enforce_in'], 'tls_enforce_out' => $row['tls_enforce_out']);
1091 }
1092 }
1093 }
1094 }
1095
1096 $stmt = $pdo->query("SHOW TABLES LIKE '" . $table . "'");
1097 $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
1098 if ($num_results != 0) {
1099 $stmt = $pdo->prepare("SELECT CONCAT('ALTER TABLE `', `table_schema`, '`.', `table_name`, ' DROP FOREIGN KEY ', `constraint_name`, ';') AS `FKEY_DROP` FROM `information_schema`.`table_constraints`
1100 WHERE `constraint_type` = 'FOREIGN KEY' AND `table_name` = :table;");
1101 $stmt->execute(array(':table' => $table));
1102 $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
1103 while ($row = array_shift($rows)) {
1104 $pdo->query($row['FKEY_DROP']);
1105 }
1106 foreach($properties['cols'] as $column => $type) {
1107 $stmt = $pdo->query("SHOW COLUMNS FROM `" . $table . "` LIKE '" . $column . "'");
1108 $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
1109 if ($num_results == 0) {
1110 if (strpos($type, 'AUTO_INCREMENT') !== false) {
1111 $type = $type . ' PRIMARY KEY ';
1112 // Adding an AUTO_INCREMENT key, need to drop primary keys first, if exists
1113 $stmt = $pdo->query("SHOW KEYS FROM `" . $table . "` WHERE Key_name = 'PRIMARY'");
1114 $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
1115 if ($num_results != 0) {
1116 $pdo->query("ALTER TABLE `" . $table . "` DROP PRIMARY KEY");
1117 }
1118 }
1119 $pdo->query("ALTER TABLE `" . $table . "` ADD `" . $column . "` " . $type);
1120 }
1121 else {
1122 $pdo->query("ALTER TABLE `" . $table . "` MODIFY COLUMN `" . $column . "` " . $type);
1123 }
1124 }
1125 foreach($properties['keys'] as $key_type => $key_content) {
1126 if (strtolower($key_type) == 'primary') {
1127 foreach ($key_content as $key_values) {
1128 $fields = "`" . implode("`, `", $key_values) . "`";
1129 $stmt = $pdo->query("SHOW KEYS FROM `" . $table . "` WHERE Key_name = 'PRIMARY'");
1130 $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
1131 $is_drop = ($num_results != 0) ? "DROP PRIMARY KEY, " : "";
1132 $pdo->query("ALTER TABLE `" . $table . "` " . $is_drop . "ADD PRIMARY KEY (" . $fields . ")");
1133 }
1134 }
1135 if (strtolower($key_type) == 'key') {
1136 foreach ($key_content as $key_name => $key_values) {
1137 $fields = "`" . implode("`, `", $key_values) . "`";
1138 $stmt = $pdo->query("SHOW KEYS FROM `" . $table . "` WHERE Key_name = '" . $key_name . "'");
1139 $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
1140 $is_drop = ($num_results != 0) ? "DROP INDEX `" . $key_name . "`, " : "";
1141 $pdo->query("ALTER TABLE `" . $table . "` " . $is_drop . "ADD KEY `" . $key_name . "` (" . $fields . ")");
1142 }
1143 }
1144 if (strtolower($key_type) == 'unique') {
1145 foreach ($key_content as $key_name => $key_values) {
1146 $fields = "`" . implode("`, `", $key_values) . "`";
1147 $stmt = $pdo->query("SHOW KEYS FROM `" . $table . "` WHERE Key_name = '" . $key_name . "'");
1148 $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
1149 $is_drop = ($num_results != 0) ? "DROP INDEX `" . $key_name . "`, " : "";
1150 $pdo->query("ALTER TABLE `" . $table . "` " . $is_drop . "ADD UNIQUE KEY `" . $key_name . "` (" . $fields . ")");
1151 }
1152 }
1153 if (strtolower($key_type) == 'fkey') {
1154 foreach ($key_content as $key_name => $key_values) {
1155 $fields = "`" . implode("`, `", $key_values) . "`";
1156 $stmt = $pdo->query("SHOW KEYS FROM `" . $table . "` WHERE Key_name = '" . $key_name . "'");
1157 $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
1158 if ($num_results != 0) {
1159 $pdo->query("ALTER TABLE `" . $table . "` DROP INDEX `" . $key_name . "`");
1160 }
1161 @list($table_ref, $field_ref) = explode('.', $key_values['ref']);
1162 $pdo->query("ALTER TABLE `" . $table . "` ADD FOREIGN KEY `" . $key_name . "` (" . $key_values['col'] . ") REFERENCES `" . $table_ref . "` (`" . $field_ref . "`)
1163 ON DELETE " . $key_values['delete'] . " ON UPDATE " . $key_values['update']);
1164 }
1165 }
1166 }
1167 // Drop all vanished columns
1168 $stmt = $pdo->query("SHOW COLUMNS FROM `" . $table . "`");
1169 $cols_in_table = $stmt->fetchAll(PDO::FETCH_ASSOC);
1170 while ($row = array_shift($cols_in_table)) {
1171 if (!array_key_exists($row['Field'], $properties['cols'])) {
1172 $pdo->query("ALTER TABLE `" . $table . "` DROP COLUMN `" . $row['Field'] . "`;");
1173 }
1174 }
1175
1176 // Step 1: Get all non-primary keys, that currently exist and those that should exist
1177 $stmt = $pdo->query("SHOW KEYS FROM `" . $table . "` WHERE `Key_name` != 'PRIMARY'");
1178 $keys_in_table = $stmt->fetchAll(PDO::FETCH_ASSOC);
1179 $keys_to_exist = array();
1180 if (isset($properties['keys']['unique']) && is_array($properties['keys']['unique'])) {
1181 foreach ($properties['keys']['unique'] as $key_name => $key_values) {
1182 $keys_to_exist[] = $key_name;
1183 }
1184 }
1185 if (isset($properties['keys']['key']) && is_array($properties['keys']['key'])) {
1186 foreach ($properties['keys']['key'] as $key_name => $key_values) {
1187 $keys_to_exist[] = $key_name;
1188 }
1189 }
1190 // Index for foreign key must exist
1191 if (isset($properties['keys']['fkey']) && is_array($properties['keys']['fkey'])) {
1192 foreach ($properties['keys']['fkey'] as $key_name => $key_values) {
1193 $keys_to_exist[] = $key_name;
1194 }
1195 }
1196 // Step 2: Drop all vanished indexes
1197 while ($row = array_shift($keys_in_table)) {
1198 if (!in_array($row['Key_name'], $keys_to_exist)) {
1199 $pdo->query("ALTER TABLE `" . $table . "` DROP INDEX `" . $row['Key_name'] . "`");
1200 }
1201 }
1202 // Step 3: Drop all vanished primary keys
1203 if (!isset($properties['keys']['primary'])) {
1204 $stmt = $pdo->query("SHOW KEYS FROM `" . $table . "` WHERE Key_name = 'PRIMARY'");
1205 $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
1206 if ($num_results != 0) {
1207 $pdo->query("ALTER TABLE `" . $table . "` DROP PRIMARY KEY");
1208 }
1209 }
1210 }
1211 else {
1212 // Create table if it is missing
1213 $sql = "CREATE TABLE IF NOT EXISTS `" . $table . "` (";
1214 foreach($properties['cols'] as $column => $type) {
1215 $sql .= "`" . $column . "` " . $type . ",";
1216 }
1217 foreach($properties['keys'] as $key_type => $key_content) {
1218 if (strtolower($key_type) == 'primary') {
1219 foreach ($key_content as $key_values) {
1220 $fields = "`" . implode("`, `", $key_values) . "`";
1221 $sql .= "PRIMARY KEY (" . $fields . ")" . ",";
1222 }
1223 }
1224 elseif (strtolower($key_type) == 'key') {
1225 foreach ($key_content as $key_name => $key_values) {
1226 $fields = "`" . implode("`, `", $key_values) . "`";
1227 $sql .= "KEY `" . $key_name . "` (" . $fields . ")" . ",";
1228 }
1229 }
1230 elseif (strtolower($key_type) == 'unique') {
1231 foreach ($key_content as $key_name => $key_values) {
1232 $fields = "`" . implode("`, `", $key_values) . "`";
1233 $sql .= "UNIQUE KEY `" . $key_name . "` (" . $fields . ")" . ",";
1234 }
1235 }
1236 elseif (strtolower($key_type) == 'fkey') {
1237 foreach ($key_content as $key_name => $key_values) {
1238 @list($table_ref, $field_ref) = explode('.', $key_values['ref']);
1239 $sql .= "FOREIGN KEY `" . $key_name . "` (" . $key_values['col'] . ") REFERENCES `" . $table_ref . "` (`" . $field_ref . "`)
1240 ON DELETE " . $key_values['delete'] . " ON UPDATE " . $key_values['update'] . ",";
1241 }
1242 }
1243 }
1244 $sql = rtrim($sql, ",");
1245 $sql .= ") " . $properties['attr'];
1246 $pdo->query($sql);
1247 }
1248 // Reset table attributes
1249 $pdo->query("ALTER TABLE `" . $table . "` " . $properties['attr'] . ";");
1250
1251 }
1252
1253 // Recreate SQL views
1254 foreach ($views as $view => $create) {
1255 $pdo->query("DROP VIEW IF EXISTS `" . $view . "`;");
1256 $pdo->query($create);
1257 }
1258
1259 // Mitigate imapsync argument injection issue
1260 $pdo->query("UPDATE `imapsync` SET `custom_params` = ''
1261 WHERE `custom_params` LIKE '%pipemess%'
1262 OR custom_params LIKE '%skipmess%'
1263 OR custom_params LIKE '%delete2foldersonly%'
1264 OR custom_params LIKE '%delete2foldersbutnot%'
1265 OR custom_params LIKE '%regexflag%'
1266 OR custom_params LIKE '%pipemess%'
1267 OR custom_params LIKE '%regextrans2%'
1268 OR custom_params LIKE '%maxlinelengthcmd%';");
1269
1270 // Migrate webauthn tfa
1271 $stmt = $pdo->query("ALTER TABLE `tfa` MODIFY COLUMN `authmech` ENUM('yubi_otp', 'u2f', 'hotp', 'totp', 'webauthn')");
1272
1273 // Inject admin if not exists
1274 $stmt = $pdo->query("SELECT NULL FROM `admin`");
1275 $num_results = count($stmt->fetchAll(PDO::FETCH_ASSOC));
1276 if ($num_results == 0) {
1277 $pdo->query("INSERT INTO `admin` (`username`, `password`, `superadmin`, `created`, `modified`, `active`)
1278 VALUES ('admin', '{SSHA256}K8eVJ6YsZbQCfuJvSUbaQRLr0HPLz5rC9IAp0PAFl0tmNDBkMDc0NDAyOTAxN2Rk', 1, NOW(), NOW(), 1)");
1279 $pdo->query("INSERT INTO `domain_admins` (`username`, `domain`, `created`, `active`)
1280 SELECT `username`, 'ALL', NOW(), 1 FROM `admin`
1281 WHERE superadmin='1' AND `username` NOT IN (SELECT `username` FROM `domain_admins`);");
1282 $pdo->query("DELETE FROM `admin` WHERE `username` NOT IN (SELECT `username` FROM `domain_admins`);");
1283 }
1284 // Insert new DB schema version
1285 $pdo->query("REPLACE INTO `versions` (`application`, `version`) VALUES ('db_schema', '" . $db_version . "');");
1286
1287 // Fix dangling domain admins
1288 $pdo->query("DELETE FROM `admin` WHERE `superadmin` = 0 AND `username` NOT IN (SELECT `username`FROM `domain_admins`);");
1289 $pdo->query("DELETE FROM `da_acl` WHERE `username` NOT IN (SELECT `username`FROM `domain_admins`);");
1290
1291 // Migrate attributes
1292 // pushover
1293 $pdo->query("UPDATE `pushover` SET `attributes` = '{}' WHERE `attributes` = '' OR `attributes` IS NULL;");
1294 $pdo->query("UPDATE `pushover` SET `attributes` = JSON_SET(`attributes`, '$.evaluate_x_prio', \"0\") WHERE JSON_VALUE(`attributes`, '$.evaluate_x_prio') IS NULL;");
1295 $pdo->query("UPDATE `pushover` SET `attributes` = JSON_SET(`attributes`, '$.only_x_prio', \"0\") WHERE JSON_VALUE(`attributes`, '$.only_x_prio') IS NULL;");
1296 $pdo->query("UPDATE `pushover` SET `attributes` = JSON_SET(`attributes`, '$.sound', \"pushover\") WHERE JSON_VALUE(`attributes`, '$.sound') IS NULL;");
1297 // mailbox
1298 $pdo->query("UPDATE `mailbox` SET `attributes` = '{}' WHERE `attributes` = '' OR `attributes` IS NULL;");
1299 $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.passwd_update', \"0\") WHERE JSON_VALUE(`attributes`, '$.passwd_update') IS NULL;");
1300 $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.relayhost', \"0\") WHERE JSON_VALUE(`attributes`, '$.relayhost') IS NULL;");
1301 $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.force_pw_update', \"0\") WHERE JSON_VALUE(`attributes`, '$.force_pw_update') IS NULL;");
1302 $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.sieve_access', \"1\") WHERE JSON_VALUE(`attributes`, '$.sieve_access') IS NULL;");
1303 $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.sogo_access', \"1\") WHERE JSON_VALUE(`attributes`, '$.sogo_access') IS NULL;");
1304 $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.imap_access', \"1\") WHERE JSON_VALUE(`attributes`, '$.imap_access') IS NULL;");
1305 $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.pop3_access', \"1\") WHERE JSON_VALUE(`attributes`, '$.pop3_access') IS NULL;");
1306 $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.smtp_access', \"1\") WHERE JSON_VALUE(`attributes`, '$.smtp_access') IS NULL;");
1307 $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.mailbox_format', \"maildir:\") WHERE JSON_VALUE(`attributes`, '$.mailbox_format') IS NULL;");
1308 $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.quarantine_notification', \"never\") WHERE JSON_VALUE(`attributes`, '$.quarantine_notification') IS NULL;");
1309 $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.quarantine_category', \"reject\") WHERE JSON_VALUE(`attributes`, '$.quarantine_category') IS NULL;");
1310 foreach($tls_options as $tls_user => $tls_options) {
1311 $stmt = $pdo->prepare("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.tls_enforce_in', :tls_enforce_in),
1312 `attributes` = JSON_SET(`attributes`, '$.tls_enforce_out', :tls_enforce_out)
1313 WHERE `username` = :username");
1314 $stmt->execute(array(':tls_enforce_in' => $tls_options['tls_enforce_in'], ':tls_enforce_out' => $tls_options['tls_enforce_out'], ':username' => $tls_user));
1315 }
1316 // Set tls_enforce_* if still missing (due to deleted attrs for example)
1317 $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.tls_enforce_out', \"1\") WHERE JSON_VALUE(`attributes`, '$.tls_enforce_out') IS NULL;");
1318 $pdo->query("UPDATE `mailbox` SET `attributes` = JSON_SET(`attributes`, '$.tls_enforce_in', \"1\") WHERE JSON_VALUE(`attributes`, '$.tls_enforce_in') IS NULL;");
1319 // Fix ACL
1320 $pdo->query("INSERT INTO `user_acl` (`username`) SELECT `username` FROM `mailbox` WHERE `kind` = '' AND NOT EXISTS (SELECT `username` FROM `user_acl`);");
1321 $pdo->query("INSERT INTO `da_acl` (`username`) SELECT DISTINCT `username` FROM `domain_admins` WHERE `username` != 'admin' AND NOT EXISTS (SELECT `username` FROM `da_acl`);");
1322 // Fix domain_admins
1323 $pdo->query("DELETE FROM `domain_admins` WHERE `domain` = 'ALL';");
1324
1325 // add default templates
1326 $default_domain_template = array(
1327 "template" => "Default",
1328 "type" => "domain",
1329 "attributes" => array(
1330 "tags" => array(),
1331 "max_num_aliases_for_domain" => 400,
1332 "max_num_mboxes_for_domain" => 10,
1333 "def_quota_for_mbox" => 3072 * 1048576,
1334 "max_quota_for_mbox" => 10240 * 1048576,
1335 "max_quota_for_domain" => 10240 * 1048576,
1336 "rl_frame" => "s",
1337 "rl_value" => "",
1338 "active" => 1,
1339 "gal" => 1,
1340 "backupmx" => 0,
1341 "relay_all_recipients" => 0,
1342 "relay_unknown_only" => 0,
1343 "dkim_selector" => "dkim",
1344 "key_size" => 2048,
1345 "max_quota_for_domain" => 10240 * 1048576,
1346 )
1347 );
1348 $default_mailbox_template = array(
1349 "template" => "Default",
1350 "type" => "mailbox",
1351 "attributes" => array(
1352 "tags" => array(),
1353 "quota" => 0,
1354 "quarantine_notification" => strval($GLOBALS['MAILBOX_DEFAULT_ATTRIBUTES']['quarantine_notification']),
1355 "quarantine_category" => strval($GLOBALS['MAILBOX_DEFAULT_ATTRIBUTES']['quarantine_category']),
1356 "rl_frame" => "s",
1357 "rl_value" => "",
1358 "force_pw_update" => intval($GLOBALS['MAILBOX_DEFAULT_ATTRIBUTES']['force_pw_update']),
1359 "sogo_access" => intval($GLOBALS['MAILBOX_DEFAULT_ATTRIBUTES']['sogo_access']),
1360 "active" => 1,
1361 "tls_enforce_in" => intval($GLOBALS['MAILBOX_DEFAULT_ATTRIBUTES']['tls_enforce_in']),
1362 "tls_enforce_out" => intval($GLOBALS['MAILBOX_DEFAULT_ATTRIBUTES']['tls_enforce_out']),
1363 "imap_access" => intval($GLOBALS['MAILBOX_DEFAULT_ATTRIBUTES']['imap_access']),
1364 "pop3_access" => intval($GLOBALS['MAILBOX_DEFAULT_ATTRIBUTES']['pop3_access']),
1365 "smtp_access" => intval($GLOBALS['MAILBOX_DEFAULT_ATTRIBUTES']['smtp_access']),
1366 "sieve_access" => intval($GLOBALS['MAILBOX_DEFAULT_ATTRIBUTES']['sieve_access']),
1367 "acl_spam_alias" => 1,
1368 "acl_tls_policy" => 1,
1369 "acl_spam_score" => 1,
1370 "acl_spam_policy" => 1,
1371 "acl_delimiter_action" => 1,
1372 "acl_syncjobs" => 0,
1373 "acl_eas_reset" => 1,
1374 "acl_sogo_profile_reset" => 0,
1375 "acl_pushover" => 1,
1376 "acl_quarantine" => 1,
1377 "acl_quarantine_attachments" => 1,
1378 "acl_quarantine_notification" => 1,
1379 "acl_quarantine_category" => 1,
1380 "acl_app_passwds" => 1,
1381 )
1382 );
1383 $stmt = $pdo->prepare("SELECT id FROM `templates` WHERE `type` = :type AND `template` = :template");
1384 $stmt->execute(array(
1385 ":type" => "domain",
1386 ":template" => $default_domain_template["template"]
1387 ));
1388 $row = $stmt->fetch(PDO::FETCH_ASSOC);
1389 if (empty($row)){
1390 $stmt = $pdo->prepare("INSERT INTO `templates` (`type`, `template`, `attributes`)
1391 VALUES (:type, :template, :attributes)");
1392 $stmt->execute(array(
1393 ":type" => "domain",
1394 ":template" => $default_domain_template["template"],
1395 ":attributes" => json_encode($default_domain_template["attributes"])
1396 ));
1397 }
1398 $stmt = $pdo->prepare("SELECT id FROM `templates` WHERE `type` = :type AND `template` = :template");
1399 $stmt->execute(array(
1400 ":type" => "mailbox",
1401 ":template" => $default_mailbox_template["template"]
1402 ));
1403 $row = $stmt->fetch(PDO::FETCH_ASSOC);
1404 if (empty($row)){
1405 $stmt = $pdo->prepare("INSERT INTO `templates` (`type`, `template`, `attributes`)
1406 VALUES (:type, :template, :attributes)");
1407 $stmt->execute(array(
1408 ":type" => "mailbox",
1409 ":template" => $default_mailbox_template["template"],
1410 ":attributes" => json_encode($default_mailbox_template["attributes"])
1411 ));
1412 }
1413
1414 if (php_sapi_name() == "cli") {
1415 echo "DB initialization completed" . PHP_EOL;
1416 } else {
1417 $_SESSION['return'][] = array(
1418 'type' => 'success',
1419 'log' => array(__FUNCTION__),
1420 'msg' => 'db_init_complete'
1421 );
1422 }
1423 }
1424 catch (PDOException $e) {
1425 if (php_sapi_name() == "cli") {
1426 echo "DB initialization failed: " . print_r($e, true) . PHP_EOL;
1427 } else {
1428 $_SESSION['return'][] = array(
1429 'type' => 'danger',
1430 'log' => array(__FUNCTION__),
1431 'msg' => array('mysql_error', $e)
1432 );
1433 }
1434 }
1435}
1436if (php_sapi_name() == "cli") {
1437 include '/web/inc/vars.inc.php';
1438 include '/web/inc/functions.docker.inc.php';
1439 // $now = new DateTime();
1440 // $mins = $now->getOffset() / 60;
1441 // $sgn = ($mins < 0 ? -1 : 1);
1442 // $mins = abs($mins);
1443 // $hrs = floor($mins / 60);
1444 // $mins -= $hrs * 60;
1445 // $offset = sprintf('%+d:%02d', $hrs*$sgn, $mins);
1446 $dsn = $database_type . ":unix_socket=" . $database_sock . ";dbname=" . $database_name;
1447 $opt = [
1448 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
1449 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
1450 PDO::ATTR_EMULATE_PREPARES => false,
1451 //PDO::MYSQL_ATTR_INIT_COMMAND => "SET time_zone = '" . $offset . "', group_concat_max_len = 3423543543;",
1452 ];
1453 $pdo = new PDO($dsn, $database_user, $database_pass, $opt);
1454 $stmt = $pdo->query("SELECT COUNT('OK') AS OK_C FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'sogo_view' OR TABLE_NAME = '_sogo_static_view';");
1455 $res = $stmt->fetch(PDO::FETCH_ASSOC);
1456 if (intval($res['OK_C']) === 2) {
1457 // Be more precise when replacing into _sogo_static_view, col orders may change
1458 try {
1459 $stmt = $pdo->query("REPLACE INTO _sogo_static_view (`c_uid`, `domain`, `c_name`, `c_password`, `c_cn`, `mail`, `aliases`, `ad_aliases`, `ext_acl`, `kind`, `multiple_bookings`)
1460 SELECT `c_uid`, `domain`, `c_name`, `c_password`, `c_cn`, `mail`, `aliases`, `ad_aliases`, `ext_acl`, `kind`, `multiple_bookings` from sogo_view");
1461 $stmt = $pdo->query("DELETE FROM _sogo_static_view WHERE `c_uid` NOT IN (SELECT `username` FROM `mailbox` WHERE `active` = '1');");
1462 echo "Fixed _sogo_static_view" . PHP_EOL;
1463 }
1464 catch ( Exception $e ) {
1465 // Dunno
1466 }
1467 }
1468 try {
1469 $m = new Memcached();
1470 $m->addServer('memcached', 11211);
1471 $m->flush();
1472 echo "Cleaned up memcached". PHP_EOL;
1473 }
1474 catch ( Exception $e ) {
1475 // Dunno
1476 }
1477 init_db_schema();
1478}