#!/usr/bin/env python
# -*- coding: UTF-8 -*-
# Copyright 2008 VEB IT
# See COPYING for distribution information.
# $Id$

from ConfigParser import ConfigParser
from pyPgSQL import PgSQL

cff = file('/usr/local/etc/vmm.cfg', 'r')
cf = ConfigParser()
cf.readfp(cff)
cff.close()

dbh = PgSQL.connect(database=cf.get('database', 'name'),
        user=cf.get('database', 'user'), host=cf.get('database', 'host'),
        password=cf.get('database', 'pass'), client_encoding='utf8',
        unicode_results=True)
dbc = dbh.cursor()
dbc.execute("SET NAMES 'UTF8'")

for service in ['smtp', 'pop3', 'imap', 'managesieve']:
    dbc.execute(
            "ALTER TABLE users ADD %s boolean NOT NULL DEFAULT TRUE" % service)
    dbh.commit()

dbc.execute("SELECT uid FROM users WHERE disabled")
res = dbc.fetchall()
if len(res):
    for uid in res:
        dbc.execute("UPDATE users SET smtp = FALSE, pop3 = FALSE, imap = FALSE, managesieve = FALSE WHERE uid = %s", uid[0])
    dbh.commit()
dbc.execute("ALTER TABLE users DROP disabled")
dbh.commit()

dbc.execute("DROP VIEW dovecot_password")
dbh.commit()
dbc.execute("""CREATE OR REPLACE VIEW dovecot_password AS
    SELECT local_part || '@' || domains.domainname AS "user",
           passwd AS "password", smtp, pop3, imap, managesieve
      FROM users
           LEFT JOIN domains USING (gid)""")
dbh.commit()
dbh.close()

# print importnat information
print 
print "* set permissions for replaced views:"
print "connect to your database [psql %s] and execute:" % cf.get('database',
'name')
print "GRANT SELECT ON dovecot_password TO your_dovecot_dbuser;"