From 87c89986bb0522df966cdccb020421eb31de2913 Mon Sep 17 00:00:00 2001 From: =?utf8?q?St=C3=A9phane=20Jacob?= Date: Mon, 17 Jan 2011 19:54:15 +0100 Subject: [PATCH] Identation for new mail scheme update script. MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Signed-off-by: Stéphane Jacob --- upgrade/1.1.0/01_new_mail_schema.sql | 106 +++++++++++------------- upgrade/1.1.0/02_new_mail_insertion.sql | 140 ++++++++++++++++---------------- 2 files changed, 116 insertions(+), 130 deletions(-) diff --git a/upgrade/1.1.0/01_new_mail_schema.sql b/upgrade/1.1.0/01_new_mail_schema.sql index e99908f..15756f7 100644 --- a/upgrade/1.1.0/01_new_mail_schema.sql +++ b/upgrade/1.1.0/01_new_mail_schema.sql @@ -7,83 +7,69 @@ DROP TABLE IF EXISTS email_virtual; DROP TABLE IF EXISTS email_virtual_domains; 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), + 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 ; +) 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 PRIMARY KEY, + hrmid 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 ( - hrmid VARCHAR(255) NOT NULL, - uid INT(11) UNSIGNED NOT NULL, + hrmid VARCHAR(255) NOT NULL, + uid INT(11) UNSIGNED NOT NULL, PRIMARY KEY (hrmid, uid), FOREIGN KEY (uid) REFERENCES accounts (uid) ON UPDATE CASCADE ON DELETE CASCADE -) ENGINE=InnoDB, CHARSET=utf8 ; +) 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 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, + 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), + KEY (uid), + KEY (redirect), FOREIGN KEY (uid) REFERENCES accounts (uid) ON UPDATE CASCADE ON DELETE CASCADE -) ENGINE=InnoDB, CHARSET=utf8 ; +) 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', + 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) -) ENGINE=InnoDB, CHARSET=utf8 ; + KEY (hrmid), + KEY (redirect) +) 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', - PRIMARY KEY (email, redirect), - KEY (email) -) ENGINE=InnoDB, CHARSET=utf8 ; + 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', + PRIMARY KEY (email, domain, redirect), + KEY (email) +) ENGINE=InnoDB, CHARSET=utf8; CREATE TABLE email_virtual_domains ( - domain VARCHAR(255) NOT NULL PRIMARY KEY -) ENGINE=InnoDB, CHARSET=utf8; + domain VARCHAR(255) NOT NULL PRIMARY KEY +) ENGINE=InnoDB, CHARSET=utf8; -- vim:set syntax=mysql: diff --git a/upgrade/1.1.0/02_new_mail_insertion.sql b/upgrade/1.1.0/02_new_mail_insertion.sql index 99aa98c..effcbb1 100644 --- a/upgrade/1.1.0/02_new_mail_insertion.sql +++ b/upgrade/1.1.0/02_new_mail_insertion.sql @@ -1,108 +1,108 @@ INSERT INTO email_source_account (uid, email, type, flags, expire) - SELECT uid, CONCAT(alias, '@polytechnique.org'), IF(type = 'a_vie', 'forlife', 'alias'), REPLACE(flags, 'epouse', 'marital'), expire - FROM aliases - WHERE type = 'a_vie' OR type = 'alias'; + SELECT uid, CONCAT(alias, '@polytechnique.org'), IF(type = 'a_vie', 'forlife', 'alias'), REPLACE(flags, 'epouse', 'marital'), expire + FROM aliases + WHERE type = 'a_vie' OR type = 'alias'; INSERT INTO email_source_account (uid, email, type, flags, expire) - SELECT uid, CONCAT(alias, '@m4x.org'), IF(type = 'a_vie', 'forlife', 'alias'), REPLACE(flags, 'epouse', 'marital'), expire - FROM aliases - WHERE type = 'a_vie' OR type = 'alias'; + SELECT uid, CONCAT(alias, '@m4x.org'), IF(type = 'a_vie', 'forlife', 'alias'), REPLACE(flags, 'epouse', 'marital'), expire + FROM aliases + WHERE type = 'a_vie' OR type = 'alias'; INSERT INTO email_source_account (uid, email, type) - SELECT a.uid, v.alias, 'alias' - FROM virtual AS v - LEFT JOIN virtual_redirect AS vr ON (v.vid = vr.vid) - LEFT JOIN accounts AS a ON (a.hruid = LEFT(vr.redirect, LOCATE('@', vr.redirect)-1)) - WHERE v.type = 'user' AND v.alias LIKE '%@melix.net' AND a.uid IS NOT NULL; + SELECT a.uid, v.alias, 'alias' + FROM virtual AS v + LEFT JOIN virtual_redirect AS vr ON (v.vid = vr.vid) + LEFT JOIN accounts AS a ON (a.hruid = LEFT(vr.redirect, LOCATE('@', vr.redirect)-1)) + WHERE v.type = 'user' AND v.alias LIKE '%@melix.net' AND a.uid IS NOT NULL; INSERT INTO email_source_account (uid, email, type) - SELECT a.uid, REPLACE(v.alias, '@melix.net', '@melix.org'), 'alias' - FROM virtual AS v - LEFT JOIN virtual_redirect AS vr ON (v.vid = vr.vid) - LEFT JOIN accounts AS a ON (a.hruid = LEFT(vr.redirect, LOCATE('@', vr.redirect)-1)) - WHERE v.type = 'user' AND v.alias LIKE '%@melix.net' AND a.uid IS NOT NULL; + SELECT a.uid, REPLACE(v.alias, '@melix.net', '@melix.org'), 'alias' + FROM virtual AS v + LEFT JOIN virtual_redirect AS vr ON (v.vid = vr.vid) + LEFT JOIN accounts AS a ON (a.hruid = LEFT(vr.redirect, LOCATE('@', vr.redirect)-1)) + WHERE v.type = 'user' AND v.alias LIKE '%@melix.net' AND a.uid IS NOT NULL; INSERT INTO email_source_other (hrmid, email, type) - SELECT CONCAT(CONCAT('h.', alias), '.polytechnique.org'), CONCAT(alias, '@polytechnique.org'), 'homonym' - FROM aliases - WHERE type = 'homonyme' - GROUP BY (alias); + SELECT CONCAT(CONCAT('h.', alias), '.polytechnique.org'), CONCAT(alias, '@polytechnique.org'), 'homonym' + FROM aliases + WHERE type = 'homonyme' + GROUP BY (alias); INSERT INTO email_source_other (hrmid, email, type) - SELECT CONCAT(CONCAT('h.', alias), '.polytechnique.org'), CONCAT(alias, '@m4x.org'), 'homonym' - FROM aliases - WHERE type = 'homonyme' - GROUP BY (alias); + SELECT CONCAT(CONCAT('h.', alias), '.polytechnique.org'), CONCAT(alias, '@m4x.org'), 'homonym' + FROM aliases + WHERE type = 'homonyme' + GROUP BY (alias); INSERT INTO homonyms_list (hrmid, uid) - SELECT CONCAT(CONCAT('h.', a.alias), '.polytechnique.org'), h.uid - FROM homonyms AS h + 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'; + WHERE a.type = 'homonyme'; INSERT INTO email_redirect_account (uid, redirect, rewrite, type, action, broken_date, broken_level, last, flags, hash, allow_rewrite) - SELECT a.uid, e.email, e.rewrite, 'smtp', ef.email, e.panne, e.panne_level, e.last, + SELECT a.uid, e.email, e.rewrite, 'smtp', ef.email, e.panne, e.panne_level, e.last, IF(e.flags = '', 'inactive', 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) - WHERE e.flags != 'filter' AND ef.flags = 'filter'; + FROM emails AS e + LEFT JOIN emails AS ef ON (e.uid = ef.uid) + LEFT JOIN accounts AS a ON (e.uid = a.uid) + WHERE e.flags != 'filter' AND ef.flags = 'filter'; INSERT INTO email_redirect_account (uid, redirect, type, action, flags) - SELECT a.uid, CONCAT(a.hruid, '@g.polytechnique.org'), 'googleapps', ef.email, 'active' - FROM email_options AS eo - LEFT JOIN accounts AS a ON (a.uid = eo.uid) - LEFT JOIN emails AS ef ON (eo.uid = ef.uid) - WHERE FIND_IN_SET('googleapps', eo.storage) AND ef.flags = 'filter'; + SELECT a.uid, CONCAT(a.hruid, '@g.polytechnique.org'), 'googleapps', ef.email, 'active' + FROM email_options AS eo + LEFT JOIN accounts AS a ON (a.uid = eo.uid) + LEFT JOIN emails AS ef ON (eo.uid = ef.uid) + WHERE FIND_IN_SET('googleapps', eo.storage) AND ef.flags = 'filter'; INSERT INTO email_redirect_account (uid, redirect, type, action, flags) - SELECT a.uid, CONCAT(a.hruid, '@imap.polytechnique.org'), 'imap', 'let_spams', 'active' - FROM email_options AS eo - LEFT JOIN accounts AS a ON (a.uid = eo.uid) - WHERE FIND_IN_SET('imap', eo.storage); + SELECT a.uid, CONCAT(a.hruid, '@imap.polytechnique.org'), 'imap', 'let_spams', 'active' + FROM email_options AS eo + LEFT JOIN accounts AS a ON (a.uid = eo.uid) + WHERE FIND_IN_SET('imap', eo.storage); INSERT INTO email_redirect_other (hrmid, type, action) - SELECT hrmid, 'homonym', 'homonym' - FROM email_source_other - WHERE type = 'homonym' - GROUP BY (hrmid); + SELECT hrmid, 'homonym', 'homonym' + FROM email_source_other + WHERE type = 'homonym' + GROUP BY (hrmid); INSERT INTO email_virtual (email, redirect, 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' AND vr.vid IS NOT NULL AND v.alias != '@melix.org'; + 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' AND vr.vid IS NOT NULL AND v.alias != '@melix.org'; INSERT INTO email_virtual (email, type, redirect) - SELECT CONCAT(alias, '@polytechnique.org'), 'list', - CONCAT('polytechnique.org_', REPLACE(REPLACE(REPLACE(CONCAT(alias, '+post@listes.polytechnique.org'), + SELECT CONCAT(alias, '@polytechnique.org'), 'list', + CONCAT('polytechnique.org_', REPLACE(REPLACE(REPLACE(CONCAT(alias, '+post@listes.polytechnique.org'), '-admin+post', '+admin'), '-owner+post', '+owner'), '-bounces+post', '+bounces')) - FROM aliases - WHERE type = 'liste'; + FROM aliases + WHERE type = 'liste'; INSERT INTO email_virtual (email, type, redirect) - SELECT CONCAT(alias, '@m4x.org'), 'list', - CONCAT('polytechnique.org_', REPLACE(REPLACE(REPLACE(CONCAT(alias, '+post@listes.polytechnique.org'), + SELECT CONCAT(alias, '@m4x.org'), 'list', + CONCAT('polytechnique.org_', REPLACE(REPLACE(REPLACE(CONCAT(alias, '+post@listes.polytechnique.org'), '-admin+post', '+admin'), '-owner+post', '+owner'), '-bounces+post', '+bounces')) - FROM aliases - WHERE type = 'liste'; + FROM aliases + WHERE type = 'liste'; INSERT INTO email_virtual (email, redirect, type) - SELECT v.alias, vr.redirect, 'user' - FROM virtual AS v - LEFT JOIN virtual_redirect AS vr ON (v.vid = vr.vid) - LEFT JOIN accounts AS a ON (a.hruid = LEFT(vr.redirect, LOCATE('@', vr.redirect)-1)) - WHERE v.type = 'user' AND v.alias LIKE '%@melix.net' AND vr.vid IS NOT NULL AND a.uid IS NULL; + SELECT v.alias, vr.redirect, 'user' + FROM virtual AS v + LEFT JOIN virtual_redirect AS vr ON (v.vid = vr.vid) + LEFT JOIN accounts AS a ON (a.hruid = LEFT(vr.redirect, LOCATE('@', vr.redirect)-1)) + WHERE v.type = 'user' AND v.alias LIKE '%@melix.net' AND vr.vid IS NOT NULL AND a.uid IS NULL; INSERT INTO email_virtual (email, redirect, type) - SELECT REPLACE(v.alias, '@melix.net', '@melix.org'), vr.redirect, 'user' - FROM virtual AS v - LEFT JOIN virtual_redirect AS vr ON (v.vid = vr.vid) - LEFT JOIN accounts AS a ON (a.hruid = LEFT(vr.redirect, LOCATE('@', vr.redirect)-1)) - WHERE v.type = 'user' AND v.alias LIKE '%@melix.net' AND vr.vid IS NOT NULL AND a.uid IS NULL; + SELECT REPLACE(v.alias, '@melix.net', '@melix.org'), vr.redirect, 'user' + FROM virtual AS v + LEFT JOIN virtual_redirect AS vr ON (v.vid = vr.vid) + LEFT JOIN accounts AS a ON (a.hruid = LEFT(vr.redirect, LOCATE('@', vr.redirect)-1)) + WHERE v.type = 'user' AND v.alias LIKE '%@melix.net' AND vr.vid IS NOT NULL AND a.uid IS NULL; -- Note: There are some adresses on virtual that have no match on the virtual_redirect. -- The adresses in this situation are dropped. INSERT INTO email_virtual_domains (domain) - VALUES ('polytechnique.org'), ('m4x.org'); + VALUES ('polytechnique.org'), ('m4x.org'); INSERT INTO email_virtual_domains (domain) - SELECT domain - FROM virtual_domains; + SELECT domain + FROM virtual_domains; -- From aliases file INSERT INTO email_virtual (email, redirect, type) -- 2.1.4