equal
deleted
inserted
replaced
68 # fix table users (Part I) |
68 # fix table users (Part I) |
69 dbc.execute("ALTER TABLE users ADD mid bigint NOT NULL DEFAULT 1") |
69 dbc.execute("ALTER TABLE users ADD mid bigint NOT NULL DEFAULT 1") |
70 dbh.commit() |
70 dbh.commit() |
71 dbc.execute("ALTER TABLE users ADD tid bigint NOT NULL DEFAULT 1") |
71 dbc.execute("ALTER TABLE users ADD tid bigint NOT NULL DEFAULT 1") |
72 dbh.commit() |
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() |
73 dbc.execute("ALTER TABLE users ADD CONSTRAINT fkey_users_mid_maillocation \ |
77 dbc.execute("ALTER TABLE users ADD CONSTRAINT fkey_users_mid_maillocation \ |
74 FOREIGN KEY (mid) REFERENCES maillocation (mid)") |
78 FOREIGN KEY (mid) REFERENCES maillocation (mid)") |
75 dbh.commit() |
79 dbh.commit() |
76 dbc.execute("ALTER TABLE users ADD CONSTRAINT fkey_users_tid_transport \ |
80 dbc.execute("ALTER TABLE users ADD CONSTRAINT fkey_users_tid_transport \ |
77 FOREIGN KEY (tid) REFERENCES transport (tid)") |
81 FOREIGN KEY (tid) REFERENCES transport (tid)") |
88 dbc.execute("SELECT mid, maillocation FROM maillocation WHERE mid > 1") |
92 dbc.execute("SELECT mid, maillocation FROM maillocation WHERE mid > 1") |
89 res = dbc.fetchall() |
93 res = dbc.fetchall() |
90 for mid, mailloc in res: |
94 for mid, mailloc in res: |
91 dbc.execute("UPDATE users SET mid = %s WHERE mail = %s", mid, |
95 dbc.execute("UPDATE users SET mid = %s WHERE mail = %s", mid, |
92 maillocation) |
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]) |
93 dbh.commit() |
105 dbh.commit() |
94 |
106 |
95 dbc.execute("SELECT gid, tid FROM domains") |
107 dbc.execute("SELECT gid, tid FROM domains") |
96 res = dbc.fetchall() |
108 res = dbc.fetchall() |
97 for gid, tid in res: |
109 for gid, tid in res: |
118 FROM users |
130 FROM users |
119 LEFT JOIN domains USING (gid) |
131 LEFT JOIN domains USING (gid) |
120 LEFT JOIN maillocation USING (mid);""") |
132 LEFT JOIN maillocation USING (mid);""") |
121 dbh.commit() |
133 dbh.commit() |
122 |
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 |
123 # fix table users (Part II) |
145 # fix table users (Part II) |
124 dbc.execute("ALTER TABLE users DROP home") |
146 dbc.execute("ALTER TABLE users DROP home") |
125 dbh.commit() |
147 dbh.commit() |
126 dbc.execute("ALTER TABLE users DROP mail") |
148 dbc.execute("ALTER TABLE users DROP mail") |
|
149 dbh.commit() |
|
150 dbc.execute("ALTER TABLE users DROP disabled") |
127 dbh.commit() |
151 dbh.commit() |
128 |
152 |
129 |
153 |
130 # Replace VIEW postfix_transport |
154 # Replace VIEW postfix_transport |
131 dbc.execute("DROP VIEW postfix_transport") |
155 dbc.execute("DROP VIEW postfix_transport") |