in reply to Re: Re: EZDBI is an easier interface to SQL databases
in thread EZDBI is an easier interface to SQL databases

tommyw says:
It's not always better to squander space
Well, I didn't say it was always better, did I? I just said it was better.
WRT the update_unless_insrt: there isn't such an option
I think I may have been thinking of MySQL's REPLACE syntax. It might be well worth emulating REPLACE with if (UPDATE fails) { try INSERT } if it could be done portably. But I do not think it can be. One problem is that the UPDATE might fail for some other reason, and understanding whether it is safe to proceed with the INSERT seems difficult.

You also suggest that I ignore the issue of multiple unique keys, and concentrate on just the primary key. But this doesn't make the problem any easier, and it would make the behavior a lot more confusing for the user. I think doing that would do the wrong thing a lot of the time, and then people would be upset.

Still, I should look into making this easier than it presently is. Right now, you would have to write something like 

    eval { UPDATE ... } or INSERT ...;

[download]
Which seems rather bizarre.

--
Mark Dominus
Perl Paraphernalia

Replies are listed 'Best First'.
INSERT or UPDATE
by Anonymous Monk on Oct 10, 2001 at 19:48 UTC
    This can be very difficult to get right. For example, Dominus's suggestion to use 
        eval { UPDATE ... } or INSERT ...;
    introduces a potential race condition. If two processes are simultaneously trying to update-or-insert a row using that technique then the following can happen: 
    (initially the row is not there)
process A: UPDATE fails
process B: UPDATE fails
process A: INSERT succeeds
process B: INSERT fails
    causing an unexpected failure in one of the processes. It's safer to do it the other way around: 
    eval { INSERT ... } or UPDATE ...;
    which ought to be safe, because a successful INSERT will lock the affected row. I think this difficulty nicely illustrates the advantage of having a REPLACE option in EZDBI, though it would have to be implemented slightly differently for each different type of database.
[reply]
      Somone said:
      This can be very difficult to get right.
      The whole thing seems like a tremendous can of worms.

      I think what I'm going to do is allow the user to specify additional functions, such as 

          use EZDBI 'Replace';

      [download]
      This will create a Replace function which can be used just like the existing Insert and Delete functions, if the database supports it. If not, the Replace function will generate a syntax error, as if you had done $dbh->prepare("REPLACE ...");

      Trying to emulate REPLACE on databases that don't support that natively seems to be too perilous. The user is still free to write eval { Insert ... } or Update... if they want to.

      --
      Mark Dominus
      Perl Paraphernalia

[reply]
[d/l]

In Section Meditations