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.
40
"""Wrapper around pg.escape_string. Prepares the Python value for use in
41
SQL. Returns a string, which may be safely placed verbatim into an SQL
43
Handles the following types:
44
* str: Escapes the string, and also quotes it.
45
* int/long/float: Just converts to an unquoted string.
46
* bool: Returns as "TRUE" or "FALSE", unquoted.
47
* NoneType: Returns "NULL", unquoted.
48
Raises a DBException if val has an unsupported type.
50
# "E'" is postgres's way of making "escape" strings.
51
# Such strings allow backslashes to escape things. Since escape_string
52
# converts a single backslash into two backslashes, it needs to be fed
54
# Ref: http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html
55
# WARNING: PostgreSQL-specific code
58
elif isinstance(val, str):
59
return "E'" + pg.escape_string(val) + "'"
60
elif isinstance(val, bool):
61
return "TRUE" if val else "FALSE"
62
elif isinstance(val, int) or isinstance(val, long) \
63
or isinstance(val, float):
66
raise DBException("Attempt to insert an unsupported type "
69
def _passhash(password):
70
return md5.md5(password).hexdigest()
72
class DBException(Exception):
73
"""A DBException is for bad conditions in the database or bad input to
74
these methods. If Postgres throws an exception it does not get rebadged.
75
This is only for additional exceptions."""
79
"""An IVLE database object. This object provides an interface to
80
interacting with the IVLE database without using any external SQL.
82
Most methods of this class have an optional dry argument. If true, they
83
will return the SQL query string and NOT actually execute it. (For
86
Methods may throw db.DBException, or any of the pg exceptions as well.
87
(In general, be prepared to catch exceptions!)
90
"""Connects to the database and creates a DB object.
91
Takes no parameters - gets all the DB info from the configuration."""
92
self.db = pg.connect(dbname=conf.db_dbname, host=conf.db_host,
93
port=conf.db_port, user=conf.db_user, passwd=conf.db_password)
100
# GENERIC DB FUNCTIONS #
103
def check_dict(dict, tablefields, disallowed=frozenset([]), must=False):
104
"""Checks that a dict does not contain keys that are not fields
105
of the specified table.
106
dict: A mapping from string keys to values; the keys are checked to
107
see that they correspond to login table fields.
108
tablefields: Collection of strings for field names in the table.
109
Only these fields will be allowed.
110
disallowed: Optional collection of strings for field names that are
112
must: If True, the dict MUST contain all fields in tablefields.
113
If False, it may contain any subset of the fields.
114
Returns True if the dict is valid, False otherwise.
116
allowed = frozenset(tablefields) - frozenset(disallowed)
117
dictkeys = frozenset(dict.keys())
119
return allowed == dictkeys
121
return allowed.issuperset(dictkeys)
123
def insert(self, dict, tablename, tablefields, disallowed=frozenset([]),
125
"""Inserts a new row in a table, using data from a supplied
126
dictionary (which will be checked by check_dict).
127
dict: Dictionary mapping column names to values. The values may be
128
any of the following types:
129
str, int, long, float, NoneType.
130
tablename: String, name of the table to insert into. Will NOT be
131
escaped - must be a valid identifier.
132
tablefields, disallowed: see check_dict.
133
dry: Returns the SQL query as a string, and does not execute it.
134
Raises a DBException if the dictionary contains invalid fields.
136
if not DB.check_dict(dict, tablefields, disallowed):
137
raise DBException("Supplied dictionary contains invalid fields.")
138
# Build two lists concurrently: field names and values, as SQL strings
141
for k,v in dict.items():
143
values.append(_escape(v))
144
if len(fieldnames) == 0: return
145
fieldnames = ', '.join(fieldnames)
146
values = ', '.join(values)
147
query = ("INSERT INTO %s (%s) VALUES (%s);"
148
% (tablename, fieldnames, values))
152
def update(self, primarydict, updatedict, tablename, tablefields,
153
primary_keys, disallowed_update=frozenset([]), dry=False):
154
"""Updates a row in a table, matching against primarydict to find the
155
row, and using the data in updatedict (which will be checked by
157
primarydict: Dict mapping column names to values. The keys should be
158
the table's primary key. Only rows which match this dict's values
160
updatedict: Dict mapping column names to values. The columns will be
161
updated with the given values for the matched rows.
162
tablename, tablefields, disallowed_update: See insert.
163
primary_keys: Collection of strings which together form the primary
164
key for this table. primarydict must contain all of these as keys,
167
if (not (DB.check_dict(primarydict, primary_keys, must=True)
168
and DB.check_dict(updatedict, tablefields, disallowed_update))):
169
raise DBException("Supplied dictionary contains invalid or "
171
# Make a list of SQL fragments of the form "field = 'new value'"
172
# These fragments are ALREADY-ESCAPED
174
for k,v in updatedict.items():
175
setlist.append("%s = %s" % (k, _escape(v)))
177
for k,v in primarydict.items():
178
wherelist.append("%s = %s" % (k, _escape(v)))
179
if len(setlist) == 0 or len(wherelist) == 0:
181
# Join the fragments into a comma-separated string
182
setstring = ', '.join(setlist)
183
wherestring = ' AND '.join(wherelist)
184
# Build the whole query as an UPDATE statement
185
query = ("UPDATE %s SET %s WHERE %s;"
186
% (tablename, setstring, wherestring))
190
def delete(self, primarydict, tablename, primary_keys, dry=False):
191
"""Deletes a row in the table, matching against primarydict to find
193
primarydict, tablename, primary_keys: See update.
195
if not DB.check_dict(primarydict, primary_keys, must=True):
196
raise DBException("Supplied dictionary contains invalid or "
199
for k,v in primarydict.items():
200
wherelist.append("%s = %s" % (k, _escape(v)))
201
if len(wherelist) == 0:
203
wherestring = ' AND '.join(wherelist)
204
query = ("DELETE FROM %s WHERE %s;" % (tablename, wherestring))
208
def get_single(self, primarydict, tablename, getfields, primary_keys,
209
error_notfound="No rows found", dry=False):
210
"""Retrieves a single row from a table, returning it as a dictionary
211
mapping field names to values. Matches against primarydict to find the
213
primarydict, tablename, primary_keys: See update/delete.
214
getfields: Collection of strings; the field names which will be
215
returned as keys in the dictionary.
216
error_notfound: Error message if 0 rows match.
217
Raises a DBException if 0 rows match, with error_notfound as the msg.
218
Raises an AssertError if >1 rows match (this should not happen if
219
primary_keys is indeed the primary key).
221
if not DB.check_dict(primarydict, primary_keys, must=True):
222
raise DBException("Supplied dictionary contains invalid or "
225
for k,v in primarydict.items():
226
wherelist.append("%s = %s" % (k, _escape(v)))
227
if len(getfields) == 0 or len(wherelist) == 0:
229
# Join the fragments into a comma-separated string
230
getstring = ', '.join(getfields)
231
wherestring = ' AND '.join(wherelist)
232
# Build the whole query as an SELECT statement
233
query = ("SELECT %s FROM %s WHERE %s;"
234
% (getstring, tablename, wherestring))
236
result = self.db.query(query)
237
# Expecting exactly one
238
if result.ntuples() != 1:
239
# It should not be possible for ntuples to be greater than 1
240
assert (result.ntuples() < 1)
241
raise DBException(error_notfound)
242
# Return as a dictionary
243
return result.dictresult()[0]
245
def get_all(self, tablename, getfields, dry=False):
246
"""Retrieves all rows from a table, returning it as a list of
247
dictionaries mapping field names to values.
248
tablename, getfields: See get_single.
250
if len(getfields) == 0:
252
getstring = ', '.join(getfields)
253
query = ("SELECT %s FROM %s;" % (getstring, tablename))
255
return self.db.query(query).dictresult()
257
# USER MANAGEMENT FUNCTIONS #
259
login_primary = frozenset(["login"])
260
login_fields_list = [
261
"login", "passhash", "state", "unixid", "email", "nick", "fullname",
262
"rolenm", "studentid", "acct_exp", "pass_exp", "last_login"
264
login_fields = frozenset(login_fields_list)
265
# Do not return passhash when reading from the DB
266
login_getfields = login_fields - frozenset(["passhash"])
268
def create_user(self, dry=False, **kwargs):
269
"""Creates a user login entry in the database.
270
All user fields are to be passed as args. The argument names
271
are the field names of the "login" table of the DB schema.
272
However, instead of supplying a "passhash", you must supply a
273
"password" argument, which will be hashed internally.
274
Also "state" must not given explicitly; it is implicitly set to
276
Raises an exception if the user already exists, or the dict contains
277
invalid keys or is missing required keys.
279
if 'passhash' in kwargs:
280
raise DBException("Supplied arguments include passhash (invalid).")
281
# Make a copy of the dict. Change password to passhash (hashing it),
282
# and set 'state' to "no_agreement".
283
kwargs = copy.copy(kwargs)
284
if 'password' in kwargs:
285
kwargs['passhash'] = _passhash(kwargs['password'])
286
del kwargs['password']
287
kwargs['state'] = "no_agreement"
289
return self.insert(kwargs, "login", self.login_fields, dry=dry)
291
def update_user(self, login, dry=False, **kwargs):
292
"""Updates fields of a particular user. login is the name of the user
293
to update. The dict contains the fields which will be modified, and
294
their new values. If any value is omitted from the dict, it does not
295
get modified. login and studentid may not be modified.
296
Passhash may be modified by supplying a "password" field, in
297
cleartext, not a hashed password.
299
Note that no checking is done. It is expected this function is called
300
by a trusted source. In particular, it allows the password to be
301
changed without knowing the old password. The caller should check
302
that the user knows the existing password before calling this function
305
if 'passhash' in kwargs:
306
raise DBException("Supplied arguments include passhash (invalid).")
307
if "password" in kwargs:
308
kwargs = copy.copy(kwargs)
309
kwargs['passhash'] = _passhash(kwargs['password'])
310
del kwargs['password']
311
return self.update({"login": login}, kwargs, "login",
312
self.login_fields, self.login_primary, ["login", "studentid"],
315
def get_user(self, login, dry=False):
316
"""Given a login, returns a dictionary of the user's DB fields,
317
excluding the passhash field.
319
Raises a DBException if the login is not found in the DB.
321
return self.get_single({"login": login}, "login",
322
self.login_getfields, self.login_primary,
323
error_notfound="get_user: No user with that login name", dry=dry)
325
def get_users(self, dry=False):
326
"""Returns a list of all users. The list elements are a dictionary of
327
the user's DB fields, excluding the passhash field.
329
return self.get_all("login", self.login_getfields, dry=dry)
331
def user_authenticate(self, login, password, dry=False):
332
"""Performs a password authentication on a user. Returns True if
333
"passhash" is the correct passhash for the given login, False
335
Also returns False if the login does not exist (so if you want to
336
differentiate these cases, use get_user and catch an exception).
338
query = ("SELECT login FROM login "
339
"WHERE login = '%s' AND passhash = %s;"
340
% (login, _escape(_passhash(password))))
342
result = self.db.query(query)
343
# If one row was returned, succeed.
344
# Otherwise, fail to authenticate.
345
return result.ntuples() == 1
348
"""Close the DB connection. Do not call any other functions after
349
this. (The behaviour of doing so is undefined).
added
removed
lib/common/db.py
