Using DBI

[Bradley Baetz]

On Fri, Jan 17, 2003 at 08:11:42PM +0000, Gervase Markham wrote:
> >The old *SQL functions are still there.  You have to "use Bugzilla::DB qw(
> >:deprecated )" to get them, but they're there.  
> 
> This is kind of depressing - mostly because, as Jonathan says, the new 
> functions are really ugly. Not necessarily to use, but certainly to look 
> at. Currently, at lot of our code is really nice, because it doesn't 
> have constructs like
>     my $dbh = $Bugzilla->instance->dbh;
>     return $dbh->selectrow_array("SELECT id FROM products WHERE name=?",
>                                  undef,
>                                  $prod);
> all over it.

No, it has

  PushGlobalSQLState();
  ' Remember the quote!
  SendSQL("SELECT id FROM products WHERE name=" . SqlQuote($prod));
  my $foo = FetchOneColumn();
  PopGlobalSQLState();

and we get stuff like bug 189446 because noone has ever been checking
for errors.

> 
> Is there no way we can keep the old interface, or something resembling 
> it? For example:
> 
> use Bugzilla::DBI; # (or some other package)
> ...
> my @products = SelectRow("SELECT id FROM products WHERE name=?", $prod);
> 

Why? The 2nd argument can have stuff in it; it just didn't in this
particular example.

We could have wrappers, I guess, but there are so many DBI functions
that its not really appropriate.

One example I hinted at, but didn't mention, was that to select all
bug_ids matching a certain criteria, we can do:

my $ref = $dbh->selectcol_arrayref("SELECT bug_id FROM bugs WHERE ...");

To produce an id=> name hash, we can do (from DBI docs):

  my $ary_ref = $dbh‐>selectcol_arrayref("select idname from table",
                                         { Columns=>[1,2] });
  my %hash = @$ary_ref; # build hash from key‐valupairs so $hash{$id} => name

and so on. You only have to get the dbh once per sub. I will think about
removing the ->instance stuff, and probably do that since its not really
needed for our case.

> [As a sidenote, I note from today's Slashdot interview that AMI (the 
> BIOS people) use Bugzilla internally.]
> 

Cool.

> Gerv
> 

Bradley