pgsql/create_tables.pgsql
branchv0.6.x
changeset 500 5ccc9c6e5193
parent 437 9823548b2717
child 502 e1b32377032f
equal deleted inserted replaced
499:23d04d1f29e8 500:5ccc9c6e5193
   171     CONSTRAINT  pkey_relocated PRIMARY KEY (gid, address),
   171     CONSTRAINT  pkey_relocated PRIMARY KEY (gid, address),
   172     CONSTRAINT  fkey_relocated_gid_domain_data FOREIGN KEY (gid)
   172     CONSTRAINT  fkey_relocated_gid_domain_data FOREIGN KEY (gid)
   173         REFERENCES domain_data (gid)
   173         REFERENCES domain_data (gid)
   174 );
   174 );
   175 
   175 
   176 CREATE OR REPLACE VIEW dovecot_password AS
       
   177     SELECT local_part || '@' || domain_name.domainname AS "user",
       
   178            passwd AS "password", smtp, pop3, imap, managesieve
       
   179       FROM users
       
   180            LEFT JOIN domain_name USING (gid)
       
   181            LEFT JOIN service_set USING (ssid);
       
   182 
       
   183 CREATE OR REPLACE VIEW dovecot_user AS
       
   184     SELECT local_part || '@' || domain_name.domainname AS userid,
       
   185            uid, gid, domain_data.domaindir || '/' || uid AS home,
       
   186            mailboxformat.format || ':~/' || maillocation.directory AS mail
       
   187       FROM users
       
   188            LEFT JOIN domain_data USING (gid)
       
   189            LEFT JOIN domain_name USING (gid)
       
   190            LEFT JOIN maillocation USING (mid)
       
   191            LEFT JOIN mailboxformat USING (fid);
       
   192 
       
   193 CREATE OR REPLACE VIEW postfix_gid AS
   176 CREATE OR REPLACE VIEW postfix_gid AS
   194     SELECT gid, domainname
   177     SELECT gid, domainname
   195       FROM domain_name;
   178       FROM domain_name;
   196 
       
   197 CREATE OR REPLACE VIEW postfix_uid AS
       
   198     SELECT local_part || '@' || domain_name.domainname AS address, uid
       
   199       FROM users
       
   200            LEFT JOIN domain_name USING (gid);
       
   201 
       
   202 CREATE OR REPLACE VIEW postfix_maildir AS
       
   203     SELECT local_part || '@' || domain_name.domainname AS address,
       
   204            domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/'
       
   205            AS maildir
       
   206       FROM users
       
   207            LEFT JOIN domain_data USING (gid)
       
   208            LEFT JOIN domain_name USING (gid)
       
   209            LEFT JOIN maillocation USING (mid);
       
   210 
       
   211 CREATE OR REPLACE VIEW postfix_relocated AS
       
   212     SELECT address || '@' || domain_name.domainname AS address, destination
       
   213       FROM relocated
       
   214            LEFT JOIN domain_name USING (gid);
       
   215 
       
   216 CREATE OR REPLACE VIEW postfix_alias AS
       
   217     SELECT address || '@' || domain_name.domainname AS address, destination, gid
       
   218       FROM alias
       
   219            LEFT JOIN domain_name USING (gid);
       
   220 
       
   221 CREATE OR REPLACE VIEW postfix_transport AS
       
   222     SELECT local_part || '@' || domain_name.domainname AS address,
       
   223            transport.transport
       
   224       FROM users
       
   225            LEFT JOIN transport USING (tid)
       
   226            LEFT JOIN domain_name USING (gid);
       
   227 
   179 
   228 CREATE OR REPLACE VIEW vmm_domain_info AS
   180 CREATE OR REPLACE VIEW vmm_domain_info AS
   229     SELECT gid, count(uid) AS accounts,
   181     SELECT gid, count(uid) AS accounts,
   230            (SELECT count(DISTINCT address)
   182            (SELECT count(DISTINCT address)
   231               FROM alias
   183               FROM alias