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