1 INSERT INTO email_source_account (uid
, email
, type, flags
, expire
)
2 SELECT uid
, CONCAT(alias, '@polytechnique.org'), IF(type = 'a_vie', 'forlife', 'alias'), REPLACE(flags
, 'epouse', 'marital'), expire
4 WHERE type = 'a_vie' OR type = 'alias';
5 INSERT INTO email_source_account (uid
, email
, type, flags
, expire
)
6 SELECT uid
, CONCAT(alias, '@m4x.org'), IF(type = 'a_vie', 'forlife', 'alias'), REPLACE(flags
, 'epouse', 'marital'), expire
8 WHERE type = 'a_vie' OR type = 'alias';
9 INSERT INTO email_source_account (uid
, email
, type)
10 SELECT a.uid
, v.
alias, 'alias'
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;
15 INSERT INTO email_source_account (uid
, email
, type)
16 SELECT a.uid
, REPLACE(v.
alias, '@melix.net', '@melix.org'), 'alias'
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;
22 INSERT INTO email_source_other (hrmid
, email
, type)
23 SELECT CONCAT(CONCAT('h.', alias), '.polytechnique.org'), CONCAT(alias, '@polytechnique.org'), 'homonym'
25 WHERE type = 'homonyme'
27 INSERT INTO email_source_other (hrmid
, email
, type)
28 SELECT CONCAT(CONCAT('h.', alias), '.polytechnique.org'), CONCAT(alias, '@m4x.org'), 'homonym'
30 WHERE type = 'homonyme'
33 INSERT INTO homonyms_list (hrmid
, uid
)
34 SELECT CONCAT(CONCAT('h.', a.
alias), '.polytechnique.org'), h.uid
36 INNER JOIN aliases
AS a
ON (a.uid
= h.homonyme_id
)
37 WHERE a.
type = 'homonyme';
39 INSERT INTO email_redirect_account (uid
, redirect
, rewrite
, type, action, broken_date
, broken_level
, last, flags
, hash
, allow_rewrite
)
40 SELECT a.uid
, e.email
, e.rewrite
, 'smtp', ef.email
, e.panne
, e.panne_level
, e.
last,
41 IF(e.flags
= '', 'inactive', IF(e.flags
= 'disable', 'disabled', IF(e.flags
= 'panne', 'broken', e.flags
))), e.hash
, e.allow_rewrite
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';
46 INSERT INTO email_redirect_account (uid
, redirect
, type, action, flags
)
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';
52 INSERT INTO email_redirect_account (uid
, redirect
, type, action, flags
)
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);
58 INSERT INTO email_redirect_other (hrmid
, type, action)
59 SELECT hrmid
, 'homonym', 'homonym'
60 FROM email_source_other
61 WHERE type = 'homonym'
64 INSERT INTO email_virtual (email
, redirect
, type)
65 SELECT v.
alias, vr.redirect
, IF(v.
type = 'dom', 'domain', IF(v.
type = 'evt', 'event', v.
type))
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';
69 INSERT INTO email_virtual (email
, type, redirect
)
70 SELECT CONCAT(alias, '@polytechnique.org'), 'list',
71 CONCAT('polytechnique.org_', REPLACE(REPLACE(REPLACE(CONCAT(alias, '+post@listes.polytechnique.org'),
72 '-admin+post', '+admin'),
73 '-owner+post', '+owner'),
74 '-bounces+post', '+bounces'))
77 INSERT INTO email_virtual (email
, type, redirect
)
78 SELECT CONCAT(alias, '@m4x.org'), 'list',
79 CONCAT('polytechnique.org_', REPLACE(REPLACE(REPLACE(CONCAT(alias, '+post@listes.polytechnique.org'),
80 '-admin+post', '+admin'),
81 '-owner+post', '+owner'),
82 '-bounces+post', '+bounces'))
85 INSERT INTO email_virtual (email
, redirect
, type)
86 SELECT v.
alias, vr.redirect
, 'user'
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;
91 INSERT INTO email_virtual (email
, redirect
, type)
92 SELECT REPLACE(v.
alias, '@melix.net', '@melix.org'), vr.redirect
, 'user'
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;
98 -- Note: There are some adresses on virtual that have no match on the virtual_redirect.
99 -- The adresses in this situation are dropped.
101 INSERT INTO email_virtual_domains (domain)
102 VALUES ('polytechnique.org'), ('m4x.org');
103 INSERT INTO email_virtual_domains (domain)
105 FROM virtual_domains
;
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');
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');
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');
246 DELETE FROM email_virtual
WHERE email
LIKE 'tech-email%@polytechnique.org';
247 DELETE FROM email_virtual
WHERE email
LIKE 'tech-email%@m4x.org';
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'
255 SET e.
action = 'imap_and_bounce'
256 WHERE sub.status
= 'imap_only' AND sub.uid
= e.uid
AND type = 'imap';
258 -- vim:set syntax=mysql: