1
# IVLE - Informatics Virtual Learning Environment
2
# Copyright (C) 2007-2008 The University of Melbourne
4
# This program is free software; you can redistribute it and/or modify
5
# it under the terms of the GNU General Public License as published by
6
# the Free Software Foundation; either version 2 of the License, or
7
# (at your option) any later version.
9
# This program is distributed in the hope that it will be useful,
10
# but WITHOUT ANY WARRANTY; without even the implied warranty of
11
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12
# GNU General Public License for more details.
14
# You should have received a copy of the GNU General Public License
15
# along with this program; if not, write to the Free Software
16
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
22
# Code to talk to the PostgreSQL database.
23
# (This is the Data Access Layer).
24
# All DB code should be in this module to ensure portability if we want to
25
# change the DB implementation.
26
# This means no SQL strings should be outside of this module. Add functions
27
# here to perform the activities needed, and place the SQL code for those
30
# CAUTION to editors of this module.
31
# All string inputs must be sanitized by calling _escape before being
32
# formatted into an SQL query string.
39
from common import (caps, user)
42
"""Wrapper around pg.escape_string. Prepares the Python value for use in
43
SQL. Returns a string, which may be safely placed verbatim into an SQL
45
Handles the following types:
46
* str: Escapes the string, and also quotes it.
47
* int/long/float: Just converts to an unquoted string.
48
* bool: Returns as "TRUE" or "FALSE", unquoted.
49
* NoneType: Returns "NULL", unquoted.
50
* common.caps.Role: Returns the role as a quoted, lowercase string.
51
Raises a DBException if val has an unsupported type.
53
# "E'" is postgres's way of making "escape" strings.
54
# Such strings allow backslashes to escape things. Since escape_string
55
# converts a single backslash into two backslashes, it needs to be fed
57
# Ref: http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html
58
# WARNING: PostgreSQL-specific code
61
elif isinstance(val, str):
62
return "E'" + pg.escape_string(val) + "'"
63
elif isinstance(val, bool):
64
return "TRUE" if val else "FALSE"
65
elif isinstance(val, int) or isinstance(val, long) \
66
or isinstance(val, float):
68
elif isinstance(val, caps.Role):
69
return _escape(str(val))
71
raise DBException("Attempt to insert an unsupported type "
74
def _passhash(password):
75
return md5.md5(password).hexdigest()
77
class DBException(Exception):
78
"""A DBException is for bad conditions in the database or bad input to
79
these methods. If Postgres throws an exception it does not get rebadged.
80
This is only for additional exceptions."""
84
"""An IVLE database object. This object provides an interface to
85
interacting with the IVLE database without using any external SQL.
87
Most methods of this class have an optional dry argument. If true, they
88
will return the SQL query string and NOT actually execute it. (For
91
Methods may throw db.DBException, or any of the pg exceptions as well.
92
(In general, be prepared to catch exceptions!)
95
"""Connects to the database and creates a DB object.
96
Takes no parameters - gets all the DB info from the configuration."""
98
self.db = pg.connect(dbname=conf.db_dbname, host=conf.db_host,
99
port=conf.db_port, user=conf.db_user, passwd=conf.db_password)
106
# GENERIC DB FUNCTIONS #
109
def check_dict(dict, tablefields, disallowed=frozenset([]), must=False):
110
"""Checks that a dict does not contain keys that are not fields
111
of the specified table.
112
dict: A mapping from string keys to values; the keys are checked to
113
see that they correspond to login table fields.
114
tablefields: Collection of strings for field names in the table.
115
Only these fields will be allowed.
116
disallowed: Optional collection of strings for field names that are
118
must: If True, the dict MUST contain all fields in tablefields.
119
If False, it may contain any subset of the fields.
120
Returns True if the dict is valid, False otherwise.
122
allowed = frozenset(tablefields) - frozenset(disallowed)
123
dictkeys = frozenset(dict.keys())
125
return allowed == dictkeys
127
return allowed.issuperset(dictkeys)
129
def insert(self, dict, tablename, tablefields, disallowed=frozenset([]),
131
"""Inserts a new row in a table, using data from a supplied
132
dictionary (which will be checked by check_dict).
133
dict: Dictionary mapping column names to values. The values may be
134
any of the following types:
135
str, int, long, float, NoneType.
136
tablename: String, name of the table to insert into. Will NOT be
137
escaped - must be a valid identifier.
138
tablefields, disallowed: see check_dict.
139
dry: Returns the SQL query as a string, and does not execute it.
140
Raises a DBException if the dictionary contains invalid fields.
142
if not DB.check_dict(dict, tablefields, disallowed):
143
raise DBException("Supplied dictionary contains invalid fields.")
144
# Build two lists concurrently: field names and values, as SQL strings
147
for k,v in dict.items():
149
values.append(_escape(v))
150
if len(fieldnames) == 0: return
151
fieldnames = ', '.join(fieldnames)
152
values = ', '.join(values)
153
query = ("INSERT INTO %s (%s) VALUES (%s);"
154
% (tablename, fieldnames, values))
158
def update(self, primarydict, updatedict, tablename, tablefields,
159
primary_keys, disallowed_update=frozenset([]), dry=False):
160
"""Updates a row in a table, matching against primarydict to find the
161
row, and using the data in updatedict (which will be checked by
163
primarydict: Dict mapping column names to values. The keys should be
164
the table's primary key. Only rows which match this dict's values
166
updatedict: Dict mapping column names to values. The columns will be
167
updated with the given values for the matched rows.
168
tablename, tablefields, disallowed_update: See insert.
169
primary_keys: Collection of strings which together form the primary
170
key for this table. primarydict must contain all of these as keys,
173
if (not (DB.check_dict(primarydict, primary_keys, must=True)
174
and DB.check_dict(updatedict, tablefields, disallowed_update))):
175
raise DBException("Supplied dictionary contains invalid or missing fields (1).")
176
# Make a list of SQL fragments of the form "field = 'new value'"
177
# These fragments are ALREADY-ESCAPED
179
for k,v in updatedict.items():
180
setlist.append("%s = %s" % (k, _escape(v)))
182
for k,v in primarydict.items():
183
wherelist.append("%s = %s" % (k, _escape(v)))
184
if len(setlist) == 0 or len(wherelist) == 0:
186
# Join the fragments into a comma-separated string
187
setstring = ', '.join(setlist)
188
wherestring = ' AND '.join(wherelist)
189
# Build the whole query as an UPDATE statement
190
query = ("UPDATE %s SET %s WHERE %s;"
191
% (tablename, setstring, wherestring))
195
def delete(self, primarydict, tablename, primary_keys, dry=False):
196
"""Deletes a row in the table, matching against primarydict to find
198
primarydict, tablename, primary_keys: See update.
200
if not DB.check_dict(primarydict, primary_keys, must=True):
201
raise DBException("Supplied dictionary contains invalid or missing fields (2).")
203
for k,v in primarydict.items():
204
wherelist.append("%s = %s" % (k, _escape(v)))
205
if len(wherelist) == 0:
207
wherestring = ' AND '.join(wherelist)
208
query = ("DELETE FROM %s WHERE %s;" % (tablename, wherestring))
212
def get_single(self, primarydict, tablename, getfields, primary_keys,
213
error_notfound="No rows found", dry=False):
214
"""Retrieves a single row from a table, returning it as a dictionary
215
mapping field names to values. Matches against primarydict to find the
217
primarydict, tablename, primary_keys: See update/delete.
218
getfields: Collection of strings; the field names which will be
219
returned as keys in the dictionary.
220
error_notfound: Error message if 0 rows match.
221
Raises a DBException if 0 rows match, with error_notfound as the msg.
222
Raises an AssertError if >1 rows match (this should not happen if
223
primary_keys is indeed the primary key).
225
if not DB.check_dict(primarydict, primary_keys, must=True):
226
raise DBException("Supplied dictionary contains invalid or missing fields (3).")
228
for k,v in primarydict.items():
229
wherelist.append("%s = %s" % (k, _escape(v)))
230
if len(getfields) == 0 or len(wherelist) == 0:
232
# Join the fragments into a comma-separated string
233
getstring = ', '.join(getfields)
234
wherestring = ' AND '.join(wherelist)
235
# Build the whole query as an SELECT statement
236
query = ("SELECT %s FROM %s WHERE %s;"
237
% (getstring, tablename, wherestring))
239
result = self.db.query(query)
240
# Expecting exactly one
241
if result.ntuples() != 1:
242
# It should not be possible for ntuples to be greater than 1
243
assert (result.ntuples() < 1)
244
raise DBException(error_notfound)
245
# Return as a dictionary
246
return result.dictresult()[0]
248
def get_all(self, tablename, getfields, dry=False):
249
"""Retrieves all rows from a table, returning it as a list of
250
dictionaries mapping field names to values.
251
tablename, getfields: See get_single.
253
if len(getfields) == 0:
255
getstring = ', '.join(getfields)
256
query = ("SELECT %s FROM %s;" % (getstring, tablename))
258
return self.db.query(query).dictresult()
260
def start_transaction(self, dry=False):
261
"""Starts a DB transaction.
262
Will not commit any changes until self.commit() is called.
264
query = "START TRANSACTION;"
268
def commit(self, dry=False):
269
"""Commits (ends) a DB transaction.
270
Commits all changes since the call to start_transaction.
276
def rollback(self, dry=False):
277
"""Rolls back (ends) a DB transaction, undoing all changes since the
278
call to start_transaction.
284
# USER MANAGEMENT FUNCTIONS #
286
login_primary = frozenset(["login"])
287
login_fields_list = [
288
"login", "passhash", "state", "unixid", "email", "nick", "fullname",
289
"rolenm", "studentid", "acct_exp", "pass_exp", "last_login", "svn_pass"
291
login_fields = frozenset(login_fields_list)
293
def create_user(self, user_obj=None, dry=False, **kwargs):
294
"""Creates a user login entry in the database.
295
Two ways to call this - passing a user object, or passing
296
all fields as separate arguments.
298
Either pass a "user_obj" as the first argument (in which case other
299
fields will be ignored), or pass all fields as arguments.
301
All user fields are to be passed as args. The argument names
302
are the field names of the "login" table of the DB schema.
303
However, instead of supplying a "passhash", you must supply a
304
"password" argument, which will be hashed internally.
305
Also "state" must not given explicitly; it is implicitly set to
307
Raises an exception if the user already exists, or the dict contains
308
invalid keys or is missing required keys.
310
if 'passhash' in kwargs:
311
raise DBException("Supplied arguments include passhash (invalid) (1).")
312
# Make a copy of the dict. Change password to passhash (hashing it),
313
# and set 'state' to "no_agreement".
316
fields = copy.copy(kwargs)
318
# Use the user object
319
fields = dict(user_obj)
320
if 'password' in fields:
321
fields['passhash'] = _passhash(fields['password'])
322
del fields['password']
324
# Convert role to rolenm
325
fields['rolenm'] = str(user_obj.role)
328
fields['state'] = "no_agreement"
329
# else, we'll trust the user, but it SHOULD be "no_agreement"
330
# (We can't change it because then the user object would not
333
return self.insert(fields, "login", self.login_fields, dry=dry)
335
def update_user(self, login, dry=False, **kwargs):
336
"""Updates fields of a particular user. login is the name of the user
337
to update. The dict contains the fields which will be modified, and
338
their new values. If any value is omitted from the dict, it does not
339
get modified. login and studentid may not be modified.
340
Passhash may be modified by supplying a "password" field, in
341
cleartext, not a hashed password.
343
Note that no checking is done. It is expected this function is called
344
by a trusted source. In particular, it allows the password to be
345
changed without knowing the old password. The caller should check
346
that the user knows the existing password before calling this function
349
if 'passhash' in kwargs:
350
raise DBException("Supplied arguments include passhash (invalid) (2).")
351
if "password" in kwargs:
352
kwargs = copy.copy(kwargs)
353
kwargs['passhash'] = _passhash(kwargs['password'])
354
del kwargs['password']
355
return self.update({"login": login}, kwargs, "login",
356
self.login_fields, self.login_primary, ["login", "studentid"],
359
def get_user(self, login, dry=False):
360
"""Given a login, returns a User object containing details looked up
363
Raises a DBException if the login is not found in the DB.
365
userdict = self.get_single({"login": login}, "login",
366
self.login_fields, self.login_primary,
367
error_notfound="get_user: No user with that login name", dry=dry)
369
return userdict # Query string
370
# Package into a User object
371
return user.User(**userdict)
373
def get_users(self, dry=False):
374
"""Returns a list of all users in the DB, as User objects.
376
userdicts = self.get_all("login", self.login_fields, dry=dry)
378
return userdicts # Query string
379
# Package into User objects
380
return [user.User(**userdict) for userdict in userdicts]
382
def user_authenticate(self, login, password, dry=False):
383
"""Performs a password authentication on a user. Returns True if
384
"passhash" is the correct passhash for the given login, False
385
if the passhash does not match the password in the DB,
386
and None if the passhash in the DB is NULL.
387
Also returns False if the login does not exist (so if you want to
388
differentiate these cases, use get_user and catch an exception).
390
query = "SELECT passhash FROM login WHERE login = '%s';" % login
392
result = self.db.query(query)
393
if result.ntuples() == 1:
394
# Valid username. Check password.
395
passhash = result.getresult()[0][0]
398
return _passhash(password) == passhash
403
"""Close the DB connection. Do not call any other functions after
404
this. (The behaviour of doing so is undefined).
added
removed
lib/common/db.py
