127 passwd varchar(270) NOT NULL, |
127 passwd varchar(270) NOT NULL, |
128 name varchar(128) NULL, |
128 name varchar(128) NULL, |
129 uid bigint NOT NULL DEFAULT nextval('users_uid'), |
129 uid bigint NOT NULL DEFAULT nextval('users_uid'), |
130 gid bigint NOT NULL, |
130 gid bigint NOT NULL, |
131 mid bigint NOT NULL DEFAULT 1, |
131 mid bigint NOT NULL DEFAULT 1, |
132 qid bigint NULL DEFAULT NULL, |
132 qid bigint NOT NULL DEFAULT 1, |
133 ssid bigint NULL DEFAULT NULL, |
133 ssid bigint NOT NULL DEFAULT 1, |
134 tid bigint NULL DEFAULT NULL, |
134 tid bigint NOT NULL DEFAULT 1, |
135 CONSTRAINT pkey_users PRIMARY KEY (local_part, gid), |
135 CONSTRAINT pkey_users PRIMARY KEY (local_part, gid), |
136 CONSTRAINT ukey_users_uid UNIQUE (uid), |
136 CONSTRAINT ukey_users_uid UNIQUE (uid), |
137 CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid) |
137 CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid) |
138 REFERENCES domain_data (gid), |
138 REFERENCES domain_data (gid), |
139 CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid) |
139 CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid) |
367 record dovecotpassword; |
367 record dovecotpassword; |
368 userid varchar(320) := localpart || '@' || the_domain; |
368 userid varchar(320) := localpart || '@' || the_domain; |
369 BEGIN |
369 BEGIN |
370 FOR record IN |
370 FOR record IN |
371 SELECT userid, passwd, smtp, pop3, imap, sieve |
371 SELECT userid, passwd, smtp, pop3, imap, sieve |
372 FROM users, service_set, domain_data |
372 FROM users, service_set |
373 WHERE users.gid = (SELECT gid |
373 WHERE gid = (SELECT gid |
374 FROM domain_name |
374 FROM domain_name |
375 WHERE domainname = the_domain) |
375 WHERE domainname = the_domain) |
376 AND local_part = localpart |
376 AND local_part = localpart |
377 AND users.gid = domain_data.gid |
377 AND service_set.ssid = users.ssid |
378 AND CASE WHEN |
|
379 users.ssid IS NOT NULL |
|
380 THEN |
|
381 service_set.ssid = users.ssid |
|
382 ELSE |
|
383 service_set.ssid = domain_data.ssid |
|
384 END |
|
385 LOOP |
378 LOOP |
386 RETURN NEXT record; |
379 RETURN NEXT record; |
387 END LOOP; |
380 END LOOP; |
388 RETURN; |
381 RETURN; |
389 END; |
382 END; |
416 WHERE users.gid = did |
409 WHERE users.gid = did |
417 AND users.local_part = localpart |
410 AND users.local_part = localpart |
418 AND maillocation.mid = users.mid |
411 AND maillocation.mid = users.mid |
419 AND mailboxformat.fid = maillocation.fid |
412 AND mailboxformat.fid = maillocation.fid |
420 AND domain_data.gid = did |
413 AND domain_data.gid = did |
421 AND CASE WHEN |
414 AND quotalimit.qid = users.qid |
422 users.qid IS NOT NULL |
|
423 THEN |
|
424 quotalimit.qid = users.qid |
|
425 ELSE |
|
426 quotalimit.qid = domain_data.qid |
|
427 END |
|
428 LOOP |
415 LOOP |
429 RETURN NEXT record; |
416 RETURN NEXT record; |
430 END LOOP; |
417 END LOOP; |
431 RETURN; |
418 RETURN; |
432 END; |
419 END; |
566 RETURNS SETOF recipient_transport |
553 RETURNS SETOF recipient_transport |
567 AS $$ |
554 AS $$ |
568 DECLARE |
555 DECLARE |
569 record recipient_transport; |
556 record recipient_transport; |
570 recipient varchar(320) := localpart || '@' || the_domain; |
557 recipient varchar(320) := localpart || '@' || the_domain; |
571 did bigint := (SELECT gid FROM domain_name WHERE domainname = the_domain); |
558 BEGIN |
572 transport_id bigint := (SELECT tid FROM users |
|
573 WHERE gid = did AND local_part = localpart); |
|
574 BEGIN |
|
575 IF transport_id IS NULL THEN |
|
576 SELECT tid INTO STRICT transport_id |
|
577 FROM domain_data |
|
578 WHERE gid = did; |
|
579 END IF; |
|
580 |
|
581 FOR record IN |
559 FOR record IN |
582 SELECT recipient, transport |
560 SELECT recipient, transport |
583 FROM transport |
561 FROM transport |
584 WHERE tid = transport_id |
562 WHERE tid = (SELECT tid |
|
563 FROM users |
|
564 WHERE gid = (SELECT gid |
|
565 FROM domain_name |
|
566 WHERE domainname = the_domain) |
|
567 AND local_part = localpart) |
585 LOOP |
568 LOOP |
586 RETURN NEXT record; |
569 RETURN NEXT record; |
587 END LOOP; |
570 END LOOP; |
588 RETURN; |
571 RETURN; |
589 END; |
572 END; |