upgrade/1.1.0/02_new_mail_insertion.sql

   1 -- 1/ Feeds email_virtual_domains from virtual_domains and aliases.
   2
   3 -- Note: There are some adresses on virtual that have no match on the virtual_redirect.
   4 --       The adresses in this situation are dropped.
   5
   6 INSERT INTO  email_virtual_domains (name)
   7      VALUES  ('polytechnique.org');
   8 INSERT INTO  email_virtual_domains (name)
   9      SELECT  domain
  10        FROM  virtual_domains;
  11 UPDATE  email_virtual_domains
  12    SET  aliasing = id;
  13
  14 SET @p_domain_id = 0;
  15 SET @m_domain_id = 0;
  16 SELECT  @p_domain_id := id
  17   FROM  email_virtual_domains
  18  WHERE  name = 'polytechnique.org';
  19 SELECT  @m_domain_id := id
  20   FROM  email_virtual_domains
  21  WHERE  name = 'melix.net';
  22
  23 DELETE FROM  email_virtual_domains
  24       WHERE  name IN ('m4x.org', 'melix.org', 'x-banque.m4x.org', 'staff.m4x.org', 'x-consult.m4x.org');
  25 INSERT INTO  email_virtual_domains (name, aliasing)
  26      SELECT  'm4x.org', id
  27        FROM  email_virtual_domains
  28       WHERE  name = 'polytechnique.org';
  29 INSERT INTO  email_virtual_domains (name, aliasing)
  30      SELECT  'melix.org', id
  31        FROM  email_virtual_domains
  32       WHERE  name = 'melix.net';
  33 INSERT INTO  email_virtual_domains (name, aliasing)
  34      SELECT  'x-banque.m4x.org', id
  35        FROM  email_virtual_domains
  36       WHERE  name = 'x-banque.polytechnique.org';
  37 INSERT INTO  email_virtual_domains (name, aliasing)
  38      SELECT  'staff.m4x.org', id
  39        FROM  email_virtual_domains
  40       WHERE  name = 'staff.polytechnique.org';
  41 INSERT INTO  email_virtual_domains (name, aliasing)
  42      SELECT  'x-consult.m4x.org', id
  43        FROM  email_virtual_domains
  44       WHERE  name = 'x-consult.polytechnique.org';
  45
  46 -- 2/ Feeds email_source_account
  47 INSERT INTO  email_source_account (uid, domain, email, type, flags, expire)
  48      SELECT  uid, @p_domain_id, alias, IF(type = 'a_vie', 'forlife', 'alias'), REPLACE(flags, 'epouse', 'marital'), expire
  49        FROM  aliases
  50       WHERE  type = 'a_vie' OR type = 'alias';
  51 INSERT INTO  email_source_account (uid, domain, email, type)
  52      SELECT  a.uid, @m_domain_id, SUBSTRING_INDEX(v.alias, '@', 1), 'alias'
  53        FROM  virtual          AS v
  54   LEFT JOIN  virtual_redirect AS vr ON (v.vid = vr.vid)
  55   LEFT JOIN  accounts         AS a  ON (a.hruid = LEFT(vr.redirect, LOCATE('@', vr.redirect) - 1))
  56       WHERE  v.type = 'user' AND v.alias LIKE '%@melix.net' AND a.uid IS NOT NULL;
  57
  58 -- 3/ Feeds email_source_other
  59 INSERT INTO  email_source_other (hrmid, email, domain, type, expire)
  60      SELECT  CONCAT(CONCAT('h.', alias), '.polytechnique.org'), alias, @p_domain_id, 'homonym', IF(expire IS NULL, '0000-00-00', expire)
  61        FROM  aliases
  62       WHERE  type = 'homonyme'
  63    GROUP BY  alias;
  64 INSERT INTO  email_source_other (hrmid, email, type, domain)
  65      VALUES  ('ax.test.polytechnique.org', 'AX-test', 'ax', @p_domain_id),
  66              ('ax.nicolas.zarpas.polytechnique.org', 'AX-nicolas.zarpas', 'ax', @p_domain_id),
  67              ('ax.carrieres.polytechnique.org', 'AX-carrieres', 'ax', @p_domain_id),
  68              ('ax.info1.polytechnique.org', 'AX-info1', 'ax', @p_domain_id),
  69              ('ax.info2.polytechnique.org', 'AX-info2', 'ax', @p_domain_id),
  70              ('ax.bal.polytechnique.org', 'AX-bal', 'ax', @p_domain_id),
  71              ('ax.annuaire.polytechnique.org', 'AX-annuaire', 'ax', @p_domain_id),
  72              ('ax.jaune-rouge.polytechnique.org', 'AX-jaune-rouge', 'ax', @p_domain_id),
  73              ('honey.jean-pierre.bilah.1980.polytechnique.org', 'jean-pierre.bilah.1980', 'honeypot', @p_domain_id),
  74              ('honey.jean-pierre.bilah.1980.polytechnique.org', 'jean-pierre.blah.1980', 'honeypot', @p_domain_id);
  75
  76 -- 4/ Feeds homonyms_list
  77 INSERT INTO  homonyms_list (hrmid, uid)
  78      SELECT  CONCAT(CONCAT('h.', a.alias), '.polytechnique.org'), h.uid
  79        FROM  homonyms AS h
  80  INNER JOIN  aliases  AS a ON (a.uid = h.homonyme_id)
  81       WHERE  a.type = 'homonyme';
  82
  83 -- 5/ Feeds email_redirect_account
  84 INSERT INTO  email_redirect_account (uid, redirect, rewrite, type, action, broken_date, broken_level, last, flags, hash, allow_rewrite)
  85      SELECT  a.uid, e.email, e.rewrite, 'smtp', ef.email, e.panne, e.panne_level, e.last,
  86              IF(e.flags = '', 'inactive', IF(e.flags = 'disable', 'disabled', IF(e.flags = 'panne', 'broken', e.flags))), e.hash, e.allow_rewrite
  87        FROM  emails   AS e
  88   LEFT JOIN  emails   AS ef ON (e.uid = ef.uid)
  89   LEFT JOIN  accounts AS a  ON (e.uid = a.uid)
  90       WHERE  e.flags != 'filter' AND ef.flags = 'filter';
  91 INSERT INTO  email_redirect_account (uid, redirect, type, action, flags)
  92      SELECT  a.uid, CONCAT(a.hruid, '@g.polytechnique.org'), 'googleapps', ef.email, 'active'
  93        FROM  email_options AS eo
  94   LEFT JOIN  accounts      AS a  ON (a.uid = eo.uid)
  95   LEFT JOIN  emails        AS ef ON (eo.uid = ef.uid)
  96       WHERE  FIND_IN_SET('googleapps', eo.storage) AND ef.flags = 'filter';
  97 INSERT INTO  email_redirect_account (uid, redirect, type, action, flags)
  98      SELECT  a.uid, CONCAT(a.hruid, '@imap.polytechnique.org'), 'imap', 'let_spams', 'active'
  99        FROM  email_options AS eo
 100   LEFT JOIN  accounts      AS a ON (a.uid = eo.uid)
 101       WHERE  FIND_IN_SET('imap', eo.storage);
 102
 103 -- Imap and bounce
 104 UPDATE  email_redirect_account AS e,
 105         (SELECT  IF(SUM(IF(type != 'imap', 1, 0)) = 0, 'imap_only', 'normal') AS status, uid
 106            FROM  email_redirect_account
 107           WHERE  flags = 'active'
 108        GROUP BY  uid) AS sub
 109    SET  e.action = 'imap_and_bounce'
 110  WHERE  sub.status = 'imap_only' AND sub.uid = e.uid AND type = 'imap';
 111
 112 -- 6/ Feeds email_redirect_other
 113 INSERT INTO  email_redirect_other (hrmid, type, action)
 114      SELECT  hrmid, 'homonym', 'homonym'
 115        FROM  email_source_other
 116       WHERE  type = 'homonym'
 117    GROUP BY  (hrmid);
 118
 119 INSERT INTO  email_redirect_other (hrmid, redirect, type, action)
 120      VALUES  ('ax.nicolas.zarpas.polytechnique.org', 'nicolas.zarpas-ax@wanadoo.fr', 'smtp', 'tag_spams'),
 121              ('ax.carrieres.polytechnique.org', 'manuela.brasseur-bdc@wanadoo.fr', 'smtp', 'tag_spams'),
 122              ('ax.info1.polytechnique.org', 'sylvie.clairefond-ax@wanadoo.fr', 'smtp', 'tag_spams'),
 123              ('ax.info2.polytechnique.org', 'catherine.perot-ax@wanadoo.fr', 'smtp', 'tag_spams'),
 124              ('ax.bal.polytechnique.org', 'baldelx-ax@wanadoo.fr', 'smtp', 'tag_spams'),
 125              ('ax.annuaire.polytechnique.org', 'annuaire-ax@wanadoo.fr', 'smtp', 'tag_spams'),
 126              ('ax.jaune-rouge.polytechnique.org', 'jaune_rouge@wanadoo.fr', 'smtp', 'tag_spams'),
 127              ('honey.jean-pierre.bilah.1980.polytechnique.org', 'jean-pierre.bilah.1980.mbox@murphy.m4x.org', 'smtp', 'let_spams'),
 128              ('honey.jean-pierre.bilah.1980.polytechnique.org', 'raphael.barrois.2006@polytechnique.org', 'smtp', 'let_spams');
 129
 130 -- 7/ Feeds email_virtual
 131 INSERT INTO  email_virtual (email, domain, redirect, type)
 132      SELECT  SUBSTRING_INDEX(v.alias, '@', 1), d.id, vr.redirect, IF(v.type = 'evt', 'event', v.type)
 133        FROM  virtual               AS v
 134  INNER JOIN  email_virtual_domains AS d  ON (SUBSTRING_INDEX(v.alias, '@', -1) = d.name AND d.id = d.aliasing)
 135   LEFT JOIN  virtual_redirect      AS vr ON (vr.vid = v.vid)
 136       WHERE  v.alias NOT LIKE '%@melix.net' AND vr.vid IS NOT NULL AND v.alias != '@melix.org' AND v.type != 'dom';
 137
 138 INSERT INTO  email_virtual (email, type, domain, redirect)
 139      SELECT  alias, 'list', @p_domain_id,
 140              CONCAT('polytechnique.org_', REPLACE(REPLACE(REPLACE(CONCAT(alias, '+post@listes.polytechnique.org'),
 141                                                                   '-admin+post', '+admin'),
 142                                                           '-owner+post', '+owner'),
 143                                                   '-bounces+post', '+bounces'))
 144        FROM  aliases
 145       WHERE  type = 'liste';
 146
 147 INSERT INTO  email_virtual (email, redirect, domain, type)
 148      SELECT  SUBSTRING_INDEX(v.alias, '@', 1), vr.redirect, @m_domain_id, 'user'
 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' AND v.alias LIKE '%@melix.net' AND vr.vid IS NOT NULL AND a.uid IS NULL;
 153
 154 -- From aliases file
 155 INSERT INTO  email_virtual (domain, email, redirect, type)
 156      VALUES  (@p_domain_id, 'otrs.platal', 'otrs.platal@svoboda.polytechnique.org', 'admin'),
 157              (@p_domain_id, 'validation', 'hotliners@staff.polytechnique.org', 'admin'),
 158              (@p_domain_id, 'listes+admin', 'br@staff.polytechnique.org', 'admin'),
 159              (@p_domain_id, 'listes', 'otrs.platal+listes@polytechnique.org', 'admin'),
 160              (@p_domain_id, 'gld', 'listes@polytechnique.org', 'admin'),
 161              (@p_domain_id, 'support', 'otrs.platal+support@polytechnique.org', 'admin'),
 162              (@p_domain_id, 'contact', 'otrs.platal+contact@polytechnique.org', 'admin'),
 163              (@p_domain_id, 'register', 'otrs.platal+register@polytechnique.org', 'admin'),
 164              (@p_domain_id, 'info', 'otrs.platal+info@polytechnique.org', 'admin'),
 165              (@p_domain_id, 'bug', 'otrs.platal+bug@polytechnique.org', 'admin'),
 166              (@p_domain_id, 'resetpass', 'otrs.platal+resetpass@polytechnique.org', 'admin'),
 167              (@p_domain_id, 'association', 'otrs.platal+association@polytechnique.org', 'admin'),
 168              (@p_domain_id, 'x-org', 'association@polytechnique.org', 'admin'),
 169              (@p_domain_id, 'manageurs', 'otrs@support.manageurs.com', 'partner'),
 170              (@p_domain_id, 'fondation', 'fondation@fondationx.org', 'partner'),
 171              (@p_domain_id, 'ax', 'ax@wanadoo.fr', 'partner'),
 172              (@p_domain_id, 'annuaire-ax', 'annuaire-ax@wanadoo.fr', 'partner'),
 173              (@p_domain_id, 'ax-bdc', 'ax-bdc@wanadoo.fr', 'partner'),
 174              (@p_domain_id, 'jaune', 'null@hruid.polytechnique.org', 'partner'),
 175              (@p_domain_id, 'jaune+rouge', 'jaune_rouge@wanadoo.fr', 'partner'),
 176              (@p_domain_id, 'xcourseaularge', 'info@xcourseaularge.polytechnique.org', 'partner'),
 177              (@p_domain_id, 'xim', 'membres@x-internet.polytechnique.org', 'partner'),
 178              (@p_domain_id, 'x-consult', 'info@x-consult.polytechnique.org', 'partner'),
 179              (@p_domain_id, 'xmcb', 'xmcb@x-consult.polytechnique.org', 'partner'),
 180              (@p_domain_id, 'x-maroc', 'allam@mtpnet.gov.ma', 'partner'),
 181              (@p_domain_id, 'x-musique', 'xmusique@free.fr', 'partner'),
 182              (@p_domain_id, 'x-resistance', 'info@xresistance.org', 'partner'),
 183              (@p_domain_id, 'x-israel', 'info@x-israel.polytechnique.org', 'partner'),
 184              (@p_domain_id, 'gpx', 'g.p.x@infonie.fr', 'partner'),
 185              (@p_domain_id, 'g.p.x', 'gpx@polytechnique.org', 'partner'),
 186              (@p_domain_id, 'pointgamma', 'gamma@frankiz.polytechnique.fr', 'partner'),
 187              (@p_domain_id, 'xmpentrepreneur', 'xmp.entrepreneur@gmail.com', 'partner'),
 188              (@p_domain_id, 'xmp-entrepreneur', 'xmp.entrepreneur@gmail.com', 'partner'),
 189              (@p_domain_id, 'xmpangels', 'xmpangels@xmp-ba.m4x.org', 'partner'),
 190              (@p_domain_id, 'xmp-angels', 'xmpangels@xmp-ba.m4x.org', 'partner'),
 191              (@p_domain_id, 'relex', 'relex@staff.polytechnique.org', 'admin'),
 192              (@p_domain_id, 'tresorier', 'tresorier@staff.polytechnique.org', 'admin'),
 193              (@p_domain_id, 'aaege-sso', 'aaege-sso@staff.polytechnique.org', 'admin'),
 194              (@p_domain_id, 'innovation', 'innovation@staff.polytechnique.org', 'admin'),
 195              (@p_domain_id, 'groupes', 'groupes@staff.polytechnique.org', 'admin'),
 196              (@p_domain_id, 'br', 'br@staff.polytechnique.org', 'admin'),
 197              (@p_domain_id, 'ca', 'ca@staff.polytechnique.org', 'admin'),
 198              (@p_domain_id, 'personnel', 'br@staff.polytechnique.org', 'admin'),
 199              (@p_domain_id, 'cil', 'cil@staff.polytechnique.org', 'admin'),
 200              (@p_domain_id, 'opensource', 'contact@polytechnique.org', 'admin'),
 201              (@p_domain_id, 'forums', 'forums@staff.m4x.org', 'admin'),
 202              (@p_domain_id, 'telepaiement', 'telepaiement@staff.m4x.org', 'admin'),
 203              (@p_domain_id, 'hotliners', 'hotliners@staff.m4x.org', 'admin'),
 204              (@p_domain_id, 'kes', 'kes@frankiz.polytechnique.fr', 'partner'),
 205              (@p_domain_id, 'kes1999', 'cariokes@polytechnique.org', 'partner'),
 206              (@p_domain_id, 'kes2000', 'kestinpowers@polytechnique.org', 'partner');
 207
 208 -- Drop renamed list
 209 DELETE FROM email_virtual WHERE email LIKE 'tech-email%';
 210
 211 -- vim:set syntax=mysql: