create_tables.pgsql
author Pascal Volk <neverseen@users.sourceforge.net>
Sun, 02 Mar 2008 22:41:59 +0000
changeset 10 8f6e34549c26
parent 9 e3d3dbeb5b84
child 13 1d9bf63a86f7
permissions -rw-r--r--
* 'pgsql-transport.cf' - fixed query * 'pgsql-smtpd_sender_login_maps.cf' - added missing ' at end of query
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
6
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
     3
CREATE SEQUENCE transport_id;
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
     4
CREATE TABLE transport (
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
     5
    tid         bigint NOT NULL DEFAULT nextval('transport_id'),
8
7e3ce56f49e6 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 7
diff changeset
     6
    transport   varchar(270) NOT NULL, -- smtps:[255-char.host.name:50025]
6
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
     7
    CONSTRAINT pkey_transport PRIMARY KEY (tid),
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
     8
    CONSTRAINT ukey_transport UNIQUE (transport)
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
     9
);
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
    10
-- Insert default transport
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
    11
INSERT INTO transport(transport) VALUES ('dovecot:');
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
    12
8
7e3ce56f49e6 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 7
diff changeset
    13
CREATE SEQUENCE maillocation_id;
7e3ce56f49e6 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 7
diff changeset
    14
CREATE TABLE maillocation(
7e3ce56f49e6 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 7
diff changeset
    15
    mid     bigint NOT NULL DEFAULT nextval('maillocation_id'),
7e3ce56f49e6 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 7
diff changeset
    16
    maillocation varchar(20) NOT NULL,
7e3ce56f49e6 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 7
diff changeset
    17
    CONSTRAINT pkey_maillocation PRIMARY KEY (mid),
7e3ce56f49e6 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 7
diff changeset
    18
    CONSTRAINT ukey_maillocation UNIQUE (maillocation)
6
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
    19
);
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
    20
-- Insert default Maildir-folder name
8
7e3ce56f49e6 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 7
diff changeset
    21
INSERT INTO maillocation(maillocation) VALUES ('Maildir');
6
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
    22
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    23
CREATE SEQUENCE domains_gid
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    24
    START WITH 70000
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    25
    INCREMENT BY 1
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    26
    MINVALUE 70000
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    27
    MAXVALUE 4294967294
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    28
    NO CYCLE;
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    29
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    30
CREATE SEQUENCE users_uid
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    31
    START WITH 70000
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    32
    INCREMENT BY 1
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    33
    MINVALUE 70000
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    34
    MAXVALUE 4294967294
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    35
    NO CYCLE;
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    36
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    37
CREATE TABLE domains (
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    38
    gid         bigint NOT NULL DEFAULT nextval('domains_gid'),
8
7e3ce56f49e6 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 7
diff changeset
    39
    tid         bigint NOT NULL DEFAULT 1, -- defualt transport
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    40
    domainname  varchar(255) NOT NULL,
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    41
    domaindir   varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    42
    CONSTRAINT pkey_domains PRIMARY KEY (gid),
8
7e3ce56f49e6 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 7
diff changeset
    43
    CONSTRAINT ukey_domains UNIQUE (domainname),
7e3ce56f49e6 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 7
diff changeset
    44
    CONSTRAINT fkey_domains_tid_transport FOREIGN KEY (tid)
7e3ce56f49e6 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 7
diff changeset
    45
        REFERENCES transport (tid)
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    46
);
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    47
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    48
CREATE TABLE users (
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    49
    local_part  varchar(64) NOT NULL,-- only localpart w/o '@'
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    50
    passwd      varchar(74) NOT NULL,-- {CRAM-MD5}+64hex numbers
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    51
    name        varchar(128) NULL,
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    52
    uid         bigint NOT NULL DEFAULT nextval('users_uid'),
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    53
    gid         bigint NOT NULL,
6
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
    54
    mid         bigint NOT NULL DEFAULT 1,
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
    55
    tid         bigint NOT NULL DEFAULT 1,
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    56
    disabled    boolean NOT NULL DEFAULT FALSE,
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    57
    CONSTRAINT pkye_users PRIMARY KEY (local_part, gid),
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    58
    CONSTRAINT ukey_users_uid UNIQUE (uid),
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    59
    CONSTRAINT fkey_users_gid_domains FOREIGN KEY (gid)
6
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
    60
        REFERENCES domains (gid),
8
7e3ce56f49e6 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 7
diff changeset
    61
    CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid)
7e3ce56f49e6 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 7
diff changeset
    62
        REFERENCES maillocation (mid),
6
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
    63
    CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid)
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
    64
        REFERENCES transport (tid)
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    65
);
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    66
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    67
CREATE TABLE alias (
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    68
    gid         bigint NOT NULL,
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    69
    address     varchar(256) NOT NULL,
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    70
    destination varchar(320) NOT NULL,
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    71
    CONSTRAINT pkey_alias PRIMARY KEY (gid, address, destination),
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    72
    CONSTRAINT fkey_alias_gid_domains FOREIGN KEY (gid)
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    73
        REFERENCES domains (gid)
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    74
);
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    75
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    76
CREATE TABLE relocated (
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    77
    gid         bigint NOT NULL,
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    78
    address     varchar(64) NOT NULL,
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    79
    destination varchar(320) NOT NULL,
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    80
    CONSTRAINT pkey_relocated PRIMARY KEY (gid, address),
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    81
    CONSTRAINT fkey_relocated_gid_domains FOREIGN KEY (gid)
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    82
        REFERENCES domains (gid)
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
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    85
CREATE OR REPLACE VIEW dovecot_password AS
6
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
    86
    SELECT local_part || '@' || domains.domainname AS "user",
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
    87
           passwd AS "password"
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    88
      FROM users
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    89
           LEFT JOIN domains USING (gid);
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    90
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    91
CREATE OR REPLACE VIEW dovecot_user AS
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    92
    SELECT local_part || '@' || domains.domainname AS userid,
6
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
    93
           domains.domaindir || '/' || uid AS home,
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    94
           uid,
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    95
           gid
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    96
      FROM users
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    97
           LEFT JOIN domains USING (gid);
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    98
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    99
CREATE OR REPLACE VIEW postfix_gid AS
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   100
    SELECT gid, domainname
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   101
      FROM domains;
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   102
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   103
CREATE OR REPLACE VIEW postfix_uid AS
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   104
    SELECT local_part || '@' || domains.domainname AS address,
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   105
           uid
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   106
      FROM users
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   107
           LEFT JOIN domains USING (gid);
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   108
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   109
CREATE OR REPLACE VIEW postfix_maildir AS
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   110
    SELECT local_part || '@' || domains.domainname AS address,
8
7e3ce56f49e6 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 7
diff changeset
   111
           domains.domaindir||'/'||uid||'/'||maillocation.maillocation||'/' AS maildir
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   112
      FROM users
6
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
   113
           LEFT JOIN domains USING (gid)
8
7e3ce56f49e6 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 7
diff changeset
   114
           LEFT JOIN maillocation USING (mid);
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   115
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   116
CREATE OR REPLACE VIEW postfix_relocated AS
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   117
    SELECT address || '@' || domains.domainname AS address, destination
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   118
      FROM relocated
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   119
           LEFT JOIN domains USING (gid);
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_alias AS
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   122
    SELECT address || '@' || domains.domainname AS address, destination, gid
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   123
      FROM alias
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   124
           LEFT JOIN domains USING (gid);
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_transport AS
6
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
   127
    SELECT local_part || '@' || domains.domainname AS address,
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
   128
           transport.transport
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
   129
      FROM users
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
   130
           LEFT JOIN transport USING (tid)
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
   131
           LEFT JOIN domains USING (gid);
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   132
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   133
CREATE OR REPLACE VIEW vmm_alias_count AS
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   134
    SELECT count(DISTINCT address) AS aliases, gid
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   135
      FROM alias 
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   136
  GROUP BY gid;
9
e3d3dbeb5b84 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 8
diff changeset
   137
e3d3dbeb5b84 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 8
diff changeset
   138
CREATE OR REPLACE VIEW vmm_domain_info AS
e3d3dbeb5b84 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 8
diff changeset
   139
    SELECT gid, domainname, transport, domaindir, count(uid) AS accounts,
e3d3dbeb5b84 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 8
diff changeset
   140
           aliases
e3d3dbeb5b84 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 8
diff changeset
   141
      FROM domains
e3d3dbeb5b84 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 8
diff changeset
   142
           LEFT JOIN transport USING (tid)
e3d3dbeb5b84 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 8
diff changeset
   143
           LEFT JOIN users USING (gid)
e3d3dbeb5b84 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 8
diff changeset
   144
           LEFT JOIN vmm_alias_count USING (gid)
e3d3dbeb5b84 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 8
diff changeset
   145
  GROUP BY gid, domainname, transport, domaindir, aliases;