70
94
def __init__(self):
71
95
"""Connects to the database and creates a DB object.
72
96
Takes no parameters - gets all the DB info from the configuration."""
73
98
self.db = pg.connect(dbname=conf.db_dbname, host=conf.db_host,
74
99
port=conf.db_port, user=conf.db_user, passwd=conf.db_password)
106
# GENERIC DB FUNCTIONS #
109
def check_dict(dict, tablefields, disallowed=frozenset([]), must=False):
110
"""Checks that a dict does not contain keys that are not fields
111
of the specified table.
112
dict: A mapping from string keys to values; the keys are checked to
113
see that they correspond to login table fields.
114
tablefields: Collection of strings for field names in the table.
115
Only these fields will be allowed.
116
disallowed: Optional collection of strings for field names that are
118
must: If True, the dict MUST contain all fields in tablefields.
119
If False, it may contain any subset of the fields.
120
Returns True if the dict is valid, False otherwise.
122
allowed = frozenset(tablefields) - frozenset(disallowed)
123
dictkeys = frozenset(dict.keys())
125
return allowed == dictkeys
127
return allowed.issuperset(dictkeys)
129
def insert(self, dict, tablename, tablefields, disallowed=frozenset([]),
131
"""Inserts a new row in a table, using data from a supplied
132
dictionary (which will be checked by check_dict).
133
dict: Dictionary mapping column names to values. The values may be
134
any of the following types:
135
str, int, long, float, NoneType.
136
tablename: String, name of the table to insert into. Will NOT be
137
escaped - must be a valid identifier.
138
tablefields, disallowed: see check_dict.
139
dry: Returns the SQL query as a string, and does not execute it.
140
Raises a DBException if the dictionary contains invalid fields.
142
if not DB.check_dict(dict, tablefields, disallowed):
143
raise DBException("Supplied dictionary contains invalid fields.")
144
# Build two lists concurrently: field names and values, as SQL strings
147
for k,v in dict.items():
149
values.append(_escape(v))
150
if len(fieldnames) == 0: return
151
fieldnames = ', '.join(fieldnames)
152
values = ', '.join(values)
153
query = ("INSERT INTO %s (%s) VALUES (%s);"
154
% (tablename, fieldnames, values))
158
def update(self, primarydict, updatedict, tablename, tablefields,
159
primary_keys, disallowed_update=frozenset([]), dry=False):
160
"""Updates a row in a table, matching against primarydict to find the
161
row, and using the data in updatedict (which will be checked by
163
primarydict: Dict mapping column names to values. The keys should be
164
the table's primary key. Only rows which match this dict's values
166
updatedict: Dict mapping column names to values. The columns will be
167
updated with the given values for the matched rows.
168
tablename, tablefields, disallowed_update: See insert.
169
primary_keys: Collection of strings which together form the primary
170
key for this table. primarydict must contain all of these as keys,
173
if (not (DB.check_dict(primarydict, primary_keys, must=True)
174
and DB.check_dict(updatedict, tablefields, disallowed_update))):
175
raise DBException("Supplied dictionary contains invalid or "
177
# Make a list of SQL fragments of the form "field = 'new value'"
178
# These fragments are ALREADY-ESCAPED
180
for k,v in updatedict.items():
181
setlist.append("%s = %s" % (k, _escape(v)))
183
for k,v in primarydict.items():
184
wherelist.append("%s = %s" % (k, _escape(v)))
185
if len(setlist) == 0 or len(wherelist) == 0:
187
# Join the fragments into a comma-separated string
188
setstring = ', '.join(setlist)
189
wherestring = ' AND '.join(wherelist)
190
# Build the whole query as an UPDATE statement
191
query = ("UPDATE %s SET %s WHERE %s;"
192
% (tablename, setstring, wherestring))
196
def delete(self, primarydict, tablename, primary_keys, dry=False):
197
"""Deletes a row in the table, matching against primarydict to find
199
primarydict, tablename, primary_keys: See update.
201
if not DB.check_dict(primarydict, primary_keys, must=True):
202
raise DBException("Supplied dictionary contains invalid or "
205
for k,v in primarydict.items():
206
wherelist.append("%s = %s" % (k, _escape(v)))
207
if len(wherelist) == 0:
209
wherestring = ' AND '.join(wherelist)
210
query = ("DELETE FROM %s WHERE %s;" % (tablename, wherestring))
214
def get_single(self, primarydict, tablename, getfields, primary_keys,
215
error_notfound="No rows found", dry=False):
216
"""Retrieves a single row from a table, returning it as a dictionary
217
mapping field names to values. Matches against primarydict to find the
219
primarydict, tablename, primary_keys: See update/delete.
220
getfields: Collection of strings; the field names which will be
221
returned as keys in the dictionary.
222
error_notfound: Error message if 0 rows match.
223
Raises a DBException if 0 rows match, with error_notfound as the msg.
224
Raises an AssertError if >1 rows match (this should not happen if
225
primary_keys is indeed the primary key).
227
if not DB.check_dict(primarydict, primary_keys, must=True):
228
raise DBException("Supplied dictionary contains invalid or "
231
for k,v in primarydict.items():
232
wherelist.append("%s = %s" % (k, _escape(v)))
233
if len(getfields) == 0 or len(wherelist) == 0:
235
# Join the fragments into a comma-separated string
236
getstring = ', '.join(getfields)
237
wherestring = ' AND '.join(wherelist)
238
# Build the whole query as an SELECT statement
239
query = ("SELECT %s FROM %s WHERE %s;"
240
% (getstring, tablename, wherestring))
242
result = self.db.query(query)
243
# Expecting exactly one
244
if result.ntuples() != 1:
245
# It should not be possible for ntuples to be greater than 1
246
assert (result.ntuples() < 1)
247
raise DBException(error_notfound)
248
# Return as a dictionary
249
return result.dictresult()[0]
251
def get_all(self, tablename, getfields, dry=False):
252
"""Retrieves all rows from a table, returning it as a list of
253
dictionaries mapping field names to values.
254
tablename, getfields: See get_single.
256
if len(getfields) == 0:
258
getstring = ', '.join(getfields)
259
query = ("SELECT %s FROM %s;" % (getstring, tablename))
261
return self.db.query(query).dictresult()
263
def start_transaction(self, dry=False):
264
"""Starts a DB transaction.
265
Will not commit any changes until self.commit() is called.
267
query = "START TRANSACTION;"
271
def commit(self, dry=False):
272
"""Commits (ends) a DB transaction.
273
Commits all changes since the call to start_transaction.
279
def rollback(self, dry=False):
280
"""Rolls back (ends) a DB transaction, undoing all changes since the
281
call to start_transaction.
76
287
# USER MANAGEMENT FUNCTIONS #
78
def create_user(self, login, password, nick, fullname, rolenm, studentid,
289
login_primary = frozenset(["login"])
290
login_fields_list = [
291
"login", "passhash", "state", "unixid", "email", "nick", "fullname",
292
"rolenm", "studentid", "acct_exp", "pass_exp", "last_login"
294
login_fields = frozenset(login_fields_list)
295
# Do not return passhash when reading from the DB
296
login_getfields = login_fields - frozenset(["passhash"])
298
def create_user(self, dry=False, **kwargs):
80
299
"""Creates a user login entry in the database.
81
Arguments are the same as those in the "login" table of the schema.
82
The exception is "password", which is a cleartext password. makeuser
83
will hash the password.
84
Raises an exception if the user already exists.
300
All user fields are to be passed as args. The argument names
301
are the field names of the "login" table of the DB schema.
302
However, instead of supplying a "passhash", you must supply a
303
"password" argument, which will be hashed internally.
304
Also "state" must not given explicitly; it is implicitly set to
306
Raises an exception if the user already exists, or the dict contains
307
invalid keys or is missing required keys.
86
passhash = _passhash(password)
87
query = ("INSERT INTO login (login, passhash, nick, fullname, "
88
"rolenm, studentid) VALUES (%s, %s, %s, %s, %s, %s);" %
89
(_escape(login), _escape(passhash), _escape(nick),
90
_escape(fullname), _escape(rolenm), _escape(studentid)))
309
if 'passhash' in kwargs:
310
raise DBException("Supplied arguments include passhash (invalid).")
311
# Make a copy of the dict. Change password to passhash (hashing it),
312
# and set 'state' to "no_agreement".
313
kwargs = copy.copy(kwargs)
314
if 'password' in kwargs:
315
kwargs['passhash'] = _passhash(kwargs['password'])
316
del kwargs['password']
317
kwargs['state'] = "no_agreement"
319
return self.insert(kwargs, "login", self.login_fields, dry=dry)
94
def update_user(self, login, password=None, nick=None,
95
fullname=None, rolenm=None, dry=False):
321
def update_user(self, login, dry=False, **kwargs):
96
322
"""Updates fields of a particular user. login is the name of the user
97
to update. The other arguments are optional fields which may be
98
modified. If None or omitted, they do not get modified. login and
99
studentid may not be modified.
323
to update. The dict contains the fields which will be modified, and
324
their new values. If any value is omitted from the dict, it does not
325
get modified. login and studentid may not be modified.
326
Passhash may be modified by supplying a "password" field, in
327
cleartext, not a hashed password.
101
329
Note that no checking is done. It is expected this function is called
102
330
by a trusted source. In particular, it allows the password to be
added
removed
lib/common/db.py
