first commit
|
1 |
package DBI::Custom; |
2 |
use Object::Simple; |
|
add test
|
3 | |
4 |
our $VERSION = '0.0101'; |
|
5 | ||
6 |
use Carp 'croak'; |
|
add some method
|
7 |
use DBI; |
cleanup
|
8 |
use DBI::Custom::Query; |
add tests
|
9 |
use DBI::Custom::Result; |
10 |
use DBI::Custom::SQL::Template; |
|
11 | ||
add tests
|
12 | |
13 |
### Class-Object Accessors |
|
update document
|
14 |
sub user : ClassObjectAttr { initialize => {clone => 'scalar'} } |
15 |
sub password : ClassObjectAttr { initialize => {clone => 'scalar'} } |
|
16 |
sub data_source : ClassObjectAttr { initialize => {clone => 'scalar'} } |
|
cleanup
|
17 |
sub dbi_options : ClassObjectAttr { initialize => {clone => 'hash', |
add tests
|
18 |
default => sub { {} } } } |
catch up Object::Simple upda...
|
19 | |
update document
|
20 |
sub bind_filter : ClassObjectAttr { initialize => {clone => 'scalar'} } |
21 |
sub fetch_filter : ClassObjectAttr { initialize => {clone => 'scalar'} } |
|
catch up Object::Simple upda...
|
22 | |
add no_bind_filters
|
23 |
sub no_bind_filters : ClassObjectAttr { initialize => {clone => 'array'} } |
24 |
sub no_fetch_filters : ClassObjectAttr { initialize => {clone => 'array'} } |
|
add various thins
|
25 | |
catch up Object::Simple upda...
|
26 |
sub filters : ClassObjectAttr { |
27 |
type => 'hash', |
|
28 |
deref => 1, |
|
29 |
initialize => { |
|
add tests
|
30 |
clone => 'hash', |
31 |
default => sub { {} } |
|
catch up Object::Simple upda...
|
32 |
} |
33 |
} |
|
first commit
|
34 | |
catch up Object::Simple upda...
|
35 |
sub result_class : ClassObjectAttr { |
36 |
initialize => { |
|
add tests
|
37 |
clone => 'scalar', |
38 |
default => 'DBI::Custom::Result' |
|
catch up Object::Simple upda...
|
39 |
} |
40 |
} |
|
cleanup
|
41 | |
catch up Object::Simple upda...
|
42 |
sub sql_template : ClassObjectAttr { |
43 |
initialize => { |
|
update document
|
44 |
clone => sub {$_[0] ? $_[0]->clone : undef}, |
catch up Object::Simple upda...
|
45 |
default => sub {DBI::Custom::SQL::Template->new} |
46 |
} |
|
47 |
} |
|
cleanup
|
48 | |
add tests
|
49 |
### Object Accessor |
add tests
|
50 |
sub dbh : Attr {} |
add tests
|
51 | |
52 | ||
53 |
### Methods |
|
add various thins
|
54 | |
add tests
|
55 |
# Add filter |
56 |
sub add_filter { |
|
57 |
my $invocant = shift; |
|
58 |
|
|
59 |
my %old_filters = $invocant->filters; |
|
60 |
my %new_filters = ref $_[0] eq 'HASH' ? %{$_[0]} : @_; |
|
61 |
$invocant->filters(%old_filters, %new_filters); |
|
update document
|
62 |
return $invocant; |
add tests
|
63 |
} |
add various
|
64 | |
65 |
# Auto commit |
|
update document
|
66 |
sub _auto_commit { |
add various
|
67 |
my $self = shift; |
68 |
|
|
add tests
|
69 |
croak("Not yet connect to database") unless $self->dbh; |
add various
|
70 |
|
71 |
if (@_) { |
|
72 |
$self->dbh->{AutoCommit} = $_[0]; |
|
73 |
return $self; |
|
74 |
} |
|
75 |
return $self->dbh->{AutoCommit}; |
|
76 |
} |
|
add test
|
77 | |
add various things
|
78 |
# Connect |
add some method
|
79 |
sub connect { |
80 |
my $self = shift; |
|
update document
|
81 |
my $data_source = $self->data_source; |
82 |
my $user = $self->user; |
|
83 |
my $password = $self->password; |
|
cleanup
|
84 |
my $dbi_options = $self->dbi_options; |
add test
|
85 |
|
add tests
|
86 |
my $dbh = eval{DBI->connect( |
update document
|
87 |
$data_source, |
88 |
$user, |
|
89 |
$password, |
|
add some method
|
90 |
{ |
91 |
RaiseError => 1, |
|
92 |
PrintError => 0, |
|
93 |
AutoCommit => 1, |
|
cleanup
|
94 |
%{$dbi_options || {} } |
add some method
|
95 |
} |
add tests
|
96 |
)}; |
97 |
|
|
98 |
croak $@ if $@; |
|
add some method
|
99 |
|
100 |
$self->dbh($dbh); |
|
add various
|
101 |
return $self; |
add some method
|
102 |
} |
first commit
|
103 | |
add tests
|
104 |
# DESTROY |
add tests
|
105 |
sub DESTROY { |
106 |
my $self = shift; |
|
add tests
|
107 |
$self->disconnect if $self->connected; |
add tests
|
108 |
} |
109 | ||
add various things
|
110 |
# Is connected? |
111 |
sub connected { |
|
112 |
my $self = shift; |
|
cleanup
|
113 |
return ref $self->{dbh} eq 'DBI::db'; |
add various things
|
114 |
} |
115 | ||
116 |
# Disconnect |
|
117 |
sub disconnect { |
|
118 |
my $self = shift; |
|
add tests
|
119 |
if ($self->connected) { |
add various things
|
120 |
$self->dbh->disconnect; |
121 |
delete $self->{dbh}; |
|
122 |
} |
|
123 |
} |
|
124 | ||
125 |
# Reconnect |
|
126 |
sub reconnect { |
|
127 |
my $self = shift; |
|
add tests
|
128 |
$self->disconnect if $self->connected; |
add various things
|
129 |
$self->connect; |
130 |
} |
|
131 | ||
cleanup
|
132 |
# Prepare statement handle |
add prepare
|
133 |
sub prepare { |
134 |
my ($self, $sql) = @_; |
|
cleanup
|
135 |
|
136 |
# Connect if not |
|
add tests
|
137 |
$self->connect unless $self->connected; |
add prepare
|
138 |
|
cleanup
|
139 |
# Prepare |
add prepare
|
140 |
my $sth = eval{$self->dbh->prepare($sql)}; |
add tests
|
141 |
|
142 |
# Error |
|
143 |
croak("$@<Your SQL>\n$sql") if $@; |
|
144 |
|
|
add prepare
|
145 |
return $sth; |
146 |
} |
|
147 | ||
cleanup
|
148 |
# Execute SQL directly |
add prepare
|
149 |
sub do{ |
150 |
my ($self, $sql, @bind_values) = @_; |
|
cleanup
|
151 |
|
152 |
# Connect if not |
|
add tests
|
153 |
$self->connect unless $self->connected; |
add prepare
|
154 |
|
cleanup
|
155 |
# Do |
add tests
|
156 |
my $ret_val = eval{$self->dbh->do($sql, @bind_values)}; |
157 |
|
|
158 |
# Error |
|
159 |
if ($@) { |
|
160 |
my $error = $@; |
|
161 |
require Data::Dumper; |
|
162 |
|
|
163 |
my $bind_value_dump |
|
164 |
= Data::Dumper->Dump([\@bind_values], ['*bind_valuds']); |
|
165 |
|
|
166 |
croak("$error<Your SQL>\n$sql\n<Your bind values>\n$bind_value_dump\n"); |
|
167 |
} |
|
add prepare
|
168 |
} |
169 | ||
cleanup
|
170 |
# Create query |
add various thins
|
171 |
sub create_query { |
172 |
my ($self, $template) = @_; |
|
add query cache system
|
173 |
my $class = ref $self; |
add test
|
174 |
|
add various thins
|
175 |
# Create query from SQL template |
add prepare
|
176 |
my $sql_template = $self->sql_template; |
add test
|
177 |
|
add query cache system
|
178 |
# Try to get cached query |
179 |
my $query = $class->_query_caches->{$template}; |
|
180 |
|
|
181 |
# Create query |
|
182 |
unless ($query) { |
|
183 |
$query = eval{$sql_template->create_query($template)}; |
|
184 |
croak($@) if $@; |
|
185 |
|
|
186 |
$query = DBI::Custom::Query->new($query); |
|
187 |
|
|
188 |
$class->_add_query_cache($template, $query); |
|
189 |
} |
|
add various thins
|
190 |
|
cleanup
|
191 |
# Connect if not |
add tests
|
192 |
$self->connect unless $self->connected; |
try varioud way
|
193 |
|
add various thins
|
194 |
# Prepare statement handle |
add tests
|
195 |
my $sth = $self->prepare($query->{sql}); |
add tests
|
196 |
|
add no_bind_filters
|
197 |
# Set statement handle |
add various thins
|
198 |
$query->sth($sth); |
add tests
|
199 |
|
add no_bind_filters
|
200 |
# Set bind filter |
201 |
$query->bind_filter($self->bind_filter); |
|
202 |
|
|
203 |
# Set no filter keys when binding |
|
204 |
$query->no_bind_filters($self->no_bind_filters); |
|
add tests
|
205 |
|
add no_bind_filters
|
206 |
# Set fetch filter |
207 |
$query->fetch_filter($self->fetch_filter); |
|
208 |
|
|
209 |
# Set no filter keys when fetching |
|
210 |
$query->no_fetch_filters($self->no_fetch_filters); |
|
211 |
|
|
add various thins
|
212 |
return $query; |
213 |
} |
|
214 | ||
cleanup
|
215 |
# Execute query |
add various thins
|
216 |
sub execute { |
217 |
my ($self, $query, $params) = @_; |
|
add tests
|
218 |
$params ||= {}; |
try varioud way
|
219 |
|
add tests
|
220 |
# First argument is SQL template |
221 |
if (!ref $query) { |
|
222 |
my $template = $query; |
|
223 |
$query = $self->create_query($template); |
|
224 |
my $query_edit_cb = $_[3]; |
|
225 |
$query_edit_cb->($query) if ref $query_edit_cb eq 'CODE'; |
|
226 |
} |
|
227 |
|
|
add various thins
|
228 |
# Create bind value |
229 |
my $bind_values = $self->_build_bind_values($query, $params); |
|
add tests
|
230 |
|
cleanup
|
231 |
# Execute |
cleanup
|
232 |
my $sth = $query->sth; |
add tests
|
233 |
my $ret_val = eval{$sth->execute(@$bind_values)}; |
cleanup
|
234 |
|
235 |
# Execute error |
|
add tests
|
236 |
if (my $execute_error = $@) { |
add tests
|
237 |
require Data::Dumper; |
add tests
|
238 |
my $sql = $query->{sql} || ''; |
239 |
my $key_infos_dump = Data::Dumper->Dump([$query->key_infos], ['*key_infos']); |
|
240 |
my $params_dump = Data::Dumper->Dump([$params], ['*params']); |
|
add tests
|
241 |
|
add tests
|
242 |
croak("$execute_error" . |
243 |
"<Your SQL>\n$sql\n" . |
|
244 |
"<Your parameters>\n$params_dump"); |
|
add tests
|
245 |
} |
add various things
|
246 |
|
cleanup
|
247 |
# Return resultset if select statement is executed |
add various things
|
248 |
if ($sth->{NUM_OF_FIELDS}) { |
cleanup
|
249 |
|
250 |
# Get result class |
|
add various things
|
251 |
my $result_class = $self->result_class; |
cleanup
|
252 |
|
253 |
# Create result |
|
add various
|
254 |
my $result = $result_class->new({ |
add no_bind_filters
|
255 |
sth => $sth, |
256 |
fetch_filter => $query->fetch_filter, |
|
257 |
no_fetch_filters => $query->no_fetch_filters |
|
add various
|
258 |
}); |
add various things
|
259 |
return $result; |
260 |
} |
|
add tests
|
261 |
return $ret_val; |
add test
|
262 |
} |
263 | ||
cleanup
|
264 |
# Build binding values |
add various thins
|
265 |
sub _build_bind_values { |
266 |
my ($self, $query, $params) = @_; |
|
add no_bind_filters
|
267 |
my $key_infos = $query->key_infos; |
268 |
my $bind_filter = $query->bind_filter; |
|
269 |
my $no_bind_filters_map = $query->_no_bind_filters_map || {}; |
|
cleanup
|
270 |
|
add various thins
|
271 |
# binding values |
272 |
my @bind_values; |
|
cleanup
|
273 |
|
add tests
|
274 |
# Create bind values |
add tests
|
275 |
KEY_INFOS : |
cleanup
|
276 |
foreach my $key_info (@$key_infos) { |
cleanup
|
277 |
# Set variable |
278 |
my $access_keys = $key_info->{access_keys}; |
|
cleanup
|
279 |
my $original_key = $key_info->{original_key} || ''; |
280 |
my $table = $key_info->{table} || ''; |
|
281 |
my $column = $key_info->{column} || ''; |
|
add various thins
|
282 |
|
cleanup
|
283 |
# Key is found? |
add tests
|
284 |
my $found; |
cleanup
|
285 |
|
286 |
# Build bind values |
|
add various thins
|
287 |
ACCESS_KEYS : |
288 |
foreach my $access_key (@$access_keys) { |
|
cleanup
|
289 |
# Root parameter |
add various thins
|
290 |
my $root_params = $params; |
cleanup
|
291 |
|
292 |
# Search corresponding value |
|
add various thins
|
293 |
for (my $i = 0; $i < @$access_key; $i++) { |
cleanup
|
294 |
# Current key |
295 |
my $current_key = $access_key->[$i]; |
|
add various thins
|
296 |
|
cleanup
|
297 |
# Last key |
add various thins
|
298 |
if ($i == @$access_key - 1) { |
cleanup
|
299 |
# Key is array reference |
300 |
if (ref $current_key eq 'ARRAY') { |
|
301 |
# Filtering |
|
302 |
if ($bind_filter && |
|
303 |
!$no_bind_filters_map->{$original_key}) |
|
304 |
{ |
|
add no_bind_filters
|
305 |
push @bind_values, |
cleanup
|
306 |
$bind_filter->($original_key, |
307 |
$root_params->[$current_key->[0]], |
|
cleanup
|
308 |
$table, $column); |
add various thins
|
309 |
} |
cleanup
|
310 |
# Not filtering |
add various thins
|
311 |
else { |
cleanup
|
312 |
push @bind_values, |
313 |
scalar $root_params->[$current_key->[0]]; |
|
add various thins
|
314 |
} |
315 |
} |
|
cleanup
|
316 |
# Key is string |
add various thins
|
317 |
else { |
cleanup
|
318 |
# Key is not found |
319 |
next ACCESS_KEYS |
|
320 |
unless exists $root_params->{$current_key}; |
|
321 |
|
|
322 |
# Filtering |
|
323 |
if ($bind_filter && |
|
324 |
!$no_bind_filters_map->{$original_key}) |
|
325 |
{ |
|
add no_bind_filters
|
326 |
push @bind_values, |
cleanup
|
327 |
$bind_filter->($original_key, |
328 |
$root_params->{$current_key}, |
|
cleanup
|
329 |
$table, $column); |
add various thins
|
330 |
} |
cleanup
|
331 |
# Not filtering |
add various thins
|
332 |
else { |
cleanup
|
333 |
push @bind_values, |
334 |
scalar $root_params->{$current_key}; |
|
add various thins
|
335 |
} |
336 |
} |
|
cleanup
|
337 |
|
338 |
# Key is found |
|
add tests
|
339 |
$found = 1; |
add tests
|
340 |
next KEY_INFOS; |
add various thins
|
341 |
} |
cleanup
|
342 |
# First or middle key |
add various thins
|
343 |
else { |
cleanup
|
344 |
# Key is array reference |
345 |
if (ref $current_key eq 'ARRAY') { |
|
346 |
# Go next key |
|
347 |
$root_params = $root_params->[$current_key->[0]]; |
|
348 |
} |
|
349 |
# Key is string |
|
350 |
else { |
|
351 |
# Not found |
|
352 |
next ACCESS_KEYS |
|
353 |
unless exists $root_params->{$current_key}; |
|
354 |
|
|
355 |
# Go next key |
|
356 |
$root_params = $root_params->{$current_key}; |
|
357 |
} |
|
add various thins
|
358 |
} |
359 |
} |
|
360 |
} |
|
add tests
|
361 |
|
cleanup
|
362 |
# Key is not found |
add tests
|
363 |
unless ($found) { |
364 |
require Data::Dumper; |
|
add tests
|
365 |
my $key_info_dump = Data::Dumper->Dump([$key_info], ['*key_info']); |
add tests
|
366 |
my $params_dump = Data::Dumper->Dump([$params], ['*params']); |
add tests
|
367 |
croak("Corresponding key is not found in your parameters\n" . |
add tests
|
368 |
"<Key information>\n$key_info_dump\n\n" . |
369 |
"<Your parameters>\n$params_dump\n"); |
|
370 |
} |
|
update document
|
371 |
} |
add tests
|
372 |
return \@bind_values; |
add test
|
373 |
} |
374 | ||
add tests
|
375 |
# Run tranzaction |
376 |
sub run_tranzaction { |
|
377 |
my ($self, $tranzaction) = @_; |
|
378 |
|
|
379 |
# Check auto commit |
|
380 |
croak("AutoCommit must be true before tranzaction start") |
|
381 |
unless $self->_auto_commit; |
|
382 |
|
|
383 |
# Auto commit off |
|
384 |
$self->_auto_commit(0); |
|
385 |
|
|
386 |
# Run tranzaction |
|
387 |
eval {$tranzaction->()}; |
|
388 |
|
|
389 |
# Tranzaction error |
|
390 |
my $tranzaction_error = $@; |
|
391 |
|
|
392 |
# Tranzaction is failed. |
|
393 |
if ($tranzaction_error) { |
|
394 |
# Rollback |
|
395 |
eval{$self->dbh->rollback}; |
|
396 |
|
|
397 |
# Rollback error |
|
398 |
my $rollback_error = $@; |
|
399 |
|
|
400 |
# Auto commit on |
|
401 |
$self->_auto_commit(1); |
|
402 |
|
|
403 |
if ($rollback_error) { |
|
404 |
# Rollback is failed |
|
405 |
croak("${tranzaction_error}Rollback is failed : $rollback_error"); |
|
406 |
} |
|
407 |
else { |
|
408 |
# Rollback is success |
|
409 |
croak("${tranzaction_error}Rollback is success"); |
|
410 |
} |
|
411 |
} |
|
412 |
# Tranzaction is success |
|
413 |
else { |
|
414 |
# Commit |
|
415 |
eval{$self->dbh->commit}; |
|
416 |
my $commit_error = $@; |
|
417 |
|
|
418 |
# Auto commit on |
|
419 |
$self->_auto_commit(1); |
|
420 |
|
|
421 |
# Commit is failed |
|
422 |
croak($commit_error) if $commit_error; |
|
423 |
} |
|
424 |
} |
|
add various thins
|
425 | |
add methods
|
426 |
# Get last insert id |
427 |
sub last_insert_id { |
|
428 |
my $self = shift; |
|
429 |
|
|
430 |
# Not connected |
|
431 |
croak("Not yet connect to database") |
|
432 |
unless $self->connected; |
|
433 |
|
|
434 |
return $self->dbh->last_insert_id(@_); |
|
435 |
} |
|
436 | ||
437 |
# Insert |
|
438 |
sub insert { |
|
add tests
|
439 |
my ($self, $table, $insert_params, $query_edit_cb) = @_; |
add tests
|
440 |
$table ||= ''; |
add methods
|
441 |
$insert_params ||= {}; |
442 |
|
|
443 |
# Insert keys |
|
444 |
my @insert_keys = keys %$insert_params; |
|
445 |
|
|
446 |
# Not exists insert keys |
|
add tests
|
447 |
croak("Key-value pairs for insert must be specified to 'insert' second argument") |
add methods
|
448 |
unless @insert_keys; |
449 |
|
|
450 |
# Templte for insert |
|
451 |
my $template = "insert into $table {insert " . join(' ', @insert_keys) . '}'; |
|
452 |
|
|
453 |
# Create query |
|
454 |
my $query = $self->create_query($template); |
|
455 |
|
|
add tests
|
456 |
# Query edit callback must be code reference |
457 |
croak("Query edit callback must be code reference") |
|
458 |
if $query_edit_cb && ref $query_edit_cb ne 'CODE'; |
|
add methods
|
459 |
|
add tests
|
460 |
# Query edit if need |
461 |
$query_edit_cb->($query) if $query_edit_cb; |
|
add methods
|
462 |
|
463 |
# Execute query |
|
464 |
my $ret_val = $self->execute($query, $insert_params); |
|
465 |
|
|
466 |
return $ret_val; |
|
467 |
} |
|
468 | ||
add tests
|
469 |
# Update |
add methods
|
470 |
sub update { |
471 |
my ($self, $table, $update_params, |
|
add tests
|
472 |
$where_params, $query_edit_cb, $options) = @_; |
add methods
|
473 |
|
add tests
|
474 |
$table ||= ''; |
add methods
|
475 |
$update_params ||= {}; |
476 |
$where_params ||= {}; |
|
477 |
|
|
478 |
# Update keys |
|
add tests
|
479 |
my @update_keys = keys %$update_params; |
add methods
|
480 |
|
481 |
# Not exists update kyes |
|
add tests
|
482 |
croak("Key-value pairs for update must be specified to 'update' second argument") |
add methods
|
483 |
unless @update_keys; |
484 |
|
|
485 |
# Where keys |
|
486 |
my @where_keys = keys %$where_params; |
|
487 |
|
|
488 |
# Not exists where keys |
|
add tests
|
489 |
croak("Key-value pairs for where clause must be specified to 'update' third argument") |
add methods
|
490 |
if !@where_keys && !$options->{allow_update_all}; |
491 |
|
|
492 |
# Update clause |
|
493 |
my $update_clause = '{update ' . join(' ', @update_keys) . '}'; |
|
494 |
|
|
495 |
# Where clause |
|
add tests
|
496 |
my $where_clause = ''; |
497 |
if (@where_keys) { |
|
498 |
$where_clause = 'where '; |
|
499 |
foreach my $where_key (@where_keys) { |
|
500 |
$where_clause .= "{= $where_key} && "; |
|
501 |
} |
|
502 |
$where_clause =~ s/ && $//; |
|
add methods
|
503 |
} |
504 |
|
|
505 |
# Template for update |
|
506 |
my $template = "update $table $update_clause $where_clause"; |
|
507 |
|
|
508 |
# Create query |
|
509 |
my $query = $self->create_query($template); |
|
510 |
|
|
add tests
|
511 |
# Query edit callback must be code reference |
512 |
croak("Query edit callback must be code reference") |
|
513 |
if $query_edit_cb && ref $query_edit_cb ne 'CODE'; |
|
add methods
|
514 |
|
add tests
|
515 |
# Query edit if need |
516 |
$query_edit_cb->($query) if $query_edit_cb; |
|
add methods
|
517 |
|
518 |
# Rearrange parammeters |
|
519 |
my $params = {'#update' => $update_params, %$where_params}; |
|
520 |
|
|
521 |
# Execute query |
|
522 |
my $ret_val = $self->execute($query, $params); |
|
523 |
|
|
524 |
return $ret_val; |
|
525 |
} |
|
526 | ||
527 |
# Update all rows |
|
528 |
sub update_all { |
|
add tests
|
529 |
my ($self, $table, $update_params, $query_edit_cb) = @_; |
add methods
|
530 |
|
add tests
|
531 |
return $self->update($table, $update_params, {}, $query_edit_cb, |
add methods
|
532 |
{allow_update_all => 1}); |
533 |
} |
|
534 | ||
535 |
# Delete |
|
536 |
sub delete { |
|
add tests
|
537 |
my ($self, $table, $where_params, $query_edit_cb, $options) = @_; |
add tests
|
538 |
$table ||= ''; |
add methods
|
539 |
$where_params ||= {}; |
540 |
|
|
541 |
# Where keys |
|
542 |
my @where_keys = keys %$where_params; |
|
543 |
|
|
544 |
# Not exists where keys |
|
add tests
|
545 |
croak("Key-value pairs for where clause must be specified to 'delete' second argument") |
546 |
if !@where_keys && !$options->{allow_delete_all}; |
|
add methods
|
547 |
|
548 |
# Where clause |
|
add tests
|
549 |
my $where_clause = ''; |
550 |
if (@where_keys) { |
|
551 |
$where_clause = 'where '; |
|
552 |
foreach my $where_key (@where_keys) { |
|
553 |
$where_clause .= "{= $where_key} && "; |
|
554 |
} |
|
555 |
$where_clause =~ s/ && $//; |
|
add methods
|
556 |
} |
557 |
|
|
558 |
# Template for delete |
|
559 |
my $template = "delete from $table $where_clause"; |
|
560 |
|
|
561 |
# Create query |
|
562 |
my $query = $self->create_query($template); |
|
563 |
|
|
add tests
|
564 |
# Query edit callback must be code reference |
565 |
croak("Query edit callback must be code reference") |
|
566 |
if $query_edit_cb && ref $query_edit_cb ne 'CODE'; |
|
add methods
|
567 |
|
add tests
|
568 |
# Query edit if need |
569 |
$query_edit_cb->($query) if $query_edit_cb; |
|
add methods
|
570 |
|
571 |
# Execute query |
|
572 |
my $ret_val = $self->execute($query, $where_params); |
|
573 |
|
|
574 |
return $ret_val; |
|
575 |
} |
|
576 | ||
577 |
# Delete all rows |
|
578 |
sub delete_all { |
|
add tests
|
579 |
my ($self, $table) = @_; |
580 |
return $self->delete($table, {}, undef, {allow_delete_all => 1}); |
|
add methods
|
581 |
} |
582 | ||
add select
|
583 |
sub _select_usage { return << 'EOS' } |
584 |
Usage select : |
|
585 |
$dbi->select( |
|
586 |
$table, # must be string |
|
587 |
[@$columns], # must be array reference. this is optional |
|
588 |
{%$where_params}, # must be hash reference. this is optional |
|
589 |
$append_statement, # must be string. this is optional |
|
590 |
$query_edit_callback # must be code reference. this is optional |
|
591 |
); |
|
592 |
EOS |
|
593 | ||
594 |
sub select { |
|
595 |
my $self = shift; |
|
596 |
|
|
597 |
# Check argument |
|
598 |
croak($self->_select_usage) unless @_; |
|
599 |
|
|
600 |
# Arguments |
|
601 |
my $tables = shift || ''; |
|
602 |
$tables = [$tables] unless ref $tables; |
|
603 |
|
|
604 |
my $columns = ref $_[0] eq 'ARRAY' ? shift : []; |
|
605 |
my $where_params = ref $_[0] eq 'HASH' ? shift : {}; |
|
606 |
my $append_statement = shift unless ref $_[0] || ''; |
|
607 |
my $query_edit_cb = shift if ref $_[0] eq 'CODE'; |
|
608 |
|
|
609 |
# Check rest argument |
|
610 |
croak($self->_select_usage) unless @_; |
|
611 |
|
|
612 |
# SQL template for select statement |
|
613 |
my $template = 'select '; |
|
614 |
|
|
615 |
# Join column clause |
|
616 |
if (@$columns) { |
|
617 |
foreach my $column (@$columns) { |
|
618 |
$template .= "$column, "; |
|
619 |
} |
|
620 |
$template .= s/, $/ /; |
|
621 |
} |
|
622 |
else { |
|
623 |
$template .= '* '; |
|
624 |
} |
|
625 |
|
|
626 |
# Join table |
|
627 |
foreach my $table (@$tables) { |
|
628 |
$template .= "$table, "; |
|
629 |
} |
|
630 |
$template =~ s/, / /; |
|
631 |
|
|
632 |
# Where clause keys |
|
633 |
my @where_keys = keys %$where_params; |
|
634 |
|
|
635 |
# Join where clause |
|
636 |
if (@where_keys) { |
|
637 |
$template .= 'where '; |
|
638 |
foreach my $where_key (@where_keys) { |
|
639 |
$template .= "{= $where_key} && "; |
|
640 |
} |
|
641 |
} |
|
642 |
$template =~ s/ && $//; |
|
643 |
|
|
644 |
# Append something to last of statement |
|
645 |
if ($append_statement =~ s/^where //) { |
|
646 |
if (@where_keys) { |
|
647 |
$template .= " && $append_statement"; |
|
648 |
} |
|
649 |
else { |
|
650 |
$template .= " where $append_statement"; |
|
651 |
} |
|
652 |
} |
|
653 |
else { |
|
654 |
$template .= " $append_statement"; |
|
655 |
} |
|
656 |
|
|
657 |
# Create query |
|
658 |
my $query = $self->create_query($template); |
|
659 |
|
|
660 |
# Query edit |
|
661 |
$query_edit_cb->($query) if $query_edit_cb; |
|
662 |
|
|
663 |
# Execute query |
|
664 |
my $result = $self->execute($query, $where_params); |
|
665 |
|
|
666 |
return $result; |
|
667 |
} |
|
668 | ||
add query cache system
|
669 |
sub _query_caches : ClassAttr { type => 'hash', |
670 |
auto_build => sub {shift->_query_caches({}) } } |
|
671 |
|
|
672 |
sub _query_cache_keys : ClassAttr { type => 'array', |
|
673 |
auto_build => sub {shift->_query_cache_keys([])} } |
|
674 |
|
|
675 |
sub query_cache_max : ClassAttr { auto_build => sub {shift->query_cache_max(50)} } |
|
676 | ||
677 |
# Add query cahce |
|
678 |
sub _add_query_cache { |
|
679 |
my ($class, $template, $query) = @_; |
|
680 |
my $query_cache_keys = $class->_query_cache_keys; |
|
681 |
my $query_caches = $class->_query_caches; |
|
682 |
|
|
683 |
return $class if $query_caches->{$template}; |
|
684 |
|
|
685 |
$query_caches->{$template} = $query; |
|
686 |
push @$query_cache_keys, $template; |
|
687 |
|
|
688 |
my $overflow = @$query_cache_keys - $class->query_cache_max; |
|
689 |
|
|
690 |
for (my $i = 0; $i < $overflow; $i++) { |
|
691 |
my $template = shift @$query_cache_keys; |
|
692 |
delete $query_caches->{$template}; |
|
693 |
} |
|
694 |
|
|
695 |
return $class; |
|
696 |
} |
|
add methods
|
697 | |
add test
|
698 |
Object::Simple->build_class; |
699 | ||
first commit
|
700 |
=head1 NAME |
701 | ||
add test
|
702 |
DBI::Custom - Customizable simple DBI |
first commit
|
703 | |
704 |
=head1 VERSION |
|
705 | ||
add test
|
706 |
Version 0.0101 |
first commit
|
707 | |
cleanup
|
708 |
=head1 CAUTION |
709 | ||
710 |
This module is now experimental stage. |
|
711 | ||
712 |
I want you to try this module |
|
713 |
because I want this module stable, and not to damage your DB data by this module bug. |
|
714 | ||
715 |
Please tell me bug if you find |
|
first commit
|
716 | |
717 |
=head1 SYNOPSIS |
|
718 | ||
add test
|
719 |
my $dbi = DBI::Custom->new; |
add various thins
|
720 |
|
721 |
my $query = $dbi->create_query($template); |
|
722 |
$dbi->execute($query); |
|
first commit
|
723 | |
update document
|
724 |
=head1 CLASS-OBJECT ACCESSORS |
first commit
|
725 | |
update document
|
726 |
=head2 user |
727 | ||
728 |
# Set and get database user name |
|
729 |
$self = $dbi->user($user); |
|
730 |
$user = $dbi->user; |
|
731 |
|
|
732 |
# Sample |
|
733 |
$dbi->user('taro'); |
|
734 | ||
735 |
=head2 password |
|
736 | ||
737 |
# Set and get database password |
|
738 |
$self = $dbi->password($password); |
|
739 |
$password = $dbi->password; |
|
740 |
|
|
741 |
# Sample |
|
742 |
$dbi->password('lkj&le`@s'); |
|
743 | ||
744 |
=head2 data_source |
|
745 | ||
746 |
# Set and get database data source |
|
747 |
$self = $dbi->data_source($data_soruce); |
|
748 |
$data_source = $dbi->data_source; |
|
749 |
|
|
750 |
# Sample(SQLite) |
|
751 |
$dbi->data_source(dbi:SQLite:dbname=$database); |
|
752 |
|
|
753 |
# Sample(MySQL); |
|
754 |
$dbi->data_source("dbi:mysql:dbname=$database"); |
|
755 |
|
|
756 |
# Sample(PostgreSQL) |
|
757 |
$dbi->data_source("dbi:Pg:dbname=$database"); |
|
cleanup
|
758 |
|
759 |
=head2 database |
|
760 | ||
761 |
# Set and get database name |
|
762 |
$self = $dbi->database($database); |
|
763 |
$database = $dbi->database; |
|
update document
|
764 | |
cleanup
|
765 |
=head2 dbi_options |
update document
|
766 | |
767 |
# Set and get DBI option |
|
cleanup
|
768 |
$self = $dbi->dbi_options({$options => $value, ...}); |
769 |
$dbi_options = $dbi->dbi_options; |
|
update document
|
770 | |
771 |
# Sample |
|
cleanup
|
772 |
$dbi->dbi_options({PrintError => 0, RaiseError => 1}); |
update document
|
773 | |
cleanup
|
774 |
dbi_options is used when you connect database by using connect. |
update document
|
775 | |
add prepare
|
776 |
=head2 prepare |
777 | ||
778 |
$sth = $dbi->prepare($sql); |
|
779 | ||
780 |
This method is same as DBI::prepare |
|
781 | ||
782 |
=head2 do |
|
783 | ||
784 |
$dbi->do($sql, @bind_values); |
|
785 | ||
786 |
This method is same as DBI::do |
|
787 | ||
update document
|
788 |
=head2 sql_template |
789 | ||
790 |
# Set and get SQL::Template object |
|
791 |
$self = $dbi->sql_template($sql_template); |
|
792 |
$sql_template = $dbi->sql_template; |
|
793 |
|
|
794 |
# Sample |
|
795 |
$dbi->sql_template(DBI::Cutom::SQL::Template->new); |
|
796 | ||
797 |
=head2 filters |
|
798 | ||
799 |
# Set and get filters |
|
800 |
$self = $dbi->filters($filters); |
|
801 |
$filters = $dbi->filters; |
|
first commit
|
802 | |
add test
|
803 |
=head2 bind_filter |
first commit
|
804 | |
update document
|
805 |
# Set and get binding filter |
806 |
$self = $dbi->bind_filter($bind_filter); |
|
807 |
$bind_filter = $dbi->bind_filter |
|
first commit
|
808 | |
update document
|
809 |
# Sample |
810 |
$dbi->bind_filter($self->filters->{default_bind_filter}); |
|
811 |
|
|
first commit
|
812 | |
update document
|
813 |
you can get DBI database handle if you need. |
first commit
|
814 | |
add test
|
815 |
=head2 fetch_filter |
first commit
|
816 | |
update document
|
817 |
# Set and get Fetch filter |
818 |
$self = $dbi->fetch_filter($fetch_filter); |
|
819 |
$fetch_filter = $dbi->fetch_filter; |
|
first commit
|
820 | |
update document
|
821 |
# Sample |
822 |
$dbi->fetch_filter($self->filters->{default_fetch_filter}); |
|
add test
|
823 | |
add no_bind_filters
|
824 |
=head2 no_bind_filters |
825 | ||
826 |
# Set and get no filter keys when binding |
|
827 |
$self = $dbi->no_bind_filters($no_bind_filters); |
|
828 |
$no_bind_filters = $dbi->no_bind_filters; |
|
829 | ||
830 |
=head2 no_fetch_filters |
|
cleanup
|
831 | |
add no_bind_filters
|
832 |
# Set and get no filter keys when fetching |
833 |
$self = $dbi->no_fetch_filters($no_fetch_filters); |
|
834 |
$no_fetch_filters = $dbi->no_fetch_filters; |
|
cleanup
|
835 | |
update document
|
836 |
=head2 result_class |
first commit
|
837 | |
update document
|
838 |
# Set and get resultset class |
839 |
$self = $dbi->result_class($result_class); |
|
840 |
$result_class = $dbi->result_class; |
|
841 |
|
|
842 |
# Sample |
|
843 |
$dbi->result_class('DBI::Custom::Result'); |
|
add test
|
844 | |
update document
|
845 |
=head2 dbh |
add test
|
846 | |
update document
|
847 |
# Get database handle |
848 |
$dbh = $self->dbh; |
|
add test
|
849 | |
update document
|
850 |
=head1 METHODS |
add tests
|
851 | |
update document
|
852 |
=head2 connect |
853 | ||
854 |
# Connect to database |
|
855 |
$self = $dbi->connect; |
|
856 |
|
|
857 |
# Sample |
|
858 |
$dbi = DBI::Custom->new(user => 'taro', password => 'lji8(', |
|
859 |
data_soruce => "dbi:mysql:dbname=$database"); |
|
860 |
$dbi->connect; |
|
add tests
|
861 | |
862 |
=head2 disconnect |
|
863 | ||
update document
|
864 |
# Disconnect database |
865 |
$dbi->disconnect; |
|
866 | ||
867 |
If database is already disconnected, this method do noting. |
|
868 | ||
add tests
|
869 |
=head2 reconnect |
870 | ||
update document
|
871 |
# Reconnect |
872 |
$dbi->reconnect; |
|
873 | ||
874 |
=head2 connected |
|
875 | ||
876 |
# Check connected |
|
877 |
$dbi->connected |
|
878 | ||
879 |
=head2 add_filter |
|
880 | ||
881 |
# Add filter (hash ref or hash can be recieve) |
|
882 |
$self = $dbi->add_filter({$filter_name => $filter, ...}); |
|
883 |
$self = $dbi->add_filter($filetr_name => $filter, ...); |
|
884 |
|
|
885 |
# Sample |
|
886 |
$dbi->add_filter( |
|
887 |
decode_utf8 => sub { |
|
cleanup
|
888 |
my ($key, $value, $table, $column) = @_; |
update document
|
889 |
return Encode::decode('UTF-8', $value); |
890 |
}, |
|
891 |
datetime_to_string => sub { |
|
cleanup
|
892 |
my ($key, $value, $table, $column) = @_; |
update document
|
893 |
return $value->strftime('%Y-%m-%d %H:%M:%S') |
894 |
}, |
|
895 |
default_bind_filter => sub { |
|
cleanup
|
896 |
my ($key, $value, $table, $column) = @_; |
update document
|
897 |
if (ref $value eq 'Time::Piece') { |
cleanup
|
898 |
return $dbi->filters->{datetime_to_string}->($value); |
update document
|
899 |
} |
900 |
else { |
|
cleanup
|
901 |
return $dbi->filters->{decode_utf8}->($value); |
update document
|
902 |
} |
903 |
}, |
|
904 |
|
|
905 |
encode_utf8 => sub { |
|
cleanup
|
906 |
my ($key, $value) = @_; |
update document
|
907 |
return Encode::encode('UTF-8', $value); |
908 |
}, |
|
909 |
string_to_datetime => sub { |
|
cleanup
|
910 |
my ($key, $value) = @_; |
update document
|
911 |
return DateTime::Format::MySQL->parse_datetime($value); |
912 |
}, |
|
913 |
default_fetch_filter => sub { |
|
cleanup
|
914 |
my ($key, $value, $type, $sth, $i) = @_; |
update document
|
915 |
if ($type eq 'DATETIME') { |
cleanup
|
916 |
return $dbi->filters->{string_to_datetime}->($value); |
update document
|
917 |
} |
918 |
else { |
|
cleanup
|
919 |
return $dbi->filters->{encode_utf8}->($value); |
update document
|
920 |
} |
921 |
} |
|
922 |
); |
|
923 | ||
924 |
add_filter add filter to filters |
|
add tests
|
925 | |
cleanup
|
926 |
=head2 create_query |
927 |
|
|
928 |
# Create Query object from SQL template |
|
929 |
my $query = $dbi->create_query($template); |
|
930 |
|
|
931 |
=head2 execute |
|
update document
|
932 | |
933 |
# Parse SQL template and execute SQL |
|
cleanup
|
934 |
$result = $dbi->query($query, $params); |
935 |
$result = $dbi->query($template, $params); # Shorcut |
|
update document
|
936 |
|
937 |
# Sample |
|
938 |
$result = $dbi->query("select * from authors where {= name} && {= age}", |
|
939 |
{author => 'taro', age => 19}); |
|
940 |
|
|
941 |
while (my @row = $result->fetch) { |
|
942 |
# do something |
|
943 |
} |
|
944 | ||
945 |
See also L<DBI::Custom::SQL::Template> |
|
946 | ||
cleanup
|
947 |
=head2 run_tranzaction |
first commit
|
948 | |
update document
|
949 |
# Run tranzaction |
950 |
$dbi->run_tranzaction(sub { |
|
951 |
# do something |
|
952 |
}); |
|
first commit
|
953 | |
update document
|
954 |
If tranzaction is success, commit is execute. |
955 |
If tranzation is died, rollback is execute. |
|
first commit
|
956 | |
add methods
|
957 |
=head2 insert |
958 | ||
959 |
# Insert |
|
960 |
$dbi->insert($table, $insert_values); |
|
961 |
|
|
962 |
# Sample |
|
963 |
$dbi->insert('books', {title => 'Perl', author => 'Taro'}); |
|
964 | ||
965 |
=head2 update |
|
966 | ||
967 |
# Update |
|
968 |
$dbi->update($table, $update_values, $where); |
|
969 |
|
|
970 |
# Sample |
|
971 |
$dbi->update('books', {title => 'Perl', author => 'Taro'}, {id => 5}); |
|
972 | ||
973 |
=head2 update_all |
|
974 | ||
975 |
# Update all rows |
|
976 |
$dbi->update($table, $updat_values); |
|
977 | ||
978 |
=head2 delete |
|
979 | ||
980 |
# Delete |
|
981 |
$dbi->delete($table, $where); |
|
982 |
|
|
983 |
# Sample |
|
984 |
$dbi->delete('Books', {id => 5}); |
|
985 | ||
986 |
=head2 delete_all |
|
987 | ||
988 |
# Delete all rows |
|
989 |
$dbi->delete_all($table); |
|
990 | ||
991 |
=head2 last_insert_id |
|
992 | ||
993 |
# Get last insert id |
|
994 |
$last_insert_id = $dbi->last_insert_id; |
|
995 |
|
|
996 |
This method is same as DBI last_insert_id; |
|
997 | ||
add select
|
998 |
=head2 select |
999 |
|
|
1000 |
# Select |
|
1001 |
$dbi->select( |
|
1002 |
$table, # must be string or array; |
|
1003 |
[@$columns], # must be array reference. this is optional |
|
1004 |
{%$where_params}, # must be hash reference. this is optional |
|
1005 |
$append_statement, # must be string. this is optional |
|
1006 |
$query_edit_callback # must be code reference. this is optional |
|
1007 |
); |
|
1008 |
|
|
1009 |
# Sample |
|
1010 |
$dbi->select( |
|
1011 |
'Books', |
|
1012 |
['title', 'author'], |
|
1013 |
{id => 1}, |
|
1014 |
"for update", |
|
1015 |
sub { |
|
1016 |
my $query = shift; |
|
1017 |
$query->bind_filter(sub { |
|
1018 |
# ... |
|
1019 |
}); |
|
1020 |
} |
|
1021 |
); |
|
1022 |
|
|
1023 |
# The way to join multi tables |
|
1024 |
$dbi->select( |
|
1025 |
['table1', 'table2'], |
|
1026 |
['table1.id as table1_id', 'title'], |
|
1027 |
{table1.id => 1}, |
|
1028 |
"where table1.id = table2.id", |
|
1029 |
); |
|
1030 | ||
add query cache system
|
1031 |
=head1 Class Accessors |
1032 | ||
1033 |
=head2 query_cache_max |
|
1034 | ||
1035 |
# Max query cache count |
|
1036 |
$class = $class->query_cache_max($query_cache_max); |
|
1037 |
$query_cache_max = $class->query_cache_max; |
|
1038 |
|
|
1039 |
# Sample |
|
1040 |
DBI::Custom->query_cache_max(50); |
|
1041 | ||
add tests
|
1042 |
=head1 CAUTION |
1043 | ||
1044 |
DBI::Custom have DIB object internal. |
|
1045 |
This module is work well in the following DBI condition. |
|
1046 | ||
1047 |
1. AutoCommit is true |
|
1048 |
2. RaiseError is true |
|
1049 | ||
1050 |
By default, Both AutoCommit and RaiseError is true. |
|
1051 |
You must not change these mode not to damage your data. |
|
1052 | ||
1053 |
If you change these mode, |
|
1054 |
you cannot get correct error message, |
|
1055 |
or run_tranzaction may fail. |
|
1056 | ||
add various
|
1057 |
=head1 AUTHOR |
first commit
|
1058 | |
add various
|
1059 |
Yuki Kimoto, C<< <kimoto.yuki at gmail.com> >> |
first commit
|
1060 | |
add tests
|
1061 |
Github L<http://github.com/yuki-kimoto> |
1062 | ||
first commit
|
1063 |
=head1 COPYRIGHT & LICENSE |
1064 | ||
1065 |
Copyright 2009 Yuki Kimoto, all rights reserved. |
|
1066 | ||
1067 |
This program is free software; you can redistribute it and/or modify it |
|
1068 |
under the same terms as Perl itself. |
|
1069 | ||
1070 |
=cut |