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')
243
members = ReferenceSet(id,
244
'Enrolment.offering_id',
247
project_sets = ReferenceSet(id, 'ProjectSet.offering_id')
249
__init__ = _kwarg_init
252
return "<%s %r in %r>" % (type(self).__name__, self.subject,
255
def enrol(self, user):
256
'''Enrol a user in this offering.'''
257
# We'll get a horrible database constraint violation error if we try
258
# to add a second enrolment.
259
if Store.of(self).find(Enrolment,
260
Enrolment.user_id == user.id,
261
Enrolment.offering_id == self.id).count() == 1:
262
raise AlreadyEnrolledError()
264
e = Enrolment(user=user, offering=self, active=True)
265
self.enrolments.add(e)
267
class Enrolment(Storm):
268
__storm_table__ = "enrolment"
269
__storm_primary__ = "user_id", "offering_id"
271
user_id = Int(name="loginid")
272
user = Reference(user_id, User.id)
273
offering_id = Int(name="offeringid")
274
offering = Reference(offering_id, Offering.id)
280
return Store.of(self).find(ProjectGroup,
281
ProjectSet.offering_id == self.offering.id,
282
ProjectGroup.project_set_id == ProjectSet.id,
283
ProjectGroupMembership.project_group_id == ProjectGroup.id,
284
ProjectGroupMembership.user_id == self.user.id)
286
__init__ = _kwarg_init
289
return "<%s %r in %r>" % (type(self).__name__, self.user,
292
class AlreadyEnrolledError(Exception):
297
class ProjectSet(Storm):
298
__storm_table__ = "project_set"
300
id = Int(name="projectsetid", primary=True)
301
offering_id = Int(name="offeringid")
302
offering = Reference(offering_id, Offering.id)
303
max_students_per_group = Int()
305
projects = ReferenceSet(id, 'Project.project_set_id')
306
project_groups = ReferenceSet(id, 'ProjectGroup.project_set_id')
308
__init__ = _kwarg_init
311
return "<%s %d in %r>" % (type(self).__name__, self.id,
314
class Project(Storm):
315
__storm_table__ = "project"
317
id = Int(name="projectid", primary=True)
320
project_set_id = Int(name="projectsetid")
321
project_set = Reference(project_set_id, ProjectSet.id)
322
deadline = DateTime()
324
__init__ = _kwarg_init
327
return "<%s '%s' in %r>" % (type(self).__name__, self.synopsis,
328
self.project_set.offering)
330
class ProjectGroup(Storm):
331
__storm_table__ = "project_group"
333
id = Int(name="groupid", primary=True)
334
name = Unicode(name="groupnm")
335
project_set_id = Int(name="projectsetid")
336
project_set = Reference(project_set_id, ProjectSet.id)
338
created_by_id = Int(name="createdby")
339
created_by = Reference(created_by_id, User.id)
342
members = ReferenceSet(id,
343
"ProjectGroupMembership.project_group_id",
344
"ProjectGroupMembership.user_id",
347
__init__ = _kwarg_init
350
return "<%s %s in %r>" % (type(self).__name__, self.name,
351
self.project_set.offering)
353
class ProjectGroupMembership(Storm):
354
__storm_table__ = "group_member"
355
__storm_primary__ = "user_id", "project_group_id"
357
user_id = Int(name="loginid")
358
user = Reference(user_id, User.id)
359
project_group_id = Int(name="groupid")
360
project_group = Reference(project_group_id, ProjectGroup.id)
362
__init__ = _kwarg_init
365
return "<%s %r in %r>" % (type(self).__name__, self.user,
368
# WORKSHEETS AND EXERCISES #
370
class Exercise(Storm):
371
# Note: Table "problem" is called "Exercise" in the Object layer, since
372
# it's called that everywhere else.
373
__storm_table__ = "problem"
375
id = Int(primary=True, name="problemid")
376
name = Unicode(name="identifier")
379
worksheets = ReferenceSet(id,
380
'WorksheetExercise.exercise_id',
381
'WorksheetExercise.worksheet_id',
385
__init__ = _kwarg_init
388
return "<%s %s>" % (type(self).__name__, self.name)
391
def get_by_name(cls, store, name):
393
Get the Exercise from the db associated with a given store and name.
394
If the exercise is not in the database, creates it and inserts it
397
ex = store.find(cls, cls.name == unicode(name)).one()
400
ex = Exercise(name=unicode(name))
405
class Worksheet(Storm):
406
__storm_table__ = "worksheet"
408
id = Int(primary=True, name="worksheetid")
409
# XXX subject is not linked to a Subject object. This is a property of
410
# the database, and will be refactored.
412
name = Unicode(name="identifier")
416
exercises = ReferenceSet(id,
417
'WorksheetExercise.worksheet_id',
418
'WorksheetExercise.exercise_id',
420
# Use worksheet_exercises to get access to the WorksheetExercise objects
421
# binding worksheets to exercises. This is required to access the
423
worksheet_exercises = ReferenceSet(id,
424
'WorksheetExercise.worksheet_id')
426
__init__ = _kwarg_init
429
return "<%s %s>" % (type(self).__name__, self.name)
431
# XXX Refactor this - make it an instance method of Subject rather than a
432
# class method of Worksheet. Can't do that now because Subject isn't
433
# linked referentially to the Worksheet.
435
def get_by_name(cls, store, subjectname, worksheetname):
437
Get the Worksheet from the db associated with a given store, subject
438
name and worksheet name.
440
return store.find(cls, cls.subject == unicode(subjectname),
441
cls.name == unicode(worksheetname)).one()
443
def remove_all_exercises(self, store):
445
Remove all exercises from this worksheet.
446
This does not delete the exercises themselves. It just removes them
449
store.find(WorksheetExercise,
450
WorksheetExercise.worksheet == self).remove()
452
class WorksheetExercise(Storm):
453
__storm_table__ = "worksheet_problem"
454
__storm_primary__ = "worksheet_id", "exercise_id"
456
worksheet_id = Int(name="worksheetid")
457
worksheet = Reference(worksheet_id, Worksheet.id)
458
exercise_id = Int(name="problemid")
459
exercise = Reference(exercise_id, Exercise.id)
462
__init__ = _kwarg_init
465
return "<%s %s in %s>" % (type(self).__name__, self.exercise.name,
468
class ExerciseSave(Storm):
470
Represents a potential solution to an exercise that a user has submitted
471
to the server for storage.
472
A basic ExerciseSave is just the current saved text for this exercise for
473
this user (doesn't count towards their attempts).
474
ExerciseSave may be extended with additional semantics (such as
477
__storm_table__ = "problem_save"
478
__storm_primary__ = "exercise_id", "user_id", "date"
480
exercise_id = Int(name="problemid")
481
exercise = Reference(exercise_id, Exercise.id)
482
user_id = Int(name="loginid")
483
user = Reference(user_id, User.id)
487
__init__ = _kwarg_init
490
return "<%s %s by %s at %s>" % (type(self).__name__,
491
self.exercise.name, self.user.login, self.date.strftime("%c"))
493
class ExerciseAttempt(ExerciseSave):
495
An ExerciseAttempt is a special case of an ExerciseSave. Like an
496
ExerciseSave, it constitutes exercise solution data that the user has
497
submitted to the server for storage.
498
In addition, it contains additional information about the submission.
499
complete - True if this submission was successful, rendering this exercise
500
complete for this user.
501
active - True if this submission is "active" (usually true). Submissions
502
may be de-activated by privileged users for special reasons, and then
503
they won't count (either as a penalty or success), but will still be
506
__storm_table__ = "problem_attempt"
507
__storm_primary__ = "exercise_id", "user_id", "date"
509
# The "text" field is the same but has a different name in the DB table
511
text = Unicode(name="attempt")
added
removed
ivle/database.py
