+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';
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'
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)
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"
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"),
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:
projects / platal.git / blobdiff