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