--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/update_tables_0.3.x-0.4.py Tue Mar 04 23:28:04 2008 +0000
@@ -0,0 +1,167 @@
+#!/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()
+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 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()
+
+# Update VIEW dovecot_user
+dbc.execute("""CREATE OR REPLACE VIEW dovecot_user AS
+ SELECT local_part || '@' || domains.domainname AS userid,
+ domains.domaindir || '/' || uid AS home, uid, gid
+ 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()
+
+
+# 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()