34 TRUE FALSE FALSE FALSE |
34 TRUE FALSE FALSE FALSE |
35 FALSE FALSE FALSE FALSE |
35 FALSE FALSE FALSE FALSE |
36 \. |
36 \. |
37 |
37 |
38 -- --- |
38 -- --- |
39 -- Make room for different mailbox formats and longer password hashes. |
39 -- Drop the obsolete VIEWs, we've functions now. |
40 -- --- |
40 -- --- |
41 DROP VIEW dovecot_user; |
41 DROP VIEW dovecot_user; |
42 DROP VIEW dovecot_password; |
42 DROP VIEW dovecot_password; |
|
43 DROP VIEW postfix_alias; |
43 DROP VIEW postfix_maildir; |
44 DROP VIEW postfix_maildir; |
|
45 DROP VIEW postfix_relocated; |
|
46 DROP VIEW postfix_transport; |
|
47 DROP VIEW postfix_uid; |
|
48 -- the vmm_domain_info view will be restored later |
44 DROP VIEW vmm_domain_info; |
49 DROP VIEW vmm_domain_info; |
45 |
50 |
46 CREATE SEQUENCE mailboxformat_id; |
51 CREATE SEQUENCE mailboxformat_id; |
47 CREATE SEQUENCE quotalimit_id; |
52 CREATE SEQUENCE quotalimit_id; |
48 |
53 |
136 ALTER TABLE users DROP COLUMN managesieve; |
141 ALTER TABLE users DROP COLUMN managesieve; |
137 ALTER TABLE users ADD CONSTRAINT fkey_users_ssid_service_set |
142 ALTER TABLE users ADD CONSTRAINT fkey_users_ssid_service_set |
138 FOREIGN KEY (ssid) REFERENCES service_set (ssid); |
143 FOREIGN KEY (ssid) REFERENCES service_set (ssid); |
139 |
144 |
140 -- --- |
145 -- --- |
141 -- Restore views |
146 -- Restore view |
142 -- --- |
147 -- --- |
143 CREATE VIEW dovecot_user AS |
|
144 SELECT local_part || '@' || domain_name.domainname AS userid, |
|
145 uid, gid, domain_data.domaindir || '/' || uid AS home, |
|
146 mailboxformat.format || ':~/' || maillocation.directory AS mail |
|
147 FROM users |
|
148 LEFT JOIN domain_data USING (gid) |
|
149 LEFT JOIN domain_name USING (gid) |
|
150 LEFT JOIN maillocation USING (mid) |
|
151 LEFT JOIN mailboxformat USING (fid); |
|
152 |
|
153 CREATE OR REPLACE VIEW dovecot_password AS |
|
154 SELECT local_part || '@' || domainname AS "user", |
|
155 passwd AS "password", smtp, pop3, imap, managesieve |
|
156 FROM users |
|
157 LEFT JOIN domain_name USING (gid) |
|
158 LEFT JOIN service_set USING (ssid); |
|
159 |
|
160 CREATE VIEW postfix_maildir AS |
|
161 SELECT local_part || '@' || domain_name.domainname AS address, |
|
162 domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/' |
|
163 AS maildir |
|
164 FROM users |
|
165 LEFT JOIN domain_data USING (gid) |
|
166 LEFT JOIN domain_name USING (gid) |
|
167 LEFT JOIN maillocation USING (mid); |
|
168 |
|
169 CREATE VIEW vmm_domain_info AS |
148 CREATE VIEW vmm_domain_info AS |
170 SELECT gid, count(uid) AS accounts, |
149 SELECT gid, count(uid) AS accounts, |
171 (SELECT count(DISTINCT address) |
150 (SELECT count(DISTINCT address) |
172 FROM alias |
151 FROM alias |
173 WHERE alias.gid = domain_data.gid) AS aliases, |
152 WHERE alias.gid = domain_data.gid) AS aliases, |