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 ivle 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(host=ivle.conf.db_host, port=ivle.conf.db_port,
130
dbname=ivle.conf.db_dbname,
131
user=ivle.conf.db_user, passwd=ivle.conf.db_password)
138
# GENERIC DB FUNCTIONS #
141
def check_dict(dict, tablefields, disallowed=frozenset([]), must=False):
142
"""Checks that a dict does not contain keys that are not fields
143
of the specified table.
144
dict: A mapping from string keys to values; the keys are checked to
145
see that they correspond to login table fields.
146
tablefields: Collection of strings for field names in the table.
147
Only these fields will be allowed.
148
disallowed: Optional collection of strings for field names that are
150
must: If True, the dict MUST contain all fields in tablefields.
151
If False, it may contain any subset of the fields.
152
Returns True if the dict is valid, False otherwise.
154
allowed = frozenset(tablefields) - frozenset(disallowed)
155
dictkeys = frozenset(dict.keys())
157
return allowed == dictkeys
159
return allowed.issuperset(dictkeys)
161
def insert(self, dict, tablename, tablefields, disallowed=frozenset([]),
163
"""Inserts a new row in a table, using data from a supplied
164
dictionary (which will be checked by check_dict).
165
dict: Dictionary mapping column names to values. The values may be
166
any of the following types:
167
str, int, long, float, NoneType.
168
tablename: String, name of the table to insert into. Will NOT be
169
escaped - must be a valid identifier.
170
tablefields, disallowed: see check_dict.
171
dry: Returns the SQL query as a string, and does not execute it.
172
Raises a DBException if the dictionary contains invalid fields.
174
if not DB.check_dict(dict, tablefields, disallowed):
175
extras = set(dict.keys()) - tablefields
176
raise DBException("Supplied dictionary contains invalid fields. (%s)" % (repr(extras)))
177
# Build two lists concurrently: field names and values, as SQL strings
180
for k,v in dict.items():
182
values.append(_escape(v))
183
if len(fieldnames) == 0: return
184
fieldnames = ', '.join(fieldnames)
185
values = ', '.join(values)
186
query = ("INSERT INTO %s (%s) VALUES (%s);"
187
% (tablename, fieldnames, values))
191
def return_insert(self, dict, tablename, tablefields, returning,
192
disallowed=frozenset([]), dry=False):
193
"""Inserts a new row in a table, using data from a supplied
194
dictionary (which will be checked by check_dict) and returns certain
196
dict: Dictionary mapping column names to values. The values may be
197
any of the following types:
198
str, int, long, float, NoneType.
199
tablename: String, name of the table to insert into. Will NOT be
200
escaped - must be a valid identifier.
201
returning: List of fields to return, not escaped
202
tablefields, disallowed: see check_dict.
203
dry: Returns the SQL query as a string, and does not execute it.
204
Raises a DBException if the dictionary contains invalid fields.
206
if not DB.check_dict(dict, tablefields, disallowed):
207
extras = set(dict.keys()) - tablefields
208
raise DBException("Supplied dictionary contains invalid fields. (%s)" % (repr(extras)))
209
# Build two lists concurrently: field names and values, as SQL strings
212
for k,v in dict.items():
214
values.append(_escape(v))
215
if len(fieldnames) == 0: return
216
fieldnames = ', '.join(fieldnames)
217
values = ', '.join(values)
218
returns = ', '.join(returning)
219
query = ("INSERT INTO %s (%s) VALUES (%s) RETURNING (%s);"
220
% (tablename, fieldnames, values, returns))
222
return self.db.query(query)
225
def update(self, primarydict, updatedict, tablename, tablefields,
226
primary_keys, disallowed_update=frozenset([]), dry=False):
227
"""Updates a row in a table, matching against primarydict to find the
228
row, and using the data in updatedict (which will be checked by
230
primarydict: Dict mapping column names to values. The keys should be
231
the table's primary key. Only rows which match this dict's values
233
updatedict: Dict mapping column names to values. The columns will be
234
updated with the given values for the matched rows.
235
tablename, tablefields, disallowed_update: See insert.
236
primary_keys: Collection of strings which together form the primary
237
key for this table. primarydict must contain all of these as keys,
240
if (not (DB.check_dict(primarydict, primary_keys, must=True)
241
and DB.check_dict(updatedict, tablefields, disallowed_update))):
242
raise DBException("Supplied dictionary contains invalid or missing fields (1).")
243
# Make a list of SQL fragments of the form "field = 'new value'"
244
# These fragments are ALREADY-ESCAPED
246
for k,v in updatedict.items():
247
setlist.append("%s = %s" % (k, _escape(v)))
249
for k,v in primarydict.items():
250
wherelist.append("%s = %s" % (k, _escape(v)))
251
if len(setlist) == 0 or len(wherelist) == 0:
253
# Join the fragments into a comma-separated string
254
setstring = ', '.join(setlist)
255
wherestring = ' AND '.join(wherelist)
256
# Build the whole query as an UPDATE statement
257
query = ("UPDATE %s SET %s WHERE %s;"
258
% (tablename, setstring, wherestring))
262
def delete(self, primarydict, tablename, primary_keys, dry=False):
263
"""Deletes a row in the table, matching against primarydict to find
265
primarydict, tablename, primary_keys: See update.
267
if not DB.check_dict(primarydict, primary_keys, must=True):
268
raise DBException("Supplied dictionary contains invalid or missing fields (2).")
270
for k,v in primarydict.items():
271
wherelist.append("%s = %s" % (k, _escape(v)))
272
if len(wherelist) == 0:
274
wherestring = ' AND '.join(wherelist)
275
query = ("DELETE FROM %s WHERE %s;" % (tablename, wherestring))
279
def get_single(self, primarydict, tablename, getfields, primary_keys,
280
error_notfound="No rows found", dry=False):
281
"""Retrieves a single row from a table, returning it as a dictionary
282
mapping field names to values. Matches against primarydict to find the
284
primarydict, tablename, primary_keys: See update/delete.
285
getfields: Collection of strings; the field names which will be
286
returned as keys in the dictionary.
287
error_notfound: Error message if 0 rows match.
288
Raises a DBException if 0 rows match, with error_notfound as the msg.
289
Raises an AssertError if >1 rows match (this should not happen if
290
primary_keys is indeed the primary key).
292
if not DB.check_dict(primarydict, primary_keys, must=True):
293
raise DBException("Supplied dictionary contains invalid or missing fields (3).")
295
for k,v in primarydict.items():
296
wherelist.append("%s = %s" % (k, _escape(v)))
297
if len(getfields) == 0 or len(wherelist) == 0:
299
# Join the fragments into a comma-separated string
300
getstring = ', '.join(getfields)
301
wherestring = ' AND '.join(wherelist)
302
# Build the whole query as an SELECT statement
303
query = ("SELECT %s FROM %s WHERE %s;"
304
% (getstring, tablename, wherestring))
306
result = self.db.query(query)
307
# Expecting exactly one
308
if result.ntuples() != 1:
309
# It should not be possible for ntuples to be greater than 1
310
assert (result.ntuples() < 1)
311
raise DBException(error_notfound)
312
# Return as a dictionary
313
return result.dictresult()[0]
315
def get_all(self, tablename, getfields, dry=False):
316
"""Retrieves all rows from a table, returning it as a list of
317
dictionaries mapping field names to values.
318
tablename, getfields: See get_single.
320
if len(getfields) == 0:
322
getstring = ', '.join(getfields)
323
query = ("SELECT %s FROM %s;" % (getstring, tablename))
325
return self.db.query(query).dictresult()
327
def start_transaction(self, dry=False):
328
"""Starts a DB transaction.
329
Will not commit any changes until self.commit() is called.
331
query = "START TRANSACTION;"
335
def commit(self, dry=False):
336
"""Commits (ends) a DB transaction.
337
Commits all changes since the call to start_transaction.
343
def rollback(self, dry=False):
344
"""Rolls back (ends) a DB transaction, undoing all changes since the
345
call to start_transaction.
351
# USER MANAGEMENT FUNCTIONS #
353
login_primary = frozenset(["login"])
354
login_fields_list = [
355
"login", "passhash", "state", "unixid", "email", "nick", "fullname",
356
"rolenm", "studentid", "acct_exp", "pass_exp", "last_login", "svn_pass"
358
login_fields = frozenset(login_fields_list)
360
def create_user(self, user_obj=None, dry=False, **kwargs):
361
"""Creates a user login entry in the database.
362
Two ways to call this - passing a user object, or passing
363
all fields as separate arguments.
365
Either pass a "user_obj" as the first argument (in which case other
366
fields will be ignored), or pass all fields as arguments.
368
All user fields are to be passed as args. The argument names
369
are the field names of the "login" table of the DB schema.
370
However, instead of supplying a "passhash", you must supply a
371
"password" argument, which will be hashed internally.
372
Also "state" must not given explicitly; it is implicitly set to
374
Raises an exception if the user already exists, or the dict contains
375
invalid keys or is missing required keys.
377
if 'passhash' in kwargs:
378
raise DBException("Supplied arguments include passhash (invalid) (1).")
379
# Make a copy of the dict. Change password to passhash (hashing it),
380
# and set 'state' to "no_agreement".
383
fields = copy.copy(kwargs)
385
# Use the user object
386
fields = dict(user_obj)
387
if 'password' in fields:
388
fields['passhash'] = _passhash(fields['password'])
389
del fields['password']
391
# Convert role to rolenm
392
fields['rolenm'] = str(user_obj.role)
395
fields['state'] = "no_agreement"
396
# else, we'll trust the user, but it SHOULD be "no_agreement"
397
# (We can't change it because then the user object would not
399
if 'local_password' in fields:
400
del fields['local_password']
402
return self.insert(fields, "login", self.login_fields, dry=dry)
404
def get_user(self, login, dry=False):
405
"""Given a login, returns a User object containing details looked up
408
Raises a DBException if the login is not found in the DB.
410
userdict = self.get_single({"login": login}, "login",
411
self.login_fields, self.login_primary,
412
error_notfound="get_user: No user with that login name", dry=dry)
414
return userdict # Query string
415
# Package into a User object
416
return user.User(**userdict)
418
def get_users(self, dry=False):
419
"""Returns a list of all users in the DB, as User objects.
421
userdicts = self.get_all("login", self.login_fields, dry=dry)
423
return userdicts # Query string
424
# Package into User objects
425
return [user.User(**userdict) for userdict in userdicts]
427
def get_user_loginid(self, login, dry=False):
428
"""Given a login, returns the integer loginid for this user.
430
Raises a DBException if the login is not found in the DB.
432
userdict = self.get_single({"login": login}, "login",
433
['loginid'], self.login_primary,
434
error_notfound="get_user_loginid: No user with that login name",
437
return userdict # Query string
438
return userdict['loginid']
440
def user_authenticate(self, login, password, dry=False):
441
"""Performs a password authentication on a user. Returns True if
442
"passhash" is the correct passhash for the given login, False
443
if the passhash does not match the password in the DB,
444
and None if the passhash in the DB is NULL.
445
Also returns False if the login does not exist (so if you want to
446
differentiate these cases, use get_user and catch an exception).
448
query = ("SELECT passhash FROM login WHERE login = %s;"
451
result = self.db.query(query)
452
if result.ntuples() == 1:
453
# Valid username. Check password.
454
passhash = result.getresult()[0][0]
457
return _passhash(password) == passhash
461
# PROBLEM AND PROBLEM ATTEMPT FUNCTIONS #
463
def get_problem_problemid(self, exercisename, dry=False):
464
"""Given an exercise name, returns the associated problemID.
465
If the exercise name is NOT in the database, it inserts it and returns
466
the new problemID. Hence this may mutate the DB, but is idempotent.
469
d = self.get_single({"identifier": exercisename}, "problem",
470
['problemid'], frozenset(["identifier"]),
473
return d # Query string
476
# Shouldn't try again, must have failed for some other reason
478
# if we failed to get a problemid, it was probably because
479
# the exercise wasn't in the db. So lets insert it!
481
# The insert can fail if someone else simultaneously does
482
# the insert, so if the insert fails, we ignore the problem.
484
self.insert({'identifier': exercisename}, "problem",
485
frozenset(['identifier']))
489
# Assuming the insert succeeded, we should be able to get the
491
d = self.get_single({"identifier": exercisename}, "problem",
492
['problemid'], frozenset(["identifier"]))
494
return d['problemid']
496
def insert_problem_attempt(self, login, exercisename, date, complete,
498
"""Inserts the details of a problem attempt into the database.
499
exercisename: Name of the exercise. (identifier field of problem
500
table). If this exercise does not exist, also creates a new row in
501
the problem table for this exercise name.
502
login: Name of the user submitting the attempt. (login field of the
504
date: struct_time, the date this attempt was made.
505
complete: bool. Whether the test passed or not.
506
attempt: Text of the attempt.
508
Note: Even if dry, will still physically call get_problem_problemid,
509
which may mutate the DB, and get_user_loginid, which may fail.
511
problemid = self.get_problem_problemid(exercisename)
512
loginid = self.get_user_loginid(login) # May raise a DBException
515
'problemid': problemid,
518
'complete': complete,
520
}, 'problem_attempt',
521
frozenset(['problemid','loginid','date','complete','attempt']),
524
def write_problem_save(self, login, exercisename, date, text, dry=False):
525
"""Writes text to the problem_save table (for when the user saves an
526
exercise). Creates a new row, or overwrites an existing one if the
527
user has already saved that problem.
528
(Unlike problem_attempt, does not keep historical records).
530
problemid = self.get_problem_problemid(exercisename)
531
loginid = self.get_user_loginid(login) # May raise a DBException
535
'problemid': problemid,
540
frozenset(['problemid','loginid','date','text']),
542
except pg.ProgrammingError:
543
# May have failed because this problemid/loginid row already
544
# exists (they have a unique key constraint).
545
# Do an update instead.
547
# Shouldn't try again, must have failed for some other reason
550
'problemid': problemid,
557
frozenset(['date', 'text']),
558
frozenset(['problemid', 'loginid']))
560
def get_problem_stored_text(self, login, exercisename, dry=False):
561
"""Given a login name and exercise name, returns the text of the
562
last saved/submitted attempt for this question.
563
Returns None if the user has not saved or made an attempt on this
565
(If the user has both saved and submitted, it returns whichever was
568
Note: Even if dry, will still physically call get_problem_problemid,
569
which may mutate the DB, and get_user_loginid, which may fail.
571
problemid = self.get_problem_problemid(exercisename)
572
loginid = self.get_user_loginid(login) # May raise a DBException
573
# This very complex query finds all submissions made by this user for
574
# this problem, as well as the save made by this user for this
575
# problem, and returns the text of the newest one.
576
# (Whichever is newer out of the save or the submit).
577
query = """SELECT text FROM
579
(SELECT * FROM problem_save WHERE loginid = %d AND problemid = %d)
581
(SELECT problemid, loginid, date, text FROM problem_attempt
582
AS problem_attempt (problemid, loginid, date, text)
583
WHERE loginid = %d AND problemid = %d AND active)
587
LIMIT 1;""" % (loginid, problemid, loginid, problemid)
589
result = self.db.query(query)
590
if result.ntuples() == 1:
591
# The user has made at least 1 attempt. Return the newest.
592
return result.getresult()[0][0]
596
def get_problem_attempts(self, login, exercisename, allow_inactive=True,
598
"""Given a login name and exercise name, returns a list of dicts, one
599
for each attempt made for that exercise.
600
Dicts are {'date': 'formatted_time', 'complete': bool}.
601
Ordered with the newest first.
603
Note: By default, returns de-activated problem attempts (unlike
604
get_problem_stored_text).
605
If allow_inactive is False, will not return disabled attempts.
607
Note: Even if dry, will still physically call get_problem_problemid,
608
which may mutate the DB, and get_user_loginid, which may fail.
610
problemid = self.get_problem_problemid(exercisename)
611
loginid = self.get_user_loginid(login) # May raise a DBException
612
andactive = '' if allow_inactive else ' AND active'
613
query = """SELECT date, complete FROM problem_attempt
614
WHERE loginid = %d AND problemid = %d%s
615
ORDER BY date DESC;""" % (loginid, problemid, andactive)
617
result = self.db.query(query).getresult()
618
# Make into dicts (could use dictresult, but want to convert values)
619
return [{'date': date, 'complete': _parse_boolean(complete)}
620
for date, complete in result]
622
def get_problem_attempt(self, login, exercisename, as_of,
623
allow_inactive=True, dry=False):
624
"""Given a login name, exercise name, and struct_time, returns the
625
text of the submitted attempt for this question as of that date.
626
Returns None if the user had not made an attempt on this problem at
629
Note: By default, returns de-activated problem attempts (unlike
630
get_problem_stored_text).
631
If allow_inactive is False, will not return disabled attempts.
633
Note: Even if dry, will still physically call get_problem_problemid,
634
which may mutate the DB, and get_user_loginid, which may fail.
636
problemid = self.get_problem_problemid(exercisename)
637
loginid = self.get_user_loginid(login) # May raise a DBException
638
# Very similar to query in get_problem_stored_text, but without
639
# looking in problem_save, and restricting to a certain date.
640
andactive = '' if allow_inactive else ' AND active'
641
query = """SELECT attempt FROM problem_attempt
642
WHERE loginid = %d AND problemid = %d%s AND date <= %s
644
LIMIT 1;""" % (loginid, problemid, andactive, _escape(as_of))
646
result = self.db.query(query)
647
if result.ntuples() == 1:
648
# The user has made at least 1 attempt. Return the newest.
649
return result.getresult()[0][0]
653
def get_problem_status(self, login, exercisename, dry=False):
654
"""Given a login name and exercise name, returns information about the
655
user's performance on that problem.
657
- A boolean, whether they have successfully passed this exercise.
658
- An int, the number of attempts they have made up to and
659
including the first successful attempt (or the total number of
660
attempts, if not yet successful).
661
Note: exercisename may be an int, in which case it will be directly
662
used as the problemid.
664
if isinstance(exercisename, int):
665
problemid = exercisename
667
problemid = self.get_problem_problemid(exercisename)
668
loginid = self.get_user_loginid(login) # May raise a DBException
670
# ASSUME that it is completed, get the total number of attempts up to
671
# and including the first successful attempt.
672
# (Get the date of the first successful attempt. Then count the number
673
# of attempts made <= that date).
674
# Will return an empty table if the problem has never been
675
# successfully completed.
676
query = """SELECT COUNT(*) FROM problem_attempt
677
WHERE loginid = %d AND problemid = %d AND active AND date <=
678
(SELECT date FROM problem_attempt
679
WHERE loginid = %d AND problemid = %d AND complete AND active
681
LIMIT 1);""" % (loginid, problemid, loginid, problemid)
683
result = self.db.query(query)
684
count = int(result.getresult()[0][0])
686
# The user has made at least 1 successful attempt.
687
# Return True for success, and the number of attempts up to and
688
# including the successful one.
691
# Returned 0 rows - this indicates that the problem has not been
693
# Return the total number of attempts, and False for success.
694
query = """SELECT COUNT(*) FROM problem_attempt
695
WHERE loginid = %d AND problemid = %d AND active;""" % (loginid, problemid)
696
result = self.db.query(query)
697
count = int(result.getresult()[0][0])
698
return (False, count)
700
# WORKSHEET/PROBLEM ASSOCIATION AND MARKS CALCULATION
702
def get_worksheet_mtime(self, subject, worksheet, dry=False):
704
For a given subject/worksheet name, gets the time the worksheet was
705
last updated in the DB, if any.
706
This can be used to check if there is a newer version on disk.
707
Returns the timestamp as a time.struct_time, or None if the worksheet
708
is not found or has no stored mtime.
712
{"subject": subject, "identifier": worksheet},
713
"worksheet", ["mtime"], ["subject", "identifier"],
716
# Assume the worksheet is not in the DB
720
if r["mtime"] is None:
722
return time.strptime(r["mtime"], TIMESTAMP_FORMAT)
724
def create_worksheet(self, subject, worksheet, problems=None,
727
Inserts or updates rows in the worksheet and worksheet_problems
728
tables, to create a worksheet in the database.
729
This atomically performs all operations. If the worksheet is already
730
in the DB, removes it and all its associated problems and rebuilds.
731
Sets the timestamp to the current time.
733
problems is a collection of pairs. The first element of the pair is
734
the problem identifier ("identifier" column of the problem table). The
735
second element is an optional boolean, "optional". This can be omitted
736
(so it's a 1-tuple), and then it will default to False.
738
Problems and assessable are optional, and if omitted, will not change
739
the existing data. If the worksheet does not yet exist, and assessable
740
is omitted, it defaults to False.
742
Note: As with get_problem_problemid, if a problem name is not in the
743
DB, it will be added to the problem table.
745
self.start_transaction()
747
# Use the current time as the "mtime" field
748
mtime = time.localtime()
750
# Get the worksheetid
752
{"subject": subject, "identifier": worksheet},
753
"worksheet", ["worksheetid"], ["subject", "identifier"])
754
worksheetid = r["worksheetid"]
756
# Delete any problems which might exist, if problems is
757
# supplied. If it isn't, keep the existing ones.
758
if problems is not None:
759
query = ("DELETE FROM worksheet_problem "
760
"WHERE worksheetid = %d;" % worksheetid)
762
# Update the row with the new details
763
if assessable is None:
764
query = ("UPDATE worksheet "
765
"SET mtime = %s WHERE worksheetid = %d;"
766
% (_escape(mtime), worksheetid))
768
query = ("UPDATE worksheet "
769
"SET assessable = %s, mtime = %s "
770
"WHERE worksheetid = %d;"
771
% (_escape(assessable), _escape(mtime), worksheetid))
774
# Assume the worksheet is not in the DB
775
# If assessable is not supplied, default to False.
776
if assessable is None:
778
# Create the worksheet row
779
query = ("INSERT INTO worksheet "
780
"(subject, identifier, assessable, mtime) "
781
"VALUES (%s, %s, %s, %s);"""
782
% (_escape(subject), _escape(worksheet),
783
_escape(assessable), _escape(mtime)))
785
# Now get the worksheetid again - should succeed
787
{"subject": subject, "identifier": worksheet},
788
"worksheet", ["worksheetid"], ["subject", "identifier"])
789
worksheetid = r["worksheetid"]
791
# Now insert each problem into the worksheet_problem table
792
if problems is not None:
793
for problem in problems:
794
if isinstance(problem, tuple):
795
prob_identifier = problem[0]
797
optional = problem[1]
801
prob_identifier = problem
803
problemid = self.get_problem_problemid(prob_identifier)
804
query = ("INSERT INTO worksheet_problem "
805
"(worksheetid, problemid, optional) "
806
"VALUES (%d, %d, %s);"
807
% (worksheetid, problemid, _escape(optional)))
815
def set_worksheet_assessable(self, subject, worksheet, assessable,
818
Sets the "assessable" field of a worksheet without updating the mtime.
820
IMPORTANT: This will NOT update the mtime. This is designed to allow
821
updates which did not come from the worksheet XML file. It would be
822
bad to update the mtime without consulting the XML file because then
823
it would appear the database is up to date, when it isn't.
825
Therefore, call this method if you are getting "assessable"
826
information from outside the worksheet XML file (eg. from the subject
829
Unlike create_worksheet, raises a DBException if the worksheet is not
832
return self.update({"subject": subject, "identifier": worksheet},
833
{"assessable": assessable}, "worksheet", ["assessable"],
834
["subject", "identifier"], dry=dry)
836
def worksheet_is_assessable(self, subject, worksheet, dry=False):
838
{"subject": subject, "identifier": worksheet},
839
"worksheet", ["assessable"], ["subject", "identifier"], dry=dry)
840
return _parse_boolean(r["assessable"])
842
def calculate_score_worksheet(self, login, subject, worksheet):
844
Calculates the score for a user on a given worksheet.
845
Returns a 4-tuple of ints, consisting of:
846
(No. mandatory exercises completed,
847
Total no. mandatory exercises,
848
No. optional exercises completed,
849
Total no. optional exercises)
851
self.start_transaction()
857
# Get a list of problems and optionality for all problems in the
859
query = ("""SELECT problemid, optional FROM worksheet_problem
860
WHERE worksheetid = (SELECT worksheetid FROM worksheet
861
WHERE subject = %s and identifier = %s);"""
862
% (_escape(subject), _escape(worksheet)))
863
result = self.db.query(query)
864
# Now get the student's pass/fail for each problem in this worksheet
865
for problemid, optional in result.getresult():
866
done, _ = self.get_problem_status(login, problemid)
867
# done is a bool, whether this student has completed that
869
if _parse_boolean(optional):
871
if done: opt_done += 1
874
if done: mand_done += 1
879
return mand_done, mand_total, opt_done, opt_total
881
# ENROLMENT INFORMATION
883
def add_enrolment(self, login, subj_code, semester, year=None, dry=False):
885
Enrol a student in the given offering of a subject.
886
Returns True on success, False on failure (which usually means either
887
the student is already enrolled in the subject, the student was not
888
found, or no offering existed with the given details).
889
The return value can usually be ignored.
891
subj_code = str(subj_code)
892
semester = str(semester)
894
year = str(time.gmtime().tm_year)
898
INSERT INTO enrolment (loginid, offeringid)
900
(SELECT loginid FROM login WHERE login=%s),
902
FROM offering, subject, semester
903
WHERE subject.subjectid = offering.subject
904
AND semester.semesterid = offering.semesterid
905
AND subj_code=%s AND semester=%s AND year=%s)
906
);""" % (_escape(login), _escape(subj_code), _escape(semester),
911
result = self.db.query(query)
912
except pg.ProgrammingError:
916
# SUBJECTS AND ENROLEMENT
918
def get_subjects(self, dry=False):
920
Get all subjects in IVLE.
921
Returns a list of dicts (all values strings), with the keys:
922
subj_code, subj_name, subj_short_name, url
924
return self.get_all("subject",
925
("subjectid", "subj_code", "subj_name", "subj_short_name", "url"),
928
def get_offering_semesters(self, subjectid, dry=False):
930
Get the semester information for a subject as well as providing
931
information about if the subject is active and which semester it is in.
934
SELECT offeringid, subj_name, year, semester, active
935
FROM semester, offering, subject
936
WHERE offering.semesterid = semester.semesterid AND
937
offering.subject = subject.subjectid AND
938
offering.subject = %d;"""%subjectid
941
results = self.db.query(query).dictresult()
942
# Parse boolean varibles
943
for result in results:
944
result['active'] = _parse_boolean(result['active'])
947
def get_offering_members(self, offeringid, dry=False):
949
Gets the logins of all the people enroled in an offering
952
SELECT login.login AS login, login.fullname AS fullname
953
FROM login, enrolment
954
WHERE login.loginid = enrolment.loginid AND
955
enrolment.offeringid = %d
956
ORDER BY login.login;"""%offeringid
959
return self.db.query(query).dictresult()
962
def get_enrolment(self, login, dry=False):
964
Get all offerings (in IVLE) the student is enrolled in.
965
Returns a list of dicts (all values strings), with the keys:
966
offeringid, subj_code, subj_name, subj_short_name, year, semester, url
969
SELECT offering.offeringid, subj_code, subj_name, subj_short_name,
970
semester.year, semester.semester, subject.url
971
FROM login, enrolment, offering, subject, semester
972
WHERE enrolment.offeringid=offering.offeringid
973
AND login.loginid=enrolment.loginid
974
AND offering.subject=subject.subjectid
975
AND semester.semesterid=offering.semesterid
977
AND login=%s;""" % _escape(login)
980
return self.db.query(query).dictresult()
982
def get_enrolment_groups(self, login, offeringid, dry=False):
984
Get all groups the user is member of in the given offering.
985
Returns a list of dicts (all values strings), with the keys:
989
SELECT project_group.groupnm as name, project_group.nick as nick
990
FROM project_set, project_group, group_member, login
992
AND project_set.offeringid=%s
993
AND group_member.loginid=login.loginid
994
AND project_group.groupid=group_member.groupid
995
AND project_group.projectsetid=project_set.projectsetid
996
""" % (_escape(login), _escape(offeringid))
999
return self.db.query(query).dictresult()
1001
def get_subjects_status(self, login, dry=False):
1003
Get all subjects in IVLE, split into lists of enrolled and unenrolled
1005
Returns a tuple of lists (enrolled, unenrolled) of dicts
1006
(all values strings) with the keys:
1007
subj_code, subj_name, subj_short_name, url
1009
enrolments = self.get_enrolment(login)
1010
all_subjects = self.get_subjects()
1012
enrolled_set = set(x['subj_code'] for x in enrolments)
1014
enrolled_subjects = [x for x in all_subjects
1015
if x['subj_code'] in enrolled_set]
1016
unenrolled_subjects = [x for x in all_subjects
1017
if x['subj_code'] not in enrolled_set]
1018
enrolled_subjects.sort(key=lambda x: x['subj_code'])
1019
unenrolled_subjects.sort(key=lambda x: x['subj_code'])
1020
return (enrolled_subjects, unenrolled_subjects)
1024
def get_groups_by_user(self, login, offeringid=None, dry=False):
1026
Get all project groups the student is in, corresponding to a
1027
particular subject offering (or all offerings, if omitted).
1028
Returns a list of tuples:
1029
(int groupid, str groupnm, str group_nick, bool is_member).
1030
(Note: If is_member is false, it means they have just been invited to
1031
this group, not a member).
1033
if offeringid is None:
1036
and_projectset_table = ", project_set"
1038
AND project_group.projectsetid = project_set.projectsetid
1039
AND project_set.offeringid = %s""" % _escape(offeringid)
1040
# Union both the groups this user is a member of, and the groups this
1041
# user is invited to.
1043
SELECT project_group.groupid, groupnm, project_group.nick, True
1044
FROM project_group, group_member, login %(and_projectset_table)s
1045
WHERE project_group.groupid = group_member.groupid
1046
AND group_member.loginid = login.loginid
1047
AND login = %(login)s
1050
SELECT project_group.groupid, groupnm, project_group.nick, False
1051
FROM project_group, group_invitation, login %(and_projectset_table)s
1052
WHERE project_group.groupid = group_invitation.groupid
1053
AND group_invitation.loginid = login.loginid
1054
AND login = %(login)s
1056
;""" % {"login": _escape(login), "and_offering": and_offering,
1057
"and_projectset_table": and_projectset_table}
1060
# Convert 't' -> True, 'f' -> False
1061
return [(groupid, groupnm, nick, ismember == 't')
1062
for groupid, groupnm, nick, ismember
1063
in self.db.query(query).getresult()]
1065
def get_offering_info(self, projectsetid, dry=False):
1066
"""Takes information from projectset and returns useful information
1067
about the subject and semester. Returns as a dictionary.
1070
SELECT subjectid, subj_code, subj_name, subj_short_name, url, year, semester,
1072
FROM subject, offering, semester, project_set
1073
WHERE offering.subject = subject.subjectid AND
1074
offering.semesterid = semester.semesterid AND
1075
project_set.offeringid = offering.offeringid AND
1076
project_set.projectsetid = %d;"""%projectsetid
1079
return self.db.query(query).dictresult()[0]
1081
def get_projectgroup_members(self, groupid, dry=False):
1082
"""Returns the logins of all students in a project group
1085
SELECT login.login as login, login.fullname as fullname
1086
FROM login, group_member
1087
WHERE login.loginid = group_member.loginid AND
1088
group_member.groupid = %d
1089
ORDER BY login.login;"""%groupid
1092
return self.db.query(query).dictresult()
1094
def get_projectsets_by_offering(self, offeringid, dry=False):
1095
"""Returns all the projectsets in a particular offering"""
1097
SELECT projectsetid, max_students_per_group
1099
WHERE project_set.offeringid = %d;"""%offeringid
1102
return self.db.query(query).dictresult()
1104
def get_groups_by_projectset(self, projectsetid, dry=False):
1105
"""Returns all the groups that are in a particular projectset"""
1107
SELECT groupid, groupnm, nick, createdby, epoch
1109
WHERE project_group.projectsetid = %d;"""%projectsetid
1112
return self.db.query(query).dictresult()
1115
"""Close the DB connection. Do not call any other functions after
1116
this. (The behaviour of doing so is undefined).
added
removed
ivle/db.py
