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
> ---------------------------------------------------------------------------------------------------
>
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
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
??!!!!!!!!!"
