1 SET client_encoding = 'UTF8'; |
1 SET client_encoding = 'UTF8'; |
2 SET client_min_messages = warning; |
2 SET client_min_messages = warning; |
3 |
3 |
4 |
4 |
5 CREATE SEQUENCE transport_id; |
5 CREATE SEQUENCE transport_id; |
|
6 |
|
7 CREATE SEQUENCE mailboxformat_id; |
6 |
8 |
7 CREATE SEQUENCE maillocation_id; |
9 CREATE SEQUENCE maillocation_id; |
8 |
10 |
9 CREATE SEQUENCE domain_gid |
11 CREATE SEQUENCE domain_gid |
10 START WITH 70000 |
12 START WITH 70000 |
28 CONSTRAINT ukey_transport UNIQUE (transport) |
30 CONSTRAINT ukey_transport UNIQUE (transport) |
29 ); |
31 ); |
30 -- Insert default transport |
32 -- Insert default transport |
31 INSERT INTO transport(transport) VALUES ('dovecot:'); |
33 INSERT INTO transport(transport) VALUES ('dovecot:'); |
32 |
34 |
33 CREATE TABLE maillocation( |
35 CREATE TABLE mailboxformat ( |
34 mid bigint NOT NULL DEFAULT nextval('maillocation_id'), |
36 fid bigint NOT NULL DEFAULT nextval('mailboxformat_id'), |
35 maillocation varchar(20) NOT NULL, |
37 format varchar(20) NOT NULL, |
|
38 CONSTRAINT pkey_mailboxformat PRIMARY KEY (fid), |
|
39 CONSTRAINT ukey_mailboxformat UNIQUE (format) |
|
40 ); |
|
41 -- Insert supported mailbox formats |
|
42 INSERT INTO mailboxformat(format) VALUES ('maildir'); |
|
43 INSERT INTO mailboxformat(format) VALUES ('mdbox'); |
|
44 INSERT INTO mailboxformat(format) VALUES ('sdbox'); |
|
45 |
|
46 CREATE TABLE maillocation ( |
|
47 mid bigint NOT NULL DEFAULT nextval('maillocation_id'), |
|
48 fid bigint NOT NULL DEFAULT 1, |
|
49 directory varchar(20) NOT NULL, |
|
50 extra varchar(1024), |
36 CONSTRAINT pkey_maillocation PRIMARY KEY (mid), |
51 CONSTRAINT pkey_maillocation PRIMARY KEY (mid), |
37 CONSTRAINT ukey_maillocation UNIQUE (maillocation) |
52 CONSTRAINT fkey_maillocation_fid_mailboxformat FOREIGN KEY (fid) |
|
53 REFERENCES mailboxformat (fid); |
38 ); |
54 ); |
39 -- Insert default Maildir-folder name |
55 -- Insert default Maildir-folder name |
40 INSERT INTO maillocation(maillocation) VALUES ('Maildir'); |
56 INSERT INTO maillocation(directory) VALUES ('Maildir'); |
41 |
57 |
42 CREATE TABLE domain_data ( |
58 CREATE TABLE domain_data ( |
43 gid bigint NOT NULL DEFAULT nextval('domain_gid'), |
59 gid bigint NOT NULL DEFAULT nextval('domain_gid'), |
44 tid bigint NOT NULL DEFAULT 1, -- default transport |
60 tid bigint NOT NULL DEFAULT 1, -- default transport |
45 domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294 |
61 domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294 |
57 REFERENCES domain_data (gid) |
73 REFERENCES domain_data (gid) |
58 ); |
74 ); |
59 |
75 |
60 CREATE TABLE users ( |
76 CREATE TABLE users ( |
61 local_part varchar(64) NOT NULL,-- only localpart w/o '@' |
77 local_part varchar(64) NOT NULL,-- only localpart w/o '@' |
62 passwd varchar(74) NOT NULL,-- {CRAM-MD5}+64hex numbers |
78 passwd varchar(270) NOT NULL, |
63 name varchar(128) NULL, |
79 name varchar(128) NULL, |
64 uid bigint NOT NULL DEFAULT nextval('users_uid'), |
80 uid bigint NOT NULL DEFAULT nextval('users_uid'), |
65 gid bigint NOT NULL, |
81 gid bigint NOT NULL, |
66 mid bigint NOT NULL DEFAULT 1, |
82 mid bigint NOT NULL DEFAULT 1, |
67 tid bigint NOT NULL DEFAULT 1, |
83 tid bigint NOT NULL DEFAULT 1, |
104 LEFT JOIN domain_name USING (gid); |
120 LEFT JOIN domain_name USING (gid); |
105 |
121 |
106 CREATE OR REPLACE VIEW dovecot_user AS |
122 CREATE OR REPLACE VIEW dovecot_user AS |
107 SELECT local_part || '@' || domain_name.domainname AS userid, |
123 SELECT local_part || '@' || domain_name.domainname AS userid, |
108 uid, gid, domain_data.domaindir || '/' || uid AS home, |
124 uid, gid, domain_data.domaindir || '/' || uid AS home, |
109 '~/' || maillocation.maillocation AS mail |
125 mailboxformat.format || ':~/' || maillocation.directory AS mail |
110 FROM users |
126 FROM users |
111 LEFT JOIN domain_data USING (gid) |
127 LEFT JOIN domain_data USING (gid) |
112 LEFT JOIN domain_name USING (gid) |
128 LEFT JOIN domain_name USING (gid) |
113 LEFT JOIN maillocation USING (mid); |
129 LEFT JOIN maillocation USING (mid) |
|
130 LEFT JOIN mailboxformat USING (fid); |
114 |
131 |
115 CREATE OR REPLACE VIEW postfix_gid AS |
132 CREATE OR REPLACE VIEW postfix_gid AS |
116 SELECT gid, domainname |
133 SELECT gid, domainname |
117 FROM domain_name; |
134 FROM domain_name; |
118 |
135 |
121 FROM users |
138 FROM users |
122 LEFT JOIN domain_name USING (gid); |
139 LEFT JOIN domain_name USING (gid); |
123 |
140 |
124 CREATE OR REPLACE VIEW postfix_maildir AS |
141 CREATE OR REPLACE VIEW postfix_maildir AS |
125 SELECT local_part || '@' || domain_name.domainname AS address, |
142 SELECT local_part || '@' || domain_name.domainname AS address, |
126 domain_data.domaindir||'/'||uid||'/'||maillocation.maillocation||'/' |
143 domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/' |
127 AS maildir |
144 AS maildir |
128 FROM users |
145 FROM users |
129 LEFT JOIN domain_data USING (gid) |
146 LEFT JOIN domain_data USING (gid) |
130 LEFT JOIN domain_name USING (gid) |
147 LEFT JOIN domain_name USING (gid) |
131 LEFT JOIN maillocation USING (mid); |
148 LEFT JOIN maillocation USING (mid); |