Introduces email type alias_aux for emails in auxiliary domains.
[platal.git] / upgrade / 1.1.0 / 01_new_mail_schema.sql
CommitLineData
77c9b1d3
SJ
1DROP TABLE IF EXISTS email_redirect_account;
2DROP TABLE IF EXISTS email_redirect_other;
b172472d
FB
3DROP TABLE IF EXISTS email_source_account;
4DROP TABLE IF EXISTS email_source_other;
5DROP TABLE IF EXISTS homonyms_list;
b172472d
FB
6DROP TABLE IF EXISTS email_virtual;
7DROP TABLE IF EXISTS email_virtual_domains;
8
b1db6e48
SJ
9CREATE TABLE email_virtual_domains (
10 id SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
11 name VARCHAR(255) NOT NULL DEFAULT '',
12 aliasing SMALLINT(3) UNSIGNED NOT NULL DEFAULT 1,
13 PRIMARY KEY (id),
14 KEY (name),
15 FOREIGN KEY (aliasing) REFERENCES email_virtual_domains (id) ON UPDATE CASCADE ON DELETE CASCADE
16) ENGINE=InnoDB, CHARSET=utf8;
17
b172472d 18CREATE TABLE email_source_account (
b1db6e48
SJ
19 email VARCHAR(255) NOT NULL,
20 domain SMALLINT(3) UNSIGNED NOT NULL DEFAULT 1,
87c89986 21 uid INT(11) UNSIGNED NOT NULL,
08d33afc 22 type ENUM('forlife', 'alias', 'alias_aux') NOT NULL DEFAULT 'forlife',
87c89986
SJ
23 flags SET('bestalias', 'usage', 'marital') NOT NULL DEFAULT '',
24 expire DATE DEFAULT NULL,
b1db6e48
SJ
25 PRIMARY KEY (email, domain),
26 KEY (domain),
87c89986
SJ
27 KEY (uid),
28 KEY (type),
b1db6e48
SJ
29 FOREIGN KEY (uid) REFERENCES accounts (uid) ON UPDATE CASCADE ON DELETE CASCADE,
30 FOREIGN KEY (domain) REFERENCES email_virtual_domains (id) ON UPDATE CASCADE ON DELETE CASCADE
87c89986 31) ENGINE=InnoDB, CHARSET=utf8;
b172472d
FB
32
33CREATE TABLE email_source_other (
b1db6e48
SJ
34 email VARCHAR(255) NOT NULL,
35 domain SMALLINT(3) UNSIGNED NOT NULL DEFAULT 1,
77c9b1d3 36 hrmid VARCHAR(255) NOT NULL DEFAULT '',
87c89986 37 type ENUM('homonym', 'ax', 'honeypot'),
4371e993 38 expire DATE DEFAULT NULL,
b1db6e48
SJ
39 PRIMARY KEY (email, domain),
40 KEY (domain),
77c9b1d3 41 KEY(hrmid),
b1db6e48 42 FOREIGN KEY (domain) REFERENCES email_virtual_domains (id) ON UPDATE CASCADE ON DELETE CASCADE
87c89986 43) ENGINE=InnoDB, CHARSET=utf8;
b172472d
FB
44
45CREATE TABLE homonyms_list (
87c89986
SJ
46 hrmid VARCHAR(255) NOT NULL,
47 uid INT(11) UNSIGNED NOT NULL,
43de2baf 48 PRIMARY KEY (hrmid, uid),
b1db6e48 49 key (uid),
43de2baf 50 FOREIGN KEY (uid) REFERENCES accounts (uid) ON UPDATE CASCADE ON DELETE CASCADE
87c89986 51) ENGINE=InnoDB, CHARSET=utf8;
b172472d
FB
52
53CREATE TABLE email_redirect_account (
87c89986
SJ
54 uid INT(11) UNSIGNED NOT NULL,
55 redirect VARCHAR(255) NOT NULL DEFAULT '',
56 rewrite VARCHAR(255) NOT NULL DEFAULT '',
57 type ENUM('smtp', 'googleapps', 'imap', 'homonym') NOT NULL DEFAULT 'smtp',
58 action ENUM('default', 'drop_spams', 'let_spams', 'tag_and_drop_spams', 'tag_spams', 'imap_and_bounce', 'homonym') NOT NULL DEFAULT 'default',
59 broken_date DATE NOT NULL DEFAULT '0000-00-00',
60 broken_level TINYINT(1) NOT NULL DEFAULT 0,
61 last DATE NOT NULL DEFAULT '0000-00-00',
62 flags ENUM('active', 'inactive', 'broken', 'disabled') NOT NULL,
63 hash VARCHAR(32) DEFAULT NULL,
64 allow_rewrite TINYINT(1) DEFAULT 0,
43de2baf 65 PRIMARY KEY (uid, redirect),
87c89986
SJ
66 KEY (uid),
67 KEY (redirect),
43de2baf 68 FOREIGN KEY (uid) REFERENCES accounts (uid) ON UPDATE CASCADE ON DELETE CASCADE
87c89986 69) ENGINE=InnoDB, CHARSET=utf8;
b172472d
FB
70
71CREATE TABLE email_redirect_other (
87c89986
SJ
72 hrmid VARCHAR(255) NOT NULL DEFAULT '',
73 redirect VARCHAR(255) NOT NULL DEFAULT '',
74 type ENUM('smtp', 'googleapps', 'imap', 'homonym') NOT NULL DEFAULT 'smtp',
75 action ENUM('default', 'drop_spams', 'let_spams', 'tag_and_drop_spams', 'tag_spams', 'imap_and_bounce', 'homonym') NOT NULL DEFAULT 'default',
43de2baf 76 PRIMARY KEY (hrmid, redirect),
87c89986 77 KEY (hrmid),
77c9b1d3
SJ
78 KEY (redirect),
79 FOREIGN KEY (hrmid) REFERENCES email_source_other (hrmid) ON DELETE CASCADE ON UPDATE CASCADE
87c89986 80) ENGINE=InnoDB, CHARSET=utf8;
b172472d
FB
81
82CREATE TABLE email_virtual (
87c89986 83 email VARCHAR(255) NOT NULL,
b1db6e48 84 domain SMALLINT(3) UNSIGNED NOT NULL DEFAULT 1,
87c89986 85 redirect VARCHAR(255) NOT NULL,
51c2c63a 86 type ENUM('alias', 'list', 'event', 'admin', 'partner'),
87c89986
SJ
87 expire DATE NOT NULL DEFAULT '0000-00-00',
88 PRIMARY KEY (email, domain, redirect),
b1db6e48
SJ
89 KEY (domain),
90 KEY (redirect),
91 FOREIGN KEY (domain) REFERENCES email_virtual_domains (id) ON UPDATE CASCADE ON DELETE CASCADE
87c89986 92) ENGINE=InnoDB, CHARSET=utf8;
b172472d
FB
93
94-- vim:set syntax=mysql: