1 -- $Id$ |
1 -- $Id$ |
|
2 |
|
3 CREATE SEQUENCE transport_id; |
|
4 CREATE TABLE transport ( |
|
5 tid bigint NOT NULL DEFAULT nextval('transport_id'), |
|
6 transport varchar(268) NOT NULL, -- smtp:[255-char.host.name:50025] |
|
7 CONSTRAINT pkey_transport PRIMARY KEY (tid), |
|
8 CONSTRAINT ukey_transport UNIQUE (transport) |
|
9 ); |
|
10 -- Insert default transport |
|
11 INSERT INTO transport(transport) VALUES ('dovecot:'); |
|
12 |
|
13 CREATE SEQUENCE maildir_id; |
|
14 CREATE TABLE maildir( |
|
15 mid bigint NOT NULL DEFAULT nextval('maildir_id'), |
|
16 maildir varchar(20) NOT NULL, |
|
17 CONSTRAINT pkey_maildir PRIMARY KEY (mid), |
|
18 CONSTRAINT ukey_maildir UNIQUE (maildir) |
|
19 ); |
|
20 -- Insert default Maildir-folder name |
|
21 INSERT INTO maildir(maildir) VALUES ('Maildir'); |
2 |
22 |
3 CREATE SEQUENCE domains_gid |
23 CREATE SEQUENCE domains_gid |
4 START WITH 70000 |
24 START WITH 70000 |
5 INCREMENT BY 1 |
25 INCREMENT BY 1 |
6 MINVALUE 70000 |
26 MINVALUE 70000 |
15 NO CYCLE; |
35 NO CYCLE; |
16 |
36 |
17 CREATE TABLE domains ( |
37 CREATE TABLE domains ( |
18 gid bigint NOT NULL DEFAULT nextval('domains_gid'), |
38 gid bigint NOT NULL DEFAULT nextval('domains_gid'), |
19 domainname varchar(255) NOT NULL, |
39 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 |
40 domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294 |
22 CONSTRAINT pkey_domains PRIMARY KEY (gid), |
41 CONSTRAINT pkey_domains PRIMARY KEY (gid), |
23 CONSTRAINT ukey_domains UNIQUE (domainname) |
42 CONSTRAINT ukey_domains UNIQUE (domainname) |
24 ); |
43 ); |
25 |
44 |
27 local_part varchar(64) NOT NULL,-- only localpart w/o '@' |
46 local_part varchar(64) NOT NULL,-- only localpart w/o '@' |
28 passwd varchar(74) NOT NULL,-- {CRAM-MD5}+64hex numbers |
47 passwd varchar(74) NOT NULL,-- {CRAM-MD5}+64hex numbers |
29 name varchar(128) NULL, |
48 name varchar(128) NULL, |
30 uid bigint NOT NULL DEFAULT nextval('users_uid'), |
49 uid bigint NOT NULL DEFAULT nextval('users_uid'), |
31 gid bigint NOT NULL, |
50 gid bigint NOT NULL, |
32 --home varchar(40) NOT NULL, --/home/virtualmail/4294967294/4294967294 |
51 mid bigint NOT NULL DEFAULT 1, |
33 home bigint NOT NULL, -- 4294967294 |
52 tid bigint NOT NULL DEFAULT 1, |
34 mail varchar(128) NOT NULL DEFAULT 'Maildir', |
|
35 disabled boolean NOT NULL DEFAULT FALSE, |
53 disabled boolean NOT NULL DEFAULT FALSE, |
36 CONSTRAINT pkye_users PRIMARY KEY (local_part, gid), |
54 CONSTRAINT pkye_users PRIMARY KEY (local_part, gid), |
37 CONSTRAINT ukey_users_uid UNIQUE (uid), |
55 CONSTRAINT ukey_users_uid UNIQUE (uid), |
38 CONSTRAINT fkey_users_gid_domains FOREIGN KEY (gid) |
56 CONSTRAINT fkey_users_gid_domains FOREIGN KEY (gid) |
39 REFERENCES domains (gid) |
57 REFERENCES domains (gid), |
|
58 CONSTRAINT fkey_users_mid_maildir FOREIGN KEY (mid) |
|
59 REFERENCES maildir (mid), |
|
60 CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid) |
|
61 REFERENCES transport (tid) |
40 ); |
62 ); |
41 |
63 |
42 CREATE SEQUENCE alias_id; |
64 CREATE SEQUENCE alias_id; |
43 CREATE TABLE alias ( |
65 CREATE TABLE alias ( |
44 id bigint NOT NULL DEFAULT nextval('alias_id'), |
66 id bigint NOT NULL DEFAULT nextval('alias_id'), |
60 CONSTRAINT fkey_relocated_gid_domains FOREIGN KEY (gid) |
82 CONSTRAINT fkey_relocated_gid_domains FOREIGN KEY (gid) |
61 REFERENCES domains (gid) |
83 REFERENCES domains (gid) |
62 ); |
84 ); |
63 |
85 |
64 CREATE OR REPLACE VIEW dovecot_password AS |
86 CREATE OR REPLACE VIEW dovecot_password AS |
65 SELECT local_part || '@' || domains.domainname AS user, |
87 SELECT local_part || '@' || domains.domainname AS "user", |
66 passwd AS password |
88 passwd AS "password" |
67 FROM users |
89 FROM users |
68 LEFT JOIN domains USING (gid); |
90 LEFT JOIN domains USING (gid); |
69 |
91 |
70 CREATE OR REPLACE VIEW dovecot_user AS |
92 CREATE OR REPLACE VIEW dovecot_user AS |
71 SELECT local_part || '@' || domains.domainname AS userid, |
93 SELECT local_part || '@' || domains.domainname AS userid, |
72 domains.domaindir || '/' || home AS home, |
94 domains.domaindir || '/' || uid AS home, |
73 uid, |
95 uid, |
74 gid |
96 gid |
75 FROM users |
97 FROM users |
76 LEFT JOIN domains USING (gid); |
98 LEFT JOIN domains USING (gid); |
77 |
99 |
85 FROM users |
107 FROM users |
86 LEFT JOIN domains USING (gid); |
108 LEFT JOIN domains USING (gid); |
87 |
109 |
88 CREATE OR REPLACE VIEW postfix_maildir AS |
110 CREATE OR REPLACE VIEW postfix_maildir AS |
89 SELECT local_part || '@' || domains.domainname AS address, |
111 SELECT local_part || '@' || domains.domainname AS address, |
90 domains.domaindir || '/' || home || '/' || mail || '/' AS maildir |
112 domains.domaindir||'/'||uid||'/'||maildir.maildir||'/' AS maildir |
91 FROM users |
113 FROM users |
92 LEFT JOIN domains USING (gid); |
114 LEFT JOIN domains USING (gid) |
|
115 LEFT JOIN maildir USING (mid); |
93 |
116 |
94 CREATE OR REPLACE VIEW postfix_relocated AS |
117 CREATE OR REPLACE VIEW postfix_relocated AS |
95 SELECT address || '@' || domains.domainname AS address, destination |
118 SELECT address || '@' || domains.domainname AS address, destination |
96 FROM relocated |
119 FROM relocated |
97 LEFT JOIN domains USING (gid); |
120 LEFT JOIN domains USING (gid); |
100 SELECT address || '@' || domains.domainname AS address, destination, gid |
123 SELECT address || '@' || domains.domainname AS address, destination, gid |
101 FROM alias |
124 FROM alias |
102 LEFT JOIN domains USING (gid); |
125 LEFT JOIN domains USING (gid); |
103 |
126 |
104 CREATE OR REPLACE VIEW postfix_transport AS |
127 CREATE OR REPLACE VIEW postfix_transport AS |
105 SELECT transport, domainname |
128 SELECT local_part || '@' || domains.domainname AS address, |
106 FROM domains; |
129 transport.transport |
|
130 FROM users |
|
131 LEFT JOIN transport USING (tid) |
|
132 LEFT JOIN domains USING (gid); |
107 |
133 |
108 CREATE OR REPLACE VIEW vmm_alias_count AS |
134 CREATE OR REPLACE VIEW vmm_alias_count AS |
109 SELECT count(DISTINCT address) AS aliases, gid |
135 SELECT count(DISTINCT address) AS aliases, gid |
110 FROM alias |
136 FROM alias |
111 GROUP BY gid; |
137 GROUP BY gid; |