Modify SQL update script to accept NULL fields for users v0.6.x
authormartin f. krafft <madduck@madduck.net>
Fri, 13 Apr 2012 17:49:14 +0200
branchv0.6.x
changeset 525 3acbff727626
parent 524 3ffe4ee3740f
child 526 85517c8fde36
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.
pgsql/create_tables-dovecot-1.2.x.pgsql
pgsql/create_tables.pgsql
pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql
pgsql/update_tables_0.5.x-0.6.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;
--- 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;
--- 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;
--- 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;