|
1 #!/usr/bin/env python |
|
2 # -*- coding: UTF-8 -*- |
|
3 # Copyright 2008 VEB IT |
|
4 # See COPYING for distribution information. |
|
5 # $Id$ |
|
6 |
|
7 from ConfigParser import ConfigParser |
|
8 from pyPgSQL import PgSQL |
|
9 |
|
10 cff = file('/usr/local/etc/vmm.cfg', 'r') |
|
11 cf = ConfigParser() |
|
12 cf.readfp(cff) |
|
13 cff.close() |
|
14 |
|
15 dbh = PgSQL.connect(database=cf.get('database', 'name'), |
|
16 user=cf.get('database', 'user'), host=cf.get('database', 'host'), |
|
17 password=cf.get('database', 'pass'), client_encoding='utf8', |
|
18 unicode_results=True) |
|
19 dbc = dbh.cursor() |
|
20 dbc.execute("SET NAMES 'UTF8'") |
|
21 |
|
22 # Create new tables |
|
23 queries = ("CREATE SEQUENCE transport_id", |
|
24 """CREATE TABLE transport ( |
|
25 tid bigint NOT NULL DEFAULT nextval('transport_id'), |
|
26 transport varchar(270) NOT NULL, |
|
27 CONSTRAINT pkey_transport PRIMARY KEY (tid), |
|
28 CONSTRAINT ukey_transport UNIQUE (transport) |
|
29 )""", |
|
30 "INSERT INTO transport(transport) VALUES ('dovecot:')", |
|
31 "CREATE SEQUENCE maillocation_id", |
|
32 """CREATE TABLE maillocation( |
|
33 mid bigint NOT NULL DEFAULT nextval('maillocation_id'), |
|
34 maillocation varchar(20) NOT NULL, |
|
35 CONSTRAINT pkey_maillocation PRIMARY KEY (mid), |
|
36 CONSTRAINT ukey_maillocation UNIQUE (maillocation) |
|
37 )""", |
|
38 "INSERT INTO maillocation(maillocation) VALUES ('Maildir')" |
|
39 ) |
|
40 for query in queries: |
|
41 dbc.execute(query) |
|
42 dbh.commit() |
|
43 |
|
44 |
|
45 # fix table domains (Part I) |
|
46 dbc.execute('ALTER TABLE domains ADD tid bigint NOT NULL DEFAULT 1') |
|
47 dbh.commit() |
|
48 dbc.execute("ALTER TABLE domains ADD CONSTRAINT fkey_domains_tid_transport \ |
|
49 FOREIGN KEY (tid) REFERENCES transport (tid)") |
|
50 dbh.commit() |
|
51 |
|
52 dbc.execute("SELECT DISTINCT transport from domains \ |
|
53 WHERE transport != 'dovecot:'") |
|
54 res = dbc.fetchall() |
|
55 if len(res): |
|
56 for trsp in res: |
|
57 dbc.execute("INSERT INTO transport(transport) VALUES (%s)", trsp[0]) |
|
58 dbh.commit() |
|
59 |
|
60 dbc.execute("SELECT tid, transport FROM transport WHERE tid > 1") |
|
61 res = dbc.fetchall() |
|
62 for tid, trsp in res: |
|
63 dbc.execute("UPDATE domains SET tid = %s WHERE transport = %s", tid, |
|
64 trsp) |
|
65 dbh.commit() |
|
66 |
|
67 |
|
68 # fix table users (Part I) |
|
69 dbc.execute("ALTER TABLE users ADD mid bigint NOT NULL DEFAULT 1") |
|
70 dbh.commit() |
|
71 dbc.execute("ALTER TABLE users ADD tid bigint NOT NULL DEFAULT 1") |
|
72 dbh.commit() |
|
73 dbc.execute("ALTER TABLE users ADD CONSTRAINT fkey_users_mid_maillocation \ |
|
74 FOREIGN KEY (mid) REFERENCES maillocation (mid)") |
|
75 dbh.commit() |
|
76 dbc.execute("ALTER TABLE users ADD CONSTRAINT fkey_users_tid_transport \ |
|
77 FOREIGN KEY (tid) REFERENCES transport (tid)") |
|
78 dbh.commit() |
|
79 |
|
80 dbc.execute("SELECT DISTINCT mail FROM users WHERE mail != 'Maildir'") |
|
81 res = dbc.fetchall() |
|
82 if len(res): |
|
83 for mailloc in res: |
|
84 dbc.execute("INSERT INTO maillocation(maillocation) VALUES (%s)", |
|
85 mailloc[0]) |
|
86 dbh.commit() |
|
87 |
|
88 dbc.execute("SELECT mid, maillocation FROM maillocation WHERE mid > 1") |
|
89 res = dbc.fetchall() |
|
90 for mid, mailloc in res: |
|
91 dbc.execute("UPDATE users SET mid = %s WHERE mail = %s", mid, |
|
92 maillocation) |
|
93 dbh.commit() |
|
94 |
|
95 dbc.execute("SELECT gid, tid FROM domains") |
|
96 res = dbc.fetchall() |
|
97 for gid, tid in res: |
|
98 dbc.execute("UPDATE users SET tid = %s WHERE gid = %s", tid, gid) |
|
99 dbh.commit() |
|
100 |
|
101 |
|
102 # Update VIEW postfix_maildir |
|
103 dbc.execute("""CREATE OR REPLACE VIEW postfix_maildir AS |
|
104 SELECT local_part || '@' || domains.domainname AS address, |
|
105 domains.domaindir||'/'||uid||'/'||maillocation.maillocation||'/' AS maildir |
|
106 FROM users |
|
107 LEFT JOIN domains USING (gid) |
|
108 LEFT JOIN maillocation USING (mid)""") |
|
109 dbh.commit() |
|
110 |
|
111 # Update VIEW dovecot_user |
|
112 dbc.execute("""CREATE OR REPLACE VIEW dovecot_user AS |
|
113 SELECT local_part || '@' || domains.domainname AS userid, |
|
114 domains.domaindir || '/' || uid AS home, uid, gid |
|
115 FROM users |
|
116 LEFT JOIN domains USING (gid)""") |
|
117 dbh.commit() |
|
118 |
|
119 # fix table users (Part II) |
|
120 dbc.execute("ALTER TABLE users DROP home") |
|
121 dbh.commit() |
|
122 dbc.execute("ALTER TABLE users DROP mail") |
|
123 dbh.commit() |
|
124 |
|
125 |
|
126 # Replace VIEW postfix_transport |
|
127 dbc.execute("DROP VIEW postfix_transport") |
|
128 dbh.commit() |
|
129 dbc.execute("""CREATE OR REPLACE VIEW postfix_transport AS |
|
130 SELECT local_part || '@' || domains.domainname AS address, |
|
131 transport.transport |
|
132 FROM users |
|
133 LEFT JOIN transport USING (tid) |
|
134 LEFT JOIN domains USING (gid)""") |
|
135 dbh.commit() |
|
136 |
|
137 |
|
138 # fix table domains (Part II) |
|
139 dbc.execute("ALTER TABLE domains DROP transport") |
|
140 dbh.commit() |
|
141 |
|
142 |
|
143 # fix table alias |
|
144 dbc.execute('ALTER TABLE alias DROP id') |
|
145 dbh.commit() |
|
146 dbc.execute('DROP SEQUENCE alias_id') |
|
147 dbh.commit() |
|
148 |
|
149 |
|
150 # fix table relocated |
|
151 dbc.execute('ALTER TABLE relocated DROP id') |
|
152 dbh.commit() |
|
153 dbc.execute('DROP SEQUENCE relocated_id') |
|
154 dbh.commit() |
|
155 |
|
156 |
|
157 # add new VIEW vmm_domain_info |
|
158 dbc.execute("""CREATE OR REPLACE VIEW vmm_domain_info AS |
|
159 SELECT gid, domainname, transport, domaindir, count(uid) AS accounts, |
|
160 aliases |
|
161 FROM domains |
|
162 LEFT JOIN transport USING (tid) |
|
163 LEFT JOIN users USING (gid) |
|
164 LEFT JOIN vmm_alias_count USING (gid) |
|
165 GROUP BY gid, domainname, transport, domaindir, aliases""") |
|
166 dbh.commit() |
|
167 dbh.close() |