18 -- --- |
18 -- --- |
19 -- Make room for different mailbox formats. |
19 -- Make room for different mailbox formats. |
20 -- --- |
20 -- --- |
21 DROP VIEW dovecot_user; |
21 DROP VIEW dovecot_user; |
22 DROP VIEW postfix_maildir; |
22 DROP VIEW postfix_maildir; |
|
23 DROP VIEW vmm_domain_info; |
23 |
24 |
24 CREATE SEQUENCE mailboxformat_id; |
25 CREATE SEQUENCE mailboxformat_id; |
25 CREATE SEQUENCE quotalimit_id; |
26 CREATE SEQUENCE quotalimit_id; |
26 |
27 |
27 CREATE TABLE mailboxformat ( |
28 CREATE TABLE mailboxformat ( |
134 FROM users |
135 FROM users |
135 LEFT JOIN domain_data USING (gid) |
136 LEFT JOIN domain_data USING (gid) |
136 LEFT JOIN domain_name USING (gid) |
137 LEFT JOIN domain_name USING (gid) |
137 LEFT JOIN maillocation USING (mid); |
138 LEFT JOIN maillocation USING (mid); |
138 |
139 |
139 CREATE OR REPLACE VIEW vmm_domain_info AS |
140 CREATE VIEW vmm_domain_info AS |
140 SELECT gid, domainname, transport, domaindir, |
141 SELECT gid, count(uid) AS accounts, |
141 count(uid) AS accounts, |
|
142 (SELECT count(DISTINCT address) |
142 (SELECT count(DISTINCT address) |
143 FROM alias |
143 FROM alias |
144 WHERE alias.gid = domain_data.gid) AS aliases, |
144 WHERE alias.gid = domain_data.gid) AS aliases, |
145 (SELECT count(gid) |
145 (SELECT count(gid) |
146 FROM relocated |
146 FROM relocated |
147 WHERE relocated.gid = domain_data.gid) AS relocated, |
147 WHERE relocated.gid = domain_data.gid) AS relocated, |
148 (SELECT count(gid) |
148 (SELECT count(gid) |
149 FROM domain_name |
149 FROM domain_name |
150 WHERE domain_name.gid = domain_data.gid |
150 WHERE domain_name.gid = domain_data.gid |
151 AND NOT domain_name.is_primary) AS aliasdomains, |
151 AND NOT domain_name.is_primary) AS aliasdomains |
152 bytes, messages |
|
153 FROM domain_data |
152 FROM domain_data |
154 LEFT JOIN domain_name USING (gid) |
153 LEFT JOIN domain_name USING (gid) |
155 LEFT JOIN quotalimit USING (qid) |
|
156 LEFT JOIN transport USING (tid) |
|
157 LEFT JOIN users USING (gid) |
154 LEFT JOIN users USING (gid) |
158 WHERE domain_name.is_primary |
155 WHERE domain_name.is_primary |
159 GROUP BY gid, domainname, transport, domaindir, bytes, messages; |
156 GROUP BY gid; |