Commit | Line | Data |
---|---|---|
77c9b1d3 SJ |
1 | DROP TABLE IF EXISTS email_redirect_account; |
2 | DROP TABLE IF EXISTS email_redirect_other; | |
b172472d FB |
3 | DROP TABLE IF EXISTS email_source_account; |
4 | DROP TABLE IF EXISTS email_source_other; | |
5 | DROP TABLE IF EXISTS homonyms_list; | |
b172472d FB |
6 | DROP TABLE IF EXISTS email_virtual; |
7 | DROP TABLE IF EXISTS email_virtual_domains; | |
8 | ||
b1db6e48 SJ |
9 | CREATE 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 | 18 | CREATE 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 | |
33 | CREATE 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 | |
45 | CREATE 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 | |
53 | CREATE 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 | |
71 | CREATE 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 | |
82 | CREATE 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: |