upgrade/1.1.0/01_new_mail.sql

   1 CREATE TABLE email_account_emails (
   2         email  VARCHAR(255) NOT NULL PRIMARY KEY,
   3         uid    INT(11) UNSIGNED NOT NULL,
   4         type   ENUM('a_vie','alias') NOT NULL DEFAULT 'a_vie',
   5         flags  SET('bestalias','usage','epouse') NOT NULL DEFAULT '',
   6         expire DATE DEFAULT NULL,
   7         KEY (uid),
   8         KEY (type)
   9 ) ENGINE=InnoDB,  CHARSET=utf8 ;
  10
  11 INSERT INTO email_account_emails (uid,email,type,flags,expire)
  12         SELECT a.uid,CONCAT(a.alias,'@polytechnique.org'),a.type,a.flags,a.expire
  13         FROM aliases AS a
  14         WHERE a.type = 'a_vie'
  15          OR a.type = 'alias';
  16 INSERT INTO email_account_emails (uid,email,type,flags,expire)
  17         SELECT a.uid,CONCAT(a.alias,'@m4x.org'),a.type,a.flags,a.expire
  18         FROM aliases AS a
  19         WHERE a.type = 'a_vie'
  20          OR a.type = 'alias';
  21 INSERT INTO email_account_emails (uid,email,type,flags,expire)
  22         SELECT a.uid,CONCAT(a.alias,'@m4x.net'),a.type,a.flags,a.expire
  23         FROM aliases AS a
  24         WHERE a.type = 'a_vie'
  25          OR a.type = 'alias';
  26 INSERT INTO email_account_emails (uid,email,type)
  27         SELECT a.uid,v.alias,'alias'
  28         FROM virtual AS v
  29         LEFT JOIN virtual_redirect AS vr ON (v.vid=vr.vid)
  30         LEFT JOIN accounts AS a ON ( a.hruid=LEFT(vr.redirect,LOCATE('@',vr.redirect)-1) )
  31         WHERE v.type='user'
  32          AND v.alias LIKE "%@melix.net"
  33          AND a.uid IS NOT NULL;
  34 INSERT INTO email_account_emails (uid,email,type)
  35         SELECT a.uid,REPLACE(v.alias,'@melix.net','@melix.org'),'alias'
  36         FROM virtual AS v
  37         LEFT JOIN virtual_redirect AS vr ON (v.vid=vr.vid)
  38         LEFT JOIN accounts AS a ON ( a.hruid=LEFT(vr.redirect,LOCATE('@',vr.redirect)-1) )
  39         WHERE v.type='user'
  40          AND v.alias LIKE "%@melix.net"
  41          AND a.uid IS NOT NULL;
  42
  43 CREATE TABLE email_nonaccount_emails (
  44         email VARCHAR(255) NOT NULL PRIMARY KEY,
  45         hruid VARCHAR(255) NOT NULL,
  46         type  ENUM('homonym','ax','honeypot'),
  47         expire DATE NOT NULL DEFAULT '0000-00-00'
  48 ) ENGINE=InnoDB,  CHARSET=utf8 ;
  49
  50 INSERT INTO email_nonaccount_emails (hruid,email,type)
  51         SELECT CONCAT(CONCAT('h.',a.alias),'.polytechnique.org'),
  52                CONCAT(a.alias,'@polytechnique.org'),'homonym'
  53         FROM aliases AS a
  54         WHERE a.type = 'homonyme'
  55         GROUP BY (a.alias);
  56 INSERT INTO email_nonaccount_emails (hruid,email,type)
  57         SELECT CONCAT(CONCAT('h.',a.alias),'.polytechnique.org'),
  58                CONCAT(a.alias,'@m4x.org'),'homonym'
  59         FROM aliases AS a
  60         WHERE a.type = 'homonyme'
  61         GROUP BY (a.alias);
  62 INSERT INTO email_nonaccount_emails (hruid,email,type)
  63         SELECT CONCAT(CONCAT('h.',a.alias),'.polytechnique.org'),
  64                CONCAT(a.alias,'@m4x.net'),'homonym'
  65         FROM aliases AS a
  66         WHERE a.type = 'homonyme'
  67         GROUP BY (a.alias);
  68
  69 CREATE TABLE homonyms_list (
  70         homonym_hruid VARCHAR(255) NOT NULL,
  71         uid           INT(11) UNSIGNED NOT NULL,
  72         KEY(homonym_hruid)
  73 ) ENGINE=InnoDB,  CHARSET=utf8 ;
  74
  75 INSERT INTO homonyms_list (homonym_hruid,uid)
  76         SELECT CONCAT(CONCAT('h.',a.alias),'.polytechnique.org'), h.uid
  77         FROM homonyms AS h
  78         INNER JOIN aliases AS a ON (a.uid=h.homonyme_id)
  79         WHERE a.type = 'homonyme';
  80
  81 CREATE TABLE email_redirect (
  82         hruid         VARCHAR(255) NOT NULL DEFAULT '',
  83         redirect      VARCHAR(255) NOT NULL DEFAULT '',
  84         rewrite       VARCHAR(255) NOT NULL DEFAULT '',
  85         type          ENUM('smtp','googleapps','imap','homonyms') NOT NULL DEFAULT 'smtp',
  86         action        ENUM('default',
  87                            'drop_spams',
  88                            'let_spams',
  89                            'tag_and_drop_spams',
  90                            'tag_spams',
  91                            'homonym')
  92                 NOT NULL DEFAULT 'default',
  93         panne         DATE NOT NULL DEFAULT '0000-00-00',
  94         panne_level   TINYINT(1) NOT NULL DEFAULT 0,
  95         last          DATE NOT NULL DEFAULT '0000-00-00',
  96         flags         ENUM('active','panne','disable') NOT NULL,
  97         hash          VARCHAR(32) DEFAULT NULL,
  98         allow_rewrite TINYINT(1) DEFAULT 0,
  99         KEY (hruid),
 100         KEY (redirect)
 101 ) ENGINE=InnoDB,  CHARSET=utf8 ;
 102
 103 INSERT INTO email_redirect (hruid,redirect,rewrite,type,action,
 104                             panne,panne_level,last,flags,hash,allow_rewrite)
 105         SELECT a.hruid,e.email,e.rewrite,'smtp',ef.email,
 106                e.panne,e.panne_level,e.last,e.flags,e.hash,e.allow_rewrite
 107         FROM emails AS e
 108         LEFT JOIN emails AS ef ON (e.uid=ef.uid)
 109         LEFT JOIN accounts AS a ON (e.uid=a.uid)
 110         WHERE e.flags != 'filter'
 111          AND  ef.flags = 'filter';
 112 INSERT INTO email_redirect (hruid,type,action,flags)
 113         SELECT ege.hruid,'homonym','homonym','active'
 114         FROM email_nonaccount_emails AS ege
 115         WHERE ege.type = 'homonym'
 116         GROUP BY (ege.hruid);
 117 INSERT INTO email_redirect (hruid,type,action,flags)
 118         SELECT ege.hruid,'smtp','default','active'
 119         FROM email_nonaccount_emails AS ege
 120         WHERE ege.type != 'homonym'
 121         GROUP BY (ege.hruid);
 122 INSERT INTO email_redirect (hruid,redirect,type,action,flags)
 123         SELECT a.hruid,CONCAT(a.hruid,"@g.polytechnique.org"),'googleapps',ef.email,'active'
 124         FROM email_options AS eo
 125         LEFT JOIN accounts AS a ON (a.uid=eo.uid)
 126         LEFT JOIN emails AS ef ON (eo.uid=ef.uid)
 127         WHERE FIND_IN_SET('googleapps',eo.storage)
 128          AND ef.flags="filter";
 129 INSERT INTO email_redirect (hruid,redirect,type,action,flags)
 130         SELECT a.hruid,CONCAT(a.hruid,"@imap.polytechnique.org"),'imap','let_spams','active'
 131         FROM email_options AS eo
 132         LEFT JOIN accounts AS a ON (a.uid=eo.uid)
 133         WHERE FIND_IN_SET('imap',eo.storage);
 134
 135 CREATE TABLE email_virtual (
 136         alias    VARCHAR(255) NOT NULL,
 137         redirect VARCHAR(255) NOT NULL,
 138         type     ENUM('user','list','dom','evt','admin','partner'),
 139         expire   DATE NOT NULL DEFAULT '0000-00-00',
 140         KEY (alias)
 141 ) ENGINE=InnoDB,  CHARSET=utf8 ;
 142
 143 INSERT INTO email_virtual (alias,redirect,type)
 144         SELECT v.alias,vr.redirect,v.type
 145         FROM virtual AS v
 146         LEFT JOIN virtual_redirect AS vr ON (vr.vid=v.vid)
 147         WHERE v.alias NOT LIKE "%@melix.net"
 148          AND vr.vid IS NOT NULL
 149          AND v.alias != '@melix.org';
 150 INSERT INTO email_virtual (alias,redirect,type)
 151         SELECT CONCAT(a.alias,"@polytechnique.org"),
 152                CONCAT(
 153                  'polytechnique.org_',
 154                  REPLACE(
 155                    REPLACE(
 156                      REPLACE(
 157                        CONCAT(a.alias, '+post@listes.polytechnique.org'),
 158                        '-admin+post', '+admin'
 159                      ),
 160                      '-owner+post', '+owner'
 161                    ),
 162                    '-bounces+post', '+bounces'
 163                  )
 164                ),
 165                'list'
 166         FROM aliases AS a
 167         WHERE type='liste';
 168 INSERT INTO email_virtual (alias,redirect,type)
 169         SELECT CONCAT(a.alias,"@m4x.org"),
 170                CONCAT(
 171                  'polytechnique.org_',
 172                  REPLACE(
 173                    REPLACE(
 174                      REPLACE(
 175                        CONCAT(a.alias, '+post@listes.polytechnique.org'),
 176                        '-admin+post', '+admin'
 177                      ),
 178                      '-owner+post', '+owner'
 179                    ),
 180                    '-bounces+post', '+bounces'
 181                  )
 182                ),
 183                'list'
 184         FROM aliases AS a
 185         WHERE type='liste';
 186 INSERT INTO email_virtual (alias,redirect,type)
 187         SELECT CONCAT(a.alias,"@m4x.net"),
 188                CONCAT(
 189                  'polytechnique.org_',
 190                  REPLACE(
 191                    REPLACE(
 192                      REPLACE(
 193                        CONCAT(a.alias, '+post@listes.polytechnique.org'),
 194                        '-admin+post', '+admin'
 195                      ),
 196                      '-owner+post', '+owner'
 197                    ),
 198                    '-bounces+post', '+bounces'
 199                  )
 200                ),
 201                'list'
 202         FROM aliases AS a
 203         WHERE type='liste';
 204 INSERT INTO email_virtual (alias,redirect,type)
 205         SELECT vr.redirect,v.alias,'user'
 206         FROM virtual AS v
 207         LEFT JOIN virtual_redirect AS vr ON (v.vid=vr.vid)
 208         LEFT JOIN accounts AS a ON ( a.hruid=LEFT(vr.redirect,LOCATE('@',vr.redirect)-1) )
 209         WHERE v.type='user'
 210          AND v.alias LIKE "%@melix.net"
 211          AND vr.vid IS NOT NULL
 212          AND a.uid IS NULL;
 213 INSERT INTO email_virtual (alias,redirect,type)
 214         SELECT vr.redirect,REPLACE(v.alias,'@melix.net','@melix.org'),'user'
 215         FROM virtual AS v
 216         LEFT JOIN virtual_redirect AS vr ON (v.vid=vr.vid)
 217         LEFT JOIN accounts AS a ON ( a.hruid=LEFT(vr.redirect,LOCATE('@',vr.redirect)-1) )
 218         WHERE v.type='user'
 219          AND v.alias LIKE "%@melix.net"
 220          AND vr.vid IS NOT NULL
 221          AND a.uid IS NULL;
 222 /*
 223 Note: There are some adresses on virtual that have no match on the virtual_redirect. The adresses
 224       in this situation are dropped.
 225  */
 226
 227 CREATE TABLE email_virtual_domains (
 228         domain VARCHAR(255) NOT NULL PRIMARY KEY
 229 ) ENGINE=InnoDB,  CHARSET=utf8 ;
 230
 231 INSERT INTO email_virtual_domains (domain)
 232         VALUES ("polytechnique.org"),
 233                ("m4x.org"),
 234                ("m4x.net");
 235 INSERT INTO email_virtual_domains (domain)
 236         SELECT domain
 237         FROM virtual_domains;
 238
 239 /* From aliases file */
 240 INSERT INTO email_virtual (alias,redirect,type) VALUES
 241         ("otrs.platal@polytechnique.org","otrs@svoboda.polytechnique.org","admin"),
 242         ("otrs.platal@m4x.org","otrs.platal@polytechnique.org","admin"),
 243         ("otrs.platal@m4x.net","otrs.platal@polytechnique.org","admin"),
 244         ("validation@polytechnique.org","hotliners@staff.polytechnique.org","admin"),
 245         ("validation@m4x.org","validation@polytechnique.org","admin"),
 246         ("validation@m4x.net","validation@polytechnique.org","admin"),
 247         ("listes+admin@polytechnique.org","br@staff.polytechnique.org","admin"),
 248         ("listes+admin@m4x.org","listes+admin@polytechnique.org","admin"),
 249         ("listes+admin@m4x.net","listes+admin@polytechnique.org","admin"),
 250         ("listes@polytechnique.org","otrs.platal+listes@polytechnique.org","admin"),
 251         ("listes@m4x.org","listes@polytechnique.org","admin"),
 252         ("listes@m4x.net","listes@polytechnique.org","admin"),
 253         ("gld@polytechnique.org","listes@polytechnique.org","admin"),
 254         ("gld@m4x.org","gld@polytechnique.org","admin"),
 255         ("gld@m4x.net","gld@polytechnique.org","admin"),
 256         ("support@polytechnique.org","otrs.platal+support@polytechnique.org","admin"),
 257         ("support@m4x.org","support@polytechnique.org","admin"),
 258         ("support@m4x.net","support@polytechnique.org","admin"),
 259         ("contact@polytechnique.org","otrs.platal+contact@polytechnique.org","admin"),
 260         ("contact@m4x.org","contact@polytechnique.org","admin"),
 261         ("contact@m4x.net","contact@polytechnique.org","admin"),
 262         ("register@polytechnique.org","otrs.platal+register@polytechnique.org","admin"),
 263         ("register@m4x.org","register@polytechnique.org","admin"),
 264         ("register@m4x.net","register@polytechnique.org","admin"),
 265         ("info@polytechnique.org","otrs.platal+info@polytechnique.org","admin"),
 266         ("info@m4x.org","info@polytechnique.org","admin"),
 267         ("info@m4x.net","info@polytechnique.org","admin"),
 268         ("bug@polytechnique.org","otrs.platal+bug@polytechnique.org","admin"),
 269         ("bug@m4x.org","bug@polytechnique.org","admin"),
 270         ("bug@m4x.net","bug@polytechnique.org","admin"),
 271         ("resetpass@polytechnique.org","otrs.platal+resetpass@polytechnique.org","admin"),
 272         ("resetpass@m4x.org","resetpass@polytechnique.org","admin"),
 273         ("resetpass@m4x.net","resetpass@polytechnique.org","admin"),
 274         ("association@polytechnique.org","otrs.platal+association@polytechnique.org","admin"),
 275         ("association@m4x.org","association@polytechnique.org","admin"),
 276         ("association@m4x.net","association@polytechnique.org","admin"),
 277         ("x-org@polytechnique.org","association@polytechnique.org","admin"),
 278         ("x-org@m4x.org","x-org@polytechnique.org","admin"),
 279         ("x-org@m4x.net","x-org@polytechnique.org","admin"),
 280         ("manageurs@polytechnique.org","otrs@support.manageurs.com","partner"),
 281         ("manageurs@m4x.org","manageurs@polytechnique.org","partner"),
 282         ("manageurs@m4x.net","manageurs@polytechnique.org","partner"),
 283         ("fondation@polytechnique.org","fondation@fondationx.org","partner"),
 284         ("fondation@m4x.org","fondation@polytechnique.org","partner"),
 285         ("fondation@m4x.net","fondation@polytechnique.org","partner"),
 286         ("ax@polytechnique.org","ax@wanadoo.fr","partner"),
 287         ("ax@m4x.org","ax@polytechnique.org","partner"),
 288         ("ax@m4x.net","ax@polytechnique.org","partner"),
 289         ("annuaire-ax@polytechnique.org","annuaire-ax@wanadoo.fr","partner"),
 290         ("annuaire-ax@m4x.org","annuaire-ax@polytechnique.org","partner"),
 291         ("annuaire-ax@m4x.net","annuaire-ax@polytechnique.org","partner"),
 292         ("ax-bdc@polytechnique.org","ax-bdc@wanadoo.fr","partner"),
 293         ("ax-bdc@m4x.org","ax-bdc@polytechnique.org","partner"),
 294         ("ax-bdc@m4x.net","ax-bdc@polytechnique.org","partner"),
 295         ("jaune@polytechnique.org","null@hruid.polytechnique.org","partner"),
 296         ("jaune@m4x.org","jaune@polytechnique.org","partner"),
 297         ("jaune@m4x.net","jaune@polytechnique.org","partner"),
 298         ("jaune+rouge@polytechnique.org","jaune_rouge@wanadoo.fr","partner"),
 299         ("jaune+rouge@m4x.org","jaune+rouge@polytechnique.org","partner"),
 300         ("jaune+rouge@m4x.net","jaune+rouge@polytechnique.org","partner"),
 301         ("xcourseaularge@polytechnique.org","info@xcourseaularge.polytechnique.org","partner"),
 302         ("xcourseaularge@m4x.org","xcourseaularge@polytechnique.org","partner"),
 303         ("xcourseaularge@m4x.net","xcourseaularge@polytechnique.org","partner"),
 304         ("xim@polytechnique.org","membres@x-internet.polytechnique.org","partner"),
 305         ("xim@m4x.org","xim@polytechnique.org","partner"),
 306         ("xim@m4x.net","xim@polytechnique.org","partner"),
 307         ("x-consult@polytechnique.org","info@x-consult.polytechnique.org","partner"),
 308         ("x-consult@m4x.org","x-consult@polytechnique.org","partner"),
 309         ("x-consult@m4x.net","x-consult@polytechnique.org","partner"),
 310         ("xmcb@polytechnique.org","xmcb@x-consult.polytechnique.org","partner"),
 311         ("xmcb@m4x.org","xmcb@polytechnique.org","partner"),
 312         ("xmcb@m4x.net","xmcb@polytechnique.org","partner"),
 313         ("x-maroc@polytechnique.org","allam@mtpnet.gov.ma","partner"),
 314         ("x-maroc@m4x.org","x-maroc@polytechnique.org","partner"),
 315         ("x-maroc@m4x.net","x-maroc@polytechnique.org","partner"),
 316         ("x-musique@polytechnique.org","xmusique@free.fr","partner"),
 317         ("x-musique@m4x.org","x-musique@polytechnique.org","partner"),
 318         ("x-musique@m4x.net","x-musique@polytechnique.org","partner"),
 319         ("x-resistance@polytechnique.org","info@xresistance.org","partner"),
 320         ("x-resistance@m4x.org","x-resistance@polytechnique.org","partner"),
 321         ("x-resistance@m4x.net","x-resistance@polytechnique.org","partner"),
 322         ("x-israel@polytechnique.org","info@x-israel.polytechnique.org","partner"),
 323         ("x-israel@m4x.org","x-israel@polytechnique.org","partner"),
 324         ("x-israel@m4x.net","x-israel@polytechnique.org","partner"),
 325         ("gpx@polytechnique.org","g.p.x@infonie.fr","partner"),
 326         ("gpx@m4x.org","gpx@polytechnique.org","partner"),
 327         ("gpx@m4x.net","gpx@polytechnique.org","partner"),
 328         ("g.p.x@polytechnique.org","gpx@polytechnique.org","partner"),
 329         ("g.p.x@m4x.org","g.p.x@polytechnique.org","partner"),
 330         ("g.p.x@m4x.net","g.p.x@polytechnique.org","partner"),
 331         ("pointgamma@polytechnique.org","gamma@frankiz.polytechnique.fr","partner"),
 332         ("pointgamma@m4x.org","pointgamma@polytechnique.org","partner"),
 333         ("pointgamma@m4x.net","pointgamma@polytechnique.org","partner"),
 334         ("xmpentrepreneur@polytechnique.org","xmp.entrepreneur@gmail.com","partner"),
 335         ("xmpentrepreneur@m4x.org","xmpentrepreneur@polytechnique.org","partner"),
 336         ("xmpentrepreneur@m4x.net","xmpentrepreneur@polytechnique.org","partner"),
 337         ("xmp-entrepreneur@polytechnique.org","xmp.entrepreneur@gmail.com","partner"),
 338         ("xmp-entrepreneur@m4x.org","xmp-entrepreneur@polytechnique.org","partner"),
 339         ("xmp-entrepreneur@m4x.net","xmp-entrepreneur@polytechnique.org","partner"),
 340         ("xmpangels@polytechnique.org","xmpangels@xmp-ba.m4x.org","partner"),
 341         ("xmpangels@m4x.org","xmpangels@polytechnique.org","partner"),
 342         ("xmpangels@m4x.net","xmpangels@polytechnique.org","partner"),
 343         ("xmp-angels@polytechnique.org","xmpangels@xmp-ba.m4x.org","partner"),
 344         ("xmp-angels@m4x.org","xmp-angels@polytechnique.org","partner"),
 345         ("xmp-angels@m4x.net","xmp-angels@polytechnique.org","partner"),
 346         ("relex@polytechnique.org","relex@staff.polytechnique.org","admin"),
 347         ("relex@m4x.org","relex@polytechnique.org","admin"),
 348         ("relex@m4x.net","relex@polytechnique.org","admin"),
 349         ("tresorier@polytechnique.org","tresorier@staff.polytechnique.org","admin"),
 350         ("tresorier@m4x.org","tresorier@polytechnique.org","admin"),
 351         ("tresorier@m4x.net","tresorier@polytechnique.org","admin"),
 352         ("aaege-sso@polytechnique.org","aaege-sso@staff.polytechnique.org","admin"),
 353         ("aaege-sso@m4x.org","aaege-sso@polytechnique.org","admin"),
 354         ("aaege-sso@m4x.net","aaege-sso@polytechnique.org","admin"),
 355         ("innovation@polytechnique.org","innovation@staff.polytechnique.org","admin"),
 356         ("innovation@m4x.org","innovation@polytechnique.org","admin"),
 357         ("innovation@m4x.net","innovation@polytechnique.org","admin"),
 358         ("groupes@polytechnique.org","groupes@staff.polytechnique.org","admin"),
 359         ("groupes@m4x.org","groupes@polytechnique.org","admin"),
 360         ("groupes@m4x.net","groupes@polytechnique.org","admin"),
 361         ("br@polytechnique.org","br@staff.polytechnique.org","admin"),
 362         ("br@m4x.org","br@polytechnique.org","admin"),
 363         ("br@m4x.net","br@polytechnique.org","admin"),
 364         ("ca@polytechnique.org","ca@staff.polytechnique.org","admin"),
 365         ("ca@m4x.org","ca@polytechnique.org","admin"),
 366         ("ca@m4x.net","ca@polytechnique.org","admin"),
 367         ("personnel@polytechnique.org","br@staff.polytechnique.org","admin"),
 368         ("personnel@m4x.org","personnel@polytechnique.org","admin"),
 369         ("personnel@m4x.net","personnel@polytechnique.org","admin"),
 370         ("cil@polytechnique.org","cil@staff.polytechnique.org","admin"),
 371         ("cil@m4x.org","cil@polytechnique.org","admin"),
 372         ("cil@m4x.net","cil@polytechnique.org","admin"),
 373         ("opensource@polytechnique.org","contact@polytechnique.org","admin"),
 374         ("opensource@m4x.org","opensource@polytechnique.org","admin"),
 375         ("opensource@m4x.net","opensource@polytechnique.org","admin"),
 376         ("forums@polytechnique.org","forums@staff.m4x.org","admin"),
 377         ("forums@m4x.org","forums@polytechnique.org","admin"),
 378         ("forums@m4x.net","forums@polytechnique.org","admin"),
 379         ("telepaiement@polytechnique.org","telepaiement@staff.m4x.org","admin"),
 380         ("telepaiement@m4x.org","telepaiement@polytechnique.org","admin"),
 381         ("telepaiement@m4x.net","telepaiement@polytechnique.org","admin"),
 382         ("hotliners@polytechnique.org","hotliners@staff.m4x.org","admin"),
 383         ("hotliners@m4x.org","hotliners@polytechnique.org","admin"),
 384         ("hotliners@m4x.net","hotliners@polytechnique.org","admin"),
 385         ("kes@polytechnique.org","kes@frankiz.polytechnique.fr","partner"),
 386         ("kes@m4x.org","kes@polytechnique.org","partner"),
 387         ("kes@m4x.net","kes@polytechnique.org","partner"),
 388         ("kes1999@polytechnique.org","cariokes@polytechnique.org","partner"),
 389         ("kes1999@m4x.org","kes1999@polytechnique.org","partner"),
 390         ("kes1999@m4x.net","kes1999@polytechnique.org","partner"),
 391         ("kes2000@polytechnique.org","kestinpowers@polytechnique.org","partner"),
 392         ("kes2000@m4x.org","kes2000@polytechnique.org","partner"),
 393         ("kes2000@m4x.net","kes2000@polytechnique.org","partner");
 394
 395 INSERT INTO email_nonaccount_emails (hruid,email,type) VALUES
 396         ("ax.test.polytechnique.org","AX-test@polytechnique.org","ax"),
 397         ("ax.test.polytechnique.org","AX-test@m4x.org","ax"),
 398         ("ax.test.polytechnique.org","AX-test@m4x.net","ax"),
 399         ("ax.nicolas.zarpas.polytechnique.org","AX-nicolas.zarpas@polytechnique.org","ax"),
 400         ("ax.nicolas.zarpas.polytechnique.org","AX-nicolas.zarpas@m4x.org","ax"),
 401         ("ax.nicolas.zarpas.polytechnique.org","AX-nicolas.zarpas@m4x.net","ax"),
 402         ("ax.carrieres.polytechnique.org","AX-carrieres@polytechnique.org","ax"),
 403         ("ax.carrieres.polytechnique.org","AX-carrieres@m4x.org","ax"),
 404         ("ax.carrieres.polytechnique.org","AX-carrieres@m4x.net","ax"),
 405         ("ax.info1.polytechnique.org","AX-info1@polytechnique.org","ax"),
 406         ("ax.info1.polytechnique.org","AX-info1@m4x.org","ax"),
 407         ("ax.info1.polytechnique.org","AX-info1@m4x.net","ax"),
 408         ("ax.info2.polytechnique.org","AX-info2@polytechnique.org","ax"),
 409         ("ax.info2.polytechnique.org","AX-info2@m4x.org","ax"),
 410         ("ax.info2.polytechnique.org","AX-info2@m4x.net","ax"),
 411         ("ax.bal.polytechnique.org","AX-bal@polytechnique.org","ax"),
 412         ("ax.bal.polytechnique.org","AX-bal@m4x.org","ax"),
 413         ("ax.bal.polytechnique.org","AX-bal@m4x.net","ax"),
 414         ("ax.annuaire.polytechnique.org","AX-annuaire@polytechnique.org","ax"),
 415         ("ax.annuaire.polytechnique.org","AX-annuaire@m4x.org","ax"),
 416         ("ax.annuaire.polytechnique.org","AX-annuaire@m4x.net","ax"),
 417         ("ax.jaune-rouge.polytechnique.org","AX-jaune-rouge@polytechnique.org","ax"),
 418         ("ax.jaune-rouge.polytechnique.org","AX-jaune-rouge@m4x.org","ax"),
 419         ("ax.jaune-rouge.polytechnique.org","AX-jaune-rouge@m4x.net","ax"),
 420         ("honey.jean-pierre.bilah.1980.polytechnique.org","jean-pierre.bilah.1980@polytechnique.org","honeypot"),
 421         ("honey.jean-pierre.bilah.1980.polytechnique.org","jean-pierre.bilah.1980@m4x.org","honeypot"),
 422         ("honey.jean-pierre.bilah.1980.polytechnique.org","jean-pierre.bilah.1980@m4x.net","honeypot"),
 423         ("honey.jean-pierre.bilah.1980.polytechnique.org","jean-pierre.blah.1980@polytechnique.org","honeypot"),
 424         ("honey.jean-pierre.bilah.1980.polytechnique.org","jean-pierre.blah.1980@m4x.org","honeypot"),
 425         ("honey.jean-pierre.bilah.1980.polytechnique.org","jean-pierre.blah.1980@m4x.net","honeypot");
 426
 427 INSERT INTO email_redirect (hruid,redirect,type,action,flags) VALUES
 428         ("ax.test.polytechnique.org","falco@[129.104.217.160]","smtp","tag_spams","active"),
 429         ("ax.nicolas.zarpas.polytechnique.org","nicolas.zarpas-ax@wanadoo.fr","smtp","tag_spams","active"),
 430         ("ax.carrieres.polytechnique.org","manuela.brasseur-bdc@wanadoo.fr","smtp","tag_spams","active"),
 431         ("ax.info1.polytechnique.org","sylvie.clairefond-ax@wanadoo.fr","smtp","tag_spams","active"),
 432         ("ax.info2.polytechnique.org","catherine.perot-ax@wanadoo.fr","smtp","tag_spams","active"),
 433         ("ax.bal.polytechnique.org","baldelx-ax@wanadoo.fr","smtp","tag_spams","active"),
 434         ("ax.annuaire.polytechnique.org","annuaire-ax@wanadoo.fr","smtp","tag_spams","active"),
 435         ("ax.jaune-rouge.polytechnique.org","jaune_rouge@wanadoo.fr","smtp","tag_spams","active"),
 436         ("honey.jean-pierre.bilah.1980.polytechnique.org","jean-pierre.bilah.1980.mbox@murphy.m4x.org","smtp","let_spams","active"),
 437         ("honey.jean-pierre.bilah.1980.polytechnique.org","raphael.barrois.2006@polytechnique.org","smtp","let_spams","active");
 438
 439 /* Drop renamed list */
 440 DELETE FROM email_virtual WHERE alias LIKE "tech-email%@polytechnique.org";
 441 DELETE FROM email_virtual WHERE alias LIKE "tech-email%@m4x.org";
 442 DELETE FROM email_virtual WHERE alias LIKE "tech-email%@m4x.net";