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