Interface module for abstracting DB access

DB.pm

package DB ;

=head1 NAME

MyDB - Module handling access to MySQL databases

=head2 DESCRIPTION

This is the interface to the databases, abstracting all the complexity of
DBI into just a few functions.

=cut

# Tools dealing with database interaction for Second Generation
# sequencing jobs

# 2009/12 DAJ - Finished initial development
# 2010/01 DAJ - Or so I thought
# 2010/03 DAJ - That's mojo. Using $Database to control what DB to use
# 2010/03 DAJ - placeholders added to db_arrayref, db_hashref and db_do
#               functions
# 2011/04 DAJ - removed requirement for 5.010 for use on RCAC systems

# This is the base class for all database connections. It is the plan and hope
# that you only need to (and get to) connect to the DBs via this module.
# 201303    DAJ     Adapted to GCore usage and added POD

use strict ;
use warnings ;
use CGI::Carp ;
use CGI ;
use DBI ;
use Exporter qw(import) ;

use lib '/group/gcore/lib' ;
use MyDB ;

our $VERSION = 0.0.1 ;
our $Database = 'default' ;

my %prepared ;

BEGIN {
    our @EXPORT = qw{
        db_arrayref
        db_hashref
        db_do
        };
    }


############################################################
# queries are prepared by the DB for later use. Takes a SQL
# query and returns the prepared object thing, which is
# cached. Not exported.
sub _prepare_query {
    my ( $sql ) = @_ ;
    my $dbh = MyDB::db_connect( $Database ) ;
    if ( !$prepared{ $sql } ) {
        $prepared{ $sql } = $dbh->prepare( $sql ) ;
        }
    return $prepared{ $sql } ;
    }

=pod

=over 12

=item B<db_arrayref>

Takes a query and an array, containing the values required
by the query. Returns a reference to an array containing
the data requested by the query.

=cut

############################################################
# takes an SQL query and an array containing all the values
# for the placeholders and returns a pointer to
# the resulting array reference
sub db_arrayref {
    my ( $sql, @args ) = @_ ;
    my $dbh = MyDB::db_connect( $Database ) ;
    my $sth = _prepare_query $sql ;
    $sth->execute( @args ) or die $dbh->errstr ;
    my $ptr = $sth->fetchall_arrayref() ;
    return $ptr ;
    }

=pod

=item B<db_hashref>

Takes a query and an array, containing the values required
by the query. Returns a reference to an array containing
the data requested by the query.

=cut

############################################################
# takes an SQL query and an array containing all the values
# for the placeholders and returns a pointer to
# the resulting hash reference
sub db_hashref {
    my ( $sql, $id, @args ) = @_ ;
    my $dbh = MyDB::db_connect( $Database ) ;
    my $sth = _prepare_query $sql ;
    $sth->execute( @args ) or die $dbh->errstr ;
    my $ptr = $sth->fetchall_hashref( $id ) ;
    return $ptr ;
    }

=pod

=item B<db_db>

Takes a query and an array, containing the values required
by the query. Returns the number of rows affected, or '0E0'
if no content.

This one is used for create, update or delete, not read.

=cut

############################################################
# takes an SQL query and an array containing all the values
# for the placeholders and returns the count
# of affected lines
sub db_do {
    my ( $sql, @args ) = @_ ;
    my $dbh = MyDB::db_connect( $Database ) ;
    my $sth = _prepare_query $sql ;
    $sth->execute( @args ) or die $dbh->errstr ;
    my $rows = $sth->rows ;
    return ( $rows == 0 ) ? "0E0" : $rows ;

    # always return true if no error
    }

=pod

=back

=head2 AUTHOR

Dave Jacoby - L<jacoby@purdue.edu>

=cut

1 ;