1
# IVLE - Informatics Virtual Learning Environment
2
# Copyright (C) 2007-2009 The University of Melbourne
4
# This program is free software; you can redistribute it and/or modify
5
# it under the terms of the GNU General Public License as published by
6
# the Free Software Foundation; either version 2 of the License, or
7
# (at your option) any later version.
9
# This program is distributed in the hope that it will be useful,
10
# but WITHOUT ANY WARRANTY; without even the implied warranty of
11
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12
# GNU General Public License for more details.
14
# You should have received a copy of the GNU General Public License
15
# along with this program; if not, write to the Free Software
16
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
18
# Author: Matt Giuca, Will Grant
21
Database Classes and Utilities for Storm ORM
23
This module provides all of the classes which map to database tables.
24
It also provides miscellaneous utility functions for database interaction.
30
from storm.locals import create_database, Store, Int, Unicode, DateTime, \
31
Reference, ReferenceSet, Bool, Storm, Desc
32
from storm.exceptions import NotOneError, IntegrityError
34
from ivle.worksheet.rst import rst
36
__all__ = ['get_store',
38
'Subject', 'Semester', 'Offering', 'Enrolment',
39
'ProjectSet', 'Project', 'ProjectGroup', 'ProjectGroupMembership',
40
'Assessed', 'ProjectSubmission', 'ProjectExtension',
41
'Exercise', 'Worksheet', 'WorksheetExercise',
42
'ExerciseSave', 'ExerciseAttempt',
43
'TestCase', 'TestSuite', 'TestSuiteVar'
46
def _kwarg_init(self, **kwargs):
47
for k,v in kwargs.items():
48
if k.startswith('_') or not hasattr(self.__class__, k):
49
raise TypeError("%s got an unexpected keyword argument '%s'"
50
% (self.__class__.__name__, k))
53
def get_conn_string(config):
54
"""Create a Storm connection string to the IVLE database
56
@param config: The IVLE configuration.
60
if config['database']['username']:
61
clusterstr += config['database']['username']
62
if config['database']['password']:
63
clusterstr += ':' + config['database']['password']
66
host = config['database']['host'] or 'localhost'
67
port = config['database']['port'] or 5432
69
clusterstr += '%s:%d' % (host, port)
71
return "postgres://%s/%s" % (clusterstr, config['database']['name'])
73
def get_store(config):
74
"""Create a Storm store connected to the IVLE database.
76
@param config: The IVLE configuration.
78
return Store(create_database(get_conn_string(config)))
84
Represents an IVLE user.
86
__storm_table__ = "login"
88
id = Int(primary=True, name="loginid")
97
last_login = DateTime()
101
studentid = Unicode()
104
__init__ = _kwarg_init
107
return "<%s '%s'>" % (type(self).__name__, self.login)
109
def authenticate(self, password):
110
"""Validate a given password against this user.
112
Returns True if the given password matches the password hash for this
113
User, False if it doesn't match, and None if there is no hash for the
116
if self.passhash is None:
118
return self.hash_password(password) == self.passhash
121
def display_name(self):
125
def password_expired(self):
126
fieldval = self.pass_exp
127
return fieldval is not None and datetime.datetime.now() > fieldval
130
def account_expired(self):
131
fieldval = self.acct_exp
132
return fieldval is not None and datetime.datetime.now() > fieldval
136
return self.state == 'enabled' and not self.account_expired
138
def _get_enrolments(self, justactive):
139
return Store.of(self).find(Enrolment,
140
Enrolment.user_id == self.id,
141
(Enrolment.active == True) if justactive else True,
142
Enrolment.offering_id == Offering.id,
143
Offering.semester_id == Semester.id,
144
Offering.subject_id == Subject.id).order_by(
146
Desc(Semester.semester),
150
def _set_password(self, password):
154
self.passhash = unicode(User.hash_password(password))
155
password = property(fset=_set_password)
159
return Store.of(self).find(Subject,
160
Enrolment.user_id == self.id,
161
Enrolment.active == True,
162
Offering.id == Enrolment.offering_id,
163
Subject.id == Offering.subject_id).config(distinct=True)
165
# TODO: Invitations should be listed too?
166
def get_groups(self, offering=None):
168
ProjectGroupMembership.user_id == self.id,
169
ProjectGroup.id == ProjectGroupMembership.project_group_id,
173
ProjectSet.offering_id == offering.id,
174
ProjectGroup.project_set_id == ProjectSet.id,
176
return Store.of(self).find(ProjectGroup, *preds)
180
return self.get_groups()
183
def active_enrolments(self):
184
'''A sanely ordered list of the user's active enrolments.'''
185
return self._get_enrolments(True)
188
def enrolments(self):
189
'''A sanely ordered list of all of the user's enrolments.'''
190
return self._get_enrolments(False)
192
def get_projects(self, offering=None, active_only=True):
193
'''Return Projects that the user can submit.
195
This will include projects for offerings in which the user is
196
enrolled, as long as the project is not in a project set which has
197
groups (ie. if maximum number of group members is 0).
199
Unless active_only is False, only projects for active offerings will
202
If an offering is specified, returned projects will be limited to
203
those for that offering.
205
return Store.of(self).find(Project,
206
Project.project_set_id == ProjectSet.id,
207
ProjectSet.max_students_per_group == None,
208
ProjectSet.offering_id == Offering.id,
209
(offering is None) or (Offering.id == offering.id),
210
Semester.id == Offering.semester_id,
211
(not active_only) or (Semester.state == u'current'),
212
Enrolment.offering_id == Offering.id,
213
Enrolment.user_id == self.id)
216
def hash_password(password):
217
return hashlib.md5(password).hexdigest()
220
def get_by_login(cls, store, login):
222
Get the User from the db associated with a given store and
225
return store.find(cls, cls.login == unicode(login)).one()
227
def get_permissions(self, user):
228
if user and user.admin or user is self:
229
return set(['view', 'edit', 'submit_project'])
233
# SUBJECTS AND ENROLMENTS #
235
class Subject(Storm):
236
__storm_table__ = "subject"
238
id = Int(primary=True, name="subjectid")
239
code = Unicode(name="subj_code")
240
name = Unicode(name="subj_name")
241
short_name = Unicode(name="subj_short_name")
244
offerings = ReferenceSet(id, 'Offering.subject_id')
246
__init__ = _kwarg_init
249
return "<%s '%s'>" % (type(self).__name__, self.short_name)
251
def get_permissions(self, user):
259
def active_offerings(self):
260
"""Return a sequence of currently active offerings for this subject
261
(offerings whose semester.state is "current"). There should be 0 or 1
262
elements in this sequence, but it's possible there are more.
264
return self.offerings.find(Offering.semester_id == Semester.id,
265
Semester.state == u'current')
267
def offering_for_semester(self, year, semester):
268
"""Get the offering for the given year/semester, or None."""
269
return self.offerings.find(Offering.semester_id == Semester.id,
270
Semester.year == unicode(year),
271
Semester.semester == unicode(semester)).one()
273
class Semester(Storm):
274
__storm_table__ = "semester"
276
id = Int(primary=True, name="semesterid")
281
offerings = ReferenceSet(id, 'Offering.semester_id')
282
enrolments = ReferenceSet(id,
283
'Offering.semester_id',
285
'Enrolment.offering_id')
287
__init__ = _kwarg_init
290
return "<%s %s/%s>" % (type(self).__name__, self.year, self.semester)
292
class Offering(Storm):
293
__storm_table__ = "offering"
295
id = Int(primary=True, name="offeringid")
296
subject_id = Int(name="subject")
297
subject = Reference(subject_id, Subject.id)
298
semester_id = Int(name="semesterid")
299
semester = Reference(semester_id, Semester.id)
300
groups_student_permissions = Unicode()
302
enrolments = ReferenceSet(id, 'Enrolment.offering_id')
303
members = ReferenceSet(id,
304
'Enrolment.offering_id',
307
project_sets = ReferenceSet(id, 'ProjectSet.offering_id')
309
worksheets = ReferenceSet(id,
310
'Worksheet.offering_id',
314
__init__ = _kwarg_init
317
return "<%s %r in %r>" % (type(self).__name__, self.subject,
320
def enrol(self, user, role=u'student'):
321
'''Enrol a user in this offering.'''
322
enrolment = Store.of(self).find(Enrolment,
323
Enrolment.user_id == user.id,
324
Enrolment.offering_id == self.id).one()
326
if enrolment is None:
327
enrolment = Enrolment(user=user, offering=self)
328
self.enrolments.add(enrolment)
330
enrolment.active = True
331
enrolment.role = role
333
def unenrol(self, user):
334
'''Unenrol a user from this offering.'''
335
enrolment = Store.of(self).find(Enrolment,
336
Enrolment.user_id == user.id,
337
Enrolment.offering_id == self.id).one()
338
Store.of(enrolment).remove(enrolment)
340
def get_permissions(self, user):
343
enrolment = self.get_enrolment(user)
344
if enrolment or user.admin:
346
if (enrolment and enrolment.role in (u'tutor', u'lecturer')) \
351
def get_enrolment(self, user):
353
enrolment = self.enrolments.find(user=user).one()
359
class Enrolment(Storm):
360
__storm_table__ = "enrolment"
361
__storm_primary__ = "user_id", "offering_id"
363
user_id = Int(name="loginid")
364
user = Reference(user_id, User.id)
365
offering_id = Int(name="offeringid")
366
offering = Reference(offering_id, Offering.id)
373
return Store.of(self).find(ProjectGroup,
374
ProjectSet.offering_id == self.offering.id,
375
ProjectGroup.project_set_id == ProjectSet.id,
376
ProjectGroupMembership.project_group_id == ProjectGroup.id,
377
ProjectGroupMembership.user_id == self.user.id)
379
__init__ = _kwarg_init
382
return "<%s %r in %r>" % (type(self).__name__, self.user,
387
class ProjectSet(Storm):
388
__storm_table__ = "project_set"
390
id = Int(name="projectsetid", primary=True)
391
offering_id = Int(name="offeringid")
392
offering = Reference(offering_id, Offering.id)
393
max_students_per_group = Int()
395
projects = ReferenceSet(id, 'Project.project_set_id')
396
project_groups = ReferenceSet(id, 'ProjectGroup.project_set_id')
398
__init__ = _kwarg_init
401
return "<%s %d in %r>" % (type(self).__name__, self.id,
404
class Project(Storm):
405
__storm_table__ = "project"
407
id = Int(name="projectid", primary=True)
409
short_name = Unicode()
412
project_set_id = Int(name="projectsetid")
413
project_set = Reference(project_set_id, ProjectSet.id)
414
deadline = DateTime()
416
assesseds = ReferenceSet(id, 'Assessed.project_id')
417
submissions = ReferenceSet(id,
418
'Assessed.project_id',
420
'ProjectSubmission.assessed_id')
422
__init__ = _kwarg_init
425
return "<%s '%s' in %r>" % (type(self).__name__, self.short_name,
426
self.project_set.offering)
428
def can_submit(self, principal):
429
return (self in principal.get_projects() and
430
self.deadline > datetime.datetime.now())
432
def submit(self, principal, path, revision, who):
433
"""Submit a Subversion path and revision to a project.
435
'principal' is the owner of the Subversion repository, and the
436
entity on behalf of whom the submission is being made. 'path' is
437
a path within that repository, and 'revision' specifies which
438
revision of that path. 'who' is the person making the submission.
441
if not self.can_submit(principal):
442
raise Exception('cannot submit')
444
a = Assessed.get(Store.of(self), principal, self)
445
ps = ProjectSubmission()
447
ps.revision = revision
448
ps.date_submitted = datetime.datetime.now()
455
class ProjectGroup(Storm):
456
__storm_table__ = "project_group"
458
id = Int(name="groupid", primary=True)
459
name = Unicode(name="groupnm")
460
project_set_id = Int(name="projectsetid")
461
project_set = Reference(project_set_id, ProjectSet.id)
463
created_by_id = Int(name="createdby")
464
created_by = Reference(created_by_id, User.id)
467
members = ReferenceSet(id,
468
"ProjectGroupMembership.project_group_id",
469
"ProjectGroupMembership.user_id",
472
__init__ = _kwarg_init
475
return "<%s %s in %r>" % (type(self).__name__, self.name,
476
self.project_set.offering)
479
def display_name(self):
480
return '%s (%s)' % (self.nick, self.name)
482
def get_projects(self, offering=None, active_only=True):
483
'''Return Projects that the group can submit.
485
This will include projects in the project set which owns this group,
486
unless the project set disallows groups (in which case none will be
489
Unless active_only is False, projects will only be returned if the
490
group's offering is active.
492
If an offering is specified, projects will only be returned if it
495
return Store.of(self).find(Project,
496
Project.project_set_id == ProjectSet.id,
497
ProjectSet.id == self.project_set.id,
498
ProjectSet.max_students_per_group != None,
499
ProjectSet.offering_id == Offering.id,
500
(offering is None) or (Offering.id == offering.id),
501
Semester.id == Offering.semester_id,
502
(not active_only) or (Semester.state == u'current'))
505
def get_permissions(self, user):
506
if user.admin or user in self.members:
507
return set(['submit_project'])
511
class ProjectGroupMembership(Storm):
512
__storm_table__ = "group_member"
513
__storm_primary__ = "user_id", "project_group_id"
515
user_id = Int(name="loginid")
516
user = Reference(user_id, User.id)
517
project_group_id = Int(name="groupid")
518
project_group = Reference(project_group_id, ProjectGroup.id)
520
__init__ = _kwarg_init
523
return "<%s %r in %r>" % (type(self).__name__, self.user,
526
class Assessed(Storm):
527
__storm_table__ = "assessed"
529
id = Int(name="assessedid", primary=True)
530
user_id = Int(name="loginid")
531
user = Reference(user_id, User.id)
532
project_group_id = Int(name="groupid")
533
project_group = Reference(project_group_id, ProjectGroup.id)
535
project_id = Int(name="projectid")
536
project = Reference(project_id, Project.id)
538
extensions = ReferenceSet(id, 'ProjectExtension.assessed_id')
539
submissions = ReferenceSet(id, 'ProjectSubmission.assessed_id')
542
return "<%s %r in %r>" % (type(self).__name__,
543
self.user or self.project_group, self.project)
546
def get(cls, store, principal, project):
548
if t not in (User, ProjectGroup):
549
raise AssertionError('principal must be User or ProjectGroup')
552
(t is User) or (cls.project_group_id == principal.id),
553
(t is ProjectGroup) or (cls.user_id == principal.id),
554
Project.id == project.id).one()
561
a.project_group = principal
568
class ProjectExtension(Storm):
569
__storm_table__ = "project_extension"
571
id = Int(name="extensionid", primary=True)
572
assessed_id = Int(name="assessedid")
573
assessed = Reference(assessed_id, Assessed.id)
574
deadline = DateTime()
575
approver_id = Int(name="approver")
576
approver = Reference(approver_id, User.id)
579
class ProjectSubmission(Storm):
580
__storm_table__ = "project_submission"
582
id = Int(name="submissionid", primary=True)
583
assessed_id = Int(name="assessedid")
584
assessed = Reference(assessed_id, Assessed.id)
587
submitter_id = Int(name="submitter")
588
submitter = Reference(submitter_id, User.id)
589
date_submitted = DateTime()
592
# WORKSHEETS AND EXERCISES #
594
class Exercise(Storm):
595
__storm_table__ = "exercise"
596
id = Unicode(primary=True, name="identifier")
598
description = Unicode()
604
worksheet_exercises = ReferenceSet(id,
605
'WorksheetExercise.exercise_id')
607
worksheets = ReferenceSet(id,
608
'WorksheetExercise.exercise_id',
609
'WorksheetExercise.worksheet_id',
613
test_suites = ReferenceSet(id,
614
'TestSuite.exercise_id',
617
__init__ = _kwarg_init
620
return "<%s %s>" % (type(self).__name__, self.name)
622
def get_permissions(self, user):
629
elif 'lecturer' in set((e.role for e in user.active_enrolments)):
635
def get_description(self):
636
return rst(self.description)
639
"""Deletes the exercise, providing it has no associated worksheets."""
640
if (self.worksheet_exercises.count() > 0):
641
raise IntegrityError()
642
for suite in self.test_suites:
644
Store.of(self).remove(self)
646
class Worksheet(Storm):
647
__storm_table__ = "worksheet"
649
id = Int(primary=True, name="worksheetid")
650
offering_id = Int(name="offeringid")
651
identifier = Unicode()
658
attempts = ReferenceSet(id, "ExerciseAttempt.worksheetid")
659
offering = Reference(offering_id, 'Offering.id')
661
all_worksheet_exercises = ReferenceSet(id,
662
'WorksheetExercise.worksheet_id')
664
# Use worksheet_exercises to get access to the *active* WorksheetExercise
665
# objects binding worksheets to exercises. This is required to access the
669
def worksheet_exercises(self):
670
return self.all_worksheet_exercises.find(active=True)
672
__init__ = _kwarg_init
675
return "<%s %s>" % (type(self).__name__, self.name)
677
def remove_all_exercises(self):
679
Remove all exercises from this worksheet.
680
This does not delete the exercises themselves. It just removes them
683
store = Store.of(self)
684
for ws_ex in self.all_worksheet_exercises:
685
if ws_ex.saves.count() > 0 or ws_ex.attempts.count() > 0:
686
raise IntegrityError()
687
store.find(WorksheetExercise,
688
WorksheetExercise.worksheet == self).remove()
690
def get_permissions(self, user):
691
return self.offering.get_permissions(user)
694
"""Returns the xml of this worksheet, converts from rst if required."""
695
if self.format == u'rst':
696
ws_xml = rst(self.data)
702
"""Deletes the worksheet, provided it has no attempts on any exercises.
704
Returns True if delete succeeded, or False if this worksheet has
705
attempts attached."""
706
for ws_ex in self.all_worksheet_exercises:
707
if ws_ex.saves.count() > 0 or ws_ex.attempts.count() > 0:
708
raise IntegrityError()
710
self.remove_all_exercises()
711
Store.of(self).remove(self)
713
class WorksheetExercise(Storm):
714
__storm_table__ = "worksheet_exercise"
716
id = Int(primary=True, name="ws_ex_id")
718
worksheet_id = Int(name="worksheetid")
719
worksheet = Reference(worksheet_id, Worksheet.id)
720
exercise_id = Unicode(name="exerciseid")
721
exercise = Reference(exercise_id, Exercise.id)
726
saves = ReferenceSet(id, "ExerciseSave.ws_ex_id")
727
attempts = ReferenceSet(id, "ExerciseAttempt.ws_ex_id")
729
__init__ = _kwarg_init
732
return "<%s %s in %s>" % (type(self).__name__, self.exercise.name,
733
self.worksheet.identifier)
735
def get_permissions(self, user):
736
return self.worksheet.get_permissions(user)
739
class ExerciseSave(Storm):
741
Represents a potential solution to an exercise that a user has submitted
742
to the server for storage.
743
A basic ExerciseSave is just the current saved text for this exercise for
744
this user (doesn't count towards their attempts).
745
ExerciseSave may be extended with additional semantics (such as
748
__storm_table__ = "exercise_save"
749
__storm_primary__ = "ws_ex_id", "user_id"
751
ws_ex_id = Int(name="ws_ex_id")
752
worksheet_exercise = Reference(ws_ex_id, "WorksheetExercise.id")
754
user_id = Int(name="loginid")
755
user = Reference(user_id, User.id)
759
__init__ = _kwarg_init
762
return "<%s %s by %s at %s>" % (type(self).__name__,
763
self.exercise.name, self.user.login, self.date.strftime("%c"))
765
class ExerciseAttempt(ExerciseSave):
767
An ExerciseAttempt is a special case of an ExerciseSave. Like an
768
ExerciseSave, it constitutes exercise solution data that the user has
769
submitted to the server for storage.
770
In addition, it contains additional information about the submission.
771
complete - True if this submission was successful, rendering this exercise
772
complete for this user.
773
active - True if this submission is "active" (usually true). Submissions
774
may be de-activated by privileged users for special reasons, and then
775
they won't count (either as a penalty or success), but will still be
778
__storm_table__ = "exercise_attempt"
779
__storm_primary__ = "ws_ex_id", "user_id", "date"
781
# The "text" field is the same but has a different name in the DB table
783
text = Unicode(name="attempt")
787
def get_permissions(self, user):
788
return set(['view']) if user is self.user else set()
790
class TestSuite(Storm):
791
"""A Testsuite acts as a container for the test cases of an exercise."""
792
__storm_table__ = "test_suite"
793
__storm_primary__ = "exercise_id", "suiteid"
796
exercise_id = Unicode(name="exerciseid")
797
description = Unicode()
801
exercise = Reference(exercise_id, Exercise.id)
802
test_cases = ReferenceSet(suiteid, 'TestCase.suiteid', order_by="seq_no")
803
variables = ReferenceSet(suiteid, 'TestSuiteVar.suiteid', order_by='arg_no')
806
"""Delete this suite, without asking questions."""
807
for vaariable in self.variables:
809
for test_case in self.test_cases:
811
Store.of(self).remove(self)
813
class TestCase(Storm):
814
"""A TestCase is a member of a TestSuite.
816
It contains the data necessary to check if an exercise is correct"""
817
__storm_table__ = "test_case"
818
__storm_primary__ = "testid", "suiteid"
822
suite = Reference(suiteid, "TestSuite.suiteid")
825
test_default = Unicode()
828
parts = ReferenceSet(testid, "TestCasePart.testid")
830
__init__ = _kwarg_init
833
for part in self.parts:
835
Store.of(self).remove(self)
837
class TestSuiteVar(Storm):
838
"""A container for the arguments of a Test Suite"""
839
__storm_table__ = "suite_variable"
840
__storm_primary__ = "varid"
845
var_value = Unicode()
849
suite = Reference(suiteid, "TestSuite.suiteid")
851
__init__ = _kwarg_init
854
Store.of(self).remove(self)
856
class TestCasePart(Storm):
857
"""A container for the test elements of a Test Case"""
858
__storm_table__ = "test_case_part"
859
__storm_primary__ = "partid"
864
part_type = Unicode()
865
test_type = Unicode()
869
test = Reference(testid, "TestCase.testid")
871
__init__ = _kwarg_init
874
Store.of(self).remove(self)
added
removed
ivle/database.py
