diff -r 668951708f2f -r 8c5f1eba0ea6 update_tables_0.3.x-0.4.py --- a/update_tables_0.3.x-0.4.py Wed Apr 30 04:05:13 2008 +0000 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,195 +0,0 @@ -#!/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'") - -# Create new tables -queries = ("CREATE SEQUENCE transport_id", - """CREATE TABLE transport ( - tid bigint NOT NULL DEFAULT nextval('transport_id'), - transport varchar(270) NOT NULL, - CONSTRAINT pkey_transport PRIMARY KEY (tid), - CONSTRAINT ukey_transport UNIQUE (transport) -)""", - "INSERT INTO transport(transport) VALUES ('dovecot:')", - "CREATE SEQUENCE maillocation_id", - """CREATE TABLE maillocation( - mid bigint NOT NULL DEFAULT nextval('maillocation_id'), - maillocation varchar(20) NOT NULL, - CONSTRAINT pkey_maillocation PRIMARY KEY (mid), - CONSTRAINT ukey_maillocation UNIQUE (maillocation) -)""", - "INSERT INTO maillocation(maillocation) VALUES ('Maildir')" - ) -for query in queries: - dbc.execute(query) -dbh.commit() - - -# fix table domains (Part I) -dbc.execute('ALTER TABLE domains ADD tid bigint NOT NULL DEFAULT 1') -dbh.commit() -dbc.execute("ALTER TABLE domains ADD CONSTRAINT fkey_domains_tid_transport \ - FOREIGN KEY (tid) REFERENCES transport (tid)") -dbh.commit() - -dbc.execute("SELECT DISTINCT transport from domains \ - WHERE transport != 'dovecot:'") -res = dbc.fetchall() -if len(res): - for trsp in res: - dbc.execute("INSERT INTO transport(transport) VALUES (%s)", trsp[0]) - dbh.commit() - - dbc.execute("SELECT tid, transport FROM transport WHERE tid > 1") - res = dbc.fetchall() - for tid, trsp in res: - dbc.execute("UPDATE domains SET tid = %s WHERE transport = %s", tid, - trsp) - dbh.commit() - - -# fix table users (Part I) -dbc.execute("ALTER TABLE users ADD mid bigint NOT NULL DEFAULT 1") -dbh.commit() -dbc.execute("ALTER TABLE users ADD tid bigint NOT NULL DEFAULT 1") -dbh.commit() -for service in ['smtp', 'pop3', 'imap', 'managesieve']: - dbc.execute( - "ALTER TABLE users ADD %s boolean NOT NULL DEFAULT TRUE" % service) - dbh.commit() -dbc.execute("ALTER TABLE users ADD CONSTRAINT fkey_users_mid_maillocation \ - FOREIGN KEY (mid) REFERENCES maillocation (mid)") -dbh.commit() -dbc.execute("ALTER TABLE users ADD CONSTRAINT fkey_users_tid_transport \ - FOREIGN KEY (tid) REFERENCES transport (tid)") -dbh.commit() - -dbc.execute("SELECT DISTINCT mail FROM users WHERE mail != 'Maildir'") -res = dbc.fetchall() -if len(res): - for mailloc in res: - dbc.execute("INSERT INTO maillocation(maillocation) VALUES (%s)", - mailloc[0]) - dbh.commit() - - dbc.execute("SELECT mid, maillocation FROM maillocation WHERE mid > 1") - res = dbc.fetchall() - for mid, mailloc in res: - dbc.execute("UPDATE users SET mid = %s WHERE mail = %s", mid, - maillocation) - 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("SELECT gid, tid FROM domains") -res = dbc.fetchall() -for gid, tid in res: - dbc.execute("UPDATE users SET tid = %s WHERE gid = %s", tid, gid) -dbh.commit() - - -# Update VIEW postfix_maildir -dbc.execute("""CREATE OR REPLACE VIEW postfix_maildir AS - SELECT local_part || '@' || domains.domainname AS address, - domains.domaindir||'/'||uid||'/'||maillocation.maillocation||'/' AS maildir - FROM users - LEFT JOIN domains USING (gid) - LEFT JOIN maillocation USING (mid)""") -dbh.commit() - -# Replace VIEW dovecot_user -dbc.execute("DROP VIEW dovecot_user") -dbh.commit() -dbc.execute("""CREATE OR REPLACE VIEW dovecot_user AS - SELECT local_part || '@' || domains.domainname AS userid, - uid, gid, domains.domaindir || '/' || uid AS home, - '~/' || maillocation.maillocation AS mail - FROM users - LEFT JOIN domains USING (gid) - LEFT JOIN maillocation USING (mid);""") -dbh.commit() - -# Replace VIEW dovecot_password -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() - -# fix table users (Part II) -dbc.execute("ALTER TABLE users DROP home") -dbh.commit() -dbc.execute("ALTER TABLE users DROP mail") -dbh.commit() -dbc.execute("ALTER TABLE users DROP disabled") -dbh.commit() - - -# Replace VIEW postfix_transport -dbc.execute("DROP VIEW postfix_transport") -dbh.commit() -dbc.execute("""CREATE OR REPLACE VIEW postfix_transport AS - SELECT local_part || '@' || domains.domainname AS address, - transport.transport - FROM users - LEFT JOIN transport USING (tid) - LEFT JOIN domains USING (gid)""") -dbh.commit() - - -# fix table domains (Part II) -dbc.execute("ALTER TABLE domains DROP transport") -dbh.commit() - - -# fix table alias -dbc.execute('ALTER TABLE alias DROP id') -dbh.commit() -dbc.execute('DROP SEQUENCE alias_id') -dbh.commit() - - -# fix table relocated -dbc.execute('ALTER TABLE relocated DROP id') -dbh.commit() -dbc.execute('DROP SEQUENCE relocated_id') -dbh.commit() - - -# add new VIEW vmm_domain_info -dbc.execute("""CREATE OR REPLACE VIEW vmm_domain_info AS - SELECT gid, domainname, transport, domaindir, count(uid) AS accounts, - aliases - FROM domains - LEFT JOIN transport USING (tid) - LEFT JOIN users USING (gid) - LEFT JOIN vmm_alias_count USING (gid) - GROUP BY gid, domainname, transport, domaindir, aliases""") -dbh.commit() -dbh.close()