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