Ima::DBI(3pm)

1Ima::DBI(3)           User Contributed Perl Documentation          Ima::DBI(3)
2
3
4

NAME

6       Ima::DBI - Database connection caching and organization
7

SYNOPSIS

9           package Foo;
10           use base 'Ima::DBI';
11
12           # Class-wide methods.
13           Foo->set_db($db_name, $data_source, $user, $password);
14           Foo->set_db($db_name, $data_source, $user, $password, \%attr);
15
16           my @database_names   = Foo->db_names;
17           my @database_handles = Foo->db_handles;
18
19           Foo->set_sql($sql_name, $statement, $db_name);
20           Foo->set_sql($sql_name, $statement, $db_name, $cache);
21
22           my @statement_names   = Foo->sql_names;
23
24           # Object methods.
25           $dbh = $obj->db_*;      # Where * is the name of the db connection.
26           $sth = $obj->sql_*;     # Where * is the name of the sql statement.
27           $sth = $obj->sql_*(@sql_pieces);
28
29           $obj->DBIwarn($what, $doing);
30
31           my $rc = $obj->commit;
32           my $rc = $obj->commit(@db_names);
33
34           my $rc = $obj->rollback;
35           my $rc = $obj->rollback(@db_names);
36

DESCRIPTION

38       Ima::DBI attempts to organize and facilitate caching and more efficient
39       use of database connections and statement handles by storing DBI and
40       SQL information with your class (instead of as seperate objects).  This
41       allows you to pass around just one object without worrying about a
42       trail of DBI handles behind it.
43
44       One of the things I always found annoying about writing large programs
45       with DBI was making sure that I didn't have duplicate database handles
46       open.  I was also annoyed by the somewhat wasteful nature of the
47       prepare/execute/finish route I'd tend to go through in my subroutines.
48       The new DBI->connect_cached and DBI->prepare_cached helped a lot, but I
49       still had to throw around global datasource, username and password
50       information.
51
52       So, after a while I grew a small library of DBI helper routines and
53       techniques.  Ima::DBI is the culmination of all this, put into a
54       nice(?), clean(?) class to be inherited from.
55
56   Why should I use this thing?
57       Ima::DBI is a little odd, and it's kinda hard to explain.  So lemme
58       explain why you'd want to use this thing...
59
60       ·   Consolidation of all SQL statements and database information
61
62           No matter what, embedding one language into another is messy.  DBI
63           alleviates this somewhat, but I've found a tendency to have that
64           scatter the SQL around inside the Perl code.  Ima::DBI allows you
65           to easily group the SQL statements in one place where they are
66           easier to maintain (especially if one developer is writing the SQL,
67           another writing the Perl).  Alternatively, you can place your SQL
68           statement alongside the code which uses it.  Whatever floats your
69           boat.
70
71           Database connection information (data source, username, password,
72           atrributes, etc...) can also be consolidated together and tracked.
73
74           Both the SQL and the connection info are probably going to change a
75           lot, so having them well organized and easy to find in the code is
76           a Big Help.
77
78       ·   Holds off opening a database connection until necessary.
79
80           While Ima::DBI is informed of all your database connections and SQL
81           statements at compile-time, it will not connect to the database
82           until you actually prepare a statement on that connection.
83
84           This is obviously very good for programs that sometimes never touch
85           the database.  It's also good for code that has lots of possible
86           connections and statements, but which typically only use a few.
87           Kinda like an autoloader.
88
89       ·   Easy integration of the DBI handles into your class
90
91           Ima::DBI causes each database handle to be associated with your
92           class, allowing you to pull handles from an instance of your
93           object, as well as making many oft-used DBI methods available
94           directly from your instance.
95
96           This gives you a cleaner OO design, since you can now just throw
97           around the object as usual and it will carry its associated DBI
98           baggage with it.
99
100       ·   Honors taint mode
101
102           It always struck me as a design deficiency that tainted SQL
103           statements could be passed to $sth->prepare().  For example:
104
105               # $user is from an untrusted source and is tainted.
106               $user = get_user_data_from_the_outside_world;
107               $sth = $dbh->prepare('DELETE FROM Users WHERE User = $user');
108
109           Looks innocent enough... but what if $user was the string "1 OR
110           User LIKE '%'".  You just blew away all your users. Hope you have
111           backups.
112
113           Ima::DBI turns on the DBI->connect Taint attribute so that all DBI
114           methods (except execute()) will no longer accept tainted data.  See
115           "Taint" in DBI for details.
116
117       ·   Taints returned data
118
119           Databases should be like any other system call.  It's the scary
120           Outside World, thus it should be tainted.  Simple.  Ima::DBI turns
121           on DBI's Taint attribute on each connection.  This feature is
122           overridable by passing your own Taint attribute to set_db as normal
123           for DBI.  See "Taint" in DBI for details.
124
125       ·   Encapsulation of some of the more repetitive bits of everyday DBI
126           usage
127
128           I get lazy a lot and I forget to do things I really should, like
129           using bind_cols(), or rigorous error checking.  Ima::DBI does some
130           of this stuff automatically, other times it just makes it more
131           convenient.
132
133       ·   Encapsulation of DBI's cache system
134
135           DBI's automatic handle caching system is relatively new, and some
136           people aren't aware of its use.  Ima::DBI uses it automatically, so
137           you don't have to worry about it. (It even makes it a bit more
138           efficient)
139
140       ·   Sharing of database and sql information amongst inherited classes
141
142           Any SQL statements and connections created by a class are available
143           to its children via normal method inheritance.
144
145       ·   Guarantees one connection per program.
146
147           One program, one database connection (per database user).  One
148           program, one prepared statement handle (per statement, per database
149           user).  That's what Ima::DBI enforces.  Extremely handy in
150           persistant environments (servers, daemons, mod_perl, FastCGI,
151           etc...)
152
153       ·   Encourages use of bind parameters and columns
154
155           Bind parameters are safer and more efficient than embedding the
156           column information straight into the SQL statement.  Bind columns
157           are more efficient than normal fetching.  Ima::DBI pretty much
158           requires the usage of the former, and eases the use of the latter.
159
160   Why shouldn't I use this thing.
161       ·   It's all about OO
162
163           Although it is possible to use Ima::DBI as a stand-alone module as
164           part of a function-oriented design, its generally not to be used
165           unless integrated into an object-oriented design.
166
167       ·   Overkill for small programs
168
169       ·   Overkill for programs with only one or two SQL statements
170
171           Its up to you whether the trouble of setting up a class and jumping
172           through the necessary Ima::DBI hoops is worth it for small
173           programs.  To me, it takes just as much time to set up an Ima::DBI
174           subclass as it would to access DBI without it... but then again I
175           wrote the module.  YMMV.
176
177       ·   Overkill for programs that only use their SQL statements once
178
179           Ima::DBI's caching might prove to be an unecessary performance hog
180           if you never use the same SQL statement twice.  Not sure, I haven't
181           looked into it.
182

USAGE

184       The basic steps to "DBIing" a class are:
185
186       1.  Inherit from Ima::DBI
187
188       2.  Set up and name all your database connections via set_db()
189
190       3.  Set up and name all your SQL statements via set_sql()
191
192       4.  Use sql_* to retrieve your statement handles ($sth) as needed and
193           db_* to retreive database handles ($dbh).
194
195       Have a look at EXAMPLE below.
196

TAINTING

198       Ima::DBI, by default, uses DBI's Taint flag on all connections.
199
200       This means that Ima::DBI methods do not accept tainted data, and that
201       all data fetched from the database will be tainted.  This may be
202       different from the DBI behavior you're used to.  See "Taint" in DBI for
203       details.
204

Class Methods

206   set_db
207           Foo->set_db($db_name, $data_source, $user, $password);
208           Foo->set_db($db_name, $data_source, $user, $password, \%attr);
209
210       This method is used in place of DBI->connect to create your database
211       handles. It sets up a new DBI database handle associated to $db_name.
212       All other arguments are passed through to DBI->connect_cached.
213
214       A new method is created for each db you setup.  This new method is
215       called "db_$db_name"... so, for example, Foo->set_db("foo", ...) will
216       create a method called "db_foo()". (Spaces in $db_name will be
217       translated into underscores: '_')
218
219       %attr is combined with a set of defaults (RaiseError => 1, AutoCommit
220       => 0, PrintError => 0, Taint => 1).  This is a better default IMHO,
221       however it does give databases without transactions (such as MySQL when
222       used with the default MyISAM table type) a hard time.  Be sure to turn
223       AutoCommit back on if your database does not support transactions.
224
225       The actual database handle creation (and thus the database connection)
226       is held off until a prepare is attempted with this handle.
227
228   set_sql
229           Foo->set_sql($sql_name, $statement, $db_name);
230           Foo->set_sql($sql_name, $statement, $db_name, $cache);
231
232       This method is used in place of DBI->prepare to create your statement
233       handles. It sets up a new statement handle associated to $sql_name
234       using the database connection associated with $db_name.  $statement is
235       passed through to either DBI->prepare or DBI->prepare_cached (depending
236       on $cache) to create the statement handle.
237
238       If $cache is true or isn't given, then prepare_cached() will be used to
239       prepare the statement handle and it will be cached.  If $cache is false
240       then a normal prepare() will be used and the statement handle will be
241       recompiled on every sql_*() call.  If you have a statement which
242       changes a lot or is used very infrequently you might not want it
243       cached.
244
245       A new method is created for each statement you set up.  This new method
246       is "sql_$sql_name"... so, as with set_db(), Foo->set_sql("bar", ...,
247       "foo"); will create a method called "sql_bar()" which uses the database
248       connection from "db_foo()". Again, spaces in $sql_name will be
249       translated into underscores ('_').
250
251       The actual statement handle creation is held off until sql_* is first
252       called on this name.
253
254   transform_sql
255       To make up for the limitations of bind parameters, $statement can
256       contain sprintf() style formatting (ie. %s and such) to allow
257       dynamically generated SQL statements (so to get a real percent sign,
258       use '%%').
259
260       The translation of the SQL happens in transform_sql(), which can be
261       overridden to do more complex transformations. See Class::DBI for an
262       example.
263
264   db_names / db_handles
265         my @database_names   = Foo->db_names;
266         my @database_handles = Foo->db_handles;
267         my @database_handles = Foo->db_handles(@db_names);
268
269       Returns a list of the database handles set up for this class using
270       set_db().  This includes all inherited handles.
271
272       db_names() simply returns the name of the handle, from which it is
273       possible to access it by converting it to a method name and calling
274       that db method...
275
276           my @db_names = Foo->db_names;
277           my $db_meth = 'db_'.$db_names[0];
278           my $dbh = $foo->$db_meth;
279
280       Icky, eh?  Fortunately, db_handles() does this for you and returns a
281       list of database handles in the same order as db_names().  Use this
282       sparingly as it will connect you to the database if you weren't already
283       connected.
284
285       If given @db_names, db_handles() will return only the handles for those
286       connections.
287
288       These both work as either class or object methods.
289
290   sql_names
291         my @statement_names   = Foo->sql_names;
292
293       Similar to db_names() this returns the names of all SQL statements set
294       up for this class using set_sql(), inherited or otherwise.
295
296       There is no corresponding sql_handles() because we can't know what
297       arguments to pass in.
298

Object Methods

300   db_*
301           $dbh = $obj->db_*;
302
303       This is how you directly access a database handle you set up with
304       set_db.
305
306       The actual particular method name is derived from what you told set_db.
307
308       db_* will handle all the issues of making sure you're already connected
309       to the database.
310
311   sql_*
312           $sth = $obj->sql_*;
313           $sth = $obj->sql_*(@sql_pieces);
314
315       sql_*() is a catch-all name for the methods you set up with set_sql().
316       For instance, if you did:
317
318           Foo->set_sql('GetAllFoo', 'Select * From Foo', 'SomeDb');
319
320       you'd run that statement with sql_GetAllFoo().
321
322       sql_* will handle all the issues of making sure the database is already
323       connected, and the statement handle is prepared.  It returns a prepared
324       statement handle for you to use.  (You're expected to execute() it)
325
326       If sql_*() is given a list of @sql_pieces it will use them to fill in
327       your statement, assuming you have sprintf() formatting tags in your
328       statement.  For example:
329
330           Foo->set_sql('GetTable', 'Select * From %s', 'Things');
331
332           # Assuming we have created an object... this will prepare the
333           # statement 'Select * From Bar'
334           $sth = $obj->sql_Search('Bar');
335
336       Be very careful with what you feed this function.  It cannot do any
337       quoting or escaping for you, so it is totally up to you to take care of
338       that.  Fortunately if you have tainting on you will be spared the
339       worst.
340
341       It is recommended you only use this in cases where bind parameters will
342       not work.
343
344   DBIwarn
345           $obj->DBIwarn($what, $doing);
346
347       Produces a useful error for exceptions with DBI.
348
349       I'm not particularly happy with this interface
350
351       Most useful like this:
352
353           eval {
354               $self->sql_Something->execute($self->{ID}, @stuff);
355           };
356           if($@) {
357               $self->DBIwarn($self->{ID}, 'Something');
358                       return;
359           }
360

Modified database handle methods

362       Ima::DBI makes some of the methods available to your object that are
363       normally only available via the database handle.  In addition, it
364       spices up the API a bit.
365
366   commit
367           $rc = $obj->commit;
368           $rc = $obj->commit(@db_names);
369
370       Derived from $dbh->commit() and basically does the same thing.
371
372       If called with no arguments, it causes commit() to be called on all
373       database handles associated with $obj.  Otherwise it commits all
374       database handles whose names are listed in @db_names.
375
376       Alternatively, you may like to do:  $rc = $obj->db_Name->commit;
377
378       If all the commits succeeded it returns true, false otherwise.
379
380   rollback
381           $rc = $obj->rollback;
382           $rc = $obj->rollback(@db_names);
383
384       Derived from $dbh->rollback, this acts just like Ima::DBI->commit,
385       except that it calls rollback().
386
387       Alternatively, you may like to do:  $rc = $obj->db_Name->rollback;
388
389       If all the rollbacks succeeded it returns true, false otherwise.
390

EXAMPLE

392           package Foo;
393           use base qw(Ima::DBI);
394
395           # Set up database connections (but don't connect yet)
396           Foo->set_db('Users', 'dbi:Oracle:Foo', 'admin', 'passwd');
397           Foo->set_db('Customers', 'dbi:Oracle:Foo', 'Staff', 'passwd');
398
399           # Set up SQL statements to be used through out the program.
400           Foo->set_sql('FindUser', <<"SQL", 'Users');
401               SELECT  *
402               FROM    Users
403               WHERE   Name LIKE ?
404           SQL
405
406           Foo->set_sql('ChangeLanguage', <<"SQL", 'Customers');
407               UPDATE  Customers
408               SET     Language = ?
409               WHERE   Country = ?
410           SQL
411
412           # rest of the class as usual.
413
414           package main;
415
416           $obj = Foo->new;
417
418           eval {
419               # Does connect & prepare
420               my $sth = $obj->sql_FindUser;
421               # bind_params, execute & bind_columns
422               $sth->execute(['Likmi%'], [\($name)]);
423               while( $sth->fetch ) {
424                   print $name;
425               }
426
427               # Uses cached database and statement handles
428               $sth = $obj->sql_FindUser;
429               # bind_params & execute.
430               $sth->execute('%Hock');
431               @names = $sth->fetchall;
432
433               # connects, prepares
434               $rows_altered = $obj->sql_ChangeLanguage->execute(qw(es_MX mx));
435           };
436           unless ($@) {
437               # Everything went okay, commit the changes to the customers.
438               $obj->commit('Customers');
439           }
440           else {
441               $obj->rollback('Customers');
442               warn "DBI failure:  $@";
443           }
444

USE WITH MOD_PERL, FASTCGI, ETC.

446       To help with use in forking environments, Ima::DBI database handles
447       keep track of the PID of the process they were openend under.  If they
448       notice a change (because you forked a new process), a new handle will
449       be opened in the new process.  This prevents a common problem seen in
450       environments like mod_perl where people would open a handle in the
451       parent process and then run into trouble when they try to use it from a
452       child process.
453
454       Because Ima::DBI handles keeping database connections persistent and
455       prevents problems with handles openend before forking, it is not
456       necessary to use Apache::DBI when using Ima::DBI.  However, there is
457       one feature of Apache::DBI which you will need in a mod_perl or FastCGI
458       environment, and that's the automatic rollback it does at the end of
459       each request.  This rollback provides safety from transactions left
460       hanging when some perl code dies -- a serious problem which could grind
461       your database to a halt with stale locks.
462
463       To replace this feature on your own under mod_perl, you can add
464       something like this in a handler at any phase of the request:
465
466          $r->push_handlers(PerlCleanupHandler => sub {
467              MyImaDBI->rollback();
468          });
469
470       Here "MyImaDBI" is your subclass of Ima::DBI.  You could also make this
471       into an actual module and set the PerlCleanupHandler from your
472       httpd.conf.  A similar approach should work in any long-running
473       environment which has a hook for running some code at the end of each
474       request.
475

TODO, Caveat, BUGS, etc....

477       I seriously doubt that it's thread safe.
478           You can bet cupcackes to sno-cones that much havoc will be wrought
479           if Ima::DBI is used in a threaded Perl.
480
481       Should make use of private_* handle method to store information
482       The docs stink.
483           The docs were originally written when I didn't have a good handle
484           on the module and how it will be used in practical cases.  I need
485           to rewrite the docs from the ground up.
486
487       Need to add debugging hooks.
488           The thing which immediately comes to mind is a Verbose flag to
489           print out SQL statements as they are made as well as mention when
490           database connections are made, etc...
491

MAINTAINERS

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

ORIGINAL AUTHOR

496       Michael G Schwern <schwern@pobox.com>
497

LICENSE

499       This module is free software.  You may distribute under the same terms
500       as Perl itself.  IT COMES WITHOUT WARRANTY OF ANY KIND.
501

THANKS MUCHLY

503       Tim Bunce, for enduring many DBI questions and adding Taint,
504       prepare_cached and connect_cached methods to DBI, simplifying this
505       greatly!
506
507       Arena Networks, for effectively paying for Mike to write most of this
508       module.
509