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')
238
enrolments = ReferenceSet(id,
239
'Offering.semester_id',
241
'Enrolment.offering_id')
243
__init__ = _kwarg_init
246
return "<%s %s/%s>" % (type(self).__name__, self.year, self.semester)
248
class Offering(Storm):
249
__storm_table__ = "offering"
251
id = Int(primary=True, name="offeringid")
252
subject_id = Int(name="subject")
253
subject = Reference(subject_id, Subject.id)
254
semester_id = Int(name="semesterid")
255
semester = Reference(semester_id, Semester.id)
256
groups_student_permissions = Unicode()
258
enrolments = ReferenceSet(id, 'Enrolment.offering_id')
259
members = ReferenceSet(id,
260
'Enrolment.offering_id',
263
project_sets = ReferenceSet(id, 'ProjectSet.offering_id')
265
worksheets = ReferenceSet(id,
266
'Worksheet.offering_id',
267
order_by="Worksheet.seq_no"
270
__init__ = _kwarg_init
273
return "<%s %r in %r>" % (type(self).__name__, self.subject,
276
def enrol(self, user, role=u'student'):
277
'''Enrol a user in this offering.'''
278
enrolment = Store.of(self).find(Enrolment,
279
Enrolment.user_id == user.id,
280
Enrolment.offering_id == self.id).one()
282
if enrolment is None:
283
enrolment = Enrolment(user=user, offering=self)
284
self.enrolments.add(enrolment)
286
enrolment.active = True
287
enrolment.role = role
289
def get_permissions(self, user):
297
class Enrolment(Storm):
298
__storm_table__ = "enrolment"
299
__storm_primary__ = "user_id", "offering_id"
301
user_id = Int(name="loginid")
302
user = Reference(user_id, User.id)
303
offering_id = Int(name="offeringid")
304
offering = Reference(offering_id, Offering.id)
311
return Store.of(self).find(ProjectGroup,
312
ProjectSet.offering_id == self.offering.id,
313
ProjectGroup.project_set_id == ProjectSet.id,
314
ProjectGroupMembership.project_group_id == ProjectGroup.id,
315
ProjectGroupMembership.user_id == self.user.id)
317
__init__ = _kwarg_init
320
return "<%s %r in %r>" % (type(self).__name__, self.user,
325
class ProjectSet(Storm):
326
__storm_table__ = "project_set"
328
id = Int(name="projectsetid", primary=True)
329
offering_id = Int(name="offeringid")
330
offering = Reference(offering_id, Offering.id)
331
max_students_per_group = Int()
333
projects = ReferenceSet(id, 'Project.project_set_id')
334
project_groups = ReferenceSet(id, 'ProjectGroup.project_set_id')
336
__init__ = _kwarg_init
339
return "<%s %d in %r>" % (type(self).__name__, self.id,
342
class Project(Storm):
343
__storm_table__ = "project"
345
id = Int(name="projectid", primary=True)
348
project_set_id = Int(name="projectsetid")
349
project_set = Reference(project_set_id, ProjectSet.id)
350
deadline = DateTime()
352
__init__ = _kwarg_init
355
return "<%s '%s' in %r>" % (type(self).__name__, self.synopsis,
356
self.project_set.offering)
358
class ProjectGroup(Storm):
359
__storm_table__ = "project_group"
361
id = Int(name="groupid", primary=True)
362
name = Unicode(name="groupnm")
363
project_set_id = Int(name="projectsetid")
364
project_set = Reference(project_set_id, ProjectSet.id)
366
created_by_id = Int(name="createdby")
367
created_by = Reference(created_by_id, User.id)
370
members = ReferenceSet(id,
371
"ProjectGroupMembership.project_group_id",
372
"ProjectGroupMembership.user_id",
375
__init__ = _kwarg_init
378
return "<%s %s in %r>" % (type(self).__name__, self.name,
379
self.project_set.offering)
381
class ProjectGroupMembership(Storm):
382
__storm_table__ = "group_member"
383
__storm_primary__ = "user_id", "project_group_id"
385
user_id = Int(name="loginid")
386
user = Reference(user_id, User.id)
387
project_group_id = Int(name="groupid")
388
project_group = Reference(project_group_id, ProjectGroup.id)
390
__init__ = _kwarg_init
393
return "<%s %r in %r>" % (type(self).__name__, self.user,
396
# WORKSHEETS AND EXERCISES #
398
class Exercise(Storm):
399
__storm_table__ = "exercise"
400
id = Unicode(primary=True, name="identifier")
402
description = Unicode()
408
worksheets = ReferenceSet(id,
409
'WorksheetExercise.exercise_id',
410
'WorksheetExercise.worksheet_id',
414
test_suites = ReferenceSet(id, 'TestSuite.exercise_id')
416
__init__ = _kwarg_init
419
return "<%s %s>" % (type(self).__name__, self.name)
421
def get_permissions(self, user):
429
class Worksheet(Storm):
430
__storm_table__ = "worksheet"
432
id = Int(primary=True, name="worksheetid")
433
offering_id = Int(name="offeringid")
434
identifier = Unicode()
441
attempts = ReferenceSet(id, "ExerciseAttempt.worksheetid")
442
offering = Reference(offering_id, 'Offering.id')
444
all_worksheet_exercises = ReferenceSet(id,
445
'WorksheetExercise.worksheet_id')
447
# Use worksheet_exercises to get access to the *active* WorksheetExercise
448
# 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)
483
class WorksheetExercise(Storm):
484
__storm_table__ = "worksheet_exercise"
486
id = Int(primary=True, name="ws_ex_id")
488
worksheet_id = Int(name="worksheetid")
489
worksheet = Reference(worksheet_id, Worksheet.id)
490
exercise_id = Unicode(name="exerciseid")
491
exercise = Reference(exercise_id, Exercise.id)
496
saves = ReferenceSet(id, "ExerciseSave.ws_ex_id")
497
attempts = ReferenceSet(id, "ExerciseAttempt.ws_ex_id")
499
__init__ = _kwarg_init
502
return "<%s %s in %s>" % (type(self).__name__, self.exercise.name,
503
self.worksheet.identifier)
505
class ExerciseSave(Storm):
507
Represents a potential solution to an exercise that a user has submitted
508
to the server for storage.
509
A basic ExerciseSave is just the current saved text for this exercise for
510
this user (doesn't count towards their attempts).
511
ExerciseSave may be extended with additional semantics (such as
514
__storm_table__ = "exercise_save"
515
__storm_primary__ = "ws_ex_id", "user_id"
517
ws_ex_id = Int(name="ws_ex_id")
518
worksheet_exercise = Reference(ws_ex_id, "WorksheetExercise.id")
520
user_id = Int(name="loginid")
521
user = Reference(user_id, User.id)
525
__init__ = _kwarg_init
528
return "<%s %s by %s at %s>" % (type(self).__name__,
529
self.exercise.name, self.user.login, self.date.strftime("%c"))
531
class ExerciseAttempt(ExerciseSave):
533
An ExerciseAttempt is a special case of an ExerciseSave. Like an
534
ExerciseSave, it constitutes exercise solution data that the user has
535
submitted to the server for storage.
536
In addition, it contains additional information about the submission.
537
complete - True if this submission was successful, rendering this exercise
538
complete for this user.
539
active - True if this submission is "active" (usually true). Submissions
540
may be de-activated by privileged users for special reasons, and then
541
they won't count (either as a penalty or success), but will still be
544
__storm_table__ = "exercise_attempt"
545
__storm_primary__ = "ws_ex_id", "user_id", "date"
547
# The "text" field is the same but has a different name in the DB table
549
text = Unicode(name="attempt")
553
def get_permissions(self, user):
554
return set(['view']) if user is self.user else set()
556
class TestSuite(Storm):
557
"""A Testsuite acts as a container for the test cases of an exercise."""
558
__storm_table__ = "test_suite"
559
__storm_primary__ = "exercise_id", "suiteid"
562
exercise_id = Unicode(name="exerciseid")
563
description = Unicode()
567
exercise = Reference(exercise_id, Exercise.id)
568
test_cases = ReferenceSet(suiteid, 'TestCase.suiteid')
569
variables = ReferenceSet(suiteid, 'TestSuiteVar.suiteid')
571
class TestCase(Storm):
572
"""A TestCase is a member of a TestSuite.
574
It contains the data necessary to check if an exercise is correct"""
575
__storm_table__ = "test_case"
576
__storm_primary__ = "testid", "suiteid"
580
suite = Reference(suiteid, "TestSuite.suiteid")
583
test_default = Unicode()
586
parts = ReferenceSet(testid, "TestCasePart.testid")
588
__init__ = _kwarg_init
590
class TestSuiteVar(Storm):
591
"""A container for the arguments of a Test Suite"""
592
__storm_table__ = "suite_variable"
593
__storm_primary__ = "varid"
598
var_value = Unicode()
602
suite = Reference(suiteid, "TestSuite.suiteid")
604
__init__ = _kwarg_init
606
class TestCasePart(Storm):
607
"""A container for the test elements of a Test Case"""
608
__storm_table__ = "test_case_part"
609
__storm_primary__ = "partid"
614
part_type = Unicode()
615
test_type = Unicode()
619
test = Reference(testid, "TestCase.testid")
621
__init__ = _kwarg_init
added
removed
ivle/database.py
