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

1 files changed, 74 insertions, 1 deletions

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():