NAME

Ima::DBI - Database connection caching and organization

SYNOPSIS

# Class-wide methods.
__PACKAGE__->set_db($db_name, $data_source, $user, $password);
__PACKAGE__->set_db($db_name, $data_source, $user, $password, \%attr);

__PACKAGE__->set_sql($sql_name, $statement, $db_name);
__PACKAGE__->set_sql($sql_name, $statement, $db_name, $cache);


# Object methods.
$dbh = $obj->db_*;      # Where * is the name of the db connection.
$sth = $obj->sql_*;     # Where * is the name of the sql statement.
$sth = $obj->sql_*(@sql_pieces);

$obj->DBIwarn($what, $doing);


# Modified statement handle methods.
$rv = $sth->execute;
$rv = $sth->execute(@bind_values);
$rv = $sth->execute(\@bind_values, \@bind_cols);

# In addition to the normal DBI sth methods...
$row_ref = $sth->fetch;
@row     = $sth->fetch;

$row_ref = $sth->fetch_hash;
%row     = $sth->fetch_hash;

$rows_ref = $sth->fetchall;
@rows     = $sth->fetchall;

$rows_ref = $sth->fetchall_hash;
@tbl      = $sth->fetchall_hash;

$rc = $obj->commit;             #UNIMPLEMENTED
$rc = $obj->commit(@db_names);  #UNIMPLEMENTED

$rc = $obj->rollback;            #UNIMPLEMENTED
$rc = $obj->rollback(@db_names); #UNIMPLEMENTED

$sth->clear_cache;  #UNIMPLEMENTED

$obj->clear_db_cache;            #UNIMPLEMENTED
$obj->clear_db_cache(@db_names); #UNIMPLEMENTED

$obj->clear_sql_cache;             #UNIMPLMENTED
$obj->clear_sql_cache(@sql_names); #UNIMPLMENTED

DESCRIPTION

Ima::DBI attempts to organize and facilitate caching and more efficient use of database connections and statement handles by storing DBI and SQL information with your class (instead of as seperate objects). This allows you to pass around just one object without worrying about a trail of DBI handles behind it.

One of the things I always found annoying about writing large programs with DBI was making sure that I didn't have duplicate database handles open. I was also annoyed by the somewhat wasteful nature of the prepare/execute/finish route I'd tend to go through in my subroutines. The new DBI->connect_cached and DBI->prepare_cached helped alot, but I still had to throw around global datasource, username and password information.

So, after a while I grew a small library of DBI helper routines and techniques. Ima::DBI is the culmination of all this, put into a nice(?), clean(?) class to be inherited from.

Why should I use this thing?

Ima::DBI is a little odd, and it's kinda hard to explain. So lemme explain why you'd want to use this thing...

Consolidation of all SQL statements and database information

No matter what, embedding one language into another is messy. DBI alleviates this somewhat, but I've found a tendency to have that scatter the SQL around inside the Perl code. Ima::DBI allows you to easily group the SQL statements in one place where they are easier to maintain (especially if one developer is writing the SQL, another writing the Perl). Alternatively, you can place your SQL statement alongside the code which uses it. Whatever floats your boat.

Database connection information (data source, username, password, atrributes, etc...) can also be consolidated together and tracked.

Both the SQL and the connection info are probably going to change alot, so having them well organized and easy to find in the code is a Big Help.
Holds off opening a database connection until necessary.

While Ima::DBI is informed of all your database connections and SQL statements at compile-time, it will not connect to the database until you actually prepare a statement on that connection.

This is obviously very good for programs that sometimes never touch the database. It's also good for code that has lots of possible connections and statements, but which typically only use a few. Kinda like an autoloader.
Easy integration of the DBI handles into your class

Ima::DBI causes each database handle to be associated with your class, allowing you to pull handles from an instance of your object, as well as making many oft-used DBI methods available directly from your instance.

This gives you a cleaner OO design, since you can now just throw around the object as usual and it will carry its associated DBI baggage with it.
Honors taint mode

It always struck me as a design deficiency that tainted SQL statements could be passed to $sth->prepare(). For example:
```
# $user is from an untrusted source and is tainted.
$user = get_user_data_from_the_outside_world;
$sth = $dbh->prepare('DELETE FROM Users WHERE User = $user');
```
Looks innocent enough... but what if $user was the string "1 OR User LIKE %". You just blew away all your users, hope you have backups.

Ima::DBI turns on the DBI->connect Taint attribute so that all DBI methods (except execute()) will no longer accept tainted data. "Taint" in DBI for details.
Taints returned data

Databases should be like any other system call. Its the scary Outside World, thus it should be tainted. Simp. Ima::DBI turns on DBI's Taint attribute on each connection. This feature is overridable by passing your own Taint attribute to set_db as normal for DBI. "Taint" in DBI for details.
Encapsulation of some of the more repetative bits of everyday DBI usage

I get lazy alot and I forget to do things I really should, like using bind_cols(), or rigorous error checking. Ima::DBI does some of this stuff automaticly, other times it just makes it more convenient.
Encapsulation of DBI's cache system

DBI's automatic handle caching system is relatively new, some people aren't aware of its use. Ima::DBI uses it automatically, so you don't have to worry your pretty little head about it. (It even makes it a bit more efficient)
Sharing of database and sql information amongst inherited classes

Any SQL statements and connections created by a class is available to its children via normal method inheritance.
Convenience and orthoganality amongst statement handle methods

It always struck me odd that DBI didn't take much advantage of Perl's context sensitivity. Ima::DBI redefines some of the various fetch methods to fix this oversight; it also adds a few new methods for convenience (though not necessarily efficiency).
Guarantees one connection per program.

One program, one database connection (per database user). One program, one prepared statement handle (per statement, per database user). That's what Ima::DBI enforces. Extremely handy in persistant environments (servers, daemons, mod_perl, FastCGI, etc...)
Encourages use of bind parameters and columns

Bind parameters are safer and more efficient than embedding the column information straight into the SQL statement. Bind columns are more efficient than normal fetching. Ima::DBI pretty much requires the usage of the former, and eases the use of the latter.

Why shouldn't I use this thing.

It's all about OO

Although it is possible to use Ima::DBI as a stand-alone module as part of a function-oriented design, its generally not to be used unless integrated into an object-oriented design.
Overkill for small programs
Overkill for programs with only one or two SQL statements

Its up to you whether the trouble of setting up a class and jumping through the necessary Ima::DBI hoops is worth it for small programs. To me, it takes just as much time to set up an Ima::DBI subclass as it would to access DBI without it... but then again I wrote the module. YMMV.
Overkill for programs that only use their SQL statements once

Ima::DBI's caching might prove to be an unecessary performance hog if you never use the same SQL statement twice (soon caching will become optional). Not sure, I haven't looked into it.

USAGE

The basic steps to "DBIing" a class are:

Inherit from Ima::DBI
Set up and name all your database connections via set_db()
Set up and name all your SQL statements via set_sql()
Use sql_* to retrieve your statement handles ($sth) as needed and db_* to retreive database handles ($dbh).

Have a look at the EXAMPLE below.

TAINTING

Ima::DBI, by default, uses DBI's Taint flag on all connections.

This means that no Ima::DBI method will accept tainted data and all data fetched from the database will be tainted. This may be different from the DBI behavior you're used to. "Taint" in DBI for details.

METHODS

Class methods

set_db

__PACKAGE__->set_db($db_name, $data_source, $user, $password);
__PACKAGE__->set_db($db_name, $data_source, $user, $password, \%attr);

This method is used in place of DBI->connect to create your database handles.

Sets up a new DBI database handle associated to $db_name. All other arguments are passed through to DBI->connect_cached.

A new method is created for each db you setup. This new method is db_$db_name... so, for example, __PACKAGE__->set_db("foo", ...) will create a method called db_foo().

If no %attr is supplied (RaiseError => 1, AutoCommit => 0, PrintError => 0) is assumed. This is a better default IMHO, however it does give databases without transactions (such as MySQL) a hard time. Be sure to turn AutoCommit back on if your database does not support transactions.

The actual database handle creation (and thus the database connection) is held off until a prepare is attempted with this handle.

Spaces in $db_name will be translated into underscores ('_')

set_sql

__PACKAGE__->set_sql($sql_name, $statement, $db_name);
__PACKAGE__->set_sql($sql_name, $statement, $db_name, $cache);

This method is used in place of DBI->prepare to create your statement handles.

Sets up a new statement handle using associated to $sql_name using the database connection associated with $db_name. $statement is passed through to either DBI->prepare or DBI->prepare_cached (depending on $cache) to create the statement handle.

If $cache is true or isn't given then prepare_cached() will be used to prepare the statement handle and it will be cached. If $cache is false then a normal prepare() will be used and the statement handle will be recompiled on every sql_*() call. If you have a statement which changes alot or is used very infrequently you might not want it cached.

A new method is created for each statement you set up. This new method is sql_$sql_name... so, as with set_db, __PACKAGE__->set_sql("bar", ..., "foo"); will create a method called sql_bar() which uses the database connection from db_foo().

The actual statement handle creation is held off until sql_* is first called on this name.

Spaces in $sql_name will be translated into underscores ('_')

To make up for the limitations of bind parameters, $statement can contain sprintf() style formatting (ie. %s and such) to allow dynamically generated SQL statements. See sql_* below for more details.

Object methods

db_*

$dbh = $obj->db_*;

This is how you directly access a database handle you set up with set_db.

The actual particular method name is derived from what you told set_db.

db_* will handle all the issues of making sure you're already connected to the database.

sql_*

$sth = $obj->sql_*;
$sth = $obj->sql_*(@sql_pieces);

sql_*() is a catch-all name for the methods you set up with set_sql(). For instance, if you did:

__PACKAGE__->set_sql('GetAllFoo', 'Select * From Foo', 'SomeDb');

you'd run that statement with sql_GetAllFoo().

sql_* will handle all the issues of making sure the database is already connected, and the statement handle is prepared. It returns a prepared statement handle for you to use. (You're expected to execute() it)

If sql_*() is given a list of @sql_pieces it will use them to fill in your statement, assuming you have sprintf() formatting tags in your statement. For example:

__PACKAGE__->set_sql('GetTable', 'Select * From %s', 'Things');

# Assuming we have created an object... this will prepare the
# statement 'Select * From Bar'
$sth = $obj->sql_Search('Bar');

Be very careful with what you feed this function. It cannot do any quoting or escaping for you, so it is totally up to you to take care of that. Fortunately if you have tainting on you will be spared the worst.

It is recommended you only use this in cases where bind parameters will not work.

clear_db_cache *UNIMPLEMENTED*

$obj->clear_db_cache;
$obj->clear_db_cache(@db_names);

Ima::DBI uses the DBI->connect_cached to cache open database handles. For whatever reason you might want to clear this cache out and start over again.

A call to clear_db_cache with no arguments deletes all database handles out of the cache and all associated statement handles. Otherwise it only deletes those handles listed in @db_names (and their associated statement handles).

Note that clearing from the cache does not necessarily destroy the database handle. Something else might have a reference to it.

Alternatively, you may do: $obj->db_Name->clear_cache;

clear_sql_cache *UNIMPLEMENTED*

$obj->clear_sql_cache;
$obj->clear_sql_cache(@sql_names);

Does the same thing as clear_db_cache, except it does it in relation to statement handles.

Alternatively, you may do: $obj->sql_Name->clear_cache;

DBIwarn *UNIMPLEMENTED*

$obj->DBIwarn($what, $doing);

Produces a useful error for exceptions with DBI.

I'm not particularly happy with this interface

Most useful like this:

eval {
    $self->sql_Something->execute($self->{ID}, @stuff);
};
if($@) {
    $self->DBIwarn($self->{ID}, 'Something');
            return;
}

Modified database handle methods

Ima::DBI makes some of the methods available to your object that are normally only available via the database handle. In addition, it spices up the API a bit.

commit *UNIMPLEMENTED*

$rc = $obj->commit;
$rc = $obj->commit(@db_names);

Derived from $dbh->commit() and basically does the same thing.

If called with no arguments, it causes commit() to be called on all database handles associated with $obj. Otherwise it commits all database handles whose names are listed in @db_names.

Alternatively, you may like to do: $rc = $obj->db_Name->commit;

rollback *UNIMPLEMENTED*

$rc = $obj->rollback;
$rc = $obj->rollback(@db_names);

Derived from $dbj->rollback, it acts just like Ima::DBI->commit, except that it calls rollback().

Alternatively, you may like to do: $rc = $obj->db_Name->rollback;

clear_cache *UNIMPLEMENTED*

$dbh->clear_cache;

Provides a mechanism to clear a given database handle from the cache.

Modified statement handle methods

Ima::DBI overrides the normal DBI statement handle with its own, slightly modified, version. Don't worry, it inherits from DBI::st, so anything not explicitly mentioned here will work just like in normal DBI.

execute

$rv = $sth->execute;
$rv = $sth->execute(@bind_values);
$rv = $sth->execute(\@bind_values, \@bind_cols);

DBI::st->execute is overridden to enhance execute() a bit.

If called with no arguments, or with a simple list, execute() operates normally. When when called with two array references, it performs the functions of bind_param, execute and bind_columns similar to the following:

$sth->execute(@bind_values);
$sth->bind_columns(undef, @bind_cols);

In addition, execute will accept tainted @bind_values. I personally found it annoying to have to detaint everything I passed to execute() and tended to shut off taint mode rather than go through the trouble. I also can't think of what a malicious user could do with a tainted bind value (in the general case. Your application may vary.)

Thus a typical idiom would be:

$sth->execute([$this, $that], [\($foo, $bar)]);

Of course, this method provides no way of passing bind attributes through to bind_param or bind_columns. If that is necessary, then you must perform the bind_param, execute, bind_col sequence yourself.

clear_cache *UNIMPLEMENTED*

$sth->clear_cache;

Provides a mechanism to clear a given statement handle from the cache.

fetching

The following are modifications or expansions on DBI's various fetch methods. Most are simply context sensitive implementations. Some just have shorter names.

Remember that most of the list context versions of the fetch methods tend to use more memory and be slower. Same with the fetchall methods. Use with care.

fetch

$row_ref = $sth->fetch;
@row     = $sth->fetch;

A context sensitive version of fetch(). When in scalar context, it will act as fetchrow_arrayref. In list context it will use fetchrow_array.

fetch_hash

$row_ref = $sth->fetch_hash;
%row     = $sth->fetch_hash;

A modification on fetchrow_hashref. When in scalar context, it acts just as fetchrow_hashref() does. In list context it returns the complete hash.

fetchall

$rows_ref = $sth->fetchall;
@rows     = $sth->fetchall;

A modification on fetchall_arrayref. In scalar context it acts as fetchall_arrayref. In list it returns an array of references to rows fetched.

fetchall_hash

$rows_ref = $sth->fetchall_hash;
@rows     = $sth->fetchall_hash;

A mating of fetchall_arrayref() with fetchrow_hashref(). It gets all rows from the hash, each as hash references. In scalar context it returns a reference to an array of hash references. In list context it returns a list of hash references.

EXAMPLE

package Foo;
use base qw(Ima::DBI);

# Set up database connections (but don't connect yet)
__PACKAGE__->set_db('Users', 'dbi:Oracle:Foo', 'admin', 'passwd');
__PACKAGE__->set_db('Customers', 'dbi:Oracle:Foo', 'Staff', 'passwd');

# Set up SQL statements to be used through out the program.
__PACKAGE__->set_sql('FindUser', <<"SQL", 'Users');
    SELECT  *
    FROM    Users
    WHERE   Name LIKE ?
SQL

__PACKAGE__->set_sql('ChangeLanguage', <<"SQL", 'Customers');
    UPDATE  Customers
    SET     Language = ?
    WHERE   Country = ?
SQL


# rest of the class as usual.


package main:

$obj = Foo->new;

eval {
    # Does connect & prepare
    my $sth = $obj->sql_FindUser;
    # bind_params, execute & bind_columns
    $sth->execute(['Likmi%'], [\($name)]);
    while( $sth->fetch ) {
        print $name;
    }
    
    # Uses cached database and statement handles
    $sth = $obj->sql_FindUser;
    # bind_params & execute.
    $sth->execute('%Hock');
    @names = $sth->fetchall;
    
    # connects, prepares
    $rows_altered = $obj->sql_ChangeLanguage->execute(qw(es_MX mx));
};
unless ($@) {
    # Everything went okay, commit the changes to the customers.
    $obj->commit('Customers');
}
else {
    $obj->rollback('Customers');
    warn "DBI failure:  $@";    
}

TODO, Caveat, BUGS, etc....

Using undocumented features of DBI

Using DBI->init_rootclass to pull of subclassing. This is currently an undocumented method (this should change soon).

Unstable Interface

I haven't totally decided if I'm satisfied with the way this module works, so expect the worst, the interface will change.

execute() extensions questionable

I'm not really sure the additional functionality added to execute() is all that useful.

tainting may be too broad

Having Ima::DBI not accept any tainted data at all is probably too general, but I'd rather be too strict to start than be too lax and try to restrict later. In the future, certain methods may accept tainted data.

This is now a joint issue between DBI and Ima::DBI (well, more like a master/slave issue.)

db_* should take arguments

But what?

set_sql() and/or sql_* needs an optional caching flag.

Right now static SQL (those without sprintf flags) is always prepared with prepare_cached() and dynamic with just prepare(). It would be nice if there was a way to explicitly set the caching behavior.

I seriously doubt its thread safe.

You can bet cupcackes to sno-cones that much havoc will be wrought if Ima::DBI is used in a threaded Perl. I don't think DBI is even thread-safe.

Should make use of private_* handle method to store information

Having difficulty storing a list of dbh and sth names.

Storing the association between names and handles is fine, via the closures (and thus, the symbol table), but trying to store a complete list of all names available to a given object (and thus, inheritable) is difficult. Many minor methods are unimplemented until I figure out this problem.

The docs stink.

The docs were originally written when I didn't have a good handle on the module and how it will be used in practical cases. I need to rewrite the docs from the ground up.

AUTHOR

Michael G Schwern <schwern@pobox.com>

COPYRIGHT

This module is free software. You may distribute under the same terms as Perl itself. IT COMES WITHOUT WARRANTY OF ANY KIND.

THANKS MUCHLY

Tim Bunce, for enduring all my DBI questions and adding Taint,
prepare_cached and connect_cached methods to DBI.  It simplified
my job greatly!

Arena Networks, for effectively paying for me to finish writing
this module.

	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)