update_tables_0.3.x-0.4.py
author Pascal Volk <neverseen@users.sourceforge.net>
Thu, 10 Apr 2008 03:24:12 +0000
changeset 15 1607f7b2dba9
parent 13 1d9bf63a86f7
child 20 55146c78b3fb
permissions -rwxr-xr-x
* 'update_config_0.3.x-0.4.py' - fixed code for view dovecot_user * 'create_tables.pgsql' - updated view dovecot_user (extra field »mail«) * 'UPGRADE' - fixed filename (update_0.3.x-0.4.sh -> upgrade.sh) - added hints for view perms and user_query * 'INSTALL' - replaced mail_extra_groups with mail_privileged_group - updated user_query
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
13
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     1
#!/usr/bin/env python
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     2
# -*- coding: UTF-8 -*-
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     3
# Copyright 2008 VEB IT
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     4
# See COPYING for distribution information.
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     5
# $Id$
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     6
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     7
from ConfigParser import ConfigParser
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     8
from pyPgSQL import PgSQL
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     9
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    10
cff = file('/usr/local/etc/vmm.cfg', 'r')
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    11
cf = ConfigParser()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    12
cf.readfp(cff)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    13
cff.close()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    14
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    15
dbh = PgSQL.connect(database=cf.get('database', 'name'),
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    16
        user=cf.get('database', 'user'), host=cf.get('database', 'host'),
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    17
        password=cf.get('database', 'pass'), client_encoding='utf8',
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    18
        unicode_results=True)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    19
dbc = dbh.cursor()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    20
dbc.execute("SET NAMES 'UTF8'")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    21
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    22
# Create new tables
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    23
queries = ("CREATE SEQUENCE transport_id",
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    24
        """CREATE TABLE transport (
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    25
    tid         bigint NOT NULL DEFAULT nextval('transport_id'),
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    26
    transport   varchar(270) NOT NULL,
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    27
    CONSTRAINT pkey_transport PRIMARY KEY (tid),
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    28
    CONSTRAINT ukey_transport UNIQUE (transport)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    29
)""",
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    30
        "INSERT INTO transport(transport) VALUES ('dovecot:')",
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    31
        "CREATE SEQUENCE maillocation_id",
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    32
        """CREATE TABLE maillocation(
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    33
    mid     bigint NOT NULL DEFAULT nextval('maillocation_id'),
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    34
    maillocation varchar(20) NOT NULL,
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    35
    CONSTRAINT pkey_maillocation PRIMARY KEY (mid),
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    36
    CONSTRAINT ukey_maillocation UNIQUE (maillocation)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    37
)""",
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    38
        "INSERT INTO maillocation(maillocation) VALUES ('Maildir')"
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    39
        )
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    40
for query in queries:
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    41
    dbc.execute(query)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    42
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    43
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    44
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    45
# fix table domains (Part I)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    46
dbc.execute('ALTER TABLE domains ADD tid bigint NOT NULL DEFAULT 1')
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    47
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    48
dbc.execute("ALTER TABLE domains ADD CONSTRAINT fkey_domains_tid_transport \
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    49
 FOREIGN KEY (tid) REFERENCES transport (tid)")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    50
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    51
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    52
dbc.execute("SELECT DISTINCT transport from domains \
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    53
 WHERE transport != 'dovecot:'")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    54
res = dbc.fetchall()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    55
if len(res):
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    56
    for trsp in res:
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    57
        dbc.execute("INSERT INTO transport(transport) VALUES (%s)", trsp[0])
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    58
    dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    59
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    60
    dbc.execute("SELECT tid, transport FROM transport WHERE tid > 1")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    61
    res = dbc.fetchall()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    62
    for tid, trsp in res:
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    63
        dbc.execute("UPDATE domains SET tid = %s WHERE transport = %s", tid,
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    64
                trsp)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    65
    dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    66
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    67
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    68
# fix table users (Part I)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    69
dbc.execute("ALTER TABLE users ADD mid bigint NOT NULL DEFAULT 1")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    70
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    71
dbc.execute("ALTER TABLE users ADD tid bigint NOT NULL DEFAULT 1")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    72
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    73
dbc.execute("ALTER TABLE users ADD CONSTRAINT fkey_users_mid_maillocation \
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    74
 FOREIGN KEY (mid) REFERENCES maillocation (mid)")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    75
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    76
dbc.execute("ALTER TABLE users ADD CONSTRAINT fkey_users_tid_transport \
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    77
 FOREIGN KEY (tid) REFERENCES transport (tid)")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    78
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    79
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    80
dbc.execute("SELECT DISTINCT mail FROM users WHERE mail != 'Maildir'")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    81
res = dbc.fetchall()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    82
if len(res):
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    83
    for mailloc in res:
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    84
        dbc.execute("INSERT INTO maillocation(maillocation) VALUES (%s)",
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    85
                mailloc[0])
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    86
    dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    87
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    88
    dbc.execute("SELECT mid, maillocation FROM maillocation WHERE mid > 1")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    89
    res = dbc.fetchall()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    90
    for mid, mailloc in res:
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    91
        dbc.execute("UPDATE users SET mid = %s WHERE mail = %s", mid,
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    92
                maillocation)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    93
    dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    94
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    95
dbc.execute("SELECT gid, tid FROM domains")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    96
res = dbc.fetchall()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    97
for gid, tid in res:
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    98
    dbc.execute("UPDATE users SET tid = %s WHERE gid = %s", tid, gid)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    99
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   100
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   101
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   102
# Update VIEW postfix_maildir
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   103
dbc.execute("""CREATE OR REPLACE VIEW postfix_maildir AS
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   104
     SELECT local_part || '@' || domains.domainname AS address,
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   105
     domains.domaindir||'/'||uid||'/'||maillocation.maillocation||'/' AS maildir
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   106
       FROM users
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   107
            LEFT JOIN domains USING (gid)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   108
            LEFT JOIN maillocation USING (mid)""")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   109
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   110
15
1607f7b2dba9 * 'update_config_0.3.x-0.4.py'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 13
diff changeset
   111
# Replace VIEW dovecot_user
1607f7b2dba9 * 'update_config_0.3.x-0.4.py'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 13
diff changeset
   112
dbc.execute("DROP VIEW dovecot_user")
1607f7b2dba9 * 'update_config_0.3.x-0.4.py'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 13
diff changeset
   113
dbh.commit()
13
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   114
dbc.execute("""CREATE OR REPLACE VIEW dovecot_user AS
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   115
    SELECT local_part || '@' || domains.domainname AS userid,
15
1607f7b2dba9 * 'update_config_0.3.x-0.4.py'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 13
diff changeset
   116
           uid, gid, domains.domaindir || '/' || uid AS home,
1607f7b2dba9 * 'update_config_0.3.x-0.4.py'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 13
diff changeset
   117
           '~/' || maillocation.maillocation AS mail
13
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   118
      FROM users
15
1607f7b2dba9 * 'update_config_0.3.x-0.4.py'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 13
diff changeset
   119
           LEFT JOIN domains USING (gid)
1607f7b2dba9 * 'update_config_0.3.x-0.4.py'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 13
diff changeset
   120
           LEFT JOIN maillocation USING (mid);""")
13
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   121
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   122
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   123
# fix table users (Part II)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   124
dbc.execute("ALTER TABLE users DROP home")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   125
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   126
dbc.execute("ALTER TABLE users DROP mail")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   127
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   128
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   129
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   130
# Replace VIEW postfix_transport
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   131
dbc.execute("DROP VIEW postfix_transport")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   132
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   133
dbc.execute("""CREATE OR REPLACE VIEW postfix_transport AS
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   134
    SELECT local_part || '@' || domains.domainname AS address,
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   135
           transport.transport
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   136
      FROM users
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   137
           LEFT JOIN transport USING (tid)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   138
           LEFT JOIN domains USING (gid)""")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   139
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   140
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   141
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   142
# fix table domains (Part II)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   143
dbc.execute("ALTER TABLE domains DROP transport")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   144
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   145
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   146
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   147
# fix table alias
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   148
dbc.execute('ALTER TABLE alias DROP id')
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   149
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   150
dbc.execute('DROP SEQUENCE alias_id')
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   151
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   152
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   153
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   154
# fix table relocated
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   155
dbc.execute('ALTER TABLE relocated DROP id')
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   156
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   157
dbc.execute('DROP SEQUENCE relocated_id')
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   158
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   159
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   160
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   161
# add new VIEW vmm_domain_info
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   162
dbc.execute("""CREATE OR REPLACE VIEW vmm_domain_info AS
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   163
    SELECT gid, domainname, transport, domaindir, count(uid) AS accounts,
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   164
           aliases
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   165
      FROM domains
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   166
           LEFT JOIN transport USING (tid)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   167
           LEFT JOIN users USING (gid)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   168
           LEFT JOIN vmm_alias_count USING (gid)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   169
  GROUP BY gid, domainname, transport, domaindir, aliases""")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   170
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   171
dbh.close()