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__ |