update_tables_0.4-dev_r24.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:
20
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     1
#!/usr/bin/env python
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     2
# -*- coding: UTF-8 -*-
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     3
# Copyright 2008 VEB IT
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     4
# See COPYING for distribution information.
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     5
# $Id$
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     6
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     7
from ConfigParser import ConfigParser
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     8
from pyPgSQL import PgSQL
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     9
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    10
cff = file('/usr/local/etc/vmm.cfg', 'r')
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    11
cf = ConfigParser()
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    12
cf.readfp(cff)
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    13
cff.close()
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    14
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    15
dbh = PgSQL.connect(database=cf.get('database', 'name'),
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    16
        user=cf.get('database', 'user'), host=cf.get('database', 'host'),
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    17
        password=cf.get('database', 'pass'), client_encoding='utf8',
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    18
        unicode_results=True)
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    19
dbc = dbh.cursor()
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    20
dbc.execute("SET NAMES 'UTF8'")
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    21
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    22
for service in ['smtp', 'pop3', 'imap', 'managesieve']:
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    23
    dbc.execute(
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    24
            "ALTER TABLE users ADD %s boolean NOT NULL DEFAULT TRUE" % service)
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    25
    dbh.commit()
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    26
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    27
dbc.execute("SELECT uid FROM users WHERE disabled")
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    28
res = dbc.fetchall()
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    29
if len(res):
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    30
    for uid in res:
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    31
        dbc.execute("UPDATE users SET smtp = FALSE, pop3 = FALSE, imap = FALSE, managesieve = FALSE WHERE uid = %s", uid[0])
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    32
    dbh.commit()
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    33
dbc.execute("ALTER TABLE users DROP disabled")
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    34
dbh.commit()
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    35
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    36
dbc.execute("DROP VIEW dovecot_password")
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    37
dbh.commit()
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    38
dbc.execute("""CREATE OR REPLACE VIEW dovecot_password AS
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    39
    SELECT local_part || '@' || domains.domainname AS "user",
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    40
           passwd AS "password", smtp, pop3, imap, managesieve
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    41
      FROM users
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    42
           LEFT JOIN domains USING (gid)""")
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    43
dbh.commit()
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    44
dbh.close()
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    45
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    46
# print importnat information
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    47
print 
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    48
print "* set permissions for replaced views:"
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    49
print "connect to your database [psql %s] and execute:" % cf.get('database',
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    50
'name')
55146c78b3fb * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    51
print "GRANT SELECT ON dovecot_password TO your_dovecot_dbuser;"