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
35
__all__ = ['get_store',
37
'Subject', 'Semester', 'Offering', 'Enrolment',
38
'ProjectSet', 'Project', 'ProjectGroup', 'ProjectGroupMembership',
39
'Exercise', 'Worksheet', 'WorksheetExercise',
40
'ExerciseSave', 'ExerciseAttempt',
41
'AlreadyEnrolledError', 'TestCase', 'TestSuite', 'TestSuiteVar'
44
def _kwarg_init(self, **kwargs):
45
for k,v in kwargs.items():
46
if k.startswith('_') or not hasattr(self.__class__, k):
47
raise TypeError("%s got an unexpected keyword argument '%s'"
48
% (self.__class__.__name__, k))
51
def get_conn_string():
53
Returns the Storm connection string, generated from the conf file.
58
clusterstr += ivle.conf.db_user
59
if ivle.conf.db_password:
60
clusterstr += ':' + ivle.conf.db_password
63
host = ivle.conf.db_host or 'localhost'
64
port = ivle.conf.db_port or 5432
66
clusterstr += '%s:%d' % (host, port)
68
return "postgres://%s/%s" % (clusterstr, ivle.conf.db_dbname)
72
Open a database connection and transaction. Return a storm.store.Store
73
instance connected to the configured IVLE database.
75
return Store(create_database(get_conn_string()))
81
Represents an IVLE user.
83
__storm_table__ = "login"
85
id = Int(primary=True, name="loginid")
94
last_login = DateTime()
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
118
def password_expired(self):
119
fieldval = self.pass_exp
120
return fieldval is not None and datetime.datetime.now() > fieldval
123
def account_expired(self):
124
fieldval = self.acct_exp
125
return fieldval is not None and datetime.datetime.now() > fieldval
129
return self.state == 'enabled' and not self.account_expired
131
def _get_enrolments(self, justactive):
132
return Store.of(self).find(Enrolment,
133
Enrolment.user_id == self.id,
134
(Enrolment.active == True) if justactive else True,
135
Enrolment.offering_id == Offering.id,
136
Offering.semester_id == Semester.id,
137
Offering.subject_id == Subject.id).order_by(
139
Desc(Semester.semester),
143
def _set_password(self, password):
147
self.passhash = unicode(User.hash_password(password))
148
password = property(fset=_set_password)
152
return Store.of(self).find(Subject,
153
Enrolment.user_id == self.id,
154
Enrolment.active == True,
155
Offering.id == Enrolment.offering_id,
156
Subject.id == Offering.subject_id).config(distinct=True)
158
# TODO: Invitations should be listed too?
159
def get_groups(self, offering=None):
161
ProjectGroupMembership.user_id == self.id,
162
ProjectGroup.id == ProjectGroupMembership.project_group_id,
166
ProjectSet.offering_id == offering.id,
167
ProjectGroup.project_set_id == ProjectSet.id,
169
return Store.of(self).find(ProjectGroup, *preds)
173
return self.get_groups()
176
def active_enrolments(self):
177
'''A sanely ordered list of the user's active enrolments.'''
178
return self._get_enrolments(True)
181
def enrolments(self):
182
'''A sanely ordered list of all of the user's enrolments.'''
183
return self._get_enrolments(False)
186
def hash_password(password):
187
return md5.md5(password).hexdigest()
190
def get_by_login(cls, store, login):
192
Get the User from the db associated with a given store and
195
return store.find(cls, cls.login == unicode(login)).one()
197
def get_permissions(self, user):
198
if user and user.admin or user is self:
199
return set(['view', 'edit'])
203
# SUBJECTS AND ENROLMENTS #
205
class Subject(Storm):
206
__storm_table__ = "subject"
208
id = Int(primary=True, name="subjectid")
209
code = Unicode(name="subj_code")
210
name = Unicode(name="subj_name")
211
short_name = Unicode(name="subj_short_name")
214
offerings = ReferenceSet(id, 'Offering.subject_id')
216
__init__ = _kwarg_init
219
return "<%s '%s'>" % (type(self).__name__, self.short_name)
221
def get_permissions(self, user):
229
class Semester(Storm):
230
__storm_table__ = "semester"
232
id = Int(primary=True, name="semesterid")
237
offerings = ReferenceSet(id, 'Offering.semester_id')
239
__init__ = _kwarg_init
242
return "<%s %s/%s>" % (type(self).__name__, self.year, self.semester)
244
class Offering(Storm):
245
__storm_table__ = "offering"
247
id = Int(primary=True, name="offeringid")
248
subject_id = Int(name="subject")
249
subject = Reference(subject_id, Subject.id)
250
semester_id = Int(name="semesterid")
251
semester = Reference(semester_id, Semester.id)
252
groups_student_permissions = Unicode()
254
enrolments = ReferenceSet(id, 'Enrolment.offering_id')
255
members = ReferenceSet(id,
256
'Enrolment.offering_id',
259
project_sets = ReferenceSet(id, 'ProjectSet.offering_id')
261
worksheets = ReferenceSet(id,
262
'Worksheet.offering_id',
263
order_by="Worksheet.seq_no"
266
__init__ = _kwarg_init
269
return "<%s %r in %r>" % (type(self).__name__, self.subject,
272
def enrol(self, user):
273
'''Enrol a user in this offering.'''
274
# We'll get a horrible database constraint violation error if we try
275
# to add a second enrolment.
276
if Store.of(self).find(Enrolment,
277
Enrolment.user_id == user.id,
278
Enrolment.offering_id == self.id).count() == 1:
279
raise AlreadyEnrolledError()
281
e = Enrolment(user=user, offering=self, active=True)
282
self.enrolments.add(e)
284
def get_permissions(self, user):
292
class Enrolment(Storm):
293
__storm_table__ = "enrolment"
294
__storm_primary__ = "user_id", "offering_id"
296
user_id = Int(name="loginid")
297
user = Reference(user_id, User.id)
298
offering_id = Int(name="offeringid")
299
offering = Reference(offering_id, Offering.id)
306
return Store.of(self).find(ProjectGroup,
307
ProjectSet.offering_id == self.offering.id,
308
ProjectGroup.project_set_id == ProjectSet.id,
309
ProjectGroupMembership.project_group_id == ProjectGroup.id,
310
ProjectGroupMembership.user_id == self.user.id)
312
__init__ = _kwarg_init
315
return "<%s %r in %r>" % (type(self).__name__, self.user,
318
class AlreadyEnrolledError(Exception):
323
class ProjectSet(Storm):
324
__storm_table__ = "project_set"
326
id = Int(name="projectsetid", primary=True)
327
offering_id = Int(name="offeringid")
328
offering = Reference(offering_id, Offering.id)
329
max_students_per_group = Int()
331
projects = ReferenceSet(id, 'Project.project_set_id')
332
project_groups = ReferenceSet(id, 'ProjectGroup.project_set_id')
334
__init__ = _kwarg_init
337
return "<%s %d in %r>" % (type(self).__name__, self.id,
340
class Project(Storm):
341
__storm_table__ = "project"
343
id = Int(name="projectid", primary=True)
346
project_set_id = Int(name="projectsetid")
347
project_set = Reference(project_set_id, ProjectSet.id)
348
deadline = DateTime()
350
__init__ = _kwarg_init
353
return "<%s '%s' in %r>" % (type(self).__name__, self.synopsis,
354
self.project_set.offering)
356
class ProjectGroup(Storm):
357
__storm_table__ = "project_group"
359
id = Int(name="groupid", primary=True)
360
name = Unicode(name="groupnm")
361
project_set_id = Int(name="projectsetid")
362
project_set = Reference(project_set_id, ProjectSet.id)
364
created_by_id = Int(name="createdby")
365
created_by = Reference(created_by_id, User.id)
368
members = ReferenceSet(id,
369
"ProjectGroupMembership.project_group_id",
370
"ProjectGroupMembership.user_id",
373
__init__ = _kwarg_init
376
return "<%s %s in %r>" % (type(self).__name__, self.name,
377
self.project_set.offering)
379
class ProjectGroupMembership(Storm):
380
__storm_table__ = "group_member"
381
__storm_primary__ = "user_id", "project_group_id"
383
user_id = Int(name="loginid")
384
user = Reference(user_id, User.id)
385
project_group_id = Int(name="groupid")
386
project_group = Reference(project_group_id, ProjectGroup.id)
388
__init__ = _kwarg_init
391
return "<%s %r in %r>" % (type(self).__name__, self.user,
394
# WORKSHEETS AND EXERCISES #
396
class Exercise(Storm):
397
__storm_table__ = "exercise"
398
id = Unicode(primary=True, name="identifier")
400
description = Unicode()
406
worksheets = ReferenceSet(id,
407
'WorksheetExercise.exercise_id',
408
'WorksheetExercise.worksheet_id',
412
test_suites = ReferenceSet(id, 'TestSuite.exercise_id')
414
__init__ = _kwarg_init
417
return "<%s %s>" % (type(self).__name__, self.name)
419
def get_permissions(self, user):
427
class Worksheet(Storm):
428
__storm_table__ = "worksheet"
430
id = Int(primary=True, name="worksheetid")
431
offering_id = Int(name="offeringid")
432
identifier = Unicode()
439
attempts = ReferenceSet(id, "ExerciseAttempt.worksheetid")
440
offering = Reference(offering_id, 'Offering.id')
442
all_worksheet_exercises = ReferenceSet(id,
443
'WorksheetExercise.worksheet_id')
445
# Use worksheet_exercises to get access to the *active* WorksheetExercise
446
# objects binding worksheets to exercises. This is required to access the
449
def worksheet_exercises(self):
450
return self.all_worksheet_exercises.find(active=True)
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
def get_permissions(self, user):
479
return self.offering.get_permissions(user)
481
class WorksheetExercise(Storm):
482
__storm_table__ = "worksheet_exercise"
484
id = Int(primary=True, name="ws_ex_id")
486
worksheet_id = Int(name="worksheetid")
487
worksheet = Reference(worksheet_id, Worksheet.id)
488
exercise_id = Unicode(name="exerciseid")
489
exercise = Reference(exercise_id, Exercise.id)
494
saves = ReferenceSet(id, "ExerciseSave.ws_ex_id")
495
attempts = ReferenceSet(id, "ExerciseAttempt.ws_ex_id")
497
__init__ = _kwarg_init
500
return "<%s %s in %s>" % (type(self).__name__, self.exercise.name,
501
self.worksheet.identifier)
503
class ExerciseSave(Storm):
505
Represents a potential solution to an exercise that a user has submitted
506
to the server for storage.
507
A basic ExerciseSave is just the current saved text for this exercise for
508
this user (doesn't count towards their attempts).
509
ExerciseSave may be extended with additional semantics (such as
512
__storm_table__ = "exercise_save"
513
__storm_primary__ = "ws_ex_id", "user_id"
515
ws_ex_id = Int(name="ws_ex_id")
516
worksheet_exercise = Reference(ws_ex_id, "WorksheetExercise.id")
518
user_id = Int(name="loginid")
519
user = Reference(user_id, User.id)
523
__init__ = _kwarg_init
526
return "<%s %s by %s at %s>" % (type(self).__name__,
527
self.exercise.name, self.user.login, self.date.strftime("%c"))
529
class ExerciseAttempt(ExerciseSave):
531
An ExerciseAttempt is a special case of an ExerciseSave. Like an
532
ExerciseSave, it constitutes exercise solution data that the user has
533
submitted to the server for storage.
534
In addition, it contains additional information about the submission.
535
complete - True if this submission was successful, rendering this exercise
536
complete for this user.
537
active - True if this submission is "active" (usually true). Submissions
538
may be de-activated by privileged users for special reasons, and then
539
they won't count (either as a penalty or success), but will still be
542
__storm_table__ = "exercise_attempt"
543
__storm_primary__ = "ws_ex_id", "user_id", "date"
545
# The "text" field is the same but has a different name in the DB table
547
text = Unicode(name="attempt")
551
def get_permissions(self, user):
552
return set(['view']) if user is self.user else set()
554
class TestSuite(Storm):
555
"""A Testsuite acts as a container for the test cases of an exercise."""
556
__storm_table__ = "test_suite"
557
__storm_primary__ = "exercise_id", "suiteid"
560
exercise_id = Unicode(name="exerciseid")
561
description = Unicode()
565
exercise = Reference(exercise_id, Exercise.id)
566
test_cases = ReferenceSet(suiteid, 'TestCase.suiteid')
567
variables = ReferenceSet(suiteid, 'TestSuiteVar.suiteid')
569
class TestCase(Storm):
570
"""A TestCase is a member of a TestSuite.
572
It contains the data necessary to check if an exercise is correct"""
573
__storm_table__ = "test_case"
574
__storm_primary__ = "testid", "suiteid"
578
suite = Reference(suiteid, "TestSuite.suiteid")
581
test_default = Unicode()
584
parts = ReferenceSet(testid, "TestCasePart.testid")
586
__init__ = _kwarg_init
588
class TestSuiteVar(Storm):
589
"""A container for the arguments of a Test Suite"""
590
__storm_table__ = "suite_variable"
591
__storm_primary__ = "varid"
596
var_value = Unicode()
600
suite = Reference(suiteid, "TestSuite.suiteid")
602
__init__ = _kwarg_init
604
class TestCasePart(Storm):
605
"""A container for the test elements of a Test Case"""
606
__storm_table__ = "test_case_part"
607
__storm_primary__ = "partid"
612
part_type = Unicode()
613
test_type = Unicode()
617
test = Reference(testid, "TestCase.testid")
619
__init__ = _kwarg_init
added
removed
ivle/database.py
