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 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
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.
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
211
for k,v in dict.items():
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))
221
return self.db.query(query)
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
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
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,
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
245
for k,v in updatedict.items():
246
setlist.append("%s = %s" % (k, _escape(v)))
248
for k,v in primarydict.items():
249
wherelist.append("%s = %s" % (k, _escape(v)))
250
if len(setlist) == 0 or len(wherelist) == 0:
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))
261
def delete(self, primarydict, tablename, primary_keys, dry=False):
262
"""Deletes a row in the table, matching against primarydict to find
264
primarydict, tablename, primary_keys: See update.
266
if not DB.check_dict(primarydict, primary_keys, must=True):
267
raise DBException("Supplied dictionary contains invalid or missing fields (2).")
269
for k,v in primarydict.items():
270
wherelist.append("%s = %s" % (k, _escape(v)))
271
if len(wherelist) == 0:
273
wherestring = ' AND '.join(wherelist)
274
query = ("DELETE FROM %s WHERE %s;" % (tablename, wherestring))
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
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).
291
if not DB.check_dict(primarydict, primary_keys, must=True):
292
raise DBException("Supplied dictionary contains invalid or missing fields (3).")
294
for k,v in primarydict.items():
295
wherelist.append("%s = %s" % (k, _escape(v)))
296
if len(getfields) == 0 or len(wherelist) == 0:
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))
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]
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.
319
if len(getfields) == 0:
321
getstring = ', '.join(getfields)
322
query = ("SELECT %s FROM %s;" % (getstring, tablename))
324
return self.db.query(query).dictresult()
326
def start_transaction(self, dry=False):
327
"""Starts a DB transaction.
328
Will not commit any changes until self.commit() is called.
330
query = "START TRANSACTION;"
334
def commit(self, dry=False):
335
"""Commits (ends) a DB transaction.
336
Commits all changes since the call to start_transaction.
342
def rollback(self, dry=False):
343
"""Rolls back (ends) a DB transaction, undoing all changes since the
344
call to start_transaction.
350
# USER MANAGEMENT FUNCTIONS #
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"
357
login_fields = frozenset(login_fields_list)
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.
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.
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
373
Raises an exception if the user already exists, or the dict contains
374
invalid keys or is missing required keys.
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".
382
fields = copy.copy(kwargs)
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']
390
# Convert role to rolenm
391
fields['rolenm'] = str(user_obj.role)
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
398
if 'local_password' in fields:
399
del fields['local_password']
401
return self.insert(fields, "login", self.login_fields, dry=dry)
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.
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
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"],
427
def get_user(self, login, dry=False):
428
"""Given a login, returns a User object containing details looked up
431
Raises a DBException if the login is not found in the DB.
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)
437
return userdict # Query string
438
# Package into a User object
439
return user.User(**userdict)
441
def get_users(self, dry=False):
442
"""Returns a list of all users in the DB, as User objects.
444
userdicts = self.get_all("login", self.login_fields, dry=dry)
446
return userdicts # Query string
447
# Package into User objects
448
return [user.User(**userdict) for userdict in userdicts]
450
def get_user_loginid(self, login, dry=False):
451
"""Given a login, returns the integer loginid for this user.
453
Raises a DBException if the login is not found in the DB.
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",
460
return userdict # Query string
461
return userdict['loginid']
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).
471
query = ("SELECT passhash FROM login WHERE login = %s;"
474
result = self.db.query(query)
475
if result.ntuples() == 1:
476
# Valid username. Check password.
477
passhash = result.getresult()[0][0]
480
return _passhash(password) == passhash
484
# PROBLEM AND PROBLEM ATTEMPT FUNCTIONS #
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.
492
d = self.get_single({"identifier": exercisename}, "problem",
493
['problemid'], frozenset(["identifier"]),
496
return d # Query string
499
# Shouldn't try again, must have failed for some other reason
501
# if we failed to get a problemid, it was probably because
502
# the exercise wasn't in the db. So lets insert it!
504
# The insert can fail if someone else simultaneously does
505
# the insert, so if the insert fails, we ignore the problem.
507
self.insert({'identifier': exercisename}, "problem",
508
frozenset(['identifier']))
512
# Assuming the insert succeeded, we should be able to get the
514
d = self.get_single({"identifier": exercisename}, "problem",
515
['problemid'], frozenset(["identifier"]))
517
return d['problemid']
519
def insert_problem_attempt(self, login, exercisename, date, complete,
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
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.
531
Note: Even if dry, will still physically call get_problem_problemid,
532
which may mutate the DB, and get_user_loginid, which may fail.
534
problemid = self.get_problem_problemid(exercisename)
535
loginid = self.get_user_loginid(login) # May raise a DBException
538
'problemid': problemid,
541
'complete': complete,
543
}, 'problem_attempt',
544
frozenset(['problemid','loginid','date','complete','attempt']),
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).
553
problemid = self.get_problem_problemid(exercisename)
554
loginid = self.get_user_loginid(login) # May raise a DBException
558
'problemid': problemid,
563
frozenset(['problemid','loginid','date','text']),
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.
570
# Shouldn't try again, must have failed for some other reason
573
'problemid': problemid,
580
frozenset(['date', 'text']),
581
frozenset(['problemid', 'loginid']))
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
588
(If the user has both saved and submitted, it returns whichever was
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.
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
602
(SELECT * FROM problem_save WHERE loginid = %d AND problemid = %d)
604
(SELECT problemid, loginid, date, text FROM problem_attempt
605
AS problem_attempt (problemid, loginid, date, text)
606
WHERE loginid = %d AND problemid = %d)
610
LIMIT 1;""" % (loginid, problemid, loginid, problemid)
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]
619
def get_problem_status(self, login, exercisename, dry=False):
620
"""Given a login name and exercise name, returns information about the
621
user's performance on that problem.
623
- A boolean, whether they have successfully passed this exercise.
624
- An int, the number of attempts they have made up to and
625
including the first successful attempt (or the total number of
626
attempts, if not yet successful).
627
Note: exercisename may be an int, in which case it will be directly
628
used as the problemid.
630
if isinstance(exercisename, int):
631
problemid = exercisename
633
problemid = self.get_problem_problemid(exercisename)
634
loginid = self.get_user_loginid(login) # May raise a DBException
636
# ASSUME that it is completed, get the total number of attempts up to
637
# and including the first successful attempt.
638
# (Get the date of the first successful attempt. Then count the number
639
# of attempts made <= that date).
640
# Will return an empty table if the problem has never been
641
# successfully completed.
642
query = """SELECT COUNT(*) FROM problem_attempt
643
WHERE loginid = %d AND problemid = %d AND date <=
644
(SELECT date FROM problem_attempt
645
WHERE loginid = %d AND problemid = %d AND complete = TRUE
647
LIMIT 1);""" % (loginid, problemid, loginid, problemid)
649
result = self.db.query(query)
650
count = int(result.getresult()[0][0])
652
# The user has made at least 1 successful attempt.
653
# Return True for success, and the number of attempts up to and
654
# including the successful one.
657
# Returned 0 rows - this indicates that the problem has not been
659
# Return the total number of attempts, and False for success.
660
query = """SELECT COUNT(*) FROM problem_attempt
661
WHERE loginid = %d AND problemid = %d;""" % (loginid, problemid)
662
result = self.db.query(query)
663
count = int(result.getresult()[0][0])
664
return (False, count)
666
# WORKSHEET/PROBLEM ASSOCIATION AND MARKS CALCULATION
668
def get_worksheet_mtime(self, subject, worksheet, dry=False):
670
For a given subject/worksheet name, gets the time the worksheet was
671
last updated in the DB, if any.
672
This can be used to check if there is a newer version on disk.
673
Returns the timestamp as a time.struct_time, or None if the worksheet
674
is not found or has no stored mtime.
678
{"subject": subject, "identifier": worksheet},
679
"worksheet", ["mtime"], ["subject", "identifier"],
682
# Assume the worksheet is not in the DB
686
if r["mtime"] is None:
688
return time.strptime(r["mtime"], TIMESTAMP_FORMAT)
690
def create_worksheet(self, subject, worksheet, problems=None,
693
Inserts or updates rows in the worksheet and worksheet_problems
694
tables, to create a worksheet in the database.
695
This atomically performs all operations. If the worksheet is already
696
in the DB, removes it and all its associated problems and rebuilds.
697
Sets the timestamp to the current time.
699
problems is a collection of pairs. The first element of the pair is
700
the problem identifier ("identifier" column of the problem table). The
701
second element is an optional boolean, "optional". This can be omitted
702
(so it's a 1-tuple), and then it will default to False.
704
Problems and assessable are optional, and if omitted, will not change
705
the existing data. If the worksheet does not yet exist, and assessable
706
is omitted, it defaults to False.
708
Note: As with get_problem_problemid, if a problem name is not in the
709
DB, it will be added to the problem table.
711
self.start_transaction()
713
# Use the current time as the "mtime" field
714
mtime = time.localtime()
716
# Get the worksheetid
718
{"subject": subject, "identifier": worksheet},
719
"worksheet", ["worksheetid"], ["subject", "identifier"])
720
worksheetid = r["worksheetid"]
722
# Delete any problems which might exist, if problems is
723
# supplied. If it isn't, keep the existing ones.
724
if problems is not None:
725
query = ("DELETE FROM worksheet_problem "
726
"WHERE worksheetid = %d;" % worksheetid)
728
# Update the row with the new details
729
if assessable is None:
730
query = ("UPDATE worksheet "
731
"SET mtime = %s WHERE worksheetid = %d;"
732
% (_escape(mtime), worksheetid))
734
query = ("UPDATE worksheet "
735
"SET assessable = %s, mtime = %s "
736
"WHERE worksheetid = %d;"
737
% (_escape(assessable), _escape(mtime), worksheetid))
740
# Assume the worksheet is not in the DB
741
# If assessable is not supplied, default to False.
742
if assessable is None:
744
# Create the worksheet row
745
query = ("INSERT INTO worksheet "
746
"(subject, identifier, assessable, mtime) "
747
"VALUES (%s, %s, %s, %s);"""
748
% (_escape(subject), _escape(worksheet),
749
_escape(assessable), _escape(mtime)))
751
# Now get the worksheetid again - should succeed
753
{"subject": subject, "identifier": worksheet},
754
"worksheet", ["worksheetid"], ["subject", "identifier"])
755
worksheetid = r["worksheetid"]
757
# Now insert each problem into the worksheet_problem table
758
if problems is not None:
759
for problem in problems:
760
if isinstance(problem, tuple):
761
prob_identifier = problem[0]
763
optional = problem[1]
767
prob_identifier = problem
769
problemid = self.get_problem_problemid(prob_identifier)
770
query = ("INSERT INTO worksheet_problem "
771
"(worksheetid, problemid, optional) "
772
"VALUES (%d, %d, %s);"
773
% (worksheetid, problemid, _escape(optional)))
781
def set_worksheet_assessable(self, subject, worksheet, assessable,
784
Sets the "assessable" field of a worksheet without updating the mtime.
786
IMPORTANT: This will NOT update the mtime. This is designed to allow
787
updates which did not come from the worksheet XML file. It would be
788
bad to update the mtime without consulting the XML file because then
789
it would appear the database is up to date, when it isn't.
791
Therefore, call this method if you are getting "assessable"
792
information from outside the worksheet XML file (eg. from the subject
795
Unlike create_worksheet, raises a DBException if the worksheet is not
798
return self.update({"subject": subject, "identifier": worksheet},
799
{"assessable": assessable}, "worksheet", ["assessable"],
800
["subject", "identifier"], dry=dry)
802
def worksheet_is_assessable(self, subject, worksheet, dry=False):
804
{"subject": subject, "identifier": worksheet},
805
"worksheet", ["assessable"], ["subject", "identifier"], dry=dry)
806
return _parse_boolean(r["assessable"])
808
def calculate_score_worksheet(self, login, subject, worksheet):
810
Calculates the score for a user on a given worksheet.
811
Returns a 4-tuple of ints, consisting of:
812
(No. mandatory exercises completed,
813
Total no. mandatory exercises,
814
No. optional exercises completed,
815
Total no. optional exercises)
817
self.start_transaction()
823
# Get a list of problems and optionality for all problems in the
825
query = ("""SELECT problemid, optional FROM worksheet_problem
826
WHERE worksheetid = (SELECT worksheetid FROM worksheet
827
WHERE subject = %s and identifier = %s);"""
828
% (_escape(subject), _escape(worksheet)))
829
result = self.db.query(query)
830
# Now get the student's pass/fail for each problem in this worksheet
831
for problemid, optional in result.getresult():
832
done, _ = self.get_problem_status(login, problemid)
833
# done is a bool, whether this student has completed that
835
if _parse_boolean(optional):
837
if done: opt_done += 1
840
if done: mand_done += 1
845
return mand_done, mand_total, opt_done, opt_total
847
# ENROLMENT INFORMATION
849
def add_enrolment(self, login, subj_code, semester, year=None, dry=False):
851
Enrol a student in the given offering of a subject.
852
Returns True on success, False on failure (which usually means either
853
the student is already enrolled in the subject, the student was not
854
found, or no offering existed with the given details).
855
The return value can usually be ignored.
857
subj_code = str(subj_code)
858
semester = str(semester)
860
year = str(time.gmtime().tm_year)
864
INSERT INTO enrolment (loginid, offeringid)
866
(SELECT loginid FROM login WHERE login=%s),
868
FROM offering, subject, semester
869
WHERE subject.subjectid = offering.subject
870
AND semester.semesterid = offering.semesterid
871
AND subj_code=%s AND semester=%s AND year=%s)
872
);""" % (_escape(login), _escape(subj_code), _escape(semester),
877
result = self.db.query(query)
878
except pg.ProgrammingError:
882
# SUBJECTS AND ENROLEMENT
884
def get_subjects(self, dry=False):
886
Get all subjects in IVLE.
887
Returns a list of dicts (all values strings), with the keys:
888
subj_code, subj_name, subj_short_name, url
890
return self.get_all("subject",
891
("subjectid", "subj_code", "subj_name", "subj_short_name", "url"),
894
def get_offering_semesters(self, subjectid, dry=False):
896
Get the semester information for a subject as well as providing
897
information about if the subject is active and which semester it is in.
900
SELECT offeringid, subj_name, year, semester, active
901
FROM semester, offering, subject
902
WHERE offering.semesterid = semester.semesterid AND
903
offering.subject = subject.subjectid AND
904
offering.subject = %d;"""%subjectid
907
results = self.db.query(query).dictresult()
908
# Parse boolean varibles
909
for result in results:
910
result['active'] = _parse_boolean(result['active'])
913
def get_offering_members(self, offeringid, dry=False):
915
Gets the logins of all the people enroled in an offering
918
SELECT login.login AS login, login.fullname AS fullname
919
FROM login, enrolment
920
WHERE login.loginid = enrolment.loginid AND
921
enrolment.offeringid = %d;"""%offeringid
924
return self.db.query(query).dictresult()
927
def get_enrolment(self, login, dry=False):
929
Get all offerings (in IVLE) the student is enrolled in.
930
Returns a list of dicts (all values strings), with the keys:
931
offeringid, subj_code, subj_name, subj_short_name, year, semester, url
934
SELECT offering.offeringid, subj_code, subj_name, subj_short_name,
935
semester.year, semester.semester, subject.url
936
FROM login, enrolment, offering, subject, semester
937
WHERE enrolment.offeringid=offering.offeringid
938
AND login.loginid=enrolment.loginid
939
AND offering.subject=subject.subjectid
940
AND semester.semesterid=offering.semesterid
942
AND login=%s;""" % _escape(login)
945
return self.db.query(query).dictresult()
947
def get_enrolment_groups(self, login, offeringid, dry=False):
949
Get all groups the user is member of in the given offering.
950
Returns a list of dicts (all values strings), with the keys:
954
SELECT project_group.groupnm as name, project_group.nick as nick
955
FROM project_set, project_group, group_member, login
957
AND project_set.offeringid=%s
958
AND group_member.loginid=login.loginid
959
AND project_group.groupid=group_member.groupid
960
AND project_group.projectsetid=project_set.projectsetid
961
""" % (_escape(login), _escape(offeringid))
964
return self.db.query(query).dictresult()
966
def get_subjects_status(self, login, dry=False):
968
Get all subjects in IVLE, split into lists of enrolled and unenrolled
970
Returns a tuple of lists (enrolled, unenrolled) of dicts
971
(all values strings) with the keys:
972
subj_code, subj_name, subj_short_name, url
974
enrolments = self.get_enrolment(login)
975
all_subjects = self.get_subjects()
977
enrolled_set = set(x['subj_code'] for x in enrolments)
979
enrolled_subjects = [x for x in all_subjects
980
if x['subj_code'] in enrolled_set]
981
unenrolled_subjects = [x for x in all_subjects
982
if x['subj_code'] not in enrolled_set]
983
enrolled_subjects.sort(key=lambda x: x['subj_code'])
984
unenrolled_subjects.sort(key=lambda x: x['subj_code'])
985
return (enrolled_subjects, unenrolled_subjects)
989
def get_groups_by_user(self, login, offeringid=None, dry=False):
991
Get all project groups the student is in, corresponding to a
992
particular subject offering (or all offerings, if omitted).
993
Returns a list of tuples:
994
(int groupid, str groupnm, str group_nick, bool is_member).
995
(Note: If is_member is false, it means they have just been invited to
996
this group, not a member).
998
if offeringid is None:
1001
and_projectset_table = ", project_set"
1003
AND project_group.projectsetid = project_set.projectsetid
1004
AND project_set.offeringid = %s""" % _escape(offeringid)
1005
# Union both the groups this user is a member of, and the groups this
1006
# user is invited to.
1008
SELECT project_group.groupid, groupnm, project_group.nick, True
1009
FROM project_group, group_member, login %(and_projectset_table)s
1010
WHERE project_group.groupid = group_member.groupid
1011
AND group_member.loginid = login.loginid
1012
AND login = %(login)s
1015
SELECT project_group.groupid, groupnm, project_group.nick, False
1016
FROM project_group, group_invitation, login %(and_projectset_table)s
1017
WHERE project_group.groupid = group_invitation.groupid
1018
AND group_invitation.loginid = login.loginid
1019
AND login = %(login)s
1021
;""" % {"login": _escape(login), "and_offering": and_offering,
1022
"and_projectset_table": and_projectset_table}
1025
# Convert 't' -> True, 'f' -> False
1026
return [(groupid, groupnm, nick, ismember == 't')
1027
for groupid, groupnm, nick, ismember
1028
in self.db.query(query).getresult()]
1030
def get_offering_info(self, projectsetid, dry=False):
1031
"""Takes information from projectset and returns useful information
1032
about the subject and semester. Returns as a dictionary.
1035
SELECT subjectid, subj_code, subj_name, subj_short_name, url, year, semester,
1037
FROM subject, offering, semester, project_set
1038
WHERE offering.subject = subject.subjectid AND
1039
offering.semesterid = semester.semesterid AND
1040
project_set.offeringid = offering.offeringid AND
1041
project_set.projectsetid = %d;"""%projectsetid
1044
return self.db.query(query).dictresult()[0]
1046
def get_projectgroup_members(self, groupid, dry=False):
1047
"""Returns the logins of all students in a project group
1050
SELECT login.login as login, login.fullname as fullname
1051
FROM login, group_member
1052
WHERE login.loginid = group_member.loginid AND
1053
group_member.groupid = %d
1054
ORDER BY login.login;"""%groupid
1057
return self.db.query(query).dictresult()
1059
def get_projectsets_by_offering(self, offeringid, dry=False):
1060
"""Returns all the projectsets in a particular offering"""
1062
SELECT projectsetid, max_students_per_group
1064
WHERE project_set.offeringid = %d;"""%offeringid
1067
return self.db.query(query).dictresult()
1069
def get_groups_by_projectset(self, projectsetid, dry=False):
1070
"""Returns all the groups that are in a particular projectset"""
1072
SELECT groupid, groupnm, nick, createdby, epoch
1074
WHERE project_group.projectsetid = %d;"""%projectsetid
1077
return self.db.query(query).dictresult()
1080
"""Close the DB connection. Do not call any other functions after
1081
this. (The behaviour of doing so is undefined).
added
removed
lib/common/db.py
