1=head1 NAME 2 3Ima::DBI - Database connection caching and organization 4 5 6=head1 SYNOPSIS 7 8 # Class-wide methods. 9 __PACKAGE__->set_db($db_name, $data_source, $user, $password); 10 __PACKAGE__->set_db($db_name, $data_source, $user, $password, \%attr); 11 12 __PACKAGE__->set_sql($sql_name, $statement, $db_name); 13 14 # Object methods. 15 $dbh = $obj->db_*; # Where * is the name of the db connection. 16 $sth = $obj->sql_*; # Where * is the name of the sql statement. 17 18 $rc = $obj->commit; #UNIMPLEMENTED 19 $rc = $obj->commit(@db_names); #UNIMPLEMENTED 20 21 $rc = $obj->rollback; #UNIMPLEMENTED 22 $rc = $obj->rollback(@db_names); #UNIMPLEMENTED 23 24 $obj->clear_db_cache; #UNIMPLEMENTED 25 $obj->clear_db_cache(@db_names); #UNIMPLEMENTED 26 27 $obj->clear_sql_cache; #UNIMPLMENTED 28 $obj->clear_sql_cache(@sql_names); #UNIMPLMENTED 29 30 $obj->DBIwarn; 31 32 $dbh->clear_cache; #UNIMPLEMENTED 33 34 # Modified statement handle methods. 35 $rv = $sth->execute; 36 $rv = $sth->execute(@bind_values); 37 $rv = $sth->execute(\@bind_values, \@bind_cols); 38 39 $row_ref = $sth->fetch; 40 @row = $sth->fetch; 41 42 $row_ref = $sth->fetch_hash; 43 %row = $sth->fetch_hash; 44 45 $rows_ref = $sth->fetchall; 46 @rows = $sth->fetchall; 47 48 $rows_ref = $sth->fetchall_hash; 49 @tbl = $sth->fetchall_hash; 50 51 $sth->clear_cache; #UNIMPLEMENTED 52 53=head1 DESCRIPTION 54 55Ima::DBI attempts to organize and facilitate caching and more efficient use of database connections and statement handles. 56 57One 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. 58 59So, 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. 60 61 62=head2 Why should I use this thing? 63 64Ima::DBI is a little odd, and it's kinda hard to explain. So lemme explain why you'd want to use this thing... 65 66=over 4 67 68=item * Consolidation of all SQL statements and database information 69 70No 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. 71 72Database connection information (data source, username, password, atrributes, etc...) can also be consolidated together and tracked. 73 74Both 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. 75 76=item * Holds off opening a database connection until necessary. 77 78While 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. 79 80This 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. 81 82=item * Easy integration of the DBI handles into your class 83 84Ima::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. 85 86This 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. 87 88=item * Honors taint mode 89 90It always struck me as a design deficiency that tainted SQL statements could be passed to $sth->prepare(). For example: 91 92 # $user is from an untrusted source and is tainted. 93 $user = get_user_data_from_the_outside_world; 94 $sth = $dbh->prepare('DELETE FROM Users WHERE User = $user'); 95 96Looks innocent enough... but what if $user was the string "1 OR User LIKE %". You just blew away all your users, hope you have backups. 97 98Using 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. 99 100=item * Encapsulation of some of the more repetative bits of everyday DBI usage 101 102I 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. 103 104=item * Encapsulation of DBI's cache system 105 106DBI'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. 107 108=item * Sharing of database and sql information amongst inherited classes 109 110Any SQL and connections created by a class is available to its children via normal method inheritance. 111 112=item * Convenience and orthoganality amongst statement handle methods 113 114It 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. 115 116=item * Guarantees one connection per program. 117 118One 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...) 119 120=item * Encourages use of bind parameters and columns 121 122Bind 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. 123 124 125=head2 Why shouldn't I use this thing. 126 127=item * It's all about OO 128 129Although 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. 130 131=item * Overkill for small programs 132 133=item * Overkill for programs with only one or two SQL statements 134 135=item * Overkill for programs that only use their SQL statements once 136 137Ima::DBI's caching will probably prove to be an unecessary performance hog if you never use the same SQL statement twice. 138 139=back 140=head1 AUTHOR 141 142Michael G Schwern <schwern@pobox.com> 143 144 145WHAT IS THIS? 146 147This is Ima::DBI, a perl module. Please see the README that comes with 148this distribution. 149 150HOW DO I INSTALL IT? 151 152To install this module, cd to the directory that contains this README 153file and type the following: 154 155 perl Makefile.PL 156 make 157 make test 158 make install 159 160To install this module into a specific directory, do: 161 perl Makefile.PL PREFIX=/name/of/the/directory 162 ...the rest is the same... 163 164Please also read the perlmodinstall man page, if available. 165 166