[sqlalchemy] Re: Deleting single association in many-to-many relationship
On Jun 13, 2007, at 10:44 AM, David Bolen wrote: > > If the cascade becomes "save-update, delete-orphan", then I get the > behavior where deleting from a single parent job flushes all > associations for the file. Although its true you never get an orphan > that way, files get orphaned "before their time". "orphan" status really only pertains to a single-parent relationship. it was not designed for use in a "collection of parents" sense. > > So, adding the cascade, and then changing your line to del j1.files[1] > (to delete the common file), I get on the second flush: > > BEGIN > DELETE FROM jobs_files WHERE jobs_files.job_id = ? AND > jobs_files.file_id = ? > [[1, 2], [2, 2]] > DELETE FROM files WHERE files.id = ? > [2] > COMMIT > > which is removing the common file association with both jobs, and then > removing the file itself. It's true that after doing the former, it's > an orphan which can be deleted, but I don't know why it's including > the second job in the association pruning. if it deleted from files without deleting all known referencing rows in the association table, youd get a constraint exception. > > On the second point, the complexity of the full cascade recursion with > orphan detection makes sense. I suppose I'm interested in any input > from anyone else as to how they are handling these sorts of operations > in many-to-many cases with changing associations. I would have a backreference on "files" referencing "jobs" and just check that the collection is of length zero..then delete. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Deleting single association in many-to-many relationship
> > On the second point, the complexity of the full cascade recursion > with orphan detection makes sense. I suppose I'm interested in any > input from anyone else as to how they are handling these sorts of > operations in many-to-many cases with changing associations. As i need a history (bitemporal) of all things in my db, i did not have updates nor deletions - all those operations become inserts (of same record with diff. status). For the same reasone, the one-to-many rels have become many-to-many - so u can add new versions on either side of the relation without changing any previous versions - only manipulating the associations inbetween. Then, for the reasons of nested user-transactions and their rollback (see the thread same weeks ago), i ended up updating and deleting these (many-to-many) associations. This "transaction" engine is not 100% working yet, but so far i haven't run into any cascade problems - and i do rely on them to work properly. svil --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Deleting single association in many-to-many relationship
Michael Bayer <[EMAIL PROTECTED]> writes: > On Jun 13, 2007, at 6:13 AM, David Bolen wrote: > >> * I want to remove the association between "File Common" and "Job 1" >> but without affecting "Job 2". >> >> If I session.delete() the fc instance directly, SA purges the file >> completely, including links to both jobs. I can understand SA >> thinking I want the file completely gone in this scenario. >> >> But if I remove the fc instance from the relation list (files) from >> either job, SA also fully purges fc, including the link to the other >> job. This includes the case of deleting one of the jobs if I have >> the cascade on the files relation including "delete". This would >> seem to prevent me from using a delete cascade, since then deleting >> any job would remove files it contains from all other jobs also >> containing those files which sort of defeats the purpose (at least >> for me) of the many to many relationship. > > If I add this code to the bottom: > > del j1.files[0] > s.flush() (...) > the second flush is: > > BEGIN > DELETE FROM jobs_files WHERE jobs_files.job_id = ? AND > jobs_files.file_id = ? > [1, 1] > COMMIT > > association is deleted only. Argh - my bad. I should have left a cascade option commented in the example, since it needs to change slightly to show different behaviors. With the default cascade (as in the example as posted, just save-update), I also see the individual removal from the parent object only affects the association. But nothing other than the association is ever touched, and if you remove it from all parents, you end up with an orphaned file (no jobs). E.g., in your case above, file 1 was only associated with job 1, and is now orphaned. If the cascade becomes "save-update, delete-orphan", then I get the behavior where deleting from a single parent job flushes all associations for the file. Although its true you never get an orphan that way, files get orphaned "before their time". So, adding the cascade, and then changing your line to del j1.files[1] (to delete the common file), I get on the second flush: BEGIN DELETE FROM jobs_files WHERE jobs_files.job_id = ? AND jobs_files.file_id = ? [[1, 2], [2, 2]] DELETE FROM files WHERE files.id = ? [2] COMMIT which is removing the common file association with both jobs, and then removing the file itself. It's true that after doing the former, it's an orphan which can be deleted, but I don't know why it's including the second job in the association pruning. So I was either getting orphans, or completely removed files when trying to remove a single association. I can't seem to get the two desired behaviors together? On the second point, the complexity of the full cascade recursion with orphan detection makes sense. I suppose I'm interested in any input from anyone else as to how they are handling these sorts of operations in many-to-many cases with changing associations. -- David --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Deleting single association in many-to-many relationship
On Jun 13, 2007, at 6:13 AM, David Bolen wrote: > * I want to remove the association between "File Common" and "Job 1" > but without affecting "Job 2". > > If I session.delete() the fc instance directly, SA purges the file > completely, including links to both jobs. I can understand SA > thinking I want the file completely gone in this scenario. > > But if I remove the fc instance from the relation list (files) from > either job, SA also fully purges fc, including the link to the other > job. This includes the case of deleting one of the jobs if I have > the cascade on the files relation including "delete". This would > seem to prevent me from using a delete cascade, since then deleting > any job would remove files it contains from all other jobs also > containing those files which sort of defeats the purpose (at least > for me) of the many to many relationship. If I add this code to the bottom: del j1.files[0] s.flush() the first flush is: BEGIN INSERT INTO files (name) VALUES (?) ['File 1'] INSERT INTO files (name) VALUES (?) ['File Common'] INSERT INTO files (name) VALUES (?) ['File 2'] INSERT INTO jobs (name) VALUES (?) ['Job 1'] INSERT INTO jobs (name) VALUES (?) ['Job 2'] INSERT INTO jobs_files (job_id, file_id) VALUES (?, ?) [[1, 1], [1, 2], [2, 3], [2, 2]] COMMIT the second flush is: BEGIN DELETE FROM jobs_files WHERE jobs_files.job_id = ? AND jobs_files.file_id = ? [1, 1] COMMIT association is deleted only. > > * Providing I can resolve the prior point, I was hoping to have a way > that would let me remove a job completely, including any associated > files, but have the file records only pruned if they did not belong > to any other job. that you have to do manually. cascade isnt going to do that for you. namely, that it requires descending into all child objects, magically producing backreferences to them in order to load their parent objects (in the case backrefs werent defined), then issuing SELECTs for all child objects to determine if they had any other parents. way out of scope. > Am I just missing something blindingly obvious, or should I be trying > to manage the many-to-many relationships, at least deleting, in some > other way? Should I be interacting with the relationship table > directly (but then, how do I remove the files from the contained > project objects without triggering the deletion behavior I don't > want)? if you really wanted to manipulate the relationship table directly, thats fine, but ensure that you expire/refresh the relevant objects and /or clear out the whole session before continuing past that point since SA wouldnt otherwise know you modified relationships behind its back. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Deleting single association in many-to-many relationship
On Jun 13, 2007, at 7:45 AM, [EMAIL PROTECTED] wrote: > the only line removed was the "from .orm import *" - u shouldnt > use any of those internal stuff unless u know what u do. no, this will be required in 0.4, and its mentioned in some of the 0.3 docs as well. sqlalchemy.orm is not an internal module. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Deleting single association in many-to-many relationship
what version u use?
i tried your thing, that is
$ python -i zz.py
s = create_session()
j = s.query(Job)[0] #get first
del j.files[0]
s.flush()
and seems to work
before:
>>> for a in s.query(Job): print a.name, a.files
...
Job 1 [<__main__.File object at 0xb78ec72c>, <__main__.File object at
0xb78e666c>]
Job 2 [<__main__.File object at 0xb78ec72c>, <__main__.File object at
0xb78ec76c>]
after:
>>> for a in s.query(Job): print a.name, a.files
...
Job 1 [<__main__.File object at 0xb78e666c>]
Job 2 [<__main__.File object at 0xb78ec72c>, <__main__.File object at
0xb78ec76c>]
-
the only line removed was the "from .orm import *" - u shouldnt
use any of those internal stuff unless u know what u do.
ciao
svil
On Wednesday 13 June 2007 13:13:58 David Bolen wrote:
> from sqlalchemy import *
> from sqlalchemy.orm import *
>
> meta = BoundMetaData('sqlite:///')
>
> jobs = Table('jobs', meta,
> Column('id', Integer, primary_key=True),
> Column('name', String))
>
> files = Table('files', meta,
> Column('id', Integer, primary_key=True),
> Column('name', String))
>
> jobs_files = Table('jobs_files', meta,
>Column('job_id', Integer,
> ForeignKey('jobs.id')), Column('file_id', Integer,
> ForeignKey('files.id')))
>
>
> class Job(object):
> pass
> class File(object):
> pass
>
> mapper(File, files)
> mapper(Job, jobs,
>properties = { 'files': relation(File, lazy=False,
> backref=backref('jobs',
> lazy=False), secondary=jobs_files) })
>
> def setup():
> meta.create_all()
> s = create_session()
>
> f1 = File()
> f1.name = 'File 1'
> f2 = File()
> f2.name = 'File 2'
> fc = File()
> fc.name = 'File Common'
>
> j1 = Job()
> j1.name = 'Job 1'
> j2 = Job()
> j2.name = 'Job 2'
>
> s.save(j1)
> s.save(j2)
>
> j1.files.extend([f1, fc])
> j2.files.extend([f2, fc])
>
> s.flush()
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
