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 NULL DEFAULT NULL; |
94 ALTER TABLE users ADD COLUMN qid bigint NOT NULL DEFAULT 1; |
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 NULL DEFAULT NULL; |
150 ALTER TABLE users ADD COLUMN ssid bigint NOT NULL DEFAULT 1; |
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); |
|
196 |
178 |
197 -- --- |
179 -- --- |
198 -- Restore view |
180 -- Restore view |
199 -- --- |
181 -- --- |
200 CREATE VIEW vmm_domain_info AS |
182 CREATE VIEW vmm_domain_info AS |
315 record dovecotpassword; |
297 record dovecotpassword; |
316 userid varchar(320) := localpart || '@' || the_domain; |
298 userid varchar(320) := localpart || '@' || the_domain; |
317 BEGIN |
299 BEGIN |
318 FOR record IN |
300 FOR record IN |
319 SELECT userid, passwd, smtp, pop3, imap, sieve |
301 SELECT userid, passwd, smtp, pop3, imap, sieve |
320 FROM users, service_set, domain_data |
302 FROM users, service_set |
321 WHERE users.gid = (SELECT gid |
303 WHERE gid = (SELECT gid |
322 FROM domain_name |
304 FROM domain_name |
323 WHERE domainname = the_domain) |
305 WHERE domainname = the_domain) |
324 AND local_part = localpart |
306 AND local_part = localpart |
325 AND users.gid = domain_data.gid |
307 AND service_set.ssid = users.ssid |
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 |
|
333 LOOP |
308 LOOP |
334 RETURN NEXT record; |
309 RETURN NEXT record; |
335 END LOOP; |
310 END LOOP; |
336 RETURN; |
311 RETURN; |
337 END; |
312 END; |
358 WHERE users.gid = did |
333 WHERE users.gid = did |
359 AND users.local_part = localpart |
334 AND users.local_part = localpart |
360 AND maillocation.mid = users.mid |
335 AND maillocation.mid = users.mid |
361 AND mailboxformat.fid = maillocation.fid |
336 AND mailboxformat.fid = maillocation.fid |
362 AND domain_data.gid = did |
337 AND domain_data.gid = did |
363 AND CASE WHEN |
338 AND quotalimit.qid = users.qid |
364 users.qid IS NOT NULL |
|
365 THEN |
|
366 quotalimit.qid = users.qid |
|
367 ELSE |
|
368 quotalimit.qid = domain_data.qid |
|
369 END |
|
370 LOOP |
339 LOOP |
371 RETURN NEXT record; |
340 RETURN NEXT record; |
372 END LOOP; |
341 END LOOP; |
373 RETURN; |
342 RETURN; |
374 END; |
343 END; |
486 RETURNS SETOF recipient_transport |
455 RETURNS SETOF recipient_transport |
487 AS $$ |
456 AS $$ |
488 DECLARE |
457 DECLARE |
489 record recipient_transport; |
458 record recipient_transport; |
490 recipient varchar(320) := localpart || '@' || the_domain; |
459 recipient varchar(320) := localpart || '@' || the_domain; |
491 did bigint := (SELECT gid FROM domain_name WHERE domainname = the_domain); |
460 BEGIN |
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 |
|
501 FOR record IN |
461 FOR record IN |
502 SELECT recipient, transport |
462 SELECT recipient, transport |
503 FROM transport |
463 FROM transport |
504 WHERE tid = transport_id |
464 WHERE tid = (SELECT tid |
|
465 FROM users |
|
466 WHERE gid = (SELECT gid |
|
467 FROM domain_name |
|
468 WHERE domainname = the_domain) |
|
469 AND local_part = localpart) |
505 LOOP |
470 LOOP |
506 RETURN NEXT record; |
471 RETURN NEXT record; |
507 END LOOP; |
472 END LOOP; |
508 RETURN; |
473 RETURN; |
509 END; |
474 END; |