projects / platal.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | blame (incremental) | history | HEAD
Merge branch 'xorg/1.0.2/master' into xorg/master
[platal.git] / upgrade / 1.1.0 / 02_new_mail_insertion.sql
CommitLineData
a187e160 1INSERT INTO email_source_account (uid,email,type,flags,expire)
e41717ad 2 SELECT a.uid,CONCAT(a.alias,'@polytechnique.org'),IF(a.type='a_vie','forlife','alias'),REPLACE(a.flags,'epouse','marital'),a.expire
96156ab8
JPAMAJ		 3 FROM aliases AS a
4 WHERE a.type = 'a_vie'
5 OR a.type = 'alias';
a187e160 6INSERT INTO email_source_account (uid,email,type,flags,expire)
e41717ad 7 SELECT a.uid,CONCAT(a.alias,'@m4x.org'),IF(a.type='a_vie','forlife','alias'),REPLACE(a.flags,'epouse','marital'),a.expire
96156ab8
JPAMAJ		 8 FROM aliases AS a
9 WHERE a.type = 'a_vie'
10 OR a.type = 'alias';
a187e160 11INSERT INTO email_source_account (uid,email,type,flags,expire)
e41717ad 12 SELECT a.uid,CONCAT(a.alias,'@m4x.net'),IF(a.type='a_vie','forlife','alias'),REPLACE(a.flags,'epouse','marital'),a.expire
96156ab8
JPAMAJ		 13 FROM aliases AS a
14 WHERE a.type = 'a_vie'
15 OR a.type = 'alias';
a187e160 16INSERT INTO email_source_account (uid,email,type)
96156ab8
JPAMAJ		 17 SELECT a.uid,v.alias,'alias'
18 FROM virtual AS v
19 LEFT JOIN virtual_redirect AS vr ON (v.vid=vr.vid)
20 LEFT JOIN accounts AS a ON ( a.hruid=LEFT(vr.redirect,LOCATE('@',vr.redirect)-1) )
21 WHERE v.type='user'
22 AND v.alias LIKE "%@melix.net"
23 AND a.uid IS NOT NULL;
a187e160 24INSERT INTO email_source_account (uid,email,type)
96156ab8
JPAMAJ		 25 SELECT a.uid,REPLACE(v.alias,'@melix.net','@melix.org'),'alias'
26 FROM virtual AS v
27 LEFT JOIN virtual_redirect AS vr ON (v.vid=vr.vid)
28 LEFT JOIN accounts AS a ON ( a.hruid=LEFT(vr.redirect,LOCATE('@',vr.redirect)-1) )
29 WHERE v.type='user'
30 AND v.alias LIKE "%@melix.net"
31 AND a.uid IS NOT NULL;
32
a187e160 33INSERT INTO email_source_other (hrmid,email,type)
96156ab8
JPAMAJ		 34 SELECT CONCAT(CONCAT('h.',a.alias),'.polytechnique.org'),
35 CONCAT(a.alias,'@polytechnique.org'),'homonym'
36 FROM aliases AS a
37 WHERE a.type = 'homonyme'
38 GROUP BY (a.alias);
a187e160 39INSERT INTO email_source_other (hrmid,email,type)
96156ab8
JPAMAJ		 40 SELECT CONCAT(CONCAT('h.',a.alias),'.polytechnique.org'),
41 CONCAT(a.alias,'@m4x.org'),'homonym'
42 FROM aliases AS a
43 WHERE a.type = 'homonyme'
44 GROUP BY (a.alias);
a187e160 45INSERT INTO email_source_other (hrmid,email,type)
96156ab8
JPAMAJ		 46 SELECT CONCAT(CONCAT('h.',a.alias),'.polytechnique.org'),
47 CONCAT(a.alias,'@m4x.net'),'homonym'
48 FROM aliases AS a
49 WHERE a.type = 'homonyme'
50 GROUP BY (a.alias);
51
1a4e11eb 52INSERT INTO homonyms_list (hrmid,uid)
96156ab8
JPAMAJ		 53 SELECT CONCAT(CONCAT('h.',a.alias),'.polytechnique.org'), h.uid
54 FROM homonyms AS h
55 INNER JOIN aliases AS a ON (a.uid=h.homonyme_id)
56 WHERE a.type = 'homonyme';
57
78b9df93 58INSERT INTO email_redirect_account (uid,redirect,rewrite,type,action,
b9dd7517 59 broken,broken_level,last,flags,hash,allow_rewrite)
78b9df93 60 SELECT a.uid,e.email,e.rewrite,'smtp',ef.email,
5e0f01c6 61 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
96156ab8
JPAMAJ		 62 FROM emails AS e
63 LEFT JOIN emails AS ef ON (e.uid=ef.uid)
64 LEFT JOIN accounts AS a ON (e.uid=a.uid)
65 WHERE e.flags != 'filter'
66 AND ef.flags = 'filter';
78b9df93
JPAMAJ		 67INSERT INTO email_redirect_account (uid,redirect,type,action,flags)
68 SELECT a.uid,CONCAT(a.hruid,"@g.polytechnique.org"),'googleapps',ef.email,'active'
96156ab8
JPAMAJ		 69 FROM email_options AS eo
70 LEFT JOIN accounts AS a ON (a.uid=eo.uid)
71 LEFT JOIN emails AS ef ON (eo.uid=ef.uid)
72 WHERE FIND_IN_SET('googleapps',eo.storage)
73 AND ef.flags="filter";
78b9df93
JPAMAJ		 74INSERT INTO email_redirect_account (uid,redirect,type,action,flags)
75 SELECT a.uid,CONCAT(a.hruid,"@imap.polytechnique.org"),'imap','let_spams','active'
96156ab8
JPAMAJ		 76 FROM email_options AS eo
77 LEFT JOIN accounts AS a ON (a.uid=eo.uid)
78 WHERE FIND_IN_SET('imap',eo.storage);
79
e41717ad
JPAMAJ		 80INSERT INTO email_redirect_other (hrmid,type,action)
81 SELECT eso.hrmid,'homonym','homonym'
82 FROM email_source_other AS eso
83 WHERE eso.type = 'homonym'
84 GROUP BY (eso.hrmid);
85
91ab69cd 86INSERT INTO email_virtual (email,redirect,type)
b9dd7517 87 SELECT v.alias,vr.redirect,IF(v.type='dom','domain',IF(v.type='evt','event',v.type))
96156ab8
JPAMAJ		 88 FROM virtual AS v
89 LEFT JOIN virtual_redirect AS vr ON (vr.vid=v.vid)
90 WHERE v.alias NOT LIKE "%@melix.net"
91 AND vr.vid IS NOT NULL
92 AND v.alias != '@melix.org';
91ab69cd 93INSERT INTO email_virtual (email,redirect,type)
96156ab8
JPAMAJ		 94 SELECT CONCAT(a.alias,"@polytechnique.org"),
95 CONCAT(
96 'polytechnique.org_',
97 REPLACE(
98 REPLACE(
99 REPLACE(
100 CONCAT(a.alias, '+post@listes.polytechnique.org'),
101 '-admin+post', '+admin'
102 ),
103 '-owner+post', '+owner'
104 ),
105 '-bounces+post', '+bounces'
106 )
107 ),
108 'list'
109 FROM aliases AS a
110 WHERE type='liste';
91ab69cd 111INSERT INTO email_virtual (email,redirect,type)
96156ab8
JPAMAJ		 112 SELECT CONCAT(a.alias,"@m4x.org"),
113 CONCAT(
114 'polytechnique.org_',
115 REPLACE(
116 REPLACE(
117 REPLACE(
118 CONCAT(a.alias, '+post@listes.polytechnique.org'),
119 '-admin+post', '+admin'
120 ),
121 '-owner+post', '+owner'
122 ),
123 '-bounces+post', '+bounces'
124 )
125 ),
126 'list'
127 FROM aliases AS a
128 WHERE type='liste';
91ab69cd 129INSERT INTO email_virtual (email,redirect,type)
96156ab8
JPAMAJ		 130 SELECT CONCAT(a.alias,"@m4x.net"),
131 CONCAT(
132 'polytechnique.org_',
133 REPLACE(
134 REPLACE(
135 REPLACE(
136 CONCAT(a.alias, '+post@listes.polytechnique.org'),
137 '-admin+post', '+admin'
138 ),
139 '-owner+post', '+owner'
140 ),
141 '-bounces+post', '+bounces'
142 )
143 ),
144 'list'
145 FROM aliases AS a
146 WHERE type='liste';
91ab69cd 147INSERT INTO email_virtual (email,redirect,type)
e78c8b94 148 SELECT v.alias,vr.redirect,'user'
96156ab8
JPAMAJ		 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'
153 AND v.alias LIKE "%@melix.net"
154 AND vr.vid IS NOT NULL
155 AND a.uid IS NULL;
91ab69cd 156INSERT INTO email_virtual (email,redirect,type)
e78c8b94 157 SELECT REPLACE(v.alias,'@melix.net','@melix.org'),vr.redirect,'user'
96156ab8
JPAMAJ		 158 FROM virtual AS v
159 LEFT JOIN virtual_redirect AS vr ON (v.vid=vr.vid)
160 LEFT JOIN accounts AS a ON ( a.hruid=LEFT(vr.redirect,LOCATE('@',vr.redirect)-1) )
161 WHERE v.type='user'
162 AND v.alias LIKE "%@melix.net"
163 AND vr.vid IS NOT NULL
164 AND a.uid IS NULL;
b172472d
FB		 165
166-- Note: There are some adresses on virtual that have no match on the virtual_redirect.
167-- The adresses in this situation are dropped.
96156ab8 168
96156ab8
JPAMAJ		 169INSERT INTO email_virtual_domains (domain)
170 VALUES ("polytechnique.org"),
171 ("m4x.org"),
172 ("m4x.net");
173INSERT INTO email_virtual_domains (domain)
174 SELECT domain
175 FROM virtual_domains;
176
b172472d 177-- From aliases file
91ab69cd 178INSERT INTO email_virtual (email,redirect,type) VALUES
96156ab8
JPAMAJ		 179 ("otrs.platal@polytechnique.org","otrs@svoboda.polytechnique.org","admin"),
180 ("otrs.platal@m4x.org","otrs.platal@polytechnique.org","admin"),
181 ("otrs.platal@m4x.net","otrs.platal@polytechnique.org","admin"),
182 ("validation@polytechnique.org","hotliners@staff.polytechnique.org","admin"),
183 ("validation@m4x.org","validation@polytechnique.org","admin"),
184 ("validation@m4x.net","validation@polytechnique.org","admin"),
185 ("listes+admin@polytechnique.org","br@staff.polytechnique.org","admin"),
186 ("listes+admin@m4x.org","listes+admin@polytechnique.org","admin"),
187 ("listes+admin@m4x.net","listes+admin@polytechnique.org","admin"),
188 ("listes@polytechnique.org","otrs.platal+listes@polytechnique.org","admin"),
189 ("listes@m4x.org","listes@polytechnique.org","admin"),
190 ("listes@m4x.net","listes@polytechnique.org","admin"),
191 ("gld@polytechnique.org","listes@polytechnique.org","admin"),
192 ("gld@m4x.org","gld@polytechnique.org","admin"),
193 ("gld@m4x.net","gld@polytechnique.org","admin"),
194 ("support@polytechnique.org","otrs.platal+support@polytechnique.org","admin"),
195 ("support@m4x.org","support@polytechnique.org","admin"),
196 ("support@m4x.net","support@polytechnique.org","admin"),
197 ("contact@polytechnique.org","otrs.platal+contact@polytechnique.org","admin"),
198 ("contact@m4x.org","contact@polytechnique.org","admin"),
199 ("contact@m4x.net","contact@polytechnique.org","admin"),
200 ("register@polytechnique.org","otrs.platal+register@polytechnique.org","admin"),
201 ("register@m4x.org","register@polytechnique.org","admin"),
202 ("register@m4x.net","register@polytechnique.org","admin"),
203 ("info@polytechnique.org","otrs.platal+info@polytechnique.org","admin"),
204 ("info@m4x.org","info@polytechnique.org","admin"),
205 ("info@m4x.net","info@polytechnique.org","admin"),
206 ("bug@polytechnique.org","otrs.platal+bug@polytechnique.org","admin"),
207 ("bug@m4x.org","bug@polytechnique.org","admin"),
208 ("bug@m4x.net","bug@polytechnique.org","admin"),
209 ("resetpass@polytechnique.org","otrs.platal+resetpass@polytechnique.org","admin"),
210 ("resetpass@m4x.org","resetpass@polytechnique.org","admin"),
211 ("resetpass@m4x.net","resetpass@polytechnique.org","admin"),
212 ("association@polytechnique.org","otrs.platal+association@polytechnique.org","admin"),
213 ("association@m4x.org","association@polytechnique.org","admin"),
214 ("association@m4x.net","association@polytechnique.org","admin"),
215 ("x-org@polytechnique.org","association@polytechnique.org","admin"),
216 ("x-org@m4x.org","x-org@polytechnique.org","admin"),
217 ("x-org@m4x.net","x-org@polytechnique.org","admin"),
218 ("manageurs@polytechnique.org","otrs@support.manageurs.com","partner"),
219 ("manageurs@m4x.org","manageurs@polytechnique.org","partner"),
220 ("manageurs@m4x.net","manageurs@polytechnique.org","partner"),
221 ("fondation@polytechnique.org","fondation@fondationx.org","partner"),
222 ("fondation@m4x.org","fondation@polytechnique.org","partner"),
223 ("fondation@m4x.net","fondation@polytechnique.org","partner"),
224 ("ax@polytechnique.org","ax@wanadoo.fr","partner"),
225 ("ax@m4x.org","ax@polytechnique.org","partner"),
226 ("ax@m4x.net","ax@polytechnique.org","partner"),
227 ("annuaire-ax@polytechnique.org","annuaire-ax@wanadoo.fr","partner"),
228 ("annuaire-ax@m4x.org","annuaire-ax@polytechnique.org","partner"),
229 ("annuaire-ax@m4x.net","annuaire-ax@polytechnique.org","partner"),
230 ("ax-bdc@polytechnique.org","ax-bdc@wanadoo.fr","partner"),
231 ("ax-bdc@m4x.org","ax-bdc@polytechnique.org","partner"),
232 ("ax-bdc@m4x.net","ax-bdc@polytechnique.org","partner"),
233 ("jaune@polytechnique.org","null@hruid.polytechnique.org","partner"),
234 ("jaune@m4x.org","jaune@polytechnique.org","partner"),
235 ("jaune@m4x.net","jaune@polytechnique.org","partner"),
236 ("jaune+rouge@polytechnique.org","jaune_rouge@wanadoo.fr","partner"),
237 ("jaune+rouge@m4x.org","jaune+rouge@polytechnique.org","partner"),
238 ("jaune+rouge@m4x.net","jaune+rouge@polytechnique.org","partner"),
239 ("xcourseaularge@polytechnique.org","info@xcourseaularge.polytechnique.org","partner"),
240 ("xcourseaularge@m4x.org","xcourseaularge@polytechnique.org","partner"),
241 ("xcourseaularge@m4x.net","xcourseaularge@polytechnique.org","partner"),
242 ("xim@polytechnique.org","membres@x-internet.polytechnique.org","partner"),
243 ("xim@m4x.org","xim@polytechnique.org","partner"),
244 ("xim@m4x.net","xim@polytechnique.org","partner"),
245 ("x-consult@polytechnique.org","info@x-consult.polytechnique.org","partner"),
246 ("x-consult@m4x.org","x-consult@polytechnique.org","partner"),
247 ("x-consult@m4x.net","x-consult@polytechnique.org","partner"),
248 ("xmcb@polytechnique.org","xmcb@x-consult.polytechnique.org","partner"),
249 ("xmcb@m4x.org","xmcb@polytechnique.org","partner"),
250 ("xmcb@m4x.net","xmcb@polytechnique.org","partner"),
251 ("x-maroc@polytechnique.org","allam@mtpnet.gov.ma","partner"),
252 ("x-maroc@m4x.org","x-maroc@polytechnique.org","partner"),
253 ("x-maroc@m4x.net","x-maroc@polytechnique.org","partner"),
254 ("x-musique@polytechnique.org","xmusique@free.fr","partner"),
255 ("x-musique@m4x.org","x-musique@polytechnique.org","partner"),
256 ("x-musique@m4x.net","x-musique@polytechnique.org","partner"),
257 ("x-resistance@polytechnique.org","info@xresistance.org","partner"),
258 ("x-resistance@m4x.org","x-resistance@polytechnique.org","partner"),
259 ("x-resistance@m4x.net","x-resistance@polytechnique.org","partner"),
260 ("x-israel@polytechnique.org","info@x-israel.polytechnique.org","partner"),
261 ("x-israel@m4x.org","x-israel@polytechnique.org","partner"),
262 ("x-israel@m4x.net","x-israel@polytechnique.org","partner"),
263 ("gpx@polytechnique.org","g.p.x@infonie.fr","partner"),
264 ("gpx@m4x.org","gpx@polytechnique.org","partner"),
265 ("gpx@m4x.net","gpx@polytechnique.org","partner"),
266 ("g.p.x@polytechnique.org","gpx@polytechnique.org","partner"),
267 ("g.p.x@m4x.org","g.p.x@polytechnique.org","partner"),
268 ("g.p.x@m4x.net","g.p.x@polytechnique.org","partner"),
269 ("pointgamma@polytechnique.org","gamma@frankiz.polytechnique.fr","partner"),
270 ("pointgamma@m4x.org","pointgamma@polytechnique.org","partner"),
271 ("pointgamma@m4x.net","pointgamma@polytechnique.org","partner"),
272 ("xmpentrepreneur@polytechnique.org","xmp.entrepreneur@gmail.com","partner"),
273 ("xmpentrepreneur@m4x.org","xmpentrepreneur@polytechnique.org","partner"),
274 ("xmpentrepreneur@m4x.net","xmpentrepreneur@polytechnique.org","partner"),
275 ("xmp-entrepreneur@polytechnique.org","xmp.entrepreneur@gmail.com","partner"),
276 ("xmp-entrepreneur@m4x.org","xmp-entrepreneur@polytechnique.org","partner"),
277 ("xmp-entrepreneur@m4x.net","xmp-entrepreneur@polytechnique.org","partner"),
278 ("xmpangels@polytechnique.org","xmpangels@xmp-ba.m4x.org","partner"),
279 ("xmpangels@m4x.org","xmpangels@polytechnique.org","partner"),
280 ("xmpangels@m4x.net","xmpangels@polytechnique.org","partner"),
281 ("xmp-angels@polytechnique.org","xmpangels@xmp-ba.m4x.org","partner"),
282 ("xmp-angels@m4x.org","xmp-angels@polytechnique.org","partner"),
283 ("xmp-angels@m4x.net","xmp-angels@polytechnique.org","partner"),
284 ("relex@polytechnique.org","relex@staff.polytechnique.org","admin"),
285 ("relex@m4x.org","relex@polytechnique.org","admin"),
286 ("relex@m4x.net","relex@polytechnique.org","admin"),
287 ("tresorier@polytechnique.org","tresorier@staff.polytechnique.org","admin"),
288 ("tresorier@m4x.org","tresorier@polytechnique.org","admin"),
289 ("tresorier@m4x.net","tresorier@polytechnique.org","admin"),
290 ("aaege-sso@polytechnique.org","aaege-sso@staff.polytechnique.org","admin"),
291 ("aaege-sso@m4x.org","aaege-sso@polytechnique.org","admin"),
292 ("aaege-sso@m4x.net","aaege-sso@polytechnique.org","admin"),
293 ("innovation@polytechnique.org","innovation@staff.polytechnique.org","admin"),
294 ("innovation@m4x.org","innovation@polytechnique.org","admin"),
295 ("innovation@m4x.net","innovation@polytechnique.org","admin"),
296 ("groupes@polytechnique.org","groupes@staff.polytechnique.org","admin"),
297 ("groupes@m4x.org","groupes@polytechnique.org","admin"),
298 ("groupes@m4x.net","groupes@polytechnique.org","admin"),
299 ("br@polytechnique.org","br@staff.polytechnique.org","admin"),
300 ("br@m4x.org","br@polytechnique.org","admin"),
301 ("br@m4x.net","br@polytechnique.org","admin"),
302 ("ca@polytechnique.org","ca@staff.polytechnique.org","admin"),
303 ("ca@m4x.org","ca@polytechnique.org","admin"),
304 ("ca@m4x.net","ca@polytechnique.org","admin"),
305 ("personnel@polytechnique.org","br@staff.polytechnique.org","admin"),
306 ("personnel@m4x.org","personnel@polytechnique.org","admin"),
307 ("personnel@m4x.net","personnel@polytechnique.org","admin"),
308 ("cil@polytechnique.org","cil@staff.polytechnique.org","admin"),
309 ("cil@m4x.org","cil@polytechnique.org","admin"),
310 ("cil@m4x.net","cil@polytechnique.org","admin"),
311 ("opensource@polytechnique.org","contact@polytechnique.org","admin"),
312 ("opensource@m4x.org","opensource@polytechnique.org","admin"),
313 ("opensource@m4x.net","opensource@polytechnique.org","admin"),
314 ("forums@polytechnique.org","forums@staff.m4x.org","admin"),
315 ("forums@m4x.org","forums@polytechnique.org","admin"),
316 ("forums@m4x.net","forums@polytechnique.org","admin"),
317 ("telepaiement@polytechnique.org","telepaiement@staff.m4x.org","admin"),
318 ("telepaiement@m4x.org","telepaiement@polytechnique.org","admin"),
319 ("telepaiement@m4x.net","telepaiement@polytechnique.org","admin"),
320 ("hotliners@polytechnique.org","hotliners@staff.m4x.org","admin"),
321 ("hotliners@m4x.org","hotliners@polytechnique.org","admin"),
322 ("hotliners@m4x.net","hotliners@polytechnique.org","admin"),
323 ("kes@polytechnique.org","kes@frankiz.polytechnique.fr","partner"),
324 ("kes@m4x.org","kes@polytechnique.org","partner"),
325 ("kes@m4x.net","kes@polytechnique.org","partner"),
326 ("kes1999@polytechnique.org","cariokes@polytechnique.org","partner"),
327 ("kes1999@m4x.org","kes1999@polytechnique.org","partner"),
328 ("kes1999@m4x.net","kes1999@polytechnique.org","partner"),
329 ("kes2000@polytechnique.org","kestinpowers@polytechnique.org","partner"),
330 ("kes2000@m4x.org","kes2000@polytechnique.org","partner"),
331 ("kes2000@m4x.net","kes2000@polytechnique.org","partner");
332
a187e160 333INSERT INTO email_source_other (hrmid,email,type) VALUES
96156ab8
JPAMAJ		 334 ("ax.test.polytechnique.org","AX-test@polytechnique.org","ax"),
335 ("ax.test.polytechnique.org","AX-test@m4x.org","ax"),
336 ("ax.test.polytechnique.org","AX-test@m4x.net","ax"),
337 ("ax.nicolas.zarpas.polytechnique.org","AX-nicolas.zarpas@polytechnique.org","ax"),
338 ("ax.nicolas.zarpas.polytechnique.org","AX-nicolas.zarpas@m4x.org","ax"),
339 ("ax.nicolas.zarpas.polytechnique.org","AX-nicolas.zarpas@m4x.net","ax"),
340 ("ax.carrieres.polytechnique.org","AX-carrieres@polytechnique.org","ax"),
341 ("ax.carrieres.polytechnique.org","AX-carrieres@m4x.org","ax"),
342 ("ax.carrieres.polytechnique.org","AX-carrieres@m4x.net","ax"),
343 ("ax.info1.polytechnique.org","AX-info1@polytechnique.org","ax"),
344 ("ax.info1.polytechnique.org","AX-info1@m4x.org","ax"),
345 ("ax.info1.polytechnique.org","AX-info1@m4x.net","ax"),
346 ("ax.info2.polytechnique.org","AX-info2@polytechnique.org","ax"),
347 ("ax.info2.polytechnique.org","AX-info2@m4x.org","ax"),
348 ("ax.info2.polytechnique.org","AX-info2@m4x.net","ax"),
349 ("ax.bal.polytechnique.org","AX-bal@polytechnique.org","ax"),
350 ("ax.bal.polytechnique.org","AX-bal@m4x.org","ax"),
351 ("ax.bal.polytechnique.org","AX-bal@m4x.net","ax"),
352 ("ax.annuaire.polytechnique.org","AX-annuaire@polytechnique.org","ax"),
353 ("ax.annuaire.polytechnique.org","AX-annuaire@m4x.org","ax"),
354 ("ax.annuaire.polytechnique.org","AX-annuaire@m4x.net","ax"),
355 ("ax.jaune-rouge.polytechnique.org","AX-jaune-rouge@polytechnique.org","ax"),
356 ("ax.jaune-rouge.polytechnique.org","AX-jaune-rouge@m4x.org","ax"),
357 ("ax.jaune-rouge.polytechnique.org","AX-jaune-rouge@m4x.net","ax"),
358 ("honey.jean-pierre.bilah.1980.polytechnique.org","jean-pierre.bilah.1980@polytechnique.org","honeypot"),
359 ("honey.jean-pierre.bilah.1980.polytechnique.org","jean-pierre.bilah.1980@m4x.org","honeypot"),
360 ("honey.jean-pierre.bilah.1980.polytechnique.org","jean-pierre.bilah.1980@m4x.net","honeypot"),
361 ("honey.jean-pierre.bilah.1980.polytechnique.org","jean-pierre.blah.1980@polytechnique.org","honeypot"),
362 ("honey.jean-pierre.bilah.1980.polytechnique.org","jean-pierre.blah.1980@m4x.org","honeypot"),
363 ("honey.jean-pierre.bilah.1980.polytechnique.org","jean-pierre.blah.1980@m4x.net","honeypot");
364
78b9df93
JPAMAJ		 365INSERT INTO email_redirect_other (hrmid,redirect,type,action) VALUES
366 ("ax.nicolas.zarpas.polytechnique.org","nicolas.zarpas-ax@wanadoo.fr","smtp","tag_spams"),
367 ("ax.carrieres.polytechnique.org","manuela.brasseur-bdc@wanadoo.fr","smtp","tag_spams"),
368 ("ax.info1.polytechnique.org","sylvie.clairefond-ax@wanadoo.fr","smtp","tag_spams"),
369 ("ax.info2.polytechnique.org","catherine.perot-ax@wanadoo.fr","smtp","tag_spams"),
370 ("ax.bal.polytechnique.org","baldelx-ax@wanadoo.fr","smtp","tag_spams"),
371 ("ax.annuaire.polytechnique.org","annuaire-ax@wanadoo.fr","smtp","tag_spams"),
372 ("ax.jaune-rouge.polytechnique.org","jaune_rouge@wanadoo.fr","smtp","tag_spams"),
373 ("honey.jean-pierre.bilah.1980.polytechnique.org","jean-pierre.bilah.1980.mbox@murphy.m4x.org","smtp","let_spams"),
374 ("honey.jean-pierre.bilah.1980.polytechnique.org","raphael.barrois.2006@polytechnique.org","smtp","let_spams");
96156ab8 375
b172472d 376-- Drop renamed list
91ab69cd
JPAMAJ		 377DELETE FROM email_virtual WHERE email LIKE "tech-email%@polytechnique.org";
378DELETE FROM email_virtual WHERE email LIKE "tech-email%@m4x.org";
379DELETE FROM email_virtual WHERE email LIKE "tech-email%@m4x.net";
d0cd28d0 380
b172472d
FB		 381-- Imap and bounce
382UPDATE email_redirect_account AS e,
383 (SELECT IF( SUM(IF(type!='imap',1,0))=0, 'imap_only', 'normal' ) AS status, uid
384 FROM email_redirect_account
385 WHERE flags = 'active'
386 GROUP BY uid) AS sub
387 SET e.action='imap_and_bounce'
388 WHERE sub.status='imap_only'
389 AND sub.uid = e.uid
390 AND type='imap';
b0e71a9f 391
d0cd28d0 392-- vim:set syntax=mysql:
plat/al