pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql
branchv0.6.x
changeset 525 3acbff727626
parent 521 75d1c0d6bb8f
child 528 4b8c3f51d7da
equal deleted inserted replaced
524:3ffe4ee3740f 525:3acbff727626
    89 -- Adjust tables (quota)
    89 -- Adjust tables (quota)
    90 ALTER TABLE domain_data ADD COLUMN qid bigint NOT NULL DEFAULT 1;
    90 ALTER TABLE domain_data ADD COLUMN qid bigint NOT NULL DEFAULT 1;
    91 ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_qid_quotalimit
    91 ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_qid_quotalimit
    92     FOREIGN KEY (qid) REFERENCES quotalimit (qid);
    92     FOREIGN KEY (qid) REFERENCES quotalimit (qid);
    93 
    93 
    94 ALTER TABLE users ADD COLUMN qid bigint NOT NULL DEFAULT 1;
    94 ALTER TABLE users ADD COLUMN qid bigint NULL DEFAULT NULL;
    95 ALTER TABLE users ADD CONSTRAINT fkey_users_qid_quotalimit
    95 ALTER TABLE users ADD CONSTRAINT fkey_users_qid_quotalimit
    96     FOREIGN KEY (qid) REFERENCES quotalimit (qid);
    96     FOREIGN KEY (qid) REFERENCES quotalimit (qid);
    97 
    97 
    98 CREATE TABLE userquota (
    98 CREATE TABLE userquota (
    99     uid         bigint NOT NULL,
    99     uid         bigint NOT NULL,
   145 -- Adjust tables (services)
   145 -- Adjust tables (services)
   146 ALTER TABLE domain_data ADD COLUMN ssid bigint NOT NULL DEFAULT 1;
   146 ALTER TABLE domain_data ADD COLUMN ssid bigint NOT NULL DEFAULT 1;
   147 ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_ssid_service_set
   147 ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_ssid_service_set
   148     FOREIGN KEY (ssid) REFERENCES service_set (ssid);
   148     FOREIGN KEY (ssid) REFERENCES service_set (ssid);
   149 
   149 
   150 ALTER TABLE users ADD COLUMN ssid bigint NOT NULL DEFAULT 1;
   150 ALTER TABLE users ADD COLUMN ssid bigint NULL DEFAULT NULL;
   151 -- save current service sets
   151 -- save current service sets
   152 UPDATE users u
   152 UPDATE users u
   153    SET ssid = ss.ssid
   153    SET ssid = ss.ssid
   154   FROM service_set ss
   154   FROM service_set ss
   155  WHERE ss.smtp = u.smtp
   155  WHERE ss.smtp = u.smtp
   173     destination varchar(320) NOT NULL,
   173     destination varchar(320) NOT NULL,
   174     CONSTRAINT  pkey_catchall PRIMARY KEY (gid, destination),
   174     CONSTRAINT  pkey_catchall PRIMARY KEY (gid, destination),
   175     CONSTRAINT  fkey_catchall_gid_domain_data FOREIGN KEY (gid)
   175     CONSTRAINT  fkey_catchall_gid_domain_data FOREIGN KEY (gid)
   176         REFERENCES domain_data (gid)
   176         REFERENCES domain_data (gid)
   177 );
   177 );
       
   178 
       
   179 -- ---
       
   180 -- Quota/Service/Transport inheritance
       
   181 -- ---
       
   182 ALTER TABLE users ALTER COLUMN tid DROP NULL;
       
   183 ALTER TABLE users ALTER COLUMN tid SET DEFAULT NULL;
       
   184 -- The qid and ssid columns have already been defined accordingly above.
       
   185 -- The rest of the logic will take place in the functions.
       
   186 
       
   187 -- While qid and ssid are new and it's perfectly okay for existing users to
       
   188 -- get NULL values (i.e. inherit from the domain's default), tid existed in
       
   189 -- vmm 0.5.x. A sensible way forward seems thus to NULL all user records' tid
       
   190 -- fields where the tid duplicates the value stored in the domain's record.
       
   191 UPDATE users
       
   192    SET tid = NULL
       
   193  WHERE tid = (SELECT tid
       
   194                 FROM domain_data
       
   195                WHERE domain_data.gid = users.gid);
   178 
   196 
   179 -- ---
   197 -- ---
   180 -- Restore view
   198 -- Restore view
   181 -- ---
   199 -- ---
   182 CREATE VIEW vmm_domain_info AS
   200 CREATE VIEW vmm_domain_info AS
   297         record dovecotpassword;
   315         record dovecotpassword;
   298         userid varchar(320) := localpart || '@' || the_domain;
   316         userid varchar(320) := localpart || '@' || the_domain;
   299     BEGIN
   317     BEGIN
   300         FOR record IN
   318         FOR record IN
   301             SELECT userid, passwd, smtp, pop3, imap, sieve
   319             SELECT userid, passwd, smtp, pop3, imap, sieve
   302               FROM users, service_set
   320               FROM users, service_set, domain_data
   303              WHERE gid = (SELECT gid
   321              WHERE users.gid = (SELECT gid
   304                             FROM domain_name
   322                                   FROM domain_name
   305                            WHERE domainname = the_domain)
   323                                  WHERE domainname = the_domain)
   306                AND local_part = localpart
   324                AND local_part = localpart
   307                AND service_set.ssid = users.ssid
   325                AND users.gid = domain_data.gid
       
   326                AND CASE WHEN
       
   327                      users.ssid IS NOT NULL
       
   328                      THEN
       
   329                        service_set.ssid = users.ssid
       
   330                      ELSE
       
   331                        service_set.ssid = domain_data.ssid
       
   332                      END
   308             LOOP
   333             LOOP
   309                 RETURN NEXT record;
   334                 RETURN NEXT record;
   310             END LOOP;
   335             END LOOP;
   311         RETURN;
   336         RETURN;
   312     END;
   337     END;
   333              WHERE users.gid = did
   358              WHERE users.gid = did
   334                AND users.local_part = localpart
   359                AND users.local_part = localpart
   335                AND maillocation.mid = users.mid
   360                AND maillocation.mid = users.mid
   336                AND mailboxformat.fid = maillocation.fid
   361                AND mailboxformat.fid = maillocation.fid
   337                AND domain_data.gid = did
   362                AND domain_data.gid = did
   338                AND quotalimit.qid = users.qid
   363                AND CASE WHEN
       
   364                      users.qid IS NOT NULL
       
   365                    THEN
       
   366                      quotalimit.qid = users.qid
       
   367                    ELSE
       
   368                      quotalimit.qid = domain_data.qid
       
   369                    END
   339             LOOP
   370             LOOP
   340                 RETURN NEXT record;
   371                 RETURN NEXT record;
   341             END LOOP;
   372             END LOOP;
   342         RETURN;
   373         RETURN;
   343     END;
   374     END;
   455     RETURNS SETOF recipient_transport
   486     RETURNS SETOF recipient_transport
   456 AS $$
   487 AS $$
   457     DECLARE
   488     DECLARE
   458         record recipient_transport;
   489         record recipient_transport;
   459         recipient varchar(320) := localpart || '@' || the_domain;
   490         recipient varchar(320) := localpart || '@' || the_domain;
   460     BEGIN
   491         did bigint := (SELECT gid FROM domain_name WHERE domainname = the_domain);
       
   492         transport_id bigint := (SELECT tid FROM users
       
   493                                   WHERE gid = did AND local_part = localpart);
       
   494     BEGIN
       
   495         IF transport_id IS NULL THEN
       
   496             SELECT tid INTO STRICT transport_id
       
   497               FROM domain_data
       
   498              WHERE gid = did;
       
   499         END IF;
       
   500 
   461         FOR record IN
   501         FOR record IN
   462             SELECT recipient, transport
   502             SELECT recipient, transport
   463               FROM transport
   503               FROM transport
   464              WHERE tid = (SELECT tid
   504              WHERE tid = transport_id
   465                             FROM users
       
   466                            WHERE gid = (SELECT gid
       
   467                                           FROM domain_name
       
   468                                          WHERE domainname = the_domain)
       
   469                              AND local_part = localpart)
       
   470             LOOP
   505             LOOP
   471                 RETURN NEXT record;
   506                 RETURN NEXT record;
   472             END LOOP;
   507             END LOOP;
   473         RETURN;
   508         RETURN;
   474     END;
   509     END;