-- ---

-- 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();