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' } |
add select
|
584 |
Your select arguments is wrong. |
585 |
select usage: |
|
add select
|
586 |
$dbi->select( |
add select
|
587 |
$table, # must be string or array ref |
add select
|
588 |
[@$columns], # must be array reference. this is optional |
589 |
{%$where_params}, # must be hash reference. this is optional |
|
590 |
$append_statement, # must be string. this is optional |
|
591 |
$query_edit_callback # must be code reference. this is optional |
|
592 |
); |
|
593 |
EOS |
|
594 | ||
595 |
sub select { |
|
596 |
my $self = shift; |
|
597 |
|
|
598 |
# Check argument |
|
599 |
croak($self->_select_usage) unless @_; |
|
600 |
|
|
601 |
# Arguments |
|
602 |
my $tables = shift || ''; |
|
603 |
$tables = [$tables] unless ref $tables; |
|
604 |
|
|
605 |
my $columns = ref $_[0] eq 'ARRAY' ? shift : []; |
|
606 |
my $where_params = ref $_[0] eq 'HASH' ? shift : {}; |
|
add tests
|
607 |
my $append_statement = $_[0] && !ref $_[0] ? shift : ''; |
add select
|
608 |
my $query_edit_cb = shift if ref $_[0] eq 'CODE'; |
609 |
|
|
610 |
# Check rest argument |
|
add tests
|
611 |
croak($self->_select_usage) if @_; |
add select
|
612 |
|
613 |
# SQL template for select statement |
|
614 |
my $template = 'select '; |
|
615 |
|
|
616 |
# Join column clause |
|
617 |
if (@$columns) { |
|
618 |
foreach my $column (@$columns) { |
|
619 |
$template .= "$column, "; |
|
620 |
} |
|
add tests
|
621 |
$template =~ s/, $/ /; |
add select
|
622 |
} |
623 |
else { |
|
624 |
$template .= '* '; |
|
625 |
} |
|
626 |
|
|
627 |
# Join table |
|
add tests
|
628 |
$template .= 'from '; |
add select
|
629 |
foreach my $table (@$tables) { |
630 |
$template .= "$table, "; |
|
631 |
} |
|
632 |
$template =~ s/, / /; |
|
633 |
|
|
634 |
# Where clause keys |
|
635 |
my @where_keys = keys %$where_params; |
|
636 |
|
|
637 |
# Join where clause |
|
638 |
if (@where_keys) { |
|
639 |
$template .= 'where '; |
|
640 |
foreach my $where_key (@where_keys) { |
|
641 |
$template .= "{= $where_key} && "; |
|
642 |
} |
|
643 |
} |
|
644 |
$template =~ s/ && $//; |
|
645 |
|
|
646 |
# Append something to last of statement |
|
647 |
if ($append_statement =~ s/^where //) { |
|
648 |
if (@where_keys) { |
|
649 |
$template .= " && $append_statement"; |
|
650 |
} |
|
651 |
else { |
|
652 |
$template .= " where $append_statement"; |
|
653 |
} |
|
654 |
} |
|
655 |
else { |
|
656 |
$template .= " $append_statement"; |
|
657 |
} |
|
658 |
|
|
659 |
# Create query |
|
660 |
my $query = $self->create_query($template); |
|
661 |
|
|
662 |
# Query edit |
|
663 |
$query_edit_cb->($query) if $query_edit_cb; |
|
664 |
|
|
665 |
# Execute query |
|
666 |
my $result = $self->execute($query, $where_params); |
|
667 |
|
|
668 |
return $result; |
|
669 |
} |
|
670 | ||
add query cache system
|
671 |
sub _query_caches : ClassAttr { type => 'hash', |
672 |
auto_build => sub {shift->_query_caches({}) } } |
|
673 |
|
|
674 |
sub _query_cache_keys : ClassAttr { type => 'array', |
|
675 |
auto_build => sub {shift->_query_cache_keys([])} } |
|
676 |
|
|
677 |
sub query_cache_max : ClassAttr { auto_build => sub {shift->query_cache_max(50)} } |
|
678 | ||
679 |
# Add query cahce |
|
680 |
sub _add_query_cache { |
|
681 |
my ($class, $template, $query) = @_; |
|
682 |
my $query_cache_keys = $class->_query_cache_keys; |
|
683 |
my $query_caches = $class->_query_caches; |
|
684 |
|
|
685 |
return $class if $query_caches->{$template}; |
|
686 |
|
|
687 |
$query_caches->{$template} = $query; |
|
688 |
push @$query_cache_keys, $template; |
|
689 |
|
|
690 |
my $overflow = @$query_cache_keys - $class->query_cache_max; |
|
691 |
|
|
692 |
for (my $i = 0; $i < $overflow; $i++) { |
|
693 |
my $template = shift @$query_cache_keys; |
|
694 |
delete $query_caches->{$template}; |
|
695 |
} |
|
696 |
|
|
697 |
return $class; |
|
698 |
} |
|
add methods
|
699 | |
add test
|
700 |
Object::Simple->build_class; |
701 | ||
first commit
|
702 |
=head1 NAME |
703 | ||
add test
|
704 |
DBI::Custom - Customizable simple DBI |
first commit
|
705 | |
706 |
=head1 VERSION |
|
707 | ||
add test
|
708 |
Version 0.0101 |
first commit
|
709 | |
cleanup
|
710 |
=head1 CAUTION |
711 | ||
712 |
This module is now experimental stage. |
|
713 | ||
714 |
I want you to try this module |
|
715 |
because I want this module stable, and not to damage your DB data by this module bug. |
|
716 | ||
717 |
Please tell me bug if you find |
|
first commit
|
718 | |
719 |
=head1 SYNOPSIS |
|
720 | ||
add test
|
721 |
my $dbi = DBI::Custom->new; |
add various thins
|
722 |
|
723 |
my $query = $dbi->create_query($template); |
|
724 |
$dbi->execute($query); |
|
first commit
|
725 | |
update document
|
726 |
=head1 CLASS-OBJECT ACCESSORS |
first commit
|
727 | |
update document
|
728 |
=head2 user |
729 | ||
730 |
# Set and get database user name |
|
731 |
$self = $dbi->user($user); |
|
732 |
$user = $dbi->user; |
|
733 |
|
|
734 |
# Sample |
|
735 |
$dbi->user('taro'); |
|
736 | ||
737 |
=head2 password |
|
738 | ||
739 |
# Set and get database password |
|
740 |
$self = $dbi->password($password); |
|
741 |
$password = $dbi->password; |
|
742 |
|
|
743 |
# Sample |
|
744 |
$dbi->password('lkj&le`@s'); |
|
745 | ||
746 |
=head2 data_source |
|
747 | ||
748 |
# Set and get database data source |
|
749 |
$self = $dbi->data_source($data_soruce); |
|
750 |
$data_source = $dbi->data_source; |
|
751 |
|
|
752 |
# Sample(SQLite) |
|
753 |
$dbi->data_source(dbi:SQLite:dbname=$database); |
|
754 |
|
|
755 |
# Sample(MySQL); |
|
756 |
$dbi->data_source("dbi:mysql:dbname=$database"); |
|
757 |
|
|
758 |
# Sample(PostgreSQL) |
|
759 |
$dbi->data_source("dbi:Pg:dbname=$database"); |
|
cleanup
|
760 |
|
761 |
=head2 database |
|
762 | ||
763 |
# Set and get database name |
|
764 |
$self = $dbi->database($database); |
|
765 |
$database = $dbi->database; |
|
update document
|
766 | |
cleanup
|
767 |
=head2 dbi_options |
update document
|
768 | |
769 |
# Set and get DBI option |
|
cleanup
|
770 |
$self = $dbi->dbi_options({$options => $value, ...}); |
771 |
$dbi_options = $dbi->dbi_options; |
|
update document
|
772 | |
773 |
# Sample |
|
cleanup
|
774 |
$dbi->dbi_options({PrintError => 0, RaiseError => 1}); |
update document
|
775 | |
cleanup
|
776 |
dbi_options is used when you connect database by using connect. |
update document
|
777 | |
add prepare
|
778 |
=head2 prepare |
779 | ||
780 |
$sth = $dbi->prepare($sql); |
|
781 | ||
782 |
This method is same as DBI::prepare |
|
783 | ||
784 |
=head2 do |
|
785 | ||
786 |
$dbi->do($sql, @bind_values); |
|
787 | ||
788 |
This method is same as DBI::do |
|
789 | ||
update document
|
790 |
=head2 sql_template |
791 | ||
792 |
# Set and get SQL::Template object |
|
793 |
$self = $dbi->sql_template($sql_template); |
|
794 |
$sql_template = $dbi->sql_template; |
|
795 |
|
|
796 |
# Sample |
|
797 |
$dbi->sql_template(DBI::Cutom::SQL::Template->new); |
|
798 | ||
799 |
=head2 filters |
|
800 | ||
801 |
# Set and get filters |
|
802 |
$self = $dbi->filters($filters); |
|
803 |
$filters = $dbi->filters; |
|
first commit
|
804 | |
add test
|
805 |
=head2 bind_filter |
first commit
|
806 | |
update document
|
807 |
# Set and get binding filter |
808 |
$self = $dbi->bind_filter($bind_filter); |
|
809 |
$bind_filter = $dbi->bind_filter |
|
first commit
|
810 | |
update document
|
811 |
# Sample |
812 |
$dbi->bind_filter($self->filters->{default_bind_filter}); |
|
813 |
|
|
first commit
|
814 | |
update document
|
815 |
you can get DBI database handle if you need. |
first commit
|
816 | |
add test
|
817 |
=head2 fetch_filter |
first commit
|
818 | |
update document
|
819 |
# Set and get Fetch filter |
820 |
$self = $dbi->fetch_filter($fetch_filter); |
|
821 |
$fetch_filter = $dbi->fetch_filter; |
|
first commit
|
822 | |
update document
|
823 |
# Sample |
824 |
$dbi->fetch_filter($self->filters->{default_fetch_filter}); |
|
add test
|
825 | |
add no_bind_filters
|
826 |
=head2 no_bind_filters |
827 | ||
828 |
# Set and get no filter keys when binding |
|
829 |
$self = $dbi->no_bind_filters($no_bind_filters); |
|
830 |
$no_bind_filters = $dbi->no_bind_filters; |
|
831 | ||
832 |
=head2 no_fetch_filters |
|
cleanup
|
833 | |
add no_bind_filters
|
834 |
# Set and get no filter keys when fetching |
835 |
$self = $dbi->no_fetch_filters($no_fetch_filters); |
|
836 |
$no_fetch_filters = $dbi->no_fetch_filters; |
|
cleanup
|
837 | |
update document
|
838 |
=head2 result_class |
first commit
|
839 | |
update document
|
840 |
# Set and get resultset class |
841 |
$self = $dbi->result_class($result_class); |
|
842 |
$result_class = $dbi->result_class; |
|
843 |
|
|
844 |
# Sample |
|
845 |
$dbi->result_class('DBI::Custom::Result'); |
|
add test
|
846 | |
update document
|
847 |
=head2 dbh |
add test
|
848 | |
update document
|
849 |
# Get database handle |
850 |
$dbh = $self->dbh; |
|
add test
|
851 | |
update document
|
852 |
=head1 METHODS |
add tests
|
853 | |
update document
|
854 |
=head2 connect |
855 | ||
856 |
# Connect to database |
|
857 |
$self = $dbi->connect; |
|
858 |
|
|
859 |
# Sample |
|
860 |
$dbi = DBI::Custom->new(user => 'taro', password => 'lji8(', |
|
861 |
data_soruce => "dbi:mysql:dbname=$database"); |
|
862 |
$dbi->connect; |
|
add tests
|
863 | |
864 |
=head2 disconnect |
|
865 | ||
update document
|
866 |
# Disconnect database |
867 |
$dbi->disconnect; |
|
868 | ||
869 |
If database is already disconnected, this method do noting. |
|
870 | ||
add tests
|
871 |
=head2 reconnect |
872 | ||
update document
|
873 |
# Reconnect |
874 |
$dbi->reconnect; |
|
875 | ||
876 |
=head2 connected |
|
877 | ||
878 |
# Check connected |
|
879 |
$dbi->connected |
|
880 | ||
881 |
=head2 add_filter |
|
882 | ||
883 |
# Add filter (hash ref or hash can be recieve) |
|
884 |
$self = $dbi->add_filter({$filter_name => $filter, ...}); |
|
885 |
$self = $dbi->add_filter($filetr_name => $filter, ...); |
|
886 |
|
|
887 |
# Sample |
|
888 |
$dbi->add_filter( |
|
889 |
decode_utf8 => sub { |
|
cleanup
|
890 |
my ($key, $value, $table, $column) = @_; |
update document
|
891 |
return Encode::decode('UTF-8', $value); |
892 |
}, |
|
893 |
datetime_to_string => sub { |
|
cleanup
|
894 |
my ($key, $value, $table, $column) = @_; |
update document
|
895 |
return $value->strftime('%Y-%m-%d %H:%M:%S') |
896 |
}, |
|
897 |
default_bind_filter => sub { |
|
cleanup
|
898 |
my ($key, $value, $table, $column) = @_; |
update document
|
899 |
if (ref $value eq 'Time::Piece') { |
cleanup
|
900 |
return $dbi->filters->{datetime_to_string}->($value); |
update document
|
901 |
} |
902 |
else { |
|
cleanup
|
903 |
return $dbi->filters->{decode_utf8}->($value); |
update document
|
904 |
} |
905 |
}, |
|
906 |
|
|
907 |
encode_utf8 => sub { |
|
cleanup
|
908 |
my ($key, $value) = @_; |
update document
|
909 |
return Encode::encode('UTF-8', $value); |
910 |
}, |
|
911 |
string_to_datetime => sub { |
|
cleanup
|
912 |
my ($key, $value) = @_; |
update document
|
913 |
return DateTime::Format::MySQL->parse_datetime($value); |
914 |
}, |
|
915 |
default_fetch_filter => sub { |
|
cleanup
|
916 |
my ($key, $value, $type, $sth, $i) = @_; |
update document
|
917 |
if ($type eq 'DATETIME') { |
cleanup
|
918 |
return $dbi->filters->{string_to_datetime}->($value); |
update document
|
919 |
} |
920 |
else { |
|
cleanup
|
921 |
return $dbi->filters->{encode_utf8}->($value); |
update document
|
922 |
} |
923 |
} |
|
924 |
); |
|
925 | ||
926 |
add_filter add filter to filters |
|
add tests
|
927 | |
cleanup
|
928 |
=head2 create_query |
929 |
|
|
930 |
# Create Query object from SQL template |
|
931 |
my $query = $dbi->create_query($template); |
|
932 |
|
|
933 |
=head2 execute |
|
update document
|
934 | |
935 |
# Parse SQL template and execute SQL |
|
cleanup
|
936 |
$result = $dbi->query($query, $params); |
937 |
$result = $dbi->query($template, $params); # Shorcut |
|
update document
|
938 |
|
939 |
# Sample |
|
940 |
$result = $dbi->query("select * from authors where {= name} && {= age}", |
|
941 |
{author => 'taro', age => 19}); |
|
942 |
|
|
943 |
while (my @row = $result->fetch) { |
|
944 |
# do something |
|
945 |
} |
|
946 | ||
947 |
See also L<DBI::Custom::SQL::Template> |
|
948 | ||
cleanup
|
949 |
=head2 run_tranzaction |
first commit
|
950 | |
update document
|
951 |
# Run tranzaction |
952 |
$dbi->run_tranzaction(sub { |
|
953 |
# do something |
|
954 |
}); |
|
first commit
|
955 | |
update document
|
956 |
If tranzaction is success, commit is execute. |
957 |
If tranzation is died, rollback is execute. |
|
first commit
|
958 | |
add methods
|
959 |
=head2 insert |
960 | ||
961 |
# Insert |
|
962 |
$dbi->insert($table, $insert_values); |
|
963 |
|
|
964 |
# Sample |
|
965 |
$dbi->insert('books', {title => 'Perl', author => 'Taro'}); |
|
966 | ||
967 |
=head2 update |
|
968 | ||
969 |
# Update |
|
970 |
$dbi->update($table, $update_values, $where); |
|
971 |
|
|
972 |
# Sample |
|
973 |
$dbi->update('books', {title => 'Perl', author => 'Taro'}, {id => 5}); |
|
974 | ||
975 |
=head2 update_all |
|
976 | ||
977 |
# Update all rows |
|
978 |
$dbi->update($table, $updat_values); |
|
979 | ||
980 |
=head2 delete |
|
981 | ||
982 |
# Delete |
|
983 |
$dbi->delete($table, $where); |
|
984 |
|
|
985 |
# Sample |
|
986 |
$dbi->delete('Books', {id => 5}); |
|
987 | ||
988 |
=head2 delete_all |
|
989 | ||
990 |
# Delete all rows |
|
991 |
$dbi->delete_all($table); |
|
992 | ||
993 |
=head2 last_insert_id |
|
994 | ||
995 |
# Get last insert id |
|
996 |
$last_insert_id = $dbi->last_insert_id; |
|
997 |
|
|
998 |
This method is same as DBI last_insert_id; |
|
999 | ||
add select
|
1000 |
=head2 select |
1001 |
|
|
1002 |
# Select |
|
1003 |
$dbi->select( |
|
1004 |
$table, # must be string or array; |
|
1005 |
[@$columns], # must be array reference. this is optional |
|
1006 |
{%$where_params}, # must be hash reference. this is optional |
|
1007 |
$append_statement, # must be string. this is optional |
|
1008 |
$query_edit_callback # must be code reference. this is optional |
|
1009 |
); |
|
1010 |
|
|
1011 |
# Sample |
|
1012 |
$dbi->select( |
|
1013 |
'Books', |
|
1014 |
['title', 'author'], |
|
1015 |
{id => 1}, |
|
1016 |
"for update", |
|
1017 |
sub { |
|
1018 |
my $query = shift; |
|
1019 |
$query->bind_filter(sub { |
|
1020 |
# ... |
|
1021 |
}); |
|
1022 |
} |
|
1023 |
); |
|
1024 |
|
|
1025 |
# The way to join multi tables |
|
1026 |
$dbi->select( |
|
1027 |
['table1', 'table2'], |
|
1028 |
['table1.id as table1_id', 'title'], |
|
1029 |
{table1.id => 1}, |
|
1030 |
"where table1.id = table2.id", |
|
1031 |
); |
|
1032 | ||
add query cache system
|
1033 |
=head1 Class Accessors |
1034 | ||
1035 |
=head2 query_cache_max |
|
1036 | ||
1037 |
# Max query cache count |
|
1038 |
$class = $class->query_cache_max($query_cache_max); |
|
1039 |
$query_cache_max = $class->query_cache_max; |
|
1040 |
|
|
1041 |
# Sample |
|
1042 |
DBI::Custom->query_cache_max(50); |
|
1043 | ||
add tests
|
1044 |
=head1 CAUTION |
1045 | ||
1046 |
DBI::Custom have DIB object internal. |
|
1047 |
This module is work well in the following DBI condition. |
|
1048 | ||
1049 |
1. AutoCommit is true |
|
1050 |
2. RaiseError is true |
|
1051 | ||
1052 |
By default, Both AutoCommit and RaiseError is true. |
|
1053 |
You must not change these mode not to damage your data. |
|
1054 | ||
1055 |
If you change these mode, |
|
1056 |
you cannot get correct error message, |
|
1057 |
or run_tranzaction may fail. |
|
1058 | ||
add various
|
1059 |
=head1 AUTHOR |
first commit
|
1060 | |
add various
|
1061 |
Yuki Kimoto, C<< <kimoto.yuki at gmail.com> >> |
first commit
|
1062 | |
add tests
|
1063 |
Github L<http://github.com/yuki-kimoto> |
1064 | ||
first commit
|
1065 |
=head1 COPYRIGHT & LICENSE |
1066 | ||
1067 |
Copyright 2009 Yuki Kimoto, all rights reserved. |
|
1068 | ||
1069 |
This program is free software; you can redistribute it and/or modify it |
|
1070 |
under the same terms as Perl itself. |
|
1071 | ||
1072 |
=cut |