selectcol_arrayref: secret weapon

Июнь 27th, 2012 | Posted by self in Pensieve - (Комментарии отключены)

Despite what the name suggests and what quick references say, selectcol_arrayref can be used with more than one column, and not only the first one, which makes it a pretty handy tool, as in some cases it’s much less cumbersome than usual selectall_arrayref and alike.

Normal use (to get values from the first column):

my $tables = $dbh->selectcol_arrayref("SHOW TABLES IN test");
foreach my $table (@$tables) { ... }

This is self-explanatory, we want to do something with every table in a schema.
Now, I also want to know if a table is a base table or a view, and do something different based on that.

my $tbl_ref = $dbh->selectcol_arrayref("SHOW FULL TABLES IN test", { Columns=>[1,2] } );
my %tables = @$tbl_ref;
foreach my $t (keys %tables) {
if ($tables{$t} eq 'VIEW') { ... }
else { ... }
}

Why is it easier than selectall_arrayref?
Just because I don’t need to bother about the second-level array reference, everything is already in the plain sight. Simple is good.

What can I say, reading documentation can be useful…
http://search.cpan.org/dist/DBI/DBI.pm#selectcol_arrayref

Perl: fork, DBI and InactiveDestroy

Июнь 3rd, 2012 | Posted by self in Pensieve - (Комментарии отключены)

I spent quite some time trying to figure how to make peace between Perl forks and DBI. It was clear that InactiveDestroy was supposed to be the solution, but making it work turned out to be tricky. Finally found a useful, and, importantly, working recipe here: http://lists.mysql.com/perl/3619 (old stuff, but still good).

This is what {InactiveDestroy} is for:
When you open a database handle ($dbh) in a process, it contains a variety of database-specific state/connection information in it. When you fork a child process, you should never use the database handle ($dbh) which you opened in the parent process. This behavior is *not* defined to work by the DBI. It does work in some cases on *some* databases, but this is not supported behavior. Every process should have its own database handle.

The problem with this is that when you close the inherited database handle in the child process, it also shuts it down on the server side, thus rendering the handle unusable even in the parent process. So here’s the logic.

  • parent process opens a database handle
  • parent forks a child process
  • parent can continue using the open database handle safely as long as no child process uses it and no child process closes it “ungracefully”
  • child process immediately sets the {InactiveDestroy} attribute on the handle
  • child process deallocates the database handle (i.e. $dbh = undef;) but does *not* explicitly close the connection. This triggers the DESTROY method  on the database handle. Since the {InactiveDestroy} attribute is set, this causes the client-side state and connection information to be deallocated and shut down without telling the server to shut down the connection. this allows the parent process to continue to use the connection.
  • child opens its own connection to the database
  • child uses its own connection and closes it normally whenever it wishes

Here is the documentation from the DBI manual page.

http://search.cpan.org/~timb/DBI/DBI.pm