a51987c7b7bde5c66d93419f30cbb949479a1f06
1 -- 1/ Feeds email_virtual_domains from virtual_domains and aliases.
3 INSERT INTO email_virtual_domains (name)
4 VALUES ('polytechnique.org');
5 INSERT INTO email_virtual_domains (name)
8 UPDATE email_virtual_domains
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';
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
)
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';
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
);
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
69 WHERE type = 'a_vie' OR type = 'alias';
70 INSERT INTO email_source_account (uid
, domain, email
, type)
71 SELECT a.uid
, @m_domain_id
, SUBSTRING_INDEX(v.
alias, '@', 1), 'alias_aux'
73 LEFT JOIN virtual_redirect
AS vr
ON (v.vid
= vr.vid
)
74 LEFT JOIN accounts
AS a
ON (a.hruid
= LEFT(vr.redirect
, LOCATE('@', vr.redirect
) - 1))
75 WHERE v.
type = 'user' AND v.
alias LIKE '%@melix.net' AND a.uid
IS NOT NULL;
77 -- 3/ Feeds email_source_other
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
)
81 WHERE type = 'homonyme'
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
);
95 -- 4/ Feeds homonyms_list
96 INSERT INTO homonyms_list (hrmid
, uid
)
97 SELECT CONCAT(CONCAT('h.', a.
alias), '.polytechnique.org'), h.uid
99 INNER JOIN aliases
AS a
ON (a.uid
= h.homonyme_id
)
100 WHERE a.
type = 'homonyme';
102 -- 5/ Feeds email_redirect_account
103 INSERT INTO email_redirect_account (uid
, redirect
, rewrite
, type, action, broken_date
, broken_level
, last, flags
, hash
, allow_rewrite
)
104 SELECT e.uid
, e.email
, e.rewrite
, 'smtp', ef.email
, e.panne
, e.panne_level
, e.
last,
105 IF(e.flags
= '', 'inactive', IF(e.flags
= 'disable', 'disabled', IF(e.flags
= 'panne', 'broken', e.flags
))), e.hash
, e.allow_rewrite
107 LEFT JOIN emails
AS ef
ON (e.uid
= ef.uid
)
108 WHERE e.flags
!= 'filter' AND ef.flags
= 'filter';
109 INSERT INTO email_redirect_account (uid
, redirect
, type, action, flags
)
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';
115 INSERT INTO email_redirect_account (uid
, redirect
, type, action, flags
)
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);
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'
127 SET e.
action = 'imap_and_bounce'
128 WHERE sub.status
= 'imap_only' AND sub.uid
= e.uid
AND type = 'imap';
130 -- 6/ Feeds email_redirect_other
131 INSERT INTO email_redirect_other (hrmid
, type, action)
132 SELECT hrmid
, 'homonym', 'homonym'
133 FROM email_source_other
134 WHERE type = 'homonym'
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');
148 -- 7/ Feeds email_virtual
150 -- Note: There are some adresses on virtual that have no match on the virtual_redirect.
151 -- The adresses in this situation are dropped.
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)
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
)
158 WHERE v.
alias NOT LIKE '%@melix.net' AND vr.vid
IS NOT NULL AND v.
type != 'dom';
160 INSERT INTO email_virtual (email
, type, domain, redirect
)
161 SELECT alias, 'list', @p_domain_id
,
162 CONCAT('polytechnique.org_', REPLACE(REPLACE(REPLACE(CONCAT(alias, '+post@listes.polytechnique.org'),
163 '-admin+post', '+admin'),
164 '-owner+post', '+owner'),
165 '-bounces+post', '+bounces'))
167 WHERE type = 'liste';
169 INSERT INTO email_virtual (email
, redirect
, domain, type)
170 SELECT SUBSTRING_INDEX(v.
alias, '@', 1), vr.redirect
, @m_domain_id
, 'alias'
172 LEFT JOIN virtual_redirect
AS vr
ON (v.vid
= vr.vid
)
173 LEFT JOIN accounts
AS a
ON (a.hruid
= LEFT(vr.redirect
, LOCATE('@', vr.redirect
) - 1))
174 WHERE v.
type = 'user' AND v.
alias LIKE '%@melix.net' AND vr.vid
IS NOT NULL AND a.uid
IS NULL;
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');
231 DELETE FROM email_virtual
WHERE email
LIKE 'tech-email%';
233 -- Deletes erroneous domains
234 DELETE FROM email_virtual_domains
235 WHERE name IN ('fanfarix.polytechnique.net', 'asd', 'x-russie', 'formation', 'groupetest', 'x-sursaut');
237 -- Deletes unused domains
238 DELETE FROM email_virtual_domains
239 WHERE name LIKE 'manageurs.%';
241 -- vim:set syntax=mysql: