# HG changeset patch # User Pascal Volk # Date 1296840575 0 # Node ID 5e6bcb2e010e682070c6e06d68d022987ce138b0 # Parent 98223e5c95e011c85d55566c42f82242cbc70e85 pgsql/*: Added tables, triggers and functions for quota support. diff -r 98223e5c95e0 -r 5e6bcb2e010e pgsql/create_optional_types_and_functions-dovecot-1.2.x.pgsql --- a/pgsql/create_optional_types_and_functions-dovecot-1.2.x.pgsql Wed Feb 02 21:09:50 2011 +0000 +++ b/pgsql/create_optional_types_and_functions-dovecot-1.2.x.pgsql Fri Feb 04 17:29:35 2011 +0000 @@ -287,6 +287,17 @@ home text, mail text ); +-- --- +-- Data type for function dovecotquotauser(varchar, varchar) +-- --- +CREATE TYPE dovecotquotauser AS ( + userid varchar(320), + uid bigint, + gid bigint, + home text, + mail text, + quota_rule text +); -- --- -- Parameters (from login name [localpart@the_domain]): @@ -327,6 +338,44 @@ RETURNS NULL ON NULL INPUT EXTERNAL SECURITY INVOKER; +-- --- +-- Nearly the same as function dovecotuser above. It returns additionally the +-- field quota_rule. +-- +-- Required access privileges for your dovecot database user: +-- GRANT SELECT +-- ON users, domain_data, domain_name, maillocation, mailboxformat, +-- quotalimit +-- TO dovecot; +-- --- +CREATE OR REPLACE FUNCTION dovecotquotauser( + IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotquotauser +AS $$ + DECLARE + record dovecotquotauser; + userid varchar(320) := localpart || '@' || the_domain; + did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); + BEGIN + FOR record IN + SELECT userid, uid, did, domaindir || '/' || uid AS home, + format || ':~/' || directory AS mail, '*:bytes=' || + bytes || ':messages=' || messages AS quota_rule + FROM users, domain_data, mailboxformat, maillocation, quotalimit + WHERE users.gid = did + AND users.local_part = localpart + AND maillocation.mid = users.mid + AND mailboxformat.fid = maillocation.fid + AND domain_data.gid = did + AND quotalimit.qid = users.qid + LOOP + RETURN NEXT record; + END LOOP; + RETURN; + END; +$$ LANGUAGE plpgsql STABLE +RETURNS NULL ON NULL INPUT +EXTERNAL SECURITY INVOKER; + -- ########################################################################## -- -- --- diff -r 98223e5c95e0 -r 5e6bcb2e010e pgsql/create_optional_types_and_functions.pgsql --- a/pgsql/create_optional_types_and_functions.pgsql Wed Feb 02 21:09:50 2011 +0000 +++ b/pgsql/create_optional_types_and_functions.pgsql Fri Feb 04 17:29:35 2011 +0000 @@ -287,6 +287,17 @@ home text, mail text ); +-- --- +-- Data type for function dovecotquotauser(varchar, varchar) +-- --- +CREATE TYPE dovecotquotauser AS ( + userid varchar(320), + uid bigint, + gid bigint, + home text, + mail text, + quota_rule text +); -- --- -- Parameters (from login name [localpart@the_domain]): @@ -327,6 +338,44 @@ RETURNS NULL ON NULL INPUT EXTERNAL SECURITY INVOKER; +-- --- +-- Nearly the same as function dovecotuser above. It returns additionally the +-- field quota_rule. +-- +-- Required access privileges for your dovecot database user: +-- GRANT SELECT +-- ON users, domain_data, domain_name, maillocation, mailboxformat, +-- quotalimit +-- TO dovecot; +-- --- +CREATE OR REPLACE FUNCTION dovecotquotauser( + IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotquotauser +AS $$ + DECLARE + record dovecotquotauser; + userid varchar(320) := localpart || '@' || the_domain; + did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); + BEGIN + FOR record IN + SELECT userid, uid, did, domaindir || '/' || uid AS home, + format || ':~/' || directory AS mail, '*:bytes=' || + bytes || ':messages=' || messages AS quota_rule + FROM users, domain_data, mailboxformat, maillocation, quotalimit + WHERE users.gid = did + AND users.local_part = localpart + AND maillocation.mid = users.mid + AND mailboxformat.fid = maillocation.fid + AND domain_data.gid = did + AND quotalimit.qid = users.qid + LOOP + RETURN NEXT record; + END LOOP; + RETURN; + END; +$$ LANGUAGE plpgsql STABLE +RETURNS NULL ON NULL INPUT +EXTERNAL SECURITY INVOKER; + -- ########################################################################## -- -- --- diff -r 98223e5c95e0 -r 5e6bcb2e010e pgsql/create_tables-dovecot-1.2.x.pgsql --- a/pgsql/create_tables-dovecot-1.2.x.pgsql Wed Feb 02 21:09:50 2011 +0000 +++ b/pgsql/create_tables-dovecot-1.2.x.pgsql Fri Feb 04 17:29:35 2011 +0000 @@ -8,6 +8,8 @@ CREATE SEQUENCE maillocation_id; +CREATE SEQUENCE quotalimit_id; + CREATE SEQUENCE domain_gid START WITH 70000 INCREMENT BY 1 @@ -55,13 +57,26 @@ -- Insert default Maildir-folder name INSERT INTO maillocation(directory) VALUES ('Maildir'); +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); + CREATE TABLE domain_data ( gid bigint NOT NULL DEFAULT nextval('domain_gid'), tid bigint NOT NULL DEFAULT 1, -- default transport + qid bigint NOT NULL DEFAULT 1, -- default quota limit domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294 CONSTRAINT pkey_domain_data PRIMARY KEY (gid), CONSTRAINT fkey_domain_data_tid_transport FOREIGN KEY (tid) - REFERENCES transport (tid) + REFERENCES transport (tid), + CONSTRAINT fkey_domain_data_qid_quotalimit FOREIGN KEY (qid) + REFERENCES quotalimit (qid) ); CREATE TABLE domain_name ( @@ -81,6 +96,7 @@ gid bigint NOT NULL, mid bigint NOT NULL DEFAULT 1, tid bigint NOT NULL DEFAULT 1, + qid bigint NOT NULL DEFAULT 1, smtp boolean NOT NULL DEFAULT TRUE, pop3 boolean NOT NULL DEFAULT TRUE, imap boolean NOT NULL DEFAULT TRUE, @@ -92,7 +108,18 @@ CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid) REFERENCES maillocation (mid), CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid) - REFERENCES transport (tid) + REFERENCES transport (tid), + 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) ); CREATE TABLE alias ( @@ -211,3 +238,43 @@ CREATE TRIGGER primary_count_upd AFTER UPDATE ON domain_name FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger(); + + +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(); diff -r 98223e5c95e0 -r 5e6bcb2e010e pgsql/create_tables.pgsql --- a/pgsql/create_tables.pgsql Wed Feb 02 21:09:50 2011 +0000 +++ b/pgsql/create_tables.pgsql Fri Feb 04 17:29:35 2011 +0000 @@ -6,6 +6,8 @@ CREATE SEQUENCE mailboxformat_id; +CREATE SEQUENCE quotalimit_id; + CREATE SEQUENCE maillocation_id; CREATE SEQUENCE domain_gid @@ -55,13 +57,26 @@ -- Insert default Maildir-folder name INSERT INTO maillocation(directory) VALUES ('Maildir'); +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); + CREATE TABLE domain_data ( gid bigint NOT NULL DEFAULT nextval('domain_gid'), tid bigint NOT NULL DEFAULT 1, -- default transport + qid bigint NOT NULL DEFAULT 1, -- default quota limit domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294 CONSTRAINT pkey_domain_data PRIMARY KEY (gid), CONSTRAINT fkey_domain_data_tid_transport FOREIGN KEY (tid) - REFERENCES transport (tid) + REFERENCES transport (tid), + CONSTRAINT fkey_domain_data_qid_quotalimit FOREIGN KEY (qid) + REFERENCES quotalimit (qid) ); CREATE TABLE domain_name ( @@ -81,6 +96,7 @@ gid bigint NOT NULL, mid bigint NOT NULL DEFAULT 1, tid bigint NOT NULL DEFAULT 1, + qid bigint NOT NULL DEFAULT 1, smtp boolean NOT NULL DEFAULT TRUE, pop3 boolean NOT NULL DEFAULT TRUE, imap boolean NOT NULL DEFAULT TRUE, @@ -92,7 +108,18 @@ CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid) REFERENCES maillocation (mid), CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid) - REFERENCES transport (tid) + REFERENCES transport (tid), + CONSTRAINT fkey_users_qid_quotalimit FOREIGN KEY (qid) + REFERENCES quotalimit (qid) +); + +CREATE TABLE userquota_11 ( + uid bigint NOT NULL, + path varchar(16) NOT NULL, + current bigint NOT NULL DEFAULT 0, + CONSTRAINT pkey_userquota_11 PRIMARY KEY (uid, path), + CONSTRAINT fkey_userquota_11_uid_users FOREIGN KEY (uid) + REFERENCES users (uid) ); CREATE TABLE alias ( @@ -211,3 +238,20 @@ CREATE TRIGGER primary_count_upd AFTER UPDATE ON domain_name FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger(); + + +CREATE OR REPLACE FUNCTION merge_userquota_11() RETURNS TRIGGER AS $$ +BEGIN + UPDATE userquota_11 + SET current = current + NEW.current + WHERE uid = NEW.uid AND path = NEW.path; + IF found THEN + RETURN NULL; + ELSE + RETURN NEW; + END IF; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER mergeuserquota_11 BEFORE INSERT ON userquota_11 + FOR EACH ROW EXECUTE PROCEDURE merge_userquota_11(); diff -r 98223e5c95e0 -r 5e6bcb2e010e pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql --- a/pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql Wed Feb 02 21:09:50 2011 +0000 +++ b/pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql Fri Feb 04 17:29:35 2011 +0000 @@ -22,6 +22,7 @@ DROP VIEW postfix_maildir; CREATE SEQUENCE mailboxformat_id; +CREATE SEQUENCE quotalimit_id; CREATE TABLE mailboxformat ( fid bigint NOT NULL DEFAULT nextval('mailboxformat_id'), @@ -44,6 +45,78 @@ FOREIGN KEY (fid) REFERENCES mailboxformat (fid); +-- --- +-- 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 +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) +); + +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(); + +-- --- +-- Restore views +-- --- CREATE VIEW dovecot_user AS SELECT local_part || '@' || domain_name.domainname AS userid, uid, gid, domain_data.domaindir || '/' || uid AS home, diff -r 98223e5c95e0 -r 5e6bcb2e010e pgsql/update_tables_0.5.x-0.6.pgsql --- a/pgsql/update_tables_0.5.x-0.6.pgsql Wed Feb 02 21:09:50 2011 +0000 +++ b/pgsql/update_tables_0.5.x-0.6.pgsql Fri Feb 04 17:29:35 2011 +0000 @@ -22,6 +22,7 @@ DROP VIEW postfix_maildir; CREATE SEQUENCE mailboxformat_id; +CREATE SEQUENCE quotalimit_id; CREATE TABLE mailboxformat ( fid bigint NOT NULL DEFAULT nextval('mailboxformat_id'), @@ -44,6 +45,56 @@ FOREIGN KEY (fid) REFERENCES mailboxformat (fid); +-- --- +-- 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 … +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_11 ( + uid bigint NOT NULL, + path varchar(16) NOT NULL, + current bigint NOT NULL DEFAULT 0, + CONSTRAINT pkey_userquota_11 PRIMARY KEY (uid, path), + CONSTRAINT fkey_userquota_11_uid_users FOREIGN KEY (uid) + REFERENCES users (uid) +); + +CREATE OR REPLACE FUNCTION merge_userquota_11() RETURNS TRIGGER AS $$ +BEGIN + UPDATE userquota_11 + SET current = current + NEW.current + WHERE uid = NEW.uid AND path = NEW.path; + IF found THEN + RETURN NULL; + ELSE + RETURN NEW; + END IF; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER mergeuserquota_11 BEFORE INSERT ON userquota_11 + FOR EACH ROW EXECUTE PROCEDURE merge_userquota_11(); + +-- --- +-- Restore views +-- --- CREATE VIEW dovecot_user AS SELECT local_part || '@' || domain_name.domainname AS userid, uid, gid, domain_data.domaindir || '/' || uid AS home, diff -r 98223e5c95e0 -r 5e6bcb2e010e pgsql/update_types_and_functions_0.5.x-0.6-dovecot-1.2.x.pgsql --- a/pgsql/update_types_and_functions_0.5.x-0.6-dovecot-1.2.x.pgsql Wed Feb 02 21:09:50 2011 +0000 +++ b/pgsql/update_types_and_functions_0.5.x-0.6-dovecot-1.2.x.pgsql Fri Feb 04 17:29:35 2011 +0000 @@ -84,6 +84,17 @@ RETURNS NULL ON NULL INPUT EXTERNAL SECURITY INVOKER; -- --- +-- Data type for function dovecotquotauser(varchar, varchar) +-- --- +CREATE TYPE dovecotquotauser AS ( + userid varchar(320), + uid bigint, + gid bigint, + home text, + mail text, + quota_rule text +); +-- --- -- Parameters (from login name [localpart@the_domain]): -- varchar localpart -- varchar the_domain @@ -121,3 +132,40 @@ $$ LANGUAGE plpgsql STABLE RETURNS NULL ON NULL INPUT EXTERNAL SECURITY INVOKER; +-- --- +-- Nearly the same as function dovecotuser above. It returns additionally the +-- field quota_rule. +-- +-- Required access privileges for your dovecot database user: +-- GRANT SELECT +-- ON users, domain_data, domain_name, maillocation, mailboxformat, +-- quotalimit +-- TO dovecot; +-- --- +CREATE OR REPLACE FUNCTION dovecotquotauser( + IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotquotauser +AS $$ + DECLARE + record dovecotquotauser; + userid varchar(320) := localpart || '@' || the_domain; + did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); + BEGIN + FOR record IN + SELECT userid, uid, did, domaindir || '/' || uid AS home, + format || ':~/' || directory AS mail, '*:bytes=' || + bytes || ':messages=' || messages AS quota_rule + FROM users, domain_data, mailboxformat, maillocation, quotalimit + WHERE users.gid = did + AND users.local_part = localpart + AND maillocation.mid = users.mid + AND mailboxformat.fid = maillocation.fid + AND domain_data.gid = did + AND quotalimit.qid = users.qid + LOOP + RETURN NEXT record; + END LOOP; + RETURN; + END; +$$ LANGUAGE plpgsql STABLE +RETURNS NULL ON NULL INPUT +EXTERNAL SECURITY INVOKER; diff -r 98223e5c95e0 -r 5e6bcb2e010e pgsql/update_types_and_functions_0.5.x-0.6.pgsql --- a/pgsql/update_types_and_functions_0.5.x-0.6.pgsql Wed Feb 02 21:09:50 2011 +0000 +++ b/pgsql/update_types_and_functions_0.5.x-0.6.pgsql Fri Feb 04 17:29:35 2011 +0000 @@ -121,3 +121,51 @@ $$ LANGUAGE plpgsql STABLE RETURNS NULL ON NULL INPUT EXTERNAL SECURITY INVOKER; +-- --- +-- Data type for function dovecotquotauser(varchar, varchar) +-- --- +CREATE TYPE dovecotquotauser AS ( + userid varchar(320), + uid bigint, + gid bigint, + home text, + mail text, + quota_rule text +); +-- --- +-- Nearly the same as function dovecotuser above. It returns additionally the +-- field quota_rule. +-- +-- Required access privileges for your dovecot database user: +-- GRANT SELECT +-- ON users, domain_data, domain_name, maillocation, mailboxformat, +-- quotalimit +-- TO dovecot; +-- --- +CREATE OR REPLACE FUNCTION dovecotquotauser( + IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotquotauser +AS $$ + DECLARE + record dovecotquotauser; + userid varchar(320) := localpart || '@' || the_domain; + did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); + BEGIN + FOR record IN + SELECT userid, uid, did, domaindir || '/' || uid AS home, + format || ':~/' || directory AS mail, '*:bytes=' || + bytes || ':messages=' || messages AS quota_rule + FROM users, domain_data, mailboxformat, maillocation, quotalimit + WHERE users.gid = did + AND users.local_part = localpart + AND maillocation.mid = users.mid + AND mailboxformat.fid = maillocation.fid + AND domain_data.gid = did + AND quotalimit.qid = users.qid + LOOP + RETURN NEXT record; + END LOOP; + RETURN; + END; +$$ LANGUAGE plpgsql STABLE +RETURNS NULL ON NULL INPUT +EXTERNAL SECURITY INVOKER;