X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;f=upgrade%2F1.1.0%2F01_new_mail.sql;h=a77c34e649fdccbc7be2e9c18fe93909f9150366;hb=b9dd7517201431e1590829a3ab292545d4c3d016;hp=3e8ec83bee0b02e05391aaafe2179b7838a393e9;hpb=18f11744795abac7cbffc5fd967b64c55bf9d30d;p=platal.git diff --git a/upgrade/1.1.0/01_new_mail.sql b/upgrade/1.1.0/01_new_mail.sql index 3e8ec83..a77c34e 100644 --- a/upgrade/1.1.0/01_new_mail.sql +++ b/upgrade/1.1.0/01_new_mail.sql @@ -1,25 +1,81 @@ +DROP TABLE IF EXISTS email_account_emails; +DROP TABLE IF EXISTS email_nonaccount_emails; +DROP TABLE IF EXISTS homonyms_list; +DROP TABLE IF EXISTS email_redirect; +DROP TABLE IF EXISTS email_virtual; +DROP TABLE IF EXISTS email_virtual_domains; + CREATE TABLE email_account_emails ( email VARCHAR(255) NOT NULL PRIMARY KEY, uid INT(11) UNSIGNED NOT NULL, - type ENUM('a_vie','alias') NOT NULL DEFAULT 'a_vie', - flags SET('bestalias','usage','epouse') NOT NULL DEFAULT '', + type ENUM('forlife','alias') NOT NULL DEFAULT 'forlife', + flags SET('bestalias','usage','marital') NOT NULL DEFAULT '', expire DATE DEFAULT NULL, KEY (uid), - KEY (type) + KEY (type), + FOREIGN KEY uid REFERENCES accounts (uid) ON UPDATE CASCADE ON DELETE CASCADE; +) ENGINE=InnoDB, CHARSET=utf8 ; + +CREATE TABLE email_nonaccount_emails ( + email VARCHAR(255) NOT NULL PRIMARY KEY, + pfix_hruid VARCHAR(255) NOT NULL, + type ENUM('homonym','ax','honeypot'), + expire DATE NOT NULL DEFAULT '0000-00-00' ) ENGINE=InnoDB, CHARSET=utf8 ; +CREATE TABLE homonyms_list ( + pfix_hruid VARCHAR(255) NOT NULL, + uid INT(11) UNSIGNED NOT NULL, + KEY(pfix_hruid) +) ENGINE=InnoDB, CHARSET=utf8 ; + +CREATE TABLE email_redirect ( + pfix_hruid VARCHAR(255) NOT NULL DEFAULT '', + redirect VARCHAR(255) NOT NULL DEFAULT '', + rewrite VARCHAR(255) NOT NULL DEFAULT '', + type ENUM('smtp','googleapps','imap','homonyms') NOT NULL DEFAULT 'smtp', + action ENUM('default', + 'drop_spams', + 'let_spams', + 'tag_and_drop_spams', + 'tag_spams', + '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 (pfix_hruid), + KEY (redirect) +) ENGINE=InnoDB, CHARSET=utf8 ; + +CREATE TABLE email_virtual ( + alias 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 (alias) +) ENGINE=InnoDB, CHARSET=utf8 ; + +CREATE TABLE email_virtual_domains ( + domain VARCHAR(255) NOT NULL PRIMARY KEY +) ENGINE=InnoDB, CHARSET=utf8 ; + + INSERT INTO email_account_emails (uid,email,type,flags,expire) - SELECT a.uid,CONCAT(a.alias,'@polytechnique.org'),a.type,a.flags,a.expire + SELECT a.uid,CONCAT(a.alias,'@polytechnique.org'),IF(a.type='a_vie','forlife','alias'),IF(a.flags='epouse','marital',a.flags),a.expire FROM aliases AS a WHERE a.type = 'a_vie' OR a.type = 'alias'; INSERT INTO email_account_emails (uid,email,type,flags,expire) - SELECT a.uid,CONCAT(a.alias,'@m4x.org'),a.type,a.flags,a.expire + SELECT a.uid,CONCAT(a.alias,'@m4x.org'),IF(a.type='a_vie','forlife','alias'),IF(a.flags='epouse','marital',a.flags),a.expire FROM aliases AS a WHERE a.type = 'a_vie' OR a.type = 'alias'; INSERT INTO email_account_emails (uid,email,type,flags,expire) - SELECT a.uid,CONCAT(a.alias,'@m4x.net'),a.type,a.flags,a.expire + SELECT a.uid,CONCAT(a.alias,'@m4x.net'),IF(a.type='a_vie','forlife','alias'),IF(a.flags='epouse','marital',a.flags),a.expire FROM aliases AS a WHERE a.type = 'a_vie' OR a.type = 'alias'; @@ -40,13 +96,6 @@ INSERT INTO email_account_emails (uid,email,type) AND v.alias LIKE "%@melix.net" AND a.uid IS NOT NULL; -CREATE TABLE email_nonaccount_emails ( - email VARCHAR(255) NOT NULL PRIMARY KEY, - pfix_hruid VARCHAR(255) NOT NULL, - type ENUM('homonym','ax','honeypot'), - expire DATE NOT NULL DEFAULT '0000-00-00' -) ENGINE=InnoDB, CHARSET=utf8 ; - INSERT INTO email_nonaccount_emails (pfix_hruid,email,type) SELECT CONCAT(CONCAT('h.',a.alias),'.polytechnique.org'), CONCAT(a.alias,'@polytechnique.org'),'homonym' @@ -66,44 +115,16 @@ INSERT INTO email_nonaccount_emails (pfix_hruid,email,type) WHERE a.type = 'homonyme' GROUP BY (a.alias); -CREATE TABLE homonyms_list ( - pfix_hruid VARCHAR(255) NOT NULL, - uid INT(11) UNSIGNED NOT NULL, - KEY(pfix_hruid) -) ENGINE=InnoDB, CHARSET=utf8 ; - INSERT INTO homonyms_list (pfix_hruid,uid) SELECT CONCAT(CONCAT('h.',a.alias),'.polytechnique.org'), h.uid FROM homonyms AS h INNER JOIN aliases AS a ON (a.uid=h.homonyme_id) WHERE a.type = 'homonyme'; -CREATE TABLE email_redirect ( - pfix_hruid VARCHAR(255) NOT NULL DEFAULT '', - redirect VARCHAR(255) NOT NULL DEFAULT '', - rewrite VARCHAR(255) NOT NULL DEFAULT '', - type ENUM('smtp','googleapps','imap','homonyms') NOT NULL DEFAULT 'smtp', - action ENUM('default', - 'drop_spams', - 'let_spams', - 'tag_and_drop_spams', - 'tag_spams', - 'homonym') - NOT NULL DEFAULT 'default', - panne DATE NOT NULL DEFAULT '0000-00-00', - panne_level TINYINT(1) NOT NULL DEFAULT 0, - last DATE NOT NULL DEFAULT '0000-00-00', - flags ENUM('active','panne','disable') NOT NULL, - hash VARCHAR(32) DEFAULT NULL, - allow_rewrite TINYINT(1) DEFAULT 0, - KEY (pfix_hruid), - KEY (redirect) -) ENGINE=InnoDB, CHARSET=utf8 ; - INSERT INTO email_redirect (pfix_hruid,redirect,rewrite,type,action, - panne,panne_level,last,flags,hash,allow_rewrite) + broken,broken_level,last,flags,hash,allow_rewrite) SELECT a.hruid,e.email,e.rewrite,'smtp',ef.email, - e.panne,e.panne_level,e.last,e.flags,e.hash,e.allow_rewrite + e.panne,e.panne_level,e.last,IF(e.flags='disable','disabled',IF(e.flags='panne','broken',e.flags)),e.hash,e.allow_rewrite FROM emails AS e LEFT JOIN emails AS ef ON (e.uid=ef.uid) LEFT JOIN accounts AS a ON (e.uid=a.uid) @@ -132,16 +153,8 @@ INSERT INTO email_redirect (pfix_hruid,redirect,type,action,flags) LEFT JOIN accounts AS a ON (a.uid=eo.uid) WHERE FIND_IN_SET('imap',eo.storage); -CREATE TABLE email_virtual ( - alias VARCHAR(255) NOT NULL, - redirect VARCHAR(255) NOT NULL, - type ENUM('user','list','dom','evt','admin','partner'), - expire DATE NOT NULL DEFAULT '0000-00-00', - KEY (alias) -) ENGINE=InnoDB, CHARSET=utf8 ; - INSERT INTO email_virtual (alias,redirect,type) - SELECT v.alias,vr.redirect,v.type + SELECT v.alias,vr.redirect,IF(v.type='dom','domain',IF(v.type='evt','event',v.type)) FROM virtual AS v LEFT JOIN virtual_redirect AS vr ON (vr.vid=v.vid) WHERE v.alias NOT LIKE "%@melix.net" @@ -224,10 +237,6 @@ Note: There are some adresses on virtual that have no match on the virtual_redir in this situation are dropped. */ -CREATE TABLE email_virtual_domains ( - domain VARCHAR(255) NOT NULL PRIMARY KEY -) ENGINE=InnoDB, CHARSET=utf8 ; - INSERT INTO email_virtual_domains (domain) VALUES ("polytechnique.org"), ("m4x.org"), @@ -440,3 +449,5 @@ INSERT INTO email_redirect (pfix_hruid,redirect,type,action,flags) VALUES DELETE FROM email_virtual WHERE alias LIKE "tech-email%@polytechnique.org"; DELETE FROM email_virtual WHERE alias LIKE "tech-email%@m4x.org"; DELETE FROM email_virtual WHERE alias LIKE "tech-email%@m4x.net"; + +-- vim:set syntax=mysql: