* 'create_tables.pgsql'
- Removed unneeded newlines from views dovecot_user and postfix_uid
* 'update_tables_0.2.x-0.3.pgsql'
- Removed from repository
* 'update_tables_0.3.x-0.4.py'
- Added to repository
#!/usr/bin/env python# -*- coding: UTF-8 -*-# Copyright 2008 VEB IT# See COPYING for distribution information.# $Id$fromConfigParserimportConfigParserfrompyPgSQLimportPgSQLcff=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 tablesqueries=("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')")forqueryinqueries: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()iflen(res):fortrspinres: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()fortid,trspinres: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()iflen(res):formaillocinres: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()formid,maillocinres:dbc.execute("UPDATE users SET mid = %s WHERE mail = %s",mid,maillocation)dbh.commit()dbc.execute("SELECT gid, tid FROM domains")res=dbc.fetchall()forgid,tidinres:dbc.execute("UPDATE users SET tid = %s WHERE gid = %s",tid,gid)dbh.commit()# Update VIEW postfix_maildirdbc.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_userdbc.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_transportdbc.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 aliasdbc.execute('ALTER TABLE alias DROP id')dbh.commit()dbc.execute('DROP SEQUENCE alias_id')dbh.commit()# fix table relocateddbc.execute('ALTER TABLE relocated DROP id')dbh.commit()dbc.execute('DROP SEQUENCE relocated_id')dbh.commit()# add new VIEW vmm_domain_infodbc.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()