class SQLStore:
def __init__(self, config):
# TODO: concurrency issue resolved?
if config['apsw']:
from apsw import Connection
self.store = Connection(config['sql'])
self.store.cursor().execute('PRAGMA foreign_keys = ON')
else:
from sqlite3 import connect
self.store = connect(config['sql'], check_same_thread=False)
self.store.execute('PRAGMA foreign_keys = ON')
self._loadAdminConf(config['admin'])
self.dropbox = config['dropbox']
# TODO: Awkward, but this'll need to be the hour difference between Pacific
# and wherever the server is. Shouldn't be an issue if we use GU.
self.timezone = 1 * 60 * 60
"""
Returns true iff the argument 'username' is equal to a known admin username.
"""
def isInstructor(self, username):
return username == self.admin_name
"""
Returns true iff the argument 'username' is associcated with the given student
table primary key.
"""
def isStudent(self, username, id):
return self.usernameID(username) == id;
"""
Returns the student primary key for a user with the given username.
"""
def usernameID(self, username):
matches = self._select('students', [('username', username)])
return None if len(matches) == 0 else matches[0]['id']
"""
Returns true iff the given username matches the given password.
"""
def passwordMatches(self, username, passwordCandidate):
actual = self.password(username)
if actual == None:
return False
return pwd_context.verify(passwordCandidate, actual)
"""
Returns all offerings of the course with code 'courseCode', or None if no such
course exists.
"""
def offerings(self, courseCode):
return self._select('courses', [('code', courseCode)])
"""
Returns the most recent course offering with code 'courseCode', or None if no
such course exists.
"""
def lastOffering(self, courseCode):
offerings = self.offerings(courseCode)
if len(offerings) == 0:
return None
return sorted(offerings, compare_courses)[-1]
"""
Returns the hashed password for the user with the given login 'username'.
"""
def password(self, username):
if self.isInstructor(username):
return self.admin_pass
else:
info = self._select('students', [('username', username)])
if len(info) != 1:
return None
return info[0]['password']
"""
Gives the given 'student' the given 'password', overwriting the prior password.
"""
def editPassword(self, student, password):
self._update('students', [('password', sha256_crypt.encrypt(password))],
[('id', student)])
"""
Adds a new course with the given attributes.
If course is just an integer (like: 101), adds the prefix 'PHIL '.
The new course will inherit all assignments from the last offering of a course
with the same code.
Returns the primary key identifier for the new course.
"""
def addCourse(self, name, code, year, semester, active=False):
try:
code = 'PHIL {0}'.format(int(code))
except:
pass
lastOffering = self.lastOffering(code)
vals = (name, code, year, semester, int(active))
id = self._insert('courses', external('courses'), vals)
# Inherit all assignments from the last offering with this code.
if lastOffering != None:
for asst in self.assignments(lastOffering['id']):
del asst['id']
asst['course'] = id
self.addAssignment(**asst)
for link in self.links(lastOffering['id']):
del link['id']
link['course'] = id
self.addLink(**link)
return id
"""
Returns a list containing a course dictionary for each registered course.
"""
def courses(self):
return self._select('courses')
"""
Adds a new student with the given attributes. If the username is already
chosen, assigns the same username with an integer suffix that will make it
unique.
Returns the primary key identifier for the new student.
"""
def addStudent(self, course, section, name, email, notes, username, pseudonym, password):
# Ensure unique username.
prior = self._select('students', [('username', username)])
while username == self.admin_name or len(prior) != 0:
username += str('_')
prior = self._select('students', [('username', username)])
vals = (course, section, name, email, notes,
username, pseudonym, sha256_crypt.encrypt(password))
return self._insert('students', external('students'), vals)
"""
Returns all the students registered for the given 'course'.
If 'bySection' is true, returns a map from section name to list containing a
student dictionary for each registered student in that section of 'course'.
"""
def students(self, course=None, bySection=True):
students = self._select('students', [] if course == None else [('course', course)])
return _groupBy(students, 'section') if bySection else students
"""
Returns the course in which the student with the given ID is enrolled.
"""
def courseFor(self, student):
matches = self._select('students', [('id', student)])
return None if len(matches) == 0 else matches[0]['course']
"""
Returns the full stored name of the student.
"""
def studentName(self, student):
matching = self._select('students', [('id', student)])
return None if len(matching) == 0 else matching[0]['name']
"""
Adds a new link with the given attributes.
Returns the primary key identifier for the new link.
"""
def addLink(self, course, link, description):
vals = (course, link, description)
return self._insert('links', external('links'), vals)
"""
Returns a list of all the link dictionaries for the given 'course'.
"""
def links(self, course):
return self._select('links', [('course', course)])
"""
Adds a new assignment with the given attributes.
Returns the primary key identifier for the new assignment.
"""
def addAssignment(self, course, name, description, formal, required,
dropbox, min_weight, max_weight, default_weight):
vals = (course, name, description, int(formal), int(required),
int(dropbox), min_weight, max_weight, default_weight)
return self._insert('assignments', external('assignments'), vals)
"""
Returns the info dict for the assignment with the given 'id'.
"""
def assignmentInfo(self, id):
matching = self._select('assignments', [('id', id)])
return None if len(matching) == 0 else matching[0]
"""
Returns a list of all the assignment dictionaries for the given 'course'.
"""
def assignments(self, course=None):
return self._select('assignments', [] if course == None else [('course', course)])
"""
Returns the full stored name of the assignment.
"""
def assignmentName(self, assignment):
matching = self._select('assignments', [('id', assignment)])
return None if len(matching) == 0 else matching[0]['name']
"""
Returns the row entry in 'table' with primary key 'id', or None if no such entry
exists.
"""
def row(self, table, id):
matching = self._select(table, [('id', id)])
if len(matching) == 0:
return None
return matching[0]
"""
Returns true if 'id' corresponds is the primary key to a row in 'table'.
"""
def rowExists(self, table, id):
return self.row(table, id) != None
"""
Updates the row in 'table' with given 'primary_key' according to each (column,
new value) mapping in 'changes'.
"""
def editRowEntry(self, table, primary_key, changes):
set = []
for column in changes:
set.append((column, changes[column]))
self._update(table, set, [ ('id', primary_key) ])
"""
Deletes the row in 'table' with 'id'.
"""
def deleteRowEntry(self, table, id):
self._delete(table, [('id', id)])
"""
Removes 'student's score for 'assignment'.
"""
def removeScore(self, student, assignment):
self._delete('scores', [('student', student), ('assignment', assignment)])
"""
Removes the weight that 'student' has given to 'assignment'.
"""
def removeWeight(self, student, assignment):
self._delete('weights', [('student', student), ('assignment', assignment)])
"""
Deletes all rows from 'table' that match 'where' clause (list of k=v pairs).
"""
def _delete(self, table, where):
if table == '*':
raise ValueError('Give a specific table.')
if len(where) == 0:
raise ValueError('Narrow the WHERE down a bit, yeah?')
placeholders, values = _escape(where, 'AND ')
fmt = 'DELETE FROM {0} WHERE {1}'.format(table, placeholders)
with self.store:
cursor = self.store.cursor()
cursor.execute(fmt, values)
self.store.commit()
"""
Returns a map of maps, keyed first on student and then on assignment id. An
entry for every student, assignment pair in the 'given' course is included.
Each value is either that student's score on that assignment, or None if no
score is recorded.
"""
def scores(self, course):
return self._fullGradesheet('scores', 'score', course)
"""
Returns a map of maps (see scores() for format), but instead of score values,
includes weight values. Any weight that value that would be None (has no entry
in the weights table) is given the default value for that assignment.
"""
def weights(self, course):
assigned = self._fullGradesheet('weights', 'weight', course)
# If the 'weights' table doesn't have an entry for a given (student,
# assignment) pair, assume the default.
assignments = self._select('assignments', [('course', course)])
defaults = {}
for a in assignments:
defaults[a['id']] = a['default_weight']
for student in assigned:
for assignment in assigned[student]:
if assigned[student][assignment] == None:
assigned[student][assignment] = float(defaults[assignment])
return assigned
"""
Returns a map with an assignment id key for each assignment in the given
'course'. Each value is a dict keyed on ('min', 'max', 'allowZero')
describing basic validation constraints for that assignment. An assignment
is allowed to have a zero weight when it is not required.
"""
def weightConstraints(self, course):
res = {}
for a in self.assignments(course):
res[a['id']] = {
'min': a['min_weight'],
'max': a['max_weight'],
'allowZero': not a['required']
}
return res
"""
Returns a map of maps (see scores() for format), with values from either the
"scores" or "weights" table. 'keyColumn' is either "score" or "weight".
Raises ValueError if some other table is passed.
"""
def _fullGradesheet(self, table, keyColumn, course):
if table != 'scores' and table != 'weights':
raise ValueError('Invalid table.')
if keyColumn != 'score' and keyColumn != 'weight':
raise ValueError('Invalid keyColumn.')
with self.store:
cursor = self.store.cursor()
cursor.execute('SELECT students.id, {0}.assignment, {0}.{1} \
FROM students \
JOIN {0} ON students.id={0}.student \
WHERE students.course=?'.format(table, keyColumn), [course])
res = self._blankGradesheet(course)
rows = cursor.fetchall()
for (student, assignment, grade) in rows:
res[student][assignment] = grade
return res
"""
Returns a map of maps, keyed first on student and then on assignment id. An
entry for every student, assignment pair in the 'given' course is included,
with every value 'None'.
"""
def _blankGradesheet(self, course):
students = self.students(course, bySection=False)
assignments = self.assignments(course)
res = {}
for s in students:
if s['id'] not in res:
res[s['id']] = {}
for a in assignments:
res[s['id']][a['id']] = None
return res
"""
Sets the given student's score on the given assignment to that provided,
overwriting any prior score for that (student, assignment) pair.
"""
def setScore(self, student, assignment, score):
# TODO: Should be implemented with INSERT OR REPLACE INTO for efficiency.
self._setGradeAttribute(student, assignment, 'scores', 'score', score)
"""
Sets the given student's weight for the given assignment to that provided,
overwriting any prior weight for that (student, assignment) pair. If the
assignment is optional, the weight can be either 0 or in the required
weight range for the assignment. If the assignment is required, the weight
must be within the required weight range for the assignment.
"""
def setWeight(self, student, assignment, weight):
# Set the weight to the minimum or maximum allowed for that assignment.
a = self._select('assignments', [('id', assignment)])
if len(a) == 0:
raise ValueError('Unknown assignment.')
else:
a = a[0]
if a['required'] or weight != 0:
weight = max(a['min_weight'], weight)
weight = min(a['max_weight'], weight)
self._setGradeAttribute(student, assignment, 'weights', 'weight', weight)
"""
Returns a list of file entries in the given 'student's dropbox for the given
'assignment'. If two file submissions have the same name, only the most
recent (based on submit timestamp) is included.
"""
def studentDropbox(self, student, assignment):
where = [('student', student), ('assignment', assignment)]
revisions = _groupBy(self._select('dropbox', where), 'original')
return self.fileRevisionHeads_(self._select('dropbox', where))
"""
Returns a map from 'student' id to list of files is that student's dropbox
for the given 'assignment'.
"""
def studentDropboxes(self, assignment):
files = self._select('dropbox', [('assignment', assignment)])
files = sorted(files, key=lambda f : f['timestamp'])
byStudent = _groupBy(files, 'student')
for id in byStudent.keys():
byStudent[id] = self.fileRevisionHeads_(byStudent[id])
return byStudent;
"""
Returns files filtered to exclude a file f if any other file has the same
original name but a later timestamp.
"""
def fileRevisionHeads_(self, files):
revisionGroups = _groupBy(files, 'original').values()
revisionGroups = filter(lambda gp : len(gp) > 0, revisionGroups)
def latestSubmission(files):
return sorted(files, key=lambda f : f['timestamp'])[-1]
return map(latestSubmission, revisionGroups)
"""
Saves the file to the dropbox directory according to a safe file naming scheme,
recording the file's new dropbox path in the store. The file's original name
is recorded unless 'newName' is provided, in which case this filename is used.
Returns the file ID of the new file.
"""
def addDropboxFile(self, student, assignment, file, newName=None):
course = self.assignmentInfo(assignment)['course']
timestamp = int((datetime.now() - datetime(1970, 1, 1)).total_seconds())
timestamp += self.timezone
original_name = newName or secure_filename(file.filename)
# Add database entry to retrieve new ID.
id = self._insert('dropbox', external('dropbox'),
(assignment, student, '', original_name, timestamp))
path = self._dropboxPath(course)
path = join(path, '_'.join(map(str, (id, student, assignment, original_name))))
# Save file contents, ensuring no overwrites.
if exists(path):
raise ValueError('Attempted to overwrite previous file.')
file.save(path)
# Record path in database.
self._update('dropbox', [('path', path)], [('id', id)])
return id
"""
Retrives the stored dropbox file info dict with the given primary key 'id'.
"""
def getDropboxFile(self, id):
res = self._select('dropbox', [('id', id)])
return res[0] if len(res) > 0 else None
"""
Returns the directory for this 'course's file dropbox, creating one if it does
not already exist.
"""
def _dropboxPath(self, course):
path = '{0}/course_{1}'.format(self.dropbox, course)
if not exists(path):
makedirs(path)
return path
"""
Sets the grade attribute (either score or weight) for the student, assignment
pair. 'table' should be either 'scores' or 'weights'. 'key' should be either
'score' or 'weight', respectively.
"""
def _setGradeAttribute(self, student, assignment, table, key, value):
keys = ('student', 'assignment', key)
values = (student, assignment, value)
priorWhere = [('student', student), ('assignment', assignment)]
prior = self._select(table, priorWhere)
if len(prior) == 0:
self._insert(table, keys, values)
else:
self._update(table, [(key, value)], [('id', prior[0]['id'])])
"""
Updates all rows in 'table' matching all filters in 'where' to the values
provided in 'set':
UPDATE <table> SET <set> WHERE <where>
Both 'set' and 'where' are lists of (column name, value) pairs.
"""
def _update(self, table, set, where):
if table not in TABLES:
raise ValueError('Unknown table "{0}".'.format(table))
fmt = 'UPDATE {0} SET {1} WHERE {2}'
sets, setValues = _escape(set)
wheres, whereValues = _escape(where)
with self.store:
cursor = self.store.cursor()
cursor.execute(fmt.format(table, sets, wheres), (setValues + whereValues))
self.store.commit()
"""
Returns the fetched rows resulting from executing an SQL query of the form
SELECT * FROM <table> WHERE <where>
as a list of row dictionaries keyed on column names and valued on entry values.
"""
def _select(self, table, where=[]):
if table not in TABLES:
raise ValueError('Unknown table "{0}".'.format(table))
with self.store:
cursor = self.store.cursor()
fmt = 'SELECT * FROM {0}'.format(table)
if len(where) != 0:
placeholders, values = _escape(where, 'AND ')
fmt += ' WHERE ' + placeholders
cursor.execute(fmt, values)
else:
cursor.execute(fmt)
rows = cursor.fetchall()
keys = map(lambda k : k[0], TABLES[table])
return map(partial(toDict, keys), rows)
"""
Returns the new primary key of a newly inserted row resulting from a query of
the form:
INSERT INTO <table>(<keys>) VALUES(<values>)
"""
def _insert(self, table, keys, values):
if table not in TABLES:
raise ValueError('Unknown table "{0}".'.format(table))
fmt = 'INSERT INTO {0}({1}) VALUES({2})'
fmt_vals = table, ','.join(keys), ','.join(['?'] * len(values))
with self.store:
cursor = self.store.cursor()
cursor.execute(fmt.format(*fmt_vals), values)
self.store.commit()
return cursor.lastrowid
"""
Adds a new table for each described in db_scheme.TABLES. Assumes no such TABLES
have already been created in this store.
"""
def createTables(self):
fmt = 'CREATE TABLE {0}({1})'
with self.store:
cursor = self.store.cursor()
for name in TABLES:
columns = map(lambda c : ' '.join(c), TABLES[name])
cursor.execute(fmt.format(name, ', '.join(columns)))
self.store.commit()
"""
Loads a simple admin (user, password) configuration based on a configuration file
with the two-line format:
<username>
<sha256 hash>
Raises IOError if the file with 'filename' is improperly formatted.
"""
def _loadAdminConf(self, filename):
try:
lines = map(lambda s : s.strip(), list(open(filename)))
self.admin_name, self.admin_pass = lines
except:
raise IOError('Malformed admin configuration file.')
