1NAME
2 Ima::DBI - Database connection caching and organization
3
4SYNOPSIS
5 package Foo;
6 use base 'Ima::DBI';
7
8 # Class-wide methods.
9 Foo->set_db($db_name, $data_source, $user, $password);
10 Foo->set_db($db_name, $data_source, $user, $password, \%attr);
11
12 my @database_names = Foo->db_names;
13 my @database_handles = Foo->db_handles;
14
15 Foo->set_sql($sql_name, $statement, $db_name);
16 Foo->set_sql($sql_name, $statement, $db_name, $cache);
17
18 my @statement_names = Foo->sql_names;
19
20 # Object methods.
21 $dbh = $obj->db_*; # Where * is the name of the db connection.
22 $sth = $obj->sql_*; # Where * is the name of the sql statement.
23 $sth = $obj->sql_*(@sql_pieces);
24
25 $obj->DBIwarn($what, $doing);
26
27 my $rc = $obj->commit;
28 my $rc = $obj->commit(@db_names);
29
30 my $rc = $obj->rollback;
31 my $rc = $obj->rollback(@db_names);
32
33DESCRIPTION
34 Ima::DBI attempts to organize and facilitate caching and more efficient
35 use of database connections and statement handles by storing DBI and SQL
36 information with your class (instead of as seperate objects). This
37 allows you to pass around just one object without worrying about a trail
38 of DBI handles behind it.
39
40 One of the things I always found annoying about writing large programs
41 with DBI was making sure that I didn't have duplicate database handles
42 open. I was also annoyed by the somewhat wasteful nature of the
43 prepare/execute/finish route I'd tend to go through in my subroutines.
44 The new DBI->connect_cached and DBI->prepare_cached helped a lot, but I
45 still had to throw around global datasource, username and password
46 information.
47
48 So, after a while I grew a small library of DBI helper routines and
49 techniques. Ima::DBI is the culmination of all this, put into a nice(?),
50 clean(?) class to be inherited from.
51
52 Why should I use this thing?
53 Ima::DBI is a little odd, and it's kinda hard to explain. So lemme
54 explain why you'd want to use this thing...
55
56 * Consolidation of all SQL statements and database information
57 No matter what, embedding one language into another is messy. DBI
58 alleviates this somewhat, but I've found a tendency to have that
59 scatter the SQL around inside the Perl code. Ima::DBI allows you to
60 easily group the SQL statements in one place where they are easier
61 to maintain (especially if one developer is writing the SQL, another
62 writing the Perl). Alternatively, you can place your SQL statement
63 alongside the code which uses it. Whatever floats your boat.
64
65 Database connection information (data source, username, password,
66 atrributes, etc...) can also be consolidated together and tracked.
67
68 Both the SQL and the connection info are probably going to change a
69 lot, so having them well organized and easy to find in the code is a
70 Big Help.
71
72 * Holds off opening a database connection until necessary.
73 While Ima::DBI is informed of all your database connections and SQL
74 statements at compile-time, it will not connect to the database
75 until you actually prepare a statement on that connection.
76
77 This is obviously very good for programs that sometimes never touch
78 the database. It's also good for code that has lots of possible
79 connections and statements, but which typically only use a few.
80 Kinda like an autoloader.
81
82 * Easy integration of the DBI handles into your class
83 Ima::DBI causes each database handle to be associated with your
84 class, allowing you to pull handles from an instance of your object,
85 as well as making many oft-used DBI methods available directly from
86 your instance.
87
88 This gives you a cleaner OO design, since you can now just throw
89 around the object as usual and it will carry its associated DBI
90 baggage with it.
91
92 * Honors taint mode
93 It always struck me as a design deficiency that tainted SQL
94 statements could be passed to $sth->prepare(). For example:
95
96 # $user is from an untrusted source and is tainted.
97 $user = get_user_data_from_the_outside_world;
98 $sth = $dbh->prepare('DELETE FROM Users WHERE User = $user');
99
100 Looks innocent enough... but what if $user was the string "1 OR User
101 LIKE '%'". You just blew away all your users. Hope you have backups.
102
103 Ima::DBI turns on the DBI->connect Taint attribute so that all DBI
104 methods (except execute()) will no longer accept tainted data. See
105 "Taint" in DBI for details.
106
107 * Taints returned data
108 Databases should be like any other system call. It's the scary
109 Outside World, thus it should be tainted. Simple. Ima::DBI turns on
110 DBI's Taint attribute on each connection. This feature is
111 overridable by passing your own Taint attribute to set_db as normal
112 for DBI. See "Taint" in DBI for details.
113
114 * Encapsulation of some of the more repetitive bits of everyday DBI
115 usage
116 I get lazy a lot and I forget to do things I really should, like
117 using bind_cols(), or rigorous error checking. Ima::DBI does some of
118 this stuff automatically, other times it just makes it more
119 convenient.
120
121 * Encapsulation of DBI's cache system
122 DBI's automatic handle caching system is relatively new, and some
123 people aren't aware of its use. Ima::DBI uses it automatically, so
124 you don't have to worry about it. (It even makes it a bit more
125 efficient)
126
127 * Sharing of database and sql information amongst inherited classes
128 Any SQL statements and connections created by a class are available
129 to its children via normal method inheritance.
130
131 * Guarantees one connection per program.
132 One program, one database connection (per database user). One
133 program, one prepared statement handle (per statement, per database
134 user). That's what Ima::DBI enforces. Extremely handy in persistant
135 environments (servers, daemons, mod_perl, FastCGI, etc...)
136
137 * Encourages use of bind parameters and columns
138 Bind parameters are safer and more efficient than embedding the
139 column information straight into the SQL statement. Bind columns are
140 more efficient than normal fetching. Ima::DBI pretty much requires
141 the usage of the former, and eases the use of the latter.
142
143 Why shouldn't I use this thing.
144 * It's all about OO
145 Although it is possible to use Ima::DBI as a stand-alone module as
146 part of a function-oriented design, its generally not to be used
147 unless integrated into an object-oriented design.
148
149 * Overkill for small programs
150 * Overkill for programs with only one or two SQL statements
151 Its up to you whether the trouble of setting up a class and jumping
152 through the necessary Ima::DBI hoops is worth it for small programs.
153 To me, it takes just as much time to set up an Ima::DBI subclass as
154 it would to access DBI without it... but then again I wrote the
155 module. YMMV.
156
157 * Overkill for programs that only use their SQL statements once
158 Ima::DBI's caching might prove to be an unecessary performance hog
159 if you never use the same SQL statement twice. Not sure, I haven't
160 looked into it.
161
162USAGE
163 The basic steps to "DBIing" a class are:
164
165 1 Inherit from Ima::DBI
166
167 2 Set up and name all your database connections via set_db()
168
169 3 Set up and name all your SQL statements via set_sql()
170
171 4 Use sql_* to retrieve your statement handles ($sth) as needed and
172 db_* to retreive database handles ($dbh).
173
174 Have a look at EXAMPLE below.
175
176TAINTING
177 Ima::DBI, by default, uses DBI's Taint flag on all connections.
178
179 This means that Ima::DBI methods do not accept tainted data, and that
180 all data fetched from the database will be tainted. This may be
181 different from the DBI behavior you're used to. See "Taint" in DBI for
182 details.
183
184Class Methods
185 set_db
186 Foo->set_db($db_name, $data_source, $user, $password);
187 Foo->set_db($db_name, $data_source, $user, $password, \%attr);
188
189 This method is used in place of DBI->connect to create your database
190 handles. It sets up a new DBI database handle associated to $db_name.
191 All other arguments are passed through to DBI->connect_cached.
192
193 A new method is created for each db you setup. This new method is called
194 "db_$db_name"... so, for example, Foo->set_db("foo", ...) will create a
195 method called "db_foo()". (Spaces in $db_name will be translated into
196 underscores: '_')
197
198 %attr is combined with a set of defaults (RaiseError => 1, AutoCommit =>
199 0, PrintError => 0, Taint => 1). This is a better default IMHO, however
200 it does give databases without transactions (such as MySQL when used
201 with the default MyISAM table type) a hard time. Be sure to turn
202 AutoCommit back on if your database does not support transactions.
203
204 The actual database handle creation (and thus the database connection)
205 is held off until a prepare is attempted with this handle.
206
207 set_sql
208 Foo->set_sql($sql_name, $statement, $db_name);
209 Foo->set_sql($sql_name, $statement, $db_name, $cache);
210
211 This method is used in place of DBI->prepare to create your statement
212 handles. It sets up a new statement handle associated to $sql_name using
213 the database connection associated with $db_name. $statement is passed
214 through to either DBI->prepare or DBI->prepare_cached (depending on
215 $cache) to create the statement handle.
216
217 If $cache is true or isn't given, then prepare_cached() will be used to
218 prepare the statement handle and it will be cached. If $cache is false
219 then a normal prepare() will be used and the statement handle will be
220 recompiled on every sql_*() call. If you have a statement which changes
221 a lot or is used very infrequently you might not want it cached.
222
223 A new method is created for each statement you set up. This new method
224 is "sql_$sql_name"... so, as with set_db(), Foo->set_sql("bar", ...,
225 "foo"); will create a method called "sql_bar()" which uses the database
226 connection from "db_foo()". Again, spaces in $sql_name will be
227 translated into underscores ('_').
228
229 The actual statement handle creation is held off until sql_* is first
230 called on this name.
231
232 transform_sql
233 To make up for the limitations of bind parameters, $statement can
234 contain sprintf() style formatting (ie. %s and such) to allow
235 dynamically generated SQL statements (so to get a real percent sign, use
236 '%%').
237
238 The translation of the SQL happens in transform_sql(), which can be
239 overridden to do more complex transformations. See Class::DBI for an
240 example.
241
242 db_names / db_handles
243 my @database_names = Foo->db_names;
244 my @database_handles = Foo->db_handles;
245 my @database_handles = Foo->db_handles(@db_names);
246
247 Returns a list of the database handles set up for this class using
248 set_db(). This includes all inherited handles.
249
250 db_names() simply returns the name of the handle, from which it is
251 possible to access it by converting it to a method name and calling that
252 db method...
253
254 my @db_names = Foo->db_names;
255 my $db_meth = 'db_'.$db_names[0];
256 my $dbh = $foo->$db_meth;
257
258 Icky, eh? Fortunately, db_handles() does this for you and returns a list
259 of database handles in the same order as db_names(). Use this sparingly
260 as it will connect you to the database if you weren't already connected.
261
262 If given @db_names, db_handles() will return only the handles for those
263 connections.
264
265 These both work as either class or object methods.
266
267 sql_names
268 my @statement_names = Foo->sql_names;
269
270 Similar to db_names() this returns the names of all SQL statements set
271 up for this class using set_sql(), inherited or otherwise.
272
273 There is no corresponding sql_handles() because we can't know what
274 arguments to pass in.
275
276Object Methods
277 db_*
278 $dbh = $obj->db_*;
279
280 This is how you directly access a database handle you set up with
281 set_db.
282
283 The actual particular method name is derived from what you told set_db.
284
285 db_* will handle all the issues of making sure you're already connected
286 to the database.
287
288 sql_*
289 $sth = $obj->sql_*;
290 $sth = $obj->sql_*(@sql_pieces);
291
292 sql_*() is a catch-all name for the methods you set up with set_sql().
293 For instance, if you did:
294
295 Foo->set_sql('GetAllFoo', 'Select * From Foo', 'SomeDb');
296
297 you'd run that statement with sql_GetAllFoo().
298
299 sql_* will handle all the issues of making sure the database is already
300 connected, and the statement handle is prepared. It returns a prepared
301 statement handle for you to use. (You're expected to execute() it)
302
303 If sql_*() is given a list of @sql_pieces it will use them to fill in
304 your statement, assuming you have sprintf() formatting tags in your
305 statement. For example:
306
307 Foo->set_sql('GetTable', 'Select * From %s', 'Things');
308
309 # Assuming we have created an object... this will prepare the
310 # statement 'Select * From Bar'
311 $sth = $obj->sql_Search('Bar');
312
313 Be very careful with what you feed this function. It cannot do any
314 quoting or escaping for you, so it is totally up to you to take care of
315 that. Fortunately if you have tainting on you will be spared the worst.
316
317 It is recommended you only use this in cases where bind parameters will
318 not work.
319
320 DBIwarn
321 $obj->DBIwarn($what, $doing);
322
323 Produces a useful error for exceptions with DBI.
324
325 I'm not particularly happy with this interface
326
327 Most useful like this:
328
329 eval {
330 $self->sql_Something->execute($self->{ID}, @stuff);
331 };
332 if($@) {
333 $self->DBIwarn($self->{ID}, 'Something');
334 return;
335 }
336
337Modified database handle methods
338 Ima::DBI makes some of the methods available to your object that are
339 normally only available via the database handle. In addition, it spices
340 up the API a bit.
341
342 commit
343 $rc = $obj->commit;
344 $rc = $obj->commit(@db_names);
345
346 Derived from $dbh->commit() and basically does the same thing.
347
348 If called with no arguments, it causes commit() to be called on all
349 database handles associated with $obj. Otherwise it commits all database
350 handles whose names are listed in @db_names.
351
352 Alternatively, you may like to do: $rc = $obj->db_Name->commit;
353
354 If all the commits succeeded it returns true, false otherwise.
355
356 rollback
357 $rc = $obj->rollback;
358 $rc = $obj->rollback(@db_names);
359
360 Derived from $dbh->rollback, this acts just like Ima::DBI->commit,
361 except that it calls rollback().
362
363 Alternatively, you may like to do: $rc = $obj->db_Name->rollback;
364
365 If all the rollbacks succeeded it returns true, false otherwise.
366
367EXAMPLE
368 package Foo;
369 use base qw(Ima::DBI);
370
371 # Set up database connections (but don't connect yet)
372 Foo->set_db('Users', 'dbi:Oracle:Foo', 'admin', 'passwd');
373 Foo->set_db('Customers', 'dbi:Oracle:Foo', 'Staff', 'passwd');
374
375 # Set up SQL statements to be used through out the program.
376 Foo->set_sql('FindUser', <<"SQL", 'Users');
377 SELECT *
378 FROM Users
379 WHERE Name LIKE ?
380 SQL
381
382 Foo->set_sql('ChangeLanguage', <<"SQL", 'Customers');
383 UPDATE Customers
384 SET Language = ?
385 WHERE Country = ?
386 SQL
387
388 # rest of the class as usual.
389
390 package main;
391
392 $obj = Foo->new;
393
394 eval {
395 # Does connect & prepare
396 my $sth = $obj->sql_FindUser;
397 # bind_params, execute & bind_columns
398 $sth->execute(['Likmi%'], [\($name)]);
399 while( $sth->fetch ) {
400 print $name;
401 }
402
403 # Uses cached database and statement handles
404 $sth = $obj->sql_FindUser;
405 # bind_params & execute.
406 $sth->execute('%Hock');
407 @names = $sth->fetchall;
408
409 # connects, prepares
410 $rows_altered = $obj->sql_ChangeLanguage->execute(qw(es_MX mx));
411 };
412 unless ($@) {
413 # Everything went okay, commit the changes to the customers.
414 $obj->commit('Customers');
415 }
416 else {
417 $obj->rollback('Customers');
418 warn "DBI failure: $@";
419 }
420
421USE WITH MOD_PERL, FASTCGI, ETC.
422 To help with use in forking environments, Ima::DBI database handles keep
423 track of the PID of the process they were openend under. If they notice
424 a change (because you forked a new process), a new handle will be opened
425 in the new process. This prevents a common problem seen in environments
426 like mod_perl where people would open a handle in the parent process and
427 then run into trouble when they try to use it from a child process.
428
429 Because Ima::DBI handles keeping database connections persistent and
430 prevents problems with handles openend before forking, it is not
431 necessary to use Apache::DBI when using Ima::DBI. However, there is one
432 feature of Apache::DBI which you will need in a mod_perl or FastCGI
433 environment, and that's the automatic rollback it does at the end of
434 each request. This rollback provides safety from transactions left
435 hanging when some perl code dies -- a serious problem which could grind
436 your database to a halt with stale locks.
437
438 To replace this feature on your own under mod_perl, you can add
439 something like this in a handler at any phase of the request:
440
441 $r->push_handlers(PerlCleanupHandler => sub {
442 MyImaDBI->rollback();
443 });
444
445 Here "MyImaDBI" is your subclass of Ima::DBI. You could also make this
446 into an actual module and set the PerlCleanupHandler from your
447 httpd.conf. A similar approach should work in any long-running
448 environment which has a hook for running some code at the end of each
449 request.
450
451TODO, Caveat, BUGS, etc....
452 I seriously doubt that it's thread safe.
453 You can bet cupcackes to sno-cones that much havoc will be wrought
454 if Ima::DBI is used in a threaded Perl.
455
456 Should make use of private_* handle method to store information
457 The docs stink.
458 The docs were originally written when I didn't have a good handle on
459 the module and how it will be used in practical cases. I need to
460 rewrite the docs from the ground up.
461
462 Need to add debugging hooks.
463 The thing which immediately comes to mind is a Verbose flag to print
464 out SQL statements as they are made as well as mention when database
465 connections are made, etc...
466
467MAINTAINERS
468 Tony Bowden <tony@tmtm.com> and Perrin Harkins <perrin@elem.com>
469
470ORIGINAL AUTHOR
471 Michael G Schwern <schwern@pobox.com>
472
473LICENSE
474 This module is free software. You may distribute under the same terms as
475 Perl itself. IT COMES WITHOUT WARRANTY OF ANY KIND.
476
477THANKS MUCHLY
478 Tim Bunce, for enduring many DBI questions and adding Taint,
479 prepare_cached and connect_cached methods to DBI, simplifying this
480 greatly!
481
482 Arena Networks, for effectively paying for Mike to write most of this
483 module.
484
485SEE ALSO
486 DBI.
487
488 You may also choose to check out Class::DBI which hides most of this
489 from view.
490
491