FROM email_virtual_domains
WHERE name = 'x-consult.polytechnique.org';
+INSERT INTO email_virtual_domains (name, aliasing)
+ VALUES ('alumni.polytechnique.org', @p_domain_id), ('alumni.m4x.org', @p_domain_id),
+ ('master.polytechnique.org', 1), ('doc.polytechnique.org', 1);
+SET @master_domain_id = 0;
+SET @doc_domain_id = 0;
+SELECT @master_domain_id := id
+ FROM email_virtual_domains
+ WHERE name = 'master.polytechnique.org';
+SELECT @doc_domain_id := id
+ FROM email_virtual_domains
+ WHERE name = 'doc.polytechnique.org';
+UPDATE email_virtual_domains
+ SET aliasing = @master_domain_id
+ WHERE name = 'master.polytechnique.org';
+UPDATE email_virtual_domains
+ SET aliasing = @doc_domain_id
+ WHERE name = 'doc.polytechnique.org';
+INSERT INTO email_virtual_domains (name, aliasing)
+ VALUES ('alumni.polytechnique.org', @master_domain_id), ('alumni.m4x.org', @master_domain_id),
+ ('alumni.polytechnique.org', @doc_domain_id), ('alumni.m4x.org', @doc_domain_id),
+ ('master.m4x.org', @master_domain_id), ('doc.m4x.org', @doc_domain_id);
+
-- 2/ Feeds email_source_account
INSERT INTO email_source_account (uid, domain, email, type, flags, expire)
SELECT uid, @p_domain_id, alias, IF(type = 'a_vie', 'forlife', 'alias'), REPLACE(flags, 'epouse', 'marital'), expire
FROM aliases
WHERE type = 'a_vie' OR type = 'alias';
INSERT INTO email_source_account (uid, domain, email, type)
- SELECT a.uid, @m_domain_id, SUBSTRING_INDEX(v.alias, '@', 1), 'alias'
+ SELECT a.uid, @m_domain_id, SUBSTRING_INDEX(v.alias, '@', 1), 'alias_aux'
FROM virtual AS v
LEFT JOIN virtual_redirect AS vr ON (v.vid = vr.vid)
LEFT JOIN accounts AS a ON (a.hruid = LEFT(vr.redirect, LOCATE('@', vr.redirect) - 1))
-- 5/ Feeds email_redirect_account
INSERT INTO email_redirect_account (uid, redirect, rewrite, type, action, broken_date, broken_level, last, flags, hash, allow_rewrite)
- SELECT a.uid, e.email, e.rewrite, 'smtp', ef.email, e.panne, e.panne_level, e.last,
+ SELECT e.uid, e.email, e.rewrite, 'smtp', ef.email, 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
FROM emails AS e
LEFT JOIN emails AS ef ON (e.uid = ef.uid)
- LEFT JOIN accounts AS a ON (e.uid = a.uid)
WHERE e.flags != 'filter' AND ef.flags = 'filter';
INSERT INTO email_redirect_account (uid, redirect, type, action, flags)
SELECT a.uid, CONCAT(a.hruid, '@g.polytechnique.org'), 'googleapps', ef.email, 'active'
FROM virtual AS v
INNER JOIN email_virtual_domains AS d ON (SUBSTRING_INDEX(v.alias, '@', -1) = d.name AND d.id = d.aliasing)
LEFT JOIN virtual_redirect AS vr ON (vr.vid = v.vid)
- WHERE v.alias NOT LIKE '%@melix.net' AND vr.vid IS NOT NULL AND v.alias != '@melix.org' AND v.type != 'dom';
+ WHERE v.alias NOT LIKE '%@melix.net' AND vr.vid IS NOT NULL AND v.type != 'dom';
INSERT INTO email_virtual (email, type, domain, redirect)
SELECT alias, 'list', @p_domain_id,
WHERE type = 'liste';
INSERT INTO email_virtual (email, redirect, domain, type)
- SELECT SUBSTRING_INDEX(v.alias, '@', 1), vr.redirect, @m_domain_id, 'user'
+ SELECT SUBSTRING_INDEX(v.alias, '@', 1), vr.redirect, @m_domain_id, 'alias'
FROM virtual AS v
LEFT JOIN virtual_redirect AS vr ON (v.vid = vr.vid)
LEFT JOIN accounts AS a ON (a.hruid = LEFT(vr.redirect, LOCATE('@', vr.redirect) - 1))
-- Drop renamed list
DELETE FROM email_virtual WHERE email LIKE 'tech-email%';
+-- Deletes erroneous domains
+DELETE FROM email_virtual_domains
+ WHERE name IN ('fanfarix.polytechnique.net', 'asd', 'x-russie', 'formation', 'groupetest', 'x-sursaut');
+
+-- Deletes unused domains
+DELETE FROM email_virtual_domains
+ WHERE name LIKE 'manageurs.%';
+
-- vim:set syntax=mysql: