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
# SUBJECTS AND ENROLMENTS #
201
class Subject(Storm):
202
__storm_table__ = "subject"
204
id = Int(primary=True, name="subjectid")
205
code = Unicode(name="subj_code")
206
name = Unicode(name="subj_name")
207
short_name = Unicode(name="subj_short_name")
210
offerings = ReferenceSet(id, 'Offering.subject_id')
212
__init__ = _kwarg_init
215
return "<%s '%s'>" % (type(self).__name__, self.short_name)
217
class Semester(Storm):
218
__storm_table__ = "semester"
220
id = Int(primary=True, name="semesterid")
225
offerings = ReferenceSet(id, 'Offering.semester_id')
227
__init__ = _kwarg_init
230
return "<%s %s/%s>" % (type(self).__name__, self.year, self.semester)
232
class Offering(Storm):
233
__storm_table__ = "offering"
235
id = Int(primary=True, name="offeringid")
236
subject_id = Int(name="subject")
237
subject = Reference(subject_id, Subject.id)
238
semester_id = Int(name="semesterid")
239
semester = Reference(semester_id, Semester.id)
240
groups_student_permissions = Unicode()
242
enrolments = ReferenceSet(id, 'Enrolment.offering_id')
244
__init__ = _kwarg_init
247
return "<%s %r in %r>" % (type(self).__name__, self.subject,
250
def enrol(self, user):
251
'''Enrol a user in this offering.'''
252
# We'll get a horrible database constraint violation error if we try
253
# to add a second enrolment.
254
if Store.of(self).find(Enrolment,
255
Enrolment.user_id == user.id,
256
Enrolment.offering_id == self.id).count() == 1:
257
raise AlreadyEnrolledError()
259
e = Enrolment(user=user, offering=self, active=True)
260
self.enrolments.add(e)
262
class Enrolment(Storm):
263
__storm_table__ = "enrolment"
264
__storm_primary__ = "user_id", "offering_id"
266
user_id = Int(name="loginid")
267
user = Reference(user_id, User.id)
268
offering_id = Int(name="offeringid")
269
offering = Reference(offering_id, Offering.id)
273
__init__ = _kwarg_init
276
return "<%s %r in %r>" % (type(self).__name__, self.user,
279
class AlreadyEnrolledError(Exception):
284
class ProjectSet(Storm):
285
__storm_table__ = "project_set"
287
id = Int(name="projectsetid", primary=True)
288
offering_id = Int(name="offeringid")
289
offering = Reference(offering_id, Offering.id)
290
max_students_per_group = Int()
292
__init__ = _kwarg_init
295
return "<%s %d in %r>" % (type(self).__name__, self.id,
298
class Project(Storm):
299
__storm_table__ = "project"
301
id = Int(name="projectid", primary=True)
304
project_set_id = Int(name="projectsetid")
305
project_set = Reference(project_set_id, ProjectSet.id)
306
deadline = DateTime()
308
__init__ = _kwarg_init
311
return "<%s '%s' in %r>" % (type(self).__name__, self.synopsis,
312
self.project_set.offering)
314
class ProjectGroup(Storm):
315
__storm_table__ = "project_group"
317
id = Int(name="groupid", primary=True)
318
name = Unicode(name="groupnm")
319
project_set_id = Int(name="projectsetid")
320
project_set = Reference(project_set_id, ProjectSet.id)
322
created_by_id = Int(name="createdby")
323
created_by = Reference(created_by_id, User.id)
326
__init__ = _kwarg_init
329
return "<%s %s in %r>" % (type(self).__name__, self.name,
330
self.project_set.offering)
334
return Store.of(self).find(User,
335
ProjectGroupMembership.project_group_id == self.id,
336
User.id == ProjectGroupMembership.user_id)
338
class ProjectGroupMembership(Storm):
339
__storm_table__ = "group_member"
340
__storm_primary__ = "user_id", "project_group_id"
342
user_id = Int(name="loginid")
343
user = Reference(user_id, User.id)
344
project_group_id = Int(name="groupid")
345
project_group = Reference(project_group_id, ProjectGroup.id)
347
__init__ = _kwarg_init
350
return "<%s %r in %r>" % (type(self).__name__, self.user,
353
# WORKSHEETS AND EXERCISES #
355
class Exercise(Storm):
356
# Note: Table "problem" is called "Exercise" in the Object layer, since
357
# it's called that everywhere else.
358
__storm_table__ = "problem"
360
id = Int(primary=True, name="problemid")
361
name = Unicode(name="identifier")
364
worksheets = ReferenceSet(id,
365
'WorksheetExercise.exercise_id',
366
'WorksheetExercise.worksheet_id',
370
__init__ = _kwarg_init
373
return "<%s %s>" % (type(self).__name__, self.name)
376
def get_by_name(cls, store, name):
378
Get the Exercise from the db associated with a given store and name.
379
If the exercise is not in the database, creates it and inserts it
382
ex = store.find(cls, cls.name == unicode(name)).one()
385
ex = Exercise(name=unicode(name))
390
class Worksheet(Storm):
391
__storm_table__ = "worksheet"
393
id = Int(primary=True, name="worksheetid")
394
# XXX subject is not linked to a Subject object. This is a property of
395
# the database, and will be refactored.
397
name = Unicode(name="identifier")
401
exercises = ReferenceSet(id,
402
'WorksheetExercise.worksheet_id',
403
'WorksheetExercise.exercise_id',
405
# Use worksheet_exercises to get access to the WorksheetExercise objects
406
# binding worksheets to exercises. This is required to access the
408
worksheet_exercises = ReferenceSet(id,
409
'WorksheetExercise.worksheet_id')
411
__init__ = _kwarg_init
414
return "<%s %s>" % (type(self).__name__, self.name)
416
# XXX Refactor this - make it an instance method of Subject rather than a
417
# class method of Worksheet. Can't do that now because Subject isn't
418
# linked referentially to the Worksheet.
420
def get_by_name(cls, store, subjectname, worksheetname):
422
Get the Worksheet from the db associated with a given store, subject
423
name and worksheet name.
425
return store.find(cls, cls.subject == unicode(subjectname),
426
cls.name == unicode(worksheetname)).one()
428
def remove_all_exercises(self, store):
430
Remove all exercises from this worksheet.
431
This does not delete the exercises themselves. It just removes them
434
store.find(WorksheetExercise,
435
WorksheetExercise.worksheet == self).remove()
437
class WorksheetExercise(Storm):
438
__storm_table__ = "worksheet_problem"
439
__storm_primary__ = "worksheet_id", "exercise_id"
441
worksheet_id = Int(name="worksheetid")
442
worksheet = Reference(worksheet_id, Worksheet.id)
443
exercise_id = Int(name="problemid")
444
exercise = Reference(exercise_id, Exercise.id)
447
__init__ = _kwarg_init
450
return "<%s %s in %s>" % (type(self).__name__, self.exercise.name,
453
class ExerciseSave(Storm):
455
Represents a potential solution to an exercise that a user has submitted
456
to the server for storage.
457
A basic ExerciseSave is just the current saved text for this exercise for
458
this user (doesn't count towards their attempts).
459
ExerciseSave may be extended with additional semantics (such as
462
__storm_table__ = "problem_save"
463
__storm_primary__ = "exercise_id", "user_id", "date"
465
exercise_id = Int(name="problemid")
466
exercise = Reference(exercise_id, Exercise.id)
467
user_id = Int(name="loginid")
468
user = Reference(user_id, User.id)
472
__init__ = _kwarg_init
475
return "<%s %s by %s at %s>" % (type(self).__name__,
476
self.exercise.name, self.user.login, self.date.strftime("%c"))
478
class ExerciseAttempt(ExerciseSave):
480
An ExerciseAttempt is a special case of an ExerciseSave. Like an
481
ExerciseSave, it constitutes exercise solution data that the user has
482
submitted to the server for storage.
483
In addition, it contains additional information about the submission.
484
complete - True if this submission was successful, rendering this exercise
485
complete for this user.
486
active - True if this submission is "active" (usually true). Submissions
487
may be de-activated by privileged users for special reasons, and then
488
they won't count (either as a penalty or success), but will still be
491
__storm_table__ = "problem_attempt"
492
__storm_primary__ = "exercise_id", "user_id", "date"
494
# The "text" field is the same but has a different name in the DB table
496
text = Unicode(name="attempt")
added
removed
ivle/database.py
