pgsql/create_tables.pgsql
branchv0.6.x
changeset 297 e21ceaabe871
parent 257 5b8fde01e4f0
child 368 be4bd77dbe57
equal deleted inserted replaced
296:62211b6a9b8e 297:e21ceaabe871
     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);