18
18
# Author: Matt Giuca, Will Grant
21
Database Classes and Utilities for Storm ORM
20
"""Database utilities and content classes.
23
22
This module provides all of the classes which map to database tables.
24
23
It also provides miscellaneous utility functions for database interaction.
30
29
from storm.locals import create_database, Store, Int, Unicode, DateTime, \
31
30
Reference, ReferenceSet, Bool, Storm, Desc
31
from storm.exceptions import NotOneError, IntegrityError
33
from ivle.worksheet.rst import rst
36
35
__all__ = ['get_store',
38
37
'Subject', 'Semester', 'Offering', 'Enrolment',
39
38
'ProjectSet', 'Project', 'ProjectGroup', 'ProjectGroupMembership',
39
'Assessed', 'ProjectSubmission', 'ProjectExtension',
40
40
'Exercise', 'Worksheet', 'WorksheetExercise',
41
41
'ExerciseSave', 'ExerciseAttempt',
42
'AlreadyEnrolledError', 'TestCase', 'TestSuite'
42
'TestCase', 'TestSuite', 'TestSuiteVar'
45
45
def _kwarg_init(self, **kwargs):
49
49
% (self.__class__.__name__, k))
50
50
setattr(self, k, v)
52
def get_conn_string():
54
Returns the Storm connection string, generated from the conf file.
56
return "postgres://%s:%s@%s:%d/%s" % (ivle.conf.db_user,
57
ivle.conf.db_password, ivle.conf.db_host, ivle.conf.db_port,
62
Open a database connection and transaction. Return a storm.store.Store
63
instance connected to the configured IVLE database.
65
return Store(create_database(get_conn_string()))
52
def get_conn_string(config):
53
"""Create a Storm connection string to the IVLE database
55
@param config: The IVLE configuration.
59
if config['database']['username']:
60
clusterstr += config['database']['username']
61
if config['database']['password']:
62
clusterstr += ':' + config['database']['password']
65
host = config['database']['host'] or 'localhost'
66
port = config['database']['port'] or 5432
68
clusterstr += '%s:%d' % (host, port)
70
return "postgres://%s/%s" % (clusterstr, config['database']['name'])
72
def get_store(config):
73
"""Create a Storm store connected to the IVLE database.
75
@param config: The IVLE configuration.
77
return Store(create_database(get_conn_string(config)))
71
Represents an IVLE user.
82
"""An IVLE user account."""
73
83
__storm_table__ = "login"
75
85
id = Int(primary=True, name="loginid")
77
87
passhash = Unicode()
82
92
pass_exp = DateTime()
188
190
'''A sanely ordered list of all of the user's enrolments.'''
189
191
return self._get_enrolments(False)
193
def get_projects(self, offering=None, active_only=True):
194
"""Find projects that the user can submit.
196
This will include projects for offerings in which the user is
197
enrolled, as long as the project is not in a project set which has
198
groups (ie. if maximum number of group members is 0).
200
@param active_only: Whether to only search active offerings.
201
@param offering: An optional offering to restrict the search to.
203
return Store.of(self).find(Project,
204
Project.project_set_id == ProjectSet.id,
205
ProjectSet.max_students_per_group == None,
206
ProjectSet.offering_id == Offering.id,
207
(offering is None) or (Offering.id == offering.id),
208
Semester.id == Offering.semester_id,
209
(not active_only) or (Semester.state == u'current'),
210
Enrolment.offering_id == Offering.id,
211
Enrolment.user_id == self.id)
192
214
def hash_password(password):
193
return md5.md5(password).hexdigest()
215
"""Hash a password with MD5."""
216
return hashlib.md5(password).hexdigest()
196
219
def get_by_login(cls, store, login):
198
Get the User from the db associated with a given store and
220
"""Find a user in a store by login name."""
201
221
return store.find(cls, cls.login == unicode(login)).one()
203
223
def get_permissions(self, user):
204
if user and user.rolenm == 'admin' or user is self:
205
return set(['view', 'edit'])
224
"""Determine privileges held by a user over this object.
226
If the user requesting privileges is this user or an admin,
227
they may do everything. Otherwise they may do nothing.
229
if user and user.admin or user is self:
230
return set(['view', 'edit', 'submit_project'])
209
234
# SUBJECTS AND ENROLMENTS #
211
236
class Subject(Storm):
237
"""A subject (or course) which is run in some semesters."""
212
239
__storm_table__ = "subject"
214
241
id = Int(primary=True, name="subjectid")
225
252
return "<%s '%s'>" % (type(self).__name__, self.short_name)
227
254
def get_permissions(self, user):
255
"""Determine privileges held by a user over this object.
257
If the user requesting privileges is an admin, they may edit.
258
Otherwise they may only read.
229
261
if user is not None:
230
262
perms.add('view')
231
if user.rolenm == 'admin':
267
def active_offerings(self):
268
"""Find active offerings for this subject.
270
Return a sequence of currently active offerings for this subject
271
(offerings whose semester.state is "current"). There should be 0 or 1
272
elements in this sequence, but it's possible there are more.
274
return self.offerings.find(Offering.semester_id == Semester.id,
275
Semester.state == u'current')
277
def offering_for_semester(self, year, semester):
278
"""Get the offering for the given year/semester, or None.
280
@param year: A string representation of the year.
281
@param semester: A string representation of the semester.
283
return self.offerings.find(Offering.semester_id == Semester.id,
284
Semester.year == unicode(year),
285
Semester.semester == unicode(semester)).one()
235
287
class Semester(Storm):
288
"""A semester in which subjects can be run."""
236
290
__storm_table__ = "semester"
238
292
id = Int(primary=True, name="semesterid")
240
294
semester = Unicode()
243
297
offerings = ReferenceSet(id, 'Offering.semester_id')
298
enrolments = ReferenceSet(id,
299
'Offering.semester_id',
301
'Enrolment.offering_id')
245
303
__init__ = _kwarg_init
272
335
return "<%s %r in %r>" % (type(self).__name__, self.subject,
275
def enrol(self, user):
276
'''Enrol a user in this offering.'''
277
# We'll get a horrible database constraint violation error if we try
278
# to add a second enrolment.
279
if Store.of(self).find(Enrolment,
280
Enrolment.user_id == user.id,
281
Enrolment.offering_id == self.id).count() == 1:
282
raise AlreadyEnrolledError()
284
e = Enrolment(user=user, offering=self, active=True)
285
self.enrolments.add(e)
338
def enrol(self, user, role=u'student'):
339
"""Enrol a user in this offering.
341
Enrolments handle both the staff and student cases. The role controls
342
the privileges granted by this enrolment.
344
enrolment = Store.of(self).find(Enrolment,
345
Enrolment.user_id == user.id,
346
Enrolment.offering_id == self.id).one()
348
if enrolment is None:
349
enrolment = Enrolment(user=user, offering=self)
350
self.enrolments.add(enrolment)
352
enrolment.active = True
353
enrolment.role = role
355
def unenrol(self, user):
356
'''Unenrol a user from this offering.'''
357
enrolment = Store.of(self).find(Enrolment,
358
Enrolment.user_id == user.id,
359
Enrolment.offering_id == self.id).one()
360
Store.of(enrolment).remove(enrolment)
362
def get_permissions(self, user):
365
enrolment = self.get_enrolment(user)
366
if enrolment or user.admin:
368
if (enrolment and enrolment.role in (u'tutor', u'lecturer')) \
373
def get_enrolment(self, user):
374
"""Find the user's enrolment in this offering."""
376
enrolment = self.enrolments.find(user=user).one()
287
382
class Enrolment(Storm):
383
"""An enrolment of a user in an offering.
385
This represents the roles of both staff and students.
288
388
__storm_table__ = "enrolment"
289
389
__storm_primary__ = "user_id", "offering_id"
331
435
return "<%s %d in %r>" % (type(self).__name__, self.id,
438
def get_permissions(self, user):
439
return self.offering.get_permissions(user)
334
441
class Project(Storm):
442
"""A student project for which submissions can be made."""
335
444
__storm_table__ = "project"
337
446
id = Int(name="projectid", primary=True)
448
short_name = Unicode()
338
449
synopsis = Unicode()
340
451
project_set_id = Int(name="projectsetid")
341
452
project_set = Reference(project_set_id, ProjectSet.id)
342
453
deadline = DateTime()
455
assesseds = ReferenceSet(id, 'Assessed.project_id')
456
submissions = ReferenceSet(id,
457
'Assessed.project_id',
459
'ProjectSubmission.assessed_id')
344
461
__init__ = _kwarg_init
346
463
def __repr__(self):
347
return "<%s '%s' in %r>" % (type(self).__name__, self.synopsis,
464
return "<%s '%s' in %r>" % (type(self).__name__, self.short_name,
348
465
self.project_set.offering)
467
def can_submit(self, principal):
468
return (self in principal.get_projects() and
469
self.deadline > datetime.datetime.now())
471
def submit(self, principal, path, revision, who):
472
"""Submit a Subversion path and revision to a project.
474
@param principal: The owner of the Subversion repository, and the
475
entity on behalf of whom the submission is being made
476
@param path: A path within that repository to submit.
477
@param revision: The revision of that path to submit.
478
@param who: The user who is actually making the submission.
481
if not self.can_submit(principal):
482
raise Exception('cannot submit')
484
a = Assessed.get(Store.of(self), principal, self)
485
ps = ProjectSubmission()
487
ps.revision = revision
488
ps.date_submitted = datetime.datetime.now()
494
def get_permissions(self, user):
495
return self.project_set.offering.get_permissions(user)
350
498
class ProjectGroup(Storm):
499
"""A group of students working together on a project."""
351
501
__storm_table__ = "project_group"
353
503
id = Int(name="groupid", primary=True)
370
520
return "<%s %s in %r>" % (type(self).__name__, self.name,
371
521
self.project_set.offering)
524
def display_name(self):
525
return '%s (%s)' % (self.nick, self.name)
527
def get_projects(self, offering=None, active_only=True):
528
'''Find projects that the group can submit.
530
This will include projects in the project set which owns this group,
531
unless the project set disallows groups (in which case none will be
534
@param active_only: Whether to only search active offerings.
535
@param offering: An optional offering to restrict the search to.
537
return Store.of(self).find(Project,
538
Project.project_set_id == ProjectSet.id,
539
ProjectSet.id == self.project_set.id,
540
ProjectSet.max_students_per_group != None,
541
ProjectSet.offering_id == Offering.id,
542
(offering is None) or (Offering.id == offering.id),
543
Semester.id == Offering.semester_id,
544
(not active_only) or (Semester.state == u'current'))
547
def get_permissions(self, user):
548
if user.admin or user in self.members:
549
return set(['submit_project'])
373
553
class ProjectGroupMembership(Storm):
554
"""A student's membership in a project group."""
374
556
__storm_table__ = "group_member"
375
557
__storm_primary__ = "user_id", "project_group_id"
385
567
return "<%s %r in %r>" % (type(self).__name__, self.user,
386
568
self.project_group)
570
class Assessed(Storm):
571
"""A composite of a user or group combined with a project.
573
Each project submission and extension refers to an Assessed. It is the
574
sole specifier of the repository and project.
577
__storm_table__ = "assessed"
579
id = Int(name="assessedid", primary=True)
580
user_id = Int(name="loginid")
581
user = Reference(user_id, User.id)
582
project_group_id = Int(name="groupid")
583
project_group = Reference(project_group_id, ProjectGroup.id)
585
project_id = Int(name="projectid")
586
project = Reference(project_id, Project.id)
588
extensions = ReferenceSet(id, 'ProjectExtension.assessed_id')
589
submissions = ReferenceSet(id, 'ProjectSubmission.assessed_id')
592
return "<%s %r in %r>" % (type(self).__name__,
593
self.user or self.project_group, self.project)
596
def get(cls, store, principal, project):
597
"""Find or create an Assessed for the given user or group and project.
599
@param principal: The user or group.
600
@param project: The project.
603
if t not in (User, ProjectGroup):
604
raise AssertionError('principal must be User or ProjectGroup')
607
(t is User) or (cls.project_group_id == principal.id),
608
(t is ProjectGroup) or (cls.user_id == principal.id),
609
Project.id == project.id).one()
616
a.project_group = principal
623
class ProjectExtension(Storm):
624
"""An extension granted to a user or group on a particular project.
626
The user or group and project are specified by the Assessed.
629
__storm_table__ = "project_extension"
631
id = Int(name="extensionid", primary=True)
632
assessed_id = Int(name="assessedid")
633
assessed = Reference(assessed_id, Assessed.id)
634
deadline = DateTime()
635
approver_id = Int(name="approver")
636
approver = Reference(approver_id, User.id)
639
class ProjectSubmission(Storm):
640
"""A submission from a user or group repository to a particular project.
642
The content of a submission is a single path and revision inside a
643
repository. The repository is that owned by the submission's user and
644
group, while the path and revision are explicit.
646
The user or group and project are specified by the Assessed.
649
__storm_table__ = "project_submission"
651
id = Int(name="submissionid", primary=True)
652
assessed_id = Int(name="assessedid")
653
assessed = Reference(assessed_id, Assessed.id)
656
submitter_id = Int(name="submitter")
657
submitter = Reference(submitter_id, User.id)
658
date_submitted = DateTime()
388
661
# WORKSHEETS AND EXERCISES #
390
663
class Exercise(Storm):
391
# Note: Table "problem" is called "Exercise" in the Object layer, since
392
# it's called that everywhere else.
393
__storm_table__ = "problem"
394
#TODO: Add in a field for the user-friendly identifier
664
"""An exercise for students to complete in a worksheet.
666
An exercise may be present in any number of worksheets.
669
__storm_table__ = "exercise"
395
670
id = Unicode(primary=True, name="identifier")
397
672
description = Unicode()
400
675
include = Unicode()
678
worksheet_exercises = ReferenceSet(id,
679
'WorksheetExercise.exercise_id')
403
681
worksheets = ReferenceSet(id,
404
682
'WorksheetExercise.exercise_id',
405
683
'WorksheetExercise.worksheet_id',
409
test_suites = ReferenceSet(id, 'TestSuite.exercise_id')
687
test_suites = ReferenceSet(id,
688
'TestSuite.exercise_id',
411
691
__init__ = _kwarg_init
413
693
def __repr__(self):
414
694
return "<%s %s>" % (type(self).__name__, self.name)
417
def get_by_name(cls, store, name):
419
Get the Exercise from the db associated with a given store and name.
420
If the exercise is not in the database, creates it and inserts it
423
ex = store.find(cls, cls.name == unicode(name)).one()
426
ex = Exercise(name=unicode(name))
696
def get_permissions(self, user):
703
elif u'lecturer' in set((e.role for e in user.active_enrolments)):
706
elif u'tutor' in set((e.role for e in user.active_enrolments)):
712
def get_description(self):
713
"""Return the description interpreted as reStructuredText."""
714
return rst(self.description)
717
"""Deletes the exercise, providing it has no associated worksheets."""
718
if (self.worksheet_exercises.count() > 0):
719
raise IntegrityError()
720
for suite in self.test_suites:
722
Store.of(self).remove(self)
431
724
class Worksheet(Storm):
725
"""A worksheet with exercises for students to complete.
727
Worksheets are owned by offerings.
432
730
__storm_table__ = "worksheet"
434
732
id = Int(primary=True, name="worksheetid")
435
# XXX subject is not linked to a Subject object. This is a property of
436
# the database, and will be refactored.
438
733
offering_id = Int(name="offeringid")
439
name = Unicode(name="identifier")
734
identifier = Unicode()
440
736
assessable = Bool()
741
attempts = ReferenceSet(id, "ExerciseAttempt.worksheetid")
443
742
offering = Reference(offering_id, 'Offering.id')
445
exercises = ReferenceSet(id,
446
'WorksheetExercise.worksheet_id',
447
'WorksheetExercise.exercise_id',
449
# Use worksheet_exercises to get access to the WorksheetExercise objects
450
# binding worksheets to exercises. This is required to access the
744
all_worksheet_exercises = ReferenceSet(id,
745
'WorksheetExercise.worksheet_id')
747
# Use worksheet_exercises to get access to the *active* WorksheetExercise
748
# objects binding worksheets to exercises. This is required to access the
451
749
# "optional" field.
452
worksheet_exercises = ReferenceSet(id,
453
'WorksheetExercise.worksheet_id')
752
def worksheet_exercises(self):
753
return self.all_worksheet_exercises.find(active=True)
456
755
__init__ = _kwarg_init
458
757
def __repr__(self):
459
758
return "<%s %s>" % (type(self).__name__, self.name)
461
# XXX Refactor this - make it an instance method of Subject rather than a
462
# class method of Worksheet. Can't do that now because Subject isn't
463
# linked referentially to the Worksheet.
465
def get_by_name(cls, store, subjectname, worksheetname):
467
Get the Worksheet from the db associated with a given store, subject
468
name and worksheet name.
470
return store.find(cls, cls.subject == unicode(subjectname),
471
cls.name == unicode(worksheetname)).one()
760
def remove_all_exercises(self):
761
"""Remove all exercises from this worksheet.
473
def remove_all_exercises(self, store):
475
Remove all exercises from this worksheet.
476
763
This does not delete the exercises themselves. It just removes them
477
764
from the worksheet.
766
store = Store.of(self)
767
for ws_ex in self.all_worksheet_exercises:
768
if ws_ex.saves.count() > 0 or ws_ex.attempts.count() > 0:
769
raise IntegrityError()
479
770
store.find(WorksheetExercise,
480
771
WorksheetExercise.worksheet == self).remove()
773
def get_permissions(self, user):
774
return self.offering.get_permissions(user)
777
"""Returns the xml of this worksheet, converts from rst if required."""
778
if self.format == u'rst':
779
ws_xml = rst(self.data)
785
"""Deletes the worksheet, provided it has no attempts on any exercises.
787
Returns True if delete succeeded, or False if this worksheet has
788
attempts attached."""
789
for ws_ex in self.all_worksheet_exercises:
790
if ws_ex.saves.count() > 0 or ws_ex.attempts.count() > 0:
791
raise IntegrityError()
793
self.remove_all_exercises()
794
Store.of(self).remove(self)
482
796
class WorksheetExercise(Storm):
483
__storm_table__ = "worksheet_problem"
484
__storm_primary__ = "worksheet_id", "exercise_id"
797
"""A link between a worksheet and one of its exercises.
799
These may be marked optional, in which case the exercise does not count
800
for marking purposes. The sequence number is used to order the worksheet
804
__storm_table__ = "worksheet_exercise"
806
id = Int(primary=True, name="ws_ex_id")
486
808
worksheet_id = Int(name="worksheetid")
487
809
worksheet = Reference(worksheet_id, Worksheet.id)
488
exercise_id = Unicode(name="problemid")
810
exercise_id = Unicode(name="exerciseid")
489
811
exercise = Reference(exercise_id, Exercise.id)
490
812
optional = Bool()
816
saves = ReferenceSet(id, "ExerciseSave.ws_ex_id")
817
attempts = ReferenceSet(id, "ExerciseAttempt.ws_ex_id")
492
819
__init__ = _kwarg_init
494
821
def __repr__(self):
495
822
return "<%s %s in %s>" % (type(self).__name__, self.exercise.name,
823
self.worksheet.identifier)
825
def get_permissions(self, user):
826
return self.worksheet.get_permissions(user)
498
829
class ExerciseSave(Storm):
500
Represents a potential solution to an exercise that a user has submitted
501
to the server for storage.
502
A basic ExerciseSave is just the current saved text for this exercise for
503
this user (doesn't count towards their attempts).
504
ExerciseSave may be extended with additional semantics (such as
507
__storm_table__ = "problem_save"
508
__storm_primary__ = "exercise_id", "user_id", "date"
510
exercise_id = Unicode(name="problemid")
511
exercise = Reference(exercise_id, Exercise.id)
830
"""A potential exercise solution submitted by a user for storage.
832
This is not an actual tested attempt at an exercise, it's just a save of
836
__storm_table__ = "exercise_save"
837
__storm_primary__ = "ws_ex_id", "user_id"
839
ws_ex_id = Int(name="ws_ex_id")
840
worksheet_exercise = Reference(ws_ex_id, "WorksheetExercise.id")
512
842
user_id = Int(name="loginid")
513
843
user = Reference(user_id, User.id)
514
844
date = DateTime()
517
worksheet = Reference(worksheetid, Worksheet.id)
519
847
__init__ = _kwarg_init
523
851
self.exercise.name, self.user.login, self.date.strftime("%c"))
525
853
class ExerciseAttempt(ExerciseSave):
527
An ExerciseAttempt is a special case of an ExerciseSave. Like an
528
ExerciseSave, it constitutes exercise solution data that the user has
529
submitted to the server for storage.
530
In addition, it contains additional information about the submission.
531
complete - True if this submission was successful, rendering this exercise
532
complete for this user.
533
active - True if this submission is "active" (usually true). Submissions
534
may be de-activated by privileged users for special reasons, and then
535
they won't count (either as a penalty or success), but will still be
538
__storm_table__ = "problem_attempt"
539
__storm_primary__ = "exercise_id", "user_id", "date"
854
"""An attempt at solving an exercise.
856
This is a special case of ExerciseSave, used when the user submits a
857
candidate solution. Like an ExerciseSave, it constitutes exercise solution
860
In addition, it contains information about the result of the submission:
862
- complete - True if this submission was successful, rendering this
863
exercise complete for this user in this worksheet.
864
- active - True if this submission is "active" (usually true).
865
Submissions may be de-activated by privileged users for
866
special reasons, and then they won't count (either as a
867
penalty or success), but will still be stored.
870
__storm_table__ = "exercise_attempt"
871
__storm_primary__ = "ws_ex_id", "user_id", "date"
541
873
# The "text" field is the same but has a different name in the DB table
542
874
# for some reason.
543
875
text = Unicode(name="attempt")
544
876
complete = Bool()
547
879
def get_permissions(self, user):
548
880
return set(['view']) if user is self.user else set()
550
882
class TestSuite(Storm):
551
"""A Testsuite acts as a container for the test cases of an exercise."""
883
"""A container to group an exercise's test cases.
885
The test suite contains some information on how to test. The function to
886
test, variables to set and stdin data are stored here.
552
889
__storm_table__ = "test_suite"
553
890
__storm_primary__ = "exercise_id", "suiteid"
556
exercise_id = Unicode(name="problemid")
557
exercise = Reference(exercise_id, Exercise.id)
558
test_cases = ReferenceSet(suiteid, 'TestCase.suiteid')
893
exercise_id = Unicode(name="exerciseid")
559
894
description = Unicode()
898
exercise = Reference(exercise_id, Exercise.id)
899
test_cases = ReferenceSet(suiteid, 'TestCase.suiteid', order_by="seq_no")
900
variables = ReferenceSet(suiteid, 'TestSuiteVar.suiteid', order_by='arg_no')
903
"""Delete this suite, without asking questions."""
904
for vaariable in self.variables:
906
for test_case in self.test_cases:
908
Store.of(self).remove(self)
562
910
class TestCase(Storm):
563
"""A TestCase is a member of a TestSuite.
565
It contains the data necessary to check if an exercise is correct"""
911
"""A container for actual tests (see TestCasePart), inside a test suite.
913
It is the lowest level shown to students on their pass/fail status."""
566
915
__storm_table__ = "test_case"
567
916
__storm_primary__ = "testid", "suiteid"
571
suite = Reference(suiteid, TestSuite.suiteid)
920
suite = Reference(suiteid, "TestSuite.suiteid")
572
921
passmsg = Unicode()
573
922
failmsg = Unicode()
575
code_type = Unicode()
923
test_default = Unicode()
580
__init__ = _kwarg_init
926
parts = ReferenceSet(testid, "TestCasePart.testid")
928
__init__ = _kwarg_init
931
for part in self.parts:
933
Store.of(self).remove(self)
935
class TestSuiteVar(Storm):
936
"""A variable used by an exercise test suite.
938
This may represent a function argument or a normal variable.
941
__storm_table__ = "suite_variable"
942
__storm_primary__ = "varid"
947
var_value = Unicode()
951
suite = Reference(suiteid, "TestSuite.suiteid")
953
__init__ = _kwarg_init
956
Store.of(self).remove(self)
958
class TestCasePart(Storm):
959
"""An actual piece of code to test an exercise solution."""
961
__storm_table__ = "test_case_part"
962
__storm_primary__ = "partid"
967
part_type = Unicode()
968
test_type = Unicode()
972
test = Reference(testid, "TestCase.testid")
974
__init__ = _kwarg_init
977
Store.of(self).remove(self)