1 """
2 This module contains C{L{OpenIDStore}} implementations that use
3 various SQL databases to back them.
4
5 Example of how to initialize a store database::
6
7 python -c 'from openid.store import sqlstore; import pysqlite2.dbapi2; sqlstore.SQLiteStore(pysqlite2.dbapi2.connect("cstore.db")).createTables()'
8 """
9 import re
10 import time
11
12 from openid.association import Association
13 from openid.store.interface import OpenIDStore
14 from openid.store import nonce
15
17 def wrapped(self, *args, **kwargs):
18 return self._callInTransaction(func, self, *args, **kwargs)
19
20 if hasattr(func, '__name__'):
21 try:
22 wrapped.__name__ = func.__name__[4:]
23 except TypeError:
24 pass
25
26 if hasattr(func, '__doc__'):
27 wrapped.__doc__ = func.__doc__
28
29 return wrapped
30
32 """
33 This is the parent class for the SQL stores, which contains the
34 logic common to all of the SQL stores.
35
36 The table names used are determined by the class variables
37 C{L{associations_table}} and
38 C{L{nonces_table}}. To change the name of the tables used, pass
39 new table names into the constructor.
40
41 To create the tables with the proper schema, see the
42 C{L{createTables}} method.
43
44 This class shouldn't be used directly. Use one of its subclasses
45 instead, as those contain the code necessary to use a specific
46 database.
47
48 All methods other than C{L{__init__}} and C{L{createTables}}
49 should be considered implementation details.
50
51
52 @cvar associations_table: This is the default name of the table to
53 keep associations in
54
55 @cvar nonces_table: This is the default name of the table to keep
56 nonces in.
57
58
59 @sort: __init__, createTables
60 """
61
62 associations_table = 'oid_associations'
63 nonces_table = 'oid_nonces'
64
65 - def __init__(self, conn, associations_table=None, nonces_table=None):
66 """
67 This creates a new SQLStore instance. It requires an
68 established database connection be given to it, and it allows
69 overriding the default table names.
70
71
72 @param conn: This must be an established connection to a
73 database of the correct type for the SQLStore subclass
74 you're using.
75
76 @type conn: A python database API compatible connection
77 object.
78
79
80 @param associations_table: This is an optional parameter to
81 specify the name of the table used for storing
82 associations. The default value is specified in
83 C{L{SQLStore.associations_table}}.
84
85 @type associations_table: C{str}
86
87
88 @param nonces_table: This is an optional parameter to specify
89 the name of the table used for storing nonces. The
90 default value is specified in C{L{SQLStore.nonces_table}}.
91
92 @type nonces_table: C{str}
93 """
94 self.conn = conn
95 self.cur = None
96 self._statement_cache = {}
97 self._table_names = {
98 'associations': associations_table or self.associations_table,
99 'nonces': nonces_table or self.nonces_table,
100 }
101 self.max_nonce_age = 6 * 60 * 60
102
103
104
105
106 if (hasattr(self.conn, 'IntegrityError') and
107 hasattr(self.conn, 'OperationalError')):
108 self.exceptions = self.conn
109
110 if not (hasattr(self.exceptions, 'IntegrityError') and
111 hasattr(self.exceptions, 'OperationalError')):
112 raise RuntimeError("Error using database connection module "
113 "(Maybe it can't be imported?)")
114
116 """Convert a blob as returned by the SQL engine into a str object.
117
118 str -> str"""
119 return blob
120
122 """Convert a str object into the necessary object for storing
123 in the database as a blob."""
124 return s
125
127 try:
128 return self._statement_cache[sql_name]
129 except KeyError:
130 sql = getattr(self, sql_name)
131 sql %= self._table_names
132 self._statement_cache[sql_name] = sql
133 return sql
134
136 sql = self._getSQL(sql_name)
137
138
139
140
141 def unicode_to_str(arg):
142 if isinstance(arg, unicode):
143 return str(arg)
144 else:
145 return arg
146 str_args = map(unicode_to_str, args)
147 self.cur.execute(sql, str_args)
148
150
151
152
153 if attr[:3] == 'db_':
154 sql_name = attr[3:] + '_sql'
155 def func(*args):
156 return self._execSQL(sql_name, *args)
157 setattr(self, attr, func)
158 return func
159 else:
160 raise AttributeError('Attribute %r not found' % (attr,))
161
163 """Execute the given function inside of a transaction, with an
164 open cursor. If no exception is raised, the transaction is
165 comitted, otherwise it is rolled back."""
166
167 self.conn.rollback()
168
169 try:
170 self.cur = self.conn.cursor()
171 try:
172 ret = func(*args, **kwargs)
173 finally:
174 self.cur.close()
175 self.cur = None
176 except:
177 self.conn.rollback()
178 raise
179 else:
180 self.conn.commit()
181
182 return ret
183
185 """
186 This method creates the database tables necessary for this
187 store to work. It should not be called if the tables already
188 exist.
189 """
190 self.db_create_nonce()
191 self.db_create_assoc()
192
193 createTables = _inTxn(txn_createTables)
194
208
209 storeAssociation = _inTxn(txn_storeAssociation)
210
212 """Get the most recent association that has been set for this
213 server URL and handle.
214
215 str -> NoneType or Association
216 """
217 if handle is not None:
218 self.db_get_assoc(server_url, handle)
219 else:
220 self.db_get_assocs(server_url)
221
222 rows = self.cur.fetchall()
223 if len(rows) == 0:
224 return None
225 else:
226 associations = []
227 for values in rows:
228 assoc = Association(*values)
229 assoc.secret = self.blobDecode(assoc.secret)
230 if assoc.getExpiresIn() == 0:
231 self.txn_removeAssociation(server_url, assoc.handle)
232 else:
233 associations.append((assoc.issued, assoc))
234
235 if associations:
236 associations.sort()
237 return associations[-1][1]
238 else:
239 return None
240
241 getAssociation = _inTxn(txn_getAssociation)
242
244 """Remove the association for the given server URL and handle,
245 returning whether the association existed at all.
246
247 (str, str) -> bool
248 """
249 self.db_remove_assoc(server_url, handle)
250 return self.cur.rowcount > 0
251
252 removeAssociation = _inTxn(txn_removeAssociation)
253
255 """Return whether this nonce is present, and if it is, then
256 remove it from the set.
257
258 str -> bool"""
259 if abs(timestamp - time.time()) > nonce.SKEW:
260 return False
261
262 try:
263 self.db_add_nonce(server_url, timestamp, salt)
264 except self.exceptions.IntegrityError:
265
266 return False
267 else:
268
269 return True
270
271 useNonce = _inTxn(txn_useNonce)
272
274 self.db_clean_nonce(int(time.time()) - nonce.SKEW)
275 return self.cur.rowcount
276
277 cleanupNonces = _inTxn(txn_cleanupNonces)
278
280 self.db_clean_assoc(int(time.time()))
281 return self.cur.rowcount
282
283 cleanupAssociations = _inTxn(txn_cleanupAssociations)
284
285
287 """
288 This is an SQLite-based specialization of C{L{SQLStore}}.
289
290 To create an instance, see C{L{SQLStore.__init__}}. To create the
291 tables it will use, see C{L{SQLStore.createTables}}.
292
293 All other methods are implementation details.
294 """
295
296 create_nonce_sql = """
297 CREATE TABLE %(nonces)s (
298 server_url VARCHAR,
299 timestamp INTEGER,
300 salt CHAR(40),
301 UNIQUE(server_url, timestamp, salt)
302 );
303 """
304
305 create_assoc_sql = """
306 CREATE TABLE %(associations)s
307 (
308 server_url VARCHAR(2047),
309 handle VARCHAR(255),
310 secret BLOB(128),
311 issued INTEGER,
312 lifetime INTEGER,
313 assoc_type VARCHAR(64),
314 PRIMARY KEY (server_url, handle)
315 );
316 """
317
318 set_assoc_sql = ('INSERT OR REPLACE INTO %(associations)s '
319 '(server_url, handle, secret, issued, '
320 'lifetime, assoc_type) '
321 'VALUES (?, ?, ?, ?, ?, ?);')
322 get_assocs_sql = ('SELECT handle, secret, issued, lifetime, assoc_type '
323 'FROM %(associations)s WHERE server_url = ?;')
324 get_assoc_sql = (
325 'SELECT handle, secret, issued, lifetime, assoc_type '
326 'FROM %(associations)s WHERE server_url = ? AND handle = ?;')
327
328 get_expired_sql = ('SELECT server_url '
329 'FROM %(associations)s WHERE issued + lifetime < ?;')
330
331 remove_assoc_sql = ('DELETE FROM %(associations)s '
332 'WHERE server_url = ? AND handle = ?;')
333
334 clean_assoc_sql = 'DELETE FROM %(associations)s WHERE issued + lifetime < ?;'
335
336 add_nonce_sql = 'INSERT INTO %(nonces)s VALUES (?, ?, ?);'
337
338 clean_nonce_sql = 'DELETE FROM %(nonces)s WHERE timestamp < ?;'
339
342
345
347
348
349
350 try:
351 return super(SQLiteStore, self).useNonce(*args, **kwargs)
352 except self.exceptions.OperationalError, why:
353 if re.match('^columns .* are not unique$', why[0]):
354 return False
355 else:
356 raise
357
359 """
360 This is a MySQL-based specialization of C{L{SQLStore}}.
361
362 Uses InnoDB tables for transaction support.
363
364 To create an instance, see C{L{SQLStore.__init__}}. To create the
365 tables it will use, see C{L{SQLStore.createTables}}.
366
367 All other methods are implementation details.
368 """
369
370 try:
371 import MySQLdb as exceptions
372 except ImportError:
373 exceptions = None
374
375 create_nonce_sql = """
376 CREATE TABLE %(nonces)s (
377 server_url BLOB NOT NULL,
378 timestamp INTEGER NOT NULL,
379 salt CHAR(40) NOT NULL,
380 PRIMARY KEY (server_url(255), timestamp, salt)
381 )
382 ENGINE=InnoDB;
383 """
384
385 create_assoc_sql = """
386 CREATE TABLE %(associations)s
387 (
388 server_url BLOB NOT NULL,
389 handle VARCHAR(255) NOT NULL,
390 secret BLOB NOT NULL,
391 issued INTEGER NOT NULL,
392 lifetime INTEGER NOT NULL,
393 assoc_type VARCHAR(64) NOT NULL,
394 PRIMARY KEY (server_url(255), handle)
395 )
396 ENGINE=InnoDB;
397 """
398
399 set_assoc_sql = ('REPLACE INTO %(associations)s '
400 'VALUES (%%s, %%s, %%s, %%s, %%s, %%s);')
401 get_assocs_sql = ('SELECT handle, secret, issued, lifetime, assoc_type'
402 ' FROM %(associations)s WHERE server_url = %%s;')
403 get_expired_sql = ('SELECT server_url '
404 'FROM %(associations)s WHERE issued + lifetime < %%s;')
405
406 get_assoc_sql = (
407 'SELECT handle, secret, issued, lifetime, assoc_type'
408 ' FROM %(associations)s WHERE server_url = %%s AND handle = %%s;')
409 remove_assoc_sql = ('DELETE FROM %(associations)s '
410 'WHERE server_url = %%s AND handle = %%s;')
411
412 clean_assoc_sql = 'DELETE FROM %(associations)s WHERE issued + lifetime < %%s;'
413
414 add_nonce_sql = 'INSERT INTO %(nonces)s VALUES (%%s, %%s, %%s);'
415
416 clean_nonce_sql = 'DELETE FROM %(nonces)s WHERE timestamp < %%s;'
417
419 if type(blob) is str:
420
421 return blob
422 else:
423
424 return blob.tostring()
425
426 -class PostgreSQLStore(SQLStore):
427 """
428 This is a PostgreSQL-based specialization of C{L{SQLStore}}.
429
430 To create an instance, see C{L{SQLStore.__init__}}. To create the
431 tables it will use, see C{L{SQLStore.createTables}}.
432
433 All other methods are implementation details.
434 """
435
436 try:
437 import psycopg as exceptions
438 except ImportError:
439
440
441
442
443
444 exceptions = None
445
446 create_nonce_sql = """
447 CREATE TABLE %(nonces)s (
448 server_url VARCHAR(2047) NOT NULL,
449 timestamp INTEGER NOT NULL,
450 salt CHAR(40) NOT NULL,
451 PRIMARY KEY (server_url, timestamp, salt)
452 );
453 """
454
455 create_assoc_sql = """
456 CREATE TABLE %(associations)s
457 (
458 server_url VARCHAR(2047) NOT NULL,
459 handle VARCHAR(255) NOT NULL,
460 secret BYTEA NOT NULL,
461 issued INTEGER NOT NULL,
462 lifetime INTEGER NOT NULL,
463 assoc_type VARCHAR(64) NOT NULL,
464 PRIMARY KEY (server_url, handle),
465 CONSTRAINT secret_length_constraint CHECK (LENGTH(secret) <= 128)
466 );
467 """
468
469 - def db_set_assoc(self, server_url, handle, secret, issued, lifetime, assoc_type):
470 """
471 Set an association. This is implemented as a method because
472 REPLACE INTO is not supported by PostgreSQL (and is not
473 standard SQL).
474 """
475 result = self.db_get_assoc(server_url, handle)
476 rows = self.cur.fetchall()
477 if len(rows):
478
479 return self.db_update_assoc(secret, issued, lifetime, assoc_type,
480 server_url, handle)
481 else:
482
483
484 return self.db_new_assoc(server_url, handle, secret, issued,
485 lifetime, assoc_type)
486
487 new_assoc_sql = ('INSERT INTO %(associations)s '
488 'VALUES (%%s, %%s, %%s, %%s, %%s, %%s);')
489 update_assoc_sql = ('UPDATE %(associations)s SET '
490 'secret = %%s, issued = %%s, '
491 'lifetime = %%s, assoc_type = %%s '
492 'WHERE server_url = %%s AND handle = %%s;')
493 get_assocs_sql = ('SELECT handle, secret, issued, lifetime, assoc_type'
494 ' FROM %(associations)s WHERE server_url = %%s;')
495 get_expired_sql = ('SELECT server_url '
496 'FROM %(associations)s WHERE issued + lifetime < %%s;')
497
498 get_assoc_sql = (
499 'SELECT handle, secret, issued, lifetime, assoc_type'
500 ' FROM %(associations)s WHERE server_url = %%s AND handle = %%s;')
501 remove_assoc_sql = ('DELETE FROM %(associations)s '
502 'WHERE server_url = %%s AND handle = %%s;')
503
504 clean_assoc_sql = 'DELETE FROM %(associations)s WHERE issued + lifetime < %%s;'
505
506 add_nonce_sql = 'INSERT INTO %(nonces)s VALUES (%%s, %%s, %%s);'
507
508 clean_nonce_sql = 'DELETE FROM %(nonces)s WHERE timestamp < %%s;'
509
510 - def blobEncode(self, blob):
511 try:
512 from psycopg2 import Binary
513 except ImportError:
514 from psycopg import Binary
515
516 return Binary(blob)
517