NAME

CPAN::Testers::Common::DBUtils - Basic Database Wrapper

SYNOPSIS

use CPAN::Testers::Common::DBUtils;

my $dbx = CPAN::Testers::Common::DBUtils->new(
              driver      => 'mysql',
              database    => 'testdb');

sub errors { print STDERR "Error: $_[0], sql=$_[1]\n" }
my $dbi = CPAN::Testers::Common::DBUtils->new(
              driver  => 'CSV',
              dbfile  => '/var/www/mysite/db
              errsub  => \&errors);

my @arr = $dbi->get_query('array',$sql);
my @arr = $dbi->get_query('array',$sql,$id);
my @arr = $dbi->get_query('hash', $sql,$id);

my $id = $dbi->id_query($sql,$id,$name);
$dbi->do_query($sql,$id);

$dbi->do_rollback();  # where AutoCommit is disabled
$dbi->do_commit();    # where AutoCommit is disabled

# array iterator
my $next = $dbi->iterator('array',$sql);
my $row = $next->();
my $id = $row->[0];

# hash iterator
my $next = $dbi->iterator('hash',$sql);
my $row = $next->();
my $id = $row->{id};

$value = $dbi->quote($value);

DESCRIPTION

The DBUtils package is a wrapper around the database interface layer, providing a collection of methods to access and alter the data within the database, which handle any errors and abstracts these commonly called routines away from the calling program.

Known supported drivers:

MySQL     (database)
SQLite    (database)
CSV       (dbfile)
ODBC      (driver)

The keys in braces above, indicate how the name/location of the data store is passed to the wrapper and thus added to the connection string.

CONSTRUCTOR

new()

The Constructor method can be called with an anonymous hash, listing the values to be used to connect to and handle the database.

Values in the hash can be

driver (*)
database (+)
dbfile (+)
dbhost
dbport
dbuser
dbpass
errsub
AutoCommit

(*) These entries MUST exist in the hash. (+) At least ONE of these must exist in the hash, and depend upon the driver.

Note that 'dbfile' is for use with a flat file database, such as DBD::CSV.

By default the errors are handle via croak(), however if you pass a subroutine reference that will be called instead. Parameters passed to the error subroutine are the error string, the SQL string and the list of arguments given.

AutoCommit is on by default, unless you explicitly pass 'AutoCommit => 0'.

PUBLIC INTERFACE METHODS

get_query(type,sql,<list>)

type - 'array' or 'hash'
sql - SQL statement
<list> - optional additional values to be inserted into SQL placeholders

This method performs a SELECT statement and returns an array of the returned rows. Each column within the row is then accessed as an array or hash as specified by 'type'.

iterator(type,sql,<list>)

type - 'array' or 'hash'
sql - SQL statement
<list> - optional additional values to be inserted into SQL placeholders

This method is used to call a SELECT statement a row at a time, via a closure. Returns a subroutine reference which can then be used to obtain each row as a array reference or hash reference. Finally returns 'undef' when no more rows can be returned.

do_query(sql,<list>)

sql - SQL statement
<list> - optional additional values to be inserted into SQL placeholders

This method is used to perform an SQL action statement.

id_query(sql,<list>)

sql - SQL statement
<list> - optional additional values to be inserted into SQL placeholders

This method is used to perform an SQL action statement. Commonly used when performing an INSERT statement, so that it returns the inserted record id.

repeat_query(sql,<list>,[(<arg1>), ... (<argN>)])

sql - SQL statement
<list> - values to be inserted into SQL placeholders
<argX> - arguments to be inserted into placeholders

This method is used to store an SQL action statement, together withe the associated arguments. Commonly used with statements where multiple arguments sets are applied to the same statement.

repeat_queries()

This method performs all stored SQL action statements.

repeater(sql,<list ref>)

sql - SQL statement
<list ref> - list of values to be inserted into SQL placeholders

This method performs an single SQL action statement, using all the associated arguments within the given list reference.

do_commit()

Performs a commit on the transaction where AutoCommit is disabled.

do_rollback()

Performs a rollback on the transaction where AutoCommit is disabled.

quote(string)

string - string to be quoted

This method performs a DBI quote operation, which will quote a string according to the SQL rules.

Accessor Methods

The following accessor methods are available:

• driver

• database

• dbfile

• dbhost

• dbport

• dbuser

• dbpass

All methods can be called to return the current value of the associated object variable. Note that these are only meant to be used as read-only methods.

SEE ALSO

CPAN::Testers::Data::Generator, CPAN::Testers::WWW::Statistics

http://www.cpantesters.org/, http://stats.cpantesters.org/, http://wiki.cpantesters.org/

AUTHOR

Barbie, <barbie@missbarbell.co.uk> for Miss Barbell Productions, http://www.missbarbell.co.uk/

COPYRIGHT & LICENSE

Copyright (C) 2002-2014 Barbie for Miss Barbell Productions
All Rights Reserved.

This module is free software; you can redistribute it and/or
modify it under the Artistic Licence v2.