147 transport.transport |
147 transport.transport |
148 FROM users |
148 FROM users |
149 LEFT JOIN transport USING (tid) |
149 LEFT JOIN transport USING (tid) |
150 LEFT JOIN domain_name USING (gid); |
150 LEFT JOIN domain_name USING (gid); |
151 |
151 |
152 CREATE OR REPLACE VIEW vmm_alias_count AS |
|
153 SELECT count(DISTINCT address) AS aliases, gid |
|
154 FROM alias |
|
155 GROUP BY gid; |
|
156 |
|
157 CREATE OR REPLACE VIEW vmm_domain_info AS |
152 CREATE OR REPLACE VIEW vmm_domain_info AS |
158 SELECT gid, domainname, transport, domaindir, |
153 SELECT gid, domainname, transport, domaindir, |
159 count(uid) AS accounts, |
154 count(uid) AS accounts, |
160 aliases, |
155 (SELECT count(DISTINCT address) |
|
156 FROM alias |
|
157 WHERE alias.gid = domain_data.gid) AS aliases, |
|
158 (SELECT count(gid) |
|
159 FROM relocated |
|
160 WHERE relocated.gid = domain_data.gid) AS relocated, |
161 (SELECT count(gid) |
161 (SELECT count(gid) |
162 FROM domain_name |
162 FROM domain_name |
163 WHERE domain_name.gid = domain_data.gid |
163 WHERE domain_name.gid = domain_data.gid |
164 AND NOT domain_name.is_primary) AS aliasdomains |
164 AND NOT domain_name.is_primary) AS aliasdomains |
165 FROM domain_data |
165 FROM domain_data |
166 LEFT JOIN domain_name USING (gid) |
166 LEFT JOIN domain_name USING (gid) |
167 LEFT JOIN transport USING (tid) |
167 LEFT JOIN transport USING (tid) |
168 LEFT JOIN users USING (gid) |
168 LEFT JOIN users USING (gid) |
169 LEFT JOIN vmm_alias_count USING (gid) |
|
170 WHERE domain_name.is_primary |
169 WHERE domain_name.is_primary |
171 GROUP BY gid, domainname, transport, domaindir, aliases; |
170 GROUP BY gid, domainname, transport, domaindir; |
172 |
171 |
173 |
172 |
174 CREATE LANGUAGE plpgsql; |
173 CREATE LANGUAGE plpgsql; |
175 |
174 |
176 |
175 |