1 """Convenient access to an SQLObject or SQLAlchemy managed database."""
2
3 import sys
4 import time
5 import logging
6
7 import cherrypy
8 from cherrypy import request
9 from cherrypy.filters.basefilter import BaseFilter
10
11 try:
12 import sqlalchemy, sqlalchemy.orm
13 from sqlalchemy import MetaData
14 try:
15 from sqlalchemy.exc import ArgumentError, OperationalError
16 except ImportError:
17 from sqlalchemy.exceptions import ArgumentError, OperationalError
18 except ImportError:
19 sqlalchemy = None
20
21 try:
22 import sqlobject
23 from sqlobject.dbconnection import ConnectionHub, Transaction, TheURIOpener
24 from sqlobject.util.threadinglocal import local as threading_local
25 except ImportError:
26 sqlobject = None
27
28 from peak.rules import abstract, when, NoApplicableMethods
29
30 from turbogears import config
31 from turbogears.util import remove_keys
32
33 log = logging.getLogger("turbogears.database")
34
35 _using_sa = False
42
43
44 if sqlalchemy:
50
58
86
88 """Create a session that uses the engine from thread-local metadata.
89
90 The session by default does not begin a transaction, and requires that
91 flush() be called explicitly in order to persist results to the database.
92
93 """
94 if not metadata.is_bound():
95 bind_metadata()
96 return sqlalchemy.orm.create_session()
97
98 session = sqlalchemy.orm.scoped_session(create_session)
99
100 if not hasattr(session, 'add'):
101 session.add = session.save_or_update
114 def mapper(cls, *args, **kw):
115 set_kwargs_on_init = kw.pop('set_kwargs_on_init', True)
116 validate = kw.pop('validate', False)
117
118
119 autoadd = kw.pop('autoadd', kw.pop('save_on_init', True))
120
121 if set_kwargs_on_init and (getattr(cls,
122 '__init__', object.__init__) is object.__init__
123 or getattr(cls.__init__, '_session_mapper', False)):
124 def __init__(self, **kwargs):
125 for key, value in kwargs.items():
126 if validate:
127 if not hasattr(self, key):
128 raise TypeError(
129 "Invalid __init__ argument: '%s'" % key)
130 setattr(self, key, value)
131 if autoadd:
132 session.add(self)
133 __init__._session_mapper = True
134 cls.__init__ = __init__
135 cls.query = scoped_session.query_property()
136 return sqlalchemy.orm.mapper(cls, *args, **kw)
137 return mapper
138 session_mapper = create_session_mapper()
139 if hasattr(session, 'mapper'):
140
141 mapper = session.mapper
142 else:
143 mapper = session_mapper
144
145 _metadatas = {}
146 _metadatas[None] = MetaData()
147 metadata = _metadatas[None]
148
149 try:
150 import elixir
151 elixir.metadata, elixir.session = metadata, session
152 except ImportError:
153 pass
154
155 else:
164 session = metadata = mapper = None
165
166 bind_meta_data = bind_metadata
167
168 hub_registry = set()
169
170 _hubs = dict()
171
172
173 if sqlobject:
175 """Convert a MySQL TIMESTAMP to a floating point number representing
176 the seconds since the Un*x Epoch. It uses custom code the input seems
177 to be the new (MySQL 4.1+) timestamp format, otherwise code from the
178 MySQLdb module is used."""
179 if raw[4] == '-':
180 return time.mktime(time.strptime(raw, '%Y-%m-%d %H:%M:%S'))
181 else:
182 import MySQLdb.converters
183 return MySQLdb.converters.mysql_timestamp_converter(raw)
184
187 """Connects to the database once per thread. The AutoConnectHub also
188 provides convenient methods for managing transactions."""
189 uri = None
190 params = {}
191
192 - def __init__(self, uri=None, supports_transactions=True):
199
201 """Return True only if version of MySQLdb <= 1.0."""
202 import MySQLdb
203 module_version = MySQLdb.version_info[0:2]
204 major = module_version[0]
205 minor = module_version[1]
206
207 return (major < 1 or (major == 1 and minor < 2))
208
210 """Enable a workaround for an incompatible timestamp format change
211 in MySQL 4.1 when using an old version of MySQLdb. See trac ticket
212 #1235 - http://trac.turbogears.org/ticket/1235 for details."""
213
214 import MySQLdb
215 import MySQLdb.converters
216 if self._is_interesting_version():
217 conversions = MySQLdb.converters.conversions.copy()
218 conversions[MySQLdb.constants.FIELD_TYPE.TIMESTAMP] = \
219 _mysql_timestamp_converter
220
221
222
223 connection.kw["conv"] = conversions
224
226 try:
227 conn = self.threadingLocal.connection
228 return self.begin(conn)
229 except AttributeError:
230 if self.uri:
231 conn = sqlobject.connectionForURI(self.uri)
232
233
234
235 if self.uri.startswith("sqlite"):
236 TheURIOpener.cachedURIs = {}
237 elif self.uri.startswith("mysql") and \
238 config.get("turbogears.enable_mysql41_timestamp_workaround", False):
239 self._enable_timestamp_workaround(conn)
240 self.threadingLocal.connection = conn
241 return self.begin(conn)
242 raise AttributeError(
243 "No connection has been defined for this thread "
244 "or process")
245
247 """Used for testing purposes. This drops all of the connections
248 that are being held."""
249 self.threadingLocal = threading_local()
250
251 - def begin(self, conn=None):
252 """Start a transaction."""
253 if not self.supports_transactions:
254 return conn
255 if not conn:
256 conn = self.getConnection()
257 if isinstance(conn, Transaction):
258 if conn._obsolete:
259 conn.begin()
260 return conn
261 self.threadingLocal.old_conn = conn
262 trans = conn.transaction()
263 self.threadingLocal.connection = trans
264 return trans
265
267 """Commit the current transaction."""
268 if not self.supports_transactions:
269 return
270 try:
271 conn = self.threadingLocal.connection
272 except AttributeError:
273 return
274 if isinstance(conn, Transaction):
275 self.threadingLocal.connection.commit()
276
278 """Rollback the current transaction."""
279 if not self.supports_transactions:
280 return
281 try:
282 conn = self.threadingLocal.connection
283 except AttributeError:
284 return
285 if isinstance(conn, Transaction) and not conn._obsolete:
286 self.threadingLocal.connection.rollback()
287
289 """End the transaction, returning to a standard connection."""
290 if not self.supports_transactions:
291 return
292 try:
293 conn = self.threadingLocal.connection
294 except AttributeError:
295 return
296 if not isinstance(conn, Transaction):
297 return
298 if not conn._obsolete:
299 conn.rollback()
300 self.threadingLocal.connection = self.threadingLocal.old_conn
301 del self.threadingLocal.old_conn
302 self.threadingLocal.connection.expireAll()
303
305 """Transparently proxies to an AutoConnectHub for the URI
306 that is appropriate for this package. A package URI is
307 configured via "packagename.dburi" in the TurboGears config
308 settings. If there is no package DB URI configured, the
309 default (provided by "sqlobject.dburi") is used.
310
311 The hub is not instantiated until an attempt is made to
312 use the database.
313 """
315 self.packagename = packagename
316 self.hub = None
317
319 if self.hub:
320 return self.hub.__get__(obj, type)
321 else:
322 return self
323
328
336
338 dburi = config.get("%s.dburi" % self.packagename, None)
339 if not dburi:
340 dburi = config.get("sqlobject.dburi", None)
341 if not dburi:
342 raise DatabaseConfigurationError(
343 "No sqlobject database configuration found!")
344 if dburi.startswith("notrans_"):
345 dburi = dburi[8:]
346 trans = False
347 else:
348 trans = True
349 hub = _hubs.get(dburi, None)
350 if not hub:
351 hub = AutoConnectHub(dburi, supports_transactions=trans)
352 _hubs[dburi] = hub
353 self.hub = hub
354 else:
357
360
362 """Sets the database URI to use either globally or for a specific
363 package. Note that once the database is accessed, calling
364 setDBUri will have no effect.
365
366 @param dburi: database URI to use
367 @param package: package name this applies to, or None to set the default.
368 """
369 if package:
370 config.update({'global':
371 {"%s.dburi" % package : dburi}
372 })
373 else:
374 config.update({'global':
375 {"sqlobject.dburi" : dburi}
376 })
377
379 """Commit the transactions in all registered hubs (for this thread)."""
380 for hub in hub_registry:
381 hub.commit()
382
387
389 """End the transactions in all registered hubs (for this thread)."""
390 for hub in hub_registry:
391 hub.end()
392
396
400
403
404
405 @when(run_with_transaction, "not _use_sa(args)")
406 -def so_rwt(func, *args, **kw):
407 log.debug("Starting SQLObject transaction")
408 try:
409 try:
410 retval = func(*args, **kw)
411 commit_all()
412 return retval
413 except cherrypy.HTTPRedirect:
414 commit_all()
415 raise
416 except cherrypy.InternalRedirect:
417 commit_all()
418 raise
419 except:
420
421
422 rollback_all()
423 raise
424 finally:
425 end_all()
426
433
435
436 from turbogears.errorhandling import dispatch_error
437
438 real_func, accept, allow_json, controller = args[:4]
439 args = args[4:]
440 exc_type, exc_value, exc_trace = sys.exc_info()
441 remove_keys(kw, ("tg_source", "tg_errors", "tg_exceptions"))
442 try:
443 output = dispatch_error(
444 controller, real_func, None, exception, *args, **kw)
445 except NoApplicableMethods:
446 raise exc_type, exc_value, exc_trace
447 else:
448 del exc_trace
449 return output
450
451
452 @when(run_with_transaction, "_use_sa(args)")
453 -def sa_rwt(func, *args, **kw):
454 log.debug("Starting SA transaction")
455 request.sa_transaction = session.begin()
456 try:
457 try:
458 retval = func(*args, **kw)
459 except (cherrypy.HTTPRedirect, cherrypy.InternalRedirect):
460
461 if sa_transaction_active():
462 log.debug('Redirect in active transaction - will commit now')
463 session.commit()
464 else:
465 log.debug('Redirect in inactive transaction')
466 raise
467 except:
468
469 if sa_transaction_active():
470 log.debug('Error in active transaction - will rollback now')
471 session.rollback()
472 else:
473 log.debug('Error in inactive transaction')
474 raise
475
476 if sa_transaction_active():
477 log.debug('Transaction is still active - will commit now')
478 session.commit()
479 else:
480 log.debug('Transaction is already inactive')
481 finally:
482 log.debug('Ending SA transaction')
483 session.close()
484 return retval
485
489 log.debug("Restarting SA transaction")
490 if sa_transaction_active():
491 log.debug('Transaction is still active - will rollback now')
492 session.rollback()
493 else:
494 log.debug('Transaction is already inactive')
495 session.close()
496 request.sa_transaction = session.begin()
497
499 """Check whether SA transaction is still active."""
500 try:
501 return session.is_active
502 except AttributeError:
503 try:
504 return session().is_active
505 except (TypeError, AttributeError):
506 try:
507 transaction = request.sa_transaction
508 return transaction and transaction.is_active
509 except AttributeError:
510 return False
511
513 """Convert SQLObject to a dictionary based on columns."""
514 d = {}
515 if sqlobj is None:
516 return d
517 for name in sqlobj.sqlmeta.columns.keys():
518 d[name] = getattr(sqlobj, name)
519 d['id'] = sqlobj.id
520 if sqlobj._inheritable:
521 d.update(so_to_dict(sqlobj._parent))
522 d.pop('childName')
523 return d
524
526 """Return a dict with all columns from a SQLObject.
527
528 This includes the columns from InheritableSO's bases.
529
530 """
531 if columns is None:
532 columns = {}
533 columns.update(filter(lambda i: i[0] != 'childName',
534 sqlclass.sqlmeta.columns.items()))
535 if sqlclass._inheritable:
536 so_columns(sqlclass.__base__, columns)
537 return columns
538
540 """Return a list with all joins from a SQLObject.
541
542 The list includes the columns from InheritableSO's bases.
543
544 """
545 if joins is None:
546 joins = []
547 joins.extend(sqlclass.sqlmeta.joins)
548 if sqlclass._inheritable:
549 so_joins(sqlclass.__base__, joins)
550 return joins
551
554 if _use_sa():
555 try:
556 session.expunge_all()
557 except AttributeError:
558 session.clear()
559 else:
560 end_all()
561
562 __all__ = ["AutoConnectHub", "bind_metadata", "create_session",
563 "create_session_mapper", "commit_all", "end_all",
564 "DatabaseError", "DatabaseConfigurationError",
565 "EndTransactionsFilter", "get_engine", "get_metadata", "mapper",
566 "metadata", "PackageHub", "rollback_all", "session",
567 "session_mapper", "set_db_uri", "so_columns", "so_joins",
568 "so_to_dict"]
569