pgsql/create_tables.pgsql
author martin f. krafft <madduck@madduck.net>
Sat, 07 Apr 2012 00:45:57 +0200
branchv0.6.x
changeset 492 e5c2b3647971
parent 437 9823548b2717
child 500 5ccc9c6e5193
permissions -rw-r--r--
Modify address check query to obtain well-defined result The way in which UNION does not yield the desired result, because (a) UNION merges results and (b) the result order is undefined. This patch changes the query to select the counts as columns and hence provides a well-defined order.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
     1
SET client_encoding = 'UTF8';
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
     2
SET client_min_messages = warning;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
     3
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
     4
6
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
     5
CREATE SEQUENCE transport_id;
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
     6
297
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
     7
CREATE SEQUENCE mailboxformat_id;
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
     8
382
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
     9
CREATE SEQUENCE quotalimit_id;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    10
8
7e3ce56f49e6 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 7
diff changeset
    11
CREATE SEQUENCE maillocation_id;
6
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
    12
437
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    13
CREATE SEQUENCE service_set_id;
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    14
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    15
CREATE SEQUENCE domain_gid
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    16
    START WITH 70000
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    17
    INCREMENT BY 1
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    18
    MINVALUE 70000
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    19
    MAXVALUE 4294967294
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    20
    NO CYCLE;
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    21
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    22
CREATE SEQUENCE users_uid
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    23
    START WITH 70000
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    24
    INCREMENT BY 1
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    25
    MINVALUE 70000
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    26
    MAXVALUE 4294967294
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    27
    NO CYCLE;
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    28
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    29
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    30
CREATE TABLE transport (
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    31
    tid         bigint NOT NULL DEFAULT nextval('transport_id'),
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    32
    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
    33
    CONSTRAINT  pkey_transport PRIMARY KEY (tid),
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    34
    CONSTRAINT  ukey_transport UNIQUE (transport)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    35
);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    36
-- Insert default transport
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    37
INSERT INTO transport(transport) VALUES ('dovecot:');
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    38
297
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    39
CREATE TABLE mailboxformat (
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    40
    fid         bigint NOT NULL DEFAULT nextval('mailboxformat_id'),
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    41
    format      varchar(20) NOT NULL,
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    42
    CONSTRAINT  pkey_mailboxformat PRIMARY KEY (fid),
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    43
    CONSTRAINT  ukey_mailboxformat UNIQUE (format)
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    44
);
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    45
-- Insert supported mailbox formats
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    46
INSERT INTO mailboxformat(format) VALUES ('maildir');
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    47
INSERT INTO mailboxformat(format) VALUES ('mdbox');
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    48
INSERT INTO mailboxformat(format) VALUES ('sdbox');
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    49
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    50
CREATE TABLE maillocation (
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    51
    mid         bigint NOT NULL DEFAULT nextval('maillocation_id'),
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    52
    fid         bigint NOT NULL DEFAULT 1,
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    53
    directory   varchar(20) NOT NULL,
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    54
    extra       varchar(1024),
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    55
    CONSTRAINT  pkey_maillocation PRIMARY KEY (mid),
297
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    56
    CONSTRAINT  fkey_maillocation_fid_mailboxformat FOREIGN KEY (fid)
368
be4bd77dbe57 pgsql/create_tables{,-dovecot-1.2.x}.pgsql: Fixed syntax errors
Tobias Berling <t-obi@users.sourceforge.net>
parents: 297
diff changeset
    57
        REFERENCES mailboxformat (fid)
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    58
);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    59
-- Insert default Maildir-folder name
297
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    60
INSERT INTO maillocation(directory) VALUES ('Maildir');
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    61
382
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    62
CREATE TABLE quotalimit (
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    63
    qid         bigint NOT NULL DEFAULT nextval('quotalimit_id'),
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    64
    bytes       bigint NOT NULL,
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    65
    messages    integer NOT NULL DEFAULT 0,
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    66
    CONSTRAINT  pkey_quotalimit PRIMARY KEY (qid),
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    67
    CONSTRAINT  ukey_quotalimit UNIQUE (bytes, messages)
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    68
);
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    69
-- Insert default (non) quota limit
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    70
INSERT INTO quotalimit(bytes, messages) VALUES (0, 0);
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    71
437
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    72
CREATE TABLE service_set (
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    73
    ssid        bigint NOT NULL DEFAULT nextval('service_set_id'),
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    74
    smtp        boolean NOT NULL DEFAULT TRUE,
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    75
    pop3        boolean NOT NULL DEFAULT TRUE,
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    76
    imap        boolean NOT NULL DEFAULT TRUE,
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    77
    managesieve boolean NOT NULL DEFAULT TRUE,
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    78
    CONSTRAINT  pkey_service_set PRIMARY KEY (ssid),
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    79
    CONSTRAINT  ukey_service_set UNIQUE (smtp, pop3, imap, managesieve)
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    80
);
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    81
-- Insert all possible service combinations
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    82
COPY service_set (smtp, pop3, imap, managesieve) FROM stdin;
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    83
TRUE	TRUE	TRUE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    84
FALSE	TRUE	TRUE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    85
TRUE	FALSE	TRUE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    86
FALSE	FALSE	TRUE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    87
TRUE	TRUE	FALSE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    88
FALSE	TRUE	FALSE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    89
TRUE	FALSE	FALSE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    90
FALSE	FALSE	FALSE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    91
TRUE	TRUE	TRUE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    92
FALSE	TRUE	TRUE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    93
TRUE	FALSE	TRUE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    94
FALSE	FALSE	TRUE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    95
TRUE	TRUE	FALSE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    96
FALSE	TRUE	FALSE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    97
TRUE	FALSE	FALSE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    98
FALSE	FALSE	FALSE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    99
\.
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   100
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   101
CREATE TABLE domain_data (
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   102
    gid         bigint NOT NULL DEFAULT nextval('domain_gid'),
437
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   103
    qid         bigint NOT NULL DEFAULT 1, -- default quota limit
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   104
    ssid        bigint NOT NULL DEFAULT 1, -- default service_set
257
5b8fde01e4f0 VMM/Alias.py: Replaced some %r with '%s'.
Tobias Berling <mail@tobiasberling.de>
parents: 160
diff changeset
   105
    tid         bigint NOT NULL DEFAULT 1, -- default transport
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   106
    domaindir   varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   107
    CONSTRAINT  pkey_domain_data PRIMARY KEY (gid),
437
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   108
    CONSTRAINT  fkey_domain_data_qid_quotalimit FOREIGN KEY (qid)
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   109
        REFERENCES quotalimit (qid),
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   110
    CONSTRAINT  fkey_domain_data_ssid_service_set FOREIGN KEY (ssid)
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   111
        REFERENCES service_set (ssid),
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   112
    CONSTRAINT  fkey_domain_data_tid_transport FOREIGN KEY (tid)
437
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   113
        REFERENCES transport (tid)
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   114
);
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   115
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   116
CREATE TABLE domain_name (
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   117
    domainname  varchar(255) NOT NULL,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   118
    gid         bigint NOT NULL,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   119
    is_primary  boolean NOT NULL,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   120
    CONSTRAINT  pkey_domain_name PRIMARY KEY (domainname),
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   121
    CONSTRAINT  fkey_domain_name_gid_domain_data FOREIGN KEY (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   122
        REFERENCES domain_data (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   123
);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   124
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   125
CREATE TABLE users (
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   126
    local_part  varchar(64) NOT NULL,-- only localpart w/o '@'
297
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
   127
    passwd      varchar(270) NOT NULL,
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   128
    name        varchar(128) NULL,
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   129
    uid         bigint NOT NULL DEFAULT nextval('users_uid'),
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   130
    gid         bigint NOT NULL,
6
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
   131
    mid         bigint NOT NULL DEFAULT 1,
437
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   132
    qid         bigint NOT NULL DEFAULT 1,
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   133
    ssid        bigint NOT NULL DEFAULT 1,
6
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
   134
    tid         bigint NOT NULL DEFAULT 1,
71
4c94ba297698 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 67
diff changeset
   135
    CONSTRAINT  pkey_users PRIMARY KEY (local_part, gid),
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   136
    CONSTRAINT  ukey_users_uid UNIQUE (uid),
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   137
    CONSTRAINT  fkey_users_gid_domain_data FOREIGN KEY (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   138
        REFERENCES domain_data (gid),
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   139
    CONSTRAINT  fkey_users_mid_maillocation FOREIGN KEY (mid)
8
7e3ce56f49e6 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 7
diff changeset
   140
        REFERENCES maillocation (mid),
437
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   141
    CONSTRAINT  fkey_users_qid_quotalimit FOREIGN KEY (qid)
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   142
        REFERENCES quotalimit (qid),
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   143
    CONSTRAINT fkey_users_ssid_service_set FOREIGN KEY (ssid)
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   144
        REFERENCES service_set (ssid),
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   145
    CONSTRAINT  fkey_users_tid_transport FOREIGN KEY (tid)
437
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   146
        REFERENCES transport (tid)
382
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   147
);
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   148
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   149
CREATE TABLE userquota_11 (
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   150
    uid         bigint NOT NULL,
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   151
    path        varchar(16) NOT NULL,
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   152
    current     bigint NOT NULL DEFAULT 0,
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   153
    CONSTRAINT  pkey_userquota_11 PRIMARY KEY (uid, path),
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   154
    CONSTRAINT  fkey_userquota_11_uid_users FOREIGN KEY (uid)
387
05dc4e1f8dff pgsql/{create,update}_tables*: Added the ON DELETE CASCADE clause
Pascal Volk <neverseen@users.sourceforge.net>
parents: 382
diff changeset
   155
        REFERENCES users (uid) ON DELETE CASCADE
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   156
);
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   157
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   158
CREATE TABLE alias (
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   159
    gid         bigint NOT NULL,
67
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 63
diff changeset
   160
    address     varchar(64) NOT NULL,-- only localpart w/o '@'
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   161
    destination varchar(320) NOT NULL,
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   162
    CONSTRAINT  pkey_alias PRIMARY KEY (gid, address, destination),
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   163
    CONSTRAINT  fkey_alias_gid_domain_data FOREIGN KEY (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   164
        REFERENCES domain_data (gid)
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   165
);
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   166
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   167
CREATE TABLE relocated (
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   168
    gid         bigint NOT NULL,
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   169
    address     varchar(64) NOT NULL,
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   170
    destination varchar(320) NOT NULL,
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   171
    CONSTRAINT  pkey_relocated PRIMARY KEY (gid, address),
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   172
    CONSTRAINT  fkey_relocated_gid_domain_data FOREIGN KEY (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   173
        REFERENCES domain_data (gid)
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   174
);
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   175
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   176
CREATE OR REPLACE VIEW dovecot_password AS
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   177
    SELECT local_part || '@' || domain_name.domainname AS "user",
20
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
   178
           passwd AS "password", smtp, pop3, imap, managesieve
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   179
      FROM users
437
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   180
           LEFT JOIN domain_name USING (gid)
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   181
           LEFT JOIN service_set USING (ssid);
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   182
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   183
CREATE OR REPLACE VIEW dovecot_user AS
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   184
    SELECT local_part || '@' || domain_name.domainname AS userid,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   185
           uid, gid, domain_data.domaindir || '/' || uid AS home,
297
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
   186
           mailboxformat.format || ':~/' || maillocation.directory AS mail
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   187
      FROM users
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   188
           LEFT JOIN domain_data USING (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   189
           LEFT JOIN domain_name USING (gid)
297
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
   190
           LEFT JOIN maillocation USING (mid)
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
   191
           LEFT JOIN mailboxformat USING (fid);
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   192
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   193
CREATE OR REPLACE VIEW postfix_gid AS
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   194
    SELECT gid, domainname
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   195
      FROM domain_name;
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   196
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   197
CREATE OR REPLACE VIEW postfix_uid AS
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   198
    SELECT local_part || '@' || domain_name.domainname AS address, uid
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   199
      FROM users
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   200
           LEFT JOIN domain_name USING (gid);
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   201
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   202
CREATE OR REPLACE VIEW postfix_maildir AS
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   203
    SELECT local_part || '@' || domain_name.domainname AS address,
297
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
   204
           domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/'
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   205
           AS maildir
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   206
      FROM users
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   207
           LEFT JOIN domain_data USING (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   208
           LEFT JOIN domain_name USING (gid)
8
7e3ce56f49e6 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 7
diff changeset
   209
           LEFT JOIN maillocation USING (mid);
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   210
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   211
CREATE OR REPLACE VIEW postfix_relocated AS
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   212
    SELECT address || '@' || domain_name.domainname AS address, destination
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   213
      FROM relocated
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   214
           LEFT JOIN domain_name USING (gid);
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   215
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   216
CREATE OR REPLACE VIEW postfix_alias AS
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   217
    SELECT address || '@' || domain_name.domainname AS address, destination, gid
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   218
      FROM alias
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   219
           LEFT JOIN domain_name USING (gid);
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   220
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   221
CREATE OR REPLACE VIEW postfix_transport AS
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   222
    SELECT local_part || '@' || domain_name.domainname AS address,
6
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
   223
           transport.transport
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
   224
      FROM users
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
   225
           LEFT JOIN transport USING (tid)
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   226
           LEFT JOIN domain_name USING (gid);
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   227
9
e3d3dbeb5b84 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 8
diff changeset
   228
CREATE OR REPLACE VIEW vmm_domain_info AS
391
8217ddd5220d pgsql: Updated view vmm_domain_info. No longer select data we have already.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 388
diff changeset
   229
    SELECT gid, count(uid) AS accounts,
80
5dedc673524e * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 71
diff changeset
   230
           (SELECT count(DISTINCT address)
5dedc673524e * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 71
diff changeset
   231
              FROM alias
5dedc673524e * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 71
diff changeset
   232
             WHERE alias.gid = domain_data.gid) AS aliases,
5dedc673524e * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 71
diff changeset
   233
           (SELECT count(gid)
5dedc673524e * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 71
diff changeset
   234
              FROM relocated
5dedc673524e * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 71
diff changeset
   235
             WHERE relocated.gid = domain_data.gid) AS relocated,
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   236
           (SELECT count(gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   237
              FROM domain_name
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   238
             WHERE domain_name.gid = domain_data.gid
391
8217ddd5220d pgsql: Updated view vmm_domain_info. No longer select data we have already.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 388
diff changeset
   239
               AND NOT domain_name.is_primary) AS aliasdomains
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   240
      FROM domain_data
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   241
           LEFT JOIN domain_name USING (gid)
9
e3d3dbeb5b84 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 8
diff changeset
   242
           LEFT JOIN users USING (gid)
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   243
     WHERE domain_name.is_primary
391
8217ddd5220d pgsql: Updated view vmm_domain_info. No longer select data we have already.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 388
diff changeset
   244
  GROUP BY gid;
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   245
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   246
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   247
CREATE LANGUAGE plpgsql;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   248
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   249
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   250
CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   251
DECLARE
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   252
    primary_count bigint;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   253
BEGIN
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   254
    SELECT INTO primary_count count(gid) + NEW.is_primary::integer
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   255
      FROM domain_name
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   256
     WHERE domain_name.gid = NEW.gid
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   257
       AND is_primary;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   258
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   259
    IF (primary_count > 1) THEN
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   260
        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
   261
    END IF;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   262
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   263
    RETURN NEW;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   264
END;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   265
$$ LANGUAGE plpgsql STABLE;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   266
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   267
63
9b627307f4a8 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
   268
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
   269
    FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();
63
9b627307f4a8 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
   270
9b627307f4a8 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
   271
CREATE TRIGGER primary_count_upd AFTER UPDATE ON domain_name
9b627307f4a8 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
   272
    FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();
382
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   273
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   274
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   275
CREATE OR REPLACE FUNCTION merge_userquota_11() RETURNS TRIGGER AS $$
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   276
BEGIN
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   277
    UPDATE userquota_11
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   278
       SET current = current + NEW.current
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   279
     WHERE uid = NEW.uid AND path = NEW.path;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   280
    IF found THEN
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   281
        RETURN NULL;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   282
    ELSE
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   283
        RETURN NEW;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   284
    END IF;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   285
END;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   286
$$ LANGUAGE plpgsql;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   287
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   288
CREATE TRIGGER mergeuserquota_11 BEFORE INSERT ON userquota_11
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   289
    FOR EACH ROW EXECUTE PROCEDURE merge_userquota_11();