projects / platal.git / blame
| Commit | Line | Data |
|---|---|---|
| 67f95129 SJ |
1 | -- 1/ Feeds email_virtual_domains from virtual_domains and aliases. |
| 2 | ||
| 67f95129 SJ |
3 | INSERT INTO email_virtual_domains (name) |
| 4 | VALUES ('polytechnique.org'); | |
| 5 | INSERT INTO email_virtual_domains (name) | |
| 6 | SELECT domain | |
| 7 | FROM virtual_domains; | |
| 8 | UPDATE email_virtual_domains | |
| 9 | SET aliasing = id; | |
| 10 | ||
| 11 | SET @p_domain_id = 0; | |
| 12 | SET @m_domain_id = 0; | |
| 13 | SELECT @p_domain_id := id | |
| 14 | FROM email_virtual_domains | |
| 15 | WHERE name = 'polytechnique.org'; | |
| 16 | SELECT @m_domain_id := id | |
| 17 | FROM email_virtual_domains | |
| 18 | WHERE name = 'melix.net'; | |
| 19 | ||
| 20 | DELETE FROM email_virtual_domains | |
| 21 | WHERE name IN ('m4x.org', 'melix.org', 'x-banque.m4x.org', 'staff.m4x.org', 'x-consult.m4x.org'); | |
| 22 | INSERT INTO email_virtual_domains (name, aliasing) | |
| 23 | SELECT 'm4x.org', id | |
| 24 | FROM email_virtual_domains | |
| 25 | WHERE name = 'polytechnique.org'; | |
| 26 | INSERT INTO email_virtual_domains (name, aliasing) | |
| 27 | SELECT 'melix.org', id | |
| 28 | FROM email_virtual_domains | |
| 29 | WHERE name = 'melix.net'; | |
| 30 | INSERT INTO email_virtual_domains (name, aliasing) | |
| 31 | SELECT 'x-banque.m4x.org', id | |
| 32 | FROM email_virtual_domains | |
| 33 | WHERE name = 'x-banque.polytechnique.org'; | |
| 34 | INSERT INTO email_virtual_domains (name, aliasing) | |
| 35 | SELECT 'staff.m4x.org', id | |
| 36 | FROM email_virtual_domains | |
| 37 | WHERE name = 'staff.polytechnique.org'; | |
| 38 | INSERT INTO email_virtual_domains (name, aliasing) | |
| 39 | SELECT 'x-consult.m4x.org', id | |
| 40 | FROM email_virtual_domains | |
| 41 | WHERE name = 'x-consult.polytechnique.org'; | |
| 42 | ||
| 43 | -- 2/ Feeds email_source_account | |
| 44 | INSERT INTO email_source_account (uid, domain, email, type, flags, expire) | |
| 45 | SELECT uid, @p_domain_id, alias, IF(type = 'a_vie', 'forlife', 'alias'), REPLACE(flags, 'epouse', 'marital'), expire | |
| 87c89986 SJ |
46 | FROM aliases |
| 47 | WHERE type = 'a_vie' OR type = 'alias'; | |
| 67f95129 SJ |
48 | INSERT INTO email_source_account (uid, domain, email, type) |
| 49 | SELECT a.uid, @m_domain_id, SUBSTRING_INDEX(v.alias, '@', 1), 'alias' | |
| 87c89986 SJ |
50 | FROM virtual AS v |
| 51 | LEFT JOIN virtual_redirect AS vr ON (v.vid = vr.vid) | |
| 67f95129 | 52 | LEFT JOIN accounts AS a ON (a.hruid = LEFT(vr.redirect, LOCATE('@', vr.redirect) - 1)) |
| 87c89986 | 53 | WHERE v.type = 'user' AND v.alias LIKE '%@melix.net' AND a.uid IS NOT NULL; |
| 96156ab8 | 54 | |
| 67f95129 | 55 | -- 3/ Feeds email_source_other |
| 4371e993 SJ |
56 | INSERT INTO email_source_other (hrmid, email, domain, type, expire) |
| 57 | SELECT CONCAT(CONCAT('h.', alias), '.polytechnique.org'), alias, @p_domain_id, 'homonym', IF(expire IS NULL, '0000-00-00', expire) | |
| 87c89986 SJ |
58 | FROM aliases |
| 59 | WHERE type = 'homonyme' | |
| 67f95129 SJ |
60 | GROUP BY alias; |
| 61 | INSERT INTO email_source_other (hrmid, email, type, domain) | |
| 62 | VALUES ('ax.test.polytechnique.org', 'AX-test', 'ax', @p_domain_id), | |
| 63 | ('ax.nicolas.zarpas.polytechnique.org', 'AX-nicolas.zarpas', 'ax', @p_domain_id), | |
| 64 | ('ax.carrieres.polytechnique.org', 'AX-carrieres', 'ax', @p_domain_id), | |
| 65 | ('ax.info1.polytechnique.org', 'AX-info1', 'ax', @p_domain_id), | |
| 66 | ('ax.info2.polytechnique.org', 'AX-info2', 'ax', @p_domain_id), | |
| 67 | ('ax.bal.polytechnique.org', 'AX-bal', 'ax', @p_domain_id), | |
| 68 | ('ax.annuaire.polytechnique.org', 'AX-annuaire', 'ax', @p_domain_id), | |
| 69 | ('ax.jaune-rouge.polytechnique.org', 'AX-jaune-rouge', 'ax', @p_domain_id), | |
| 70 | ('honey.jean-pierre.bilah.1980.polytechnique.org', 'jean-pierre.bilah.1980', 'honeypot', @p_domain_id), | |
| 71 | ('honey.jean-pierre.bilah.1980.polytechnique.org', 'jean-pierre.blah.1980', 'honeypot', @p_domain_id); | |
| 96156ab8 | 72 | |
| 67f95129 | 73 | -- 4/ Feeds homonyms_list |
| 43de2baf | 74 | INSERT INTO homonyms_list (hrmid, uid) |
| 87c89986 SJ |
75 | SELECT CONCAT(CONCAT('h.', a.alias), '.polytechnique.org'), h.uid |
| 76 | FROM homonyms AS h | |
| 43de2baf | 77 | INNER JOIN aliases AS a ON (a.uid = h.homonyme_id) |
| 87c89986 | 78 | WHERE a.type = 'homonyme'; |
| 96156ab8 | 79 | |
| 67f95129 | 80 | -- 5/ Feeds email_redirect_account |
| 43de2baf | 81 | INSERT INTO email_redirect_account (uid, redirect, rewrite, type, action, broken_date, broken_level, last, flags, hash, allow_rewrite) |
| 7756ff03 | 82 | SELECT e.uid, e.email, e.rewrite, 'smtp', ef.email, e.panne, e.panne_level, e.last, |
| 43de2baf | 83 | IF(e.flags = '', 'inactive', IF(e.flags = 'disable', 'disabled', IF(e.flags = 'panne', 'broken', e.flags))), e.hash, e.allow_rewrite |
| 87c89986 SJ |
84 | FROM emails AS e |
| 85 | LEFT JOIN emails AS ef ON (e.uid = ef.uid) | |
| 87c89986 | 86 | WHERE e.flags != 'filter' AND ef.flags = 'filter'; |
| 43de2baf | 87 | INSERT INTO email_redirect_account (uid, redirect, type, action, flags) |
| 87c89986 SJ |
88 | SELECT a.uid, CONCAT(a.hruid, '@g.polytechnique.org'), 'googleapps', ef.email, 'active' |
| 89 | FROM email_options AS eo | |
| 90 | LEFT JOIN accounts AS a ON (a.uid = eo.uid) | |
| 91 | LEFT JOIN emails AS ef ON (eo.uid = ef.uid) | |
| 92 | WHERE FIND_IN_SET('googleapps', eo.storage) AND ef.flags = 'filter'; | |
| 43de2baf | 93 | INSERT INTO email_redirect_account (uid, redirect, type, action, flags) |
| 87c89986 SJ |
94 | SELECT a.uid, CONCAT(a.hruid, '@imap.polytechnique.org'), 'imap', 'let_spams', 'active' |
| 95 | FROM email_options AS eo | |
| 96 | LEFT JOIN accounts AS a ON (a.uid = eo.uid) | |
| 97 | WHERE FIND_IN_SET('imap', eo.storage); | |
| 96156ab8 | 98 | |
| 67f95129 SJ |
99 | -- Imap and bounce |
| 100 | UPDATE email_redirect_account AS e, | |
| 101 | (SELECT IF(SUM(IF(type != 'imap', 1, 0)) = 0, 'imap_only', 'normal') AS status, uid | |
| 102 | FROM email_redirect_account | |
| 103 | WHERE flags = 'active' | |
| 104 | GROUP BY uid) AS sub | |
| 105 | SET e.action = 'imap_and_bounce' | |
| 106 | WHERE sub.status = 'imap_only' AND sub.uid = e.uid AND type = 'imap'; | |
| 107 | ||
| 108 | -- 6/ Feeds email_redirect_other | |
| 43de2baf | 109 | INSERT INTO email_redirect_other (hrmid, type, action) |
| 87c89986 SJ |
110 | SELECT hrmid, 'homonym', 'homonym' |
| 111 | FROM email_source_other | |
| 112 | WHERE type = 'homonym' | |
| 113 | GROUP BY (hrmid); | |
| e41717ad | 114 | |
| 67f95129 SJ |
115 | INSERT INTO email_redirect_other (hrmid, redirect, type, action) |
| 116 | VALUES ('ax.nicolas.zarpas.polytechnique.org', 'nicolas.zarpas-ax@wanadoo.fr', 'smtp', 'tag_spams'), | |
| 117 | ('ax.carrieres.polytechnique.org', 'manuela.brasseur-bdc@wanadoo.fr', 'smtp', 'tag_spams'), | |
| 118 | ('ax.info1.polytechnique.org', 'sylvie.clairefond-ax@wanadoo.fr', 'smtp', 'tag_spams'), | |
| 119 | ('ax.info2.polytechnique.org', 'catherine.perot-ax@wanadoo.fr', 'smtp', 'tag_spams'), | |
| 120 | ('ax.bal.polytechnique.org', 'baldelx-ax@wanadoo.fr', 'smtp', 'tag_spams'), | |
| 121 | ('ax.annuaire.polytechnique.org', 'annuaire-ax@wanadoo.fr', 'smtp', 'tag_spams'), | |
| 122 | ('ax.jaune-rouge.polytechnique.org', 'jaune_rouge@wanadoo.fr', 'smtp', 'tag_spams'), | |
| 123 | ('honey.jean-pierre.bilah.1980.polytechnique.org', 'jean-pierre.bilah.1980.mbox@murphy.m4x.org', 'smtp', 'let_spams'), | |
| 124 | ('honey.jean-pierre.bilah.1980.polytechnique.org', 'raphael.barrois.2006@polytechnique.org', 'smtp', 'let_spams'); | |
| 125 | ||
| 126 | -- 7/ Feeds email_virtual | |
| 3f64dbef SJ |
127 | |
| 128 | -- Note: There are some adresses on virtual that have no match on the virtual_redirect. | |
| 129 | -- The adresses in this situation are dropped. | |
| 130 | ||
| 67f95129 SJ |
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) | |
| eaf67a5f | 136 | WHERE v.alias NOT LIKE '%@melix.net' AND vr.vid IS NOT NULL AND v.type != 'dom'; |
| 67f95129 SJ |
137 | |
| 138 | INSERT INTO email_virtual (email, type, domain, redirect) | |
| 139 | SELECT alias, 'list', @p_domain_id, | |
| 87c89986 | 140 | CONCAT('polytechnique.org_', REPLACE(REPLACE(REPLACE(CONCAT(alias, '+post@listes.polytechnique.org'), |
| 43de2baf SJ |
141 | '-admin+post', '+admin'), |
| 142 | '-owner+post', '+owner'), | |
| 143 | '-bounces+post', '+bounces')) | |
| 87c89986 SJ |
144 | FROM aliases |
| 145 | WHERE type = 'liste'; | |
| 67f95129 SJ |
146 | |
| 147 | INSERT INTO email_virtual (email, redirect, domain, type) | |
| 51c2c63a | 148 | SELECT SUBSTRING_INDEX(v.alias, '@', 1), vr.redirect, @m_domain_id, 'alias' |
| 87c89986 SJ |
149 | FROM virtual AS v |
| 150 | LEFT JOIN virtual_redirect AS vr ON (v.vid = vr.vid) | |
| 67f95129 | 151 | LEFT JOIN accounts AS a ON (a.hruid = LEFT(vr.redirect, LOCATE('@', vr.redirect) - 1)) |
| 87c89986 | 152 | WHERE v.type = 'user' AND v.alias LIKE '%@melix.net' AND vr.vid IS NOT NULL AND a.uid IS NULL; |
| b172472d | 153 | |
| b172472d | 154 | -- From aliases file |
| 67f95129 SJ |
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'); | |
| 96156ab8 | 207 | |
| b172472d | 208 | -- Drop renamed list |
| 67f95129 | 209 | DELETE FROM email_virtual WHERE email LIKE 'tech-email%'; |
| b0e71a9f | 210 | |
| d0cd28d0 | 211 | -- vim:set syntax=mysql: |