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