- changelog for pullreq github:74

- various improvemnts to oracle docs, rewrite section on unicode, more linking,
enhance section on resolve_synonyms

3 files changed, 104 insertions, 41 deletions

diff --git a/doc/build/changelog/changelog_09.rst b/doc/build/changelog/changelog_09.rst
index 12b7c8583..0e5b43b2d 100644
--- a/doc/build/changelog/changelog_09.rst
+++ b/doc/build/changelog/changelog_09.rst
@@ -15,6 +15,19 @@
     :version: 0.9.4
 
     .. change::
+        :tags: enhancement, oracle
+        :tickets: 2911
+        :pullreq: github:74
+
+        Added a new engine option ``coerce_to_unicode=True`` to the
+        cx_Oracle dialect, which restores the cx_Oracle outputtypehandler
+        approach to Python unicode conversion under Python 2, which was
+        removed in 0.9.2 as a result of :ticket:`2911`.  Some use cases would
+        prefer that unicode coersion is unconditional for all string values,
+        despite performance concerns.  Pull request courtesy
+        Christoph Zwerschke.
+
+    .. change::
         :tags: bug, pool
 
         Fixed small issue in :class:`.SingletonThreadPool` where the current
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index b3434c221..d883d74f7 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -85,6 +85,8 @@ http://www.sqlalchemy.org/trac/wiki/UsageRecipes/WindowFunctionsByDefault
 which installs a select compiler that overrides the generation of limit/offset with
 a window function.
 
+.. _oracle_returning:
+
 RETURNING Support
 -----------------
 
@@ -155,9 +157,24 @@ Synonym/DBLINK Reflection
 
 When using reflection with Table objects, the dialect can optionally search for tables
 indicated by synonyms, either in local or remote schemas or accessed over DBLINK,
-by passing the flag oracle_resolve_synonyms=True as a
-keyword argument to the Table construct.   If synonyms are not in use
-this flag should be left off.
+by passing the flag ``oracle_resolve_synonyms=True`` as a
+keyword argument to the :class:`.Table` construct::
+
+    some_table = Table('some_table', autoload=True,
+                                autoload_with=some_engine,
+                                oracle_resolve_synonyms=True)
+
+When this flag is set, the given name (such as ``some_table`` above) will
+be searched not just in the ``ALL_TABLES`` view, but also within the
+``ALL_SYNONYMS`` view to see if this name is actually a synonym to another name.
+If the synonym is located and refers to a DBLINK, the oracle dialect knows
+how to locate the table's information using DBLINK syntax (e.g. ``@dblink``).
+
+``oracle_resolve_synonyms`` is accepted wherever reflection arguments are
+accepted, including methods such as :meth:`.MetaData.reflect` and
+:meth:`.Inspector.get_columns`.
+
+If synonyms are not in use, this flag should be left disabled.
 
 """
 
diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
index 0e9d7175e..e875c4667 100644
--- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py
+++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
@@ -23,20 +23,26 @@ directly as a TNS name.
 Additional arguments which may be specified either as query string arguments
 on the URL, or as keyword arguments to :func:`.create_engine()` are:
 
-* allow_twophase - enable two-phase transactions.  Defaults to ``True``.
+* ``allow_twophase`` - enable two-phase transactions.  Defaults to ``True``.
 
-* arraysize - set the cx_oracle.arraysize value on cursors, in SQLAlchemy
-  it defaults to 50.  See the section on "LOB Objects" below.
+* ``arraysize`` - set the cx_oracle.arraysize value on cursors, defaulted
+  to 50.  This setting is significant with cx_Oracle as the contents of LOB
+  objects are only readable within a "live" row (e.g. within a batch of
+  50 rows).
 
-* auto_convert_lobs - defaults to True, see the section on LOB objects.
+* ``auto_convert_lobs`` - defaults to True; See :ref:`cx_oracle_lob`.
 
-* auto_setinputsizes - the cx_oracle.setinputsizes() call is issued for
+* ``auto_setinputsizes`` - the cx_oracle.setinputsizes() call is issued for
   all bind parameters.  This is required for LOB datatypes but can be
   disabled to reduce overhead.  Defaults to ``True``.  Specific types
   can be excluded from this process using the ``exclude_setinputsizes``
   parameter.
 
-* exclude_setinputsizes - a tuple or list of string DBAPI type names to
+* ``coerce_to_unicode`` - see :ref:`cx_oracle_unicode` for detail.
+
+* ``coerce_to_decimal`` - see :ref:`cx_oracle_numeric` for detail.
+
+* ``exclude_setinputsizes`` - a tuple or list of string DBAPI type names to
   be excluded from the "auto setinputsizes" feature.  The type names here
   must match DBAPI types that are found in the "cx_Oracle" module namespace,
   such as cx_Oracle.UNICODE, cx_Oracle.NCLOB, etc.   Defaults to
@@ -45,14 +51,16 @@ on the URL, or as keyword arguments to :func:`.create_engine()` are:
   .. versionadded:: 0.8 specific DBAPI types can be excluded from the
      auto_setinputsizes feature via the exclude_setinputsizes attribute.
 
-* mode - This is given the string value of SYSDBA or SYSOPER, or alternatively
+* ``mode`` - This is given the string value of SYSDBA or SYSOPER, or alternatively
   an integer value.  This value is only available as a URL query string
   argument.
 
-* threaded - enable multithreaded access to cx_oracle connections.  Defaults
+* ``threaded`` - enable multithreaded access to cx_oracle connections.  Defaults
   to ``True``.  Note that this is the opposite default of the cx_Oracle DBAPI
   itself.
 
+.. _cx_oracle_unicode:
+
 Unicode
 -------
 
@@ -65,27 +73,47 @@ unicode those column values that are of type ``NVARCHAR`` or ``NCLOB``.  For
 column values that are of type ``VARCHAR`` or other non-unicode string types,
 it will return values as Python strings (e.g. bytestrings).
 
-While the cx_Oracle DBAPI has the ability to return all string types as
-unicode by using outputtypehandlers, SQLAlchemy has observed that usage
-of a unicode-converting outputtypehandler in Python 2 (not Python 3) incurs
-significant performance overhead for all statements that deliver string
-results, whether or not values contain non-ASCII characters.  For this reason,
-SQLAlchemy as of 0.9.2 does not use cx_Oracle's outputtypehandlers for unicode
-conversion by default. If you want to use this feature anyway, you can enable
-it by passing the flag ``coerce_to_unicode=True`` to :func:`.create_engine`::
-
-    engine = create_engine("oracle+cx_oracle://dsn",
-                        coerce_to_unicode=True)
-
-Keeping in mind that any NVARCHAR or NCLOB type is returned as Python unicode
-unconditionally, in order for VARCHAR values to be returned as Python unicode
-objects in Python 2, SQLAlchemy's own unicode facilities should be used.
-This means ensuring that the :class:`.Unicode` or :class:`.String` type with
-:paramref:`.String.convert_unicode` should be linked to any result columns
-where non-default unicode coersion is desired.   For Core and ORM
-select constructs, if those constructs are linked to :class:`.Column` objects
-of the appropriate type, this conversion will be automatic.  For a textual
-SQL statement, use :func:`.text`::
+The cx_Oracle SQLAlchemy dialect presents two different options for the use case of
+returning ``VARCHAR`` column values as Python unicode objects under Python 2:
+
+* the cx_Oracle DBAPI has the ability to coerce all string results to Python
+  unicode objects unconditionally using output type handlers.  This has
+  the advantage that the unicode conversion is global to all statements
+  at the cx_Oracle driver level, meaning it works with raw textual SQL
+  statements that have no typing information associated.  However, this system
+  has been observed to incur signfiicant performance overhead, not only because
+  it takes effect for all string values unconditionally, but also because cx_Oracle under
+  Python 2 seems to use a pure-Python function call in order to do the
+  decode operation, which under cPython can orders of magnitude slower
+  than doing it using C functions alone.
+
+* SQLAlchemy has unicode-decoding services built in, and when using SQLAlchemy's
+  C extensions, these functions do not use any Python function calls and
+  are very fast.  The disadvantage to this approach is that the unicode
+  conversion only takes effect for statements where the :class:`.Unicode` type
+  or :class:`.String` type with ``convert_unicode=True`` is explicitly
+  associated with the result column.  This is the case for any ORM or Core
+  query or SQL expression as well as for a :func:`.text` construct that specifies
+  output column types, so in the vast majority of cases this is not an issue.
+  However, when sending a completely raw string to :meth:`.Connection.execute`,
+  this typing information isn't present, unless the string is handled
+  within a :func:`.text` construct that adds typing information.
+
+As of version 0.9.2 of SQLAlchemy, the default approach is to use SQLAlchemy's
+typing system.  This keeps cx_Oracle's expensive Python 2 approach
+disabled unless the user explicitly wants it.  Under Python 3, SQLAlchemy detects
+that cx_Oracle is returning unicode objects natively and cx_Oracle's system
+is used.
+
+To re-enable cx_Oracle's output type handler under Python 2, the
+``coerce_to_unicode=True`` flag (new in 0.9.4) can be passed to
+:func:`.create_engine`::
+
+    engine = create_engine("oracle+cx_oracle://dsn", coerce_to_unicode=True)
+
+Alternatively, to run a pure string SQL statement and get ``VARCHAR`` results
+as Python unicode under Python 2 without using cx_Oracle's native handlers,
+the :func:`.text` feature can be used::
 
     from sqlalchemy import text, Unicode
     result = conn.execute(text("select username from user").columns(username=Unicode))
@@ -95,29 +123,34 @@ SQL statement, use :func:`.text`::
    performance bottleneck.  SQLAlchemy's own unicode facilities are used
    instead.
 
-.. versionadded:: 0.9.4
-    Add the ``coerce_to_unicode`` flag.
+.. versionadded:: 0.9.4 Added the ``coerce_to_unicode`` flag, to re-enable
+   cx_Oracle's outputtypehandler and revert to pre-0.9.2 behavior.
 
 .. _cx_oracle_returning:
 
 RETURNING Support
 -----------------
 
-cx_oracle supports a limited subset of Oracle's already limited RETURNING support.
+The cx_oracle DBAPI supports a limited subset of Oracle's already limited RETURNING support.
 Typically, results can only be guaranteed for at most one column being returned;
 this is the typical case when SQLAlchemy uses RETURNING to get just the value of a
 primary-key-associated sequence value.    Additional column expressions will
 cause problems in a non-determinative way, due to cx_oracle's lack of support for
 the OCI_DATA_AT_EXEC API which is required for more complex RETURNING scenarios.
 
+For this reason, stability may be enhanced by disabling RETURNING support completely;
+SQLAlchemy otherwise will use RETURNING to fetch newly sequence-generated
+primary keys.  As illustrated in :ref:`oracle_returning`::
+
+    engine = create_engine("oracle://scott:tiger@dsn", implicit_returning=False)
+
 .. seealso::
 
     http://docs.oracle.com/cd/B10501_01/appdev.920/a96584/oci05bnd.htm#420693 - OCI documentation for RETURNING
 
     http://sourceforge.net/mailarchive/message.php?msg_id=31338136 - cx_oracle developer commentary
 
-
-
+.. _cx_oracle_lob:
 
 LOB Objects
 -----------
@@ -168,6 +201,7 @@ the application can make one of several choices:
     Support for cx_oracle prepared transactions has been implemented
     and tested.
 
+.. _cx_oracle_numeric:
 
 Precision Numerics
 ------------------
@@ -190,8 +224,7 @@ If precision numerics aren't required, the decimal handling
 can be disabled by passing the flag ``coerce_to_decimal=False``
 to :func:`.create_engine`::
 
-    engine = create_engine("oracle+cx_oracle://dsn",
-                        coerce_to_decimal=False)
+    engine = create_engine("oracle+cx_oracle://dsn", coerce_to_decimal=False)
 
 .. versionadded:: 0.7.6
     Add the ``coerce_to_decimal`` flag.
@@ -229,8 +262,8 @@ when dealing with numerics with locale settings that don't use
 a period "." as the decimal character.
 
 .. versionchanged:: 0.6.6
-    The outputtypehandler uses a comma "," character to represent
-    a decimal point.
+    The outputtypehandler supports the case where the locale uses a
+    comma "," character to represent a decimal point.
 
 .. _OCI: http://www.oracle.com/technetwork/database/features/oci/index.html