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 |
158 ALTER TABLE users DROP COLUMN sieve; |
163 ALTER TABLE users DROP COLUMN sieve; |
159 ALTER TABLE users ADD CONSTRAINT fkey_users_ssid_service_set |
164 ALTER TABLE users ADD CONSTRAINT fkey_users_ssid_service_set |
160 FOREIGN KEY (ssid) REFERENCES service_set (ssid); |
165 FOREIGN KEY (ssid) REFERENCES service_set (ssid); |
161 |
166 |
162 -- --- |
167 -- --- |
163 -- Restore views |
168 -- Restore view |
164 -- --- |
169 -- --- |
165 CREATE VIEW dovecot_user AS |
|
166 SELECT local_part || '@' || domain_name.domainname AS userid, |
|
167 uid, gid, domain_data.domaindir || '/' || uid AS home, |
|
168 mailboxformat.format || ':~/' || maillocation.directory AS mail |
|
169 FROM users |
|
170 LEFT JOIN domain_data USING (gid) |
|
171 LEFT JOIN domain_name USING (gid) |
|
172 LEFT JOIN maillocation USING (mid) |
|
173 LEFT JOIN mailboxformat USING (fid); |
|
174 |
|
175 CREATE OR REPLACE VIEW dovecot_password AS |
|
176 SELECT local_part || '@' || domainname AS "user", |
|
177 passwd AS "password", smtp, pop3, imap, sieve |
|
178 FROM users |
|
179 LEFT JOIN domain_name USING (gid) |
|
180 LEFT JOIN service_set USING (ssid); |
|
181 |
|
182 CREATE VIEW postfix_maildir AS |
|
183 SELECT local_part || '@' || domain_name.domainname AS address, |
|
184 domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/' |
|
185 AS maildir |
|
186 FROM users |
|
187 LEFT JOIN domain_data USING (gid) |
|
188 LEFT JOIN domain_name USING (gid) |
|
189 LEFT JOIN maillocation USING (mid); |
|
190 |
|
191 CREATE VIEW vmm_domain_info AS |
170 CREATE VIEW vmm_domain_info AS |
192 SELECT gid, count(uid) AS accounts, |
171 SELECT gid, count(uid) AS accounts, |
193 (SELECT count(DISTINCT address) |
172 (SELECT count(DISTINCT address) |
194 FROM alias |
173 FROM alias |
195 WHERE alias.gid = domain_data.gid) AS aliases, |
174 WHERE alias.gid = domain_data.gid) AS aliases, |