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