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);
        
        # Object methods.
        $dbh = $obj->db_*;              # Where * is the name of the db connection.
        $sth = $obj->sql_*;             # Where * is the name of the sql statement.
        
        $rc = $obj->commit;             #UNIMPLEMENTED
        $rc = $obj->commit(@db_names);  #UNIMPLEMENTED
        
        $rc = $obj->rollback;            #UNIMPLEMENTED
        $rc = $obj->rollback(@db_names); #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
        
        $obj->DBIwarn;
        
    $dbh->clear_cache;  #UNIMPLEMENTED
        
        # Modified statement handle methods.
        $rv = $sth->execute;
        $rv = $sth->execute(@bind_values);
        $rv = $sth->execute(\@bind_values, \@bind_cols);

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

    $sth->clear_cache;  #UNIMPLEMENTED

DESCRIPTION

Ima::DBI attempts to organize and facilitate caching and more efficient use of database connections and statement handles.

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.

  Using taint mode can prevent this problem, but DBI does not honor taint since all of its system calls are done inside XS code. So, Ima::DBI manually checks to see if a given SQL statement is tainted before passing it on to prepare.

• 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 automatic, 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.

• Sharing of database and sql information amongst inherited classes

  Any SQL 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.

• 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

• Overkill for programs that only use their SQL statements once

  Ima::DBI's caching will probably prove to be an unecessary performance hog if you never use the same SQL statement twice.

Michael G Schwern <schwern@pobox.com>

WHAT IS THIS?

This is Ima::DBI, a perl module. Please see the README that comes with this distribution.

HOW DO I INSTALL IT?

To install this module, cd to the directory that contains this README file and type the following:

   perl Makefile.PL
   make
   make test
   make install

To install this module into a specific directory, do: perl Makefile.PL PREFIX=/name/of/the/directory ...the rest is the same...

Please also read the perlmodinstall man page, if available.