« back to all changes in this revision
Viewing changes to lib/common/db.py
- browse files at revision 731
- compare with another revision
- download diff
- download tarball
- view history from revision 731
- Committer: dcoles
- Date: 2008-04-14 03:52:13 UTC
- Revision ID: svn-v3-trunk0:2b9c9e99-6f39-0410-b283-7f802c844ae2:trunk:731
Help: Wrote a small help document for the console app. Now one less glaring
'TODO:' in IVLE.
'TODO:' in IVLE.
- files added:
- www/media/help
- files modified:
- lib/common/cgirequest.py
added
removed
lib/common/db.py
51
51
* bool: Returns as "TRUE" or "FALSE", unquoted.
52
52
* NoneType: Returns "NULL", unquoted.
53
53
* common.caps.Role: Returns the role as a quoted, lowercase string.
54
* time.struct_time: Returns the time as a quoted string for insertion into
55
a TIMESTAMP column.
54
56
Raises a DBException if val has an unsupported type.
55
57
"""
56
58
# "E'" is postgres's way of making "escape" strings.
74
76
return _escape(time.strftime(TIMESTAMP_FORMAT, val))
75
77
else:
76
78
raise DBException("Attempt to insert an unsupported type "
77
"into the database")
79
"into the database (%s)" % repr(type(val)))
80
81
def _parse_boolean(val):
82
"""
83
Accepts a boolean as output from the DB (either the string 't' or 'f').
84
Returns a boolean value True or False.
85
Also accepts other values which mean True or False in PostgreSQL.
86
If none match, raises a DBException.
87
"""
88
# On a personal note, what sort of a language allows 7 different values
89
# to denote each of True and False?? (A: SQL)
90
if isinstance(val, bool):
91
return val
92
elif val == 't':
93
return True
94
elif val == 'f':
95
return False
96
elif val == 'true' or val == 'y' or val == 'yes' or val == '1' \
97
or val == 1:
98
return True
99
elif val == 'false' or val == 'n' or val == 'no' or val == '0' \
100
or val == 0:
101
return False
102
else:
103
raise DBException("Invalid boolean value returned from DB")
78
104
79
105
def _passhash(password):
80
106
return md5.md5(password).hexdigest()
564
590
- An int, the number of attempts they have made up to and
565
591
including the first successful attempt (or the total number of
566
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.
567
595
"""
568
problemid = self.get_problem_problemid(exercisename)
596
if isinstance(exercisename, int):
597
problemid = exercisename
598
else:
599
problemid = self.get_problem_problemid(exercisename)
569
600
loginid = self.get_user_loginid(login) # May raise a DBException
570
601
571
602
# ASSUME that it is completed, get the total number of attempts up to
598
629
count = int(result.getresult()[0][0])
599
630
return (False, count)
600
631
632
# WORKSHEET/PROBLEM ASSOCIATION AND MARKS CALCULATION
633
634
def get_worksheet_mtime(self, subject, worksheet, dry=False):
635
"""
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.
641
"""
642
try:
643
r = self.get_single(
644
{"subject": subject, "identifier": worksheet},
645
"worksheet", ["mtime"], ["subject", "identifier"],
646
dry=dry)
647
except DBException:
648
# Assume the worksheet is not in the DB
649
return None
650
if dry:
651
return r
652
if r["mtime"] is None:
653
return None
654
return time.strptime(r["mtime"], TIMESTAMP_FORMAT)
655
656
def create_worksheet(self, subject, worksheet, problems,
657
assessable=False):
658
"""
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.
664
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.
669
670
Note: As with get_problem_problemid, if a problem name is not in the
671
DB, it will be added to the problem table.
672
"""
673
self.start_transaction()
674
try:
675
# Use the current time as the "mtime" field
676
mtime = time.localtime()
677
try:
678
# Get the worksheetid
679
r = self.get_single(
680
{"subject": subject, "identifier": worksheet},
681
"worksheet", ["worksheetid"], ["subject", "identifier"])
682
worksheetid = r["worksheetid"]
683
684
# Delete any problems which might exist
685
query = ("DELETE FROM worksheet_problem "
686
"WHERE worksheetid = %d;" % worksheetid)
687
self.db.query(query)
688
# Update the row with the new details
689
query = ("UPDATE worksheet "
690
"SET assessable = %s, mtime = %s "
691
"WHERE worksheetid = %d;"
692
% (_escape(assessable), _escape(mtime), worksheetid))
693
self.db.query(query)
694
except DBException:
695
# Assume the worksheet is not in the DB
696
# Create the worksheet row
697
query = ("INSERT INTO worksheet "
698
"(subject, identifier, assessable, mtime) "
699
"VALUES (%s, %s, %s, %s);"""
700
% (_escape(subject), _escape(worksheet),
701
_escape(assessable), _escape(mtime)))
702
self.db.query(query)
703
# Now get the worksheetid again - should succeed
704
r = self.get_single(
705
{"subject": subject, "identifier": worksheet},
706
"worksheet", ["worksheetid"], ["subject", "identifier"])
707
worksheetid = r["worksheetid"]
708
709
# Now insert each problem into the worksheet_problem table
710
for problem in problems:
711
if isinstance(problem, tuple):
712
prob_identifier = problem[0]
713
try:
714
optional = problem[1]
715
except IndexError:
716
optional = False
717
else:
718
prob_identifier = problem
719
optional = False
720
problemid = self.get_problem_problemid(prob_identifier)
721
query = ("INSERT INTO worksheet_problem "
722
"(worksheetid, problemid, optional) "
723
"VALUES (%d, %d, %s);"
724
% (worksheetid, problemid, _escape(optional)))
725
self.db.query(query)
726
727
self.commit()
728
except:
729
self.rollback()
730
raise
731
732
def worksheet_is_assessable(self, subject, worksheet, dry=False):
733
r = self.get_single(
734
{"subject": subject, "identifier": worksheet},
735
"worksheet", ["assessable"], ["subject", "identifier"], dry=dry)
736
return _parse_boolean(r["assessable"])
737
738
def calculate_score_worksheet(self, login, subject, worksheet):
739
"""
740
Calculates the score for a user on a given worksheet.
741
Returns a 4-tuple of ints, consisting of:
742
(No. mandatory exercises completed,
743
Total no. mandatory exercises,
744
No. optional exercises completed,
745
Total no. optional exercises)
746
"""
747
self.start_transaction()
748
try:
749
mand_done = 0
750
mand_total = 0
751
opt_done = 0
752
opt_total = 0
753
# Get a list of problems and optionality for all problems in the
754
# worksheet
755
query = ("""SELECT problemid, optional FROM worksheet_problem
756
WHERE worksheetid = (SELECT worksheetid FROM worksheet
757
WHERE subject = %s and identifier = %s);"""
758
% (_escape(subject), _escape(worksheet)))
759
result = self.db.query(query)
760
# Now get the student's pass/fail for each problem in this worksheet
761
for problemid, optional in result.getresult():
762
done, _ = self.get_problem_status(login, problemid)
763
# done is a bool, whether this student has completed that
764
# problem
765
if _parse_boolean(optional):
766
opt_total += 1
767
if done: opt_done += 1
768
else:
769
mand_total += 1
770
if done: mand_done += 1
771
self.commit()
772
except:
773
self.rollback()
774
raise
775
return mand_done, mand_total, opt_done, opt_total
776
601
777
def close(self):
602
778
"""Close the DB connection. Do not call any other functions after
603
779
this. (The behaviour of doing so is undefined).
Loggerhead is a web-based interface for Breezy
Version: 2.0.1