On May 29, 2008, at 11:58 AM, Brad Wells wrote:
> Thank you very much for your help. The post_update=True addition is
> what I was missing.
>
> The test case I was using was c1.created_by = c1 where created_by
> was previously null.
> (c1 having previously been saved and flushed) This seems to be the
> one corner case where
> post_update=True was necessary.
>
> I have attached a script to demonstrate the problem I encountered. I
> don't know if this is
> expected or known behavior or not, but it was certainly confusing to
> me. I would assume that saving
> against a null vs non-null value should not result in different
> behavior.
that exact issue is this:
INSERT INTO TABLE (id, data, parent_id) VALUES (x, y, z)
When using a database like SQLite or MySQL which uses an "identity"
scheme to generate primary keys, we are not able to populate the
"parent_id" column in the above statement; we don't yet know what "id"
is. Therefore, an INSERT followed by an UPDATE with the newly
generated ID is required. With databases that use sequences like PG
or Oracle, SQLA abstracts the usage of the sequence so that it
operates like an "identity" (the lowest common denominator of
behavior) to the ORM, so the same issue exists. It's obvious that if
parent_id is meant to be NULL, nothing would go wrong.
For that exact example, SQLA will not have a "circular dependency"
error since the topological algorithm doesn't represent the same
object more than once, but its likely that it should at some point be
made to recognize that case. So the error isn't raised but the
actual case fails.
>
>
> Thank you again.
>
> -brad
>
> On May 28, 8:47 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> > Here's a script which exercises your mapping against 0.4 - the
> > remote_side should be on the "many-to-one" side of "updated_by".
> >
> > Addtionally, depending on what kind of combinations of Contact
> objects
> > you want to store, you might run into the scenario where ContactA
> > references ContactB, and ContactB references ContactA. Theres no
> way
> > to INSERT data like that without an UPDATE (assuming FK integrity
> and
> > no sequences), so SQLA wants you to use a flag called
> "post_update" if
> > that occurs. It needs the flag if the co-dependent items are being
> > UPDATEd too since it works out dependencies for UPDATE and INSERTs
> in
> > the same way...this is something that could perhaps be improved
> upon.
> > So the second half of the script illustrates that mapping as an
> > alternative.
> >
> > cyclical_contacts.py
> > 3KDownload
>
> >
> from sqlalchemy import *
> from sqlalchemy import exceptions
> from sqlalchemy.orm import *
>
> import logging
> #logging.basicConfig()
> #logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
> #logging
> .getLogger
> ('sqlalchemy.orm.properties.PropertyLoader').setLevel(logging.INFO)
> #logging
> .getLogger
> ('sqlalchemy.orm.strategies.LazyLoader').setLevel(logging.INFO)
>
> meta = MetaData(create_engine('sqlite://'))
>
> contacts = Table('contacts', meta,
> Column('id', Integer, primary_key=True),
> Column('name', String(50)),
> Column('created_by', Integer),
> Column('updated_by', Integer),
> ForeignKeyConstraint(['created_by'], ['contacts.id']),
> ForeignKeyConstraint(['updated_by'], ['contacts.id'])
> )
>
> meta.create_all()
>
> class Contact(object):
> def __init__(self, name, created_by=None):
> self.name = name
> self.created_by = created_by
>
> def __eq__(self, other):
> return other.name == self.name
>
> def __repr__(self):
> return "Contact(created_by=%r, updated_by=%r)" %
> (self.created_by, self.updated_by)
>
> # create a sample mapper with and without post_update=True
> # updated_by uses post_update, created_by does not.
> mapper(Contact, contacts, properties={
> '_created_by': contacts.c.created_by,
> '_updated_by': contacts.c.updated_by,
> 'created_by': relation(Contact,
> primaryjoin=contacts.c.created_by==contacts.c.id,
> remote_side=[contacts.c.id],
> ),
> 'updated_by': relation(Contact,
> primaryjoin=contacts.c.updated_by==contacts.c.id,
> remote_side=[contacts.c.id],
> post_update=True,
> )
> })
>
>
> sess = create_session()
>
> # saving indivisually seems to be necessary to ensure ID ordering
> c1 = Contact('c1')
> sess.save(c1)
> sess.flush()
>
> c2 = Contact('c2')
> sess.save(c2)
> sess.flush()
>
> # assert proper data
> assert
> contacts.select().order_by(contacts.c.name).execute().fetchall() == [
> (1, 'c1', None, None),
> (2, 'c2', None, None),
> ]
> sess.clear()
>
> [c1, c2] = sess.query(Contact).order_by(Contact.name).all()
>
> c1.created_by = c2
> c1.updated_by = c2
> sess.flush()
>
> # saving against a null key with a non self referential key works as
> expected
> assert
> contacts.select().order_by(contacts.c.name).execute().fetchall() == [
> (1, 'c1', 2, 2),
> (2, 'c2', None, None),
> ]
> sess.clear()
>
> [c1, c2] = sess.query(Contact).order_by(Contact.name).all()
>
> c1.created_by = c1
> c1.updated_by = c1
> c2.created_by = c2
> c2.updated_by = c2
> sess.flush()
>
> # updating a non-null key with a self referential key works as
> expected
> # with and without post_update=True
> #
> # saving against a null key with a self referential key works only
> with
> # post_update=True.
> #
> # This does not seem to be a true circular relationship as the
> resulting SQL
> # can be one simple UPDATE.
> #
> # expected outcome (to me):
> # c1 created_by=1, updated_by=1
> # c2 created_by=2, updated_by=2
> # actual outcome:
> # c1 created_by=1, updated_by=1
> # c2 created_by=None, updated_by=2
> assert
> contacts.select().order_by(contacts.c.name).execute().fetchall() == [
> (1, 'c1', 1, 1),
> (2, 'c2', 2, 2),
> ]
> sess.clear()
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
