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
34
from ivle.worksheet.rst import rst
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')
240
__init__ = _kwarg_init
243
return "<%s %s/%s>" % (type(self).__name__, self.year, self.semester)
245
class Offering(Storm):
246
__storm_table__ = "offering"
248
id = Int(primary=True, name="offeringid")
249
subject_id = Int(name="subject")
250
subject = Reference(subject_id, Subject.id)
251
semester_id = Int(name="semesterid")
252
semester = Reference(semester_id, Semester.id)
253
groups_student_permissions = Unicode()
255
enrolments = ReferenceSet(id, 'Enrolment.offering_id')
256
members = ReferenceSet(id,
257
'Enrolment.offering_id',
260
project_sets = ReferenceSet(id, 'ProjectSet.offering_id')
262
worksheets = ReferenceSet(id,
263
'Worksheet.offering_id',
267
__init__ = _kwarg_init
270
return "<%s %r in %r>" % (type(self).__name__, self.subject,
273
def enrol(self, user, role=u'student'):
274
'''Enrol a user in this offering.'''
275
enrolment = Store.of(self).find(Enrolment,
276
Enrolment.user_id == user.id,
277
Enrolment.offering_id == self.id).one()
279
if enrolment is None:
280
enrolment = Enrolment(user=user, offering=self)
281
self.enrolments.add(enrolment)
283
enrolment.active = True
284
enrolment.role = role
286
def get_permissions(self, user):
294
class Enrolment(Storm):
295
__storm_table__ = "enrolment"
296
__storm_primary__ = "user_id", "offering_id"
298
user_id = Int(name="loginid")
299
user = Reference(user_id, User.id)
300
offering_id = Int(name="offeringid")
301
offering = Reference(offering_id, Offering.id)
308
return Store.of(self).find(ProjectGroup,
309
ProjectSet.offering_id == self.offering.id,
310
ProjectGroup.project_set_id == ProjectSet.id,
311
ProjectGroupMembership.project_group_id == ProjectGroup.id,
312
ProjectGroupMembership.user_id == self.user.id)
314
__init__ = _kwarg_init
317
return "<%s %r in %r>" % (type(self).__name__, self.user,
322
class ProjectSet(Storm):
323
__storm_table__ = "project_set"
325
id = Int(name="projectsetid", primary=True)
326
offering_id = Int(name="offeringid")
327
offering = Reference(offering_id, Offering.id)
328
max_students_per_group = Int()
330
projects = ReferenceSet(id, 'Project.project_set_id')
331
project_groups = ReferenceSet(id, 'ProjectGroup.project_set_id')
333
__init__ = _kwarg_init
336
return "<%s %d in %r>" % (type(self).__name__, self.id,
339
class Project(Storm):
340
__storm_table__ = "project"
342
id = Int(name="projectid", primary=True)
345
project_set_id = Int(name="projectsetid")
346
project_set = Reference(project_set_id, ProjectSet.id)
347
deadline = DateTime()
349
__init__ = _kwarg_init
352
return "<%s '%s' in %r>" % (type(self).__name__, self.synopsis,
353
self.project_set.offering)
355
class ProjectGroup(Storm):
356
__storm_table__ = "project_group"
358
id = Int(name="groupid", primary=True)
359
name = Unicode(name="groupnm")
360
project_set_id = Int(name="projectsetid")
361
project_set = Reference(project_set_id, ProjectSet.id)
363
created_by_id = Int(name="createdby")
364
created_by = Reference(created_by_id, User.id)
367
members = ReferenceSet(id,
368
"ProjectGroupMembership.project_group_id",
369
"ProjectGroupMembership.user_id",
372
__init__ = _kwarg_init
375
return "<%s %s in %r>" % (type(self).__name__, self.name,
376
self.project_set.offering)
378
class ProjectGroupMembership(Storm):
379
__storm_table__ = "group_member"
380
__storm_primary__ = "user_id", "project_group_id"
382
user_id = Int(name="loginid")
383
user = Reference(user_id, User.id)
384
project_group_id = Int(name="groupid")
385
project_group = Reference(project_group_id, ProjectGroup.id)
387
__init__ = _kwarg_init
390
return "<%s %r in %r>" % (type(self).__name__, self.user,
393
# WORKSHEETS AND EXERCISES #
395
class Exercise(Storm):
396
__storm_table__ = "exercise"
397
id = Unicode(primary=True, name="identifier")
399
description = Unicode()
405
worksheets = ReferenceSet(id,
406
'WorksheetExercise.exercise_id',
407
'WorksheetExercise.worksheet_id',
411
test_suites = ReferenceSet(id,
412
'TestSuite.exercise_id',
415
__init__ = _kwarg_init
418
return "<%s %s>" % (type(self).__name__, self.name)
420
def get_permissions(self, user):
428
class Worksheet(Storm):
429
__storm_table__ = "worksheet"
431
id = Int(primary=True, name="worksheetid")
432
offering_id = Int(name="offeringid")
433
identifier = Unicode()
440
attempts = ReferenceSet(id, "ExerciseAttempt.worksheetid")
441
offering = Reference(offering_id, 'Offering.id')
443
all_worksheet_exercises = ReferenceSet(id,
444
'WorksheetExercise.worksheet_id')
446
# Use worksheet_exercises to get access to the *active* WorksheetExercise
447
# objects binding worksheets to exercises. This is required to access the
451
def worksheet_exercises(self):
452
return self.all_worksheet_exercises.find(active=True)
454
__init__ = _kwarg_init
457
return "<%s %s>" % (type(self).__name__, self.name)
459
# XXX Refactor this - make it an instance method of Subject rather than a
460
# class method of Worksheet. Can't do that now because Subject isn't
461
# linked referentially to the Worksheet.
463
def get_by_name(cls, store, subjectname, worksheetname):
465
Get the Worksheet from the db associated with a given store, subject
466
name and worksheet name.
468
return store.find(cls, cls.subject == unicode(subjectname),
469
cls.name == unicode(worksheetname)).one()
471
def remove_all_exercises(self, store):
473
Remove all exercises from this worksheet.
474
This does not delete the exercises themselves. It just removes them
477
store.find(WorksheetExercise,
478
WorksheetExercise.worksheet == self).remove()
480
def get_permissions(self, user):
481
return self.offering.get_permissions(user)
484
"""Returns the xml of this worksheet, converts from rst if required."""
485
if self.format == u'rst':
486
ws_xml = '<worksheet>' + rst(self.data) + '</worksheet>'
491
class WorksheetExercise(Storm):
492
__storm_table__ = "worksheet_exercise"
494
id = Int(primary=True, name="ws_ex_id")
496
worksheet_id = Int(name="worksheetid")
497
worksheet = Reference(worksheet_id, Worksheet.id)
498
exercise_id = Unicode(name="exerciseid")
499
exercise = Reference(exercise_id, Exercise.id)
504
saves = ReferenceSet(id, "ExerciseSave.ws_ex_id")
505
attempts = ReferenceSet(id, "ExerciseAttempt.ws_ex_id")
507
__init__ = _kwarg_init
510
return "<%s %s in %s>" % (type(self).__name__, self.exercise.name,
511
self.worksheet.identifier)
513
class ExerciseSave(Storm):
515
Represents a potential solution to an exercise that a user has submitted
516
to the server for storage.
517
A basic ExerciseSave is just the current saved text for this exercise for
518
this user (doesn't count towards their attempts).
519
ExerciseSave may be extended with additional semantics (such as
522
__storm_table__ = "exercise_save"
523
__storm_primary__ = "ws_ex_id", "user_id"
525
ws_ex_id = Int(name="ws_ex_id")
526
worksheet_exercise = Reference(ws_ex_id, "WorksheetExercise.id")
528
user_id = Int(name="loginid")
529
user = Reference(user_id, User.id)
533
__init__ = _kwarg_init
536
return "<%s %s by %s at %s>" % (type(self).__name__,
537
self.exercise.name, self.user.login, self.date.strftime("%c"))
539
class ExerciseAttempt(ExerciseSave):
541
An ExerciseAttempt is a special case of an ExerciseSave. Like an
542
ExerciseSave, it constitutes exercise solution data that the user has
543
submitted to the server for storage.
544
In addition, it contains additional information about the submission.
545
complete - True if this submission was successful, rendering this exercise
546
complete for this user.
547
active - True if this submission is "active" (usually true). Submissions
548
may be de-activated by privileged users for special reasons, and then
549
they won't count (either as a penalty or success), but will still be
552
__storm_table__ = "exercise_attempt"
553
__storm_primary__ = "ws_ex_id", "user_id", "date"
555
# The "text" field is the same but has a different name in the DB table
557
text = Unicode(name="attempt")
561
def get_permissions(self, user):
562
return set(['view']) if user is self.user else set()
564
class TestSuite(Storm):
565
"""A Testsuite acts as a container for the test cases of an exercise."""
566
__storm_table__ = "test_suite"
567
__storm_primary__ = "exercise_id", "suiteid"
570
exercise_id = Unicode(name="exerciseid")
571
description = Unicode()
575
exercise = Reference(exercise_id, Exercise.id)
576
test_cases = ReferenceSet(suiteid, 'TestCase.suiteid', order_by="seq_no")
577
variables = ReferenceSet(suiteid, 'TestSuiteVar.suiteid', order_by='arg_no')
579
class TestCase(Storm):
580
"""A TestCase is a member of a TestSuite.
582
It contains the data necessary to check if an exercise is correct"""
583
__storm_table__ = "test_case"
584
__storm_primary__ = "testid", "suiteid"
588
suite = Reference(suiteid, "TestSuite.suiteid")
591
test_default = Unicode()
594
parts = ReferenceSet(testid, "TestCasePart.testid")
596
__init__ = _kwarg_init
598
class TestSuiteVar(Storm):
599
"""A container for the arguments of a Test Suite"""
600
__storm_table__ = "suite_variable"
601
__storm_primary__ = "varid"
606
var_value = Unicode()
610
suite = Reference(suiteid, "TestSuite.suiteid")
612
__init__ = _kwarg_init
614
class TestCasePart(Storm):
615
"""A container for the test elements of a Test Case"""
616
__storm_table__ = "test_case_part"
617
__storm_primary__ = "partid"
622
part_type = Unicode()
623
test_type = Unicode()
627
test = Reference(testid, "TestCase.testid")
629
__init__ = _kwarg_init
added
removed
ivle/database.py
