--- a/pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql	Fri Apr 13 23:24:12 2012 +0200
+++ b/pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql	Sat Apr 14 15:11:27 2012 +0000
@@ -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 NULL DEFAULT NULL;
+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);
 
@@ -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 NULL DEFAULT NULL;
+ALTER TABLE users ADD COLUMN ssid bigint NOT NULL DEFAULT 1;
 -- save current service sets
 UPDATE users u
    SET ssid = ss.ssid
@@ -177,24 +177,6 @@
 );
 
 -- ---
--- 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
@@ -317,19 +299,12 @@
     BEGIN
         FOR record IN
             SELECT userid, passwd, smtp, pop3, imap, sieve
-              FROM users, service_set, domain_data
-             WHERE users.gid = (SELECT gid
-                                  FROM domain_name
-                                 WHERE domainname = the_domain)
+              FROM users, service_set
+             WHERE gid = (SELECT gid
+                            FROM domain_name
+                           WHERE domainname = the_domain)
                AND local_part = localpart
-               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
+               AND service_set.ssid = users.ssid
             LOOP
                 RETURN NEXT record;
             END LOOP;
@@ -360,13 +335,7 @@
                AND maillocation.mid = users.mid
                AND mailboxformat.fid = maillocation.fid
                AND domain_data.gid = did
-               AND CASE WHEN
-                     users.qid IS NOT NULL
-                   THEN
-                     quotalimit.qid = users.qid
-                   ELSE
-                     quotalimit.qid = domain_data.qid
-                   END
+               AND quotalimit.qid = users.qid
             LOOP
                 RETURN NEXT record;
             END LOOP;
@@ -488,20 +457,16 @@
     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 = transport_id
+             WHERE tid = (SELECT tid
+                            FROM users
+                           WHERE gid = (SELECT gid
+                                          FROM domain_name
+                                         WHERE domainname = the_domain)
+                             AND local_part = localpart)
             LOOP
                 RETURN NEXT record;
             END LOOP;