| 1 | DROP TABLE IF EXISTS email_redirect_account; |
| 2 | DROP TABLE IF EXISTS email_redirect_other; |
| 3 | DROP TABLE IF EXISTS email_source_account; |
| 4 | DROP TABLE IF EXISTS email_source_other; |
| 5 | DROP TABLE IF EXISTS homonyms_list; |
| 6 | DROP TABLE IF EXISTS email_virtual; |
| 7 | DROP TABLE IF EXISTS email_virtual_domains; |
| 8 | |
| 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 | |
| 18 | CREATE TABLE email_source_account ( |
| 19 | email VARCHAR(255) NOT NULL, |
| 20 | domain SMALLINT(3) UNSIGNED NOT NULL DEFAULT 1, |
| 21 | uid INT(11) UNSIGNED NOT NULL, |
| 22 | type ENUM('forlife', 'alias', 'alias_aux') NOT NULL DEFAULT 'forlife', |
| 23 | flags SET('bestalias', 'usage', 'marital') NOT NULL DEFAULT '', |
| 24 | expire DATE DEFAULT NULL, |
| 25 | PRIMARY KEY (email, domain), |
| 26 | KEY (domain), |
| 27 | KEY (uid), |
| 28 | KEY (type), |
| 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 |
| 31 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 32 | |
| 33 | CREATE TABLE email_source_other ( |
| 34 | email VARCHAR(255) NOT NULL, |
| 35 | domain SMALLINT(3) UNSIGNED NOT NULL DEFAULT 1, |
| 36 | hrmid VARCHAR(255) NOT NULL DEFAULT '', |
| 37 | type ENUM('homonym', 'ax', 'honeypot'), |
| 38 | expire DATE DEFAULT NULL, |
| 39 | PRIMARY KEY (email, domain), |
| 40 | KEY (domain), |
| 41 | KEY(hrmid), |
| 42 | FOREIGN KEY (domain) REFERENCES email_virtual_domains (id) ON UPDATE CASCADE ON DELETE CASCADE |
| 43 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 44 | |
| 45 | CREATE TABLE homonyms_list ( |
| 46 | hrmid VARCHAR(255) NOT NULL, |
| 47 | uid INT(11) UNSIGNED NOT NULL, |
| 48 | PRIMARY KEY (hrmid, uid), |
| 49 | key (uid), |
| 50 | FOREIGN KEY (uid) REFERENCES accounts (uid) ON UPDATE CASCADE ON DELETE CASCADE |
| 51 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 52 | |
| 53 | CREATE TABLE email_redirect_account ( |
| 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, |
| 65 | PRIMARY KEY (uid, redirect), |
| 66 | KEY (uid), |
| 67 | KEY (redirect), |
| 68 | FOREIGN KEY (uid) REFERENCES accounts (uid) ON UPDATE CASCADE ON DELETE CASCADE |
| 69 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 70 | |
| 71 | CREATE TABLE email_redirect_other ( |
| 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', |
| 76 | PRIMARY KEY (hrmid, redirect), |
| 77 | KEY (hrmid), |
| 78 | KEY (redirect), |
| 79 | FOREIGN KEY (hrmid) REFERENCES email_source_other (hrmid) ON DELETE CASCADE ON UPDATE CASCADE |
| 80 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 81 | |
| 82 | CREATE TABLE email_virtual ( |
| 83 | email VARCHAR(255) NOT NULL, |
| 84 | domain SMALLINT(3) UNSIGNED NOT NULL DEFAULT 1, |
| 85 | redirect VARCHAR(255) NOT NULL, |
| 86 | type ENUM('alias', 'list', 'event', 'admin', 'partner'), |
| 87 | expire DATE NOT NULL DEFAULT '0000-00-00', |
| 88 | PRIMARY KEY (email, domain, redirect), |
| 89 | KEY (domain), |
| 90 | KEY (redirect), |
| 91 | FOREIGN KEY (domain) REFERENCES email_virtual_domains (id) ON UPDATE CASCADE ON DELETE CASCADE |
| 92 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 93 | |
| 94 | -- vim:set syntax=mysql: |