# HG changeset patch # User martin f. krafft # Date 1334332154 -7200 # Node ID 3acbff7276268b1bba238a26571e58c6e86654ce # Parent 3ffe4ee3740fd66e3baf6066b5c68bbfc1567d00 Modify SQL update script to accept NULL fields for users The value NULL in the qid/ssid/tid fields of the user table means that the value from the associated domain record should be used instead. This patch modifies the PL/pgSQL functions used by Dovecot and Postfix accordingly. diff -r 3ffe4ee3740f -r 3acbff727626 pgsql/create_tables-dovecot-1.2.x.pgsql --- a/pgsql/create_tables-dovecot-1.2.x.pgsql Sat Apr 14 11:50:33 2012 +0200 +++ b/pgsql/create_tables-dovecot-1.2.x.pgsql Fri Apr 13 17:49:14 2012 +0200 @@ -129,9 +129,9 @@ uid bigint NOT NULL DEFAULT nextval('users_uid'), gid bigint NOT NULL, mid bigint NOT NULL DEFAULT 1, - qid bigint NOT NULL DEFAULT 1, - ssid bigint NOT NULL DEFAULT 1, - tid bigint NOT NULL DEFAULT 1, + qid bigint NULL DEFAULT NULL, + ssid bigint NULL DEFAULT NULL, + tid bigint NULL DEFAULT NULL, CONSTRAINT pkey_users PRIMARY KEY (local_part, gid), CONSTRAINT ukey_users_uid UNIQUE (uid), CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid) @@ -369,12 +369,19 @@ BEGIN FOR record IN SELECT userid, passwd, smtp, pop3, imap, sieve - FROM users, service_set - WHERE gid = (SELECT gid - FROM domain_name - WHERE domainname = the_domain) + FROM users, service_set, domain_data + WHERE users.gid = (SELECT gid + FROM domain_name + WHERE domainname = the_domain) AND local_part = localpart - AND service_set.ssid = users.ssid + AND users.gid = domain_data.gid + AND CASE WHEN + users.ssid IS NOT NULL + THEN + service_set.ssid = users.ssid + ELSE + service_set.ssid = domain_data.ssid + END LOOP RETURN NEXT record; END LOOP; @@ -411,7 +418,13 @@ AND maillocation.mid = users.mid AND mailboxformat.fid = maillocation.fid AND domain_data.gid = did - AND quotalimit.qid = users.qid + AND CASE WHEN + users.qid IS NOT NULL + THEN + quotalimit.qid = users.qid + ELSE + quotalimit.qid = domain_data.qid + END LOOP RETURN NEXT record; END LOOP; @@ -555,16 +568,20 @@ DECLARE record recipient_transport; recipient varchar(320) := localpart || '@' || the_domain; + did bigint := (SELECT gid FROM domain_name WHERE domainname = the_domain); + transport_id bigint := (SELECT tid FROM users + WHERE gid = did AND local_part = localpart); BEGIN + IF transport_id IS NULL THEN + SELECT tid INTO STRICT transport_id + FROM domain_data + WHERE gid = did; + END IF; + FOR record IN SELECT recipient, transport FROM transport - WHERE tid = (SELECT tid - FROM users - WHERE gid = (SELECT gid - FROM domain_name - WHERE domainname = the_domain) - AND local_part = localpart) + WHERE tid = transport_id LOOP RETURN NEXT record; END LOOP; diff -r 3ffe4ee3740f -r 3acbff727626 pgsql/create_tables.pgsql --- a/pgsql/create_tables.pgsql Sat Apr 14 11:50:33 2012 +0200 +++ b/pgsql/create_tables.pgsql Fri Apr 13 17:49:14 2012 +0200 @@ -129,9 +129,9 @@ uid bigint NOT NULL DEFAULT nextval('users_uid'), gid bigint NOT NULL, mid bigint NOT NULL DEFAULT 1, - qid bigint NOT NULL DEFAULT 1, - ssid bigint NOT NULL DEFAULT 1, - tid bigint NOT NULL DEFAULT 1, + qid bigint NULL DEFAULT NULL, + ssid bigint NULL DEFAULT NULL, + tid bigint NULL DEFAULT NULL, CONSTRAINT pkey_users PRIMARY KEY (local_part, gid), CONSTRAINT ukey_users_uid UNIQUE (uid), CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid) @@ -346,12 +346,20 @@ BEGIN FOR record IN SELECT userid, passwd, smtp, pop3, imap, managesieve - FROM users, service_set - WHERE gid = (SELECT gid - FROM domain_name - WHERE domainname = the_domain) + FROM users, service_set, domain_data + WHERE users.gid = (SELECT gid + FROM domain_name + WHERE domainname = the_domain) AND local_part = localpart AND service_set.ssid = users.ssid + AND users.gid = domain_data.gid + AND CASE WHEN + users.ssid IS NOT NULL + THEN + service_set.ssid = users.ssid + ELSE + service_set.ssid = domain_data.ssid + END LOOP RETURN NEXT record; END LOOP; @@ -388,7 +396,13 @@ AND maillocation.mid = users.mid AND mailboxformat.fid = maillocation.fid AND domain_data.gid = did - AND quotalimit.qid = users.qid + AND CASE WHEN + users.qid IS NOT NULL + THEN + quotalimit.qid = users.qid + ELSE + quotalimit.qid = domain_data.qid + END LOOP RETURN NEXT record; END LOOP; @@ -532,16 +546,20 @@ DECLARE record recipient_transport; recipient varchar(320) := localpart || '@' || the_domain; + did bigint := (SELECT gid FROM domain_name WHERE domainname = the_domain); + transport_id bigint := (SELECT tid FROM users + WHERE gid = did AND local_part = localpart); BEGIN + IF transport_id IS NULL THEN + SELECT tid INTO STRICT transport_id + FROM domain_data + WHERE gid = did; + END IF; + FOR record IN SELECT recipient, transport FROM transport - WHERE tid = (SELECT tid - FROM users - WHERE gid = (SELECT gid - FROM domain_name - WHERE domainname = the_domain) - AND local_part = localpart) + WHERE tid = transport_id LOOP RETURN NEXT record; END LOOP; diff -r 3ffe4ee3740f -r 3acbff727626 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 Sat Apr 14 11:50:33 2012 +0200 +++ b/pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql Fri Apr 13 17:49:14 2012 +0200 @@ -91,7 +91,7 @@ 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 COLUMN qid bigint NULL DEFAULT NULL; ALTER TABLE users ADD CONSTRAINT fkey_users_qid_quotalimit FOREIGN KEY (qid) REFERENCES quotalimit (qid); @@ -147,7 +147,7 @@ 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; +ALTER TABLE users ADD COLUMN ssid bigint NULL DEFAULT NULL; -- save current service sets UPDATE users u SET ssid = ss.ssid @@ -177,6 +177,24 @@ ); -- --- +-- Quota/Service/Transport inheritance +-- --- +ALTER TABLE users ALTER COLUMN tid DROP NULL; +ALTER TABLE users ALTER COLUMN tid SET DEFAULT NULL; +-- The qid and ssid columns have already been defined accordingly above. +-- The rest of the logic will take place in the functions. + +-- While qid and ssid are new and it's perfectly okay for existing users to +-- get NULL values (i.e. inherit from the domain's default), tid existed in +-- vmm 0.5.x. A sensible way forward seems thus to NULL all user records' tid +-- fields where the tid duplicates the value stored in the domain's record. +UPDATE users + SET tid = NULL + WHERE tid = (SELECT tid + FROM domain_data + WHERE domain_data.gid = users.gid); + +-- --- -- Restore view -- --- CREATE VIEW vmm_domain_info AS @@ -299,12 +317,19 @@ BEGIN FOR record IN SELECT userid, passwd, smtp, pop3, imap, sieve - FROM users, service_set - WHERE gid = (SELECT gid - FROM domain_name - WHERE domainname = the_domain) + FROM users, service_set, domain_data + WHERE users.gid = (SELECT gid + FROM domain_name + WHERE domainname = the_domain) AND local_part = localpart - AND service_set.ssid = users.ssid + AND users.gid = domain_data.gid + AND CASE WHEN + users.ssid IS NOT NULL + THEN + service_set.ssid = users.ssid + ELSE + service_set.ssid = domain_data.ssid + END LOOP RETURN NEXT record; END LOOP; @@ -335,7 +360,13 @@ AND maillocation.mid = users.mid AND mailboxformat.fid = maillocation.fid AND domain_data.gid = did - AND quotalimit.qid = users.qid + AND CASE WHEN + users.qid IS NOT NULL + THEN + quotalimit.qid = users.qid + ELSE + quotalimit.qid = domain_data.qid + END LOOP RETURN NEXT record; END LOOP; @@ -457,16 +488,20 @@ DECLARE record recipient_transport; recipient varchar(320) := localpart || '@' || the_domain; + did bigint := (SELECT gid FROM domain_name WHERE domainname = the_domain); + transport_id bigint := (SELECT tid FROM users + WHERE gid = did AND local_part = localpart); BEGIN + IF transport_id IS NULL THEN + SELECT tid INTO STRICT transport_id + FROM domain_data + WHERE gid = did; + END IF; + FOR record IN SELECT recipient, transport FROM transport - WHERE tid = (SELECT tid - FROM users - WHERE gid = (SELECT gid - FROM domain_name - WHERE domainname = the_domain) - AND local_part = localpart) + WHERE tid = transport_id LOOP RETURN NEXT record; END LOOP; diff -r 3ffe4ee3740f -r 3acbff727626 pgsql/update_tables_0.5.x-0.6.pgsql --- a/pgsql/update_tables_0.5.x-0.6.pgsql Sat Apr 14 11:50:33 2012 +0200 +++ b/pgsql/update_tables_0.5.x-0.6.pgsql Fri Apr 13 17:49:14 2012 +0200 @@ -91,7 +91,7 @@ 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 COLUMN qid bigint NULL DEFAULT NULL; ALTER TABLE users ADD CONSTRAINT fkey_users_qid_quotalimit FOREIGN KEY (qid) REFERENCES quotalimit (qid); @@ -125,7 +125,7 @@ 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; +ALTER TABLE users ADD COLUMN ssid bigint NULL DEFAULT NULL; -- save current service sets UPDATE users u SET ssid = ss.ssid @@ -145,6 +145,24 @@ -- --- -- Catchall -- --- +-- Quota/Service/Transport inheritance +-- --- +ALTER TABLE users ALTER COLUMN tid DROP NULL; +ALTER TABLE users ALTER COLUMN tid SET DEFAULT NULL; +-- The qid and ssid columns have already been defined accordingly above. +-- The rest of the logic will take place in the functions. + +-- While qid and ssid are new and it's perfectly okay for existing users to +-- get NULL values (i.e. inherit from the domain's default), tid existed in +-- vmm 0.5.x. A sensible way forward seems thus to NULL all user records' tid +-- fields where the tid duplicates the value stored in the domain's record. +UPDATE users + SET tid = NULL + WHERE tid = (SELECT tid + FROM domain_data + WHERE domain_data.gid = users.gid); + +-- --- CREATE TABLE catchall ( gid bigint NOT NULL, @@ -277,12 +295,20 @@ BEGIN FOR record IN SELECT userid, passwd, smtp, pop3, imap, managesieve - FROM users, service_set - WHERE gid = (SELECT gid - FROM domain_name - WHERE domainname = the_domain) + FROM users, service_set, domain_data + WHERE users.gid = (SELECT gid + FROM domain_name + WHERE domainname = the_domain) AND local_part = localpart AND service_set.ssid = users.ssid + AND users.gid = domain_data.gid + AND CASE WHEN + users.ssid IS NOT NULL + THEN + service_set.ssid = users.ssid + ELSE + service_set.ssid = domain_data.ssid + END LOOP RETURN NEXT record; END LOOP; @@ -313,7 +339,13 @@ AND maillocation.mid = users.mid AND mailboxformat.fid = maillocation.fid AND domain_data.gid = did - AND quotalimit.qid = users.qid + AND CASE WHEN + users.qid IS NOT NULL + THEN + quotalimit.qid = users.qid + ELSE + quotalimit.qid = domain_data.qid + END LOOP RETURN NEXT record; END LOOP; @@ -435,16 +467,20 @@ DECLARE record recipient_transport; recipient varchar(320) := localpart || '@' || the_domain; + did bigint := (SELECT gid FROM domain_name WHERE domainname = the_domain); + transport_id bigint := (SELECT tid FROM users + WHERE gid = did AND local_part = localpart); BEGIN + IF transport_id IS NULL THEN + SELECT tid INTO STRICT transport_id + FROM domain_data + WHERE gid = did; + END IF; + FOR record IN SELECT recipient, transport FROM transport - WHERE tid = (SELECT tid - FROM users - WHERE gid = (SELECT gid - FROM domain_name - WHERE domainname = the_domain) - AND local_part = localpart) + WHERE tid = transport_id LOOP RETURN NEXT record; END LOOP;