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