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'
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
worksheets = ReferenceSet(id, 'Worksheet.offering_id')
265
__init__ = _kwarg_init
268
return "<%s %r in %r>" % (type(self).__name__, self.subject,
271
def enrol(self, user):
272
'''Enrol a user in this offering.'''
273
# We'll get a horrible database constraint violation error if we try
274
# to add a second enrolment.
275
if Store.of(self).find(Enrolment,
276
Enrolment.user_id == user.id,
277
Enrolment.offering_id == self.id).count() == 1:
278
raise AlreadyEnrolledError()
280
e = Enrolment(user=user, offering=self, active=True)
281
self.enrolments.add(e)
283
class Enrolment(Storm):
284
__storm_table__ = "enrolment"
285
__storm_primary__ = "user_id", "offering_id"
287
user_id = Int(name="loginid")
288
user = Reference(user_id, User.id)
289
offering_id = Int(name="offeringid")
290
offering = Reference(offering_id, Offering.id)
296
return Store.of(self).find(ProjectGroup,
297
ProjectSet.offering_id == self.offering.id,
298
ProjectGroup.project_set_id == ProjectSet.id,
299
ProjectGroupMembership.project_group_id == ProjectGroup.id,
300
ProjectGroupMembership.user_id == self.user.id)
302
__init__ = _kwarg_init
305
return "<%s %r in %r>" % (type(self).__name__, self.user,
308
class AlreadyEnrolledError(Exception):
313
class ProjectSet(Storm):
314
__storm_table__ = "project_set"
316
id = Int(name="projectsetid", primary=True)
317
offering_id = Int(name="offeringid")
318
offering = Reference(offering_id, Offering.id)
319
max_students_per_group = Int()
321
projects = ReferenceSet(id, 'Project.project_set_id')
322
project_groups = ReferenceSet(id, 'ProjectGroup.project_set_id')
324
__init__ = _kwarg_init
327
return "<%s %d in %r>" % (type(self).__name__, self.id,
330
class Project(Storm):
331
__storm_table__ = "project"
333
id = Int(name="projectid", primary=True)
336
project_set_id = Int(name="projectsetid")
337
project_set = Reference(project_set_id, ProjectSet.id)
338
deadline = DateTime()
340
__init__ = _kwarg_init
343
return "<%s '%s' in %r>" % (type(self).__name__, self.synopsis,
344
self.project_set.offering)
346
class ProjectGroup(Storm):
347
__storm_table__ = "project_group"
349
id = Int(name="groupid", primary=True)
350
name = Unicode(name="groupnm")
351
project_set_id = Int(name="projectsetid")
352
project_set = Reference(project_set_id, ProjectSet.id)
354
created_by_id = Int(name="createdby")
355
created_by = Reference(created_by_id, User.id)
358
members = ReferenceSet(id,
359
"ProjectGroupMembership.project_group_id",
360
"ProjectGroupMembership.user_id",
363
__init__ = _kwarg_init
366
return "<%s %s in %r>" % (type(self).__name__, self.name,
367
self.project_set.offering)
369
class ProjectGroupMembership(Storm):
370
__storm_table__ = "group_member"
371
__storm_primary__ = "user_id", "project_group_id"
373
user_id = Int(name="loginid")
374
user = Reference(user_id, User.id)
375
project_group_id = Int(name="groupid")
376
project_group = Reference(project_group_id, ProjectGroup.id)
378
__init__ = _kwarg_init
381
return "<%s %r in %r>" % (type(self).__name__, self.user,
384
# WORKSHEETS AND EXERCISES #
386
class Exercise(Storm):
387
# Note: Table "problem" is called "Exercise" in the Object layer, since
388
# it's called that everywhere else.
389
__storm_table__ = "problem"
390
#TODO: Add in a field for the user-friendly identifier
391
id = Unicode(primary=True, name="identifier")
393
description = Unicode()
399
worksheets = ReferenceSet(id,
400
'WorksheetExercise.exercise_id',
401
'WorksheetExercise.worksheet_id',
405
test_suites = ReferenceSet(id, 'TestSuite.exercise_id')
407
__init__ = _kwarg_init
410
return "<%s %s>" % (type(self).__name__, self.name)
413
def get_by_name(cls, store, name):
415
Get the Exercise from the db associated with a given store and name.
416
If the exercise is not in the database, creates it and inserts it
419
ex = store.find(cls, cls.name == unicode(name)).one()
422
ex = Exercise(name=unicode(name))
427
class Worksheet(Storm):
428
__storm_table__ = "worksheet"
430
id = Int(primary=True, name="worksheetid")
431
# XXX subject is not linked to a Subject object. This is a property of
432
# the database, and will be refactored.
434
offering_id = Int(name="offeringid")
435
name = Unicode(name="identifier")
439
offering = Reference (offeringid, 'Offering.id')
441
exercises = ReferenceSet(id,
442
'WorksheetExercise.worksheet_id',
443
'WorksheetExercise.exercise_id',
445
# Use worksheet_exercises to get access to the WorksheetExercise objects
446
# binding worksheets to exercises. This is required to access the
448
worksheet_exercises = ReferenceSet(id,
449
'WorksheetExercise.worksheet_id')
452
__init__ = _kwarg_init
455
return "<%s %s>" % (type(self).__name__, self.name)
457
# XXX Refactor this - make it an instance method of Subject rather than a
458
# class method of Worksheet. Can't do that now because Subject isn't
459
# linked referentially to the Worksheet.
461
def get_by_name(cls, store, subjectname, worksheetname):
463
Get the Worksheet from the db associated with a given store, subject
464
name and worksheet name.
466
return store.find(cls, cls.subject == unicode(subjectname),
467
cls.name == unicode(worksheetname)).one()
469
def remove_all_exercises(self, store):
471
Remove all exercises from this worksheet.
472
This does not delete the exercises themselves. It just removes them
475
store.find(WorksheetExercise,
476
WorksheetExercise.worksheet == self).remove()
478
class WorksheetExercise(Storm):
479
__storm_table__ = "worksheet_problem"
480
__storm_primary__ = "worksheet_id", "exercise_id"
482
worksheet_id = Int(name="worksheetid")
483
worksheet = Reference(worksheet_id, Worksheet.id)
484
exercise_id = Unicode(name="problemid")
485
exercise = Reference(exercise_id, Exercise.id)
488
__init__ = _kwarg_init
491
return "<%s %s in %s>" % (type(self).__name__, self.exercise.name,
494
class ExerciseSave(Storm):
496
Represents a potential solution to an exercise that a user has submitted
497
to the server for storage.
498
A basic ExerciseSave is just the current saved text for this exercise for
499
this user (doesn't count towards their attempts).
500
ExerciseSave may be extended with additional semantics (such as
503
__storm_table__ = "problem_save"
504
__storm_primary__ = "exercise_id", "user_id", "date"
506
exercise_id = Unicode(name="problemid")
507
exercise = Reference(exercise_id, Exercise.id)
508
user_id = Int(name="loginid")
509
user = Reference(user_id, User.id)
513
worksheet = Reference(worksheetid, Worksheet.id)
515
__init__ = _kwarg_init
518
return "<%s %s by %s at %s>" % (type(self).__name__,
519
self.exercise.name, self.user.login, self.date.strftime("%c"))
521
class ExerciseAttempt(ExerciseSave):
523
An ExerciseAttempt is a special case of an ExerciseSave. Like an
524
ExerciseSave, it constitutes exercise solution data that the user has
525
submitted to the server for storage.
526
In addition, it contains additional information about the submission.
527
complete - True if this submission was successful, rendering this exercise
528
complete for this user.
529
active - True if this submission is "active" (usually true). Submissions
530
may be de-activated by privileged users for special reasons, and then
531
they won't count (either as a penalty or success), but will still be
534
__storm_table__ = "problem_attempt"
535
__storm_primary__ = "exercise_id", "user_id", "date"
537
# The "text" field is the same but has a different name in the DB table
539
text = Unicode(name="attempt")
543
def get_permissions(self, user):
544
return set(['view']) if user is self.user else set()
546
class TestSuite(Storm):
547
"""A Testsuite acts as a container for the test cases of an exercise."""
548
__storm_table__ = "test_suite"
549
__storm_primary__ = "exercise_id", "suiteid"
552
exercise_id = Unicode(name="problemid")
553
exercise = Reference(exercise_id, Exercise.id)
554
test_cases = ReferenceSet(suiteid, 'TestCase.suiteid')
555
description = Unicode()
558
class TestCase(Storm):
559
"""A TestCase is a member of a TestSuite.
561
It contains the data necessary to check if an exercise is correct"""
562
__storm_table__ = "test_case"
563
__storm_primary__ = "testid", "suiteid"
567
suite = Reference(suiteid, TestSuite.suiteid)
571
code_type = Unicode()
576
__init__ = _kwarg_init
added
removed
ivle/database.py
