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 46cbd97..47bcb54 100644 (file)
--- a/upgrade/1.1.0/01_new_mail_schema.sql
+++ b/upgrade/1.1.0/01_new_mail_schema.sql
@@ -1,8 +1,8 @@
+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;
 
@@ -19,7 +19,7 @@ CREATE TABLE email_source_account (
   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),
@@ -33,11 +33,12 @@ CREATE TABLE email_source_account (
 CREATE TABLE email_source_other (
   email VARCHAR(255) NOT NULL,
   domain SMALLINT(3) UNSIGNED NOT NULL DEFAULT 1,
-  hrmid VARCHAR(255) NOT NULL,
+  hrmid VARCHAR(255) NOT NULL DEFAULT '',
   type ENUM('homonym', 'ax', 'honeypot'),
   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;
 
@@ -74,14 +75,15 @@ 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 (domain),