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 managesieve boolean NOT NULL DEFAULT TRUE, |
103 managesieve 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_11 ( |
|
117 uid bigint NOT NULL, |
|
118 path varchar(16) NOT NULL, |
|
119 current bigint NOT NULL DEFAULT 0, |
|
120 CONSTRAINT pkey_userquota_11 PRIMARY KEY (uid, path), |
|
121 CONSTRAINT fkey_userquota_11_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_11() RETURNS TRIGGER AS $$ |
|
244 BEGIN |
|
245 UPDATE userquota_11 |
|
246 SET current = current + NEW.current |
|
247 WHERE uid = NEW.uid AND path = NEW.path; |
|
248 IF found THEN |
|
249 RETURN NULL; |
|
250 ELSE |
|
251 RETURN NEW; |
|
252 END IF; |
|
253 END; |
|
254 $$ LANGUAGE plpgsql; |
|
255 |
|
256 CREATE TRIGGER mergeuserquota_11 BEFORE INSERT ON userquota_11 |
|
257 FOR EACH ROW EXECUTE PROCEDURE merge_userquota_11(); |