summaryrefslogtreecommitdiff
path: root/test/dialect/postgresql/test_compiler.py
Commit message (Collapse)AuthorAgeFilesLines
* Fixed issue when copying ExcludeConstraintFederico Caselli2023-03-011-0/+34
| | | | | Fixes: #9401 Change-Id: Ie10192348749567110f53ae618fc724f37d1a6a1
* Improve exclude constraint docs and tests.Federico Caselli2023-02-271-6/+19
| | | | | | Follow up of 71693c94d52612a5e88128575ff308ee4a923c00 Change-Id: Icc9d9942bda92171581dec82cf0cacbd3e3e4162
* ExcludeConstraint literal_compileFederico Caselli2023-02-221-2/+17
| | | | | | | | ExcludeConstraint correctly uses literal compile when compiling expression ddl. Fixes: #9349 Change-Id: I11a994ac46556a972afc696a2baad7ddbdd3de97
* Dedicated bitwise operatorsjazzthief2023-02-061-0/+8
| | | | | | | | | | | | | Added a full suite of new SQL bitwise operators, for performing database-side bitwise expressions on appropriate data values such as integers, bit-strings, and similar. Pull request courtesy Yegor Statkevich. Fixes: #8780 Closes: #9204 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/9204 Pull-request-sha: a4541772a6a784f9161ad78ef84d2ea7a62fa8de Change-Id: I4c70e80f9548dcc1b4e3dccd71bd59d51d3ed46e
* add explicit REGCONFIG, pg full text functionsMike Bayer2022-12-151-0/+179
| | | | | | | | | | | | | | | | | | | Added support for explicit use of PG full text functions with asyncpg and psycopg (SQLAlchemy 2.0 only), with regards to the ``REGCONFIG`` type cast for the first argument, which previously would be incorrectly cast to a VARCHAR, causing failures on these dialects that rely upon explicit type casts. This includes support for :class:`_postgresql.to_tsvector`, :class:`_postgresql.to_tsquery`, :class:`_postgresql.plainto_tsquery`, :class:`_postgresql.phraseto_tsquery`, :class:`_postgresql.websearch_to_tsquery`, :class:`_postgresql.ts_headline`, each of which will determine based on number of arguments passed if the first string argument should be interpreted as a PostgreSQL "REGCONFIG" value; if so, the argument is typed using a newly added type object :class:`_postgresql.REGCONFIG` which is then explicitly cast in the SQL expression. Fixes: #8977 Change-Id: Ib36698a984fd4194bd6e0eb663105f790f3db7d3
* Fix positional compiling bugsFederico Caselli2022-12-011-3/+3
| | | | | | | | | | | Fixed a series of issues regarding positionally rendered bound parameters, such as those used for SQLite, asyncpg, MySQL and others. Some compiled forms would not maintain the order of parameters correctly, such as the PostgreSQL ``regexp_replace()`` function as well as within the "nesting" feature of the :class:`.CTE` construct first introduced in :ticket:`4123`. Fixes: #8827 Change-Id: I9813ed7c358cc5c1e26725c48df546b209a442cb
* Add value-level hooks for SQL type detection; apply to RangeLele Gaifax2022-11-291-0/+21
| | | | | | | | | | | | | | | | | | Added additional type-detection for the new PostgreSQL :class:`_postgresql.Range` type, where previous cases that allowed the psycopg2-native range objects to be received directly by the DBAPI without SQLAlchemy intercepting them stopped working, as we now have our own value object. The :class:`_postgresql.Range` object has been enhanced such that SQLAlchemy Core detects it in otherwise ambiguous situations (such as comparison to dates) and applies appropriate bind handlers. Pull request courtesy Lele Gaifax. Fixes: #8884 Closes: #8886 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/8886 Pull-request-sha: 6e95e08a30597d3735ab38f2f1a2ccabd968852c Change-Id: I3ca277c826dcf4b5644f44eb251345b439a84ee4
* Try running pyupgrade on the codeFederico Caselli2022-11-161-3/+2
| | | | | | | | command run is "pyupgrade --py37-plus --keep-runtime-typing --keep-percent-format <files...>" pyupgrade will change assert_ to assertTrue. That was reverted since assertTrue does not exists in sqlalchemy fixtures Change-Id: Ie1ed2675c7b11d893d78e028aad0d1576baebb55
* establish consistency for RETURNING column labelsMike Bayer2022-11-111-0/+30
| | | | | | | | | | | | | | | The RETURNING clause now renders columns using the routine as that of the :class:`.Select` to generate labels, which will include disambiguating labels, as well as that a SQL function surrounding a named column will be labeled using the column name itself. This is a more comprehensive change than a similar one made for the 1.4 series that adjusted the function label issue only. includes 1.4's changelog for the backported version which also fixes an Oracle issue independently of the 2.0 series. Fixes: #8770 Change-Id: I2ab078a214a778ffe1720dbd864ae4c105a0691d
* Revert automatic set of sequence start to 1Federico Caselli2022-10-171-2/+2
| | | | | | | | | | | | | | | | | The :class:`.Sequence` construct restores itself to the DDL behavior it had prior to the 1.4 series, where creating a :class:`.Sequence` with no additional arguments will emit a simple ``CREATE SEQUENCE`` instruction **without** any additional parameters for "start value". For most backends, this is how things worked previously in any case; **however**, for MS SQL Server, the default value on this database is ``-2**63``; to prevent this generally impractical default from taking effect on SQL Server, the :paramref:`.Sequence.start` parameter should be provided. As usage of :class:`.Sequence` is unusual for SQL Server which for many years has standardized on ``IDENTITY``, it is hoped that this change has minimal impact. Fixes: #7211 Change-Id: I1207ea10c8cb1528a1519a0fb3581d9621c27b31
* `aggregate_order_by` now supports cache generation.Federico Caselli2022-09-251-0/+33
| | | | | | | | | also adjusted CacheKeyFixture to be a general purpose fixture so that sub-components / dialects can run their own cache key tests. Fixes: #8574 Change-Id: I6c66107856aee11e548d357cea77bceee3e316a0
* JSONPATH type can be used in casts in PostgreSQLFederico Caselli2022-08-171-0/+14
| | | | | | | | | | Introduced the type :class:`_postgresql.JSONPATH` that can be used in cast expressions. This is required by some PostgreSQL dialects when using functions such as ``jsonb_path_exists`` or ``jsonb_path_match`` that accept a ``jsonpath`` as input. Fixes: #8216 Change-Id: I3e7337eab91680cab1604e1f3058854a0a19c5be
* ensure RETURNING renders in stringify w/ no server versionMike Bayer2022-08-031-0/+16
| | | | | | | | | just in my own testing, if I say insert().return_defaults() and stringify, I should see it, so make sure all the dialects default to "insert_returning" etc. , with downgrade on server version check. Change-Id: Id64e78fcb03c48b5dcb0feb21cb9cc495edd15e9
* Domain typeDavid Baumgold2022-06-211-1/+75
| | | | | | | | | | | | | | Added a new Postgresql :class:`_postgresql.DOMAIN` datatype, which follows the same CREATE TYPE / DROP TYPE behaviors as that of PostgreSQL :class:`_postgresql.ENUM`. Much thanks to David Baumgold for the efforts on this. Fixes: #7316 Closes: #7317 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/7317 Pull-request-sha: bc9a82f010e6ca2f70a6e8a7620b748e483c26c3 Change-Id: Id8d7e48843a896de17d20cc466b115b3cc065132
* use plainto_tsquery for PG matchMike Bayer2022-05-221-5/+36
| | | | | | | | | | | | | | | | The :meth:`.Operators.match` operator now uses ``plainto_tsquery()`` for PostgreSQL full text search, rather than ``to_tsquery()``. The rationale for this change is to provide better cross-compatibility with match on other database backends. Full support for all PostgreSQL full text functions remains available through the use of :data:`.func` in conjunction with :meth:`.Operators.bool_op` (an improved version of :meth:`.Operators.op` for boolean operators). Additional doc updates here apply to 1.4 so will backport these out to a separate commit. Fixes: #7086 Change-Id: I1946075daf5d9c558e85f73f1bf852604b3b1b8c
* render col name in on conflict set clause, not given keyMike Bayer2022-05-151-13/+75
| | | | | | | | | | | | | Fixed bug where the PostgreSQL :meth:`_postgresql.Insert.on_conflict` method and the SQLite :meth:`_sqlite.Insert.on_conflict` method would both fail to correctly accommodate a column with a separate ".key" when specifying the column using its key name in the dictionary passed to ``set_``, as well as if the :attr:`_sqlite.Insert.excluded` or :attr:`_postgresql.Insert.excluded` collection were used as the dictionary directly. Fixes: #8014 Change-Id: I67226aeedcb2c683e22405af64720cc1f990f274
* properly type array element in any() / all()Mike Bayer2022-04-221-16/+16
| | | | | | | | | | Fixed bug in :class:`.ARRAY` datatype in combination with :class:`.Enum` on PostgreSQL where using the ``.any()`` method to render SQL ANY(), given members of the Python enumeration as arguments, would produce a type adaptation failure on all drivers. Fixes: #6515 Change-Id: Ia1e3b4e10aaf264ed436ce6030d105fc60023433
* pop the stack that we pushedMike Bayer2022-03-081-0/+22
| | | | | | | | | | | Fixed regression caused by :ticket:`7760` where the new capabilities of :class:`.TextualSelect` were not fully implemented within the compiler properly, leading to issues with composed INSERT constructs such as "INSERT FROM SELECT" and "INSERT...ON CONFLICT" when combined with CTE and textual statements. Fixes: #7798 Change-Id: Ia2ce92507e574dd36fd26dd38ec9dd2713584467
* Implement generic Double and related fixed typeszeeeeeb2022-02-251-0/+12
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Added :class:`.Double`, :class:`.DOUBLE`, :class:`.DOUBLE_PRECISION` datatypes to the base ``sqlalchemy.`` module namespace, for explicit use of double/double precision as well as generic "double" datatypes. Use :class:`.Double` for generic support that will resolve to DOUBLE/DOUBLE PRECISION/FLOAT as needed for different backends. Implemented DDL and reflection support for ``FLOAT`` datatypes which include an explicit "binary_precision" value. Using the Oracle-specific :class:`_oracle.FLOAT` datatype, the new parameter :paramref:`_oracle.FLOAT.binary_precision` may be specified which will render Oracle's precision for floating point types directly. This value is interpreted during reflection. Upon reflecting back a ``FLOAT`` datatype, the datatype returned is one of :class:`_types.DOUBLE_PRECISION` for a ``FLOAT`` for a precision of 126 (this is also Oracle's default precision for ``FLOAT``), :class:`_types.REAL` for a precision of 63, and :class:`_oracle.FLOAT` for a custom precision, as per Oracle documentation. As part of this change, the generic :paramref:`_sqltypes.Float.precision` value is explicitly rejected when generating DDL for Oracle, as this precision cannot be accurately converted to "binary precision"; instead, an error message encourages the use of :meth:`_sqltypes.TypeEngine.with_variant` so that Oracle's specific form of precision may be chosen exactly. This is a backwards-incompatible change in behavior, as the previous "precision" value was silently ignored for Oracle. Fixes: #5465 Closes: #7674 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/7674 Pull-request-sha: 5c68419e5aee2e27bf21a8ac9eb5950d196c77e5 Change-Id: I831f4af3ee3b23fde02e8f6393c83e23dd7cd34d
* Add compiler support for PostgreSQL "NOT VALID" constraints.Gilbert Gilb's2022-01-261-0/+59
| | | | | | | | | | | | | | Added compiler support for the PostgreSQL ``NOT VALID`` phrase when rendering DDL for the :class:`.CheckConstraint`, :class:`.ForeignKeyConstraint` and :class:`.ForeignKey` schema constructs. Pull request courtesy Gilbert Gilb's. Fixes: #7600 Closes: #7601 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/7601 Pull-request-sha: 78eecd55fd9fad07030d963f5fd6713c4af60e80 Change-Id: I84bfe84596856eeea2bcca45c04ad23d980a75ec
* consider truediv as truediv; support floordiv operatorMike Bayer2021-12-261-1/+1
| | | | | | | | | | | | | | | | | | | | | Implemented full support for "truediv" and "floordiv" using the "/" and "//" operators. A "truediv" operation between two expressions using :class:`_types.Integer` now considers the result to be :class:`_types.Numeric`, and the dialect-level compilation will cast the right operand to a numeric type on a dialect-specific basis to ensure truediv is achieved. For floordiv, conversion is also added for those databases that don't already do floordiv by default (MySQL, Oracle) and the ``FLOOR()`` function is rendered in this case, as well as for cases where the right operand is not an integer (needed for PostgreSQL, others). The change resolves issues both with inconsistent behavior of the division operator on different backends and also fixes an issue where integer division on Oracle would fail to be able to fetch a result due to inappropriate outputtypehandlers. Fixes: #4926 Change-Id: Id54cc018c1fb7a49dd3ce1216d68d40f43fe2659
* Merge "propose emulated setinputsizes embedded in the compiler" into mainmike bayer2021-11-251-9/+0
|\
| * propose emulated setinputsizes embedded in the compilerMike Bayer2021-11-231-9/+0
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Add a new system so that PostgreSQL and other dialects have a reliable way to add casts to bound parameters in SQL statements, replacing previous use of setinputsizes() for PG dialects. rationale: 1. psycopg3 will be using the same SQLAlchemy-side "setinputsizes" as asyncpg, so we will be seeing a lot more of this 2. the full rendering that SQLAlchemy's compilation is performing is in the engine log as well as error messages. Without this, we introduce three levels of SQL rendering, the compiler, the hidden "setinputsizes" in SQLAlchemy, and then whatever the DBAPI driver does. With this new approach, users reporting bugs etc. will be less confused that there are as many as two separate layers of "hidden rendering"; SQLAlchemy's rendering is again fully transparent 3. calling upon a setinputsizes() method for every statement execution is expensive. this way, the work is done behind the caching layer 4. for "fast insertmany()", I also want there to be a fast approach towards setinputsizes. As it was, we were going to be taking a SQL INSERT with thousands of bound parameter placeholders and running a whole second pass on it to apply typecasts. this way, we will at least be able to build the SQL string once without a huge second pass over the whole string 5. psycopg2 can use this same system for its ARRAY casts 6. the general need for PostgreSQL to have lots of type casts is now mostly in the base PostgreSQL dialect and works independently of a DBAPI being present. dependence on DBAPI symbols that aren't complete / consistent / hashable is removed I was originally going to try to build this into bind_expression(), but it was revealed this worked poorly with custom bind_expression() as well as empty sets. the current impl also doesn't need to run a second expression pass over the POSTCOMPILE sections, which came out better than I originally thought it would. Change-Id: I363e6d593d059add7bcc6d1f6c3f91dd2e683c0c
* | Clean up most py3k compatFederico Caselli2021-11-241-8/+2
|/ | | | Change-Id: I8172fdcc3103ff92aa049827728484c8779af6b7
* Support lightweight compiler column elements w/ slotsMike Bayer2021-11-221-0/+9
| | | | | | | | | | | | | | | | | | | | | | | | | | | | the _CompileLabel class included ``__slots__`` but these weren't used as the superclasses included slots. Create a ``__slots__`` superclass for ``ClauseElement``, creating a new class of compilable SQL elements that don't include heavier features like caching, annotations and cloning, which are meant to be used only in an ad-hoc compiler fashion. Create new ``CompilerColumnElement`` from that which serves in column-oriented contexts, but similarly does not include any expression operator support as it is intended to be used only to generate a string. Apply this to both ``_CompileLabel`` as well as PostgreSQL ``_ColonCast``, which does not actually subclass ``ColumnElement`` as this class has memoized attributes that aren't worth changing, and does not include SQL operator capabilities as these are not needed for these compiler-only objects. this allows us to more inexpensively add new ad-hoc labels / casts etc. at compile time, as we will be seeking to expand out the typecasts that are needed for PostgreSQL dialects in a subsequent patch. Change-Id: I52973ae3295cb6e2eb0d7adc816c678a626643ed
* Remove object in class definitionFederico Caselli2021-11-221-2/+2
| | | | | References: #4600 Change-Id: I2a62ddfe00bc562720f0eae700a497495d7a987a
* Deprecate create_engine.implicit_returningjonathan vanasco2021-11-091-24/+1
| | | | | | | | | | | | | | | | | | | | | | | | The :paramref:`_sa.create_engine.implicit_returning` parameter is deprecated on the :func:`_sa.create_engine` function only; the parameter remains available on the :class:`_schema.Table` object. This parameter was originally intended to enable the "implicit returning" feature of SQLAlchemy when it was first developed and was not enabled by default. Under modern use, there's no reason this parameter should be disabled, and it has been observed to cause confusion as it degrades performance and makes it more difficult for the ORM to retrieve recently inserted server defaults. The parameter remains available on :class:`_schema.Table` to specifically suit database-level edge cases which make RETURNING infeasible, the sole example currently being SQL Server's limitation that INSERT RETURNING may not be used on a table that has INSERT triggers on it. Also removed from the Oracle dialect some logic that would upgrade an Oracle 8/8i server version to use implicit returning if the parameter were explictly passed; these versions of Oracle still support RETURNING so the feature is now enabled for all Oracle versions. Fixes: #6962 Change-Id: Ib338e300cd7c8026c3083043f645084a8211aed8
* Modernize tests - calling_mapper_directlyGord Thompson2021-09-301-4/+5
| | | | | | | | | | | | | a few changes for py2k: * map_imperatively() includes the check that a class is being sent, this was only working for mapper() before * the test suite didn't place the py2k "autouse" workaround in the correct order, seemingly, tried to adjust the per-test ordering setup in pytestplugin.py Change-Id: I4cc39630724e810953cfda7b2afdadc8b948e3c2
* fix linter JOIN logic; fix PostgreSQL ARRAY op comparisonMike Bayer2021-08-151-0/+33
| | | | | | | | | | | | | | | | | | | Adjusted the "from linter" warning feature to accommodate for a chain of joins more than one level deep where the ON clauses don't explicitly match up the targets, such as an expression such as "ON TRUE". This mode of use is intended to cancel the cartesian product warning simply by the fact that there's a JOIN from "a to b", which was not working for the case where the chain of joins had more than one element. this incurs a bit more compiler overhead that comes out in profiling but is not extensive. Added the "is_comparison" flag to the PostgreSQL "overlaps", "contained_by", "contains" operators, so that they work in relevant ORM contexts as well as in conjunction with the "from linter" feature. Fixes: #6886 Change-Id: I078dc3fe6d4f7871ffe4ebac3e71e62f3f213d12
* Modernize tests - dml_valuesGord Thompson2021-07-261-36/+47
| | | | Change-Id: Ida86ed40c43d91813151621b847376976773a5f9
* Merge "Extract format_constraint truncation rules to ON CONFLICT"mike bayer2021-07-131-0/+26
|\
| * Extract format_constraint truncation rules to ON CONFLICTMike Bayer2021-07-131-0/+26
| | | | | | | | | | | | | | | | | | | | | | Fixed issue where a too-long constraint name rendered as part of the "ON CONFLICT ON CONSTRAINT" element of the :class:`_postgresql.Insert` construct due to naming convention generation would not correctly truncate the name in the same way that it normally renders within a CREATE TABLE statement, thus producing a non-matching and too-long constraint name. Fixes: #6755 Change-Id: Ib27014a5ecbc9cd5861a396f8bb49fbc60bf49fe
* | Merge "implement independent CTEs"mike bayer2021-07-121-1/+25
|\ \
| * | implement independent CTEsMike Bayer2021-07-121-1/+25
| |/ | | | | | | | | | | | | | | | | | | | | | | | | | | Added new method :meth:`_sql.HasCTE.add_cte` to each of the :func:`_sql.select`, :func:`_sql.insert`, :func:`_sql.update` and :func:`_sql.delete` constructs. This method will add the given :class:`_sql.CTE` as an "independent" CTE of the statement, meaning it renders in the WITH clause above the statement unconditionally even if it is not otherwise referenced in the primary statement. This is a popular use case on the PostgreSQL database where a CTE is used for a DML statement that runs against database rows independently of the primary statement. Fixes: #6752 Change-Id: Ibf635763e40269cbd10f4c17e208850d8e8d0188
* | Merge "Modernize tests - select(whereclause)"mike bayer2021-07-121-63/+91
|\ \
| * | Modernize tests - select(whereclause)Gord Thompson2021-07-041-63/+91
| |/ | | | | | | Change-Id: I306cfbea9920b35100e3087dcc21d7ffa6c39c55
* | repair schema_translate_map for schema type use casesMike Bayer2021-07-111-0/+48
|/ | | | | | | | | | | | | Fixed issue where the PostgreSQL ``ENUM`` datatype as embedded in the ``ARRAY`` datatype would fail to emit correctly in create/drop when the ``schema_translate_map`` feature were also in use. Additionally repairs a related issue where the same ``schema_translate_map`` feature would not work for the ``ENUM`` datatype in combination with a ``CAST``, that's also intrinsic to how the ``ARRAY(ENUM)`` combination works on the PostgreSQL dialect. Fixes: #6739 Change-Id: I44b1ad4db4af3acbf639aa422c46c22dd3b0d3a6
* apply quoting to "ON CONSTRAINT" symbolMike Bayer2021-06-301-0/+26
| | | | | | | | | | Fixed issue in :meth:`_postgresql.Insert.on_conflict_do_nothing` and :meth:`_postgresql.Insert.on_conflict_do_update` where the name of a unique constraint passed as the ``constraint`` parameter would not be properly quoted if it contained characters which required quoting. Fixes: #6696 Change-Id: I4ffca9b8c72cef4ed39e2de96831ccc11a620422
* Test some complex update cases in the pg dialectFederico Caselli2021-05-271-0/+154
| | | | Change-Id: I2323e155e78aa8e1e00359b103974fb8d27d80eb
* Propertly ignore ``Identity`` in MySQL and MariaDb.Federico Caselli2021-04-281-3/+35
| | | | | | | | | | | | | | Ensure that the MySQL and MariaDB dialect ignore the :class:`_sql.Identity` construct while rendering the ``AUTO_INCREMENT`` keyword in a create table. The Oracle and PostgreSQL compiler was updated to not render :class:`_sql.Identity` if the database version does not support it (Oracle < 12 and PostgreSQL < 10). Previously it was rendered regardless of the database version. Fixes: #6338 Change-Id: I2ca0902fdd7b4be4fc1a563cf5585504cbea9360
* CAST the elements in ARRAYs when using psycopg2Federico Caselli2021-03-151-14/+46
| | | | | | | | | | | | Adjusted the psycopg2 dialect to emit an explicit PostgreSQL-style cast for bound parameters that contain ARRAY elements. This allows the full range of datatypes to function correctly within arrays. The asyncpg dialect already generated these internal casts in the final statement. This also includes support for array slice updates as well as the PostgreSQL-specific :meth:`_postgresql.ARRAY.contains` method. Fixes: #6023 Change-Id: Ia7519ac4371a635f05ac69a3a4d0f4e6d2f04cad
* Deprecate and rename schema .copy() methodsGord Thompson2021-02-181-1/+1
| | | | | Fixes: #5953 Change-Id: I1e69a1628e408f06b43efbc0cc52fc0ad1e8cbc4
* Use schema._copy_expression() fully in column collection constraintsGord Thompson2021-01-291-0/+59
| | | | | | | | | | Fixed issue where using :meth:`_schema.Table.to_metadata` (called :meth:`_schema.Table.tometadata` in 1.3) in conjunction with a PostgreSQL :class:`_postgresql.ExcludeConstraint` that made use of ad-hoc column expressions would fail to copy correctly. Fixes: #5850 Change-Id: I062480afb23f6f60962b7b55bc93f5e4e6ff05e4
* Replace with_labels() and apply_labels() in ORM/CoreGord Thompson2021-01-261-5/+7
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Replace :meth:`_orm.Query.with_labels` and :meth:`_sql.GenerativeSelect.apply_labels` with explicit getters and setters ``get_label_style`` and ``set_label_style`` to accommodate the three supported label styles: ``LABEL_STYLE_DISAMBIGUATE_ONLY`` (default), ``LABEL_STYLE_TABLENAME_PLUS_COL``, and ``LABEL_STYLE_NONE``. In addition, for Core and "future style" ORM queries, ``LABEL_STYLE_DISAMBIGUATE_ONLY`` is now the default label style. This style differs from the existing "no labels" style in that labeling is applied in the case of column name conflicts; with ``LABEL_STYLE_NONE``, a duplicate column name is not accessible via name in any case. For legacy ORM queries using :class:`_query.Query`, the table-plus-column names labeling style applied by ``LABEL_STYLE_TABLENAME_PLUS_COL`` continues to be used so that existing test suites and logging facilities see no change in behavior by default, however this style of labeling is no longer required for SQLAlchemy queries to function, as result sets are commonly matched to columns using a positional approach since SQLAlchemy 1.0. Within test suites, all use of apply_labels() / use_labels now uses the new methods. New tests added to test/sql/test_deprecations.py nad test/orm/test_deprecations.py to cover just the old apply_labels() method call. Tests in ORM that made explicit use apply_labels()/ etc. where it isn't needed for the ORM to work correctly use default label style now. Co-authored-by: Mike Bayer <mike_mp@zzzcomputing.com> Fixes: #4757 Change-Id: I5fdcd2ed4ae8c7fe62f8be2b6d0e8f66409b6a54
* Merge "``Identity`` implies ``nullable=False``."mike bayer2021-01-171-0/+19
|\
| * ``Identity`` implies ``nullable=False``.Federico Caselli2021-01-161-0/+19
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Altered the behavior of the :class:`_schema.Identity` construct such that when applied to a :class:`_schema.Column`, it will automatically imply that the value of :paramref:`_sql.Column.nullable` should default to ``False``, in a similar manner as when the :paramref:`_sql.Column.primary_key` parameter is set to ``True``. This matches the default behavior of all supporting databases where ``IDENTITY`` implies ``NOT NULL``. The PostgreSQL backend is the only one that supports adding ``NULL`` to an ``IDENTITY`` column, which is here supported by passing a ``True`` value for the :paramref:`_sql.Column.nullable` parameter at the same time. Fixes: #5775 Change-Id: I0516d506ff327cff35cda605e8897a27440e0373
* | introduce generalized decorator to prevent invalid method callsMike Bayer2021-01-161-0/+21
|/ | | | | | | | | | | | | | | | | | | This introduces the ``_exclusive_against()`` utility decorator that can be used to prevent repeated invocations of methods that typically should only be called once. An informative error message is now raised for a selected set of DML methods (currently all part of :class:`_dml.Insert` constructs) if they are called a second time, which would implicitly cancel out the previous setting. The methods altered include: :class:`_sqlite.Insert.on_conflict_do_update`, :class:`_sqlite.Insert.on_conflict_do_nothing` (SQLite), :class:`_postgresql.Insert.on_conflict_do_update`, :class:`_postgresql.Insert.on_conflict_do_nothing` (PostgreSQL), :class:`_mysql.Insert.on_duplicate_key_update` (MySQL) Fixes: #5169 Change-Id: I9278fa87cd3470dcf296ff96bb0fb17a3236d49d
* reinvent xdist hooks in terms of pytest fixturesMike Bayer2021-01-131-4/+4
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | To allow the "connection" pytest fixture and others work correctly in conjunction with setup/teardown that expects to be external to the transaction, remove and prevent any usage of "xdist" style names that are hardcoded by pytest to run inside of fixtures, even function level ones. Instead use pytest autouse fixtures to implement our own r"setup|teardown_test(?:_class)?" methods so that we can ensure function-scoped fixtures are run within them. A new more explicit flow is set up within plugin_base and pytestplugin such that the order of setup/teardown steps, which there are now many, is fully documented and controllable. New granularity has been added to the test teardown phase to distinguish between "end of the test" when lock-holding structures on connections should be released to allow for table drops, vs. "end of the test plus its teardown steps" when we can perform final cleanup on connections and run assertions that everything is closed out. From there we can remove most of the defensive "tear down everything" logic inside of engines which for many years would frequently dispose of pools over and over again, creating for a broken and expensive connection flow. A quick test shows that running test/sql/ against a single Postgresql engine with the new approach uses 75% fewer new connections, creating 42 new connections total, vs. 164 new connections total with the previous system. As part of this, the new fixtures metadata/connection/future_connection have been integrated such that they can be combined together effectively. The fixture_session(), provide_metadata() fixtures have been improved, including that fixture_session() now strongly references sessions which are explicitly torn down before table drops occur afer a test. Major changes have been made to the ConnectionKiller such that it now features different "scopes" for testing engines and will limit its cleanup to those testing engines corresponding to end of test, end of test class, or end of test session. The system by which it tracks DBAPI connections has been reworked, is ultimately somewhat similar to how it worked before but is organized more clearly along with the proxy-tracking logic. A "testing_engine" fixture is also added that works as a pytest fixture rather than a standalone function. The connection cleanup logic should now be very robust, as we now can use the same global connection pools for the whole suite without ever disposing them, while also running a query for PostgreSQL locks remaining after every test and assert there are no open transactions leaking between tests at all. Additional steps are added that also accommodate for asyncio connections not explicitly closed, as is the case for legacy sync-style tests as well as the async tests themselves. As always, hundreds of tests are further refined to use the new fixtures where problems with loose connections were identified, largely as a result of the new PostgreSQL assertions, many more tests have moved from legacy patterns into the newest. An unfortunate discovery during the creation of this system is that autouse fixtures (as well as if they are set up by @pytest.mark.usefixtures) are not usable at our current scale with pytest 4.6.11 running under Python 2. It's unclear if this is due to the older version of pytest or how it implements itself for Python 2, as well as if the issue is CPU slowness or just large memory use, but collecting the full span of tests takes over a minute for a single process when any autouse fixtures are in place and on CI the jobs just time out after ten minutes. So at the moment this patch also reinvents a small version of "autouse" fixtures when py2k is running, which skips generating the real fixture and instead uses two global pytest fixtures (which don't seem to impact performance) to invoke the "autouse" fixtures ourselves outside of pytest. This will limit our ability to do more with fixtures until we can remove py2k support. py.test is still observed to be much slower in collection in the 4.6.11 version compared to modern 6.2 versions, so add support for new TOX_POSTGRESQL_PY2K and TOX_MYSQL_PY2K environment variables that will run the suite for fewer backends under Python 2. For Python 3 pin pytest to modern 6.2 versions where performance for collection has been improved greatly. Includes the following improvements: Fixed bug in asyncio connection pool where ``asyncio.TimeoutError`` would be raised rather than :class:`.exc.TimeoutError`. Also repaired the :paramref:`_sa.create_engine.pool_timeout` parameter set to zero when using the async engine, which previously would ignore the timeout and block rather than timing out immediately as is the behavior with regular :class:`.QueuePool`. For asyncio the connection pool will now also not interact at all with an asyncio connection whose ConnectionFairy is being garbage collected; a warning that the connection was not properly closed is emitted and the connection is discarded. Within the test suite the ConnectionKiller is now maintaining strong references to all DBAPI connections and ensuring they are released when tests end, including those whose ConnectionFairy proxies are GCed. Identified cx_Oracle.stmtcachesize as a major factor in Oracle test scalability issues, this can be reset on a per-test basis rather than setting it to zero across the board. the addition of this flag has resolved the long-standing oracle "two task" error problem. For SQL Server, changed the temp table style used by the "suite" tests to be the double-pound-sign, i.e. global, variety, which is much easier to test generically. There are already reflection tests that are more finely tuned to both styles of temp table within the mssql test suite. Additionally, added an extra step to the "dropfirst" mechanism for SQL Server that will remove all foreign key constraints first as some issues were observed when using this flag when multiple schemas had not been torn down. Identified and fixed two subtle failure modes in the engine, when commit/rollback fails in a begin() context manager, the connection is explicitly closed, and when "initialize()" fails on the first new connection of a dialect, the transactional state on that connection is still rolled back. Fixes: #5826 Fixes: #5827 Change-Id: Ib1d05cb8c7cf84f9a4bfd23df397dc23c9329bfe
* Emit deprecation warnings for plain text under SessionMike Bayer2020-12-111-1/+1
| | | | | | | | | | | | | | | | Deprecation warnings are emitted under "SQLALCHEMY_WARN_20" mode when passing a plain string to :meth:`_orm.Session.execute`. It was also considered to have DDL string expressions to include this as well, however this leaves us with no backwards-compatible way of handling reflection of elemens, such as an Index() which reflects "postgresql_where='x > 5'", there's no place for a rule that will turn those into text() within the reflection process that would be separate from when the user passes postgresql_where to the Index. Not worth it right now. Fixes: #5754 Change-Id: I8673a79f0e87de0df576b655f39dad0351725ca8
* Merge "Don't discard leftovers from surface_selectables"mike bayer2020-11-271-0/+20
|\
View Lorry Controller Status