I'm having a problem with cascading deletes. When deleting a parent
row SQLAlchemy raises a data integrity error because child rows still
exist (IntegrityError - update or delete ... violates foreign key
constraint ...). This seems strange to me since it should be deleting
the child rows first. I am using the "private=True" flag in my mapper
relation configuration. I also noticed that the problem disappears
when I comment out two mapper relationships on the bottom table in my
hierarchy. This is interesting because those two relationships are
not mapped with private=True, so they should have no affect on the
main hierarchy. I have listed my mapper config at the end of this email.
BTW why is the private=... parameter named "private"? Why not call it
"cascade" since that's a much more familiar/intuitive term. I'd like
to propose that this parameter be changed to "cascade" and it will
accept these values:
cascade=None # don't cascade anything (shallow save)
cascade="orphan" # set child foreign key to null (currently
private=False)
cascade="delete" # delete child rows when deleting the parent row
(currently private=True)
This adds an additional option to ignore cascades on some
relationships. I think it would fix the problem I described above (I
would put this option on the two relationships that are causing the
problem). This option may also even be useful for allowing the
database to handle cascading deletes (i.e. with ON DELETE CASCADE).
Note: this proposal is still different from Hibernate, which has a
more complicated cascade mechanism. For example, Hibernates cascade
mechanism addresses objects in memory as well as database objects.
Hibernate does not implement persistence by reachability by default.
This is a difference between Hibernate and SQLAlchemy. I haven't
decided which I like better yet, so I'm leaving out a few cascade
options found in Hibernate that have to do with saving/updating
object graphs. These other options are about how new objects become
associated with a session (SA automatically associates them with the
default (thread-local) session, Hibernate does not since it has no
concept of a default session). If SA switched to handle new objects
like Hibernate, we could add cascade="insert", which would cause new
objects associated with a parent to be inserted. At that point we may
also want to allow a comma-separated value in cascade since some
people may want cascade="insert,delete" or other combinations. For
now the options are mutually exclusive.
~ Daniel
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Table defs (abbreviated for clarity)
order = sa.Table("orders", engine,
sa.Column("id", sa.Integer, sa.Sequence("orders_id_seq"),
primary_key=True),
# ...
)
orderLineItem = sa.Table("order_line_item", engine,
sa.Column("id", sa.Integer, sa.Sequence("order_line_item_id_seq"),
primary_key=True),
sa.Column("order_id", sa.Integer, sa.ForeignKey("orders.id"),
nullable=False),
# ...
)
orderLineItemAttribute = sa.Table("order_line_item_attribute", engine,
sa.Column("id", sa.Integer, sa.Sequence
("order_line_item_attribute_id_seq"), primary_key=True),
sa.Column("order_line_item_id", sa.Integer, sa.ForeignKey
("order_line_item.id"), nullable=False),
sa.Column("design_type_attribute_id", sa.Integer, sa.ForeignKey
("design_type_attribute.id"), nullable=False),
# ...
)
orderLineItemAttributeValue = sa.Table
("order_line_item_attribute_value", engine,
sa.Column("id", sa.Integer, sa.Sequence
("order_line_item_attribute_value_id_seq"), primary_key=True),
sa.Column("order_line_item_attribute_id", sa.Integer, sa.ForeignKey
("order_line_item_attribute.id"), nullable=False),
sa.Column("attribute_value_alias_id", sa.Integer, sa.ForeignKey
("attribute_value_alias.id")),
sa.Column("attribute_value_id", sa.Integer, sa.ForeignKey
("attribute_value.id")),
# ...
)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Mapper defs (abbreviated for clarity)
OrderLineItemAttributeValue.mapper = sa.mapper
(OrderLineItemAttributeValue, orderLineItemAttributeValue)
OrderLineItemAttribute.mapper = sa.mapper(OrderLineItemAttribute,
orderLineItemAttribute, properties=dict(
values=sa.relation(OrderLineItemAttributeValue, private=True,
backref="attribute"),
# ...
))
OrderLineItem.mapper = sa.mapper(OrderLineItem, orderLineItem,
properties=dict(
_attributes=sa.relation(OrderLineItemAttribute, private=True,
backref="lineItem"),
# ...
))
Order.mapper = sa.mapper(Order, order, properties=dict(
lineItems=sa.relation(OrderLineItem, private=True, backref="order"),
))
# *** NOTE: When i comment out the following two lines the problem
disappears ***
OrderLineItemAttributeValue.mapper.add_property
("attributeValueAlias", sa.relation(AttributeValueAlias))
OrderLineItemAttributeValue.mapper.add_property("attributeValue",
sa.relation(AttributeValue))
-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting language
that extends applications into web and mobile media. Attend the live webcast
and join the prime developer group breaking into this new coding territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
