From 149584d322da1654226e204a71d1aa35bebb24a6 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sat, 15 Mar 2014 15:44:29 -0400 Subject: [PATCH] - fairly epic rework of the cascade documentation Conflicts: lib/sqlalchemy/orm/relationships.py --- doc/build/core/constraints.rst | 2 + doc/build/glossary.rst | 12 + doc/build/orm/relationships.rst | 2 + doc/build/orm/session.rst | 419 +++++++++++++++++++++++--------- lib/sqlalchemy/orm/__init__.py | 74 ++---- 5 files changed, 337 insertions(+), 172 deletions(-) diff --git a/doc/build/core/constraints.rst b/doc/build/core/constraints.rst index 75150cbd98..5bfb6203a6 100644 --- a/doc/build/core/constraints.rst +++ b/doc/build/core/constraints.rst @@ -129,6 +129,8 @@ For example:: ) ) +.. _on_update_on_delete: + ON UPDATE and ON DELETE ~~~~~~~~~~~~~~~~~~~~~~~ diff --git a/doc/build/glossary.rst b/doc/build/glossary.rst index 7603b8c4b9..6e4be73fec 100644 --- a/doc/build/glossary.rst +++ b/doc/build/glossary.rst @@ -81,6 +81,18 @@ Glossary >>> inspect(m1).attrs.data.history.added "some data" + DDL + An acronym for *Data Definition Language*. DDL is the subset + of SQL that relational databases use to configure tables, constraints, + and other permanent objects within a database schema. SQLAlchemy + provides a rich API for constructing and emitting DDL expressions. + + .. seealso:: + + :ref:`metadata_toplevel` + + `DDL (via Wikipedia) `_ + discriminator A result-set column which is used during :term:`polymorphic` loading to determine what kind of mapped class should be applied to a particular diff --git a/doc/build/orm/relationships.rst b/doc/build/orm/relationships.rst index cef1b4580d..37a7548d67 100644 --- a/doc/build/orm/relationships.rst +++ b/doc/build/orm/relationships.rst @@ -187,6 +187,8 @@ is accepted as well, matching the name of the table as stored in ``Base.metadata secondary="association", backref="parents") +.. _relationships_many_to_many_deletion: + Deleting Rows from the Many to Many Table ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ diff --git a/doc/build/orm/session.rst b/doc/build/orm/session.rst index 5b65ec1141..cdc563fbfb 100644 --- a/doc/build/orm/session.rst +++ b/doc/build/orm/session.rst @@ -725,7 +725,7 @@ and made our ``a1`` object pending, as though we had added it. Now we have Above, our ``a1`` is already pending in the session. The subsequent :meth:`~.Session.merge` operation essentially -does nothing. Cascade can be configured via the ``cascade`` +does nothing. Cascade can be configured via the :paramref:`~.relationship.cascade` option on :func:`.relationship`, although in this case it would mean removing the ``save-update`` cascade from the ``User.addresses`` relationship - and usually, that behavior @@ -1132,28 +1132,29 @@ setting. Cascades ======== -Mappers support the concept of configurable **cascade** behavior on +Mappers support the concept of configurable :term:`cascade` behavior on :func:`~sqlalchemy.orm.relationship` constructs. This refers -to how operations performed on a parent object relative to a +to how operations performed on a "parent" object relative to a particular :class:`.Session` should be propagated to items -referred to by that relationship. -The default cascade behavior is usually suitable for -most situations, and the option is normally invoked explicitly -in order to enable ``delete`` and ``delete-orphan`` cascades, -which refer to how the relationship should be treated when -the parent is marked for deletion as well as when a child -is de-associated from its parent. - -Cascade behavior is configured by setting the ``cascade`` keyword -argument on +referred to by that relationship (e.g. "child" objects), and is +affected by the :paramref:`.relationship.cascade` option. + +The default behavior of cascade is limited to cascades of the +so-called :ref:`cascade_save_update` and :ref:`cascade_merge` settings. +The typical "alternative" setting for cascade is to add +the :ref:`cascade_delete` and :ref:`cascade_delete_orphan` options; +these settings are appropriate for related objects which only exist as +long as they are attached to their parent, and are otherwise deleted. + +Cascade behavior is configured using the by changing the +:paramref:`~.relationship.cascade` option on :func:`~sqlalchemy.orm.relationship`:: class Order(Base): __tablename__ = 'order' items = relationship("Item", cascade="all, delete-orphan") - customer = relationship("User", secondary=user_orders_table, - cascade="save-update") + customer = relationship("User", cascade="save-update") To set cascades on a backref, the same flag can be used with the :func:`~.sqlalchemy.orm.backref` function, which ultimately feeds @@ -1166,109 +1167,303 @@ its arguments back into :func:`~sqlalchemy.orm.relationship`:: backref=backref("items", cascade="all, delete-orphan") ) -The default value of ``cascade`` is ``save-update, merge``. -The ``all`` symbol in the cascade options indicates that all -cascade flags should be enabled, with the exception of ``delete-orphan``. -Typically, cascade is usually left at its default, or configured -as ``all, delete-orphan``, indicating the child objects should be -treated as "owned" by the parent. - -The list of available values which can be specified in ``cascade`` -are as follows: - -* ``save-update`` - Indicates that when an object is placed into a - :class:`.Session` - via :meth:`.Session.add`, all the objects associated with it via this - :func:`~sqlalchemy.orm.relationship` should also be added to that - same :class:`.Session`. Additionally, if this object is already present in - a :class:`.Session`, child objects will be added to that session as they - are associated with this parent, i.e. as they are appended to lists, - added to sets, or otherwise associated with the parent. - - ``save-update`` cascade also cascades the *pending history* of the - target attribute, meaning that objects which were - removed from a scalar or collection attribute whose changes have not - yet been flushed are also placed into the target session. This - is because they may have foreign key attributes present which - will need to be updated to no longer refer to the parent. - - The ``save-update`` cascade is on by default, and it's common to not - even be aware of it. It's customary that only a single call to - :meth:`.Session.add` against the lead object of a structure - has the effect of placing the full structure of - objects into the :class:`.Session` at once. - - However, it can be turned off, which would - imply that objects associated with a parent would need to be - placed individually using :meth:`.Session.add` calls for - each one. - - Another default behavior of ``save-update`` cascade is that it will - take effect in the reverse direction, that is, associating a child - with a parent when a backref is present means both relationships - are affected; the parent will be added to the child's session. - To disable this somewhat indirect session addition, use the - ``cascade_backrefs=False`` option described below in - :ref:`backref_cascade`. - -* ``delete`` - This cascade indicates that when the parent object - is marked for deletion, the related objects should also be marked - for deletion. Without this cascade present, SQLAlchemy will - set the foreign key on a one-to-many relationship to NULL - when the parent object is deleted. When enabled, the row is instead - deleted. - - ``delete`` cascade is often used in conjunction with ``delete-orphan`` - cascade, as is appropriate for an object whose foreign key is - not intended to be nullable. On some backends, it's also - a good idea to set ``ON DELETE`` on the foreign key itself; - see the section :ref:`passive_deletes` for more details. - - Note that for many-to-many relationships which make usage of the - ``secondary`` argument to :func:`~.sqlalchemy.orm.relationship`, - SQLAlchemy always emits - a DELETE for the association row in between "parent" and "child", - when the parent is deleted or whenever the linkage between a particular - parent and child is broken. - -* ``delete-orphan`` - This cascade adds behavior to the ``delete`` cascade, - such that a child object will be marked for deletion when it is - de-associated from the parent, not just when the parent is marked - for deletion. This is a common feature when dealing with a related - object that is "owned" by its parent, with a NOT NULL foreign key, - so that removal of the item from the parent collection results - in its deletion. - - ``delete-orphan`` cascade implies that each child object can only - have one parent at a time, so is configured in the vast majority of cases - on a one-to-many relationship. Setting it on a many-to-one or - many-to-many relationship is more awkward; for this use case, - SQLAlchemy requires that the :func:`~sqlalchemy.orm.relationship` - be configured with the :paramref:`~.relationship.single_parent` argument, - establishes Python-side validation that ensures the object - is associated with only one parent at a time. - -* ``merge`` - This cascade indicates that the :meth:`.Session.merge` - operation should be propagated from a parent that's the subject - of the :meth:`.Session.merge` call down to referred objects. - This cascade is also on by default. - -* ``refresh-expire`` - A less common option, indicates that the - :meth:`.Session.expire` operation should be propagated from a parent - down to referred objects. When using :meth:`.Session.refresh`, - the referred objects are expired only, but not actually refreshed. - -* ``expunge`` - Indicate that when the parent object is removed - from the :class:`.Session` using :meth:`.Session.expunge`, the - operation should be propagated down to referred objects. +.. sidebar:: The Origins of Cascade + + SQLAlchemy's notion of cascading behavior on relationships, + as well as the options to configure them, are primarily derived + from the similar feature in the Hibernate ORM; Hibernate refers + to "cascade" in a few places such as in + `Example: Parent/Child `_. + If cascades are confusing, we'll refer to their conclusion, + stating "The sections we have just covered can be a bit confusing. + However, in practice, it all works out nicely.". + +The default value of :paramref:`~.relationship.cascade` is ``save-update, merge``. +The typical alternative setting for this parameter is either +``all`` or more commonly ``all, delete-orphan``. The ``all`` symbol +is a synonym for ``save-update, merge, refresh-expire, expunge, delete``, +and using it in conjunction with ``delete-orphan`` indicates that the child +object should follow along with its parent in all cases, and be deleted once +it is no longer associated with that parent. + +The list of available values which can be specified for +the :paramref:`~.relationship.cascade` parameter are described in the following subsections. + +.. _cascade_save_update: + +save-update +----------- + +``save-update`` cacade indicates that when an object is placed into a +:class:`.Session` via :meth:`.Session.add`, all the objects associated +with it via this :func:`.relationship` should also be added to that +same :class:`.Session`. Suppose we have an object ``user1`` with two +related objects ``address1``, ``address2``:: + + >>> user1 = User() + >>> address1, address2 = Address(), Address() + >>> user1.addresses = [address1, address2] + +If we add ``user1`` to a :class:`.Session`, it will also add +``address1``, ``address2`` implicitly:: + + >>> sess = Session() + >>> sess.add(user1) + >>> address1 in sess + True + +``save-update`` cascade also affects attribute operations for objects +that are already present in a :class:`.Session`. If we add a third +object, ``address3`` to the ``user1.addresses`` collection, it +becomes part of the state of that :class:`.Session`:: + + >>> address3 = Address() + >>> user1.append(address3) + >>> address3 in sess + >>> True + +``save-update`` has the possibly surprising behavior which is that +persistent objects which were *removed* from a collection +or in some cases a scalar attribute +may also be pulled into the :class:`.Session` of a parent object; this is +so that the flush process may handle that related object appropriately. +This case can usually only arise if an object is removed from one :class:`.Session` +and added to another:: + + >>> user1 = sess1.query(User).filter_by(id=1).first() + >>> address1 = user1.addresses[0] + >>> sess1.close() # user1, address1 no longer associated with sess1 + >>> user1.addresses.remove(address1) # address1 no longer associated with user1 + >>> sess2 = Session() + >>> sess2.add(user1) # ... but it still gets added to the new session, + >>> address1 in sess2 # because it's still "pending" for flush + True + +The ``save-update`` cascade is on by default, and is typically taken +for granted; it simplifies code by allowing a single call to +:meth:`.Session.add` to register an entire structure of objects within +that :class:`.Session` at once. While it can be disabled, there +is usually not a need to do so. + +One case where ``save-update`` cascade does sometimes get in the way is in that +it takes place in both directions for bi-directional relationships, e.g. +backrefs, meaning that the association of a child object with a particular parent +can have the effect of the parent object being implicitly associated with that +child object's :class:`.Session`; this pattern, as well as how to modify its +behavior using the :paramref:`~.relationship.cascade_backrefs` flag, +is discussed in the section :ref:`backref_cascade`. + +.. _cascade_delete: + +delete +------ + +The ``delete`` cascade indicates that when a "parent" object +is marked for deletion, its related "child" objects should also be marked +for deletion. If for example we we have a relationship ``User.addresses`` +with ``delete`` cascade configured:: + + class User(Base): + # ... + + addresses = relationship("Address", cascade="save-update, merge, delete") + +If using the above mapping, we have a ``User`` object and two +related ``Address`` objects:: + + >>> user1 = sess.query(User).filter_by(id=1).first() + >>> address1, address2 = user1.addresses + +If we mark ``user1`` for deletion, after the flush operation proceeds, +``address1`` and ``address2`` will also be deleted: + +.. sourcecode:: python+sql + + >>> sess.delete(user1) + >>> sess.commit() + {opensql}DELETE FROM address WHERE address.id = ? + ((1,), (2,)) + DELETE FROM user WHERE user.id = ? + (1,) + COMMIT + +Alternatively, if our ``User.addresses`` relationship does *not* have +``delete`` cascade, SQLAlchemy's default behavior is to instead de-associate +``address1`` and ``address2`` from ``user1`` by setting their foreign key +reference to ``NULL``. Using a mapping as follows:: + + class User(Base): + # ... + + addresses = relationship("Address") + +Upon deletion of a parent ``User`` object, the rows in ``address`` are not +deleted, but are instead de-associated: + +.. sourcecode:: python+sql + + >>> sess.delete(user1) + >>> sess.commit() + {opensql}UPDATE address SET user_id=? WHERE address.id = ? + (None, 1) + UPDATE address SET user_id=? WHERE address.id = ? + (None, 2) + DELETE FROM user WHERE user.id = ? + (1,) + COMMIT + +``delete`` cascade is more often than not used in conjunction with +:ref:`cascade_delete_orphan` cascade, which will emit a DELETE for the related +row if the "child" object is deassociated from the parent. The combination +of ``delete`` and ``delete-orphan`` cascade covers both situations where +SQLAlchemy has to decide between setting a foreign key column to NULL versus +deleting the row entirely. + +.. topic:: ORM-level "delete" cascade vs. FOREIGN KEY level "ON DELETE" cascade + + The behavior of SQLAlchemy's "delete" cascade has a lot of overlap with the + ``ON DELETE CASCADE`` feature of a database foreign key, as well + as with that of the ``ON DELETE SET NULL`` foreign key setting when "delete" + cascade is not specified. Database level "ON DELETE" cascades are specific to the + "FOREIGN KEY" construct of the relational database; SQLAlchemy allows + configuration of these schema-level constructs at the :term:`DDL` level + using options on :class:`.ForeignKeyConstraint` which are described + at :ref:`on_update_on_delete`. + + It is important to note the differences between the ORM and the relational + database's notion of "cascade" as well as how they integrate: + + * A database level ``ON DELETE`` cascade is configured effectively + on the **many-to-one** side of the relationship; that is, we configure + it relative to the ``FOREIGN KEY`` constraint that is the "many" side + of a relationship. At the ORM level, **this direction is reversed**. + SQLAlchemy handles the deletion of "child" objects relative to a + "parent" from the "parent" side, which means that ``delete`` and + ``delete-orphan`` cascade are configured on the **one-to-many** + side. + + * Database level foreign keys with no ``ON DELETE`` setting + are often used to **prevent** a parent + row from being removed, as it would necessarily leave an unhandled + related row present. If this behavior is desired in a one-to-many + relationship, SQLAlchemy's default behavior of setting a foreign key + to ``NULL`` can be caught in one of two ways: + + * The easiest and most common is just to to set the + foreign-key-holding column to ``NOT NULL`` at the database schema + level. An attempt by SQLAlchemy to set the column to NULL will + fail with a simple NOT NULL constraint exception. + + * The other, more special case way is to set the :paramref:`~.relationship.passive_deletes` + flag to the string ``"all"``. This has the effect of entirely + disabling SQLAlchemy's behavior of setting the foreign key column + to NULL, and a DELETE will be emitted for the parent row without + any affect on the child row, even if the child row is present + in memory. This may be desirable in the case when + database-level foreign key triggers, either special ``ON DELETE`` settings + or otherwise, need to be activated in all cases when a parent row is deleted. + + * Database level ``ON DELETE`` cascade is **vastly more efficient** + than that of SQLAlchemy. The database can chain a series of cascade + operations across many relationships at once; e.g. if row A is deleted, + all the related rows in table B can be deleted, and all the C rows related + to each of those B rows, and on and on, all within the scope of a single + DELETE statement. SQLAlchemy on the other hand, in order to support + the cascading delete operation fully, has to individually load each + related collection in order to target all rows that then may have further + related collections. That is, SQLAlchemy isn't sophisticated enough + to emit a DELETE for all those related rows at once within this context. + + * SQLAlchemy doesn't **need** to be this sophisticated, as we instead provide + smooth integration with the database's own ``ON DELETE`` functionality, + by using the :paramref:`~.relationship.passive_deletes` option in conjunction + with properly configured foreign key constraints. Under this behavior, + SQLAlchemy only emits DELETE for those rows that are already locally + present in the :class:`.Session`; for any collections that are unloaded, + it leaves them to the database to handle, rather than emitting a SELECT + for them. The section :ref:`passive_deletes` provides an example of this use. + + * While database-level ``ON DELETE`` functionality works only on the "many" + side of a relationship, SQLAlchemy's "delete" cascade + has **limited** ability to operate in the *reverse* direction as well, + meaning it can be configured on the "many" side to delete an object + on the "one" side when the reference on the "many" side is deleted. However + this can easily result in constraint violations if there are other objects + referring to this "one" side from the "many", so it typically is only + useful when a relationship is in fact a "one to one". The + :paramref:`~.relationship.single_parent` flag should be used to establish + an in-Python assertion for this case. + + +When using a :func:`.relationship` that also includes a many-to-many +table using the :paramref:`~.relationship.secondary` option, SQLAlchemy's +delete cascade handles the rows in this many-to-many table automatically. +Just like, as described in :ref:`relationships_many_to_many_deletion`, +the addition or removal of an object from a many-to-many collection +results in the INSERT or DELETE of a row in the many-to-many table, +the ``delete`` cascade, when activated as the result of a parent object +delete operation, will DELETE not just the row in the "child" table but also +in the many-to-many table. + +.. _cascade_delete_orphan: + +delete-orphan +------------- + +``delete-orphan`` cascade adds behavior to the ``delete`` cascade, +such that a child object will be marked for deletion when it is +de-associated from the parent, not just when the parent is marked +for deletion. This is a common feature when dealing with a related +object that is "owned" by its parent, with a NOT NULL foreign key, +so that removal of the item from the parent collection results +in its deletion. + +``delete-orphan`` cascade implies that each child object can only +have one parent at a time, so is configured in the vast majority of cases +on a one-to-many relationship. Setting it on a many-to-one or +many-to-many relationship is more awkward; for this use case, +SQLAlchemy requires that the :func:`~sqlalchemy.orm.relationship` +be configured with the :paramref:`~.relationship.single_parent` argument, +establishes Python-side validation that ensures the object +is associated with only one parent at a time. + +.. _cascade_merge: + +merge +----- + +``merge`` cascade indicates that the :meth:`.Session.merge` +operation should be propagated from a parent that's the subject +of the :meth:`.Session.merge` call down to referred objects. +This cascade is also on by default. + +.. _cascade_refresh_expire: + +refresh-expire +-------------- + +``refresh-expire`` is an uncommon option, indicating that the +:meth:`.Session.expire` operation should be propagated from a parent +down to referred objects. When using :meth:`.Session.refresh`, +the referred objects are expired only, but not actually refreshed. + +.. _cascade_expunge: + +expunge +------- + +``expunge`` cascade indicates that when the parent object is removed +from the :class:`.Session` using :meth:`.Session.expunge`, the +operation should be propagated down to referred objects. .. _backref_cascade: Controlling Cascade on Backrefs ------------------------------- -The ``save-update`` cascade takes place on backrefs by default. This means -that, given a mapping such as this:: +The :ref:`cascade_save_update` cascade by default takes place on attribute change events +emitted from backrefs. This is probably a confusing statement more +easily described through demonstration; it means that, given a mapping such as this:: mapper(Order, order_table, properties={ 'items' : relationship(Item, backref='order') diff --git a/lib/sqlalchemy/orm/__init__.py b/lib/sqlalchemy/orm/__init__.py index de5f0ba926..8a296cf740 100644 --- a/lib/sqlalchemy/orm/__init__.py +++ b/lib/sqlalchemy/orm/__init__.py @@ -318,47 +318,20 @@ def relationship(argument, secondary=None, **kwargs): a comma-separated list of cascade rules which determines how Session operations should be "cascaded" from parent to child. This defaults to ``False``, which means the default cascade - should be used. The default value is ``"save-update, merge"``. + should be used - this default cascade is ``"save-update, merge"``. - Available cascades are: - - * ``save-update`` - cascade the :meth:`.Session.add` - operation. This cascade applies both to future and - past calls to :meth:`.Session.add`, - meaning new items added to a collection or scalar relationship - get placed into the same session as that of the parent, and - also applies to items which have been removed from this - relationship but are still part of unflushed history. - - * ``merge`` - cascade the :meth:`.Session.merge` - operation - - * ``expunge`` - cascade the :meth:`.Session.expunge` - operation - - * ``delete`` - cascade the :meth:`.Session.delete` - operation - - * ``delete-orphan`` - if an item of the child's type is - detached from its parent, mark it for deletion. - - .. versionchanged:: 0.7 - This option does not prevent - a new instance of the child object from being persisted - without a parent to start with; to constrain against - that case, ensure the child's foreign key column(s) - is configured as NOT NULL - - * ``refresh-expire`` - cascade the :meth:`.Session.expire` - and :meth:`.Session.refresh` operations - - * ``all`` - shorthand for "save-update,merge, refresh-expire, - expunge, delete" + The available cascades are ``save-update``, ``merge``, + ``expunge``, ``delete``, ``delete-orphan``, and ``refresh-expire``. + An additional option, ``all`` indicates shorthand for + ``"save-update, merge, refresh-expire, + expunge, delete"``, and is often used as in ``"all, delete-orphan"`` + to indicate that related objects should follow along with the + parent object in all cases, and be deleted when de-associated. .. seealso:: - :ref:`unitofwork_cascades` - Introductory documentation and - examples. + :ref:`unitofwork_cascades` - Full detail on each of the available + cascade options. :ref:`tutorial_delete_cascade` - Tutorial example describing a delete cascade. @@ -366,33 +339,14 @@ def relationship(argument, secondary=None, **kwargs): :param cascade_backrefs=True: a boolean value indicating if the ``save-update`` cascade should operate along an assignment event intercepted by a backref. - When set to ``False``, - the attribute managed by this relationship will not cascade - an incoming transient object into the session of a + When set to ``False``, the attribute managed by this relationship + will not cascade an incoming transient object into the session of a persistent parent, if the event is received via backref. - That is:: - - mapper(A, a_table, properties={ - 'bs':relationship(B, backref="a", cascade_backrefs=False) - }) - - If an ``A()`` is present in the session, assigning it to - the "a" attribute on a transient ``B()`` will not place - the ``B()`` into the session. To set the flag in the other - direction, i.e. so that ``A().bs.append(B())`` won't add - a transient ``A()`` into the session for a persistent ``B()``:: - - mapper(A, a_table, properties={ - 'bs':relationship(B, - backref=backref("a", cascade_backrefs=False) - ) - }) - .. seealso:: - :ref:`backref_cascade` - Introductory documentation and - examples. + :ref:`backref_cascade` - Full discussion and examples on how + the :paramref:`~.relationship.cascade_backrefs` option is used. :param collection_class: a class or callable that returns a new list-holding object. will -- 2.39.5