|
1 -- $Id$ |
|
2 |
|
3 CREATE SEQUENCE domains_gid |
|
4 START WITH 70000 |
|
5 INCREMENT BY 1 |
|
6 MINVALUE 70000 |
|
7 MAXVALUE 4294967294 |
|
8 NO CYCLE; |
|
9 |
|
10 CREATE SEQUENCE users_uid |
|
11 START WITH 70000 |
|
12 INCREMENT BY 1 |
|
13 MINVALUE 70000 |
|
14 MAXVALUE 4294967294 |
|
15 NO CYCLE; |
|
16 |
|
17 CREATE TABLE domains ( |
|
18 gid bigint NOT NULL DEFAULT nextval('domains_gid'), |
|
19 domainname varchar(255) NOT NULL, |
|
20 transport varchar(268) NOT NULL DEFAULT 'dovecot:', -- smtp:[255-char.host.name:50025] |
|
21 domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294 |
|
22 CONSTRAINT pkey_domains PRIMARY KEY (gid), |
|
23 CONSTRAINT ukey_domains UNIQUE (domainname) |
|
24 ); |
|
25 |
|
26 CREATE TABLE users ( |
|
27 local_part varchar(64) NOT NULL,-- only localpart w/o '@' |
|
28 passwd varchar(74) NOT NULL,-- {CRAM-MD5}+64hex numbers |
|
29 name varchar(128) NULL, |
|
30 uid bigint NOT NULL DEFAULT nextval('users_uid'), |
|
31 gid bigint NOT NULL, |
|
32 --home varchar(40) NOT NULL, --/home/virtualmail/4294967294/4294967294 |
|
33 home bigint NOT NULL, -- 4294967294 |
|
34 mail varchar(128) NOT NULL DEFAULT 'Maildir', |
|
35 disabled boolean NOT NULL DEFAULT FALSE, |
|
36 CONSTRAINT pkye_users PRIMARY KEY (local_part, gid), |
|
37 CONSTRAINT ukey_users_uid UNIQUE (uid), |
|
38 CONSTRAINT fkey_users_gid_domains FOREIGN KEY (gid) |
|
39 REFERENCES domains (gid) |
|
40 ); |
|
41 |
|
42 CREATE SEQUENCE alias_id; |
|
43 CREATE TABLE alias ( |
|
44 id bigint NOT NULL DEFAULT nextval('alias_id'), |
|
45 gid bigint NOT NULL, |
|
46 address varchar(256) NOT NULL, |
|
47 destination varchar(320) NOT NULL, |
|
48 CONSTRAINT pkey_alias PRIMARY KEY (gid, address, destination), |
|
49 CONSTRAINT fkey_alias_gid_domains FOREIGN KEY (gid) |
|
50 REFERENCES domains (gid) |
|
51 ); |
|
52 |
|
53 CREATE SEQUENCE relocated_id; |
|
54 CREATE TABLE relocated ( |
|
55 id bigint NOT NULL DEFAULT nextval('relocated_id'), |
|
56 gid bigint NOT NULL, |
|
57 address varchar(64) NOT NULL, |
|
58 destination varchar(320) NOT NULL, |
|
59 CONSTRAINT pkey_relocated PRIMARY KEY (gid, address), |
|
60 CONSTRAINT fkey_relocated_gid_domains FOREIGN KEY (gid) |
|
61 REFERENCES domains (gid) |
|
62 ); |
|
63 |
|
64 CREATE OR REPLACE VIEW dovecot_password AS |
|
65 SELECT local_part || '@' || domains.domainname AS user, |
|
66 passwd AS password |
|
67 FROM users |
|
68 LEFT JOIN domains USING (gid); |
|
69 |
|
70 CREATE OR REPLACE VIEW dovecot_user AS |
|
71 SELECT local_part || '@' || domains.domainname AS userid, |
|
72 domains.domaindir || '/' || home AS home, |
|
73 uid, |
|
74 gid |
|
75 FROM users |
|
76 LEFT JOIN domains USING (gid); |
|
77 |
|
78 CREATE OR REPLACE VIEW postfix_gid AS |
|
79 SELECT gid, domainname |
|
80 FROM domains; |
|
81 |
|
82 CREATE OR REPLACE VIEW postfix_uid AS |
|
83 SELECT local_part || '@' || domains.domainname AS address, |
|
84 uid |
|
85 FROM users |
|
86 LEFT JOIN domains USING (gid); |
|
87 |
|
88 CREATE OR REPLACE VIEW postfix_maildir AS |
|
89 SELECT local_part || '@' || domains.domainname AS address, |
|
90 domains.domaindir || '/' || home || '/' || mail || '/' AS maildir |
|
91 FROM users |
|
92 LEFT JOIN domains USING (gid); |
|
93 |
|
94 CREATE OR REPLACE VIEW postfix_relocated AS |
|
95 SELECT address || '@' || domains.domainname AS address, destination |
|
96 FROM relocated |
|
97 LEFT JOIN domains USING (gid); |
|
98 |
|
99 CREATE OR REPLACE VIEW postfix_alias AS |
|
100 SELECT address || '@' || domains.domainname AS address, destination, gid |
|
101 FROM alias |
|
102 LEFT JOIN domains USING (gid); |
|
103 |
|
104 CREATE OR REPLACE VIEW postfix_transport AS |
|
105 SELECT transport, domainname |
|
106 FROM domains; |
|
107 |
|
108 CREATE OR REPLACE VIEW vmm_alias_count AS |
|
109 SELECT count(DISTINCT address) AS aliases, gid |
|
110 FROM alias |
|
111 GROUP BY gid; |