Revision 451843cf
Von Sven Schöling vor etwa 15 Jahren hinzugefügt
| SL/DBUtils.pm | ||
|---|---|---|
| 9 | 9 |
selectall_hashref_query selectall_array_query |
| 10 | 10 |
selectall_as_map |
| 11 | 11 |
prepare_execute_query prepare_query |
| 12 |
create_sort_spec does_table_exist); |
|
| 12 |
create_sort_spec does_table_exist |
|
| 13 |
add_token); |
|
| 13 | 14 |
|
| 14 | 15 |
sub conv_i {
|
| 15 | 16 |
my ($value, $default) = @_; |
| ... | ... | |
| 280 | 281 |
return $result; |
| 281 | 282 |
} |
| 282 | 283 |
|
| 284 |
# add token to values. |
|
| 285 |
# usage: |
|
| 286 |
# add_token( |
|
| 287 |
# \@where_tokens, |
|
| 288 |
# \@where_values, |
|
| 289 |
# col => 'id', |
|
| 290 |
# val => [ 23, 34, 17 ] |
|
| 291 |
# esc => \&conf_i |
|
| 292 |
# ) |
|
| 293 |
# will append to the given arrays: |
|
| 294 |
# -> 'id IN (?, ?, ?)' |
|
| 295 |
# -> (conv_i(23), conv_i(34), conv_i(17)) |
|
| 296 |
# |
|
| 297 |
# features: |
|
| 298 |
# - don't care if one or multiple values are given. singlewill result in 'col = ?' |
|
| 299 |
# - pass escape routines |
|
| 300 |
# - expand for future method |
|
| 301 |
# - no need to type "push @where_tokens, 'id = ?'" over and over again |
|
| 302 |
sub add_token {
|
|
| 303 |
my $tokens = shift() || []; |
|
| 304 |
my $values = shift() || []; |
|
| 305 |
my %params = @_; |
|
| 306 |
my $col = $params{col};
|
|
| 307 |
my $val = $params{val};
|
|
| 308 |
my $method = $params{method} || '=';
|
|
| 309 |
my $escape = $params{esc} || sub { $_ };
|
|
| 310 |
|
|
| 311 |
$val = [ $val ] unless ref $val eq 'ARRAY'; |
|
| 312 |
|
|
| 313 |
my %escapes = ( |
|
| 314 |
id => \&conv_i, |
|
| 315 |
date => \&conv_date, |
|
| 316 |
); |
|
| 317 |
|
|
| 318 |
my %methods = ( |
|
| 319 |
'=' => sub {
|
|
| 320 |
my $col = shift; |
|
| 321 |
return scalar @_ > 1 ? sprintf '%s IN (%s)', $col, join ', ', ("?") x scalar @_
|
|
| 322 |
: scalar @_ == 1 ? sprintf '%s = ?', $col |
|
| 323 |
: undef; |
|
| 324 |
}, |
|
| 325 |
); |
|
| 326 |
|
|
| 327 |
$method = $methods{$method} || $method;
|
|
| 328 |
$escape = $escapes{$escape} || $escape;
|
|
| 329 |
|
|
| 330 |
my $token = $method->($col, @{ $val });
|
|
| 331 |
my @vals = map { $escape->($_) } @{ $val };
|
|
| 332 |
|
|
| 333 |
return unless $token; |
|
| 334 |
|
|
| 335 |
push @{ $tokens }, $token;
|
|
| 336 |
push @{ $values }, @vals;
|
|
| 337 |
|
|
| 338 |
return ($token, @vals); |
|
| 339 |
} |
|
| 283 | 340 |
|
| 284 | 341 |
1; |
| 285 | 342 |
|
| ... | ... | |
| 293 | 350 |
=head1 SYNOPSIS |
| 294 | 351 |
|
| 295 | 352 |
use DBUtils; |
| 296 |
|
|
| 353 |
|
|
| 297 | 354 |
conv_i($str, $default) |
| 298 | 355 |
conv_date($str) |
| 299 | 356 |
conv_dateq($str) |
| ... | ... | |
| 307 | 364 |
|
| 308 | 365 |
my $all_results_ref = selectall_hashref_query($form, $dbh, $query) |
| 309 | 366 |
my $first_result_hash_ref = selectfirst_hashref_query($form, $dbh, $query); |
| 310 |
|
|
| 367 |
|
|
| 311 | 368 |
my @first_result = selectfirst_array_query($form, $dbh, $query); # == |
| 312 | 369 |
my @first_result = selectrow_query($form, $dbh, $query); |
| 313 |
|
|
| 370 |
|
|
| 314 | 371 |
my %sort_spec = create_sort_spec(%params); |
| 315 | 372 |
|
| 316 | 373 |
=head1 DESCRIPTION |
| ... | ... | |
| 329 | 386 |
- Safe value binding. Although DBI is far from perfect in terms of binding, the rest of the bindings should happen here. |
| 330 | 387 |
- Error handling. Should a query fail, an error message will be generated here instead of in the backend code invoking DBUtils. |
| 331 | 388 |
|
| 332 |
Note that binding is not perfect here either...
|
|
| 333 |
|
|
| 389 |
Note that binding is not perfect here either... |
|
| 390 |
|
|
| 334 | 391 |
=head2 QUOTING FUNCTIONS |
| 335 | 392 |
|
| 336 | 393 |
=over 4 |
| ... | ... | |
| 386 | 443 |
|
| 387 | 444 |
=item selectrow_query FORM,DBH,QUERY,ARRAY |
| 388 | 445 |
|
| 389 |
Prepares and executes a query using DBUtils functions, retireves the first row from the database, and returns it as an arrayref of the first row.
|
|
| 446 |
Prepares and executes a query using DBUtils functions, retireves the first row from the database, and returns it as an arrayref of the first row. |
|
| 390 | 447 |
|
| 391 | 448 |
=item selectfirst_hashref_query FORM,DBH,QUERY,ARRAY |
| 392 | 449 |
|
| 393 |
Prepares and executes a query using DBUtils functions, retireves the first row from the database, and returns it as a hashref of the first row.
|
|
| 450 |
Prepares and executes a query using DBUtils functions, retireves the first row from the database, and returns it as a hashref of the first row. |
|
| 394 | 451 |
|
| 395 | 452 |
=item selectall_hashref_query FORM,DBH,QUERY,ARRAY |
| 396 | 453 |
|
| ... | ... | |
| 484 | 541 |
=item A more complicated example, using dynamic binding values: |
| 485 | 542 |
|
| 486 | 543 |
my @values; |
| 487 |
|
|
| 544 |
|
|
| 488 | 545 |
if ($form->{language_values} ne "") {
|
| 489 | 546 |
$query = qq|SELECT l.id, l.description, tr.translation, tr.longdescription |
| 490 | 547 |
FROM language l |
| ... | ... | |
| 493 | 550 |
} else {
|
| 494 | 551 |
$query = qq|SELECT id, description FROM language|; |
| 495 | 552 |
} |
| 496 |
|
|
| 553 |
|
|
| 497 | 554 |
my $languages = selectall_hashref_query($form, $dbh, $query, @values); |
| 498 | 555 |
|
| 499 | 556 |
=back |
| ... | ... | |
| 504 | 561 |
|
| 505 | 562 |
Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt> |
| 506 | 563 |
Sven Schoeling E<lt>s.schoeling@linet-services.deE<gt> |
| 507 |
|
|
| 564 |
|
|
| 508 | 565 |
=head1 DOCUMENTATION AUTHORS |
| 509 | 566 |
|
| 510 | 567 |
Udo Spallek E<lt>udono@gmx.netE<gt> |
| ... | ... | |
| 526 | 583 |
You should have received a copy of the GNU General Public License |
| 527 | 584 |
along with this program; if not, write to the Free Software |
| 528 | 585 |
Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. |
| 529 |
=cut |
|
| 586 |
=cut |
|
Auch abrufbar als: Unified diff
Löschen von RecordLinks