/ 
SQL-Secretary-0.02
/ SQL::Secretary

NAME

SQL::Secretary - concise SQL-based DBI usage

SYNOPSIS

use SQL::Secretary;

my $col  = one_col       $sql, @bind ; # returns 1st column
my @col  = one_col       $sql, @bind ; # returns entire row

my $row  = one_row       $sql, @bind ; # returns 1st column
my @row  = one_row       $sql, @bind ; # returns entire row
my $row  = one_row_href  $sql, @bind ; # returns entire row as href

my @rows = all_rows      $sql, @bind ; # returns all rows
my @rows = all_rows_href $sql, @bind ; # returns all rows, each as href

my $sth  = sql_do        $sql, @bind ; # prepare_cached + exec

my $date = sql_date;	  # calculated from now... example: 2002/01/08 03:42:51
my $date = sql_date_from_href # calc from href... example: 1969/05/11 05:12:35
 { year   => 1969,
   month  => 5,
   day    => 11,
   hours  => 5,        # defaults to 00 if not specified
   minutes => 12,	# defaults to 00 if not specified
   seconds => 35	# defaults to 00 if not specified
 } ;

begin_transaction;        # similar to $dbh->begin_work. see below
 # some SQL activities ...
commit_transaction;       # similar to $dbh->commit
 # or maybe
rollback_transaction;     # similar to $dbh->rollback

# nothing like hung_transactions comes with DBI transaction support
DESTROY {
 if (my $count = hung_transactions) { 
   warn "DBH is going out of scope with unbalanced begin_tran/commit call count of $count";
 }

DESCRIPTION

SQL::Secretary provides a convenient meta-layer for DBI usage. Actually, to be honest SQL::Secretary provides code written by Matt Seargent in the Example::DB::Default of DBIx::AnyDBD. However, most of that code was of such obvious general utility that I decided to rip it out and make it generally useful. In doing so, I decided to make the passing of a database handle to the API functions optional and provide a means of searching for a pre-created handle. This is an idea borrowed from PApp::SQL, an excellent DBI meta-layer.

The synopsis should make it clear how to use this package. The only other thing that requires note is that undef is returned when the requested data cannot be retrieved, e.g:

my $row  = one_row_href 'SELECT * FROM user WHERE email = ?', $email;
$row or die "no user has email $email";

Oh and two more things. I will discuss how database handles are found so that the API functions can actually do database work. And I will also touch on transaction support.

LOOKING UP DATABASE HANDLES

All API functions shown in the SYNOPSIS actually take an argument just after the function name which must be a viable DBI database handle. However, this argument is optional and if it does not exist then a search is made for a database handle as follows:

1 it looks in the current package for a package variable $DBH
2 it looks for a package variable $SQL::Secretary::DBH

Thus the following call:

my $col  = one_col       $sql, @bind ;

which performs the documented lookup described above could actually be:

my $col  = one_col       $dbh, $sql, @bind ;

and eliminate the lookup.

TRANSACTIONS

DBI's begin_work, commit, and rollback are perfectly fine. The only thing added by the related SQL::Secretary functions is a count of the number of opened and closed transactions so that you can check for hung transactions.

EXPORT

All the things listed in the SYNOPSIS are exported.

AUTHOR

T. M. Brannon, <tbone@cpan.org>

Shamelessly stolen from Matt Seargent's Example::DB::Default code in DBIx::AnyDBD

SEE ALSO

DBIx::AnyDBD, PApp::SQL, DBIx::Broker, DBIx::Easy, EZDBI, DBIx::DWIW, DBIx::Abstract, DBIx::AbstractLite, and the DBIx and SQL hierarchies on http://kobesearch.CPAN.org