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, IntegrityError
35
from ivle.worksheet.rst import rst
37
__all__ = ['get_store',
39
'Subject', 'Semester', 'Offering', 'Enrolment',
40
'ProjectSet', 'Project', 'ProjectGroup', 'ProjectGroupMembership',
41
'Exercise', 'Worksheet', 'WorksheetExercise',
42
'ExerciseSave', 'ExerciseAttempt',
43
'TestCase', 'TestSuite', 'TestSuiteVar'
46
def _kwarg_init(self, **kwargs):
47
for k,v in kwargs.items():
48
if k.startswith('_') or not hasattr(self.__class__, k):
49
raise TypeError("%s got an unexpected keyword argument '%s'"
50
% (self.__class__.__name__, k))
53
def get_conn_string():
55
Returns the Storm connection string, generated from the conf file.
60
clusterstr += ivle.conf.db_user
61
if ivle.conf.db_password:
62
clusterstr += ':' + ivle.conf.db_password
65
host = ivle.conf.db_host or 'localhost'
66
port = ivle.conf.db_port or 5432
68
clusterstr += '%s:%d' % (host, port)
70
return "postgres://%s/%s" % (clusterstr, ivle.conf.db_dbname)
74
Open a database connection and transaction. Return a storm.store.Store
75
instance connected to the configured IVLE database.
77
return Store(create_database(get_conn_string()))
83
Represents an IVLE user.
85
__storm_table__ = "login"
87
id = Int(primary=True, name="loginid")
96
last_login = DateTime()
100
studentid = Unicode()
103
__init__ = _kwarg_init
106
return "<%s '%s'>" % (type(self).__name__, self.login)
108
def authenticate(self, password):
109
"""Validate a given password against this user.
111
Returns True if the given password matches the password hash for this
112
User, False if it doesn't match, and None if there is no hash for the
115
if self.passhash is None:
117
return self.hash_password(password) == self.passhash
120
def password_expired(self):
121
fieldval = self.pass_exp
122
return fieldval is not None and datetime.datetime.now() > fieldval
125
def account_expired(self):
126
fieldval = self.acct_exp
127
return fieldval is not None and datetime.datetime.now() > fieldval
131
return self.state == 'enabled' and not self.account_expired
133
def _get_enrolments(self, justactive):
134
return Store.of(self).find(Enrolment,
135
Enrolment.user_id == self.id,
136
(Enrolment.active == True) if justactive else True,
137
Enrolment.offering_id == Offering.id,
138
Offering.semester_id == Semester.id,
139
Offering.subject_id == Subject.id).order_by(
141
Desc(Semester.semester),
145
def _set_password(self, password):
149
self.passhash = unicode(User.hash_password(password))
150
password = property(fset=_set_password)
154
return Store.of(self).find(Subject,
155
Enrolment.user_id == self.id,
156
Enrolment.active == True,
157
Offering.id == Enrolment.offering_id,
158
Subject.id == Offering.subject_id).config(distinct=True)
160
# TODO: Invitations should be listed too?
161
def get_groups(self, offering=None):
163
ProjectGroupMembership.user_id == self.id,
164
ProjectGroup.id == ProjectGroupMembership.project_group_id,
168
ProjectSet.offering_id == offering.id,
169
ProjectGroup.project_set_id == ProjectSet.id,
171
return Store.of(self).find(ProjectGroup, *preds)
175
return self.get_groups()
178
def active_enrolments(self):
179
'''A sanely ordered list of the user's active enrolments.'''
180
return self._get_enrolments(True)
183
def enrolments(self):
184
'''A sanely ordered list of all of the user's enrolments.'''
185
return self._get_enrolments(False)
188
def hash_password(password):
189
return md5.md5(password).hexdigest()
192
def get_by_login(cls, store, login):
194
Get the User from the db associated with a given store and
197
return store.find(cls, cls.login == unicode(login)).one()
199
def get_permissions(self, user):
200
if user and user.admin or user is self:
201
return set(['view', 'edit'])
205
# SUBJECTS AND ENROLMENTS #
207
class Subject(Storm):
208
__storm_table__ = "subject"
210
id = Int(primary=True, name="subjectid")
211
code = Unicode(name="subj_code")
212
name = Unicode(name="subj_name")
213
short_name = Unicode(name="subj_short_name")
216
offerings = ReferenceSet(id, 'Offering.subject_id')
218
__init__ = _kwarg_init
221
return "<%s '%s'>" % (type(self).__name__, self.short_name)
223
def get_permissions(self, user):
231
class Semester(Storm):
232
__storm_table__ = "semester"
234
id = Int(primary=True, name="semesterid")
239
offerings = ReferenceSet(id, 'Offering.semester_id')
240
enrolments = ReferenceSet(id,
241
'Offering.semester_id',
243
'Enrolment.offering_id')
245
__init__ = _kwarg_init
248
return "<%s %s/%s>" % (type(self).__name__, self.year, self.semester)
250
class Offering(Storm):
251
__storm_table__ = "offering"
253
id = Int(primary=True, name="offeringid")
254
subject_id = Int(name="subject")
255
subject = Reference(subject_id, Subject.id)
256
semester_id = Int(name="semesterid")
257
semester = Reference(semester_id, Semester.id)
258
groups_student_permissions = Unicode()
260
enrolments = ReferenceSet(id, 'Enrolment.offering_id')
261
members = ReferenceSet(id,
262
'Enrolment.offering_id',
265
project_sets = ReferenceSet(id, 'ProjectSet.offering_id')
267
worksheets = ReferenceSet(id,
268
'Worksheet.offering_id',
272
__init__ = _kwarg_init
275
return "<%s %r in %r>" % (type(self).__name__, self.subject,
278
def enrol(self, user, role=u'student'):
279
'''Enrol a user in this offering.'''
280
enrolment = Store.of(self).find(Enrolment,
281
Enrolment.user_id == user.id,
282
Enrolment.offering_id == self.id).one()
284
if enrolment is None:
285
enrolment = Enrolment(user=user, offering=self)
286
self.enrolments.add(enrolment)
288
enrolment.active = True
289
enrolment.role = role
291
def unenrol(self, user):
292
'''Unenrol a user from this offering.'''
293
enrolment = Store.of(self).find(Enrolment,
294
Enrolment.user_id == user.id,
295
Enrolment.offering_id == self.id).one()
296
Store.of(enrolment).remove(enrolment)
298
def get_permissions(self, user):
301
enrolment = self.get_enrolment(user)
302
if enrolment or user.admin:
304
if (enrolment and enrolment.role in (u'tutor', u'lecturer')) \
309
def get_enrolment(self, user):
311
enrolment = self.enrolments.find(user=user).one()
317
class Enrolment(Storm):
318
__storm_table__ = "enrolment"
319
__storm_primary__ = "user_id", "offering_id"
321
user_id = Int(name="loginid")
322
user = Reference(user_id, User.id)
323
offering_id = Int(name="offeringid")
324
offering = Reference(offering_id, Offering.id)
331
return Store.of(self).find(ProjectGroup,
332
ProjectSet.offering_id == self.offering.id,
333
ProjectGroup.project_set_id == ProjectSet.id,
334
ProjectGroupMembership.project_group_id == ProjectGroup.id,
335
ProjectGroupMembership.user_id == self.user.id)
337
__init__ = _kwarg_init
340
return "<%s %r in %r>" % (type(self).__name__, self.user,
345
class ProjectSet(Storm):
346
__storm_table__ = "project_set"
348
id = Int(name="projectsetid", primary=True)
349
offering_id = Int(name="offeringid")
350
offering = Reference(offering_id, Offering.id)
351
max_students_per_group = Int()
353
projects = ReferenceSet(id, 'Project.project_set_id')
354
project_groups = ReferenceSet(id, 'ProjectGroup.project_set_id')
356
__init__ = _kwarg_init
359
return "<%s %d in %r>" % (type(self).__name__, self.id,
362
class Project(Storm):
363
__storm_table__ = "project"
365
id = Int(name="projectid", primary=True)
368
project_set_id = Int(name="projectsetid")
369
project_set = Reference(project_set_id, ProjectSet.id)
370
deadline = DateTime()
372
__init__ = _kwarg_init
375
return "<%s '%s' in %r>" % (type(self).__name__, self.synopsis,
376
self.project_set.offering)
378
class ProjectGroup(Storm):
379
__storm_table__ = "project_group"
381
id = Int(name="groupid", primary=True)
382
name = Unicode(name="groupnm")
383
project_set_id = Int(name="projectsetid")
384
project_set = Reference(project_set_id, ProjectSet.id)
386
created_by_id = Int(name="createdby")
387
created_by = Reference(created_by_id, User.id)
390
members = ReferenceSet(id,
391
"ProjectGroupMembership.project_group_id",
392
"ProjectGroupMembership.user_id",
395
__init__ = _kwarg_init
398
return "<%s %s in %r>" % (type(self).__name__, self.name,
399
self.project_set.offering)
401
class ProjectGroupMembership(Storm):
402
__storm_table__ = "group_member"
403
__storm_primary__ = "user_id", "project_group_id"
405
user_id = Int(name="loginid")
406
user = Reference(user_id, User.id)
407
project_group_id = Int(name="groupid")
408
project_group = Reference(project_group_id, ProjectGroup.id)
410
__init__ = _kwarg_init
413
return "<%s %r in %r>" % (type(self).__name__, self.user,
416
# WORKSHEETS AND EXERCISES #
418
class Exercise(Storm):
419
__storm_table__ = "exercise"
420
id = Unicode(primary=True, name="identifier")
422
description = Unicode()
428
worksheet_exercises = ReferenceSet(id,
429
'WorksheetExercise.exercise_id')
431
worksheets = ReferenceSet(id,
432
'WorksheetExercise.exercise_id',
433
'WorksheetExercise.worksheet_id',
437
test_suites = ReferenceSet(id,
438
'TestSuite.exercise_id',
441
__init__ = _kwarg_init
444
return "<%s %s>" % (type(self).__name__, self.name)
446
def get_permissions(self, user):
453
elif 'lecturer' in set((e.role for e in user.active_enrolments)):
459
def get_description(self):
460
return rst(self.description)
463
"""Deletes the exercise, providing it has no associated worksheets."""
464
if (self.worksheet_exercises.count() > 0):
465
raise IntegrityError()
466
for suite in self.test_suites:
468
Store.of(self).remove(self)
470
class Worksheet(Storm):
471
__storm_table__ = "worksheet"
473
id = Int(primary=True, name="worksheetid")
474
offering_id = Int(name="offeringid")
475
identifier = Unicode()
482
attempts = ReferenceSet(id, "ExerciseAttempt.worksheetid")
483
offering = Reference(offering_id, 'Offering.id')
485
all_worksheet_exercises = ReferenceSet(id,
486
'WorksheetExercise.worksheet_id')
488
# Use worksheet_exercises to get access to the *active* WorksheetExercise
489
# objects binding worksheets to exercises. This is required to access the
493
def worksheet_exercises(self):
494
return self.all_worksheet_exercises.find(active=True)
496
__init__ = _kwarg_init
499
return "<%s %s>" % (type(self).__name__, self.name)
501
# XXX Refactor this - make it an instance method of Subject rather than a
502
# class method of Worksheet. Can't do that now because Subject isn't
503
# linked referentially to the Worksheet.
505
def get_by_name(cls, store, subjectname, worksheetname):
507
Get the Worksheet from the db associated with a given store, subject
508
name and worksheet name.
510
return store.find(cls, cls.subject == unicode(subjectname),
511
cls.name == unicode(worksheetname)).one()
513
def remove_all_exercises(self):
515
Remove all exercises from this worksheet.
516
This does not delete the exercises themselves. It just removes them
519
store = Store.of(self)
520
for ws_ex in self.all_worksheet_exercises:
521
if ws_ex.saves.count() > 0 or ws_ex.attempts.count() > 0:
522
raise IntegrityError()
523
store.find(WorksheetExercise,
524
WorksheetExercise.worksheet == self).remove()
526
def get_permissions(self, user):
527
return self.offering.get_permissions(user)
530
"""Returns the xml of this worksheet, converts from rst if required."""
531
if self.format == u'rst':
532
ws_xml = rst(self.data)
538
"""Deletes the worksheet, provided it has no attempts on any exercises.
540
Returns True if delete succeeded, or False if this worksheet has
541
attempts attached."""
542
for ws_ex in self.all_worksheet_exercises:
543
if ws_ex.saves.count() > 0 or ws_ex.attempts.count() > 0:
544
raise IntegrityError()
546
self.remove_all_exercises()
547
Store.of(self).remove(self)
549
class WorksheetExercise(Storm):
550
__storm_table__ = "worksheet_exercise"
552
id = Int(primary=True, name="ws_ex_id")
554
worksheet_id = Int(name="worksheetid")
555
worksheet = Reference(worksheet_id, Worksheet.id)
556
exercise_id = Unicode(name="exerciseid")
557
exercise = Reference(exercise_id, Exercise.id)
562
saves = ReferenceSet(id, "ExerciseSave.ws_ex_id")
563
attempts = ReferenceSet(id, "ExerciseAttempt.ws_ex_id")
565
__init__ = _kwarg_init
568
return "<%s %s in %s>" % (type(self).__name__, self.exercise.name,
569
self.worksheet.identifier)
571
def get_permissions(self, user):
572
return self.worksheet.get_permissions(user)
575
class ExerciseSave(Storm):
577
Represents a potential solution to an exercise that a user has submitted
578
to the server for storage.
579
A basic ExerciseSave is just the current saved text for this exercise for
580
this user (doesn't count towards their attempts).
581
ExerciseSave may be extended with additional semantics (such as
584
__storm_table__ = "exercise_save"
585
__storm_primary__ = "ws_ex_id", "user_id"
587
ws_ex_id = Int(name="ws_ex_id")
588
worksheet_exercise = Reference(ws_ex_id, "WorksheetExercise.id")
590
user_id = Int(name="loginid")
591
user = Reference(user_id, User.id)
595
__init__ = _kwarg_init
598
return "<%s %s by %s at %s>" % (type(self).__name__,
599
self.exercise.name, self.user.login, self.date.strftime("%c"))
601
class ExerciseAttempt(ExerciseSave):
603
An ExerciseAttempt is a special case of an ExerciseSave. Like an
604
ExerciseSave, it constitutes exercise solution data that the user has
605
submitted to the server for storage.
606
In addition, it contains additional information about the submission.
607
complete - True if this submission was successful, rendering this exercise
608
complete for this user.
609
active - True if this submission is "active" (usually true). Submissions
610
may be de-activated by privileged users for special reasons, and then
611
they won't count (either as a penalty or success), but will still be
614
__storm_table__ = "exercise_attempt"
615
__storm_primary__ = "ws_ex_id", "user_id", "date"
617
# The "text" field is the same but has a different name in the DB table
619
text = Unicode(name="attempt")
623
def get_permissions(self, user):
624
return set(['view']) if user is self.user else set()
626
class TestSuite(Storm):
627
"""A Testsuite acts as a container for the test cases of an exercise."""
628
__storm_table__ = "test_suite"
629
__storm_primary__ = "exercise_id", "suiteid"
632
exercise_id = Unicode(name="exerciseid")
633
description = Unicode()
637
exercise = Reference(exercise_id, Exercise.id)
638
test_cases = ReferenceSet(suiteid, 'TestCase.suiteid', order_by="seq_no")
639
variables = ReferenceSet(suiteid, 'TestSuiteVar.suiteid', order_by='arg_no')
642
"""Delete this suite, without asking questions."""
643
for vaariable in self.variables:
645
for test_case in self.test_cases:
647
Store.of(self).remove(self)
649
class TestCase(Storm):
650
"""A TestCase is a member of a TestSuite.
652
It contains the data necessary to check if an exercise is correct"""
653
__storm_table__ = "test_case"
654
__storm_primary__ = "testid", "suiteid"
658
suite = Reference(suiteid, "TestSuite.suiteid")
661
test_default = Unicode()
664
parts = ReferenceSet(testid, "TestCasePart.testid")
666
__init__ = _kwarg_init
669
for part in self.parts:
671
Store.of(self).remove(self)
673
class TestSuiteVar(Storm):
674
"""A container for the arguments of a Test Suite"""
675
__storm_table__ = "suite_variable"
676
__storm_primary__ = "varid"
681
var_value = Unicode()
685
suite = Reference(suiteid, "TestSuite.suiteid")
687
__init__ = _kwarg_init
690
Store.of(self).remove(self)
692
class TestCasePart(Storm):
693
"""A container for the test elements of a Test Case"""
694
__storm_table__ = "test_case_part"
695
__storm_primary__ = "partid"
700
part_type = Unicode()
701
test_type = Unicode()
705
test = Reference(testid, "TestCase.testid")
707
__init__ = _kwarg_init
710
Store.of(self).remove(self)
added
removed
ivle/database.py
