Shopware6: Status completed innerhalb des Konnektors mappen

[kivitendo-erp.git] / SL / DBUtils.pm

diff --git a/SL/DBUtils.pm b/SL/DBUtils.pm
index 6ba89df..f095244 100644 (file)
--- a/SL/DBUtils.pm
+++ b/SL/DBUtils.pm
@@ -8,12 +8,12 @@ our @ISA = qw(Exporter);
 our @EXPORT = qw(conv_i conv_date conv_dateq do_query selectrow_query do_statement
              dump_query quote_db_date like
              selectfirst_hashref_query selectfirst_array_query
 our @EXPORT = qw(conv_i conv_date conv_dateq do_query selectrow_query do_statement
              dump_query quote_db_date like
              selectfirst_hashref_query selectfirst_array_query

-             selectall_hashref_query selectall_array_query
+             selectall_hashref_query selectall_array_query selectcol_array_query

              selectall_as_map
              selectall_ids
              prepare_execute_query prepare_query
              create_sort_spec does_table_exist
              selectall_as_map
              selectall_ids
              prepare_execute_query prepare_query
              create_sort_spec does_table_exist

-             add_token);
+             add_token check_trgm);

 
 use strict;
 
 
 use strict;
 


@@ -92,7 +92,7 @@ sub dump_query {
   my $self_filename = 'SL/DBUtils.pm';
   my $filename      = $self_filename;
   my ($caller_level, $line, $subroutine);
   my $self_filename = 'SL/DBUtils.pm';
   my $filename      = $self_filename;
   my ($caller_level, $line, $subroutine);

-  while ($filename eq $self_filename) {
+  while ($filename =~ m{$self_filename$}) {

     (undef, $filename, $line, $subroutine) = caller $caller_level++;
   }
 
     (undef, $filename, $line, $subroutine) = caller $caller_level++;
   }
 


@@ -171,16 +171,15 @@ sub selectall_hashref_query {
   return wantarray ? @{ $result } : $result;
 }
 
   return wantarray ? @{ $result } : $result;
 }
 

-sub selectall_array_query {
+sub selectall_array_query { goto &selectcol_array_query; }
+
+sub selectcol_array_query {

   $main::lxdebug->enter_sub(2);
 
   my ($form, $dbh, $query) = splice(@_, 0, 3);
 
   my $sth = prepare_execute_query($form, $dbh, $query, @_);
   $main::lxdebug->enter_sub(2);
 
   my ($form, $dbh, $query) = splice(@_, 0, 3);
 
   my $sth = prepare_execute_query($form, $dbh, $query, @_);

-  my @result;
-  while (my ($value) = $sth->fetchrow_array()) {
-    push(@result, $value);
-  }
+  my @result = @{ $dbh->selectcol_arrayref($sth) };

   $sth->finish();
 
   $main::lxdebug->leave_sub(2);
   $sth->finish();
 
   $main::lxdebug->leave_sub(2);


@@ -399,6 +398,14 @@ sub role_is_superuser {
   return $is_superuser;
 }
 
   return $is_superuser;
 }
 

+sub check_trgm {
+  my ($dbh)  = @_;
+
+  my $version = $dbh->selectrow_array(qq|SELECT installed_version FROM pg_available_extensions WHERE name = 'pg_trgm'|);
+
+  return !!$version;
+}
+

 1;
 
 
 1;
 
 


@@ -408,7 +415,7 @@ __END__
 
 =head1 NAME
 
 
 =head1 NAME
 

-SL::DBUTils.pm: All about database connections in kivitendo
+SL::DBUtils.pm: All about database connections in kivitendo

 
 =head1 SYNOPSIS
 
 
 =head1 SYNOPSIS
 


@@ -419,6 +426,8 @@ SL::DBUTils.pm: All about database connections in kivitendo
   conv_dateq($str)
   quote_db_date($date)
 
   conv_dateq($str)
   quote_db_date($date)
 

+  my $dbh = SL::DB->client->dbh;
+

   do_query($form, $dbh, $query)
   do_statement($form, $sth, $query)
 
   do_query($form, $dbh, $query)
   do_statement($form, $sth, $query)
 


@@ -428,9 +437,11 @@ SL::DBUTils.pm: All about database connections in kivitendo
   my $all_results_ref       = selectall_hashref_query($form, $dbh, $query)
   my $first_result_hash_ref = selectfirst_hashref_query($form, $dbh, $query);
 
   my $all_results_ref       = selectall_hashref_query($form, $dbh, $query)
   my $first_result_hash_ref = selectfirst_hashref_query($form, $dbh, $query);
 

-  my @first_result =  selectfirst_array_query($form, $dbh, $query);  # ==
+  my @first_result =  selectfirst_array_query($form, $dbh, $query);

   my @first_result =  selectrow_query($form, $dbh, $query);
 
   my @first_result =  selectrow_query($form, $dbh, $query);
 

+  my @values = selectcol_array_query($form, $dbh, $query);
+

   my %sort_spec = create_sort_spec(%params);
 
 =head1 DESCRIPTION
   my %sort_spec = create_sort_spec(%params);
 
 =head1 DESCRIPTION


@@ -454,10 +465,8 @@ not. In most cases you will call it with C<$::form>.
 
 C<DBH> is a handle to the database, as returned by the C<DBI::connect> routine.
 If you don't have an active connection, you can use
 
 C<DBH> is a handle to the database, as returned by the C<DBI::connect> routine.
 If you don't have an active connection, you can use

-C<<$::form->get_standard_dbh>> to get a generic no_auto connection or get a
-C<Rose::DB::Object> handle from any RDBO class with
-C<<SL::DB::Part->new->db->dbh>>. The former will be without autocommit, the
-latter with autocommit.
+C<SL::DB->client->dbh> or get a C<Rose::DB::Object> handle from any RDBO class with
+C<<SL::DB::Part->new->db->dbh>>. In both cases the handle will have AutoCommit set.

 
 See C<PITFALLS AND CAVEATS> for common errors.
 
 
 See C<PITFALLS AND CAVEATS> for common errors.
 


@@ -514,7 +523,7 @@ or export only what you need:
   selectall_hashref_query(...)
 
 
   selectall_hashref_query(...)
 
 

-=head2 Peformance
+=head2 Performance

 
 Since it is really easy to write something like
 
 
 Since it is really easy to write something like
 


@@ -522,7 +531,7 @@ Since it is really easy to write something like
 
 people do so from time to time. When writing code, consider this a ticking
 timebomb. Someone out there has a database with 1mio parts in it, and this
 
 people do so from time to time. When writing code, consider this a ticking
 timebomb. Someone out there has a database with 1mio parts in it, and this

-statement just shovelled ate 2GB of memory and timeouted the request.
+statement just gobbled up 2GB of memory and timeouted the request.

 
 Parts may be the obvious example, but the same applies to customer, vendors,
 records, projects or custom variables.
 
 Parts may be the obvious example, but the same applies to customer, vendors,
 records, projects or custom variables.


@@ -615,6 +624,16 @@ the database, and returns it in hashref mode. This is slightly confusing, as
 the data structure will actually be a reference to an array, containing
 hashrefs for each row.
 
 the data structure will actually be a reference to an array, containing
 hashrefs for each row.
 

+
+=item selectall_array_query FORM,DBH,QUERY,ARRAY
+
+Deprecated, see C<selectcol_array_query>
+
+=item selectcol_array_query FORM,DBH,QUERY,ARRAY
+
+Prepares and executes a query using DBUtils functions, retrieves the values of
+the first result column and returns the values as an array.
+

 =item selectall_as_map FORM,DBH,QUERY,KEY_COL,VALUE_COL,ARRAY
 
 Prepares and executes a query using DBUtils functions, retrieves all data from
 =item selectall_as_map FORM,DBH,QUERY,KEY_COL,VALUE_COL,ARRAY
 
 Prepares and executes a query using DBUtils functions, retrieves all data from


@@ -673,6 +692,11 @@ application (e.g. if the user clicked on a column header in a
 report). If it is undefined then the 'default_dir' parameter will be
 used instead.
 
 report). If it is undefined then the 'default_dir' parameter will be
 used instead.
 

+=item check_trgm
+
+Checks if the postgresextension pg_trgm is installed and return trueish
+or falsish.
+

 =back
 
 =head1 DEBUG FUNCTIONS
 =back
 
 =head1 DEBUG FUNCTIONS


@@ -704,6 +728,11 @@ general little need to invoke it manually.
   $query = qq|SELECT nextval('glid')|;
   ($new_id) = selectrow_query($form, $dbh, $query);
 
   $query = qq|SELECT nextval('glid')|;
   ($new_id) = selectrow_query($form, $dbh, $query);
 

+=item Retrieving all values from a column:
+
+  $query = qq|SELECT id FROM units|;
+  @units = selectcol_array_query($form, $dbh, $query);
+

 =item Using binding values:
 
   $query = qq|UPDATE ar SET paid = amount + paid, storno = 't' WHERE id = ?|;
 =item Using binding values:
 
   $query = qq|UPDATE ar SET paid = amount + paid, storno = 't' WHERE id = ?|;