Re: "Transactions are bad, real bad" - discuss

"Alfredo Novoa" <alfredo_at_ncs.es> wrote in message news:e4330f45.0305031051.6be5b3d7_at_posting.google.com...
> "Paul Vernon" <paul.vernon_at_ukk.ibmm.comm> wrote in message
news:<b8p5dh$3356$1_at_gazette.almaden.ibm.com>...
>
> > Personally, I'd be more than comfortable working in a system that did not
> > support INSERT/UPDATE/DELETE shorthands. OK, so UPDATEs are slightly
painful
> > to write out long hand, but INSERT, DELETE are trivial (almost shorter
when
> > written as an assignment).
> >
> > Do you prefer
> > INSERT INTO Parts VALUES (P34, 'A shiny new Hammer')
> > or
> > Parts := Parts UNION VALUES (P34, 'A shiny new Hammer')
>
> Imagine this:
>
> Parts := VALUES (P34, 'A shiny new Hammer');
> Parts := Parts UNION VALUES (P34, 'A shiny new Hammer');
>
> It should work without errors.

Agreed

> Parts := VALUES (P34, 'A shiny new Hammer');
> INSERT INTO Parts VALUES (P34, 'A shiny new Hammer');
>
> And this?
>
> If they are equivalent (and they are), it should work without errors, unlike
in SQL.
>
> Do you agree?
>

Agreed.

If you want the SQL semantics for INSERT then rather than

     INSERT INTO Parts VALUES (P34, 'A shiny new Hammer')

being a macro for:

    Parts := Parts UNION VALUES (P34, 'A shiny new Hammer')

it would be a macro for syntax such as:

    Parts := CASE WHEN VALUES (P34, 'A shiny new Hammer') MINUS Parts <> {}

            THEN Raise_Error('Tuple already exists')
            ELSE Parts UNION VALUES (P34, 'A shiny new Hammer')
            END

Note I should look for duplicate keys above and not entire tuples, but you get the
idea.

I prefer the non-check version for a number of reasons. One is that I feel that in most well designed databases, the possibility of inserting duplicates is a
matter of concern for a small set of business actions only.

An alternative to Raise_Error(), which I do not like as it is somewhat 'outside' of the relational model, is 'select-update' statements. E.g. the following returns the number of rows inserted by comparing cardinalities

WITH NewParts AS (Parts UNION VALUES (P34, 'A shiny new Hammer'))

    RETURN | NewParts | - | Parts |
    Parts := NewParts
;

Using | r | for the number of tuples in a relation and noting that the relations in the RETURN expression contain their value before the assignment

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Wed May 07 2003 - 18:58:53 CEST