Re: Unknown SQL

From: Philip Lijnzaad <lijnzaad_at_ebi.ac.uk>
Date: Sat, 21 Jul 2001 18:01:41 GMT
Message-ID: <u7wv7xaycl.fsf@sol6.ebi.ac.uk>

On Fri, 4 May 2001 17:14:57 +0200,
"Carl" == Carl Rosenberger <carl_at_db4o.com> wrote:

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

agreed, but I practice you would consider these things exceptions, and in such a case a rollback on the complete mass update, if not essential, acceptable.

Carl> Transactions do not have anything to do with the problem.

I agree, but only if we're talking about OODBMSs and if it has transactions (I suppose most do).

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

There are a few that I left out, but which are very relevant when managing large datasets:

• flexible, general ad hoc, querying. You simply need that in management, typically for rectifying entry or update problems. So this is a mass update, but not something as simple as uploading a TAB delimited file to the RDBMS server. Frequently you need to interrogate bits and pieces of the schema, in totally unforeseen ways, checking and double checking until you can issue the commit.
• Another area where flexible, general ad hoc, querying is important is data mining. And I claim that this is not a specialized, new-fangled discipline for the marketing researchers. Any data manager worth his salt will want to have a feel for the data, trying out new questions. Sometimes this will uncover problems, sometimes interesting oppurtunities for optimization, or research, or indeed marketing. Same goes for a newcomer trying to get a feel for the by 'browsing' it.
• Schema flexibility and schema changes. If you sit on top of giga-bytes of information, chances are that the schema will be used for many different purposes, and that these different purposes evolve over time. Clean-room engineering of nice object models is out of the question, and even you manage to do that, the needs will have changed. RDBMSs handle this gracefully.

Carl> Mass-update-statements might very well be suitable for object databases for
Carl> specific tasks. Two solutions come to my mind:
Carl> - Declarative update staments

Do you mean something like

  UPDATE Customers c
  SET c.balance = c.balance +

                      (SELECT SUM(amount) 
                       FROM Payments p
                       WHERE c.custid = p.custid
                         AND p.date > c.last_update
                       HAVING SUM(amount) > 0.0) ,
      c.last_update = TODAY()

  WHERE c.credit_rating < 3;

If OODBMs offer this, excellent. I haven't seen the query capability necessary for this yet, though.

Carl> - A callback interface "Updateable". The update() method would be called Carl> with every object of the resultset of a query.

So you would have 1 million callbacks if the result set is that big? You prolly want to put the update() or commit() method on the objectSet, not on the individual objects.

Cheers,

                                                                      Philip

-- 
If you have a procedure with 10 parameters, you probably missed some. (Kraulis)
-----------------------------------------------------------------------------
Philip Lijnzaad, lijnzaad_at_ebi.ac.uk \ European Bioinformatics Institute,rm A2-08
+44 (0)1223 49 4639                 / Wellcome Trust Genome Campus, Hinxton
+44 (0)1223 49 4468 (fax)           \ Cambridgeshire CB10 1SD,  GREAT BRITAIN
PGP fingerprint: E1 03 BF 80 94 61 B6 FC  50 3D 1F 64 40 75 FB 53

Received on Sat Jul 21 2001 - 13:01:41 CDT