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