Mike Bayer [Sun, 17 Jul 2022 15:32:27 +0000 (11:32 -0400)]
use concat() directly for contains, startswith, endswith
Adjusted the SQL compilation for string containment functions
``.contains()``, ``.startswith()``, ``.endswith()`` to force the use of the
string concatenation operator, rather than relying upon the overload of the
addition operator, so that non-standard use of these operators with for
example bytestrings still produces string concatenation operators.
To accommodate this, needed to add a new _rconcat operator function,
which is private, as well as a fallback in concat_op() that works
similarly to Python builtin ops.
Mike Bayer [Sat, 16 Jul 2022 20:19:15 +0000 (16:19 -0400)]
implement column._merge()
this takes the user-defined args of one Column and merges
them into the not-user-defined args of another Column.
Implemented within the pep-593 column transfer operation
to begin to make this new feature more robust.
work may still be needed for constraints etc. but
in theory everything from the left side annotated column
should take effect for the right side if not otherwise
specified on the right.
Mike Bayer [Fri, 15 Jul 2022 16:25:22 +0000 (12:25 -0400)]
make anno-only Mapped[] column available for mixins
Documentation is relying on the recently improved
behavior of produce_column_copies() to make sure everything is
available on cls for a declared_attr. therefore for anno-only
attribute, we also need to generate the mapped_column() up front
before scan is called.
noticed in pylance, allow @declared_attr to recognize
@classmethod also which allows letting typing tools know
something is explicitly a classmethod
Mike Bayer [Mon, 11 Jul 2022 01:24:17 +0000 (21:24 -0400)]
support "SELECT *" for ORM queries
A :func:`_sql.select` construct that is passed a sole '*' argument for
``SELECT *``, either via string, :func:`_sql.text`, or
:func:`_sql.literal_column`, will be interpreted as a Core-level SQL
statement rather than as an ORM level statement. This is so that the ``*``,
when expanded to match any number of columns, will result in all columns
returned in the result. the ORM- level interpretation of
:func:`_sql.select` needs to know the names and types of all ORM columns up
front which can't be achieved when ``'*'`` is used.
If ``'*`` is used amongst other expressions simultaneously with an ORM
statement, an error is raised as this can't be interpreted correctly by the
ORM.
Mike Bayer [Thu, 7 Jul 2022 15:44:09 +0000 (11:44 -0400)]
document using fetch() with Oracle
We implemented working FETCH support, but it's not
yet implied by limit/offset. The docs make no mention
that this is available which is very misleading including
to maintainers. Make it clear that fetch() support is
there right now, it's just not yet implicit with
limit/offset.
Mike Bayer [Wed, 6 Jul 2022 01:05:18 +0000 (21:05 -0400)]
generalize sql server check for id col to accommodate ORM cases
Fixed issues that prevented the new usage patterns for using DML with ORM
objects presented at :ref:`orm_dml_returning_objects` from working
correctly with the SQL Server pyodbc dialect.
Here we add a step to look in compile_state._dict_values more thoroughly
for the keys we need to determine "identity insert" or not, and also
add a new compiler variable dml_compile_state so that we can skip the
ORM's compile_state if present.
Mike Bayer [Sun, 3 Jul 2022 17:55:20 +0000 (13:55 -0400)]
test transfer of default, insert_default
right now "default" goes to Column.default unconditionally
if insert_default is not present, including if dataclasses
are in use where the field effectively now does two things.
This generally works out because Python side default
can be assigned to the object or picked up by Core in any case.
However, we might want to look into later on migrating this
to have the fields act more separately. I think it's
"OK" for now, will try to doc that this might change.
Restrict the GitHub token permissions only to the required ones; this way, even if the attackers will succeed in compromising your workflow, they won’t be able to do much.
- Included permissions for the action. https://github.com/ossf/scorecard/blob/main/docs/checks.md#token-permissions
[Keeping your GitHub Actions and workflows secure Part 1: Preventing pwn requests](https://securitylab.github.com/research/github-actions-preventing-pwn-requests/)
Mike Bayer [Thu, 30 Jun 2022 23:10:06 +0000 (19:10 -0400)]
repair yield_per for non-SS dialects and add new options
Implemented new :paramref:`_engine.Connection.execution_options.yield_per`
execution option for :class:`_engine.Connection` in Core, to mirror that of
the same :ref:`yield_per <orm_queryguide_yield_per>` option available in
the ORM. The option sets both the
:paramref:`_engine.Connection.execution_options.stream_results` option at
the same time as invoking :meth:`_engine.Result.yield_per`, to provide the
most common streaming result configuration which also mirrors that of the
ORM use case in its usage pattern.
Fixed bug in :class:`_engine.Result` where the usage of a buffered result
strategy would not be used if the dialect in use did not support an
explicit "server side cursor" setting, when using
:paramref:`_engine.Connection.execution_options.stream_results`. This is in
error as DBAPIs such as that of SQLite and Oracle already use a
non-buffered result fetching scheme, which still benefits from usage of
partial result fetching. The "buffered" strategy is now used in all
cases where :paramref:`_engine.Connection.execution_options.stream_results`
is set.
Added :meth:`.FilterResult.yield_per` so that result implementations
such as :class:`.MappingResult`, :class:`.ScalarResult` and
:class:`.AsyncResult` have access to this method.
Mike Bayer [Tue, 28 Jun 2022 22:55:19 +0000 (18:55 -0400)]
produce column copies up the whole hierarchy first
Fixed issue where a hierarchy of classes set up as an abstract or mixin
declarative classes could not declare standalone columns on a superclass
that would then be copied correctly to a :class:`_orm.declared_attr`
callable that wanted to make use of them on a descendant class.
Originally it looked like this would produce an ordering change,
however an adjustment to the flow for produce_column_copies
has avoided that for now.
Gord Thompson [Sat, 25 Jun 2022 16:34:51 +0000 (10:34 -0600)]
Change setinputsizes behavior for mssql+pyodbc
The ``use_setinputsizes`` parameter for the ``mssql+pyodbc`` dialect now
defaults to ``True``; this is so that non-unicode string comparisons are
bound by pyodbc to pyodbc.SQL_VARCHAR rather than pyodbc.SQL_WVARCHAR,
allowing indexes against VARCHAR columns to take effect. In order for the
``fast_executemany=True`` parameter to continue functioning, the
``use_setinputsizes`` mode now skips the ``cursor.setinputsizes()`` call
specifically when ``fast_executemany`` is True and the specific method in
use is ``cursor.executemany()``, which doesn't support setinputsizes. The
change also adds appropriate pyodbc DBAPI typing to values that are typed
as :class:`_types.Unicode` or :class:`_types.UnicodeText`, as well as
altered the base :class:`_types.JSON` datatype to consider JSON string
values as :class:`_types.Unicode` rather than :class:`_types.String`.
cheremnov [Thu, 24 Feb 2022 07:22:33 +0000 (02:22 -0500)]
Comments on (named) constraints
Adds support for comments on named constraints, including `ForeignKeyConstraint`, `PrimaryKeyConstraint`, `CheckConstraint`, `UniqueConstraint`, solving the [Issue 5667](https://github.com/sqlalchemy/sqlalchemy/issues/5667).
Supports only PostgreSQL backend.
### Description
Following the example of [Issue 1546](https://github.com/sqlalchemy/sqlalchemy/issues/1546), supports comments on constraints. Specifically, enables comments on _named_ ones — as I get it, PostgreSQL prohibits comments on unnamed constraints.
Enables setting the comments for named constraints like this:
```
Table(
'example', metadata,
Column('id', Integer),
Column('data', sa.String(30)),
PrimaryKeyConstraint(
"id", name="id_pk", comment="id_pk comment"
),
CheckConstraint('id < 100', name="cc1", comment="Id value can't exceed 100"),
UniqueConstraint(['data'], name="uc1", comment="Must have unique data field"),
)
```
Provides the DDL representation for constraint comments and routines to create and drop them. Class `.Inspector` reflects constraint comments via methods like `get_check_constraints` .
### Checklist
<!-- go over following points. check them with an `x` if they do apply, (they turn into clickable checkboxes once the PR is submitted, so no need to do everything at once)
-->
This pull request is:
- [ ] A documentation / typographical error fix
- [ ] A short code fix
- [x] A new feature implementation
- Solves the issue 5667.
- The commit message includes `Fixes: 5667`.
- Includes tests based on comment reflection.
Mike Bayer [Mon, 27 Jun 2022 16:56:27 +0000 (12:56 -0400)]
merge column args from Annotated left side
because we are forced by pep-681 to use the argument
"default", we need a way to have client Column default
separate from a dataclasses level default. Also, pep-681
does not support deriving the descriptor function from
Annotated, so allow a brief right side mapped_column() to
be present that will have more column-centric arguments
from the left side Annotated to be merged.
Federico Caselli [Sun, 26 Jun 2022 10:31:45 +0000 (12:31 +0200)]
Ensure type lengths are int in oracle
Repair change introduced by the multi reflection that caused
char length of varchar like types or precisions in numberic
like types to be set as float.
This will fix the test errors in alembic that are
currently broken, as shown in
I9ad803df1d3ccf2a5111266b781061936717b8c8
Mike Bayer [Fri, 24 Jun 2022 14:31:46 +0000 (10:31 -0400)]
add fallback for old mutable format
Fixed regression caused by :ticket:`8133` where the pickle format for
mutable attributes was changed, without a fallback to recognize the old
format, causing in-place upgrades of SQLAlchemy to no longer be able to
read pickled data from previous versions. A check plus a fallback for the
old format is now in place.
Mike Bayer [Thu, 23 Jun 2022 15:15:19 +0000 (11:15 -0400)]
refine _include_fn to not include sibling mappers
Fixed regression caused by :ticket:`8064` where a particular check for
column correspondence was made too liberal, resulting in incorrect
rendering for some ORM subqueries such as those using
:meth:`.PropComparator.has` or :meth:`.PropComparator.any` in conjunction
with joined-inheritance queries that also use legacy aliasing features.
Mike Bayer [Wed, 22 Jun 2022 22:50:35 +0000 (18:50 -0400)]
sub-categorize special function forms
this is the tutorial, which should have some semblence of
not getting too far into the weeds. however, as we dont
really have other places to explain SQL concepts, and SQL
functions have a lot of them, we dont have another home right
now. so at least further sub-categorize window functions,
table/column valued functions, and WITHIN GROUP into an
"advanced function techniques" section with a disclaimer that
these are less common use cases.
Mike Bayer [Mon, 13 Jun 2022 15:46:28 +0000 (11:46 -0400)]
rework ORM mapping docs
prepare docs for newly incoming mapper styles, including
new dataclass mapping. move the existing dataclass/attrs
docs all into their own section and try to improve organization
and wording into the relatively recent "mapping styles"
document.
David Baumgold [Fri, 11 Feb 2022 17:30:24 +0000 (12:30 -0500)]
Domain type
Added a new Postgresql :class:`_postgresql.DOMAIN` datatype, which follows
the same CREATE TYPE / DROP TYPE behaviors as that of PostgreSQL
:class:`_postgresql.ENUM`. Much thanks to David Baumgold for the efforts on
this.
Mike Bayer [Mon, 20 Jun 2022 15:06:34 +0000 (11:06 -0400)]
remove warnings for index/unique skipped due to exclude_cols
The warnings that are emitted regarding reflection of indexes or unique
constraints, when the :paramref:`.Table.include_columns` parameter is used
to exclude columns that are then found to be part of those constraints,
have been removed. When the :paramref:`.Table.include_columns` parameter is
used it should be expected that the resulting :class:`.Table` construct
will not include constraints that rely upon omitted columns. This change
was made in response to :ticket:`8100` which repaired
:paramref:`.Table.include_columns` in conjunction with foreign key
constraints that rely upon omitted columns, where the use case became
clear that omitting such constraints should be expected.
Mike Bayer [Thu, 16 Jun 2022 17:35:16 +0000 (13:35 -0400)]
create new approach for deeply nested post loader options
Added very experimental feature to the :func:`_orm.selectinload` and
:func:`_orm.immediateload` loader options called
:paramref:`_orm.selectinload.recursion_depth` /
:paramref:`_orm.immediateload.recursion_depth` , which allows a single
loader option to automatically recurse into self-referential relationships.
Is set to an integer indicating depth, and may also be set to -1 to
indicate to continue loading until no more levels deep are found.
Major internal changes to :func:`_orm.selectinload` and
:func:`_orm.immediateload` allow this feature to work while continuing
to make correct use of the compilation cache, as well as not using
arbitrary recursion, so any level of depth is supported (though would
emit that many queries). This may be useful for
self-referential structures that must be loaded fully eagerly, such as when
using asyncio.
A warning is also emitted when loader options are connected together with
arbitrary lengths (that is, without using the new ``recursion_depth``
option) when excessive recursion depth is detected in related object
loading. This operation continues to use huge amounts of memory and
performs extremely poorly; the cache is disabled when this condition is
detected to protect the cache from being flooded with arbitrary statements.
Federico Caselli [Thu, 14 Oct 2021 19:45:57 +0000 (21:45 +0200)]
rearchitect reflection for batched performance
Rearchitected the schema reflection API to allow some dialects to make use
of high performing batch queries to reflect the schemas of many tables at
once using much fewer queries. The new performance features are targeted
first at the PostgreSQL and Oracle backends, and may be applied to any
dialect that makes use of SELECT queries against system catalog tables to
reflect tables (currently this omits the MySQL and SQLite dialects which
instead make use of parsing the "CREATE TABLE" statement, however these
dialects do not have a pre-existing performance issue with reflection. MS
SQL Server is still a TODO).
The new API is backwards compatible with the previous system, and should
require no changes to third party dialects to retain compatibility;
third party dialects can also opt into the new system by implementing
batched queries for schema reflection.
Along with this change is an updated reflection API that is fully
:pep:`484` typed, features many new methods and some changes.
Gord Thompson [Tue, 14 Jun 2022 16:09:04 +0000 (10:09 -0600)]
Allow NUMERIC()/DECIMAL() IDENTITY columns
Fixed issue where :class:`.Table` objects that made use of IDENTITY columns
with a :class:`.Numeric` datatype would produce errors when attempting to
reconcile the "autoincrement" column, preventing construction of the
:class:`.Column` from using the :paramref:`.Column.autoincrement` parameter
as well as emitting errors when attempting to invoke an :class:`.Insert`
construct.
Mike Bayer [Thu, 16 Jun 2022 18:46:11 +0000 (14:46 -0400)]
Revert "add auto_recurse option to selectinload, immediateload"
this option works very badly with caching and the API
is likely not what we want either. Work continues for
#8126 including that the additional work in
I9f162e0a09c1ed327dd19498aac193f649333a01
tries to add new recursive features.
Mike Bayer [Wed, 15 Jun 2022 16:42:44 +0000 (12:42 -0400)]
implement literal stringification for arrays
as we already implement stringification for the contents,
provide a bracketed syntax for default and ARRAY literal
for PG specifically. ARRAY literal seems much simpler to
render than their quoted syntax which requires double quotes
for strings.
also open up testing for pg8000 which has likely been
fine with arrays for awhile now, bump the version pin
also.
Mike Bayer [Tue, 14 Jun 2022 21:05:44 +0000 (17:05 -0400)]
new features for pep 593 Annotated
* extract the inner type from Annotated when the outer type
isn't present in the type map, to allow for arbitrary Annotated
* allow _IntrospectsAnnotations objects to be directly present
in an Annotated and resolve the mapper property from that.
Currently implemented for mapped_column(), with message for
others. Can work for composite() and likely some
relationship() as well at some point
References: https://twitter.com/zzzeek/status/1536693554621341697 and
replies
Mike Bayer [Tue, 14 Jun 2022 19:41:31 +0000 (15:41 -0400)]
pickle mutable parents according to key
Fixed bug in :class:`.Mutable` where pickling and unpickling of an ORM
mapped instance would not correctly restore state for mappings that
contained multiple :class:`.Mutable`-enabled attributes.
Mike Bayer [Tue, 14 Jun 2022 13:31:09 +0000 (09:31 -0400)]
typing adjustments for composites
* if dataclass isn't used, columns have to be named
* _CompositeClassProto is not useful as dataclasses have no
methods / bases we can use, so composite is against Any
* Adjust session.get() feature to work w/ dataclass composites
projects / thirdparty / sqlalchemy / sqlalchemy.git / log
