--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/pgsql/dovecot_update_v1.2+.pgsql	Mon Aug 27 23:58:33 2012 +0000
@@ -0,0 +1,111 @@
+-- ---
+-- Use this file to update the database layout, if you are upgrading your
+-- Dovecot < v1.2.beta2 to Dovecot >= v1.2.beta2.
+-- 
+-- IMPORTANT
+-- This file supports only the current vmm 0.6.0 database layout.
+-- ---
+
+SET client_encoding = 'UTF8';
+SET client_min_messages = warning;
+
+
+ALTER TABLE service_set DROP CONSTRAINT ukey_service_set;
+ALTER TABLE service_set RENAME managesieve to sieve;
+ALTER TABLE service_set
+    ADD CONSTRAINT ukey_service_set UNIQUE (smtp, pop3, imap, sieve);
+
+
+DROP TRIGGER mergeuserquota_11 ON userquota_11;
+DROP FUNCTION merge_userquota_11();
+DROP TABLE userquota_11;
+
+
+DROP TYPE dovecotpassword CASCADE;
+CREATE TYPE dovecotpassword AS (
+    userid    varchar(320),
+    password  varchar(270),
+    smtp      boolean,
+    pop3      boolean,
+    imap      boolean,
+    sieve     boolean
+);
+
+CREATE OR REPLACE FUNCTION dovecotpassword(
+    IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword
+AS $$
+    DECLARE
+        record dovecotpassword;
+        userid varchar(320) := localpart || '@' || the_domain;
+    BEGIN
+        FOR record IN
+            SELECT userid, passwd, smtp, pop3, imap, sieve
+              FROM users, service_set, domain_data
+             WHERE users.gid = (SELECT gid
+                                  FROM domain_name
+                                 WHERE domainname = the_domain)
+               AND local_part = localpart
+               AND users.gid = domain_data.gid
+               AND CASE WHEN
+                     users.ssid IS NOT NULL
+                     THEN
+                       service_set.ssid = users.ssid
+                     ELSE
+                       service_set.ssid = domain_data.ssid
+                     END
+            LOOP
+                RETURN NEXT record;
+            END LOOP;
+        RETURN;
+    END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+
+
+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) ON DELETE CASCADE
+);
+
+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();