File Coverage

blib/lib/SQL/Executor.pm
Criterion Covered Total %
statement 216 230 93.9
branch 42 52 80.7
condition 11 21 52.3
subroutine 59 60 98.3
pod 32 32 100.0
total 360 395 91.1


line stmt bran cond sub pod time code
1             package SQL::Executor;
2 12     12   38287 use parent qw(Exporter);
  12         3749  
  12         56  
3 12     12   542 use strict;
  12         21  
  12         294  
4 12     12   52 use warnings;
  12         25  
  12         706  
5             our $VERSION = '0.17';
6              
7             our @EXPORT_OK = qw(named_bind);
8              
9             use Class::Accessor::Lite (
10 12         91 ro => ['builder', 'handler', 'allow_empty_condition', 'backup_callback', 'check_empty_bind'],
11             rw => ['callback'],
12 12     12   10962 );
  12         13587  
13 12     12   15978 use SQL::Maker;
  12         217892  
  12         583  
14 12     12   121 use Carp qw();
  12         23  
  12         234  
15 12     12   11182 use Try::Tiny;
  12         19334  
  12         689  
16 12     12   6530 use SQL::Executor::Iterator;
  12         30  
  12         332  
17 12     12   10889 use DBIx::Handler;
  12         380950  
  12         52404  
18              
19              
20             =head1 NAME
21              
22             SQL::Executor - Thin DBI wrapper using SQL::Maker
23              
24             =head1 SYNOPSIS
25              
26             use DBI;
27             use SQL::Executor;
28             my $dbh = DBI->connect($dsn, $id, $pass);
29             my $ex = SQL::Executor->new($dbh);
30             #
31             # SQL::Maker-like interfaces
32             my @rows = $ex->select('SOME_TABLE', { id => 123 });
33             $ex->insert('SOME_TABLE', { id => 124, value => 'xxxx'} );
34             $ex->update('SOME_TABLE', { value => 'yyyy'}, { id => 124 } );
35             $ex->delete('SOME_TABLE', { id => 124 } );
36             #
37             # select using SQL with named placeholder
38             my @rows= $ex->select_named('SELECT id, value1 FROM SOME_TABLE WHERE value2 = :arg1', { arg1 => 'aaa' });
39              
40             =head1 DESCRIPTION
41              
42             SQL::Executor is thin DBI wrapper using L. This module provides interfaces to make easier access to SQL.
43              
44             You can execute SQL via SQL::Maker-like interface in select(), select_row(), select_all(), select_with_fields(), select_row_with_fields(), select_all_with_fields(), insert(), insert_multi(), update() and delete().
45              
46             If you want to use more complex select query, you can use select_named(), select_row_named() or select_all_named() these execute SQL with named placeholder. If you don't want to use named placeholder, you can use select_by_sql(), select_row_by_sql() or select_all_by_sql() these execute SQL with normal placeholder('?').
47              
48             =cut
49              
50             =head1 METHODS
51              
52             =cut
53              
54             =head2 new($dbh, $option_href)
55              
56             $dbh: Database Handler
57             $option_href: option
58              
59             available option is as follows
60              
61             =over 4
62              
63             =item * allow_empty_condition (BOOL default 1): allow empty condition(where) in select/delete/update
64              
65             =item * callback (coderef): specify callback coderef. callback is called for each select* method
66              
67             =item * check_empty_bind (BOOL default 0): if TRUE(1), select*_named() do not accept unbound parameter, see named_bind() for detail.
68              
69             =back
70              
71             These callbacks are useful for making row object.
72              
73             my $ex = SQL::Executor->new($dbh, {
74             callback => sub {
75             my ($self, $row, $table_name, $select_id) = @_;
76             return CallBack::Class->new($row);
77             },
78             });
79              
80             my $row = $ex->select_by_sql($sql1, \@binds1, 'TEST');
81             # $row isa 'CallBack::Class'
82              
83              
84             =cut
85              
86             sub new {
87 39     39 1 256817 my ($class, $dbh, $option_href) = @_;
88 39         944 my $builder = SQL::Maker->new( driver => $dbh->{Driver}->{Name} );
89              
90 39         1179 my $self = {
91             builder => $builder,
92             dbh => $dbh,
93             _options($option_href),
94             };
95 39         177 bless $self, $class;
96             }
97              
98             =head2 connect($dsn, $user, $pass, $option_for_dbi, $option_href)
99              
100             $dsn: DSN
101             $user: database user
102             $pass: database password
103             $option_href_for_dbi: options passed to DBI
104             $option_href: option for SQL::Executor (options are same as new() method)
105              
106             connect database and create SQL::Executor instance. using this method, SQL::Executor uses
107             managed connection and transaction via L
108              
109             =cut
110              
111             sub connect {
112 1     1 1 667 my ($class, $dsn, $user, $pass, $option_href_for_dbi, $option_href) = @_;
113 1         11 my $handler = DBIx::Handler->new($dsn, $user, $pass, $option_href_for_dbi);
114 1         27 my $builder = SQL::Maker->new( driver => $handler->dbh->{Driver}->{Name} );
115              
116 1         12757 my $self = {
117             builder => $builder,
118             handler => $handler,
119             _options($option_href),
120             };
121 1         9 bless $self, $class;
122             }
123              
124             sub _options {
125 40     40   164 my ($option_href) = @_;
126              
127             return (
128 40 100       480 allow_empty_condition => defined $option_href->{allow_empty_condition} ? $option_href->{allow_empty_condition} : 1,
129             check_empty_bind => !!$option_href->{check_empty_bind},
130             callback => $option_href->{callback},
131             backup_callback => $option_href->{callback},
132             );
133             }
134              
135             =head2 dbh()
136              
137             return database handler
138              
139             =cut
140              
141             sub dbh {
142 55     55 1 83 my ($self) = @_;
143 55 100       170 return $self->handler->dbh if ( defined $self->handler );
144 54         439 return $self->{dbh};
145             }
146              
147              
148             =head2 select($table_name, $where, $option)
149              
150             select row(s). parameter is the same as select method in L. But array ref for filed names are not needed.
151             In array context, this method behaves the same as select_all. In scalar context, this method behaves the same as select_one
152              
153             =cut
154              
155             sub select {
156 13     13 1 3653 my ($self, $table_name, $where, $option) = @_;
157 13 100       41 if( wantarray() ) {
158 5         21 return $self->select_all($table_name, $where, $option);
159             }
160 8         34 return $self->select_row($table_name, $where, $option);
161             }
162              
163              
164             =head2 select_row($table_name, $where, $option)
165              
166             select only one row. parameter is the same as select method in L. But array ref for filed names are not needed.
167             this method returns hash ref and it is the same as return value in DBI's selectrow_hashref/fetchrow_hashref.
168              
169             =cut
170              
171             sub select_row {
172 12     12 1 1036 my ($self, $table_name, $where, $option) = @_;
173 12 100       24 my %option = %{ $option || {} };
  12         86  
174 12         35 $option{limit} = 1;
175 12         61 return $self->select_row_with_fields($table_name, ['*'], $where, \%option);
176             }
177              
178             =head2 select_all($table_name, $where, $option)
179              
180             select all rows. parameter is the same as select method in L. But array ref for filed names are not needed.
181             this method returns array that is composed of hash refs. (hash ref is same as DBI's selectrow_hashref/fetchrow_hashref).
182              
183             =cut
184              
185             sub select_all {
186 8     8 1 59 my ($self, $table_name, $where, $option) = @_;
187 8         38 return $self->select_all_with_fields($table_name, ['*'], $where, $option);
188             }
189              
190             =head2 select_itr($table_name, $where, $option)
191              
192             select and returns iterator. parameter is the same as select method in L. But array ref for field names are not needed.
193             Iterator is L object.
194              
195             my $itr = select_itr('SOME_TABLE', { name => 'aaa' });
196             while( my $row = $itr->next ) {
197             # ... using row
198             }
199              
200             =cut
201              
202             sub select_itr {
203 3     3 1 2187 my ($self, $table_name, $where, $option) = @_;
204 3         15 return $self->select_itr_with_fields($table_name, ['*'], $where, $option);
205             }
206              
207              
208             =head2 select_named($sql, $params_href, $table_name)
209              
210             select row(s). In array context, this method behaves the same as select_all_with_fields.
211             In scalar context, this method behaves the same as select_one_with_fileds
212              
213             You can use named placeholder in SQL like this,
214              
215             my $ex = SQL::Executor->new($dbh);
216             my $row = $ex->select_named("SELECT * FROM SOME_TABLE WHERE id = :id", { id => 1234 });
217              
218             $table_name is used for callback.
219              
220             =cut
221              
222             sub select_named {
223 4     4 1 2600 my ($self, $sql, $params_href, $table_name) = @_;
224 4 100       14 if( wantarray() ) {
225 2         8 return $self->select_all_named($sql, $params_href, $table_name);
226             }
227 2         10 return $self->select_row_named($sql, $params_href, $table_name);
228             }
229              
230             =head2 select_row_named($sql, $params_href, $table_name)
231              
232             select only one row. You can use named placeholder in SQL like this,
233              
234             my $ex = SQL::Executor->new($dbh);
235             my $row = $ex->select_row_named("SELECT * FROM SOME_TABLE WHERE id = :id", { id => 1234 });
236              
237             this method returns hash ref and it is the same as return value in DBI's selectrow_hashref/fetchrow_hashref.
238              
239             $table_name is used for callback.
240              
241             =cut
242              
243             sub select_row_named {
244 3     3 1 14 my ($self, $sql, $params_href, $table_name) = @_;
245 3         41 my ($new_sql, @binds) = named_bind($sql, $params_href, $self->check_empty_bind);
246 3         14 return $self->select_row_by_sql($new_sql, \@binds, $table_name);
247             }
248              
249             =head2 select_all_named($sql, $params_href, $table_name)
250              
251             select all rows. You can use named placeholder in SQL like this,
252              
253             my $ex = SQL::Executor->new($dbh);
254             my @rows = $ex->select_all_named("SELECT * FROM SOME_TABLE WHERE id = :id", { id => 1234 });
255              
256             this method returns array that is composed of hash refs. (hash ref is same as DBI's selectrow_hashref/fetchrow_hashref).
257             $table_name is used for callback.
258              
259             =cut
260              
261             sub select_all_named {
262 3     3 1 11 my ($self, $sql, $params_href, $table_name) = @_;
263 3         12 my ($new_sql, @binds) = named_bind($sql, $params_href, $self->check_empty_bind);
264 3         15 return $self->select_all_by_sql($new_sql, \@binds, $table_name);
265             }
266              
267             =head2 select_itr_named($sql, $params_href, $table_name)
268              
269             select and returns iterator. You can use named placeholder in SQL like this,
270              
271             my $ex = SQL::Executor->new($dbh);
272             my $itr = $ex->select_itr_named("SELECT * FROM SOME_TABLE WHERE id = :id", { id => 1234 });
273              
274             $table_name is used for callback.
275              
276             =cut
277              
278             sub select_itr_named {
279 2     2 1 926 my ($self, $sql, $params_href, $table_name) = @_;
280 2         8 my ($new_sql, @binds) = named_bind($sql, $params_href, $self->check_empty_bind);
281 2         8 return $self->select_itr_by_sql($new_sql, \@binds, $table_name);
282             }
283              
284              
285             =head2 named_bind($sql, $params_href, $check_empty_bind)
286              
287             returns sql which is executable in execute_query() and parameters for bind.
288              
289             my ($sql, @binds) = named_bind("SELECT * FROM SOME_TABLE WHERE id = :id", { id => 123 });
290             # $sql => "SELECT * FROM SOME_TABLE WHERE id = ?"
291             # @binds => (123)
292              
293             parameter $check_empty_bind is optional. By default (or set $check_empty_bind=0),
294             named_bind() accepts unbound parameter like this,
295              
296             my ($sql, @binds) = named_bind("SELECT * FROM SOME_TABLE WHERE id = :id", { });# do not bind :id
297             # $sql => "SELECT * FROM SOME_TABLE WHERE id = ?"
298             # @binds => (undef)
299              
300             if $check_empty_bind is 1, named_bind() dies when unbound parameter is specified.
301              
302             =cut
303              
304             # this code is taken from Teng's search_named()
305             sub named_bind {
306 13     13 1 20786 my ($sql, $params_href, $check_empty_bind) = @_;
307              
308 13 100       20 my %named_bind = %{ $params_href || {} };
  13         69  
309 13         21 my @binds;
310 13         22 my $new_sql = $sql;
311 13         87 $new_sql =~ s{:([A-Za-z_][A-Za-z0-9_]*)}{
312 15 100 66     317 Carp::croak("'$1' does not exist in bind hash") if ( !exists $named_bind{$1} && !!$check_empty_bind );
313 14 50 33     55 if ( ref $named_bind{$1} && ref $named_bind{$1} eq "ARRAY" ) {
314 0         0 push @binds, @{ $named_bind{$1} };
  0         0  
315 0         0 my $tmp = join ',', map { '?' } @{ $named_bind{$1} };
  0         0  
  0         0  
316 0         0 "( $tmp )";
317             } else {
318 14         52 push @binds, $named_bind{$1};
319 14         48 '?'
320             }
321             }ge;
322 12         49 return ($new_sql, @binds);
323             }
324              
325              
326             =head2 select_by_sql($sql, \@binds, $table_name)
327              
328             select row(s). In array context, this method behaves the same as select_all_with_fields.
329             In scalar context, this method behaves the same as select_one_with_fileds
330              
331             my $ex = SQL::Executor->new($dbh);
332             my $row = $ex->select_by_sql("SELECT * FROM SOME_TABLE WHERE id = ?", [1234]);
333              
334             $table_name is only used for callback.
335              
336             =cut
337              
338             sub select_by_sql {
339 4     4 1 3199 my ($self, $sql, $binds_aref, $table_name) = @_;
340 4 100       17 if( wantarray() ) {
341 2         11 return $self->select_all_by_sql($sql, $binds_aref, $table_name);
342             }
343 2         12 return $self->select_row_by_sql($sql, $binds_aref, $table_name);
344             }
345              
346             =head2 select_row_by_sql($sql, \@binds, $table_name)
347              
348             select only one row.
349              
350             my $ex = SQL::Executor->new($dbh);
351             my $row = $ex->select_row_by_sql("SELECT * FROM SOME_TABLE WHERE id = ?", [1234]);
352              
353             this method returns hash ref and it is the same as return value in DBI's selectrow_hashref/fetchrow_hashref.
354              
355             =cut
356              
357             sub select_row_by_sql {
358 20     20 1 76 my ($self, $sql, $binds_aref, $table_name) = @_;
359 20         68 my $row = undef;
360 20         64 local $Carp::Internal{(__PACKAGE__)} = 1;
361             try {
362 20     20   684 $row = $self->_select_row_by_sql($sql, $binds_aref, $table_name);
363             } catch {
364 1     1   121 $self->handle_exception($sql, $binds_aref, $_);
365 20         200 };
366 19         709 return $row;
367             }
368              
369             sub _select_row_by_sql {
370 20     20   47 my ($self, $sql, $binds_aref, $table_name) = @_;
371 20         572 my $dbh = $self->dbh;
372 20 50       80 my $row = $dbh->selectrow_hashref($sql, undef, @{ $binds_aref || [] } );
  20         262  
373 19         4091 my $callback = $self->callback;
374 19 100 66     190 if ( defined $callback && defined $row ) {
375 5         26 return $callback->($self, $row, $table_name, $self->select_id);
376             }
377 14         56 return $row;
378             }
379              
380             =head2 select_all_by_sql($sql, \@binds, $table_name)
381              
382             select all rows.
383              
384             my $ex = SQL::Executor->new($dbh);
385             my @rows = $ex->select_all_by_sql("SELECT * FROM SOME_TABLE WHERE id = ?", [1234]);
386              
387             this method returns array that is composed of hash refs. (hash ref is same as DBI's selectrow_hashref/fetchrow_hashref).
388              
389             =cut
390              
391             sub select_all_by_sql {
392 15     15 1 43 my ($self, $sql, $binds_aref, $table_name) = @_;
393 15         30 my @result = ();
394 15         49 local $Carp::Internal{(__PACKAGE__)} = 1;
395             try {
396 15     15   546 @result = $self->_select_all_by_sql($sql, $binds_aref, $table_name);
397             } catch {
398 1     1   104 $self->handle_exception($sql, $binds_aref, $_);
399 15         139 };
400 14         254 return @result;
401             }
402              
403             sub _select_all_by_sql {
404 15     15   38 my ($self, $sql, $binds_aref, $table_name) = @_;
405 15         45 my $dbh = $self->dbh;
406 15 50       32 my @rows = @{ $dbh->selectall_arrayref($sql, { Slice => {} }, @{ $binds_aref || [] }) };
  15         54  
  15         396  
407 14         3585 my $callback = $self->callback;
408 14 100       206 if( defined $callback ) {
409 4         19 my $select_id = $self->select_id;
410 4         12 my @result = map{ $callback->($self, $_, $table_name, $select_id) } @rows;
  8         61  
411 4         133 return @result;
412             }
413 10         51 return @rows;
414             }
415              
416             =head2 select_itr_by_sql($sql, \@binds, $table_name)
417              
418             select and returns iterator
419              
420             my $ex = SQL::Executor->new($dbh);
421             my $itr = $ex->select_itr_by_sql("SELECT * FROM SOME_TABLE WHERE id = ?", [1234]);
422              
423             Iterator is L object.
424              
425             =cut
426              
427             sub select_itr_by_sql {
428 9     9 1 1142 my ($self, $sql, $binds_aref, $table_name) = @_;
429 9         18 my $itr = undef;
430 9         27 local $Carp::Internal{(__PACKAGE__)} = 1;
431             try {
432 9     9   246 $itr = $self->_select_itr_by_sql($sql, $binds_aref, $table_name);
433             } catch {
434 1     1   91 $self->handle_exception($sql, $binds_aref, $_);
435 9         79 };
436 8         134 return $itr;
437             }
438              
439             sub _select_itr_by_sql {
440 9     9   22 my ($self, $sql, $binds_aref, $table_name) = @_;
441 9         26 my $dbh = $self->dbh;
442 9         139 my $sth = $dbh->prepare($sql);
443 8 50       1577 $sth->execute(@{ $binds_aref || [] });
  8         157  
444 8 100       33 my $select_id = defined $self->callback ? $self->select_id : undef; #select_id does not need if callback is disabled.
445 8         88 return SQL::Executor::Iterator->new($sth, $table_name, $self, $select_id);
446             }
447              
448              
449             =head2 select_with_fields($table_name, $fields_aref, $where, $option)
450              
451             select row(s). parameter is the same as select method in L.
452             In array context, this method behaves the same as select_all_with_fields.
453             In scalar context, this method behaves the same as select_one_with_fileds
454              
455             =cut
456              
457             sub select_with_fields {
458 4     4 1 2012 my ($self, $table_name, $fields_aref, $where, $option) = @_;
459 4 100       13 if( wantarray() ) {
460 2         7 return $self->select_all_with_fields($table_name, $fields_aref, $where, $option);
461             }
462 2         8 return $self->select_row_with_fields($table_name, $fields_aref, $where, $option);
463             }
464              
465             =head2 select_row_with_fields($table_name, $fields_aref, $where, $option)
466              
467             select only one row. parameter is the same as select method in L.
468             this method returns hash ref and it is the same as return value in DBI's selectrow_hashref/fetchrow_hashref.
469              
470             =cut
471              
472             sub select_row_with_fields {
473 16     16 1 51 my ($self, $table_name, $fields_aref, $where, $option) = @_;
474 16 100       27 my %option = %{ $option || {} };
  16         83  
475 16         35 $option{limit} = 1;
476 16         64 my ($sql, @binds) = $self->_prepare_select_statement($table_name, $fields_aref, $where, \%option);
477 14         88 return $self->select_row_by_sql($sql, \@binds, $table_name);
478             }
479              
480             =head2 select_all_with_fields($table_name, $fields_aref, $where, $option)
481              
482             select all rows. parameter is the same as select method in L. But array ref for filed names are not needed.
483             this method returns array that is composed of hash refs. (hash ref is same as DBI's selectrow_hashref/fetchrow_hashref).
484              
485             =cut
486              
487             sub select_all_with_fields {
488 12     12 1 41 my ($self, $table_name, $fields_aref, $where, $option) = @_;
489 12         38 my ($sql, @binds) = $self->_prepare_select_statement($table_name, $fields_aref, $where, $option);
490 9         43 return $self->select_all_by_sql($sql, \@binds, $table_name);
491             }
492              
493             =head2 select_itr_with_fields($table_name, $fields_aref, $where, $option)
494              
495             select and return iterator object(L). parameter is the same as select method in L.
496              
497             =cut
498              
499             sub select_itr_with_fields {
500 5     5 1 789 my ($self, $table_name, $fields_aref, $where, $option) = @_;
501 5         16 my ($sql, @binds) = $self->_prepare_select_statement($table_name, $fields_aref, $where, $option);
502 5         22 return $self->select_itr_by_sql($sql, \@binds, $table_name);
503             }
504              
505              
506             # prepare select statment using SQL::Maker
507             sub _prepare_select_statement {
508 33     33   58 my ($self, $table_name, $fields_aref, $where, $option) = @_;
509 33 100 66     142 Carp::croak "condition is empty" if ( !$self->allow_empty_condition && $self->_is_empty_where($where) );
510 28         358 my $builder = $self->builder;
511 28         215 my ($sql, @binds) = $builder->select($table_name, $fields_aref, $where, $option);
512 28         10006 return ($sql, @binds);
513             }
514              
515              
516             =head2 insert($table_name, $values)
517              
518             Do INSERT statement. parameter is the same as select method in L.
519              
520             =cut
521              
522             sub insert {
523 2     2 1 16 my ($self, $table_name, $values) = @_;
524 2         8 my $builder = $self->builder;
525 2         23 my ($sql, @binds) = $builder->insert($table_name, $values);
526 2         212 $self->_execute_and_finish($sql, \@binds);
527             }
528              
529             =head2 insert_multi($table_name, @args)
530              
531             Do INSERT-multi statement using L.
532              
533             =cut
534              
535             sub insert_multi {
536 1     1 1 10 my ($self, $table_name, @args) = @_;
537 1         5 $self->_load_sql_maker_plugin('InsertMulti');
538 1         7 my $builder = $self->builder;
539 1         9 my ($sql, @binds) = $builder->insert_multi($table_name, @args);
540 1         192 $self->_execute_and_finish($sql, \@binds);
541             }
542              
543             =head2 insert_on_duplicate($table_name, $insert_value_href, $update_value_href)
544              
545             Do "INSERT ... ON DUPLICATE KEY UPDATE" query (works only MySQL) using L.
546              
547             this method is available when L >= 1.09 is installed. If older version is installed, you will
548             got error like "Can't locate SQL/Maker/Plugin/InsertOnDuplicate.pm in @INC ..."
549              
550             =cut
551              
552             sub insert_on_duplicate {
553 0     0 1 0 my ($self, $table_name, $insert_value_href, $update_value_href) = @_;
554 0         0 $self->_load_sql_maker_plugin('InsertOnDuplicate');
555 0         0 my $builder = $self->builder;
556 0         0 my ($sql, @binds) = $builder->insert_on_duplicate($table_name, $insert_value_href, $update_value_href);
557 0         0 $self->_execute_and_finish($sql, \@binds);
558             }
559              
560              
561              
562             =head2 delete($table_name, $where)
563              
564             Do DELETE statement. parameter is the same as select method in L.
565              
566             =cut
567              
568             sub delete {
569 2     2 1 12 my ($self, $table_name, $where) = @_;
570 2 100 66     9 Carp::croak "condition is empty" if ( !$self->allow_empty_condition && $self->_is_empty_where($where) );
571 1         18 my $builder = $self->builder;
572 1         20 my ($sql, @binds) = $builder->delete($table_name, $where);
573 1         189 $self->_execute_and_finish($sql, \@binds);
574             }
575              
576              
577             =head2 update($table_name, $set, $where)
578              
579             Do UPDATE statement. parameter is the same as select method in L.
580              
581             =cut
582              
583             sub update {
584 2     2 1 19 my ($self, $table_name, $set, $where) = @_;
585 2 100 66     10 Carp::croak "condition is empty" if ( !$self->allow_empty_condition && $self->_is_empty_where($where) );
586 1         21 my $builder = $self->builder;
587 1         20 my ($sql, @binds) = $builder->update($table_name, $set, $where);
588 1         263 $self->_execute_and_finish($sql, \@binds);
589             }
590              
591              
592             =head2 execute_query($sql, \@binds)
593              
594             execute query and returns statement handler($sth).
595              
596             =cut
597              
598             sub execute_query {
599 6     6 1 49 my ($self, $sql, $binds_aref) = @_;
600 6         28 my $sth = undef;
601 6         19 local $Carp::Internal{(__PACKAGE__)} = 1;
602             try {
603 6     6   233 $self->_execute_query($sql, $binds_aref);
604             } catch {
605 1     1   108 $self->handle_exception($sql, $binds_aref, $_);
606 6         75 };
607             }
608              
609             sub _execute_query {
610 6     6   58 my ($self, $sql, $binds_aref) = @_;
611 6         53 my $dbh = $self->dbh;
612 6         41 my $sth = $dbh->prepare($sql);
613 5 50       358 $sth->execute(@{ $binds_aref || [] });
  5         153  
614 5         19 return $sth;
615             }
616              
617             =head2 execute_query_named($sql, $params_href)
618              
619             execute query with named placeholder and returns statement handler($sth).
620              
621             =cut
622              
623             sub execute_query_named {
624 2     2 1 48 my ($self, $sql, $params_href) = @_;
625 2         5 local $Carp::Internal{(__PACKAGE__)} = 1;
626 2         4 my $sth = undef;
627 2         16 my ($new_sql, @binds) = named_bind($sql, $params_href, $self->check_empty_bind);
628             try {
629 2     2   60 my $dbh = $self->dbh;
630 2         14 $sth = $dbh->prepare($new_sql);
631 1         86 $sth->execute(@binds);
632             } catch {
633 1     1   80 $self->handle_exception($new_sql, \@binds, $_);
634 2         19 };
635 1         30 return $sth;
636             }
637              
638              
639              
640              
641             =head2 disable_callback()
642              
643             disable callback temporarily,
644              
645             =cut
646              
647             sub disable_callback {
648 1     1 1 295 my ($self) = @_;
649 1         4 $self->callback(undef);
650             }
651              
652             =head2 restore_callback()
653              
654             restore disabled callback.
655              
656             =cut
657              
658             sub restore_callback {
659 1     1 1 402 my ($self) = @_;
660 1         5 $self->callback($self->backup_callback);
661             }
662              
663             =head2 last_insert_id(@args)
664              
665             If driver is mysql, return $dbh->{mysql_insertid}.If driver is SQLite, return $dbh->sqlite_last_insert_rowid.
666             If other driver is used, return $dbh->last_insert_id(@args)
667              
668             =cut
669              
670             sub last_insert_id {
671 1     1 1 7 my ($self, @args) = @_;
672 1 50       3 if( $self->dbh->{Driver}->{Name} eq 'mysql' ) {
673 0         0 return $self->dbh->{mysql_insertid};
674             }
675 1 50       4 if( $self->dbh->{Driver}->{Name} eq 'SQLite' ) {
676 1         3 return $self->dbh->sqlite_last_insert_rowid;
677             }
678              
679 0         0 return $self->dbh->last_insert_id(@args);
680             }
681              
682              
683             sub _execute_and_finish {
684 5     5   16 my ($self, $sql, $binds_aref) = @_;
685 5         22 my $sth = $self->execute_query($sql, $binds_aref);
686 5         176 $sth->finish;
687             }
688              
689             sub _is_empty_where {
690 7     7   73 my ($self, $where) = @_;
691             return !defined $where
692             || ( ref $where eq 'ARRAY' && !@{ $where } )
693             || ( ref $where eq 'HASH' && !%{ $where } )
694 7   0     1322 || ( eval{ $where->can('as_sql') } && $where->as_sql eq '' ) #SQL::Maker::Condition
695             ;
696             }
697              
698              
699             =head2 handle_exception($sql, $binds_aref, $err_message)
700              
701             show error message. you can override this method in subclass to provide
702             customized error message.
703              
704             default error message is like this,
705              
706             Error $error_message sql: $sql, binds: [$binds_aref]\n
707              
708             =cut
709              
710             sub handle_exception {
711 5     5 1 12 my ($self, $sql, $binds_aref, $err) = @_;
712 5 0       6 my $binds_text = join(',', map{ defined $_ ? "'$_'" : 'NULL' } @{ $binds_aref || [] });
  0 100       0  
  5         24  
713 5         14 my $message = "Error $err sql: $sql, binds: [$binds_text]\n";
714 5         729 Carp::croak($message);
715             }
716              
717             =head2 select_id()
718              
719             generate id for select statament. but by default, id is not generated.
720             If you want to generate id, please override
721              
722             =cut
723              
724             sub select_id {
725 13     13 1 48 my ($self) = @_;
726 13         38 return;
727             }
728              
729             # load SQL::Maker plugin
730             sub _load_sql_maker_plugin {
731 1     1   2 my ($self, $plugin_name) = @_;
732              
733 1 50       17 if( !defined $self->{sql_maker_load_plugin}->{$plugin_name} ) {
734 1         6 SQL::Maker->load_plugin($plugin_name);
735 1         1717 $self->{sql_maker_load_plugin}->{$plugin_name} = 1;
736             }
737             }
738              
739              
740             1;
741             __END__