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
# ENROLMENT INFORMATION
815
def add_enrolment(self, login, subj_code, semester, year=None, dry=False):
817
Enrol a student in the given offering of a subject.
818
Returns True on success, False on failure (which usually means either
819
the student is already enrolled in the subject, the student was not
820
found, or no offering existed with the given details).
821
The return value can usually be ignored.
823
subj_code = str(subj_code)
824
semester = str(semester)
826
year = str(time.gmtime().tm_year)
830
INSERT INTO enrolment (loginid, offeringid)
832
(SELECT loginid FROM login WHERE login=%s),
834
FROM (offering INNER JOIN subject
835
ON subject.subjectid = offering.subject
837
ON semester.semesterid = offering.semesterid)
838
WHERE subj_code=%s AND semester=%s AND year=%s)
839
);""" % (_escape(login), _escape(subj_code), _escape(semester),
844
result = self.db.query(query)
845
except pg.ProgrammingError:
849
# SUBJECTS AND ENROLEMENT
851
def get_enrolment(self, login, dry=False):
853
Get all subjects (in IVLE) the student is enrolled in.
854
Returns a list of tuples (all elements strings):
855
(offeringid, subj_code, subj_name, subj_short_name, year, semester).
858
SELECT offering.offeringid, subj_code, subj_name, subj_short_name,
859
semester.year, semester.semester
860
FROM login, enrolment, offering, subject, semester
861
WHERE enrolment.offeringid=offering.offeringid
862
AND login.loginid=enrolment.loginid
863
AND offering.subject=subject.subjectid
864
AND semester.semesterid=offering.semesterid
866
AND login=%s;""" % _escape(login)
869
return self.db.query(query).dictresult()
873
def get_groups_by_user(self, login, offeringid=None, dry=False):
875
Get all project groups the student is in, corresponding to a
876
particular subject offering (or all offerings, if omitted).
877
Returns a list of tuples:
878
(int groupid, str groupnm, str group_nick, bool is_member).
879
(Note: If is_member is false, it means they have just been invited to
880
this group, not a member).
882
if offeringid is None:
885
and_projectset_table = ", project_set"
887
AND project_group.projectsetid = project_set.projectsetid
888
AND project_set.offeringid = %s""" % _escape(offeringid)
889
# Union both the groups this user is a member of, and the groups this
890
# user is invited to.
892
SELECT project_group.groupid, groupnm, project_group.nick, True
893
FROM project_group, group_member, login %(and_projectset_table)s
894
WHERE project_group.groupid = group_member.groupid
895
AND group_member.loginid = login.loginid
896
AND login = %(login)s
899
SELECT project_group.groupid, groupnm, project_group.nick, False
900
FROM project_group, group_invitation, login %(and_projectset_table)s
901
WHERE project_group.groupid = group_invitation.groupid
902
AND group_invitation.loginid = login.loginid
903
AND login = %(login)s
905
;""" % {"login": _escape(login), "and_offering": and_offering,
906
"and_projectset_table": and_projectset_table}
909
# Convert 't' -> True, 'f' -> False
910
return [(groupid, groupnm, nick, ismember == 't')
911
for groupid, groupnm, nick, ismember
912
in self.db.query(query).getresult()]
915
406
"""Close the DB connection. Do not call any other functions after
916
407
this. (The behaviour of doing so is undefined).
added
removed
lib/common/db.py
