[orm-devel] When to execute INSERT statements

[Werner F. Bruhin]

Diedrich,

I hope/wish that you will update ORM's insert behavior so the database 
can enforce data integrity (DI).  One of the main reason for me to 
implement an application using a DB like Firebird is that it provides 
DI.  Presently I am not concerned that multiple statements are used, but 
I guess in a high volume application this might be another reason to 
have a hang up with the present implementation of ORM.

Should you decide NOT to make any change I think you should at least 
explain the situation in the documentation, so people (at least the once 
who read documentation) are aware of it.

See you
Werner

Diedrich Vorberg wrote:

>Hello everybody,
>
>Werner's work on the Firebird adapter and his thinking-through of ORM
>brought up a point I was thinking about ago some time back: should ORM
>automatically create INSERT statements of dependant objects?
>
>Answer: Hääää...
>(Anohter free German lesson: it means: "What the heck are'ya 
>talking about?" ;-)
>
>Ok, let me illustrate my point with an example:
>
>Let's say I've got the old one2many relationship of authors and
>books. Storing the information of "'A Farewell To Arms' and 'The Old
>Man And The Sea' are writen by Hemingway" would look like:
>
>  ernest = author(name="Hemingway")
>  ds.insert(ernest)
>
>  book1 = book(title="A Farewall To Arms")
>  ds.insert(book1)
>  ernest.books.append(book1)
>
>  book2 = book(title="The Old Man And The Sea")
>  ds.insert(book2)
>  ernest.books.append(book2)
>
>  ds.commit()
>
>This will result in one INSERT for Ernest and one INSERT plus one 
>UPDATE statement for the books. As Werner correctly pointed out, this 
>will fail if the backend enforces constrains to ensure table 
>integrity: the books are INSERTed with empty book_id columns. Also 
>there are five statements where three would do.
>
>My argument for implementing this the way I did was, that I wanted 
>the programmer to knowingly insert things in the database so he knows 
>exactly what's going on.
>
>There is another, simmilar case I was thinking about:
>
>  ernest = author(name="Steinbeck",
>                  books=[book(title="Tortilla Flat"),
>                         book(title="Cannery Row")])
>
>Here dbclass' constructor would have to automatically issue three
>INSERT statements.
>
>This get's sort of complicated if book had another relationship as one
>of it's attributes. Of course ds.insert() could recursively travers
>the resulting object tree and create appropriate INSERTs but if
>something goes wrong inside ORM or the data model has a subtlety this
>can be very hard to look-through and debug.
>
>My idea for this would be to extend the UPDATE statement cache to 
>also include CREATEs and this way avoid redundant SQL queries and the 
>problem with constrains.
>
>(As a reminder: the update cache keeps track of all UPDATE statements 
>and joins consecutive UPDATEs on the same object into a single 
>statement.)
>
>Do you think this a good idea?
>What kind of drawbacks would you expect (increased complexity aside)?
>Do you think the subtlety of one ds.insert() or append() call 
>creating several SQL statements can be lived with?
>
>Diedrich
>
>  
>