On Feb 10, 2010, at 7:10 PM, Kent wrote: > > When I do something "simple" like this script: > > o=Order() > o.orderid = 'KBORDE' > ol=OrderDetail() > ol.lineid=1 # exists in database > o.orderdetails=[ol] > mo=DBSession.merge(o) > > mo.orderdetails[0] in DBSession.new > > mo.orderdetails[0].saleprice = 65 > > DBSession.flush() > > ====(output pasted below)===== > > I get output that is not what I hoped for in that, because of merge() > not being aware of allow_null_pks with composite keys: > mo.orderdetails[0] in DBSession.new == True. > > This is making validation, etc troublesome for me, since I was > inspecting DBSession.new to indicate whether the record exists in the > database. The flush() works it out correctly in the end and sqla does > an update instead of insert, but inspecting DBSession.new is incorrect > semantically.
um, if i understand OrderDetail pk is the combination of orderid and lineid, you may have to set "orderid" on your OrderDetail object before merging it. the merge process currently does not populate foreign key columns before testing for the primary key. > > If you make merge() aware of "allow_partial_pks" in 0.6, will > mo.orderdetails[0] in DBSession.new == False then? > > (In a previous post "using merge() with composite key", you mentioned > this: > """ > Your assessment of the issue is correct, in that the reconcilation of > l1/l2 "orderid" does not occur within merge so it remains None. This > behavior is not intentional, except to the degree that merge() was not > intended to run through the dependency rules which occur during a > flush, > instead expecting to receive objects with fully composed primary > keys. > It's not immediately apparent to me what degree of rearchitecture of > the > unit of work would be required for this behavior to be added, or if it > is > even a good idea. I understand the argument in favor. That doesn't > mean > there aren't arguments in opposition, just that they aren't > immediately > obvious. > """ > see > http://groups.google.com/group/sqlalchemy/browse_thread/thread/20b199b4f78e7cad) > > So I am wondering now if this is the same issue and will it be changed > ("fixed") in 0.6? > > If so and in the meantime, is there a workaround I could apply to > merge() or is it not very straightforward? > > (I can also hack up my userland code to workaround this in the > meantime, but ultimately wanted to know whether this will all be > solved and if there is an easy patch I could apply until then...) > > > Pasted output to the above script:============================ > >>>> o=Order() >>>> o.orderid = 'KBORDE' >>>> ol=OrderDetail() >>>> ol.lineid=1 >>>> o.orderdetails=[ol] >>>> mo=DBSession.merge(o) > 10:09:22,607 INFO [sqlalchemy.engine.base.Engine.0x...2190] BEGIN > /home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.5.8.01- > py2.6.egg/sqlalchemy/engine/default.py:242: SAWarning: Unicode type > received non-unicode bind param value 'KBORDE' > param[key.encode(encoding)] = processors[key](compiled_params[key]) > 10:09:22,617 INFO [sqlalchemy.engine.base.Engine.0x...2190] SELECT > orders.orderid AS orders_orderid, orders.type AS orders_type, > orders.orderdate AS orders_orderdate, orders.status AS orders_status, > orders.customerid AS orders_customerid, orders.ordersite AS > orders_ordersite, orders.salesperson1 AS orders_salesperson1, > orders.commisspercent1 AS orders_commisspercent1, orders.deliverytype > AS orders_deliverytype, orders.deliverydate AS orders_deliverydate, > orders.mainorder AS orders_mainorder, orders.sequence AS > orders_sequence, orders.massfinalize AS orders_massfinalize, (SELECT > sum(od__a.qtyordered * od__a.saleprice) AS sum_1 > FROM orderdetails AS od__a > WHERE orders.orderid = od__a.orderid) AS totalsale, > products_1.productid AS products_1_productid, products_1.brand AS > products_1_brand, products_1.description AS products_1_description, > products_1.regular AS products_1_regular, products_1.sale AS > products_1_sale, products_1.onhand AS products_1_onhand, > products_1.onorder AS products_1_onorder, products_1.imageurl AS > products_1_imageurl, products_1.special AS products_1_special, > products_1.featured AS products_1_featured, products_1.newproduct AS > products_1_newproduct, orderdetails_1.orderid AS > orderdetails_1_orderid, orderdetails_1.lineid AS > orderdetails_1_lineid, orderdetails_1.productid AS > orderdetails_1_productid, orderdetails_1.qtyordered AS > orderdetails_1_qtyordered, orderdetails_1.saleprice AS > orderdetails_1_saleprice, orderdetails_1.voided AS > orderdetails_1_voided, orderdetails_1.commissiontype AS > orderdetails_1_commissiontype, orderdetails_1.mainorder AS > orderdetails_1_mainorder, orderdetails_1.picked AS > orderdetails_1_picked, customers_1.customerid AS > customers_1_customerid, customers_1.phonenumber AS > customers_1_phonenumber, customers_1.firstname AS > customers_1_firstname, customers_1.lastname AS customers_1_lastname, > customers_1.address1 AS customers_1_address1, customers_1.address2 AS > customers_1_address2, customers_1.city AS customers_1_city, > customers_1.state AS customers_1_state, customers_1.zip AS > customers_1_zip, customers_1.email AS customers_1_email, > customers_1.type AS customers_1_type, customers_1.accountopendate AS > customers_1_accountopendate > FROM orders LEFT OUTER JOIN orderdetails AS orderdetails_1 ON > orders.orderid = orderdetails_1.orderid JOIN products AS products_1 ON > products_1.productid = orderdetails_1.productid LEFT OUTER JOIN > customers AS customers_1 ON customers_1.customerid = orders.customerid > WHERE orders.orderid = %(param_1)s > 10:09:22,617 INFO [sqlalchemy.engine.base.Engine.0x...2190] > {'param_1': 'KBORDE'} > 10:09:22,631 INFO [sqlalchemy.engine.base.Engine.0x...2190] SELECT > orderdetails.orderid AS orderdetails_orderid, orderdetails.lineid AS > orderdetails_lineid, orderdetails.productid AS orderdetails_productid, > orderdetails.qtyordered AS orderdetails_qtyordered, > orderdetails.saleprice AS orderdetails_saleprice, orderdetails.voided > AS orderdetails_voided, orderdetails.commissiontype AS > orderdetails_commissiontype, orderdetails.mainorder AS > orderdetails_mainorder, orderdetails.picked AS orderdetails_picked, > products_1.productid AS products_1_productid, products_1.brand AS > products_1_brand, products_1.description AS products_1_description, > products_1.regular AS products_1_regular, products_1.sale AS > products_1_sale, products_1.onhand AS products_1_onhand, > products_1.onorder AS products_1_onorder, products_1.imageurl AS > products_1_imageurl, products_1.special AS products_1_special, > products_1.featured AS products_1_featured, products_1.newproduct AS > products_1_newproduct > FROM orderdetails JOIN products AS products_1 ON products_1.productid > = orderdetails.productid > WHERE orderdetails.orderid = %(param_1)s AND orderdetails.lineid = % > (param_2)s > 10:09:22,631 INFO [sqlalchemy.engine.base.Engine.0x...2190] > {'param_1': None, 'param_2': 1} >>>> > > > ################# this shouldn't be True, should it? ################ >>>> mo.orderdetails[0] in DBSession.new ########################### > True > > > >>>> >>>> mo.orderdetails[0].saleprice = 65 >>>> >>>> DBSession.flush() > 10:09:22,640 INFO [sqlalchemy.engine.base.Engine.0x...2190] UPDATE > orderdetails SET saleprice=%(saleprice)s WHERE orderdetails.orderid = % > (orderdetails_orderid)s AND orderdetails.lineid = % > (orderdetails_lineid)s > 10:09:22,640 INFO [sqlalchemy.engine.base.Engine.0x...2190] > {'orderdetails_orderid': 'KBORDE', 'saleprice': 65, > 'orderdetails_lineid': 1} > > > > > > > > > > > On Feb 10, 9:59 am, Michael Bayer <mike...@zzzcomputing.com> wrote: >> On Feb 10, 2010, at 8:36 AM, Kent wrote: >> >>>> 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 for your consideration, it seems that would be beneficial for >>> us. As a side note, though, if no databases allow this, why would we >>> default to True instead of False? >> >> because people map to outerjoins (often). then you get a partial PK. >> >> >> >>> Does allow_partial_pks have additional meaning, such as "complain if >>> the object only has part of the primary key set?" >> >> not allowing partial pks means dont consider (2, None) to be a primary key - >> its treated like "None". >> >> >> >>> You mentioned the main thing was how this affects outer joins. Can >>> you expand on how this might cause outer joins to return no rows? Is >>> it because users still expected a row returned from the *other* >>> tables, even though part of this key is null? >>> (I don't want to make you go back through the effort of re-adding this >>> flag if it might cause me unanticipated side-effects that force me to >>> abandon it, so maybe pointing me to an example of the main complaint >>> when setting it to False? I'd like attempt to rule out that it might >>> affect me.) >> >> yes, an outerjoin can return a row for table A but NULL for table B. >> >> its totally fine, we have flipped the defaults in 0.6 and we'd just be >> making the "other" behavior available again. >> >> >> >>> Thanks >> >>> -- >>> 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.
