1
# IVLE - Informatics Virtual Learning Environment
2
# Copyright (C) 2007-2008 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
22
# Code to talk to the PostgreSQL database.
23
# (This is the Data Access Layer).
24
# All DB code should be in this module to ensure portability if we want to
25
# change the DB implementation.
26
# This means no SQL strings should be outside of this module. Add functions
27
# here to perform the activities needed, and place the SQL code for those
30
# CAUTION to editors of this module.
31
# All string inputs must be sanitized by calling _escape before being
32
# formatted into an SQL query string.
42
TIMESTAMP_FORMAT = '%Y-%m-%d %H:%M:%S'
45
"""Wrapper around pg.escape_string. Prepares the Python value for use in
46
SQL. Returns a string, which may be safely placed verbatim into an SQL
48
Handles the following types:
49
* str: Escapes the string, and also quotes it.
50
* int/long/float: Just converts to an unquoted string.
51
* bool: Returns as "TRUE" or "FALSE", unquoted.
52
* NoneType: Returns "NULL", unquoted.
53
* common.caps.Role: Returns the role as a quoted, lowercase string.
54
* time.struct_time: Returns the time as a quoted string for insertion into
56
Raises a DBException if val has an unsupported type.
58
# "E'" is postgres's way of making "escape" strings.
59
# Such strings allow backslashes to escape things. Since escape_string
60
# converts a single backslash into two backslashes, it needs to be fed
62
# Ref: http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html
63
# WARNING: PostgreSQL-specific code
66
elif isinstance(val, str) or isinstance(val, unicode):
67
return "E'" + pg.escape_string(val) + "'"
68
elif isinstance(val, bool):
69
return "TRUE" if val else "FALSE"
70
elif isinstance(val, int) or isinstance(val, long) \
71
or isinstance(val, float):
73
elif isinstance(val, caps.Role):
74
return _escape(str(val))
75
elif isinstance(val, time.struct_time):
76
return _escape(time.strftime(TIMESTAMP_FORMAT, val))
78
raise DBException("Attempt to insert an unsupported type "
79
"into the database (%s)" % repr(type(val)))
81
def _parse_boolean(val):
83
Accepts a boolean as output from the DB (either the string 't' or 'f').
84
Returns a boolean value True or False.
85
Also accepts other values which mean True or False in PostgreSQL.
86
If none match, raises a DBException.
88
# On a personal note, what sort of a language allows 7 different values
89
# to denote each of True and False?? (A: SQL)
90
if isinstance(val, bool):
96
elif val == 'true' or val == 'y' or val == 'yes' or val == '1' \
99
elif val == 'false' or val == 'n' or val == 'no' or val == '0' \
103
raise DBException("Invalid boolean value returned from DB")
105
def _passhash(password):
106
return md5.md5(password).hexdigest()
108
class DBException(Exception):
109
"""A DBException is for bad conditions in the database or bad input to
110
these methods. If Postgres throws an exception it does not get rebadged.
111
This is only for additional exceptions."""
115
"""An IVLE database object. This object provides an interface to
116
interacting with the IVLE database without using any external SQL.
118
Most methods of this class have an optional dry argument. If true, they
119
will return the SQL query string and NOT actually execute it. (For
122
Methods may throw db.DBException, or any of the pg exceptions as well.
123
(In general, be prepared to catch exceptions!)
126
"""Connects to the database and creates a DB object.
127
Takes no parameters - gets all the DB info from the configuration."""
129
self.db = pg.connect(host=ivle.conf.db_host, port=ivle.conf.db_port,
130
dbname=ivle.conf.db_dbname,
131
user=ivle.conf.db_user, passwd=ivle.conf.db_password)
138
# GENERIC DB FUNCTIONS #
141
def check_dict(dict, tablefields, disallowed=frozenset([]), must=False):
142
"""Checks that a dict does not contain keys that are not fields
143
of the specified table.
144
dict: A mapping from string keys to values; the keys are checked to
145
see that they correspond to login table fields.
146
tablefields: Collection of strings for field names in the table.
147
Only these fields will be allowed.
148
disallowed: Optional collection of strings for field names that are
150
must: If True, the dict MUST contain all fields in tablefields.
151
If False, it may contain any subset of the fields.
152
Returns True if the dict is valid, False otherwise.
154
allowed = frozenset(tablefields) - frozenset(disallowed)
155
dictkeys = frozenset(dict.keys())
157
return allowed == dictkeys
159
return allowed.issuperset(dictkeys)
161
def insert(self, dict, tablename, tablefields, disallowed=frozenset([]),
163
"""Inserts a new row in a table, using data from a supplied
164
dictionary (which will be checked by check_dict).
165
dict: Dictionary mapping column names to values. The values may be
166
any of the following types:
167
str, int, long, float, NoneType.
168
tablename: String, name of the table to insert into. Will NOT be
169
escaped - must be a valid identifier.
170
tablefields, disallowed: see check_dict.
171
dry: Returns the SQL query as a string, and does not execute it.
172
Raises a DBException if the dictionary contains invalid fields.
174
if not DB.check_dict(dict, tablefields, disallowed):
175
extras = set(dict.keys()) - tablefields
176
raise DBException("Supplied dictionary contains invalid fields. (%s)" % (repr(extras)))
177
# Build two lists concurrently: field names and values, as SQL strings
180
for k,v in dict.items():
182
values.append(_escape(v))
183
if len(fieldnames) == 0: return
184
fieldnames = ', '.join(fieldnames)
185
values = ', '.join(values)
186
query = ("INSERT INTO %s (%s) VALUES (%s);"
187
% (tablename, fieldnames, values))
191
def return_insert(self, dict, tablename, tablefields, returning,
192
disallowed=frozenset([]), dry=False):
193
"""Inserts a new row in a table, using data from a supplied
194
dictionary (which will be checked by check_dict) and returns certain
196
dict: Dictionary mapping column names to values. The values may be
197
any of the following types:
198
str, int, long, float, NoneType.
199
tablename: String, name of the table to insert into. Will NOT be
200
escaped - must be a valid identifier.
201
returning: List of fields to return, not escaped
202
tablefields, disallowed: see check_dict.
203
dry: Returns the SQL query as a string, and does not execute it.
204
Raises a DBException if the dictionary contains invalid fields.
206
if not DB.check_dict(dict, tablefields, disallowed):
207
extras = set(dict.keys()) - tablefields
208
raise DBException("Supplied dictionary contains invalid fields. (%s)" % (repr(extras)))
209
# Build two lists concurrently: field names and values, as SQL strings
212
for k,v in dict.items():
214
values.append(_escape(v))
215
if len(fieldnames) == 0: return
216
fieldnames = ', '.join(fieldnames)
217
values = ', '.join(values)
218
returns = ', '.join(returning)
219
query = ("INSERT INTO %s (%s) VALUES (%s) RETURNING (%s);"
220
% (tablename, fieldnames, values, returns))
222
return self.db.query(query)
225
def update(self, primarydict, updatedict, tablename, tablefields,
226
primary_keys, disallowed_update=frozenset([]), dry=False):
227
"""Updates a row in a table, matching against primarydict to find the
228
row, and using the data in updatedict (which will be checked by
230
primarydict: Dict mapping column names to values. The keys should be
231
the table's primary key. Only rows which match this dict's values
233
updatedict: Dict mapping column names to values. The columns will be
234
updated with the given values for the matched rows.
235
tablename, tablefields, disallowed_update: See insert.
236
primary_keys: Collection of strings which together form the primary
237
key for this table. primarydict must contain all of these as keys,
240
if (not (DB.check_dict(primarydict, primary_keys, must=True)
241
and DB.check_dict(updatedict, tablefields, disallowed_update))):
242
raise DBException("Supplied dictionary contains invalid or missing fields (1).")
243
# Make a list of SQL fragments of the form "field = 'new value'"
244
# These fragments are ALREADY-ESCAPED
246
for k,v in updatedict.items():
247
setlist.append("%s = %s" % (k, _escape(v)))
249
for k,v in primarydict.items():
250
wherelist.append("%s = %s" % (k, _escape(v)))
251
if len(setlist) == 0 or len(wherelist) == 0:
253
# Join the fragments into a comma-separated string
254
setstring = ', '.join(setlist)
255
wherestring = ' AND '.join(wherelist)
256
# Build the whole query as an UPDATE statement
257
query = ("UPDATE %s SET %s WHERE %s;"
258
% (tablename, setstring, wherestring))
262
def get_single(self, primarydict, tablename, getfields, primary_keys,
263
error_notfound="No rows found", dry=False):
264
"""Retrieves a single row from a table, returning it as a dictionary
265
mapping field names to values. Matches against primarydict to find the
267
primarydict, tablename, primary_keys: See update/delete.
268
getfields: Collection of strings; the field names which will be
269
returned as keys in the dictionary.
270
error_notfound: Error message if 0 rows match.
271
Raises a DBException if 0 rows match, with error_notfound as the msg.
272
Raises an AssertError if >1 rows match (this should not happen if
273
primary_keys is indeed the primary key).
275
if not DB.check_dict(primarydict, primary_keys, must=True):
276
raise DBException("Supplied dictionary contains invalid or missing fields (3).")
278
for k,v in primarydict.items():
279
wherelist.append("%s = %s" % (k, _escape(v)))
280
if len(getfields) == 0 or len(wherelist) == 0:
282
# Join the fragments into a comma-separated string
283
getstring = ', '.join(getfields)
284
wherestring = ' AND '.join(wherelist)
285
# Build the whole query as an SELECT statement
286
query = ("SELECT %s FROM %s WHERE %s;"
287
% (getstring, tablename, wherestring))
289
result = self.db.query(query)
290
# Expecting exactly one
291
if result.ntuples() != 1:
292
# It should not be possible for ntuples to be greater than 1
293
assert (result.ntuples() < 1)
294
raise DBException(error_notfound)
295
# Return as a dictionary
296
return result.dictresult()[0]
298
def start_transaction(self, dry=False):
299
"""Starts a DB transaction.
300
Will not commit any changes until self.commit() is called.
302
query = "START TRANSACTION;"
306
def commit(self, dry=False):
307
"""Commits (ends) a DB transaction.
308
Commits all changes since the call to start_transaction.
314
def rollback(self, dry=False):
315
"""Rolls back (ends) a DB transaction, undoing all changes since the
316
call to start_transaction.
322
# USER MANAGEMENT FUNCTIONS #
324
login_primary = frozenset(["login"])
325
login_fields_list = [
326
"login", "passhash", "state", "unixid", "email", "nick", "fullname",
327
"rolenm", "studentid", "acct_exp", "pass_exp", "last_login", "svn_pass"
329
login_fields = frozenset(login_fields_list)
331
def create_user(self, user_obj=None, dry=False, **kwargs):
332
"""Creates a user login entry in the database.
333
Two ways to call this - passing a user object, or passing
334
all fields as separate arguments.
336
Either pass a "user_obj" as the first argument (in which case other
337
fields will be ignored), or pass all fields as arguments.
339
All user fields are to be passed as args. The argument names
340
are the field names of the "login" table of the DB schema.
341
However, instead of supplying a "passhash", you must supply a
342
"password" argument, which will be hashed internally.
343
Also "state" must not given explicitly; it is implicitly set to
345
Raises an exception if the user already exists, or the dict contains
346
invalid keys or is missing required keys.
348
if 'passhash' in kwargs:
349
raise DBException("Supplied arguments include passhash (invalid) (1).")
350
# Make a copy of the dict. Change password to passhash (hashing it),
351
# and set 'state' to "no_agreement".
354
fields = copy.copy(kwargs)
356
# Use the user object
357
fields = dict(user_obj)
358
if 'password' in fields:
359
fields['passhash'] = _passhash(fields['password'])
360
del fields['password']
362
# Convert role to rolenm
363
fields['rolenm'] = str(user_obj.role)
366
fields['state'] = "no_agreement"
367
# else, we'll trust the user, but it SHOULD be "no_agreement"
368
# (We can't change it because then the user object would not
370
if 'local_password' in fields:
371
del fields['local_password']
373
return self.insert(fields, "login", self.login_fields, dry=dry)
375
def get_user_loginid(self, login, dry=False):
376
"""Given a login, returns the integer loginid for this user.
378
Raises a DBException if the login is not found in the DB.
380
userdict = self.get_single({"login": login}, "login",
381
['loginid'], self.login_primary,
382
error_notfound="get_user_loginid: No user with that login name",
385
return userdict # Query string
386
return userdict['loginid']
388
# PROBLEM AND PROBLEM ATTEMPT FUNCTIONS #
390
def get_problem_problemid(self, exercisename, dry=False):
391
"""Given an exercise name, returns the associated problemID.
392
If the exercise name is NOT in the database, it inserts it and returns
393
the new problemID. Hence this may mutate the DB, but is idempotent.
396
d = self.get_single({"identifier": exercisename}, "problem",
397
['problemid'], frozenset(["identifier"]),
400
return d # Query string
403
# Shouldn't try again, must have failed for some other reason
405
# if we failed to get a problemid, it was probably because
406
# the exercise wasn't in the db. So lets insert it!
408
# The insert can fail if someone else simultaneously does
409
# the insert, so if the insert fails, we ignore the problem.
411
self.insert({'identifier': exercisename}, "problem",
412
frozenset(['identifier']))
416
# Assuming the insert succeeded, we should be able to get the
418
d = self.get_single({"identifier": exercisename}, "problem",
419
['problemid'], frozenset(["identifier"]))
421
return d['problemid']
423
def insert_problem_attempt(self, login, exercisename, date, complete,
425
"""Inserts the details of a problem attempt into the database.
426
exercisename: Name of the exercise. (identifier field of problem
427
table). If this exercise does not exist, also creates a new row in
428
the problem table for this exercise name.
429
login: Name of the user submitting the attempt. (login field of the
431
date: struct_time, the date this attempt was made.
432
complete: bool. Whether the test passed or not.
433
attempt: Text of the attempt.
435
Note: Even if dry, will still physically call get_problem_problemid,
436
which may mutate the DB, and get_user_loginid, which may fail.
438
problemid = self.get_problem_problemid(exercisename)
439
loginid = self.get_user_loginid(login) # May raise a DBException
442
'problemid': problemid,
445
'complete': complete,
447
}, 'problem_attempt',
448
frozenset(['problemid','loginid','date','complete','attempt']),
451
def write_problem_save(self, login, exercisename, date, text, dry=False):
452
"""Writes text to the problem_save table (for when the user saves an
453
exercise). Creates a new row, or overwrites an existing one if the
454
user has already saved that problem.
455
(Unlike problem_attempt, does not keep historical records).
457
problemid = self.get_problem_problemid(exercisename)
458
loginid = self.get_user_loginid(login) # May raise a DBException
462
'problemid': problemid,
467
frozenset(['problemid','loginid','date','text']),
469
except pg.ProgrammingError:
470
# May have failed because this problemid/loginid row already
471
# exists (they have a unique key constraint).
472
# Do an update instead.
474
# Shouldn't try again, must have failed for some other reason
477
'problemid': problemid,
484
frozenset(['date', 'text']),
485
frozenset(['problemid', 'loginid']))
487
# SUBJECTS AND ENROLEMENT
489
def get_offering_semesters(self, subjectid, dry=False):
491
Get the semester information for a subject as well as providing
492
information about if the subject is active and which semester it is in.
495
SELECT offeringid, subj_name, year, semester, active
496
FROM semester, offering, subject
497
WHERE offering.semesterid = semester.semesterid AND
498
offering.subject = subject.subjectid AND
499
offering.subject = %d;"""%subjectid
502
results = self.db.query(query).dictresult()
503
# Parse boolean varibles
504
for result in results:
505
result['active'] = _parse_boolean(result['active'])
508
def get_offering_members(self, offeringid, dry=False):
510
Gets the logins of all the people enroled in an offering
513
SELECT login.login AS login, login.fullname AS fullname
514
FROM login, enrolment
515
WHERE login.loginid = enrolment.loginid AND
516
enrolment.offeringid = %d
517
ORDER BY login.login;"""%offeringid
520
return self.db.query(query).dictresult()
523
def get_enrolment_groups(self, login, offeringid, dry=False):
525
Get all groups the user is member of in the given offering.
526
Returns a list of dicts (all values strings), with the keys:
530
SELECT project_group.groupnm as name, project_group.nick as nick
531
FROM project_set, project_group, group_member, login
533
AND project_set.offeringid=%s
534
AND group_member.loginid=login.loginid
535
AND project_group.groupid=group_member.groupid
536
AND project_group.projectsetid=project_set.projectsetid
537
""" % (_escape(login), _escape(offeringid))
540
return self.db.query(query).dictresult()
544
def get_offering_info(self, projectsetid, dry=False):
545
"""Takes information from projectset and returns useful information
546
about the subject and semester. Returns as a dictionary.
549
SELECT subjectid, subj_code, subj_name, subj_short_name, url, year, semester,
551
FROM subject, offering, semester, project_set
552
WHERE offering.subject = subject.subjectid AND
553
offering.semesterid = semester.semesterid AND
554
project_set.offeringid = offering.offeringid AND
555
project_set.projectsetid = %d;"""%projectsetid
558
return self.db.query(query).dictresult()[0]
560
def get_projectgroup_members(self, groupid, dry=False):
561
"""Returns the logins of all students in a project group
564
SELECT login.login as login, login.fullname as fullname
565
FROM login, group_member
566
WHERE login.loginid = group_member.loginid AND
567
group_member.groupid = %d
568
ORDER BY login.login;"""%groupid
571
return self.db.query(query).dictresult()
573
def get_projectsets_by_offering(self, offeringid, dry=False):
574
"""Returns all the projectsets in a particular offering"""
576
SELECT projectsetid, max_students_per_group
578
WHERE project_set.offeringid = %d;"""%offeringid
581
return self.db.query(query).dictresult()
583
def get_groups_by_projectset(self, projectsetid, dry=False):
584
"""Returns all the groups that are in a particular projectset"""
586
SELECT groupid, groupnm, nick, createdby, epoch
588
WHERE project_group.projectsetid = %d;"""%projectsetid
591
return self.db.query(query).dictresult()
594
"""Close the DB connection. Do not call any other functions after
595
this. (The behaviour of doing so is undefined).
added
removed
ivle/db.py
