[OT [OT]] - assering database query results

[Luis Motta Campos]

Nicholas Clark wrote:
> Off topicly off topic, as it's not even about that 4 letter P word.
> 
> So, we have quite a lot of our database queries in config files, with
>  placeholders, etc
> 
> Some of them are only supposed to return 1 row. However, if our
> assumptions are wrong, they might return multiple rows. This isn't a
> fatal bug in our code, which is written such that it takes the first
> row returned. However, it is a bug in our logic.
> 
> Is there a good way to "assert" that there is only one row? So that
> in the development (and QA) environments, it is fatal to breach
> assumptions. But production keeps going.

I assume you're using DBI. If that's not the case, please be more specific.

By reading what you wrote here, looks like you're trying to determine
upfront how many rows where returned by a SQL query.

I don't think it's possible to determine upfront, without an extra
query, how many rows would be returned.

My implementation suggestion would be

# in production (and I hope perl optimizes this away?)
sub ASSERT_ONE_ROW { return 1; }

# in Dev / QA
sub ASSERT_ONE_ROW {
  my ( $dbi, $statement, @bind_params ) = @_;
  @rows =
    $dbi->selectall_arrayref(
      $statement,
      { RaiseError => 0, PrintError => 0 },
      @bind_params
  );
  die "ASSERTION FAILED: $statement contains ".(scalar @rows)."rows."
    if scalar @rows != 1;
  return 1;
}

The only problem is how to hook this up in your code -- it might be too
much to ask to edit all your code and add this assertion.

Cheers
-- 
Luis Motta Campos is a software engineer,
Perl Programmer, foodie and photographer.