update_tables_0.3.x-0.4.py
author Pascal Volk <neverseen@users.sourceforge.net>
Sun, 06 Apr 2008 21:36:00 +0000
changeset 14 ebc9b864bc9a
parent 13 1d9bf63a86f7
child 15 1607f7b2dba9
permissions -rwxr-xr-x
* 'update_config_0.3.x-0.4.py' * 'upgrade.sh' * 'UPGRADE' - added to repository * 'install.sh' - Removed $PF_GID * 'INSTALL' - Added some hints * 'VirtualMailManager/VirtualMailManager.py' - fixed an ugly bug
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
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   111
# Update VIEW dovecot_user
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   112
dbc.execute("""CREATE OR REPLACE VIEW dovecot_user AS
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   113
    SELECT local_part || '@' || domains.domainname AS userid,
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   114
           domains.domaindir || '/' || uid AS home, uid, gid
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   115
      FROM users
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   116
           LEFT JOIN domains USING (gid)""")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   117
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   118
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   119
# fix table users (Part II)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   120
dbc.execute("ALTER TABLE users DROP home")
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
dbc.execute("ALTER TABLE users DROP mail")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   123
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   124
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   125
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   126
# Replace VIEW postfix_transport
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   127
dbc.execute("DROP VIEW postfix_transport")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   128
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   129
dbc.execute("""CREATE OR REPLACE VIEW postfix_transport AS
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   130
    SELECT local_part || '@' || domains.domainname AS address,
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   131
           transport.transport
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   132
      FROM users
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   133
           LEFT JOIN transport USING (tid)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   134
           LEFT JOIN domains USING (gid)""")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   135
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   136
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   137
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   138
# fix table domains (Part II)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   139
dbc.execute("ALTER TABLE domains DROP transport")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   140
dbh.commit()
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
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   143
# fix table alias
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   144
dbc.execute('ALTER TABLE alias DROP id')
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   145
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   146
dbc.execute('DROP SEQUENCE alias_id')
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   147
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   148
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   149
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   150
# fix table relocated
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   151
dbc.execute('ALTER TABLE relocated DROP id')
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   152
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   153
dbc.execute('DROP SEQUENCE relocated_id')
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   154
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   155
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   156
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   157
# add new VIEW vmm_domain_info
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   158
dbc.execute("""CREATE OR REPLACE VIEW vmm_domain_info AS
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   159
    SELECT gid, domainname, transport, domaindir, count(uid) AS accounts,
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   160
           aliases
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   161
      FROM domains
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   162
           LEFT JOIN transport USING (tid)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   163
           LEFT JOIN users USING (gid)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   164
           LEFT JOIN vmm_alias_count USING (gid)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   165
  GROUP BY gid, domainname, transport, domaindir, aliases""")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   166
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   167
dbh.close()