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
* time.struct_time: Returns the time as a quoted string for insertion into
56
Raises a DBException if val has an unsupported type.
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
62
# Ref: http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html
63
# WARNING: PostgreSQL-specific code
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):
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))
78
raise DBException("Attempt to insert an unsupported type "
79
"into the database (%s)" % repr(type(val)))
81
def _parse_boolean(val):
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.
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):
96
elif val == 'true' or val == 'y' or val == 'yes' or val == '1' \
99
elif val == 'false' or val == 'n' or val == 'no' or val == '0' \
103
raise DBException("Invalid boolean value returned from DB")
105
def _passhash(password):
106
return md5.md5(password).hexdigest()
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."""
115
"""An IVLE database object. This object provides an interface to
116
interacting with the IVLE database without using any external SQL.
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
122
Methods may throw db.DBException, or any of the pg exceptions as well.
123
(In general, be prepared to catch exceptions!)
126
"""Connects to the database and creates a DB object.
127
Takes no parameters - gets all the DB info from the configuration."""
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)
137
# GENERIC DB FUNCTIONS #
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
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.
153
allowed = frozenset(tablefields) - frozenset(disallowed)
154
dictkeys = frozenset(dict.keys())
156
return allowed == dictkeys
158
return allowed.issuperset(dictkeys)
160
def insert(self, dict, tablename, tablefields, disallowed=frozenset([]),
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.
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
179
for k,v in dict.items():
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))
190
def update(self, primarydict, updatedict, tablename, tablefields,
191
primary_keys, disallowed_update=frozenset([]), dry=False):
192
"""Updates a row in a table, matching against primarydict to find the
193
row, and using the data in updatedict (which will be checked by
195
primarydict: Dict mapping column names to values. The keys should be
196
the table's primary key. Only rows which match this dict's values
198
updatedict: Dict mapping column names to values. The columns will be
199
updated with the given values for the matched rows.
200
tablename, tablefields, disallowed_update: See insert.
201
primary_keys: Collection of strings which together form the primary
202
key for this table. primarydict must contain all of these as keys,
205
if (not (DB.check_dict(primarydict, primary_keys, must=True)
206
and DB.check_dict(updatedict, tablefields, disallowed_update))):
207
raise DBException("Supplied dictionary contains invalid or missing fields (1).")
208
# Make a list of SQL fragments of the form "field = 'new value'"
209
# These fragments are ALREADY-ESCAPED
211
for k,v in updatedict.items():
212
setlist.append("%s = %s" % (k, _escape(v)))
214
for k,v in primarydict.items():
215
wherelist.append("%s = %s" % (k, _escape(v)))
216
if len(setlist) == 0 or len(wherelist) == 0:
218
# Join the fragments into a comma-separated string
219
setstring = ', '.join(setlist)
220
wherestring = ' AND '.join(wherelist)
221
# Build the whole query as an UPDATE statement
222
query = ("UPDATE %s SET %s WHERE %s;"
223
% (tablename, setstring, wherestring))
227
def delete(self, primarydict, tablename, primary_keys, dry=False):
228
"""Deletes a row in the table, matching against primarydict to find
230
primarydict, tablename, primary_keys: See update.
232
if not DB.check_dict(primarydict, primary_keys, must=True):
233
raise DBException("Supplied dictionary contains invalid or missing fields (2).")
235
for k,v in primarydict.items():
236
wherelist.append("%s = %s" % (k, _escape(v)))
237
if len(wherelist) == 0:
239
wherestring = ' AND '.join(wherelist)
240
query = ("DELETE FROM %s WHERE %s;" % (tablename, wherestring))
244
def get_single(self, primarydict, tablename, getfields, primary_keys,
245
error_notfound="No rows found", dry=False):
246
"""Retrieves a single row from a table, returning it as a dictionary
247
mapping field names to values. Matches against primarydict to find the
249
primarydict, tablename, primary_keys: See update/delete.
250
getfields: Collection of strings; the field names which will be
251
returned as keys in the dictionary.
252
error_notfound: Error message if 0 rows match.
253
Raises a DBException if 0 rows match, with error_notfound as the msg.
254
Raises an AssertError if >1 rows match (this should not happen if
255
primary_keys is indeed the primary key).
257
if not DB.check_dict(primarydict, primary_keys, must=True):
258
raise DBException("Supplied dictionary contains invalid or missing fields (3).")
260
for k,v in primarydict.items():
261
wherelist.append("%s = %s" % (k, _escape(v)))
262
if len(getfields) == 0 or len(wherelist) == 0:
264
# Join the fragments into a comma-separated string
265
getstring = ', '.join(getfields)
266
wherestring = ' AND '.join(wherelist)
267
# Build the whole query as an SELECT statement
268
query = ("SELECT %s FROM %s WHERE %s;"
269
% (getstring, tablename, wherestring))
271
result = self.db.query(query)
272
# Expecting exactly one
273
if result.ntuples() != 1:
274
# It should not be possible for ntuples to be greater than 1
275
assert (result.ntuples() < 1)
276
raise DBException(error_notfound)
277
# Return as a dictionary
278
return result.dictresult()[0]
280
def get_all(self, tablename, getfields, dry=False):
281
"""Retrieves all rows from a table, returning it as a list of
282
dictionaries mapping field names to values.
283
tablename, getfields: See get_single.
285
if len(getfields) == 0:
287
getstring = ', '.join(getfields)
288
query = ("SELECT %s FROM %s;" % (getstring, tablename))
290
return self.db.query(query).dictresult()
292
def start_transaction(self, dry=False):
293
"""Starts a DB transaction.
294
Will not commit any changes until self.commit() is called.
296
query = "START TRANSACTION;"
300
def commit(self, dry=False):
301
"""Commits (ends) a DB transaction.
302
Commits all changes since the call to start_transaction.
308
def rollback(self, dry=False):
309
"""Rolls back (ends) a DB transaction, undoing all changes since the
310
call to start_transaction.
316
# USER MANAGEMENT FUNCTIONS #
318
login_primary = frozenset(["login"])
319
login_fields_list = [
320
"login", "passhash", "state", "unixid", "email", "nick", "fullname",
321
"rolenm", "studentid", "acct_exp", "pass_exp", "last_login", "svn_pass"
323
login_fields = frozenset(login_fields_list)
325
def create_user(self, user_obj=None, dry=False, **kwargs):
326
"""Creates a user login entry in the database.
327
Two ways to call this - passing a user object, or passing
328
all fields as separate arguments.
330
Either pass a "user_obj" as the first argument (in which case other
331
fields will be ignored), or pass all fields as arguments.
333
All user fields are to be passed as args. The argument names
334
are the field names of the "login" table of the DB schema.
335
However, instead of supplying a "passhash", you must supply a
336
"password" argument, which will be hashed internally.
337
Also "state" must not given explicitly; it is implicitly set to
339
Raises an exception if the user already exists, or the dict contains
340
invalid keys or is missing required keys.
342
if 'passhash' in kwargs:
343
raise DBException("Supplied arguments include passhash (invalid) (1).")
344
# Make a copy of the dict. Change password to passhash (hashing it),
345
# and set 'state' to "no_agreement".
348
fields = copy.copy(kwargs)
350
# Use the user object
351
fields = dict(user_obj)
352
if 'password' in fields:
353
fields['passhash'] = _passhash(fields['password'])
354
del fields['password']
356
# Convert role to rolenm
357
fields['rolenm'] = str(user_obj.role)
360
fields['state'] = "no_agreement"
361
# else, we'll trust the user, but it SHOULD be "no_agreement"
362
# (We can't change it because then the user object would not
364
if 'local_password' in fields:
365
del fields['local_password']
367
return self.insert(fields, "login", self.login_fields, dry=dry)
369
def update_user(self, login, dry=False, **kwargs):
370
"""Updates fields of a particular user. login is the name of the user
371
to update. The dict contains the fields which will be modified, and
372
their new values. If any value is omitted from the dict, it does not
373
get modified. login and studentid may not be modified.
374
Passhash may be modified by supplying a "password" field, in
375
cleartext, not a hashed password.
377
Note that no checking is done. It is expected this function is called
378
by a trusted source. In particular, it allows the password to be
379
changed without knowing the old password. The caller should check
380
that the user knows the existing password before calling this function
383
if 'passhash' in kwargs:
384
raise DBException("Supplied arguments include passhash (invalid) (2).")
385
if "password" in kwargs:
386
kwargs = copy.copy(kwargs)
387
kwargs['passhash'] = _passhash(kwargs['password'])
388
del kwargs['password']
389
return self.update({"login": login}, kwargs, "login",
390
self.login_fields, self.login_primary, ["login", "studentid"],
393
def get_user(self, login, dry=False):
394
"""Given a login, returns a User object containing details looked up
397
Raises a DBException if the login is not found in the DB.
399
userdict = self.get_single({"login": login}, "login",
400
self.login_fields, self.login_primary,
401
error_notfound="get_user: No user with that login name", dry=dry)
403
return userdict # Query string
404
# Package into a User object
405
return user.User(**userdict)
407
def get_users(self, dry=False):
408
"""Returns a list of all users in the DB, as User objects.
410
userdicts = self.get_all("login", self.login_fields, dry=dry)
412
return userdicts # Query string
413
# Package into User objects
414
return [user.User(**userdict) for userdict in userdicts]
416
def get_user_loginid(self, login, dry=False):
417
"""Given a login, returns the integer loginid for this user.
419
Raises a DBException if the login is not found in the DB.
421
userdict = self.get_single({"login": login}, "login",
422
['loginid'], self.login_primary,
423
error_notfound="get_user_loginid: No user with that login name",
426
return userdict # Query string
427
return userdict['loginid']
429
def user_authenticate(self, login, password, dry=False):
430
"""Performs a password authentication on a user. Returns True if
431
"passhash" is the correct passhash for the given login, False
432
if the passhash does not match the password in the DB,
433
and None if the passhash in the DB is NULL.
434
Also returns False if the login does not exist (so if you want to
435
differentiate these cases, use get_user and catch an exception).
437
query = ("SELECT passhash FROM login WHERE login = %s;"
440
result = self.db.query(query)
441
if result.ntuples() == 1:
442
# Valid username. Check password.
443
passhash = result.getresult()[0][0]
446
return _passhash(password) == passhash
450
# PROBLEM AND PROBLEM ATTEMPT FUNCTIONS #
452
def get_problem_problemid(self, exercisename, dry=False):
453
"""Given an exercise name, returns the associated problemID.
454
If the exercise name is NOT in the database, it inserts it and returns
455
the new problemID. Hence this may mutate the DB, but is idempotent.
458
d = self.get_single({"identifier": exercisename}, "problem",
459
['problemid'], frozenset(["identifier"]),
462
return d # Query string
465
# Shouldn't try again, must have failed for some other reason
467
# if we failed to get a problemid, it was probably because
468
# the exercise wasn't in the db. So lets insert it!
470
# The insert can fail if someone else simultaneously does
471
# the insert, so if the insert fails, we ignore the problem.
473
self.insert({'identifier': exercisename}, "problem",
474
frozenset(['identifier']))
478
# Assuming the insert succeeded, we should be able to get the
480
d = self.get_single({"identifier": exercisename}, "problem",
481
['problemid'], frozenset(["identifier"]))
483
return d['problemid']
485
def insert_problem_attempt(self, login, exercisename, date, complete,
487
"""Inserts the details of a problem attempt into the database.
488
exercisename: Name of the exercise. (identifier field of problem
489
table). If this exercise does not exist, also creates a new row in
490
the problem table for this exercise name.
491
login: Name of the user submitting the attempt. (login field of the
493
date: struct_time, the date this attempt was made.
494
complete: bool. Whether the test passed or not.
495
attempt: Text of the attempt.
497
Note: Even if dry, will still physically call get_problem_problemid,
498
which may mutate the DB, and get_user_loginid, which may fail.
500
problemid = self.get_problem_problemid(exercisename)
501
loginid = self.get_user_loginid(login) # May raise a DBException
504
'problemid': problemid,
507
'complete': complete,
509
}, 'problem_attempt',
510
frozenset(['problemid','loginid','date','complete','attempt']),
513
def write_problem_save(self, login, exercisename, date, text, dry=False):
514
"""Writes text to the problem_save table (for when the user saves an
515
exercise). Creates a new row, or overwrites an existing one if the
516
user has already saved that problem.
517
(Unlike problem_attempt, does not keep historical records).
519
problemid = self.get_problem_problemid(exercisename)
520
loginid = self.get_user_loginid(login) # May raise a DBException
524
'problemid': problemid,
529
frozenset(['problemid','loginid','date','text']),
531
except pg.ProgrammingError:
532
# May have failed because this problemid/loginid row already
533
# exists (they have a unique key constraint).
534
# Do an update instead.
536
# Shouldn't try again, must have failed for some other reason
539
'problemid': problemid,
546
frozenset(['date', 'text']),
547
frozenset(['problemid', 'loginid']))
549
def get_problem_stored_text(self, login, exercisename, dry=False):
550
"""Given a login name and exercise name, returns the text of the
551
last saved/submitted attempt for this question.
552
Returns None if the user has not saved or made an attempt on this
554
(If the user has both saved and submitted, it returns whichever was
557
Note: Even if dry, will still physically call get_problem_problemid,
558
which may mutate the DB, and get_user_loginid, which may fail.
560
problemid = self.get_problem_problemid(exercisename)
561
loginid = self.get_user_loginid(login) # May raise a DBException
562
# This very complex query finds all submissions made by this user for
563
# this problem, as well as the save made by this user for this
564
# problem, and returns the text of the newest one.
565
# (Whichever is newer out of the save or the submit).
566
query = """SELECT text FROM
568
(SELECT * FROM problem_save WHERE loginid = %d AND problemid = %d)
570
(SELECT problemid, loginid, date, text FROM problem_attempt
571
AS problem_attempt (problemid, loginid, date, text)
572
WHERE loginid = %d AND problemid = %d)
576
LIMIT 1;""" % (loginid, problemid, loginid, problemid)
578
result = self.db.query(query)
579
if result.ntuples() == 1:
580
# The user has made at least 1 attempt. Return the newest.
581
return result.getresult()[0][0]
585
def get_problem_status(self, login, exercisename, dry=False):
586
"""Given a login name and exercise name, returns information about the
587
user's performance on that problem.
589
- A boolean, whether they have successfully passed this exercise.
590
- An int, the number of attempts they have made up to and
591
including the first successful attempt (or the total number of
592
attempts, if not yet successful).
593
Note: exercisename may be an int, in which case it will be directly
594
used as the problemid.
596
if isinstance(exercisename, int):
597
problemid = exercisename
599
problemid = self.get_problem_problemid(exercisename)
600
loginid = self.get_user_loginid(login) # May raise a DBException
602
# ASSUME that it is completed, get the total number of attempts up to
603
# and including the first successful attempt.
604
# (Get the date of the first successful attempt. Then count the number
605
# of attempts made <= that date).
606
# Will return an empty table if the problem has never been
607
# successfully completed.
608
query = """SELECT COUNT(*) FROM problem_attempt
609
WHERE loginid = %d AND problemid = %d AND date <=
610
(SELECT date FROM problem_attempt
611
WHERE loginid = %d AND problemid = %d AND complete = TRUE
613
LIMIT 1);""" % (loginid, problemid, loginid, problemid)
615
result = self.db.query(query)
616
count = int(result.getresult()[0][0])
618
# The user has made at least 1 successful attempt.
619
# Return True for success, and the number of attempts up to and
620
# including the successful one.
623
# Returned 0 rows - this indicates that the problem has not been
625
# Return the total number of attempts, and False for success.
626
query = """SELECT COUNT(*) FROM problem_attempt
627
WHERE loginid = %d AND problemid = %d;""" % (loginid, problemid)
628
result = self.db.query(query)
629
count = int(result.getresult()[0][0])
630
return (False, count)
632
# WORKSHEET/PROBLEM ASSOCIATION AND MARKS CALCULATION
634
def get_worksheet_mtime(self, subject, worksheet, dry=False):
636
For a given subject/worksheet name, gets the time the worksheet was
637
last updated in the DB, if any.
638
This can be used to check if there is a newer version on disk.
639
Returns the timestamp as a time.struct_time, or None if the worksheet
640
is not found or has no stored mtime.
644
{"subject": subject, "identifier": worksheet},
645
"worksheet", ["mtime"], ["subject", "identifier"],
648
# Assume the worksheet is not in the DB
652
if r["mtime"] is None:
654
return time.strptime(r["mtime"], TIMESTAMP_FORMAT)
656
def create_worksheet(self, subject, worksheet, problems=None,
659
Inserts or updates rows in the worksheet and worksheet_problems
660
tables, to create a worksheet in the database.
661
This atomically performs all operations. If the worksheet is already
662
in the DB, removes it and all its associated problems and rebuilds.
663
Sets the timestamp to the current time.
665
problems is a collection of pairs. The first element of the pair is
666
the problem identifier ("identifier" column of the problem table). The
667
second element is an optional boolean, "optional". This can be omitted
668
(so it's a 1-tuple), and then it will default to False.
670
Problems and assessable are optional, and if omitted, will not change
671
the existing data. If the worksheet does not yet exist, and assessable
672
is omitted, it defaults to False.
674
Note: As with get_problem_problemid, if a problem name is not in the
675
DB, it will be added to the problem table.
677
self.start_transaction()
679
# Use the current time as the "mtime" field
680
mtime = time.localtime()
682
# Get the worksheetid
684
{"subject": subject, "identifier": worksheet},
685
"worksheet", ["worksheetid"], ["subject", "identifier"])
686
worksheetid = r["worksheetid"]
688
# Delete any problems which might exist, if problems is
689
# supplied. If it isn't, keep the existing ones.
690
if problems is not None:
691
query = ("DELETE FROM worksheet_problem "
692
"WHERE worksheetid = %d;" % worksheetid)
694
# Update the row with the new details
695
if assessable is None:
696
query = ("UPDATE worksheet "
697
"SET mtime = %s WHERE worksheetid = %d;"
698
% (_escape(mtime), worksheetid))
700
query = ("UPDATE worksheet "
701
"SET assessable = %s, mtime = %s "
702
"WHERE worksheetid = %d;"
703
% (_escape(assessable), _escape(mtime), worksheetid))
706
# Assume the worksheet is not in the DB
707
# If assessable is not supplied, default to False.
708
if assessable is None:
710
# Create the worksheet row
711
query = ("INSERT INTO worksheet "
712
"(subject, identifier, assessable, mtime) "
713
"VALUES (%s, %s, %s, %s);"""
714
% (_escape(subject), _escape(worksheet),
715
_escape(assessable), _escape(mtime)))
717
# Now get the worksheetid again - should succeed
719
{"subject": subject, "identifier": worksheet},
720
"worksheet", ["worksheetid"], ["subject", "identifier"])
721
worksheetid = r["worksheetid"]
723
# Now insert each problem into the worksheet_problem table
724
if problems is not None:
725
for problem in problems:
726
if isinstance(problem, tuple):
727
prob_identifier = problem[0]
729
optional = problem[1]
733
prob_identifier = problem
735
problemid = self.get_problem_problemid(prob_identifier)
736
query = ("INSERT INTO worksheet_problem "
737
"(worksheetid, problemid, optional) "
738
"VALUES (%d, %d, %s);"
739
% (worksheetid, problemid, _escape(optional)))
747
def set_worksheet_assessable(self, subject, worksheet, assessable,
750
Sets the "assessable" field of a worksheet without updating the mtime.
752
IMPORTANT: This will NOT update the mtime. This is designed to allow
753
updates which did not come from the worksheet XML file. It would be
754
bad to update the mtime without consulting the XML file because then
755
it would appear the database is up to date, when it isn't.
757
Therefore, call this method if you are getting "assessable"
758
information from outside the worksheet XML file (eg. from the subject
761
Unlike create_worksheet, raises a DBException if the worksheet is not
764
return self.update({"subject": subject, "identifier": worksheet},
765
{"assessable": assessable}, "worksheet", ["assessable"],
766
["subject", "identifier"], dry=dry)
768
def worksheet_is_assessable(self, subject, worksheet, dry=False):
770
{"subject": subject, "identifier": worksheet},
771
"worksheet", ["assessable"], ["subject", "identifier"], dry=dry)
772
return _parse_boolean(r["assessable"])
774
def calculate_score_worksheet(self, login, subject, worksheet):
776
Calculates the score for a user on a given worksheet.
777
Returns a 4-tuple of ints, consisting of:
778
(No. mandatory exercises completed,
779
Total no. mandatory exercises,
780
No. optional exercises completed,
781
Total no. optional exercises)
783
self.start_transaction()
789
# Get a list of problems and optionality for all problems in the
791
query = ("""SELECT problemid, optional FROM worksheet_problem
792
WHERE worksheetid = (SELECT worksheetid FROM worksheet
793
WHERE subject = %s and identifier = %s);"""
794
% (_escape(subject), _escape(worksheet)))
795
result = self.db.query(query)
796
# Now get the student's pass/fail for each problem in this worksheet
797
for problemid, optional in result.getresult():
798
done, _ = self.get_problem_status(login, problemid)
799
# done is a bool, whether this student has completed that
801
if _parse_boolean(optional):
803
if done: opt_done += 1
806
if done: mand_done += 1
811
return mand_done, mand_total, opt_done, opt_total
813
def add_enrolment(self, login, subj_code, semester, year=None, dry=False):
815
Enrol a student in the given offering of a subject.
816
Returns True on success, False on failure (which usually means either
817
the student is already enrolled in the subject, the student was not
818
found, or no offering existed with the given details).
819
The return value can usually be ignored.
821
subj_code = str(subj_code)
822
semester = str(semester)
824
year = str(time.gmtime().tm_year)
828
INSERT INTO enrolment (loginid, offeringid)
830
(SELECT loginid FROM login WHERE login=%s),
832
FROM (offering INNER JOIN subject
833
ON subject.subjectid = offering.subject)
834
WHERE subj_code=%s AND semester=%s AND year=%s)
835
);""" % (_escape(login), _escape(subj_code), _escape(semester),
840
result = self.db.query(query)
841
except pg.ProgrammingError:
846
"""Close the DB connection. Do not call any other functions after
847
this. (The behaviour of doing so is undefined).
added
removed
lib/common/db.py
