how to tell if ->insert succeeded in DBIx::Class?

perl5ever has asked for the wisdom of the Perl Monks concerning the following question:

I've been using this code, and then realized that it is very Oracle specific:

sub insert_run {
  my $self = shift;
  my $run = shift;

  eval {
    $run->insert;
  };
  if ($@) {
    if ($@ =~ m/ORA-00001: unique constraint/) {
      return 0;
    }
    die $@;
  }
  return 1;
}
[download]

$run is a DBIx::Class result object created using ->new. The purpose of this is to atomically test to see if the primary key for $run already exists in the database and insert the row if it doesn't. Otherwise it returns false.

Is there a database independent way to do this?

Comment on how to tell if ->insert succeeded in DBIx::Class? Select or Download Code

Replies are listed 'Best First'.
Re: how to tell if ->insert succeeded in DBIx::Class? by ikegami (Patriarch) on Feb 22, 2011 at 23:16 UTC
Prevent inserts with a lock. Check if the key exists. If not, insert the record. Release the lock. I don't see anything that would allow what you want more directly in the PostgreSQL or DBIx::Class docs. Neither even support a database-level "insert or update". I'm by no means a DBIx::Class expert. Or user.	[reply]
Re: how to tell if ->insert succeeded in DBIx::Class? by 7stud (Deacon) on Feb 22, 2011 at 23:16 UTC
in_storage()?	[reply]
Re^2: how to tell if ->insert succeeded in DBIx::Class? by perl5ever (Pilgrim) on Feb 22, 2011 at 23:40 UTC
This naive use of `->in_storage` leads to a race condition: `sub insert_row { my ($self, $run) = @_; if ($run->in_storage) { return; } else { # race condition here $run->insert; return 1; # ??? }` [download] Part of the problem is that the Oracle driver throws an exception for primary key violations. Catching that exception requires that I know what the exception looks like, and that leads to the Oracle-specific code. If, for instance, `$run->insert` returned false if the insert failed for a unique index violation, then I could write database independent code.	[reply] [d/l] [select]
Re^2: how to tell if ->insert succeeded in DBIx::Class? by ikegami (Patriarch) on Feb 22, 2011 at 23:26 UTC
I don't think that checks the database at all. "in_storage tells us whether the Row object is in the database or not. This is set when fetching data, and when we insert a new row." Update: Confirmed: `sub in_storage { my ($self, $val) = @_; $self->{_in_storage} = $val if @_ > 1; return $self->{_in_storage} ? 1 : 0; }` [download]	[reply] [d/l]