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
32
from storm.exceptions import NotOneError
36
__all__ = ['get_store',
38
'Subject', 'Semester', 'Offering', 'Enrolment',
39
'ProjectSet', 'Project', 'ProjectGroup', 'ProjectGroupMembership',
40
'Exercise', 'Worksheet', 'WorksheetExercise',
41
'ExerciseSave', 'ExerciseAttempt',
42
'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()
102
__init__ = _kwarg_init
105
return "<%s '%s'>" % (type(self).__name__, self.login)
107
def authenticate(self, password):
108
"""Validate a given password against this user.
110
Returns True if the given password matches the password hash for this
111
User, False if it doesn't match, and None if there is no hash for the
114
if self.passhash is None:
116
return self.hash_password(password) == self.passhash
119
def password_expired(self):
120
fieldval = self.pass_exp
121
return fieldval is not None and datetime.datetime.now() > fieldval
124
def account_expired(self):
125
fieldval = self.acct_exp
126
return fieldval is not None and datetime.datetime.now() > fieldval
130
return self.state == 'enabled' and not self.account_expired
132
def _get_enrolments(self, justactive):
133
return Store.of(self).find(Enrolment,
134
Enrolment.user_id == self.id,
135
(Enrolment.active == True) if justactive else True,
136
Enrolment.offering_id == Offering.id,
137
Offering.semester_id == Semester.id,
138
Offering.subject_id == Subject.id).order_by(
140
Desc(Semester.semester),
144
def _set_password(self, password):
148
self.passhash = unicode(User.hash_password(password))
149
password = property(fset=_set_password)
153
return Store.of(self).find(Subject,
154
Enrolment.user_id == self.id,
155
Enrolment.active == True,
156
Offering.id == Enrolment.offering_id,
157
Subject.id == Offering.subject_id).config(distinct=True)
159
# TODO: Invitations should be listed too?
160
def get_groups(self, offering=None):
162
ProjectGroupMembership.user_id == self.id,
163
ProjectGroup.id == ProjectGroupMembership.project_group_id,
167
ProjectSet.offering_id == offering.id,
168
ProjectGroup.project_set_id == ProjectSet.id,
170
return Store.of(self).find(ProjectGroup, *preds)
174
return self.get_groups()
177
def active_enrolments(self):
178
'''A sanely ordered list of the user's active enrolments.'''
179
return self._get_enrolments(True)
182
def enrolments(self):
183
'''A sanely ordered list of all of the user's enrolments.'''
184
return self._get_enrolments(False)
187
def hash_password(password):
188
return md5.md5(password).hexdigest()
191
def get_by_login(cls, store, login):
193
Get the User from the db associated with a given store and
196
return store.find(cls, cls.login == unicode(login)).one()
198
def get_permissions(self, user):
199
if user and user.admin or user is self:
200
return set(['view', 'edit'])
204
# SUBJECTS AND ENROLMENTS #
206
class Subject(Storm):
207
__storm_table__ = "subject"
209
id = Int(primary=True, name="subjectid")
210
code = Unicode(name="subj_code")
211
name = Unicode(name="subj_name")
212
short_name = Unicode(name="subj_short_name")
215
offerings = ReferenceSet(id, 'Offering.subject_id')
217
__init__ = _kwarg_init
220
return "<%s '%s'>" % (type(self).__name__, self.short_name)
222
def get_permissions(self, user):
230
class Semester(Storm):
231
__storm_table__ = "semester"
233
id = Int(primary=True, name="semesterid")
238
offerings = ReferenceSet(id, 'Offering.semester_id')
239
enrolments = ReferenceSet(id,
240
'Offering.semester_id',
242
'Enrolment.offering_id')
244
__init__ = _kwarg_init
247
return "<%s %s/%s>" % (type(self).__name__, self.year, self.semester)
249
class Offering(Storm):
250
__storm_table__ = "offering"
252
id = Int(primary=True, name="offeringid")
253
subject_id = Int(name="subject")
254
subject = Reference(subject_id, Subject.id)
255
semester_id = Int(name="semesterid")
256
semester = Reference(semester_id, Semester.id)
257
groups_student_permissions = Unicode()
259
enrolments = ReferenceSet(id, 'Enrolment.offering_id')
260
members = ReferenceSet(id,
261
'Enrolment.offering_id',
264
project_sets = ReferenceSet(id, 'ProjectSet.offering_id')
266
worksheets = ReferenceSet(id,
267
'Worksheet.offering_id',
268
order_by="Worksheet.seq_no"
271
__init__ = _kwarg_init
274
return "<%s %r in %r>" % (type(self).__name__, self.subject,
277
def enrol(self, user, role=u'student'):
278
'''Enrol a user in this offering.'''
279
enrolment = Store.of(self).find(Enrolment,
280
Enrolment.user_id == user.id,
281
Enrolment.offering_id == self.id).one()
283
if enrolment is None:
284
enrolment = Enrolment(user=user, offering=self)
285
self.enrolments.add(enrolment)
287
enrolment.active = True
288
enrolment.role = role
290
def unenrol(self, user):
291
'''Unenrol a user from this offering.'''
292
enrolment = Store.of(self).find(Enrolment,
293
Enrolment.user_id == user.id,
294
Enrolment.offering_id == self.id).one()
295
Store.of(enrolment).remove(enrolment)
297
def get_permissions(self, user):
300
enrolment = self.get_enrolment(user)
301
if enrolment or user.admin:
303
if (enrolment and enrolment.role in (u'tutor', u'lecturer')) \
308
def get_enrolment(self, user):
310
enrolment = self.enrolments.find(user=user).one()
316
class Enrolment(Storm):
317
__storm_table__ = "enrolment"
318
__storm_primary__ = "user_id", "offering_id"
320
user_id = Int(name="loginid")
321
user = Reference(user_id, User.id)
322
offering_id = Int(name="offeringid")
323
offering = Reference(offering_id, Offering.id)
330
return Store.of(self).find(ProjectGroup,
331
ProjectSet.offering_id == self.offering.id,
332
ProjectGroup.project_set_id == ProjectSet.id,
333
ProjectGroupMembership.project_group_id == ProjectGroup.id,
334
ProjectGroupMembership.user_id == self.user.id)
336
__init__ = _kwarg_init
339
return "<%s %r in %r>" % (type(self).__name__, self.user,
344
class ProjectSet(Storm):
345
__storm_table__ = "project_set"
347
id = Int(name="projectsetid", primary=True)
348
offering_id = Int(name="offeringid")
349
offering = Reference(offering_id, Offering.id)
350
max_students_per_group = Int()
352
projects = ReferenceSet(id, 'Project.project_set_id')
353
project_groups = ReferenceSet(id, 'ProjectGroup.project_set_id')
355
__init__ = _kwarg_init
358
return "<%s %d in %r>" % (type(self).__name__, self.id,
361
class Project(Storm):
362
__storm_table__ = "project"
364
id = Int(name="projectid", primary=True)
367
project_set_id = Int(name="projectsetid")
368
project_set = Reference(project_set_id, ProjectSet.id)
369
deadline = DateTime()
371
__init__ = _kwarg_init
374
return "<%s '%s' in %r>" % (type(self).__name__, self.synopsis,
375
self.project_set.offering)
377
class ProjectGroup(Storm):
378
__storm_table__ = "project_group"
380
id = Int(name="groupid", primary=True)
381
name = Unicode(name="groupnm")
382
project_set_id = Int(name="projectsetid")
383
project_set = Reference(project_set_id, ProjectSet.id)
385
created_by_id = Int(name="createdby")
386
created_by = Reference(created_by_id, User.id)
389
members = ReferenceSet(id,
390
"ProjectGroupMembership.project_group_id",
391
"ProjectGroupMembership.user_id",
394
__init__ = _kwarg_init
397
return "<%s %s in %r>" % (type(self).__name__, self.name,
398
self.project_set.offering)
400
class ProjectGroupMembership(Storm):
401
__storm_table__ = "group_member"
402
__storm_primary__ = "user_id", "project_group_id"
404
user_id = Int(name="loginid")
405
user = Reference(user_id, User.id)
406
project_group_id = Int(name="groupid")
407
project_group = Reference(project_group_id, ProjectGroup.id)
409
__init__ = _kwarg_init
412
return "<%s %r in %r>" % (type(self).__name__, self.user,
415
# WORKSHEETS AND EXERCISES #
417
class Exercise(Storm):
418
__storm_table__ = "exercise"
419
id = Unicode(primary=True, name="identifier")
421
description = Unicode()
427
worksheets = ReferenceSet(id,
428
'WorksheetExercise.exercise_id',
429
'WorksheetExercise.worksheet_id',
433
test_suites = ReferenceSet(id, 'TestSuite.exercise_id')
435
__init__ = _kwarg_init
438
return "<%s %s>" % (type(self).__name__, self.name)
440
def get_permissions(self, user):
448
class Worksheet(Storm):
449
__storm_table__ = "worksheet"
451
id = Int(primary=True, name="worksheetid")
452
offering_id = Int(name="offeringid")
453
identifier = Unicode()
460
attempts = ReferenceSet(id, "ExerciseAttempt.worksheetid")
461
offering = Reference(offering_id, 'Offering.id')
463
all_worksheet_exercises = ReferenceSet(id,
464
'WorksheetExercise.worksheet_id')
466
# Use worksheet_exercises to get access to the *active* WorksheetExercise
467
# objects binding worksheets to exercises. This is required to access the
470
def worksheet_exercises(self):
471
return self.all_worksheet_exercises.find(active=True)
473
__init__ = _kwarg_init
476
return "<%s %s>" % (type(self).__name__, self.name)
478
# XXX Refactor this - make it an instance method of Subject rather than a
479
# class method of Worksheet. Can't do that now because Subject isn't
480
# linked referentially to the Worksheet.
482
def get_by_name(cls, store, subjectname, worksheetname):
484
Get the Worksheet from the db associated with a given store, subject
485
name and worksheet name.
487
return store.find(cls, cls.subject == unicode(subjectname),
488
cls.name == unicode(worksheetname)).one()
490
def remove_all_exercises(self, store):
492
Remove all exercises from this worksheet.
493
This does not delete the exercises themselves. It just removes them
496
store.find(WorksheetExercise,
497
WorksheetExercise.worksheet == self).remove()
499
def get_permissions(self, user):
500
return self.offering.get_permissions(user)
502
class WorksheetExercise(Storm):
503
__storm_table__ = "worksheet_exercise"
505
id = Int(primary=True, name="ws_ex_id")
507
worksheet_id = Int(name="worksheetid")
508
worksheet = Reference(worksheet_id, Worksheet.id)
509
exercise_id = Unicode(name="exerciseid")
510
exercise = Reference(exercise_id, Exercise.id)
515
saves = ReferenceSet(id, "ExerciseSave.ws_ex_id")
516
attempts = ReferenceSet(id, "ExerciseAttempt.ws_ex_id")
518
__init__ = _kwarg_init
521
return "<%s %s in %s>" % (type(self).__name__, self.exercise.name,
522
self.worksheet.identifier)
524
def get_permissions(self, user):
525
return self.worksheet.get_permissions(user)
527
class ExerciseSave(Storm):
529
Represents a potential solution to an exercise that a user has submitted
530
to the server for storage.
531
A basic ExerciseSave is just the current saved text for this exercise for
532
this user (doesn't count towards their attempts).
533
ExerciseSave may be extended with additional semantics (such as
536
__storm_table__ = "exercise_save"
537
__storm_primary__ = "ws_ex_id", "user_id"
539
ws_ex_id = Int(name="ws_ex_id")
540
worksheet_exercise = Reference(ws_ex_id, "WorksheetExercise.id")
542
user_id = Int(name="loginid")
543
user = Reference(user_id, User.id)
547
__init__ = _kwarg_init
550
return "<%s %s by %s at %s>" % (type(self).__name__,
551
self.exercise.name, self.user.login, self.date.strftime("%c"))
553
class ExerciseAttempt(ExerciseSave):
555
An ExerciseAttempt is a special case of an ExerciseSave. Like an
556
ExerciseSave, it constitutes exercise solution data that the user has
557
submitted to the server for storage.
558
In addition, it contains additional information about the submission.
559
complete - True if this submission was successful, rendering this exercise
560
complete for this user.
561
active - True if this submission is "active" (usually true). Submissions
562
may be de-activated by privileged users for special reasons, and then
563
they won't count (either as a penalty or success), but will still be
566
__storm_table__ = "exercise_attempt"
567
__storm_primary__ = "ws_ex_id", "user_id", "date"
569
# The "text" field is the same but has a different name in the DB table
571
text = Unicode(name="attempt")
575
def get_permissions(self, user):
576
return set(['view']) if user is self.user else set()
578
class TestSuite(Storm):
579
"""A Testsuite acts as a container for the test cases of an exercise."""
580
__storm_table__ = "test_suite"
581
__storm_primary__ = "exercise_id", "suiteid"
584
exercise_id = Unicode(name="exerciseid")
585
description = Unicode()
589
exercise = Reference(exercise_id, Exercise.id)
590
test_cases = ReferenceSet(suiteid, 'TestCase.suiteid')
591
variables = ReferenceSet(suiteid, 'TestSuiteVar.suiteid')
593
class TestCase(Storm):
594
"""A TestCase is a member of a TestSuite.
596
It contains the data necessary to check if an exercise is correct"""
597
__storm_table__ = "test_case"
598
__storm_primary__ = "testid", "suiteid"
602
suite = Reference(suiteid, "TestSuite.suiteid")
605
test_default = Unicode()
608
parts = ReferenceSet(testid, "TestCasePart.testid")
610
__init__ = _kwarg_init
612
class TestSuiteVar(Storm):
613
"""A container for the arguments of a Test Suite"""
614
__storm_table__ = "suite_variable"
615
__storm_primary__ = "varid"
620
var_value = Unicode()
624
suite = Reference(suiteid, "TestSuite.suiteid")
626
__init__ = _kwarg_init
628
class TestCasePart(Storm):
629
"""A container for the test elements of a Test Case"""
630
__storm_table__ = "test_case_part"
631
__storm_primary__ = "partid"
636
part_type = Unicode()
637
test_type = Unicode()
641
test = Reference(testid, "TestCase.testid")
643
__init__ = _kwarg_init
added
removed
ivle/database.py
