New ORM Query Guide featuring DML support

reviewers:  these docs publish periodically at:
https://docs.sqlalchemy.org/en/gerrit/4042/orm/queryguide/index.html

See the "last generated" timestamp near the bottom of the
page to ensure the latest version is up

Change includes some other adjustments:

* small typing fixes for end-user benefit
* removal of a bunch of old examples for patterns that nobody
  uses or aren't really what we promote now
* modernization of some examples, including inheritance

Change-Id: I9929daab7797be9515f71c888b28af1209e789ff

3 files changed, 60 insertions, 342 deletions

diff --git a/doc/build/tutorial/data_update.rst b/doc/build/tutorial/data_update.rst
index 46236a6e9..ccd274a80 100644
--- a/doc/build/tutorial/data_update.rst
+++ b/doc/build/tutorial/data_update.rst
@@ -346,7 +346,6 @@ Further Reading for UPDATE, DELETE
 
     ORM-enabled UPDATE and DELETE:
 
-    * :ref:`tutorial_orm_enabled_update`
+    :ref:`orm_expression_update_delete` - in the :ref:`queryguide_toplevel`
 
-    * :ref:`tutorial_orm_enabled_delete`
 
diff --git a/doc/build/tutorial/orm_data_manipulation.rst b/doc/build/tutorial/orm_data_manipulation.rst
index f6237f4aa..d33146b55 100644
--- a/doc/build/tutorial/orm_data_manipulation.rst
+++ b/doc/build/tutorial/orm_data_manipulation.rst
@@ -27,15 +27,17 @@ two previous ORM-centric sections in this document:
 
 .. _tutorial_inserting_orm:
 
-Inserting Rows with the ORM
----------------------------
+Inserting Rows using the ORM Unit of Work pattern
+-------------------------------------------------
 
 When using the ORM, the :class:`_orm.Session` object is responsible for
-constructing :class:`_sql.Insert` constructs and emitting them for us in a
-transaction. The way we instruct the :class:`_orm.Session` to do so is by
-**adding** object entries to it; the :class:`_orm.Session` then makes sure
-these new entries will be emitted to the database when they are needed, using
-a process known as a **flush**.
+constructing :class:`_sql.Insert` constructs and emitting them as INSERT
+statements within the ongoing transaction. The way we instruct the
+:class:`_orm.Session` to do so is by **adding** object entries to it; the
+:class:`_orm.Session` then makes sure these new entries will be emitted to the
+database when they are needed, using a process known as a **flush**. The
+overall process used by the :class:`_orm.Session` to persist objects is known
+as the :term:`unit of work` pattern.
 
 Instances of Classes represent Rows
 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
@@ -233,12 +235,10 @@ which is a state they stay in until the :class:`.Session` is closed
   More on this is at :ref:`tutorial_orm_closing`.
 
 
-
-
 .. _tutorial_orm_updating:
 
-Updating ORM Objects
---------------------
+Updating ORM Objects using the Unit of Work pattern
+----------------------------------------------------
 
 In the preceding section :ref:`tutorial_core_update_delete`, we introduced the
 :class:`_sql.Update` construct that represents a SQL UPDATE statement. When
@@ -246,9 +246,7 @@ using the ORM, there are two ways in which this construct is used. The primary
 way is that it is emitted automatically as part of the :term:`unit of work`
 process used by the :class:`_orm.Session`, where an UPDATE statement is emitted
 on a per-primary key basis corresponding to individual objects that have
-changes on them.   A second form of UPDATE is called an "ORM enabled
-UPDATE" and allows us to use the :class:`_sql.Update` construct with the
-:class:`_orm.Session` explicitly; this is described in the next section.
+changes on them.
 
 Supposing we loaded the ``User`` object for the username ``sandy`` into
 a transaction (also showing off the :meth:`_sql.Select.filter_by` method
@@ -321,53 +319,17 @@ we roll back the transaction.  But first we'll make some more data changes.
     :ref:`session_flushing`- details the flush process as well as information
     about the :paramref:`_orm.Session.autoflush` setting.
 
-.. _tutorial_orm_enabled_update:
-
-ORM-enabled UPDATE statements
-^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-
-As previously mentioned, there's a second way to emit UPDATE statements in
-terms of the ORM, which is known as an **ORM enabled UPDATE statement**.   This allows the use
-of a generic SQL UPDATE statement that can affect many rows at once.   For example
-to emit an UPDATE that will change the ``User.fullname`` column based on
-a value in the ``User.name`` column:
-
-.. sourcecode:: pycon+sql
-
-    >>> session.execute(
-    ...     update(User).
-    ...     where(User.name == "sandy").
-    ...     values(fullname="Sandy Squirrel Extraordinaire")
-    ... )
-    {opensql}UPDATE user_account SET fullname=? WHERE user_account.name = ?
-    [...] ('Sandy Squirrel Extraordinaire', 'sandy'){stop}
-    <sqlalchemy.engine.cursor.CursorResult object ...>
-
-When invoking the ORM-enabled UPDATE statement, special logic is used to locate
-objects in the current session that match the given criteria, so that they
-are refreshed with the new data.  Above, the ``sandy`` object identity
-was located in memory and refreshed::
-
-    >>> sandy.fullname
-    'Sandy Squirrel Extraordinaire'
-
-The refresh logic is known as the ``synchronize_session`` option, and is described
-in detail in the section :ref:`orm_expression_update_delete`.
-
-.. seealso::
-
-    :ref:`orm_expression_update_delete` - describes ORM use of :func:`_sql.update`
-    and :func:`_sql.delete` as well as ORM synchronization options.
 
 
 .. _tutorial_orm_deleting:
 
 
-Deleting ORM Objects
----------------------
+Deleting ORM Objects using the Unit of Work pattern
+----------------------------------------------------
 
 To round out the basic persistence operations, an individual ORM object
-may be marked for deletion by using the :meth:`_orm.Session.delete` method.
+may be marked for deletion within the :term:`unit of work` process
+by using the :meth:`_orm.Session.delete` method.
 Let's load up ``patrick`` from the database:
 
 .. sourcecode:: pycon+sql
@@ -428,42 +390,43 @@ we've made here is local to an ongoing transaction, which won't become
 permanent if we don't commit it.  As rolling the transaction back is actually
 more interesting at the moment, we will do that in the next section.
 
-.. _tutorial_orm_enabled_delete:
 
-ORM-enabled DELETE Statements
-^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
 
-Like UPDATE operations, there is also an ORM-enabled version of DELETE which we can
-illustrate by using the :func:`_sql.delete` construct with
-:meth:`_orm.Session.execute`.  It also has a feature by which **non expired**
-objects (see :term:`expired`) that match the given deletion criteria will be
-automatically marked as ":term:`deleted`" in the :class:`_orm.Session`:
+Bulk / Multi Row INSERT, upsert, UPDATE and DELETE
+---------------------------------------------------
 
-.. sourcecode:: pycon+sql
+The :term:`unit of work` techniques discussed in this section
+are intended to integrate :term:`dml`, or INSERT/UPDATE/DELETE statements,
+with Python object mechanics, often involving complex graphs of
+inter-related objects.  Once objects are added to a :class:`.Session` using
+:meth:`.Session.add`, the unit of work process transparently emits
+INSERT/UPDATE/DELETE on our behalf as attributes on our objects are created
+and modified.
 
-    >>> # refresh the target object for demonstration purposes
-    >>> # only, not needed for the DELETE
-    {sql}>>> squidward = session.get(User, 4)
-    SELECT user_account.id AS user_account_id, user_account.name AS user_account_name,
-    user_account.fullname AS user_account_fullname
-    FROM user_account
-    WHERE user_account.id = ?
-    [...] (4,){stop}
+However, the ORM :class:`.Session` also has the ability to process commands
+that allow it to emit INSERT, UPDATE and DELETE statements directly without
+being passed any ORM-persisted objects, instead being passed lists of values to
+be INSERTed, UPDATEd, or upserted, or WHERE criteria so that an UPDATE or
+DELETE statement that matches many rows at once can be invoked. This mode of
+use is of particular importance when large numbers of rows must be affected
+without the need to construct and manipulate mapped objects, which may be
+cumbersome and unnecessary for simplistic, performance-intensive tasks such as
+large bulk inserts.
 
-    >>> session.execute(delete(User).where(User.name == "squidward"))
-    {opensql}DELETE FROM user_account WHERE user_account.name = ?
-    [...] ('squidward',){stop}
-    <sqlalchemy.engine.cursor.CursorResult object at 0x...>
+The Bulk / Multi row features of the ORM :class:`_orm.Session` make use of the
+:func:`_dml.insert`, :func:`_dml.update` and :func:`_dml.delete` constructs
+directly, and their usage resembles how they are used with SQLAlchemy Core
+(first introduced in this tutorial at :ref:`tutorial_core_insert` and
+:ref:`tutorial_core_update_delete`).  When using these constructs
+with the ORM :class:`_orm.Session` instead of a plain :class:`_engine.Connection`,
+their construction, execution and result handling is fully integrated with the ORM.
 
-The ``squidward`` identity, like that of ``patrick``, is now also in a
-deleted state.   Note that we had to re-load ``squidward`` above in order
-to demonstrate this; if the object were expired, the DELETE operation
-would not take the time to refresh expired objects just to see that they
-had been deleted::
+For background and examples on using these features, see the section
+:ref:`orm_expression_update_delete` in the :ref:`queryguide_toplevel`.
 
-    >>> squidward in session
-    False
+.. seealso::
 
+    :ref:`orm_expression_update_delete` - in the :ref:`queryguide_toplevel`
 
 
 Rolling Back
diff --git a/doc/build/tutorial/orm_related_objects.rst b/doc/build/tutorial/orm_related_objects.rst
index a6bb2b9a3..04a3d1728 100644
--- a/doc/build/tutorial/orm_related_objects.rst
+++ b/doc/build/tutorial/orm_related_objects.rst
@@ -313,9 +313,9 @@ right side of the join as well as the ON clause at once::
     FROM user_account JOIN address ON user_account.id = address.user_id
 
 The presence of an ORM :func:`_orm.relationship` on a mapping is not used
-by :meth:`_sql.Select.join` or :meth:`_sql.Select.join_from` if we don't
-specify it; it is **not used for ON clause
-inference**.  This means, if we join from ``User`` to ``Address`` without an
+by :meth:`_sql.Select.join` or :meth:`_sql.Select.join_from`
+to infer the ON clause if we don't
+specify it.  This means, if we join from ``User`` to ``Address`` without an
 ON clause, it works because of the :class:`_schema.ForeignKeyConstraint`
 between the two mapped :class:`_schema.Table` objects, not because of the
 :func:`_orm.relationship` objects on the ``User`` and ``Address`` classes::
@@ -327,199 +327,29 @@ between the two mapped :class:`_schema.Table` objects, not because of the
     {opensql}SELECT address.email_address
     FROM user_account JOIN address ON user_account.id = address.user_id
 
-.. _tutorial_joining_relationships_aliased:
+See the section :ref:`orm_queryguide_joins` in the :ref:`queryguide_toplevel`
+for many more examples of how to use :meth:`.Select.join` and :meth:`.Select.join_from`
+with :func:`_orm.relationship` constructs.
 
-Joining between Aliased targets
-^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-
-In the section :ref:`tutorial_orm_entity_aliases` we introduced the
-:func:`_orm.aliased` construct, which is used to apply a SQL alias to an
-ORM entity.   When using a :func:`_orm.relationship` to help construct SQL JOIN, the
-use case where the target of the join is to be an :func:`_orm.aliased` is suited
-by making use of the :meth:`_orm.PropComparator.of_type` modifier.    To
-demonstrate we will construct the same join illustrated at :ref:`tutorial_orm_entity_aliases`
-using the :func:`_orm.relationship` attributes to join instead::
-
-    >>> print(
-    ...        select(User).
-    ...        join(User.addresses.of_type(address_alias_1)).
-    ...        where(address_alias_1.email_address == 'patrick@aol.com').
-    ...        join(User.addresses.of_type(address_alias_2)).
-    ...        where(address_alias_2.email_address == 'patrick@gmail.com')
-    ...    )
-    {opensql}SELECT user_account.id, user_account.name, user_account.fullname
-    FROM user_account
-    JOIN address AS address_1 ON user_account.id = address_1.user_id
-    JOIN address AS address_2 ON user_account.id = address_2.user_id
-    WHERE address_1.email_address = :email_address_1
-    AND address_2.email_address = :email_address_2
-
-To make use of a :func:`_orm.relationship` to construct a join **from** an
-aliased entity, the attribute is available from the :func:`_orm.aliased`
-construct directly::
-
-    >>> user_alias_1 = aliased(User)
-    >>> print(
-    ...     select(user_alias_1.name).
-    ...     join(user_alias_1.addresses)
-    ... )
-    {opensql}SELECT user_account_1.name
-    FROM user_account AS user_account_1
-    JOIN address ON user_account_1.id = address.user_id
-
-.. _tutorial_joining_relationships_augmented:
-
-Augmenting the ON Criteria
-^^^^^^^^^^^^^^^^^^^^^^^^^^
-
-The ON clause generated by the :func:`_orm.relationship` construct may
-also be augmented with additional criteria.  This is useful both for
-quick ways to limit the scope of a particular join over a relationship path,
-and also for use cases like configuring loader strategies, introduced below
-at :ref:`tutorial_orm_loader_strategies`.  The :meth:`_orm.PropComparator.and_`
-method accepts a series of SQL expressions positionally that will be joined
-to the ON clause of the JOIN via AND.  For example if we wanted to
-JOIN from ``User`` to ``Address`` but also limit the ON criteria to only certain
-email addresses:
-
-.. sourcecode:: pycon+sql
-
-    >>> stmt = (
-    ...   select(User.fullname).
-    ...   join(User.addresses.and_(Address.email_address == 'pearl.krabs@gmail.com'))
-    ... )
-    >>> session.execute(stmt).all()
-    {opensql}SELECT user_account.fullname
-    FROM user_account
-    JOIN address ON user_account.id = address.user_id AND address.email_address = ?
-    [...] ('pearl.krabs@gmail.com',){stop}
-    [('Pearl Krabs',)]
-
-
-.. _tutorial_relationship_exists:
-
-EXISTS forms: has() / any()
-^^^^^^^^^^^^^^^^^^^^^^^^^^^
-
-In the section :ref:`tutorial_exists`, we introduced the :class:`_sql.Exists`
-object that provides for the SQL EXISTS keyword in conjunction with a
-scalar subquery.   The :func:`_orm.relationship` construct provides for some
-helper methods that may be used to generate some common EXISTS styles
-of queries in terms of the relationship.
-
-For a one-to-many relationship such as ``User.addresses``, an EXISTS against
-the ``address`` table that correlates back to the ``user_account`` table
-can be produced using :meth:`_orm.PropComparator.any`.  This method accepts
-an optional WHERE criteria to limit the rows matched by the subquery:
-
-.. sourcecode:: pycon+sql
-
-    >>> stmt = (
-    ...   select(User.fullname).
-    ...   where(User.addresses.any(Address.email_address == 'pearl.krabs@gmail.com'))
-    ... )
-    >>> session.execute(stmt).all()
-    {opensql}SELECT user_account.fullname
-    FROM user_account
-    WHERE EXISTS (SELECT 1
-    FROM address
-    WHERE user_account.id = address.user_id AND address.email_address = ?)
-    [...] ('pearl.krabs@gmail.com',){stop}
-    [('Pearl Krabs',)]
-
-As EXISTS tends to be more efficient for negative lookups, a common query
-is to locate entities where there are no related entities present.  This
-is succinct using a phrase such as ``~User.addresses.any()``, to select
-for ``User`` entities that have no related ``Address`` rows:
-
-.. sourcecode:: pycon+sql
-
-    >>> stmt = (
-    ...   select(User.fullname).
-    ...   where(~User.addresses.any())
-    ... )
-    >>> session.execute(stmt).all()
-    {opensql}SELECT user_account.fullname
-    FROM user_account
-    WHERE NOT (EXISTS (SELECT 1
-    FROM address
-    WHERE user_account.id = address.user_id))
-    [...] (){stop}
-    [('Patrick McStar',), ('Squidward Tentacles',), ('Eugene H. Krabs',)]
-
-The :meth:`_orm.PropComparator.has` method works in mostly the same way as
-:meth:`_orm.PropComparator.any`, except that it's used for many-to-one
-relationships, such as if we wanted to locate all ``Address`` objects
-which belonged to "pearl":
+.. seealso::
 
-.. sourcecode:: pycon+sql
-
-    >>> stmt = (
-    ...   select(Address.email_address).
-    ...   where(Address.user.has(User.name=="pkrabs"))
-    ... )
-    >>> session.execute(stmt).all()
-    {opensql}SELECT address.email_address
-    FROM address
-    WHERE EXISTS (SELECT 1
-    FROM user_account
-    WHERE user_account.id = address.user_id AND user_account.name = ?)
-    [...] ('pkrabs',){stop}
-    [('pearl.krabs@gmail.com',), ('pearl@aol.com',)]
+    :ref:`orm_queryguide_joins` in the :ref:`queryguide_toplevel`
 
 .. _tutorial_relationship_operators:
 
-Common Relationship Operators
+Relationship WHERE Operators
 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
 
 There are some additional varieties of SQL generation helpers that come with
-:func:`_orm.relationship`, including:
-
-* **many to one equals comparison** - a specific object instance can be
-  compared to many-to-one relationship, to select rows where the
-  foreign key of the target entity matches the primary key value of the
-  object given::
-
-      >>> print(select(Address).where(Address.user == u1))
-      {opensql}SELECT address.id, address.email_address, address.user_id
-      FROM address
-      WHERE :param_1 = address.user_id
-
-  ..
-
-* **many to one not equals comparison** - the not equals operator may also
-  be used::
+:func:`_orm.relationship` which are typically useful when building up the
+WHERE clause of a statement.  See the section
+:ref:`orm_queryguide_relationship_operators` in the :ref:`queryguide_toplevel`.
 
-      >>> print(select(Address).where(Address.user != u1))
-      {opensql}SELECT address.id, address.email_address, address.user_id
-      FROM address
-      WHERE address.user_id != :user_id_1 OR address.user_id IS NULL
-
-  ..
-
-* **object is contained in a one-to-many collection** - this is essentially
-  the one-to-many version of the "equals" comparison, select rows where the
-  primary key equals the value of the foreign key in a related object::
-
-      >>> print(select(User).where(User.addresses.contains(a1)))
-      {opensql}SELECT user_account.id, user_account.name, user_account.fullname
-      FROM user_account
-      WHERE user_account.id = :param_1
-
-  ..
+.. seealso::
 
-* **An object has a particular parent from a one-to-many perspective** - the
-  :func:`_orm.with_parent` function produces a comparison that returns rows
-  which are referred towards by a given parent, this is essentially the
-  same as using the ``==`` operator with the many-to-one side::
+    :ref:`orm_queryguide_relationship_operators` in the :ref:`queryguide_toplevel`
 
-      >>> from sqlalchemy.orm import with_parent
-      >>> print(select(Address).where(with_parent(u1, User.addresses)))
-      {opensql}SELECT address.id, address.email_address, address.user_id
-      FROM address
-      WHERE :param_1 = address.user_id
 
-  ..
 
 .. _tutorial_orm_loader_strategies:
 
@@ -690,15 +520,6 @@ is applied to the ``user_account`` table such that is not directly addressable
 in the query.   This concept is discussed in more detail in the section
 :ref:`zen_of_eager_loading`.
 
-The ON clause rendered by :func:`_orm.joinedload` may be affected directly by
-using the :meth:`_orm.PropComparator.and_` method described previously at
-:ref:`tutorial_joining_relationships_augmented`; examples of this technique
-with loader strategies are further below at :ref:`tutorial_loader_strategy_augmented`.
-However, more generally, "joined eager loading" may be applied to a
-:class:`_sql.Select` that uses :meth:`_sql.Select.join` using the approach
-described in the next section,
-:ref:`tutorial_orm_loader_strategies_contains_eager`.
-
 
 .. tip::
 
@@ -777,71 +598,6 @@ SQL query that unnecessarily joins twice::
 
     * :ref:`contains_eager` - using :func:`.contains_eager`
 
-.. _tutorial_loader_strategy_augmented:
-
-Augmenting Loader Strategy Paths
-^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-
-In :ref:`tutorial_joining_relationships_augmented` we illustrated how to add
-arbitrary criteria to a JOIN rendered with :func:`_orm.relationship` to also
-include additional criteria in the ON clause.   The :meth:`_orm.PropComparator.and_`
-method is in fact generally available for most loader options.   For example,
-if we wanted to re-load the names of users and their email addresses, but omitting
-the email addresses with the ``sqlalchemy.org`` domain, we can apply
-:meth:`_orm.PropComparator.and_` to the argument passed to
-:func:`_orm.selectinload` to limit this criteria:
-
-
-.. sourcecode:: pycon+sql
-
-    >>> from sqlalchemy.orm import selectinload
-    >>> stmt = (
-    ...   select(User).
-    ...   options(
-    ...       selectinload(
-    ...           User.addresses.and_(
-    ...             ~Address.email_address.endswith("sqlalchemy.org")
-    ...           )
-    ...       )
-    ...   ).
-    ...   order_by(User.id).
-    ...   execution_options(populate_existing=True)
-    ... )
-    >>> for row in session.execute(stmt):
-    ...     print(f"{row.User.name}  ({', '.join(a.email_address for a in row.User.addresses)})")
-    {opensql}SELECT user_account.id, user_account.name, user_account.fullname
-    FROM user_account ORDER BY user_account.id
-    [...] ()
-    SELECT address.user_id AS address_user_id, address.id AS address_id,
-    address.email_address AS address_email_address
-    FROM address
-    WHERE address.user_id IN (?, ?, ?, ?, ?, ?)
-    AND (address.email_address NOT LIKE '%' || ?)
-    [...] (1, 2, 3, 4, 5, 6, 'sqlalchemy.org'){stop}
-    spongebob  ()
-    sandy  (sandy@squirrelpower.org)
-    patrick  ()
-    squidward  ()
-    ehkrabs  ()
-    pkrabs  (pearl.krabs@gmail.com, pearl@aol.com)
-
-
-A very important thing to note above is that a special option is added with
-``.execution_options(populate_existing=True)``.    This option which takes
-effect when rows are being fetched indicates that the loader option we are
-using should **replace** the existing contents of collections on the objects,
-if they are already loaded.  As we are working with a single
-:class:`_orm.Session` repeatedly, the objects we see being loaded above are the
-same Python instances as those that were first persisted at the start of the
-ORM section of this tutorial.
-
-
-.. seealso::
-
-    :ref:`loader_option_criteria` - in :ref:`loading_toplevel`
-
-    :ref:`orm_queryguide_populate_existing` - in :ref:`queryguide_toplevel`
-
 
 Raiseload
 ^^^^^^^^^