Debian Codegraph @

taskflow/persistence/backends/impl_sqlalchemy.py

raw
1 # Copyright (C) 2012 Yahoo! Inc. All Rights Reserved.
2 # Copyright (C) 2013 Rackspace Hosting All Rights Reserved.
3 #
4 # Licensed under the Apache License, Version 2.0 (the "License"); you may
5 # not use this file except in compliance with the License. You may obtain
6 # a copy of the License at
7 #
8 # http://www.apache.org/licenses/LICENSE-2.0
9 #
10 # Unless required by applicable law or agreed to in writing, software
11 # distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
12 # WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
13 # License for the specific language governing permissions and limitations
14 # under the License.
15
16 import copy
17 import functools
18 import threading
19 import time
20
21 from oslo_utils import strutils
22 import sqlalchemy as sa
23 from sqlalchemy import exc as sa_exc
24 from sqlalchemy import pool as sa_pool
25 from sqlalchemy import sql
26 import tenacity
27
28 from taskflow import exceptions as exc
29 from taskflow import logging
30 from taskflow.persistence.backends.sqlalchemy import migration
31 from taskflow.persistence.backends.sqlalchemy import tables
32 from taskflow.persistence import base
33 from taskflow.persistence import models
34 from taskflow.utils import eventlet_utils
35 from taskflow.utils import misc
36
37
38 LOG = logging.getLogger(__name__)
39
40 # NOTE(harlowja): This is all very similar to what oslo-incubator uses but is
41 # not based on using oslo.cfg and its global configuration (which should not be
42 # used in libraries such as taskflow).
43 #
44 # TODO(harlowja): once oslo.db appears we should be able to use that instead
45 # since it's not supposed to have any usage of oslo.cfg in it when it
46 # materializes as a library.
47
48 # See: http://dev.mysql.com/doc/refman/5.0/en/error-messages-client.html
49 MY_SQL_CONN_ERRORS = (
50 # Lost connection to MySQL server at '%s', system error: %d
51 '2006',
52 # Can't connect to MySQL server on '%s' (%d)
53 '2003',
54 # Can't connect to local MySQL server through socket '%s' (%d)
55 '2002',
56 )
57 MY_SQL_GONE_WAY_AWAY_ERRORS = (
58 # Lost connection to MySQL server at '%s', system error: %d
59 '2006',
60 # Lost connection to MySQL server during query
61 '2013',
62 # Commands out of sync; you can't run this command now
63 '2014',
64 # Can't open shared memory; no answer from server (%lu)
65 '2045',
66 # Lost connection to MySQL server at '%s', system error: %d
67 '2055',
68 )
69
70 # See: http://www.postgresql.org/docs/9.1/static/errcodes-appendix.html
71 POSTGRES_CONN_ERRORS = (
72 # connection_exception
73 '08000',
74 # connection_does_not_exist
75 '08003',
76 # connection_failure
77 '08006',
78 # sqlclient_unable_to_establish_sqlconnection
79 '08001',
80 # sqlserver_rejected_establishment_of_sqlconnection
81 '08004',
82 # Just couldn't connect (postgres errors are pretty weird)
83 'could not connect to server',
84 )
85 POSTGRES_GONE_WAY_AWAY_ERRORS = (
86 # Server terminated while in progress (postgres errors are pretty weird).
87 'server closed the connection unexpectedly',
88 'terminating connection due to administrator command',
89 )
90
91 # These connection urls mean sqlite is being used as an in-memory DB.
92 SQLITE_IN_MEMORY = ('sqlite://', 'sqlite:///', 'sqlite:///:memory:')
93
94 # Transacation isolation levels that will be automatically applied, we prefer
95 # strong read committed isolation levels to avoid merging and using dirty
96 # data...
97 #
98 # See: http://en.wikipedia.org/wiki/Isolation_(database_systems)
99 DEFAULT_TXN_ISOLATION_LEVELS = {
100 'mysql': 'READ COMMITTED',
101 'postgresql': 'READ COMMITTED',
102 'postgres': 'READ COMMITTED',
103 }
104
105
106 def _log_statements(log_level, conn, cursor, statement, parameters, *args):
107 if LOG.isEnabledFor(log_level):
108 LOG.log(log_level, "Running statement '%s' with parameters %s",
109 statement, parameters)
110
111
112 def _in_any(reason, err_haystack):
113 """Checks if any elements of the haystack are in the given reason."""
114 for err in err_haystack:
115 if reason.find(str(err)) != -1:
116 return True
117 return False
118
119
120 def _is_db_connection_error(reason):
121 return _in_any(reason, list(MY_SQL_CONN_ERRORS + POSTGRES_CONN_ERRORS))
122
123
124 def _as_bool(value):
125 if isinstance(value, bool):
126 return value
127 # This is different than strutils, but imho is an acceptable difference.
128 if value is None:
129 return False
130 # NOTE(harlowja): prefer strictness to avoid users getting accustomed
131 # to passing bad values in and this *just working* (which imho is a bad
132 # habit to encourage).
133 return strutils.bool_from_string(value, strict=True)
134
135
136 def _thread_yield(dbapi_con, con_record):
137 """Ensure other greenthreads get a chance to be executed.
138
139 If we use eventlet.monkey_patch(), eventlet.greenthread.sleep(0) will
140 execute instead of time.sleep(0).
141
142 Force a context switch. With common database backends (eg MySQLdb and
143 sqlite), there is no implicit yield caused by network I/O since they are
144 implemented by C libraries that eventlet cannot monkey patch.
145 """
146 time.sleep(0)
147
148
149 def _set_sql_mode(sql_mode, dbapi_con, connection_rec):
150 """Set the sql_mode session variable.
151
152 MySQL supports several server modes. The default is None, but sessions
153 may choose to enable server modes like TRADITIONAL, ANSI,
154 several STRICT_* modes and others.
155
156 Note: passing in '' (empty string) for sql_mode clears
157 the SQL mode for the session, overriding a potentially set
158 server default.
159 """
160 cursor = dbapi_con.cursor()
161 cursor.execute("SET SESSION sql_mode = %s", [sql_mode])
162
163
164 def _ping_listener(dbapi_conn, connection_rec, connection_proxy):
165 """Ensures that MySQL connections checked out of the pool are alive.
166
167 Modified + borrowed from: http://bit.ly/14BYaW6.
168 """
169 try:
170 dbapi_conn.cursor().execute('select 1')
171 except dbapi_conn.OperationalError as ex:
172 if _in_any(str(ex.args[0]), MY_SQL_GONE_WAY_AWAY_ERRORS):
173 LOG.warning('Got mysql server has gone away', exc_info=True)
174 raise sa_exc.DisconnectionError("Database server went away")
175 elif _in_any(str(ex.args[0]), POSTGRES_GONE_WAY_AWAY_ERRORS):
176 LOG.warning('Got postgres server has gone away', exc_info=True)
177 raise sa_exc.DisconnectionError("Database server went away")
178 else:
179 raise
180
181
182 class _Alchemist:
183 """Internal <-> external row <-> objects + other helper functions.
184
185 NOTE(harlowja): for internal usage only.
186 """
187 def __init__(self, tables):
188 self._tables = tables
189
190 @staticmethod
191 def convert_flow_detail(row):
192 return models.FlowDetail.from_dict(dict(row.items()))
193
194 @staticmethod
195 def convert_book(row):
196 return models.LogBook.from_dict(dict(row.items()))
197
198 @staticmethod
199 def convert_atom_detail(row):
200 row = dict(row.items())
201 atom_cls = models.atom_detail_class(row.pop('atom_type'))
202 return atom_cls.from_dict(row)
203
204 def atom_query_iter(self, conn, parent_uuid):
205 q = (sql.select(self._tables.atomdetails).
206 where(self._tables.atomdetails.c.parent_uuid == parent_uuid))
207 for row in conn.execute(q):
208 row = row._mapping
209 yield self.convert_atom_detail(row)
210
211 def flow_query_iter(self, conn, parent_uuid):
212 q = (sql.select(self._tables.flowdetails).
213 where(self._tables.flowdetails.c.parent_uuid == parent_uuid))
214 for row in conn.execute(q):
215 row = row._mapping
216 yield self.convert_flow_detail(row)
217
218 def populate_book(self, conn, book):
219 for fd in self.flow_query_iter(conn, book.uuid):
220 book.add(fd)
221 self.populate_flow_detail(conn, fd)
222
223 def populate_flow_detail(self, conn, fd):
224 for ad in self.atom_query_iter(conn, fd.uuid):
225 fd.add(ad)
226
227
228 class SQLAlchemyBackend(base.Backend):
229 """A sqlalchemy backend.
230
231 Example configuration::
232
233 conf = {
234 "connection": "sqlite:////tmp/test.db",
235 }
236 """
237 def __init__(self, conf, engine=None):
238 super().__init__(conf)
239 if engine is not None:
240 self._engine = engine
241 self._owns_engine = False
242 else:
243 self._engine = self._create_engine(self._conf)
244 self._owns_engine = True
245 self._validated = False
246 self._upgrade_lock = threading.Lock()
247 try:
248 self._max_retries = misc.as_int(self._conf.get('max_retries'))
249 except TypeError:
250 self._max_retries = 0
251
252 @staticmethod
253 def _create_engine(conf):
254 # NOTE(harlowja): copy the internal one so that we don't modify it via
255 # all the popping that will happen below.
256 conf = copy.deepcopy(conf)
257 engine_args = {
258 'echo': _as_bool(conf.pop('echo', False)),
259 'pool_recycle': 3600,
260 }
261 if 'idle_timeout' in conf:
262 idle_timeout = misc.as_int(conf.pop('idle_timeout'))
263 engine_args['pool_recycle'] = idle_timeout
264 sql_connection = conf.pop('connection')
265 e_url = sa.engine.url.make_url(sql_connection)
266 if 'sqlite' in e_url.drivername:
267 engine_args["poolclass"] = sa_pool.NullPool
268
269 # Adjustments for in-memory sqlite usage.
270 if sql_connection.lower().strip() in SQLITE_IN_MEMORY:
271 engine_args["poolclass"] = sa_pool.StaticPool
272 engine_args["connect_args"] = {'check_same_thread': False}
273 else:
274 for (k, lookup_key) in [('pool_size', 'max_pool_size'),
275 ('max_overflow', 'max_overflow'),
276 ('pool_timeout', 'pool_timeout')]:
277 if lookup_key in conf:
278 engine_args[k] = misc.as_int(conf.pop(lookup_key))
279 if 'isolation_level' not in conf:
280 # Check driver name exact matches first, then try driver name
281 # partial matches...
282 txn_isolation_levels = conf.pop('isolation_levels',
283 DEFAULT_TXN_ISOLATION_LEVELS)
284 level_applied = False
285 for (driver, level) in txn_isolation_levels.items():
286 if driver == e_url.drivername:
287 engine_args['isolation_level'] = level
288 level_applied = True
289 break
290 if not level_applied:
291 for (driver, level) in txn_isolation_levels.items():
292 if e_url.drivername.find(driver) != -1:
293 engine_args['isolation_level'] = level
294 break
295 else:
296 engine_args['isolation_level'] = conf.pop('isolation_level')
297 # If the configuration dict specifies any additional engine args
298 # or engine arg overrides make sure we merge them in.
299 engine_args.update(conf.pop('engine_args', {}))
300 engine = sa.create_engine(sql_connection, **engine_args)
301 log_statements = conf.pop('log_statements', False)
302 if _as_bool(log_statements):
303 log_statements_level = conf.pop("log_statements_level",
304 logging.TRACE)
305 sa.event.listen(engine, "before_cursor_execute",
306 functools.partial(_log_statements,
307 log_statements_level))
308 checkin_yield = conf.pop('checkin_yield',
309 eventlet_utils.EVENTLET_AVAILABLE)
310 if _as_bool(checkin_yield):
311 sa.event.listen(engine, 'checkin', _thread_yield)
312 if 'mysql' in e_url.drivername:
313 if _as_bool(conf.pop('checkout_ping', True)):
314 sa.event.listen(engine, 'checkout', _ping_listener)
315 mode = None
316 if 'mysql_sql_mode' in conf:
317 mode = conf.pop('mysql_sql_mode')
318 if mode is not None:
319 sa.event.listen(engine, 'connect',
320 functools.partial(_set_sql_mode, mode))
321 return engine
322
323 @property
324 def engine(self):
325 return self._engine
326
327 def get_connection(self):
328 conn = Connection(self, upgrade_lock=self._upgrade_lock)
329 if not self._validated:
330 conn.validate(max_retries=self._max_retries)
331 self._validated = True
332 return conn
333
334 def close(self):
335 # NOTE(harlowja): Only dispose of the engine if we actually own the
336 # engine in the first place. If the user passed in their own engine
337 # we should not be disposing it on their behalf...
338 if self._owns_engine:
339 self._engine.dispose()
340 self._validated = False
341
342
343 class Connection(base.Connection):
344 def __init__(self, backend, upgrade_lock):
345 self._backend = backend
346 self._upgrade_lock = upgrade_lock
347 self._engine = backend.engine
348 self._metadata = sa.MetaData()
349 self._tables = tables.fetch(self._metadata)
350 self._converter = _Alchemist(self._tables)
351
352 @property
353 def backend(self):
354 return self._backend
355
356 def validate(self, max_retries=0):
357 """Performs basic **connection** validation of a sqlalchemy engine."""
358
359 def _retry_on_exception(exc):
360 LOG.warning("Engine connection (validate) failed due to '%s'", exc)
361 if isinstance(exc, sa_exc.OperationalError) and \
362 _is_db_connection_error(str(exc.args[0])):
363 # We may be able to fix this by retrying...
364 return True
365 if isinstance(exc, (sa_exc.TimeoutError,
366 sa_exc.ResourceClosedError,
367 sa_exc.DisconnectionError)):
368 # We may be able to fix this by retrying...
369 return True
370 # Other failures we likely can't fix by retrying...
371 return False
372
373 @tenacity.retry(
374 stop=tenacity.stop_after_attempt(max(0, int(max_retries))),
375 wait=tenacity.wait_exponential(),
376 reraise=True,
377 retry=tenacity.retry_if_exception(_retry_on_exception)
378 )
379 def _try_connect(engine):
380 # See if we can make a connection happen.
381 #
382 # NOTE(harlowja): note that even though we are connecting
383 # once it does not mean that we will be able to connect in
384 # the future, so this is more of a sanity test and is not
385 # complete connection insurance.
386 with engine.connect():
387 pass
388
389 _try_connect(self._engine)
390
391 def upgrade(self):
392 try:
393 with self._upgrade_lock:
394 with self._engine.connect() as conn:
395 # NOTE(imelnikov): Alembic does not support SQLite,
396 # and we don't recommend to use SQLite in production
397 # deployments, so migrations are rarely needed
398 # for SQLite. So we don't bother about working around
399 # SQLite limitations, and create the database directly
400 # from the tables when it is in use...
401 if 'sqlite' in self._engine.url.drivername:
402 with conn.begin():
403 self._metadata.create_all(bind=conn)
404 else:
405 migration.db_sync(conn)
406 except sa_exc.SQLAlchemyError:
407 exc.raise_with_cause(exc.StorageFailure,
408 "Failed upgrading database version")
409
410 def clear_all(self):
411 try:
412 logbooks = self._tables.logbooks
413 with self._engine.begin() as conn:
414 conn.execute(logbooks.delete())
415 except sa_exc.DBAPIError:
416 exc.raise_with_cause(exc.StorageFailure,
417 "Failed clearing all entries")
418
419 def update_atom_details(self, atom_detail):
420 try:
421 atomdetails = self._tables.atomdetails
422 with self._engine.begin() as conn:
423 q = (sql.select(atomdetails).
424 where(atomdetails.c.uuid == atom_detail.uuid))
425 row = conn.execute(q).first()
426 if not row:
427 raise exc.NotFound("No atom details found with uuid"
428 " '%s'" % atom_detail.uuid)
429 row = row._mapping
430 e_ad = self._converter.convert_atom_detail(row)
431 self._update_atom_details(conn, atom_detail, e_ad)
432 return e_ad
433 except sa_exc.SQLAlchemyError:
434 exc.raise_with_cause(exc.StorageFailure,
435 "Failed updating atom details"
436 " with uuid '%s'" % atom_detail.uuid)
437
438 def _insert_flow_details(self, conn, fd, parent_uuid):
439 value = fd.to_dict()
440 value['parent_uuid'] = parent_uuid
441 conn.execute(sql.insert(self._tables.flowdetails).values(**value))
442 for ad in fd:
443 self._insert_atom_details(conn, ad, fd.uuid)
444
445 def _insert_atom_details(self, conn, ad, parent_uuid):
446 value = ad.to_dict()
447 value['parent_uuid'] = parent_uuid
448 value['atom_type'] = models.atom_detail_type(ad)
449 conn.execute(sql.insert(self._tables.atomdetails).values(**value))
450
451 def _update_atom_details(self, conn, ad, e_ad):
452 e_ad.merge(ad)
453 conn.execute(sql.update(self._tables.atomdetails)
454 .where(self._tables.atomdetails.c.uuid == e_ad.uuid)
455 .values(e_ad.to_dict()))
456
457 def _update_flow_details(self, conn, fd, e_fd):
458 e_fd.merge(fd)
459 conn.execute(sql.update(self._tables.flowdetails)
460 .where(self._tables.flowdetails.c.uuid == e_fd.uuid)
461 .values(e_fd.to_dict()))
462 for ad in fd:
463 e_ad = e_fd.find(ad.uuid)
464 if e_ad is None:
465 e_fd.add(ad)
466 self._insert_atom_details(conn, ad, fd.uuid)
467 else:
468 self._update_atom_details(conn, ad, e_ad)
469
470 def update_flow_details(self, flow_detail):
471 try:
472 flowdetails = self._tables.flowdetails
473 with self._engine.begin() as conn:
474 q = (sql.select(flowdetails).
475 where(flowdetails.c.uuid == flow_detail.uuid))
476 row = conn.execute(q).first()
477 if not row:
478 raise exc.NotFound("No flow details found with"
479 " uuid '%s'" % flow_detail.uuid)
480 row = row._mapping
481 e_fd = self._converter.convert_flow_detail(row)
482 self._converter.populate_flow_detail(conn, e_fd)
483 self._update_flow_details(conn, flow_detail, e_fd)
484 return e_fd
485 except sa_exc.SQLAlchemyError:
486 exc.raise_with_cause(exc.StorageFailure,
487 "Failed updating flow details with"
488 " uuid '%s'" % flow_detail.uuid)
489
490 def destroy_logbook(self, book_uuid):
491 try:
492 logbooks = self._tables.logbooks
493 with self._engine.begin() as conn:
494 q = logbooks.delete().where(logbooks.c.uuid == book_uuid)
495 r = conn.execute(q)
496 if r.rowcount == 0:
497 raise exc.NotFound("No logbook found with"
498 " uuid '%s'" % book_uuid)
499 except sa_exc.DBAPIError:
500 exc.raise_with_cause(exc.StorageFailure,
501 "Failed destroying logbook '%s'" % book_uuid)
502
503 def save_logbook(self, book):
504 try:
505 logbooks = self._tables.logbooks
506 with self._engine.begin() as conn:
507 q = (sql.select(logbooks).
508 where(logbooks.c.uuid == book.uuid))
509 row = conn.execute(q).first()
510 if row:
511 row = row._mapping
512 e_lb = self._converter.convert_book(row)
513 self._converter.populate_book(conn, e_lb)
514 e_lb.merge(book)
515 conn.execute(sql.update(logbooks)
516 .where(logbooks.c.uuid == e_lb.uuid)
517 .values(e_lb.to_dict()))
518 for fd in book:
519 e_fd = e_lb.find(fd.uuid)
520 if e_fd is None:
521 e_lb.add(fd)
522 self._insert_flow_details(conn, fd, e_lb.uuid)
523 else:
524 self._update_flow_details(conn, fd, e_fd)
525 return e_lb
526 else:
527 conn.execute(sql.insert(logbooks).values(**book.to_dict()))
528 for fd in book:
529 self._insert_flow_details(conn, fd, book.uuid)
530 return book
531 except sa_exc.DBAPIError:
532 exc.raise_with_cause(
533 exc.StorageFailure,
534 "Failed saving logbook '%s'" % book.uuid)
535
536 def get_logbook(self, book_uuid, lazy=False):
537 try:
538 logbooks = self._tables.logbooks
539 with self._engine.connect() as conn:
540 q = (sql.select(logbooks).
541 where(logbooks.c.uuid == book_uuid))
542 row = conn.execute(q).first()
543 if not row:
544 raise exc.NotFound("No logbook found with"
545 " uuid '%s'" % book_uuid)
546 row = row._mapping
547 book = self._converter.convert_book(row)
548 if not lazy:
549 self._converter.populate_book(conn, book)
550 return book
551 except sa_exc.DBAPIError:
552 exc.raise_with_cause(exc.StorageFailure,
553 "Failed getting logbook '%s'" % book_uuid)
554
555 def get_logbooks(self, lazy=False):
556 gathered = []
557 try:
558 with self._engine.connect() as conn:
559 q = sql.select(self._tables.logbooks)
560 for row in conn.execute(q):
561 row = row._mapping
562 book = self._converter.convert_book(row)
563 if not lazy:
564 self._converter.populate_book(conn, book)
565 gathered.append(book)
566 except sa_exc.DBAPIError:
567 exc.raise_with_cause(exc.StorageFailure,
568 "Failed getting logbooks")
569 for book in gathered:
570 yield book
571
572 def get_flows_for_book(self, book_uuid, lazy=False):
573 gathered = []
574 try:
575 with self._engine.connect() as conn:
576 for fd in self._converter.flow_query_iter(conn, book_uuid):
577 if not lazy:
578 self._converter.populate_flow_detail(conn, fd)
579 gathered.append(fd)
580 except sa_exc.DBAPIError:
581 exc.raise_with_cause(exc.StorageFailure,
582 "Failed getting flow details in"
583 " logbook '%s'" % book_uuid)
584 yield from gathered
585
586 def get_flow_details(self, fd_uuid, lazy=False):
587 try:
588 flowdetails = self._tables.flowdetails
589 with self._engine.begin() as conn:
590 q = (sql.select(flowdetails).
591 where(flowdetails.c.uuid == fd_uuid))
592 row = conn.execute(q).first()
593 if not row:
594 raise exc.NotFound("No flow details found with uuid"
595 " '%s'" % fd_uuid)
596 row = row._mapping
597 fd = self._converter.convert_flow_detail(row)
598 if not lazy:
599 self._converter.populate_flow_detail(conn, fd)
600 return fd
601 except sa_exc.SQLAlchemyError:
602 exc.raise_with_cause(exc.StorageFailure,
603 "Failed getting flow details with"
604 " uuid '%s'" % fd_uuid)
605
606 def get_atom_details(self, ad_uuid):
607 try:
608 atomdetails = self._tables.atomdetails
609 with self._engine.begin() as conn:
610 q = (sql.select(atomdetails).
611 where(atomdetails.c.uuid == ad_uuid))
612 row = conn.execute(q).first()
613 if not row:
614 raise exc.NotFound("No atom details found with uuid"
615 " '%s'" % ad_uuid)
616 row = row._mapping
617 return self._converter.convert_atom_detail(row)
618 except sa_exc.SQLAlchemyError:
619 exc.raise_with_cause(exc.StorageFailure,
620 "Failed getting atom details with"
621 " uuid '%s'" % ad_uuid)
622
623 def get_atoms_for_flow(self, fd_uuid):
624 gathered = []
625 try:
626 with self._engine.connect() as conn:
627 for ad in self._converter.atom_query_iter(conn, fd_uuid):
628 gathered.append(ad)
629 except sa_exc.DBAPIError:
630 exc.raise_with_cause(exc.StorageFailure,
631 "Failed getting atom details in flow"
632 " detail '%s'" % fd_uuid)
633 yield from gathered
634
635 def close(self):
636 pass