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'
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
133
def _get_enrolments(self, justactive):
134
return Store.of(self).find(Enrolment,
135
Enrolment.user_id == self.id,
136
(Enrolment.active == True) if justactive else True,
137
Enrolment.offering_id == Offering.id,
138
Offering.semester_id == Semester.id,
139
Offering.subject_id == Subject.id).order_by(
141
Desc(Semester.semester),
145
def _set_password(self, password):
149
self.passhash = unicode(User.hash_password(password))
150
password = property(fset=_set_password)
154
return Store.of(self).find(Subject,
155
Enrolment.user_id == self.id,
156
Enrolment.active == True,
157
Offering.id == Enrolment.offering_id,
158
Subject.id == Offering.subject_id).config(distinct=True)
160
# TODO: Invitations should be listed too?
161
def get_groups(self, offering=None):
163
ProjectGroupMembership.user_id == self.id,
164
ProjectGroup.id == ProjectGroupMembership.project_group_id,
168
ProjectSet.offering_id == offering.id,
169
ProjectGroup.project_set_id == ProjectSet.id,
171
return Store.of(self).find(ProjectGroup, *preds)
175
return self.get_groups()
178
def active_enrolments(self):
179
'''A sanely ordered list of the user's active enrolments.'''
180
return self._get_enrolments(True)
183
def enrolments(self):
184
'''A sanely ordered list of all of the user's enrolments.'''
185
return self._get_enrolments(False)
188
def hash_password(password):
189
return md5.md5(password).hexdigest()
192
def get_by_login(cls, store, login):
194
Get the User from the db associated with a given store and
197
return store.find(cls, cls.login == unicode(login)).one()
199
def get_permissions(self, user):
200
if user and user.rolenm == 'admin' or user is self:
201
return set(['view', 'edit'])
205
# SUBJECTS AND ENROLMENTS #
207
class Subject(Storm):
208
__storm_table__ = "subject"
210
id = Int(primary=True, name="subjectid")
211
code = Unicode(name="subj_code")
212
name = Unicode(name="subj_name")
213
short_name = Unicode(name="subj_short_name")
216
offerings = ReferenceSet(id, 'Offering.subject_id')
218
__init__ = _kwarg_init
221
return "<%s '%s'>" % (type(self).__name__, self.short_name)
223
def get_permissions(self, user):
227
if user.rolenm == 'admin':
231
class Semester(Storm):
232
__storm_table__ = "semester"
234
id = Int(primary=True, name="semesterid")
239
offerings = ReferenceSet(id, 'Offering.semester_id')
241
__init__ = _kwarg_init
244
return "<%s %s/%s>" % (type(self).__name__, self.year, self.semester)
246
class Offering(Storm):
247
__storm_table__ = "offering"
249
id = Int(primary=True, name="offeringid")
250
subject_id = Int(name="subject")
251
subject = Reference(subject_id, Subject.id)
252
semester_id = Int(name="semesterid")
253
semester = Reference(semester_id, Semester.id)
254
groups_student_permissions = Unicode()
256
enrolments = ReferenceSet(id, 'Enrolment.offering_id')
257
members = ReferenceSet(id,
258
'Enrolment.offering_id',
261
project_sets = ReferenceSet(id, 'ProjectSet.offering_id')
263
__init__ = _kwarg_init
266
return "<%s %r in %r>" % (type(self).__name__, self.subject,
269
def enrol(self, user):
270
'''Enrol a user in this offering.'''
271
# We'll get a horrible database constraint violation error if we try
272
# to add a second enrolment.
273
if Store.of(self).find(Enrolment,
274
Enrolment.user_id == user.id,
275
Enrolment.offering_id == self.id).count() == 1:
276
raise AlreadyEnrolledError()
278
e = Enrolment(user=user, offering=self, active=True)
279
self.enrolments.add(e)
281
class Enrolment(Storm):
282
__storm_table__ = "enrolment"
283
__storm_primary__ = "user_id", "offering_id"
285
user_id = Int(name="loginid")
286
user = Reference(user_id, User.id)
287
offering_id = Int(name="offeringid")
288
offering = Reference(offering_id, Offering.id)
294
return Store.of(self).find(ProjectGroup,
295
ProjectSet.offering_id == self.offering.id,
296
ProjectGroup.project_set_id == ProjectSet.id,
297
ProjectGroupMembership.project_group_id == ProjectGroup.id,
298
ProjectGroupMembership.user_id == self.user.id)
300
__init__ = _kwarg_init
303
return "<%s %r in %r>" % (type(self).__name__, self.user,
306
class AlreadyEnrolledError(Exception):
311
class ProjectSet(Storm):
312
__storm_table__ = "project_set"
314
id = Int(name="projectsetid", primary=True)
315
offering_id = Int(name="offeringid")
316
offering = Reference(offering_id, Offering.id)
317
max_students_per_group = Int()
319
projects = ReferenceSet(id, 'Project.project_set_id')
320
project_groups = ReferenceSet(id, 'ProjectGroup.project_set_id')
322
__init__ = _kwarg_init
325
return "<%s %d in %r>" % (type(self).__name__, self.id,
328
class Project(Storm):
329
__storm_table__ = "project"
331
id = Int(name="projectid", primary=True)
334
project_set_id = Int(name="projectsetid")
335
project_set = Reference(project_set_id, ProjectSet.id)
336
deadline = DateTime()
338
__init__ = _kwarg_init
341
return "<%s '%s' in %r>" % (type(self).__name__, self.synopsis,
342
self.project_set.offering)
344
class ProjectGroup(Storm):
345
__storm_table__ = "project_group"
347
id = Int(name="groupid", primary=True)
348
name = Unicode(name="groupnm")
349
project_set_id = Int(name="projectsetid")
350
project_set = Reference(project_set_id, ProjectSet.id)
352
created_by_id = Int(name="createdby")
353
created_by = Reference(created_by_id, User.id)
356
members = ReferenceSet(id,
357
"ProjectGroupMembership.project_group_id",
358
"ProjectGroupMembership.user_id",
361
__init__ = _kwarg_init
364
return "<%s %s in %r>" % (type(self).__name__, self.name,
365
self.project_set.offering)
367
class ProjectGroupMembership(Storm):
368
__storm_table__ = "group_member"
369
__storm_primary__ = "user_id", "project_group_id"
371
user_id = Int(name="loginid")
372
user = Reference(user_id, User.id)
373
project_group_id = Int(name="groupid")
374
project_group = Reference(project_group_id, ProjectGroup.id)
376
__init__ = _kwarg_init
379
return "<%s %r in %r>" % (type(self).__name__, self.user,
382
# WORKSHEETS AND EXERCISES #
384
class Exercise(Storm):
385
# Note: Table "problem" is called "Exercise" in the Object layer, since
386
# it's called that everywhere else.
387
__storm_table__ = "problem"
389
id = Int(primary=True, name="problemid")
390
name = Unicode(name="identifier")
393
worksheets = ReferenceSet(id,
394
'WorksheetExercise.exercise_id',
395
'WorksheetExercise.worksheet_id',
399
__init__ = _kwarg_init
402
return "<%s %s>" % (type(self).__name__, self.name)
405
def get_by_name(cls, store, name):
407
Get the Exercise from the db associated with a given store and name.
408
If the exercise is not in the database, creates it and inserts it
411
ex = store.find(cls, cls.name == unicode(name)).one()
414
ex = Exercise(name=unicode(name))
419
class Worksheet(Storm):
420
__storm_table__ = "worksheet"
422
id = Int(primary=True, name="worksheetid")
423
# XXX subject is not linked to a Subject object. This is a property of
424
# the database, and will be refactored.
426
name = Unicode(name="identifier")
430
exercises = ReferenceSet(id,
431
'WorksheetExercise.worksheet_id',
432
'WorksheetExercise.exercise_id',
434
# Use worksheet_exercises to get access to the WorksheetExercise objects
435
# binding worksheets to exercises. This is required to access the
437
worksheet_exercises = ReferenceSet(id,
438
'WorksheetExercise.worksheet_id')
440
__init__ = _kwarg_init
443
return "<%s %s>" % (type(self).__name__, self.name)
445
# XXX Refactor this - make it an instance method of Subject rather than a
446
# class method of Worksheet. Can't do that now because Subject isn't
447
# linked referentially to the Worksheet.
449
def get_by_name(cls, store, subjectname, worksheetname):
451
Get the Worksheet from the db associated with a given store, subject
452
name and worksheet name.
454
return store.find(cls, cls.subject == unicode(subjectname),
455
cls.name == unicode(worksheetname)).one()
457
def remove_all_exercises(self, store):
459
Remove all exercises from this worksheet.
460
This does not delete the exercises themselves. It just removes them
463
store.find(WorksheetExercise,
464
WorksheetExercise.worksheet == self).remove()
466
class WorksheetExercise(Storm):
467
__storm_table__ = "worksheet_problem"
468
__storm_primary__ = "worksheet_id", "exercise_id"
470
worksheet_id = Int(name="worksheetid")
471
worksheet = Reference(worksheet_id, Worksheet.id)
472
exercise_id = Int(name="problemid")
473
exercise = Reference(exercise_id, Exercise.id)
476
__init__ = _kwarg_init
479
return "<%s %s in %s>" % (type(self).__name__, self.exercise.name,
482
class ExerciseSave(Storm):
484
Represents a potential solution to an exercise that a user has submitted
485
to the server for storage.
486
A basic ExerciseSave is just the current saved text for this exercise for
487
this user (doesn't count towards their attempts).
488
ExerciseSave may be extended with additional semantics (such as
491
__storm_table__ = "problem_save"
492
__storm_primary__ = "exercise_id", "user_id", "date"
494
exercise_id = Int(name="problemid")
495
exercise = Reference(exercise_id, Exercise.id)
496
user_id = Int(name="loginid")
497
user = Reference(user_id, User.id)
501
__init__ = _kwarg_init
504
return "<%s %s by %s at %s>" % (type(self).__name__,
505
self.exercise.name, self.user.login, self.date.strftime("%c"))
507
class ExerciseAttempt(ExerciseSave):
509
An ExerciseAttempt is a special case of an ExerciseSave. Like an
510
ExerciseSave, it constitutes exercise solution data that the user has
511
submitted to the server for storage.
512
In addition, it contains additional information about the submission.
513
complete - True if this submission was successful, rendering this exercise
514
complete for this user.
515
active - True if this submission is "active" (usually true). Submissions
516
may be de-activated by privileged users for special reasons, and then
517
they won't count (either as a penalty or success), but will still be
520
__storm_table__ = "problem_attempt"
521
__storm_primary__ = "exercise_id", "user_id", "date"
523
# The "text" field is the same but has a different name in the DB table
525
text = Unicode(name="attempt")
529
def get_permissions(self, user):
530
return set(['view']) if user is self.user else set()
added
removed
ivle/database.py
