X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;f=upgrade%2F1.1.0%2F02_new_mail_insertion.sql;h=a51987c7b7bde5c66d93419f30cbb949479a1f06;hb=1773c63489f9bf9b3ed3a96b97f3d4f176400fa4;hp=effcbb169ccb0b47c2aba5b3849371d2418c0cd4;hpb=a43cdc7aab599efc9ebc698e91c131d3a40d6bee;p=platal.git diff --git a/upgrade/1.1.0/02_new_mail_insertion.sql b/upgrade/1.1.0/02_new_mail_insertion.sql index effcbb1..a51987c 100644 --- a/upgrade/1.1.0/02_new_mail_insertion.sql +++ b/upgrade/1.1.0/02_new_mail_insertion.sql @@ -1,47 +1,110 @@ -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'; -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 +-- 1/ Feeds email_virtual_domains from virtual_domains and aliases. + +INSERT INTO email_virtual_domains (name) + VALUES ('polytechnique.org'); +INSERT INTO email_virtual_domains (name) + SELECT domain + FROM virtual_domains; +UPDATE email_virtual_domains + SET aliasing = id; + +SET @p_domain_id = 0; +SET @m_domain_id = 0; +SELECT @p_domain_id := id + FROM email_virtual_domains + WHERE name = 'polytechnique.org'; +SELECT @m_domain_id := id + FROM email_virtual_domains + WHERE name = 'melix.net'; + +DELETE FROM email_virtual_domains + WHERE name IN ('m4x.org', 'melix.org', 'x-banque.m4x.org', 'staff.m4x.org', 'x-consult.m4x.org'); +INSERT INTO email_virtual_domains (name, aliasing) + SELECT 'm4x.org', id + FROM email_virtual_domains + WHERE name = 'polytechnique.org'; +INSERT INTO email_virtual_domains (name, aliasing) + SELECT 'melix.org', id + FROM email_virtual_domains + WHERE name = 'melix.net'; +INSERT INTO email_virtual_domains (name, aliasing) + SELECT 'x-banque.m4x.org', id + FROM email_virtual_domains + WHERE name = 'x-banque.polytechnique.org'; +INSERT INTO email_virtual_domains (name, aliasing) + SELECT 'staff.m4x.org', id + FROM email_virtual_domains + WHERE name = 'staff.polytechnique.org'; +INSERT INTO email_virtual_domains (name, aliasing) + SELECT 'x-consult.m4x.org', id + FROM email_virtual_domains + WHERE name = 'x-consult.polytechnique.org'; + +INSERT INTO email_virtual_domains (name, aliasing) + VALUES ('alumni.polytechnique.org', @p_domain_id), ('alumni.m4x.org', @p_domain_id), + ('master.polytechnique.org', 1), ('doc.polytechnique.org', 1); +SET @master_domain_id = 0; +SET @doc_domain_id = 0; +SELECT @master_domain_id := id + FROM email_virtual_domains + WHERE name = 'master.polytechnique.org'; +SELECT @doc_domain_id := id + FROM email_virtual_domains + WHERE name = 'doc.polytechnique.org'; +UPDATE email_virtual_domains + SET aliasing = @master_domain_id + WHERE name = 'master.polytechnique.org'; +UPDATE email_virtual_domains + SET aliasing = @doc_domain_id + WHERE name = 'doc.polytechnique.org'; +INSERT INTO email_virtual_domains (name, aliasing) + VALUES ('alumni.polytechnique.org', @master_domain_id), ('alumni.m4x.org', @master_domain_id), + ('alumni.polytechnique.org', @doc_domain_id), ('alumni.m4x.org', @doc_domain_id), + ('master.m4x.org', @master_domain_id), ('doc.m4x.org', @doc_domain_id); + +-- 2/ Feeds email_source_account +INSERT INTO email_source_account (uid, domain, email, type, flags, expire) + SELECT uid, @p_domain_id, alias, 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; -INSERT INTO email_source_account (uid, email, type) - SELECT a.uid, REPLACE(v.alias, '@melix.net', '@melix.org'), 'alias' +INSERT INTO email_source_account (uid, domain, email, type) + SELECT a.uid, @m_domain_id, SUBSTRING_INDEX(v.alias, '@', 1), 'alias_aux' 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)) + 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' +-- 3/ Feeds email_source_other +INSERT INTO email_source_other (hrmid, email, domain, type, expire) + SELECT CONCAT(CONCAT('h.', alias), '.polytechnique.org'), alias, @p_domain_id, 'homonym', IF(expire IS NULL, '0000-00-00', expire) 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); + GROUP BY alias; +INSERT INTO email_source_other (hrmid, email, type, domain) + VALUES ('ax.test.polytechnique.org', 'AX-test', 'ax', @p_domain_id), + ('ax.nicolas.zarpas.polytechnique.org', 'AX-nicolas.zarpas', 'ax', @p_domain_id), + ('ax.carrieres.polytechnique.org', 'AX-carrieres', 'ax', @p_domain_id), + ('ax.info1.polytechnique.org', 'AX-info1', 'ax', @p_domain_id), + ('ax.info2.polytechnique.org', 'AX-info2', 'ax', @p_domain_id), + ('ax.bal.polytechnique.org', 'AX-bal', 'ax', @p_domain_id), + ('ax.annuaire.polytechnique.org', 'AX-annuaire', 'ax', @p_domain_id), + ('ax.jaune-rouge.polytechnique.org', 'AX-jaune-rouge', 'ax', @p_domain_id), + ('honey.jean-pierre.bilah.1980.polytechnique.org', 'jean-pierre.bilah.1980', 'honeypot', @p_domain_id), + ('honey.jean-pierre.bilah.1980.polytechnique.org', 'jean-pierre.blah.1980', 'honeypot', @p_domain_id); +-- 4/ Feeds homonyms_list INSERT INTO homonyms_list (hrmid, 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'; +-- 5/ Feeds email_redirect_account 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 e.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'; INSERT INTO email_redirect_account (uid, redirect, type, action, flags) SELECT a.uid, CONCAT(a.hruid, '@g.polytechnique.org'), 'googleapps', ef.email, 'active' @@ -55,204 +118,124 @@ INSERT INTO email_redirect_account (uid, redirect, type, action, flags) LEFT JOIN accounts AS a ON (a.uid = eo.uid) WHERE FIND_IN_SET('imap', eo.storage); +-- Imap and bounce +UPDATE email_redirect_account AS e, + (SELECT IF(SUM(IF(type != 'imap', 1, 0)) = 0, 'imap_only', 'normal') AS status, uid + FROM email_redirect_account + WHERE flags = 'active' + GROUP BY uid) AS sub + SET e.action = 'imap_and_bounce' + WHERE sub.status = 'imap_only' AND sub.uid = e.uid AND type = 'imap'; + +-- 6/ Feeds email_redirect_other INSERT INTO email_redirect_other (hrmid, type, action) 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'; -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'), - '-admin+post', '+admin'), - '-owner+post', '+owner'), - '-bounces+post', '+bounces')) - FROM aliases - WHERE type = 'liste'; -INSERT INTO email_virtual (email, type, redirect) - SELECT CONCAT(alias, '@m4x.org'), 'list', +INSERT INTO email_redirect_other (hrmid, redirect, type, action) + VALUES ('ax.nicolas.zarpas.polytechnique.org', 'nicolas.zarpas-ax@wanadoo.fr', 'smtp', 'tag_spams'), + ('ax.carrieres.polytechnique.org', 'manuela.brasseur-bdc@wanadoo.fr', 'smtp', 'tag_spams'), + ('ax.info1.polytechnique.org', 'sylvie.clairefond-ax@wanadoo.fr', 'smtp', 'tag_spams'), + ('ax.info2.polytechnique.org', 'catherine.perot-ax@wanadoo.fr', 'smtp', 'tag_spams'), + ('ax.bal.polytechnique.org', 'baldelx-ax@wanadoo.fr', 'smtp', 'tag_spams'), + ('ax.annuaire.polytechnique.org', 'annuaire-ax@wanadoo.fr', 'smtp', 'tag_spams'), + ('ax.jaune-rouge.polytechnique.org', 'jaune_rouge@wanadoo.fr', 'smtp', 'tag_spams'), + ('honey.jean-pierre.bilah.1980.polytechnique.org', 'jean-pierre.bilah.1980.mbox@murphy.m4x.org', 'smtp', 'let_spams'), + ('honey.jean-pierre.bilah.1980.polytechnique.org', 'raphael.barrois.2006@polytechnique.org', 'smtp', 'let_spams'); + +-- 7/ Feeds email_virtual + +-- 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 (email, domain, redirect, type) + SELECT SUBSTRING_INDEX(v.alias, '@', 1), d.id, vr.redirect, IF(v.type = 'evt', 'event', v.type) + FROM virtual AS v + INNER JOIN email_virtual_domains AS d ON (SUBSTRING_INDEX(v.alias, '@', -1) = d.name AND d.id = d.aliasing) + 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.type != 'dom'; + +INSERT INTO email_virtual (email, type, domain, redirect) + SELECT alias, 'list', @p_domain_id, 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'; -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; -INSERT INTO email_virtual (email, redirect, type) - SELECT REPLACE(v.alias, '@melix.net', '@melix.org'), vr.redirect, 'user' + +INSERT INTO email_virtual (email, redirect, domain, type) + SELECT SUBSTRING_INDEX(v.alias, '@', 1), vr.redirect, @m_domain_id, '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)) + 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'); -INSERT INTO email_virtual_domains (domain) - SELECT domain - FROM virtual_domains; - -- From aliases file -INSERT INTO email_virtual (email, redirect, type) - VALUES ('otrs.platal@polytechnique.org', 'otrs@svoboda.polytechnique.org', 'admin'), - ('otrs.platal@m4x.org', 'otrs.platal@polytechnique.org', 'admin'), - ('validation@polytechnique.org', 'hotliners@staff.polytechnique.org', 'admin'), - ('validation@m4x.org', 'validation@polytechnique.org', 'admin'), - ('listes+admin@polytechnique.org', 'br@staff.polytechnique.org', 'admin'), - ('listes+admin@m4x.org', 'listes+admin@polytechnique.org', 'admin'), - ('listes@polytechnique.org', 'otrs.platal+listes@polytechnique.org', 'admin'), - ('listes@m4x.org', 'listes@polytechnique.org', 'admin'), - ('gld@polytechnique.org', 'listes@polytechnique.org', 'admin'), - ('gld@m4x.org', 'gld@polytechnique.org', 'admin'), - ('support@polytechnique.org', 'otrs.platal+support@polytechnique.org', 'admin'), - ('support@m4x.org', 'support@polytechnique.org', 'admin'), - ('contact@polytechnique.org', 'otrs.platal+contact@polytechnique.org', 'admin'), - ('contact@m4x.org', 'contact@polytechnique.org', 'admin'), - ('register@polytechnique.org', 'otrs.platal+register@polytechnique.org', 'admin'), - ('register@m4x.org', 'register@polytechnique.org', 'admin'), - ('info@polytechnique.org', 'otrs.platal+info@polytechnique.org', 'admin'), - ('info@m4x.org', 'info@polytechnique.org', 'admin'), - ('bug@polytechnique.org', 'otrs.platal+bug@polytechnique.org', 'admin'), - ('bug@m4x.org', 'bug@polytechnique.org', 'admin'), - ('resetpass@polytechnique.org', 'otrs.platal+resetpass@polytechnique.org', 'admin'), - ('resetpass@m4x.org', 'resetpass@polytechnique.org', 'admin'), - ('association@polytechnique.org', 'otrs.platal+association@polytechnique.org', 'admin'), - ('association@m4x.org', 'association@polytechnique.org', 'admin'), - ('x-org@polytechnique.org', 'association@polytechnique.org', 'admin'), - ('x-org@m4x.org', 'x-org@polytechnique.org', 'admin'), - ('manageurs@polytechnique.org', 'otrs@support.manageurs.com', 'partner'), - ('manageurs@m4x.org', 'manageurs@polytechnique.org', 'partner'), - ('fondation@polytechnique.org', 'fondation@fondationx.org', 'partner'), - ('fondation@m4x.org', 'fondation@polytechnique.org', 'partner'), - ('ax@polytechnique.org', 'ax@wanadoo.fr', 'partner'), - ('ax@m4x.org', 'ax@polytechnique.org', 'partner'), - ('annuaire-ax@polytechnique.org', 'annuaire-ax@wanadoo.fr', 'partner'), - ('annuaire-ax@m4x.org', 'annuaire-ax@polytechnique.org', 'partner'), - ('ax-bdc@polytechnique.org', 'ax-bdc@wanadoo.fr', 'partner'), - ('ax-bdc@m4x.org', 'ax-bdc@polytechnique.org', 'partner'), - ('jaune@polytechnique.org', 'null@hruid.polytechnique.org', 'partner'), - ('jaune@m4x.org', 'jaune@polytechnique.org', 'partner'), - ('jaune+rouge@polytechnique.org', 'jaune_rouge@wanadoo.fr', 'partner'), - ('jaune+rouge@m4x.org', 'jaune+rouge@polytechnique.org', 'partner'), - ('xcourseaularge@polytechnique.org', 'info@xcourseaularge.polytechnique.org', 'partner'), - ('xcourseaularge@m4x.org', 'xcourseaularge@polytechnique.org', 'partner'), - ('xim@polytechnique.org', 'membres@x-internet.polytechnique.org', 'partner'), - ('xim@m4x.org', 'xim@polytechnique.org', 'partner'), - ('x-consult@polytechnique.org', 'info@x-consult.polytechnique.org', 'partner'), - ('x-consult@m4x.org', 'x-consult@polytechnique.org', 'partner'), - ('xmcb@polytechnique.org', 'xmcb@x-consult.polytechnique.org', 'partner'), - ('xmcb@m4x.org', 'xmcb@polytechnique.org', 'partner'), - ('x-maroc@polytechnique.org', 'allam@mtpnet.gov.ma', 'partner'), - ('x-maroc@m4x.org', 'x-maroc@polytechnique.org', 'partner'), - ('x-musique@polytechnique.org', 'xmusique@free.fr', 'partner'), - ('x-musique@m4x.org', 'x-musique@polytechnique.org', 'partner'), - ('x-resistance@polytechnique.org', 'info@xresistance.org', 'partner'), - ('x-resistance@m4x.org', 'x-resistance@polytechnique.org', 'partner'), - ('x-israel@polytechnique.org', 'info@x-israel.polytechnique.org', 'partner'), - ('x-israel@m4x.org', 'x-israel@polytechnique.org', 'partner'), - ('gpx@polytechnique.org', 'g.p.x@infonie.fr', 'partner'), - ('gpx@m4x.org', 'gpx@polytechnique.org', 'partner'), - ('g.p.x@polytechnique.org', 'gpx@polytechnique.org', 'partner'), - ('g.p.x@m4x.org', 'g.p.x@polytechnique.org', 'partner'), - ('pointgamma@polytechnique.org', 'gamma@frankiz.polytechnique.fr', 'partner'), - ('pointgamma@m4x.org', 'pointgamma@polytechnique.org', 'partner'), - ('xmpentrepreneur@polytechnique.org', 'xmp.entrepreneur@gmail.com', 'partner'), - ('xmpentrepreneur@m4x.org', 'xmpentrepreneur@polytechnique.org', 'partner'), - ('xmp-entrepreneur@polytechnique.org', 'xmp.entrepreneur@gmail.com', 'partner'), - ('xmp-entrepreneur@m4x.org', 'xmp-entrepreneur@polytechnique.org', 'partner'), - ('xmpangels@polytechnique.org', 'xmpangels@xmp-ba.m4x.org', 'partner'), - ('xmpangels@m4x.org', 'xmpangels@polytechnique.org', 'partner'), - ('xmp-angels@polytechnique.org', 'xmpangels@xmp-ba.m4x.org', 'partner'), - ('xmp-angels@m4x.org', 'xmp-angels@polytechnique.org', 'partner'), - ('relex@polytechnique.org', 'relex@staff.polytechnique.org', 'admin'), - ('relex@m4x.org', 'relex@polytechnique.org', 'admin'), - ('tresorier@polytechnique.org', 'tresorier@staff.polytechnique.org', 'admin'), - ('tresorier@m4x.org', 'tresorier@polytechnique.org', 'admin'), - ('aaege-sso@polytechnique.org', 'aaege-sso@staff.polytechnique.org', 'admin'), - ('aaege-sso@m4x.org', 'aaege-sso@polytechnique.org', 'admin'), - ('innovation@polytechnique.org', 'innovation@staff.polytechnique.org', 'admin'), - ('innovation@m4x.org', 'innovation@polytechnique.org', 'admin'), - ('groupes@polytechnique.org', 'groupes@staff.polytechnique.org', 'admin'), - ('groupes@m4x.org', 'groupes@polytechnique.org', 'admin'), - ('br@polytechnique.org', 'br@staff.polytechnique.org', 'admin'), - ('br@m4x.org', 'br@polytechnique.org', 'admin'), - ('ca@polytechnique.org', 'ca@staff.polytechnique.org', 'admin'), - ('ca@m4x.org', 'ca@polytechnique.org', 'admin'), - ('personnel@polytechnique.org', 'br@staff.polytechnique.org', 'admin'), - ('personnel@m4x.org', 'personnel@polytechnique.org', 'admin'), - ('cil@polytechnique.org', 'cil@staff.polytechnique.org', 'admin'), - ('cil@m4x.org', 'cil@polytechnique.org', 'admin'), - ('opensource@polytechnique.org', 'contact@polytechnique.org', 'admin'), - ('opensource@m4x.org', 'opensource@polytechnique.org', 'admin'), - ('forums@polytechnique.org', 'forums@staff.m4x.org', 'admin'), - ('forums@m4x.org', 'forums@polytechnique.org', 'admin'), - ('telepaiement@polytechnique.org', 'telepaiement@staff.m4x.org', 'admin'), - ('telepaiement@m4x.org', 'telepaiement@polytechnique.org', 'admin'), - ('hotliners@polytechnique.org', 'hotliners@staff.m4x.org', 'admin'), - ('hotliners@m4x.org', 'hotliners@polytechnique.org', 'admin'), - ('kes@polytechnique.org', 'kes@frankiz.polytechnique.fr', 'partner'), - ('kes@m4x.org', 'kes@polytechnique.org', 'partner'), - ('kes1999@polytechnique.org', 'cariokes@polytechnique.org', 'partner'), - ('kes1999@m4x.org', 'kes1999@polytechnique.org', 'partner'), - ('kes2000@polytechnique.org', 'kestinpowers@polytechnique.org', 'partner'), - ('kes2000@m4x.org', 'kes2000@polytechnique.org', 'partner'); - -INSERT INTO email_source_other (hrmid, email, type) - VALUES ('ax.test.polytechnique.org', 'AX-test@polytechnique.org', 'ax'), - ('ax.test.polytechnique.org', 'AX-test@m4x.org', 'ax'), - ('ax.nicolas.zarpas.polytechnique.org', 'AX-nicolas.zarpas@polytechnique.org', 'ax'), - ('ax.nicolas.zarpas.polytechnique.org', 'AX-nicolas.zarpas@m4x.org', 'ax'), - ('ax.carrieres.polytechnique.org', 'AX-carrieres@polytechnique.org', 'ax'), - ('ax.carrieres.polytechnique.org', 'AX-carrieres@m4x.org', 'ax'), - ('ax.info1.polytechnique.org', 'AX-info1@polytechnique.org', 'ax'), - ('ax.info1.polytechnique.org', 'AX-info1@m4x.org', 'ax'), - ('ax.info2.polytechnique.org', 'AX-info2@polytechnique.org', 'ax'), - ('ax.info2.polytechnique.org', 'AX-info2@m4x.org', 'ax'), - ('ax.bal.polytechnique.org', 'AX-bal@polytechnique.org', 'ax'), - ('ax.bal.polytechnique.org', 'AX-bal@m4x.org', 'ax'), - ('ax.annuaire.polytechnique.org', 'AX-annuaire@polytechnique.org', 'ax'), - ('ax.annuaire.polytechnique.org', 'AX-annuaire@m4x.org', 'ax'), - ('ax.jaune-rouge.polytechnique.org', 'AX-jaune-rouge@polytechnique.org', 'ax'), - ('ax.jaune-rouge.polytechnique.org', 'AX-jaune-rouge@m4x.org', 'ax'), - ('honey.jean-pierre.bilah.1980.polytechnique.org', 'jean-pierre.bilah.1980@polytechnique.org', 'honeypot'), - ('honey.jean-pierre.bilah.1980.polytechnique.org', 'jean-pierre.bilah.1980@m4x.org', 'honeypot'), - ('honey.jean-pierre.bilah.1980.polytechnique.org', 'jean-pierre.blah.1980@polytechnique.org', 'honeypot'), - ('honey.jean-pierre.bilah.1980.polytechnique.org', 'jean-pierre.blah.1980@m4x.org', 'honeypot'); - -INSERT INTO email_redirect_other (hrmid, redirect, type, action) - VALUES ('ax.nicolas.zarpas.polytechnique.org', 'nicolas.zarpas-ax@wanadoo.fr', 'smtp', 'tag_spams'), - ('ax.carrieres.polytechnique.org', 'manuela.brasseur-bdc@wanadoo.fr', 'smtp', 'tag_spams'), - ('ax.info1.polytechnique.org', 'sylvie.clairefond-ax@wanadoo.fr', 'smtp', 'tag_spams'), - ('ax.info2.polytechnique.org', 'catherine.perot-ax@wanadoo.fr', 'smtp', 'tag_spams'), - ('ax.bal.polytechnique.org', 'baldelx-ax@wanadoo.fr', 'smtp', 'tag_spams'), - ('ax.annuaire.polytechnique.org', 'annuaire-ax@wanadoo.fr', 'smtp', 'tag_spams'), - ('ax.jaune-rouge.polytechnique.org', 'jaune_rouge@wanadoo.fr', 'smtp', 'tag_spams'), - ('honey.jean-pierre.bilah.1980.polytechnique.org', 'jean-pierre.bilah.1980.mbox@murphy.m4x.org', 'smtp', 'let_spams'), - ('honey.jean-pierre.bilah.1980.polytechnique.org', 'raphael.barrois.2006@polytechnique.org', 'smtp', 'let_spams'); +INSERT INTO email_virtual (domain, email, redirect, type) + VALUES (@p_domain_id, 'otrs.platal', 'otrs.platal@svoboda.polytechnique.org', 'admin'), + (@p_domain_id, 'validation', 'hotliners@staff.polytechnique.org', 'admin'), + (@p_domain_id, 'listes+admin', 'br@staff.polytechnique.org', 'admin'), + (@p_domain_id, 'listes', 'otrs.platal+listes@polytechnique.org', 'admin'), + (@p_domain_id, 'gld', 'listes@polytechnique.org', 'admin'), + (@p_domain_id, 'support', 'otrs.platal+support@polytechnique.org', 'admin'), + (@p_domain_id, 'contact', 'otrs.platal+contact@polytechnique.org', 'admin'), + (@p_domain_id, 'register', 'otrs.platal+register@polytechnique.org', 'admin'), + (@p_domain_id, 'info', 'otrs.platal+info@polytechnique.org', 'admin'), + (@p_domain_id, 'bug', 'otrs.platal+bug@polytechnique.org', 'admin'), + (@p_domain_id, 'resetpass', 'otrs.platal+resetpass@polytechnique.org', 'admin'), + (@p_domain_id, 'association', 'otrs.platal+association@polytechnique.org', 'admin'), + (@p_domain_id, 'x-org', 'association@polytechnique.org', 'admin'), + (@p_domain_id, 'manageurs', 'otrs@support.manageurs.com', 'partner'), + (@p_domain_id, 'fondation', 'fondation@fondationx.org', 'partner'), + (@p_domain_id, 'ax', 'ax@wanadoo.fr', 'partner'), + (@p_domain_id, 'annuaire-ax', 'annuaire-ax@wanadoo.fr', 'partner'), + (@p_domain_id, 'ax-bdc', 'ax-bdc@wanadoo.fr', 'partner'), + (@p_domain_id, 'jaune', 'null@hruid.polytechnique.org', 'partner'), + (@p_domain_id, 'jaune+rouge', 'jaune_rouge@wanadoo.fr', 'partner'), + (@p_domain_id, 'xcourseaularge', 'info@xcourseaularge.polytechnique.org', 'partner'), + (@p_domain_id, 'xim', 'membres@x-internet.polytechnique.org', 'partner'), + (@p_domain_id, 'x-consult', 'info@x-consult.polytechnique.org', 'partner'), + (@p_domain_id, 'xmcb', 'xmcb@x-consult.polytechnique.org', 'partner'), + (@p_domain_id, 'x-maroc', 'allam@mtpnet.gov.ma', 'partner'), + (@p_domain_id, 'x-musique', 'xmusique@free.fr', 'partner'), + (@p_domain_id, 'x-resistance', 'info@xresistance.org', 'partner'), + (@p_domain_id, 'x-israel', 'info@x-israel.polytechnique.org', 'partner'), + (@p_domain_id, 'gpx', 'g.p.x@infonie.fr', 'partner'), + (@p_domain_id, 'g.p.x', 'gpx@polytechnique.org', 'partner'), + (@p_domain_id, 'pointgamma', 'gamma@frankiz.polytechnique.fr', 'partner'), + (@p_domain_id, 'xmpentrepreneur', 'xmp.entrepreneur@gmail.com', 'partner'), + (@p_domain_id, 'xmp-entrepreneur', 'xmp.entrepreneur@gmail.com', 'partner'), + (@p_domain_id, 'xmpangels', 'xmpangels@xmp-ba.m4x.org', 'partner'), + (@p_domain_id, 'xmp-angels', 'xmpangels@xmp-ba.m4x.org', 'partner'), + (@p_domain_id, 'relex', 'relex@staff.polytechnique.org', 'admin'), + (@p_domain_id, 'tresorier', 'tresorier@staff.polytechnique.org', 'admin'), + (@p_domain_id, 'aaege-sso', 'aaege-sso@staff.polytechnique.org', 'admin'), + (@p_domain_id, 'innovation', 'innovation@staff.polytechnique.org', 'admin'), + (@p_domain_id, 'groupes', 'groupes@staff.polytechnique.org', 'admin'), + (@p_domain_id, 'br', 'br@staff.polytechnique.org', 'admin'), + (@p_domain_id, 'ca', 'ca@staff.polytechnique.org', 'admin'), + (@p_domain_id, 'personnel', 'br@staff.polytechnique.org', 'admin'), + (@p_domain_id, 'cil', 'cil@staff.polytechnique.org', 'admin'), + (@p_domain_id, 'opensource', 'contact@polytechnique.org', 'admin'), + (@p_domain_id, 'forums', 'forums@staff.m4x.org', 'admin'), + (@p_domain_id, 'telepaiement', 'telepaiement@staff.m4x.org', 'admin'), + (@p_domain_id, 'hotliners', 'hotliners@staff.m4x.org', 'admin'), + (@p_domain_id, 'kes', 'kes@frankiz.polytechnique.fr', 'partner'), + (@p_domain_id, 'kes1999', 'cariokes@polytechnique.org', 'partner'), + (@p_domain_id, 'kes2000', 'kestinpowers@polytechnique.org', 'partner'); -- Drop renamed list -DELETE FROM email_virtual WHERE email LIKE 'tech-email%@polytechnique.org'; -DELETE FROM email_virtual WHERE email LIKE 'tech-email%@m4x.org'; +DELETE FROM email_virtual WHERE email LIKE 'tech-email%'; --- Imap and bounce -UPDATE email_redirect_account AS e, - (SELECT IF(SUM(IF(type != 'imap', 1, 0)) = 0, 'imap_only', 'normal') AS status, uid - FROM email_redirect_account - WHERE flags = 'active' - GROUP BY uid) AS sub - SET e.action = 'imap_and_bounce' - WHERE sub.status = 'imap_only' AND sub.uid = e.uid AND type = 'imap'; +-- Deletes erroneous domains +DELETE FROM email_virtual_domains + WHERE name IN ('fanfarix.polytechnique.net', 'asd', 'x-russie', 'formation', 'groupetest', 'x-sursaut'); + +-- Deletes unused domains +DELETE FROM email_virtual_domains + WHERE name LIKE 'manageurs.%'; -- vim:set syntax=mysql: