Introduces email type alias_aux for emails in auxiliary domains.

[platal.git] / upgrade / 1.1.0 / 01_new_mail_schema.sql

diff --git a/upgrade/1.1.0/01_new_mail_schema.sql b/upgrade/1.1.0/01_new_mail_schema.sql
index 15756f7..47bcb54 100644 (file)
--- a/upgrade/1.1.0/01_new_mail_schema.sql
+++ b/upgrade/1.1.0/01_new_mail_schema.sql
@@ -1,33 +1,52 @@
+DROP TABLE IF EXISTS email_redirect_account;
+DROP TABLE IF EXISTS email_redirect_other;
 DROP TABLE IF EXISTS email_source_account;
 DROP TABLE IF EXISTS email_source_other;
 DROP TABLE IF EXISTS homonyms_list;
-DROP TABLE IF EXISTS email_redirect_account;
-DROP TABLE IF EXISTS email_redirect_other;
 DROP TABLE IF EXISTS email_virtual;
 DROP TABLE IF EXISTS email_virtual_domains;
 
+CREATE TABLE email_virtual_domains (
+  id SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
+  name VARCHAR(255) NOT NULL DEFAULT '',
+  aliasing SMALLINT(3) UNSIGNED NOT NULL DEFAULT 1,
+  PRIMARY KEY (id),
+  KEY (name),
+  FOREIGN KEY (aliasing) REFERENCES email_virtual_domains (id) ON UPDATE CASCADE ON DELETE CASCADE
+) ENGINE=InnoDB, CHARSET=utf8;
+
 CREATE TABLE email_source_account (
-  email VARCHAR(255) NOT NULL PRIMARY KEY,
+  email VARCHAR(255) NOT NULL,
+  domain SMALLINT(3) UNSIGNED NOT NULL DEFAULT 1,
   uid INT(11) UNSIGNED NOT NULL,
-  type ENUM('forlife', 'alias') NOT NULL DEFAULT 'forlife',
+  type ENUM('forlife', 'alias', 'alias_aux') NOT NULL DEFAULT 'forlife',
   flags SET('bestalias', 'usage', 'marital') NOT NULL DEFAULT '',
   expire DATE DEFAULT NULL,
+  PRIMARY KEY (email, domain),
+  KEY (domain),
   KEY (uid),
   KEY (type),
-  FOREIGN KEY (uid) REFERENCES accounts (uid) ON UPDATE CASCADE ON DELETE CASCADE
+  FOREIGN KEY (uid) REFERENCES accounts (uid) ON UPDATE CASCADE ON DELETE CASCADE,
+  FOREIGN KEY (domain) REFERENCES email_virtual_domains (id) ON UPDATE CASCADE ON DELETE CASCADE
 ) ENGINE=InnoDB, CHARSET=utf8;
 
 CREATE TABLE email_source_other (
-  email VARCHAR(255) NOT NULL PRIMARY KEY,
-  hrmid VARCHAR(255) NOT NULL,
+  email VARCHAR(255) NOT NULL,
+  domain SMALLINT(3) UNSIGNED NOT NULL DEFAULT 1,
+  hrmid VARCHAR(255) NOT NULL DEFAULT '',
   type ENUM('homonym', 'ax', 'honeypot'),
-  expire DATE NOT NULL DEFAULT '0000-00-00'
+  expire DATE DEFAULT NULL,
+  PRIMARY KEY (email, domain),
+  KEY (domain),
+  KEY(hrmid),
+  FOREIGN KEY (domain) REFERENCES email_virtual_domains (id) ON UPDATE CASCADE ON DELETE CASCADE
 ) ENGINE=InnoDB, CHARSET=utf8;
 
 CREATE TABLE homonyms_list (
   hrmid VARCHAR(255) NOT NULL,
   uid INT(11) UNSIGNED NOT NULL,
   PRIMARY KEY (hrmid, uid),
+  key (uid),
   FOREIGN KEY (uid) REFERENCES accounts (uid) ON UPDATE CASCADE ON DELETE CASCADE
 ) ENGINE=InnoDB, CHARSET=utf8;
 
@@ -56,20 +75,20 @@ CREATE TABLE email_redirect_other (
   action ENUM('default', 'drop_spams', 'let_spams', 'tag_and_drop_spams', 'tag_spams', 'imap_and_bounce', 'homonym') NOT NULL DEFAULT 'default',
   PRIMARY KEY (hrmid, redirect),
   KEY (hrmid),
-  KEY (redirect)
+  KEY (redirect),
+  FOREIGN KEY (hrmid) REFERENCES email_source_other (hrmid) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB, CHARSET=utf8;
 
 CREATE TABLE email_virtual (
   email VARCHAR(255) NOT NULL,
+  domain SMALLINT(3) UNSIGNED NOT NULL DEFAULT 1,
   redirect VARCHAR(255) NOT NULL,
-  type ENUM('user', 'list', 'domain', 'event', 'admin', 'partner'),
+  type ENUM('alias', 'list', 'event', 'admin', 'partner'),
   expire DATE NOT NULL DEFAULT '0000-00-00',
   PRIMARY KEY (email, domain, redirect),
-  KEY (email)
-) ENGINE=InnoDB, CHARSET=utf8;
-
-CREATE TABLE email_virtual_domains (
-  domain VARCHAR(255) NOT NULL PRIMARY KEY
+  KEY (domain),
+  KEY (redirect),
+  FOREIGN KEY (domain) REFERENCES email_virtual_domains (id) ON UPDATE CASCADE ON DELETE CASCADE
 ) ENGINE=InnoDB, CHARSET=utf8;
 
 -- vim:set syntax=mysql: