« back to all changes in this revision
Viewing changes to ivle/db.py
- browse files at revision 1080.1.89
- compare with another revision
- compare with revision 630
- download diff
- download tarball
- view history from revision 1080.1.89
- Committer: William Grant
- Date: 2009-01-20 05:20:39 UTC
- mto: This revision was merged to the branch mainline in revision 1090.
- Revision ID: grantw@unimelb.edu.au-20090120052039-wx69dr7wxpfukkbo
ivle.db: Remove. Finally.
- files removed:
- ivle/db.py
added
removed
ivle/db.py
1
# IVLE - Informatics Virtual Learning Environment
2
# Copyright (C) 2007-2008 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
# Module: Database
19
# Author: Matt Giuca
20
# Date: 15/2/2008
21
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
28
# activities within.
29
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.
33
34
import pg
35
import md5
36
import copy
37
import time
38
39
import ivle.conf
40
from ivle import caps
41
42
TIMESTAMP_FORMAT = '%Y-%m-%d %H:%M:%S'
43
44
def _escape(val):
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
47
query.
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
55
a TIMESTAMP column.
56
Raises a DBException if val has an unsupported type.
57
"""
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
61
# into E mode.
62
# Ref: http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html
63
# WARNING: PostgreSQL-specific code
64
if val is None:
65
return "NULL"
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):
72
return str(val)
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))
77
else:
78
raise DBException("Attempt to insert an unsupported type "
79
"into the database (%s)" % repr(type(val)))
80
81
class DBException(Exception):
82
"""A DBException is for bad conditions in the database or bad input to
83
these methods. If Postgres throws an exception it does not get rebadged.
84
This is only for additional exceptions."""
85
pass
86
87
class DB:
88
"""An IVLE database object. This object provides an interface to
89
interacting with the IVLE database without using any external SQL.
90
91
Most methods of this class have an optional dry argument. If true, they
92
will return the SQL query string and NOT actually execute it. (For
93
debugging purposes).
94
95
Methods may throw db.DBException, or any of the pg exceptions as well.
96
(In general, be prepared to catch exceptions!)
97
"""
98
def __init__(self):
99
"""Connects to the database and creates a DB object.
100
Takes no parameters - gets all the DB info from the configuration."""
101
self.open = False
102
self.db = pg.connect(host=ivle.conf.db_host, port=ivle.conf.db_port,
103
dbname=ivle.conf.db_dbname,
104
user=ivle.conf.db_user, passwd=ivle.conf.db_password)
105
self.open = True
106
107
def __del__(self):
108
if self.open:
109
self.db.close()
110
111
# GENERIC DB FUNCTIONS #
112
113
@staticmethod
114
def check_dict(dict, tablefields, disallowed=frozenset([]), must=False):
115
"""Checks that a dict does not contain keys that are not fields
116
of the specified table.
117
dict: A mapping from string keys to values; the keys are checked to
118
see that they correspond to login table fields.
119
tablefields: Collection of strings for field names in the table.
120
Only these fields will be allowed.
121
disallowed: Optional collection of strings for field names that are
122
not allowed.
123
must: If True, the dict MUST contain all fields in tablefields.
124
If False, it may contain any subset of the fields.
125
Returns True if the dict is valid, False otherwise.
126
"""
127
allowed = frozenset(tablefields) - frozenset(disallowed)
128
dictkeys = frozenset(dict.keys())
129
if must:
130
return allowed == dictkeys
131
else:
132
return allowed.issuperset(dictkeys)
133
134
def insert(self, dict, tablename, tablefields, disallowed=frozenset([]),
135
dry=False):
136
"""Inserts a new row in a table, using data from a supplied
137
dictionary (which will be checked by check_dict).
138
dict: Dictionary mapping column names to values. The values may be
139
any of the following types:
140
str, int, long, float, NoneType.
141
tablename: String, name of the table to insert into. Will NOT be
142
escaped - must be a valid identifier.
143
tablefields, disallowed: see check_dict.
144
dry: Returns the SQL query as a string, and does not execute it.
145
Raises a DBException if the dictionary contains invalid fields.
146
"""
147
if not DB.check_dict(dict, tablefields, disallowed):
148
extras = set(dict.keys()) - tablefields
149
raise DBException("Supplied dictionary contains invalid fields. (%s)" % (repr(extras)))
150
# Build two lists concurrently: field names and values, as SQL strings
151
fieldnames = []
152
values = []
153
for k,v in dict.items():
154
fieldnames.append(k)
155
values.append(_escape(v))
156
if len(fieldnames) == 0: return
157
fieldnames = ', '.join(fieldnames)
158
values = ', '.join(values)
159
query = ("INSERT INTO %s (%s) VALUES (%s);"
160
% (tablename, fieldnames, values))
161
if dry: return query
162
self.db.query(query)
163
164
def update(self, primarydict, updatedict, tablename, tablefields,
165
primary_keys, disallowed_update=frozenset([]), dry=False):
166
"""Updates a row in a table, matching against primarydict to find the
167
row, and using the data in updatedict (which will be checked by
168
check_dict).
169
primarydict: Dict mapping column names to values. The keys should be
170
the table's primary key. Only rows which match this dict's values
171
will be updated.
172
updatedict: Dict mapping column names to values. The columns will be
173
updated with the given values for the matched rows.
174
tablename, tablefields, disallowed_update: See insert.
175
primary_keys: Collection of strings which together form the primary
176
key for this table. primarydict must contain all of these as keys,
177
and only these keys.
178
"""
179
if (not (DB.check_dict(primarydict, primary_keys, must=True)
180
and DB.check_dict(updatedict, tablefields, disallowed_update))):
181
raise DBException("Supplied dictionary contains invalid or missing fields (1).")
182
# Make a list of SQL fragments of the form "field = 'new value'"
183
# These fragments are ALREADY-ESCAPED
184
setlist = []
185
for k,v in updatedict.items():
186
setlist.append("%s = %s" % (k, _escape(v)))
187
wherelist = []
188
for k,v in primarydict.items():
189
wherelist.append("%s = %s" % (k, _escape(v)))
190
if len(setlist) == 0 or len(wherelist) == 0:
191
return
192
# Join the fragments into a comma-separated string
193
setstring = ', '.join(setlist)
194
wherestring = ' AND '.join(wherelist)
195
# Build the whole query as an UPDATE statement
196
query = ("UPDATE %s SET %s WHERE %s;"
197
% (tablename, setstring, wherestring))
198
if dry: return query
199
self.db.query(query)
200
201
def get_single(self, primarydict, tablename, getfields, primary_keys,
202
error_notfound="No rows found", dry=False):
203
"""Retrieves a single row from a table, returning it as a dictionary
204
mapping field names to values. Matches against primarydict to find the
205
row.
206
primarydict, tablename, primary_keys: See update/delete.
207
getfields: Collection of strings; the field names which will be
208
returned as keys in the dictionary.
209
error_notfound: Error message if 0 rows match.
210
Raises a DBException if 0 rows match, with error_notfound as the msg.
211
Raises an AssertError if >1 rows match (this should not happen if
212
primary_keys is indeed the primary key).
213
"""
214
if not DB.check_dict(primarydict, primary_keys, must=True):
215
raise DBException("Supplied dictionary contains invalid or missing fields (3).")
216
wherelist = []
217
for k,v in primarydict.items():
218
wherelist.append("%s = %s" % (k, _escape(v)))
219
if len(getfields) == 0 or len(wherelist) == 0:
220
return
221
# Join the fragments into a comma-separated string
222
getstring = ', '.join(getfields)
223
wherestring = ' AND '.join(wherelist)
224
# Build the whole query as an SELECT statement
225
query = ("SELECT %s FROM %s WHERE %s;"
226
% (getstring, tablename, wherestring))
227
if dry: return query
228
result = self.db.query(query)
229
# Expecting exactly one
230
if result.ntuples() != 1:
231
# It should not be possible for ntuples to be greater than 1
232
assert (result.ntuples() < 1)
233
raise DBException(error_notfound)
234
# Return as a dictionary
235
return result.dictresult()[0]
236
237
# PROBLEM AND PROBLEM ATTEMPT FUNCTIONS #
238
239
def get_problem_problemid(self, exercisename, dry=False):
240
"""Given an exercise name, returns the associated problemID.
241
If the exercise name is NOT in the database, it inserts it and returns
242
the new problemID. Hence this may mutate the DB, but is idempotent.
243
"""
244
try:
245
d = self.get_single({"identifier": exercisename}, "problem",
246
['problemid'], frozenset(["identifier"]),
247
dry=dry)
248
if dry:
249
return d # Query string
250
except DBException:
251
if dry:
252
# Shouldn't try again, must have failed for some other reason
253
raise
254
# if we failed to get a problemid, it was probably because
255
# the exercise wasn't in the db. So lets insert it!
256
#
257
# The insert can fail if someone else simultaneously does
258
# the insert, so if the insert fails, we ignore the problem.
259
try:
260
self.insert({'identifier': exercisename}, "problem",
261
frozenset(['identifier']))
262
except Exception, e:
263
pass
264
265
# Assuming the insert succeeded, we should be able to get the
266
# problemid now.
267
d = self.get_single({"identifier": exercisename}, "problem",
268
['problemid'], frozenset(["identifier"]))
269
270
return d['problemid']
271
272
def write_problem_save(self, user, exercisename, date, text, dry=False):
273
"""Writes text to the problem_save table (for when the user saves an
274
exercise). Creates a new row, or overwrites an existing one if the
275
user has already saved that problem.
276
(Unlike problem_attempt, does not keep historical records).
277
"""
278
problemid = self.get_problem_problemid(exercisename)
279
280
try:
281
return self.insert({
282
'problemid': problemid,
283
'loginid': user.id,
284
'date': date,
285
'text': text,
286
}, 'problem_save',
287
frozenset(['problemid','loginid','date','text']),
288
dry=dry)
289
except pg.ProgrammingError:
290
# May have failed because this problemid/loginid row already
291
# exists (they have a unique key constraint).
292
# Do an update instead.
293
if dry:
294
# Shouldn't try again, must have failed for some other reason
295
raise
296
self.update({
297
'problemid': problemid,
298
'loginid': user.id,
299
},
300
{
301
'date': date,
302
'text': text,
303
}, "problem_save",
304
frozenset(['date', 'text']),
305
frozenset(['problemid', 'loginid']))
306
307
def close(self):
308
"""Close the DB connection. Do not call any other functions after
309
this. (The behaviour of doing so is undefined).
310
"""
311
self.db.close()
312
self.open = False
Loggerhead is a web-based interface for Breezy
Version: 2.0.1