Sqlalchemy

:released: March 27, 2017

.. change::
:tags: feature, orm
:tickets: 3933
:versions: 1.2.0b1

An :func:`.aliased()` construct can now be passed to the
:meth:`_query.Query.select_entity_from` method. Entities will be pulled
from the selectable represented by the :func:`.aliased` construct.
This allows special options for :func:`.aliased` such as
:paramref:`.aliased.adapt_on_names` to be used in conjunction with
:meth:`_query.Query.select_entity_from`.

.. change::
:tags: bug, engine
:tickets: 3946
:versions: 1.2.0b1

Added an exception handler that will warn for the "cause" exception on
Py2K when the "autorollback" feature of :class:`_engine.Connection` itself
raises an exception. In Py3K, the two exceptions are naturally reported
by the interpreter as one occurring during the handling of the other.
This is continuing with the series of changes for rollback failure
handling that were last visited as part of :ticket:`2696` in 1.0.12.

.. change::
:tags: bug, orm
:tickets: 3947
:versions: 1.2.0b1

Fixed a race condition which could occur under threaded environments
as a result of the caching added via :ticket:`3915`. An internal
collection of ``Column`` objects could be regenerated on an alias
object inappropriately, confusing a joined eager loader when it
attempts to render SQL and collect results and resulting in an
attribute error. The collection is now generated up front before
the alias object is cached and shared among threads.

.. change::
:tags: bug, sql, postgresql
:tickets: 2892

Added support for the :class:`.Variant` and the :class:`.SchemaType`
objects to be compatible with each other. That is, a variant
can be created against a type like :class:`.Enum`, and the instructions
to create constraints and/or database-specific type objects will
propagate correctly as per the variant's dialect mapping.

.. change::
:tags: bug, sql
:tickets: 3931

Fixed bug in compiler where the string identifier of a savepoint would
be cached in the identifier quoting dictionary; as these identifiers
are arbitrary, a small memory leak could occur if a single
:class:`_engine.Connection` had an unbounded number of savepoints used,
as well as if the savepoint clause constructs were used directly
with an unbounded umber of savepoint names. The memory leak does
**not** impact the vast majority of cases as normally the
:class:`_engine.Connection`, which renders savepoint names with a simple
counter starting at "1", is used on a per-transaction or
per-fixed-number-of-transactions basis before being discarded.

.. change::
:tags: bug, sql
:tickets: 3924

Fixed bug in new "schema translate" feature where the translated schema
name would be invoked in terms of an alias name when rendered along
with a column expression; occurred only when the source translate
name was "None". The "schema translate" feature now only takes
effect for :class:`.SchemaItem` and :class:`.SchemaType` subclasses,
that is, objects that correspond to a DDL-creatable structure in
a database.

.. changelog::

:released: February 28, 2017

.. change::
:tags: bug, mysql

Added new MySQL 8.0 reserved words to the MySQL dialect for proper
quoting. Pull request courtesy Hanno Schlichting.

.. change:: 3915
:tags: bug, orm
:tickets: 3915

Addressed some long unattended performance concerns within the joined
eager loader query construction system that have accumulated since
earlier versions as a result of increased abstraction. The use of ad-
hoc :class:`.AliasedClass` objects per query, which produces lots of
column lookup overhead each time, has been replaced with a cached
approach that makes use of a small pool of :class:`.AliasedClass`
objects that are reused between invocations of joined eager loading.
Some mechanics involving eager join path construction have also been
optimized. Callcounts for an end-to-end query construction + single
row fetch test with a worst-case joined loader scenario have been
reduced by about 60% vs. 1.1.5 and 42% vs. that of 0.8.6.

.. change:: 3804
:tags: bug, postgresql
:tickets: 3804

Added regular expressions for the "IMPORT FOREIGN SCHEMA",
"REFRESH MATERIALIZED VIEW" PostgreSQL statements so that they
autocommit when invoked via a connection or engine without
an explicit transaction. Pull requests courtesy Frazer McLean
and Paweł Stiasny.

.. change:: 3909
:tags: bug, orm
:tickets: 3909

Fixed a major inefficiency in the "eager_defaults" feature whereby
an unnecessary SELECT would be emitted for column values where the
ORM had explicitly inserted NULL, corresponding to attributes that
were unset on the object but did not have any server default
specified, as well as expired attributes on update that nevertheless
had no server onupdate set up. As these columns are not part of the
RETURNING that eager_defaults tries to use, they should not be
post-SELECTed either.

.. change:: 3908
:tags: bug, orm
:tickets: 3908

Fixed two closely related bugs involving the mapper eager_defaults
flag in conjunction with single-table inheritance; one where the
eager defaults logic would inadvertently try to access a column
that's part of the mapper's "exclude_properties" list (used by
Declarative with single table inheritance) during the eager defaults
fetch, and the other where the full load of the row in order to
fetch the defaults would fail to use the correct inheriting mapper.


.. change:: 3905
:tags: bug, sql
:tickets: 3905

Fixed bug whereby the :meth:`.DDLEvents.column_reflect` event would not
allow a non-textual expression to be passed as the value of the
"default" for the new column, such as a :class:`.FetchedValue`
object to indicate a generic triggered default or a
:func:`_expression.text` construct. Clarified the documentation
in this regard as well.

.. change:: 3901
:tags: bug, ext
:tickets: 3901

Fixed bug in new :mod:`sqlalchemy.ext.indexable` extension
where setting of a property that itself refers to another property
would fail.

.. change:: 3900
:tags: bug, postgresql
:tickets: 3900

Fixed bug in PostgreSQL :class:`.ExcludeConstraint` where the
"whereclause" and "using" parameters would not be copied during an
operation like :meth:`_schema.Table.tometadata`.

.. change:: 3898
:tags: bug, mssql
:tickets: 3898

Added a version check to the "get_isolation_level" feature, which is
invoked upon first connect, so that it skips for SQL Server version
2000, as the necessary system view is not available prior to SQL Server
2005.

.. change:: 3897
:tags: feature, ext
:tickets: 3896

Added :meth:`.baked.Result.scalar` and :meth:`.baked.Result.count`
to the "baked" query system.

.. change:: 3895
:tags: bug, orm, declarative
:tickets: 3895

Fixed bug where the "automatic exclude" feature of declarative that
ensures a column local to a single table inheritance subclass does
not appear as an attribute on other derivations of the base would
not take effect for multiple levels of subclassing from the base.

.. change:: 3893
:tags: bug, orm
:tickets: 3893

Fixed bug first introduced in 0.9.7 as a result of :ticket:`3106`
which would cause an incorrect query in some forms of multi-level
subqueryload against aliased entities, with an unnecessary extra
FROM entity in the innermost subquery.

.. changelog::

:released: January 17, 2017

.. change:: mysql_index_prefix
:tags: feature, mysql

Added a new parameter ``mysql_prefix`` supported by the :class:`.Index`
construct, allows specification of MySQL-specific prefixes such as
"FULLTEXT". Pull request courtesy Joseph Schorr.

.. change:: 3854
:tags: bug, orm
:tickets: 3854

Fixed bug in subquery loading where an object encountered as an
"existing" row, e.g. already loaded from a different path in the
same query, would not invoke subquery loaders for unloaded attributes
that specified this loading. This issue is in the same area
as that of :ticket:`3431`, :ticket:`3811` which involved
similar issues with joined loading.

.. change:: 3888
:tags: bug, postgresql
:tickets: 3888

Fixed bug in new "ON CONFLICT DO UPDATE" feature where the "set"
values for the UPDATE clause would not be subject to type-level
processing, as normally takes effect to handle both user-defined
type level conversions as well as dialect-required conversions, such
as those required for JSON datatypes. Additionally, clarified that
the keys in the ``set_`` dictionary should match the "key" of the
column, if distinct from the column name. A warning is emitted
for remaining column names that don't match column keys; for
compatibility reasons, these are emitted as they were previously.

.. change:: 3872
:tags: bug, examples
:tickets: 3872

Fixed two issues with the versioned_history example, one is that
the history table now gets autoincrement=False to avoid 1.1's new
errors regarding composite primary keys with autoincrement; the other
is that the sqlite_autoincrement flag is now used to ensure on SQLite,
unique identifiers are used for the lifespan of a table even if
some rows are deleted. Pull request courtesy Carlos García Montoro.

.. change:: 3882
:tags: bug, sql
:tickets: 3882

Fixed bug originally introduced in 0.9 via :ticket:`1068` where
order_by(<some Label()>) would order by the label name based on name
alone, that is, even if the labeled expression were not at all the same
expression otherwise present, implicitly or explicitly, in the
selectable. The logic that orders by label now ensures that the
labeled expression is related to the one that resolves to that name
before ordering by the label name; additionally, the name has to
resolve to an actual label explicit in the expression elsewhere, not
just a column name. This logic is carefully kept separate from the
order by(textual name) feature that has a slightly different purpose.

.. change:: try_finally_for_noautoflush
:tags: bug, orm

The :attr:`.Session.no_autoflush` context manager now ensures that
the autoflush flag is reset within a "finally" block, so that if
an exception is raised within the block, the state still resets
appropriately. Pull request courtesy Emin Arakelian.

.. change:: 3878
:tags: bug, sql
:tickets: 3878

Fixed 1.1 regression where ``import *`` would not work for
sqlalchemy.sql.expression, due to mis-spelled ``any_`` and ``all_``
functions.

.. change:: 3880
:tags: bg, sql
:tickets: 3880

Fixed bug where literal_binds compiler flag was not honored by the
:class:`_expression.Insert` construct for the "multiple values" feature; the
subsequent values are now rendered as literals.

.. change:: 3877
:tags: bug, oracle, postgresql
:tickets: 3877

Fixed bug where an INSERT from SELECT where the source table contains
an autoincrementing Sequence would fail to compile correctly.

.. change:: 3876
:tags: bug, mssql
:tickets: 3876

Fixed bug where SQL Server dialects would attempt to select the
last row identity for an INSERT from SELECT, failing in the case when
the SELECT has no rows. For such a statement,
the inline flag is set to True indicating no last primary key
should be fetched.

.. change:: 3875
:tags: bug, oracle
:tickets: 3875

Fixed bug where the "COMPRESSION" keyword was used in the ALL_TABLES
query on Oracle 9.2; even though Oracle docs state table compression
was introduced in 9i, the actual column is not present until
10.1.

.. change:: 3874
:tags: bug, orm
:tickets: 3874

Fixed bug where the single-table inheritance query criteria would not
be inserted into the query in the case that the :class:`.Bundle`
construct were used as the selection criteria.

.. change:: repr_for_url_reflect
:tags: bug, sql

The engine URL embedded in the exception for "could not reflect"
in :meth:`_schema.MetaData.reflect` now conceals the password; also
the ``__repr__`` for :class:`.TLEngine` now acts like that of
:class:`_engine.Engine`, concealing the URL password. Pull request courtesy
Valery Yundin.

.. change:: 3867
:tags: bug, mysql
:tickets: 3867

The MySQL dialect now will not warn when a reflected column has a
"COMMENT" keyword on it, but note however the comment is not yet
reflected; this is on the roadmap for a future release. Pull request
courtesy Lele Long.

.. change:: pg_timestamp_zero_prec
:tags: bug, postgresql

The :class:`_postgresql.TIME` and :class:`_postgresql.TIMESTAMP`
datatypes now support a setting of zero for "precision"; previously
a zero would be ignored. Pull request courtesy Ionuț Ciocîrlan.

.. change:: 3861
:tags: bug, engine
:tickets: 3861

The "extend_existing" option of :class:`_schema.Table` reflection would
cause indexes and constraints to be doubled up in the case that the parameter
were used with :meth:`_schema.MetaData.reflect` (as the automap extension does)
due to tables being reflected both within the foreign key path as well
as directly. A new de-duplicating set is passed through within the
:meth:`_schema.MetaData.reflect` sequence to prevent double reflection in this
way.

.. change:: 3859
:tags: bug, sql
:tickets: 3859

Fixed issue in :class:`.Variant` where the "right hand coercion" logic,
inherited from :class:`.TypeDecorator`, would
coerce the right-hand side into the :class:`.Variant` itself, rather than
what the default type for the :class:`.Variant` would do. In the
case of :class:`.Variant`, we want the type to act mostly like the base
type so the default logic of :class:`.TypeDecorator` is now overridden
to fall back to the underlying wrapped type's logic. Is mostly relevant
for JSON at the moment.

.. change:: 3856
:tags: bug, orm
:tickets: 3856

Fixed bug related to :ticket:`3177`, where a UNION or other set operation
emitted by a :class:`_query.Query` would apply "single-inheritance" criteria
to the outside of the union (also referencing the wrong selectable),
even though this criteria is now expected to
be already present on the inside subqueries. The single-inheritance
criteria is now omitted once union() or another set operation is
called against :class:`_query.Query` in the same way as :meth:`_query.Query.from_self`.

.. change:: 3548
:tags: bug, firebird
:tickets: 3548

Ported the fix for Oracle quoted-lowercase names to Firebird, so that
a table name that is quoted as lower case can be reflected properly
including when the table name comes from the get_table_names()
inspection function.

.. changelog::

:released: November 15, 2016

.. change:: 3842
:tags: bug, sql
:tickets: 3842

Fixed bug where newly added warning for primary key on insert w/o
autoincrement setting (see :ticket:`3216`) would fail to emit
correctly when invoked upon a lower-case :func:`.table` construct.

.. change:: 3852
:tags: bug, orm
:tickets: 3852

Fixed regression in collections due to :ticket:`3457` whereby
deserialize during pickle or deepcopy would fail to establish all
attributes of an ORM collection, causing further mutation operations to
fail.

.. change:: default_schema
:tags: bug, engine

Removed long-broken "default_schema_name()" method from
:class:`_engine.Connection`. This method was left over from a very old
version and was non-working (e.g. would raise). Pull request
courtesy Benjamin Dopplinger.

.. change:: pragma
:tags: bug, sqlite

Added quotes to the PRAGMA directives in the pysqlcipher dialect
to support additional cipher arguments appropriately. Pull request
courtesy Kevin Jurczyk.

.. change:: 3846
:tags: bug, postgresql
:tickets: 3846, 3807

Fixed regression caused by the fix in :ticket:`3807` (version 1.1.0)
where we ensured that the tablename was qualified in the WHERE clause
of the DO UPDATE portion of PostgreSQL's ON CONFLICT, however you
*cannot* put the table name in the WHERE clause in the actual ON
CONFLICT itself. This was an incorrect assumption, so that portion
of the change in :ticket:`3807` is rolled back.

.. change:: 3845
:tags: bug, orm
:tickets: 3845

Fixed long-standing bug where the "noload" relationship loading
strategy would cause backrefs and/or back_populates options to be
ignored.

.. change:: sscursor_mysql
:tags: feature, mysql

Added support for server side cursors to the mysqlclient and
pymysql dialects. This feature is available via the
:paramref:`.Connection.execution_options.stream_results` flag as well
as the ``server_side_cursors=True`` dialect argument in the
same way that it has been for psycopg2 on PostgreSQL. Pull request
courtesy Roman Podoliaka.

.. change::
:tags: bug, mysql
:tickets: 3841

MySQL's native ENUM type supports any non-valid value being sent, and
in response will return a blank string. A hardcoded rule to check for
"is returning the blank string" has been added to the MySQL
implementation for ENUM so that this blank string is returned to the
application rather than being rejected as a non-valid value. Note that
if your MySQL enum is linking values to objects, you still get the
blank string back.

.. change::
:tags: bug, sqlite, py3k

Added an optional import for the pysqlcipher3 DBAPI when using the
pysqlcipher dialect. This package will attempt to be imported
if the Python-2 only pysqlcipher DBAPI is non-present.
Pull request courtesy Kevin Jurczyk.

.. changelog::

:released: October 27, 2016

.. change::
:tags: bug, orm
:tickets: 3839

Fixed regression caused by :ticket:`2677` whereby calling
:meth:`.Session.delete` on an object that was already flushed as
deleted in that session would fail to set up the object in the
identity map (or reject the object), causing flush errors as the
object were in a state not accommodated by the unit of work.
The pre-1.1 behavior in this case has been restored, which is that
the object is put back into the identity map so that the DELETE
statement will be attempted again, which emits a warning that the number
of expected rows was not matched (unless the row were restored outside
of the session).

.. change::
:tags: bug, postgresql
:tickets: 3835

PostgreSQL table reflection will ensure that the
:paramref:`_schema.Column.autoincrement` flag is set to False when reflecting
a primary key column that is not of an :class:`.Integer` datatype,
even if the default is related to an integer-generating sequence.
This can happen if a column is created as SERIAL and the datatype
is changed. The autoincrement flag can only be True if the datatype
is of integer affinity in the 1.1 series.

.. change::
:tags: bug, orm
:tickets: 3836

Fixed regression where some :class:`_query.Query` methods like
:meth:`_query.Query.update` and others would fail if the :class:`_query.Query`
were against a series of mapped columns, rather than the mapped
entity as a whole.

.. change::
:tags: bug, sql
:tickets: 3833

Fixed bug involving new value translation and validation feature
in :class:`.Enum` whereby using the enum object in a string
concatenation would maintain the :class:`.Enum` type as the type
of the expression overall, producing missing lookups. A string
concatenation against an :class:`.Enum`-typed column now uses
:class:`.String` as the datatype of the expression itself.

.. change::
:tags: bug, sql
:tickets: 3832

Fixed regression which occurred as a side effect of :ticket:`2919`,
which in the less typical case of a user-defined
:class:`.TypeDecorator` that was also itself an instance of
:class:`.SchemaType` (rather than the implementation being such)
would cause the column attachment events to be skipped for the
type itself.


.. changelog::

:released: October 17, 2016

.. change::
:tags: bug, sql
:tickets: 3823

Fixed a regression caused by a newly added function that performs the
"wrap callable" function of sql :class:`.DefaultGenerator` objects,
an attribute error raised for ``__module__`` when the default callable
was a ``functools.partial`` or other object that doesn't have a
``__module__`` attribute.

.. change::
:tags: bug, orm
:tickets: 3824

Fixed bug involving the rule to disable a joined collection eager
loader on the other side of a many-to-one lazy loader, first added
in :ticket:`1495`, where the rule would fail if the parent object
had some other lazyloader-bound query options associated with it.

.. change::
:tags: bug, orm
:tickets: 3822

Fixed self-referential entity, deferred column loading issue in a
similar style as that of :ticket:`3431`, :ticket:`3811` where an entity
is present in multiple positions within the row due to self-referential
eager loading; when the deferred loader only applies to one of the
paths, the "present" column loader will now override the deferred non-
load for that entity regardless of row ordering.

.. change::
:tags: bug, sql, postgresql
:tickets: 3827

Fixed regression in :class:`.Enum` type where event handlers were not
transferred in the case of the type object being copied, due to a
conflicting copy() method added as part of :ticket:`3250`. This copy
occurs normally in situations when the column is copied, such as
in tometadata() or when using declarative mixins with columns. The
event handler not being present would impact the constraint being
created for a non-native enumerated type, but more critically the
ENUM object on the PostgreSQL backend.


.. change::
:tags: bug, postgresql, sql
:tickets: 3828

Changed the naming convention used when generating bound parameters
for a multi-VALUES insert statement, so that the numbered parameter
names don't conflict with the anonymized parameters of a WHERE clause,
as is now common in a PostgreSQL ON CONFLICT construct.

.. changelog::