| 1 | -- 1/ Feeds email_virtual_domains from virtual_domains and aliases. |
| 2 | |
| 3 | -- Note: There are some adresses on virtual that have no match on the virtual_redirect. |
| 4 | -- The adresses in this situation are dropped. |
| 5 | |
| 6 | INSERT INTO email_virtual_domains (name) |
| 7 | VALUES ('polytechnique.org'); |
| 8 | INSERT INTO email_virtual_domains (name) |
| 9 | SELECT domain |
| 10 | FROM virtual_domains; |
| 11 | UPDATE email_virtual_domains |
| 12 | SET aliasing = id; |
| 13 | |
| 14 | SET @p_domain_id = 0; |
| 15 | SET @m_domain_id = 0; |
| 16 | SELECT @p_domain_id := id |
| 17 | FROM email_virtual_domains |
| 18 | WHERE name = 'polytechnique.org'; |
| 19 | SELECT @m_domain_id := id |
| 20 | FROM email_virtual_domains |
| 21 | WHERE name = 'melix.net'; |
| 22 | |
| 23 | DELETE FROM email_virtual_domains |
| 24 | WHERE name IN ('m4x.org', 'melix.org', 'x-banque.m4x.org', 'staff.m4x.org', 'x-consult.m4x.org'); |
| 25 | INSERT INTO email_virtual_domains (name, aliasing) |
| 26 | SELECT 'm4x.org', id |
| 27 | FROM email_virtual_domains |
| 28 | WHERE name = 'polytechnique.org'; |
| 29 | INSERT INTO email_virtual_domains (name, aliasing) |
| 30 | SELECT 'melix.org', id |
| 31 | FROM email_virtual_domains |
| 32 | WHERE name = 'melix.net'; |
| 33 | INSERT INTO email_virtual_domains (name, aliasing) |
| 34 | SELECT 'x-banque.m4x.org', id |
| 35 | FROM email_virtual_domains |
| 36 | WHERE name = 'x-banque.polytechnique.org'; |
| 37 | INSERT INTO email_virtual_domains (name, aliasing) |
| 38 | SELECT 'staff.m4x.org', id |
| 39 | FROM email_virtual_domains |
| 40 | WHERE name = 'staff.polytechnique.org'; |
| 41 | INSERT INTO email_virtual_domains (name, aliasing) |
| 42 | SELECT 'x-consult.m4x.org', id |
| 43 | FROM email_virtual_domains |
| 44 | WHERE name = 'x-consult.polytechnique.org'; |
| 45 | |
| 46 | -- 2/ Feeds email_source_account |
| 47 | INSERT INTO email_source_account (uid, domain, email, type, flags, expire) |
| 48 | SELECT uid, @p_domain_id, alias, IF(type = 'a_vie', 'forlife', 'alias'), REPLACE(flags, 'epouse', 'marital'), expire |
| 49 | FROM aliases |
| 50 | WHERE type = 'a_vie' OR type = 'alias'; |
| 51 | INSERT INTO email_source_account (uid, domain, email, type) |
| 52 | SELECT a.uid, @m_domain_id, SUBSTRING_INDEX(v.alias, '@', 1), 'alias' |
| 53 | FROM virtual AS v |
| 54 | LEFT JOIN virtual_redirect AS vr ON (v.vid = vr.vid) |
| 55 | LEFT JOIN accounts AS a ON (a.hruid = LEFT(vr.redirect, LOCATE('@', vr.redirect) - 1)) |
| 56 | WHERE v.type = 'user' AND v.alias LIKE '%@melix.net' AND a.uid IS NOT NULL; |
| 57 | |
| 58 | -- 3/ Feeds email_source_other |
| 59 | INSERT INTO email_source_other (hrmid, email, domain, type, expire) |
| 60 | SELECT CONCAT(CONCAT('h.', alias), '.polytechnique.org'), alias, @p_domain_id, 'homonym', IF(expire IS NULL, '0000-00-00', expire) |
| 61 | FROM aliases |
| 62 | WHERE type = 'homonyme' |
| 63 | GROUP BY alias; |
| 64 | INSERT INTO email_source_other (hrmid, email, type, domain) |
| 65 | VALUES ('ax.test.polytechnique.org', 'AX-test', 'ax', @p_domain_id), |
| 66 | ('ax.nicolas.zarpas.polytechnique.org', 'AX-nicolas.zarpas', 'ax', @p_domain_id), |
| 67 | ('ax.carrieres.polytechnique.org', 'AX-carrieres', 'ax', @p_domain_id), |
| 68 | ('ax.info1.polytechnique.org', 'AX-info1', 'ax', @p_domain_id), |
| 69 | ('ax.info2.polytechnique.org', 'AX-info2', 'ax', @p_domain_id), |
| 70 | ('ax.bal.polytechnique.org', 'AX-bal', 'ax', @p_domain_id), |
| 71 | ('ax.annuaire.polytechnique.org', 'AX-annuaire', 'ax', @p_domain_id), |
| 72 | ('ax.jaune-rouge.polytechnique.org', 'AX-jaune-rouge', 'ax', @p_domain_id), |
| 73 | ('honey.jean-pierre.bilah.1980.polytechnique.org', 'jean-pierre.bilah.1980', 'honeypot', @p_domain_id), |
| 74 | ('honey.jean-pierre.bilah.1980.polytechnique.org', 'jean-pierre.blah.1980', 'honeypot', @p_domain_id); |
| 75 | |
| 76 | -- 4/ Feeds homonyms_list |
| 77 | INSERT INTO homonyms_list (hrmid, uid) |
| 78 | SELECT CONCAT(CONCAT('h.', a.alias), '.polytechnique.org'), h.uid |
| 79 | FROM homonyms AS h |
| 80 | INNER JOIN aliases AS a ON (a.uid = h.homonyme_id) |
| 81 | WHERE a.type = 'homonyme'; |
| 82 | |
| 83 | -- 5/ Feeds email_redirect_account |
| 84 | INSERT INTO email_redirect_account (uid, redirect, rewrite, type, action, broken_date, broken_level, last, flags, hash, allow_rewrite) |
| 85 | SELECT a.uid, e.email, e.rewrite, 'smtp', ef.email, e.panne, e.panne_level, e.last, |
| 86 | IF(e.flags = '', 'inactive', IF(e.flags = 'disable', 'disabled', IF(e.flags = 'panne', 'broken', e.flags))), e.hash, e.allow_rewrite |
| 87 | FROM emails AS e |
| 88 | LEFT JOIN emails AS ef ON (e.uid = ef.uid) |
| 89 | LEFT JOIN accounts AS a ON (e.uid = a.uid) |
| 90 | WHERE e.flags != 'filter' AND ef.flags = 'filter'; |
| 91 | INSERT INTO email_redirect_account (uid, redirect, type, action, flags) |
| 92 | SELECT a.uid, CONCAT(a.hruid, '@g.polytechnique.org'), 'googleapps', ef.email, 'active' |
| 93 | FROM email_options AS eo |
| 94 | LEFT JOIN accounts AS a ON (a.uid = eo.uid) |
| 95 | LEFT JOIN emails AS ef ON (eo.uid = ef.uid) |
| 96 | WHERE FIND_IN_SET('googleapps', eo.storage) AND ef.flags = 'filter'; |
| 97 | INSERT INTO email_redirect_account (uid, redirect, type, action, flags) |
| 98 | SELECT a.uid, CONCAT(a.hruid, '@imap.polytechnique.org'), 'imap', 'let_spams', 'active' |
| 99 | FROM email_options AS eo |
| 100 | LEFT JOIN accounts AS a ON (a.uid = eo.uid) |
| 101 | WHERE FIND_IN_SET('imap', eo.storage); |
| 102 | |
| 103 | -- Imap and bounce |
| 104 | UPDATE email_redirect_account AS e, |
| 105 | (SELECT IF(SUM(IF(type != 'imap', 1, 0)) = 0, 'imap_only', 'normal') AS status, uid |
| 106 | FROM email_redirect_account |
| 107 | WHERE flags = 'active' |
| 108 | GROUP BY uid) AS sub |
| 109 | SET e.action = 'imap_and_bounce' |
| 110 | WHERE sub.status = 'imap_only' AND sub.uid = e.uid AND type = 'imap'; |
| 111 | |
| 112 | -- 6/ Feeds email_redirect_other |
| 113 | INSERT INTO email_redirect_other (hrmid, type, action) |
| 114 | SELECT hrmid, 'homonym', 'homonym' |
| 115 | FROM email_source_other |
| 116 | WHERE type = 'homonym' |
| 117 | GROUP BY (hrmid); |
| 118 | |
| 119 | INSERT INTO email_redirect_other (hrmid, redirect, type, action) |
| 120 | VALUES ('ax.nicolas.zarpas.polytechnique.org', 'nicolas.zarpas-ax@wanadoo.fr', 'smtp', 'tag_spams'), |
| 121 | ('ax.carrieres.polytechnique.org', 'manuela.brasseur-bdc@wanadoo.fr', 'smtp', 'tag_spams'), |
| 122 | ('ax.info1.polytechnique.org', 'sylvie.clairefond-ax@wanadoo.fr', 'smtp', 'tag_spams'), |
| 123 | ('ax.info2.polytechnique.org', 'catherine.perot-ax@wanadoo.fr', 'smtp', 'tag_spams'), |
| 124 | ('ax.bal.polytechnique.org', 'baldelx-ax@wanadoo.fr', 'smtp', 'tag_spams'), |
| 125 | ('ax.annuaire.polytechnique.org', 'annuaire-ax@wanadoo.fr', 'smtp', 'tag_spams'), |
| 126 | ('ax.jaune-rouge.polytechnique.org', 'jaune_rouge@wanadoo.fr', 'smtp', 'tag_spams'), |
| 127 | ('honey.jean-pierre.bilah.1980.polytechnique.org', 'jean-pierre.bilah.1980.mbox@murphy.m4x.org', 'smtp', 'let_spams'), |
| 128 | ('honey.jean-pierre.bilah.1980.polytechnique.org', 'raphael.barrois.2006@polytechnique.org', 'smtp', 'let_spams'); |
| 129 | |
| 130 | -- 7/ Feeds email_virtual |
| 131 | INSERT INTO email_virtual (email, domain, redirect, type) |
| 132 | SELECT SUBSTRING_INDEX(v.alias, '@', 1), d.id, vr.redirect, IF(v.type = 'evt', 'event', v.type) |
| 133 | FROM virtual AS v |
| 134 | INNER JOIN email_virtual_domains AS d ON (SUBSTRING_INDEX(v.alias, '@', -1) = d.name AND d.id = d.aliasing) |
| 135 | LEFT JOIN virtual_redirect AS vr ON (vr.vid = v.vid) |
| 136 | WHERE v.alias NOT LIKE '%@melix.net' AND vr.vid IS NOT NULL AND v.alias != '@melix.org' AND v.type != 'dom'; |
| 137 | |
| 138 | INSERT INTO email_virtual (email, type, domain, redirect) |
| 139 | SELECT alias, 'list', @p_domain_id, |
| 140 | CONCAT('polytechnique.org_', REPLACE(REPLACE(REPLACE(CONCAT(alias, '+post@listes.polytechnique.org'), |
| 141 | '-admin+post', '+admin'), |
| 142 | '-owner+post', '+owner'), |
| 143 | '-bounces+post', '+bounces')) |
| 144 | FROM aliases |
| 145 | WHERE type = 'liste'; |
| 146 | |
| 147 | INSERT INTO email_virtual (email, redirect, domain, type) |
| 148 | SELECT SUBSTRING_INDEX(v.alias, '@', 1), vr.redirect, @m_domain_id, 'user' |
| 149 | FROM virtual AS v |
| 150 | LEFT JOIN virtual_redirect AS vr ON (v.vid = vr.vid) |
| 151 | LEFT JOIN accounts AS a ON (a.hruid = LEFT(vr.redirect, LOCATE('@', vr.redirect) - 1)) |
| 152 | WHERE v.type = 'user' AND v.alias LIKE '%@melix.net' AND vr.vid IS NOT NULL AND a.uid IS NULL; |
| 153 | |
| 154 | -- From aliases file |
| 155 | INSERT INTO email_virtual (domain, email, redirect, type) |
| 156 | VALUES (@p_domain_id, 'otrs.platal', 'otrs.platal@svoboda.polytechnique.org', 'admin'), |
| 157 | (@p_domain_id, 'validation', 'hotliners@staff.polytechnique.org', 'admin'), |
| 158 | (@p_domain_id, 'listes+admin', 'br@staff.polytechnique.org', 'admin'), |
| 159 | (@p_domain_id, 'listes', 'otrs.platal+listes@polytechnique.org', 'admin'), |
| 160 | (@p_domain_id, 'gld', 'listes@polytechnique.org', 'admin'), |
| 161 | (@p_domain_id, 'support', 'otrs.platal+support@polytechnique.org', 'admin'), |
| 162 | (@p_domain_id, 'contact', 'otrs.platal+contact@polytechnique.org', 'admin'), |
| 163 | (@p_domain_id, 'register', 'otrs.platal+register@polytechnique.org', 'admin'), |
| 164 | (@p_domain_id, 'info', 'otrs.platal+info@polytechnique.org', 'admin'), |
| 165 | (@p_domain_id, 'bug', 'otrs.platal+bug@polytechnique.org', 'admin'), |
| 166 | (@p_domain_id, 'resetpass', 'otrs.platal+resetpass@polytechnique.org', 'admin'), |
| 167 | (@p_domain_id, 'association', 'otrs.platal+association@polytechnique.org', 'admin'), |
| 168 | (@p_domain_id, 'x-org', 'association@polytechnique.org', 'admin'), |
| 169 | (@p_domain_id, 'manageurs', 'otrs@support.manageurs.com', 'partner'), |
| 170 | (@p_domain_id, 'fondation', 'fondation@fondationx.org', 'partner'), |
| 171 | (@p_domain_id, 'ax', 'ax@wanadoo.fr', 'partner'), |
| 172 | (@p_domain_id, 'annuaire-ax', 'annuaire-ax@wanadoo.fr', 'partner'), |
| 173 | (@p_domain_id, 'ax-bdc', 'ax-bdc@wanadoo.fr', 'partner'), |
| 174 | (@p_domain_id, 'jaune', 'null@hruid.polytechnique.org', 'partner'), |
| 175 | (@p_domain_id, 'jaune+rouge', 'jaune_rouge@wanadoo.fr', 'partner'), |
| 176 | (@p_domain_id, 'xcourseaularge', 'info@xcourseaularge.polytechnique.org', 'partner'), |
| 177 | (@p_domain_id, 'xim', 'membres@x-internet.polytechnique.org', 'partner'), |
| 178 | (@p_domain_id, 'x-consult', 'info@x-consult.polytechnique.org', 'partner'), |
| 179 | (@p_domain_id, 'xmcb', 'xmcb@x-consult.polytechnique.org', 'partner'), |
| 180 | (@p_domain_id, 'x-maroc', 'allam@mtpnet.gov.ma', 'partner'), |
| 181 | (@p_domain_id, 'x-musique', 'xmusique@free.fr', 'partner'), |
| 182 | (@p_domain_id, 'x-resistance', 'info@xresistance.org', 'partner'), |
| 183 | (@p_domain_id, 'x-israel', 'info@x-israel.polytechnique.org', 'partner'), |
| 184 | (@p_domain_id, 'gpx', 'g.p.x@infonie.fr', 'partner'), |
| 185 | (@p_domain_id, 'g.p.x', 'gpx@polytechnique.org', 'partner'), |
| 186 | (@p_domain_id, 'pointgamma', 'gamma@frankiz.polytechnique.fr', 'partner'), |
| 187 | (@p_domain_id, 'xmpentrepreneur', 'xmp.entrepreneur@gmail.com', 'partner'), |
| 188 | (@p_domain_id, 'xmp-entrepreneur', 'xmp.entrepreneur@gmail.com', 'partner'), |
| 189 | (@p_domain_id, 'xmpangels', 'xmpangels@xmp-ba.m4x.org', 'partner'), |
| 190 | (@p_domain_id, 'xmp-angels', 'xmpangels@xmp-ba.m4x.org', 'partner'), |
| 191 | (@p_domain_id, 'relex', 'relex@staff.polytechnique.org', 'admin'), |
| 192 | (@p_domain_id, 'tresorier', 'tresorier@staff.polytechnique.org', 'admin'), |
| 193 | (@p_domain_id, 'aaege-sso', 'aaege-sso@staff.polytechnique.org', 'admin'), |
| 194 | (@p_domain_id, 'innovation', 'innovation@staff.polytechnique.org', 'admin'), |
| 195 | (@p_domain_id, 'groupes', 'groupes@staff.polytechnique.org', 'admin'), |
| 196 | (@p_domain_id, 'br', 'br@staff.polytechnique.org', 'admin'), |
| 197 | (@p_domain_id, 'ca', 'ca@staff.polytechnique.org', 'admin'), |
| 198 | (@p_domain_id, 'personnel', 'br@staff.polytechnique.org', 'admin'), |
| 199 | (@p_domain_id, 'cil', 'cil@staff.polytechnique.org', 'admin'), |
| 200 | (@p_domain_id, 'opensource', 'contact@polytechnique.org', 'admin'), |
| 201 | (@p_domain_id, 'forums', 'forums@staff.m4x.org', 'admin'), |
| 202 | (@p_domain_id, 'telepaiement', 'telepaiement@staff.m4x.org', 'admin'), |
| 203 | (@p_domain_id, 'hotliners', 'hotliners@staff.m4x.org', 'admin'), |
| 204 | (@p_domain_id, 'kes', 'kes@frankiz.polytechnique.fr', 'partner'), |
| 205 | (@p_domain_id, 'kes1999', 'cariokes@polytechnique.org', 'partner'), |
| 206 | (@p_domain_id, 'kes2000', 'kestinpowers@polytechnique.org', 'partner'); |
| 207 | |
| 208 | -- Drop renamed list |
| 209 | DELETE FROM email_virtual WHERE email LIKE 'tech-email%'; |
| 210 | |
| 211 | -- vim:set syntax=mysql: |