pgsql/create_tables-dovecot-1.2.x.pgsql
branchv0.6.x
changeset 525 3acbff727626
parent 521 75d1c0d6bb8f
child 528 4b8c3f51d7da
--- 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;