X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;ds=sidebyside;f=SL%2FDBUtils.pm;h=9dfe2715392671da3b882c875e9e7d12d8b44f42;hb=58190585c35976c33c0594e920251ec8f7e3d40e;hp=5fc33cedc75dedbc9c73f0653d0efe22af756793;hpb=8545bbce275692e1e4ac9023320c7c4a8451bfdc;p=kivitendo-erp.git diff --git a/SL/DBUtils.pm b/SL/DBUtils.pm index 5fc33cedc..9dfe27153 100644 --- a/SL/DBUtils.pm +++ b/SL/DBUtils.pm @@ -1,13 +1,16 @@ package SL::DBUtils; +use SL::Util qw(trim); + require Exporter; our @ISA = qw(Exporter); our @EXPORT = qw(conv_i conv_date conv_dateq do_query selectrow_query do_statement - dump_query quote_db_date + dump_query quote_db_date like selectfirst_hashref_query selectfirst_array_query selectall_hashref_query selectall_array_query selectall_as_map + selectall_ids prepare_execute_query prepare_query create_sort_spec does_table_exist add_token); @@ -29,7 +32,9 @@ sub conv_b { sub conv_date { my ($value) = @_; - return (defined($value) && "$value" ne "") ? $value : undef; + return undef if !defined $value; + $value = trim($value); + return $value eq "" ? undef : $value; } sub conv_dateq { @@ -221,11 +226,11 @@ sub selectall_as_map { my %hash; if ('' eq ref $value_col) { while (my $ref = $sth->fetchrow_hashref()) { - $hash{$ref->{$key_col}} = $ref->{$value_col}; + $hash{$ref->{$key_col} // ''} = $ref->{$value_col}; } } else { while (my $ref = $sth->fetchrow_hashref()) { - $hash{$ref->{$key_col}} = { map { $_ => $ref->{$_} } @{ $value_col } }; + $hash{$ref->{$key_col} // ''} = { map { $_ => $ref->{$_} } @{ $value_col } }; } } @@ -236,6 +241,25 @@ sub selectall_as_map { return %hash; } +sub selectall_ids { + $main::lxdebug->enter_sub(2); + + my ($form, $dbh, $query, $key_col) = splice(@_, 0, 4); + + my $sth = prepare_execute_query($form, $dbh, $query, @_); + + my @ids; + while (my $ref = $sth->fetchrow_arrayref()) { + push @ids, $ref->[$key_col]; + } + + $sth->finish; + + $main::lxdebug->leave_sub(2); + + return @ids; +} + sub create_sort_spec { $main::lxdebug->enter_sub(2); @@ -324,9 +348,9 @@ sub add_token { id => \&conv_i, bool => \&conv_b, date => \&conv_date, - start => sub { $_[0] . '%' }, - end => sub { '%' . $_[0] }, - substr => sub { '%' . $_[0] . '%' }, + start => sub { trim($_[0]) . '%' }, + end => sub { '%' . trim($_[0]) }, + substr => sub { like($_[0]) }, ); my $_long_token = sub { @@ -362,14 +386,22 @@ sub add_token { return ($token, @vals); } +sub like { + my ($string) = @_; + + return "%" . SL::Util::trim($string // '') . "%"; +} + 1; __END__ +=encoding utf-8 + =head1 NAME -SL::DBUTils.pm: All about Databaseconections in Lx +SL::DBUTils.pm: All about database connections in kivitendo =head1 SYNOPSIS @@ -396,23 +428,100 @@ SL::DBUTils.pm: All about Databaseconections in Lx =head1 DESCRIPTION -DBUtils is the attempt to reduce the amount of overhead it takes to retrieve information from the database in Lx-Office. Previously it would take about 15 lines of code just to get one single integer out of the database, including failure procedures and importing the necessary packages. Debugging would take even more. +DBUtils provides wrapper functions for low level database retrieval. It saves +you the trouble of mucking around with statement handles for small databse +queries and does exception handling in the common cases for you. + +Query and retrieval function share the parameter scheme: + + query_or_retrieval(C