--- a/pgsql/create_tables-dovecot-1.2.x.pgsql	Wed Feb 02 21:09:50 2011 +0000
+++ b/pgsql/create_tables-dovecot-1.2.x.pgsql	Fri Feb 04 17:29:35 2011 +0000
@@ -8,6 +8,8 @@
 
 CREATE SEQUENCE maillocation_id;
 
+CREATE SEQUENCE quotalimit_id;
+
 CREATE SEQUENCE domain_gid
     START WITH 70000
     INCREMENT BY 1
@@ -55,13 +57,26 @@
 -- Insert default Maildir-folder name
 INSERT INTO maillocation(directory) VALUES ('Maildir');
 
+CREATE TABLE quotalimit (
+    qid         bigint NOT NULL DEFAULT nextval('quotalimit_id'),
+    bytes       bigint NOT NULL,
+    messages    integer NOT NULL DEFAULT 0,
+    CONSTRAINT  pkey_quotalimit PRIMARY KEY (qid),
+    CONSTRAINT  ukey_quotalimit UNIQUE (bytes, messages)
+);
+-- Insert default (non) quota limit
+INSERT INTO quotalimit(bytes, messages) VALUES (0, 0);
+
 CREATE TABLE domain_data (
     gid         bigint NOT NULL DEFAULT nextval('domain_gid'),
     tid         bigint NOT NULL DEFAULT 1, -- default transport
+    qid         bigint NOT NULL DEFAULT 1, -- default quota limit
     domaindir   varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294
     CONSTRAINT  pkey_domain_data PRIMARY KEY (gid),
     CONSTRAINT  fkey_domain_data_tid_transport FOREIGN KEY (tid)
-        REFERENCES transport (tid)
+        REFERENCES transport (tid),
+    CONSTRAINT  fkey_domain_data_qid_quotalimit FOREIGN KEY (qid)
+        REFERENCES quotalimit (qid)
 );
 
 CREATE TABLE domain_name (
@@ -81,6 +96,7 @@
     gid         bigint NOT NULL,
     mid         bigint NOT NULL DEFAULT 1,
     tid         bigint NOT NULL DEFAULT 1,
+    qid         bigint NOT NULL DEFAULT 1,
     smtp        boolean NOT NULL DEFAULT TRUE,
     pop3        boolean NOT NULL DEFAULT TRUE,
     imap        boolean NOT NULL DEFAULT TRUE,
@@ -92,7 +108,18 @@
     CONSTRAINT  fkey_users_mid_maillocation FOREIGN KEY (mid)
         REFERENCES maillocation (mid),
     CONSTRAINT  fkey_users_tid_transport FOREIGN KEY (tid)
-        REFERENCES transport (tid)
+        REFERENCES transport (tid),
+    CONSTRAINT  fkey_users_qid_quotalimit FOREIGN KEY (qid)
+        REFERENCES quotalimit (qid)
+);
+
+CREATE TABLE userquota (
+    uid         bigint NOT NULL,
+    bytes       bigint NOT NULL DEFAULT 0,
+    messages    integer NOT NULL DEFAULT 0,
+    CONSTRAINT  pkey_userquota PRIMARY KEY (uid),
+    CONSTRAINT  fkey_userquota_uid_users FOREIGN KEY (uid)
+        REFERENCES users (uid)
 );
 
 CREATE TABLE alias (
@@ -211,3 +238,43 @@
 
 CREATE TRIGGER primary_count_upd AFTER UPDATE ON domain_name
     FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();
+
+
+CREATE OR REPLACE FUNCTION merge_userquota() RETURNS TRIGGER AS $$
+BEGIN
+    IF NEW.messages < 0 OR NEW.messages IS NULL THEN
+        IF NEW.messages IS NULL THEN
+            NEW.messages = 0;
+        ELSE
+            NEW.messages = -NEW.messages;
+        END IF;
+        RETURN NEW;
+    END IF;
+    LOOP
+        UPDATE userquota
+           SET bytes = bytes + NEW.bytes, messages = messages + NEW.messages
+         WHERE uid = NEW.uid;
+        IF found THEN
+            RETURN NULL;
+        END IF;
+        BEGIN
+            IF NEW.messages = 0 THEN
+              INSERT INTO userquota VALUES (NEW.uid, NEW.bytes, NULL);
+            ELSE
+              INSERT INTO userquota VALUES (NEW.uid, NEW.bytes, -NEW.messages);
+            END IF;
+            RETURN NULL;
+        EXCEPTION
+            WHEN unique_violation THEN
+                -- do nothing, and loop to try the UPDATE again
+            WHEN foreign_key_violation THEN
+                -- break the loop: a non matching uid means no such user
+                RETURN NULL;
+        END;
+    END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+
+CREATE TRIGGER mergeuserquota BEFORE INSERT ON userquota
+    FOR EACH ROW EXECUTE PROCEDURE merge_userquota();