equal
deleted
inserted
replaced
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_11 ( |
98 CREATE TABLE userquota_11 ( |
99 uid bigint NOT NULL, |
99 uid bigint NOT NULL, |
123 -- Adjust tables (services) |
123 -- Adjust tables (services) |
124 ALTER TABLE domain_data ADD COLUMN ssid bigint NOT NULL DEFAULT 1; |
124 ALTER TABLE domain_data ADD COLUMN ssid bigint NOT NULL DEFAULT 1; |
125 ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_ssid_service_set |
125 ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_ssid_service_set |
126 FOREIGN KEY (ssid) REFERENCES service_set (ssid); |
126 FOREIGN KEY (ssid) REFERENCES service_set (ssid); |
127 |
127 |
128 ALTER TABLE users ADD COLUMN ssid bigint NOT NULL DEFAULT 1; |
128 ALTER TABLE users ADD COLUMN ssid bigint NULL DEFAULT NULL; |
129 -- save current service sets |
129 -- save current service sets |
130 UPDATE users u |
130 UPDATE users u |
131 SET ssid = ss.ssid |
131 SET ssid = ss.ssid |
132 FROM service_set ss |
132 FROM service_set ss |
133 WHERE ss.smtp = u.smtp |
133 WHERE ss.smtp = u.smtp |
142 ALTER TABLE users ADD CONSTRAINT fkey_users_ssid_service_set |
142 ALTER TABLE users ADD CONSTRAINT fkey_users_ssid_service_set |
143 FOREIGN KEY (ssid) REFERENCES service_set (ssid); |
143 FOREIGN KEY (ssid) REFERENCES service_set (ssid); |
144 |
144 |
145 -- --- |
145 -- --- |
146 -- Catchall |
146 -- Catchall |
|
147 -- --- |
|
148 -- Quota/Service/Transport inheritance |
|
149 -- --- |
|
150 ALTER TABLE users ALTER COLUMN tid DROP NULL; |
|
151 ALTER TABLE users ALTER COLUMN tid SET DEFAULT NULL; |
|
152 -- The qid and ssid columns have already been defined accordingly above. |
|
153 -- The rest of the logic will take place in the functions. |
|
154 |
|
155 -- While qid and ssid are new and it's perfectly okay for existing users to |
|
156 -- get NULL values (i.e. inherit from the domain's default), tid existed in |
|
157 -- vmm 0.5.x. A sensible way forward seems thus to NULL all user records' tid |
|
158 -- fields where the tid duplicates the value stored in the domain's record. |
|
159 UPDATE users |
|
160 SET tid = NULL |
|
161 WHERE tid = (SELECT tid |
|
162 FROM domain_data |
|
163 WHERE domain_data.gid = users.gid); |
|
164 |
147 -- --- |
165 -- --- |
148 |
166 |
149 CREATE TABLE catchall ( |
167 CREATE TABLE catchall ( |
150 gid bigint NOT NULL, |
168 gid bigint NOT NULL, |
151 destination varchar(320) NOT NULL, |
169 destination varchar(320) NOT NULL, |
275 record dovecotpassword; |
293 record dovecotpassword; |
276 userid varchar(320) := localpart || '@' || the_domain; |
294 userid varchar(320) := localpart || '@' || the_domain; |
277 BEGIN |
295 BEGIN |
278 FOR record IN |
296 FOR record IN |
279 SELECT userid, passwd, smtp, pop3, imap, managesieve |
297 SELECT userid, passwd, smtp, pop3, imap, managesieve |
280 FROM users, service_set |
298 FROM users, service_set, domain_data |
281 WHERE gid = (SELECT gid |
299 WHERE users.gid = (SELECT gid |
282 FROM domain_name |
300 FROM domain_name |
283 WHERE domainname = the_domain) |
301 WHERE domainname = the_domain) |
284 AND local_part = localpart |
302 AND local_part = localpart |
285 AND service_set.ssid = users.ssid |
303 AND service_set.ssid = users.ssid |
|
304 AND users.gid = domain_data.gid |
|
305 AND CASE WHEN |
|
306 users.ssid IS NOT NULL |
|
307 THEN |
|
308 service_set.ssid = users.ssid |
|
309 ELSE |
|
310 service_set.ssid = domain_data.ssid |
|
311 END |
286 LOOP |
312 LOOP |
287 RETURN NEXT record; |
313 RETURN NEXT record; |
288 END LOOP; |
314 END LOOP; |
289 RETURN; |
315 RETURN; |
290 END; |
316 END; |
311 WHERE users.gid = did |
337 WHERE users.gid = did |
312 AND users.local_part = localpart |
338 AND users.local_part = localpart |
313 AND maillocation.mid = users.mid |
339 AND maillocation.mid = users.mid |
314 AND mailboxformat.fid = maillocation.fid |
340 AND mailboxformat.fid = maillocation.fid |
315 AND domain_data.gid = did |
341 AND domain_data.gid = did |
316 AND quotalimit.qid = users.qid |
342 AND CASE WHEN |
|
343 users.qid IS NOT NULL |
|
344 THEN |
|
345 quotalimit.qid = users.qid |
|
346 ELSE |
|
347 quotalimit.qid = domain_data.qid |
|
348 END |
317 LOOP |
349 LOOP |
318 RETURN NEXT record; |
350 RETURN NEXT record; |
319 END LOOP; |
351 END LOOP; |
320 RETURN; |
352 RETURN; |
321 END; |
353 END; |
433 RETURNS SETOF recipient_transport |
465 RETURNS SETOF recipient_transport |
434 AS $$ |
466 AS $$ |
435 DECLARE |
467 DECLARE |
436 record recipient_transport; |
468 record recipient_transport; |
437 recipient varchar(320) := localpart || '@' || the_domain; |
469 recipient varchar(320) := localpart || '@' || the_domain; |
438 BEGIN |
470 did bigint := (SELECT gid FROM domain_name WHERE domainname = the_domain); |
|
471 transport_id bigint := (SELECT tid FROM users |
|
472 WHERE gid = did AND local_part = localpart); |
|
473 BEGIN |
|
474 IF transport_id IS NULL THEN |
|
475 SELECT tid INTO STRICT transport_id |
|
476 FROM domain_data |
|
477 WHERE gid = did; |
|
478 END IF; |
|
479 |
439 FOR record IN |
480 FOR record IN |
440 SELECT recipient, transport |
481 SELECT recipient, transport |
441 FROM transport |
482 FROM transport |
442 WHERE tid = (SELECT tid |
483 WHERE tid = transport_id |
443 FROM users |
|
444 WHERE gid = (SELECT gid |
|
445 FROM domain_name |
|
446 WHERE domainname = the_domain) |
|
447 AND local_part = localpart) |
|
448 LOOP |
484 LOOP |
449 RETURN NEXT record; |
485 RETURN NEXT record; |
450 END LOOP; |
486 END LOOP; |
451 RETURN; |
487 RETURN; |
452 END; |
488 END; |