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 ( |
|
71 uid bigint NOT NULL, |
|
72 bytes bigint NOT NULL DEFAULT 0, |
|
73 messages integer NOT NULL DEFAULT 0, |
|
74 CONSTRAINT pkey_userquota PRIMARY KEY (uid), |
|
75 CONSTRAINT fkey_userquota_uid_users FOREIGN KEY (uid) |
|
76 REFERENCES users (uid) |
|
77 ); |
|
78 |
|
79 CREATE OR REPLACE FUNCTION merge_userquota() RETURNS TRIGGER AS $$ |
|
80 BEGIN |
|
81 IF NEW.messages < 0 OR NEW.messages IS NULL THEN |
|
82 IF NEW.messages IS NULL THEN |
|
83 NEW.messages = 0; |
|
84 ELSE |
|
85 NEW.messages = -NEW.messages; |
|
86 END IF; |
|
87 RETURN NEW; |
|
88 END IF; |
|
89 LOOP |
|
90 UPDATE userquota |
|
91 SET bytes = bytes + NEW.bytes, messages = messages + NEW.messages |
|
92 WHERE uid = NEW.uid; |
|
93 IF found THEN |
|
94 RETURN NULL; |
|
95 END IF; |
|
96 BEGIN |
|
97 IF NEW.messages = 0 THEN |
|
98 INSERT INTO userquota VALUES (NEW.uid, NEW.bytes, NULL); |
|
99 ELSE |
|
100 INSERT INTO userquota VALUES (NEW.uid, NEW.bytes, -NEW.messages); |
|
101 END IF; |
|
102 RETURN NULL; |
|
103 EXCEPTION |
|
104 WHEN unique_violation THEN |
|
105 -- do nothing, and loop to try the UPDATE again |
|
106 WHEN foreign_key_violation THEN |
|
107 -- break the loop: a non matching uid means no such user |
|
108 RETURN NULL; |
|
109 END; |
|
110 END LOOP; |
|
111 END; |
|
112 $$ LANGUAGE plpgsql; |
|
113 |
|
114 CREATE TRIGGER mergeuserquota BEFORE INSERT ON userquota |
|
115 FOR EACH ROW EXECUTE PROCEDURE merge_userquota(); |
|
116 |
|
117 -- --- |
|
118 -- Restore views |
|
119 -- --- |
47 CREATE VIEW dovecot_user AS |
120 CREATE VIEW dovecot_user AS |
48 SELECT local_part || '@' || domain_name.domainname AS userid, |
121 SELECT local_part || '@' || domain_name.domainname AS userid, |
49 uid, gid, domain_data.domaindir || '/' || uid AS home, |
122 uid, gid, domain_data.domaindir || '/' || uid AS home, |
50 mailboxformat.format || ':~/' || maillocation.directory AS mail |
123 mailboxformat.format || ':~/' || maillocation.directory AS mail |
51 FROM users |
124 FROM users |