X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;f=upgrade%2F1.1.0%2F01_new_mail_schema.sql;h=caca7d223cab2a2f4161e2cb43d7ea0e72ab3b74;hb=51c2c63a898801a7a6839f7b08313c12ddd1c47f;hp=773f46bae9afbc37e88669a86b4470dcfc9ee5a8;hpb=77de31c98f05be58870afbaa0cb6ba9f6cbb1799;p=platal.git diff --git a/upgrade/1.1.0/01_new_mail_schema.sql b/upgrade/1.1.0/01_new_mail_schema.sql index 773f46b..caca7d2 100644 --- a/upgrade/1.1.0/01_new_mail_schema.sql +++ b/upgrade/1.1.0/01_new_mail_schema.sql @@ -1,84 +1,94 @@ +DROP TABLE IF EXISTS email_redirect_account; +DROP TABLE IF EXISTS email_redirect_other; DROP TABLE IF EXISTS email_source_account; DROP TABLE IF EXISTS email_source_other; DROP TABLE IF EXISTS homonyms_list; -DROP TABLE IF EXISTS email_redirect_account; -DROP TABLE IF EXISTS email_redirect_other; DROP TABLE IF EXISTS email_virtual; DROP TABLE IF EXISTS email_virtual_domains; +CREATE TABLE email_virtual_domains ( + id SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT, + name VARCHAR(255) NOT NULL DEFAULT '', + aliasing SMALLINT(3) UNSIGNED NOT NULL DEFAULT 1, + PRIMARY KEY (id), + KEY (name), + FOREIGN KEY (aliasing) REFERENCES email_virtual_domains (id) ON UPDATE CASCADE ON DELETE CASCADE +) ENGINE=InnoDB, CHARSET=utf8; + CREATE TABLE email_source_account ( - email VARCHAR(255) NOT NULL PRIMARY KEY, - uid INT(11) UNSIGNED NOT NULL, - type ENUM('forlife','alias') NOT NULL DEFAULT 'forlife', - flags SET('bestalias','usage','marital') NOT NULL DEFAULT '', - expire DATE DEFAULT NULL, - KEY (uid), - KEY (type), - FOREIGN KEY (uid) REFERENCES accounts (uid) ON UPDATE CASCADE ON DELETE CASCADE -) ENGINE=InnoDB, CHARSET=utf8 ; + email VARCHAR(255) NOT NULL, + domain SMALLINT(3) UNSIGNED NOT NULL DEFAULT 1, + uid INT(11) UNSIGNED NOT NULL, + type ENUM('forlife', 'alias') NOT NULL DEFAULT 'forlife', + flags SET('bestalias', 'usage', 'marital') NOT NULL DEFAULT '', + expire DATE DEFAULT NULL, + PRIMARY KEY (email, domain), + KEY (domain), + KEY (uid), + KEY (type), + FOREIGN KEY (uid) REFERENCES accounts (uid) ON UPDATE CASCADE ON DELETE CASCADE, + FOREIGN KEY (domain) REFERENCES email_virtual_domains (id) ON UPDATE CASCADE ON DELETE CASCADE +) ENGINE=InnoDB, CHARSET=utf8; CREATE TABLE email_source_other ( - email VARCHAR(255) NOT NULL PRIMARY KEY, - hrmid VARCHAR(255) NOT NULL, - type ENUM('homonym','ax','honeypot'), - expire DATE NOT NULL DEFAULT '0000-00-00' -) ENGINE=InnoDB, CHARSET=utf8 ; + email VARCHAR(255) NOT NULL, + domain SMALLINT(3) UNSIGNED NOT NULL DEFAULT 1, + hrmid VARCHAR(255) NOT NULL DEFAULT '', + type ENUM('homonym', 'ax', 'honeypot'), + expire DATE DEFAULT NULL, + PRIMARY KEY (email, domain), + KEY (domain), + KEY(hrmid), + FOREIGN KEY (domain) REFERENCES email_virtual_domains (id) ON UPDATE CASCADE ON DELETE CASCADE +) ENGINE=InnoDB, CHARSET=utf8; CREATE TABLE homonyms_list ( - hrmid VARCHAR(255) NOT NULL, - uid INT(11) UNSIGNED NOT NULL, - KEY(hrmid) -) ENGINE=InnoDB, CHARSET=utf8 ; + hrmid VARCHAR(255) NOT NULL, + uid INT(11) UNSIGNED NOT NULL, + PRIMARY KEY (hrmid, uid), + key (uid), + FOREIGN KEY (uid) REFERENCES accounts (uid) ON UPDATE CASCADE ON DELETE CASCADE +) ENGINE=InnoDB, CHARSET=utf8; CREATE TABLE email_redirect_account ( - uid INT(11) UNSIGNED NOT NULL, - redirect VARCHAR(255) NOT NULL DEFAULT '', - rewrite VARCHAR(255) NOT NULL DEFAULT '', - type ENUM('smtp','googleapps','imap','homonym') NOT NULL DEFAULT 'smtp', - action ENUM('default', - 'drop_spams', - 'let_spams', - 'tag_and_drop_spams', - 'tag_spams', - 'imap_and_bounce', - 'homonym') - NOT NULL DEFAULT 'default', - broken DATE NOT NULL DEFAULT '0000-00-00', - broken_level TINYINT(1) NOT NULL DEFAULT 0, - last DATE NOT NULL DEFAULT '0000-00-00', - flags ENUM('active','broken','disabled') NOT NULL, - hash VARCHAR(32) DEFAULT NULL, - allow_rewrite TINYINT(1) DEFAULT 0, - KEY (uid), - KEY (redirect) -) ENGINE=InnoDB, CHARSET=utf8 ; + uid INT(11) UNSIGNED NOT NULL, + redirect VARCHAR(255) NOT NULL DEFAULT '', + rewrite VARCHAR(255) NOT NULL DEFAULT '', + type ENUM('smtp', 'googleapps', 'imap', 'homonym') NOT NULL DEFAULT 'smtp', + action ENUM('default', 'drop_spams', 'let_spams', 'tag_and_drop_spams', 'tag_spams', 'imap_and_bounce', 'homonym') NOT NULL DEFAULT 'default', + broken_date DATE NOT NULL DEFAULT '0000-00-00', + broken_level TINYINT(1) NOT NULL DEFAULT 0, + last DATE NOT NULL DEFAULT '0000-00-00', + flags ENUM('active', 'inactive', 'broken', 'disabled') NOT NULL, + hash VARCHAR(32) DEFAULT NULL, + allow_rewrite TINYINT(1) DEFAULT 0, + PRIMARY KEY (uid, redirect), + KEY (uid), + KEY (redirect), + FOREIGN KEY (uid) REFERENCES accounts (uid) ON UPDATE CASCADE ON DELETE CASCADE +) ENGINE=InnoDB, CHARSET=utf8; CREATE TABLE email_redirect_other ( - hrmid VARCHAR(255) NOT NULL DEFAULT '', - redirect VARCHAR(255) NOT NULL DEFAULT '', - type ENUM('smtp','googleapps','imap','homonym') NOT NULL DEFAULT 'smtp', - action ENUM('default', - 'drop_spams', - 'let_spams', - 'tag_and_drop_spams', - 'tag_spams', - 'imap_and_bounce', - 'homonym') - NOT NULL DEFAULT 'default', - KEY (hrmid), - KEY (redirect) -) ENGINE=InnoDB, CHARSET=utf8 ; + hrmid VARCHAR(255) NOT NULL DEFAULT '', + redirect VARCHAR(255) NOT NULL DEFAULT '', + type ENUM('smtp', 'googleapps', 'imap', 'homonym') NOT NULL DEFAULT 'smtp', + action ENUM('default', 'drop_spams', 'let_spams', 'tag_and_drop_spams', 'tag_spams', 'imap_and_bounce', 'homonym') NOT NULL DEFAULT 'default', + PRIMARY KEY (hrmid, redirect), + KEY (hrmid), + KEY (redirect), + FOREIGN KEY (hrmid) REFERENCES email_source_other (hrmid) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB, CHARSET=utf8; CREATE TABLE email_virtual ( - email VARCHAR(255) NOT NULL, - redirect VARCHAR(255) NOT NULL, - type ENUM('user','list','domain','event','admin','partner'), - expire DATE NOT NULL DEFAULT '0000-00-00', - KEY (email) -) ENGINE=InnoDB, CHARSET=utf8 ; - -CREATE TABLE email_virtual_domains ( - domain VARCHAR(255) NOT NULL PRIMARY KEY -) ENGINE=InnoDB, CHARSET=utf8; + email VARCHAR(255) NOT NULL, + domain SMALLINT(3) UNSIGNED NOT NULL DEFAULT 1, + redirect VARCHAR(255) NOT NULL, + type ENUM('alias', 'list', 'event', 'admin', 'partner'), + expire DATE NOT NULL DEFAULT '0000-00-00', + PRIMARY KEY (email, domain, redirect), + KEY (domain), + KEY (redirect), + FOREIGN KEY (domain) REFERENCES email_virtual_domains (id) ON UPDATE CASCADE ON DELETE CASCADE +) ENGINE=InnoDB, CHARSET=utf8; -- vim:set syntax=mysql: