53 REFERENCES mailboxformat (fid) |
55 REFERENCES mailboxformat (fid) |
54 ); |
56 ); |
55 -- Insert default Maildir-folder name |
57 -- Insert default Maildir-folder name |
56 INSERT INTO maillocation(directory) VALUES ('Maildir'); |
58 INSERT INTO maillocation(directory) VALUES ('Maildir'); |
57 |
59 |
|
60 CREATE TABLE quotalimit ( |
|
61 qid bigint NOT NULL DEFAULT nextval('quotalimit_id'), |
|
62 bytes bigint NOT NULL, |
|
63 messages integer NOT NULL DEFAULT 0, |
|
64 CONSTRAINT pkey_quotalimit PRIMARY KEY (qid), |
|
65 CONSTRAINT ukey_quotalimit UNIQUE (bytes, messages) |
|
66 ); |
|
67 -- Insert default (non) quota limit |
|
68 INSERT INTO quotalimit(bytes, messages) VALUES (0, 0); |
|
69 |
58 CREATE TABLE domain_data ( |
70 CREATE TABLE domain_data ( |
59 gid bigint NOT NULL DEFAULT nextval('domain_gid'), |
71 gid bigint NOT NULL DEFAULT nextval('domain_gid'), |
60 tid bigint NOT NULL DEFAULT 1, -- default transport |
72 tid bigint NOT NULL DEFAULT 1, -- default transport |
|
73 qid bigint NOT NULL DEFAULT 1, -- default quota limit |
61 domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294 |
74 domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294 |
62 CONSTRAINT pkey_domain_data PRIMARY KEY (gid), |
75 CONSTRAINT pkey_domain_data PRIMARY KEY (gid), |
63 CONSTRAINT fkey_domain_data_tid_transport FOREIGN KEY (tid) |
76 CONSTRAINT fkey_domain_data_tid_transport FOREIGN KEY (tid) |
64 REFERENCES transport (tid) |
77 REFERENCES transport (tid), |
|
78 CONSTRAINT fkey_domain_data_qid_quotalimit FOREIGN KEY (qid) |
|
79 REFERENCES quotalimit (qid) |
65 ); |
80 ); |
66 |
81 |
67 CREATE TABLE domain_name ( |
82 CREATE TABLE domain_name ( |
68 domainname varchar(255) NOT NULL, |
83 domainname varchar(255) NOT NULL, |
69 gid bigint NOT NULL, |
84 gid bigint NOT NULL, |
79 name varchar(128) NULL, |
94 name varchar(128) NULL, |
80 uid bigint NOT NULL DEFAULT nextval('users_uid'), |
95 uid bigint NOT NULL DEFAULT nextval('users_uid'), |
81 gid bigint NOT NULL, |
96 gid bigint NOT NULL, |
82 mid bigint NOT NULL DEFAULT 1, |
97 mid bigint NOT NULL DEFAULT 1, |
83 tid bigint NOT NULL DEFAULT 1, |
98 tid bigint NOT NULL DEFAULT 1, |
|
99 qid bigint NOT NULL DEFAULT 1, |
84 smtp boolean NOT NULL DEFAULT TRUE, |
100 smtp boolean NOT NULL DEFAULT TRUE, |
85 pop3 boolean NOT NULL DEFAULT TRUE, |
101 pop3 boolean NOT NULL DEFAULT TRUE, |
86 imap boolean NOT NULL DEFAULT TRUE, |
102 imap boolean NOT NULL DEFAULT TRUE, |
87 sieve boolean NOT NULL DEFAULT TRUE, |
103 sieve boolean NOT NULL DEFAULT TRUE, |
88 CONSTRAINT pkey_users PRIMARY KEY (local_part, gid), |
104 CONSTRAINT pkey_users PRIMARY KEY (local_part, gid), |
90 CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid) |
106 CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid) |
91 REFERENCES domain_data (gid), |
107 REFERENCES domain_data (gid), |
92 CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid) |
108 CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid) |
93 REFERENCES maillocation (mid), |
109 REFERENCES maillocation (mid), |
94 CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid) |
110 CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid) |
95 REFERENCES transport (tid) |
111 REFERENCES transport (tid), |
|
112 CONSTRAINT fkey_users_qid_quotalimit FOREIGN KEY (qid) |
|
113 REFERENCES quotalimit (qid) |
|
114 ); |
|
115 |
|
116 CREATE TABLE userquota ( |
|
117 uid bigint NOT NULL, |
|
118 bytes bigint NOT NULL DEFAULT 0, |
|
119 messages integer NOT NULL DEFAULT 0, |
|
120 CONSTRAINT pkey_userquota PRIMARY KEY (uid), |
|
121 CONSTRAINT fkey_userquota_uid_users FOREIGN KEY (uid) |
|
122 REFERENCES users (uid) |
96 ); |
123 ); |
97 |
124 |
98 CREATE TABLE alias ( |
125 CREATE TABLE alias ( |
99 gid bigint NOT NULL, |
126 gid bigint NOT NULL, |
100 address varchar(64) NOT NULL,-- only localpart w/o '@' |
127 address varchar(64) NOT NULL,-- only localpart w/o '@' |
209 CREATE TRIGGER primary_count_ins BEFORE INSERT ON domain_name |
236 CREATE TRIGGER primary_count_ins BEFORE INSERT ON domain_name |
210 FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger(); |
237 FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger(); |
211 |
238 |
212 CREATE TRIGGER primary_count_upd AFTER UPDATE ON domain_name |
239 CREATE TRIGGER primary_count_upd AFTER UPDATE ON domain_name |
213 FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger(); |
240 FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger(); |
|
241 |
|
242 |
|
243 CREATE OR REPLACE FUNCTION merge_userquota() RETURNS TRIGGER AS $$ |
|
244 BEGIN |
|
245 IF NEW.messages < 0 OR NEW.messages IS NULL THEN |
|
246 IF NEW.messages IS NULL THEN |
|
247 NEW.messages = 0; |
|
248 ELSE |
|
249 NEW.messages = -NEW.messages; |
|
250 END IF; |
|
251 RETURN NEW; |
|
252 END IF; |
|
253 LOOP |
|
254 UPDATE userquota |
|
255 SET bytes = bytes + NEW.bytes, messages = messages + NEW.messages |
|
256 WHERE uid = NEW.uid; |
|
257 IF found THEN |
|
258 RETURN NULL; |
|
259 END IF; |
|
260 BEGIN |
|
261 IF NEW.messages = 0 THEN |
|
262 INSERT INTO userquota VALUES (NEW.uid, NEW.bytes, NULL); |
|
263 ELSE |
|
264 INSERT INTO userquota VALUES (NEW.uid, NEW.bytes, -NEW.messages); |
|
265 END IF; |
|
266 RETURN NULL; |
|
267 EXCEPTION |
|
268 WHEN unique_violation THEN |
|
269 -- do nothing, and loop to try the UPDATE again |
|
270 WHEN foreign_key_violation THEN |
|
271 -- break the loop: a non matching uid means no such user |
|
272 RETURN NULL; |
|
273 END; |
|
274 END LOOP; |
|
275 END; |
|
276 $$ LANGUAGE plpgsql; |
|
277 |
|
278 |
|
279 CREATE TRIGGER mergeuserquota BEFORE INSERT ON userquota |
|
280 FOR EACH ROW EXECUTE PROCEDURE merge_userquota(); |