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 | ||
| f036c896 SJ |
43 | INSERT INTO email_virtual_domains (name, aliasing) |
| 44 | VALUES ('alumni.polytechnique.org', @p_domain_id), ('alumni.m4x.org', @p_domain_id), | |
| 45 | ('master.polytechnique.org', 1), ('doc.polytechnique.org', 1); | |
| 46 | SET @master_domain_id = 0; | |
| 47 | SET @doc_domain_id = 0; | |
| 48 | SELECT @master_domain_id := id | |
| 49 | FROM email_virtual_domains | |
| 50 | WHERE name = 'master.polytechnique.org'; | |
| 51 | SELECT @doc_domain_id := id | |
| 52 | FROM email_virtual_domains | |
| 53 | WHERE name = 'doc.polytechnique.org'; | |
| 54 | UPDATE email_virtual_domains | |
| 55 | SET aliasing = @master_domain_id | |
| 56 | WHERE name = 'master.polytechnique.org'; | |
| 57 | UPDATE email_virtual_domains | |
| 58 | SET aliasing = @doc_domain_id | |
| 59 | WHERE name = 'doc.polytechnique.org'; | |
| 60 | INSERT INTO email_virtual_domains (name, aliasing) | |
| 61 | VALUES ('alumni.polytechnique.org', @master_domain_id), ('alumni.m4x.org', @master_domain_id), | |
| 62 | ('alumni.polytechnique.org', @doc_domain_id), ('alumni.m4x.org', @doc_domain_id), | |
| 63 | ('master.m4x.org', @master_domain_id), ('doc.m4x.org', @doc_domain_id); | |
| 64 | ||
| 67f95129 SJ |
65 | -- 2/ Feeds email_source_account |
| 66 | INSERT INTO email_source_account (uid, domain, email, type, flags, expire) | |
| 67 | SELECT uid, @p_domain_id, alias, IF(type = 'a_vie', 'forlife', 'alias'), REPLACE(flags, 'epouse', 'marital'), expire | |
| 87c89986 SJ |
68 | FROM aliases |
| 69 | WHERE type = 'a_vie' OR type = 'alias'; | |
| 67f95129 | 70 | INSERT INTO email_source_account (uid, domain, email, type) |
| 08d33afc | 71 | SELECT a.uid, @m_domain_id, SUBSTRING_INDEX(v.alias, '@', 1), 'alias_aux' |
| 87c89986 SJ |
72 | FROM virtual AS v |
| 73 | LEFT JOIN virtual_redirect AS vr ON (v.vid = vr.vid) | |
| 67f95129 | 74 | LEFT JOIN accounts AS a ON (a.hruid = LEFT(vr.redirect, LOCATE('@', vr.redirect) - 1)) |
| 87c89986 | 75 | WHERE v.type = 'user' AND v.alias LIKE '%@melix.net' AND a.uid IS NOT NULL; |
| 96156ab8 | 76 | |
| 67f95129 | 77 | -- 3/ Feeds email_source_other |
| 4371e993 SJ |
78 | INSERT INTO email_source_other (hrmid, email, domain, type, expire) |
| 79 | SELECT CONCAT(CONCAT('h.', alias), '.polytechnique.org'), alias, @p_domain_id, 'homonym', IF(expire IS NULL, '0000-00-00', expire) | |
| 87c89986 SJ |
80 | FROM aliases |
| 81 | WHERE type = 'homonyme' | |
| 67f95129 SJ |
82 | GROUP BY alias; |
| 83 | INSERT INTO email_source_other (hrmid, email, type, domain) | |
| 84 | VALUES ('ax.test.polytechnique.org', 'AX-test', 'ax', @p_domain_id), | |
| 85 | ('ax.nicolas.zarpas.polytechnique.org', 'AX-nicolas.zarpas', 'ax', @p_domain_id), | |
| 86 | ('ax.carrieres.polytechnique.org', 'AX-carrieres', 'ax', @p_domain_id), | |
| 87 | ('ax.info1.polytechnique.org', 'AX-info1', 'ax', @p_domain_id), | |
| 88 | ('ax.info2.polytechnique.org', 'AX-info2', 'ax', @p_domain_id), | |
| 89 | ('ax.bal.polytechnique.org', 'AX-bal', 'ax', @p_domain_id), | |
| 90 | ('ax.annuaire.polytechnique.org', 'AX-annuaire', 'ax', @p_domain_id), | |
| 91 | ('ax.jaune-rouge.polytechnique.org', 'AX-jaune-rouge', 'ax', @p_domain_id), | |
| 92 | ('honey.jean-pierre.bilah.1980.polytechnique.org', 'jean-pierre.bilah.1980', 'honeypot', @p_domain_id), | |
| 93 | ('honey.jean-pierre.bilah.1980.polytechnique.org', 'jean-pierre.blah.1980', 'honeypot', @p_domain_id); | |
| 96156ab8 | 94 | |
| 67f95129 | 95 | -- 4/ Feeds homonyms_list |
| 43de2baf | 96 | INSERT INTO homonyms_list (hrmid, uid) |
| 87c89986 SJ |
97 | SELECT CONCAT(CONCAT('h.', a.alias), '.polytechnique.org'), h.uid |
| 98 | FROM homonyms AS h | |
| 43de2baf | 99 | INNER JOIN aliases AS a ON (a.uid = h.homonyme_id) |
| 87c89986 | 100 | WHERE a.type = 'homonyme'; |
| 96156ab8 | 101 | |
| 67f95129 | 102 | -- 5/ Feeds email_redirect_account |
| 43de2baf | 103 | INSERT INTO email_redirect_account (uid, redirect, rewrite, type, action, broken_date, broken_level, last, flags, hash, allow_rewrite) |
| 7756ff03 | 104 | SELECT e.uid, e.email, e.rewrite, 'smtp', ef.email, e.panne, e.panne_level, e.last, |
| 43de2baf | 105 | IF(e.flags = '', 'inactive', IF(e.flags = 'disable', 'disabled', IF(e.flags = 'panne', 'broken', e.flags))), e.hash, e.allow_rewrite |
| 87c89986 SJ |
106 | FROM emails AS e |
| 107 | LEFT JOIN emails AS ef ON (e.uid = ef.uid) | |
| 87c89986 | 108 | WHERE e.flags != 'filter' AND ef.flags = 'filter'; |
| 43de2baf | 109 | INSERT INTO email_redirect_account (uid, redirect, type, action, flags) |
| 87c89986 SJ |
110 | SELECT a.uid, CONCAT(a.hruid, '@g.polytechnique.org'), 'googleapps', ef.email, 'active' |
| 111 | FROM email_options AS eo | |
| 112 | LEFT JOIN accounts AS a ON (a.uid = eo.uid) | |
| 113 | LEFT JOIN emails AS ef ON (eo.uid = ef.uid) | |
| 114 | WHERE FIND_IN_SET('googleapps', eo.storage) AND ef.flags = 'filter'; | |
| 43de2baf | 115 | INSERT INTO email_redirect_account (uid, redirect, type, action, flags) |
| 87c89986 SJ |
116 | SELECT a.uid, CONCAT(a.hruid, '@imap.polytechnique.org'), 'imap', 'let_spams', 'active' |
| 117 | FROM email_options AS eo | |
| 118 | LEFT JOIN accounts AS a ON (a.uid = eo.uid) | |
| 119 | WHERE FIND_IN_SET('imap', eo.storage); | |
| 96156ab8 | 120 | |
| 67f95129 SJ |
121 | -- Imap and bounce |
| 122 | UPDATE email_redirect_account AS e, | |
| 123 | (SELECT IF(SUM(IF(type != 'imap', 1, 0)) = 0, 'imap_only', 'normal') AS status, uid | |
| 124 | FROM email_redirect_account | |
| 125 | WHERE flags = 'active' | |
| 126 | GROUP BY uid) AS sub | |
| 127 | SET e.action = 'imap_and_bounce' | |
| 128 | WHERE sub.status = 'imap_only' AND sub.uid = e.uid AND type = 'imap'; | |
| 129 | ||
| 130 | -- 6/ Feeds email_redirect_other | |
| 43de2baf | 131 | INSERT INTO email_redirect_other (hrmid, type, action) |
| 87c89986 SJ |
132 | SELECT hrmid, 'homonym', 'homonym' |
| 133 | FROM email_source_other | |
| 134 | WHERE type = 'homonym' | |
| 135 | GROUP BY (hrmid); | |
| e41717ad | 136 | |
| 67f95129 SJ |
137 | INSERT INTO email_redirect_other (hrmid, redirect, type, action) |
| 138 | VALUES ('ax.nicolas.zarpas.polytechnique.org', 'nicolas.zarpas-ax@wanadoo.fr', 'smtp', 'tag_spams'), | |
| 139 | ('ax.carrieres.polytechnique.org', 'manuela.brasseur-bdc@wanadoo.fr', 'smtp', 'tag_spams'), | |
| 140 | ('ax.info1.polytechnique.org', 'sylvie.clairefond-ax@wanadoo.fr', 'smtp', 'tag_spams'), | |
| 141 | ('ax.info2.polytechnique.org', 'catherine.perot-ax@wanadoo.fr', 'smtp', 'tag_spams'), | |
| 142 | ('ax.bal.polytechnique.org', 'baldelx-ax@wanadoo.fr', 'smtp', 'tag_spams'), | |
| 143 | ('ax.annuaire.polytechnique.org', 'annuaire-ax@wanadoo.fr', 'smtp', 'tag_spams'), | |
| 144 | ('ax.jaune-rouge.polytechnique.org', 'jaune_rouge@wanadoo.fr', 'smtp', 'tag_spams'), | |
| 145 | ('honey.jean-pierre.bilah.1980.polytechnique.org', 'jean-pierre.bilah.1980.mbox@murphy.m4x.org', 'smtp', 'let_spams'), | |
| 146 | ('honey.jean-pierre.bilah.1980.polytechnique.org', 'raphael.barrois.2006@polytechnique.org', 'smtp', 'let_spams'); | |
| 147 | ||
| 148 | -- 7/ Feeds email_virtual | |
| 3f64dbef SJ |
149 | |
| 150 | -- Note: There are some adresses on virtual that have no match on the virtual_redirect. | |
| 151 | -- The adresses in this situation are dropped. | |
| 152 | ||
| 67f95129 SJ |
153 | INSERT INTO email_virtual (email, domain, redirect, type) |
| 154 | SELECT SUBSTRING_INDEX(v.alias, '@', 1), d.id, vr.redirect, IF(v.type = 'evt', 'event', v.type) | |
| 155 | FROM virtual AS v | |
| 156 | INNER JOIN email_virtual_domains AS d ON (SUBSTRING_INDEX(v.alias, '@', -1) = d.name AND d.id = d.aliasing) | |
| 157 | LEFT JOIN virtual_redirect AS vr ON (vr.vid = v.vid) | |
| eaf67a5f | 158 | WHERE v.alias NOT LIKE '%@melix.net' AND vr.vid IS NOT NULL AND v.type != 'dom'; |
| 67f95129 SJ |
159 | |
| 160 | INSERT INTO email_virtual (email, type, domain, redirect) | |
| 161 | SELECT alias, 'list', @p_domain_id, | |
| 87c89986 | 162 | CONCAT('polytechnique.org_', REPLACE(REPLACE(REPLACE(CONCAT(alias, '+post@listes.polytechnique.org'), |
| 43de2baf SJ |
163 | '-admin+post', '+admin'), |
| 164 | '-owner+post', '+owner'), | |
| 165 | '-bounces+post', '+bounces')) | |
| 87c89986 SJ |
166 | FROM aliases |
| 167 | WHERE type = 'liste'; | |
| 67f95129 SJ |
168 | |
| 169 | INSERT INTO email_virtual (email, redirect, domain, type) | |
| 51c2c63a | 170 | SELECT SUBSTRING_INDEX(v.alias, '@', 1), vr.redirect, @m_domain_id, 'alias' |
| 87c89986 SJ |
171 | FROM virtual AS v |
| 172 | LEFT JOIN virtual_redirect AS vr ON (v.vid = vr.vid) | |
| 67f95129 | 173 | LEFT JOIN accounts AS a ON (a.hruid = LEFT(vr.redirect, LOCATE('@', vr.redirect) - 1)) |
| 87c89986 | 174 | WHERE v.type = 'user' AND v.alias LIKE '%@melix.net' AND vr.vid IS NOT NULL AND a.uid IS NULL; |
| b172472d | 175 | |
| b172472d | 176 | -- From aliases file |
| 67f95129 SJ |
177 | INSERT INTO email_virtual (domain, email, redirect, type) |
| 178 | VALUES (@p_domain_id, 'otrs.platal', 'otrs.platal@svoboda.polytechnique.org', 'admin'), | |
| 179 | (@p_domain_id, 'validation', 'hotliners@staff.polytechnique.org', 'admin'), | |
| 180 | (@p_domain_id, 'listes+admin', 'br@staff.polytechnique.org', 'admin'), | |
| 181 | (@p_domain_id, 'listes', 'otrs.platal+listes@polytechnique.org', 'admin'), | |
| 182 | (@p_domain_id, 'gld', 'listes@polytechnique.org', 'admin'), | |
| 183 | (@p_domain_id, 'support', 'otrs.platal+support@polytechnique.org', 'admin'), | |
| 184 | (@p_domain_id, 'contact', 'otrs.platal+contact@polytechnique.org', 'admin'), | |
| 185 | (@p_domain_id, 'register', 'otrs.platal+register@polytechnique.org', 'admin'), | |
| 186 | (@p_domain_id, 'info', 'otrs.platal+info@polytechnique.org', 'admin'), | |
| 187 | (@p_domain_id, 'bug', 'otrs.platal+bug@polytechnique.org', 'admin'), | |
| 188 | (@p_domain_id, 'resetpass', 'otrs.platal+resetpass@polytechnique.org', 'admin'), | |
| 189 | (@p_domain_id, 'association', 'otrs.platal+association@polytechnique.org', 'admin'), | |
| 190 | (@p_domain_id, 'x-org', 'association@polytechnique.org', 'admin'), | |
| 191 | (@p_domain_id, 'manageurs', 'otrs@support.manageurs.com', 'partner'), | |
| 192 | (@p_domain_id, 'fondation', 'fondation@fondationx.org', 'partner'), | |
| 193 | (@p_domain_id, 'ax', 'ax@wanadoo.fr', 'partner'), | |
| 194 | (@p_domain_id, 'annuaire-ax', 'annuaire-ax@wanadoo.fr', 'partner'), | |
| 195 | (@p_domain_id, 'ax-bdc', 'ax-bdc@wanadoo.fr', 'partner'), | |
| 196 | (@p_domain_id, 'jaune', 'null@hruid.polytechnique.org', 'partner'), | |
| 197 | (@p_domain_id, 'jaune+rouge', 'jaune_rouge@wanadoo.fr', 'partner'), | |
| 198 | (@p_domain_id, 'xcourseaularge', 'info@xcourseaularge.polytechnique.org', 'partner'), | |
| 199 | (@p_domain_id, 'xim', 'membres@x-internet.polytechnique.org', 'partner'), | |
| 200 | (@p_domain_id, 'x-consult', 'info@x-consult.polytechnique.org', 'partner'), | |
| 201 | (@p_domain_id, 'xmcb', 'xmcb@x-consult.polytechnique.org', 'partner'), | |
| 202 | (@p_domain_id, 'x-maroc', 'allam@mtpnet.gov.ma', 'partner'), | |
| 203 | (@p_domain_id, 'x-musique', 'xmusique@free.fr', 'partner'), | |
| 204 | (@p_domain_id, 'x-resistance', 'info@xresistance.org', 'partner'), | |
| 205 | (@p_domain_id, 'x-israel', 'info@x-israel.polytechnique.org', 'partner'), | |
| 206 | (@p_domain_id, 'gpx', 'g.p.x@infonie.fr', 'partner'), | |
| 207 | (@p_domain_id, 'g.p.x', 'gpx@polytechnique.org', 'partner'), | |
| 208 | (@p_domain_id, 'pointgamma', 'gamma@frankiz.polytechnique.fr', 'partner'), | |
| 209 | (@p_domain_id, 'xmpentrepreneur', 'xmp.entrepreneur@gmail.com', 'partner'), | |
| 210 | (@p_domain_id, 'xmp-entrepreneur', 'xmp.entrepreneur@gmail.com', 'partner'), | |
| 211 | (@p_domain_id, 'xmpangels', 'xmpangels@xmp-ba.m4x.org', 'partner'), | |
| 212 | (@p_domain_id, 'xmp-angels', 'xmpangels@xmp-ba.m4x.org', 'partner'), | |
| 213 | (@p_domain_id, 'relex', 'relex@staff.polytechnique.org', 'admin'), | |
| 214 | (@p_domain_id, 'tresorier', 'tresorier@staff.polytechnique.org', 'admin'), | |
| 215 | (@p_domain_id, 'aaege-sso', 'aaege-sso@staff.polytechnique.org', 'admin'), | |
| 216 | (@p_domain_id, 'innovation', 'innovation@staff.polytechnique.org', 'admin'), | |
| 217 | (@p_domain_id, 'groupes', 'groupes@staff.polytechnique.org', 'admin'), | |
| 218 | (@p_domain_id, 'br', 'br@staff.polytechnique.org', 'admin'), | |
| 219 | (@p_domain_id, 'ca', 'ca@staff.polytechnique.org', 'admin'), | |
| 220 | (@p_domain_id, 'personnel', 'br@staff.polytechnique.org', 'admin'), | |
| 221 | (@p_domain_id, 'cil', 'cil@staff.polytechnique.org', 'admin'), | |
| 222 | (@p_domain_id, 'opensource', 'contact@polytechnique.org', 'admin'), | |
| 223 | (@p_domain_id, 'forums', 'forums@staff.m4x.org', 'admin'), | |
| 224 | (@p_domain_id, 'telepaiement', 'telepaiement@staff.m4x.org', 'admin'), | |
| 225 | (@p_domain_id, 'hotliners', 'hotliners@staff.m4x.org', 'admin'), | |
| 226 | (@p_domain_id, 'kes', 'kes@frankiz.polytechnique.fr', 'partner'), | |
| 227 | (@p_domain_id, 'kes1999', 'cariokes@polytechnique.org', 'partner'), | |
| 228 | (@p_domain_id, 'kes2000', 'kestinpowers@polytechnique.org', 'partner'); | |
| 96156ab8 | 229 | |
| b172472d | 230 | -- Drop renamed list |
| 67f95129 | 231 | DELETE FROM email_virtual WHERE email LIKE 'tech-email%'; |
| b0e71a9f | 232 | |
| 6dcd580c SJ |
233 | -- Deletes erroneous domains |
| 234 | DELETE FROM email_virtual_domains | |
| 235 | WHERE name IN ('fanfarix.polytechnique.net', 'asd', 'x-russie', 'formation', 'groupetest', 'x-sursaut'); | |
| 236 | ||
| 909b70e3 SJ |
237 | -- Deletes unused domains |
| 238 | DELETE FROM email_virtual_domains | |
| 239 | WHERE name LIKE 'manageurs.%'; | |
| 240 | ||
| d0cd28d0 | 241 | -- vim:set syntax=mysql: |