Activator
view release on metacpan or search on metacpan
lib/Activator/DB.pm view on Meta::CPAN
$e .
$self->_get_sql( $sql, $bind )
);
}
# clean up return value for total consistency.
if ( !defined( $row ) ) {
if ( $fn eq 'getrow_hashref' ) {
$row = {};
}
else {
$row = [];
}
}
$self->_debug_sql( 5, $sql, $bind, \%args);
if ( $fn eq 'getrow' ) {
return ( $self, $sql, $bind, \%args, @row );
}
return ( $self, $sql, $bind, \%args, $row );
}
sub do_id {
my ( $pkg, $sql, $bindref, %args ) = @_;
my ( $self, $bind, $attr ) = $pkg->_explode( $bindref, \%args );
my $conn = $self->_get_cur_conn();
$self->_start_timer();
my $res;
try eval {
$res = $self->_get_sth( $sql, $bind, $attr, 'want_exec_result' );
};
if ( catch my $e ) {
$e->rethrow;
}
$self->_debug_sql( 4, $sql, $bind, \%args );
if ( $res == 1 ) {
if ( $conn->{engine} eq 'mysql' ) {
return $conn->{dbh}->{mysql_insertid};
}
elsif ( $conn->{engine} eq 'Pg' ) {
my $row = $self->getrow_arrayref( "SELECT currval('$args{seq}')" );
return @$row[0];
}
} else {
Activator::Exception::DB->throw('execute',
'failure',
$self->_get_sql( $sql, $bind ) .
" did not cause an insert"
);
}
}
sub do {
my ( $pkg, $sql, $bindref, %args ) = @_;
my ( $self, $bind, $attr, $alt_error ) = $pkg->_explode( $bindref, \%args );
my $conn = $self->_get_cur_conn();
$self->_start_timer();
my $res;
try eval {
$res = $conn->{dbh}->do( $sql, $attr, @$bind );
};
if ( catch my $e ) {
$e->rethrow;
}
$self->_debug_sql( 4, $sql, $bind, \%args );
if ( $res eq '0E0' ) {
return 0;
}
return $res;
}
# allow diconnection before DESTROY is called
sub disconnect_all {
my ( $pkg ) = @_;
my $self = $pkg->connect('default');
foreach my $conn ( keys %{ $self->{connections} } ) {
if ( exists( $self->{connections}->{ $conn }->{dbh} ) ) {
$self->{connections}->{ $conn }->{dbh}->disconnect();
}
}
}
# Transaction support
sub begin_work {
my ( $self ) = @_;
$self->begin();
}
sub begin {
my ( $self ) = @_;
my $conn = $self->_get_cur_conn();
$conn->{dbh}->{AutoCommit} = 0;
}
sub commit {
my ( $self ) = @_;
my $conn = $self->_get_cur_conn();
$conn->{dbh}->commit;
$conn->{dbh}->{AutoCommit} = 1;
}
sub abort {
my ( $self ) = @_;
$self->rollback();
}
sub rollback {
my ( $self ) = @_;
my $conn = $self->_get_cur_conn();
try eval {
$conn->{dbh}->rollback;
lib/Activator/DB.pm view on Meta::CPAN
my $id = $db->do_id( $sql, $bind, @args );
( NOTE: this is very mysql dependant at the moment)
=item *
Get data from a different db for a while:
$db->connect('alt'); # connect to alternate db
# do something
$db->connect('def'); # reset to default connection
# do something else
=item *
Transactions (NOT YET IMPLEMENTED)::
my $altdb = Activator::DB->connect('altdb');
$db->begin_work();
$db->do( @stuff );
$db->do( @more_stuff );
$db->commit();
=back
=head1 DESCRIPTION
C<Activator::DB> module provides convenience and total consistency to
accessing a database throughout a project. The idea is to reduce
typing for the common cases, and remove worrying about connections.
This module is a wrapper for DBI providing these advantages:
=over
=item *
Provides connect string aliases centrally configured.
=item *
Provide consistent arguments handling to all query functions.
=item *
Provides connection caching without Apache::DBI -- this allows use of
your model layer code in crons, daemons AND website.
=item *
Connection and query debug dumps using your project or module level
C<Activator::Log> config, or on a per-query basis.
=item *
Allows all code in your project/team/company to access the db in a
consistent fashion.
=item *
By default, dies on all errors enforcing try/catch programming
=item *
Implemented as a singleton so each process is guranteed to be using no
more than one connection to each database from the pool.
=back
Disadvantages:
=over
=item *
If you know DBI, you don't necessarily know C<Activator::DB>
=item *
NOT THREAD SAFE
=item *
Only tested with MySql and PostgreSQL
=back
=head1 CONFIGURATION
This module uses L<Activator::Registry> to automatically choose default
databases, and L<Activator::Log> to log warnings and errors.
=head2 Registry Setup (from Activator::Registry)
This module expects an environment variable ACT_REG_YAML_FILE to be
set. If you are utilizing this module from apache, this directive must
be in your httpd configuration:
SetEnv ACT_REG_YAML_FILE '/path/to/config.yml'
If you are using this module from a script, you need to insure that
the environment is properly set using a BEGIN block:
BEGIN{
$ENV{ACT_REG_YAML_FILE} ||= '/path/to/config.yml'
}
=head2 Registry Configuration
Add an C<Activator::DB> section to your project YAML configuration file:
'Activator::Registry':
log4perl<.conf>: # Log4perl config file or definition
# See Logging Configuration below
'Activator::DB':
default: # default configuration for all connections
connection: <conn_alias>
## Optional default attributes and config for all connections
config:
debug: 0/1 # default: 0, affects all queries, all aliases
reconn_att: <int> # attempt reconnects this many times. default: 3
reconn_sleep: <int> # initial sleep seconds between reconnect attempts.
# doubles every attempt. default: 1
attr: # connection attributes. Only AutoCommit at this time
AutoCommit: 0/1 # default: 1
## You must define at least one connection alias
connections:
<conn_alias>:
user: <user>
pass: <password>
dsn: '<DSN>' # MySql Example: DBI:mysql:<DBNAME>:<DBHOST>
# PostgreSQL Example: DBI:Pg:dbname=<DBNAME>
# see: perldoc DBI, perldoc DBD::Pg, perldoc DBD::mysql
# for descriptions of valid DSNs
## These attributes and config are all optional, and use the default from above
attr:
AutoCommit: 0/1
config:
debug: 0/1 # only affects this connection
=head1 USAGE
This module can be used either pseudo-OO or static on multiple
databases. I say pseudo-OO, because you don't call new: this module
auto-vivicates a singleton object whenever you connect for the first
time.
lib/Activator/DB.pm view on Meta::CPAN
Note that C<connect()> always returns the singleton object, which in some
usage patterns could cause some confusion:
my $db1->connect('db1'); # connect to db1
$db1->query( $sql, $bind, @args ); # acts on db1
my $db2->connect('db2'); # connect to db2
$db2->query( $sql, $bind, @args ); # acts on db2
$db1->query( $sql, $bind, @args ); # still acts on db2!
For this reason, it is highly recommended that you always use the same
variable name (probably C<$db>) for the Activator::DB object.
=head2 Query Methods Usage
Every query function takes named arguments in the format of:
Activator::DB->$query_method( $sql, $bind, opt_arg => <opt_value> );
Mandatory Arguments:
sql : sql statement string
bind : bind values arrayref
Optional Arguments:
conn => alias of the db connection (default is 'default')
NOTE: this changes the connection alias for all future queries
attr => hashref of attributes to use for ONLY THIS QUERY
Supported: AutoCommit
debug => pretty print sql debugging lines
# NOT YET SUPPORTED
slice => possible future support for DBI::getall_hashref
max_rows => possible future support for DBI::getall_hashref
Examples:
=over
=item ## Simple query:
my @row = $db->getrow( $sql );
=item ## Needy query:
my $res = $db->do( $sql, $bind,
connect => 'altdb', # changes the alias for future connections!
attr => { AutoCommit => 0, },
debug => 1,
);
=back
=head2 Query Failures & Errors
All query methods die on failure, and must be wrapped in a try/catch block.
eval {
Activator::DB->query_method( $sql, $bind, @args );
};
if ($@) {
# catch the error
}
We highly recommend (and use extensively)
L<Exception::Class::TryCatch> which allows this syntactic sugar:
try eval {
Activator::DB->query_method( $sql, $bind, @args );
};
if ( catch my $e ) {
# rethrow, throw a new error, print something, AKA: handle it!
}
Errors Thrown:
connection failure - could not connect to database
sql missing - query sub called without 'sql=>' argument
connect missing - static call without 'connect=>' argument
prepare failure - failure to $dbh->prepare
execute failure - failure to $dbh->execute
alias_config missing - connection alias has no configuration
activator_db error - sub _warn_or_die() died without error args passed in
fetch failure - $sth->fetch* call failed
do failure - $dbh->do call failed
=head1 METHODS
=head2 getrow
=head2 getrow_arrayref
=head2 getrow_hashref
Prepare and Execute a SQL statement and get a the result of values
back via DBI::fetchrow_array(), DBI::fetchrow_arrayref(),
DBI::fetchrow_hashref() respectively. NOTE: Unlike DBI, these return
empty array/arrayref/hashref (like DBI::fetchall_arrayref does,
instead of undef) when there are no results.
Usage:
my @row = $db->getrow( $sql, $bind, @args )
my $rowref = $db->getrow_arrayref( $sql, $bind, @args )
my $hashref = $db->getrow_hashref( $sql, $bind, @args )
=head2 getall
=head2 getall_arrayrefs
=head2 getall_hashrefs
Prepare and Execute a SQL statement, and return a reference to the
result obtained by DBI::fetchall_arrayref(). Returns an empty arrayref
if no rows returned for the query.
=over
=item *
C<getall()> is an alias for C<getall_arrayrefs()> and they both return an
arrayref of arrayrefs, one arrayref of values for each row of data
from the query.
$rowrefs is [ [ row1_col1_val, row1_col2_val ],
[ row2_col1_val, row2_col2_val ],
];
=item *
C<getall_hashrefs()> returns an arrayref of of rows represented by
hashrefs of column name => value mappings.
$rowrefs is [ { col1 => val, col2 => val },
{ col1 => val, col2 => val },
];
=back
my $rowref = $db->getall( $sql, $bind, @args )
my $rowref = $db->getall_arrayrefs( $sql, $bind, @args )
my $rowref = $db->getall_hashrefs( $sql, $bind, @args )
( run in 0.911 second using v1.01-cache-2.11-cpan-39bf76dae61 )