« back to all changes in this revision
Viewing changes to ivle/database.py
- browse files at revision 1090
- compare with another revision
- compare with revision 1690
- download diff
- download tarball
- view history from revision 1090
- Committer: William Grant
- Date: 2009-01-22 04:47:42 UTC
- mfrom: (1080.1.93 storm)
- Revision ID: grantw@unimelb.edu.au-20090122044742-sa8gnww0ma2bm2rv
Merge Storm branch. ivle.db is dead. Watch out for the schema change.
- files added:
- ivle/database.py
- ivle/rpc
- files removed:
- ivle/auth/autherror.py
- files modified:
- bin/ivle-enrol
added
removed
ivle/database.py
1
# IVLE - Informatics Virtual Learning Environment
2
# Copyright (C) 2007-2009 The University of Melbourne
3
#
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.
8
#
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.
13
#
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
17
18
# Author: Matt Giuca, Will Grant
19
20
"""
21
Database Classes and Utilities for Storm ORM
22
23
This module provides all of the classes which map to database tables.
24
It also provides miscellaneous utility functions for database interaction.
25
"""
26
27
import md5
28
import datetime
29
30
from storm.locals import create_database, Store, Int, Unicode, DateTime, \
31
Reference, ReferenceSet, Bool, Storm, Desc
32
33
import ivle.conf
34
import ivle.caps
35
36
__all__ = ['get_store',
37
'User',
38
'Subject', 'Semester', 'Offering', 'Enrolment',
39
'ProjectSet', 'Project', 'ProjectGroup', 'ProjectGroupMembership',
40
'Exercise', 'Worksheet', 'WorksheetExercise',
41
'ExerciseSave', 'ExerciseAttempt',
42
'AlreadyEnrolledError'
43
]
44
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))
50
setattr(self, k, v)
51
52
def get_conn_string():
53
"""
54
Returns the Storm connection string, generated from the conf file.
55
"""
56
return "postgres://%s:%s@%s:%d/%s" % (ivle.conf.db_user,
57
ivle.conf.db_password, ivle.conf.db_host, ivle.conf.db_port,
58
ivle.conf.db_dbname)
59
60
def get_store():
61
"""
62
Open a database connection and transaction. Return a storm.store.Store
63
instance connected to the configured IVLE database.
64
"""
65
return Store(create_database(get_conn_string()))
66
67
# USERS #
68
69
class User(Storm):
70
"""
71
Represents an IVLE user.
72
"""
73
__storm_table__ = "login"
74
75
id = Int(primary=True, name="loginid")
76
login = Unicode()
77
passhash = Unicode()
78
state = Unicode()
79
rolenm = Unicode()
80
unixid = Int()
81
nick = Unicode()
82
pass_exp = DateTime()
83
acct_exp = DateTime()
84
last_login = DateTime()
85
svn_pass = Unicode()
86
email = Unicode()
87
fullname = Unicode()
88
studentid = Unicode()
89
settings = Unicode()
90
91
def _get_role(self):
92
if self.rolenm is None:
93
return None
94
return ivle.caps.Role(self.rolenm)
95
def _set_role(self, value):
96
if not isinstance(value, ivle.caps.Role):
97
raise TypeError("role must be an ivle.caps.Role")
98
self.rolenm = unicode(value)
99
role = property(_get_role, _set_role)
100
101
__init__ = _kwarg_init
102
103
def __repr__(self):
104
return "<%s '%s'>" % (type(self).__name__, self.login)
105
106
def authenticate(self, password):
107
"""Validate a given password against this user.
108
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
111
user.
112
"""
113
if self.passhash is None:
114
return None
115
return self.hash_password(password) == self.passhash
116
117
def hasCap(self, capability):
118
"""Given a capability (which is a Role object), returns True if this
119
User has that capability, False otherwise.
120
"""
121
return self.role.hasCap(capability)
122
123
@property
124
def password_expired(self):
125
fieldval = self.pass_exp
126
return fieldval is not None and datetime.datetime.now() > fieldval
127
128
@property
129
def account_expired(self):
130
fieldval = self.acct_exp
131
return fieldval is not None and datetime.datetime.now() > fieldval
132
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(
140
Desc(Semester.year),
141
Desc(Semester.semester),
142
Desc(Subject.code)
143
)
144
145
def _set_password(self, password):
146
if password is None:
147
self.passhash = None
148
else:
149
self.passhash = unicode(User.hash_password(password))
150
password = property(fset=_set_password)
151
152
@property
153
def subjects(self):
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)
159
160
# TODO: Invitations should be listed too?
161
def get_groups(self, offering=None):
162
preds = [
163
ProjectGroupMembership.user_id == self.id,
164
ProjectGroup.id == ProjectGroupMembership.project_group_id,
165
]
166
if offering:
167
preds.extend([
168
ProjectSet.offering_id == offering.id,
169
ProjectGroup.project_set_id == ProjectSet.id,
170
])
171
return Store.of(self).find(ProjectGroup, *preds)
172
173
@property
174
def groups(self):
175
return self.get_groups()
176
177
@property
178
def active_enrolments(self):
179
'''A sanely ordered list of the user's active enrolments.'''
180
return self._get_enrolments(True)
181
182
@property
183
def enrolments(self):
184
'''A sanely ordered list of all of the user's enrolments.'''
185
return self._get_enrolments(False)
186
187
@staticmethod
188
def hash_password(password):
189
return md5.md5(password).hexdigest()
190
191
@classmethod
192
def get_by_login(cls, store, login):
193
"""
194
Get the User from the db associated with a given store and
195
login.
196
"""
197
return store.find(cls, cls.login == unicode(login)).one()
198
199
# SUBJECTS AND ENROLMENTS #
200
201
class Subject(Storm):
202
__storm_table__ = "subject"
203
204
id = Int(primary=True, name="subjectid")
205
code = Unicode(name="subj_code")
206
name = Unicode(name="subj_name")
207
short_name = Unicode(name="subj_short_name")
208
url = Unicode()
209
210
offerings = ReferenceSet(id, 'Offering.subject_id')
211
212
__init__ = _kwarg_init
213
214
def __repr__(self):
215
return "<%s '%s'>" % (type(self).__name__, self.short_name)
216
217
class Semester(Storm):
218
__storm_table__ = "semester"
219
220
id = Int(primary=True, name="semesterid")
221
year = Unicode()
222
semester = Unicode()
223
active = Bool()
224
225
offerings = ReferenceSet(id, 'Offering.semester_id')
226
227
__init__ = _kwarg_init
228
229
def __repr__(self):
230
return "<%s %s/%s>" % (type(self).__name__, self.year, self.semester)
231
232
class Offering(Storm):
233
__storm_table__ = "offering"
234
235
id = Int(primary=True, name="offeringid")
236
subject_id = Int(name="subject")
237
subject = Reference(subject_id, Subject.id)
238
semester_id = Int(name="semesterid")
239
semester = Reference(semester_id, Semester.id)
240
groups_student_permissions = Unicode()
241
242
enrolments = ReferenceSet(id, 'Enrolment.offering_id')
243
members = ReferenceSet(id,
244
'Enrolment.offering_id',
245
'Enrolment.user_id',
246
'User.id')
247
project_sets = ReferenceSet(id, 'ProjectSet.offering_id')
248
249
__init__ = _kwarg_init
250
251
def __repr__(self):
252
return "<%s %r in %r>" % (type(self).__name__, self.subject,
253
self.semester)
254
255
def enrol(self, user):
256
'''Enrol a user in this offering.'''
257
# We'll get a horrible database constraint violation error if we try
258
# to add a second enrolment.
259
if Store.of(self).find(Enrolment,
260
Enrolment.user_id == user.id,
261
Enrolment.offering_id == self.id).count() == 1:
262
raise AlreadyEnrolledError()
263
264
e = Enrolment(user=user, offering=self, active=True)
265
self.enrolments.add(e)
266
267
class Enrolment(Storm):
268
__storm_table__ = "enrolment"
269
__storm_primary__ = "user_id", "offering_id"
270
271
user_id = Int(name="loginid")
272
user = Reference(user_id, User.id)
273
offering_id = Int(name="offeringid")
274
offering = Reference(offering_id, Offering.id)
275
notes = Unicode()
276
active = Bool()
277
278
@property
279
def groups(self):
280
return Store.of(self).find(ProjectGroup,
281
ProjectSet.offering_id == self.offering.id,
282
ProjectGroup.project_set_id == ProjectSet.id,
283
ProjectGroupMembership.project_group_id == ProjectGroup.id,
284
ProjectGroupMembership.user_id == self.user.id)
285
286
__init__ = _kwarg_init
287
288
def __repr__(self):
289
return "<%s %r in %r>" % (type(self).__name__, self.user,
290
self.offering)
291
292
class AlreadyEnrolledError(Exception):
293
pass
294
295
# PROJECTS #
296
297
class ProjectSet(Storm):
298
__storm_table__ = "project_set"
299
300
id = Int(name="projectsetid", primary=True)
301
offering_id = Int(name="offeringid")
302
offering = Reference(offering_id, Offering.id)
303
max_students_per_group = Int()
304
305
projects = ReferenceSet(id, 'Project.project_set_id')
306
project_groups = ReferenceSet(id, 'ProjectGroup.project_set_id')
307
308
__init__ = _kwarg_init
309
310
def __repr__(self):
311
return "<%s %d in %r>" % (type(self).__name__, self.id,
312
self.offering)
313
314
class Project(Storm):
315
__storm_table__ = "project"
316
317
id = Int(name="projectid", primary=True)
318
synopsis = Unicode()
319
url = Unicode()
320
project_set_id = Int(name="projectsetid")
321
project_set = Reference(project_set_id, ProjectSet.id)
322
deadline = DateTime()
323
324
__init__ = _kwarg_init
325
326
def __repr__(self):
327
return "<%s '%s' in %r>" % (type(self).__name__, self.synopsis,
328
self.project_set.offering)
329
330
class ProjectGroup(Storm):
331
__storm_table__ = "project_group"
332
333
id = Int(name="groupid", primary=True)
334
name = Unicode(name="groupnm")
335
project_set_id = Int(name="projectsetid")
336
project_set = Reference(project_set_id, ProjectSet.id)
337
nick = Unicode()
338
created_by_id = Int(name="createdby")
339
created_by = Reference(created_by_id, User.id)
340
epoch = DateTime()
341
342
members = ReferenceSet(id,
343
"ProjectGroupMembership.project_group_id",
344
"ProjectGroupMembership.user_id",
345
"User.id")
346
347
__init__ = _kwarg_init
348
349
def __repr__(self):
350
return "<%s %s in %r>" % (type(self).__name__, self.name,
351
self.project_set.offering)
352
353
class ProjectGroupMembership(Storm):
354
__storm_table__ = "group_member"
355
__storm_primary__ = "user_id", "project_group_id"
356
357
user_id = Int(name="loginid")
358
user = Reference(user_id, User.id)
359
project_group_id = Int(name="groupid")
360
project_group = Reference(project_group_id, ProjectGroup.id)
361
362
__init__ = _kwarg_init
363
364
def __repr__(self):
365
return "<%s %r in %r>" % (type(self).__name__, self.user,
366
self.project_group)
367
368
# WORKSHEETS AND EXERCISES #
369
370
class Exercise(Storm):
371
# Note: Table "problem" is called "Exercise" in the Object layer, since
372
# it's called that everywhere else.
373
__storm_table__ = "problem"
374
375
id = Int(primary=True, name="problemid")
376
name = Unicode(name="identifier")
377
spec = Unicode()
378
379
worksheets = ReferenceSet(id,
380
'WorksheetExercise.exercise_id',
381
'WorksheetExercise.worksheet_id',
382
'Worksheet.id'
383
)
384
385
__init__ = _kwarg_init
386
387
def __repr__(self):
388
return "<%s %s>" % (type(self).__name__, self.name)
389
390
@classmethod
391
def get_by_name(cls, store, name):
392
"""
393
Get the Exercise from the db associated with a given store and name.
394
If the exercise is not in the database, creates it and inserts it
395
automatically.
396
"""
397
ex = store.find(cls, cls.name == unicode(name)).one()
398
if ex is not None:
399
return ex
400
ex = Exercise(name=unicode(name))
401
store.add(ex)
402
store.commit()
403
return ex
404
405
class Worksheet(Storm):
406
__storm_table__ = "worksheet"
407
408
id = Int(primary=True, name="worksheetid")
409
# XXX subject is not linked to a Subject object. This is a property of
410
# the database, and will be refactored.
411
subject = Unicode()
412
name = Unicode(name="identifier")
413
assessable = Bool()
414
mtime = DateTime()
415
416
exercises = ReferenceSet(id,
417
'WorksheetExercise.worksheet_id',
418
'WorksheetExercise.exercise_id',
419
Exercise.id)
420
# Use worksheet_exercises to get access to the WorksheetExercise objects
421
# binding worksheets to exercises. This is required to access the
422
# "optional" field.
423
worksheet_exercises = ReferenceSet(id,
424
'WorksheetExercise.worksheet_id')
425
426
__init__ = _kwarg_init
427
428
def __repr__(self):
429
return "<%s %s>" % (type(self).__name__, self.name)
430
431
# XXX Refactor this - make it an instance method of Subject rather than a
432
# class method of Worksheet. Can't do that now because Subject isn't
433
# linked referentially to the Worksheet.
434
@classmethod
435
def get_by_name(cls, store, subjectname, worksheetname):
436
"""
437
Get the Worksheet from the db associated with a given store, subject
438
name and worksheet name.
439
"""
440
return store.find(cls, cls.subject == unicode(subjectname),
441
cls.name == unicode(worksheetname)).one()
442
443
def remove_all_exercises(self, store):
444
"""
445
Remove all exercises from this worksheet.
446
This does not delete the exercises themselves. It just removes them
447
from the worksheet.
448
"""
449
store.find(WorksheetExercise,
450
WorksheetExercise.worksheet == self).remove()
451
452
class WorksheetExercise(Storm):
453
__storm_table__ = "worksheet_problem"
454
__storm_primary__ = "worksheet_id", "exercise_id"
455
456
worksheet_id = Int(name="worksheetid")
457
worksheet = Reference(worksheet_id, Worksheet.id)
458
exercise_id = Int(name="problemid")
459
exercise = Reference(exercise_id, Exercise.id)
460
optional = Bool()
461
462
__init__ = _kwarg_init
463
464
def __repr__(self):
465
return "<%s %s in %s>" % (type(self).__name__, self.exercise.name,
466
self.worksheet.name)
467
468
class ExerciseSave(Storm):
469
"""
470
Represents a potential solution to an exercise that a user has submitted
471
to the server for storage.
472
A basic ExerciseSave is just the current saved text for this exercise for
473
this user (doesn't count towards their attempts).
474
ExerciseSave may be extended with additional semantics (such as
475
ExerciseAttempt).
476
"""
477
__storm_table__ = "problem_save"
478
__storm_primary__ = "exercise_id", "user_id", "date"
479
480
exercise_id = Int(name="problemid")
481
exercise = Reference(exercise_id, Exercise.id)
482
user_id = Int(name="loginid")
483
user = Reference(user_id, User.id)
484
date = DateTime()
485
text = Unicode()
486
487
__init__ = _kwarg_init
488
489
def __repr__(self):
490
return "<%s %s by %s at %s>" % (type(self).__name__,
491
self.exercise.name, self.user.login, self.date.strftime("%c"))
492
493
class ExerciseAttempt(ExerciseSave):
494
"""
495
An ExerciseAttempt is a special case of an ExerciseSave. Like an
496
ExerciseSave, it constitutes exercise solution data that the user has
497
submitted to the server for storage.
498
In addition, it contains additional information about the submission.
499
complete - True if this submission was successful, rendering this exercise
500
complete for this user.
501
active - True if this submission is "active" (usually true). Submissions
502
may be de-activated by privileged users for special reasons, and then
503
they won't count (either as a penalty or success), but will still be
504
stored.
505
"""
506
__storm_table__ = "problem_attempt"
507
__storm_primary__ = "exercise_id", "user_id", "date"
508
509
# The "text" field is the same but has a different name in the DB table
510
# for some reason.
511
text = Unicode(name="attempt")
512
complete = Bool()
513
active = Bool()
Loggerhead is a web-based interface for Breezy
Version: 2.0.1