619
def get_problem_attempts(self, login, exercisename, allow_inactive=True,
621
"""Given a login name and exercise name, returns a list of dicts, one
622
for each attempt made for that exercise.
623
Dicts are {'date': 'formatted_time', 'complete': bool}.
624
Ordered with the newest first.
626
Note: By default, returns de-activated problem attempts (unlike
627
get_problem_stored_text).
628
If allow_inactive is False, will not return disabled attempts.
630
Note: Even if dry, will still physically call get_problem_problemid,
631
which may mutate the DB, and get_user_loginid, which may fail.
633
problemid = self.get_problem_problemid(exercisename)
634
loginid = self.get_user_loginid(login) # May raise a DBException
635
andactive = '' if allow_inactive else ' AND active'
636
query = """SELECT date, complete FROM problem_attempt
637
WHERE loginid = %d AND problemid = %d%s
638
ORDER BY date DESC;""" % (loginid, problemid, andactive)
640
result = self.db.query(query).getresult()
641
# Make into dicts (could use dictresult, but want to convert values)
642
return [{'date': date, 'complete': _parse_boolean(complete)}
643
for date, complete in result]
645
def get_problem_attempt(self, login, exercisename, as_of,
646
allow_inactive=True, dry=False):
647
"""Given a login name, exercise name, and struct_time, returns the
648
text of the submitted attempt for this question as of that date.
649
Returns None if the user had not made an attempt on this problem at
652
Note: By default, returns de-activated problem attempts (unlike
653
get_problem_stored_text).
654
If allow_inactive is False, will not return disabled attempts.
656
Note: Even if dry, will still physically call get_problem_problemid,
657
which may mutate the DB, and get_user_loginid, which may fail.
659
problemid = self.get_problem_problemid(exercisename)
660
loginid = self.get_user_loginid(login) # May raise a DBException
661
# Very similar to query in get_problem_stored_text, but without
662
# looking in problem_save, and restricting to a certain date.
663
andactive = '' if allow_inactive else ' AND active'
664
query = """SELECT attempt FROM problem_attempt
665
WHERE loginid = %d AND problemid = %d%s AND date <= %s
667
LIMIT 1;""" % (loginid, problemid, andactive, _escape(as_of))
669
result = self.db.query(query)
670
if result.ntuples() == 1:
671
# The user has made at least 1 attempt. Return the newest.
672
return result.getresult()[0][0]
559
676
def get_problem_status(self, login, exercisename, dry=False):
560
677
"""Given a login name and exercise name, returns information about the
561
678
user's performance on that problem.
594
716
# Return the total number of attempts, and False for success.
595
717
query = """SELECT COUNT(*) FROM problem_attempt
596
WHERE loginid = %d AND problemid = %d;""" % (loginid, problemid)
718
WHERE loginid = %d AND problemid = %d AND active;""" % (loginid, problemid)
597
719
result = self.db.query(query)
598
720
count = int(result.getresult()[0][0])
599
721
return (False, count)
723
# WORKSHEET/PROBLEM ASSOCIATION AND MARKS CALCULATION
725
def get_worksheet_mtime(self, subject, worksheet, dry=False):
727
For a given subject/worksheet name, gets the time the worksheet was
728
last updated in the DB, if any.
729
This can be used to check if there is a newer version on disk.
730
Returns the timestamp as a time.struct_time, or None if the worksheet
731
is not found or has no stored mtime.
735
{"subject": subject, "identifier": worksheet},
736
"worksheet", ["mtime"], ["subject", "identifier"],
739
# Assume the worksheet is not in the DB
743
if r["mtime"] is None:
745
return time.strptime(r["mtime"], TIMESTAMP_FORMAT)
747
def create_worksheet(self, subject, worksheet, problems=None,
750
Inserts or updates rows in the worksheet and worksheet_problems
751
tables, to create a worksheet in the database.
752
This atomically performs all operations. If the worksheet is already
753
in the DB, removes it and all its associated problems and rebuilds.
754
Sets the timestamp to the current time.
756
problems is a collection of pairs. The first element of the pair is
757
the problem identifier ("identifier" column of the problem table). The
758
second element is an optional boolean, "optional". This can be omitted
759
(so it's a 1-tuple), and then it will default to False.
761
Problems and assessable are optional, and if omitted, will not change
762
the existing data. If the worksheet does not yet exist, and assessable
763
is omitted, it defaults to False.
765
Note: As with get_problem_problemid, if a problem name is not in the
766
DB, it will be added to the problem table.
768
self.start_transaction()
770
# Use the current time as the "mtime" field
771
mtime = time.localtime()
773
# Get the worksheetid
775
{"subject": subject, "identifier": worksheet},
776
"worksheet", ["worksheetid"], ["subject", "identifier"])
777
worksheetid = r["worksheetid"]
779
# Delete any problems which might exist, if problems is
780
# supplied. If it isn't, keep the existing ones.
781
if problems is not None:
782
query = ("DELETE FROM worksheet_problem "
783
"WHERE worksheetid = %d;" % worksheetid)
785
# Update the row with the new details
786
if assessable is None:
787
query = ("UPDATE worksheet "
788
"SET mtime = %s WHERE worksheetid = %d;"
789
% (_escape(mtime), worksheetid))
791
query = ("UPDATE worksheet "
792
"SET assessable = %s, mtime = %s "
793
"WHERE worksheetid = %d;"
794
% (_escape(assessable), _escape(mtime), worksheetid))
797
# Assume the worksheet is not in the DB
798
# If assessable is not supplied, default to False.
799
if assessable is None:
801
# Create the worksheet row
802
query = ("INSERT INTO worksheet "
803
"(subject, identifier, assessable, mtime) "
804
"VALUES (%s, %s, %s, %s);"""
805
% (_escape(subject), _escape(worksheet),
806
_escape(assessable), _escape(mtime)))
808
# Now get the worksheetid again - should succeed
810
{"subject": subject, "identifier": worksheet},
811
"worksheet", ["worksheetid"], ["subject", "identifier"])
812
worksheetid = r["worksheetid"]
814
# Now insert each problem into the worksheet_problem table
815
if problems is not None:
816
for problem in problems:
817
if isinstance(problem, tuple):
818
prob_identifier = problem[0]
820
optional = problem[1]
824
prob_identifier = problem
826
problemid = self.get_problem_problemid(prob_identifier)
827
query = ("INSERT INTO worksheet_problem "
828
"(worksheetid, problemid, optional) "
829
"VALUES (%d, %d, %s);"
830
% (worksheetid, problemid, _escape(optional)))
838
def set_worksheet_assessable(self, subject, worksheet, assessable,
841
Sets the "assessable" field of a worksheet without updating the mtime.
843
IMPORTANT: This will NOT update the mtime. This is designed to allow
844
updates which did not come from the worksheet XML file. It would be
845
bad to update the mtime without consulting the XML file because then
846
it would appear the database is up to date, when it isn't.
848
Therefore, call this method if you are getting "assessable"
849
information from outside the worksheet XML file (eg. from the subject
852
Unlike create_worksheet, raises a DBException if the worksheet is not
855
return self.update({"subject": subject, "identifier": worksheet},
856
{"assessable": assessable}, "worksheet", ["assessable"],
857
["subject", "identifier"], dry=dry)
859
def worksheet_is_assessable(self, subject, worksheet, dry=False):
861
{"subject": subject, "identifier": worksheet},
862
"worksheet", ["assessable"], ["subject", "identifier"], dry=dry)
863
return _parse_boolean(r["assessable"])
865
def calculate_score_worksheet(self, login, subject, worksheet):
867
Calculates the score for a user on a given worksheet.
868
Returns a 4-tuple of ints, consisting of:
869
(No. mandatory exercises completed,
870
Total no. mandatory exercises,
871
No. optional exercises completed,
872
Total no. optional exercises)
874
self.start_transaction()
880
# Get a list of problems and optionality for all problems in the
882
query = ("""SELECT problemid, optional FROM worksheet_problem
883
WHERE worksheetid = (SELECT worksheetid FROM worksheet
884
WHERE subject = %s and identifier = %s);"""
885
% (_escape(subject), _escape(worksheet)))
886
result = self.db.query(query)
887
# Now get the student's pass/fail for each problem in this worksheet
888
for problemid, optional in result.getresult():
889
done, _ = self.get_problem_status(login, problemid)
890
# done is a bool, whether this student has completed that
892
if _parse_boolean(optional):
894
if done: opt_done += 1
897
if done: mand_done += 1
902
return mand_done, mand_total, opt_done, opt_total
904
# ENROLMENT INFORMATION
906
def add_enrolment(self, login, subj_code, semester, year=None, dry=False):
908
Enrol a student in the given offering of a subject.
909
Returns True on success, False on failure (which usually means either
910
the student is already enrolled in the subject, the student was not
911
found, or no offering existed with the given details).
912
The return value can usually be ignored.
914
subj_code = str(subj_code)
915
semester = str(semester)
917
year = str(time.gmtime().tm_year)
921
INSERT INTO enrolment (loginid, offeringid)
923
(SELECT loginid FROM login WHERE login=%s),
925
FROM offering, subject, semester
926
WHERE subject.subjectid = offering.subject
927
AND semester.semesterid = offering.semesterid
928
AND subj_code=%s AND semester=%s AND year=%s)
929
);""" % (_escape(login), _escape(subj_code), _escape(semester),
934
result = self.db.query(query)
935
except pg.ProgrammingError:
939
# SUBJECTS AND ENROLEMENT
941
def get_subjects(self, dry=False):
943
Get all subjects in IVLE.
944
Returns a list of dicts (all values strings), with the keys:
945
subj_code, subj_name, subj_short_name, url
947
return self.get_all("subject",
948
("subjectid", "subj_code", "subj_name", "subj_short_name", "url"),
951
def get_offering_semesters(self, subjectid, dry=False):
953
Get the semester information for a subject as well as providing
954
information about if the subject is active and which semester it is in.
957
SELECT offeringid, subj_name, year, semester, active
958
FROM semester, offering, subject
959
WHERE offering.semesterid = semester.semesterid AND
960
offering.subject = subject.subjectid AND
961
offering.subject = %d;"""%subjectid
964
results = self.db.query(query).dictresult()
965
# Parse boolean varibles
966
for result in results:
967
result['active'] = _parse_boolean(result['active'])
970
def get_offering_members(self, offeringid, dry=False):
972
Gets the logins of all the people enroled in an offering
975
SELECT login.login AS login, login.fullname AS fullname
976
FROM login, enrolment
977
WHERE login.loginid = enrolment.loginid AND
978
enrolment.offeringid = %d;"""%offeringid
981
return self.db.query(query).dictresult()
984
def get_enrolment(self, login, dry=False):
986
Get all offerings (in IVLE) the student is enrolled in.
987
Returns a list of dicts (all values strings), with the keys:
988
offeringid, subj_code, subj_name, subj_short_name, year, semester, url
991
SELECT offering.offeringid, subj_code, subj_name, subj_short_name,
992
semester.year, semester.semester, subject.url
993
FROM login, enrolment, offering, subject, semester
994
WHERE enrolment.offeringid=offering.offeringid
995
AND login.loginid=enrolment.loginid
996
AND offering.subject=subject.subjectid
997
AND semester.semesterid=offering.semesterid
999
AND login=%s;""" % _escape(login)
1002
return self.db.query(query).dictresult()
1004
def get_enrolment_groups(self, login, offeringid, dry=False):
1006
Get all groups the user is member of in the given offering.
1007
Returns a list of dicts (all values strings), with the keys:
1011
SELECT project_group.groupnm as name, project_group.nick as nick
1012
FROM project_set, project_group, group_member, login
1013
WHERE login.login=%s
1014
AND project_set.offeringid=%s
1015
AND group_member.loginid=login.loginid
1016
AND project_group.groupid=group_member.groupid
1017
AND project_group.projectsetid=project_set.projectsetid
1018
""" % (_escape(login), _escape(offeringid))
1021
return self.db.query(query).dictresult()
1023
def get_subjects_status(self, login, dry=False):
1025
Get all subjects in IVLE, split into lists of enrolled and unenrolled
1027
Returns a tuple of lists (enrolled, unenrolled) of dicts
1028
(all values strings) with the keys:
1029
subj_code, subj_name, subj_short_name, url
1031
enrolments = self.get_enrolment(login)
1032
all_subjects = self.get_subjects()
1034
enrolled_set = set(x['subj_code'] for x in enrolments)
1036
enrolled_subjects = [x for x in all_subjects
1037
if x['subj_code'] in enrolled_set]
1038
unenrolled_subjects = [x for x in all_subjects
1039
if x['subj_code'] not in enrolled_set]
1040
enrolled_subjects.sort(key=lambda x: x['subj_code'])
1041
unenrolled_subjects.sort(key=lambda x: x['subj_code'])
1042
return (enrolled_subjects, unenrolled_subjects)
1046
def get_groups_by_user(self, login, offeringid=None, dry=False):
1048
Get all project groups the student is in, corresponding to a
1049
particular subject offering (or all offerings, if omitted).
1050
Returns a list of tuples:
1051
(int groupid, str groupnm, str group_nick, bool is_member).
1052
(Note: If is_member is false, it means they have just been invited to
1053
this group, not a member).
1055
if offeringid is None:
1058
and_projectset_table = ", project_set"
1060
AND project_group.projectsetid = project_set.projectsetid
1061
AND project_set.offeringid = %s""" % _escape(offeringid)
1062
# Union both the groups this user is a member of, and the groups this
1063
# user is invited to.
1065
SELECT project_group.groupid, groupnm, project_group.nick, True
1066
FROM project_group, group_member, login %(and_projectset_table)s
1067
WHERE project_group.groupid = group_member.groupid
1068
AND group_member.loginid = login.loginid
1069
AND login = %(login)s
1072
SELECT project_group.groupid, groupnm, project_group.nick, False
1073
FROM project_group, group_invitation, login %(and_projectset_table)s
1074
WHERE project_group.groupid = group_invitation.groupid
1075
AND group_invitation.loginid = login.loginid
1076
AND login = %(login)s
1078
;""" % {"login": _escape(login), "and_offering": and_offering,
1079
"and_projectset_table": and_projectset_table}
1082
# Convert 't' -> True, 'f' -> False
1083
return [(groupid, groupnm, nick, ismember == 't')
1084
for groupid, groupnm, nick, ismember
1085
in self.db.query(query).getresult()]
1087
def get_offering_info(self, projectsetid, dry=False):
1088
"""Takes information from projectset and returns useful information
1089
about the subject and semester. Returns as a dictionary.
1092
SELECT subjectid, subj_code, subj_name, subj_short_name, url, year, semester,
1094
FROM subject, offering, semester, project_set
1095
WHERE offering.subject = subject.subjectid AND
1096
offering.semesterid = semester.semesterid AND
1097
project_set.offeringid = offering.offeringid AND
1098
project_set.projectsetid = %d;"""%projectsetid
1101
return self.db.query(query).dictresult()[0]
1103
def get_projectgroup_members(self, groupid, dry=False):
1104
"""Returns the logins of all students in a project group
1107
SELECT login.login as login, login.fullname as fullname
1108
FROM login, group_member
1109
WHERE login.loginid = group_member.loginid AND
1110
group_member.groupid = %d
1111
ORDER BY login.login;"""%groupid
1114
return self.db.query(query).dictresult()
1116
def get_projectsets_by_offering(self, offeringid, dry=False):
1117
"""Returns all the projectsets in a particular offering"""
1119
SELECT projectsetid, max_students_per_group
1121
WHERE project_set.offeringid = %d;"""%offeringid
1124
return self.db.query(query).dictresult()
1126
def get_groups_by_projectset(self, projectsetid, dry=False):
1127
"""Returns all the groups that are in a particular projectset"""
1129
SELECT groupid, groupnm, nick, createdby, epoch
1131
WHERE project_group.projectsetid = %d;"""%projectsetid
1134
return self.db.query(query).dictresult()
601
1136
def close(self):
602
1137
"""Close the DB connection. Do not call any other functions after
603
1138
this. (The behaviour of doing so is undefined).
added
removed
lib/common/db.py
