--- a/ChangeLog	Fri Jan 11 01:24:07 2008 +0000
+++ b/ChangeLog	Tue Jan 15 01:33:25 2008 +0000
@@ -1,4 +1,9 @@
 === 0.0.0 ===
+2008-01-15  Pascal Volk  <neverseen@users.sourceforge.net>
+
+	* create_tables.pgsql: Redesign of table layout, create separate tables for
+	maildir-folder and transport. Also assign transport to users not to domains
+
 2008-01-11  Pascal Volk  <neverseen@users.sourceforge.net>
 
 	* vmm (usage): Fixed a syntax error.

--- a/create_tables.pgsql	Fri Jan 11 01:24:07 2008 +0000
+++ b/create_tables.pgsql	Tue Jan 15 01:33:25 2008 +0000
@@ -1,5 +1,25 @@
 -- $Id$ 
 
+CREATE SEQUENCE transport_id;
+CREATE TABLE transport (
+    tid         bigint NOT NULL DEFAULT nextval('transport_id'),
+    transport   varchar(268) NOT NULL, -- smtp:[255-char.host.name:50025]
+    CONSTRAINT pkey_transport PRIMARY KEY (tid),
+    CONSTRAINT ukey_transport UNIQUE (transport)
+);
+-- Insert default transport
+INSERT INTO transport(transport) VALUES ('dovecot:');
+
+CREATE SEQUENCE maildir_id;
+CREATE TABLE maildir(
+    mid     bigint NOT NULL DEFAULT nextval('maildir_id'),
+    maildir varchar(20) NOT NULL,
+    CONSTRAINT pkey_maildir PRIMARY KEY (mid),
+    CONSTRAINT ukey_maildir UNIQUE (maildir)
+);
+-- Insert default Maildir-folder name
+INSERT INTO maildir(maildir) VALUES ('Maildir');
+
 CREATE SEQUENCE domains_gid
     START WITH 70000
     INCREMENT BY 1
@@ -17,7 +37,6 @@
 CREATE TABLE domains (
     gid         bigint NOT NULL DEFAULT nextval('domains_gid'),
     domainname  varchar(255) NOT NULL,
-    transport   varchar(268) NOT NULL DEFAULT 'dovecot:', -- smtp:[255-char.host.name:50025]
     domaindir   varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294
     CONSTRAINT pkey_domains PRIMARY KEY (gid),
     CONSTRAINT ukey_domains UNIQUE (domainname)
@@ -29,14 +48,17 @@
     name        varchar(128) NULL,
     uid         bigint NOT NULL DEFAULT nextval('users_uid'),
     gid         bigint NOT NULL,
-  --home        varchar(40) NOT NULL, --/home/virtualmail/4294967294/4294967294
-    home        bigint NOT NULL, -- 4294967294
-    mail        varchar(128) NOT NULL DEFAULT 'Maildir',
+    mid         bigint NOT NULL DEFAULT 1,
+    tid         bigint NOT NULL DEFAULT 1,
     disabled    boolean NOT NULL DEFAULT FALSE,
     CONSTRAINT pkye_users PRIMARY KEY (local_part, gid),
     CONSTRAINT ukey_users_uid UNIQUE (uid),
     CONSTRAINT fkey_users_gid_domains FOREIGN KEY (gid)
-        REFERENCES domains (gid)
+        REFERENCES domains (gid),
+    CONSTRAINT fkey_users_mid_maildir FOREIGN KEY (mid)
+        REFERENCES maildir (mid),
+    CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid)
+        REFERENCES transport (tid)
 );
 
 CREATE SEQUENCE alias_id;
@@ -62,14 +84,14 @@
 );
 
 CREATE OR REPLACE VIEW dovecot_password AS
-    SELECT local_part || '@' || domains.domainname AS user,
-           passwd AS password
+    SELECT local_part || '@' || domains.domainname AS "user",
+           passwd AS "password"
       FROM users
            LEFT JOIN domains USING (gid);
 
 CREATE OR REPLACE VIEW dovecot_user AS
     SELECT local_part || '@' || domains.domainname AS userid,
-           domains.domaindir || '/' || home AS home,
+           domains.domaindir || '/' || uid AS home,
            uid,
            gid
       FROM users
@@ -87,9 +109,10 @@
 
 CREATE OR REPLACE VIEW postfix_maildir AS
     SELECT local_part || '@' || domains.domainname AS address,
-           domains.domaindir || '/' || home || '/' || mail || '/' AS maildir
+           domains.domaindir||'/'||uid||'/'||maildir.maildir||'/' AS maildir
       FROM users
-           LEFT JOIN domains USING (gid);
+           LEFT JOIN domains USING (gid)
+           LEFT JOIN maildir USING (mid);
 
 CREATE OR REPLACE VIEW postfix_relocated AS
     SELECT address || '@' || domains.domainname AS address, destination
@@ -102,8 +125,11 @@
            LEFT JOIN domains USING (gid);
 
 CREATE OR REPLACE VIEW postfix_transport AS
-    SELECT transport, domainname
-      FROM domains;
+    SELECT local_part || '@' || domains.domainname AS address,
+           transport.transport
+      FROM users
+           LEFT JOIN transport USING (tid)
+           LEFT JOIN domains USING (gid);
 
 CREATE OR REPLACE VIEW vmm_alias_count AS
     SELECT count(DISTINCT address) AS aliases, gid