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
35
from ivle.worksheet.rst import rst
37
__all__ = ['get_store',
39
'Subject', 'Semester', 'Offering', 'Enrolment',
40
'ProjectSet', 'Project', 'ProjectGroup', 'ProjectGroupMembership',
41
'Assessed', 'ProjectSubmission', 'ProjectExtension',
42
'Exercise', 'Worksheet', 'WorksheetExercise',
43
'ExerciseSave', 'ExerciseAttempt',
44
'TestCase', 'TestSuite', 'TestSuiteVar'
47
def _kwarg_init(self, **kwargs):
48
for k,v in kwargs.items():
49
if k.startswith('_') or not hasattr(self.__class__, k):
50
raise TypeError("%s got an unexpected keyword argument '%s'"
51
% (self.__class__.__name__, k))
54
def get_conn_string():
56
Returns the Storm connection string, generated from the conf file.
61
clusterstr += ivle.conf.db_user
62
if ivle.conf.db_password:
63
clusterstr += ':' + ivle.conf.db_password
66
host = ivle.conf.db_host or 'localhost'
67
port = ivle.conf.db_port or 5432
69
clusterstr += '%s:%d' % (host, port)
71
return "postgres://%s/%s" % (clusterstr, ivle.conf.db_dbname)
75
Open a database connection and transaction. Return a storm.store.Store
76
instance connected to the configured IVLE database.
78
return Store(create_database(get_conn_string()))
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 == 0,
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 md5.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
class Semester(Storm):
260
__storm_table__ = "semester"
262
id = Int(primary=True, name="semesterid")
267
offerings = ReferenceSet(id, 'Offering.semester_id')
268
enrolments = ReferenceSet(id,
269
'Offering.semester_id',
271
'Enrolment.offering_id')
273
__init__ = _kwarg_init
276
return "<%s %s/%s>" % (type(self).__name__, self.year, self.semester)
278
class Offering(Storm):
279
__storm_table__ = "offering"
281
id = Int(primary=True, name="offeringid")
282
subject_id = Int(name="subject")
283
subject = Reference(subject_id, Subject.id)
284
semester_id = Int(name="semesterid")
285
semester = Reference(semester_id, Semester.id)
286
groups_student_permissions = Unicode()
288
enrolments = ReferenceSet(id, 'Enrolment.offering_id')
289
members = ReferenceSet(id,
290
'Enrolment.offering_id',
293
project_sets = ReferenceSet(id, 'ProjectSet.offering_id')
295
worksheets = ReferenceSet(id,
296
'Worksheet.offering_id',
300
__init__ = _kwarg_init
303
return "<%s %r in %r>" % (type(self).__name__, self.subject,
306
def enrol(self, user, role=u'student'):
307
'''Enrol a user in this offering.'''
308
enrolment = Store.of(self).find(Enrolment,
309
Enrolment.user_id == user.id,
310
Enrolment.offering_id == self.id).one()
312
if enrolment is None:
313
enrolment = Enrolment(user=user, offering=self)
314
self.enrolments.add(enrolment)
316
enrolment.active = True
317
enrolment.role = role
319
def unenrol(self, user):
320
'''Unenrol a user from this offering.'''
321
enrolment = Store.of(self).find(Enrolment,
322
Enrolment.user_id == user.id,
323
Enrolment.offering_id == self.id).one()
324
Store.of(enrolment).remove(enrolment)
326
def get_permissions(self, user):
329
enrolment = self.get_enrolment(user)
330
if enrolment or user.admin:
332
if (enrolment and enrolment.role in (u'tutor', u'lecturer')) \
337
def get_enrolment(self, user):
339
enrolment = self.enrolments.find(user=user).one()
345
class Enrolment(Storm):
346
__storm_table__ = "enrolment"
347
__storm_primary__ = "user_id", "offering_id"
349
user_id = Int(name="loginid")
350
user = Reference(user_id, User.id)
351
offering_id = Int(name="offeringid")
352
offering = Reference(offering_id, Offering.id)
359
return Store.of(self).find(ProjectGroup,
360
ProjectSet.offering_id == self.offering.id,
361
ProjectGroup.project_set_id == ProjectSet.id,
362
ProjectGroupMembership.project_group_id == ProjectGroup.id,
363
ProjectGroupMembership.user_id == self.user.id)
365
__init__ = _kwarg_init
368
return "<%s %r in %r>" % (type(self).__name__, self.user,
373
class ProjectSet(Storm):
374
__storm_table__ = "project_set"
376
id = Int(name="projectsetid", primary=True)
377
offering_id = Int(name="offeringid")
378
offering = Reference(offering_id, Offering.id)
379
max_students_per_group = Int()
381
projects = ReferenceSet(id, 'Project.project_set_id')
382
project_groups = ReferenceSet(id, 'ProjectGroup.project_set_id')
384
__init__ = _kwarg_init
387
return "<%s %d in %r>" % (type(self).__name__, self.id,
390
def get_permissions(self, user):
391
return self.offering.get_permissions(user)
393
class Project(Storm):
394
__storm_table__ = "project"
396
id = Int(name="projectid", primary=True)
398
short_name = Unicode()
401
project_set_id = Int(name="projectsetid")
402
project_set = Reference(project_set_id, ProjectSet.id)
403
deadline = DateTime()
405
assesseds = ReferenceSet(id, 'Assessed.project_id')
406
submissions = ReferenceSet(id,
407
'Assessed.project_id',
409
'ProjectSubmission.assessed_id')
411
__init__ = _kwarg_init
414
return "<%s '%s' in %r>" % (type(self).__name__, self.short_name,
415
self.project_set.offering)
417
def can_submit(self, principal):
418
return (self in principal.get_projects() and
419
self.deadline > datetime.datetime.now())
421
def submit(self, principal, path, revision, who):
422
"""Submit a Subversion path and revision to a project.
424
'principal' is the owner of the Subversion repository, and the
425
entity on behalf of whom the submission is being made. 'path' is
426
a path within that repository, and 'revision' specifies which
427
revision of that path. 'who' is the person making the submission.
430
if not self.can_submit(principal):
431
raise Exception('cannot submit')
433
a = Assessed.get(Store.of(self), principal, self)
434
ps = ProjectSubmission()
436
ps.revision = revision
437
ps.date_submitted = datetime.datetime.now()
443
def get_permissions(self, user):
444
return self.project_set.offering.get_permissions(user)
447
class ProjectGroup(Storm):
448
__storm_table__ = "project_group"
450
id = Int(name="groupid", primary=True)
451
name = Unicode(name="groupnm")
452
project_set_id = Int(name="projectsetid")
453
project_set = Reference(project_set_id, ProjectSet.id)
455
created_by_id = Int(name="createdby")
456
created_by = Reference(created_by_id, User.id)
459
members = ReferenceSet(id,
460
"ProjectGroupMembership.project_group_id",
461
"ProjectGroupMembership.user_id",
464
__init__ = _kwarg_init
467
return "<%s %s in %r>" % (type(self).__name__, self.name,
468
self.project_set.offering)
471
def display_name(self):
472
return '%s (%s)' % (self.nick, self.name)
474
def get_projects(self, offering=None, active_only=True):
475
'''Return Projects that the group can submit.
477
This will include projects in the project set which owns this group,
478
unless the project set disallows groups (in which case none will be
481
Unless active_only is False, projects will only be returned if the
482
group's offering is active.
484
If an offering is specified, projects will only be returned if it
487
return Store.of(self).find(Project,
488
Project.project_set_id == ProjectSet.id,
489
ProjectSet.id == self.project_set.id,
490
ProjectSet.max_students_per_group > 0,
491
ProjectSet.offering_id == Offering.id,
492
(offering is None) or (Offering.id == offering.id),
493
Semester.id == Offering.semester_id,
494
(not active_only) or (Semester.state == u'current'))
497
def get_permissions(self, user):
498
if user.admin or user in self.members:
499
return set(['submit_project'])
503
class ProjectGroupMembership(Storm):
504
__storm_table__ = "group_member"
505
__storm_primary__ = "user_id", "project_group_id"
507
user_id = Int(name="loginid")
508
user = Reference(user_id, User.id)
509
project_group_id = Int(name="groupid")
510
project_group = Reference(project_group_id, ProjectGroup.id)
512
__init__ = _kwarg_init
515
return "<%s %r in %r>" % (type(self).__name__, self.user,
518
class Assessed(Storm):
519
__storm_table__ = "assessed"
521
id = Int(name="assessedid", primary=True)
522
user_id = Int(name="loginid")
523
user = Reference(user_id, User.id)
524
project_group_id = Int(name="groupid")
525
project_group = Reference(project_group_id, ProjectGroup.id)
527
project_id = Int(name="projectid")
528
project = Reference(project_id, Project.id)
530
extensions = ReferenceSet(id, 'ProjectExtension.assessed_id')
531
submissions = ReferenceSet(id, 'ProjectSubmission.assessed_id')
534
return "<%s %r in %r>" % (type(self).__name__,
535
self.user or self.project_group, self.project)
538
def get(cls, store, principal, project):
540
if t not in (User, ProjectGroup):
541
raise AssertionError('principal must be User or ProjectGroup')
544
(t is User) or (cls.project_group_id == principal.id),
545
(t is ProjectGroup) or (cls.user_id == principal.id),
546
Project.id == project.id).one()
553
a.project_group = principal
560
class ProjectExtension(Storm):
561
__storm_table__ = "project_extension"
563
id = Int(name="extensionid", primary=True)
564
assessed_id = Int(name="assessedid")
565
assessed = Reference(assessed_id, Assessed.id)
566
deadline = DateTime()
567
approver_id = Int(name="approver")
568
approver = Reference(approver_id, User.id)
571
class ProjectSubmission(Storm):
572
__storm_table__ = "project_submission"
574
id = Int(name="submissionid", primary=True)
575
assessed_id = Int(name="assessedid")
576
assessed = Reference(assessed_id, Assessed.id)
579
submitter_id = Int(name="submitter")
580
submitter = Reference(submitter_id, User.id)
581
date_submitted = DateTime()
584
# WORKSHEETS AND EXERCISES #
586
class Exercise(Storm):
587
__storm_table__ = "exercise"
588
id = Unicode(primary=True, name="identifier")
590
description = Unicode()
596
worksheet_exercises = ReferenceSet(id,
597
'WorksheetExercise.exercise_id')
599
worksheets = ReferenceSet(id,
600
'WorksheetExercise.exercise_id',
601
'WorksheetExercise.worksheet_id',
605
test_suites = ReferenceSet(id,
606
'TestSuite.exercise_id',
609
__init__ = _kwarg_init
612
return "<%s %s>" % (type(self).__name__, self.name)
614
def get_permissions(self, user):
621
elif u'lecturer' in set((e.role for e in user.active_enrolments)):
624
elif u'tutor' in set((e.role for e in user.active_enrolments)):
630
def get_description(self):
631
return rst(self.description)
634
"""Deletes the exercise, providing it has no associated worksheets."""
635
if (self.worksheet_exercises.count() > 0):
636
raise IntegrityError()
637
for suite in self.test_suites:
639
Store.of(self).remove(self)
641
class Worksheet(Storm):
642
__storm_table__ = "worksheet"
644
id = Int(primary=True, name="worksheetid")
645
offering_id = Int(name="offeringid")
646
identifier = Unicode()
653
attempts = ReferenceSet(id, "ExerciseAttempt.worksheetid")
654
offering = Reference(offering_id, 'Offering.id')
656
all_worksheet_exercises = ReferenceSet(id,
657
'WorksheetExercise.worksheet_id')
659
# Use worksheet_exercises to get access to the *active* WorksheetExercise
660
# objects binding worksheets to exercises. This is required to access the
664
def worksheet_exercises(self):
665
return self.all_worksheet_exercises.find(active=True)
667
__init__ = _kwarg_init
670
return "<%s %s>" % (type(self).__name__, self.name)
672
# XXX Refactor this - make it an instance method of Subject rather than a
673
# class method of Worksheet. Can't do that now because Subject isn't
674
# linked referentially to the Worksheet.
676
def get_by_name(cls, store, subjectname, worksheetname):
678
Get the Worksheet from the db associated with a given store, subject
679
name and worksheet name.
681
return store.find(cls, cls.subject == unicode(subjectname),
682
cls.name == unicode(worksheetname)).one()
684
def remove_all_exercises(self):
686
Remove all exercises from this worksheet.
687
This does not delete the exercises themselves. It just removes them
690
store = Store.of(self)
691
for ws_ex in self.all_worksheet_exercises:
692
if ws_ex.saves.count() > 0 or ws_ex.attempts.count() > 0:
693
raise IntegrityError()
694
store.find(WorksheetExercise,
695
WorksheetExercise.worksheet == self).remove()
697
def get_permissions(self, user):
698
return self.offering.get_permissions(user)
701
"""Returns the xml of this worksheet, converts from rst if required."""
702
if self.format == u'rst':
703
ws_xml = rst(self.data)
709
"""Deletes the worksheet, provided it has no attempts on any exercises.
711
Returns True if delete succeeded, or False if this worksheet has
712
attempts attached."""
713
for ws_ex in self.all_worksheet_exercises:
714
if ws_ex.saves.count() > 0 or ws_ex.attempts.count() > 0:
715
raise IntegrityError()
717
self.remove_all_exercises()
718
Store.of(self).remove(self)
720
class WorksheetExercise(Storm):
721
__storm_table__ = "worksheet_exercise"
723
id = Int(primary=True, name="ws_ex_id")
725
worksheet_id = Int(name="worksheetid")
726
worksheet = Reference(worksheet_id, Worksheet.id)
727
exercise_id = Unicode(name="exerciseid")
728
exercise = Reference(exercise_id, Exercise.id)
733
saves = ReferenceSet(id, "ExerciseSave.ws_ex_id")
734
attempts = ReferenceSet(id, "ExerciseAttempt.ws_ex_id")
736
__init__ = _kwarg_init
739
return "<%s %s in %s>" % (type(self).__name__, self.exercise.name,
740
self.worksheet.identifier)
742
def get_permissions(self, user):
743
return self.worksheet.get_permissions(user)
746
class ExerciseSave(Storm):
748
Represents a potential solution to an exercise that a user has submitted
749
to the server for storage.
750
A basic ExerciseSave is just the current saved text for this exercise for
751
this user (doesn't count towards their attempts).
752
ExerciseSave may be extended with additional semantics (such as
755
__storm_table__ = "exercise_save"
756
__storm_primary__ = "ws_ex_id", "user_id"
758
ws_ex_id = Int(name="ws_ex_id")
759
worksheet_exercise = Reference(ws_ex_id, "WorksheetExercise.id")
761
user_id = Int(name="loginid")
762
user = Reference(user_id, User.id)
766
__init__ = _kwarg_init
769
return "<%s %s by %s at %s>" % (type(self).__name__,
770
self.exercise.name, self.user.login, self.date.strftime("%c"))
772
class ExerciseAttempt(ExerciseSave):
774
An ExerciseAttempt is a special case of an ExerciseSave. Like an
775
ExerciseSave, it constitutes exercise solution data that the user has
776
submitted to the server for storage.
777
In addition, it contains additional information about the submission.
778
complete - True if this submission was successful, rendering this exercise
779
complete for this user.
780
active - True if this submission is "active" (usually true). Submissions
781
may be de-activated by privileged users for special reasons, and then
782
they won't count (either as a penalty or success), but will still be
785
__storm_table__ = "exercise_attempt"
786
__storm_primary__ = "ws_ex_id", "user_id", "date"
788
# The "text" field is the same but has a different name in the DB table
790
text = Unicode(name="attempt")
794
def get_permissions(self, user):
795
return set(['view']) if user is self.user else set()
797
class TestSuite(Storm):
798
"""A Testsuite acts as a container for the test cases of an exercise."""
799
__storm_table__ = "test_suite"
800
__storm_primary__ = "exercise_id", "suiteid"
803
exercise_id = Unicode(name="exerciseid")
804
description = Unicode()
808
exercise = Reference(exercise_id, Exercise.id)
809
test_cases = ReferenceSet(suiteid, 'TestCase.suiteid', order_by="seq_no")
810
variables = ReferenceSet(suiteid, 'TestSuiteVar.suiteid', order_by='arg_no')
813
"""Delete this suite, without asking questions."""
814
for vaariable in self.variables:
816
for test_case in self.test_cases:
818
Store.of(self).remove(self)
820
class TestCase(Storm):
821
"""A TestCase is a member of a TestSuite.
823
It contains the data necessary to check if an exercise is correct"""
824
__storm_table__ = "test_case"
825
__storm_primary__ = "testid", "suiteid"
829
suite = Reference(suiteid, "TestSuite.suiteid")
832
test_default = Unicode()
835
parts = ReferenceSet(testid, "TestCasePart.testid")
837
__init__ = _kwarg_init
840
for part in self.parts:
842
Store.of(self).remove(self)
844
class TestSuiteVar(Storm):
845
"""A container for the arguments of a Test Suite"""
846
__storm_table__ = "suite_variable"
847
__storm_primary__ = "varid"
852
var_value = Unicode()
856
suite = Reference(suiteid, "TestSuite.suiteid")
858
__init__ = _kwarg_init
861
Store.of(self).remove(self)
863
class TestCasePart(Storm):
864
"""A container for the test elements of a Test Case"""
865
__storm_table__ = "test_case_part"
866
__storm_primary__ = "partid"
871
part_type = Unicode()
872
test_type = Unicode()
876
test = Reference(testid, "TestCase.testid")
878
__init__ = _kwarg_init
881
Store.of(self).remove(self)
added
removed
ivle/database.py
