Commits

Zhang Huangbin committed 245456c

Update SQL trigger in samples/dovecot/used_quota.mysql.

Comments (0)

Files changed (5)

iRedMail/functions/dovecot1.sh

 
 # Import SQL template.
 USE ${IREDADMIN_DB_NAME};
-SOURCE ${SAMPLE_DIR}/dovecot/used_quota.sql;
+SOURCE ${SAMPLE_DIR}/dovecot/used_quota.mysql;
 GRANT SELECT,INSERT,UPDATE,DELETE ON ${IREDADMIN_DB_NAME}.* TO "${IREDADMIN_DB_USER}"@"${SQL_HOSTNAME}" IDENTIFIED BY "${IREDADMIN_DB_PASSWD}";
 
 FLUSH PRIVILEGES;

iRedMail/functions/dovecot2.sh

 
 # Import SQL template.
 USE ${IREDADMIN_DB_NAME};
-SOURCE ${SAMPLE_DIR}/dovecot/used_quota.sql;
+SOURCE ${SAMPLE_DIR}/dovecot/used_quota.mysql;
 GRANT SELECT,INSERT,UPDATE,DELETE ON ${IREDADMIN_DB_NAME}.* TO "${IREDADMIN_DB_USER}"@"${SQL_HOSTNAME}" IDENTIFIED BY "${IREDADMIN_DB_PASSWD}";
 
 FLUSH PRIVILEGES;

iRedMail/functions/iredadmin.sh

         if [ X"${BACKEND}" == X"OPENLDAP" ]; then
             mysql -h${SQL_SERVER} -P${SQL_SERVER_PORT} -u${MYSQL_ROOT_USER} -p"${MYSQL_ROOT_PASSWD}" <<EOF
 USE ${IREDADMIN_DB_NAME};
-SOURCE ${SAMPLE_DIR}/dovecot/used_quota.sql;
+SOURCE ${SAMPLE_DIR}/dovecot/used_quota.mysql;
 SOURCE ${SAMPLE_DIR}/dovecot/imap_share_folder.sql;
 FLUSH PRIVILEGES;
 EOF

iRedMail/samples/dovecot/used_quota.mysql

+-- used_quota
+-- Used to store realtime mailbox quota in Dovecot.
+-- WARNING: Works only with Dovecot 1.2+.
+--
+-- Note: Don't touch this table, it will be updated by Dovecot automatically.
+CREATE TABLE IF NOT EXISTS `used_quota` (
+    `username` VARCHAR(255) NOT NULL,
+    `bytes` BIGINT NOT NULL DEFAULT 0,
+    `messages` BIGINT NOT NULL DEFAULT 0,
+    `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 ;

iRedMail/samples/dovecot/used_quota.sql

---
--- Table `used_quota`. Used for Dovecot to store realtime quota.
---
--- WARNING:
---   - Works only with Dovecot 1.2+.
---   - This table is not required in MySQL backend.
---
-CREATE TABLE IF NOT EXISTS `used_quota` (
-    `username` VARCHAR(255) NOT NULL,
-    `bytes` BIGINT NOT NULL DEFAULT 0,
-    `messages` BIGINT NOT NULL DEFAULT 0,
-    PRIMARY KEY (`username`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;