summaryrefslogtreecommitdiff
path: root/test/dialect/mssql/test_compiler.py
Commit message (Collapse)AuthorAgeFilesLines
* Run bracket interpretation for reflectionShan2023-01-231-0/+41
| | | | | | | | | | | | | | | | | | | | | Fixed bug where a schema name given with brackets, but no dots inside the name, for parameters such as :paramref:`_schema.Table.schema` would not be interpreted within the context of the SQL Server dialect's documented behavior of interpreting explicit brackets as token delimiters, first added in 1.2 for #2626, when referring to the schema name in reflection operations. The original assumption for #2626's behavior was that the special interpretation of brackets was only significant if dots were present, however in practice, the brackets are not included as part of the identifier name for all SQL rendering operations since these are not valid characters within regular or delimited identifiers. Pull request courtesy Shan. Fixes: #9133 Closes: #9134 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/9134 Pull-request-sha: 5dac87c82cd3063dd8e50f0075c7c00330be6439 Change-Id: I7a507bc38d75a04ffcb7e920298775baae22c6d1
* Try running pyupgrade on the codeFederico Caselli2022-11-161-1/+0
| | | | | | | | 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/+29
| | | | | | | | | | | | | | | 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
* 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
* Warn when caching is disabled / documentMike Bayer2021-12-061-1/+1
| | | | | | | | | | | | | | | | | | | | | | | | | | | This patch adds new warnings for all elements that don't indicate their caching behavior, including user-defined ClauseElement subclasses and third party dialects. it additionally adds new documentation to discuss an apparent performance degradation in 1.4 when caching is disabled as a result in the significant expense incurred by ORM lazy loaders, which in 1.3 used BakedQuery so were actually cached. As a result of adding the warnings, a fair degree of lesser used SQL expression objects identified that they did not define caching behavior so would have been producing ``[no key]``, including PostgreSQL constructs ``hstore`` and ``array``. These have been amended to use inherit cache where appropriate. "on conflict" constructs in PostgreSQL, MySQL, SQLite still explicitly don't generate a cache key at this time. The change also adds a test for all constructs via assert_compile() to assert they will not generate cache warnings. Fixes: #7394 Change-Id: I85958affbb99bfad0f5efa21bc8f2a95e7e46981
* change the POSTCOMPILE/ SCHEMA symbols to not conflict w mssql quotingMike Bayer2021-11-091-17/+18
| | | | | | | | | | | | | | | | | | Adjusted the compiler's generation of "post compile" symbols including those used for "expanding IN" as well as for the "schema translate map" to not be based directly on plain bracketed strings with underscores, as this conflicts directly with SQL Server's quoting format of also using brackets, which produces false matches when the compiler replaces "post compile" and "schema translate" symbols. The issue created easy to reproduce examples both with the :meth:`.Inspector.get_schema_names` method when used in conjunction with the :paramref:`_engine.Connection.execution_options.schema_translate_map` feature, as well in the unlikely case that a symbol overlapping with the internal name "POSTCOMPILE" would be used with a feature like "expanding in". Fixes: #7300 Change-Id: I6255c850b140522a4aba95085216d0bca18ce230
* Remove deprecated dialects and driversFederico Caselli2021-10-311-4/+3
| | | | | Fixes: #7258 Change-Id: I3577f665eca04f2632b69bcb090f0a4ec9271db9
* Merge "dont qualify literal_binds with literal_execute"mike bayer2021-08-071-0/+17
|\
| * dont qualify literal_binds with literal_executeMike Bayer2021-08-071-0/+17
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | this appears to be unnecessary and prevents end-user literal_binds case from working. Fixed issue where the ``literal_binds`` compiler flag, as used externally to render bound parameters inline, would fail to work when used with a certain class of parameters known as "literal_execute", which covers things like LIMIT and OFFSET values for dialects where the drivers don't allow a bound parameter, such as SQL Server's "TOP" clause. The issue locally seemed to affect only the MSSQL dialect. Fixes: #6863 Change-Id: Ia74cff5b0107b129a11b9b965883552b2962e449
* | Modernize tests - dml_valuesGord Thompson2021-07-261-11/+20
|/ | | | Change-Id: Ida86ed40c43d91813151621b847376976773a5f9
* Modernize tests - dml_whereclauseGord Thompson2021-07-261-5/+5
| | | | | | | | | Fixed issue where the unit of work would internally use a 2.0-deprecated SQL expression form, emitting a deprecation warning when SQLALCHEMY_WARN_20 were enabled. Fixes: #6812 Change-Id: I0a031e728527a1c3382848b6ddc793939362b128
* Modernize tests - legacy_selectGord Thompson2021-07-211-1/+7
| | | | Change-Id: I04057cc3d3f93de60b02999803e2ba6a23cdf68d
* Modernize tests - unionGord Thompson2021-07-121-1/+1
| | | | Change-Id: I2ccb714a249350f23e2b5f78f5f9ffb0d4f7efb0
* Ensure compiler uses quote_schema hook for translates rendersMike Bayer2021-06-301-12/+44
| | | | | | | | | Fixed regression where the special dotted-schema name handling for the SQL Server dialect would not function correctly if the dotted schema name were used within the ``schema_translate_map`` feature. Fixes: #6697 Change-Id: Idb610755cbf8122e71223d5dd0a17fcb61b1b98d
* don't base compilation off the int value of offset/limit part IIMike Bayer2021-04-141-2/+25
| | | | | | | | | | | | | | | | Fixed an additional regression in the same area as that of :ticket:`6184`, where using a value of 0 for OFFSET in conjunction with LIMIT with SQL Server would create a statement using "TOP", as was the behavior in 1.3, however due to caching would then fail to respond accordingly to other values of OFFSET. If the "0" wasn't first, then it would be fine. For the fix, the "TOP" syntax is now only emitted if the OFFSET value is omitted entirely, that is, :meth:`_sql.Select.offset` is not used. Note that this change now requires that if the "with_ties" or "percent" modifiers are used, the statement can't specify an OFFSET of zero, it now needs to be omitted entirely. Fixes: #6265 Change-Id: If30596b8dcd9f2ce4221cd87c5407fa81f5f9a90
* Fix MSSQL / Oracle limit/offset regressionsFederico Caselli2021-03-311-4/+6
| | | | | | | | | | | | | | Fixed a regression in MSSQL 2012+ that prevented the order clause to be rendered when ``offset=0`` is used in a subquery. Fixed critical regression where the Oracle compiler would not maintain the correct parameter values in the LIMIT/OFFSET for a select due to a caching issue. Co-authored-by: Mike Bayer <mike_mp@zzzcomputing.com> Fixes: #6163 Fixes: #6173 Change-Id: Ieb12354271d09ad935d684ee0db4fa0128837215
* Replace with_labels() and apply_labels() in ORM/CoreGord Thompson2021-01-261-1/+1
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 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
* reinvent xdist hooks in terms of pytest fixturesMike Bayer2021-01-131-1/+1
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 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
* Fixed compile for mssql dialectGord Thompson2020-12-091-0/+26
| | | | | | | | | | | Fixed string compilation when both mssql_include and mssql_where are used Fixes: #5751 Closes: #5752 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/5752 Pull-request-sha: aa57ad5d93cd69bf7728d864569c31c7e59b54fb Change-Id: I1201170affd9911c252df5c9b841e538bb577085
* Fetch first supportFederico Caselli2020-10-021-14/+238
| | | | | | | | | Add support to ``FETCH {FIRST | NEXT} [ count ] {ROW | ROWS} {ONLY | WITH TIES}`` in the select for the supported backends, currently PostgreSQL, Oracle and MSSQL. Fixes: #5576 Change-Id: Ibb5871a457c0555f82b37e354e7787d15575f1f7
* upgrade to black 20.8b1Mike Bayer2020-09-281-5/+24
| | | | | | | It's better, the majority of these changes look more readable to me. also found some docstrings that had formatting / quoting issues. Change-Id: I582a45fde3a5648b2f36bab96bad56881321899b
* Merge "Reflect mssql/postgresql filtered/partial indexes"mike bayer2020-09-121-0/+6
|\
| * Reflect mssql/postgresql filtered/partial indexesRamonWill2020-09-121-0/+6
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Added support for inspection / reflection of partial indexes / filtered indexes, i.e. those which use the ``mssql_where`` or ``postgresql_where`` parameters, with :class:`_schema.Index`. The entry is both part of the dictionary returned by :meth:`.Inspector.get_indexes` as well as part of a reflected :class:`_schema.Index` construct that was reflected. Pull request courtesy Ramon Williams. **Have a nice day!** Fixes: #4966 Closes: #5504 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/5504 Pull-request-sha: b3018bac987081193b2e65cfdb6aeb7d5d270fcd Change-Id: Icbb2f93d1545700718ccb5222097185b815f5dbc
* | Update select usage to use the new 1.4 formatFederico Caselli2020-09-081-19/+22
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | This change includes mainly that the bracketed use within select() is moved to positional, and keyword arguments are removed from calls to the select() function. it does not yet fully address other issues such as keyword arguments passed to the table.select(). Additionally, allows False / None to both be considered as "disable" for all of select.correlate(), select.correlate_except(), query.correlate(), which establishes consistency with passing of ``False`` for the legact select(correlate=False) argument. Change-Id: Ie6c6e6abfbd3d75d4c8de504c0cf0159e6999108
* | Add support for identity columnsFederico Caselli2020-08-191-90/+237
|/ | | | | | | | | | | | | | Added the :class:`_schema.Identity` construct that can be used to configure identity columns rendered with GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY. Currently the supported backends are PostgreSQL >= 10, Oracle >= 12 and MSSQL (with different syntax and a subset of functionalities). Fixes: #5362 Fixes: #5324 Fixes: #5360 Change-Id: Iecea6f3ceb36821e8b96f0b61049b580507a1875
* Fix mssql dialect escaping object names containing ']'Gord Thompson2020-07-181-3/+16
| | | | | Fixes: #5467 Change-Id: I054ec219717ba62847a9daf1214e215dd6b70633
* Add future=True to create_engine/Session; unify select()Mike Bayer2020-07-081-30/+30
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 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
* Merge "Support multiple dotted sections in mssql schema names"mike bayer2020-06-011-0/+37
|\
| * Support multiple dotted sections in mssql schema namesMike Bayer2020-06-011-0/+37
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Refined the logic used by the SQL Server dialect to interpret multi-part schema names that contain many dots, to not actually lose any dots if the name does not have bracking or quoting used, and additionally to support a "dbname" token that has many parts including that it may have multiple, independently-bracketed sections. This fix addresses #5364 to some degree but probably does not resolve it fully. References: #5364 Fixes: #5366 Change-Id: I460cd74ce443efb35fb63b6864f00c6d81422688
* | Add support for "real" sequences in mssqlGord Thompson2020-05-291-47/+0
|/ | | | | | | | | | | | | | | | | Added support for "CREATE SEQUENCE" and full :class:`.Sequence` support for Microsoft SQL Server. This removes the deprecated feature of using :class:`.Sequence` objects to manipulate IDENTITY characteristics which should now be performed using ``mssql_identity_start`` and ``mssql_identity_increment`` as documented at :ref:`mssql_identity`. The change includes a new parameter :paramref:`.Sequence.data_type` to accommodate SQL Server's choice of datatype, which for that backend includes INTEGER and BIGINT. The default starting value for SQL Server's version of :class:`.Sequence` has been set at 1; this default is now emitted within the CREATE SEQUENCE DDL for all backends. Fixes: #4235 Fixes: #4633 Change-Id: I6aa55c441e8146c2f002e2e201a7f645e667b916
* Document new LIMIT/OFFSET support; support subquery ORDER BYMike Bayer2020-02-131-3/+109
| | | | | | | | | An adjustment to the original commit for the fix to #5084 in ab1799a2a1951fe8f188b6395fde04a233a3ac0d, correctly rendering ORDER BY for subqueries with the new syntax. Fixes: #5084 Change-Id: I5ab5c1887c5a10f0a5eed1e9aae1f5994c28d88e
* MSSQL 2014 OFFSET/FETCH syntax supportElkin2020-02-071-0/+23
| | | | | | | | | | | | | | SQL Server OFFSET and FETCH keywords are now used for limit/offset, rather than using a window function, for SQL Server versions 11 and higher. TOP is still used for a query that features only LIMIT. Pull request courtesy Elkin. Fixes: #5084 Closes: #5125 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/5125 Pull-request-sha: a45b7f73090d2053e3a7020d4e3d7fabb0c5627d Change-Id: Id6a01ba30caac87d7d3d92c3903cdfd77fbcee5e
* Enable F821Mike Bayer2020-01-041-2/+2
| | | | | | | | | | | | | | | | | | | | | | | | | | | | In Ia63a510f9c1d08b055eef62cf047f1f427f0450c we introduced "lambda combinations" which use a bit of function closure inspection in order to allow for testing combinations that make use of symbols that come from test fixtures, or from the test itself. Two problems. One is that we can't use F821 flake8 rule without either adding lots of noqas, skipping the file, or adding arguments to the lambdas themselves that are then populated, which makes for a very verbose system. The other is that the system is already verbose with all those lambdas and the magic in use is a non-explicit kind, hence F821 reminds us that if we can improve upon this, we should. So let's improve upon it by making it so that the "lambda" is just once and up front for the whole thing, and let it accept the arguments directly. This still requires magic, because these test cases need to resolve at test collection time, not test runtime. But we will instead substitute a namespace up front that can be coerced into its desired form within the tests. Additionally, there's a little bit of py2k compatible type annotations present; f821 is checking these, so we have to add those imports also using the TYPE_CHECKING boolean so they don't take place in py2k. Change-Id: Idb7e7a0c8af86d9ab133f548511306ef68cdba14
* Use expanding IN for all literal value IN expressionsMike Bayer2019-12-221-20/+37
| | | | | | | | | | | | | | | | | | | | | | | | The "expanding IN" feature, which generates IN expressions at query execution time which are based on the particular parameters associated with the statement execution, is now used for all IN expressions made against lists of literal values. This allows IN expressions to be fully cacheable independently of the list of values being passed, and also includes support for empty lists. For any scenario where the IN expression contains non-literal SQL expressions, the old behavior of pre-rendering for each position in the IN is maintained. The change also completes support for expanding IN with tuples, where previously type-specific bind processors weren't taking effect. As part of this change, a more explicit separation between "literal execute" and "post compile" bound parameters is being made; as the "ansi bind rules" feature is rendering bound parameters inline, as we now support "postcompile" generically, these should be used here, however we have to render literal values at execution time even for "expanding" parameters. new test fixtures etc. are added to assert everything goes to the right place. Fixes: #4645 Change-Id: Iaa2b7bfbfaaf5b80799ee17c9b8507293cba6ed1
* Interpret empty LIMIT, expression LIMIT correctlyMike Bayer2019-11-091-0/+23
| | | | | | | | | Fixed issue in MSSQL dialect where an expression-based OFFSET value in a SELECT would be rejected, even though the dialect can render this expression inside of a ROW NUMBER-oriented LIMIT/OFFSET construct. Fixes: #4973 Change-Id: I040d34f781791c4ed5a727e1b8fb98c68ddd0622
* Support for generated columnsCaselIT2019-11-081-0/+22
| | | | | | | | | | | | | | | | | | | | | | | | | | 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
* Apply quoting to SQL Server _switch_dbMike Bayer2019-10-031-16/+0
| | | | | | | | | | | | | Added identifier quoting to the schema name applied to the "use" statement which is invoked when a SQL Server multipart schema name is used within a :class:`.Table` that is being reflected, as well as for :class:`.Inspector` methods such as :meth:`.Inspector.get_table_names`; this accommodates for special characters or spaces in the database name. Additionally, the "use" statement is not emitted if the current database matches the target owner database name being passed. Fixes: #4883 Change-Id: I84419730e94aac3a88d331ad8c24d10aabbc34af
* Render LIMIT/OFFSET conditions after compile on select dialectsMike Bayer2019-08-301-6/+9
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | 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
* Label simple column transformations as the column nameMike Bayer2019-08-281-1/+1
| | | | | | | | | | | | | | | | | | | | Additional logic has been added such that certain SQL expressions which typically wrap a single database column will use the name of that column as their "anonymous label" name within a SELECT statement, potentially making key-based lookups in result tuples more intutive. The primary example of this is that of a CAST expression, e.g. ``CAST(table.colname AS INTEGER)``, which will export its default name as "colname", rather than the usual "anon_1" label, that is, ``CAST(table.colname AS INTEGER) AS colname``. If the inner expression doesn't have a name, then the previous "anonymous label" logic is used. When using SELECT statements that make use of :meth:`.Select.apply_labels`, such as those emitted by the ORM, the labeling logic will produce ``<tablename>_<inner column name>`` in the same was as if the column were named alone. The logic applies right now to the :func:`.cast` and :func:`.type_coerce` constructs as well as some single-element boolean expressions. Fixes: #4449 Change-Id: Ie3b73470e3bea53f2386cd86514cdc556491564e
* Add support for try_cast function on sqlalchemy.dialects.mssqlLeonel Atencio2019-08-121-0/+10
| | | | | | | | | | | | Added new :func:`.mssql.try_cast` construct for SQL Server which emits "TRY_CAST" syntax. Pull request courtesy Leonel Atencio. Fixes: #4782 Closes: #4785 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/4785 Pull-request-sha: cf13303a9d1c0cc0233a82a5d2ca01f438b6fb9b Change-Id: I74a71ff5e587353f67472534aabe0d54ae8039ae
* Implement new ClauseElement role and coercion systemMike Bayer2019-05-181-5/+5
| | | | | | | | | | | | | | | | | | | | 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
* Add support for filtered indexes for mssql dialectmollardthomas2019-05-061-0/+9
| | | | | | | | | | | | | Added support for SQL Server filtered indexes, via the ``mssql_where`` parameter which works similarly to that of the ``postgresql_where`` index function in the PostgreSQL dialect. Fixes: #4657 Closes: #4658 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/4658 Pull-request-sha: cf609c19bccc74c0dba38d2fc4976df3a205f3f6 Change-Id: I9c61b97d0b0cb6f6d417da7b1875b40f8f918a3c
* Black updatesMike Bayer2019-04-111-4/+4
| | | | | | Updating for a few black adjustments Change-Id: I9c8abadd1ccd6173e6d68dd62f14cec208cc304a
* MSSQL: only compile ORDER BY if it will be renderedMatt Lewellyn2019-04-041-0/+30
| | | | | | | | | | | | | | | Fixed issue in SQL Server dialect where if a bound parameter were present in an ORDER BY expression that would ultimately not be rendered in the SQL Server version of the statement, the parameters would still be part of the execution parameters, leading to DBAPI-level errors. Pull request courtesy Matt Lewellyn. Fixes: #4587 Closes: #4588 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/4588 Pull-request-sha: 2992a473e0f6d4fc27794cfd949ba20a81fad2ca Change-Id: Ie709aefdb1babf810bb81526289448f8cc7a4cb1
* Post black reformattingMike Bayer2019-01-061-26/+28
| | | | | | | | | | | | | 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-456/+613
| | | | | | | | | | | | | | 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
* Move CRUDTest, InlineDefaultTest from test_compilerMike Bayer2018-12-011-0/+29
| | | | | | | | | | test_compiler is mostly related to SELECT statements as well as smaller SQL elements. While it still has some DDL related tests, move out all the remaining insert/update tests into the already present test_insert.py, test_update.py Fixes: #2630 Change-Id: I4167618543fd1235d12d1717c8c629d2374b325a
* Add new parameters for IDENTITY start/increment in mssqlMike Bayer2018-11-141-9/+98
| | | | | | | | | | | Deprecated the use of :class:`.Sequence` with SQL Server in order to affect the "start" and "increment" of the IDENTITY value, in favor of new parameters ``mssql_identity_start`` and ``mssql_identity_increment`` which set these parameters directly. :class:`.Sequence` will be used to generate real ``CREATE SEQUENCE`` DDL with SQL Server in a future release. Fixes: #4362 Change-Id: I1e69378c5c960ff0bc28137c923589692f1a918f
* Make column-level collation quoting dialect-specificMike Bayer2018-01-121-0/+14
| | | | | | | | | | | | | Fixed regression in 1.2 where newly repaired quoting of collation names in :ticket:`3785` breaks SQL Server, which explicitly does not understand a quoted collation name. Whether or not mixed-case collation names are quoted or not is now deferred down to a dialect-level decision so that each dialect can prepare these identifiers directly. Change-Id: Iaf0a8123d9bf4711219e320896bb28c5d2649304 Fixes: #4154
* Allow delete where clause to refer multiple tables.inytar2017-12-051-0/+17
| | | | | | | | | | | | | | | | | | | | | Implemented "DELETE..FROM" syntax for Postgresql, MySQL, MS SQL Server (as well as within the unsupported Sybase dialect) in a manner similar to how "UPDATE..FROM" works. A DELETE statement that refers to more than one table will switch into "multi-table" mode and render the appropriate "USING" or multi-table "FROM" clause as understood by the database. Pull request courtesy Pieter Mulder. For SQL syntaxes see: Postgresql: https://www.postgresql.org/docs/current/static/sql-delete.html MySQL: https://dev.mysql.com/doc/refman/5.7/en/delete.html#multiple-table_syntax MSSQL: https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql Sybase: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00801.1510/html/iqrefso/X315721.htm Co-authored by: Mike Bayer <mike_mp@zzzcomputing.com> Change-Id: I6dfd57b49e44a095d076dc493cd2360bb5d920d3 Pull-request: https://github.com/zzzeek/sqlalchemy/pull/392 Fixes: #959
View Lorry Controller Status