NAME

Activator::DB - Wrap DBI with convenience subroutines and consistant access accross all programs in a project.

Synopsis

  use Activator::DB;
  my $db = Activator::DB->connect('default'); # connect to default db

Get a single row:

    my @row     = $db->getrow( $sql, $bind, @args );
    my $rowref  = $db->getrow_arrayref( $sql, $bind, @args );

Get hashref of col->value pairs:

    my $hashref = $db->getrow_hashref( $sql, $bind, @args );

Get all rows arrayref (these are identical):

    my $rowsref = $db->getall( $sql, $bind, @args );
    my $rowsref = $db->getall_arrayrefs( $sql, $bind, @args );

Get all rows ref: with each row a hashref of cols->value pairs:

    my $rowsref = $db->getall_hashrefs( $sql, $bind, @args );

do any query ( usually INSERT, DELETE, UPDATE ):

    my $id = $db->do( $sql, $bind, @args );

do query, but return id instead of success.:

    my $id = $db->do_id( $sql, $bind, @args );
   ( NOTE: this is very mysql dependant at the moment)

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

Transactions (NOT YET IMPLEMENTED)::

    my $altdb = Activator::DB->connect('altdb');
    $db->begin_work();
    $db->do( @stuff );
    $db->do( @more_stuff );
    $db->commit();

DESCRIPTION

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:

Provides connect string aliases centrally configured.
Provide consistent arguments handling to all query functions.
Provides connection caching without Apache::DBI -- this allows use of your model layer code in crons, daemons AND website.
Connection and query debug dumps using your project or module level Activator::Log config, or on a per-query basis.
Allows all code in your project/team/company to access the db in a consistent fashion.
By default, dies on all errors enforcing try/catch programming
Implemented as a singleton so each process is guranteed to be using no more than one connection to each database from the pool.

Disadvantages:

If you know DBI, you don't necessarily know Activator::DB
NOT THREAD SAFE
Only tested with MySql and PostgreSQL

CONFIGURATION

This module uses Activator::Registry to automatically choose default databases, and Activator::Log to log warnings and errors.

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'
  }

Registry Configuration

Add an 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

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.

## pseudo-OO example:

  my $db = Activator::DB->connect( 'db_alias' );
  $db->query_method( $sql, $bind, @args );
  $db->connect( 'alt_db_alias' );
  $db->query_method( $sql, $bind, @args );
  $db->connect( 'db_alias' );
  $db->query_method( $sql, $bind, @args );

## Static formatted calls require that you dictate the connection for every request. So, the above can also be done as:

  Activator::DB->query_method( $sql, $bind, connect => 'db_alias', @args );
  Activator::DB->query_method( $sql, $bind, connect => 'alt_db_alias', @args );
  Activator::DB->query_method( $sql, $bind, connect => 'db_alias', @args );

## However, the common use case for this module is:

  my $db = Activator::DB->connect( 'db_alias' );
  $db->query_method( $sql, $bind, @args );
    ### do some perl
  $db->query_method( $sql, $bind, @args );
    ### do some perl
  $db->query_method( $sql, $bind, @args );
    ### do some perl
  ... etc.

connect() Usage

  my $db = Activator::DB->connect('my_db');   # connect to my_db
  my $db->connect('default'); # connect to default db
  my $db->connect('def');     # shortcut to default db
  my $db->connect();          # shortercut to default db

connect() Caveat

Note that 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 $db) for the Activator::DB object.

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:

## Simple query:

    my @row = $db->getrow( $sql );

## Needy query:

    my $res = $db->do( $sql, $bind,
          connect => 'altdb', # changes the alias for future connections!
          attr => { AutoCommit => 0, },
          debug => 1,
     );

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) 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

METHODS

getrow

getrow_arrayref

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 )

getall

getall_arrayrefs

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.

getall() is an alias for 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 ],
              ];
```

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 },
              ];

  my $rowref = $db->getall( $sql, $bind, @args )
  my $rowref = $db->getall_arrayrefs( $sql, $bind, @args )
  my $rowref = $db->getall_hashrefs( $sql, $bind, @args )

do

Execute a SQL statement and return the number of rows affected. Dies on failure.

Usage:

  my $res = $db->do( $sql, $bind, @args )

do_id

Execute a SQL statement that generates an id and return the id. Dies on failure.

Usage:

  my $id = $db->do_id( $sql, $bind, @args )

AUTHOR

Karim A. Nassar

COPYRIGHT

You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.

To install Activator, copy and paste the appropriate command in to your terminal.

cpanm

cpanm Activator

CPAN shell

perl -MCPAN -e shell
install Activator

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

Synopsis

DESCRIPTION

CONFIGURATION

Registry Setup (from Activator::Registry)

Registry Configuration

USAGE

connect() Usage

connect() Caveat

Query Methods Usage

Query Failures & Errors

METHODS

getrow

getrow_arrayref

getrow_hashref

getall

getall_arrayrefs

getall_hashrefs

do

do_id

SEE ALSO

AUTHOR

COPYRIGHT

Module Install Instructions