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