summaryrefslogtreecommitdiff
path: root/lib/sqlalchemy/sql/selectable.py
Commit message (Collapse)AuthorAgeFilesLines
* Turn off the is_literal flag when proxying literal_column() to LabelMike Bayer2019-06-151-1/+0
| | | | | | | | | | | | | | | | | | | Fixed a series of quoting issues which all stemmed from the concept of the :func:`.literal_column` construct, which when being "proxied" through a subquery to be referred towards by a label that matches its text, the label would not have quoting rules applied to it, even if the string in the :class:`.Label` were set up as a :class:`.quoted_name` construct. Not applying quoting to the text of the :class:`.Label` is a bug because this text is strictly a SQL identifier name and not a SQL expression, and the string should not have quotes embedded into it already unlike the :func:`.literal_column` which it may be applied towards. The existing behavior of a non-labeled :func:`.literal_column` being propagated as is on the outside of a subquery is maintained in order to help with manual quoting schemes, although it's not clear if valid SQL can be generated for such a construct in any case. Fixes: #4730 Change-Id: I300941f27872fc4298c74a1d1ed65aef1a5cdd82
* Reverse Alias nesting conceptMike Bayer2019-06-131-21/+30
| | | | | | | | | | | | | | | | | | | | | | | | | The Alias object no longer has "element" and "original", it now has "wrapped" and "element" (the name .original is also left as a descriptor for legacy access by third party dialects). These two data members refer to the dual roles Alias needs to play, where in the Python sense it needs to refer to the thing it was applied against directly, whereas in the SQL sense it needs to refer to the ultimate "non-alias" thing it refers towards. Both are necessary to maintain. However, the change here has each Alias object access the non-Alias object immediately so that the "unwrapping" is simpler and does not need any special logic. In the SQL sense, Alias objects don't nest, the only potential was that of the CTE, however there is no such thing as a nested CTE, see link below. This change is an interim change along the way to breaking Alias into more classes and breaking away Select objects from being FromClause objects. Change-Id: Ie7a0d064226cb074ca745505129b5ec7d879e389 References: https://stackoverflow.com/questions/1413516/can-you-create-nested-with-clauses-for-common-table-expressions
* Use roles for ORM alias() conversionMike Bayer2019-05-271-3/+39
| | | | | | | | as SELECT statements will have subquery() and not alias(), start getting ready for the places where the ORM coerces SELECTs into subqueries and be ready to warn about it Change-Id: I90d4b6cae2c72816c6b192016ce074589caf4731
* Implement new ClauseElement role and coercion systemMike Bayer2019-05-181-147/+157
| | | | | | | | | | | | | | | | | | | | A major refactoring of all the functions handle all detection of Core argument types as well as perform coercions into a new class hierarchy based on "roles", each of which identify a syntactical location within a SQL statement. In contrast to the ClauseElement hierarchy that identifies "what" each object is syntactically, the SQLRole hierarchy identifies the "where does it go" of each object syntactically. From this we define a consistent type checking and coercion system that establishes well defined behviors. This is a breakout of the patch that is reorganizing select() constructs to no longer be in the FromClause hierarchy. Also includes a rename of as_scalar() into scalar_subquery(); deprecates automatic coercion to scalar_subquery(). Partially-fixes: #4617 Change-Id: I26f1e78898693c6b99ef7ea2f4e7dfd0e8e1a1bd
* - fix long lineMike Bayer2019-05-091-1/+2
| | | | Change-Id: If44d364ae02da447169a3dc51b6514225578cf82
* Add documentation for MySQL optimizer hints using prefix_withMike Bayer2019-05-081-0/+7
| | | | | Fixes: #4667 Change-Id: Iac3345319dc7c5a20bc7a6520492d2f341b64807
* Add _cache_key implementation.Mike Bayer2019-04-291-0/+98
| | | | | | | This leverages the work started in #4336 to allow ClauseElement structures to be cachable based on structure, not just identity. Change-Id: Ia99ddeb5353496dd7d61243245685f02b98d8100
* Reimplement .compare() in terms of a visitorMike Bayer2019-04-291-0/+9
| | | | | | | | | | | | Reworked the :meth:`.ClauseElement.compare` methods in terms of a new visitor-based approach, and additionally added test coverage ensuring that all :class:`.ClauseElement` subclasses can be accurately compared against each other in terms of structure. Structural comparison capability is used to a small degree within the ORM currently, however it also may form the basis for new caching features. Fixes: #4336 Change-Id: I581b667d8e1642a6c27165cc9f4aded1c66effc6
* Clone internals for Select._correlate_except collection as well as _correlateMike Bayer2019-03-101-0/+8
| | | | | | | | | | | | | | Fixed bug where use of :func:`.with_polymorphic` or other aliased construct would not properly adapt when the aliased target were used as the :meth:`.Select.correlate_except` target of a subquery used inside of a :func:`.column_property`. This required a fix to the clause adaption mechanics to properly handle a selectable that shows up in the "correlate except" list, in a similar manner as which occurs for selectables that show up in the "correlate" list. This is ultimately a fairly fundamental bug that has lasted for a long time but it is hard to come across it. Fixes: #4537 Change-Id: Ibb97d4eea18b3c452aad519dd14919bfb84d422f
* Prevent __init__ from being called for Alias, subclassesMike Bayer2019-02-211-123/+153
| | | | | | | | | | | The :class:`.Alias` class and related subclasses :class:`.CTE`, :class:`.Lateral` and :class:`.TableSample` have been reworked so that it is not possible for a user to construct the objects directly. These constructs require that the standalone construction function or selectable-bound method be used to instantiate new objects. Fixes: #4509 Change-Id: I74ae4786cb3ae625dab33b00bfd6bdc4e1219139
* Remove all remaining text() coercions and ensure identifiers are safeMike Bayer2019-02-061-11/+21
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 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
* Fix many spell glitchesLele Gaifax2019-01-251-2/+2
| | | | | | | | | | | | This affects mostly docstrings, except in orm/events.py::dispose_collection() where one parameter gets renamed: given that the method is empty, it seemed reasonable to me to fix that too. Closes: #4440 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/4440 Pull-request-sha: 779ed75acb6142e1f1daac467b5b14134529bb4b Change-Id: Ic0553fe97853054b09c2453af76d96363de6eb0e
* Add deprecation warnings to all deprecated APIsMike Bayer2019-01-231-28/+32
| | | | | | | | | | | | | | | A large change throughout the library has ensured that all objects, parameters, and behaviors which have been noted as deprecated or legacy now emit ``DeprecationWarning`` warnings when invoked. As the Python 3 interpreter now defaults to displaying deprecation warnings, as well as that modern test suites based on tools like tox and pytest tend to display deprecation warnings, this change should make it easier to note what API features are obsolete. See the notes added to the changelog and migration notes for further details. Fixes: #4393 Change-Id: If0ea11a1fc24f9a8029352eeadfc49a7a54c0a1b
* Remove version directives for 0.6, 0.7, 0.8Mike Bayer2019-01-151-27/+0
| | | | | | | | | - fix a few "seealso"s - ComparableProprerty's "superseded in 0.7" becomes deprecated in 0.7 Backport to currently maintained doc versions 1.2, 1.1 Change-Id: Ib1fcb2df8673dbe5c4ffc47f3896a60d1dfcb4b2
* Merge "use ..deprecated directive w/ version in all cases"mike bayer2019-01-121-33/+35
|\
| * use ..deprecated directive w/ version in all casesMike Bayer2019-01-111-33/+35
| | | | | | | | | | | | | | | | | | These changes should be ported from 1.3 back to 1.0 or possibly 0.9 to the extent they are relevant in each version. In 1.3 we hope to turn all deprecation documentation into warnings. Change-Id: I205186cde161af9389af513a425c62ce90dd54d8
* | happy new yearMike Bayer2019-01-111-1/+1
|/ | | | Change-Id: I6a71f4924d046cf306961c58dffccf21e9c03911
* Post black reformattingMike Bayer2019-01-061-52/+47
| | | | | | | | | | | | | Applied on top of a pure run of black -l 79 in I7eda77fed3d8e73df84b3651fd6cfcfe858d4dc9, this set of changes resolves all remaining flake8 conditions for those codes we have enabled in setup.cfg. Included are resolutions for all remaining flake8 issues including shadowed builtins, long lines, import order, unused imports, duplicate imports, and docstring issues. Change-Id: I4f72d3ba1380dd601610ff80b8fb06a2aff8b0fe
* Run black -l 79 against all source filesMike Bayer2019-01-061-312/+500
| | | | | | | | | | | | | | This is a straight reformat run using black as is, with no edits applied at all. The black run will format code consistently, however in some cases that are prevalent in SQLAlchemy code it produces too-long lines. The too-long lines will be resolved in the following commit that will resolve all remaining flake8 issues including shadowed builtins, long lines, import order, unused imports, duplicate imports, and docstring issues. Change-Id: I7eda77fed3d8e73df84b3651fd6cfcfe858d4dc9
* Allow join() to pick the best candidate from multiple froms/entitiesMike Bayer2018-11-141-0/+14
| | | | | | | | | | | | | | Refactored :meth:`.Query.join` to further clarify the individual components of structuring the join. This refactor adds the ability for :meth:`.Query.join` to determine the most appropriate "left" side of the join when there is more than one element in the FROM list or the query is against multiple entities. In particular this targets the regression we saw in :ticket:`4363` but is also of general use. The codepaths within :meth:`.Query.join` are now easier to follow and the error cases are decided more specifically at an earlier point in the operation. Fixes: #4365 Change-Id: I403f451243904a020ceab4c3f94bead550c7b2d5
* Adapt right side in join if lateral detectedMike Bayer2018-09-181-1/+3
| | | | | | | | | | | Fixed bug where use of :class:`.Lateral` construct in conjunction with :meth:`.Query.join` as well as :meth:`.Query.select_entity_from` would not apply clause adaption to the right side of the join. "lateral" introduces the use case of the right side of a join being correlatable. Previously, adaptation of this clause wasn't considered. Fixes: #4334 Change-Id: I3631e562092769d30069a2aa5e50a580f4661a23
* Add concept of "implicit boolean", treat as nativeMike Bayer2018-08-151-0/+1
| | | | | | | | | | | | | | | Fixed issue that is closely related to :ticket:`3639` where an expression rendered in a boolean context on a non-native boolean backend would be compared to 1/0 even though it is already an implcitly boolean expression, when :meth:`.ColumnElement.self_group` were used. While this does not affect the user-friendly backends (MySQL, SQLite) it was not handled by Oracle (and possibly SQL Server). Whether or not the expression is implicitly boolean on any database is now determined up front as an additional check to not generate the integer comparison within the compliation of the statement. Fixes: #4320 Change-Id: Iae0a65e5c01bd576e64733c3651e1e1a1a1b240c
* Clone _cte_alias instead of assigning "self"Mike Bayer2018-03-051-1/+1
| | | | | | | | | | Fixed bug in :class:.`CTE` construct along the same lines as that of :ticket:`4204` where a :class:`.CTE` that was aliased would not copy itself correctly during a "clone" operation as is frequent within the ORM as well as when using the :meth:`.ClauseElement.params` method. Change-Id: Id68d72dd244dedfc7bd6116c9a5123c51a55ea20 Fixes: #4210
* happy new yearMike Bayer2018-01-121-1/+1
| | | | Change-Id: I3ef36bfd0cb0ba62b3123c8cf92370a43156cf8f
* Add with_for_update() support in session.refresh()Mike Bayer2017-05-241-0/+13
| | | | | | | | | | | | | | | Session.refresh() is still hardcoded to legacy lockmode, come up with a new API so that the newer argument style works with it. Added new argument :paramref:`.with_for_update` to the :meth:`.Session.refresh` method. When the :meth:`.Query.with_lockmode` method were deprecated in favor of :meth:`.Query.with_for_update`, the :meth:`.Session.refresh` method was never updated to reflect the new option. Change-Id: Ia02a653746b7024699b515451525a88d7a17d63a Fixes: #3991
* Add links to with_only_columns to Select.column, append_columnMike Bayer2017-05-121-19/+16
| | | | | | | | | | | Provide a brief example for these two methods indicating that typically a table-bound (or other selectable) column is appended here, then link to with_only_columns documentation which has in-depth guidelines already including that one should not append columns from the current select to itself. Change-Id: I0742405a7f3c41450d337b9c633519d9cc101dfb Fixes: #3987
* Use consistent method signature for Alias.self_group()Mike Bayer2017-04-031-4/+4
| | | | | | | | | | | | Fixed bug where the use of an :class:`.Alias` object in a column context would raise an argument error when it tried to group itself into a parenthesized expression. Using :class:`.Alias` in this way is not yet a fully supported API, however it applies to some end-user recipes and may have a more prominent role in support of some future Postgresql features. Change-Id: I81717e30416e0350f08d1e022c3d84656e0a9735 Fixes: #3939
* Only use schema_translate_map on SchemaItem subclassesMike Bayer2017-02-281-0/+3
| | | | | | | | | | | | | 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. Change-Id: Ie8cb35aeaba2c67efec8c8c57c219e4dd346e44a Fixes: #3924
* Support python3.6Mike Bayer2017-01-131-32/+32
| | | | | | | | | | | Corrects some warnings and adds tox config. Adds DeprecationWarning to the error category. Large sweep for string literals w/ backslashes as this is common in docstrings Co-authored-by: Andrii Soldatenko Fixes: #3886 Change-Id: Ia7c838dfbbe70b262622ed0803d581edc736e085 Pull-request: https://github.com/zzzeek/sqlalchemy/pull/337
* Merge "Tighten rules for order_by(Label) resolution"mike bayer2017-01-091-2/+3
|\
| * Tighten rules for order_by(Label) resolutionMike Bayer2017-01-061-2/+3
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | - 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-Id: I44fc36dab34380cc238c1e79ecbe23f1628d588a Fixes: #3882
* | update for 2017 copyrightMike Bayer2017-01-041-1/+1
|/ | | | Change-Id: I4e8c2aa8fe817bb2af8707410fa0201f938781de
* spelling: Postgresql -> PostgreSQLVille Skyttä2016-10-081-13/+13
|
* Repair foreign_keys population for Join._refresh_for_new_columnMike Bayer2016-09-151-1/+1
| | | | | | | | | | Fixed bug where setting up a single-table inh subclass of a joined-table subclass which included an extra column would corrupt the foreign keys collection of the mapped table, thereby interfering with the initialization of relationships. Change-Id: I04a0cf98fd456d12d5a5b9e77a46a01246969a63 Fixes: #3797
* Fixed example of upsert in the HasCTE.cte docstring bypr/302Vladimir Magamedov2016-08-271-18/+28
| | | | replacing it with more concrete and working example.
* Merge "Add TABLESAMPLE clause support."mike bayer2016-06-151-0/+92
|\
| * Add TABLESAMPLE clause support.saarni2016-06-151-0/+92
| | | | | | | | | | | | | | | | | | | | The TABLESAMPLE clause allows randomly selecting an approximate percentage of rows from a table. At least DB2, Microsoft SQL Server and recent Postgresql support this standard clause. Fixes: #3718 Change-Id: I3fb8b9223e12a57100df30876b461884c58d72fa Pull-request: https://github.com/zzzeek/sqlalchemy/pull/277
* | Interpret lateral, alias as from clausesMike Bayer2016-06-151-2/+2
|/ | | | | | Also add doc members for Lateral. Change-Id: Ic801c296377ce84230139c6a43ecb3e93a852832
* Deprecate FromClause.count()Mike Bayer2016-06-141-16/+25
| | | | | | | | | | | count() here is misleading in that it not only counts from an arbitrary column in the table, it also does not make accommodations for DISTINCT, JOIN, etc. as the ORM-level function does. Core should not be attempting to provide a function like this. Change-Id: I9916fc51ef744389a92c54660ab08e9695b8afc2 Fixes: #3724
* Ensure CTE internals are handled during cloneMike Bayer2016-06-101-0/+8
| | | | | | | | | The CTE construct was missing a _copy_internals() method which would handle CTE-specific structures including _cte_alias, _restates during a clone operation. Change-Id: I9aeac9cd24d8f7ae6b70e52650d61f7c96cb6d7e Fixes: #3722
* Add 'FOR NO KEY UPDATE' / 'FOR KEY SHARE' support for PostgresqlSergey Skopin2016-06-081-5/+12
| | | | | | | | Adds ``key_share=True`` for with_for_update(). Co-authored-by: Mike Bayer <mike_mp@zzzcomputing.com> Change-Id: I74e0c3fcbc023e1dc98a1fa0c7db67b4c3693a31 Pull-request: https://github.com/zzzeek/sqlalchemy/pull/279
* Add SKIP LOCKED support for Postgresql, OracleJack Zhou2016-06-021-3/+14
| | | | | | | | This adds `SELECT ... FOR UPDATE SKIP LOCKED`/ `SELECT ... FOR SHARE SKIP LOCKED` rendering. Change-Id: Id1dc4f1cafc1de23f397a6f73d54ab2c58d5910d Pull-request: https://bitbucket.org/zzzeek/sqlalchemy/pull-requests/86
* - Added :meth:`.Select.lateral` and related constructs to allowMike Bayer2016-03-291-0/+58
| | | | | for the SQL standard LATERAL keyword, currently only supported by Postgresql. fixes #2857
* - Added support for rendering "FULL OUTER JOIN" to both Core and ORM.Mike Bayer2016-03-281-10/+26
| | | | Pull request courtesy Stefan Urbanek. fixes #1957
* - CTE functionality has been expanded to support all DML, allowingMike Bayer2016-02-111-120/+168
| | | | | | | INSERT, UPDATE, and DELETE statements to both specify their own WITH clause, as well as for these statements themselves to be CTE expressions when they include a RETURNING clause. fixes #2551
* - happy new yearMike Bayer2016-01-291-1/+1
|
* - A deep improvement to the recently added :meth:`.TextClause.columns`Mike Bayer2016-01-141-1/+2
| | | | | | | | | | | | | | | | | | | method, and its interaction with result-row processing, now allows the columns passed to the method to be positionally matched with the result columns in the statement, rather than matching on name alone. The advantage to this includes that when linking a textual SQL statement to an ORM or Core table model, no system of labeling or de-duping of common column names needs to occur, which also means there's no need to worry about how label names match to ORM columns and so-forth. In addition, the :class:`.ResultProxy` has been further enhanced to map column and string keys to a row with greater precision in some cases. fixes #3501 - reorganize the initialization of ResultMetaData for readability and complexity; use the name "cursor_description", define the task of "merging" cursor_description with compiled column information as its own function, and also define "name extraction" as a separate task. - fully change the name we use in the "ambiguous column" error to be the actual name that was ambiguous, modify the C ext also
* - The behavior of the :func:`.union` construct and related constructsMike Bayer2015-08-121-1/+17
| | | | | | | | | such as :meth:`.Query.union` now handle the case where the embedded SELECT statements need to be parenthesized due to the fact that they include LIMIT, OFFSET and/or ORDER BY. These queries **do not work on SQLite**, and will fail on that backend as they did before, but should now work on all other backends. fixes #2528
* - fix typo in suffix_with() docs, fixes #3502Mike Bayer2015-07-291-1/+1
|
* - Fixed a regression that was incorrectly fixed in 1.0.0b4Mike Bayer2015-04-241-3/+4
| | | | | | | | | | | | | | | | | | (hence becoming two regressions); reports that SELECT statements would GROUP BY a label name and fail was misconstrued that certain backends such as SQL Server should not be emitting ORDER BY or GROUP BY on a simple label name at all; when in fact, we had forgotten that 0.9 was already emitting ORDER BY on a simple label name for all backends, as described in :ref:`migration_1068`, as 1.0 had rewritten this logic as part of :ticket:`2992`. In 1.0.2, the bug is fixed both that SQL Server, Firebird and others will again emit ORDER BY on a simple label name when passed a :class:`.Label` construct that is expressed in the columns clause, and no backend will emit GROUP BY on a simple label name in this case, as even Postgresql can't reliably do GROUP BY on a simple name in every case. fixes #3338, fixes #3385
View Lorry Controller Status