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 == 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 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
class Project(Storm):
391
__storm_table__ = "project"
393
id = Int(name="projectid", primary=True)
395
short_name = Unicode()
398
project_set_id = Int(name="projectsetid")
399
project_set = Reference(project_set_id, ProjectSet.id)
400
deadline = DateTime()
402
assesseds = ReferenceSet(id, 'Assessed.project_id')
403
submissions = ReferenceSet(id,
404
'Assessed.project_id',
406
'ProjectSubmission.assessed_id')
408
__init__ = _kwarg_init
411
return "<%s '%s' in %r>" % (type(self).__name__, self.short_name,
412
self.project_set.offering)
414
def can_submit(self, principal):
415
return (self in principal.get_projects() and
416
self.deadline > datetime.datetime.now())
418
def submit(self, principal, path, revision, who):
419
"""Submit a Subversion path and revision to a project.
421
'principal' is the owner of the Subversion repository, and the
422
entity on behalf of whom the submission is being made. 'path' is
423
a path within that repository, and 'revision' specifies which
424
revision of that path. 'who' is the person making the submission.
427
if not self.can_submit(principal):
428
raise Exception('cannot submit')
430
a = Assessed.get(Store.of(self), principal, self)
431
ps = ProjectSubmission()
433
ps.revision = revision
434
ps.date_submitted = datetime.datetime.now()
441
class ProjectGroup(Storm):
442
__storm_table__ = "project_group"
444
id = Int(name="groupid", primary=True)
445
name = Unicode(name="groupnm")
446
project_set_id = Int(name="projectsetid")
447
project_set = Reference(project_set_id, ProjectSet.id)
449
created_by_id = Int(name="createdby")
450
created_by = Reference(created_by_id, User.id)
453
members = ReferenceSet(id,
454
"ProjectGroupMembership.project_group_id",
455
"ProjectGroupMembership.user_id",
458
__init__ = _kwarg_init
461
return "<%s %s in %r>" % (type(self).__name__, self.name,
462
self.project_set.offering)
465
def display_name(self):
466
return '%s (%s)' % (self.nick, self.name)
468
def get_projects(self, offering=None, active_only=True):
469
'''Return Projects that the group can submit.
471
This will include projects in the project set which owns this group,
472
unless the project set disallows groups (in which case none will be
475
Unless active_only is False, projects will only be returned if the
476
group's offering is active.
478
If an offering is specified, projects will only be returned if it
481
return Store.of(self).find(Project,
482
Project.project_set_id == ProjectSet.id,
483
ProjectSet.id == self.project_set.id,
484
ProjectSet.max_students_per_group != None,
485
ProjectSet.offering_id == Offering.id,
486
(offering is None) or (Offering.id == offering.id),
487
Semester.id == Offering.semester_id,
488
(not active_only) or (Semester.state == u'current'))
491
def get_permissions(self, user):
492
if user.admin or user in self.members:
493
return set(['submit_project'])
497
class ProjectGroupMembership(Storm):
498
__storm_table__ = "group_member"
499
__storm_primary__ = "user_id", "project_group_id"
501
user_id = Int(name="loginid")
502
user = Reference(user_id, User.id)
503
project_group_id = Int(name="groupid")
504
project_group = Reference(project_group_id, ProjectGroup.id)
506
__init__ = _kwarg_init
509
return "<%s %r in %r>" % (type(self).__name__, self.user,
512
class Assessed(Storm):
513
__storm_table__ = "assessed"
515
id = Int(name="assessedid", primary=True)
516
user_id = Int(name="loginid")
517
user = Reference(user_id, User.id)
518
project_group_id = Int(name="groupid")
519
project_group = Reference(project_group_id, ProjectGroup.id)
521
project_id = Int(name="projectid")
522
project = Reference(project_id, Project.id)
524
extensions = ReferenceSet(id, 'ProjectExtension.assessed_id')
525
submissions = ReferenceSet(id, 'ProjectSubmission.assessed_id')
528
return "<%s %r in %r>" % (type(self).__name__,
529
self.user or self.project_group, self.project)
532
def get(cls, store, principal, project):
534
if t not in (User, ProjectGroup):
535
raise AssertionError('principal must be User or ProjectGroup')
538
(t is User) or (cls.project_group_id == principal.id),
539
(t is ProjectGroup) or (cls.user_id == principal.id),
540
Project.id == project.id).one()
547
a.project_group = principal
554
class ProjectExtension(Storm):
555
__storm_table__ = "project_extension"
557
id = Int(name="extensionid", primary=True)
558
assessed_id = Int(name="assessedid")
559
assessed = Reference(assessed_id, Assessed.id)
560
deadline = DateTime()
561
approver_id = Int(name="approver")
562
approver = Reference(approver_id, User.id)
565
class ProjectSubmission(Storm):
566
__storm_table__ = "project_submission"
568
id = Int(name="submissionid", primary=True)
569
assessed_id = Int(name="assessedid")
570
assessed = Reference(assessed_id, Assessed.id)
573
submitter_id = Int(name="submitter")
574
submitter = Reference(submitter_id, User.id)
575
date_submitted = DateTime()
578
# WORKSHEETS AND EXERCISES #
580
class Exercise(Storm):
581
__storm_table__ = "exercise"
582
id = Unicode(primary=True, name="identifier")
584
description = Unicode()
590
worksheet_exercises = ReferenceSet(id,
591
'WorksheetExercise.exercise_id')
593
worksheets = ReferenceSet(id,
594
'WorksheetExercise.exercise_id',
595
'WorksheetExercise.worksheet_id',
599
test_suites = ReferenceSet(id,
600
'TestSuite.exercise_id',
603
__init__ = _kwarg_init
606
return "<%s %s>" % (type(self).__name__, self.name)
608
def get_permissions(self, user):
615
elif 'lecturer' in set((e.role for e in user.active_enrolments)):
621
def get_description(self):
622
return rst(self.description)
625
"""Deletes the exercise, providing it has no associated worksheets."""
626
if (self.worksheet_exercises.count() > 0):
627
raise IntegrityError()
628
for suite in self.test_suites:
630
Store.of(self).remove(self)
632
class Worksheet(Storm):
633
__storm_table__ = "worksheet"
635
id = Int(primary=True, name="worksheetid")
636
offering_id = Int(name="offeringid")
637
identifier = Unicode()
644
attempts = ReferenceSet(id, "ExerciseAttempt.worksheetid")
645
offering = Reference(offering_id, 'Offering.id')
647
all_worksheet_exercises = ReferenceSet(id,
648
'WorksheetExercise.worksheet_id')
650
# Use worksheet_exercises to get access to the *active* WorksheetExercise
651
# objects binding worksheets to exercises. This is required to access the
655
def worksheet_exercises(self):
656
return self.all_worksheet_exercises.find(active=True)
658
__init__ = _kwarg_init
661
return "<%s %s>" % (type(self).__name__, self.name)
663
# XXX Refactor this - make it an instance method of Subject rather than a
664
# class method of Worksheet. Can't do that now because Subject isn't
665
# linked referentially to the Worksheet.
667
def get_by_name(cls, store, subjectname, worksheetname):
669
Get the Worksheet from the db associated with a given store, subject
670
name and worksheet name.
672
return store.find(cls, cls.subject == unicode(subjectname),
673
cls.name == unicode(worksheetname)).one()
675
def remove_all_exercises(self):
677
Remove all exercises from this worksheet.
678
This does not delete the exercises themselves. It just removes them
681
store = Store.of(self)
682
for ws_ex in self.all_worksheet_exercises:
683
if ws_ex.saves.count() > 0 or ws_ex.attempts.count() > 0:
684
raise IntegrityError()
685
store.find(WorksheetExercise,
686
WorksheetExercise.worksheet == self).remove()
688
def get_permissions(self, user):
689
return self.offering.get_permissions(user)
692
"""Returns the xml of this worksheet, converts from rst if required."""
693
if self.format == u'rst':
694
ws_xml = rst(self.data)
700
"""Deletes the worksheet, provided it has no attempts on any exercises.
702
Returns True if delete succeeded, or False if this worksheet has
703
attempts attached."""
704
for ws_ex in self.all_worksheet_exercises:
705
if ws_ex.saves.count() > 0 or ws_ex.attempts.count() > 0:
706
raise IntegrityError()
708
self.remove_all_exercises()
709
Store.of(self).remove(self)
711
class WorksheetExercise(Storm):
712
__storm_table__ = "worksheet_exercise"
714
id = Int(primary=True, name="ws_ex_id")
716
worksheet_id = Int(name="worksheetid")
717
worksheet = Reference(worksheet_id, Worksheet.id)
718
exercise_id = Unicode(name="exerciseid")
719
exercise = Reference(exercise_id, Exercise.id)
724
saves = ReferenceSet(id, "ExerciseSave.ws_ex_id")
725
attempts = ReferenceSet(id, "ExerciseAttempt.ws_ex_id")
727
__init__ = _kwarg_init
730
return "<%s %s in %s>" % (type(self).__name__, self.exercise.name,
731
self.worksheet.identifier)
733
def get_permissions(self, user):
734
return self.worksheet.get_permissions(user)
737
class ExerciseSave(Storm):
739
Represents a potential solution to an exercise that a user has submitted
740
to the server for storage.
741
A basic ExerciseSave is just the current saved text for this exercise for
742
this user (doesn't count towards their attempts).
743
ExerciseSave may be extended with additional semantics (such as
746
__storm_table__ = "exercise_save"
747
__storm_primary__ = "ws_ex_id", "user_id"
749
ws_ex_id = Int(name="ws_ex_id")
750
worksheet_exercise = Reference(ws_ex_id, "WorksheetExercise.id")
752
user_id = Int(name="loginid")
753
user = Reference(user_id, User.id)
757
__init__ = _kwarg_init
760
return "<%s %s by %s at %s>" % (type(self).__name__,
761
self.exercise.name, self.user.login, self.date.strftime("%c"))
763
class ExerciseAttempt(ExerciseSave):
765
An ExerciseAttempt is a special case of an ExerciseSave. Like an
766
ExerciseSave, it constitutes exercise solution data that the user has
767
submitted to the server for storage.
768
In addition, it contains additional information about the submission.
769
complete - True if this submission was successful, rendering this exercise
770
complete for this user.
771
active - True if this submission is "active" (usually true). Submissions
772
may be de-activated by privileged users for special reasons, and then
773
they won't count (either as a penalty or success), but will still be
776
__storm_table__ = "exercise_attempt"
777
__storm_primary__ = "ws_ex_id", "user_id", "date"
779
# The "text" field is the same but has a different name in the DB table
781
text = Unicode(name="attempt")
785
def get_permissions(self, user):
786
return set(['view']) if user is self.user else set()
788
class TestSuite(Storm):
789
"""A Testsuite acts as a container for the test cases of an exercise."""
790
__storm_table__ = "test_suite"
791
__storm_primary__ = "exercise_id", "suiteid"
794
exercise_id = Unicode(name="exerciseid")
795
description = Unicode()
799
exercise = Reference(exercise_id, Exercise.id)
800
test_cases = ReferenceSet(suiteid, 'TestCase.suiteid', order_by="seq_no")
801
variables = ReferenceSet(suiteid, 'TestSuiteVar.suiteid', order_by='arg_no')
804
"""Delete this suite, without asking questions."""
805
for vaariable in self.variables:
807
for test_case in self.test_cases:
809
Store.of(self).remove(self)
811
class TestCase(Storm):
812
"""A TestCase is a member of a TestSuite.
814
It contains the data necessary to check if an exercise is correct"""
815
__storm_table__ = "test_case"
816
__storm_primary__ = "testid", "suiteid"
820
suite = Reference(suiteid, "TestSuite.suiteid")
823
test_default = Unicode()
826
parts = ReferenceSet(testid, "TestCasePart.testid")
828
__init__ = _kwarg_init
831
for part in self.parts:
833
Store.of(self).remove(self)
835
class TestSuiteVar(Storm):
836
"""A container for the arguments of a Test Suite"""
837
__storm_table__ = "suite_variable"
838
__storm_primary__ = "varid"
843
var_value = Unicode()
847
suite = Reference(suiteid, "TestSuite.suiteid")
849
__init__ = _kwarg_init
852
Store.of(self).remove(self)
854
class TestCasePart(Storm):
855
"""A container for the test elements of a Test Case"""
856
__storm_table__ = "test_case_part"
857
__storm_primary__ = "partid"
862
part_type = Unicode()
863
test_type = Unicode()
867
test = Reference(testid, "TestCase.testid")
869
__init__ = _kwarg_init
872
Store.of(self).remove(self)
added
removed
ivle/database.py
