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.
40
from common import (caps, user)
42
TIMESTAMP_FORMAT = '%Y-%m-%d %H:%M:%S'
45
"""Wrapper around pg.escape_string. Prepares the Python value for use in
46
SQL. Returns a string, which may be safely placed verbatim into an SQL
48
Handles the following types:
49
* str: Escapes the string, and also quotes it.
50
* int/long/float: Just converts to an unquoted string.
51
* bool: Returns as "TRUE" or "FALSE", unquoted.
52
* NoneType: Returns "NULL", unquoted.
53
* common.caps.Role: Returns the role as a quoted, lowercase string.
54
Raises a DBException if val has an unsupported type.
56
# "E'" is postgres's way of making "escape" strings.
57
# Such strings allow backslashes to escape things. Since escape_string
58
# converts a single backslash into two backslashes, it needs to be fed
60
# Ref: http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html
61
# WARNING: PostgreSQL-specific code
64
elif isinstance(val, str) or isinstance(val, unicode):
65
return "E'" + pg.escape_string(val) + "'"
66
elif isinstance(val, bool):
67
return "TRUE" if val else "FALSE"
68
elif isinstance(val, int) or isinstance(val, long) \
69
or isinstance(val, float):
71
elif isinstance(val, caps.Role):
72
return _escape(str(val))
73
elif isinstance(val, time.struct_time):
74
return _escape(time.strftime(TIMESTAMP_FORMAT, val))
76
raise DBException("Attempt to insert an unsupported type "
79
def _passhash(password):
80
return md5.md5(password).hexdigest()
82
class DBException(Exception):
83
"""A DBException is for bad conditions in the database or bad input to
84
these methods. If Postgres throws an exception it does not get rebadged.
85
This is only for additional exceptions."""
89
"""An IVLE database object. This object provides an interface to
90
interacting with the IVLE database without using any external SQL.
92
Most methods of this class have an optional dry argument. If true, they
93
will return the SQL query string and NOT actually execute it. (For
96
Methods may throw db.DBException, or any of the pg exceptions as well.
97
(In general, be prepared to catch exceptions!)
100
"""Connects to the database and creates a DB object.
101
Takes no parameters - gets all the DB info from the configuration."""
103
self.db = pg.connect(dbname=conf.db_dbname, host=conf.db_host,
104
port=conf.db_port, user=conf.db_user, passwd=conf.db_password)
111
# GENERIC DB FUNCTIONS #
114
def check_dict(dict, tablefields, disallowed=frozenset([]), must=False):
115
"""Checks that a dict does not contain keys that are not fields
116
of the specified table.
117
dict: A mapping from string keys to values; the keys are checked to
118
see that they correspond to login table fields.
119
tablefields: Collection of strings for field names in the table.
120
Only these fields will be allowed.
121
disallowed: Optional collection of strings for field names that are
123
must: If True, the dict MUST contain all fields in tablefields.
124
If False, it may contain any subset of the fields.
125
Returns True if the dict is valid, False otherwise.
127
allowed = frozenset(tablefields) - frozenset(disallowed)
128
dictkeys = frozenset(dict.keys())
130
return allowed == dictkeys
132
return allowed.issuperset(dictkeys)
134
def insert(self, dict, tablename, tablefields, disallowed=frozenset([]),
136
"""Inserts a new row in a table, using data from a supplied
137
dictionary (which will be checked by check_dict).
138
dict: Dictionary mapping column names to values. The values may be
139
any of the following types:
140
str, int, long, float, NoneType.
141
tablename: String, name of the table to insert into. Will NOT be
142
escaped - must be a valid identifier.
143
tablefields, disallowed: see check_dict.
144
dry: Returns the SQL query as a string, and does not execute it.
145
Raises a DBException if the dictionary contains invalid fields.
147
if not DB.check_dict(dict, tablefields, disallowed):
148
extras = set(dict.keys()) - tablefields
149
raise DBException("Supplied dictionary contains invalid fields. (%s)" % (repr(extras)))
150
# Build two lists concurrently: field names and values, as SQL strings
153
for k,v in dict.items():
155
values.append(_escape(v))
156
if len(fieldnames) == 0: return
157
fieldnames = ', '.join(fieldnames)
158
values = ', '.join(values)
159
query = ("INSERT INTO %s (%s) VALUES (%s);"
160
% (tablename, fieldnames, values))
164
def update(self, primarydict, updatedict, tablename, tablefields,
165
primary_keys, disallowed_update=frozenset([]), dry=False):
166
"""Updates a row in a table, matching against primarydict to find the
167
row, and using the data in updatedict (which will be checked by
169
primarydict: Dict mapping column names to values. The keys should be
170
the table's primary key. Only rows which match this dict's values
172
updatedict: Dict mapping column names to values. The columns will be
173
updated with the given values for the matched rows.
174
tablename, tablefields, disallowed_update: See insert.
175
primary_keys: Collection of strings which together form the primary
176
key for this table. primarydict must contain all of these as keys,
179
if (not (DB.check_dict(primarydict, primary_keys, must=True)
180
and DB.check_dict(updatedict, tablefields, disallowed_update))):
181
raise DBException("Supplied dictionary contains invalid or missing fields (1).")
182
# Make a list of SQL fragments of the form "field = 'new value'"
183
# These fragments are ALREADY-ESCAPED
185
for k,v in updatedict.items():
186
setlist.append("%s = %s" % (k, _escape(v)))
188
for k,v in primarydict.items():
189
wherelist.append("%s = %s" % (k, _escape(v)))
190
if len(setlist) == 0 or len(wherelist) == 0:
192
# Join the fragments into a comma-separated string
193
setstring = ', '.join(setlist)
194
wherestring = ' AND '.join(wherelist)
195
# Build the whole query as an UPDATE statement
196
query = ("UPDATE %s SET %s WHERE %s;"
197
% (tablename, setstring, wherestring))
201
def delete(self, primarydict, tablename, primary_keys, dry=False):
202
"""Deletes a row in the table, matching against primarydict to find
204
primarydict, tablename, primary_keys: See update.
206
if not DB.check_dict(primarydict, primary_keys, must=True):
207
raise DBException("Supplied dictionary contains invalid or missing fields (2).")
209
for k,v in primarydict.items():
210
wherelist.append("%s = %s" % (k, _escape(v)))
211
if len(wherelist) == 0:
213
wherestring = ' AND '.join(wherelist)
214
query = ("DELETE FROM %s WHERE %s;" % (tablename, wherestring))
218
def get_single(self, primarydict, tablename, getfields, primary_keys,
219
error_notfound="No rows found", dry=False):
220
"""Retrieves a single row from a table, returning it as a dictionary
221
mapping field names to values. Matches against primarydict to find the
223
primarydict, tablename, primary_keys: See update/delete.
224
getfields: Collection of strings; the field names which will be
225
returned as keys in the dictionary.
226
error_notfound: Error message if 0 rows match.
227
Raises a DBException if 0 rows match, with error_notfound as the msg.
228
Raises an AssertError if >1 rows match (this should not happen if
229
primary_keys is indeed the primary key).
231
if not DB.check_dict(primarydict, primary_keys, must=True):
232
raise DBException("Supplied dictionary contains invalid or missing fields (3).")
234
for k,v in primarydict.items():
235
wherelist.append("%s = %s" % (k, _escape(v)))
236
if len(getfields) == 0 or len(wherelist) == 0:
238
# Join the fragments into a comma-separated string
239
getstring = ', '.join(getfields)
240
wherestring = ' AND '.join(wherelist)
241
# Build the whole query as an SELECT statement
242
query = ("SELECT %s FROM %s WHERE %s;"
243
% (getstring, tablename, wherestring))
245
result = self.db.query(query)
246
# Expecting exactly one
247
if result.ntuples() != 1:
248
# It should not be possible for ntuples to be greater than 1
249
assert (result.ntuples() < 1)
250
raise DBException(error_notfound)
251
# Return as a dictionary
252
return result.dictresult()[0]
254
def get_all(self, tablename, getfields, dry=False):
255
"""Retrieves all rows from a table, returning it as a list of
256
dictionaries mapping field names to values.
257
tablename, getfields: See get_single.
259
if len(getfields) == 0:
261
getstring = ', '.join(getfields)
262
query = ("SELECT %s FROM %s;" % (getstring, tablename))
264
return self.db.query(query).dictresult()
266
def start_transaction(self, dry=False):
267
"""Starts a DB transaction.
268
Will not commit any changes until self.commit() is called.
270
query = "START TRANSACTION;"
274
def commit(self, dry=False):
275
"""Commits (ends) a DB transaction.
276
Commits all changes since the call to start_transaction.
282
def rollback(self, dry=False):
283
"""Rolls back (ends) a DB transaction, undoing all changes since the
284
call to start_transaction.
290
# USER MANAGEMENT FUNCTIONS #
292
login_primary = frozenset(["login"])
293
login_fields_list = [
294
"login", "passhash", "state", "unixid", "email", "nick", "fullname",
295
"rolenm", "studentid", "acct_exp", "pass_exp", "last_login", "svn_pass"
297
login_fields = frozenset(login_fields_list)
299
def create_user(self, user_obj=None, dry=False, **kwargs):
300
"""Creates a user login entry in the database.
301
Two ways to call this - passing a user object, or passing
302
all fields as separate arguments.
304
Either pass a "user_obj" as the first argument (in which case other
305
fields will be ignored), or pass all fields as arguments.
307
All user fields are to be passed as args. The argument names
308
are the field names of the "login" table of the DB schema.
309
However, instead of supplying a "passhash", you must supply a
310
"password" argument, which will be hashed internally.
311
Also "state" must not given explicitly; it is implicitly set to
313
Raises an exception if the user already exists, or the dict contains
314
invalid keys or is missing required keys.
316
if 'passhash' in kwargs:
317
raise DBException("Supplied arguments include passhash (invalid) (1).")
318
# Make a copy of the dict. Change password to passhash (hashing it),
319
# and set 'state' to "no_agreement".
322
fields = copy.copy(kwargs)
324
# Use the user object
325
fields = dict(user_obj)
326
if 'password' in fields:
327
fields['passhash'] = _passhash(fields['password'])
328
del fields['password']
330
# Convert role to rolenm
331
fields['rolenm'] = str(user_obj.role)
334
fields['state'] = "no_agreement"
335
# else, we'll trust the user, but it SHOULD be "no_agreement"
336
# (We can't change it because then the user object would not
338
if 'local_password' in fields:
339
del fields['local_password']
341
return self.insert(fields, "login", self.login_fields, dry=dry)
343
def update_user(self, login, dry=False, **kwargs):
344
"""Updates fields of a particular user. login is the name of the user
345
to update. The dict contains the fields which will be modified, and
346
their new values. If any value is omitted from the dict, it does not
347
get modified. login and studentid may not be modified.
348
Passhash may be modified by supplying a "password" field, in
349
cleartext, not a hashed password.
351
Note that no checking is done. It is expected this function is called
352
by a trusted source. In particular, it allows the password to be
353
changed without knowing the old password. The caller should check
354
that the user knows the existing password before calling this function
357
if 'passhash' in kwargs:
358
raise DBException("Supplied arguments include passhash (invalid) (2).")
359
if "password" in kwargs:
360
kwargs = copy.copy(kwargs)
361
kwargs['passhash'] = _passhash(kwargs['password'])
362
del kwargs['password']
363
return self.update({"login": login}, kwargs, "login",
364
self.login_fields, self.login_primary, ["login", "studentid"],
367
def get_user(self, login, dry=False):
368
"""Given a login, returns a User object containing details looked up
371
Raises a DBException if the login is not found in the DB.
373
userdict = self.get_single({"login": login}, "login",
374
self.login_fields, self.login_primary,
375
error_notfound="get_user: No user with that login name", dry=dry)
377
return userdict # Query string
378
# Package into a User object
379
return user.User(**userdict)
381
def get_users(self, dry=False):
382
"""Returns a list of all users in the DB, as User objects.
384
userdicts = self.get_all("login", self.login_fields, dry=dry)
386
return userdicts # Query string
387
# Package into User objects
388
return [user.User(**userdict) for userdict in userdicts]
390
def get_user_loginid(self, login, dry=False):
391
"""Given a login, returns the integer loginid for this user.
393
Raises a DBException if the login is not found in the DB.
395
userdict = self.get_single({"login": login}, "login",
396
['loginid'], self.login_primary,
397
error_notfound="get_user_loginid: No user with that login name",
400
return userdict # Query string
401
return userdict['loginid']
403
def user_authenticate(self, login, password, dry=False):
404
"""Performs a password authentication on a user. Returns True if
405
"passhash" is the correct passhash for the given login, False
406
if the passhash does not match the password in the DB,
407
and None if the passhash in the DB is NULL.
408
Also returns False if the login does not exist (so if you want to
409
differentiate these cases, use get_user and catch an exception).
411
query = ("SELECT passhash FROM login WHERE login = %s;"
414
result = self.db.query(query)
415
if result.ntuples() == 1:
416
# Valid username. Check password.
417
passhash = result.getresult()[0][0]
420
return _passhash(password) == passhash
424
# PROBLEM AND PROBLEM ATTEMPT FUNCTIONS #
426
def get_problem_problemid(self, exercisename, dry=False):
427
"""Given an exercise name, returns the associated problemID.
428
If the exercise name is NOT in the database, it inserts it and returns
429
the new problemID. Hence this may mutate the DB, but is idempotent.
432
d = self.get_single({"identifier": exercisename}, "problem",
433
['problemid'], frozenset(["identifier"]),
436
return d # Query string
439
# Shouldn't try again, must have failed for some other reason
441
# if we failed to get a problemid, it was probably because
442
# the exercise wasn't in the db. So lets insert it!
444
# The insert can fail if someone else simultaneously does
445
# the insert, so if the insert fails, we ignore the problem.
447
self.insert({'identifier': exercisename}, "problem",
448
frozenset(['identifier']))
452
# Assuming the insert succeeded, we should be able to get the
454
d = self.get_single({"identifier": exercisename}, "problem",
455
['problemid'], frozenset(["identifier"]))
457
return d['problemid']
459
def insert_problem_attempt(self, login, exercisename, date, complete,
461
"""Inserts the details of a problem attempt into the database.
462
exercisename: Name of the exercise. (identifier field of problem
463
table). If this exercise does not exist, also creates a new row in
464
the problem table for this exercise name.
465
login: Name of the user submitting the attempt. (login field of the
467
date: struct_time, the date this attempt was made.
468
complete: bool. Whether the test passed or not.
469
attempt: Text of the attempt.
471
Note: Even if dry, will still physically call get_problem_problemid,
472
which may mutate the DB, and get_user_loginid, which may fail.
474
problemid = self.get_problem_problemid(exercisename)
475
loginid = self.get_user_loginid(login) # May raise a DBException
478
'problemid': problemid,
481
'complete': complete,
483
}, 'problem_attempt',
484
frozenset(['problemid','loginid','date','complete','attempt']),
487
def write_problem_save(self, login, exercisename, date, text, dry=False):
488
"""Writes text to the problem_save table (for when the user saves an
489
exercise). Creates a new row, or overwrites an existing one if the
490
user has already saved that problem.
491
(Unlike problem_attempt, does not keep historical records).
493
problemid = self.get_problem_problemid(exercisename)
494
loginid = self.get_user_loginid(login) # May raise a DBException
498
'problemid': problemid,
503
frozenset(['problemid','loginid','date','text']),
505
except pg.ProgrammingError:
506
# May have failed because this problemid/loginid row already
507
# exists (they have a unique key constraint).
508
# Do an update instead.
510
# Shouldn't try again, must have failed for some other reason
513
'problemid': problemid,
520
frozenset(['date', 'text']),
521
frozenset(['problemid', 'loginid']))
523
def get_problem_stored_text(self, login, exercisename, dry=False):
524
"""Given a login name and exercise name, returns the text of the
525
last saved/submitted attempt for this question.
526
Returns None if the user has not saved or made an attempt on this
528
(If the user has both saved and submitted, it returns whichever was
531
Note: Even if dry, will still physically call get_problem_problemid,
532
which may mutate the DB, and get_user_loginid, which may fail.
534
problemid = self.get_problem_problemid(exercisename)
535
loginid = self.get_user_loginid(login) # May raise a DBException
536
# This very complex query finds all submissions made by this user for
537
# this problem, as well as the save made by this user for this
538
# problem, and returns the text of the newest one.
539
# (Whichever is newer out of the save or the submit).
540
query = """SELECT text FROM
542
(SELECT * FROM problem_save WHERE loginid = %d AND problemid = %d)
544
(SELECT problemid, loginid, date, text FROM problem_attempt
545
AS problem_attempt (problemid, loginid, date, text)
546
WHERE loginid = %d AND problemid = %d)
550
LIMIT 1;""" % (loginid, problemid, loginid, problemid)
552
result = self.db.query(query)
553
if result.ntuples() == 1:
554
# The user has made at least 1 attempt. Return the newest.
555
return result.getresult()[0][0]
559
def get_problem_status(self, login, exercisename, dry=False):
560
"""Given a login name and exercise name, returns information about the
561
user's performance on that problem.
563
- A boolean, whether they have successfully passed this exercise.
564
- An int, the number of attempts they have made up to and
565
including the first successful attempt (or the total number of
566
attempts, if not yet successful).
568
problemid = self.get_problem_problemid(exercisename)
569
loginid = self.get_user_loginid(login) # May raise a DBException
571
# ASSUME that it is completed, get the total number of attempts up to
572
# and including the first successful attempt.
573
# (Get the date of the first successful attempt. Then count the number
574
# of attempts made <= that date).
575
# Will return an empty table if the problem has never been
576
# successfully completed.
577
query = """SELECT COUNT(*) FROM problem_attempt
578
WHERE loginid = %d AND problemid = %d AND date <=
579
(SELECT date FROM problem_attempt
580
WHERE loginid = %d AND problemid = %d AND complete = TRUE
582
LIMIT 1);""" % (loginid, problemid, loginid, problemid)
584
result = self.db.query(query)
585
count = int(result.getresult()[0][0])
587
# The user has made at least 1 successful attempt.
588
# Return True for success, and the number of attempts up to and
589
# including the successful one.
592
# Returned 0 rows - this indicates that the problem has not been
594
# Return the total number of attempts, and False for success.
595
query = """SELECT COUNT(*) FROM problem_attempt
596
WHERE loginid = %d AND problemid = %d;""" % (loginid, problemid)
597
result = self.db.query(query)
598
count = int(result.getresult()[0][0])
599
return (False, count)
602
"""Close the DB connection. Do not call any other functions after
603
this. (The behaviour of doing so is undefined).
added
removed
lib/common/db.py
