summaryrefslogtreecommitdiff
path: root/test/dialect/postgresql
Commit message (Collapse)AuthorAgeFilesLines
* Add future=True to create_engine/Session; unify select()Mike Bayer2020-07-085-108/+95
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Several weeks of using the future_select() construct has led to the proposal there be just one select() construct again which features the new join() method, and otherwise accepts both the 1.x and 2.x argument styles. This would make migration simpler and reduce confusion. However, confusion may be increased by the fact that select().join() is different Current thinking is we may be better off with a few hard behavioral changes to old and relatively unknown APIs rather than trying to play both sides within two extremely similar but subtly different APIs. At the moment, the .join() thing seems to be the only behavioral change that occurs without the user taking any explicit steps. Session.execute() will still behave the old way as we are adding a future flag. This change also adds the "future" flag to Session() and session.execute(), so that interpretation of the incoming statement, as well as that the new style result is returned, does not occur for existing applications unless they add the use of this flag. The change in general is moving the "removed in 2.0" system further along where we want the test suite to fully pass even if the SQLALCHEMY_WARN_20 flag is set. Get many tests to pass when SQLALCHEMY_WARN_20 is set; this should be ongoing after this patch merges. Improve the RemovedIn20 warning; these are all deprecated "since" 1.4, so ensure that's what the messages read. Make sure the inforamtion link is on all warnings. Add deprecation warnings for parameters present and add warnings to all FromClause.select() types of methods. Fixes: #5379 Fixes: #5284 Change-Id: I765a0b912b3dcd0e995426427d8bb7997cbffd51 References: #5159
* Rename Table.tometadata to to_metadataGord Thompson2020-06-291-1/+1
| | | | | | | | | | | | | | | | Renamed the :meth:`_schema.Table.tometadata` method to :meth:`_schema.Table.to_metadata`. The previous name remains with a deprecation warning. Updated the "decorate" utility function to support decoration of functions that include non-builtins as default values. Moves test for deprecated "databases" package into test/dialect/test_deprecations.py Fixes: #5413 Fixes: #5426 Change-Id: I6ed899871c935f9e46360127c17ccb7cf97cea6e
* ORM executemany returningMike Bayer2020-06-271-40/+115
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | Build on #5401 to allow the ORM to take advanage of executemany INSERT + RETURNING. Implemented the feature updated tests to support INSERT DEFAULT VALUES, needed to come up with a new syntax for compiler INSERT INTO table (anycol) VALUES (DEFAULT) which can then be iterated out for executemany. Added graceful degrade to plain executemany for PostgreSQL <= 8.2 Renamed EXECUTEMANY_DEFAULT to EXECUTEMANY_PLAIN Fix issue where unicode identifiers or parameter names wouldn't work with execute_values() under Py2K, because we have to encode the statement and therefore have to encode the insert_single_values_expr too. Correct issue from #5401 to support executemany + return_defaults for a PK that is explicitly pre-generated, meaning we aren't actually getting RETURNING but need to return it from compiled_parameters. Fixes: #5263 Change-Id: Id68e5c158c4f9ebc33b61c06a448907921c2a657
* Default psycopg2 executemany mode to "values_only"Mike Bayer2020-06-256-236/+224
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | The psycopg2 dialect now defaults to using the very performant ``execute_values()`` psycopg2 extension for compiled INSERT statements, and also impements RETURNING support when this extension is used. This allows INSERT statements that even include an autoincremented SERIAL or IDENTITY value to run very fast while still being able to return the newly generated primary key values. The ORM will then integrate this new feature in a separate change. Implements RETURNING for insert with executemany Adds support to return_defaults() mode and inserted_primary_key to support mutiple INSERTed rows, via return_defauls_rows and inserted_primary_key_rows accessors. within default execution context, new cached compiler getters are used to fetch primary keys from rows inserted_primary_key now returns a plain tuple. this is not yet a row-like object however this can be added. Adds distinct "values_only" and "batch" modes, as "values" has a lot of benefits but "batch" breaks cursor.rowcount psycopg2 minimum version 2.7 so we can remove the large number of checks for very old versions of psycopg2 simplify tests to no longer distinguish between native and non-native json Fixes: #5401 Change-Id: Ic08fd3423d4c5d16ca50994460c0c234868bd61c
* Turn on caching everywhere, add loggingMike Bayer2020-06-101-16/+3
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | A variety of caching issues found by running all tests with statement caching turned on. The cache system now has a more conservative approach where any subclass of a SQL element will by default invalidate the cache key unless it adds the flag inherit_cache=True at the class level, or if it implements its own caching. Add working caching to a few elements that were omitted previously; fix some caching implementations to suit lesser used edge cases such as json casts and array slices. Refine the way BaseCursorResult and CursorMetaData interact with caching; to suit cases like Alembic modifying table structures, don't cache the cursor metadata if it were created against a cursor.description using non-positional matching, e.g. "select *". if a table re-ordered its columns or added/removed, now that data is obsolete. Additionally we have to adapt the cursor metadata _keymap regardless of if we just processed cursor.description, because if we ran against a cached SQLCompiler we won't have the right columns in _keymap. Other refinements to how and when we do this adaption as some weird cases were exposed in the Postgresql dialect, a text() construct that names just one column that is not actually in the statement. Fixed that also as it looks like a cut-and-paste artifact that doesn't actually affect anything. Various issues with re-use of compiled result maps and cursor metadata in conjunction with tables being changed, such as change in order of columns. mappers can be cleared but the class remains, meaning a mapper has to use itself as the cache key not the class. lots of bound parameter / literal issues, due to Alembic creating a straight subclass of bindparam that renders inline directly. While we can update Alembic to not do this, we have to assume other people might be doing this, so bindparam() implements the inherit_cache=True logic as well that was a bit involved. turn on cache stats in logging. Includes a fix to subqueryloader which moves all setup to the create_row_processor() phase and elminates any storage within the compiled context. This includes some changes to create_row_processor() signature and a revising of the technique used to determine if the loader can participate in polymorphic queries, which is also applied to selectinloading. DML update.values() and ordered_values() now coerces the keys as we have tests that pass an arbitrary class here which only includes __clause_element__(), so the key can't be cached unless it is coerced. this in turn changed how composite attributes support bulk update to use the standard approach of ClauseElement with annotations that are parsed in the ORM context. memory profiling successfully caught that the Session from Query was getting passed into _statement_20() so that was a big win for that test suite. Apparently Compiler had .execute() and .scalar() methods stuck on it, these date back to version 0.4 and there was a single test in the PostgreSQL dialect tests that exercised it for no apparent reason. Removed these methods as well as the concept of a Compiler holding onto a "bind". Fixes: #5386 Change-Id: I990b43aab96b42665af1b2187ad6020bee778784
* Default create_constraint to FalseMike Bayer2020-06-032-5/+36
| | | | | | | | | | | | The :paramref:`.Enum.create_constraint` and :paramref:`.Boolean.create_constraint` parameters now default to False, indicating when a so-called "non-native" version of these two datatypes is created, a CHECK constraint will not be generated by default. These CHECK constraints present schema-management maintenance complexities that should be opted in to, rather than being turned on by default. Fixes: #5367 Change-Id: I0a3fb608ce32143fa757546cc17ba2013e93272a
* Add with_for_update mysql new functionalitiesRobotScribe2020-05-151-0/+66
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Fixes: #4860 # Description Add nowait, skip_lock, of arguments to for_update_clause for mysql ### Checklist This pull request is: - [ ] A documentation / typographical error fix - Good to go, no issue or tests are needed - [ ] A short code fix - please include the issue number, and create an issue if none exists, which must include a complete example of the issue. one line code fixes without an issue and demonstration will not be accepted. - Please include: `Fixes: #<issue number>` in the commit message - please include tests. one line code fixes without tests will not be accepted. - [x] A new feature implementation - please include the issue number, and create an issue if none exists, which must include a complete example of how the feature would look. - Please include: `Fixes: #<issue number>` in the commit message - please include tests. **Have a nice day!** Closes: #5290 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/5290 Pull-request-sha: 490e822e73e92ffe63cf45df9c49f3b31af1954d Change-Id: Ibd2acc47b538c601c69c8fb954776035ecab4c6c
* Support `ARRAY` of `Enum`, `JSON` or `JSONB`Federico Caselli2020-04-201-0/+140
| | | | | | | | | | | | | | Added support for columns or type :class:`.ARRAY` of :class:`.Enum`, :class:`.JSON` or :class:`_postgresql.JSONB` in PostgreSQL. Previously a workaround was required in these use cases. Raise an explicit :class:`.exc.CompileError` when adding a table with a column of type :class:`.ARRAY` of :class:`.Enum` configured with :paramref:`.Enum.native_enum` set to ``False`` when :paramref:`.Enum.create_constraint` is not set to ``False`` Fixes: #5265 Fixes: #5266 Change-Id: I83a2d20a599232b7066d0839f3e55ff8b78cd8fc
* Pass connection to TablesTest.insert_data()Mike Bayer2020-04-152-19/+18
| | | | | | | | | | towards the goal of reducing verbosity and repetition in test fixtures as well as that we are moving to connection only for execution, move the insert_data() classmethod to accept a connection and adjust all fixtures to use it. Change-Id: I3bf534acca0d5f4cda1d4da8ae91f1155b829b09
* Remove code deprecated before version 1.1Federico Caselli2020-04-091-9/+0
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | - Remove deprecated method ``get_primary_keys` in the :class:`.Dialect` and :class:`.Inspector` classes. - Remove deprecated event ``dbapi_error`` and the method ``ConnectionEvents.dbapi_error`. - Remove support for deprecated engine URLs of the form ``postgres://``. - Remove deprecated dialect ``mysql+gaerdbms``. - Remove deprecated parameter ``quoting`` from :class:`.mysql.ENUM` and :class:`.mysql.SET` in the ``mysql`` dialect. - Remove deprecated function ``comparable_property``. and function ``comparable_using`` in the declarative extension. - Remove deprecated function ``compile_mappers``. - Remove deprecated method ``collection.linker``. - Remove deprecated method ``Session.prune`` and parameter ``Session.weak_identity_map``. This change also removes the class ``StrongInstanceDict``. - Remove deprecated parameter ``mapper.order_by``. - Remove deprecated parameter ``Session._enable_transaction_accounting`. - Remove deprecated parameter ``Session.is_modified.passive``. - Remove deprecated class ``Binary``. Please use :class:`.LargeBinary`. - Remove deprecated methods ``Compiled.compile``, ``ClauseElement.__and__`` and ``ClauseElement.__or__`` and attribute ``Over.func``. - Remove deprecated ``FromClause.count`` method. - Remove deprecated parameter ``Table.useexisting``. - Remove deprecated parameters ``text.bindparams`` and ``text.typemap``. - Remove boolean support for the ``passive`` parameter in ``get_history``. - Remove deprecated ``adapt_operator`` in ``UserDefinedType.Comparator``. Fixes: #4643 Change-Id: Idcd390c77bf7b0e9957907716993bdaa3f1a1763
* Clean up .execute calls in PostgreSQL testsGord Thompson2020-03-315-482/+508
| | | | | Fixes: #5220 Change-Id: I789e45dffc2b177ebb15ea3268bb965be8b06397
* Convert schema_translate to a post compileMike Bayer2020-03-241-0/+3
| | | | | | | | | | | | | | | | Revised the :paramref:`.Connection.execution_options.schema_translate_map` feature such that the processing of the SQL statement to receive a specific schema name occurs within the execution phase of the statement, rather than at the compile phase. This is to support the statement being efficiently cached. Previously, the current schema being rendered into the statement for a particular run would be considered as part of the cache key itself, meaning that for a run against hundreds of schemas, there would be hundreds of cache keys, rendering the cache much less performant. The new behavior is that the rendering is done in a similar manner as the "post compile" rendering added in 1.4 as part of :ticket:`4645`, :ticket:`4808`. Fixes: #5004 Change-Id: Ia5c89eb27cc8dc2c5b8e76d6c07c46290a7901b6
* Deprecate plain string in execute and introduce `exec_driver_sql`Federico Caselli2020-03-212-65/+70
| | | | | | | | | | | | | | | Execution of literal sql string is deprecated in the :meth:`.Connection.execute` and a warning is raised when used stating that it will be coerced to :func:`.text` in a future release. To execute a raw sql string the new connection method :meth:`.Connection.exec_driver_sql` was added, that will retain the previous behavior, passing the string to the DBAPI driver unchanged. Usage of scalar or tuple positional parameters in :meth:`.Connection.execute` is also deprecated. Fixes: #4848 Fixes: #5178 Change-Id: I2830181054327996d594f7f0d59c157d477c3aa9
* Don't include PG INCLUDE columns as regular index columnsmike bayer2020-03-181-0/+32
| | | | | | | | | | | | | | | | Fixed issue where a "covering" index, e.g. those which have an INCLUDE clause, would be reflected including all the columns in INCLUDE clause as regular columns. A warning is now emitted if these additional columns are detected indicating that they are currently ignored. Note that full support for "covering" indexes is part of :ticket:`4458`. Pull request courtesy Marat Sharafutdinov. Fixes: #5205 Closes: #5206 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/5206 Pull-request-sha: 512a3817bb21991142add2d192fa7ce9b285369d Change-Id: I3196a2bf77dc5a6abd85b2fbf0ebff1b30d4fb00
* Support inspection of computed columnFederico Caselli2020-03-151-1/+1
| | | | | | | | | | | | | | | | | | | Added support for reflection of "computed" columns, which are now returned as part of the structure returned by :meth:`.Inspector.get_columns`. When reflecting full :class:`.Table` objects, computed columns will be represented using the :class:`.Computed` construct. Also improve the documentation in :meth:`Inspector.get_columns`, correctly listing all the returned keys. Fixes: #5063 Fixes: #4051 Closes: #5064 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/5064 Pull-request-sha: ba00fc321ce468f8885aad23b3dd33c789e50fbe Change-Id: I789986554fc8ac7f084270474d0b2c12046b1cc2
* Decouple compiler state from DML objects; make cacheableMike Bayer2020-03-061-2/+6
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Targeting select / insert / update / delete, the goal is to minimize overhead of construction and generative methods so that only the raw arguments passed are handled. An interim stage that converts the raw state into more compiler-ready state is added, which is analogous to the ORM QueryContext which will also be rolled in to be a similar concept, as is currently being prototyped in I19e05b3424b07114cce6c439b05198ac47f7ac10. the ORM update/delete BulkUD concept is also going to be rolled onto this idea. So while the compiler-ready state object, here called DMLState, looks a little thin, it's the base of a bigger pattern that will allow for ORM functionality to embed itself directly into the compiler, execution context, and result set objects. This change targets the DML objects, primarily focused on the values() method which is the most complex process. The work done by values() is minimized as much as possible while still being able to create a cache key. Additional computation is then offloaded to a new object ValuesState that is handled by the compiler. Architecturally, a big change here is that insert.values() and update.values() will generate BindParameter objects for the values now, which are then carefully received by crud.py so that they generate the expected names. This is so that the values() portion of these constructs is cacheable. for the "multi-values" version of Insert, this is all skipped and the plan right now is that a multi-values insert is not worth caching (can always be revisited). Using the coercions system in values() also gets us nicer validation for free, we can remove the NotAClauseElement thing from schema, and we also now require scalar_subquery() is called for an insert/update that uses a SELECT as a column value, 1.x deprecation path is added. The traversal system is then applied to the DML objects including tests so that they have traversal, cloning, and cache key support. cloning is not a use case for DML however having it present allows better validation of the structure within the tests. Special per-dialect DML is explicitly not cacheable at the moment, more as a proof of concept that third party DML constructs can exist as gracefully not-cacheable rather than producing an incomplete cache key. A few selected performance improvements have been added as well, simplifying the immutabledict.union() method and adding a new SQLCompiler function that can generate delimeter-separated clauses like WHERE and ORDER BY without having to build a ClauseList object at all. The use of ClauseList will be removed from Select in an upcoming commit. Overall, ClaustList is unnecessary for internal use and only adds overhead to statement construction and will likely be removed as much as possible except for explcit use of conjunctions like and_() and or_(). Change-Id: I408e0b8be91fddd77cf279da97f55020871f75a9
* Merge "Deprecate the databases packages"mike bayer2020-03-021-4/+4
|\
| * Deprecate the databases packagesFederico Caselli2020-02-281-4/+4
| | | | | | | | | | | | | | The databases package is an old alias to the dialect package, and its usage is now deprecated Change-Id: I5343a2d270ed5a8c654b9fe13dff40cdf54649ed
* | While parsing for check constraints, ignore newline characters in the check ↵Eric Borczuk2020-02-291-0/+37
|/ | | | | | | | | | | | | | | | condition Fixed bug where PostgreSQL reflection of CHECK constraints would fail to parse the constraint if the SQL text contained newline characters. The regular expression has been adjusted to accommodate for this case. Pull request courtesy Eric Borczuk. Fixes: #5170 Closes: #5172 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/5172 Pull-request-sha: 5701b7f09f723b727bbee95d19d107d6cc1d7717 Change-Id: If727e9140b645e8b685c3476fb0fa4417c1e6526
* Result initial introductionMike Bayer2020-02-211-7/+7
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | This builds on cc718cccc0bf8a01abdf4068c7ea4f3 which moved RowProxy to Row, allowing Row to be more like a named tuple. - KeyedTuple in ORM is replaced with Row - ResultSetMetaData broken out into "simple" and "cursor" versions for ORM and Core, as well as LegacyCursor version. - Row now has _mapping attribute that supplies full mapping behavior. Row and SimpleRow both have named tuple behavior otherwise. LegacyRow has some mapping features on the tuple which emit deprecation warnings (e.g. keys(), values(), etc). the biggest change for mapping->tuple is the behavior of __contains__ which moves from testing of "key in row" to "value in row". - ResultProxy breaks into ResultProxy and FutureResult (interim), the latter has the newer APIs. Made available to dialects using execution options. - internal reflection methods and most tests move off of implicit Row mapping behavior and move to row._mapping, result.mappings() method using future result - a new strategy system for cursor handling replaces the various subclasses of RowProxy - some execution context adjustments. We will leave EC in but refined things like get_result_proxy() and out parameter handling. Dialects for 1.4 will need to adjust from get_result_proxy() to get_result_cursor_strategy(), if they are using this method - out parameter handling now accommodated by get_out_parameter_values() EC method. Oracle changes for this. external dialect for DB2 for example will also need to adjust for this. - deprecate case_insensitive flag for engine / result, this feature is not used mapping-methods on Row are deprecated, and replaced with Row._mapping.<meth>, including: row.keys() -> use row._mapping.keys() row.items() -> use row._mapping.items() row.values() -> use row._mapping.values() key in row -> use key in row._mapping int in row -> use int < len(row) Fixes: #4710 Fixes: #4878 Change-Id: Ieb9085e9bcff564359095b754da9ae0af55679f0
* Deprecate connection branchingMike Bayer2020-02-211-5/+4
| | | | | | | | | | | | | | | The :meth:`.Connection.connect` method is deprecated as is the concept of "connection branching", which copies a :class:`.Connection` into a new one that has a no-op ".close()" method. This pattern is oriented around the "connectionless execution" concept which is also being removed in 2.0. As part of this change we begin to move the internals away from "connectionless execution" overall. Remove the "connectionless execution" concept from the reflection internals and replace with explicit patterns at the Inspector level. Fixes: #5131 Change-Id: Id23d28a9889212ac5ae7329b85136157815d3e6f
* Pass DDLCompiler IdentifierPreparer to visit_ENUMMike Bayer2020-02-172-0/+68
| | | | | | | | | | | | Fixed issue where the "schema_translate_map" feature would not work with a PostgreSQL native enumeration type (i.e. :class:`.Enum`, :class:`.postgresql.ENUM`) in that while the "CREATE TYPE" statement would be emitted with the correct schema, the schema would not be rendered in the CREATE TABLE statement at the point at which the enumeration was referenced. Fixes: #5158 Change-Id: I41529785de2e736c70a142c2ae5705060bfed73e
* Add pyprojectFederico Caselli2020-01-271-4/+4
| | | | | | | | | | | | | - Added pyproject.toml with black arguments - Updated black version in precommit hook - Reformatted the code Fixes: #5100 Closes: #5103 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/5103 Pull-request-sha: 795fd5f896be4a07a2b18e6525674b815ac17593 Change-Id: I14eedbaa51fb531cbf90fcefe6a1e07c8a565625
* Improve regex parsing of CHECK constraints for PostgreSQL.Gord Thompson2020-01-181-3/+21
| | | | | | | | | | | | | Fixed issue where the PostgreSQL dialect would fail to parse a reflected CHECK constraint that was a boolean-valued function (as opposed to a boolean-valued expression). Fixes: #5039 Closes: #5044 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/5044 Pull-request-sha: b6903c656422abf658c4cc88b8cd03291d3a50f8 Change-Id: I7d39b104a8ce346cb593d541c1b4e5eab88867f9
* Remove jython code, remove all jython / pypy symbolsMike Bayer2020-01-173-34/+3
| | | | | | | | | | | | | | | | | | | | | | Removed all dialect code related to support for Jython and zxJDBC. Jython has not been supported by SQLAlchemy for many years and it is not expected that the current zxJDBC code is at all functional; for the moment it just takes up space and adds confusion by showing up in documentation. At the moment, it appears that Jython has achieved Python 2.7 support in its releases but not Python 3. If Jython were to be supported again, the form it should take is against the Python 3 version of Jython, and the various zxJDBC stubs for various backends should be implemented as a third party dialect. Additionally modernized logic that distinguishes between "cpython" and "pypy" to instead look at platform.python_distribution() which reliably tells us if we are cPython or not; all booleans which previously checked for pypy and sometimes jython are now converted to be "not cpython", this impacts the test suite for tests that are cPython centric. Fixes: #5094 Change-Id: I226cb55827f997daf6b4f4a755c18e7f4eb8d9ad
* Add type accessors for JSON indexed/pathed element accessMike Bayer2019-11-111-0/+13
| | | | | | | | | | | | | | | Added new accessors to expressions of type :class:`.JSON` to allow for specific datatype access and comparison, covering strings, integers, numeric, boolean elements. This revises the documented approach of CASTing to string when comparing values, instead adding specific functionality into the PostgreSQL, SQlite, MySQL dialects to reliably deliver these basic types in all cases. The change also delivers a new feature to the test exclusions system so that combinations and exclusions can be used together. Fixes: #4276 Change-Id: Ica5a926c060feb40a0a7cd60b9d6e061d7825728
* Support for generated columnsCaselIT2019-11-081-0/+37
| | | | | | | | | | | | | | | | | | | | | | | | | | Added DDL support for "computed columns"; these are DDL column specifications for columns that have a server-computed value, either upon SELECT (known as "virtual") or at the point of which they are INSERTed or UPDATEd (known as "stored"). Support is established for Postgresql, MySQL, Oracle SQL Server and Firebird. Thanks to Federico Caselli for lots of work on this one. ORM round trip tests included. The ORM makes use of existing FetchedValue support and no additional ORM logic is present for the basic feature. It has been observed that Oracle RETURNING does not return the new value of a computed column upon UPDATE; it returns the prior value. As this is very dangerous, a warning is emitted if a computed column is rendered into the RETURNING clause of an UPDATE statement. Fixes: #4894 Closes: #4928 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/4928 Pull-request-sha: d39c521d5ac6ebfb4fb5b53846451de79752e64c Change-Id: I2610b2999a5b1b127ed927dcdaeee98b769643ce
* Cache every key in reflection_cacheMike Bayer2019-10-311-0/+11
| | | | | | | | | | | | | Fixed bug in :class:`.Inspector` where the cache key generation did not take into account arguments passed in the form of tuples, such as the tuple of view name styles to return for the PostgreSQL dialect. This would lead the inspector to cache too generally for a more specific set of criteria. The logic has been adjusted to include every keyword element in the cache, as every argument is expected to be appropriate for a cache else the caching decorator should be bypassed by the dialect. Fixes: #4955 Change-Id: Icd97744323407977a3f7db26e8a63a1a5c576010
* Refactor dialect tests for combinationsMike Bayer2019-10-221-351/+234
| | | | | | | | | | | | | | Dialect tests tend to have a lot of lists of types, SQL constructs etc, convert as many of these to @combinations as possible. This is exposing that we don't have per-combination exclusion rules set up which is making things a little bit cumbersome. Also set up a fixture that does metadata + DDL. Change-Id: Ief820e48c9202982b0b1e181b87862490cd7b0c3
* Render LIMIT/OFFSET conditions after compile on select dialectsMike Bayer2019-08-301-1/+1
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | Added new "post compile parameters" feature. This feature allows a :func:`.bindparam` construct to have its value rendered into the SQL string before being passed to the DBAPI driver, but after the compilation step, using the "literal render" feature of the compiler. The immediate rationale for this feature is to support LIMIT/OFFSET schemes that don't work or perform well as bound parameters handled by the database driver, while still allowing for SQLAlchemy SQL constructs to be cacheable in their compiled form. The immediate targets for the new feature are the "TOP N" clause used by SQL Server (and Sybase) which does not support a bound parameter, as well as the "ROWNUM" and optional "FIRST_ROWS()" schemes used by the Oracle dialect, the former of which has been known to perform better without bound parameters and the latter of which does not support a bound parameter. The feature builds upon the mechanisms first developed to support "expanding" parameters for IN expressions. As part of this feature, the Oracle ``use_binds_for_limits`` feature is turned on unconditionally and this flag is now deprecated. - adds limited support for "unique" bound parameters within a text() construct. - adds an additional int() check within the literal render function of the Integer datatype and tests that non-int values raise ValueError. Fixes: #4808 Change-Id: Iace97d544d1a7351ee07db970c6bc06a19c712c6
* PGDialect.get_check_constraints: Handle "NOT VALID"Bill Finn2019-08-271-2/+27
| | | | | | | | | | | | | | | | | | Added support for reflection of CHECK constraints that include the special PostgreSQL qualifier "NOT VALID", which can be present for CHECK constraints that were added to an exsiting table with the directive that they not be applied to existing data in the table. The PostgreSQL dictionary for CHECK constraints as returned by :meth:`.Inspector.get_check_constraints` may include an additional entry ``dialect_options`` which within will contain an entry ``"not_valid": True`` if this symbol is detected. Pull request courtesy Bill Finn. Fixes: #4824 Closes: #4825 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/4825 Pull-request-sha: a0e1ab133c2d46521a74e55423ac2ba866682dae Change-Id: I78365f50055c95474c92124b85df66c5c80c00c8
* Remove erroneous assertion from array._bind_paramMike Bayer2019-08-261-0/+77
| | | | | | | | | | | Fixed bug where Postgresql operators such as :meth:`.postgresql.ARRAY.Comparator.contains` and :meth:`.postgresql.ARRAY.Comparator.contained_by` would fail to function correctly for non-integer values when used against a :class:`.postgresql.array` object, due to an erroneous assert statement. Fixes: #4822 Change-Id: I886aca4c86dc7d64e9d0dfc1d910a0ae64d775a1
* Revise psycopg2 execute_values approachMike Bayer2019-08-181-15/+163
| | | | | | | | | | | | Revised the approach for the just added support for the psycopg2 "execute_values()" feature added in 1.3.7 for :ticket:`4623`. The approach relied upon a regular expression that would fail to match for a more complex INSERT statement such as one which had subqueries involved. The new approach matches exactly the string that was rendered as the VALUES clause. Fixes: #4623 Change-Id: Icaae0f7b6bcf87a2cf5c6290a839c8429dd5fac3
* Add new executemany_mode, support for psycopg2.extras.execute_values()Yuval Dinari2019-08-121-9/+193
| | | | | | | | | | | | | | | | | | | Added new dialect flag for the psycopg2 dialect, ``executemany_mode`` which supersedes the previous experimental ``use_batch_mode`` flag. ``executemany_mode`` supports both the "execute batch" and "execute values" functions provided by psycopg2, the latter which is used for compiled :func:`.insert` constructs. Pull request courtesy Yuval Dinari. .. seealso:: :ref:`executemany_mode` Fixes: #4623 Closes: #4764 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/4764 Pull-request-sha: c3d3a36f7eb66c86d14ed9c1c31b4b48bd204855 Change-Id: I77e26ca729f9317af1488a6c054c23fa1a6b622b
* Modernize internal reflectionMike Bayer2019-08-061-5/+5
| | | | | | | | | | | | | | | | | | | | | | | - Deprecated remaining engine-level introspection and utility methods including :meth:`.Engine.run_callable`, :meth:`.Engine.transaction`, :meth:`.Engine.table_names`, :meth:`.Engine.has_table`. The utility methods are superseded by modern context-manager patterns, and the table introspection tasks are suited by the :class:`.Inspector` object. - The internal dialect method ``Dialect.reflecttable`` has been removed. A review of third party dialects has not found any making use of this method, as it was already documented as one that should not be used by external dialects. Additionally, the private ``Engine._run_visitor`` method is also removed. - The long-deprecated ``Inspector.get_table_names.order_by`` parameter has been removed. - The :paramref:`.Table.autoload_with` parameter now accepts an :class:`.Inspector` object directly, as well as any :class:`.Engine` or :class:`.Connection` as was the case before. Fixes: #4755 Change-Id: Iec3a8b0f3e298ba87d532b16fac1e1132f464e21
* Include 'p' for get_indexes() queryMike Bayer2019-07-191-2/+20
| | | | | | | | Added support for reflection of indexes on PostgreSQL partitioned tables, which was added to PostgreSQL as of version 11. Fixes: #4771 Change-Id: I9e8e75c4d8a667b4d52d12afbd384e0a8db00466
* self_group() for FunctionFilterMike Bayer2019-07-121-0/+15
| | | | | | | | | Fixed issue where the :class:`.array_agg` construct in combination with :meth:`.FunctionElement.filter` would not produce the correct operator precedence between the FILTER keyword and the array index operator. Fixes: #4760 Change-Id: Ic662cd3da3330554ec673bafd80495b3f1506098
* Support multidimensional array literals in PostgresqlMike Bayer2019-07-081-1/+111
| | | | | | | | | Added support for multidimensional Postgresql array literals via nesting the :class:`.postgresql.array` object within another one. The multidimensional array type is detected automatically. Fixes: #4756 Change-Id: Ie2107ad3cf291112f6ca330dc90dc15a0a940cee
* Reflect "NO ACTION" as None; support "RESTRICT"Mike Bayer2019-06-261-7/+1
| | | | | | | | | | | | | | The "NO ACTION" keyword for foreign key "ON UPDATE" is now considered to be the default cascade for a foreign key on all supporting backends (SQlite, MySQL, PostgreSQL) and when detected is not included in the reflection dictionary; this is already the behavior for PostgreSQL and MySQL for all previous SQLAlchemy versions in any case. The "RESTRICT" keyword is positively stored when detected; PostgreSQL does report on this keyword, and MySQL as of version 8.0 does as well. On earlier MySQL versions, it is not reported by the database. Fixes: #4741 Change-Id: I6becf1f2450605c1991158bb8a04d954dcc7396c
* Enable F841Mike Bayer2019-06-202-18/+16
| | | | | | | | | | | This is a very useful assertion which prevents unused variables from being set up allows code to be more readable and sometimes even more efficient. test suites seem to be where the most problems are and there do not seem to be documentation examples that are using this, or at least the linter is not taking effect within rst blocks. Change-Id: I2b3341d8dd14da34879d8425838e66a4b9f8e27d
* PostgreSQL now reflects per-column sort order on indexes.Eli Collins2019-06-171-0/+77
| | | | | | | | | | | | | | Added support for column sorting flags when reflecting indexes for PostgreSQL, including ASC, DESC, NULLSFIRST, NULLSLAST. Also adds this facility to the reflection system in general which can be applied to other dialects in future releases. Pull request courtesy Eli Collins. Fixes: #4717 Closes: #4725 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/4725 Pull-request-sha: 3cbb067bd46776fdb125553ba0ac192cb45d060c Change-Id: I8b0617d68580cfe4ff79d758a077263f33e852c2
* psycopg2 NOTICE fixupMike Bayer2019-06-091-6/+19
| | | | | | | | | | | - don't call relatively expensive isEnabledFor(), just call _log_notices - don't reset the list if it's empty - fix the test to use a custom function to definitely create a notice, confirmed that PG seems to no longer create the "implicit sequence" notices - assert that the reset of the notices works too - update the docs to illustrate for folks who haven't worked with logging before Change-Id: I7291e647c177d338e0ad673f3106b4d503e4b3ea
* PostgreSQL enum with no elements returns NULL for the "label", skip thisMike Bayer2019-05-301-0/+27
| | | | | | | | | | Fixed bug where PostgreSQL dialect could not correctly reflect an ENUM datatype that has no members, returning a list with ``None`` for the ``get_enums()`` call and raising a TypeError when reflecting a column which has such a datatype. The inspection now returns an empty list. Fixes: #4701 Change-Id: I202bab19728862cbc64deae211d5ba6a103b8317
* Move initialize do_rollback() outside of the dialectMatthew Wilkes2019-05-091-0/+9
| | | | | | | | | | | | | | | | Moved the "rollback" which occurs during dialect initialization so that it occurs after additional dialect-specific initialize steps, in particular those of the psycopg2 dialect which would inadvertently leave transactional state on the first new connection, which could interfere with some psycopg2-specific APIs which require that no transaction is started. Pull request courtesy Matthew Wilkes. Fixes: #4663 Closes: #4664 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/4664 Pull-request-sha: e544fe671d443ed06b210ba1cd1d7ba9c5653831 Change-Id: If40a15a1679b4eec0b8b8222f678697728009c30
* Black updatesMike Bayer2019-04-111-1/+1
| | | | | | Updating for a few black adjustments Change-Id: I9c8abadd1ccd6173e6d68dd62f14cec208cc304a
* Propagate query-arg-only URL to psycopg2; don't send blank hostMike Bayer2019-04-091-0/+21
| | | | | | | | | | | | | | Fixed regression from release 1.3.2 caused by :ticket:`4562` where a URL that contained only a query string and no hostname, such as for the purposes of specifying a service file with connection information, would no longer be propagated to psycopg2 properly. The change in :ticket:`4562` has been adjusted to further suit psycopg2's exact requirements, which is that if there are any connection parameters whatsoever, the "dsn" parameter is no longer required, so in this case the query string parameters are passed alone. Fixes: #4601 Change-Id: Ic29a8b77bcf50ee996968bab25aaac7ae4bfc26f
* Support DNS-less connections for psycopg2Julian Mehnle2019-03-241-0/+15
| | | | | | | | | | | | | | | | Added support for parameter-less connection URLs for the psycopg2 dialect, meaning, the URL can be passed to :func:`.create_engine` as ``"postgresql+psycopg2://"`` with no additional arguments to indicate an empty DSN passed to libpq, which indicates to connect to "localhost" with no username, password, or database given. Pull request courtesy Julian Mehnle. Fixes: #4562 Closes: #4563 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/4563 Pull-request-sha: 8a05c96944a0523b06e9772cfe1832e500a43641 Change-Id: Ib6fca3c3c9eebeaf590d7f7fb0bc8cd4b6e4a55a
* Expand joins when calculating PostgreSQL "WITH FOR UPDATE OF"raylu2019-03-211-0/+24
| | | | | | | | | | | | | | | | Modified the :paramref:`.Select.with_for_update.of` parameter so that if a join or other composed selectable is passed, the individual :class:`.Table` objects will be filtered from it, allowing one to pass a join() object to the parameter, as occurs normally when using joined table inheritance with the ORM. Pull request courtesy Raymond Lu. Fixes: #4550 Co-authored-by: Mike Bayer <mike_mp@zzzcomputing.com> Closes: #4551 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/4551 Pull-request-sha: 452da77d154a4087d530456db1c9af207d65cef4 Change-Id: If4b7c231f7b71190d7245543959fb5c3351125a1
* Remove all remaining text() coercions and ensure identifiers are safeMike Bayer2019-02-061-0/+55
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Fully removed the behavior of strings passed directly as components of a :func:`.select` or :class:`.Query` object being coerced to :func:`.text` constructs automatically; the warning that has been emitted is now an ArgumentError or in the case of order_by() / group_by() a CompileError. This has emitted a warning since version 1.0 however its presence continues to create concerns for the potential of mis-use of this behavior. Note that public CVEs have been posted for order_by() / group_by() which are resolved by this commit: CVE-2019-7164 CVE-2019-7548 Added "SQL phrase validation" to key DDL phrases that are accepted as plain strings, including :paramref:`.ForeignKeyConstraint.on_delete`, :paramref:`.ForeignKeyConstraint.on_update`, :paramref:`.ExcludeConstraint.using`, :paramref:`.ForeignKeyConstraint.initially`, for areas where a series of SQL keywords only are expected.Any non-space characters that suggest the phrase would need to be quoted will raise a :class:`.CompileError`. This change is related to the series of changes committed as part of :ticket:`4481`. Fixed issue where using an uppercase name for an index type (e.g. GIST, BTREE, etc. ) or an EXCLUDE constraint would treat it as an identifier to be quoted, rather than rendering it as is. The new behavior converts these types to lowercase and ensures they contain only valid SQL characters. Quoting is applied to :class:`.Function` names, those which are usually but not necessarily generated from the :attr:`.sql.func` construct, at compile time if they contain illegal characters, such as spaces or punctuation. The names are as before treated as case insensitive however, meaning if the names contain uppercase or mixed case characters, that alone does not trigger quoting. The case insensitivity is currently maintained for backwards compatibility. Fixes: #4481 Fixes: #4473 Fixes: #4467 Change-Id: Ib22a27d62930e24702e2f0f7c74a0473385a08eb
* Use pg_get_constraintdef for CHECK constraint reflectionMike Bayer2019-01-241-0/+15
| | | | | | | | | Revised the query used when reflecting CHECK constraints to make use of the ``pg_get_constraintdef`` function, as the ``consrc`` column is being deprecated in PG 12. Thanks to John A Stevenson for the tip. Fixes: #4463 Change-Id: Ie0ee9bdfddb0635db72b35c2e2e4b27f154162fd
View Lorry Controller Status