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