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.
902
811
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
979
ORDER BY login.login;"""%offeringid
982
return self.db.query(query).dictresult()
985
def get_enrolment(self, login, dry=False):
987
Get all offerings (in IVLE) the student is enrolled in.
988
Returns a list of dicts (all values strings), with the keys:
989
offeringid, subj_code, subj_name, subj_short_name, year, semester, url
992
SELECT offering.offeringid, subj_code, subj_name, subj_short_name,
993
semester.year, semester.semester, subject.url
994
FROM login, enrolment, offering, subject, semester
995
WHERE enrolment.offeringid=offering.offeringid
996
AND login.loginid=enrolment.loginid
997
AND offering.subject=subject.subjectid
998
AND semester.semesterid=offering.semesterid
1000
AND login=%s;""" % _escape(login)
1003
return self.db.query(query).dictresult()
1005
def get_enrolment_groups(self, login, offeringid, dry=False):
1007
Get all groups the user is member of in the given offering.
1008
Returns a list of dicts (all values strings), with the keys:
1012
SELECT project_group.groupnm as name, project_group.nick as nick
1013
FROM project_set, project_group, group_member, login
1014
WHERE login.login=%s
1015
AND project_set.offeringid=%s
1016
AND group_member.loginid=login.loginid
1017
AND project_group.groupid=group_member.groupid
1018
AND project_group.projectsetid=project_set.projectsetid
1019
""" % (_escape(login), _escape(offeringid))
1022
return self.db.query(query).dictresult()
1024
def get_subjects_status(self, login, dry=False):
1026
Get all subjects in IVLE, split into lists of enrolled and unenrolled
1028
Returns a tuple of lists (enrolled, unenrolled) of dicts
1029
(all values strings) with the keys:
1030
subj_code, subj_name, subj_short_name, url
1032
enrolments = self.get_enrolment(login)
1033
all_subjects = self.get_subjects()
1035
enrolled_set = set(x['subj_code'] for x in enrolments)
1037
enrolled_subjects = [x for x in all_subjects
1038
if x['subj_code'] in enrolled_set]
1039
unenrolled_subjects = [x for x in all_subjects
1040
if x['subj_code'] not in enrolled_set]
1041
enrolled_subjects.sort(key=lambda x: x['subj_code'])
1042
unenrolled_subjects.sort(key=lambda x: x['subj_code'])
1043
return (enrolled_subjects, unenrolled_subjects)
1047
def get_groups_by_user(self, login, offeringid=None, dry=False):
1049
Get all project groups the student is in, corresponding to a
1050
particular subject offering (or all offerings, if omitted).
1051
Returns a list of tuples:
1052
(int groupid, str groupnm, str group_nick, bool is_member).
1053
(Note: If is_member is false, it means they have just been invited to
1054
this group, not a member).
1056
if offeringid is None:
1059
and_projectset_table = ", project_set"
1061
AND project_group.projectsetid = project_set.projectsetid
1062
AND project_set.offeringid = %s""" % _escape(offeringid)
1063
# Union both the groups this user is a member of, and the groups this
1064
# user is invited to.
1066
SELECT project_group.groupid, groupnm, project_group.nick, True
1067
FROM project_group, group_member, login %(and_projectset_table)s
1068
WHERE project_group.groupid = group_member.groupid
1069
AND group_member.loginid = login.loginid
1070
AND login = %(login)s
1073
SELECT project_group.groupid, groupnm, project_group.nick, False
1074
FROM project_group, group_invitation, login %(and_projectset_table)s
1075
WHERE project_group.groupid = group_invitation.groupid
1076
AND group_invitation.loginid = login.loginid
1077
AND login = %(login)s
1079
;""" % {"login": _escape(login), "and_offering": and_offering,
1080
"and_projectset_table": and_projectset_table}
1083
# Convert 't' -> True, 'f' -> False
1084
return [(groupid, groupnm, nick, ismember == 't')
1085
for groupid, groupnm, nick, ismember
1086
in self.db.query(query).getresult()]
1088
def get_offering_info(self, projectsetid, dry=False):
1089
"""Takes information from projectset and returns useful information
1090
about the subject and semester. Returns as a dictionary.
1093
SELECT subjectid, subj_code, subj_name, subj_short_name, url, year, semester,
1095
FROM subject, offering, semester, project_set
1096
WHERE offering.subject = subject.subjectid AND
1097
offering.semesterid = semester.semesterid AND
1098
project_set.offeringid = offering.offeringid AND
1099
project_set.projectsetid = %d;"""%projectsetid
1102
return self.db.query(query).dictresult()[0]
1104
def get_projectgroup_members(self, groupid, dry=False):
1105
"""Returns the logins of all students in a project group
1108
SELECT login.login as login, login.fullname as fullname
1109
FROM login, group_member
1110
WHERE login.loginid = group_member.loginid AND
1111
group_member.groupid = %d
1112
ORDER BY login.login;"""%groupid
1115
return self.db.query(query).dictresult()
1117
def get_projectsets_by_offering(self, offeringid, dry=False):
1118
"""Returns all the projectsets in a particular offering"""
1120
SELECT projectsetid, max_students_per_group
1122
WHERE project_set.offeringid = %d;"""%offeringid
1125
return self.db.query(query).dictresult()
1127
def get_groups_by_projectset(self, projectsetid, dry=False):
1128
"""Returns all the groups that are in a particular projectset"""
1130
SELECT groupid, groupnm, nick, createdby, epoch
1132
WHERE project_group.projectsetid = %d;"""%projectsetid
1135
return self.db.query(query).dictresult()
1137
813
def close(self):
1138
814
"""Close the DB connection. Do not call any other functions after
1139
815
this. (The behaviour of doing so is undefined).
added
removed
lib/common/db.py
