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
class DBException(Exception):
106
"""A DBException is for bad conditions in the database or bad input to
107
these methods. If Postgres throws an exception it does not get rebadged.
108
This is only for additional exceptions."""
112
"""An IVLE database object. This object provides an interface to
113
interacting with the IVLE database without using any external SQL.
115
Most methods of this class have an optional dry argument. If true, they
116
will return the SQL query string and NOT actually execute it. (For
119
Methods may throw db.DBException, or any of the pg exceptions as well.
120
(In general, be prepared to catch exceptions!)
123
"""Connects to the database and creates a DB object.
124
Takes no parameters - gets all the DB info from the configuration."""
126
self.db = pg.connect(host=ivle.conf.db_host, port=ivle.conf.db_port,
127
dbname=ivle.conf.db_dbname,
128
user=ivle.conf.db_user, passwd=ivle.conf.db_password)
135
# GENERIC DB FUNCTIONS #
138
def check_dict(dict, tablefields, disallowed=frozenset([]), must=False):
139
"""Checks that a dict does not contain keys that are not fields
140
of the specified table.
141
dict: A mapping from string keys to values; the keys are checked to
142
see that they correspond to login table fields.
143
tablefields: Collection of strings for field names in the table.
144
Only these fields will be allowed.
145
disallowed: Optional collection of strings for field names that are
147
must: If True, the dict MUST contain all fields in tablefields.
148
If False, it may contain any subset of the fields.
149
Returns True if the dict is valid, False otherwise.
151
allowed = frozenset(tablefields) - frozenset(disallowed)
152
dictkeys = frozenset(dict.keys())
154
return allowed == dictkeys
156
return allowed.issuperset(dictkeys)
158
def insert(self, dict, tablename, tablefields, disallowed=frozenset([]),
160
"""Inserts a new row in a table, using data from a supplied
161
dictionary (which will be checked by check_dict).
162
dict: Dictionary mapping column names to values. The values may be
163
any of the following types:
164
str, int, long, float, NoneType.
165
tablename: String, name of the table to insert into. Will NOT be
166
escaped - must be a valid identifier.
167
tablefields, disallowed: see check_dict.
168
dry: Returns the SQL query as a string, and does not execute it.
169
Raises a DBException if the dictionary contains invalid fields.
171
if not DB.check_dict(dict, tablefields, disallowed):
172
extras = set(dict.keys()) - tablefields
173
raise DBException("Supplied dictionary contains invalid fields. (%s)" % (repr(extras)))
174
# Build two lists concurrently: field names and values, as SQL strings
177
for k,v in dict.items():
179
values.append(_escape(v))
180
if len(fieldnames) == 0: return
181
fieldnames = ', '.join(fieldnames)
182
values = ', '.join(values)
183
query = ("INSERT INTO %s (%s) VALUES (%s);"
184
% (tablename, fieldnames, values))
188
def return_insert(self, dict, tablename, tablefields, returning,
189
disallowed=frozenset([]), dry=False):
190
"""Inserts a new row in a table, using data from a supplied
191
dictionary (which will be checked by check_dict) and returns certain
193
dict: Dictionary mapping column names to values. The values may be
194
any of the following types:
195
str, int, long, float, NoneType.
196
tablename: String, name of the table to insert into. Will NOT be
197
escaped - must be a valid identifier.
198
returning: List of fields to return, not escaped
199
tablefields, disallowed: see check_dict.
200
dry: Returns the SQL query as a string, and does not execute it.
201
Raises a DBException if the dictionary contains invalid fields.
203
if not DB.check_dict(dict, tablefields, disallowed):
204
extras = set(dict.keys()) - tablefields
205
raise DBException("Supplied dictionary contains invalid fields. (%s)" % (repr(extras)))
206
# Build two lists concurrently: field names and values, as SQL strings
209
for k,v in dict.items():
211
values.append(_escape(v))
212
if len(fieldnames) == 0: return
213
fieldnames = ', '.join(fieldnames)
214
values = ', '.join(values)
215
returns = ', '.join(returning)
216
query = ("INSERT INTO %s (%s) VALUES (%s) RETURNING (%s);"
217
% (tablename, fieldnames, values, returns))
219
return self.db.query(query)
222
def update(self, primarydict, updatedict, tablename, tablefields,
223
primary_keys, disallowed_update=frozenset([]), dry=False):
224
"""Updates a row in a table, matching against primarydict to find the
225
row, and using the data in updatedict (which will be checked by
227
primarydict: Dict mapping column names to values. The keys should be
228
the table's primary key. Only rows which match this dict's values
230
updatedict: Dict mapping column names to values. The columns will be
231
updated with the given values for the matched rows.
232
tablename, tablefields, disallowed_update: See insert.
233
primary_keys: Collection of strings which together form the primary
234
key for this table. primarydict must contain all of these as keys,
237
if (not (DB.check_dict(primarydict, primary_keys, must=True)
238
and DB.check_dict(updatedict, tablefields, disallowed_update))):
239
raise DBException("Supplied dictionary contains invalid or missing fields (1).")
240
# Make a list of SQL fragments of the form "field = 'new value'"
241
# These fragments are ALREADY-ESCAPED
243
for k,v in updatedict.items():
244
setlist.append("%s = %s" % (k, _escape(v)))
246
for k,v in primarydict.items():
247
wherelist.append("%s = %s" % (k, _escape(v)))
248
if len(setlist) == 0 or len(wherelist) == 0:
250
# Join the fragments into a comma-separated string
251
setstring = ', '.join(setlist)
252
wherestring = ' AND '.join(wherelist)
253
# Build the whole query as an UPDATE statement
254
query = ("UPDATE %s SET %s WHERE %s;"
255
% (tablename, setstring, wherestring))
259
def get_single(self, primarydict, tablename, getfields, primary_keys,
260
error_notfound="No rows found", dry=False):
261
"""Retrieves a single row from a table, returning it as a dictionary
262
mapping field names to values. Matches against primarydict to find the
264
primarydict, tablename, primary_keys: See update/delete.
265
getfields: Collection of strings; the field names which will be
266
returned as keys in the dictionary.
267
error_notfound: Error message if 0 rows match.
268
Raises a DBException if 0 rows match, with error_notfound as the msg.
269
Raises an AssertError if >1 rows match (this should not happen if
270
primary_keys is indeed the primary key).
272
if not DB.check_dict(primarydict, primary_keys, must=True):
273
raise DBException("Supplied dictionary contains invalid or missing fields (3).")
275
for k,v in primarydict.items():
276
wherelist.append("%s = %s" % (k, _escape(v)))
277
if len(getfields) == 0 or len(wherelist) == 0:
279
# Join the fragments into a comma-separated string
280
getstring = ', '.join(getfields)
281
wherestring = ' AND '.join(wherelist)
282
# Build the whole query as an SELECT statement
283
query = ("SELECT %s FROM %s WHERE %s;"
284
% (getstring, tablename, wherestring))
286
result = self.db.query(query)
287
# Expecting exactly one
288
if result.ntuples() != 1:
289
# It should not be possible for ntuples to be greater than 1
290
assert (result.ntuples() < 1)
291
raise DBException(error_notfound)
292
# Return as a dictionary
293
return result.dictresult()[0]
295
def start_transaction(self, dry=False):
296
"""Starts a DB transaction.
297
Will not commit any changes until self.commit() is called.
299
query = "START TRANSACTION;"
303
def commit(self, dry=False):
304
"""Commits (ends) a DB transaction.
305
Commits all changes since the call to start_transaction.
311
def rollback(self, dry=False):
312
"""Rolls back (ends) a DB transaction, undoing all changes since the
313
call to start_transaction.
319
# PROBLEM AND PROBLEM ATTEMPT FUNCTIONS #
321
def get_problem_problemid(self, exercisename, dry=False):
322
"""Given an exercise name, returns the associated problemID.
323
If the exercise name is NOT in the database, it inserts it and returns
324
the new problemID. Hence this may mutate the DB, but is idempotent.
327
d = self.get_single({"identifier": exercisename}, "problem",
328
['problemid'], frozenset(["identifier"]),
331
return d # Query string
334
# Shouldn't try again, must have failed for some other reason
336
# if we failed to get a problemid, it was probably because
337
# the exercise wasn't in the db. So lets insert it!
339
# The insert can fail if someone else simultaneously does
340
# the insert, so if the insert fails, we ignore the problem.
342
self.insert({'identifier': exercisename}, "problem",
343
frozenset(['identifier']))
347
# Assuming the insert succeeded, we should be able to get the
349
d = self.get_single({"identifier": exercisename}, "problem",
350
['problemid'], frozenset(["identifier"]))
352
return d['problemid']
354
def insert_problem_attempt(self, user, exercisename, date, complete,
356
"""Inserts the details of a problem attempt into the database.
357
exercisename: Name of the exercise. (identifier field of problem
358
table). If this exercise does not exist, also creates a new row in
359
the problem table for this exercise name.
360
user: The user submitting the attempt.
361
date: struct_time, the date this attempt was made.
362
complete: bool. Whether the test passed or not.
363
attempt: Text of the attempt.
365
Note: Even if dry, will still physically call get_problem_problemid,
366
which may mutate the DB.
368
problemid = self.get_problem_problemid(exercisename)
371
'problemid': problemid,
374
'complete': complete,
376
}, 'problem_attempt',
377
frozenset(['problemid','loginid','date','complete','attempt']),
380
def write_problem_save(self, user, exercisename, date, text, dry=False):
381
"""Writes text to the problem_save table (for when the user saves an
382
exercise). Creates a new row, or overwrites an existing one if the
383
user has already saved that problem.
384
(Unlike problem_attempt, does not keep historical records).
386
problemid = self.get_problem_problemid(exercisename)
390
'problemid': problemid,
395
frozenset(['problemid','loginid','date','text']),
397
except pg.ProgrammingError:
398
# May have failed because this problemid/loginid row already
399
# exists (they have a unique key constraint).
400
# Do an update instead.
402
# Shouldn't try again, must have failed for some other reason
405
'problemid': problemid,
412
frozenset(['date', 'text']),
413
frozenset(['problemid', 'loginid']))
415
# SUBJECTS AND ENROLEMENT
417
def get_offering_semesters(self, subjectid, dry=False):
419
Get the semester information for a subject as well as providing
420
information about if the subject is active and which semester it is in.
423
SELECT offeringid, subj_name, year, semester, active
424
FROM semester, offering, subject
425
WHERE offering.semesterid = semester.semesterid AND
426
offering.subject = subject.subjectid AND
427
offering.subject = %d;"""%subjectid
430
results = self.db.query(query).dictresult()
431
# Parse boolean varibles
432
for result in results:
433
result['active'] = _parse_boolean(result['active'])
436
def get_offering_members(self, offeringid, dry=False):
438
Gets the logins of all the people enroled in an offering
441
SELECT login.login AS login, login.fullname AS fullname
442
FROM login, enrolment
443
WHERE login.loginid = enrolment.loginid AND
444
enrolment.offeringid = %d
445
ORDER BY login.login;"""%offeringid
448
return self.db.query(query).dictresult()
451
def get_enrolment_groups(self, login, offeringid, dry=False):
453
Get all groups the user is member of in the given offering.
454
Returns a list of dicts (all values strings), with the keys:
458
SELECT project_group.groupnm as name, project_group.nick as nick
459
FROM project_set, project_group, group_member, login
461
AND project_set.offeringid=%s
462
AND group_member.loginid=login.loginid
463
AND project_group.groupid=group_member.groupid
464
AND project_group.projectsetid=project_set.projectsetid
465
""" % (_escape(login), _escape(offeringid))
468
return self.db.query(query).dictresult()
472
def get_offering_info(self, projectsetid, dry=False):
473
"""Takes information from projectset and returns useful information
474
about the subject and semester. Returns as a dictionary.
477
SELECT subjectid, subj_code, subj_name, subj_short_name, url, year, semester,
479
FROM subject, offering, semester, project_set
480
WHERE offering.subject = subject.subjectid AND
481
offering.semesterid = semester.semesterid AND
482
project_set.offeringid = offering.offeringid AND
483
project_set.projectsetid = %d;"""%projectsetid
486
return self.db.query(query).dictresult()[0]
488
def get_projectgroup_members(self, groupid, dry=False):
489
"""Returns the logins of all students in a project group
492
SELECT login.login as login, login.fullname as fullname
493
FROM login, group_member
494
WHERE login.loginid = group_member.loginid AND
495
group_member.groupid = %d
496
ORDER BY login.login;"""%groupid
499
return self.db.query(query).dictresult()
501
def get_projectsets_by_offering(self, offeringid, dry=False):
502
"""Returns all the projectsets in a particular offering"""
504
SELECT projectsetid, max_students_per_group
506
WHERE project_set.offeringid = %d;"""%offeringid
509
return self.db.query(query).dictresult()
511
def get_groups_by_projectset(self, projectsetid, dry=False):
512
"""Returns all the groups that are in a particular projectset"""
514
SELECT groupid, groupnm, nick, createdby, epoch
516
WHERE project_group.projectsetid = %d;"""%projectsetid
519
return self.db.query(query).dictresult()
522
"""Close the DB connection. Do not call any other functions after
523
this. (The behaviour of doing so is undefined).
added
removed
ivle/db.py
