42
42
functionality should be handled at a logical level. For an example, look at
43
43
the code around quotas and reservation_rollback().
47
47
def get_foo(context, foo):
48
return model_query(context, models.Foo).\
48
return (model_query(context, models.Foo).
52
52
def update_foo(context, id, newfoo):
53
model_query(context, models.Foo).\
55
update({'foo': newfoo})
53
(model_query(context, models.Foo).
55
update({'foo': newfoo}))
57
57
def create_foo(context, values):
58
58
foo_ref = models.Foo()
66
66
handler will take care of calling flush() and commit() for you.
67
67
If using this approach, you should not explicitly call flush() or commit().
68
68
Any error within the context of the session will cause the session to emit
69
a ROLLBACK. If the connection is dropped before this is possible, the
70
database will implicitly rollback the transaction.
69
a ROLLBACK. Database Errors like IntegrityError will be raised in
70
session's __exit__ handler, and any try/except within the context managed
71
by session will not be triggered. And catching other non-database errors in
72
the session will not trigger the ROLLBACK, so exception handlers should
73
always be outside the session, unless the developer wants to do a partial
74
commit on purpose. If the connection is dropped before this is possible,
75
the database will implicitly roll back the transaction.
72
77
Note: statements in the session scope will not be automatically retried.
74
79
If you create models within the session, they need to be added, but you
75
80
do not need to call model.save()
77
84
def create_many_foo(context, foos):
78
85
session = get_session()
79
86
with session.begin():
85
92
def update_bar(context, foo_id, newbar):
86
93
session = get_session()
87
94
with session.begin():
88
foo_ref = model_query(context, models.Foo, session).\
89
filter_by(id=foo_id).\
91
model_query(context, models.Bar, session).\
92
filter_by(id=foo_ref['bar_id']).\
93
update({'bar': newbar})
95
foo_ref = (model_query(context, models.Foo, session).
98
(model_query(context, models.Bar, session).
99
filter_by(id=foo_ref['bar_id']).
100
update({'bar': newbar}))
95
102
Note: update_bar is a trivially simple example of using "with session.begin".
96
103
Whereas create_many_foo is a good example of when a transaction is needed,
97
104
it is always best to use as few queries as possible. The two queries in
98
105
update_bar can be better expressed using a single query which avoids
99
the need for an explicit transaction. It can be expressed like so:
106
the need for an explicit transaction. It can be expressed like so::
101
108
def update_bar(context, foo_id, newbar):
102
subq = model_query(context, models.Foo.id).\
103
filter_by(id=foo_id).\
106
model_query(context, models.Bar).\
107
filter_by(id=subq.as_scalar()).\
108
update({'bar': newbar})
109
subq = (model_query(context, models.Foo.id).
110
filter_by(id=foo_id).
113
(model_query(context, models.Bar).
114
filter_by(id=subq.as_scalar()).
115
update({'bar': newbar}))
110
For reference, this emits approximagely the following SQL statement:
117
For reference, this emits approximately the following SQL statement::
112
119
UPDATE bar SET bar = ${newbar}
113
120
WHERE id=(SELECT bar_id FROM foo WHERE id = ${foo_id} LIMIT 1);
122
Note: create_duplicate_foo is a trivially simple example of catching an
123
exception while using "with session.begin". Here create two duplicate
124
instances with same primary key, must catch the exception out of context
125
managed by a single session:
127
def create_duplicate_foo(context):
130
foo1.id = foo2.id = 1
131
session = get_session()
133
with session.begin():
136
except exception.DBDuplicateEntry as e:
115
139
* Passing an active session between methods. Sessions should only be passed
116
140
to private methods. The private method must use a subtransaction; otherwise
117
141
SQLAlchemy will throw an error when you call session.begin() on an existing
180
206
Efficient use of soft deletes:
182
* There are two possible ways to mark a record as deleted:
208
* There are two possible ways to mark a record as deleted::
183
210
model.soft_delete() and query.soft_delete().
185
212
model.soft_delete() method works with single already fetched entry.
186
213
query.soft_delete() makes only one db request for all entries that correspond
189
* In almost all cases you should use query.soft_delete(). Some examples:
216
* In almost all cases you should use query.soft_delete(). Some examples::
191
218
def soft_delete_bar():
192
219
count = model_query(BarModel).find(some_condition).soft_delete()
197
224
if session is None:
198
225
session = get_session()
199
226
with session.begin(subtransactions=True):
200
count = model_query(BarModel).\
201
find(some_condition).\
202
soft_delete(synchronize_session=True)
227
count = (model_query(BarModel).
228
find(some_condition).
229
soft_delete(synchronize_session=True))
203
230
# Here synchronize_session is required, because we
204
231
# don't know what is going on in outer session.
217
246
bar_ref.soft_delete(session=session)
219
248
However, if you need to work with all entries that correspond to query and
220
then soft delete them you should use query.soft_delete() method:
249
then soft delete them you should use query.soft_delete() method::
222
251
def soft_delete_multi_models():
223
252
session = get_session()
224
253
with session.begin():
225
query = model_query(BarModel, session=session).\
254
query = (model_query(BarModel, session=session).
255
find(some_condition))
227
256
model_refs = query.all()
228
257
# Work with model_refs
229
258
query.soft_delete(synchronize_session=False)
247
278
from oslo.config import cfg
249
280
from sqlalchemy import exc as sqla_exc
250
import sqlalchemy.interfaces
251
281
from sqlalchemy.interfaces import PoolListener
252
282
import sqlalchemy.orm
253
283
from sqlalchemy.pool import NullPool, StaticPool
254
284
from sqlalchemy.sql.expression import literal_column
256
286
from ceilometer.openstack.common.db import exception
257
from ceilometer.openstack.common.gettextutils import _ # noqa
287
from ceilometer.openstack.common.gettextutils import _
258
288
from ceilometer.openstack.common import log as logging
259
289
from ceilometer.openstack.common import timeutils
289
321
deprecated_opts=[cfg.DeprecatedOpt('sql_idle_timeout',
290
322
group='DEFAULT'),
291
323
cfg.DeprecatedOpt('sql_idle_timeout',
325
cfg.DeprecatedOpt('idle_timeout',
293
327
help='timeout before idle sql connections are reaped'),
294
328
cfg.IntOpt('min_pool_size',
407
441
dbapi_con.execute('pragma foreign_keys=ON')
410
def get_session(autocommit=True, expire_on_commit=False,
411
sqlite_fk=False, slave_session=False):
444
def get_session(autocommit=True, expire_on_commit=False, sqlite_fk=False,
445
slave_session=False, mysql_traditional_mode=False):
412
446
"""Return a SQLAlchemy session."""
414
448
global _SLAVE_MAKER
418
452
maker = _SLAVE_MAKER
420
454
if maker is None:
421
engine = get_engine(sqlite_fk=sqlite_fk, slave_engine=slave_session)
455
engine = get_engine(sqlite_fk=sqlite_fk, slave_engine=slave_session,
456
mysql_traditional_mode=mysql_traditional_mode)
422
457
maker = get_maker(engine, autocommit, expire_on_commit)
424
459
if slave_session:
437
472
# 1 column - (IntegrityError) column c1 is not unique
438
473
# N columns - (IntegrityError) column c1, c2, ..., N are not unique
475
# sqlite since 3.7.16:
476
# 1 column - (IntegrityError) UNIQUE constraint failed: k1
478
# N columns - (IntegrityError) UNIQUE constraint failed: k1, k2
441
481
# 1 column - (IntegrityError) duplicate key value violates unique
442
482
# constraint "users_c1_key"
449
489
# N columns - (IntegrityError) (1062, "Duplicate entry 'values joined
450
490
# with -' for key 'name_of_our_constraint'")
451
491
_DUP_KEY_RE_DB = {
452
"sqlite": re.compile(r"^.*columns?([^)]+)(is|are)\s+not\s+unique$"),
453
"postgresql": re.compile(r"^.*duplicate\s+key.*\"([^\"]+)\"\s*\n.*$"),
454
"mysql": re.compile(r"^.*\(1062,.*'([^\']+)'\"\)$")
492
"sqlite": (re.compile(r"^.*columns?([^)]+)(is|are)\s+not\s+unique$"),
493
re.compile(r"^.*UNIQUE\s+constraint\s+failed:\s+(.+)$")),
494
"postgresql": (re.compile(r"^.*duplicate\s+key.*\"([^\"]+)\"\s*\n.*$"),),
495
"mysql": (re.compile(r"^.*\(1062,.*'([^\']+)'\"\)$"),)
481
522
# SQLAlchemy can differ when using unicode() and accessing .message.
482
523
# An audit across all three supported engines will be necessary to
483
524
# ensure there are no regressions.
484
m = _DUP_KEY_RE_DB[engine_name].match(integrity_error.message)
525
for pattern in _DUP_KEY_RE_DB[engine_name]:
526
match = pattern.match(integrity_error.message)
532
columns = match.group(1)
489
534
if engine_name == "sqlite":
490
535
columns = columns.strip().split(", ")
604
def _ping_listener(dbapi_conn, connection_rec, connection_proxy):
605
"""Ensures that MySQL connections checked out of the pool are alive.
650
def _ping_listener(engine, dbapi_conn, connection_rec, connection_proxy):
651
"""Ensures that MySQL and DB2 connections are alive.
608
654
http://groups.google.com/group/sqlalchemy/msg/a4ce563d802c929f
656
cursor = dbapi_conn.cursor()
611
dbapi_conn.cursor().execute('select 1')
612
except dbapi_conn.OperationalError as ex:
613
if ex.args[0] in (2006, 2013, 2014, 2045, 2055):
614
LOG.warn(_('Got mysql server has gone away: %s'), ex)
615
raise sqla_exc.DisconnectionError("Database server went away")
658
ping_sql = 'select 1'
659
if engine.name == 'ibm_db_sa':
660
# DB2 requires a table expression
661
ping_sql = 'select 1 from (values (1)) AS t1'
662
cursor.execute(ping_sql)
663
except Exception as ex:
664
if engine.dialect.is_disconnect(ex, dbapi_conn, cursor):
665
msg = _('Database server has gone away: %s') % ex
667
raise sqla_exc.DisconnectionError(msg)
672
def _set_mode_traditional(dbapi_con, connection_rec, connection_proxy):
673
"""Set engine mode to 'traditional'.
675
Required to prevent silent truncates at insert or update operations
676
under MySQL. By default MySQL truncates inserted string if it longer
677
than a declared field just with warning. That is fraught with data
680
dbapi_con.cursor().execute("SET SESSION sql_mode = TRADITIONAL;")
620
683
def _is_db_connection_error(args):
621
684
"""Return True if error in connecting to db."""
622
685
# NOTE(adam_g): This is currently MySQL specific and needs to be extended
632
def create_engine(sql_connection, sqlite_fk=False):
695
def create_engine(sql_connection, sqlite_fk=False,
696
mysql_traditional_mode=False):
633
697
"""Return a new SQLAlchemy engine."""
634
698
# NOTE(geekinutah): At this point we could be connecting to the normal
635
699
# db handle or the slave db handle. Things like
671
735
sqlalchemy.event.listen(engine, 'checkin', _thread_yield)
673
if 'mysql' in connection_dict.drivername:
674
sqlalchemy.event.listen(engine, 'checkout', _ping_listener)
737
if engine.name in ['mysql', 'ibm_db_sa']:
738
callback = functools.partial(_ping_listener, engine)
739
sqlalchemy.event.listen(engine, 'checkout', callback)
740
if mysql_traditional_mode:
741
sqlalchemy.event.listen(engine, 'checkout', _set_mode_traditional)
743
LOG.warning(_("This application has not enabled MySQL traditional"
744
" mode, which means silent data corruption may"
745
" occur. Please encourage the application"
746
" developers to enable this mode."))
675
747
elif 'sqlite' in connection_dict.drivername:
676
748
if not CONF.sqlite_synchronous:
677
749
sqlalchemy.event.listen(engine, 'connect',
added
removed
ceilometer/openstack/common/db/sqlalchemy/session.py
