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
36
__all__ = ['get_store',
38
'Subject', 'Semester', 'Offering', 'Enrolment',
39
'ProjectSet', 'Project', 'ProjectGroup', 'ProjectGroupMembership',
40
'Exercise', 'Worksheet', 'WorksheetExercise',
41
'ExerciseSave', 'ExerciseAttempt',
42
'AlreadyEnrolledError', 'TestCase', 'TestSuite', 'TestSuiteVar'
45
def _kwarg_init(self, **kwargs):
46
for k,v in kwargs.items():
47
if k.startswith('_') or not hasattr(self.__class__, k):
48
raise TypeError("%s got an unexpected keyword argument '%s'"
49
% (self.__class__.__name__, k))
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()))
71
Represents an IVLE user.
73
__storm_table__ = "login"
75
id = Int(primary=True, name="loginid")
84
last_login = DateTime()
92
if self.rolenm is None:
94
return ivle.caps.Role(self.rolenm)
95
def _set_role(self, value):
96
if not isinstance(value, ivle.caps.Role):
97
raise TypeError("role must be an ivle.caps.Role")
98
self.rolenm = unicode(value)
99
role = property(_get_role, _set_role)
101
__init__ = _kwarg_init
104
return "<%s '%s'>" % (type(self).__name__, self.login)
106
def authenticate(self, password):
107
"""Validate a given password against this user.
109
Returns True if the given password matches the password hash for this
110
User, False if it doesn't match, and None if there is no hash for the
113
if self.passhash is None:
115
return self.hash_password(password) == self.passhash
117
def hasCap(self, capability):
118
"""Given a capability (which is a Role object), returns True if this
119
User has that capability, False otherwise.
121
return self.role.hasCap(capability)
124
def password_expired(self):
125
fieldval = self.pass_exp
126
return fieldval is not None and datetime.datetime.now() > fieldval
129
def account_expired(self):
130
fieldval = self.acct_exp
131
return fieldval is not None and datetime.datetime.now() > fieldval
135
return self.state == 'enabled' and not self.account_expired
137
def _get_enrolments(self, justactive):
138
return Store.of(self).find(Enrolment,
139
Enrolment.user_id == self.id,
140
(Enrolment.active == True) if justactive else True,
141
Enrolment.offering_id == Offering.id,
142
Offering.semester_id == Semester.id,
143
Offering.subject_id == Subject.id).order_by(
145
Desc(Semester.semester),
149
def _set_password(self, password):
153
self.passhash = unicode(User.hash_password(password))
154
password = property(fset=_set_password)
158
return Store.of(self).find(Subject,
159
Enrolment.user_id == self.id,
160
Enrolment.active == True,
161
Offering.id == Enrolment.offering_id,
162
Subject.id == Offering.subject_id).config(distinct=True)
164
# TODO: Invitations should be listed too?
165
def get_groups(self, offering=None):
167
ProjectGroupMembership.user_id == self.id,
168
ProjectGroup.id == ProjectGroupMembership.project_group_id,
172
ProjectSet.offering_id == offering.id,
173
ProjectGroup.project_set_id == ProjectSet.id,
175
return Store.of(self).find(ProjectGroup, *preds)
179
return self.get_groups()
182
def active_enrolments(self):
183
'''A sanely ordered list of the user's active enrolments.'''
184
return self._get_enrolments(True)
187
def enrolments(self):
188
'''A sanely ordered list of all of the user's enrolments.'''
189
return self._get_enrolments(False)
192
def hash_password(password):
193
return md5.md5(password).hexdigest()
196
def get_by_login(cls, store, login):
198
Get the User from the db associated with a given store and
201
return store.find(cls, cls.login == unicode(login)).one()
203
def get_permissions(self, user):
204
if user and user.rolenm == 'admin' or user is self:
205
return set(['view', 'edit'])
209
# SUBJECTS AND ENROLMENTS #
211
class Subject(Storm):
212
__storm_table__ = "subject"
214
id = Int(primary=True, name="subjectid")
215
code = Unicode(name="subj_code")
216
name = Unicode(name="subj_name")
217
short_name = Unicode(name="subj_short_name")
220
offerings = ReferenceSet(id, 'Offering.subject_id')
222
__init__ = _kwarg_init
225
return "<%s '%s'>" % (type(self).__name__, self.short_name)
227
def get_permissions(self, user):
231
if user.rolenm == 'admin':
235
class Semester(Storm):
236
__storm_table__ = "semester"
238
id = Int(primary=True, name="semesterid")
243
offerings = ReferenceSet(id, 'Offering.semester_id')
245
__init__ = _kwarg_init
248
return "<%s %s/%s>" % (type(self).__name__, self.year, self.semester)
250
class Offering(Storm):
251
__storm_table__ = "offering"
253
id = Int(primary=True, name="offeringid")
254
subject_id = Int(name="subject")
255
subject = Reference(subject_id, Subject.id)
256
semester_id = Int(name="semesterid")
257
semester = Reference(semester_id, Semester.id)
258
groups_student_permissions = Unicode()
260
enrolments = ReferenceSet(id, 'Enrolment.offering_id')
261
members = ReferenceSet(id,
262
'Enrolment.offering_id',
265
project_sets = ReferenceSet(id, 'ProjectSet.offering_id')
267
worksheets = ReferenceSet(id, 'Worksheet.offering_id')
269
__init__ = _kwarg_init
272
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)
287
def get_permissions(self, user):
291
if user.rolenm == 'admin':
295
class Enrolment(Storm):
296
__storm_table__ = "enrolment"
297
__storm_primary__ = "user_id", "offering_id"
299
user_id = Int(name="loginid")
300
user = Reference(user_id, User.id)
301
offering_id = Int(name="offeringid")
302
offering = Reference(offering_id, Offering.id)
308
return Store.of(self).find(ProjectGroup,
309
ProjectSet.offering_id == self.offering.id,
310
ProjectGroup.project_set_id == ProjectSet.id,
311
ProjectGroupMembership.project_group_id == ProjectGroup.id,
312
ProjectGroupMembership.user_id == self.user.id)
314
__init__ = _kwarg_init
317
return "<%s %r in %r>" % (type(self).__name__, self.user,
320
class AlreadyEnrolledError(Exception):
325
class ProjectSet(Storm):
326
__storm_table__ = "project_set"
328
id = Int(name="projectsetid", primary=True)
329
offering_id = Int(name="offeringid")
330
offering = Reference(offering_id, Offering.id)
331
max_students_per_group = Int()
333
projects = ReferenceSet(id, 'Project.project_set_id')
334
project_groups = ReferenceSet(id, 'ProjectGroup.project_set_id')
336
__init__ = _kwarg_init
339
return "<%s %d in %r>" % (type(self).__name__, self.id,
342
class Project(Storm):
343
__storm_table__ = "project"
345
id = Int(name="projectid", primary=True)
348
project_set_id = Int(name="projectsetid")
349
project_set = Reference(project_set_id, ProjectSet.id)
350
deadline = DateTime()
352
__init__ = _kwarg_init
355
return "<%s '%s' in %r>" % (type(self).__name__, self.synopsis,
356
self.project_set.offering)
358
class ProjectGroup(Storm):
359
__storm_table__ = "project_group"
361
id = Int(name="groupid", primary=True)
362
name = Unicode(name="groupnm")
363
project_set_id = Int(name="projectsetid")
364
project_set = Reference(project_set_id, ProjectSet.id)
366
created_by_id = Int(name="createdby")
367
created_by = Reference(created_by_id, User.id)
370
members = ReferenceSet(id,
371
"ProjectGroupMembership.project_group_id",
372
"ProjectGroupMembership.user_id",
375
__init__ = _kwarg_init
378
return "<%s %s in %r>" % (type(self).__name__, self.name,
379
self.project_set.offering)
381
class ProjectGroupMembership(Storm):
382
__storm_table__ = "group_member"
383
__storm_primary__ = "user_id", "project_group_id"
385
user_id = Int(name="loginid")
386
user = Reference(user_id, User.id)
387
project_group_id = Int(name="groupid")
388
project_group = Reference(project_group_id, ProjectGroup.id)
390
__init__ = _kwarg_init
393
return "<%s %r in %r>" % (type(self).__name__, self.user,
396
# WORKSHEETS AND EXERCISES #
398
class Exercise(Storm):
399
# Note: Table "problem" is called "Exercise" in the Object layer, since
400
# it's called that everywhere else.
401
__storm_table__ = "problem"
402
#TODO: Add in a field for the user-friendly identifier
403
id = Unicode(primary=True, name="identifier")
405
description = Unicode()
411
worksheets = ReferenceSet(id,
412
'WorksheetExercise.exercise_id',
413
'WorksheetExercise.worksheet_id',
417
test_suites = ReferenceSet(id, 'TestSuite.exercise_id')
419
__init__ = _kwarg_init
422
return "<%s %s>" % (type(self).__name__, self.name)
425
# def get_by_name(cls, store, name):
427
# Get the Exercise from the db associated with a given store and name.
428
# If the exercise is not in the database, creates it and inserts it
431
# ex = store.find(cls, cls.name == unicode(name)).one()
434
# ex = Exercise(name=unicode(name))
439
class Worksheet(Storm):
440
__storm_table__ = "worksheet"
442
id = Int(primary=True, name="worksheetid")
443
# XXX subject is not linked to a Subject object. This is a property of
444
# the database, and will be refactored.
446
offering_id = Int(name="offeringid")
447
name = Unicode(name="identifier")
451
attempts = ReferenceSet(id, "ExerciseAttempt.worksheetid")
452
offering = Reference(offering_id, 'Offering.id')
454
exercises = ReferenceSet(id,
455
'WorksheetExercise.worksheet_id',
456
'WorksheetExercise.exercise_id',
458
# Use worksheet_exercises to get access to the WorksheetExercise objects
459
# binding worksheets to exercises. This is required to access the
461
worksheet_exercises = ReferenceSet(id,
462
'WorksheetExercise.worksheet_id')
465
__init__ = _kwarg_init
468
return "<%s %s>" % (type(self).__name__, self.name)
470
# XXX Refactor this - make it an instance method of Subject rather than a
471
# class method of Worksheet. Can't do that now because Subject isn't
472
# linked referentially to the Worksheet.
474
def get_by_name(cls, store, subjectname, worksheetname):
476
Get the Worksheet from the db associated with a given store, subject
477
name and worksheet name.
479
return store.find(cls, cls.subject == unicode(subjectname),
480
cls.name == unicode(worksheetname)).one()
482
def remove_all_exercises(self, store):
484
Remove all exercises from this worksheet.
485
This does not delete the exercises themselves. It just removes them
488
store.find(WorksheetExercise,
489
WorksheetExercise.worksheet == self).remove()
491
def get_permissions(self, user):
492
return self.offering.get_permissions(user)
494
class WorksheetExercise(Storm):
495
__storm_table__ = "worksheet_problem"
496
__storm_primary__ = "worksheet_id", "exercise_id"
498
worksheet_id = Int(name="worksheetid")
499
worksheet = Reference(worksheet_id, Worksheet.id)
500
exercise_id = Unicode(name="problemid")
501
exercise = Reference(exercise_id, Exercise.id)
504
__init__ = _kwarg_init
507
return "<%s %s in %s>" % (type(self).__name__, self.exercise.name,
510
class ExerciseSave(Storm):
512
Represents a potential solution to an exercise that a user has submitted
513
to the server for storage.
514
A basic ExerciseSave is just the current saved text for this exercise for
515
this user (doesn't count towards their attempts).
516
ExerciseSave may be extended with additional semantics (such as
519
__storm_table__ = "problem_save"
520
__storm_primary__ = "exercise_id", "user_id", "date"
522
exercise_id = Unicode(name="problemid")
523
exercise = Reference(exercise_id, Exercise.id)
524
user_id = Int(name="loginid")
525
user = Reference(user_id, User.id)
529
worksheet = Reference(worksheetid, Worksheet.id)
531
__init__ = _kwarg_init
534
return "<%s %s by %s at %s>" % (type(self).__name__,
535
self.exercise.name, self.user.login, self.date.strftime("%c"))
537
class ExerciseAttempt(ExerciseSave):
539
An ExerciseAttempt is a special case of an ExerciseSave. Like an
540
ExerciseSave, it constitutes exercise solution data that the user has
541
submitted to the server for storage.
542
In addition, it contains additional information about the submission.
543
complete - True if this submission was successful, rendering this exercise
544
complete for this user.
545
active - True if this submission is "active" (usually true). Submissions
546
may be de-activated by privileged users for special reasons, and then
547
they won't count (either as a penalty or success), but will still be
550
__storm_table__ = "problem_attempt"
551
__storm_primary__ = "exercise_id", "user_id", "date"
553
# The "text" field is the same but has a different name in the DB table
555
text = Unicode(name="attempt")
559
def get_permissions(self, user):
560
return set(['view']) if user is self.user else set()
562
class TestSuite(Storm):
563
"""A Testsuite acts as a container for the test cases of an exercise."""
564
__storm_table__ = "test_suite"
565
__storm_primary__ = "exercise_id", "suiteid"
568
exercise_id = Unicode(name="problemid")
569
description = Unicode()
573
exercise = Reference(exercise_id, Exercise.id)
574
test_cases = ReferenceSet(suiteid, 'TestCase.suiteid')
575
variables = ReferenceSet(suiteid, 'TestSuiteVar.suiteid')
577
class TestCase(Storm):
578
"""A TestCase is a member of a TestSuite.
580
It contains the data necessary to check if an exercise is correct"""
581
__storm_table__ = "test_case"
582
__storm_primary__ = "testid", "suiteid"
586
suite = Reference(suiteid, "TestSuite.suiteid")
589
test_default = Unicode()
592
parts = ReferenceSet(testid, "TestCasePart.testid")
594
__init__ = _kwarg_init
596
class TestSuiteVar(Storm):
597
"""A container for the arguments of a Test Suite"""
598
__storm_table__ = "suite_variables"
599
__storm_primary__ = "varid"
604
var_value = Unicode()
608
suite = Reference(suiteid, "TestSuite.suiteid")
610
__init__ = _kwarg_init
612
class TestCasePart(Storm):
613
"""A container for the test elements of a Test Case"""
614
__storm_table__ = "test_case_parts"
615
__storm_primary__ = "partid"
620
part_type = Unicode()
621
test_type = Unicode()
625
test = Reference(testid, "TestCase.testid")
627
__init__ = _kwarg_init
added
removed
ivle/database.py
