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 for service in ['smtp', 'pop3', 'imap', 'managesieve']: |
|
74 dbc.execute( |
|
75 "ALTER TABLE users ADD %s boolean NOT NULL DEFAULT TRUE" % service) |
|
76 dbh.commit() |
|
77 dbc.execute("ALTER TABLE users ADD CONSTRAINT fkey_users_mid_maillocation \ |
|
78 FOREIGN KEY (mid) REFERENCES maillocation (mid)") |
|
79 dbh.commit() |
|
80 dbc.execute("ALTER TABLE users ADD CONSTRAINT fkey_users_tid_transport \ |
|
81 FOREIGN KEY (tid) REFERENCES transport (tid)") |
|
82 dbh.commit() |
|
83 |
|
84 dbc.execute("SELECT DISTINCT mail FROM users WHERE mail != 'Maildir'") |
|
85 res = dbc.fetchall() |
|
86 if len(res): |
|
87 for mailloc in res: |
|
88 dbc.execute("INSERT INTO maillocation(maillocation) VALUES (%s)", |
|
89 mailloc[0]) |
|
90 dbh.commit() |
|
91 |
|
92 dbc.execute("SELECT mid, maillocation FROM maillocation WHERE mid > 1") |
|
93 res = dbc.fetchall() |
|
94 for mid, mailloc in res: |
|
95 dbc.execute("UPDATE users SET mid = %s WHERE mail = %s", mid, |
|
96 maillocation) |
|
97 dbh.commit() |
|
98 |
|
99 dbc.execute("SELECT uid FROM users WHERE disabled") |
|
100 res = dbc.fetchall() |
|
101 if len(res): |
|
102 for uid in res: |
|
103 dbc.execute("UPDATE users SET smtp = FALSE, pop3 = FALSE, imap = FALSE,\ |
|
104 managesieve = FALSE WHERE uid = %s", uid[0]) |
|
105 dbh.commit() |
|
106 |
|
107 dbc.execute("SELECT gid, tid FROM domains") |
|
108 res = dbc.fetchall() |
|
109 for gid, tid in res: |
|
110 dbc.execute("UPDATE users SET tid = %s WHERE gid = %s", tid, gid) |
|
111 dbh.commit() |
|
112 |
|
113 |
|
114 # Update VIEW postfix_maildir |
|
115 dbc.execute("""CREATE OR REPLACE VIEW postfix_maildir AS |
|
116 SELECT local_part || '@' || domains.domainname AS address, |
|
117 domains.domaindir||'/'||uid||'/'||maillocation.maillocation||'/' AS maildir |
|
118 FROM users |
|
119 LEFT JOIN domains USING (gid) |
|
120 LEFT JOIN maillocation USING (mid)""") |
|
121 dbh.commit() |
|
122 |
|
123 # Replace VIEW dovecot_user |
|
124 dbc.execute("DROP VIEW dovecot_user") |
|
125 dbh.commit() |
|
126 dbc.execute("""CREATE OR REPLACE VIEW dovecot_user AS |
|
127 SELECT local_part || '@' || domains.domainname AS userid, |
|
128 uid, gid, domains.domaindir || '/' || uid AS home, |
|
129 '~/' || maillocation.maillocation AS mail |
|
130 FROM users |
|
131 LEFT JOIN domains USING (gid) |
|
132 LEFT JOIN maillocation USING (mid);""") |
|
133 dbh.commit() |
|
134 |
|
135 # Replace VIEW dovecot_password |
|
136 dbc.execute("DROP VIEW dovecot_password") |
|
137 dbh.commit() |
|
138 dbc.execute("""CREATE OR REPLACE VIEW dovecot_password AS |
|
139 SELECT local_part || '@' || domains.domainname AS "user", |
|
140 passwd AS "password", smtp, pop3, imap, managesieve |
|
141 FROM users |
|
142 LEFT JOIN domains USING (gid)""") |
|
143 dbh.commit() |
|
144 |
|
145 # fix table users (Part II) |
|
146 dbc.execute("ALTER TABLE users DROP home") |
|
147 dbh.commit() |
|
148 dbc.execute("ALTER TABLE users DROP mail") |
|
149 dbh.commit() |
|
150 dbc.execute("ALTER TABLE users DROP disabled") |
|
151 dbh.commit() |
|
152 |
|
153 |
|
154 # Replace VIEW postfix_transport |
|
155 dbc.execute("DROP VIEW postfix_transport") |
|
156 dbh.commit() |
|
157 dbc.execute("""CREATE OR REPLACE VIEW postfix_transport AS |
|
158 SELECT local_part || '@' || domains.domainname AS address, |
|
159 transport.transport |
|
160 FROM users |
|
161 LEFT JOIN transport USING (tid) |
|
162 LEFT JOIN domains USING (gid)""") |
|
163 dbh.commit() |
|
164 |
|
165 |
|
166 # fix table domains (Part II) |
|
167 dbc.execute("ALTER TABLE domains DROP transport") |
|
168 dbh.commit() |
|
169 |
|
170 |
|
171 # fix table alias |
|
172 dbc.execute('ALTER TABLE alias DROP id') |
|
173 dbh.commit() |
|
174 dbc.execute('DROP SEQUENCE alias_id') |
|
175 dbh.commit() |
|
176 |
|
177 |
|
178 # fix table relocated |
|
179 dbc.execute('ALTER TABLE relocated DROP id') |
|
180 dbh.commit() |
|
181 dbc.execute('DROP SEQUENCE relocated_id') |
|
182 dbh.commit() |
|
183 |
|
184 |
|
185 # add new VIEW vmm_domain_info |
|
186 dbc.execute("""CREATE OR REPLACE VIEW vmm_domain_info AS |
|
187 SELECT gid, domainname, transport, domaindir, count(uid) AS accounts, |
|
188 aliases |
|
189 FROM domains |
|
190 LEFT JOIN transport USING (tid) |
|
191 LEFT JOIN users USING (gid) |
|
192 LEFT JOIN vmm_alias_count USING (gid) |
|
193 GROUP BY gid, domainname, transport, domaindir, aliases""") |
|
194 dbh.commit() |
|
195 dbh.close() |
|