upgrade/1.1.0/01_new_mail.sql

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