Spezielle Beziehungspersistenzmuster

Zeilen, die auf sich selbst verweisen / Gegenseitig abhängige Zeilen

Dies ist ein sehr spezifischer Fall, bei dem `relationship()` einen INSERT und ein zweites UPDATE ausführen muss, um eine Zeile korrekt zu befüllen (und umgekehrt ein UPDATE und DELETE, um zu löschen, ohne Fremdschlüsselbeschränkungen zu verletzen). Die beiden Anwendungsfälle sind:

  • Eine Tabelle enthält einen Fremdschlüssel auf sich selbst, und eine einzelne Zeile enthält einen Fremdschlüsselwert, der auf ihren eigenen Primärschlüssel verweist.

  • Zwei Tabellen enthalten jeweils einen Fremdschlüssel, der auf die andere Tabelle verweist, wobei sich eine Zeile in jeder Tabelle auf die andere bezieht.

Zum Beispiel:

          user
---------------------------------
user_id    name   related_user_id
   1       'ed'          1

Oder:

             widget                                                  entry
-------------------------------------------             ---------------------------------
widget_id     name        favorite_entry_id             entry_id      name      widget_id
   1       'somewidget'          5                         5       'someentry'     1

Im ersten Fall verweist eine Zeile auf sich selbst. Technisch gesehen kann eine Datenbank, die Sequenzen wie PostgreSQL oder Oracle Database verwendet, die Zeile sofort mit einem zuvor generierten Wert einfügen. Datenbanken, die auf Autoincrement-ähnliche Primärschlüssel-Identifikatoren angewiesen sind, können dies nicht. Die Funktion relationship() geht während des Flushes immer von einem "Eltern/Kind"-Modell der Zeilenbefüllung aus. Daher muss relationship() zwei Anweisungen verwenden, es sei denn, Sie befüllen die Primärschlüssel-/Fremdschlüsselspalten direkt.

Im zweiten Fall muss die "Widget"-Zeile eingefügt werden, bevor eine verweisende "Entry"-Zeile erstellt wird. Anschließend kann die Spalte "favorite_entry_id" dieser "Widget"-Zeile erst gesetzt werden, wenn die "Entry"-Zeilen generiert wurden. In diesem Fall ist es in der Regel unmöglich, die "Widget"- und "Entry"-Zeilen mit nur zwei INSERT-Anweisungen einzufügen. Ein UPDATE muss durchgeführt werden, um die Fremdschlüsselbeschränkungen einzuhalten. Die Ausnahme bildet, wenn die Fremdschlüssel als "bis zum Commit verzögert" konfiguriert sind (eine Funktion, die einige Datenbanken unterstützen) und wenn die Bezeichner manuell befüllt wurden (was im Wesentlichen relationship() umgeht).

Um die Verwendung einer ergänzenden UPDATE-Anweisung zu ermöglichen, verwenden wir die Option relationship.post_update von relationship(). Dies gibt an, dass die Verknüpfung zwischen den beiden Zeilen nach dem INSERT beider Zeilen mittels einer UPDATE-Anweisung erstellt werden soll; es bewirkt auch, dass die Zeilen vor der Ausgabe eines DELETE mittels UPDATE voneinander gelöst werden. Das Flag sollte nur auf *einer* der Beziehungen platziert werden, vorzugsweise auf der Many-to-One-Seite. Im Folgenden zeigen wir ein vollständiges Beispiel, einschließlich zweier ForeignKey-Konstrukte.

from sqlalchemy import Integer, ForeignKey
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import relationship


class Base(DeclarativeBase):
    pass


class Entry(Base):
    __tablename__ = "entry"
    entry_id = mapped_column(Integer, primary_key=True)
    widget_id = mapped_column(Integer, ForeignKey("widget.widget_id"))
    name = mapped_column(String(50))


class Widget(Base):
    __tablename__ = "widget"

    widget_id = mapped_column(Integer, primary_key=True)
    favorite_entry_id = mapped_column(
        Integer, ForeignKey("entry.entry_id", name="fk_favorite_entry")
    )
    name = mapped_column(String(50))

    entries = relationship(Entry, primaryjoin=widget_id == Entry.widget_id)
    favorite_entry = relationship(
        Entry, primaryjoin=favorite_entry_id == Entry.entry_id, post_update=True
    )

Wenn eine Struktur basierend auf der obigen Konfiguration geleert wird, wird die "Widget"-Zeile ohne den Wert "favorite_entry_id" eingefügt, dann werden alle "Entry"-Zeilen eingefügt, die auf die übergeordnete "Widget"-Zeile verweisen, und dann wird eine UPDATE-Anweisung die Spalte "favorite_entry_id" der "Widget"-Tabelle befüllen (derzeit erfolgt dies zeilenweise).

>>> w1 = Widget(name="somewidget")
>>> e1 = Entry(name="someentry")
>>> w1.favorite_entry = e1
>>> w1.entries = [e1]
>>> session.add_all([w1, e1])
>>> session.commit()

BEGIN (implicit)
INSERT INTO widget (favorite_entry_id, name) VALUES (?, ?)
(None, 'somewidget')
INSERT INTO entry (widget_id, name) VALUES (?, ?)
(1, 'someentry')
UPDATE widget SET favorite_entry_id=? WHERE widget.widget_id = ?
(1, 1)
COMMIT

Eine zusätzliche Konfiguration, die wir angeben können, besteht darin, eine umfassendere Fremdschlüsselbeschränkung für Widget bereitzustellen, sodass garantiert ist, dass favorite_entry_id sich auf einen Entry bezieht, der auch auf diesen Widget verweist. Wir können einen zusammengesetzten Fremdschlüssel verwenden, wie unten gezeigt.

from sqlalchemy import (
    Integer,
    ForeignKey,
    String,
    UniqueConstraint,
    ForeignKeyConstraint,
)
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship


class Base(DeclarativeBase):
    pass


class Entry(Base):
    __tablename__ = "entry"
    entry_id = mapped_column(Integer, primary_key=True)
    widget_id = mapped_column(Integer, ForeignKey("widget.widget_id"))
    name = mapped_column(String(50))
    __table_args__ = (UniqueConstraint("entry_id", "widget_id"),)


class Widget(Base):
    __tablename__ = "widget"

    widget_id = mapped_column(Integer, autoincrement="ignore_fk", primary_key=True)
    favorite_entry_id = mapped_column(Integer)

    name = mapped_column(String(50))

    __table_args__ = (
        ForeignKeyConstraint(
            ["widget_id", "favorite_entry_id"],
            ["entry.widget_id", "entry.entry_id"],
            name="fk_favorite_entry",
        ),
    )

    entries = relationship(
        Entry, primaryjoin=widget_id == Entry.widget_id, foreign_keys=Entry.widget_id
    )
    favorite_entry = relationship(
        Entry,
        primaryjoin=favorite_entry_id == Entry.entry_id,
        foreign_keys=favorite_entry_id,
        post_update=True,
    )

Das obige Mapping weist eine zusammengesetzte ForeignKeyConstraint auf, die die Spalten widget_id und favorite_entry_id verbindet. Um sicherzustellen, dass Widget.widget_id eine "automatisch inkrementierende" Spalte bleibt, geben wir Column.autoincrement den Wert "ignore_fk" auf Column an. Zusätzlich müssen wir auf jeder relationship() die Spalten begrenzen, die für den Zweck der Verknüpfung und der Kreuzbefüllung als Teil des Fremdschlüssels betrachtet werden.

Veränderliche Primärschlüssel / Update-Kaskaden

Wenn sich der Primärschlüssel einer Entität ändert, müssen auch zugehörige Elemente, die auf den Primärschlüssel verweisen, aktualisiert werden. Für Datenbanken, die die referenzielle Integrität erzwingen, ist die beste Strategie die ON UPDATE CASCADE-Funktionalität der Datenbank zu nutzen, um Änderungen des Primärschlüssels auf referenzierte Fremdschlüssel zu übertragen – die Werte dürfen keinen Moment lang inkonsistent sein, es sei denn, die Beschränkungen sind als "verzögerbar" markiert, d. h. bis zum Abschluss der Transaktion nicht erzwungen.

Es wird **dringend empfohlen**, dass eine Anwendung, die natürliche Primärschlüssel mit veränderlichen Werten verwenden möchte, die ON UPDATE CASCADE-Funktionen der Datenbank nutzt. Ein Beispiel-Mapping, das dies veranschaulicht, ist:

class User(Base):
    __tablename__ = "user"
    __table_args__ = {"mysql_engine": "InnoDB"}

    username = mapped_column(String(50), primary_key=True)
    fullname = mapped_column(String(100))

    addresses = relationship("Address")


class Address(Base):
    __tablename__ = "address"
    __table_args__ = {"mysql_engine": "InnoDB"}

    email = mapped_column(String(50), primary_key=True)
    username = mapped_column(
        String(50), ForeignKey("user.username", onupdate="cascade")
    )

Oben veranschaulichen wir onupdate="cascade" auf dem ForeignKey-Objekt. Wir veranschaulichen auch die Einstellung mysql_engine='InnoDB', die auf einem MySQL-Backend sicherstellt, dass die referenzielle Integrität unterstützende InnoDB-Engine verwendet wird. Bei der Verwendung von SQLite sollte die referenzielle Integrität aktiviert sein, indem die unter Fremdschlüsselunterstützung beschriebene Konfiguration verwendet wird.

Siehe auch

Verwendung von Fremdschlüssel ON DELETE cascade mit ORM-Beziehungen – Unterstützung von ON DELETE CASCADE mit Beziehungen

mapper.passive_updates – ähnliche Funktion auf Mapper

Simulation einer eingeschränkten ON UPDATE CASCADE ohne Fremdschlüsselunterstützung

In Fällen, in denen eine Datenbank ohne Unterstützung für referenzielle Integrität verwendet wird und natürliche Primärschlüssel mit veränderlichen Werten im Spiel sind, bietet SQLAlchemy eine Funktion, um die Weitergabe von Primärschlüsselwerten an bereits referenzierte Fremdschlüssel in einem **eingeschränkten** Umfang zu ermöglichen, indem eine UPDATE-Anweisung für Fremdschlüsselspalten ausgegeben wird, die unmittelbar auf eine Primärschlüsselspalte verweisen, deren Wert sich geändert hat. Die primären Plattformen ohne Funktionen zur referenziellen Integrität sind MySQL, wenn die MyISAM-Speicher-Engine verwendet wird, und SQLite, wenn das Pragma PRAGMA foreign_keys=ON nicht verwendet wird. Oracle Database unterstützt ebenfalls keine ON UPDATE CASCADE, aber da es die referenzielle Integrität dennoch erzwingt, müssen Beschränkungen als verzögerbar markiert werden, damit SQLAlchemy UPDATE-Anweisungen ausgeben kann.

Die Funktion wird aktiviert, indem das Flag relationship.passive_updates auf False gesetzt wird, vorzugsweise auf einer Eins-zu-Viele- oder Viele-zu-Viele- relationship(). Wenn "Updates" nicht mehr "passiv" sind, bedeutet dies, dass SQLAlchemy einzelne UPDATE-Anweisungen für Objekte ausgibt, die in der Sammlung referenziert werden, auf die das übergeordnete Objekt mit einem sich ändernden Primärschlüsselwert verweist. Dies impliziert auch, dass Sammlungen vollständig in den Speicher geladen werden, wenn sie nicht bereits lokal vorhanden sind.

Unser vorheriges Mapping mit passive_updates=False sieht wie folgt aus:

class User(Base):
    __tablename__ = "user"

    username = mapped_column(String(50), primary_key=True)
    fullname = mapped_column(String(100))

    # passive_updates=False *only* needed if the database
    # does not implement ON UPDATE CASCADE
    addresses = relationship("Address", passive_updates=False)


class Address(Base):
    __tablename__ = "address"

    email = mapped_column(String(50), primary_key=True)
    username = mapped_column(String(50), ForeignKey("user.username"))

Wichtige Einschränkungen von passive_updates=False sind:

  • sie ist deutlich schlechter als die direkte Datenbank-ON-UPDATE-CASCADE, da sie betroffene Sammlungen vollständig vorladen muss, indem sie SELECTs verwendet, und auch UPDATE-Anweisungen für diese Werte ausgeben muss, die sie in "Stapeln" auszuführen versucht, aber auf DBAPI-Ebene immer noch zeilenweise ausgeführt werden.

  • die Funktion kann nicht mehr als eine Ebene "kaskadieren". Das heißt, wenn Mapping X einen Fremdschlüssel hat, der auf den Primärschlüssel von Mapping Y verweist, und dann der Primärschlüssel von Mapping Y selbst ein Fremdschlüssel auf Mapping Z ist, kann passive_updates=False eine Änderung des Primärschlüsselwerts von Z nach X nicht kaskadieren.

  • Die Konfiguration von passive_updates=False nur auf der Many-to-One-Seite einer Beziehung hat keine vollständige Auswirkung, da die Einheit der Arbeit nur durch die aktuelle Identitätszuordnung nach Objekten sucht, die möglicherweise auf das Objekt mit einem sich ändernden Primärschlüssel verweisen, nicht über die gesamte Datenbank.

Da praktisch alle Datenbanken außer Oracle Database jetzt ON UPDATE CASCADE unterstützen, wird dringend empfohlen, die herkömmliche ON UPDATE CASCADE-Unterstützung zu verwenden, falls natürliche und veränderliche Primärschlüsselwerte verwendet werden.