1Ima::DBI(3) User Contributed Perl Documentation Ima::DBI(3)
2
3
4
6 Ima::DBI - Database connection caching and organization
7
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
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
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
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
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
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
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
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
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
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
493 Tony Bowden <tony@tmtm.com> and Perrin Harkins <perrin@elem.com>
494
496 Michael G Schwern <schwern@pobox.com>
497
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
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
511 DBI.
512
513 You may also choose to check out Class::DBI which hides most of this
514 from view.
515
516
517
518perl v5.34.0 2022-01-21 Ima::DBI(3)