pgsql/create_tables.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)
   344         record dovecotpassword;
   344         record dovecotpassword;
   345         userid varchar(320) := localpart || '@' || the_domain;
   345         userid varchar(320) := localpart || '@' || the_domain;
   346     BEGIN
   346     BEGIN
   347         FOR record IN
   347         FOR record IN
   348             SELECT userid, passwd, smtp, pop3, imap, managesieve
   348             SELECT userid, passwd, smtp, pop3, imap, managesieve
   349               FROM users, service_set
   349               FROM users, service_set, domain_data
   350              WHERE gid = (SELECT gid
   350              WHERE users.gid = (SELECT gid
   351                             FROM domain_name
   351                                   FROM domain_name
   352                            WHERE domainname = the_domain)
   352                                  WHERE domainname = the_domain)
   353                AND local_part = localpart
   353                AND local_part = localpart
   354                AND service_set.ssid = users.ssid
   354                AND service_set.ssid = users.ssid
       
   355                AND users.gid = domain_data.gid
       
   356                AND CASE WHEN
       
   357                   users.ssid IS NOT NULL
       
   358                   THEN
       
   359                     service_set.ssid = users.ssid
       
   360                   ELSE
       
   361                     service_set.ssid = domain_data.ssid
       
   362                   END
   355             LOOP
   363             LOOP
   356                 RETURN NEXT record;
   364                 RETURN NEXT record;
   357             END LOOP;
   365             END LOOP;
   358         RETURN;
   366         RETURN;
   359     END;
   367     END;
   386              WHERE users.gid = did
   394              WHERE users.gid = did
   387                AND users.local_part = localpart
   395                AND users.local_part = localpart
   388                AND maillocation.mid = users.mid
   396                AND maillocation.mid = users.mid
   389                AND mailboxformat.fid = maillocation.fid
   397                AND mailboxformat.fid = maillocation.fid
   390                AND domain_data.gid = did
   398                AND domain_data.gid = did
   391                AND quotalimit.qid = users.qid
   399                AND CASE WHEN
       
   400                      users.qid IS NOT NULL
       
   401                    THEN
       
   402                      quotalimit.qid = users.qid
       
   403                    ELSE
       
   404                      quotalimit.qid = domain_data.qid
       
   405                    END
   392             LOOP
   406             LOOP
   393                 RETURN NEXT record;
   407                 RETURN NEXT record;
   394             END LOOP;
   408             END LOOP;
   395         RETURN;
   409         RETURN;
   396     END;
   410     END;
   530     RETURNS SETOF recipient_transport
   544     RETURNS SETOF recipient_transport
   531 AS $$
   545 AS $$
   532     DECLARE
   546     DECLARE
   533         record recipient_transport;
   547         record recipient_transport;
   534         recipient varchar(320) := localpart || '@' || the_domain;
   548         recipient varchar(320) := localpart || '@' || the_domain;
   535     BEGIN
   549         did bigint := (SELECT gid FROM domain_name WHERE domainname = the_domain);
       
   550         transport_id bigint := (SELECT tid FROM users
       
   551                                   WHERE gid = did AND local_part = localpart);
       
   552     BEGIN
       
   553         IF transport_id IS NULL THEN
       
   554             SELECT tid INTO STRICT transport_id
       
   555               FROM domain_data
       
   556              WHERE gid = did;
       
   557         END IF;
       
   558 
   536         FOR record IN
   559         FOR record IN
   537             SELECT recipient, transport
   560             SELECT recipient, transport
   538               FROM transport
   561               FROM transport
   539              WHERE tid = (SELECT tid
   562              WHERE tid = transport_id
   540                             FROM users
       
   541                            WHERE gid = (SELECT gid
       
   542                                           FROM domain_name
       
   543                                          WHERE domainname = the_domain)
       
   544                              AND local_part = localpart)
       
   545             LOOP
   563             LOOP
   546                 RETURN NEXT record;
   564                 RETURN NEXT record;
   547             END LOOP;
   565             END LOOP;
   548         RETURN;
   566         RETURN;
   549     END;
   567     END;