[Twisted-Python] enterprise.row and two-way relationships

[Justin Ryan]

On Sun, 2002-10-20 at 16:32, Sean Riley wrote:
> well, i can think of two ways - but neither are particularly good.

I was afraid of this :)

> You could create RowObject classes for each of the three tables - account,
> contact, and rel  - and define foreign keys with the "rowForeignKey"
> attributes between the tables.
> 
> Then use loadObjectsFrom to load objects from the rel table, and have those
> "relRow" objects auto-load their children - which would be the rows from the
> contacts table... This is not terribly efficient as it doesn't take
> advantage of sql joins or set operations at all, but it should work.

this had occurred to me but I hoped there would be a better way..
 
> For the second way, create a view on the rel and contacts tables:
> 
> create view account_contacts as
> select rel.account_id, contact.contact_id, contact.name
> from contact, rel
> where rel.contact_id = contact.contact_id;
> 
> and create a rowObjectClass for the view. This would allow the rows to be
> loaded, but updates and inserts could be a problem. Some databases cant do
> updates or inserts to views... Others require rules or triggers to do it.
> 

hrm.. do you know if postgres can?  I spose i could find out fairly
easily :)

seems this would still require two queries in order to grab the account
name as well as the contact info, but is still more efficient than
making account and contact children of rel..

> Dealing with many-to-many relationships like this is really outside of the
> immediate scope of the current row code. Its a pretty common problem though,
> so it is a logical place for the next extensions.  I'm not yet sure how to
> make a good interface for this. I am reluctant to introduce more rowClass
> attributes without some better validation or generation scheme - it is
> already tedious and error-prone enough to type in all the columns, types and
> relationships.
> 
> An SQL parser may be one way. If there was some way to generate
> rowObjectClasses for queries dynamically, with functionality similar to the
> way cursors work, that would be a generally useful solution....
> 
> Any thoughts on extending the Row API are welcome :)

I may just poke around in the code and see what idears I can come up
with :)

Thanks!
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part
URL: </pipermail/twisted-python/attachments/20021020/b375b01d/attachment.sig>