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

It's not always better to squander space: doing so may result in the program not working at all. Squandering time just makes it run slowly. But that's more of a philosophical point.

WRT the update_unless_insrt: there isn't such an option: PrincePawn's referring to the sort of situation where you've got enough information to create a record from scratch, or replace an existing one.

Thus (under Oracle) you get code similar to: 

BEGIN
  INSERT INTO mytab (id, ...) VALUES (id_value, ...);
EXCEPTION
WHEN DUP_VAL THEN
  UPDATE mytab
  SET .. = ...
  WHERE id=id_value;
END;

[download]
. id would presumably be limited to the primary key, rather than any unique key. Personally, I think I'd prefer to see it as inserting if the update fails (which avoids the problem of duplicating a different column).

Replies are listed 'Best First'.
Re: EZDBI is an easier interface to SQL databases
by Dominus (Parson) on Oct 08, 2001 at 21:23 UTC
    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

[reply]
[d/l]
      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