441
359
def get_users(self, dry=False):
442
360
"""Returns a list of all users in the DB, as User objects.
444
userdicts = self.get_all("login", self.login_fields, dry=dry)
362
userdicts = self.get_all("login", self.login_getfields, dry=dry)
446
364
return userdicts # Query string
447
365
# Package into User objects
448
366
return [user.User(**userdict) for userdict in userdicts]
450
def get_user_loginid(self, login, dry=False):
451
"""Given a login, returns the integer loginid for this user.
453
Raises a DBException if the login is not found in the DB.
455
userdict = self.get_single({"login": login}, "login",
456
['loginid'], self.login_primary,
457
error_notfound="get_user_loginid: No user with that login name",
460
return userdict # Query string
461
return userdict['loginid']
463
368
def user_authenticate(self, login, password, dry=False):
464
369
"""Performs a password authentication on a user. Returns True if
465
370
"passhash" is the correct passhash for the given login, False
466
if the passhash does not match the password in the DB,
467
and None if the passhash in the DB is NULL.
468
372
Also returns False if the login does not exist (so if you want to
469
373
differentiate these cases, use get_user and catch an exception).
471
query = ("SELECT passhash FROM login WHERE login = %s;"
474
result = self.db.query(query)
475
if result.ntuples() == 1:
476
# Valid username. Check password.
477
passhash = result.getresult()[0][0]
480
return _passhash(password) == passhash
484
# PROBLEM AND PROBLEM ATTEMPT FUNCTIONS #
486
def get_problem_problemid(self, exercisename, dry=False):
487
"""Given an exercise name, returns the associated problemID.
488
If the exercise name is NOT in the database, it inserts it and returns
489
the new problemID. Hence this may mutate the DB, but is idempotent.
492
d = self.get_single({"identifier": exercisename}, "problem",
493
['problemid'], frozenset(["identifier"]),
496
return d # Query string
499
# Shouldn't try again, must have failed for some other reason
501
# if we failed to get a problemid, it was probably because
502
# the exercise wasn't in the db. So lets insert it!
504
# The insert can fail if someone else simultaneously does
505
# the insert, so if the insert fails, we ignore the problem.
507
self.insert({'identifier': exercisename}, "problem",
508
frozenset(['identifier']))
512
# Assuming the insert succeeded, we should be able to get the
514
d = self.get_single({"identifier": exercisename}, "problem",
515
['problemid'], frozenset(["identifier"]))
517
return d['problemid']
519
def insert_problem_attempt(self, login, exercisename, date, complete,
521
"""Inserts the details of a problem attempt into the database.
522
exercisename: Name of the exercise. (identifier field of problem
523
table). If this exercise does not exist, also creates a new row in
524
the problem table for this exercise name.
525
login: Name of the user submitting the attempt. (login field of the
527
date: struct_time, the date this attempt was made.
528
complete: bool. Whether the test passed or not.
529
attempt: Text of the attempt.
531
Note: Even if dry, will still physically call get_problem_problemid,
532
which may mutate the DB, and get_user_loginid, which may fail.
534
problemid = self.get_problem_problemid(exercisename)
535
loginid = self.get_user_loginid(login) # May raise a DBException
538
'problemid': problemid,
541
'complete': complete,
543
}, 'problem_attempt',
544
frozenset(['problemid','loginid','date','complete','attempt']),
547
def write_problem_save(self, login, exercisename, date, text, dry=False):
548
"""Writes text to the problem_save table (for when the user saves an
549
exercise). Creates a new row, or overwrites an existing one if the
550
user has already saved that problem.
551
(Unlike problem_attempt, does not keep historical records).
553
problemid = self.get_problem_problemid(exercisename)
554
loginid = self.get_user_loginid(login) # May raise a DBException
558
'problemid': problemid,
563
frozenset(['problemid','loginid','date','text']),
565
except pg.ProgrammingError:
566
# May have failed because this problemid/loginid row already
567
# exists (they have a unique key constraint).
568
# Do an update instead.
570
# Shouldn't try again, must have failed for some other reason
573
'problemid': problemid,
580
frozenset(['date', 'text']),
581
frozenset(['problemid', 'loginid']))
583
def get_problem_stored_text(self, login, exercisename, dry=False):
584
"""Given a login name and exercise name, returns the text of the
585
last saved/submitted attempt for this question.
586
Returns None if the user has not saved or made an attempt on this
588
(If the user has both saved and submitted, it returns whichever was
591
Note: Even if dry, will still physically call get_problem_problemid,
592
which may mutate the DB, and get_user_loginid, which may fail.
594
problemid = self.get_problem_problemid(exercisename)
595
loginid = self.get_user_loginid(login) # May raise a DBException
596
# This very complex query finds all submissions made by this user for
597
# this problem, as well as the save made by this user for this
598
# problem, and returns the text of the newest one.
599
# (Whichever is newer out of the save or the submit).
600
query = """SELECT text FROM
602
(SELECT * FROM problem_save WHERE loginid = %d AND problemid = %d)
604
(SELECT problemid, loginid, date, text FROM problem_attempt
605
AS problem_attempt (problemid, loginid, date, text)
606
WHERE loginid = %d AND problemid = %d AND active)
610
LIMIT 1;""" % (loginid, problemid, loginid, problemid)
612
result = self.db.query(query)
613
if result.ntuples() == 1:
614
# The user has made at least 1 attempt. Return the newest.
615
return result.getresult()[0][0]
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
def get_problem_status(self, login, exercisename, dry=False):
677
"""Given a login name and exercise name, returns information about the
678
user's performance on that problem.
680
- A boolean, whether they have successfully passed this exercise.
681
- An int, the number of attempts they have made up to and
682
including the first successful attempt (or the total number of
683
attempts, if not yet successful).
684
Note: exercisename may be an int, in which case it will be directly
685
used as the problemid.
687
if isinstance(exercisename, int):
688
problemid = exercisename
690
problemid = self.get_problem_problemid(exercisename)
691
loginid = self.get_user_loginid(login) # May raise a DBException
693
# ASSUME that it is completed, get the total number of attempts up to
694
# and including the first successful attempt.
695
# (Get the date of the first successful attempt. Then count the number
696
# of attempts made <= that date).
697
# Will return an empty table if the problem has never been
698
# successfully completed.
699
query = """SELECT COUNT(*) FROM problem_attempt
700
WHERE loginid = %d AND problemid = %d AND active AND date <=
701
(SELECT date FROM problem_attempt
702
WHERE loginid = %d AND problemid = %d AND complete AND active
704
LIMIT 1);""" % (loginid, problemid, loginid, problemid)
706
result = self.db.query(query)
707
count = int(result.getresult()[0][0])
709
# The user has made at least 1 successful attempt.
710
# Return True for success, and the number of attempts up to and
711
# including the successful one.
714
# Returned 0 rows - this indicates that the problem has not been
716
# Return the total number of attempts, and False for success.
717
query = """SELECT COUNT(*) FROM problem_attempt
718
WHERE loginid = %d AND problemid = %d AND active;""" % (loginid, problemid)
719
result = self.db.query(query)
720
count = int(result.getresult()[0][0])
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
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()
375
query = ("SELECT login FROM login "
376
"WHERE login = '%s' AND passhash = %s;"
377
% (login, _escape(_passhash(password))))
379
result = self.db.query(query)
380
# If one row was returned, succeed.
381
# Otherwise, fail to authenticate.
382
return result.ntuples() == 1
1137
384
def close(self):
1138
385
"""Close the DB connection. Do not call any other functions after
added
removed
lib/common/db.py
