+
−SET client_encoding = 'UTF8';
+
−SET client_min_messages = warning;
+
−
+
−-- ---
+
−-- Create the new service_set table and insert all possible combinations
+
−-- --
+
−CREATE SEQUENCE service_set_id;
+
−
+
−CREATE TABLE service_set (
+
−    ssid        bigint NOT NULL DEFAULT nextval('service_set_id'),
+
−    smtp        boolean NOT NULL DEFAULT TRUE,
+
−    pop3        boolean NOT NULL DEFAULT TRUE,
+
−    imap        boolean NOT NULL DEFAULT TRUE,
+
−    sieve       boolean NOT NULL DEFAULT TRUE,
+
−    CONSTRAINT  pkey_service_set PRIMARY KEY (ssid),
+
−    CONSTRAINT  ukey_service_set UNIQUE (smtp, pop3, imap, sieve)
+
−);
+
−
+
−COPY service_set (smtp, pop3, imap, sieve) FROM stdin;
+
−TRUE	TRUE	TRUE	TRUE
+
−FALSE	TRUE	TRUE	TRUE
+
−TRUE	FALSE	TRUE	TRUE
+
−FALSE	FALSE	TRUE	TRUE
+
−TRUE	TRUE	FALSE	TRUE
+
−FALSE	TRUE	FALSE	TRUE
+
−TRUE	FALSE	FALSE	TRUE
+
−FALSE	FALSE	FALSE	TRUE
+
−TRUE	TRUE	TRUE	FALSE
+
−FALSE	TRUE	TRUE	FALSE
+
−TRUE	FALSE	TRUE	FALSE
+
−FALSE	FALSE	TRUE	FALSE
+
−TRUE	TRUE	FALSE	FALSE
+
−FALSE	TRUE	FALSE	FALSE
+
−TRUE	FALSE	FALSE	FALSE
+
−FALSE	FALSE	FALSE	FALSE
+
−\.
+
−
+
−-- ---
+
−-- Make room for different mailbox formats and longer password hashes.
+
−-- ---
+
−DROP VIEW dovecot_user;
+
−DROP VIEW dovecot_password;
+
−DROP VIEW postfix_maildir;
+
−DROP VIEW vmm_domain_info;
+
−
+
−CREATE SEQUENCE mailboxformat_id;
+
−CREATE SEQUENCE quotalimit_id;
+
−
+
−CREATE TABLE mailboxformat (
+
−    fid         bigint NOT NULL DEFAULT nextval('mailboxformat_id'),
+
−    format      varchar(20) NOT NULL,
+
−    CONSTRAINT  pkey_mailboxformat PRIMARY KEY (fid),
+
−    CONSTRAINT  ukey_mailboxformat UNIQUE (format)
+
−);
+
−-- Insert supported mailbox formats
+
−INSERT INTO mailboxformat(format) VALUES ('maildir');
+
−INSERT INTO mailboxformat(format) VALUES ('mdbox');
+
−INSERT INTO mailboxformat(format) VALUES ('sdbox');
+
−
+
−-- Adjust maillocation table
+
−ALTER TABLE maillocation DROP CONSTRAINT ukey_maillocation;
+
−ALTER TABLE maillocation RENAME COLUMN maillocation TO directory;
+
−ALTER TABLE maillocation
+
−    ADD COLUMN fid bigint NOT NULL DEFAULT 1,
+
−    ADD COLUMN extra varchar(1024);
+
−ALTER TABLE maillocation ADD CONSTRAINT fkey_maillocation_fid_mailboxformat
+
−    FOREIGN KEY (fid) REFERENCES mailboxformat (fid);
+
−
+
−ALTER TABLE users ALTER COLUMN passwd TYPE varchar(270);
+
−
+
−-- ---
+
−-- Add quota stuff
+
−-- ---
+
−CREATE TABLE quotalimit (
+
−    qid         bigint NOT NULL DEFAULT nextval('quotalimit_id'),
+
−    bytes       bigint NOT NULL,
+
−    messages    integer NOT NULL DEFAULT 0,
+
−    CONSTRAINT  pkey_quotalimit PRIMARY KEY (qid),
+
−    CONSTRAINT  ukey_quotalimit UNIQUE (bytes, messages)
+
−);
+
−-- Insert default (non) quota limit
+
−INSERT INTO quotalimit(bytes, messages) VALUES (0, 0);
+
−
+
−-- Adjust tables (quota)
+
−ALTER TABLE domain_data ADD COLUMN qid bigint NOT NULL DEFAULT 1;
+
−ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_qid_quotalimit
+
−    FOREIGN KEY (qid) REFERENCES quotalimit (qid);
+
−
+
−ALTER TABLE users ADD COLUMN qid bigint NOT NULL DEFAULT 1;
+
−ALTER TABLE users ADD CONSTRAINT fkey_users_qid_quotalimit
+
−    FOREIGN KEY (qid) REFERENCES quotalimit (qid);
+
−
+
−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();
+
−
+
−-- Adjust tables (services)
+
−ALTER TABLE domain_data ADD COLUMN ssid bigint NOT NULL DEFAULT 1;
+
−ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_ssid_service_set
+
−    FOREIGN KEY (ssid) REFERENCES service_set (ssid);
+
−
+
−ALTER TABLE users ADD COLUMN ssid bigint NOT NULL DEFAULT 1;
+
−-- save current service sets
+
−UPDATE users u
+
−   SET ssid = ss.ssid
+
−  FROM service_set ss
+
− WHERE ss.smtp = u.smtp
+
−   AND ss.pop3 = u.pop3
+
−   AND ss.imap = u.imap
+
−   AND ss.sieve = u.sieve;
+
−
+
−ALTER TABLE users DROP COLUMN smtp;
+
−ALTER TABLE users DROP COLUMN pop3;
+
−ALTER TABLE users DROP COLUMN imap;
+
−ALTER TABLE users DROP COLUMN sieve;
+
−ALTER TABLE users ADD CONSTRAINT fkey_users_ssid_service_set
+
−    FOREIGN KEY (ssid) REFERENCES service_set (ssid);
+
−
+
−-- ---
+
−-- Restore views
+
−-- ---
+
−CREATE VIEW dovecot_user AS
+
−    SELECT local_part || '@' || domain_name.domainname AS userid,
+
−           uid, gid, domain_data.domaindir || '/' || uid AS home,
+
−           mailboxformat.format || ':~/' || maillocation.directory AS mail
+
−      FROM users
+
−           LEFT JOIN domain_data USING (gid)
+
−           LEFT JOIN domain_name USING (gid)
+
−           LEFT JOIN maillocation USING (mid)
+
−           LEFT JOIN mailboxformat USING (fid);
+
−
+
−CREATE OR REPLACE VIEW dovecot_password AS
+
−    SELECT local_part || '@' || domainname AS "user",
+
−           passwd AS "password", smtp, pop3, imap, sieve
+
−      FROM users
+
−           LEFT JOIN domain_name USING (gid)
+
−           LEFT JOIN service_set USING (ssid);
+
−
+
−CREATE VIEW postfix_maildir AS
+
−    SELECT local_part || '@' || domain_name.domainname AS address,
+
−           domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/'
+
−           AS maildir
+
−      FROM users
+
−           LEFT JOIN domain_data USING (gid)
+
−           LEFT JOIN domain_name USING (gid)
+
−           LEFT JOIN maillocation USING (mid);
+
−
+
−CREATE VIEW vmm_domain_info AS
+
−    SELECT gid, count(uid) AS accounts,
+
−           (SELECT count(DISTINCT address)
+
−              FROM alias
+
−             WHERE alias.gid = domain_data.gid) AS aliases,
+
−           (SELECT count(gid)
+
−              FROM relocated
+
−             WHERE relocated.gid = domain_data.gid) AS relocated,
+
−           (SELECT count(gid)
+
−              FROM domain_name
+
−             WHERE domain_name.gid = domain_data.gid
+
−               AND NOT domain_name.is_primary) AS aliasdomains
+
−      FROM domain_data
+
−           LEFT JOIN domain_name USING (gid)
+
−           LEFT JOIN users USING (gid)
+
−     WHERE domain_name.is_primary
+
−  GROUP BY gid;