From eda851676d14956c03616e5b2cb856acdc8edbef Mon Sep 17 00:00:00 2001 From: Boris Pavlovic Date: Wed, 19 Dec 2012 14:21:01 +0400 Subject: Create and use subclass of sqlalchemy Query with soft_delete() method Create subclass of sqlalchemy.orm.query.Query that has soft_delete() method Change get_maker() method to create Session with our Query class The main idea of soft_delete() method is to get good performance with clean syntax: Let we have already `query` instance. Clean syntax example: If we want to make soft delete for each entry that correspond to query: we should write: query.soft_delete() instead of: query.update({ deleted:True, 'deleted_at': timeutils.utcnow(), 'updated_at': literal_column('updated_at')}) Performance examples: If we make: query.soft_delete() we will make only one request to soft delete corresponding entries. But if we make: for model_ref in query.all(): model_ref.delete(session=session) it will produce >= N + 1 queries. In almost all situation it is better to use query.soft_delete() than model.delete() method, because in second approach we should make 2 request (get model_ref and then soft delete it). In future patch model.delete() will be at least renamed to model.soft_delete(). Probably it should be removed at all, to avoid situation in code with N+1 requests for soft deletion. blueprint db-session-cleanup Change-Id: I6059982b83648133b6d8becf94ad8980b056c6ff --- nova/db/sqlalchemy/session.py | 75 ++++++++++++++++++++++++++++++++++++++++++- 1 file changed, 74 insertions(+), 1 deletion(-) diff --git a/nova/db/sqlalchemy/session.py b/nova/db/sqlalchemy/session.py index 8a8414662..e7eaeaf94 100644 --- a/nova/db/sqlalchemy/session.py +++ b/nova/db/sqlalchemy/session.py @@ -158,6 +158,67 @@ There are some things which it is best to avoid: However, this can not be done until the "deleted" columns are removed and proper UNIQUE constraints are added to the tables. + +Efficient use of soft deletes: + +* There are two possible ways to mark a record as deleted: + model.delete() and query.soft_delete(). + + model.delete() method works with single already fetched entry. + query.soft_delete() makes only one db request for all entries that correspond + to query. + +* In almost all cases you should use query.soft_delete(). Some examples: + + def soft_delete_bar(): + count = model_query(BarModel).find(some_condition).soft_delete() + if count == 0: + raise Exception("0 entries were soft deleted") + + def complex_soft_delete_with_synchronization_bar(session=None): + if session is None: + session = get_session() + with session.begin(subtransactions=True): + count = model_query(BarModel).\ + find(some_condition).\ + soft_delete(synchronize_session=True) + # Here synchronize_session is required, because we + # don't know what is going on in outer session. + if count == 0: + raise Exception("0 entries were soft deleted") + +* There is only one situation where model.delete is appropriate: when you fetch + a single record, work with it, and mark it as deleted in the same + transaction. + + def soft_delete_bar_model(): + session = get_session() + with session.begin(): + bar_ref = model_query(BarModel).find(some_condition).first() + # Work with bar_ref + bar_ref.delete(session=session) + + However, if you need to work with all entries that correspond to query and + then soft delete them you should use query.soft_delete() method: + + def soft_delete_multi_models(): + session = get_session() + with session.begin(): + query = model_query(BarModel, session=session).\ + find(some_condition) + model_refs = query.all() + # Work with model_refs + query.soft_delete(synchronize_session=False) + # synchronize_session=False should be set if there is no outer + # session and these entries are not used after this. + + When working with many rows, it is very important to use query.soft_delete, + which issues a single query. Using model.delete, as in the following example, + is very inefficient. + + for bar_ref in bar_refs: + bar_ref.delete(session=session) + # This will produce count(bar_refs) db requests. """ import re @@ -173,11 +234,13 @@ from sqlalchemy.exc import DisconnectionError, OperationalError, IntegrityError import sqlalchemy.interfaces import sqlalchemy.orm from sqlalchemy.pool import NullPool, StaticPool +from sqlalchemy.sql.expression import literal_column from nova.exception import DBDuplicateEntry from nova.exception import DBError from nova.openstack.common import cfg import nova.openstack.common.log as logging +from nova.openstack.common import timeutils sql_opts = [ @@ -475,11 +538,21 @@ def create_engine(sql_connection): return engine +class Query(sqlalchemy.orm.query.Query): + """Subclass of sqlalchemy.query with soft_delete() method""" + def soft_delete(self, synchronize_session='evaluate'): + return self.update({'deleted': True, + 'updated_at': literal_column('updated_at'), + 'deleted_at': timeutils.utcnow()}, + synchronize_session=synchronize_session) + + def get_maker(engine, autocommit=True, expire_on_commit=False): """Return a SQLAlchemy sessionmaker using the given engine.""" return sqlalchemy.orm.sessionmaker(bind=engine, autocommit=autocommit, - expire_on_commit=expire_on_commit) + expire_on_commit=expire_on_commit, + query_cls=Query) def patch_mysqldb_with_stacktrace_comments(): -- cgit