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 password_expired(self):
122
fieldval = self.pass_exp
123
return fieldval is not None and datetime.datetime.now() > fieldval
126
def account_expired(self):
127
fieldval = self.acct_exp
128
return fieldval is not None and datetime.datetime.now() > fieldval
132
return self.state == 'enabled' and not self.account_expired
134
def _get_enrolments(self, justactive):
135
return Store.of(self).find(Enrolment,
136
Enrolment.user_id == self.id,
137
(Enrolment.active == True) if justactive else True,
138
Enrolment.offering_id == Offering.id,
139
Offering.semester_id == Semester.id,
140
Offering.subject_id == Subject.id).order_by(
142
Desc(Semester.semester),
146
def _set_password(self, password):
150
self.passhash = unicode(User.hash_password(password))
151
password = property(fset=_set_password)
155
return Store.of(self).find(Subject,
156
Enrolment.user_id == self.id,
157
Enrolment.active == True,
158
Offering.id == Enrolment.offering_id,
159
Subject.id == Offering.subject_id).config(distinct=True)
161
# TODO: Invitations should be listed too?
162
def get_groups(self, offering=None):
164
ProjectGroupMembership.user_id == self.id,
165
ProjectGroup.id == ProjectGroupMembership.project_group_id,
169
ProjectSet.offering_id == offering.id,
170
ProjectGroup.project_set_id == ProjectSet.id,
172
return Store.of(self).find(ProjectGroup, *preds)
176
return self.get_groups()
179
def active_enrolments(self):
180
'''A sanely ordered list of the user's active enrolments.'''
181
return self._get_enrolments(True)
184
def enrolments(self):
185
'''A sanely ordered list of all of the user's enrolments.'''
186
return self._get_enrolments(False)
189
def hash_password(password):
190
return md5.md5(password).hexdigest()
193
def get_by_login(cls, store, login):
195
Get the User from the db associated with a given store and
198
return store.find(cls, cls.login == unicode(login)).one()
200
def get_permissions(self, user):
201
if user and user.admin or user is self:
202
return set(['view', 'edit', 'submit_project'])
206
# SUBJECTS AND ENROLMENTS #
208
class Subject(Storm):
209
__storm_table__ = "subject"
211
id = Int(primary=True, name="subjectid")
212
code = Unicode(name="subj_code")
213
name = Unicode(name="subj_name")
214
short_name = Unicode(name="subj_short_name")
217
offerings = ReferenceSet(id, 'Offering.subject_id')
219
__init__ = _kwarg_init
222
return "<%s '%s'>" % (type(self).__name__, self.short_name)
224
def get_permissions(self, user):
232
class Semester(Storm):
233
__storm_table__ = "semester"
235
id = Int(primary=True, name="semesterid")
240
offerings = ReferenceSet(id, 'Offering.semester_id')
241
enrolments = ReferenceSet(id,
242
'Offering.semester_id',
244
'Enrolment.offering_id')
246
__init__ = _kwarg_init
249
return "<%s %s/%s>" % (type(self).__name__, self.year, self.semester)
251
class Offering(Storm):
252
__storm_table__ = "offering"
254
id = Int(primary=True, name="offeringid")
255
subject_id = Int(name="subject")
256
subject = Reference(subject_id, Subject.id)
257
semester_id = Int(name="semesterid")
258
semester = Reference(semester_id, Semester.id)
259
groups_student_permissions = Unicode()
261
enrolments = ReferenceSet(id, 'Enrolment.offering_id')
262
members = ReferenceSet(id,
263
'Enrolment.offering_id',
266
project_sets = ReferenceSet(id, 'ProjectSet.offering_id')
268
worksheets = ReferenceSet(id,
269
'Worksheet.offering_id',
273
__init__ = _kwarg_init
276
return "<%s %r in %r>" % (type(self).__name__, self.subject,
279
def enrol(self, user, role=u'student'):
280
'''Enrol a user in this offering.'''
281
enrolment = Store.of(self).find(Enrolment,
282
Enrolment.user_id == user.id,
283
Enrolment.offering_id == self.id).one()
285
if enrolment is None:
286
enrolment = Enrolment(user=user, offering=self)
287
self.enrolments.add(enrolment)
289
enrolment.active = True
290
enrolment.role = role
292
def unenrol(self, user):
293
'''Unenrol a user from this offering.'''
294
enrolment = Store.of(self).find(Enrolment,
295
Enrolment.user_id == user.id,
296
Enrolment.offering_id == self.id).one()
297
Store.of(enrolment).remove(enrolment)
299
def get_permissions(self, user):
302
enrolment = self.get_enrolment(user)
303
if enrolment or user.admin:
305
if (enrolment and enrolment.role in (u'tutor', u'lecturer')) \
310
def get_enrolment(self, user):
312
enrolment = self.enrolments.find(user=user).one()
318
class Enrolment(Storm):
319
__storm_table__ = "enrolment"
320
__storm_primary__ = "user_id", "offering_id"
322
user_id = Int(name="loginid")
323
user = Reference(user_id, User.id)
324
offering_id = Int(name="offeringid")
325
offering = Reference(offering_id, Offering.id)
332
return Store.of(self).find(ProjectGroup,
333
ProjectSet.offering_id == self.offering.id,
334
ProjectGroup.project_set_id == ProjectSet.id,
335
ProjectGroupMembership.project_group_id == ProjectGroup.id,
336
ProjectGroupMembership.user_id == self.user.id)
338
__init__ = _kwarg_init
341
return "<%s %r in %r>" % (type(self).__name__, self.user,
346
class ProjectSet(Storm):
347
__storm_table__ = "project_set"
349
id = Int(name="projectsetid", primary=True)
350
offering_id = Int(name="offeringid")
351
offering = Reference(offering_id, Offering.id)
352
max_students_per_group = Int()
354
projects = ReferenceSet(id, 'Project.project_set_id')
355
project_groups = ReferenceSet(id, 'ProjectGroup.project_set_id')
357
__init__ = _kwarg_init
360
return "<%s %d in %r>" % (type(self).__name__, self.id,
363
class Project(Storm):
364
__storm_table__ = "project"
366
id = Int(name="projectid", primary=True)
368
short_name = Unicode()
371
project_set_id = Int(name="projectsetid")
372
project_set = Reference(project_set_id, ProjectSet.id)
373
deadline = DateTime()
375
assesseds = ReferenceSet(id, 'Assessed.project_id')
376
submissions = ReferenceSet(id,
377
'Assessed.project_id',
379
'ProjectSubmission.assessed_id')
381
__init__ = _kwarg_init
384
return "<%s '%s' in %r>" % (type(self).__name__, self.short_name,
385
self.project_set.offering)
387
class ProjectGroup(Storm):
388
__storm_table__ = "project_group"
390
id = Int(name="groupid", primary=True)
391
name = Unicode(name="groupnm")
392
project_set_id = Int(name="projectsetid")
393
project_set = Reference(project_set_id, ProjectSet.id)
395
created_by_id = Int(name="createdby")
396
created_by = Reference(created_by_id, User.id)
399
members = ReferenceSet(id,
400
"ProjectGroupMembership.project_group_id",
401
"ProjectGroupMembership.user_id",
404
__init__ = _kwarg_init
407
return "<%s %s in %r>" % (type(self).__name__, self.name,
408
self.project_set.offering)
410
def get_permissions(self, user):
411
if user.admin or user in self.members:
412
return set(['submit_project'])
416
class ProjectGroupMembership(Storm):
417
__storm_table__ = "group_member"
418
__storm_primary__ = "user_id", "project_group_id"
420
user_id = Int(name="loginid")
421
user = Reference(user_id, User.id)
422
project_group_id = Int(name="groupid")
423
project_group = Reference(project_group_id, ProjectGroup.id)
425
__init__ = _kwarg_init
428
return "<%s %r in %r>" % (type(self).__name__, self.user,
431
class Assessed(Storm):
432
__storm_table__ = "assessed"
434
id = Int(name="assessedid", primary=True)
435
user_id = Int(name="loginid")
436
user = Reference(user_id, User.id)
437
project_group_id = Int(name="groupid")
438
project_group = Reference(project_group_id, ProjectGroup.id)
440
project_id = Int(name="projectid")
441
project = Reference(project_id, Project.id)
443
extensions = ReferenceSet(id, 'ProjectExtension.assessed_id')
444
submissions = ReferenceSet(id, 'ProjectSubmission.assessed_id')
447
return "<%s %r in %r>" % (type(self).__name__,
448
self.user or self.project_group, self.project)
450
class ProjectExtension(Storm):
451
__storm_table__ = "project_extension"
453
id = Int(name="extensionid", primary=True)
454
assessed_id = Int(name="assessedid")
455
assessed = Reference(assessed_id, Assessed.id)
456
deadline = DateTime()
457
approver_id = Int(name="approver")
458
approver = Reference(approver_id, User.id)
461
class ProjectSubmission(Storm):
462
__storm_table__ = "project_submission"
464
id = Int(name="submissionid", primary=True)
465
assessed_id = Int(name="assessedid")
466
assessed = Reference(assessed_id, Assessed.id)
469
date_submitted = DateTime()
472
# WORKSHEETS AND EXERCISES #
474
class Exercise(Storm):
475
__storm_table__ = "exercise"
476
id = Unicode(primary=True, name="identifier")
478
description = Unicode()
484
worksheet_exercises = ReferenceSet(id,
485
'WorksheetExercise.exercise_id')
487
worksheets = ReferenceSet(id,
488
'WorksheetExercise.exercise_id',
489
'WorksheetExercise.worksheet_id',
493
test_suites = ReferenceSet(id,
494
'TestSuite.exercise_id',
497
__init__ = _kwarg_init
500
return "<%s %s>" % (type(self).__name__, self.name)
502
def get_permissions(self, user):
509
elif 'lecturer' in set((e.role for e in user.active_enrolments)):
515
def get_description(self):
516
return rst(self.description)
519
"""Deletes the exercise, providing it has no associated worksheets."""
520
if (self.worksheet_exercises.count() > 0):
521
raise IntegrityError()
522
for suite in self.test_suites:
524
Store.of(self).remove(self)
526
class Worksheet(Storm):
527
__storm_table__ = "worksheet"
529
id = Int(primary=True, name="worksheetid")
530
offering_id = Int(name="offeringid")
531
identifier = Unicode()
538
attempts = ReferenceSet(id, "ExerciseAttempt.worksheetid")
539
offering = Reference(offering_id, 'Offering.id')
541
all_worksheet_exercises = ReferenceSet(id,
542
'WorksheetExercise.worksheet_id')
544
# Use worksheet_exercises to get access to the *active* WorksheetExercise
545
# objects binding worksheets to exercises. This is required to access the
549
def worksheet_exercises(self):
550
return self.all_worksheet_exercises.find(active=True)
552
__init__ = _kwarg_init
555
return "<%s %s>" % (type(self).__name__, self.name)
557
# XXX Refactor this - make it an instance method of Subject rather than a
558
# class method of Worksheet. Can't do that now because Subject isn't
559
# linked referentially to the Worksheet.
561
def get_by_name(cls, store, subjectname, worksheetname):
563
Get the Worksheet from the db associated with a given store, subject
564
name and worksheet name.
566
return store.find(cls, cls.subject == unicode(subjectname),
567
cls.name == unicode(worksheetname)).one()
569
def remove_all_exercises(self):
571
Remove all exercises from this worksheet.
572
This does not delete the exercises themselves. It just removes them
575
store = Store.of(self)
576
for ws_ex in self.all_worksheet_exercises:
577
if ws_ex.saves.count() > 0 or ws_ex.attempts.count() > 0:
578
raise IntegrityError()
579
store.find(WorksheetExercise,
580
WorksheetExercise.worksheet == self).remove()
582
def get_permissions(self, user):
583
return self.offering.get_permissions(user)
586
"""Returns the xml of this worksheet, converts from rst if required."""
587
if self.format == u'rst':
588
ws_xml = rst(self.data)
594
"""Deletes the worksheet, provided it has no attempts on any exercises.
596
Returns True if delete succeeded, or False if this worksheet has
597
attempts attached."""
598
for ws_ex in self.all_worksheet_exercises:
599
if ws_ex.saves.count() > 0 or ws_ex.attempts.count() > 0:
600
raise IntegrityError()
602
self.remove_all_exercises()
603
Store.of(self).remove(self)
605
class WorksheetExercise(Storm):
606
__storm_table__ = "worksheet_exercise"
608
id = Int(primary=True, name="ws_ex_id")
610
worksheet_id = Int(name="worksheetid")
611
worksheet = Reference(worksheet_id, Worksheet.id)
612
exercise_id = Unicode(name="exerciseid")
613
exercise = Reference(exercise_id, Exercise.id)
618
saves = ReferenceSet(id, "ExerciseSave.ws_ex_id")
619
attempts = ReferenceSet(id, "ExerciseAttempt.ws_ex_id")
621
__init__ = _kwarg_init
624
return "<%s %s in %s>" % (type(self).__name__, self.exercise.name,
625
self.worksheet.identifier)
627
def get_permissions(self, user):
628
return self.worksheet.get_permissions(user)
631
class ExerciseSave(Storm):
633
Represents a potential solution to an exercise that a user has submitted
634
to the server for storage.
635
A basic ExerciseSave is just the current saved text for this exercise for
636
this user (doesn't count towards their attempts).
637
ExerciseSave may be extended with additional semantics (such as
640
__storm_table__ = "exercise_save"
641
__storm_primary__ = "ws_ex_id", "user_id"
643
ws_ex_id = Int(name="ws_ex_id")
644
worksheet_exercise = Reference(ws_ex_id, "WorksheetExercise.id")
646
user_id = Int(name="loginid")
647
user = Reference(user_id, User.id)
651
__init__ = _kwarg_init
654
return "<%s %s by %s at %s>" % (type(self).__name__,
655
self.exercise.name, self.user.login, self.date.strftime("%c"))
657
class ExerciseAttempt(ExerciseSave):
659
An ExerciseAttempt is a special case of an ExerciseSave. Like an
660
ExerciseSave, it constitutes exercise solution data that the user has
661
submitted to the server for storage.
662
In addition, it contains additional information about the submission.
663
complete - True if this submission was successful, rendering this exercise
664
complete for this user.
665
active - True if this submission is "active" (usually true). Submissions
666
may be de-activated by privileged users for special reasons, and then
667
they won't count (either as a penalty or success), but will still be
670
__storm_table__ = "exercise_attempt"
671
__storm_primary__ = "ws_ex_id", "user_id", "date"
673
# The "text" field is the same but has a different name in the DB table
675
text = Unicode(name="attempt")
679
def get_permissions(self, user):
680
return set(['view']) if user is self.user else set()
682
class TestSuite(Storm):
683
"""A Testsuite acts as a container for the test cases of an exercise."""
684
__storm_table__ = "test_suite"
685
__storm_primary__ = "exercise_id", "suiteid"
688
exercise_id = Unicode(name="exerciseid")
689
description = Unicode()
693
exercise = Reference(exercise_id, Exercise.id)
694
test_cases = ReferenceSet(suiteid, 'TestCase.suiteid', order_by="seq_no")
695
variables = ReferenceSet(suiteid, 'TestSuiteVar.suiteid', order_by='arg_no')
698
"""Delete this suite, without asking questions."""
699
for vaariable in self.variables:
701
for test_case in self.test_cases:
703
Store.of(self).remove(self)
705
class TestCase(Storm):
706
"""A TestCase is a member of a TestSuite.
708
It contains the data necessary to check if an exercise is correct"""
709
__storm_table__ = "test_case"
710
__storm_primary__ = "testid", "suiteid"
714
suite = Reference(suiteid, "TestSuite.suiteid")
717
test_default = Unicode()
720
parts = ReferenceSet(testid, "TestCasePart.testid")
722
__init__ = _kwarg_init
725
for part in self.parts:
727
Store.of(self).remove(self)
729
class TestSuiteVar(Storm):
730
"""A container for the arguments of a Test Suite"""
731
__storm_table__ = "suite_variable"
732
__storm_primary__ = "varid"
737
var_value = Unicode()
741
suite = Reference(suiteid, "TestSuite.suiteid")
743
__init__ = _kwarg_init
746
Store.of(self).remove(self)
748
class TestCasePart(Storm):
749
"""A container for the test elements of a Test Case"""
750
__storm_table__ = "test_case_part"
751
__storm_primary__ = "partid"
756
part_type = Unicode()
757
test_type = Unicode()
761
test = Reference(testid, "TestCase.testid")
763
__init__ = _kwarg_init
766
Store.of(self).remove(self)
added
removed
ivle/database.py
