view release on metacpan or  search on metacpan

lib/Bio/EnsEMBL/Utils/SqlHelper.pm  view on Meta::CPAN

Bio::EnsEMBL::Utils::SqlHelper

=head1 VERSION

$Revision$

=head1 SYNOPSIS

  use Bio::EnsEMBL::Utils::SqlHelper;

  my $helper =
    Bio::EnsEMBL::Utils::SqlHelper->new( -DB_CONNECTION => $dbc );

  my $arr_ref = $helper->execute(
    -SQL      => 'select name, age from tab where col =?',
    -CALLBACK => sub {
      my @row = @{ shift @_ };
      return { name => $row[0], age => $row[1] };
    },
    -PARAMS => ['A'] );

  use Data::Dumper;
  print Dumper($arr_ref), "\n";
  # Prints out [name=>'name', age=>1] maybe ....


  # For transactional work; only works if your MySQL table
  # engine/database supports transactional work (such as InnoDB)

  $helper->transaction(
    -CALLBACK => sub {
      if ( $helper->execute_single_result(
                                      -SQL => 'select count(*) from tab'
           ) )
      {
        return $helper->execute_update('delete from tab');
      } else {
        return
          $helper->batch( -SQL  => 'insert into tab (?,?)',
                          -DATA => [ [ 1, 2 ], [ 1, 3 ], [ 1, 4 ] ] );
      }
    } );

=head1 DESCRIPTION

Easier database interaction

=cut

package Bio::EnsEMBL::Utils::SqlHelper;
$Bio::EnsEMBL::Utils::SqlHelper::VERSION = '114.0.0';
use warnings;
use strict;

use Bio::EnsEMBL::Utils::Argument qw(rearrange);
use Bio::EnsEMBL::Utils::Scalar qw(assert_ref check_ref);
use Bio::EnsEMBL::Utils::Exception qw(throw);
use Bio::EnsEMBL::Utils::Iterator;
use English qw( -no_match_vars ); #Used for $PROCESS_ID
use Scalar::Util qw(weaken); #Used to not hold a strong ref to DBConnection
use Time::HiRes;

=pod

=head2 new()

  Arg [DB_CONNECTION] : Bio::EnsEMBL::DBSQL::DBConnection $db_connection
  Returntype          : Instance of helper
  Exceptions          : If the object given as a DBConnection is not one or it
                        was undefined
  Status              : Stable
  Description         : Creates a new instance of this object.
  Example             :
  my $dba = get_dba('mydb');    # New DBAdaptor from somewhere
  my $helper = Bio::EnsEMBL::Utils::SqlHelper->new(
    -DB_CONNECTION => $dba->dbc() 
  );

  $helper->execute_update( -SQL    => 'update tab set flag=?',
                           -PARAMS => [1] );

=cut

sub new {
	my ( $class, @args ) = @_;
	
	my ($db_connection) = rearrange([qw(db_connection)], @args);
	 
	my $self = bless( {}, ref($class) || $class );
	throw('-DB_CONNECTION construction parameter was undefined.') 
	 unless defined $db_connection;
	$self->db_connection($db_connection);
	
	return $self;
}

=pod

=head2 db_connection()

  Arg [1]     : Bio::EnsEMBL::DBSQL::DBConnection $db_connection
  Description : Sets and retrieves the DBConnection 
  Returntype  : Bio::EnsEMBL::DBSQL::DBConnection
  Exceptions  : If the object given as a DBConnection is not one or if an 
                attempt is made to set the value more than once
  Status      : Stable

=cut

sub db_connection {
  my ($self, $db_connection) = @_;
  if(defined $db_connection) {
    if(exists $self->{db_connection}) {
      throw('Cannot reset the DBConnection object; already defined ');
    }
    assert_ref($db_connection, 'Bio::EnsEMBL::DBSQL::DBConnection', 'db_connection');
    $self->{db_connection} = $db_connection;
    weaken $self->{db_connection};
  }
  return $self->{db_connection};
}

# --------- SQL Methods

=pod

=head2 execute() - Execute a SQL statement with a custom row handler

  Arg [SQL]             : string SQL to execute
  Arg [CALLBACK]        : CodeRef; The callback to use for mapping a row to a data  
                          point; leave blank for a default mapping to a 2D array
  Arg [USE_HASHREFS]    : boolean If set to true will cause HashRefs to be returned 
                          to the callback & not ArrayRefs
  Arg [PARAMS]          : ArrayRef The binding parameters to the SQL statement
  Arg [PREPARE_PARAMS]  : boolean Parameters to be passed onto the Statement Handle 
                          prepare call
  Arg [ITERATOR]        : boolean Request a Bio::EnsEMBL::Utils::Iterator
                          rather than a 2D array
  Returntype :  ArrayRef or Bio::EnsEMBL::Utils::Iterator
  Exceptions :  If errors occur in the execution of the SQL
  Status     :  Stable
  Example    :
  my $arr_ref = $helper->execute(
    -SQL      => 'select a,b,c from tab where col =?',
    -CALLBACK => sub {
      my @row = @{ shift @_ };
      return { A => $row[0], B => $row[1], C => $row[2] };
    },
    -PARAMS => ['A'] );

  #Or with hashrefs
  my $arr_ref = $helper->execute(
    -SQL          => 'select a,b,c from tab where col =?',
    -USE_HASHREFS => 1,
    -CALLBACK     => sub {
      my $row = shift @_;
      return { A => $row->{a}, B => $row->{b}, C => $row->{c} };
    },
    -PARAMS => ['A'] );

  Description:
Uses a callback defined by the sub decalaration. Here we specify how
the calling code will deal with each row of a database's result set. The
sub can return any type of Object/hash/data structure you require.

Should you not specify a callback then a basic one will be assigned to
you which will return a 2D array structure e.g.

  my $arr_ref = $helper->execute(
                           -SQL => 'select a,b,c from tab where col =?',
                           -PARAMS => ['A'] );

This is equivalent to DBI's selectall_arrayref() subroutine.

As an extension to this method you can write a closure subroutine which
takes in two parameters. The first is the array/hash reference & the
second is the statement handle used to execute. 99% of the time you will
not need it but there are occasions where you do need it. An example of