telling merge() to insert certain objects instead of update

[Kent]

There might/probably is a better way to achieve what I am trying to
achieve. Let me explain:

I've got a typical Customer/Order/Orderdetail relation.

merge() appeals to me because it does most all the same work I'd
otherwise need to do. I get passed to me an "Order" object will the
customer object and the details objects.

Now, when I run merge, if there are any changes to the customer, these
get automagically updated. If a line is missing, it is automatically
deleted. If a new line exists, it is automatically inserted.

session.merge() does all this.

However, the above is a bit too simplified. If the Customer's primary
key is not supplied, I need to create this in a special way (not from
a sequence) and then insert it. In this case, I don't even want merge
to *attempt* to get() from the database first because if, by chance,
the customerid I construct is already in use, I do not want to
accidentally update that record, I *want* the database to complain on
the INSERT that the primary key already exists.

Same with the order and orderdetails. If an orderdetail is supplied
to me without a primary key, I would like to choose the next
appropriate primary key and INSERT for that particular record, so that
(if something is messed up or a race condition exists with another
instance of the program choosing the same primary key) I get a
database error instead of merge() accidentally updating that other
record.

I don't want to give up the benefits of merge() by programmatically
figuring this out myself. In other words, if I add() to the session
all the lines I *know* should be INSERTs then I need to work out all
the other magical things merge() is doing for me (like deleting
missing lines).

I tried seeing what would happen if I just called session.add() for
the cases of an INSERT and then calling session.merge() for the entire
cascading order(customer/details), but when I tried that I got:
New Instance conflicts with persistent instance type error.

I assume this means: you tried to session.add() and object and now I
just looked up this same instance during the session.merge().

So instead, I was wondering if there is a way to mark an object in
such a way that when session.merge() recursively hits this object, it
knows to not bother with the fetch but instead does a session.add()?

Otherwise, is there a better approach to what I am trying to
accomplish?

[Michael Bayer]

your primary key generation scheme here requires manual steps - you create a new identifier, then you need to check that it doesnt exist. merge() and add() won't do this for you, so you do this yourself as needed with your given objects, and assign those identifiers to the objects. Then you can proceed to merge() them normally. There is no "trick" that will do this for you.

>
> I tried seeing what would happen if I just called session.add() for
> the cases of an INSERT and then calling session.merge() for the entire
> cascading order(customer/details), but when I tried that I got:
> New Instance conflicts with persistent instance type error.
>
> I assume this means: you tried to session.add() and object and now I
> just looked up this same instance during the session.merge().

unless I'm forgetting something (possible), merge() should not be able to produce that error, since it is not placing any new instances in the session unless that identity did not exist. Did you turn off autoflush perhaps ? a flush needs to occur after your add() so that the pending object now becomes persistent (this is why Session does its work in a transaction, in case you're concerned about "writes").

[Kent]

Thank you again for your response and time.

I think a different approach may be better for me, but was hoping for
your input.

I think I'd like to let session.merge() work out whether it is going
to do an add() vs. update properties on the object retrieved
from .get()

Then, after merge(), but before the flush(), I can change anything I
need/run validations and populate primary keys, etc.
Then I can call flush().

I think this approach should work for me, but here is my question:
When merge() returns the merged object, how can I tell whether a
new_instance was created vs get() having returned an existing object?

Can I inspect the object (and its children) to find this out? (Am I
really wondering if this object is persistent? How do I determine
this?) Or can I inspect the session to determine this?

Can I tell which children are "scheduled" for deletion by inspecting
the session or merged object somehow?

Lastly, by the time merge() returns, we have no reference any longer
to the original data, only the merged data. If, for my validations, I
wanted to compare the old property to the new property, can you
recommend an approach to this? I'd rather not hit the database twice,
since merge is going to look up the data anyway.

I was hoping I might be able to extend Session or override its
behavior so as to keep a copy of the object returned from .get()
before it merges the changes.

Input and ideas for me?

Thanks very much in advance,
Kent

[Michael Bayer]

Kent wrote:
> Thank you again for your response and time.
>
> I think a different approach may be better for me, but was hoping for
> your input.
>
> I think I'd like to let session.merge() work out whether it is going
> to do an add() vs. update properties on the object retrieved
> from .get()
>
> Then, after merge(), but before the flush(), I can change anything I
> need/run validations and populate primary keys, etc.
> Then I can call flush().
>
> I think this approach should work for me, but here is my question:
> When merge() returns the merged object, how can I tell whether a
> new_instance was created vs get() having returned an existing object?

"obj in session.new"

>
> Can I inspect the object (and its children) to find this out? (Am I
> really wondering if this object is persistent? How do I determine
> this?) Or can I inspect the session to determine this?
>
> Can I tell which children are "scheduled" for deletion by inspecting
> the session or merged object somehow?

"obj in session.deleted"

>
> Lastly, by the time merge() returns, we have no reference any longer
> to the original data, only the merged data. If, for my validations, I
> wanted to compare the old property to the new property, can you
> recommend an approach to this? I'd rather not hit the database twice,
> since merge is going to look up the data anyway.

whatever Python state you send into merge() remains unchanged. the "new"
data is what is returned. So you could compare those two things.
Alternatively, whats returned also represents in itself a "diff" of the
data you passed into merge vs. what was already in the DB. So if you
wanted to look at individual attributes on the newly merged objects to see
what changed:

from sqlalchemy.orm import attributes
history = attributes.get_history(instance, "somekey")

this returns a tuple of (new, unchanged, deleted). For a scalar, each
member is a one-item tuple.

> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>

[Kent]

You guys have done some great work, thanks. (How many developers are
you?)

[Gunnlaugur Briem]

Trunk commits over the past year:

svn log -q --xml --with-revprop svn:author -r {2009-02-09}:HEAD | grep
'<author>' | sed 's#<author>\(.*\)</author>#\1#' | sort | uniq -c |
sort -n -k 1

1 ellisj
1 ram
4 ants
5 jek
14 lele
23 ged
28 empty
39 pjenvey
413 zzzeek

So, mostly Mike Bayer. No conclusive data on how many developers he
is.

- Gulli