pgsql/dovecot_update_v1.2+.pgsql
author Pascal Volk <user@localhost.localdomain.org>
Sun, 21 Nov 2021 21:27:53 +0000
branchv0.7.x
changeset 759 2a23083e2ea3
parent 598 bb23693e5fc9
permissions -rw-r--r--
closed branch v0.7.x
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
598
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
     1
-- ---
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
     2
-- Use this file to update the database layout, if you are upgrading your
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
     3
-- Dovecot < v1.2.beta2 to Dovecot >= v1.2.beta2.
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
     4
-- 
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
     5
-- IMPORTANT
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
     6
-- This file supports only the current vmm 0.6.0 database layout.
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
     7
-- ---
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
     8
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
     9
SET client_encoding = 'UTF8';
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    10
SET client_min_messages = warning;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    11
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    12
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    13
ALTER TABLE service_set DROP CONSTRAINT ukey_service_set;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    14
ALTER TABLE service_set RENAME managesieve to sieve;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    15
ALTER TABLE service_set
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    16
    ADD CONSTRAINT ukey_service_set UNIQUE (smtp, pop3, imap, sieve);
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    17
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    18
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    19
DROP TRIGGER mergeuserquota_11 ON userquota_11;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    20
DROP FUNCTION merge_userquota_11();
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    21
DROP TABLE userquota_11;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    22
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    23
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    24
DROP TYPE dovecotpassword CASCADE;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    25
CREATE TYPE dovecotpassword AS (
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    26
    userid    varchar(320),
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    27
    password  varchar(270),
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    28
    smtp      boolean,
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    29
    pop3      boolean,
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    30
    imap      boolean,
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    31
    sieve     boolean
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    32
);
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    33
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    34
CREATE OR REPLACE FUNCTION dovecotpassword(
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    35
    IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    36
AS $$
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    37
    DECLARE
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    38
        record dovecotpassword;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    39
        userid varchar(320) := localpart || '@' || the_domain;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    40
    BEGIN
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    41
        FOR record IN
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    42
            SELECT userid, passwd, smtp, pop3, imap, sieve
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    43
              FROM users, service_set, domain_data
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    44
             WHERE users.gid = (SELECT gid
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    45
                                  FROM domain_name
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    46
                                 WHERE domainname = the_domain)
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    47
               AND local_part = localpart
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    48
               AND users.gid = domain_data.gid
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    49
               AND CASE WHEN
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    50
                     users.ssid IS NOT NULL
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    51
                     THEN
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    52
                       service_set.ssid = users.ssid
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    53
                     ELSE
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    54
                       service_set.ssid = domain_data.ssid
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    55
                     END
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    56
            LOOP
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    57
                RETURN NEXT record;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    58
            END LOOP;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    59
        RETURN;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    60
    END;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    61
$$ LANGUAGE plpgsql STABLE
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    62
RETURNS NULL ON NULL INPUT
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    63
EXTERNAL SECURITY INVOKER;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    64
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    65
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    66
CREATE TABLE userquota (
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    67
    uid         bigint NOT NULL,
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    68
    bytes       bigint NOT NULL DEFAULT 0,
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    69
    messages    integer NOT NULL DEFAULT 0,
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    70
    CONSTRAINT  pkey_userquota PRIMARY KEY (uid),
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    71
    CONSTRAINT  fkey_userquota_uid_users FOREIGN KEY (uid)
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    72
        REFERENCES users (uid) ON DELETE CASCADE
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    73
);
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    74
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    75
CREATE OR REPLACE FUNCTION merge_userquota() RETURNS TRIGGER AS $$
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    76
BEGIN
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    77
    IF NEW.messages < 0 OR NEW.messages IS NULL THEN
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    78
        IF NEW.messages IS NULL THEN
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    79
            NEW.messages = 0;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    80
        ELSE
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    81
            NEW.messages = -NEW.messages;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    82
        END IF;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    83
        RETURN NEW;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    84
    END IF;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    85
    LOOP
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    86
        UPDATE userquota
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    87
           SET bytes = bytes + NEW.bytes, messages = messages + NEW.messages
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    88
         WHERE uid = NEW.uid;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    89
        IF found THEN
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    90
            RETURN NULL;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    91
        END IF;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    92
        BEGIN
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    93
            IF NEW.messages = 0 THEN
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    94
              INSERT INTO userquota VALUES (NEW.uid, NEW.bytes, NULL);
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    95
            ELSE
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    96
              INSERT INTO userquota VALUES (NEW.uid, NEW.bytes, -NEW.messages);
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    97
            END IF;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    98
            RETURN NULL;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
    99
        EXCEPTION
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   100
            WHEN unique_violation THEN
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   101
                -- do nothing, and loop to try the UPDATE again
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   102
            WHEN foreign_key_violation THEN
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   103
                -- break the loop: a non matching uid means no such user
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   104
                RETURN NULL;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   105
        END;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   106
    END LOOP;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   107
END;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   108
$$ LANGUAGE plpgsql;
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   109
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   110
CREATE TRIGGER mergeuserquota BEFORE INSERT ON userquota
bb23693e5fc9 pgsql: Added dovecot_update_v1.2+.pgsql.
Pascal Volk <user@localhost.localdomain.org>
parents:
diff changeset
   111
    FOR EACH ROW EXECUTE PROCEDURE merge_userquota();