187
187
if dry: return query
188
188
self.db.query(query)
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
190
def update(self, primarydict, updatedict, tablename, tablefields,
225
191
primary_keys, disallowed_update=frozenset([]), dry=False):
226
192
"""Updates a row in a table, matching against primarydict to find the
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]
676
585
def get_problem_status(self, login, exercisename, dry=False):
677
586
"""Given a login name and exercise name, returns information about the
678
587
user's performance on that problem.
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()
1136
845
def close(self):
1137
846
"""Close the DB connection. Do not call any other functions after
1138
847
this. (The behaviour of doing so is undefined).
added
removed
lib/common/db.py
