424
# PROBLEM AND PROBLEM ATTEMPT FUNCTIONS #
426
def get_problem_problemid(self, exercisename, dry=False):
427
"""Given an exercise name, returns the associated problemID.
428
If the exercise name is NOT in the database, it inserts it and returns
429
the new problemID. Hence this may mutate the DB, but is idempotent.
432
d = self.get_single({"identifier": exercisename}, "problem",
433
['problemid'], frozenset(["identifier"]),
436
return d # Query string
439
# Shouldn't try again, must have failed for some other reason
441
# if we failed to get a problemid, it was probably because
442
# the exercise wasn't in the db. So lets insert it!
444
# The insert can fail if someone else simultaneously does
445
# the insert, so if the insert fails, we ignore the problem.
447
self.insert({'identifier': exercisename}, "problem",
448
frozenset(['identifier']))
452
# Assuming the insert succeeded, we should be able to get the
454
d = self.get_single({"identifier": exercisename}, "problem",
455
['problemid'], frozenset(["identifier"]))
457
return d['problemid']
459
def insert_problem_attempt(self, login, exercisename, date, complete,
461
"""Inserts the details of a problem attempt into the database.
462
exercisename: Name of the exercise. (identifier field of problem
463
table). If this exercise does not exist, also creates a new row in
464
the problem table for this exercise name.
465
login: Name of the user submitting the attempt. (login field of the
467
date: struct_time, the date this attempt was made.
468
complete: bool. Whether the test passed or not.
469
attempt: Text of the attempt.
471
Note: Even if dry, will still physically call get_problem_problemid,
472
which may mutate the DB, and get_user_loginid, which may fail.
474
problemid = self.get_problem_problemid(exercisename)
475
loginid = self.get_user_loginid(login) # May raise a DBException
478
'problemid': problemid,
481
'complete': complete,
483
}, 'problem_attempt',
484
frozenset(['problemid','loginid','date','complete','attempt']),
487
def write_problem_save(self, login, exercisename, date, text, dry=False):
488
"""Writes text to the problem_save table (for when the user saves an
489
exercise). Creates a new row, or overwrites an existing one if the
490
user has already saved that problem.
491
(Unlike problem_attempt, does not keep historical records).
493
problemid = self.get_problem_problemid(exercisename)
494
loginid = self.get_user_loginid(login) # May raise a DBException
498
'problemid': problemid,
503
frozenset(['problemid','loginid','date','text']),
505
except pg.ProgrammingError:
506
# May have failed because this problemid/loginid row already
507
# exists (they have a unique key constraint).
508
# Do an update instead.
510
# Shouldn't try again, must have failed for some other reason
513
'problemid': problemid,
520
frozenset(['date', 'text']),
521
frozenset(['problemid', 'loginid']))
523
def get_problem_stored_text(self, login, exercisename, dry=False):
524
"""Given a login name and exercise name, returns the text of the
525
last saved/submitted attempt for this question.
526
Returns None if the user has not saved or made an attempt on this
528
(If the user has both saved and submitted, it returns whichever was
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
536
# This very complex query finds all submissions made by this user for
537
# this problem, as well as the save made by this user for this
538
# problem, and returns the text of the newest one.
539
# (Whichever is newer out of the save or the submit).
540
query = """SELECT text FROM
542
(SELECT * FROM problem_save WHERE loginid = %d AND problemid = %d)
544
(SELECT problemid, loginid, date, text FROM problem_attempt
545
AS problem_attempt (problemid, loginid, date, text)
546
WHERE loginid = %d AND problemid = %d)
550
LIMIT 1;""" % (loginid, problemid, loginid, problemid)
552
result = self.db.query(query)
553
if result.ntuples() == 1:
554
# The user has made at least 1 attempt. Return the newest.
555
return result.getresult()[0][0]
559
def get_problem_status(self, login, exercisename, dry=False):
560
"""Given a login name and exercise name, returns information about the
561
user's performance on that problem.
563
- A boolean, whether they have successfully passed this exercise.
564
- An int, the number of attempts they have made up to and
565
including the first successful attempt (or the total number of
566
attempts, if not yet successful).
568
problemid = self.get_problem_problemid(exercisename)
569
loginid = self.get_user_loginid(login) # May raise a DBException
571
# ASSUME that it is completed, get the total number of attempts up to
572
# and including the first successful attempt.
573
# (Get the date of the first successful attempt. Then count the number
574
# of attempts made <= that date).
575
# Will return an empty table if the problem has never been
576
# successfully completed.
577
query = """SELECT COUNT(*) FROM problem_attempt
578
WHERE loginid = %d AND problemid = %d AND date <=
579
(SELECT date FROM problem_attempt
580
WHERE loginid = %d AND problemid = %d AND complete = TRUE
582
LIMIT 1);""" % (loginid, problemid, loginid, problemid)
584
result = self.db.query(query)
585
count = int(result.getresult()[0][0])
587
# The user has made at least 1 successful attempt.
588
# Return True for success, and the number of attempts up to and
589
# including the successful one.
592
# Returned 0 rows - this indicates that the problem has not been
594
# Return the total number of attempts, and False for success.
595
query = """SELECT COUNT(*) FROM problem_attempt
596
WHERE loginid = %d AND problemid = %d;""" % (loginid, problemid)
597
result = self.db.query(query)
598
count = int(result.getresult()[0][0])
599
return (False, count)
602
406
"""Close the DB connection. Do not call any other functions after
603
407
this. (The behaviour of doing so is undefined).
added
removed
lib/common/db.py
