105 transport.transport |
105 transport.transport |
106 FROM users |
106 FROM users |
107 LEFT JOIN transport USING (tid) |
107 LEFT JOIN transport USING (tid) |
108 LEFT JOIN domain_name USING (gid); |
108 LEFT JOIN domain_name USING (gid); |
109 |
109 |
110 CREATE OR REPLACE VIEW vmm_alias_count AS |
|
111 SELECT count(DISTINCT address) AS aliases, gid |
|
112 FROM alias |
|
113 GROUP BY gid; |
|
114 |
|
115 CREATE OR REPLACE VIEW vmm_domain_info AS |
110 CREATE OR REPLACE VIEW vmm_domain_info AS |
116 SELECT gid, domainname, transport, domaindir, |
111 SELECT gid, domainname, transport, domaindir, |
117 count(uid) AS accounts, |
112 count(uid) AS accounts, |
118 aliases, |
113 (SELECT count(DISTINCT address) |
|
114 FROM alias |
|
115 WHERE alias.gid = domain_data.gid) AS aliases, |
|
116 (SELECT count(gid) |
|
117 FROM relocated |
|
118 WHERE relocated.gid = domain_data.gid) AS relocated, |
119 (SELECT count(gid) |
119 (SELECT count(gid) |
120 FROM domain_name |
120 FROM domain_name |
121 WHERE domain_name.gid = domain_data.gid |
121 WHERE domain_name.gid = domain_data.gid |
122 AND NOT domain_name.is_primary) AS aliasdomains |
122 AND NOT domain_name.is_primary) AS aliasdomains |
123 FROM domain_data |
123 FROM domain_data |
124 LEFT JOIN domain_name USING (gid) |
124 LEFT JOIN domain_name USING (gid) |
125 LEFT JOIN transport USING (tid) |
125 LEFT JOIN transport USING (tid) |
126 LEFT JOIN users USING (gid) |
126 LEFT JOIN users USING (gid) |
127 LEFT JOIN vmm_alias_count USING (gid) |
|
128 WHERE domain_name.is_primary |
127 WHERE domain_name.is_primary |
129 GROUP BY gid, domainname, transport, domaindir, aliases; |
128 GROUP BY gid, domainname, transport, domaindir; |
130 |
129 |
131 |
130 |
132 DROP TABLE domains; |
131 DROP TABLE domains; |
133 |
132 |
134 |
133 |