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
'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, role=u'student'):
273
'''Enrol a user in this offering.'''
274
enrolment = Store.of(self).find(Enrolment,
275
Enrolment.user_id == user.id,
276
Enrolment.offering_id == self.id).one()
278
if enrolment is None:
279
enrolment = Enrolment(user=user, offering=self)
280
self.enrolments.add(enrolment)
282
enrolment.active = True
283
enrolment.role = role
285
def get_permissions(self, user):
293
class Enrolment(Storm):
294
__storm_table__ = "enrolment"
295
__storm_primary__ = "user_id", "offering_id"
297
user_id = Int(name="loginid")
298
user = Reference(user_id, User.id)
299
offering_id = Int(name="offeringid")
300
offering = Reference(offering_id, Offering.id)
307
return Store.of(self).find(ProjectGroup,
308
ProjectSet.offering_id == self.offering.id,
309
ProjectGroup.project_set_id == ProjectSet.id,
310
ProjectGroupMembership.project_group_id == ProjectGroup.id,
311
ProjectGroupMembership.user_id == self.user.id)
313
__init__ = _kwarg_init
316
return "<%s %r in %r>" % (type(self).__name__, self.user,
321
class ProjectSet(Storm):
322
__storm_table__ = "project_set"
324
id = Int(name="projectsetid", primary=True)
325
offering_id = Int(name="offeringid")
326
offering = Reference(offering_id, Offering.id)
327
max_students_per_group = Int()
329
projects = ReferenceSet(id, 'Project.project_set_id')
330
project_groups = ReferenceSet(id, 'ProjectGroup.project_set_id')
332
__init__ = _kwarg_init
335
return "<%s %d in %r>" % (type(self).__name__, self.id,
338
class Project(Storm):
339
__storm_table__ = "project"
341
id = Int(name="projectid", primary=True)
344
project_set_id = Int(name="projectsetid")
345
project_set = Reference(project_set_id, ProjectSet.id)
346
deadline = DateTime()
348
__init__ = _kwarg_init
351
return "<%s '%s' in %r>" % (type(self).__name__, self.synopsis,
352
self.project_set.offering)
354
class ProjectGroup(Storm):
355
__storm_table__ = "project_group"
357
id = Int(name="groupid", primary=True)
358
name = Unicode(name="groupnm")
359
project_set_id = Int(name="projectsetid")
360
project_set = Reference(project_set_id, ProjectSet.id)
362
created_by_id = Int(name="createdby")
363
created_by = Reference(created_by_id, User.id)
366
members = ReferenceSet(id,
367
"ProjectGroupMembership.project_group_id",
368
"ProjectGroupMembership.user_id",
371
__init__ = _kwarg_init
374
return "<%s %s in %r>" % (type(self).__name__, self.name,
375
self.project_set.offering)
377
class ProjectGroupMembership(Storm):
378
__storm_table__ = "group_member"
379
__storm_primary__ = "user_id", "project_group_id"
381
user_id = Int(name="loginid")
382
user = Reference(user_id, User.id)
383
project_group_id = Int(name="groupid")
384
project_group = Reference(project_group_id, ProjectGroup.id)
386
__init__ = _kwarg_init
389
return "<%s %r in %r>" % (type(self).__name__, self.user,
392
# WORKSHEETS AND EXERCISES #
394
class Exercise(Storm):
395
__storm_table__ = "exercise"
396
id = Unicode(primary=True, name="identifier")
398
description = Unicode()
404
worksheets = ReferenceSet(id,
405
'WorksheetExercise.exercise_id',
406
'WorksheetExercise.worksheet_id',
410
test_suites = ReferenceSet(id, 'TestSuite.exercise_id')
412
__init__ = _kwarg_init
415
return "<%s %s>" % (type(self).__name__, self.name)
417
def get_permissions(self, user):
425
class Worksheet(Storm):
426
__storm_table__ = "worksheet"
428
id = Int(primary=True, name="worksheetid")
429
offering_id = Int(name="offeringid")
430
identifier = Unicode()
437
attempts = ReferenceSet(id, "ExerciseAttempt.worksheetid")
438
offering = Reference(offering_id, 'Offering.id')
440
all_worksheet_exercises = ReferenceSet(id,
441
'WorksheetExercise.worksheet_id')
443
# Use worksheet_exercises to get access to the *active* WorksheetExercise
444
# objects binding worksheets to exercises. This is required to access the
447
def worksheet_exercises(self):
448
return self.all_worksheet_exercises.find(active=True)
450
__init__ = _kwarg_init
453
return "<%s %s>" % (type(self).__name__, self.name)
455
# XXX Refactor this - make it an instance method of Subject rather than a
456
# class method of Worksheet. Can't do that now because Subject isn't
457
# linked referentially to the Worksheet.
459
def get_by_name(cls, store, subjectname, worksheetname):
461
Get the Worksheet from the db associated with a given store, subject
462
name and worksheet name.
464
return store.find(cls, cls.subject == unicode(subjectname),
465
cls.name == unicode(worksheetname)).one()
467
def remove_all_exercises(self, store):
469
Remove all exercises from this worksheet.
470
This does not delete the exercises themselves. It just removes them
473
store.find(WorksheetExercise,
474
WorksheetExercise.worksheet == self).remove()
476
def get_permissions(self, user):
477
return self.offering.get_permissions(user)
479
class WorksheetExercise(Storm):
480
__storm_table__ = "worksheet_exercise"
482
id = Int(primary=True, name="ws_ex_id")
484
worksheet_id = Int(name="worksheetid")
485
worksheet = Reference(worksheet_id, Worksheet.id)
486
exercise_id = Unicode(name="exerciseid")
487
exercise = Reference(exercise_id, Exercise.id)
492
saves = ReferenceSet(id, "ExerciseSave.ws_ex_id")
493
attempts = ReferenceSet(id, "ExerciseAttempt.ws_ex_id")
495
__init__ = _kwarg_init
498
return "<%s %s in %s>" % (type(self).__name__, self.exercise.name,
499
self.worksheet.identifier)
501
class ExerciseSave(Storm):
503
Represents a potential solution to an exercise that a user has submitted
504
to the server for storage.
505
A basic ExerciseSave is just the current saved text for this exercise for
506
this user (doesn't count towards their attempts).
507
ExerciseSave may be extended with additional semantics (such as
510
__storm_table__ = "exercise_save"
511
__storm_primary__ = "ws_ex_id", "user_id"
513
ws_ex_id = Int(name="ws_ex_id")
514
worksheet_exercise = Reference(ws_ex_id, "WorksheetExercise.id")
516
user_id = Int(name="loginid")
517
user = Reference(user_id, User.id)
521
__init__ = _kwarg_init
524
return "<%s %s by %s at %s>" % (type(self).__name__,
525
self.exercise.name, self.user.login, self.date.strftime("%c"))
527
class ExerciseAttempt(ExerciseSave):
529
An ExerciseAttempt is a special case of an ExerciseSave. Like an
530
ExerciseSave, it constitutes exercise solution data that the user has
531
submitted to the server for storage.
532
In addition, it contains additional information about the submission.
533
complete - True if this submission was successful, rendering this exercise
534
complete for this user.
535
active - True if this submission is "active" (usually true). Submissions
536
may be de-activated by privileged users for special reasons, and then
537
they won't count (either as a penalty or success), but will still be
540
__storm_table__ = "exercise_attempt"
541
__storm_primary__ = "ws_ex_id", "user_id", "date"
543
# The "text" field is the same but has a different name in the DB table
545
text = Unicode(name="attempt")
549
def get_permissions(self, user):
550
return set(['view']) if user is self.user else set()
552
class TestSuite(Storm):
553
"""A Testsuite acts as a container for the test cases of an exercise."""
554
__storm_table__ = "test_suite"
555
__storm_primary__ = "exercise_id", "suiteid"
558
exercise_id = Unicode(name="exerciseid")
559
description = Unicode()
563
exercise = Reference(exercise_id, Exercise.id)
564
test_cases = ReferenceSet(suiteid, 'TestCase.suiteid')
565
variables = ReferenceSet(suiteid, 'TestSuiteVar.suiteid')
567
class TestCase(Storm):
568
"""A TestCase is a member of a TestSuite.
570
It contains the data necessary to check if an exercise is correct"""
571
__storm_table__ = "test_case"
572
__storm_primary__ = "testid", "suiteid"
576
suite = Reference(suiteid, "TestSuite.suiteid")
579
test_default = Unicode()
582
parts = ReferenceSet(testid, "TestCasePart.testid")
584
__init__ = _kwarg_init
586
class TestSuiteVar(Storm):
587
"""A container for the arguments of a Test Suite"""
588
__storm_table__ = "suite_variable"
589
__storm_primary__ = "varid"
594
var_value = Unicode()
598
suite = Reference(suiteid, "TestSuite.suiteid")
600
__init__ = _kwarg_init
602
class TestCasePart(Storm):
603
"""A container for the test elements of a Test Case"""
604
__storm_table__ = "test_case_part"
605
__storm_primary__ = "partid"
610
part_type = Unicode()
611
test_type = Unicode()
615
test = Reference(testid, "TestCase.testid")
617
__init__ = _kwarg_init
added
removed
ivle/database.py
