Re: Unknown SQL

Philip Lijnzaad wrote:
> but is it too much ask of a professional to know
> the tricks of the trade (in this case a modicum of relational theory).
 How
> can anyone with some exposure to handling large amounts of data, escape
 the
> conclusion that you have to handle this data as proper sets, not as one
> object at a time, millions of times over?
>
> Let the DBMS do your loops for you; it is orders of magnitude faster (no
> silly roundtrips), cheaper (no coders having to write and debug silly
 loops),
> safer (transactions) and more flexible (very general querying) than you
 can
> ever hope to achieve.

There is one exception here:
Statements for mass updates have no means to resolve locking conflicts.

Transactions do not have anything to do with the problem. A commit() always commits an ACID transaction, no matter how the changes were issued.

> > Typically we do program with objects, though.
>
> Well, I agree that there is a tension between objects and relations.
 Objects
> are useful/essential for application code; relations are useful/essential
 for
> large-scale data management. In my experience, having a (or several)
 object
> layer(s) (or an 'object-relational mapping') around an RDBMS goes a long
 way
> towards resolving this. There are quite a few products that address this:
> Persistence, CocoBase, TopLink, Novera, Aptivity, Objectmatter,
 Softwaretree,
> Watershed, ProEnable ... I'm sure this list is out of date.
>
> Most or all of these automatic mappings are fine, but don't generate
> efficient queries that across several tables. And they can't: the need for
> this completely application dependent. Special dedicated objects accessing
> database views are sometimes a solution to this.

I think we don't disagree at all that here.

We are talking about two totally different tasks: - Mass updates with simple statements.
- Mapping objects.

Mass-update-statements might very well be suitable for object databases for specific tasks. Two solutions come to my mind: - Declarative update staments
- A callback interface "Updateable". The update() method would be called with every object of the resultset of a query. You could even transfer "Updateable" objects to the server to be executed there.

> I would also argue that using the right tools for the job makes our
 business
> less complicated (e.g., don't use a hammer for screws, kind of thing).

Yes, of course.
No doubt that there is no simpler approach to specify updates than: UPDATE postings SET read = TRUE;

Kind regards,
Carl

---
Carl Rosenberger
db4o - database for objects - http://www.db4o.com