[orm-devel] When to execute INSERT statements

[Diedrich Vorberg]

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=E4=E4=E4=E4...
(Anohter free German lesson: it means: "What the heck are'ya=20
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 =3D author(name=3D"Hemingway")
  ds.insert(ernest)

  book1 =3D book(title=3D"A Farewall To Arms")
  ds.insert(book1)
  ernest.books.append(book1)

  book2 =3D book(title=3D"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=20
UPDATE statement for the books. As Werner correctly pointed out, this=20=

will fail if the backend enforces constrains to ensure table=20
integrity: the books are INSERTed with empty book_id columns. Also=20
there are five statements where three would do.

My argument for implementing this the way I did was, that I wanted=20
the programmer to knowingly insert things in the database so he knows=20=

exactly what's going on.

There is another, simmilar case I was thinking about:

  ernest =3D author(name=3D"Steinbeck",
                  books=3D[book(title=3D"Tortilla Flat"),
                         book(title=3D"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=20
also include CREATEs and this way avoid redundant SQL queries and the=20=

problem with constrains.

(As a reminder: the update cache keeps track of all UPDATE statements=20=

and joins consecutive UPDATEs on the same object into a single=20
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=20
creating several SQL statements can be lived with?

Diedrich

--=20
           _..._                            Diedrich Vorberg
         .'     '.
        /  _   _  \                         http://www.tux4web.de
        | (o)_(o) |                         info@tux4web.de
         \(     ) /            .---.
         //'._.'\ \           /     \       Internet Dienstleistungen
        //   .   \ \          \.@-@./       und 'Consulting'.
       ||   .     \ \         /`\_/`\
       |\   :     / |        //  _  \\      Linux Rules!
       \ `) '   (`  /_      | \     )|_
     _)``".____,.'"` (_    /`\_`>  <_/ \
     )     )'--'(     (    \__/'---'\__/
      '---`      `---`