Add do_setinputsizes event for cx_Oracle

Added a new event currently used only by the cx_Oracle dialect,
:meth:`.DialectEvents.setiputsizes`.  The event passes a dictionary of
:class:`.BindParameter` objects to DBAPI-specific type objects that will be
passed, after conversion to parameter names, to the cx_Oracle
``cursor.setinputsizes()`` method.  This allows both visibility into the
setinputsizes process as well as the ability to alter the behavior of what
datatypes are passed to this method.

Change-Id: I43b97c8e3c840cad6f01edb274dc9cfed19cb5fc
Fixes: #4290

3 files changed, 132 insertions, 17 deletions

diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
index 2fbb2074c..2225000f7 100644
--- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py
+++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
@@ -94,7 +94,7 @@ Python 2:
   of plain string values.
 
 Sending String Values as Unicode or Non-Unicode
-------------------------------------------------
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
 
 As of SQLAlchemy 1.2.2, the cx_Oracle dialect unconditionally calls
 ``setinputsizes()`` for bound values that are passed as Python unicode objects.
@@ -124,6 +124,75 @@ with an explicit non-unicode type::
         func.trunc(func.sysdate(), literal('dd', String))
     )
 
+For full control over this ``setinputsizes()`` behavior, see the section
+:ref:`cx_oracle_setinputsizes`
+
+.. _cx_oracle_setinputsizes:
+
+Fine grained control over cx_Oracle data binding and performance with setinputsizes
+-----------------------------------------------------------------------------------
+
+The cx_Oracle DBAPI has a deep and fundamental reliance upon the usage of the
+DBAPI ``setinputsizes()`` call.   The purpose of this call is to establish the
+datatypes that are bound to a SQL statement for Python values being passed as
+parameters.  While virtually no other DBAPI assigns any use to the
+``setinputsizes()`` call, the cx_Oracle DBAPI relies upon it heavily in its
+interactions with the Oracle client interface, and in some scenarios it is  not
+possible for SQLAlchemy to know exactly how data should be bound, as some
+settings can cause profoundly different performance characteristics, while
+altering the type coercion behavior at the same time.
+
+Users of the cx_Oracle dialect are **strongly encouraged** to read through
+cx_Oracle's list of built-in datatype symbols at http://cx-oracle.readthedocs.io/en/latest/module.html#types.
+Note that in some cases, signficant performance degradation can occur when using
+these types vs. not, in particular when specifying ``cx_Oracle.CLOB``.
+
+On the SQLAlchemy side, the :meth:`.DialectEvents.do_setinputsizes` event
+can be used both for runtime visibliity (e.g. logging) of the setinputsizes
+step as well as to fully control how ``setinputsizes()`` is used on a per-statement
+basis.
+
+.. versionadded:: 1.2.9 Added :meth:`.DialectEvents.setinputsizes`
+
+
+Example 1 - logging all setinputsizes calls
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+The following example illustrates how to log the intermediary values from
+a SQLAlchemy perspective before they are converted to the raw ``setinputsizes()``
+parameter dictionary.  The keys of the dictionary are :class:`.BindParameter`
+objects which have a ``.key`` and a ``.type`` attribute::
+
+    from sqlalchemy import create_engine, event
+
+    engine = create_engine("oracle+cx_oracle://scott:tiger@host/xe")
+
+    @event.listens_for(engine, "do_setinputsizes")
+    def _log_setinputsizes(inputsizes, cursor, statement, parameters, context):
+        for bindparam, dbapitype in inputsizes.items():
+                log.info(
+                    "Bound parameter name: %s  SQLAlchemy type: %r  "
+                    "DBAPI object: %s",
+                    bindparam.key, bindparam.type, dbapitype)
+
+Example 2 - remove all bindings to CLOB
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+The ``CLOB`` datatype in cx_Oracle incurs a significant performance overhead,
+however is set by default for the ``Text`` type within the SQLAlchemy 1.2
+series.   This setting can be modified as follows::
+
+    from sqlalchemy import create_engine, event
+    from cx_Oracle import CLOB
+
+    engine = create_engine("oracle+cx_oracle://scott:tiger@host/xe")
+
+    @event.listens_for(engine, "do_setinputsizes")
+    def _remove_clob(inputsizes, cursor, statement, parameters, context):
+        for bindparam, dbapitype in list(inputsizes.items()):
+            if dbapitype is CLOB:
+                del inputsizes[bindparam]
+
 
 .. _cx_oracle_returning:
 
diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py
index 54fb25c16..915812a4f 100644
--- a/lib/sqlalchemy/engine/default.py
+++ b/lib/sqlalchemy/engine/default.py
@@ -1127,9 +1127,9 @@ class DefaultExecutionContext(interfaces.ExecutionContext):
         if not hasattr(self.compiled, 'bind_names'):
             return
 
-        key_to_dbapi_type = {}
+        inputsizes = {}
         for bindparam in self.compiled.bind_names:
-            key = self.compiled.bind_names[bindparam]
+
             dialect_impl = bindparam.type._unwrapped_dialect_impl(self.dialect)
             dialect_impl_cls = type(dialect_impl)
             dbtype = dialect_impl.get_dbapi_type(self.dialect.dbapi)
@@ -1140,28 +1140,36 @@ class DefaultExecutionContext(interfaces.ExecutionContext):
                 not include_types or dbtype in include_types or
                 dialect_impl_cls in include_types
             ):
-                key_to_dbapi_type[key] = dbtype
+                inputsizes[bindparam] = dbtype
+            else:
+                inputsizes[bindparam] = None
+
+        if self.dialect._has_events:
+            self.dialect.dispatch.do_setinputsizes(
+                inputsizes, self.cursor, self.statement, self.parameters, self
+            )
 
         if self.dialect.positional:
-            inputsizes = []
+            positional_inputsizes = []
             for key in self.compiled.positiontup:
-                if key in key_to_dbapi_type:
-                    dbtype = key_to_dbapi_type[key]
+                bindparam = self.compiled.binds[key]
+                dbtype = inputsizes.get(bindparam, None)
+                if dbtype is not None:
                     if key in self._expanded_parameters:
-                        inputsizes.extend(
+                        positional_inputsizes.extend(
                             [dbtype] * len(self._expanded_parameters[key]))
                     else:
-                        inputsizes.append(dbtype)
+                        positional_inputsizes.append(dbtype)
             try:
-                self.cursor.setinputsizes(*inputsizes)
+                self.cursor.setinputsizes(*positional_inputsizes)
             except BaseException as e:
                 self.root_connection._handle_dbapi_exception(
                     e, None, None, None, self)
         else:
-            inputsizes = {}
-            for key in self.compiled.bind_names.values():
-                if key in key_to_dbapi_type:
-                    dbtype = key_to_dbapi_type[key]
+            keyword_inputsizes = {}
+            for bindparam, key in self.compiled.bind_names.items():
+                dbtype = inputsizes.get(bindparam, None)
+                if dbtype is not None:
                     if translate:
                         # TODO: this part won't work w/ the
                         # expanded_parameters feature, e.g. for cx_oracle
@@ -1170,14 +1178,14 @@ class DefaultExecutionContext(interfaces.ExecutionContext):
                     if not self.dialect.supports_unicode_binds:
                         key = self.dialect._encoder(key)[0]
                     if key in self._expanded_parameters:
-                        inputsizes.update(
+                        keyword_inputsizes.update(
                             (expand_key, dbtype) for expand_key
                             in self._expanded_parameters[key]
                         )
                     else:
-                        inputsizes[key] = dbtype
+                        keyword_inputsizes[key] = dbtype
             try:
-                self.cursor.setinputsizes(**inputsizes)
+                self.cursor.setinputsizes(**keyword_inputsizes)
             except BaseException as e:
                 self.root_connection._handle_dbapi_exception(
                     e, None, None, None, self)
diff --git a/lib/sqlalchemy/events.py b/lib/sqlalchemy/events.py
index 2a84d0a25..3e97ea896 100644
--- a/lib/sqlalchemy/events.py
+++ b/lib/sqlalchemy/events.py
@@ -1234,3 +1234,41 @@ class DialectEvents(event.Events):
         place within the event handler.
 
         """
+
+    def do_setinputsizes(self,
+                         inputsizes, cursor, statement, parameters, context):
+        """Receive the setinputsizes dictionary for possible modification.
+
+        This event is emitted in the case where the dialect makes use of the
+        DBAPI ``cursor.setinputsizes()`` method which passes information about
+        parameter binding for a particular statement.   The given
+        ``inputsizes`` dictionary will contain :class:`.BindParameter` objects
+        as keys, linked to DBAPI-specific type objects as values; for
+        parameters that are not bound, they are added to the dictionary with
+        ``None`` as the value, which means the parameter will not be included
+        in the ultimate setinputsizes call.   The event may be used to inspect
+        and/or log the datatypes that are being bound, as well as to modify the
+        dictionary in place.  Parameters can be added, modified, or removed
+        from this dictionary.   Callers will typically want to inspect the
+        :attr:`.BindParameter.type` attribute of the given bind objects in
+        order to make decisions about the DBAPI object.
+
+        After the event, the ``inputsizes`` dictionary is converted into
+        an appropriate datastructure to be passed to ``cursor.setinputsizes``;
+        either a list for a positional bound parameter execution style,
+        or a dictionary of string parameter keys to DBAPI type objects for
+        a named bound parameter execution style.
+
+        Most dialects **do not use** this method at all; the only built-in
+        dialect which uses this hook is the cx_Oracle dialect.   The hook here
+        is made available so as to allow customization of how datatypes are set
+        up with the  cx_Oracle DBAPI.
+
+        .. versionadded:: 1.2.9
+
+        .. seealso::
+
+            :ref:`cx_oracle_setinputsizes`
+
+        """
+        pass