create_tables.pgsql
changeset 42 9d10877e1c10
parent 29 9653d4f5d97f
child 63 9b627307f4a8
equal deleted inserted replaced
41:fbcb7e314510 42:9d10877e1c10
     1 -- $Id$ 
     1 -- $Id$ 
     2 
     2 
       
     3 SET client_encoding = 'UTF8';
       
     4 SET client_min_messages = warning;
       
     5 
       
     6 
     3 CREATE SEQUENCE transport_id;
     7 CREATE SEQUENCE transport_id;
     4 CREATE TABLE transport (
       
     5     tid         bigint NOT NULL DEFAULT nextval('transport_id'),
       
     6     transport   varchar(270) NOT NULL, -- smtps:[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 
     8 
    13 CREATE SEQUENCE maillocation_id;
     9 CREATE SEQUENCE maillocation_id;
    14 CREATE TABLE maillocation(
       
    15     mid     bigint NOT NULL DEFAULT nextval('maillocation_id'),
       
    16     maillocation varchar(20) NOT NULL,
       
    17     CONSTRAINT pkey_maillocation PRIMARY KEY (mid),
       
    18     CONSTRAINT ukey_maillocation UNIQUE (maillocation)
       
    19 );
       
    20 -- Insert default Maildir-folder name
       
    21 INSERT INTO maillocation(maillocation) VALUES ('Maildir');
       
    22 
    10 
    23 CREATE SEQUENCE domains_gid
    11 CREATE SEQUENCE domain_gid
    24     START WITH 70000
    12     START WITH 70000
    25     INCREMENT BY 1
    13     INCREMENT BY 1
    26     MINVALUE 70000
    14     MINVALUE 70000
    27     MAXVALUE 4294967294
    15     MAXVALUE 4294967294
    28     NO CYCLE;
    16     NO CYCLE;
    32     INCREMENT BY 1
    20     INCREMENT BY 1
    33     MINVALUE 70000
    21     MINVALUE 70000
    34     MAXVALUE 4294967294
    22     MAXVALUE 4294967294
    35     NO CYCLE;
    23     NO CYCLE;
    36 
    24 
    37 CREATE TABLE domains (
    25 
    38     gid         bigint NOT NULL DEFAULT nextval('domains_gid'),
    26 CREATE TABLE transport (
       
    27     tid         bigint NOT NULL DEFAULT nextval('transport_id'),
       
    28     transport   varchar(270) NOT NULL, -- smtps:[255-char.host.name:50025]
       
    29     CONSTRAINT  pkey_transport PRIMARY KEY (tid),
       
    30     CONSTRAINT  ukey_transport UNIQUE (transport)
       
    31 );
       
    32 -- Insert default transport
       
    33 INSERT INTO transport(transport) VALUES ('dovecot:');
       
    34 
       
    35 CREATE TABLE maillocation(
       
    36     mid     bigint NOT NULL DEFAULT nextval('maillocation_id'),
       
    37     maillocation varchar(20) NOT NULL,
       
    38     CONSTRAINT  pkey_maillocation PRIMARY KEY (mid),
       
    39     CONSTRAINT  ukey_maillocation UNIQUE (maillocation)
       
    40 );
       
    41 -- Insert default Maildir-folder name
       
    42 INSERT INTO maillocation(maillocation) VALUES ('Maildir');
       
    43 
       
    44 CREATE TABLE domain_data (
       
    45     gid         bigint NOT NULL DEFAULT nextval('domain_gid'),
    39     tid         bigint NOT NULL DEFAULT 1, -- defualt transport
    46     tid         bigint NOT NULL DEFAULT 1, -- defualt transport
       
    47     domaindir   varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294
       
    48     CONSTRAINT  pkey_domain_data PRIMARY KEY (gid),
       
    49     CONSTRAINT  fkey_domain_data_tid_transport FOREIGN KEY (tid)
       
    50         REFERENCES transport (tid)
       
    51 );
       
    52 
       
    53 CREATE TABLE domain_name (
    40     domainname  varchar(255) NOT NULL,
    54     domainname  varchar(255) NOT NULL,
    41     domaindir   varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294
    55     gid         bigint NOT NULL,
    42     CONSTRAINT pkey_domains PRIMARY KEY (gid),
    56     is_primary  boolean NOT NULL,
    43     CONSTRAINT ukey_domains UNIQUE (domainname),
    57     CONSTRAINT  pkey_domain_name PRIMARY KEY (domainname),
    44     CONSTRAINT fkey_domains_tid_transport FOREIGN KEY (tid)
    58     CONSTRAINT  fkey_domain_name_gid_domain_data FOREIGN KEY (gid)
    45         REFERENCES transport (tid)
    59         REFERENCES domain_data (gid)
    46 );
    60 );
    47 
    61 
    48 CREATE TABLE users (
    62 CREATE TABLE users (
    49     local_part  varchar(64) NOT NULL,-- only localpart w/o '@'
    63     local_part  varchar(64) NOT NULL,-- only localpart w/o '@'
    50     passwd      varchar(74) NOT NULL,-- {CRAM-MD5}+64hex numbers
    64     passwd      varchar(74) NOT NULL,-- {CRAM-MD5}+64hex numbers
    55     tid         bigint NOT NULL DEFAULT 1,
    69     tid         bigint NOT NULL DEFAULT 1,
    56     smtp        boolean NOT NULL DEFAULT TRUE,
    70     smtp        boolean NOT NULL DEFAULT TRUE,
    57     pop3        boolean NOT NULL DEFAULT TRUE,
    71     pop3        boolean NOT NULL DEFAULT TRUE,
    58     imap        boolean NOT NULL DEFAULT TRUE,
    72     imap        boolean NOT NULL DEFAULT TRUE,
    59     managesieve boolean NOT NULL DEFAULT TRUE,
    73     managesieve boolean NOT NULL DEFAULT TRUE,
    60     CONSTRAINT pkye_users PRIMARY KEY (local_part, gid),
    74     CONSTRAINT  pkye_users PRIMARY KEY (local_part, gid),
    61     CONSTRAINT ukey_users_uid UNIQUE (uid),
    75     CONSTRAINT  ukey_users_uid UNIQUE (uid),
    62     CONSTRAINT fkey_users_gid_domains FOREIGN KEY (gid)
    76     CONSTRAINT  fkey_users_gid_domain_data FOREIGN KEY (gid)
    63         REFERENCES domains (gid),
    77         REFERENCES domain_data (gid),
    64     CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid)
    78     CONSTRAINT  fkey_users_mid_maillocation FOREIGN KEY (mid)
    65         REFERENCES maillocation (mid),
    79         REFERENCES maillocation (mid),
    66     CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid)
    80     CONSTRAINT  fkey_users_tid_transport FOREIGN KEY (tid)
    67         REFERENCES transport (tid)
    81         REFERENCES transport (tid)
    68 );
    82 );
    69 
    83 
    70 CREATE TABLE alias (
    84 CREATE TABLE alias (
    71     gid         bigint NOT NULL,
    85     gid         bigint NOT NULL,
    72     address     varchar(256) NOT NULL,
    86     address     varchar(256) NOT NULL,
    73     destination varchar(320) NOT NULL,
    87     destination varchar(320) NOT NULL,
    74     CONSTRAINT pkey_alias PRIMARY KEY (gid, address, destination),
    88     CONSTRAINT  pkey_alias PRIMARY KEY (gid, address, destination),
    75     CONSTRAINT fkey_alias_gid_domains FOREIGN KEY (gid)
    89     CONSTRAINT  fkey_alias_gid_domain_data FOREIGN KEY (gid)
    76         REFERENCES domains (gid)
    90         REFERENCES domain_data (gid)
    77 );
    91 );
    78 
    92 
    79 CREATE TABLE relocated (
    93 CREATE TABLE relocated (
    80     gid         bigint NOT NULL,
    94     gid         bigint NOT NULL,
    81     address     varchar(64) NOT NULL,
    95     address     varchar(64) NOT NULL,
    82     destination varchar(320) NOT NULL,
    96     destination varchar(320) NOT NULL,
    83     CONSTRAINT pkey_relocated PRIMARY KEY (gid, address),
    97     CONSTRAINT  pkey_relocated PRIMARY KEY (gid, address),
    84     CONSTRAINT fkey_relocated_gid_domains FOREIGN KEY (gid)
    98     CONSTRAINT  fkey_relocated_gid_domain_data FOREIGN KEY (gid)
    85         REFERENCES domains (gid)
    99         REFERENCES domain_data (gid)
    86 );
   100 );
    87 
   101 
    88 CREATE OR REPLACE VIEW dovecot_password AS
   102 CREATE OR REPLACE VIEW dovecot_password AS
    89     SELECT local_part || '@' || domains.domainname AS "user",
   103     SELECT local_part || '@' || domain_name.domainname AS "user",
    90            passwd AS "password", smtp, pop3, imap, managesieve
   104            passwd AS "password", smtp, pop3, imap, managesieve
    91       FROM users
   105       FROM users
    92            LEFT JOIN domains USING (gid);
   106            LEFT JOIN domain_name USING (gid);
    93 
   107 
    94 CREATE OR REPLACE VIEW dovecot_user AS
   108 CREATE OR REPLACE VIEW dovecot_user AS
    95     SELECT local_part || '@' || domains.domainname AS userid,
   109     SELECT local_part || '@' || domain_name.domainname AS userid,
    96            uid, gid, domains.domaindir || '/' || uid AS home,
   110            uid, gid, domain_data.domaindir || '/' || uid AS home,
    97            '~/' || maillocation.maillocation AS mail
   111            '~/' || maillocation.maillocation AS mail
    98       FROM users
   112       FROM users
    99            LEFT JOIN domains USING (gid)
   113            LEFT JOIN domain_data USING (gid)
       
   114            LEFT JOIN domain_name USING (gid)
   100            LEFT JOIN maillocation USING (mid);
   115            LEFT JOIN maillocation USING (mid);
   101 
   116 
   102 CREATE OR REPLACE VIEW postfix_gid AS
   117 CREATE OR REPLACE VIEW postfix_gid AS
   103     SELECT gid, domainname
   118     SELECT gid, domainname
   104       FROM domains;
   119       FROM domain_name;
   105 
   120 
   106 CREATE OR REPLACE VIEW postfix_uid AS
   121 CREATE OR REPLACE VIEW postfix_uid AS
   107     SELECT local_part || '@' || domains.domainname AS address, uid
   122     SELECT local_part || '@' || domain_name.domainname AS address, uid
   108       FROM users
   123       FROM users
   109            LEFT JOIN domains USING (gid);
   124            LEFT JOIN domain_name USING (gid);
   110 
   125 
   111 CREATE OR REPLACE VIEW postfix_maildir AS
   126 CREATE OR REPLACE VIEW postfix_maildir AS
   112     SELECT local_part || '@' || domains.domainname AS address,
   127     SELECT local_part || '@' || domain_name.domainname AS address,
   113            domains.domaindir||'/'||uid||'/'||maillocation.maillocation||'/' AS maildir
   128            domain_data.domaindir||'/'||uid||'/'||maillocation.maillocation||'/'
       
   129            AS maildir
   114       FROM users
   130       FROM users
   115            LEFT JOIN domains USING (gid)
   131            LEFT JOIN domain_data USING (gid)
       
   132            LEFT JOIN domain_name USING (gid)
   116            LEFT JOIN maillocation USING (mid);
   133            LEFT JOIN maillocation USING (mid);
   117 
   134 
   118 CREATE OR REPLACE VIEW postfix_relocated AS
   135 CREATE OR REPLACE VIEW postfix_relocated AS
   119     SELECT address || '@' || domains.domainname AS address, destination
   136     SELECT address || '@' || domain_name.domainname AS address, destination
   120       FROM relocated
   137       FROM relocated
   121            LEFT JOIN domains USING (gid);
   138            LEFT JOIN domain_name USING (gid);
   122 
   139 
   123 CREATE OR REPLACE VIEW postfix_alias AS
   140 CREATE OR REPLACE VIEW postfix_alias AS
   124     SELECT address || '@' || domains.domainname AS address, destination, gid
   141     SELECT address || '@' || domain_name.domainname AS address, destination, gid
   125       FROM alias
   142       FROM alias
   126            LEFT JOIN domains USING (gid);
   143            LEFT JOIN domain_name USING (gid);
   127 
   144 
   128 CREATE OR REPLACE VIEW postfix_transport AS
   145 CREATE OR REPLACE VIEW postfix_transport AS
   129     SELECT local_part || '@' || domains.domainname AS address,
   146     SELECT local_part || '@' || domain_name.domainname AS address,
   130            transport.transport
   147            transport.transport
   131       FROM users
   148       FROM users
   132            LEFT JOIN transport USING (tid)
   149            LEFT JOIN transport USING (tid)
   133            LEFT JOIN domains USING (gid);
   150            LEFT JOIN domain_name USING (gid);
   134 
   151 
   135 CREATE OR REPLACE VIEW vmm_alias_count AS
   152 CREATE OR REPLACE VIEW vmm_alias_count AS
   136     SELECT count(DISTINCT address) AS aliases, gid
   153     SELECT count(DISTINCT address) AS aliases, gid
   137       FROM alias 
   154       FROM alias 
   138   GROUP BY gid;
   155   GROUP BY gid;
   139 
   156 
   140 CREATE OR REPLACE VIEW vmm_domain_info AS
   157 CREATE OR REPLACE VIEW vmm_domain_info AS
   141     SELECT gid, domainname, transport, domaindir, count(uid) AS accounts,
   158     SELECT gid, domainname, transport, domaindir,
   142            aliases
   159            count(uid) AS accounts,
   143       FROM domains
   160            aliases,
       
   161            (SELECT count(gid)
       
   162               FROM domain_name
       
   163              WHERE domain_name.gid = domain_data.gid
       
   164                AND NOT domain_name.is_primary) AS aliasdomains
       
   165       FROM domain_data
       
   166            LEFT JOIN domain_name USING (gid)
   144            LEFT JOIN transport USING (tid)
   167            LEFT JOIN transport USING (tid)
   145            LEFT JOIN users USING (gid)
   168            LEFT JOIN users USING (gid)
   146            LEFT JOIN vmm_alias_count USING (gid)
   169            LEFT JOIN vmm_alias_count USING (gid)
       
   170      WHERE domain_name.is_primary
   147   GROUP BY gid, domainname, transport, domaindir, aliases;
   171   GROUP BY gid, domainname, transport, domaindir, aliases;
       
   172 
       
   173 
       
   174 CREATE LANGUAGE plpgsql;
       
   175 
       
   176 
       
   177 CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$
       
   178 DECLARE
       
   179     primary_count bigint;
       
   180 BEGIN
       
   181     SELECT INTO primary_count count(gid) + NEW.is_primary::integer
       
   182       FROM domain_name
       
   183      WHERE domain_name.gid = NEW.gid
       
   184        AND is_primary;
       
   185 
       
   186     IF (primary_count > 1) THEN
       
   187         RAISE EXCEPTION 'There can only be one domain marked as primary.';
       
   188     END IF;
       
   189 
       
   190     RETURN NEW;
       
   191 END;
       
   192 $$ LANGUAGE plpgsql STABLE;
       
   193 
       
   194 
       
   195 CREATE TRIGGER primary_count BEFORE INSERT OR UPDATE ON domain_name
       
   196     FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();