Ah! I see - I had the cascade and passive_delete arguments in the
wrong place.
This works as expected in 0.4.3 and 0.5 now.
Thanks for the help
James
On Dec 5, 12:47 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> Assuming "user_id" is a surrogate primary key, I dont see any need for
> onupdate="CASCADE" to be used here. Additionally, ondelete="CASCADE"
> on your hat.user_id column implies that "hat" will be deleted when a
> "user" entry is deleted - however your relation has this set up on the
> many-to-one side indicating that a "user" would be deleted when a
> "hat" is deleted.
>
> I think this is the full setup you're looking for:
>
> users_table = Table('tg_user', metadata,
> Column('user_id', Integer, primary_key=True),
> Column('user_name', Unicode(16), unique=True),
> Column('created', DateTime, default=datetime.now),
> mysql_engine='InnoDB',
> )
>
> hat_table = Table('hat', metadata,
> Column('id', Integer, primary_key=True),
> Column('name', Unicode(100)),
> Column('user_id', Integer, ForeignKey('tg_user.user_id',
> ondelete='CASCADE')),
> mysql_engine='InnoDB',
> )
>
> class User(object):
> pass
>
> class Hat(object):
> pass
>
> mapper(User, users_table)
>
> mapper(Hat, hat_table,
> properties = {
> 'user': relation(User,
> backref=backref("hats", cascade="all, delete, delete-orphan",
> passive_deletes=True)
> ),
> }
> )
>
> On Dec 5, 2008, at 1:42 PM, James wrote:
>
>
>
> > Thanks for your quick response! Unfortunately I made the changes you
> > suggest and I still get the UPDATE commands being sent to the children
> > before the DELETE on the parent. My model is now:
>
> > users_table = Table('tg_user', metadata,
> > Column('user_id', Integer, primary_key=True),
> > Column('user_name', Unicode(16), unique=True),
> > Column('created', DateTime, default=datetime.now),
> > mysql_engine='InnoDB',
> > )
>
> > hat_table = Table('hat', metadata,
> > Column('id', Integer, primary_key=True),
> > Column('name', Unicode(100)),
> > Column('user_id', Integer, ForeignKey('tg_user.user_id',
> > onupdate='CASCADE', ondelete='CASCADE')),
> > mysql_engine='InnoDB',
> > )
>
> > class User(object):
> > pass
>
> > class Hat(object):
> > pass
>
> > mapper(User, users_table)
>
> > mapper(Hat, hat_table,
> > properties = {
> > 'user': relation(User, backref="hats", cascade="all, delete,
> > delete-orphan", passive_updates=True, passive_deletes=True),
> > }
> > )
>
> > And I get the same pattern in the log:
> > BEGIN
> > UPDATE hat SET user_id=%s WHERE hat.id = %s
> > [None, 1L]
> > DELETE FROM tg_user WHERE tg_user.user_id = %s
> > [1L]
> > COMMIT
>
> > The ondelete and cascade arguments I have should be working in this
> > situation, right?
>
> > Thanks again,
> > James
>
> > On Dec 5, 11:36 am, Michael Bayer <[EMAIL PROTECTED]> wrote:
> >> actually, use passive_deletes=True, not 'all'. It will issue
> >> DELETEs
> >> only for collections that are already loaded, this doesn't break
> >> anything and prevents unnecessary SELECTs of unloaded collections.
> >> The True setting is needed so that the session can update the state
> >> of
> >> those collections during the flush process.
>
> >> On Dec 5, 2008, at 12:29 PM, Michael Bayer wrote:
>
> >>> use the "passive_updates=True, passive_deletes='all'" flags. These
> >>> are described
> >>> athttp://www.sqlalchemy.org/docs/05/sqlalchemy_orm.html#docstrings_sqla
> >>> ...
> >>> .
>
> >>> On Dec 5, 2008, at 11:42 AM, James Brady wrote:
>
> >>>> Hi all,
> >>>> I'm trying to get deletes and updates cascaded down from a parent
> >>>> object to the child objects (connected by ForeignKey).
>
> >>>> It all seems pretty simple in the docs, but I can't get it to work!
> >>>> I'm using MySQL with the InnoDB engine, and have played with all
> >>>> the variation of the onupdate, ondelete and cascade arguments I can
> >>>> think of.
>
> >>>> The problem is that immediately before the DELETE command is sent
> >>>> to MySQL, there are UPDATE commands nulling out the foreign key
> >>>> references of the child objects, so MySQL doesn't trigger it's ON
> >>>> DELETE CASCADE action, and for some reason cascade="all, delete-
> >>>> orphan" doesn't clean up the children with NULL FKs.
>
> >>>> Here's a simplified model:
> >>>> users_table = Table('tg_user', metadata,
> >>>> Column('user_id', Integer, primary_key=True),
> >>>> Column('created', DateTime, default=datetime.now),
> >>>> mysql_engine='InnoDB',
> >>>> )
>
> >>>> hat_table = Table('hat', metadata,
> >>>> Column('id', Integer, primary_key=True),
> >>>> Column('name', Unicode(100)),
> >>>> Column('user_id', Integer, ForeignKey('tg_user.user_id'),
> >>>> onupdate='CASCADE', ondelete='CASCADE'),
> >>>> mysql_engine='InnoDB',
> >>>> )
>
> >>>> class User(object):
> >>>> pass
>
> >>>> class Hat(object):
> >>>> pass
>
> >>>> mapper(User, users_table)
>
> >>>> mapper(Hat, hat_table,
> >>>> properties = {
> >>>> 'user': relation(User, backref="hats", cascade="all,
> >>>> delete, delete-orphan"),
> >>>> }
> >>>> )
>
> >>>> And the log from SA when I do a User.delete:
> >>>> BEGIN
> >>>> UPDATE hat SET user_id=%s WHERE hat.id = %s
> >>>> [None, 1L]
> >>>> DELETE FROM tg_user WHERE tg_user.user_id = %s
> >>>> [1L]
> >>>> COMMIT
>
> >>>> Any help would be much appreciated!
>
> >>>> James
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
