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 ( |
112 FROM users |
113 FROM users |
113 LEFT JOIN domain_data USING (gid) |
114 LEFT JOIN domain_data USING (gid) |
114 LEFT JOIN domain_name USING (gid) |
115 LEFT JOIN domain_name USING (gid) |
115 LEFT JOIN maillocation USING (mid); |
116 LEFT JOIN maillocation USING (mid); |
116 |
117 |
117 CREATE OR REPLACE VIEW vmm_domain_info AS |
118 CREATE VIEW vmm_domain_info AS |
118 SELECT gid, domainname, transport, domaindir, |
119 SELECT gid, count(uid) AS accounts, |
119 count(uid) AS accounts, |
|
120 (SELECT count(DISTINCT address) |
120 (SELECT count(DISTINCT address) |
121 FROM alias |
121 FROM alias |
122 WHERE alias.gid = domain_data.gid) AS aliases, |
122 WHERE alias.gid = domain_data.gid) AS aliases, |
123 (SELECT count(gid) |
123 (SELECT count(gid) |
124 FROM relocated |
124 FROM relocated |
125 WHERE relocated.gid = domain_data.gid) AS relocated, |
125 WHERE relocated.gid = domain_data.gid) AS relocated, |
126 (SELECT count(gid) |
126 (SELECT count(gid) |
127 FROM domain_name |
127 FROM domain_name |
128 WHERE domain_name.gid = domain_data.gid |
128 WHERE domain_name.gid = domain_data.gid |
129 AND NOT domain_name.is_primary) AS aliasdomains, |
129 AND NOT domain_name.is_primary) AS aliasdomains |
130 bytes, messages |
|
131 FROM domain_data |
130 FROM domain_data |
132 LEFT JOIN domain_name USING (gid) |
131 LEFT JOIN domain_name USING (gid) |
133 LEFT JOIN quotalimit USING (qid) |
|
134 LEFT JOIN transport USING (tid) |
|
135 LEFT JOIN users USING (gid) |
132 LEFT JOIN users USING (gid) |
136 WHERE domain_name.is_primary |
133 WHERE domain_name.is_primary |
137 GROUP BY gid, domainname, transport, domaindir, bytes, messages; |
134 GROUP BY gid; |