Ubuntu Manpage: Ima::DBI - Database connection caching and organization

NAME

       Ima::DBI - Database connection caching and organization

SYNOPSIS

           package Foo;
           use base 'Ima::DBI';

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

           my @database_names   = Foo->db_names;
           my @database_handles = Foo->db_handles;

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

           my @statement_names   = Foo->sql_names;

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

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

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

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 separate 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 a lot, 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 a lot, 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. See "Taint" in DBI for details.

• Taints returned data

Databases should be like any other system call. It's the scary Outside World, thus it should be
tainted. Simple. 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. See "Taint" in DBI for
details.

• Encapsulation of some of the more repetitive bits of everyday DBI usage

I get lazy a lot and I forget to do things I really should, like using bind_cols(), or rigorous error
checking. Ima::DBI does some of this stuff automatically, other times it just makes it more
convenient.

• Encapsulation of DBI's cache system

DBI's automatic handle caching system is relatively new, and some people aren't aware of its use.
Ima::DBI uses it automatically, so you don't have to worry 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 are available to its children via normal method
inheritance.

• 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 persistent
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 unnecessary performance hog if you never use the same SQL
statement twice. Not sure, I haven't looked into it.

USAGE

       The basic steps to "DBIing" a class are:

       1.  Inherit from Ima::DBI

       2.  Set up and name all your database connections via set_db()

       3.  Set up and name all your SQL statements via set_sql()

       4.  Use  sql_*  to retrieve your statement handles ($sth) as needed and db_* to retrieve database handles
           ($dbh).

       Have a look at EXAMPLE below.

TAINTING

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

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

Class Methods

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

       This method is used in place of DBI->connect to create your database  handles.  It  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 called "db_$db_name"... so, for
       example, Foo->set_db("foo", ...) will create a method called "db_foo()".  (Spaces  in  $db_name  will  be
       translated into underscores: '_')

       %attr is combined with a set of defaults (RaiseError => 1, AutoCommit => 0, PrintError => 0, Taint => 1).
       This  is  a  better default IMHO, however it does give databases without transactions (such as MySQL when
       used with the default MyISAM table type) 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.

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

       This method is used in place of DBI->prepare to create your statement handles. It sets up a new statement
       handle 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 a lot 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(), Foo->set_sql("bar", ..., "foo"); will  create  a  method  called  "sql_bar()"  which  uses  the
       database  connection  from  "db_foo()".  Again,  spaces  in $sql_name will be translated into underscores
       ('_').

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

   transform_sql
       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 (so to get a real percent sign, use '%%').

       The translation of the SQL happens in transform_sql(),  which  can  be  overridden  to  do  more  complex
       transformations. See Class::DBI for an example.

   db_names / db_handles
         my @database_names   = Foo->db_names;
         my @database_handles = Foo->db_handles;
         my @database_handles = Foo->db_handles(@db_names);

       Returns a list of the database handles set up for this class using set_db().  This includes all inherited
       handles.

       db_names() simply returns the name of the handle, from which it is possible to access it by converting it
       to a method name and calling that db method...

           my @db_names = Foo->db_names;
           my $db_meth = 'db_'.$db_names[0];
           my $dbh = $foo->$db_meth;

       Icky, eh?  Fortunately, db_handles() does this for you and returns a list of database handles in the same
       order  as  db_names().   Use this sparingly as it will connect you to the database if you weren't already
       connected.

       If given @db_names, db_handles() will return only the handles for those connections.

       These both work as either class or object methods.

   sql_names
         my @statement_names   = Foo->sql_names;

       Similar to db_names() this returns the names of all SQL statements set up for this class using set_sql(),
       inherited or otherwise.

       There is no corresponding sql_handles() because we can't know what arguments to pass in.

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:

           Foo->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:

           Foo->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.

   DBIwarn
           $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
           $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;

       If all the commits succeeded it returns true, false otherwise.

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

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

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

       If all the rollbacks succeeded it returns true, false otherwise.

EXAMPLE

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

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

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

           Foo->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:  $@";
           }

USE WITH MOD_PERL, FASTCGI, ETC.

       To help with use in forking environments, Ima::DBI database handles keep track of the PID of the  process
       they  were  openend under.  If they notice a change (because you forked a new process), a new handle will
       be opened in the new process.  This prevents a common problem seen in environments  like  mod_perl  where
       people would open a handle in the parent process and then run into trouble when they try to use it from a
       child process.

       Because  Ima::DBI  handles  keeping  database  connections  persistent and prevents problems with handles
       openend before forking, it is not necessary to use Apache::DBI when using Ima::DBI.   However,  there  is
       one  feature  of  Apache::DBI  which  you  will need in a mod_perl or FastCGI environment, and that's the
       automatic rollback it does at the end of each request.  This rollback provides safety  from  transactions
       left hanging when some perl code dies -- a serious problem which could grind your database to a halt with
       stale locks.

       To  replace  this feature on your own under mod_perl, you can add something like this in a handler at any
       phase of the request:

          $r->push_handlers(PerlCleanupHandler => sub {
              MyImaDBI->rollback();
          });

       Here "MyImaDBI" is your subclass of Ima::DBI.  You could also make this into an actual module and set the
       PerlCleanupHandler from your httpd.conf.  A similar approach should work in any long-running  environment
       which has a hook for running some code at the end of each request.

TODO, Caveat, BUGS, etc....

       I seriously doubt that it's thread safe.
           You  can bet cupcackes to sno-cones that much havoc will be wrought if Ima::DBI is used in a threaded
           Perl.

       Should make use of private_* handle method to store information
       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.

       Need to add debugging hooks.
           The  thing  which immediately comes to mind is a Verbose flag to print out SQL statements as they are
           made as well as mention when database connections are made, etc...

MAINTAINERS

       Tony Bowden <tony@tmtm.com> and Perrin Harkins <perrin@elem.com>

ORIGINAL AUTHOR

       Michael G Schwern <schwern@pobox.com>

LICENSE

       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 many DBI questions and adding Taint, prepare_cached and connect_cached methods to
       DBI, simplifying this greatly!

       Arena Networks, for effectively paying for Mike to write most of this module.