« back to all changes in this revision
Viewing changes to lib/common/db.py
- browse files at revision 468
- compare with another revision
- compare with revision 1213
- download diff
- download tarball
- view history from revision 468
- Committer: mattgiuca
- Date: 2008-02-15 00:39:02 UTC
- Revision ID: svn-v3-trunk0:2b9c9e99-6f39-0410-b283-7f802c844ae2:trunk:468
db.py: Epic Refactor.
Replaced all the user management methods with very generic methods that take
dictionaries and will work for any DB table. Essentially this now provides us
with a generic high-level database API.
Then put all the user management methods back in, as simple wrappers to the
generic methods.
The user management methods, however, have all had their interfaces changed.
They now take dictionaries of key:value pairs to be put in the DB instead of
arguments. This serves 2 purposes: firstly, it's MUCH easier to add new fields
now (2 or 3 points of control, down from about 7 points of control). Secondly,
it allows None (NULL in SQL) to be distinguished from omitted fields (this
particularly applies to update_user.
Notes: Preserved some of the changes (the self-closing object) from
r466 [conway]. Most of these have been lost in the refactor, and are no
longer necessary as dicts are now general (do not need to explicitly
specify keys).
I am committing this checkpoint even though it will break makeuser.
Need to make major changes to that anyway, so it might as well be broken
for the time being.
Replaced all the user management methods with very generic methods that take
dictionaries and will work for any DB table. Essentially this now provides us
with a generic high-level database API.
Then put all the user management methods back in, as simple wrappers to the
generic methods.
The user management methods, however, have all had their interfaces changed.
They now take dictionaries of key:value pairs to be put in the DB instead of
arguments. This serves 2 purposes: firstly, it's MUCH easier to add new fields
now (2 or 3 points of control, down from about 7 points of control). Secondly,
it allows None (NULL in SQL) to be distinguished from omitted fields (this
particularly applies to update_user.
Notes: Preserved some of the changes (the self-closing object) from
r466 [conway]. Most of these have been lost in the refactor, and are no
longer necessary as dicts are now general (do not need to explicitly
specify keys).
I am committing this checkpoint even though it will break makeuser.
Need to make major changes to that anyway, so it might as well be broken
for the time being.
- files modified:
- lib/common/db.py
added
removed
lib/common/db.py
17
17
18
18
# Module: Database
19
19
# Author: Matt Giuca
20
# Date: 1/2/2008
20
# Date: 15/2/2008
21
21
22
22
# Code to talk to the PostgreSQL database.
23
23
# (This is the Data Access Layer).
34
34
import pg
35
35
import conf
36
36
import md5
37
import copy
37
38
38
def _escape(str):
39
"""Wrapper around pg.escape_string. Escapes the string for use in SQL, and
40
also quotes it to make sure that every string used in a query is quoted.
41
If str is None, returns "NULL", which is unescaped and thus a valid SQL
42
value.
39
def _escape(val):
40
"""Wrapper around pg.escape_string. Prepares the Python value for use in
41
SQL. Returns a string, which may be safely placed verbatim into an SQL
42
query.
43
Handles the following types:
44
* str: Escapes the string, and also quotes it.
45
* int/long/float: Just converts to an unquoted string.
46
* bool: Returns as "TRUE" or "FALSE", unquoted.
47
* NoneType: Returns "NULL", unquoted.
48
Raises a DBException if val has an unsupported type.
43
49
"""
44
50
# "E'" is postgres's way of making "escape" strings.
45
51
# Such strings allow backslashes to escape things. Since escape_string
47
53
# into E mode.
48
54
# Ref: http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html
49
55
# WARNING: PostgreSQL-specific code
50
if str is None:
56
if val is None:
51
57
return "NULL"
52
return "E'" + pg.escape_string(str) + "'"
58
elif isinstance(val, str):
59
return "E'" + pg.escape_string(val) + "'"
60
elif isinstance(val, bool):
61
return "TRUE" if val else "FALSE"
62
elif isinstance(val, int) or isinstance(val, long) \
63
or isinstance(val, float):
64
return str(val)
65
else:
66
raise DBException("Attempt to insert an unsupported type "
67
"into the database")
53
68
54
69
def _passhash(password):
55
70
return md5.md5(password).hexdigest()
82
97
if self.open:
83
98
self.db.close()
84
99
100
# GENERIC DB FUNCTIONS #
101
102
@staticmethod
103
def check_dict(dict, tablefields, disallowed=frozenset([]), must=False):
104
"""Checks that a dict does not contain keys that are not fields
105
of the specified table.
106
dict: A mapping from string keys to values; the keys are checked to
107
see that they correspond to login table fields.
108
tablefields: Collection of strings for field names in the table.
109
Only these fields will be allowed.
110
disallowed: Optional collection of strings for field names that are
111
not allowed.
112
must: If True, the dict MUST contain all fields in tablefields.
113
If False, it may contain any subset of the fields.
114
Returns True if the dict is valid, False otherwise.
115
"""
116
allowed = frozenset(tablefields) - frozenset(disallowed)
117
dictkeys = frozenset(dict.keys())
118
if must:
119
return allowed == dictkeys
120
else:
121
return allowed.issuperset(dictkeys)
122
123
def insert(self, dict, tablename, tablefields, disallowed=frozenset([]),
124
dry=False):
125
"""Inserts a new row in a table, using data from a supplied
126
dictionary (which will be checked by check_dict).
127
dict: Dictionary mapping column names to values. The values may be
128
any of the following types:
129
str, int, long, float, NoneType.
130
tablename: String, name of the table to insert into. Will NOT be
131
escaped - must be a valid identifier.
132
tablefields, disallowed: see check_dict.
133
dry: Returns the SQL query as a string, and does not execute it.
134
Raises a DBException if the dictionary contains invalid fields.
135
"""
136
if not DB.check_dict(dict, tablefields, disallowed):
137
raise DBException("Supplied dictionary contains invalid fields.")
138
# Build two lists concurrently: field names and values, as SQL strings
139
fieldnames = []
140
values = []
141
for k,v in dict.items():
142
fieldnames.append(k)
143
values.append(_escape(v))
144
if len(fieldnames) == 0: return
145
fieldnames = ', '.join(fieldnames)
146
values = ', '.join(values)
147
query = ("INSERT INTO %s (%s) VALUES (%s);"
148
% (tablename, fieldnames, values))
149
if dry: return query
150
self.db.query(query)
151
152
def update(self, primarydict, updatedict, tablename, tablefields,
153
primary_keys, disallowed_update=frozenset([]), dry=False):
154
"""Updates a row in a table, matching against primarydict to find the
155
row, and using the data in updatedict (which will be checked by
156
check_dict).
157
primarydict: Dict mapping column names to values. The keys should be
158
the table's primary key. Only rows which match this dict's values
159
will be updated.
160
updatedict: Dict mapping column names to values. The columns will be
161
updated with the given values for the matched rows.
162
tablename, tablefields, disallowed_update: See insert.
163
primary_keys: Collection of strings which together form the primary
164
key for this table. primarydict must contain all of these as keys,
165
and only these keys.
166
"""
167
if (not (DB.check_dict(primarydict, primary_keys, must=True)
168
and DB.check_dict(updatedict, tablefields, disallowed_update))):
169
raise DBException("Supplied dictionary contains invalid or "
170
" missing fields.")
171
# Make a list of SQL fragments of the form "field = 'new value'"
172
# These fragments are ALREADY-ESCAPED
173
setlist = []
174
for k,v in updatedict.items():
175
setlist.append("%s = %s" % (k, _escape(v)))
176
wherelist = []
177
for k,v in primarydict.items():
178
wherelist.append("%s = %s" % (k, _escape(v)))
179
if len(setlist) == 0 or len(wherelist) == 0:
180
return
181
# Join the fragments into a comma-separated string
182
setstring = ', '.join(setlist)
183
wherestring = ' AND '.join(wherelist)
184
# Build the whole query as an UPDATE statement
185
query = ("UPDATE %s SET %s WHERE %s;"
186
% (tablename, setstring, wherestring))
187
if dry: return query
188
self.db.query(query)
189
190
def delete(self, primarydict, tablename, primary_keys, dry=False):
191
"""Deletes a row in the table, matching against primarydict to find
192
the row.
193
primarydict, tablename, primary_keys: See update.
194
"""
195
if not DB.check_dict(primarydict, primary_keys, must=True):
196
raise DBException("Supplied dictionary contains invalid or "
197
" missing fields.")
198
wherelist = []
199
for k,v in primarydict.items():
200
wherelist.append("%s = %s" % (k, _escape(v)))
201
if len(wherelist) == 0:
202
return
203
wherestring = ' AND '.join(wherelist)
204
query = ("DELETE FROM %s WHERE %s;" % (tablename, wherestring))
205
if dry: return query
206
self.db.query(query)
207
208
def get_single(self, primarydict, tablename, getfields, primary_keys,
209
error_notfound="No rows found", dry=False):
210
"""Retrieves a single row from a table, returning it as a dictionary
211
mapping field names to values. Matches against primarydict to find the
212
row.
213
primarydict, tablename, primary_keys: See update/delete.
214
getfields: Collection of strings; the field names which will be
215
returned as keys in the dictionary.
216
error_notfound: Error message if 0 rows match.
217
Raises a DBException if 0 rows match, with error_notfound as the msg.
218
Raises an AssertError if >1 rows match (this should not happen if
219
primary_keys is indeed the primary key).
220
"""
221
if not DB.check_dict(primarydict, primary_keys, must=True):
222
raise DBException("Supplied dictionary contains invalid or "
223
" missing fields.")
224
wherelist = []
225
for k,v in primarydict.items():
226
wherelist.append("%s = %s" % (k, _escape(v)))
227
if len(getfields) == 0 or len(wherelist) == 0:
228
return
229
# Join the fragments into a comma-separated string
230
getstring = ', '.join(getfields)
231
wherestring = ' AND '.join(wherelist)
232
# Build the whole query as an SELECT statement
233
query = ("SELECT %s FROM %s WHERE %s;"
234
% (getstring, tablename, wherestring))
235
if dry: return query
236
result = self.db.query(query)
237
# Expecting exactly one
238
if result.ntuples() != 1:
239
# It should not be possible for ntuples to be greater than 1
240
assert (result.ntuples() < 1)
241
raise DBException(error_notfound)
242
# Return as a dictionary
243
return result.dictresult()[0]
244
245
def get_all(self, tablename, getfields, dry=False):
246
"""Retrieves all rows from a table, returning it as a list of
247
dictionaries mapping field names to values.
248
tablename, getfields: See get_single.
249
"""
250
if len(getfields) == 0:
251
return
252
getstring = ', '.join(getfields)
253
query = ("SELECT %s FROM %s;" % (getstring, tablename))
254
if dry: return query
255
return self.db.query(query).dictresult()
256
85
257
# USER MANAGEMENT FUNCTIONS #
86
258
87
def create_user(self, login, password, unixid, email, nick, fullname,
88
rolenm, studentid, acct_exp=None, dry=False):
259
login_primary = frozenset(["login"])
260
login_fields = frozenset([
261
"login", "passhash", "state", "unixid", "email", "nick", "fullname",
262
"rolenm", "studentid", "acct_exp", "pass_exp"
263
])
264
# Do not return passhash when reading from the DB
265
login_getfields = login_fields - frozenset(["passhash"])
266
267
def create_user(self, dict, dry=False):
89
268
"""Creates a user login entry in the database.
90
Arguments are the same as those in the "login" table of the schema.
91
The exception is "password", which is a cleartext password. makeuser
92
will hash the password.
93
Also "state" is not given explicitly; it is implicitly set to
269
dict is a dictionary mapping string keys to values. The string keys
270
are the field names of the "login" table of the DB schema.
271
However, instead of supplying a "passhash", you must supply a
272
"password", which will be hashed internally.
273
Also "state" must not given explicitly; it is implicitly set to
94
274
"no_agreement".
95
Raises an exception if the user already exists.
275
Raises an exception if the user already exists, or the dict contains
276
invalid keys or is missing required keys.
96
277
"""
97
passhash = _passhash(password)
98
query = ("INSERT INTO login (login, passhash, state, unixid, email, "
99
"nick, fullname, rolenm, studentid, acct_exp) VALUES "
100
"(%s, %s, 'no_agreement', %d, %s, %s, %s, %s, %s);" %
101
(_escape(login), _escape(passhash), unixid, _escape(email),
102
_escape(nick), _escape(fullname), _escape(rolenm),
103
_escape(studentid), _escape(acct_exp))
104
if dry: return query
105
self.db.query(query)
278
if 'passhash' in dict:
279
raise DBException("Supplied dictionary contains passhash (invalid).")
280
# Make a copy of the dict. Change password to passhash (hashing it),
281
# and set 'state' to "no_agreement".
282
dict = copy.copy(dict)
283
dict['passhash'] = _passhash(dict['password'])
284
del dict['password']
285
dict['state'] = "no_agreement"
286
# Execute the query.
287
return self.insert(dict, "login", self.login_fields, dry=dry)
106
288
107
def update_user(self, login, password=None, state=None, email=None,
108
nick=None, fullname=None, rolenm=None, acct_exp=None, pass_exp=None,
109
last_login=None, dry=False):
289
def update_user(self, login, dict, dry=False):
110
290
"""Updates fields of a particular user. login is the name of the user
111
to update. The other arguments are optional fields which may be
112
modified. If None or omitted, they do not get modified. login and
113
studentid may not be modified.
291
to update. The dict contains the fields which will be modified, and
292
their new values. If any value is omitted from the dict, it does not
293
get modified. login and studentid may not be modified.
294
Passhash may be modified by supplying a "password" field, in
295
cleartext, not a hashed password.
114
296
115
297
Note that no checking is done. It is expected this function is called
116
298
by a trusted source. In particular, it allows the password to be
117
299
changed without knowing the old password. The caller should check
118
300
that the user knows the existing password before calling this function
119
301
with a new one.
120
121
FIXME: this interface does not allow fields to be set to NULL (None).
122
302
"""
123
# Make a list of SQL fragments of the form "field = 'new value'"
124
# These fragments are ALREADY-ESCAPED
125
setlist = []
126
if passhash is not None:
127
setlist.append("passhash = " + _escape(_passhash(password)))
128
if state is not None:
129
setlist.append("state = " + _escape(state))
130
if email is not None:
131
setlist.append("email = " + _escape(email))
132
if nick is not None:
133
setlist.append("nick = " + _escape(nick))
134
if fullname is not None:
135
setlist.append("fullname = " + _escape(fullname))
136
if rolenm is not None:
137
setlist.append("rolenm = " + _escape(rolenm))
138
if pass_exp is not None:
139
setlist.append("pass_exp = " + _escape(pass_exp))
140
if acct_exp is not None:
141
setlist.append("acct_exp = " + _escape(acct_exp))
142
if last_login is not None:
143
setlist.append("last_login = " + _escape(last_login))
144
if len(setlist) == 0:
145
return
146
# Join the fragments into a comma-separated string
147
setstring = ', '.join(setlist)
148
# Build the whole query as an UPDATE statement
149
query = ("UPDATE login SET %s WHERE login = %s;"
150
% (setstring, _escape(login)))
151
if dry: return query
152
self.db.query(query)
153
154
def delete_user(self, login, dry=False):
155
"""Deletes a user login entry from the database."""
156
query = "DELETE FROM login WHERE login = %s;" % _escape(login)
157
if dry: return query
158
self.db.query(query)
303
if 'passhash' in dict:
304
raise DBException("Supplied dictionary contains passhash (invalid).")
305
if "password" in dict:
306
dict = copy.copy(dict)
307
dict['passhash'] = _passhash(dict['password'])
308
del dict['password']
309
return self.update({"login": login}, dict, "login", self.login_fields,
310
self.login_primary, ["login", "studentid"], dry=dry)
159
311
160
312
def get_user(self, login, dry=False):
161
313
"""Given a login, returns a dictionary of the user's DB fields,
163
315
164
316
Raises a DBException if the login is not found in the DB.
165
317
"""
166
query = ("SELECT login, state, unixid, email, nick, fullname, "
167
"rolenm, studentid FROM login WHERE login = %s;" % _escape(login))
168
if dry: return query
169
result = self.db.query(query)
170
# Expecting exactly one
171
if result.ntuples() != 1:
172
# It should not be possible for ntuples to be greater than 1
173
assert (result.ntuples() < 1)
174
raise DBException("get_user: No user with that login name")
175
# Return as a dictionary
176
return result.dictresult()[0]
318
return self.get_single({"login": login}, "login",
319
self.login_getfields, self.login_primary,
320
error_notfound="get_user: No user with that login name", dry=dry)
177
321
178
322
def get_users(self, dry=False):
179
323
"""Returns a list of all users. The list elements are a dictionary of
180
324
the user's DB fields, excluding the passhash field.
181
325
"""
182
query = ("SELECT login, state, unixid, email, nick, fullname, "
183
"rolenm, studentid FROM login")
184
if dry: return query
185
return self.db.query(query).dictresult()
326
return self.get_all("login", self.login_getfields, dry=dry)
186
327
187
328
def user_authenticate(self, login, password, dry=False):
188
329
"""Performs a password authentication on a user. Returns True if
Loggerhead is a web-based interface for Breezy
Version: 2.0.1