blib/lib/DBIx/PhraseBook.pm

filter: Statementstmt 88.8% 16 / 18 Branchbran n/a Conditioncond n/a MC/DCmcdc n/a Subroutinesub 100% 6 / 6 Podpod n/a totaltotal 91.6% 22 / 24 CC 1 SCAR 0.5
CC
1
Cov
89%
BEGIN
1.3
BEGIN
1.0
BEGIN
1.0
CRAP 1.0
↑ Summary
1 package DBIx::PhraseBook;
2  
3 1 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
4 1 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
5  
6 =pod
7  
8 =head1 NAME
9  
10 DBIx::PhraseBook - provides phrasebooked database queries, allowing client code to avoid embedding
11 sql and direct dbi calls. supports named bind parameters only if the underlying DBD driver does.
12  
13 =head1 WARNING
14  
15 always returns uppercased key names and resultset is a structure as returned by fetchrow_hashref
16 (see DBI documentation).
17  
18 =head1 IMPLEMENTATION
19  
20 =over
21  
22 =item
23  
24 my %phraseBooks = DBIx::PhraseBook->load( $prefix, $propsfile );
25  
26 loads phrasebooks defined in a properties file, returning a hash keyed on phrasebook name.
27  
28 example props file:
29  
30 test.hosts.db.dsn=dbi:mysql:hostname=127.0.0.1;debug=1;port=1367;database=hosts
31 test.hosts.db.username=testuser
32 test.hosts.db.password=passwordstring
33 test.hosts.db.phrasebooks.1.name=hosts
34 test.hosts.db.phrasebooks.1.path=/fullpath/to/phrasebooks/hosts.xml
35 test.hosts.db.phrasebooks.1.key=key1
36 test.hosts.db.phrasebooks.2.name=hosts
37 test.hosts.db.phrasebooks.2.path=/fullpath/to/phrasebooks/hosts.xml
38 test.hosts.db.phrasebooks.2.key=key1
39  
40 $prefix would be test.hosts.db for this properties file.
41  
42 =back
43  
44  
45 =over
46  
47 =item
48  
49 my $status = $phraseBooks{keyname}->execute($queryName,$inputArg,[outarg1=>$ref])
50  
51 =item
52  
53 my $status = $phraseBooks{keyname}->execute($queryName,{inarg1=>$i1,$inarg2=>$i2},{outarg1=>$ref1,outarg2=>$ref2})
54  
55 =item
56  
57 my $status = $phraseBooks{keyname}->execute($queryName,[$i1,$i2])
58  
59 =item
60  
61 my $hashref = $phraseBooks{keyname}->fetch($queryName,$inputArg,[outarg1=>$ref])
62  
63 =item
64  
65 my @data = $phraseBooks{keyname}->fetch($queryName,$inputArg,[outarg1=>$ref])
66  
67 =item
68  
69 my @data = $phraseBooks{keyname}->fetch($queryName,{inarg1=>$i1,$inarg2=>$i2},{outarg1=>$ref1,outarg2=>$ref2})
70  
71 C<execute> and C<fetch> are the main methods in this class that client code will use. both execute a
72 query retrieved from an xml file given a key, binding all necessary variables
73 along the way. C<fetch> returns an array or single row resultset according to
74 what the caller expects (uses C<wantarray>).
75  
76 C<execute> does not do a fetch from the statement
77 handle and only returns execute status.
78  
79 in array context, fetch will return an array containing hashrefs of all rows.
80 in scalar context, will return one ie the first row as a hashref.
81  
82 if query only has one input bind parameter,
83 and it is called ':id', or query has a single '?', then first form of each of
84 C<execute> and C<fetch> (above) can be used.
85  
86  
87 =item
88  
89 my @data = $phraseBooks{keyname}->fetchReport($queryName,$inputArg,[outarg1=>$ref])
90  
91 does the same as fetch, but prints a timings report to the logger.
92  
93 =item
94  
95 $phraseBooks{keyname}->prepare($queryName)
96  
97 prepares named query and returns a statement handle - is used by C<execute>/C<fetch> and by test scripts.
98 not normally invoked directly by user.
99  
100 =item
101  
102 $phraseBooks{keyname}->getAllQueryNames( )
103  
104 returns names of all queries in phrasebook. used by test scripts.
105  
106 =item
107  
108 $phraseBooks{keyname}->useDbh($database_handle)
109  
110 Force use of an existing handle
111  
112 =item
113  
114 $phraseBooks{keyname}->getDbh()
115  
116 Return existing handle
117  
118 =item
119  
120 $phraseBooks{keyname}->debugOn( )
121  
122 Switch DBMS Debugging on
123  
124 =item
125  
126 $phraseBooks{keyname}->debugOff( )
127  
128 Switch DBMS Debugging off
129  
130  
131 =head1 AUTHOR
132  
133 Mark Clements, February 2003
134  
135 =head1 BUGS
136  
137 is a relatively thin wrapper around Class::Phrasebook and DBI and is quite simplistic - have probably missed a few tricks but should be flexible enough to extend as necessary without too much trouble. possibly should be implemented as a singleton.
138  
139 C<getAllQueryNames> probably belongs in the Class::Phrasebook module - it's a bit messy
140 having xpath in this class that directly references the phrasebook xml file.
141  
142 =cut
143  
144 1 ▶use Benchmark::Timer;
Subroutine BEGIN: called CC=1 SCAR=0.0
145 1 ▶use Carp qw(confess cluck);
Subroutine BEGIN: called CC=1 SCAR=0.0
146 1 ▶use Carp::Assert;
Subroutine BEGIN: called CC=1 SCAR=0.0
147 0 ▶use Class::Phrasebook;
Subroutine BEGIN: called CC=1 SCAR=2.6
148 use Config::PropertiesSequence;
149 use Data::Dumper;
150 use DBI;
151 use Log::Log4perl;
152 use Storable qw(dclone);
153 use XML::XPath;
154  
155 our $logger;
156 our Class::Phrasebook $phraseBook;
157  
158 use constant DEFAULT_FETCH_KEYS => "NAME_uc";
159 use constant DEFAULTBINDPARAMETERNAME => 1;
160  
161 use constant MAINTIMINGS => 0;
162 use constant MILLISECONDFORMAT => "%.2fms ";
163 use constant CALLERLEVEL => 3;
164 use constant CALLERLINEFIELD => 2;
165 use constant CALLERSUBROUTINEFIELD => 3;
166  
167 our $keepTimer;
168 our $VERSION = sprintf "%d.%03d", q$Revision: 1.3 $ =~ /(\d+)/g;
169  
170 use fields qw(_phrasebook _dbh _keyname _timer);
171  
172 INIT {
173 DBI->trace(0);
174 my $log4perl = join "\n",<DATA>;
175  
176 Log::Log4perl::init(\$log4perl);
177 $logger = Log::Log4perl->get_logger("dbix.phrasebook");
178  
179 our Benchmark::Timer $timer;
180 sub _getAllQueriesXpath ($){
181 my $keyname = shift;
182 return q(/phrasebook/dictionary[@name=') . $keyname . q(']/phrase/@name);
183 }
184 }
185  
186 sub setLogger($$) {
187 my $class = shift;
188 $logger = shift;
189 }
190  
191 sub init($$$) {
192 my __PACKAGE__ $self = shift;
193 my $phraseBookPath = shift;
194 my $dictionaryKey = shift;
195 my $setdbh = shift;
196 $phraseBook = Class::Phrasebook->new( undef, $phraseBookPath );
197 $phraseBook->load($dictionaryKey);
198  
199 }
200  
201 sub new($$$) {
202 my $class = shift;
203 my $phraseBookPath = shift;
204 my $dictionaryKey = shift;
205  
206 my __PACKAGE__ $self = fields::new($class);
207 my Class::Phrasebook $phraseBook = Class::Phrasebook->new( undef, $phraseBookPath );
208 $phraseBook->load($dictionaryKey);
209 $self->{_phrasebook} = $phraseBook;
210 $self->{_keyname} = $dictionaryKey;
211 return $self;
212 }
213  
214 sub load($$$) {
215 my $class = shift;
216 my $prefix = shift;
217 my $propsFile = shift;
218  
219 assert( defined $prefix, "prefix not defined" );
220 assert( -r $propsFile, "can't read propsfile $propsFile - $!" );
221  
222 ## load up properties
223 my Config::PropertiesSequence $props = Config::PropertiesSequence->new();
224 $props->load( FileHandle->new( $propsFile, "r" ) );
225  
226 ## get db connection defs (if any)
227 my $defaultDBH;
228 {
229 my $dsn = $props->getProperty("$prefix.dsn");
230 my $username = $props->getProperty("$prefix.username");
231 my $password = $props->getProperty("$prefix.password");
232  
233 if ( defined $dsn ) {
234 $defaultDBH = _connect(
235 dsn => $dsn,
236 username => $username,
237 password => $password
238 );
239 }
240 }
241  
242 ## get phrasebook definitions
243 my @phraseBookDefs =
244 $props->getPropertySequence( "$prefix.phrasebooks", qw(path name key default) );
245 my %phraseBooks = ();
246  
247 ## load up phrasebooks
248 foreach my $phraseBookDef (@phraseBookDefs) {
249 my $phraseBookName = $phraseBookDef->{name};
250 my $phraseBookKey = $phraseBookDef->{key};
251 my $phraseBookPath = $phraseBookDef->{path};
252
253 $logger->info("load $phraseBookKey -> $phraseBookPath");
254 my $newPhraseBook = __PACKAGE__->new( $phraseBookPath, $phraseBookKey );
255 $newPhraseBook->setDBH($defaultDBH);
256 $phraseBooks{$phraseBookName} = $newPhraseBook;
257 }
258 return %phraseBooks;
259  
260 }
261  
262 sub _connect(@) {
263 my %settings = @_;
264 my $dbh = DBI->connect(
265 $settings{dsn},
266 $settings{username},
267 $settings{password},
268 {
269 AutoCommit => 1,
270 RaiseError => 1,
271 }
272 );
273 my $errstr = $DBI::errstr || "";
274 assert( defined $dbh, "failed to connect: $errstr" );
275  
276 return $dbh;
277 }
278  
279 sub setDBH($$) {
280 my __PACKAGE__ $self = shift;
281 my $dbh = shift;
282  
283 $self->{_dbh} = $dbh;
284 }
285  
286 sub useDbh ($$) {
287 my __PACKAGE__ $self = shift;
288 $self->{_dbh} = shift;
289 }
290  
291 sub getDbh ($) {
292 my __PACKAGE__ $self = shift;
293 return $self->{_dbh};
294 }
295  
296 sub getLogger($) {
297 my $class = shift;
298 my $newLogger = shift;
299  
300 $logger = $newLogger;
301 }
302  
303 sub prepare($$) {
304 my __PACKAGE__ $self = shift;
305 my $type = shift;
306  
307 ## retrieve query from phrasebook
308 my $phraseBook = $self->{_phrasebook};
309 my $query = $phraseBook->get($type);
310 $logger->info("query $type = $query");
311  
312 my $dbh = $self->{_dbh};
313 assert( defined $query, "attempted to get query for undefined query type $type" );
314  
315 ## prepare query and return statement handle
316 my $sth = $dbh->prepare($query);
317 assert( $sth, "failed to prepare query " . ( $dbh->errstr() || "(no error)" ) );
318  
319 return $sth;
320 }
321  
322 sub getAllQueryNames($) {
323 my __PACKAGE__ $self = shift;
324  
325 ## extract all query names from phrasebook
326 my XML::XPath $xpath = XML::XPath->new( filename => $self->{_phrasebook}->get_xml_path() );
327 assert( defined $xpath, "could not load xpath - $! " );
328 my $nodeset = $xpath->find(_getAllQueriesXpath($self->{_keyname}));
329  
330 ## store returned names in array and return
331 my @allQueries = ();
332 foreach my $node ( $nodeset->get_nodelist() ) {
333 my $queryName = $node->string_value();
334 push @allQueries, $queryName;
335 }
336 return @allQueries;
337  
338 }
339  
340 sub execute($$;$$) {
341 my __PACKAGE__ $self = shift;
342 my $type = shift;
343 my $inargs = shift;
344 my $outargs = shift;
345  
346 my $mainTimer;
347 if (MAINTIMINGS) {
348 $mainTimer = Benchmark::Timer->new();
349 $mainTimer->start("main timing");
350 }
351 my $debugArgs = $self->getDebugArgs( $inargs, $outargs );
352 my ( $result, $sth ) = $self->_executeQuery( $type, $inargs, $outargs );
353  
354 if ( !defined $sth ) {
355 $logger->warn( "failed => " . $self->getDebug( $type, $debugArgs ) );
356  
357 } else {
358 my $timing = "";
359 if (MAINTIMINGS) {
360 $mainTimer->stop("main timing");
361 $timing = $mainTimer->result("main timing");
362 $logger->info( $self->getDebug( $type, $debugArgs, $timing ) );
363 }
364  
365 # undef $timer;
366 }
367 return $result;
368  
369 }
370  
371 sub fetchReport($$;$$) {
372 my __PACKAGE__ $self = shift;
373 my $type = shift;
374 my $inargs = shift;
375 my $outargs = shift;
376 my $trials = shift || 1;
377  
378 my $timer = Benchmark::Timer->new();
379 $self->{_timer} = $timer;
380 $keepTimer = 1;
381 my @results = ();
382 for ( my $ii = 0 ; $ii < $trials ; $ii++ ) {
383 @results = $self->fetch( $type, $inargs, $outargs );
384 }
385 $self->report();
386  
387 if(wantarray){
388 return @results;
389 }else{
390 return $results[0];
391 }
392 }
393  
394 sub report($) {
395 my __PACKAGE__ $self = shift;
396 $self->{_timer}->report();
397 }
398  
399 sub fetch($$;$$) {
400 my __PACKAGE__ $self = shift;
401 my $type = shift;
402 my $inargs = shift;
403 my $outargs = shift;
404  
405 my $mainTimer;
406 if (MAINTIMINGS) {
407 $mainTimer = Benchmark::Timer->new();
408 $mainTimer->start("main timing");
409 }
410  
411 my $timer = $self->{_timer};
412 $timer->start("overall") if defined $timer;
413
414 my $debugArgs = "";
415  
416 $timer->start("build args") if defined $timer;
417 if ( $logger->is_info() ) {
418 $debugArgs = $self->getDebugArgs( $inargs, $outargs );
419 }
420 $timer->stop("build args") if defined $timer;
421  
422 $timer->start("main") if defined $timer;
423 my ( $result, $sth ) = $self->_executeQuery( $type, $inargs, $outargs );
424 $timer->stop("main") if defined $timer;
425  
426 ## fetch resultset from statement handle as necessary
427 my @out = ();
428  
429 my $resultSet;
430 if ( wantarray() ) {
431 $timer->start("multirow fetch") if defined $timer;
432 while ( my $row = $sth->fetchrow_hashref(DEFAULT_FETCH_KEYS) ) {
433 push @out, $row;
434 }
435 $timer->stop("multirow fetch") if defined $timer;
436 } else {
437 $timer->start("single row fetch") if defined $timer;
438 $resultSet = $sth->fetchrow_hashref(DEFAULT_FETCH_KEYS);
439 $timer->stop("single row fetch") if defined $timer;
440  
441 }
442 $timer->stop("overall") if defined $timer;
443
444 if (MAINTIMINGS) {
445 my $timing;
446 $mainTimer->stop("main timing");
447 $timing = $mainTimer->result("main timing");
448 $logger->info( $self->getDebug( $type, $debugArgs, $timing ) );
449 }
450  
451 if ( wantarray() ) {
452 return @out;
453 } else {
454 return $resultSet;
455 }
456 }
457  
458 sub getDebugArgs($$$) {
459 my __PACKAGE__ $self = shift;
460 my $inargs = shift;
461 my $outargs = shift;
462  
463 my $debugArgs = "";
464  
465 ## note use of dclone - Dumper does something weird to inargs and outargs...
466 local $Data::Dumper::Terse = 1;
467 if ( defined $inargs ) {
468 if ( ref $inargs ) {
469 $debugArgs = Dumper( dclone $inargs);
470 } else {
471 $debugArgs = $inargs;
472 }
473 }
474 if ( defined $outargs ) {
475 if ( ref $outargs ) {
476 $debugArgs .= ", " . Dumper( dclone $outargs);
477 } else {
478 $debugArgs .= ", " . $outargs;
479 }
480 }
481 $debugArgs =~ s/\n/ /g;
482 $debugArgs =~ s/ +/ /g;
483  
484 return $debugArgs;
485 }
486  
487 sub _executeQuery($$;$$) {
488 my __PACKAGE__ $self = shift;
489 my $type = shift;
490 my $inargs = shift;
491 my $outargs = shift;
492  
493 ## prepare query
494 my $timer = $self->{_timer};
495 $timer->start("prepare") if defined $timer;
496 my $sth = $self->prepare($type);
497 $timer->stop("prepare") if defined $timer;
498  
499 ## bind arguments accordingly
500 $timer->start("bind") if defined $timer;
501 eval {
502 if ( defined $inargs )
503 {
504 if ( my $reftype = ref $inargs ) {
505 if ( $reftype eq "HASH" ) {
506 while ( my ( $key, $value ) = each %{$inargs} ) {
507 $sth->bind_param( ":$key" => $value );
508 }
509 } elsif ( $reftype eq "ARRAY" ) {
510 for ( my $ii = 0 ; $ii < @$inargs ; $ii++ ) {
511 my $bindPos = $ii + 1;
512 $logger->info("bind $bindPos => $inargs->[$ii]");
513 $sth->bind_param( $bindPos, $inargs->[$ii] );
514 }
515 } else {
516 warn "unknown arg - $reftype";
517 }
518 } else {
519 $sth->bind_param( DEFAULTBINDPARAMETERNAME, $inargs );
520 }
521 }
522  
523 if ( ref $outargs ) {
524 while ( my ( $key, $value ) = each %{$outargs} ) {
525 $sth->bind_param_inout( ":$key" => $value, 0 );
526 }
527 }
528 };
529 $timer->stop("bind") if defined $timer;
530  
531 ## whinge and die as necessary
532 confess $@ if $@;
533  
534 ## execute
535 $timer->start("execute") if defined $timer;
536 my $rv;
537 eval { $rv = $sth->execute(); };
538 my $dbh = $self->{_dbh};
539 if ( $@ || $dbh->errstr() ) {
540 cluck( "problem with $type => " . $dbh->errstr );
541 $self->error_handle( $dbh->errstr );
542 return;
543 }
544 $timer->stop("execute") if defined $timer;
545  
546 return ( $rv, $sth );
547 }
548 sub error_handle($){
549 my __PACKAGE__ $self = shift;
550 my $errorMessage = shift;
551  
552 }
553  
554 sub DESTROY($$$$) {
555 my __PACKAGE__ $self = shift;
556 eval { $self->{_dbh}->disconnect(); };
557 }
558  
559 sub getDebug($$$$) {
560 my __PACKAGE__ $self = shift;
561 my $type = shift;
562 my $debugArgs = shift;
563 my $timing = shift;
564  
565 my ( $line, $subroutine ) = ( caller(CALLERLEVEL) )[ CALLERLINEFIELD, CALLERSUBROUTINEFIELD ];
566 return (
567 "query=> $type sub=> $subroutine line=> $line"
568 . (
569 MAINTIMINGS
570 ? " t=> " . ( sprintf( MILLISECONDFORMAT, $timing * 1000 ) )
571 : ""
572 )
573 . ( $debugArgs ne "" ? "args=> " . $debugArgs : "(no arguments)" )
574 );
575 }
576  
577 sub debugOn () {
578 my __PACKAGE__ $self = shift;
579 $self->{_dbh}->trace(1);
580 }
581  
582 sub debugOff () {
583 my __PACKAGE__ $self = shift;
584 $self->{_dbh}->trace(0);
585 }
586  
587 1;
588  
589 __DATA__
↑ Summary