update_tables_0.4.x-0.5.pgsql
author Pascal Volk <neverseen@users.sourceforge.net>
Wed, 27 Aug 2008 20:42:41 +0000
changeset 69 0c124160a991
parent 67 e4d25f50164d
child 71 4c94ba297698
permissions -rw-r--r--
* 'VirtualMailManager/VirtualMailManager.py' - Moved unused RE_MAILLOCATION to VirtualMailManager/MailLocation.py * 'VirtualMailManager/MailLocation.py' - MailLocation.__init__() checks 'maillocation' for validity if given * 'VirtualMailManager/Config.py' - Overwritten ConfigParser.get() -> string as Config.get() -> unicode * 'po/de.po' * 'po/vmm.pot' - updated
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     1
-- $Id$ 
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     2
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     3
SET client_encoding = 'UTF8';
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     4
SET client_min_messages = warning;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     5
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     6
ALTER SEQUENCE domains_gid RENAME TO domain_gid;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     7
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     8
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     9
CREATE TABLE domain_data (
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    10
    gid         bigint NOT NULL DEFAULT nextval('domain_gid'),
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    11
    tid         bigint NOT NULL DEFAULT 1,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    12
    domaindir   varchar(40) NOT NULL,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    13
    CONSTRAINT  pkey_domain_data PRIMARY KEY (gid),
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    14
    CONSTRAINT  fkey_domain_data_tid_transport FOREIGN KEY (tid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    15
        REFERENCES transport (tid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    16
);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    17
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    18
CREATE TABLE domain_name (
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    19
    domainname  varchar(255) NOT NULL,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    20
    gid         bigint NOT NULL,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    21
    is_primary  boolean NOT NULL,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    22
    CONSTRAINT  pkey_domain_name PRIMARY KEY (domainname),
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    23
    CONSTRAINT  fkey_domain_name_gid_domain_data FOREIGN KEY (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    24
        REFERENCES domain_data (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    25
);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    26
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    27
INSERT INTO domain_data (gid, tid, domaindir) 
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    28
    SELECT gid, tid, domaindir
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    29
      FROM domains;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    30
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    31
INSERT INTO domain_name (domainname, gid, is_primary) 
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    32
    SELECT domainname, gid, TRUE
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    33
      FROM domains;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    34
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    35
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    36
ALTER TABLE users DROP CONSTRAINT fkey_users_gid_domains;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    37
ALTER TABLE users ADD CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    38
    REFERENCES domain_data (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    39
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    40
ALTER TABLE alias DROP CONSTRAINT fkey_alias_gid_domains;
67
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
    41
ALTER TABLE alias DROP CONSTRAINT pkey_alias;
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    42
ALTER TABLE alias ADD CONSTRAINT fkey_alias_gid_domain_data FOREIGN KEY (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    43
    REFERENCES domain_data (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    44
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    45
ALTER TABLE relocated DROP CONSTRAINT fkey_relocated_gid_domains;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    46
ALTER TABLE relocated ADD CONSTRAINT fkey_relocated_gid_domain_data
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    47
    FOREIGN KEY (gid) REFERENCES domain_data (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    48
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    49
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    50
CREATE OR REPLACE VIEW dovecot_password AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    51
    SELECT local_part || '@' || domain_name.domainname AS "user",
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    52
           passwd AS "password", smtp, pop3, imap, managesieve
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    53
      FROM users
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    54
           LEFT JOIN domain_name USING (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    55
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    56
CREATE OR REPLACE VIEW dovecot_user AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    57
    SELECT local_part || '@' || domain_name.domainname AS userid,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    58
           uid, gid, domain_data.domaindir || '/' || uid AS home,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    59
           '~/' || maillocation.maillocation AS mail
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    60
      FROM users
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    61
           LEFT JOIN domain_data USING (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    62
           LEFT JOIN domain_name USING (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    63
           LEFT JOIN maillocation USING (mid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    64
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    65
CREATE OR REPLACE VIEW postfix_gid AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    66
    SELECT gid, domainname
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    67
      FROM domain_name;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    68
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    69
CREATE OR REPLACE VIEW postfix_uid AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    70
    SELECT local_part || '@' || domain_name.domainname AS address, uid
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    71
      FROM users
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    72
           LEFT JOIN domain_name USING (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    73
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    74
CREATE OR REPLACE VIEW postfix_maildir AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    75
    SELECT local_part || '@' || domain_name.domainname AS address,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    76
           domain_data.domaindir||'/'||uid||'/'||maillocation.maillocation||'/'
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    77
           AS maildir
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    78
      FROM users
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    79
           LEFT JOIN domain_data USING (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    80
           LEFT JOIN domain_name USING (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    81
           LEFT JOIN maillocation USING (mid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    82
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    83
CREATE OR REPLACE VIEW postfix_relocated AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    84
    SELECT address || '@' || domain_name.domainname AS address, destination
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    85
      FROM relocated
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    86
           LEFT JOIN domain_name USING (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    87
67
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
    88
DROP VIEW postfix_alias;
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
    89
DROP VIEW vmm_domain_info;
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
    90
DROP VIEW vmm_alias_count;
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
    91
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
    92
ALTER TABLE alias ALTER address TYPE varchar(64);
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
    93
ALTER TABLE alias ADD CONSTRAINT pkey_alias 
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
    94
    PRIMARY KEY (gid, address, destination);
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
    95
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    96
CREATE OR REPLACE VIEW postfix_alias AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    97
    SELECT address || '@' || domain_name.domainname AS address, destination, gid
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    98
      FROM alias
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    99
           LEFT JOIN domain_name USING (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   100
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   101
CREATE OR REPLACE VIEW postfix_transport AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   102
    SELECT local_part || '@' || domain_name.domainname AS address,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   103
           transport.transport
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   104
      FROM users
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   105
           LEFT JOIN transport USING (tid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   106
           LEFT JOIN domain_name USING (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   107
67
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
   108
CREATE OR REPLACE VIEW vmm_alias_count AS
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
   109
    SELECT count(DISTINCT address) AS aliases, gid
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
   110
      FROM alias 
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
   111
  GROUP BY gid;
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
   112
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   113
CREATE OR REPLACE VIEW vmm_domain_info AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   114
    SELECT gid, domainname, transport, domaindir,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   115
           count(uid) AS accounts,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   116
           aliases,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   117
           (SELECT count(gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   118
              FROM domain_name
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   119
             WHERE domain_name.gid = domain_data.gid
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   120
               AND NOT domain_name.is_primary) AS aliasdomains
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   121
      FROM domain_data
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   122
           LEFT JOIN domain_name USING (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   123
           LEFT JOIN transport USING (tid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   124
           LEFT JOIN users USING (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   125
           LEFT JOIN vmm_alias_count USING (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   126
     WHERE domain_name.is_primary
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   127
  GROUP BY gid, domainname, transport, domaindir, aliases;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   128
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   129
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   130
DROP TABLE domains;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   131
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   132
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   133
CREATE LANGUAGE plpgsql;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   134
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   135
CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   136
DECLARE
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   137
    primary_count bigint;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   138
BEGIN
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   139
    SELECT INTO primary_count count(gid) + NEW.is_primary::integer
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   140
      FROM domain_name
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   141
     WHERE domain_name.gid = NEW.gid
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   142
       AND is_primary;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   143
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   144
    IF (primary_count > 1) THEN
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   145
        RAISE EXCEPTION 'There can only be one domain marked as primary.';
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   146
    END IF;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   147
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   148
    RETURN NEW;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   149
END;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   150
$$ LANGUAGE plpgsql STABLE;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   151
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   152
CREATE TRIGGER primary_count BEFORE INSERT OR UPDATE ON domain_name
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   153
    FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();