Fixes comment position.

[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..d0e729d 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',
   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'),
   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: