update_tables_0.3.x-0.4.py
author Pascal Volk <neverseen@users.sourceforge.net>
Tue, 22 Apr 2008 07:34:02 +0000
changeset 22 576a4709a44f
parent 20 55146c78b3fb
permissions -rwxr-xr-x
* 'vmm.1' - extended (not completed yet)
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()
20
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
    73
for service in ['smtp', 'pop3', 'imap', 'managesieve']:
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
    74
    dbc.execute(
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
    75
            "ALTER TABLE users ADD %s boolean NOT NULL DEFAULT TRUE" % service)
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
    76
    dbh.commit()
13
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    77
dbc.execute("ALTER TABLE users ADD CONSTRAINT fkey_users_mid_maillocation \
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    78
 FOREIGN KEY (mid) REFERENCES maillocation (mid)")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    79
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    80
dbc.execute("ALTER TABLE users ADD CONSTRAINT fkey_users_tid_transport \
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    81
 FOREIGN KEY (tid) REFERENCES transport (tid)")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    82
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    83
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    84
dbc.execute("SELECT DISTINCT mail FROM users WHERE mail != 'Maildir'")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    85
res = dbc.fetchall()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    86
if len(res):
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    87
    for mailloc in res:
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    88
        dbc.execute("INSERT INTO maillocation(maillocation) VALUES (%s)",
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    89
                mailloc[0])
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    90
    dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    91
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    92
    dbc.execute("SELECT mid, maillocation FROM maillocation WHERE mid > 1")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    93
    res = dbc.fetchall()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    94
    for mid, mailloc in res:
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    95
        dbc.execute("UPDATE users SET mid = %s WHERE mail = %s", mid,
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    96
                maillocation)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    97
    dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    98
20
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
    99
dbc.execute("SELECT uid FROM users WHERE disabled")
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
   100
res = dbc.fetchall()
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
   101
if len(res):
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
   102
    for uid in res:
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
   103
        dbc.execute("UPDATE users SET smtp = FALSE, pop3 = FALSE, imap = FALSE,\
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
   104
 managesieve = FALSE WHERE uid = %s", uid[0])
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
   105
    dbh.commit()
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
   106
13
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   107
dbc.execute("SELECT gid, tid FROM domains")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   108
res = dbc.fetchall()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   109
for gid, tid in res:
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   110
    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
   111
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   112
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   113
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   114
# Update VIEW postfix_maildir
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   115
dbc.execute("""CREATE OR REPLACE VIEW postfix_maildir AS
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   116
     SELECT local_part || '@' || domains.domainname AS address,
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   117
     domains.domaindir||'/'||uid||'/'||maillocation.maillocation||'/' AS maildir
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   118
       FROM users
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   119
            LEFT JOIN domains USING (gid)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   120
            LEFT JOIN maillocation USING (mid)""")
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
15
1607f7b2dba9 * 'update_config_0.3.x-0.4.py'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 13
diff changeset
   123
# Replace VIEW dovecot_user
1607f7b2dba9 * 'update_config_0.3.x-0.4.py'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 13
diff changeset
   124
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
   125
dbh.commit()
13
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   126
dbc.execute("""CREATE OR REPLACE VIEW dovecot_user AS
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   127
    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
   128
           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
   129
           '~/' || maillocation.maillocation AS mail
13
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   130
      FROM users
15
1607f7b2dba9 * 'update_config_0.3.x-0.4.py'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 13
diff changeset
   131
           LEFT JOIN domains USING (gid)
1607f7b2dba9 * 'update_config_0.3.x-0.4.py'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 13
diff changeset
   132
           LEFT JOIN maillocation USING (mid);""")
13
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   133
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   134
20
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
   135
# Replace VIEW dovecot_password
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
   136
dbc.execute("DROP VIEW dovecot_password")
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
   137
dbh.commit()
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
   138
dbc.execute("""CREATE OR REPLACE VIEW dovecot_password AS
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
   139
    SELECT local_part || '@' || domains.domainname AS "user",
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
   140
           passwd AS "password", smtp, pop3, imap, managesieve
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
   141
      FROM users
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
   142
           LEFT JOIN domains USING (gid)""")
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
   143
dbh.commit()
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
   144
13
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   145
# fix table users (Part II)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   146
dbc.execute("ALTER TABLE users DROP home")
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
dbc.execute("ALTER TABLE users DROP mail")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   149
dbh.commit()
20
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
   150
dbc.execute("ALTER TABLE users DROP disabled")
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 15
diff changeset
   151
dbh.commit()
13
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
# Replace VIEW postfix_transport
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   155
dbc.execute("DROP VIEW postfix_transport")
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("""CREATE OR REPLACE VIEW postfix_transport AS
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   158
    SELECT local_part || '@' || domains.domainname AS address,
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   159
           transport.transport
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   160
      FROM users
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   161
           LEFT JOIN transport USING (tid)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   162
           LEFT JOIN domains USING (gid)""")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   163
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   164
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   165
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   166
# fix table domains (Part II)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   167
dbc.execute("ALTER TABLE domains DROP transport")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   168
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   169
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   170
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   171
# fix table alias
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   172
dbc.execute('ALTER TABLE alias DROP id')
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   173
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   174
dbc.execute('DROP SEQUENCE alias_id')
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   175
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   176
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   177
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   178
# fix table relocated
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   179
dbc.execute('ALTER TABLE relocated DROP id')
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   180
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   181
dbc.execute('DROP SEQUENCE relocated_id')
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   182
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   183
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   184
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   185
# add new VIEW vmm_domain_info
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   186
dbc.execute("""CREATE OR REPLACE VIEW vmm_domain_info AS
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   187
    SELECT gid, domainname, transport, domaindir, count(uid) AS accounts,
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   188
           aliases
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   189
      FROM domains
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   190
           LEFT JOIN transport USING (tid)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   191
           LEFT JOIN users USING (gid)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   192
           LEFT JOIN vmm_alias_count USING (gid)
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   193
  GROUP BY gid, domainname, transport, domaindir, aliases""")
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   194
dbh.commit()
1d9bf63a86f7 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   195
dbh.close()