upgrade/1.1.0/01_new_mail_schema.sql

   1 DROP TABLE IF EXISTS email_source_account;
   2 DROP TABLE IF EXISTS email_source_other;
   3 DROP TABLE IF EXISTS homonyms_list;
   4 DROP TABLE IF EXISTS email_redirect_account;
   5 DROP TABLE IF EXISTS email_redirect_other;
   6 DROP TABLE IF EXISTS email_virtual;
   7 DROP TABLE IF EXISTS email_virtual_domains;
   8
   9 CREATE TABLE email_source_account (
  10         email  VARCHAR(255) NOT NULL PRIMARY KEY,
  11         uid    INT(11) UNSIGNED NOT NULL,
  12         type   ENUM('forlife','alias') NOT NULL DEFAULT 'forlife',
  13         flags  SET('bestalias','usage','marital') NOT NULL DEFAULT '',
  14         expire DATE DEFAULT NULL,
  15         KEY (uid),
  16         KEY (type),
  17     FOREIGN KEY (uid) REFERENCES accounts (uid) ON UPDATE CASCADE ON DELETE CASCADE
  18 ) ENGINE=InnoDB,  CHARSET=utf8 ;
  19
  20 CREATE TABLE email_source_other (
  21         email VARCHAR(255) NOT NULL PRIMARY KEY,
  22         hrmid VARCHAR(255) NOT NULL,
  23         type  ENUM('homonym','ax','honeypot'),
  24         expire DATE NOT NULL DEFAULT '0000-00-00'
  25 ) ENGINE=InnoDB,  CHARSET=utf8 ;
  26
  27 CREATE TABLE homonyms_list (
  28         hrmid VARCHAR(255) NOT NULL,
  29         uid   INT(11) UNSIGNED NOT NULL,
  30         KEY(hrmid)
  31 ) ENGINE=InnoDB,  CHARSET=utf8 ;
  32
  33 CREATE TABLE email_redirect_account (
  34         uid           INT(11) UNSIGNED NOT NULL,
  35         redirect      VARCHAR(255) NOT NULL DEFAULT '',
  36         rewrite       VARCHAR(255) NOT NULL DEFAULT '',
  37         type          ENUM('smtp','googleapps','imap','homonym') NOT NULL DEFAULT 'smtp',
  38         action        ENUM('default',
  39                            'drop_spams',
  40                            'let_spams',
  41                            'tag_and_drop_spams',
  42                            'tag_spams',
  43                            'imap_and_bounce',
  44                            'homonym')
  45                 NOT NULL DEFAULT 'default',
  46         broken        DATE NOT NULL DEFAULT '0000-00-00',
  47         broken_level  TINYINT(1) NOT NULL DEFAULT 0,
  48         last          DATE NOT NULL DEFAULT '0000-00-00',
  49         flags         ENUM('active','broken','disabled') NOT NULL,
  50         hash          VARCHAR(32) DEFAULT NULL,
  51         allow_rewrite TINYINT(1) DEFAULT 0,
  52         KEY (uid),
  53         KEY (redirect)
  54 ) ENGINE=InnoDB,  CHARSET=utf8 ;
  55
  56 CREATE TABLE email_redirect_other (
  57         hrmid         VARCHAR(255) NOT NULL DEFAULT '',
  58         redirect      VARCHAR(255) NOT NULL DEFAULT '',
  59         type          ENUM('smtp','googleapps','imap','homonym') NOT NULL DEFAULT 'smtp',
  60         action        ENUM('default',
  61                            'drop_spams',
  62                            'let_spams',
  63                            'tag_and_drop_spams',
  64                            'tag_spams',
  65                            'imap_and_bounce',
  66                            'homonym')
  67                 NOT NULL DEFAULT 'default',
  68         KEY (hrmid),
  69         KEY (redirect)
  70 ) ENGINE=InnoDB,  CHARSET=utf8 ;
  71
  72 CREATE TABLE email_virtual (
  73         email    VARCHAR(255) NOT NULL,
  74         redirect VARCHAR(255) NOT NULL,
  75         type     ENUM('user','list','domain','event','admin','partner'),
  76         expire   DATE NOT NULL DEFAULT '0000-00-00',
  77         KEY (email)
  78 ) ENGINE=InnoDB,  CHARSET=utf8 ;
  79
  80 CREATE TABLE email_virtual_domains (
  81         domain VARCHAR(255) NOT NULL PRIMARY KEY
  82 ) ENGINE=InnoDB,  CHARSET=utf8;
  83
  84 -- vim:set syntax=mysql: