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.
59
clusterstr += ivle.conf.db_user
60
if ivle.conf.db_password:
61
clusterstr += ':' + ivle.conf.db_password
64
host = ivle.conf.db_host or 'localhost'
65
port = ivle.conf.db_port or 5432
67
clusterstr += '%s:%d' % (host, port)
69
return "postgres://%s/%s" % (clusterstr, ivle.conf.db_dbname)
73
Open a database connection and transaction. Return a storm.store.Store
74
instance connected to the configured IVLE database.
76
return Store(create_database(get_conn_string()))
82
Represents an IVLE user.
84
__storm_table__ = "login"
86
id = Int(primary=True, name="loginid")
95
last_login = DateTime()
103
if self.rolenm is None:
105
return ivle.caps.Role(self.rolenm)
106
def _set_role(self, value):
107
if not isinstance(value, ivle.caps.Role):
108
raise TypeError("role must be an ivle.caps.Role")
109
self.rolenm = unicode(value)
110
role = property(_get_role, _set_role)
112
__init__ = _kwarg_init
115
return "<%s '%s'>" % (type(self).__name__, self.login)
117
def authenticate(self, password):
118
"""Validate a given password against this user.
120
Returns True if the given password matches the password hash for this
121
User, False if it doesn't match, and None if there is no hash for the
124
if self.passhash is None:
126
return self.hash_password(password) == self.passhash
128
def hasCap(self, capability):
129
"""Given a capability (which is a Role object), returns True if this
130
User has that capability, False otherwise.
132
return self.role.hasCap(capability)
135
def password_expired(self):
136
fieldval = self.pass_exp
137
return fieldval is not None and datetime.datetime.now() > fieldval
140
def account_expired(self):
141
fieldval = self.acct_exp
142
return fieldval is not None and datetime.datetime.now() > fieldval
146
return self.state == 'enabled' and not self.account_expired
148
def _get_enrolments(self, justactive):
149
return Store.of(self).find(Enrolment,
150
Enrolment.user_id == self.id,
151
(Enrolment.active == True) if justactive else True,
152
Enrolment.offering_id == Offering.id,
153
Offering.semester_id == Semester.id,
154
Offering.subject_id == Subject.id).order_by(
156
Desc(Semester.semester),
160
def _set_password(self, password):
164
self.passhash = unicode(User.hash_password(password))
165
password = property(fset=_set_password)
169
return Store.of(self).find(Subject,
170
Enrolment.user_id == self.id,
171
Enrolment.active == True,
172
Offering.id == Enrolment.offering_id,
173
Subject.id == Offering.subject_id).config(distinct=True)
175
# TODO: Invitations should be listed too?
176
def get_groups(self, offering=None):
178
ProjectGroupMembership.user_id == self.id,
179
ProjectGroup.id == ProjectGroupMembership.project_group_id,
183
ProjectSet.offering_id == offering.id,
184
ProjectGroup.project_set_id == ProjectSet.id,
186
return Store.of(self).find(ProjectGroup, *preds)
190
return self.get_groups()
193
def active_enrolments(self):
194
'''A sanely ordered list of the user's active enrolments.'''
195
return self._get_enrolments(True)
198
def enrolments(self):
199
'''A sanely ordered list of all of the user's enrolments.'''
200
return self._get_enrolments(False)
203
def hash_password(password):
204
return md5.md5(password).hexdigest()
207
def get_by_login(cls, store, login):
209
Get the User from the db associated with a given store and
212
return store.find(cls, cls.login == unicode(login)).one()
214
def get_permissions(self, user):
215
if user and user.rolenm == 'admin' or user is self:
216
return set(['view', 'edit'])
220
# SUBJECTS AND ENROLMENTS #
222
class Subject(Storm):
223
__storm_table__ = "subject"
225
id = Int(primary=True, name="subjectid")
226
code = Unicode(name="subj_code")
227
name = Unicode(name="subj_name")
228
short_name = Unicode(name="subj_short_name")
231
offerings = ReferenceSet(id, 'Offering.subject_id')
233
__init__ = _kwarg_init
236
return "<%s '%s'>" % (type(self).__name__, self.short_name)
238
def get_permissions(self, user):
242
if user.rolenm == 'admin':
246
class Semester(Storm):
247
__storm_table__ = "semester"
249
id = Int(primary=True, name="semesterid")
254
offerings = ReferenceSet(id, 'Offering.semester_id')
256
__init__ = _kwarg_init
259
return "<%s %s/%s>" % (type(self).__name__, self.year, self.semester)
261
class Offering(Storm):
262
__storm_table__ = "offering"
264
id = Int(primary=True, name="offeringid")
265
subject_id = Int(name="subject")
266
subject = Reference(subject_id, Subject.id)
267
semester_id = Int(name="semesterid")
268
semester = Reference(semester_id, Semester.id)
269
groups_student_permissions = Unicode()
271
enrolments = ReferenceSet(id, 'Enrolment.offering_id')
272
members = ReferenceSet(id,
273
'Enrolment.offering_id',
276
project_sets = ReferenceSet(id, 'ProjectSet.offering_id')
278
worksheets = ReferenceSet(id, 'Worksheet.offering_id')
280
__init__ = _kwarg_init
283
return "<%s %r in %r>" % (type(self).__name__, self.subject,
286
def enrol(self, user):
287
'''Enrol a user in this offering.'''
288
# We'll get a horrible database constraint violation error if we try
289
# to add a second enrolment.
290
if Store.of(self).find(Enrolment,
291
Enrolment.user_id == user.id,
292
Enrolment.offering_id == self.id).count() == 1:
293
raise AlreadyEnrolledError()
295
e = Enrolment(user=user, offering=self, active=True)
296
self.enrolments.add(e)
298
def get_permissions(self, user):
302
if user.rolenm == 'admin':
306
class Enrolment(Storm):
307
__storm_table__ = "enrolment"
308
__storm_primary__ = "user_id", "offering_id"
310
user_id = Int(name="loginid")
311
user = Reference(user_id, User.id)
312
offering_id = Int(name="offeringid")
313
offering = Reference(offering_id, Offering.id)
319
return Store.of(self).find(ProjectGroup,
320
ProjectSet.offering_id == self.offering.id,
321
ProjectGroup.project_set_id == ProjectSet.id,
322
ProjectGroupMembership.project_group_id == ProjectGroup.id,
323
ProjectGroupMembership.user_id == self.user.id)
325
__init__ = _kwarg_init
328
return "<%s %r in %r>" % (type(self).__name__, self.user,
331
class AlreadyEnrolledError(Exception):
336
class ProjectSet(Storm):
337
__storm_table__ = "project_set"
339
id = Int(name="projectsetid", primary=True)
340
offering_id = Int(name="offeringid")
341
offering = Reference(offering_id, Offering.id)
342
max_students_per_group = Int()
344
projects = ReferenceSet(id, 'Project.project_set_id')
345
project_groups = ReferenceSet(id, 'ProjectGroup.project_set_id')
347
__init__ = _kwarg_init
350
return "<%s %d in %r>" % (type(self).__name__, self.id,
353
class Project(Storm):
354
__storm_table__ = "project"
356
id = Int(name="projectid", primary=True)
359
project_set_id = Int(name="projectsetid")
360
project_set = Reference(project_set_id, ProjectSet.id)
361
deadline = DateTime()
363
__init__ = _kwarg_init
366
return "<%s '%s' in %r>" % (type(self).__name__, self.synopsis,
367
self.project_set.offering)
369
class ProjectGroup(Storm):
370
__storm_table__ = "project_group"
372
id = Int(name="groupid", primary=True)
373
name = Unicode(name="groupnm")
374
project_set_id = Int(name="projectsetid")
375
project_set = Reference(project_set_id, ProjectSet.id)
377
created_by_id = Int(name="createdby")
378
created_by = Reference(created_by_id, User.id)
381
members = ReferenceSet(id,
382
"ProjectGroupMembership.project_group_id",
383
"ProjectGroupMembership.user_id",
386
__init__ = _kwarg_init
389
return "<%s %s in %r>" % (type(self).__name__, self.name,
390
self.project_set.offering)
392
class ProjectGroupMembership(Storm):
393
__storm_table__ = "group_member"
394
__storm_primary__ = "user_id", "project_group_id"
396
user_id = Int(name="loginid")
397
user = Reference(user_id, User.id)
398
project_group_id = Int(name="groupid")
399
project_group = Reference(project_group_id, ProjectGroup.id)
401
__init__ = _kwarg_init
404
return "<%s %r in %r>" % (type(self).__name__, self.user,
407
# WORKSHEETS AND EXERCISES #
409
class Exercise(Storm):
410
# Note: Table "problem" is called "Exercise" in the Object layer, since
411
# it's called that everywhere else.
412
__storm_table__ = "problem"
413
#TODO: Add in a field for the user-friendly identifier
414
id = Unicode(primary=True, name="identifier")
416
description = Unicode()
422
worksheets = ReferenceSet(id,
423
'WorksheetExercise.exercise_id',
424
'WorksheetExercise.worksheet_id',
428
test_suites = ReferenceSet(id, 'TestSuite.exercise_id')
430
__init__ = _kwarg_init
433
return "<%s %s>" % (type(self).__name__, self.name)
436
class Worksheet(Storm):
437
__storm_table__ = "worksheet"
439
id = Int(primary=True, name="worksheetid")
440
# XXX subject is not linked to a Subject object. This is a property of
441
# the database, and will be refactored.
442
offering_id = Int(name="offeringid")
443
name = Unicode(name="identifier")
447
attempts = ReferenceSet(id, "ExerciseAttempt.worksheetid")
448
offering = Reference(offering_id, 'Offering.id')
450
exercises = ReferenceSet(id,
451
'WorksheetExercise.worksheet_id',
452
'WorksheetExercise.exercise_id',
454
# Use worksheet_exercises to get access to the WorksheetExercise objects
455
# binding worksheets to exercises. This is required to access the
457
worksheet_exercises = ReferenceSet(id,
458
'WorksheetExercise.worksheet_id')
461
__init__ = _kwarg_init
464
return "<%s %s>" % (type(self).__name__, self.name)
466
# XXX Refactor this - make it an instance method of Subject rather than a
467
# class method of Worksheet. Can't do that now because Subject isn't
468
# linked referentially to the Worksheet.
470
def get_by_name(cls, store, subjectname, worksheetname):
472
Get the Worksheet from the db associated with a given store, subject
473
name and worksheet name.
475
return store.find(cls, cls.subject == unicode(subjectname),
476
cls.name == unicode(worksheetname)).one()
478
def remove_all_exercises(self, store):
480
Remove all exercises from this worksheet.
481
This does not delete the exercises themselves. It just removes them
484
store.find(WorksheetExercise,
485
WorksheetExercise.worksheet == self).remove()
487
def get_permissions(self, user):
488
return self.offering.get_permissions(user)
490
class WorksheetExercise(Storm):
491
__storm_table__ = "worksheet_problem"
492
__storm_primary__ = "worksheet_id", "exercise_id"
494
worksheet_id = Int(name="worksheetid")
495
worksheet = Reference(worksheet_id, Worksheet.id)
496
exercise_id = Unicode(name="problemid")
497
exercise = Reference(exercise_id, Exercise.id)
500
__init__ = _kwarg_init
503
return "<%s %s in %s>" % (type(self).__name__, self.exercise.name,
506
class ExerciseSave(Storm):
508
Represents a potential solution to an exercise that a user has submitted
509
to the server for storage.
510
A basic ExerciseSave is just the current saved text for this exercise for
511
this user (doesn't count towards their attempts).
512
ExerciseSave may be extended with additional semantics (such as
515
__storm_table__ = "problem_save"
516
__storm_primary__ = "exercise_id", "user_id", "date"
518
exercise_id = Unicode(name="problemid")
519
exercise = Reference(exercise_id, Exercise.id)
520
user_id = Int(name="loginid")
521
user = Reference(user_id, User.id)
525
worksheet = Reference(worksheetid, Worksheet.id)
527
__init__ = _kwarg_init
530
return "<%s %s by %s at %s>" % (type(self).__name__,
531
self.exercise.name, self.user.login, self.date.strftime("%c"))
533
class ExerciseAttempt(ExerciseSave):
535
An ExerciseAttempt is a special case of an ExerciseSave. Like an
536
ExerciseSave, it constitutes exercise solution data that the user has
537
submitted to the server for storage.
538
In addition, it contains additional information about the submission.
539
complete - True if this submission was successful, rendering this exercise
540
complete for this user.
541
active - True if this submission is "active" (usually true). Submissions
542
may be de-activated by privileged users for special reasons, and then
543
they won't count (either as a penalty or success), but will still be
546
__storm_table__ = "problem_attempt"
547
__storm_primary__ = "exercise_id", "user_id", "date"
549
# The "text" field is the same but has a different name in the DB table
551
text = Unicode(name="attempt")
555
def get_permissions(self, user):
556
return set(['view']) if user is self.user else set()
558
class TestSuite(Storm):
559
"""A Testsuite acts as a container for the test cases of an exercise."""
560
__storm_table__ = "test_suite"
561
__storm_primary__ = "exercise_id", "suiteid"
564
exercise_id = Unicode(name="problemid")
565
description = Unicode()
569
exercise = Reference(exercise_id, Exercise.id)
570
test_cases = ReferenceSet(suiteid, 'TestCase.suiteid')
571
variables = ReferenceSet(suiteid, 'TestSuiteVar.suiteid')
573
class TestCase(Storm):
574
"""A TestCase is a member of a TestSuite.
576
It contains the data necessary to check if an exercise is correct"""
577
__storm_table__ = "test_case"
578
__storm_primary__ = "testid", "suiteid"
582
suite = Reference(suiteid, "TestSuite.suiteid")
585
test_default = Unicode()
588
parts = ReferenceSet(testid, "TestCasePart.testid")
590
__init__ = _kwarg_init
592
class TestSuiteVar(Storm):
593
"""A container for the arguments of a Test Suite"""
594
__storm_table__ = "suite_variables"
595
__storm_primary__ = "varid"
600
var_value = Unicode()
604
suite = Reference(suiteid, "TestSuite.suiteid")
606
__init__ = _kwarg_init
608
class TestCasePart(Storm):
609
"""A container for the test elements of a Test Case"""
610
__storm_table__ = "test_case_parts"
611
__storm_primary__ = "partid"
616
part_type = Unicode()
617
test_type = Unicode()
621
test = Reference(testid, "TestCase.testid")
623
__init__ = _kwarg_init
added
removed
ivle/database.py
