On Feb 9, 2010, at 7:09 PM, Michael Bayer wrote:
> Kent wrote:
>> Maybe you're still looking into that, but I still don't understand why
>> this:
>>
>> merged.orderdetails[0].saleprice
>>
>> causes a new issue to the database.
>
> as I mentioned earlier, if the value isn't in __dict__ on a persistent
> instance, it will be loaded when accessed. Your example doesn't set this
> field on the object to be merged - so the merge operation actually expires
> the value on the loaded object. At the moment that's because the
> attribute missing from __dict__ is assumed to have been expired, so it
> expires it on the to-be-merged side as well (otherwise, what would it
> merge it to? particularly if the load=False flag is set).
if the load flag is set to True though, not sure if this is really ideal
behavior and it seems like resetting any pending state on the
we-know-to-be-loaded attribute might be the better way to go, so ticket 1681 is
a reminder for me to think about this before the 0.6.0 release.
>
>
>>
>> (Also, wondering if some databases allow a primary key to be null...)
>
> I've researched this in the past and they don't. I will look into
> re-introducing "allow_null_pks" as a new flag "allow_partial_pks",
> defaults to True, will be honored by merge(), you set yours to False.
> this is 0.6 only.
>
>
>
>>
>> Thanks again.
>>
>>
>> On Feb 9, 2:50 pm, Kent <k...@retailarchitects.com> wrote:
>>> Ah ha. Thanks for tracking that down, makes sense.
>>>
>>> On Feb 9, 2:25 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote:
>>>
>>>> Kent wrote:
>>>>> I am on version 0.5.8.
>>>
>>>> part of your issue is this:
>>>
>>>> line1.lineid = '15'
>>>
>>>> should be this:
>>>
>>>> line1.lineid = 15
>>>
>>>> This because the value comes back from the DB as a numeric, not a
>>> string,
>>>> producing the wrong identity key ( (<class '__main__.OrderDetail'>,
>>>> ('0206001A134', '15')) vs (<class '__main__.OrderDetail'>,
>>>> (u'0206001A134', 15)) ).
>>>
>>>> The merge then issues the eager load for the lead order + 1 detail,
>>> and a
>>>> second select for the additional detail.
>>>
>>>>> As far as how upset it is making me: well, I certainly have no right
>>>>> to demand this very nice, free software be enhanced or changed: I'm
>>>>> just grateful for it.
>>>
>>>>> We will be supporting clients on webservers that are removed by a
>>> long
>>>>> distance from the database server, so I would like to limit the
>>> round
>>>>> trips as much as is feasible...
>>>
>>>>> I've taken out most everything and left the logic in a simple case
>>> to
>>>>> create the behavior. Here is the script that will demonstrate:
>>>
>>>>> =============================================================================
>>>
>>>>> from sqlalchemy import *
>>>>> from sqlalchemy.orm import *
>>>
>>>>> engine = create_engine('postgres://dbuser:dbu...@localhost:5444/
>>>>> dbuser',echo=True)
>>>>> metadata = MetaData()
>>>>> Session = sessionmaker(bind=engine)
>>>>> session = Session()
>>>
>>>>> order_table = Table("orders", metadata,
>>>>> Column("orderid", Unicode, primary_key=True)
>>>>> )
>>>
>>>>> orderdetail_table = Table("orderdetails",metadata,
>>>>> Column("orderid", Unicode, ForeignKey('orders.orderid'),
>>>>> primary_key=True),
>>>>> Column("lineid", Integer, primary_key=True),
>>>>> Column("saleprice", Numeric, nullable=False)
>>>>> )
>>>
>>>>> class Order(object):
>>>>> pass
>>>
>>>>> class OrderDetail(object):
>>>>> pass
>>>
>>>>> order_mapper = mapper(Order, order_table,
>>>>> properties=dict(orderdetails=relation(OrderDetail,
>>>>> cascade='all,delete-orphan',
>>>>> single_parent=True,
>>>>> lazy=False,
>>>>> backref=backref('parentorder',
>>>>> cascade='refresh-expire,expunge'))))
>>>
>>>>> orderdetail_mapper = mapper(OrderDetail, orderdetail_table)
>>>
>>>>> metadata.create_all(engine)
>>>
>>>>> o=Order()
>>>>> o.orderid = '0206001A134' #this order exists in the database -
>>> You'll
>>>>> need to set add it to the DB
>>>
>>>>> line1=OrderDetail() #line exists in database - You'll need
>>> to
>>>>> set add it to the DB
>>>>> line1.orderid = '0206001A134'
>>>>> line1.lineid = '15'
>>>
>>>>> line2=OrderDetail() #new line does not exist in database
>>>>> line2.orderid = '0206001A134'
>>>
>>>>> o.orderdetails = [line1, line2]
>>>
>>>>> #
>>>>> #
>>>>> # Question a above - the following merge results in 3 SELECT
>>>>> statements, but the first
>>>>> # is an eagerly loaded query joined with orderdetails. So, unless
>>> the
>>>>> JOIN returned fewer rows
>>>>> # (for example, an inner join instead of outer was used), all the
>>>>> orderdetails should
>>>>> # already be in existence as persistent objects:
>>>>> merged=session.merge(o)
>>>
>>>>> merged in session.new #this order exists in the database
>>>
>>>>> merged.orderdetails[0]
>>>>> merged.orderdetails[0] in session.new # already in database (in new
>>> =
>>>>> False)
>>>
>>>>> merged.orderdetails[1]
>>>>> merged.orderdetails[1] in session.new # not yet in database (in new
>>> =
>>>>> True)
>>>
>>>>> #
>>>>> # Question b:
>>>>> # Why does this issue another select? The object should have been
>>>>> eagerly loaded,
>>>>> # but even if not that, it was later reSELECTED during the merge()
>>>>> merged.orderdetails[0].saleprice
>>>
>>>>> #
>>>>> # Question c:
>>>>> # Are there databases that allow part of a primary key to be
>>> undefined
>>>>> (None)?
>>>>> # That is a foreign concept to me, so I expected this object would
>>>>> realize it
>>>>> # needn't query the database.
>>>>> merged.orderdetails[1].saleprice
>>>
>>>>> =============================================================================
>>>
>>>>> Thanks in advance,
>>>
>>>>> Kent
>>>
>>>>> On Feb 8, 9:57 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
>>>>>> On Feb 8, 2010, at 4:12 PM, Kent wrote:
>>>
>>>>>>> Ok, here are my questions:
>>>
>>>>>>> a) The merge eagerloads the order, along with its lines, but
>>> then,
>>>>>>> directly afterward, issues two additional SELECT statements for
>>> the
>>>>>>> two lines, even though these were already eagerly-loaded. That
>>>>>>> surprised me. Why is that occurring?
>>>
>>>>>> I dont know. I would need more than code fragments to reproduce
>>> your
>>>>>> behavior. (nor do I know what version you're on). It doesn't
>>>>>> reproduce with a simple test.
>>>
>>>>>>> b) When I ask for the property .saleprice on the order line,
>>> another
>>>>>>> SELECT statement is issued. Why does that occur when it was
>>> eagerly
>>>>>>> loaded already?
>>>
>>>>>> Same. If the value is in __dict__ it would not issue another load.
>>>
>>>>>>> c) In the case of line2, can SQLAlchemy be made to realize that
>>> part
>>>>>>> of the primary key is not set and therefore there is no reason to
>>>>>>> attempt a fetch from the database? It already detected this was a
>>> new
>>>>>>> record during the merge.
>>>
>>>>>> the "fetch for None", meaning issuing a fetch when the primary key
>>> was
>>>>>> completely None, was resolved in a recent 0.5 version, probably
>>> 0.5.8.
>>>>>> However, a partial primary key is considered to be valid. There is
>>> a
>>>>>> flag on the mapper() called allow_null_pks=True which in 0.5 is set
>>> to
>>>>>> False by default - it means that partial primary key is not valid.
>>>>>> That flag is not in fact checked by merge() in this case, which is
>>>>>> because the flag was already being removed in 0.6 by the time this
>>> fix
>>>>>> went into place. The flag only led to confusion over and over again
>>>>>> when users mapped to outerjoins, and didn't receive rows. Whereas
>>>>>> nobody ever complained about merge issuing a load for None as a key
>>> -
>>>>>> the issue was fixed because I noticed it myself. So you're the
>>> first
>>>>>> person to ever complain about it, which is unfortunate since it may
>>> have
>>>>>> led to a different path for the allow_null_pks flag. I dont like
>>> the
>>>>>> flag very much since its obscure and nobody really ever used it
>>> unless I
>>>>>> told them to directly. The only other way would be for the mapper
>>> to
>>>>>> "guess" if nulls are allowed in the primary key based on the type
>>> of
>>>>>> table/join structure its mapped to. So it depends on how upset this
>>>>>> behavior is making you if I really need to find some way for it to
>>>>>> differentiate between None-capable primary keys or not.
>>>
>>>>>>> Thanks in advance.
>>>
>>>>>>> Kent
>>>
>>>>>>> --
>>>>>>> You received this message because you are subscribed to the
>>> Google
>>>>>> Groups "sqlalchemy" group.
>>>>>>> To post to this group, send email to sqlalch...@googlegroups.com.
>>>>>>> To unsubscribe from this group, send email to
>>>>>> sqlalchemy+unsubscr...@googlegroups.com.
>>>>>>> For more options, visit this group
>>>>>> athttp://groups.google.com/group/sqlalchemy?hl=en.
>>>
>>>>> --
>>>>> You received this message because you are subscribed to the Google
>>> Groups
>>>>> "sqlalchemy" group.
>>>>> To post to this group, send email to sqlalch...@googlegroups.com.
>>>>> To unsubscribe from this group, send email to
>>>>> sqlalchemy+unsubscr...@googlegroups.com.
>>>>> For more options, visit this group at
>>>>> http://groups.google.com/group/sqlalchemy?hl=en.
>>>
>>>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To post to this group, send email to sqlalch...@googlegroups.com.
>> To unsubscribe from this group, send email to
>> sqlalchemy+unsubscr...@googlegroups.com.
>> For more options, visit this group at
>> http://groups.google.com/group/sqlalchemy?hl=en.
>>
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
