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 pre‐
47       pare/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
58       Ima::DBI is a little odd, and it's kinda hard to explain.  So lemme
59       explain why you'd want to use this thing...
60
61       * Consolidation of all SQL statements and database information
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 eas‐
66           ier 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           While Ima::DBI is informed of all your database connections and SQL
80           statements at compile-time, it will not connect to the database
81           until you actually prepare a statement on that connection.
82
83           This is obviously very good for programs that sometimes never touch
84           the database.  It's also good for code that has lots of possible
85           connections and statements, but which typically only use a few.
86           Kinda like an autoloader.
87
88       * Easy integration of the DBI handles into your class
89           Ima::DBI causes each database handle to be associated with your
90           class, allowing you to pull handles from an instance of your
91           object, as well as making many oft-used DBI methods available
92           directly from your instance.
93
94           This gives you a cleaner OO design, since you can now just throw
95           around the object as usual and it will carry its associated DBI
96           baggage with it.
97
98       * Honors taint mode
99           It always struck me as a design deficiency that tainted SQL state‐
100           ments could be passed to $sth->prepare().  For example:
101
102               # $user is from an untrusted source and is tainted.
103               $user = get_user_data_from_the_outside_world;
104               $sth = $dbh->prepare('DELETE FROM Users WHERE User = $user');
105
106           Looks innocent enough... but what if $user was the string "1 OR
107           User LIKE '%'".  You just blew away all your users. Hope you have
108           backups.
109
110           Ima::DBI turns on the DBI->connect Taint attribute so that all DBI
111           methods (except execute()) will no longer accept tainted data.  See
112           "Taint" in DBI for details.
113
114       * Taints returned data
115           Databases should be like any other system call.  It's the scary
116           Outside World, thus it should be tainted.  Simple.  Ima::DBI turns
117           on DBI's Taint attribute on each connection.  This feature is over‐
118           ridable by passing your own Taint attribute to set_db as normal for
119           DBI.  See "Taint" in DBI for details.
120
121       * Encapsulation of some of the more repetitive bits of everyday DBI
122       usage
123           I get lazy a lot and I forget to do things I really should, like
124           using bind_cols(), or rigorous error checking.  Ima::DBI does some
125           of this stuff automatically, other times it just makes it more con‐
126           venient.
127
128       * Encapsulation of DBI's cache system
129           DBI's automatic handle caching system is relatively new, and some
130           people aren't aware of its use.  Ima::DBI uses it automatically, so
131           you don't have to worry about it. (It even makes it a bit more
132           efficient)
133
134       * Sharing of database and sql information amongst inherited classes
135           Any SQL statements and connections created by a class are available
136           to its children via normal method inheritance.
137
138       * Guarantees one connection per program.
139           One program, one database connection (per database user).  One pro‐
140           gram, one prepared statement handle (per statement, per database
141           user).  That's what Ima::DBI enforces.  Extremely handy in persis‐
142           tant environments (servers, daemons, mod_perl, FastCGI, etc...)
143
144       * Encourages use of bind parameters and columns
145           Bind parameters are safer and more efficient than embedding the
146           column information straight into the SQL statement.  Bind columns
147           are more efficient than normal fetching.  Ima::DBI pretty much
148           requires the usage of the former, and eases the use of the latter.
149
150       Why shouldn't I use this thing.
151
152       * It's all about OO
153           Although it is possible to use Ima::DBI as a stand-alone module as
154           part of a function-oriented design, its generally not to be used
155           unless integrated into an object-oriented design.
156
157       * Overkill for small programs
158       * Overkill for programs with only one or two SQL statements
159           Its up to you whether the trouble of setting up a class and jumping
160           through the necessary Ima::DBI hoops is worth it for small pro‐
161           grams.  To me, it takes just as much time to set up an Ima::DBI
162           subclass as it would to access DBI without it... but then again I
163           wrote the module.  YMMV.
164
165       * Overkill for programs that only use their SQL statements once
166           Ima::DBI's caching might prove to be an unecessary performance hog
167           if you never use the same SQL statement twice.  Not sure, I haven't
168           looked into it.
169

USAGE

171       The basic steps to "DBIing" a class are:
172
173       1   Inherit from Ima::DBI
174
175       2   Set up and name all your database connections via set_db()
176
177       3   Set up and name all your SQL statements via set_sql()
178
179       4   Use sql_* to retrieve your statement handles ($sth) as needed and
180           db_* to retreive database handles ($dbh).
181
182       Have a look at EXAMPLE below.
183

TAINTING

185       Ima::DBI, by default, uses DBI's Taint flag on all connections.
186
187       This means that Ima::DBI methods do not accept tainted data, and that
188       all data fetched from the database will be tainted.  This may be dif‐
189       ferent from the DBI behavior you're used to.  See "Taint" in DBI for
190       details.
191

Class Methods

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

Object Methods

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

Modified database handle methods

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

EXAMPLE

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

TODO, Caveat, BUGS, etc....

442       I seriously doubt that it's thread safe.
443           You can bet cupcackes to sno-cones that much havoc will be wrought
444           if Ima::DBI is used in a threaded Perl.
445
446       Should make use of private_* handle method to store information
447       The docs stink.
448           The docs were originally written when I didn't have a good handle
449           on the module and how it will be used in practical cases.  I need
450           to rewrite the docs from the ground up.
451
452       Need to add debugging hooks.
453           The thing which immediately comes to mind is a Verbose flag to
454           print out SQL statements as they are made as well as mention when
455           database connections are made, etc...
456

MAINTAINER

458       Tony Bowden <tony@tmtm.com>
459

ORIGINAL AUTHOR

461       Michael G Schwern <schwern@pobox.com>
462

LICENSE

464       This module is free software.  You may distribute under the same terms
465       as Perl itself.  IT COMES WITHOUT WARRANTY OF ANY KIND.
466

THANKS MUCHLY

468       Tim Bunce, for enduring many DBI questions and adding Taint, pre‐
469       pare_cached and connect_cached methods to DBI, simplifying this
470       greatly!
471
472       Arena Networks, for effectively paying for Mike to write most of this
473       module.
474