32 INCREMENT BY 1 |
20 INCREMENT BY 1 |
33 MINVALUE 70000 |
21 MINVALUE 70000 |
34 MAXVALUE 4294967294 |
22 MAXVALUE 4294967294 |
35 NO CYCLE; |
23 NO CYCLE; |
36 |
24 |
37 CREATE TABLE domains ( |
25 |
38 gid bigint NOT NULL DEFAULT nextval('domains_gid'), |
26 CREATE TABLE transport ( |
|
27 tid bigint NOT NULL DEFAULT nextval('transport_id'), |
|
28 transport varchar(270) NOT NULL, -- smtps:[255-char.host.name:50025] |
|
29 CONSTRAINT pkey_transport PRIMARY KEY (tid), |
|
30 CONSTRAINT ukey_transport UNIQUE (transport) |
|
31 ); |
|
32 -- Insert default transport |
|
33 INSERT INTO transport(transport) VALUES ('dovecot:'); |
|
34 |
|
35 CREATE TABLE maillocation( |
|
36 mid bigint NOT NULL DEFAULT nextval('maillocation_id'), |
|
37 maillocation varchar(20) NOT NULL, |
|
38 CONSTRAINT pkey_maillocation PRIMARY KEY (mid), |
|
39 CONSTRAINT ukey_maillocation UNIQUE (maillocation) |
|
40 ); |
|
41 -- Insert default Maildir-folder name |
|
42 INSERT INTO maillocation(maillocation) VALUES ('Maildir'); |
|
43 |
|
44 CREATE TABLE domain_data ( |
|
45 gid bigint NOT NULL DEFAULT nextval('domain_gid'), |
39 tid bigint NOT NULL DEFAULT 1, -- defualt transport |
46 tid bigint NOT NULL DEFAULT 1, -- defualt transport |
|
47 domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294 |
|
48 CONSTRAINT pkey_domain_data PRIMARY KEY (gid), |
|
49 CONSTRAINT fkey_domain_data_tid_transport FOREIGN KEY (tid) |
|
50 REFERENCES transport (tid) |
|
51 ); |
|
52 |
|
53 CREATE TABLE domain_name ( |
40 domainname varchar(255) NOT NULL, |
54 domainname varchar(255) NOT NULL, |
41 domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294 |
55 gid bigint NOT NULL, |
42 CONSTRAINT pkey_domains PRIMARY KEY (gid), |
56 is_primary boolean NOT NULL, |
43 CONSTRAINT ukey_domains UNIQUE (domainname), |
57 CONSTRAINT pkey_domain_name PRIMARY KEY (domainname), |
44 CONSTRAINT fkey_domains_tid_transport FOREIGN KEY (tid) |
58 CONSTRAINT fkey_domain_name_gid_domain_data FOREIGN KEY (gid) |
45 REFERENCES transport (tid) |
59 REFERENCES domain_data (gid) |
46 ); |
60 ); |
47 |
61 |
48 CREATE TABLE users ( |
62 CREATE TABLE users ( |
49 local_part varchar(64) NOT NULL,-- only localpart w/o '@' |
63 local_part varchar(64) NOT NULL,-- only localpart w/o '@' |
50 passwd varchar(74) NOT NULL,-- {CRAM-MD5}+64hex numbers |
64 passwd varchar(74) NOT NULL,-- {CRAM-MD5}+64hex numbers |
55 tid bigint NOT NULL DEFAULT 1, |
69 tid bigint NOT NULL DEFAULT 1, |
56 smtp boolean NOT NULL DEFAULT TRUE, |
70 smtp boolean NOT NULL DEFAULT TRUE, |
57 pop3 boolean NOT NULL DEFAULT TRUE, |
71 pop3 boolean NOT NULL DEFAULT TRUE, |
58 imap boolean NOT NULL DEFAULT TRUE, |
72 imap boolean NOT NULL DEFAULT TRUE, |
59 managesieve boolean NOT NULL DEFAULT TRUE, |
73 managesieve boolean NOT NULL DEFAULT TRUE, |
60 CONSTRAINT pkye_users PRIMARY KEY (local_part, gid), |
74 CONSTRAINT pkye_users PRIMARY KEY (local_part, gid), |
61 CONSTRAINT ukey_users_uid UNIQUE (uid), |
75 CONSTRAINT ukey_users_uid UNIQUE (uid), |
62 CONSTRAINT fkey_users_gid_domains FOREIGN KEY (gid) |
76 CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid) |
63 REFERENCES domains (gid), |
77 REFERENCES domain_data (gid), |
64 CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid) |
78 CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid) |
65 REFERENCES maillocation (mid), |
79 REFERENCES maillocation (mid), |
66 CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid) |
80 CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid) |
67 REFERENCES transport (tid) |
81 REFERENCES transport (tid) |
68 ); |
82 ); |
69 |
83 |
70 CREATE TABLE alias ( |
84 CREATE TABLE alias ( |
71 gid bigint NOT NULL, |
85 gid bigint NOT NULL, |
72 address varchar(256) NOT NULL, |
86 address varchar(256) NOT NULL, |
73 destination varchar(320) NOT NULL, |
87 destination varchar(320) NOT NULL, |
74 CONSTRAINT pkey_alias PRIMARY KEY (gid, address, destination), |
88 CONSTRAINT pkey_alias PRIMARY KEY (gid, address, destination), |
75 CONSTRAINT fkey_alias_gid_domains FOREIGN KEY (gid) |
89 CONSTRAINT fkey_alias_gid_domain_data FOREIGN KEY (gid) |
76 REFERENCES domains (gid) |
90 REFERENCES domain_data (gid) |
77 ); |
91 ); |
78 |
92 |
79 CREATE TABLE relocated ( |
93 CREATE TABLE relocated ( |
80 gid bigint NOT NULL, |
94 gid bigint NOT NULL, |
81 address varchar(64) NOT NULL, |
95 address varchar(64) NOT NULL, |
82 destination varchar(320) NOT NULL, |
96 destination varchar(320) NOT NULL, |
83 CONSTRAINT pkey_relocated PRIMARY KEY (gid, address), |
97 CONSTRAINT pkey_relocated PRIMARY KEY (gid, address), |
84 CONSTRAINT fkey_relocated_gid_domains FOREIGN KEY (gid) |
98 CONSTRAINT fkey_relocated_gid_domain_data FOREIGN KEY (gid) |
85 REFERENCES domains (gid) |
99 REFERENCES domain_data (gid) |
86 ); |
100 ); |
87 |
101 |
88 CREATE OR REPLACE VIEW dovecot_password AS |
102 CREATE OR REPLACE VIEW dovecot_password AS |
89 SELECT local_part || '@' || domains.domainname AS "user", |
103 SELECT local_part || '@' || domain_name.domainname AS "user", |
90 passwd AS "password", smtp, pop3, imap, managesieve |
104 passwd AS "password", smtp, pop3, imap, managesieve |
91 FROM users |
105 FROM users |
92 LEFT JOIN domains USING (gid); |
106 LEFT JOIN domain_name USING (gid); |
93 |
107 |
94 CREATE OR REPLACE VIEW dovecot_user AS |
108 CREATE OR REPLACE VIEW dovecot_user AS |
95 SELECT local_part || '@' || domains.domainname AS userid, |
109 SELECT local_part || '@' || domain_name.domainname AS userid, |
96 uid, gid, domains.domaindir || '/' || uid AS home, |
110 uid, gid, domain_data.domaindir || '/' || uid AS home, |
97 '~/' || maillocation.maillocation AS mail |
111 '~/' || maillocation.maillocation AS mail |
98 FROM users |
112 FROM users |
99 LEFT JOIN domains USING (gid) |
113 LEFT JOIN domain_data USING (gid) |
|
114 LEFT JOIN domain_name USING (gid) |
100 LEFT JOIN maillocation USING (mid); |
115 LEFT JOIN maillocation USING (mid); |
101 |
116 |
102 CREATE OR REPLACE VIEW postfix_gid AS |
117 CREATE OR REPLACE VIEW postfix_gid AS |
103 SELECT gid, domainname |
118 SELECT gid, domainname |
104 FROM domains; |
119 FROM domain_name; |
105 |
120 |
106 CREATE OR REPLACE VIEW postfix_uid AS |
121 CREATE OR REPLACE VIEW postfix_uid AS |
107 SELECT local_part || '@' || domains.domainname AS address, uid |
122 SELECT local_part || '@' || domain_name.domainname AS address, uid |
108 FROM users |
123 FROM users |
109 LEFT JOIN domains USING (gid); |
124 LEFT JOIN domain_name USING (gid); |
110 |
125 |
111 CREATE OR REPLACE VIEW postfix_maildir AS |
126 CREATE OR REPLACE VIEW postfix_maildir AS |
112 SELECT local_part || '@' || domains.domainname AS address, |
127 SELECT local_part || '@' || domain_name.domainname AS address, |
113 domains.domaindir||'/'||uid||'/'||maillocation.maillocation||'/' AS maildir |
128 domain_data.domaindir||'/'||uid||'/'||maillocation.maillocation||'/' |
|
129 AS maildir |
114 FROM users |
130 FROM users |
115 LEFT JOIN domains USING (gid) |
131 LEFT JOIN domain_data USING (gid) |
|
132 LEFT JOIN domain_name USING (gid) |
116 LEFT JOIN maillocation USING (mid); |
133 LEFT JOIN maillocation USING (mid); |
117 |
134 |
118 CREATE OR REPLACE VIEW postfix_relocated AS |
135 CREATE OR REPLACE VIEW postfix_relocated AS |
119 SELECT address || '@' || domains.domainname AS address, destination |
136 SELECT address || '@' || domain_name.domainname AS address, destination |
120 FROM relocated |
137 FROM relocated |
121 LEFT JOIN domains USING (gid); |
138 LEFT JOIN domain_name USING (gid); |
122 |
139 |
123 CREATE OR REPLACE VIEW postfix_alias AS |
140 CREATE OR REPLACE VIEW postfix_alias AS |
124 SELECT address || '@' || domains.domainname AS address, destination, gid |
141 SELECT address || '@' || domain_name.domainname AS address, destination, gid |
125 FROM alias |
142 FROM alias |
126 LEFT JOIN domains USING (gid); |
143 LEFT JOIN domain_name USING (gid); |
127 |
144 |
128 CREATE OR REPLACE VIEW postfix_transport AS |
145 CREATE OR REPLACE VIEW postfix_transport AS |
129 SELECT local_part || '@' || domains.domainname AS address, |
146 SELECT local_part || '@' || domain_name.domainname AS address, |
130 transport.transport |
147 transport.transport |
131 FROM users |
148 FROM users |
132 LEFT JOIN transport USING (tid) |
149 LEFT JOIN transport USING (tid) |
133 LEFT JOIN domains USING (gid); |
150 LEFT JOIN domain_name USING (gid); |
134 |
151 |
135 CREATE OR REPLACE VIEW vmm_alias_count AS |
152 CREATE OR REPLACE VIEW vmm_alias_count AS |
136 SELECT count(DISTINCT address) AS aliases, gid |
153 SELECT count(DISTINCT address) AS aliases, gid |
137 FROM alias |
154 FROM alias |
138 GROUP BY gid; |
155 GROUP BY gid; |
139 |
156 |
140 CREATE OR REPLACE VIEW vmm_domain_info AS |
157 CREATE OR REPLACE VIEW vmm_domain_info AS |
141 SELECT gid, domainname, transport, domaindir, count(uid) AS accounts, |
158 SELECT gid, domainname, transport, domaindir, |
142 aliases |
159 count(uid) AS accounts, |
143 FROM domains |
160 aliases, |
|
161 (SELECT count(gid) |
|
162 FROM domain_name |
|
163 WHERE domain_name.gid = domain_data.gid |
|
164 AND NOT domain_name.is_primary) AS aliasdomains |
|
165 FROM domain_data |
|
166 LEFT JOIN domain_name USING (gid) |
144 LEFT JOIN transport USING (tid) |
167 LEFT JOIN transport USING (tid) |
145 LEFT JOIN users USING (gid) |
168 LEFT JOIN users USING (gid) |
146 LEFT JOIN vmm_alias_count USING (gid) |
169 LEFT JOIN vmm_alias_count USING (gid) |
|
170 WHERE domain_name.is_primary |
147 GROUP BY gid, domainname, transport, domaindir, aliases; |
171 GROUP BY gid, domainname, transport, domaindir, aliases; |
|
172 |
|
173 |
|
174 CREATE LANGUAGE plpgsql; |
|
175 |
|
176 |
|
177 CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$ |
|
178 DECLARE |
|
179 primary_count bigint; |
|
180 BEGIN |
|
181 SELECT INTO primary_count count(gid) + NEW.is_primary::integer |
|
182 FROM domain_name |
|
183 WHERE domain_name.gid = NEW.gid |
|
184 AND is_primary; |
|
185 |
|
186 IF (primary_count > 1) THEN |
|
187 RAISE EXCEPTION 'There can only be one domain marked as primary.'; |
|
188 END IF; |
|
189 |
|
190 RETURN NEW; |
|
191 END; |
|
192 $$ LANGUAGE plpgsql STABLE; |
|
193 |
|
194 |
|
195 CREATE TRIGGER primary_count BEFORE INSERT OR UPDATE ON domain_name |
|
196 FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger(); |