vmm: comparison pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql

equal deleted inserted replaced

-:916b468cf994
+:95dd123b552e
 -- 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 COLUMN qid bigint NULL DEFAULT NULL;
 ALTER TABLE users ADD CONSTRAINT fkey_users_qid_quotalimit
 FOREIGN KEY (qid) REFERENCES quotalimit (qid);
 CREATE TABLE userquota (
 uid         bigint NOT NULL,
 -- 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;
+ALTER TABLE users ADD COLUMN ssid bigint NULL DEFAULT NULL;
 -- save current service sets
 UPDATE users u
 SET ssid = ss.ssid
 FROM service_set ss
 WHERE ss.smtp = u.smtp
 destination varchar(320) NOT NULL,
 CONSTRAINT  pkey_catchall PRIMARY KEY (gid, destination),
 CONSTRAINT  fkey_catchall_gid_domain_data FOREIGN KEY (gid)
 REFERENCES domain_data (gid)
 );
+-- ---
+-- 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
 record dovecotpassword;
 userid varchar(320) := localpart || '@' || the_domain;
 BEGIN
 FOR record IN
 SELECT userid, passwd, smtp, pop3, imap, sieve
-FROM users, service_set
+FROM users, service_set, domain_data
-WHERE gid = (SELECT gid
+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;
 RETURN;
 END;
 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
+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;
 RETURN;
 END;
 RETURNS SETOF recipient_transport
 AS $$
 DECLARE
 record recipient_transport;
 recipient varchar(320) := localpart || '@' || the_domain;
-BEGIN
+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
+WHERE tid = transport_id
-FROM users
-WHERE gid = (SELECT gid
-FROM domain_name
-WHERE domainname = the_domain)
-AND local_part = localpart)
 LOOP
 RETURN NEXT record;
 END LOOP;
 RETURN;
 END;

branch	v0.6.x
changeset 530	95dd123b552e
parent 528	4b8c3f51d7da
child 538	1f9ea5658627