NAME

GlobalDBI - Simple DBI wrapper with support for multiple connections

SYNOPSIS

  use GlobalDBI;

  #
  # Define a new data source:
  #
  my $dsn = 'DBI:SQLite:dbname=example';

  GlobalDBI->define(
    "YourApp" => [ $dsn, '', '', { RaiseError => 1 } ],
  };

  #
  # Connect to a named data source:
  #
  my $dbi = GlobalDBI->new(dbName => "YourApp") || die $@;

DESCRIPTION

GlobalDBI is a helper/wrapper for DBI. It provides error logging, methods to perform common db functions, and support for connections to multiple databases. Since it uses DBI, you can still use native DBI method calls using the db-handle returned from get_dbh().

Errors are logged to the files defined in _log_error - one for read, one for write type functions

Database connection info (type, host, user, paswd, attributes) are defined in the CONNECTION hash located just before the _get_dbConnection method. Since all errors are logged, the PrintError attribute can be set to 0 unless you still want errors printed to STDERR.

Most all methods return undef on error so you should check the value before using it.

Example

  my $myDBI = GlobalDBI->new(dbName => 'my_db');
  my $sth = $myDBI->select_data('select * from JUNK where a=? and b=?',
    ['joe', 'bob']);
  foreach my $row($sth->fetchrow_hashref())
  {
    print "a = $row->{a} b = $row->{b} c = $row->{c}\n";
  }

GlobalDBI internally calls DBI's prepare and execute methods and logs any errors according to the settings in _log_error. Errors will cause undef to be returned so you should check the value before using it.

METHODS

• db_disconnect

  explicitly drop this connection now

• get_dbh

  returns the db handle for the current db so you can make direct DBI calls

    my $dbh = $db->get_dbh();
    $dbh->prepare... etc

• get_column_names

  returns the column names for the supplied table

    my $columns = $db->get_column_names('my_table');
    print join(',', @$columns);

• select_record

  meant for selecting 1 unique record using a primary key

  pass an array ref for the value key to select multiple records by primary key

    my $record = $db->select_record({
        table => 'my_table',
        key => 'my_table_id',
        value => 1000,
    });

• select_hash_list

  pass your custom sql (select) and bind param list

  returns a reference to a list of hashrefs

    my $list = $db->select_hash_list('select * from my_table where id > ?',$minID);
    foreach my $hRef(@$list) {
        print $hRef->{fieldNameA};
    }

• select_hash_by_key

  pass your custom sql (select), bind param list, and a column name to use as the hash key to point at each row of data returned. Make sure the key will be unique to avoid overwriting. Also make sure the key is in the select list (unless doing a 'select *')

    my $hashRef = $db->select_hash_by_key({
        sql => 'select id, name, age, sex from user where active=?',
        args => 'YES',
        key => 'id',
    });
  
    print $hashRef->{1001}{name};
    print $hashRef->{1002}{age};

• select_data

  pass your custom sql (select) and bind param list then use the returned DBI::st handle to call fetchrow_hashref, fetchrow_arrayref, etc

    my $sth = $db->select_data('select * from my_table where id > ?', $value);
    my $rows = $sth->fetchall_arrayref();
    ...

• insert_record

  pass the table name and a hashRef of data where the keys are the field names

  returns the new id (assuming the table had an auto-incrementing id)

    my $id = $db->insert_record('my_table', $dataRef);

• update_record

  meant for updating a unique record using a primary key

  pass an array ref for the value key to update multiple records by primary key

  returns the number of rows updated

    my $rowsAffected = $db->update_record({
        table    => 'my_table',
        data     => $dataRef, # hashref where keys are the field names in test_table
        keyName  => 'id',
        keyValue => [1,2,3,...] or '1',
    });

• delete_record

  meant for deleting a unique record using a primary key

  pass an array ref for the value key to update multiple records by primary key

  returns the number of rows deleted

  Specifying a LIMIT is optional

    my $rowsAffected = $db->delete_record({
        table => 'test_table',
        key => 'my_table_id',
        value => $id,
        limit => 1,
    });

• write_data

  pass your custom sql (update, insert, delete) and param list

  returns affected row count or insertid

    my $deleted_rows = $db->write_data(
      'delete from my_table where date_created > ? and userid like \'%test\'',
      $testDate
    );

• errstr, get_error_string

  returns the latest error text set as a result of the last action

    my $insertID = $db->write_data('delete from non_existent_table');
    unless ($insertID > 0) {
      print STDERR $db->errstr();
    }

• insert_record

  pass the table name and a hashRef of data where the keys are the field names

  returns the new id (assuming the table had an auto-incrementing id)

    my $id = $db->insert_record('my_table', $dataRef);

• update_record

  meant for updating a unique record using a primary key

  pass an array ref for the value key to update multiple records by primary key

  returns the number of rows updated

    my $rowsAffected = $db->update_record({
        table => 'my_table',
        data => $dataRef, # hashref where keys are the field names in test_table
        value => $id,
    });

• delete_record

  meant for deleting a unique record using a primary key

  pass an array ref for the value key to update multiple records by primary key

  returns the number of rows deleted

    my $rowsAffected = $db->delete_record({
        table => 'test_table',
        key => 'my_table_id',
        value => $id,
    });

• write_data

  pass your custom sql (update, insert, delete) and param list

  returns affected row count or insertid

    my $deleted_rows = $db->write_data(
      'delete from my_table where date_created > ? and userid like \'%test\'',
      $testDate
    );

• errstr, get_error_string

  returns the latest error text set as a result of the last action

    my $insertID = $db->write_data('delete from non_existent_table');
    unless ($insertID > 0) {
      print STDERR $db->errstr();
    }

REVISION

This document is for version 0.22 of GlobalDBI.

AUTHOR

Ryan Rose, Joe Spinney, Alex Ayars <pause@nodekit.org>

COPYRIGHT

  File: GlobalDBI.pm
 
  Copyright (c) 2009 TiVo Inc.
 
  All rights reserved. This program and the accompanying materials
  are made available under the terms of the Common Public License v1.0
  which accompanies this distribution, and is available at
  http://opensource.org/licenses/cpl1.0.txt