pgsql/create_tables-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
   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 NOT NULL DEFAULT 1,
   132     qid         bigint NULL DEFAULT NULL,
   133     ssid        bigint NOT NULL DEFAULT 1,
   133     ssid        bigint NULL DEFAULT NULL,
   134     tid         bigint NOT NULL DEFAULT 1,
   134     tid         bigint NULL DEFAULT NULL,
   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
   372               FROM users, service_set, domain_data
   373              WHERE gid = (SELECT gid
   373              WHERE users.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 service_set.ssid = users.ssid
   377                AND users.gid = domain_data.gid
       
   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
   378             LOOP
   385             LOOP
   379                 RETURN NEXT record;
   386                 RETURN NEXT record;
   380             END LOOP;
   387             END LOOP;
   381         RETURN;
   388         RETURN;
   382     END;
   389     END;
   409              WHERE users.gid = did
   416              WHERE users.gid = did
   410                AND users.local_part = localpart
   417                AND users.local_part = localpart
   411                AND maillocation.mid = users.mid
   418                AND maillocation.mid = users.mid
   412                AND mailboxformat.fid = maillocation.fid
   419                AND mailboxformat.fid = maillocation.fid
   413                AND domain_data.gid = did
   420                AND domain_data.gid = did
   414                AND quotalimit.qid = users.qid
   421                AND CASE WHEN
       
   422                      users.qid IS NOT NULL
       
   423                    THEN
       
   424                      quotalimit.qid = users.qid
       
   425                    ELSE
       
   426                      quotalimit.qid = domain_data.qid
       
   427                    END
   415             LOOP
   428             LOOP
   416                 RETURN NEXT record;
   429                 RETURN NEXT record;
   417             END LOOP;
   430             END LOOP;
   418         RETURN;
   431         RETURN;
   419     END;
   432     END;
   553     RETURNS SETOF recipient_transport
   566     RETURNS SETOF recipient_transport
   554 AS $$
   567 AS $$
   555     DECLARE
   568     DECLARE
   556         record recipient_transport;
   569         record recipient_transport;
   557         recipient varchar(320) := localpart || '@' || the_domain;
   570         recipient varchar(320) := localpart || '@' || the_domain;
   558     BEGIN
   571         did bigint := (SELECT gid FROM domain_name WHERE domainname = the_domain);
       
   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 
   559         FOR record IN
   581         FOR record IN
   560             SELECT recipient, transport
   582             SELECT recipient, transport
   561               FROM transport
   583               FROM transport
   562              WHERE tid = (SELECT tid
   584              WHERE tid = transport_id
   563                             FROM users
       
   564                            WHERE gid = (SELECT gid
       
   565                                           FROM domain_name
       
   566                                          WHERE domainname = the_domain)
       
   567                              AND local_part = localpart)
       
   568             LOOP
   585             LOOP
   569                 RETURN NEXT record;
   586                 RETURN NEXT record;
   570             END LOOP;
   587             END LOOP;
   571         RETURN;
   588         RETURN;
   572     END;
   589     END;