« back to all changes in this revision
Viewing changes to ivle/db.py
- browse files at revision 1090
- compare with another revision
- compare with revision 1791.2.10
- download diff
- download tarball
- view history from revision 1090
- Committer: William Grant
- Date: 2009-01-22 04:47:42 UTC
- mfrom: (1080.1.93 storm)
- Revision ID: grantw@unimelb.edu.au-20090122044742-sa8gnww0ma2bm2rv
Merge Storm branch. ivle.db is dead. Watch out for the schema change.
- files added:
- ivle/database.py
- ivle/rpc
- files removed:
- ivle/auth/autherror.py
- files modified:
- bin/ivle-enrol
added
removed
ivle/db.py
1
# IVLE - Informatics Virtual Learning Environment
2
# Copyright (C) 2007-2008 The University of Melbourne
3
#
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.
8
#
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.
13
#
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
17
18
# Module: Database
19
# Author: Matt Giuca
20
# Date: 15/2/2008
21
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
28
# activities within.
29
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.
33
34
import pg
35
import md5
36
import copy
37
import time
38
39
import ivle.conf
40
from ivle import (caps, user)
41
42
TIMESTAMP_FORMAT = '%Y-%m-%d %H:%M:%S'
43
44
def _escape(val):
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
47
query.
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
* time.struct_time: Returns the time as a quoted string for insertion into
55
a TIMESTAMP column.
56
Raises a DBException if val has an unsupported type.
57
"""
58
# "E'" is postgres's way of making "escape" strings.
59
# Such strings allow backslashes to escape things. Since escape_string
60
# converts a single backslash into two backslashes, it needs to be fed
61
# into E mode.
62
# Ref: http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html
63
# WARNING: PostgreSQL-specific code
64
if val is None:
65
return "NULL"
66
elif isinstance(val, str) or isinstance(val, unicode):
67
return "E'" + pg.escape_string(val) + "'"
68
elif isinstance(val, bool):
69
return "TRUE" if val else "FALSE"
70
elif isinstance(val, int) or isinstance(val, long) \
71
or isinstance(val, float):
72
return str(val)
73
elif isinstance(val, caps.Role):
74
return _escape(str(val))
75
elif isinstance(val, time.struct_time):
76
return _escape(time.strftime(TIMESTAMP_FORMAT, val))
77
else:
78
raise DBException("Attempt to insert an unsupported type "
79
"into the database (%s)" % repr(type(val)))
80
81
def _parse_boolean(val):
82
"""
83
Accepts a boolean as output from the DB (either the string 't' or 'f').
84
Returns a boolean value True or False.
85
Also accepts other values which mean True or False in PostgreSQL.
86
If none match, raises a DBException.
87
"""
88
# On a personal note, what sort of a language allows 7 different values
89
# to denote each of True and False?? (A: SQL)
90
if isinstance(val, bool):
91
return val
92
elif val == 't':
93
return True
94
elif val == 'f':
95
return False
96
elif val == 'true' or val == 'y' or val == 'yes' or val == '1' \
97
or val == 1:
98
return True
99
elif val == 'false' or val == 'n' or val == 'no' or val == '0' \
100
or val == 0:
101
return False
102
else:
103
raise DBException("Invalid boolean value returned from DB")
104
105
def _passhash(password):
106
return md5.md5(password).hexdigest()
107
108
class DBException(Exception):
109
"""A DBException is for bad conditions in the database or bad input to
110
these methods. If Postgres throws an exception it does not get rebadged.
111
This is only for additional exceptions."""
112
pass
113
114
class DB:
115
"""An IVLE database object. This object provides an interface to
116
interacting with the IVLE database without using any external SQL.
117
118
Most methods of this class have an optional dry argument. If true, they
119
will return the SQL query string and NOT actually execute it. (For
120
debugging purposes).
121
122
Methods may throw db.DBException, or any of the pg exceptions as well.
123
(In general, be prepared to catch exceptions!)
124
"""
125
def __init__(self):
126
"""Connects to the database and creates a DB object.
127
Takes no parameters - gets all the DB info from the configuration."""
128
self.open = False
129
self.db = pg.connect(host=ivle.conf.db_host, port=ivle.conf.db_port,
130
dbname=ivle.conf.db_dbname,
131
user=ivle.conf.db_user, passwd=ivle.conf.db_password)
132
self.open = True
133
134
def __del__(self):
135
if self.open:
136
self.db.close()
137
138
# GENERIC DB FUNCTIONS #
139
140
@staticmethod
141
def check_dict(dict, tablefields, disallowed=frozenset([]), must=False):
142
"""Checks that a dict does not contain keys that are not fields
143
of the specified table.
144
dict: A mapping from string keys to values; the keys are checked to
145
see that they correspond to login table fields.
146
tablefields: Collection of strings for field names in the table.
147
Only these fields will be allowed.
148
disallowed: Optional collection of strings for field names that are
149
not allowed.
150
must: If True, the dict MUST contain all fields in tablefields.
151
If False, it may contain any subset of the fields.
152
Returns True if the dict is valid, False otherwise.
153
"""
154
allowed = frozenset(tablefields) - frozenset(disallowed)
155
dictkeys = frozenset(dict.keys())
156
if must:
157
return allowed == dictkeys
158
else:
159
return allowed.issuperset(dictkeys)
160
161
def insert(self, dict, tablename, tablefields, disallowed=frozenset([]),
162
dry=False):
163
"""Inserts a new row in a table, using data from a supplied
164
dictionary (which will be checked by check_dict).
165
dict: Dictionary mapping column names to values. The values may be
166
any of the following types:
167
str, int, long, float, NoneType.
168
tablename: String, name of the table to insert into. Will NOT be
169
escaped - must be a valid identifier.
170
tablefields, disallowed: see check_dict.
171
dry: Returns the SQL query as a string, and does not execute it.
172
Raises a DBException if the dictionary contains invalid fields.
173
"""
174
if not DB.check_dict(dict, tablefields, disallowed):
175
extras = set(dict.keys()) - tablefields
176
raise DBException("Supplied dictionary contains invalid fields. (%s)" % (repr(extras)))
177
# Build two lists concurrently: field names and values, as SQL strings
178
fieldnames = []
179
values = []
180
for k,v in dict.items():
181
fieldnames.append(k)
182
values.append(_escape(v))
183
if len(fieldnames) == 0: return
184
fieldnames = ', '.join(fieldnames)
185
values = ', '.join(values)
186
query = ("INSERT INTO %s (%s) VALUES (%s);"
187
% (tablename, fieldnames, values))
188
if dry: return query
189
self.db.query(query)
190
191
def return_insert(self, dict, tablename, tablefields, returning,
192
disallowed=frozenset([]), dry=False):
193
"""Inserts a new row in a table, using data from a supplied
194
dictionary (which will be checked by check_dict) and returns certain
195
fields as a dict.
196
dict: Dictionary mapping column names to values. The values may be
197
any of the following types:
198
str, int, long, float, NoneType.
199
tablename: String, name of the table to insert into. Will NOT be
200
escaped - must be a valid identifier.
201
returning: List of fields to return, not escaped
202
tablefields, disallowed: see check_dict.
203
dry: Returns the SQL query as a string, and does not execute it.
204
Raises a DBException if the dictionary contains invalid fields.
205
"""
206
if not DB.check_dict(dict, tablefields, disallowed):
207
extras = set(dict.keys()) - tablefields
208
raise DBException("Supplied dictionary contains invalid fields. (%s)" % (repr(extras)))
209
# Build two lists concurrently: field names and values, as SQL strings
210
fieldnames = []
211
values = []
212
for k,v in dict.items():
213
fieldnames.append(k)
214
values.append(_escape(v))
215
if len(fieldnames) == 0: return
216
fieldnames = ', '.join(fieldnames)
217
values = ', '.join(values)
218
returns = ', '.join(returning)
219
query = ("INSERT INTO %s (%s) VALUES (%s) RETURNING (%s);"
220
% (tablename, fieldnames, values, returns))
221
if dry: return query
222
return self.db.query(query)
223
224
225
def update(self, primarydict, updatedict, tablename, tablefields,
226
primary_keys, disallowed_update=frozenset([]), dry=False):
227
"""Updates a row in a table, matching against primarydict to find the
228
row, and using the data in updatedict (which will be checked by
229
check_dict).
230
primarydict: Dict mapping column names to values. The keys should be
231
the table's primary key. Only rows which match this dict's values
232
will be updated.
233
updatedict: Dict mapping column names to values. The columns will be
234
updated with the given values for the matched rows.
235
tablename, tablefields, disallowed_update: See insert.
236
primary_keys: Collection of strings which together form the primary
237
key for this table. primarydict must contain all of these as keys,
238
and only these keys.
239
"""
240
if (not (DB.check_dict(primarydict, primary_keys, must=True)
241
and DB.check_dict(updatedict, tablefields, disallowed_update))):
242
raise DBException("Supplied dictionary contains invalid or missing fields (1).")
243
# Make a list of SQL fragments of the form "field = 'new value'"
244
# These fragments are ALREADY-ESCAPED
245
setlist = []
246
for k,v in updatedict.items():
247
setlist.append("%s = %s" % (k, _escape(v)))
248
wherelist = []
249
for k,v in primarydict.items():
250
wherelist.append("%s = %s" % (k, _escape(v)))
251
if len(setlist) == 0 or len(wherelist) == 0:
252
return
253
# Join the fragments into a comma-separated string
254
setstring = ', '.join(setlist)
255
wherestring = ' AND '.join(wherelist)
256
# Build the whole query as an UPDATE statement
257
query = ("UPDATE %s SET %s WHERE %s;"
258
% (tablename, setstring, wherestring))
259
if dry: return query
260
self.db.query(query)
261
262
def delete(self, primarydict, tablename, primary_keys, dry=False):
263
"""Deletes a row in the table, matching against primarydict to find
264
the row.
265
primarydict, tablename, primary_keys: See update.
266
"""
267
if not DB.check_dict(primarydict, primary_keys, must=True):
268
raise DBException("Supplied dictionary contains invalid or missing fields (2).")
269
wherelist = []
270
for k,v in primarydict.items():
271
wherelist.append("%s = %s" % (k, _escape(v)))
272
if len(wherelist) == 0:
273
return
274
wherestring = ' AND '.join(wherelist)
275
query = ("DELETE FROM %s WHERE %s;" % (tablename, wherestring))
276
if dry: return query
277
self.db.query(query)
278
279
def get_single(self, primarydict, tablename, getfields, primary_keys,
280
error_notfound="No rows found", dry=False):
281
"""Retrieves a single row from a table, returning it as a dictionary
282
mapping field names to values. Matches against primarydict to find the
283
row.
284
primarydict, tablename, primary_keys: See update/delete.
285
getfields: Collection of strings; the field names which will be
286
returned as keys in the dictionary.
287
error_notfound: Error message if 0 rows match.
288
Raises a DBException if 0 rows match, with error_notfound as the msg.
289
Raises an AssertError if >1 rows match (this should not happen if
290
primary_keys is indeed the primary key).
291
"""
292
if not DB.check_dict(primarydict, primary_keys, must=True):
293
raise DBException("Supplied dictionary contains invalid or missing fields (3).")
294
wherelist = []
295
for k,v in primarydict.items():
296
wherelist.append("%s = %s" % (k, _escape(v)))
297
if len(getfields) == 0 or len(wherelist) == 0:
298
return
299
# Join the fragments into a comma-separated string
300
getstring = ', '.join(getfields)
301
wherestring = ' AND '.join(wherelist)
302
# Build the whole query as an SELECT statement
303
query = ("SELECT %s FROM %s WHERE %s;"
304
% (getstring, tablename, wherestring))
305
if dry: return query
306
result = self.db.query(query)
307
# Expecting exactly one
308
if result.ntuples() != 1:
309
# It should not be possible for ntuples to be greater than 1
310
assert (result.ntuples() < 1)
311
raise DBException(error_notfound)
312
# Return as a dictionary
313
return result.dictresult()[0]
314
315
def get_all(self, tablename, getfields, dry=False):
316
"""Retrieves all rows from a table, returning it as a list of
317
dictionaries mapping field names to values.
318
tablename, getfields: See get_single.
319
"""
320
if len(getfields) == 0:
321
return
322
getstring = ', '.join(getfields)
323
query = ("SELECT %s FROM %s;" % (getstring, tablename))
324
if dry: return query
325
return self.db.query(query).dictresult()
326
327
def start_transaction(self, dry=False):
328
"""Starts a DB transaction.
329
Will not commit any changes until self.commit() is called.
330
"""
331
query = "START TRANSACTION;"
332
if dry: return query
333
self.db.query(query)
334
335
def commit(self, dry=False):
336
"""Commits (ends) a DB transaction.
337
Commits all changes since the call to start_transaction.
338
"""
339
query = "COMMIT;"
340
if dry: return query
341
self.db.query(query)
342
343
def rollback(self, dry=False):
344
"""Rolls back (ends) a DB transaction, undoing all changes since the
345
call to start_transaction.
346
"""
347
query = "ROLLBACK;"
348
if dry: return query
349
self.db.query(query)
350
351
# USER MANAGEMENT FUNCTIONS #
352
353
login_primary = frozenset(["login"])
354
login_fields_list = [
355
"login", "passhash", "state", "unixid", "email", "nick", "fullname",
356
"rolenm", "studentid", "acct_exp", "pass_exp", "last_login", "svn_pass"
357
]
358
login_fields = frozenset(login_fields_list)
359
360
def create_user(self, user_obj=None, dry=False, **kwargs):
361
"""Creates a user login entry in the database.
362
Two ways to call this - passing a user object, or passing
363
all fields as separate arguments.
364
365
Either pass a "user_obj" as the first argument (in which case other
366
fields will be ignored), or pass all fields as arguments.
367
368
All user fields are to be passed as args. The argument names
369
are the field names of the "login" table of the DB schema.
370
However, instead of supplying a "passhash", you must supply a
371
"password" argument, which will be hashed internally.
372
Also "state" must not given explicitly; it is implicitly set to
373
"no_agreement".
374
Raises an exception if the user already exists, or the dict contains
375
invalid keys or is missing required keys.
376
"""
377
if 'passhash' in kwargs:
378
raise DBException("Supplied arguments include passhash (invalid) (1).")
379
# Make a copy of the dict. Change password to passhash (hashing it),
380
# and set 'state' to "no_agreement".
381
if user_obj is None:
382
# Use the kwargs
383
fields = copy.copy(kwargs)
384
else:
385
# Use the user object
386
fields = dict(user_obj)
387
if 'password' in fields:
388
fields['passhash'] = _passhash(fields['password'])
389
del fields['password']
390
if 'role' in fields:
391
# Convert role to rolenm
392
fields['rolenm'] = str(user_obj.role)
393
del fields['role']
394
if user_obj is None:
395
fields['state'] = "no_agreement"
396
# else, we'll trust the user, but it SHOULD be "no_agreement"
397
# (We can't change it because then the user object would not
398
# reflect the DB).
399
if 'local_password' in fields:
400
del fields['local_password']
401
# Execute the query.
402
return self.insert(fields, "login", self.login_fields, dry=dry)
403
404
def update_user(self, login, dry=False, **kwargs):
405
"""Updates fields of a particular user. login is the name of the user
406
to update. The dict contains the fields which will be modified, and
407
their new values. If any value is omitted from the dict, it does not
408
get modified. login and studentid may not be modified.
409
Passhash may be modified by supplying a "password" field, in
410
cleartext, not a hashed password.
411
412
Note that no checking is done. It is expected this function is called
413
by a trusted source. In particular, it allows the password to be
414
changed without knowing the old password. The caller should check
415
that the user knows the existing password before calling this function
416
with a new one.
417
"""
418
if 'passhash' in kwargs:
419
raise DBException("Supplied arguments include passhash (invalid) (2).")
420
if "password" in kwargs:
421
kwargs = copy.copy(kwargs)
422
kwargs['passhash'] = _passhash(kwargs['password'])
423
del kwargs['password']
424
return self.update({"login": login}, kwargs, "login",
425
self.login_fields, self.login_primary, ["login", "studentid"],
426
dry=dry)
427
428
def get_user(self, login, dry=False):
429
"""Given a login, returns a User object containing details looked up
430
in the DB.
431
432
Raises a DBException if the login is not found in the DB.
433
"""
434
userdict = self.get_single({"login": login}, "login",
435
self.login_fields, self.login_primary,
436
error_notfound="get_user: No user with that login name", dry=dry)
437
if dry:
438
return userdict # Query string
439
# Package into a User object
440
return user.User(**userdict)
441
442
def get_users(self, dry=False):
443
"""Returns a list of all users in the DB, as User objects.
444
"""
445
userdicts = self.get_all("login", self.login_fields, dry=dry)
446
if dry:
447
return userdicts # Query string
448
# Package into User objects
449
return [user.User(**userdict) for userdict in userdicts]
450
451
def get_user_loginid(self, login, dry=False):
452
"""Given a login, returns the integer loginid for this user.
453
454
Raises a DBException if the login is not found in the DB.
455
"""
456
userdict = self.get_single({"login": login}, "login",
457
['loginid'], self.login_primary,
458
error_notfound="get_user_loginid: No user with that login name",
459
dry=dry)
460
if dry:
461
return userdict # Query string
462
return userdict['loginid']
463
464
def user_authenticate(self, login, password, dry=False):
465
"""Performs a password authentication on a user. Returns True if
466
"passhash" is the correct passhash for the given login, False
467
if the passhash does not match the password in the DB,
468
and None if the passhash in the DB is NULL.
469
Also returns False if the login does not exist (so if you want to
470
differentiate these cases, use get_user and catch an exception).
471
"""
472
query = ("SELECT passhash FROM login WHERE login = %s;"
473
% _escape(login))
474
if dry: return query
475
result = self.db.query(query)
476
if result.ntuples() == 1:
477
# Valid username. Check password.
478
passhash = result.getresult()[0][0]
479
if passhash is None:
480
return None
481
return _passhash(password) == passhash
482
else:
483
return False
484
485
# PROBLEM AND PROBLEM ATTEMPT FUNCTIONS #
486
487
def get_problem_problemid(self, exercisename, dry=False):
488
"""Given an exercise name, returns the associated problemID.
489
If the exercise name is NOT in the database, it inserts it and returns
490
the new problemID. Hence this may mutate the DB, but is idempotent.
491
"""
492
try:
493
d = self.get_single({"identifier": exercisename}, "problem",
494
['problemid'], frozenset(["identifier"]),
495
dry=dry)
496
if dry:
497
return d # Query string
498
except DBException:
499
if dry:
500
# Shouldn't try again, must have failed for some other reason
501
raise
502
# if we failed to get a problemid, it was probably because
503
# the exercise wasn't in the db. So lets insert it!
504
#
505
# The insert can fail if someone else simultaneously does
506
# the insert, so if the insert fails, we ignore the problem.
507
try:
508
self.insert({'identifier': exercisename}, "problem",
509
frozenset(['identifier']))
510
except Exception, e:
511
pass
512
513
# Assuming the insert succeeded, we should be able to get the
514
# problemid now.
515
d = self.get_single({"identifier": exercisename}, "problem",
516
['problemid'], frozenset(["identifier"]))
517
518
return d['problemid']
519
520
def insert_problem_attempt(self, login, exercisename, date, complete,
521
attempt, dry=False):
522
"""Inserts the details of a problem attempt into the database.
523
exercisename: Name of the exercise. (identifier field of problem
524
table). If this exercise does not exist, also creates a new row in
525
the problem table for this exercise name.
526
login: Name of the user submitting the attempt. (login field of the
527
login table).
528
date: struct_time, the date this attempt was made.
529
complete: bool. Whether the test passed or not.
530
attempt: Text of the attempt.
531
532
Note: Even if dry, will still physically call get_problem_problemid,
533
which may mutate the DB, and get_user_loginid, which may fail.
534
"""
535
problemid = self.get_problem_problemid(exercisename)
536
loginid = self.get_user_loginid(login) # May raise a DBException
537
538
return self.insert({
539
'problemid': problemid,
540
'loginid': loginid,
541
'date': date,
542
'complete': complete,
543
'attempt': attempt,
544
}, 'problem_attempt',
545
frozenset(['problemid','loginid','date','complete','attempt']),
546
dry=dry)
547
548
def write_problem_save(self, login, exercisename, date, text, dry=False):
549
"""Writes text to the problem_save table (for when the user saves an
550
exercise). Creates a new row, or overwrites an existing one if the
551
user has already saved that problem.
552
(Unlike problem_attempt, does not keep historical records).
553
"""
554
problemid = self.get_problem_problemid(exercisename)
555
loginid = self.get_user_loginid(login) # May raise a DBException
556
557
try:
558
return self.insert({
559
'problemid': problemid,
560
'loginid': loginid,
561
'date': date,
562
'text': text,
563
}, 'problem_save',
564
frozenset(['problemid','loginid','date','text']),
565
dry=dry)
566
except pg.ProgrammingError:
567
# May have failed because this problemid/loginid row already
568
# exists (they have a unique key constraint).
569
# Do an update instead.
570
if dry:
571
# Shouldn't try again, must have failed for some other reason
572
raise
573
self.update({
574
'problemid': problemid,
575
'loginid': loginid,
576
},
577
{
578
'date': date,
579
'text': text,
580
}, "problem_save",
581
frozenset(['date', 'text']),
582
frozenset(['problemid', 'loginid']))
583
584
def get_problem_stored_text(self, login, exercisename, dry=False):
585
"""Given a login name and exercise name, returns the text of the
586
last saved/submitted attempt for this question.
587
Returns None if the user has not saved or made an attempt on this
588
problem.
589
(If the user has both saved and submitted, it returns whichever was
590
made last).
591
592
Note: Even if dry, will still physically call get_problem_problemid,
593
which may mutate the DB, and get_user_loginid, which may fail.
594
"""
595
problemid = self.get_problem_problemid(exercisename)
596
loginid = self.get_user_loginid(login) # May raise a DBException
597
# This very complex query finds all submissions made by this user for
598
# this problem, as well as the save made by this user for this
599
# problem, and returns the text of the newest one.
600
# (Whichever is newer out of the save or the submit).
601
query = """SELECT text FROM
602
(
603
(SELECT * FROM problem_save WHERE loginid = %d AND problemid = %d)
604
UNION
605
(SELECT problemid, loginid, date, text FROM problem_attempt
606
AS problem_attempt (problemid, loginid, date, text)
607
WHERE loginid = %d AND problemid = %d AND active)
608
)
609
AS _
610
ORDER BY date DESC
611
LIMIT 1;""" % (loginid, problemid, loginid, problemid)
612
if dry: return query
613
result = self.db.query(query)
614
if result.ntuples() == 1:
615
# The user has made at least 1 attempt. Return the newest.
616
return result.getresult()[0][0]
617
else:
618
return None
619
620
def get_problem_attempts(self, login, exercisename, allow_inactive=True,
621
dry=False):
622
"""Given a login name and exercise name, returns a list of dicts, one
623
for each attempt made for that exercise.
624
Dicts are {'date': 'formatted_time', 'complete': bool}.
625
Ordered with the newest first.
626
627
Note: By default, returns de-activated problem attempts (unlike
628
get_problem_stored_text).
629
If allow_inactive is False, will not return disabled attempts.
630
631
Note: Even if dry, will still physically call get_problem_problemid,
632
which may mutate the DB, and get_user_loginid, which may fail.
633
"""
634
problemid = self.get_problem_problemid(exercisename)
635
loginid = self.get_user_loginid(login) # May raise a DBException
636
andactive = '' if allow_inactive else ' AND active'
637
query = """SELECT date, complete FROM problem_attempt
638
WHERE loginid = %d AND problemid = %d%s
639
ORDER BY date DESC;""" % (loginid, problemid, andactive)
640
if dry: return query
641
result = self.db.query(query).getresult()
642
# Make into dicts (could use dictresult, but want to convert values)
643
return [{'date': date, 'complete': _parse_boolean(complete)}
644
for date, complete in result]
645
646
def get_problem_attempt(self, login, exercisename, as_of,
647
allow_inactive=True, dry=False):
648
"""Given a login name, exercise name, and struct_time, returns the
649
text of the submitted attempt for this question as of that date.
650
Returns None if the user had not made an attempt on this problem at
651
that date.
652
653
Note: By default, returns de-activated problem attempts (unlike
654
get_problem_stored_text).
655
If allow_inactive is False, will not return disabled attempts.
656
657
Note: Even if dry, will still physically call get_problem_problemid,
658
which may mutate the DB, and get_user_loginid, which may fail.
659
"""
660
problemid = self.get_problem_problemid(exercisename)
661
loginid = self.get_user_loginid(login) # May raise a DBException
662
# Very similar to query in get_problem_stored_text, but without
663
# looking in problem_save, and restricting to a certain date.
664
andactive = '' if allow_inactive else ' AND active'
665
query = """SELECT attempt FROM problem_attempt
666
WHERE loginid = %d AND problemid = %d%s AND date <= %s
667
ORDER BY date DESC
668
LIMIT 1;""" % (loginid, problemid, andactive, _escape(as_of))
669
if dry: return query
670
result = self.db.query(query)
671
if result.ntuples() == 1:
672
# The user has made at least 1 attempt. Return the newest.
673
return result.getresult()[0][0]
674
else:
675
return None
676
677
def get_problem_status(self, login, exercisename, dry=False):
678
"""Given a login name and exercise name, returns information about the
679
user's performance on that problem.
680
Returns a tuple of:
681
- A boolean, whether they have successfully passed this exercise.
682
- An int, the number of attempts they have made up to and
683
including the first successful attempt (or the total number of
684
attempts, if not yet successful).
685
Note: exercisename may be an int, in which case it will be directly
686
used as the problemid.
687
"""
688
if isinstance(exercisename, int):
689
problemid = exercisename
690
else:
691
problemid = self.get_problem_problemid(exercisename)
692
loginid = self.get_user_loginid(login) # May raise a DBException
693
694
# ASSUME that it is completed, get the total number of attempts up to
695
# and including the first successful attempt.
696
# (Get the date of the first successful attempt. Then count the number
697
# of attempts made <= that date).
698
# Will return an empty table if the problem has never been
699
# successfully completed.
700
query = """SELECT COUNT(*) FROM problem_attempt
701
WHERE loginid = %d AND problemid = %d AND active AND date <=
702
(SELECT date FROM problem_attempt
703
WHERE loginid = %d AND problemid = %d AND complete AND active
704
ORDER BY date ASC
705
LIMIT 1);""" % (loginid, problemid, loginid, problemid)
706
if dry: return query
707
result = self.db.query(query)
708
count = int(result.getresult()[0][0])
709
if count > 0:
710
# The user has made at least 1 successful attempt.
711
# Return True for success, and the number of attempts up to and
712
# including the successful one.
713
return (True, count)
714
else:
715
# Returned 0 rows - this indicates that the problem has not been
716
# completed.
717
# Return the total number of attempts, and False for success.
718
query = """SELECT COUNT(*) FROM problem_attempt
719
WHERE loginid = %d AND problemid = %d AND active;""" % (loginid, problemid)
720
result = self.db.query(query)
721
count = int(result.getresult()[0][0])
722
return (False, count)
723
724
# WORKSHEET/PROBLEM ASSOCIATION AND MARKS CALCULATION
725
726
def get_worksheet_mtime(self, subject, worksheet, dry=False):
727
"""
728
For a given subject/worksheet name, gets the time the worksheet was
729
last updated in the DB, if any.
730
This can be used to check if there is a newer version on disk.
731
Returns the timestamp as a time.struct_time, or None if the worksheet
732
is not found or has no stored mtime.
733
"""
734
try:
735
r = self.get_single(
736
{"subject": subject, "identifier": worksheet},
737
"worksheet", ["mtime"], ["subject", "identifier"],
738
dry=dry)
739
except DBException:
740
# Assume the worksheet is not in the DB
741
return None
742
if dry:
743
return r
744
if r["mtime"] is None:
745
return None
746
return time.strptime(r["mtime"], TIMESTAMP_FORMAT)
747
748
def create_worksheet(self, subject, worksheet, problems=None,
749
assessable=None):
750
"""
751
Inserts or updates rows in the worksheet and worksheet_problems
752
tables, to create a worksheet in the database.
753
This atomically performs all operations. If the worksheet is already
754
in the DB, removes it and all its associated problems and rebuilds.
755
Sets the timestamp to the current time.
756
757
problems is a collection of pairs. The first element of the pair is
758
the problem identifier ("identifier" column of the problem table). The
759
second element is an optional boolean, "optional". This can be omitted
760
(so it's a 1-tuple), and then it will default to False.
761
762
Problems and assessable are optional, and if omitted, will not change
763
the existing data. If the worksheet does not yet exist, and assessable
764
is omitted, it defaults to False.
765
766
Note: As with get_problem_problemid, if a problem name is not in the
767
DB, it will be added to the problem table.
768
"""
769
self.start_transaction()
770
try:
771
# Use the current time as the "mtime" field
772
mtime = time.localtime()
773
try:
774
# Get the worksheetid
775
r = self.get_single(
776
{"subject": subject, "identifier": worksheet},
777
"worksheet", ["worksheetid"], ["subject", "identifier"])
778
worksheetid = r["worksheetid"]
779
780
# Delete any problems which might exist, if problems is
781
# supplied. If it isn't, keep the existing ones.
782
if problems is not None:
783
query = ("DELETE FROM worksheet_problem "
784
"WHERE worksheetid = %d;" % worksheetid)
785
self.db.query(query)
786
# Update the row with the new details
787
if assessable is None:
788
query = ("UPDATE worksheet "
789
"SET mtime = %s WHERE worksheetid = %d;"
790
% (_escape(mtime), worksheetid))
791
else:
792
query = ("UPDATE worksheet "
793
"SET assessable = %s, mtime = %s "
794
"WHERE worksheetid = %d;"
795
% (_escape(assessable), _escape(mtime), worksheetid))
796
self.db.query(query)
797
except DBException:
798
# Assume the worksheet is not in the DB
799
# If assessable is not supplied, default to False.
800
if assessable is None:
801
assessable = False
802
# Create the worksheet row
803
query = ("INSERT INTO worksheet "
804
"(subject, identifier, assessable, mtime) "
805
"VALUES (%s, %s, %s, %s);"""
806
% (_escape(subject), _escape(worksheet),
807
_escape(assessable), _escape(mtime)))
808
self.db.query(query)
809
# Now get the worksheetid again - should succeed
810
r = self.get_single(
811
{"subject": subject, "identifier": worksheet},
812
"worksheet", ["worksheetid"], ["subject", "identifier"])
813
worksheetid = r["worksheetid"]
814
815
# Now insert each problem into the worksheet_problem table
816
if problems is not None:
817
for problem in problems:
818
if isinstance(problem, tuple):
819
prob_identifier = problem[0]
820
try:
821
optional = problem[1]
822
except IndexError:
823
optional = False
824
else:
825
prob_identifier = problem
826
optional = False
827
problemid = self.get_problem_problemid(prob_identifier)
828
query = ("INSERT INTO worksheet_problem "
829
"(worksheetid, problemid, optional) "
830
"VALUES (%d, %d, %s);"
831
% (worksheetid, problemid, _escape(optional)))
832
self.db.query(query)
833
834
self.commit()
835
except:
836
self.rollback()
837
raise
838
839
def set_worksheet_assessable(self, subject, worksheet, assessable,
840
dry=False):
841
"""
842
Sets the "assessable" field of a worksheet without updating the mtime.
843
844
IMPORTANT: This will NOT update the mtime. This is designed to allow
845
updates which did not come from the worksheet XML file. It would be
846
bad to update the mtime without consulting the XML file because then
847
it would appear the database is up to date, when it isn't.
848
849
Therefore, call this method if you are getting "assessable"
850
information from outside the worksheet XML file (eg. from the subject
851
XML file).
852
853
Unlike create_worksheet, raises a DBException if the worksheet is not
854
in the database.
855
"""
856
return self.update({"subject": subject, "identifier": worksheet},
857
{"assessable": assessable}, "worksheet", ["assessable"],
858
["subject", "identifier"], dry=dry)
859
860
def worksheet_is_assessable(self, subject, worksheet, dry=False):
861
r = self.get_single(
862
{"subject": subject, "identifier": worksheet},
863
"worksheet", ["assessable"], ["subject", "identifier"], dry=dry)
864
return _parse_boolean(r["assessable"])
865
866
def calculate_score_worksheet(self, login, subject, worksheet):
867
"""
868
Calculates the score for a user on a given worksheet.
869
Returns a 4-tuple of ints, consisting of:
870
(No. mandatory exercises completed,
871
Total no. mandatory exercises,
872
No. optional exercises completed,
873
Total no. optional exercises)
874
"""
875
self.start_transaction()
876
try:
877
mand_done = 0
878
mand_total = 0
879
opt_done = 0
880
opt_total = 0
881
# Get a list of problems and optionality for all problems in the
882
# worksheet
883
query = ("""SELECT problemid, optional FROM worksheet_problem
884
WHERE worksheetid = (SELECT worksheetid FROM worksheet
885
WHERE subject = %s and identifier = %s);"""
886
% (_escape(subject), _escape(worksheet)))
887
result = self.db.query(query)
888
# Now get the student's pass/fail for each problem in this worksheet
889
for problemid, optional in result.getresult():
890
done, _ = self.get_problem_status(login, problemid)
891
# done is a bool, whether this student has completed that
892
# problem
893
if _parse_boolean(optional):
894
opt_total += 1
895
if done: opt_done += 1
896
else:
897
mand_total += 1
898
if done: mand_done += 1
899
self.commit()
900
except:
901
self.rollback()
902
raise
903
return mand_done, mand_total, opt_done, opt_total
904
905
# ENROLMENT INFORMATION
906
907
def add_enrolment(self, login, subj_code, semester, year=None, dry=False):
908
"""
909
Enrol a student in the given offering of a subject.
910
Returns True on success, False on failure (which usually means either
911
the student is already enrolled in the subject, the student was not
912
found, or no offering existed with the given details).
913
The return value can usually be ignored.
914
"""
915
subj_code = str(subj_code)
916
semester = str(semester)
917
if year is None:
918
year = str(time.gmtime().tm_year)
919
else:
920
year = str(year)
921
query = """\
922
INSERT INTO enrolment (loginid, offeringid)
923
VALUES (
924
(SELECT loginid FROM login WHERE login=%s),
925
(SELECT offeringid
926
FROM offering, subject, semester
927
WHERE subject.subjectid = offering.subject
928
AND semester.semesterid = offering.semesterid
929
AND subj_code=%s AND semester=%s AND year=%s)
930
);""" % (_escape(login), _escape(subj_code), _escape(semester),
931
_escape(year))
932
if dry:
933
return query
934
try:
935
result = self.db.query(query)
936
except pg.ProgrammingError:
937
return False
938
return True
939
940
# SUBJECTS AND ENROLEMENT
941
942
def get_subjects(self, dry=False):
943
"""
944
Get all subjects in IVLE.
945
Returns a list of dicts (all values strings), with the keys:
946
subj_code, subj_name, subj_short_name, url
947
"""
948
return self.get_all("subject",
949
("subjectid", "subj_code", "subj_name", "subj_short_name", "url"),
950
dry)
951
952
def get_offering_semesters(self, subjectid, dry=False):
953
"""
954
Get the semester information for a subject as well as providing
955
information about if the subject is active and which semester it is in.
956
"""
957
query = """\
958
SELECT offeringid, subj_name, year, semester, active
959
FROM semester, offering, subject
960
WHERE offering.semesterid = semester.semesterid AND
961
offering.subject = subject.subjectid AND
962
offering.subject = %d;"""%subjectid
963
if dry:
964
return query
965
results = self.db.query(query).dictresult()
966
# Parse boolean varibles
967
for result in results:
968
result['active'] = _parse_boolean(result['active'])
969
return results
970
971
def get_offering_members(self, offeringid, dry=False):
972
"""
973
Gets the logins of all the people enroled in an offering
974
"""
975
query = """\
976
SELECT login.login AS login, login.fullname AS fullname
977
FROM login, enrolment
978
WHERE login.loginid = enrolment.loginid AND
979
enrolment.offeringid = %d
980
ORDER BY login.login;"""%offeringid
981
if dry:
982
return query
983
return self.db.query(query).dictresult()
984
985
986
def get_enrolment(self, login, dry=False):
987
"""
988
Get all offerings (in IVLE) the student is enrolled in.
989
Returns a list of dicts (all values strings), with the keys:
990
offeringid, subj_code, subj_name, subj_short_name, year, semester, url
991
"""
992
query = """\
993
SELECT offering.offeringid, subj_code, subj_name, subj_short_name,
994
semester.year, semester.semester, subject.url
995
FROM login, enrolment, offering, subject, semester
996
WHERE enrolment.offeringid=offering.offeringid
997
AND login.loginid=enrolment.loginid
998
AND offering.subject=subject.subjectid
999
AND semester.semesterid=offering.semesterid
1000
AND enrolment.active
1001
AND login=%s;""" % _escape(login)
1002
if dry:
1003
return query
1004
return self.db.query(query).dictresult()
1005
1006
def get_enrolment_groups(self, login, offeringid, dry=False):
1007
"""
1008
Get all groups the user is member of in the given offering.
1009
Returns a list of dicts (all values strings), with the keys:
1010
name, nick
1011
"""
1012
query = """\
1013
SELECT project_group.groupnm as name, project_group.nick as nick
1014
FROM project_set, project_group, group_member, login
1015
WHERE login.login=%s
1016
AND project_set.offeringid=%s
1017
AND group_member.loginid=login.loginid
1018
AND project_group.groupid=group_member.groupid
1019
AND project_group.projectsetid=project_set.projectsetid
1020
""" % (_escape(login), _escape(offeringid))
1021
if dry:
1022
return query
1023
return self.db.query(query).dictresult()
1024
1025
def get_subjects_status(self, login, dry=False):
1026
"""
1027
Get all subjects in IVLE, split into lists of enrolled and unenrolled
1028
subjects.
1029
Returns a tuple of lists (enrolled, unenrolled) of dicts
1030
(all values strings) with the keys:
1031
subj_code, subj_name, subj_short_name, url
1032
"""
1033
enrolments = self.get_enrolment(login)
1034
all_subjects = self.get_subjects()
1035
1036
enrolled_set = set(x['subj_code'] for x in enrolments)
1037
1038
enrolled_subjects = [x for x in all_subjects
1039
if x['subj_code'] in enrolled_set]
1040
unenrolled_subjects = [x for x in all_subjects
1041
if x['subj_code'] not in enrolled_set]
1042
enrolled_subjects.sort(key=lambda x: x['subj_code'])
1043
unenrolled_subjects.sort(key=lambda x: x['subj_code'])
1044
return (enrolled_subjects, unenrolled_subjects)
1045
1046
1047
# PROJECT GROUPS
1048
def get_groups_by_user(self, login, offeringid=None, dry=False):
1049
"""
1050
Get all project groups the student is in, corresponding to a
1051
particular subject offering (or all offerings, if omitted).
1052
Returns a list of tuples:
1053
(int groupid, str groupnm, str group_nick, bool is_member).
1054
(Note: If is_member is false, it means they have just been invited to
1055
this group, not a member).
1056
"""
1057
if offeringid is None:
1058
and_offering = ""
1059
else:
1060
and_projectset_table = ", project_set"
1061
and_offering = """
1062
AND project_group.projectsetid = project_set.projectsetid
1063
AND project_set.offeringid = %s""" % _escape(offeringid)
1064
# Union both the groups this user is a member of, and the groups this
1065
# user is invited to.
1066
query = """\
1067
SELECT project_group.groupid, groupnm, project_group.nick, True
1068
FROM project_group, group_member, login %(and_projectset_table)s
1069
WHERE project_group.groupid = group_member.groupid
1070
AND group_member.loginid = login.loginid
1071
AND login = %(login)s
1072
%(and_offering)s
1073
UNION
1074
SELECT project_group.groupid, groupnm, project_group.nick, False
1075
FROM project_group, group_invitation, login %(and_projectset_table)s
1076
WHERE project_group.groupid = group_invitation.groupid
1077
AND group_invitation.loginid = login.loginid
1078
AND login = %(login)s
1079
%(and_offering)s
1080
;""" % {"login": _escape(login), "and_offering": and_offering,
1081
"and_projectset_table": and_projectset_table}
1082
if dry:
1083
return query
1084
# Convert 't' -> True, 'f' -> False
1085
return [(groupid, groupnm, nick, ismember == 't')
1086
for groupid, groupnm, nick, ismember
1087
in self.db.query(query).getresult()]
1088
1089
def get_offering_info(self, projectsetid, dry=False):
1090
"""Takes information from projectset and returns useful information
1091
about the subject and semester. Returns as a dictionary.
1092
"""
1093
query = """\
1094
SELECT subjectid, subj_code, subj_name, subj_short_name, url, year, semester,
1095
active
1096
FROM subject, offering, semester, project_set
1097
WHERE offering.subject = subject.subjectid AND
1098
offering.semesterid = semester.semesterid AND
1099
project_set.offeringid = offering.offeringid AND
1100
project_set.projectsetid = %d;"""%projectsetid
1101
if dry:
1102
return query
1103
return self.db.query(query).dictresult()[0]
1104
1105
def get_projectgroup_members(self, groupid, dry=False):
1106
"""Returns the logins of all students in a project group
1107
"""
1108
query = """\
1109
SELECT login.login as login, login.fullname as fullname
1110
FROM login, group_member
1111
WHERE login.loginid = group_member.loginid AND
1112
group_member.groupid = %d
1113
ORDER BY login.login;"""%groupid
1114
if dry:
1115
return query
1116
return self.db.query(query).dictresult()
1117
1118
def get_projectsets_by_offering(self, offeringid, dry=False):
1119
"""Returns all the projectsets in a particular offering"""
1120
query = """\
1121
SELECT projectsetid, max_students_per_group
1122
FROM project_set
1123
WHERE project_set.offeringid = %d;"""%offeringid
1124
if dry:
1125
return query
1126
return self.db.query(query).dictresult()
1127
1128
def get_groups_by_projectset(self, projectsetid, dry=False):
1129
"""Returns all the groups that are in a particular projectset"""
1130
query = """\
1131
SELECT groupid, groupnm, nick, createdby, epoch
1132
FROM project_group
1133
WHERE project_group.projectsetid = %d;"""%projectsetid
1134
if dry:
1135
return query
1136
return self.db.query(query).dictresult()
1137
1138
def close(self):
1139
"""Close the DB connection. Do not call any other functions after
1140
this. (The behaviour of doing so is undefined).
1141
"""
1142
self.db.close()
1143
self.open = False
Loggerhead is a web-based interface for Breezy
Version: 2.0.1