| 1 | package DB::Batch::Fetch; | ||
| 2 | # | ||
| 3 | # encapsulate fetching functionality | ||
| 4 | # | ||
| 5 | # Copyright 2010, Chris Becker <clbecker@gmail.com> | ||
| 6 | # | ||
| 7 | # Original work sponsered by Shutterstock, LLC. http://shutterstock.com | ||
| 8 | # | ||
| 9 | # This program is free software; you can redistribute it and/or modify | ||
| 10 | # it under the same terms as Perl itself. | ||
| 11 | |||
| 12 | use strict; | ||
| 13 | use warnings; | ||
| 14 | |||
| 15 | sub new { | ||
| 16 | my $class = shift; | ||
| 17 | my %args = @_; | ||
| 18 | my $self = bless \%args, $class; | ||
| 19 | return $self; | ||
| 20 | } | ||
| 21 | |||
| 22 | # execute a query with the next batch of bind values | ||
| 23 | # then cache the statement handle to retrieve results | ||
| 24 | # via process_sth() | ||
| 25 | sub execute_batch_read { | ||
| 26 | my $self = shift; | ||
| 27 | my %args = @_; | ||
| 28 | my $parent = $args{parent}; # Batch::Main instance calling this | ||
| 29 | my $query = $args{query}; # Batch::Query instance | ||
| 30 | my $opts = $args{opts}; # hashref passed in from the calling script | ||
| 31 | my $bind = $args{bindvalues}; # Batch::BindValues instance | ||
| 32 | my $dbi_bind = $args{dbi_bind}; # values passed through, bound to '?' placeholders for DBI | ||
| 33 | |||
| 34 | my $dbh = $parent->get_dbh(); | ||
| 35 | my $key = $query->get_key(); # md5 checksum of query string | ||
| 36 | my $H = $parent->{output}; # optional output handle/array/sub | ||
| 37 | |||
| 38 | unless (defined $parent->{sth}{$key}) { | ||
| 39 | my @batch = $bind->increment(); # get the current batch of bind values | ||
| 40 | |||
| 41 | # set flag to indicate the execution of the next query in the batch | ||
| 42 | # call Batch::Main->is_new_batch() from the while loop processing the query | ||
| 43 | $parent->{__new_batch__} = 1; | ||
| 44 | unless (@batch) { | ||
| 45 | # if we're out of data to bind, clear cached stuff and finish | ||
| 46 | $parent->clear($key); | ||
| 47 | return; | ||
| 48 | } | ||
| 49 | |||
| 50 | my $bound_query = $query->bind_batch(\@batch); # bind current batch of data to placeholders | ||
| 51 | $parent->{__last_query_key__} = $key; | ||
| 52 | $parent->{__last_query__} = $bound_query; # accessed with Batch::Main->get_last_query() | ||
| 53 | |||
| 54 | # run post hook subroutine if provided in opts | ||
| 55 | if (defined $opts->{pre_hook} && ref $opts->{pre_hook} eq 'CODE') { | ||
| 56 | $opts->{pre_hook}->(); | ||
| 57 | } | ||
| 58 | |||
| 59 | $parent->{sth}{$key} = $dbh->prepare($bound_query); # prepare statement handle and cache it | ||
| 60 | $opts->{log}->info($query->bindstr($bound_query,@$dbi_bind)) if $opts->{log}; | ||
| 61 | |||
| 62 | if (defined $H) { | ||
| 63 | |||
| 64 | # send the query to whatever was specified in $opts->{output} | ||
| 65 | # if nothing was specified then the query is executed on the db | ||
| 66 | # | ||
| 67 | # if a sub ref was passed in then the sub is called with args [query,(dbi bind values)] | ||
| 68 | # if an array ref was passed in, then the fully bound query is added to the array | ||
| 69 | # if a filehandle was passed in, then the fully bound query is printed to the handle | ||
| 70 | |||
| 71 | if (ref($H) eq 'CODE') { | ||
| 72 | $H->($bound_query,@$dbi_bind); | ||
| 73 | } elsif (ref($H) eq 'ARRAY') { | ||
| 74 | push @$H, $query->bindstr($bound_query,@$dbi_bind); | ||
| 75 | } elsif(ref(\$H) eq 'GLOB') { | ||
| 76 | print $H $query->bindstr($bound_query,@$dbi_bind); | ||
| 77 | } | ||
| 78 | |||
| 79 | } else { | ||
| 80 | $parent->{sth}{$key}->execute(@$dbi_bind); # execute the cached statement handle | ||
| 81 | } | ||
| 82 | } else { | ||
| 83 | $parent->{__new_batch__} = 0; | ||
| 84 | } | ||
| 85 | return 1; | ||
| 86 | } | ||
| 87 | |||
| 88 | # return a row from the cached statement handle for the current query | ||
| 89 | # if the current statement handle is finished, execute the query with the next batch of bind values | ||
| 90 | sub process_sth { | ||
| 91 | my $self = shift; | ||
| 92 | my %args = @_; | ||
| 93 | my $parent = $args{parent}; | ||
| 94 | my $query = $args{query}; | ||
| 95 | my $opts = $args{opts}; | ||
| 96 | my $dbi_bind = $args{dbi_bind}; # these are bound to ? placeholders for DBI | ||
| 97 | my $caller = $args{caller}; | ||
| 98 | my $key = $query->get_key(); | ||
| 99 | my $dbh = $parent->get_dbh(); | ||
| 100 | |||
| 101 | # call the appropriate fetch function from the statement handle based on what was called on Batch::Main | ||
| 102 | if (defined $parent->{sth}{$key}) { | ||
| 103 | if ($caller =~ m/fetchrow_hashref$/) { | ||
| 104 | if (my $hr = $parent->{sth}{$key}->fetchrow_hashref()) { | ||
| 105 | return $hr; | ||
| 106 | } | ||
| 107 | } elsif ($caller =~ m/fetchrow_arrayref$/) { | ||
| 108 | if (my $ar = $parent->{sth}{$key}->fetchrow_arrayref()) { | ||
| 109 | return $ar; | ||
| 110 | } | ||
| 111 | } elsif ($caller =~ m/fetchrow_array$/) { | ||
| 112 | if (my @arr = $parent->{sth}{$key}->fetchrow_array()) { | ||
| 113 | return @arr; | ||
| 114 | } | ||
| 115 | } elsif ($caller =~ m/fetchbatch_custom$/) { | ||
| 116 | # send the sth to the custom subroutine and return the result | ||
| 117 | $parent->auto_throttle($opts); | ||
| 118 | $parent->throttle($opts); | ||
| 119 | return $opts->{sub}->( $parent->{sth}{$key}, $opts->{sub_args}, $opts ); | ||
| 120 | } | ||
| 121 | |||
| 122 | # run post hook subroutine if provided in opts | ||
| 123 | if (defined $opts->{post_hook} && ref $opts->{post_hook} eq 'CODE') { | ||
| 124 | $opts->{post_hook}->(); | ||
| 125 | } | ||
| 126 | |||
| 127 | # perform any throttling before executing the next query | ||
| 128 | $parent->auto_throttle($opts); | ||
| 129 | $parent->throttle($opts); | ||
| 130 | undef $parent->{sth}{$key}; | ||
| 131 | |||
| 132 | # if we've finished executing a statement handle, proceed to execute the query with the next batch of arguments | ||
| 133 | if ($caller =~ m/fetchrow_hashref$/) { | ||
| 134 | $parent->fetchrow_hashref($query->get_original_query(),$opts,@$dbi_bind); | ||
| 135 | |||
| 136 | } elsif ($caller =~ m/fetchrow_arrayref$/) { | ||
| 137 | $parent->fetchrow_arrayref($query->get_original_query(),$opts,@$dbi_bind); | ||
| 138 | |||
| 139 | } elsif ($caller =~ m/fetchrow_array$/) { | ||
| 140 | $parent->fetchrow_array($query->get_original_query(),$opts,@$dbi_bind); | ||
| 141 | } | ||
| 142 | } | ||
| 143 | } | ||
| 144 | |||
| 145 | 1; |