yes, thats ticket #249. ive expanded the scope of that ticket since
this particular pattern is much more general usage than what I
initially thought it was.
anyway this will be the next ticket i fix since its pretty major.
On Mar 19, 2007, at 5:54 AM, Alexandre CONRAD wrote:
> Hello Mike,
>
> Here's a simple test case script that creates tables in sqlite
> (memory).
>
> To resume, we have "site_table" table and "option_table" objects as a
> many-to-many relationship through a secondary "weak" table
> "options_has_sites".
>
> I want to be able to:
> - delete a "site" without deleting the attached options.
> - delete an "option" without deleting the attached sites.
> - make sure the weak table is beeing cleaned up correctly depending
> if a
> site or an option has been removed.
>
> Regards,
> --
> Alexandre CONRAD
>
>
>
> Michael Bayer wrote:
>
>> the rows in the M2M table should be deleted automatically. it
>> *might* require that your instances are present in the session but
>> its not supposed to. can you make me a short test case for this
>> one ? its not the first time ive heard about it.
>>
>> technically you can just put ON DELETE CASCADE on the tables too but
>> id like to fix this issue.
>>
>>
>> On Mar 16, 2007, at 12:19 PM, Alexandre CONRAD wrote:
>>
>>
>>> Humm, this doesn't help as if a site is deleted, it deletes the
>>> options
>>> that where related to that option.
>>>
>>> I want to be able to:
>>> - delete an option without deleting a site
>>> - delete a site without deleting an option
>>>
>>> just delete (clean up) the related rows inside the weak
>>> "options_has_sites" table.
>>>
>>> I just can't figure it out...
>>>
>>>
>>> Michael Bayer wrote:
>>>
>>>
>>>> youd need to add some "delete" cascades to your relationship, maybe
>>>> on just the backref (using the backref() function), e.g.
>>>>
>>>> option_mapper = assign_mapper(ctx, Option, option_table,
>>>> properties={
>>>> 'sites':relation(Site, backref=backref("options",
>>>> cascade="save-update, delete"),
>>>> secondary=options_has_sites, cascade="save-update"),
>>>> },
>>>> order_by=option_table.c.name,
>>>> )
>>>>
>>>>
>>>>
>>>> On Mar 16, 2007, at 10:38 AM, Alexandre CONRAD wrote:
>>>>
>>>>
>>>>
>>>>> Hello,
>>>>>
>>>>> I have a many-to-many relation between an option table and a site
>>>>> table.
>>>>>
>>>>> Deleting an option correctly deletes the related rows in
>>>>> "options_has_sites" table.
>>>>>
>>>>> But when I delete a site, I have some orphan rows in the
>>>>> "options_has_sites" table. How can I avoid this ?
>>>>>
>>>>>
>>>>> # SITE TABLE -----------
>>>>> site_table = Table('sites', meta,
>>>>> Column('id', Integer, primary_key=True),
>>>>> Column('name', Unicode(20), nullable=False, unique=True),
>>>>> )
>>>>>
>>>>> class Site(object):
>>>>> pass
>>>>>
>>>>> site_mapper = assign_mapper(ctx, Site, site_table,
>>>>> order_by=site_table.c.name,
>>>>> )
>>>>>
>>>>>
>>>>> # OPTION TABLE ------------
>>>>> option_table = Table('options', meta,
>>>>> Column('id', Integer, primary_key=True),
>>>>> Column('name', Unicode(20), unique=True, nullable=False),
>>>>> )
>>>>>
>>>>> options_has_sites = Table('sites_has_options', meta,
>>>>> Column('id_site', None, ForeignKey('sites.id'),
>>>>> primary_key=True),
>>>>> Column('id_option', None, ForeignKey('options.id'),
>>>>> primary_key=True),
>>>>> )
>>>>>
>>>>> class Option(object):
>>>>> pass
>>>>>
>>>>> option_mapper = assign_mapper(ctx, Option, option_table,
>>>>> properties={
>>>>> 'sites':relation(Site, backref="options",
>>>>> secondary=options_has_sites, cascade="save-update"),
>>>>> },
>>>>> order_by=option_table.c.name,
>>>>> )
>>>>>
>>>>>
>>>>> Should I play with backref() ?
>>>>>
>>>>> Regards,
>>>>> --
>>>>> Alexandre CONRAD
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>> --
>>> Alexandre CONRAD - TLV FRANCE
>>> Research & Development
>>>
>>>
>>>
>>
>>
>>>
>>
>>
>> ---------------------------------------------------------------------
>> ------------------------------
>> Texte inséré par Platinum 2007:
>>
>> S'il s'agit d'un mail indésirable (SPAM), cliquez sur le lien
>> suivant pour le reclasser : http://127.0.0.1:6083/Panda?
>> ID=pav_31925&SPAM=true
>> ---------------------------------------------------------------------
>> ------------------------------
>>
>>
>
>
>
>
> >
> from sqlalchemy import *
>
> meta = DynamicMetaData()
>
> # Sites
> site_table = Table('sites', meta,
> Column('id', Integer, primary_key=True),
> Column('name', Unicode(20), nullable=False, unique=True),
> Column('email', Unicode(100)),
> )
>
> class Site(object):
> pass
>
> site_mapper = mapper(Site, site_table,
> order_by=site_table.c.name,
> )
>
>
> # Options
> option_table = Table('options', meta,
> Column('id', Integer, primary_key=True),
> Column('name', Unicode(20), unique=True, nullable=False),
> Column('description', Unicode(40)),
> )
>
> options_has_sites = Table('options_has_sites', meta,
> Column('id_site', None, ForeignKey('sites.id'), primary_key=True),
> Column('id_option', None, ForeignKey('options.id'),
> primary_key=True),
> )
>
> class Option(object):
> def __repr__(self):
> return "Option: %s" % repr(self.name)
>
> option_mapper = mapper(Option, option_table,
> properties={
> 'sites':relation(Site, backref="options",
> secondary=options_has_sites, cascade="save-update"),
> },
> order_by=option_table.c.name,
> )
>
> meta.connect("sqlite://", echo=True)
> meta.create_all()
>
> # Make session.
> session = create_session()
>
> # Inject sites and options.
> for i in range(1, 4):
> o = Option()
> o.name, o.description = "opt%d" % i, "This is option %d" % i
> session.save(o)
> s = Site()
> s.name, s.email = "site%d" % i, "[EMAIL PROTECTED]" % i
> session.save(s)
>
> session.flush()
> session.clear()
>
> print """\n### Now, let's query for site 1."""
> s = session.query(Site).get(1)
> opts = session.query(Option).select()
> print """\n### Add options from 1 to 3 to the site."""
> s.options = opts[0:3] # Put option 1, 2 and 3
> session.flush()
> session.clear()
>
> print """\n### Now, let's query for site 1 again."""
> s = session.query(Site).get(1)
> print """\n### Check the site has the options 1, 2 and 3."""
> print s.options, "!!!!!!!we should have opt 1, 2 and 3 here!!!!!!!"
> session.clear()
>
> print """\n### If it has, we should have pairs of (id_site, id_opt)
> in options_has_sites."""
> print options_has_sites.select().execute().fetchall(), "!!!!!!!we
> should have 3 pairs here!!!!!!!"
>
> print """\n### Now, let's query for option 1."""
> o = session.query(Option).get(1)
> print """\n### Now remove option 1."""
> session.delete(o)
> session.flush()
> session.clear()
>
> print """\n### Now, let's query for site 1 again."""
> s = session.query(Site).get(1)
> print """\n### Check what options has the site. Option 1 should be
> removed from sites."""
> print s.options, "!!!!!!!!!we should only have opt2 and opt3 in
> here!!!!!!!!!!!!!"
> session.clear()
>
> print """\n### Now check that the row (id_site, id_opt) for option
> 1 should be removed from table "options_has_sites"."""
> print options_has_sites.select().execute().fetchall(), "!!!!!!!we
> should only have 2 pairs now that opt1 was removed!!!!!!!"
>
> print """\n### Let's query for site 1."""
> s = session.query(Site).get(1)
> print """\n### Now let's delete the site."""
> session.delete(s)
> session.flush()
> session.clear()
>
> print """\n### We should still have option 2 and 3, even if the
> site was deleted."""
> print session.query(Option).select()
>
> print """\n### The rows (id_site, id_opt) for option 2 and 3 of the
> site should be removed from "options_has_sites"."""
> print options_has_sites.select().execute().fetchall(), "!!!!!We
> want this cleaned up and empty now that we have removed the site.
> How to do that ??!!!!!!!!!"
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
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
-~----------~----~----~----~------~----~------~--~---
