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: |