Commits

Zhang Huangbin committed 06ea16c

Update SQL triggers to insert value for column 'used_quota.domain' automatically.

  • Participants
  • Parent commits 5cf07d6

Comments (0)

Files changed (3)

File iRedMail/ChangeLog

 iRedMail-0.8.5:
+    * Update SQL triggers to insert value for column 'used_quota.domain'
+      automatically.
     * Install php53 from vender official yum repo on RHEL/CentOS 5.
     * Allow to assign mail list/alias as member of mailing list.
     * New SQL column and LDAP attribute for mail domain, used to store

File iRedMail/samples/iredmail.mysql

     `username` VARCHAR(255) NOT NULL,
     `bytes` BIGINT NOT NULL DEFAULT 0,
     `messages` BIGINT NOT NULL DEFAULT 0,
-    PRIMARY KEY (`username`)
+    `domain` VARCHAR(255) NOT NULL DEFAULT '',
+    PRIMARY KEY (`username`),
+    INDEX (domain)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+-- Trigger `after_insert_used_quota`, used to set `used_quota.domain`.
+-- NOTE: `used_quota.domain` is not used by Dovecot, but used in iRedAdmin to
+--       get better SQL query performance while calculating per-domain used
+--       quota.
+DELIMITER $$
+CREATE TRIGGER `after_insert_used_quota` BEFORE INSERT ON `used_quota` FOR EACH ROW
+    BEGIN
+        SET NEW.domain = SUBSTRING_INDEX(NEW.username, '@', -1);
+    END;
+$$
+DELIMITER ;

File iRedMail/samples/iredmail.pgsql

     username VARCHAR(255) NOT NULL,
     bytes INT8 NOT NULL DEFAULT 0,
     messages INT8 NOT NULL DEFAULT 0,
+    domain VARCHAR(255) NOT NULL DEFAULT '',
     PRIMARY KEY (username)
 );
+CREATE INDEX idx_used_quota_domain ON used_quota (domain);
 
 -- Trigger required by quota dict
 CREATE OR REPLACE FUNCTION merge_quota() RETURNS TRIGGER AS $$
 
     LOOP
         UPDATE used_quota
-        SET bytes = bytes + NEW.bytes, messages = messages + NEW.messages
+        SET bytes = bytes + NEW.bytes, messages = messages + NEW.messages, domain=split_part(NEW.username, '@', 2)
         WHERE username = NEW.username;
         IF found THEN
             RETURN NULL;
 
         BEGIN
             IF NEW.messages = 0 THEN
-                INSERT INTO used_quota (bytes, messages, username)
-                VALUES (NEW.bytes, NULL, NEW.username);
+                INSERT INTO used_quota (bytes, messages, username, domain)
+                VALUES (NEW.bytes, NULL, NEW.username, split_part(NEW.username, '@', 2));
             ELSE
-                INSERT INTO used_quota (bytes, messages, username)
-                VALUES (NEW.bytes, -NEW.messages, NEW.username);
+                INSERT INTO used_quota (bytes, messages, username, domain)
+                VALUES (NEW.bytes, -NEW.messages, NEW.username, split_part(NEW.username, '@', 2));
             END IF;
             return NULL;
             EXCEPTION WHEN unique_violation THEN