--- 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()