upgrade/1.1.0/02_new_mail_insertion.sql

   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
   3        FROM  aliases
   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
   7        FROM  aliases
   8       WHERE  type = 'a_vie' OR type = 'alias';
   9 INSERT INTO  email_source_account (uid, email, type)
  10      SELECT  a.uid, v.alias, 'alias'
  11        FROM  virtual          AS v
  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'
  17        FROM  virtual          AS v
  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;
  21
  22 INSERT INTO  email_source_other (hrmid, email, type)
  23      SELECT  CONCAT(CONCAT('h.', alias), '.polytechnique.org'), CONCAT(alias, '@polytechnique.org'), 'homonym'
  24        FROM  aliases
  25       WHERE  type = 'homonyme'
  26    GROUP BY  (alias);
  27 INSERT INTO  email_source_other (hrmid, email, type)
  28      SELECT  CONCAT(CONCAT('h.', alias), '.polytechnique.org'), CONCAT(alias, '@m4x.org'), 'homonym'
  29        FROM  aliases
  30       WHERE  type = 'homonyme'
  31    GROUP BY  (alias);
  32
  33 INSERT INTO  homonyms_list (hrmid, uid)
  34      SELECT  CONCAT(CONCAT('h.', a.alias), '.polytechnique.org'), h.uid
  35        FROM  homonyms AS h
  36  INNER JOIN  aliases  AS a ON (a.uid = h.homonyme_id)
  37       WHERE  a.type = 'homonyme';
  38
  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
  42        FROM  emails   AS e
  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);
  57
  58 INSERT INTO  email_redirect_other (hrmid, type, action)
  59      SELECT  hrmid, 'homonym', 'homonym'
  60        FROM  email_source_other
  61       WHERE  type = 'homonym'
  62    GROUP BY  (hrmid);
  63
  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))
  66        FROM  virtual          AS v
  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'))
  75        FROM  aliases
  76       WHERE  type = 'liste';
  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'))
  83        FROM  aliases
  84       WHERE  type = 'liste';
  85 INSERT INTO  email_virtual (email, redirect, type)
  86      SELECT  v.alias, vr.redirect, 'user'
  87        FROM  virtual          AS v
  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'
  93        FROM  virtual          AS v
  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;
  97
  98 -- Note: There are some adresses on virtual that have no match on the virtual_redirect.
  99 --       The adresses in this situation are dropped.
 100
 101 INSERT INTO  email_virtual_domains (domain)
 102      VALUES  ('polytechnique.org'), ('m4x.org');
 103 INSERT INTO  email_virtual_domains (domain)
 104      SELECT  domain
 105        FROM  virtual_domains;
 106
 107 -- From aliases file
 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');
 211
 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');
 233
 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');
 244
 245 -- Drop renamed list
 246 DELETE FROM email_virtual WHERE email LIKE 'tech-email%@polytechnique.org';
 247 DELETE FROM email_virtual WHERE email LIKE 'tech-email%@m4x.org';
 248
 249 -- Imap and bounce
 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'
 254        GROUP BY  uid) AS sub
 255    SET  e.action = 'imap_and_bounce'
 256  WHERE  sub.status = 'imap_only' AND sub.uid = e.uid AND type = 'imap';
 257
 258 -- vim:set syntax=mysql: