1 -- $Id$ |
1 -- $Id$ |
2 |
2 |
3 CREATE SEQUENCE transport_id; |
3 CREATE SEQUENCE transport_id; |
4 CREATE TABLE transport ( |
4 CREATE TABLE transport ( |
5 tid bigint NOT NULL DEFAULT nextval('transport_id'), |
5 tid bigint NOT NULL DEFAULT nextval('transport_id'), |
6 transport varchar(268) NOT NULL, -- smtp:[255-char.host.name:50025] |
6 transport varchar(270) NOT NULL, -- smtps:[255-char.host.name:50025] |
7 CONSTRAINT pkey_transport PRIMARY KEY (tid), |
7 CONSTRAINT pkey_transport PRIMARY KEY (tid), |
8 CONSTRAINT ukey_transport UNIQUE (transport) |
8 CONSTRAINT ukey_transport UNIQUE (transport) |
9 ); |
9 ); |
10 -- Insert default transport |
10 -- Insert default transport |
11 INSERT INTO transport(transport) VALUES ('dovecot:'); |
11 INSERT INTO transport(transport) VALUES ('dovecot:'); |
12 |
12 |
13 CREATE SEQUENCE maildir_id; |
13 CREATE SEQUENCE maillocation_id; |
14 CREATE TABLE maildir( |
14 CREATE TABLE maillocation( |
15 mid bigint NOT NULL DEFAULT nextval('maildir_id'), |
15 mid bigint NOT NULL DEFAULT nextval('maillocation_id'), |
16 maildir varchar(20) NOT NULL, |
16 maillocation varchar(20) NOT NULL, |
17 CONSTRAINT pkey_maildir PRIMARY KEY (mid), |
17 CONSTRAINT pkey_maillocation PRIMARY KEY (mid), |
18 CONSTRAINT ukey_maildir UNIQUE (maildir) |
18 CONSTRAINT ukey_maillocation UNIQUE (maillocation) |
19 ); |
19 ); |
20 -- Insert default Maildir-folder name |
20 -- Insert default Maildir-folder name |
21 INSERT INTO maildir(maildir) VALUES ('Maildir'); |
21 INSERT INTO maillocation(maillocation) VALUES ('Maildir'); |
22 |
22 |
23 CREATE SEQUENCE domains_gid |
23 CREATE SEQUENCE domains_gid |
24 START WITH 70000 |
24 START WITH 70000 |
25 INCREMENT BY 1 |
25 INCREMENT BY 1 |
26 MINVALUE 70000 |
26 MINVALUE 70000 |
34 MAXVALUE 4294967294 |
34 MAXVALUE 4294967294 |
35 NO CYCLE; |
35 NO CYCLE; |
36 |
36 |
37 CREATE TABLE domains ( |
37 CREATE TABLE domains ( |
38 gid bigint NOT NULL DEFAULT nextval('domains_gid'), |
38 gid bigint NOT NULL DEFAULT nextval('domains_gid'), |
|
39 tid bigint NOT NULL DEFAULT 1, -- defualt transport |
39 domainname varchar(255) NOT NULL, |
40 domainname varchar(255) NOT NULL, |
40 domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294 |
41 domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294 |
41 CONSTRAINT pkey_domains PRIMARY KEY (gid), |
42 CONSTRAINT pkey_domains PRIMARY KEY (gid), |
42 CONSTRAINT ukey_domains UNIQUE (domainname) |
43 CONSTRAINT ukey_domains UNIQUE (domainname), |
|
44 CONSTRAINT fkey_domains_tid_transport FOREIGN KEY (tid) |
|
45 REFERENCES transport (tid) |
43 ); |
46 ); |
44 |
47 |
45 CREATE TABLE users ( |
48 CREATE TABLE users ( |
46 local_part varchar(64) NOT NULL,-- only localpart w/o '@' |
49 local_part varchar(64) NOT NULL,-- only localpart w/o '@' |
47 passwd varchar(74) NOT NULL,-- {CRAM-MD5}+64hex numbers |
50 passwd varchar(74) NOT NULL,-- {CRAM-MD5}+64hex numbers |
53 disabled boolean NOT NULL DEFAULT FALSE, |
56 disabled boolean NOT NULL DEFAULT FALSE, |
54 CONSTRAINT pkye_users PRIMARY KEY (local_part, gid), |
57 CONSTRAINT pkye_users PRIMARY KEY (local_part, gid), |
55 CONSTRAINT ukey_users_uid UNIQUE (uid), |
58 CONSTRAINT ukey_users_uid UNIQUE (uid), |
56 CONSTRAINT fkey_users_gid_domains FOREIGN KEY (gid) |
59 CONSTRAINT fkey_users_gid_domains FOREIGN KEY (gid) |
57 REFERENCES domains (gid), |
60 REFERENCES domains (gid), |
58 CONSTRAINT fkey_users_mid_maildir FOREIGN KEY (mid) |
61 CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid) |
59 REFERENCES maildir (mid), |
62 REFERENCES maillocation (mid), |
60 CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid) |
63 CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid) |
61 REFERENCES transport (tid) |
64 REFERENCES transport (tid) |
62 ); |
65 ); |
63 |
66 |
64 CREATE TABLE alias ( |
67 CREATE TABLE alias ( |
103 FROM users |
106 FROM users |
104 LEFT JOIN domains USING (gid); |
107 LEFT JOIN domains USING (gid); |
105 |
108 |
106 CREATE OR REPLACE VIEW postfix_maildir AS |
109 CREATE OR REPLACE VIEW postfix_maildir AS |
107 SELECT local_part || '@' || domains.domainname AS address, |
110 SELECT local_part || '@' || domains.domainname AS address, |
108 domains.domaindir||'/'||uid||'/'||maildir.maildir||'/' AS maildir |
111 domains.domaindir||'/'||uid||'/'||maillocation.maillocation||'/' AS maildir |
109 FROM users |
112 FROM users |
110 LEFT JOIN domains USING (gid) |
113 LEFT JOIN domains USING (gid) |
111 LEFT JOIN maildir USING (mid); |
114 LEFT JOIN maillocation USING (mid); |
112 |
115 |
113 CREATE OR REPLACE VIEW postfix_relocated AS |
116 CREATE OR REPLACE VIEW postfix_relocated AS |
114 SELECT address || '@' || domains.domainname AS address, destination |
117 SELECT address || '@' || domains.domainname AS address, destination |
115 FROM relocated |
118 FROM relocated |
116 LEFT JOIN domains USING (gid); |
119 LEFT JOIN domains USING (gid); |