20 -- --- |
20 -- --- |
21 DROP VIEW dovecot_user; |
21 DROP VIEW dovecot_user; |
22 DROP VIEW postfix_maildir; |
22 DROP VIEW postfix_maildir; |
23 |
23 |
24 CREATE SEQUENCE mailboxformat_id; |
24 CREATE SEQUENCE mailboxformat_id; |
|
25 CREATE SEQUENCE quotalimit_id; |
25 |
26 |
26 CREATE TABLE mailboxformat ( |
27 CREATE TABLE mailboxformat ( |
27 fid bigint NOT NULL DEFAULT nextval('mailboxformat_id'), |
28 fid bigint NOT NULL DEFAULT nextval('mailboxformat_id'), |
28 format varchar(20) NOT NULL, |
29 format varchar(20) NOT NULL, |
29 CONSTRAINT pkey_mailboxformat PRIMARY KEY (fid), |
30 CONSTRAINT pkey_mailboxformat PRIMARY KEY (fid), |
42 ADD COLUMN extra varchar(1024); |
43 ADD COLUMN extra varchar(1024); |
43 ALTER TABLE maillocation ADD CONSTRAINT fkey_maillocation_fid_mailboxformat |
44 ALTER TABLE maillocation ADD CONSTRAINT fkey_maillocation_fid_mailboxformat |
44 FOREIGN KEY (fid) REFERENCES mailboxformat (fid); |
45 FOREIGN KEY (fid) REFERENCES mailboxformat (fid); |
45 |
46 |
46 |
47 |
|
48 -- --- |
|
49 -- Add quota stuff |
|
50 -- --- |
|
51 CREATE TABLE quotalimit ( |
|
52 qid bigint NOT NULL DEFAULT nextval('quotalimit_id'), |
|
53 bytes bigint NOT NULL, |
|
54 messages integer NOT NULL DEFAULT 0, |
|
55 CONSTRAINT pkey_quotalimit PRIMARY KEY (qid), |
|
56 CONSTRAINT ukey_quotalimit UNIQUE (bytes, messages) |
|
57 ); |
|
58 -- Insert default (non) quota limit |
|
59 INSERT INTO quotalimit(bytes, messages) VALUES (0, 0); |
|
60 |
|
61 -- Adjust tables … |
|
62 ALTER TABLE domain_data ADD COLUMN qid bigint NOT NULL DEFAULT 1; |
|
63 ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_qid_quotalimit |
|
64 FOREIGN KEY (qid) REFERENCES quotalimit (qid); |
|
65 |
|
66 ALTER TABLE users ADD COLUMN qid bigint NOT NULL DEFAULT 1; |
|
67 ALTER TABLE users ADD CONSTRAINT fkey_users_qid_quotalimit |
|
68 FOREIGN KEY (qid) REFERENCES quotalimit (qid); |
|
69 |
|
70 CREATE TABLE userquota_11 ( |
|
71 uid bigint NOT NULL, |
|
72 path varchar(16) NOT NULL, |
|
73 current bigint NOT NULL DEFAULT 0, |
|
74 CONSTRAINT pkey_userquota_11 PRIMARY KEY (uid, path), |
|
75 CONSTRAINT fkey_userquota_11_uid_users FOREIGN KEY (uid) |
|
76 REFERENCES users (uid) |
|
77 ); |
|
78 |
|
79 CREATE OR REPLACE FUNCTION merge_userquota_11() RETURNS TRIGGER AS $$ |
|
80 BEGIN |
|
81 UPDATE userquota_11 |
|
82 SET current = current + NEW.current |
|
83 WHERE uid = NEW.uid AND path = NEW.path; |
|
84 IF found THEN |
|
85 RETURN NULL; |
|
86 ELSE |
|
87 RETURN NEW; |
|
88 END IF; |
|
89 END; |
|
90 $$ LANGUAGE plpgsql; |
|
91 |
|
92 CREATE TRIGGER mergeuserquota_11 BEFORE INSERT ON userquota_11 |
|
93 FOR EACH ROW EXECUTE PROCEDURE merge_userquota_11(); |
|
94 |
|
95 -- --- |
|
96 -- Restore views |
|
97 -- --- |
47 CREATE VIEW dovecot_user AS |
98 CREATE VIEW dovecot_user AS |
48 SELECT local_part || '@' || domain_name.domainname AS userid, |
99 SELECT local_part || '@' || domain_name.domainname AS userid, |
49 uid, gid, domain_data.domaindir || '/' || uid AS home, |
100 uid, gid, domain_data.domaindir || '/' || uid AS home, |
50 mailboxformat.format || ':~/' || maillocation.directory AS mail |
101 mailboxformat.format || ':~/' || maillocation.directory AS mail |
51 FROM users |
102 FROM users |