pgsql/dovecot_update_v1.2+.pgsql
changeset 598 bb23693e5fc9
equal deleted inserted replaced
597:c79fdce49908 598:bb23693e5fc9
       
     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();