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