1 -- ---

     2 -- Use this file to update the database layout, if you are upgrading your

     3 -- Dovecot < v1.2.beta2 to Dovecot >= v1.2.beta2.

     4 -- 

     5 -- IMPORTANT

     6 -- This file supports only the current vmm 0.6.0 database layout.

     7 -- ---

     8 

     9 SET client_encoding = 'UTF8';

    10 SET client_min_messages = warning;

    11 

    12 

    13 ALTER TABLE service_set DROP CONSTRAINT ukey_service_set;

    14 ALTER TABLE service_set RENAME managesieve to sieve;

    15 ALTER TABLE service_set

    16     ADD CONSTRAINT ukey_service_set UNIQUE (smtp, pop3, imap, sieve);

    17 

    18 

    19 DROP TRIGGER mergeuserquota_11 ON userquota_11;

    20 DROP FUNCTION merge_userquota_11();

    21 DROP TABLE userquota_11;

    22 

    23 

    24 DROP TYPE dovecotpassword CASCADE;

    25 CREATE TYPE dovecotpassword AS (

    26     userid    varchar(320),

    27     password  varchar(270),

    28     smtp      boolean,

    29     pop3      boolean,

    30     imap      boolean,

    31     sieve     boolean

    32 );

    33 

    34 CREATE OR REPLACE FUNCTION dovecotpassword(

    35     IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword

    36 AS $$

    37     DECLARE

    38         record dovecotpassword;

    39         userid varchar(320) := localpart || '@' || the_domain;

    40     BEGIN

    41         FOR record IN

    42             SELECT userid, passwd, smtp, pop3, imap, sieve

    43               FROM users, service_set, domain_data

    44              WHERE users.gid = (SELECT gid

    45                                   FROM domain_name

    46                                  WHERE domainname = the_domain)

    47                AND local_part = localpart

    48                AND users.gid = domain_data.gid

    49                AND CASE WHEN

    50                      users.ssid IS NOT NULL

    51                      THEN

    52                        service_set.ssid = users.ssid

    53                      ELSE

    54                        service_set.ssid = domain_data.ssid

    55                      END

    56             LOOP

    57                 RETURN NEXT record;

    58             END LOOP;

    59         RETURN;

    60     END;

    61 $$ LANGUAGE plpgsql STABLE

    62 RETURNS NULL ON NULL INPUT

    63 EXTERNAL SECURITY INVOKER;

    64 

    65 

    66 CREATE TABLE userquota (

    67     uid         bigint NOT NULL,

    68     bytes       bigint NOT NULL DEFAULT 0,

    69     messages    integer NOT NULL DEFAULT 0,

    70     CONSTRAINT  pkey_userquota PRIMARY KEY (uid),

    71     CONSTRAINT  fkey_userquota_uid_users FOREIGN KEY (uid)

    72         REFERENCES users (uid) ON DELETE CASCADE

    73 );

    74 

    75 CREATE OR REPLACE FUNCTION merge_userquota() RETURNS TRIGGER AS $$

    76 BEGIN

    77     IF NEW.messages < 0 OR NEW.messages IS NULL THEN

    78         IF NEW.messages IS NULL THEN

    79             NEW.messages = 0;

    80         ELSE

    81             NEW.messages = -NEW.messages;

    82         END IF;

    83         RETURN NEW;

    84     END IF;

    85     LOOP

    86         UPDATE userquota

    87            SET bytes = bytes + NEW.bytes, messages = messages + NEW.messages

    88          WHERE uid = NEW.uid;

    89         IF found THEN

    90             RETURN NULL;

    91         END IF;

    92         BEGIN

    93             IF NEW.messages = 0 THEN

    94               INSERT INTO userquota VALUES (NEW.uid, NEW.bytes, NULL);

    95             ELSE

    96               INSERT INTO userquota VALUES (NEW.uid, NEW.bytes, -NEW.messages);

    97             END IF;

    98             RETURN NULL;

    99         EXCEPTION

   100             WHEN unique_violation THEN

   101                 -- do nothing, and loop to try the UPDATE again

   102             WHEN foreign_key_violation THEN

   103                 -- break the loop: a non matching uid means no such user

   104                 RETURN NULL;

   105         END;

   106     END LOOP;

   107 END;

   108 $$ LANGUAGE plpgsql;

   109 

   110 CREATE TRIGGER mergeuserquota BEFORE INSERT ON userquota

   111     FOR EACH ROW EXECUTE PROCEDURE merge_userquota();