|
1 SET client_encoding = 'UTF8'; |
|
2 SET client_min_messages = warning; |
|
3 |
|
4 ALTER SEQUENCE domains_gid RENAME TO domain_gid; |
|
5 |
|
6 |
|
7 CREATE TABLE domain_data ( |
|
8 gid bigint NOT NULL DEFAULT nextval('domain_gid'), |
|
9 tid bigint NOT NULL DEFAULT 1, |
|
10 domaindir varchar(40) NOT NULL, |
|
11 CONSTRAINT pkey_domain_data PRIMARY KEY (gid), |
|
12 CONSTRAINT fkey_domain_data_tid_transport FOREIGN KEY (tid) |
|
13 REFERENCES transport (tid) |
|
14 ); |
|
15 |
|
16 CREATE TABLE domain_name ( |
|
17 domainname varchar(255) NOT NULL, |
|
18 gid bigint NOT NULL, |
|
19 is_primary boolean NOT NULL, |
|
20 CONSTRAINT pkey_domain_name PRIMARY KEY (domainname), |
|
21 CONSTRAINT fkey_domain_name_gid_domain_data FOREIGN KEY (gid) |
|
22 REFERENCES domain_data (gid) |
|
23 ); |
|
24 |
|
25 INSERT INTO domain_data (gid, tid, domaindir) |
|
26 SELECT gid, tid, domaindir |
|
27 FROM domains; |
|
28 |
|
29 INSERT INTO domain_name (domainname, gid, is_primary) |
|
30 SELECT domainname, gid, TRUE |
|
31 FROM domains; |
|
32 |
|
33 |
|
34 ALTER TABLE users DROP CONSTRAINT pkye_users; |
|
35 ALTER TABLE users ADD CONSTRAINT pkey_users PRIMARY KEY (local_part, gid); |
|
36 ALTER TABLE users DROP CONSTRAINT fkey_users_gid_domains; |
|
37 ALTER TABLE users ADD CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid) |
|
38 REFERENCES domain_data (gid); |
|
39 |
|
40 ALTER TABLE alias DROP CONSTRAINT fkey_alias_gid_domains; |
|
41 ALTER TABLE alias DROP CONSTRAINT pkey_alias; |
|
42 ALTER TABLE alias ADD CONSTRAINT fkey_alias_gid_domain_data FOREIGN KEY (gid) |
|
43 REFERENCES domain_data (gid); |
|
44 |
|
45 ALTER TABLE relocated DROP CONSTRAINT fkey_relocated_gid_domains; |
|
46 ALTER TABLE relocated ADD CONSTRAINT fkey_relocated_gid_domain_data |
|
47 FOREIGN KEY (gid) REFERENCES domain_data (gid); |
|
48 |
|
49 |
|
50 CREATE OR REPLACE VIEW dovecot_password AS |
|
51 SELECT local_part || '@' || domain_name.domainname AS "user", |
|
52 passwd AS "password", smtp, pop3, imap, managesieve |
|
53 FROM users |
|
54 LEFT JOIN domain_name USING (gid); |
|
55 |
|
56 CREATE OR REPLACE VIEW dovecot_user AS |
|
57 SELECT local_part || '@' || domain_name.domainname AS userid, |
|
58 uid, gid, domain_data.domaindir || '/' || uid AS home, |
|
59 '~/' || maillocation.maillocation AS mail |
|
60 FROM users |
|
61 LEFT JOIN domain_data USING (gid) |
|
62 LEFT JOIN domain_name USING (gid) |
|
63 LEFT JOIN maillocation USING (mid); |
|
64 |
|
65 CREATE OR REPLACE VIEW postfix_gid AS |
|
66 SELECT gid, domainname |
|
67 FROM domain_name; |
|
68 |
|
69 CREATE OR REPLACE VIEW postfix_uid AS |
|
70 SELECT local_part || '@' || domain_name.domainname AS address, uid |
|
71 FROM users |
|
72 LEFT JOIN domain_name USING (gid); |
|
73 |
|
74 CREATE OR REPLACE VIEW postfix_maildir AS |
|
75 SELECT local_part || '@' || domain_name.domainname AS address, |
|
76 domain_data.domaindir||'/'||uid||'/'||maillocation.maillocation||'/' |
|
77 AS maildir |
|
78 FROM users |
|
79 LEFT JOIN domain_data USING (gid) |
|
80 LEFT JOIN domain_name USING (gid) |
|
81 LEFT JOIN maillocation USING (mid); |
|
82 |
|
83 CREATE OR REPLACE VIEW postfix_relocated AS |
|
84 SELECT address || '@' || domain_name.domainname AS address, destination |
|
85 FROM relocated |
|
86 LEFT JOIN domain_name USING (gid); |
|
87 |
|
88 DROP VIEW postfix_alias; |
|
89 DROP VIEW vmm_domain_info; |
|
90 DROP VIEW vmm_alias_count; |
|
91 |
|
92 ALTER TABLE alias ALTER address TYPE varchar(64); |
|
93 ALTER TABLE alias ADD CONSTRAINT pkey_alias |
|
94 PRIMARY KEY (gid, address, destination); |
|
95 |
|
96 CREATE OR REPLACE VIEW postfix_alias AS |
|
97 SELECT address || '@' || domain_name.domainname AS address, destination, gid |
|
98 FROM alias |
|
99 LEFT JOIN domain_name USING (gid); |
|
100 |
|
101 CREATE OR REPLACE VIEW postfix_transport AS |
|
102 SELECT local_part || '@' || domain_name.domainname AS address, |
|
103 transport.transport |
|
104 FROM users |
|
105 LEFT JOIN transport USING (tid) |
|
106 LEFT JOIN domain_name USING (gid); |
|
107 |
|
108 CREATE OR REPLACE VIEW vmm_domain_info AS |
|
109 SELECT gid, domainname, transport, domaindir, |
|
110 count(uid) AS accounts, |
|
111 (SELECT count(DISTINCT address) |
|
112 FROM alias |
|
113 WHERE alias.gid = domain_data.gid) AS aliases, |
|
114 (SELECT count(gid) |
|
115 FROM relocated |
|
116 WHERE relocated.gid = domain_data.gid) AS relocated, |
|
117 (SELECT count(gid) |
|
118 FROM domain_name |
|
119 WHERE domain_name.gid = domain_data.gid |
|
120 AND NOT domain_name.is_primary) AS aliasdomains |
|
121 FROM domain_data |
|
122 LEFT JOIN domain_name USING (gid) |
|
123 LEFT JOIN transport USING (tid) |
|
124 LEFT JOIN users USING (gid) |
|
125 WHERE domain_name.is_primary |
|
126 GROUP BY gid, domainname, transport, domaindir; |
|
127 |
|
128 |
|
129 DROP TABLE domains; |
|
130 |
|
131 |
|
132 CREATE LANGUAGE plpgsql; |
|
133 |
|
134 CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$ |
|
135 DECLARE |
|
136 primary_count bigint; |
|
137 BEGIN |
|
138 SELECT INTO primary_count count(gid) + NEW.is_primary::integer |
|
139 FROM domain_name |
|
140 WHERE domain_name.gid = NEW.gid |
|
141 AND is_primary; |
|
142 |
|
143 IF (primary_count > 1) THEN |
|
144 RAISE EXCEPTION 'There can only be one domain marked as primary.'; |
|
145 END IF; |
|
146 |
|
147 RETURN NEW; |
|
148 END; |
|
149 $$ LANGUAGE plpgsql STABLE; |
|
150 |
|
151 DROP TRIGGER IF EXISTS primary_count ON domain_name; |
|
152 CREATE TRIGGER primary_count_ins BEFORE INSERT ON domain_name |
|
153 FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger(); |
|
154 |
|
155 CREATE TRIGGER primary_count_upd AFTER UPDATE ON domain_name |
|
156 FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger(); |