Package openid :: Package store :: Module sqlstore
[frames] | no frames]

Source Code for Module openid.store.sqlstore

  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   
16 -def _inTxn(func):
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
31 -class SQLStore(OpenIDStore):
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{settings_table}}, 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 settings_table: This is the default name of the table to 53 keep this store's settings in. 54 55 @cvar associations_table: This is the default name of the table to 56 keep associations in 57 58 @cvar nonces_table: This is the default name of the table to keep 59 nonces in. 60 61 62 @sort: __init__, createTables 63 """ 64 65 settings_table = 'oid_settings' 66 associations_table = 'oid_associations' 67 nonces_table = 'oid_nonces' 68
69 - def __init__(self, conn, settings_table=None, associations_table=None, 70 nonces_table=None):
71 """ 72 This creates a new SQLStore instance. It requires an 73 established database connection be given to it, and it allows 74 overriding the default table names. 75 76 77 @param conn: This must be an established connection to a 78 database of the correct type for the SQLStore subclass 79 you're using. 80 81 @type conn: A python database API compatible connection 82 object. 83 84 85 @param settings_table: This is an optional parameter to 86 specify the name of the table used for this store's 87 settings. The default value is specified in 88 C{L{SQLStore.settings_table}}. 89 90 @type settings_table: C{str} 91 92 93 @param associations_table: This is an optional parameter to 94 specify the name of the table used for storing 95 associations. The default value is specified in 96 C{L{SQLStore.associations_table}}. 97 98 @type associations_table: C{str} 99 100 101 @param nonces_table: This is an optional parameter to specify 102 the name of the table used for storing nonces. The 103 default value is specified in C{L{SQLStore.nonces_table}}. 104 105 @type nonces_table: C{str} 106 """ 107 self.conn = conn 108 self.cur = None 109 self._statement_cache = {} 110 self._table_names = { 111 'settings': settings_table or self.settings_table, 112 'associations': associations_table or self.associations_table, 113 'nonces': nonces_table or self.nonces_table, 114 } 115 self.max_nonce_age = 6 * 60 * 60 # Six hours, in seconds
116
117 - def blobDecode(self, blob):
118 """Convert a blob as returned by the SQL engine into a str object. 119 120 str -> str""" 121 return blob
122
123 - def blobEncode(self, s):
124 """Convert a str object into the necessary object for storing 125 in the database as a blob.""" 126 return s
127
128 - def _getSQL(self, sql_name):
129 try: 130 return self._statement_cache[sql_name] 131 except KeyError: 132 sql = getattr(self, sql_name) 133 sql %= self._table_names 134 self._statement_cache[sql_name] = sql 135 return sql
136
137 - def _execSQL(self, sql_name, *args):
138 sql = self._getSQL(sql_name) 139 self.cur.execute(sql, args)
140
141 - def __getattr__(self, attr):
142 # if the attribute starts with db_, use a default 143 # implementation that looks up the appropriate SQL statement 144 # as an attribute of this object and executes it. 145 if attr[:3] == 'db_': 146 sql_name = attr[3:] + '_sql' 147 def func(*args): 148 return self._execSQL(sql_name, *args)
149 setattr(self, attr, func) 150 return func 151 else: 152 raise AttributeError('Attribute %r not found' % (attr,))
153
154 - def _callInTransaction(self, func, *args, **kwargs):
155 """Execute the given function inside of a transaction, with an 156 open cursor. If no exception is raised, the transaction is 157 comitted, otherwise it is rolled back.""" 158 # No nesting of transactions 159 self.conn.rollback() 160 161 try: 162 self.cur = self.conn.cursor() 163 try: 164 ret = func(*args, **kwargs) 165 finally: 166 self.cur.close() 167 self.cur = None 168 except: 169 self.conn.rollback() 170 raise 171 else: 172 self.conn.commit() 173 174 return ret
175
176 - def txn_createTables(self):
177 """ 178 This method creates the database tables necessary for this 179 store to work. It should not be called if the tables already 180 exist. 181 """ 182 self.db_create_nonce() 183 self.db_create_assoc() 184 self.db_create_settings()
185 186 createTables = _inTxn(txn_createTables) 187
188 - def txn_storeAssociation(self, server_url, association):
189 """Set the association for the server URL. 190 191 Association -> NoneType 192 """ 193 a = association 194 self.db_set_assoc( 195 server_url, 196 a.handle, 197 self.blobEncode(a.secret), 198 a.issued, 199 a.lifetime, 200 a.assoc_type)
201 202 storeAssociation = _inTxn(txn_storeAssociation) 203
204 - def txn_getAssociation(self, server_url, handle=None):
205 """Get the most recent association that has been set for this 206 server URL and handle. 207 208 str -> NoneType or Association 209 """ 210 if handle is not None: 211 self.db_get_assoc(server_url, handle) 212 else: 213 self.db_get_assocs(server_url) 214 215 rows = self.cur.fetchall() 216 if len(rows) == 0: 217 return None 218 else: 219 associations = [] 220 for values in rows: 221 assoc = Association(*values) 222 assoc.secret = self.blobDecode(assoc.secret) 223 if assoc.getExpiresIn() == 0: 224 self.txn_removeAssociation(server_url, assoc.handle) 225 else: 226 associations.append((assoc.issued, assoc)) 227 228 if associations: 229 associations.sort() 230 return associations[-1][1] 231 else: 232 return None
233 234 getAssociation = _inTxn(txn_getAssociation) 235
236 - def txn_removeAssociation(self, server_url, handle):
237 """Remove the association for the given server URL and handle, 238 returning whether the association existed at all. 239 240 (str, str) -> bool 241 """ 242 self.db_remove_assoc(server_url, handle) 243 return self.cur.rowcount > 0 # -1 is undefined
244 245 removeAssociation = _inTxn(txn_removeAssociation) 246
247 - def txn_useNonce(self, server_url, timestamp, salt):
248 """Return whether this nonce is present, and if it is, then 249 remove it from the set. 250 251 str -> bool""" 252 if abs(timestamp - time.time()) > nonce.SKEW: 253 return False 254 255 try: 256 self.db_add_nonce(server_url, timestamp, salt) 257 except self.dbapi.IntegrityError: 258 # The key uniqueness check failed 259 return False 260 else: 261 # The nonce was successfully added 262 return True
263 264 useNonce = _inTxn(txn_useNonce) 265
266 - def txn_cleanupNonces(self):
267 self.db_clean_nonce(int(time.time()) - nonce.SKEW) 268 return self.cur.rowcount
269 270 cleanupNonces = _inTxn(txn_cleanupNonces) 271
272 - def txn_cleanupAssociations(self):
273 self.db_clean_assoc(int(time.time())) 274 return self.cur.rowcount
275 276 cleanupAssociations = _inTxn(txn_cleanupAssociations) 277 278
279 -class SQLiteStore(SQLStore):
280 """ 281 This is an SQLite-based specialization of C{L{SQLStore}}. 282 283 To create an instance, see C{L{SQLStore.__init__}}. To create the 284 tables it will use, see C{L{SQLStore.createTables}}. 285 286 All other methods are implementation details. 287 """ 288 289 try: 290 from pysqlite2 import dbapi2 as dbapi 291 except ImportError: 292 pass 293 294 create_nonce_sql = """ 295 CREATE TABLE %(nonces)s ( 296 server_url VARCHAR, 297 timestamp INTEGER, 298 salt CHAR(40), 299 UNIQUE(server_url, timestamp, salt) 300 ); 301 """ 302 303 create_assoc_sql = """ 304 CREATE TABLE %(associations)s 305 ( 306 server_url VARCHAR(2047), 307 handle VARCHAR(255), 308 secret BLOB(128), 309 issued INTEGER, 310 lifetime INTEGER, 311 assoc_type VARCHAR(64), 312 PRIMARY KEY (server_url, handle) 313 ); 314 """ 315 316 create_settings_sql = """ 317 CREATE TABLE %(settings)s 318 ( 319 setting VARCHAR(128) UNIQUE PRIMARY KEY, 320 value BLOB(20) 321 ); 322 """ 323 324 set_assoc_sql = ('INSERT OR REPLACE INTO %(associations)s ' 325 'VALUES (?, ?, ?, ?, ?, ?);') 326 get_assocs_sql = ('SELECT handle, secret, issued, lifetime, assoc_type ' 327 'FROM %(associations)s WHERE server_url = ?;') 328 get_assoc_sql = ( 329 'SELECT handle, secret, issued, lifetime, assoc_type ' 330 'FROM %(associations)s WHERE server_url = ? AND handle = ?;') 331 332 get_expired_sql = ('SELECT server_url ' 333 'FROM %(associations)s WHERE issued + lifetime < ?;') 334 335 remove_assoc_sql = ('DELETE FROM %(associations)s ' 336 'WHERE server_url = ? AND handle = ?;') 337 338 clean_assoc_sql = 'DELETE FROM %(associations)s WHERE issued + lifetime < ?;' 339 340 add_nonce_sql = 'INSERT INTO %(nonces)s VALUES (?, ?, ?);' 341 342 clean_nonce_sql = 'DELETE FROM %(nonces)s WHERE timestamp < ?;' 343
344 - def blobDecode(self, buf):
345 return str(buf)
346
347 - def blobEncode(self, s):
348 return buffer(s)
349
350 - def useNonce(self, *args, **kwargs):
351 # Older versions of the sqlite wrapper do not raise 352 # IntegrityError as they should, so we have to detect the 353 # message from the OperationalError. 354 try: 355 return super(SQLiteStore, self).useNonce(*args, **kwargs) 356 except self.dbapi.OperationalError, why: 357 if re.match('^columns .* are not unique$', why[0]): 358 return False 359 else: 360 raise
361
362 -class MySQLStore(SQLStore):
363 """ 364 This is a MySQL-based specialization of C{L{SQLStore}}. 365 366 Uses InnoDB tables for transaction support. 367 368 To create an instance, see C{L{SQLStore.__init__}}. To create the 369 tables it will use, see C{L{SQLStore.createTables}}. 370 371 All other methods are implementation details. 372 """ 373 374 try: 375 import MySQLdb as dbapi 376 except ImportError: 377 pass 378 379 create_nonce_sql = """ 380 CREATE TABLE %(nonces)s ( 381 server_url BLOB, 382 timestamp INTEGER, 383 salt CHAR(40), 384 PRIMARY KEY (server_url(255), timestamp, salt) 385 ) 386 TYPE=InnoDB; 387 """ 388 389 create_assoc_sql = """ 390 CREATE TABLE %(associations)s 391 ( 392 server_url BLOB, 393 handle VARCHAR(255), 394 secret BLOB, 395 issued INTEGER, 396 lifetime INTEGER, 397 assoc_type VARCHAR(64), 398 PRIMARY KEY (server_url(255), handle) 399 ) 400 TYPE=InnoDB; 401 """ 402 403 create_settings_sql = """ 404 CREATE TABLE %(settings)s 405 ( 406 setting VARCHAR(128) UNIQUE PRIMARY KEY, 407 value BLOB 408 ) 409 TYPE=InnoDB; 410 """ 411 412 set_assoc_sql = ('REPLACE INTO %(associations)s ' 413 'VALUES (%%s, %%s, %%s, %%s, %%s, %%s);') 414 get_assocs_sql = ('SELECT handle, secret, issued, lifetime, assoc_type' 415 ' FROM %(associations)s WHERE server_url = %%s;') 416 get_expired_sql = ('SELECT server_url ' 417 'FROM %(associations)s WHERE issued + lifetime < %%s;') 418 419 get_assoc_sql = ( 420 'SELECT handle, secret, issued, lifetime, assoc_type' 421 ' FROM %(associations)s WHERE server_url = %%s AND handle = %%s;') 422 remove_assoc_sql = ('DELETE FROM %(associations)s ' 423 'WHERE server_url = %%s AND handle = %%s;') 424 425 clean_assoc_sql = 'DELETE FROM %(associations)s WHERE issued + lifetime < %%s;' 426 427 add_nonce_sql = 'INSERT INTO %(nonces)s VALUES (%%s, %%s, %%s);' 428 429 clean_nonce_sql = 'DELETE FROM %(nonces)s WHERE timestamp < %%s;' 430
431 - def blobDecode(self, blob):
432 return blob.tostring()
433
434 -class PostgreSQLStore(SQLStore):
435 """ 436 This is a PostgreSQL-based specialization of C{L{SQLStore}}. 437 438 To create an instance, see C{L{SQLStore.__init__}}. To create the 439 tables it will use, see C{L{SQLStore.createTables}}. 440 441 All other methods are implementation details. 442 """ 443 444 try: 445 import psycopg as dbapi 446 except ImportError: 447 pass 448 449 create_nonce_sql = """ 450 CREATE TABLE %(nonces)s ( 451 server_url VARCHAR(2047), 452 timestamp INTEGER, 453 salt CHAR(40), 454 PRIMARY KEY (server_url, timestamp, salt) 455 ); 456 """ 457 458 create_assoc_sql = """ 459 CREATE TABLE %(associations)s 460 ( 461 server_url VARCHAR(2047), 462 handle VARCHAR(255), 463 secret BYTEA, 464 issued INTEGER, 465 lifetime INTEGER, 466 assoc_type VARCHAR(64), 467 PRIMARY KEY (server_url, handle), 468 CONSTRAINT secret_length_constraint CHECK (LENGTH(secret) <= 128) 469 ); 470 """ 471 472 create_settings_sql = """ 473 CREATE TABLE %(settings)s 474 ( 475 setting VARCHAR(128) UNIQUE PRIMARY KEY, 476 value BYTEA, 477 CONSTRAINT value_length_constraint CHECK (LENGTH(value) <= 20) 478 ); 479 """ 480
481 - def db_set_assoc(self, server_url, handle, secret, issued, lifetime, assoc_type):
482 """ 483 Set an association. This is implemented as a method because 484 REPLACE INTO is not supported by PostgreSQL (and is not 485 standard SQL). 486 """ 487 result = self.db_get_assoc(server_url, handle) 488 rows = self.cur.fetchall() 489 if len(rows): 490 # Update the table since this associations already exists. 491 return self.db_update_assoc(secret, issued, lifetime, assoc_type, 492 server_url, handle) 493 else: 494 # Insert a new record because this association wasn't 495 # found. 496 return self.db_new_assoc(server_url, handle, secret, issued, 497 lifetime, assoc_type)
498 499 new_assoc_sql = ('INSERT INTO %(associations)s ' 500 'VALUES (%%s, %%s, %%s, %%s, %%s, %%s);') 501 update_assoc_sql = ('UPDATE %(associations)s SET ' 502 'secret = %%s, issued = %%s, ' 503 'lifetime = %%s, assoc_type = %%s ' 504 'WHERE server_url = %%s AND handle = %%s;') 505 get_assocs_sql = ('SELECT handle, secret, issued, lifetime, assoc_type' 506 ' FROM %(associations)s WHERE server_url = %%s;') 507 get_expired_sql = ('SELECT server_url ' 508 'FROM %(associations)s WHERE issued + lifetime < %%s;') 509 510 get_assoc_sql = ( 511 'SELECT handle, secret, issued, lifetime, assoc_type' 512 ' FROM %(associations)s WHERE server_url = %%s AND handle = %%s;') 513 remove_assoc_sql = ('DELETE FROM %(associations)s ' 514 'WHERE server_url = %%s AND handle = %%s;') 515 516 clean_assoc_sql = 'DELETE FROM %(associations)s WHERE issued + lifetime < %%s;' 517 518 add_nonce_sql = 'INSERT INTO %(nonces)s VALUES (%%s, %%s, %%s);' 519 520 clean_nonce_sql = 'DELETE FROM %(nonces)s WHERE timestamp < %%s;' 521
522 - def blobEncode(self, blob):
523 import psycopg 524 return psycopg.Binary(blob)
525