X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FDBUtils.pm;h=f0952440e4aaa6595fe621eb4e77eee48ca11b46;hb=b36fb0f412d2d2b43b8d98d787743cbe1fbf2540;hp=54cf7da1726e31001d9b0470dce258ad1689174b;hpb=51dd16d697cc3ff688af1a59369833650e9e1fbf;p=kivitendo-erp.git

diff --git a/SL/DBUtils.pm b/SL/DBUtils.pm
index 54cf7da17..f0952440e 100644
--- 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
-             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
-             add_token);
+             add_token check_trgm);
 
 use strict;
 
@@ -92,7 +92,7 @@ sub dump_query {
   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++;
   }
 
@@ -171,16 +171,15 @@ sub selectall_hashref_query {
   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, @_);
-  my @result;
-  while (my ($value) = $sth->fetchrow_array()) {
-    push(@result, $value);
-  }
+  my @result = @{ $dbh->selectcol_arrayref($sth) };
   $sth->finish();
 
   $main::lxdebug->leave_sub(2);
@@ -392,6 +391,21 @@ sub like {
   return "%" . SL::Util::trim($string // '') . "%";
 }
 
+sub role_is_superuser {
+  my ($dbh, $login)  = @_;
+  my ($is_superuser) = $dbh->selectrow_array(qq|SELECT usesuper FROM pg_user WHERE usename = ?|, undef, $login);
+
+  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;
 
 
@@ -401,7 +415,7 @@ __END__
 
 =head1 NAME
 
-SL::DBUTils.pm: All about database connections in kivitendo
+SL::DBUtils.pm: All about database connections in kivitendo
 
 =head1 SYNOPSIS
 
@@ -412,6 +426,8 @@ SL::DBUTils.pm: All about database connections in kivitendo
   conv_dateq($str)
   quote_db_date($date)
 
+  my $dbh = SL::DB->client->dbh;
+
   do_query($form, $dbh, $query)
   do_statement($form, $sth, $query)
 
@@ -421,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 @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 @values = selectcol_array_query($form, $dbh, $query);
+
   my %sort_spec = create_sort_spec(%params);
 
 =head1 DESCRIPTION
@@ -432,7 +450,7 @@ DBUtils provides wrapper functions for low level database retrieval. It saves
 you the trouble of mucking around with statement handles for small database
 queries and does exception handling in the common cases for you.
 
-Query and retrieval function share the parameter scheme:
+Query and retrieval functions share the parameter scheme:
 
   query_or_retrieval(C<FORM, DBH, QUERY[, BINDVALUES]>)
 
@@ -447,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<<$::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.
 
@@ -507,7 +523,7 @@ or export only what you need:
   selectall_hashref_query(...)
 
 
-=head2 Peformance
+=head2 Performance
 
 Since it is really easy to write something like
 
@@ -515,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
-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.
@@ -593,24 +609,34 @@ is passed as binding values to execute.
 
 =item selectrow_query FORM,DBH,QUERY,ARRAY
 
-Prepares and executes a query using DBUtils functions, retireves the first row
+Prepares and executes a query using DBUtils functions, retrieves the first row
 from the database, and returns it as an arrayref of the first row.
 
 =item selectfirst_hashref_query FORM,DBH,QUERY,ARRAY
 
-Prepares and executes a query using DBUtils functions, retireves the first row
+Prepares and executes a query using DBUtils functions, retrieves the first row
 from the database, and returns it as a hashref of the first row.
 
 =item selectall_hashref_query FORM,DBH,QUERY,ARRAY
 
-Prepares and executes a query using DBUtils functions, retireves all data from
+Prepares and executes a query using DBUtils functions, retrieves all data from
 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.
 
+
+=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, retireves all data from
+Prepares and executes a query using DBUtils functions, retrieves all data from
 the database, and creates a hash from the results using KEY_COL as the column
 for the hash keys and VALUE_COL for its values.
 
@@ -666,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.
 
+=item check_trgm
+
+Checks if the postgresextension pg_trgm is installed and return trueish
+or falsish.
+
 =back
 
 =head1 DEBUG FUNCTIONS
@@ -697,6 +728,11 @@ general little need to invoke it manually.
   $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 = ?|;