1 SET client_encoding = 'UTF8'; |
1 SET client_encoding = 'UTF8'; |
2 SET client_min_messages = warning; |
2 SET client_min_messages = warning; |
3 |
3 |
|
4 -- --- |
|
5 -- Create the new service_set table and insert all possible combinations |
|
6 -- -- |
|
7 CREATE SEQUENCE service_set_id; |
|
8 |
|
9 CREATE TABLE service_set ( |
|
10 ssid bigint NOT NULL DEFAULT nextval('service_set_id'), |
|
11 smtp boolean NOT NULL DEFAULT TRUE, |
|
12 pop3 boolean NOT NULL DEFAULT TRUE, |
|
13 imap boolean NOT NULL DEFAULT TRUE, |
|
14 managesieve boolean NOT NULL DEFAULT TRUE, |
|
15 CONSTRAINT pkey_service_set PRIMARY KEY (ssid), |
|
16 CONSTRAINT ukey_service_set UNIQUE (smtp, pop3, imap, managesieve) |
|
17 ); |
|
18 |
|
19 COPY service_set (smtp, pop3, imap, managesieve) FROM stdin; |
|
20 TRUE TRUE TRUE TRUE |
|
21 FALSE TRUE TRUE TRUE |
|
22 TRUE FALSE TRUE TRUE |
|
23 FALSE FALSE TRUE TRUE |
|
24 TRUE TRUE FALSE TRUE |
|
25 FALSE TRUE FALSE TRUE |
|
26 TRUE FALSE FALSE TRUE |
|
27 FALSE FALSE FALSE TRUE |
|
28 TRUE TRUE TRUE FALSE |
|
29 FALSE TRUE TRUE FALSE |
|
30 TRUE FALSE TRUE FALSE |
|
31 FALSE FALSE TRUE FALSE |
|
32 TRUE TRUE FALSE FALSE |
|
33 FALSE TRUE FALSE FALSE |
|
34 TRUE FALSE FALSE FALSE |
|
35 FALSE FALSE FALSE FALSE |
|
36 \. |
4 |
37 |
5 -- --- |
38 -- --- |
6 -- Make room for sha512-crypt.hex hashed passwords |
39 -- Make room for different mailbox formats and longer password hashes. |
7 -- --- |
|
8 DROP VIEW dovecot_password; |
|
9 |
|
10 ALTER TABLE users ALTER COLUMN passwd TYPE varchar(270); |
|
11 |
|
12 CREATE VIEW dovecot_password AS |
|
13 SELECT local_part || '@' || domain_name.domainname AS "user", |
|
14 passwd AS "password", smtp, pop3, imap, managesieve |
|
15 FROM users |
|
16 LEFT JOIN domain_name USING (gid); |
|
17 |
|
18 -- --- |
|
19 -- Make room for different mailbox formats. |
|
20 -- --- |
40 -- --- |
21 DROP VIEW dovecot_user; |
41 DROP VIEW dovecot_user; |
|
42 DROP VIEW dovecot_password; |
22 DROP VIEW postfix_maildir; |
43 DROP VIEW postfix_maildir; |
23 DROP VIEW vmm_domain_info; |
44 DROP VIEW vmm_domain_info; |
24 |
45 |
25 CREATE SEQUENCE mailboxformat_id; |
46 CREATE SEQUENCE mailboxformat_id; |
26 CREATE SEQUENCE quotalimit_id; |
47 CREATE SEQUENCE quotalimit_id; |
43 ADD COLUMN fid bigint NOT NULL DEFAULT 1, |
64 ADD COLUMN fid bigint NOT NULL DEFAULT 1, |
44 ADD COLUMN extra varchar(1024); |
65 ADD COLUMN extra varchar(1024); |
45 ALTER TABLE maillocation ADD CONSTRAINT fkey_maillocation_fid_mailboxformat |
66 ALTER TABLE maillocation ADD CONSTRAINT fkey_maillocation_fid_mailboxformat |
46 FOREIGN KEY (fid) REFERENCES mailboxformat (fid); |
67 FOREIGN KEY (fid) REFERENCES mailboxformat (fid); |
47 |
68 |
|
69 ALTER TABLE users ALTER COLUMN passwd TYPE varchar(270); |
48 |
70 |
49 -- --- |
71 -- --- |
50 -- Add quota stuff |
72 -- Add quota stuff |
51 -- --- |
73 -- --- |
52 CREATE TABLE quotalimit ( |
74 CREATE TABLE quotalimit ( |
57 CONSTRAINT ukey_quotalimit UNIQUE (bytes, messages) |
79 CONSTRAINT ukey_quotalimit UNIQUE (bytes, messages) |
58 ); |
80 ); |
59 -- Insert default (non) quota limit |
81 -- Insert default (non) quota limit |
60 INSERT INTO quotalimit(bytes, messages) VALUES (0, 0); |
82 INSERT INTO quotalimit(bytes, messages) VALUES (0, 0); |
61 |
83 |
62 -- Adjust tables … |
84 -- Adjust tables (quota) |
63 ALTER TABLE domain_data ADD COLUMN qid bigint NOT NULL DEFAULT 1; |
85 ALTER TABLE domain_data ADD COLUMN qid bigint NOT NULL DEFAULT 1; |
64 ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_qid_quotalimit |
86 ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_qid_quotalimit |
65 FOREIGN KEY (qid) REFERENCES quotalimit (qid); |
87 FOREIGN KEY (qid) REFERENCES quotalimit (qid); |
66 |
88 |
67 ALTER TABLE users ADD COLUMN qid bigint NOT NULL DEFAULT 1; |
89 ALTER TABLE users ADD COLUMN qid bigint NOT NULL DEFAULT 1; |
91 $$ LANGUAGE plpgsql; |
113 $$ LANGUAGE plpgsql; |
92 |
114 |
93 CREATE TRIGGER mergeuserquota_11 BEFORE INSERT ON userquota_11 |
115 CREATE TRIGGER mergeuserquota_11 BEFORE INSERT ON userquota_11 |
94 FOR EACH ROW EXECUTE PROCEDURE merge_userquota_11(); |
116 FOR EACH ROW EXECUTE PROCEDURE merge_userquota_11(); |
95 |
117 |
|
118 -- Adjust tables (services) |
|
119 ALTER TABLE domain_data ADD COLUMN ssid bigint NOT NULL DEFAULT 1; |
|
120 ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_ssid_service_set |
|
121 FOREIGN KEY (ssid) REFERENCES service_set (ssid); |
|
122 |
|
123 ALTER TABLE users ADD COLUMN ssid bigint NOT NULL DEFAULT 1; |
|
124 -- save current service sets |
|
125 UPDATE users u |
|
126 SET ssid = ss.ssid |
|
127 FROM service_set ss |
|
128 WHERE ss.smtp = u.smtp |
|
129 AND ss.pop3 = u.pop3 |
|
130 AND ss.imap = u.imap |
|
131 AND ss.managesieve = u.managesieve; |
|
132 |
|
133 ALTER TABLE users DROP COLUMN smtp; |
|
134 ALTER TABLE users DROP COLUMN pop3; |
|
135 ALTER TABLE users DROP COLUMN imap; |
|
136 ALTER TABLE users DROP COLUMN managesieve; |
|
137 ALTER TABLE users ADD CONSTRAINT fkey_users_ssid_service_set |
|
138 FOREIGN KEY (ssid) REFERENCES service_set (ssid); |
|
139 |
96 -- --- |
140 -- --- |
97 -- Restore views |
141 -- Restore views |
98 -- --- |
142 -- --- |
99 CREATE VIEW dovecot_user AS |
143 CREATE VIEW dovecot_user AS |
100 SELECT local_part || '@' || domain_name.domainname AS userid, |
144 SELECT local_part || '@' || domain_name.domainname AS userid, |
103 FROM users |
147 FROM users |
104 LEFT JOIN domain_data USING (gid) |
148 LEFT JOIN domain_data USING (gid) |
105 LEFT JOIN domain_name USING (gid) |
149 LEFT JOIN domain_name USING (gid) |
106 LEFT JOIN maillocation USING (mid) |
150 LEFT JOIN maillocation USING (mid) |
107 LEFT JOIN mailboxformat USING (fid); |
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); |
108 |
159 |
109 CREATE VIEW postfix_maildir AS |
160 CREATE VIEW postfix_maildir AS |
110 SELECT local_part || '@' || domain_name.domainname AS address, |
161 SELECT local_part || '@' || domain_name.domainname AS address, |
111 domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/' |
162 domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/' |
112 AS maildir |
163 AS maildir |