X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=C4%2FSQLHelper.pm;h=e1cb5d0a9aa35e8787d95e867c69077184d2a87f;hb=a7832c47562288f163a6ede0a50861142e13e155;hp=bbc31edb2708b292ea71da631a001931d7dac9e8;hpb=4271bbb738265a0ddc41def39cfd67592645e674;p=koha_gimpoz

diff --git a/C4/SQLHelper.pm b/C4/SQLHelper.pm
index bbc31edb27..e1cb5d0a9a 100644
--- a/C4/SQLHelper.pm
+++ b/C4/SQLHelper.pm
@@ -13,20 +13,37 @@ package C4::SQLHelper;
 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
 # A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
 #
-# You should have received a copy of the GNU General Public License along with
-# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
-# Suite 330, Boston, MA  02111-1307 USA
+# You should have received a copy of the GNU General Public License along
+# with Koha; if not, write to the Free Software Foundation, Inc.,
+# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
 
 
-use List::MoreUtils qw(first_value);
+use strict;
+use warnings;
+use List::MoreUtils qw(first_value any);
 use C4::Context;
 use C4::Dates qw(format_date_in_iso);
 use C4::Debug;
-use strict;
-use warnings;
 require Exporter;
 use vars qw($VERSION @ISA @EXPORT_OK %EXPORT_TAGS);
 
+eval {
+    my $servers = C4::Context->config('memcached_servers');
+    if ($servers) {
+        require Memoize::Memcached;
+        import Memoize::Memcached qw(memoize_memcached);
+
+        my $memcached = {
+            servers     => [$servers],
+            key_prefix  => C4::Context->config('memcached_namespace') || 'koha',
+            expire_time => 600
+        };    # cache for 10 mins
+
+        memoize_memcached( '_get_columns',   memcached => $memcached );
+        memoize_memcached( 'GetPrimaryKeys', memcached => $memcached );
+    }
+};
+
 BEGIN {
 	# set the version for version checking
 	$VERSION = 0.5;
@@ -34,16 +51,18 @@ BEGIN {
 	@ISA    = qw(Exporter);
 @EXPORT_OK=qw(
 	InsertInTable
+	DeleteInTable
 	SearchInTable
 	UpdateInTable
-	GetPrimaryKey
+	GetPrimaryKeys
+        clear_columns_cache
 );
-	%EXPORT_TAGS = ( all =>[qw( InsertInTable SearchInTable UpdateInTable GetPrimaryKey)]
+	%EXPORT_TAGS = ( all =>[qw( InsertInTable DeleteInTable SearchInTable UpdateInTable GetPrimaryKeys)]
 				);
 }
 
 my $tablename;
-my $hash;
+my $hashref;
 
 =head1 NAME
 
@@ -59,142 +78,202 @@ This module contains routines for adding, modifying and Searching Data in MysqlD
 
 =head1 FUNCTIONS
 
-=over 2
-
-=back
-
-
 =head2 SearchInTable
 
-=over 4
+  $hashref = &SearchInTable($tablename,$data, $orderby, $limit, 
+                      $columns_out, $filtercolumns, $searchtype);
 
-  $hashref = &SearchInTable($tablename,$data, $orderby);
 
-=back
+$tablename Name of the table (string)
 
 $data may contain 
 	- string
+
 	- data_hashref : will be considered as an AND of all the data searched
+
 	- data_array_ref on hashrefs : Will be considered as an OR of Datahasref elements
 
-$orderby is a hashref with fieldnames as key and 0 or 1 as values (ASCENDING or DESCENDING order)
+$orderby is an arrayref of hashref with fieldnames as key and 0 or 1 as values (ASCENDING or DESCENDING order)
+
+$limit is an array ref on 2 values in order to limit results to MIN..MAX
+
+$columns_out is an array ref on field names is used to limit results on those fields (* by default)
+
+$filtercolums is an array ref on field names : is used to limit expansion of research for strings
+
+$searchtype is string Can be "start_with" or "exact" 
+
+This query builder is very limited, it should be replaced with DBIx::Class
+or similar  very soon
+Meanwhile adding support for special key '' in case of a data_hashref to
+support filters of type
+
+  ( f1 = a OR f2 = a ) AND fx = b AND fy = c
+
+Call for the query above is:
+
+  SearchInTable($tablename, {'' => a, fx => b, fy => c}, $orderby, $limit,
+                $columns_out, [f1, f2], 'exact');
+
+NOTE: Current implementation may remove parts of the iinput hashrefs. If that is a problem
+a copy needs to be created in _filter_fields() below
 
 =cut
 
 sub SearchInTable{
-    my ($tablename,$filters,$orderby) = @_; 
+    my ($tablename,$filters,$orderby, $limit, $columns_out, $filter_columns,$searchtype) = @_; 
+	$searchtype||="exact";
     my $dbh      = C4::Context->dbh; 
-    my $sql      = "SELECT * from $tablename"; 
+	$columns_out||=["*"];
+    my $sql      = do { local $"=', '; 
+                qq{ SELECT @$columns_out from $tablename} 
+               };
     my $row; 
     my $sth; 
-    my ($keys,$values)=_filter_fields($filters,$tablename, "search"); 
-    if ($filters) { 
-        $sql.= do { local $"=' AND '; 
-                qq{ WHERE @$keys } 
-               }; 
-    } 
+    my ($keys,$values)=_filter_fields($tablename,$filters,$searchtype,$filter_columns); 
+	if ($keys){
+		my @criteria=grep{defined($_) && $_ !~/^\W$/ }@$keys;
+		if (@criteria) { 
+			$sql.= do { local $"=') OR ('; 
+					qq{ WHERE (@criteria) } 
+				   }; 
+		} 
+	}
     if ($orderby){ 
-        my @orders=map{ "$_".($$orderby{$_}? " DESC" : "") } keys %$orderby; 
-        $sql.= do { local $"=', '; 
-                qq{ ORDER BY @orders} 
-               }; 
+		#Order by desc by default
+		my @orders;
+		foreach my $order ( ref($orderby) ? @$orderby : $orderby ){
+            if (ref $order) {
+			    push @orders,map{ "$_".($order->{$_}? " DESC " : "") } keys %$order; 
+            } else {
+			    push @orders,$order; 
+            }
+		}
+		$sql.= do { local $"=', '; 
+				qq{ ORDER BY @orders} 
+        }; 
     } 
+	if ($limit){
+		$sql.=qq{ LIMIT }.join(",",@$limit);
+	}
      
-    $debug && warn $sql," ",join(",",@$values); 
-    $sth = $dbh->prepare($sql); 
-    $sth->execute(@$values); 
+    $debug && $values && warn $sql," ",join(",",@$values); 
+    $sth = $dbh->prepare_cached($sql); 
+    eval{$sth->execute(@$values)}; 
+	warn $@ if ($@ && $debug);
     my $results = $sth->fetchall_arrayref( {} ); 
     return $results;
 }
 
 =head2 InsertInTable
 
-=over 4
+  $data_id_in_table = &InsertInTable($tablename,$data_hashref,$withprimarykeys);
 
-  $data_id_in_table = &InsertInTable($tablename,$data_hashref);
+Insert Data in table and returns the id of the row inserted
 
-=back
-
-  Insert Data in table
-  and returns the id of the row inserted
 =cut
 
 sub InsertInTable{
-    my ($tablename,$data) = @_;
+    my ($tablename,$data,$withprimarykeys) = @_;
     my $dbh      = C4::Context->dbh;
-    my ($keys,$values)=_filter_fields($data,$tablename);
-
-    my $query = do { local $"=',';
-    qq{
-            INSERT $tablename
-            SET  @$keys
-        };
-    };
+    my ($keys,$values)=_filter_hash($tablename,$data,($withprimarykeys?"exact":0));
+    my $query = qq{ INSERT INTO $tablename SET  }.join(", ",@$keys);
 
 	$debug && warn $query, join(",",@$values);
-    my $sth = $dbh->prepare($query);
-    $sth->execute( @$values);
+    my $sth = $dbh->prepare_cached($query);
+    eval{$sth->execute(@$values)}; 
+	warn $@ if ($@ && $debug);
 
 	return $dbh->last_insert_id(undef, undef, $tablename, undef);
 }
 
 =head2 UpdateInTable
 
-=over 4
-
   $status = &UpdateInTable($tablename,$data_hashref);
 
-=back
+Update Data in table and returns the status of the operation
 
-  Update Data in table
-  and returns the status of the operation
 =cut
 
 sub UpdateInTable{
     my ($tablename,$data) = @_;
-	my $field_id=GetPrimaryKey($tablename);
-    my $id=$$data{$field_id};
+	my @field_ids=GetPrimaryKeys($tablename);
+    my @ids=@$data{@field_ids};
     my $dbh      = C4::Context->dbh;
-    my ($keys,$values)=_filter_fields($data,$tablename);
+    my ($keys,$values)=_filter_hash($tablename,$data,0);
+    return unless ($keys);
+    my $query = 
+    qq{     UPDATE $tablename
+            SET  }.join(",",@$keys).qq{
+            WHERE }.join (" AND ",map{ "$_=?" }@field_ids);
+	$debug && warn $query, join(",",@$values,@ids);
+
+    my $sth = $dbh->prepare_cached($query);
+	my $result;
+    eval{$result=$sth->execute(@$values,@ids)}; 
+	warn $@ if ($@ && $debug);
+    return $result;
+}
 
-    my $query = do { local $"=',';
-    qq{
-            UPDATE $tablename
-            SET  @$keys
-            WHERE  $field_id=?
-        };
-    };
-	$debug && warn $query, join(",",@$values,$id);
+=head2 DeleteInTable
 
-    my $sth = $dbh->prepare($query);
-    return $sth->execute( @$values,$id);
+  $status = &DeleteInTable($tablename,$data_hashref);
 
-}
+Delete Data in table and returns the status of the operation
 
-=head2 GetPrimaryKey
+=cut
 
-=over 4
+sub DeleteInTable{
+    my ($tablename,$data) = @_;
+    my $dbh      = C4::Context->dbh;
+    my ($keys,$values)=_filter_fields($tablename,$data,1);
+	if ($keys){
+		my $query = do { local $"=') AND (';
+		qq{ DELETE FROM $tablename WHERE (@$keys)};
+		};
+		$debug && warn $query, join(",",@$values);
+		my $sth = $dbh->prepare_cached($query);
+   		my $result;
+    	eval{$result=$sth->execute(@$values)}; 
+		warn $@ if ($@ && $debug);
+    	return $result;
+	}
+}
 
-  $primarykeyname = &GetPrimaryKey($tablename)
+=head2 GetPrimaryKeys
 
-=back
+  @primarykeys = &GetPrimaryKeys($tablename)
+
+Get the Primary Key field names of the table
 
-	Get the Primary Key field name of the table
 =cut
 
-sub GetPrimaryKey($) {
+sub GetPrimaryKeys($) {
 	my $tablename=shift;
-	my $hash_columns=_columns($tablename);
-	return  first_value { $$hash_columns{$_}{'Key'} =~/PRI/}  keys %$hash_columns;
+	my $hash_columns=_get_columns($tablename);
+	return  grep { $hash_columns->{$_}->{'Key'} =~/PRI/i}  keys %$hash_columns;
 }
 
-=head2 _get_columns
 
-=over 4
+=head2 clear_columns_cache
 
-_get_columns($tablename)
+  C4::SQLHelper->clear_columns_cache();
 
-=back
+cleans the internal cache of sysprefs. Please call this method if
+you update a tables structure. Otherwise, your new changes
+will not be seen by this process.
+
+=cut
+
+sub clear_columns_cache {
+    %$hashref = ();
+}
+
+
+
+=head2 _get_columns
+
+    _get_columns($tablename)
 
 Given a tablename 
 Returns a hashref of all the fieldnames of the table
@@ -205,27 +284,60 @@ With
 
 =cut
 
-sub _columns($) {
-	my $tablename=shift;
-	$debug && warn $tablename;
-	my $dbh=C4::Context->dbh;
-	my $sth=$dbh->prepare(qq{SHOW COLUMNS FROM $tablename });
-	$sth->execute;
-    return $sth->fetchall_hashref(qw(Field));
+sub _get_columns($) {
+    my ($tablename) = @_;
+    unless ( exists( $hashref->{$tablename} ) ) {
+        my $dbh = C4::Context->dbh;
+        my $sth = $dbh->prepare_cached(qq{SHOW COLUMNS FROM $tablename });
+        $sth->execute;
+        my $columns = $sth->fetchall_hashref(qw(Field));
+        $hashref->{$tablename} = $columns;
+    }
+    return $hashref->{$tablename};
 }
 
-=head2 _filter_fields
+=head2 _filter_columns
 
 =over 4
 
-_filter_fields
+_filter_columns($tablename,$research, $filtercolumns)
 
 =back
 
 Given 
+	- a tablename 
+	- indicator on purpose whether all fields should be returned or only non Primary keys
+	- array_ref to columns to limit to
+
+Returns an array of all the fieldnames of the table
+If it is not for research purpose, filter primary keys
+
+=cut
+
+sub _filter_columns ($$;$) {
+	my ($tablename,$research, $filtercolumns)=@_;
+	if ($filtercolumns){
+		return (@$filtercolumns);
+	}
+	else {
+		my $columns=_get_columns($tablename);
+		if ($research){
+			return keys %$columns;
+		}
+		else {
+			return grep {my $column=$_; any {$_ ne $column }GetPrimaryKeys($tablename) } keys %$columns;
+		}
+	}
+}
+=head2 _filter_fields
+
+  _filter_fields
+
+Given 
 	- a tablename
-	- a hashref of data 
-	- an indicator on operation
+	- a string or a hashref (containing, fieldnames and datatofilter) or an arrayref to one of those elements
+	- an indicator of operation whether it is a wide research or a narrow one
+	- an array ref to columns to restrict string filter to.
 
 Returns a ref of key array to use in SQL functions
 and a ref to value array
@@ -233,48 +345,156 @@ and a ref to value array
 =cut
 
 sub _filter_fields{
-	my ($data_to_filter,$tablename,$research)=@_;
-	warn "$tablename";
+	my ($tablename,$filter_input,$searchtype,$filtercolumns)=@_;
     my @keys; 
 	my @values;
-	my $columns= _columns($tablename);
-	#Filter Primary Keys of table
-    my $elements=join "|",grep {$$columns{$_}{'Key'} ne "PRI"} keys %$columns;
-	if (ref($data_to_filter) eq "HASH"){
-		foreach my $field (grep {/\b($elements)\b/} keys %$data_to_filter){
-			## supposed to be a hash of simple values, hashes of arrays could be implemented
-			$$data_to_filter{$field}=format_date_in_iso($$data_to_filter{$field}) if ($$columns{$field}{Type}=~/date/ && $$data_to_filter{$field} !~C4::Dates->regexp("iso"));
-			my $strkeys= " $field = ? ";
-			if ($field=~/code/ && $research){
-				$strkeys="( $strkeys OR $field='' OR $field IS NULL) ";
-			}
-			push @values, $$data_to_filter{$field};
-			push @keys, $strkeys;
+	if (ref($filter_input) eq "HASH"){
+		my ($keys, $values);
+        if (my $special = delete $filter_input->{''}) { # XXX destroyes '' key
+		    ($keys, $values) = _filter_fields($tablename,$special, $searchtype,$filtercolumns);
+        }
+		my ($hkeys, $hvalues) = _filter_hash($tablename,$filter_input, $searchtype);
+		if ($hkeys){
+            push @$keys, @$hkeys;
+            push @$values, @$hvalues;
+        }
+		if ($keys){
+		    my $stringkey="(".join (") AND (",@$keys).")";
+		    return [$stringkey],$values;
+		}
+		else {
+		    return ();
 		}
-	} elsif (ref($data_to_filter) eq "ARRAY"){
-		foreach my $element (@$data_to_filter){
-			my (@localkeys,@localvalues)=_filter_fields($element);
-			push @keys, join(' AND ',@localkeys);
-			push @values, @localvalues;
+	} elsif (ref($filter_input) eq "ARRAY"){
+		foreach my $element_data (@$filter_input){
+			my ($localkeys,$localvalues)=_filter_fields($tablename,$element_data,$searchtype,$filtercolumns);
+			if ($localkeys){
+				@$localkeys=grep{defined($_) && $_ !~/^\W*$/}@$localkeys;
+				my $string=do{ 
+								local $"=") OR (";
+								qq{(@$localkeys)}
+							};
+				push @keys, $string;
+				push @values, @$localvalues;
+			}
 		}
 	} 
 	else{
-			my @operands=split / /,$data_to_filter;
-			foreach my $operand (@operands){
-				my @localvalues=($operand,"\%$operand\%") ;
-				foreach my $field (keys %$columns){
-					my $strkeys= " ( $field = ? OR $field LIKE ? )";
-					if ($field=~/code/){
-						$strkeys="( $strkeys OR $field='' OR $field IS NULL) ";
-					}
-					push @values, @localvalues;
-					push @keys, $strkeys;
-				}
-			}
+        $debug && warn "filterstring : $filter_input";
+		my ($keys, $values) = _filter_string($tablename,$filter_input, $searchtype,$filtercolumns);
+		if ($keys){
+		my $stringkey="(".join (") AND (",@$keys).")";
+		return [$stringkey],$values;
+		}
+		else {
+		return ();
+		}
 	}
 
 	return (\@keys,\@values);
 }
 
+sub _filter_hash{
+	my ($tablename,$filter_input, $searchtype)=@_;
+	my (@values, @keys);
+	my $columns= _get_columns($tablename);
+	my @columns_filtered= _filter_columns($tablename,$searchtype);
+	
+	#Filter Primary Keys of table
+    my $elements=join "|",@columns_filtered;
+	foreach my $field (grep {/\b($elements)\b/} keys %$filter_input){
+		## supposed to be a hash of simple values, hashes of arrays could be implemented
+		$filter_input->{$field}=format_date_in_iso($filter_input->{$field})
+          if $columns->{$field}{Type}=~/date/ &&
+             $filter_input->{$field} !~C4::Dates->regexp("iso");
+		my ($tmpkeys, $localvalues)=_Process_Operands($filter_input->{$field},"$tablename.$field",$searchtype,$columns);
+		if (@$tmpkeys){
+			push @values, @$localvalues;
+			push @keys, @$tmpkeys;
+		}
+	}
+	if (@keys){
+		return (\@keys,\@values);
+	}
+	else {
+		return ();
+	}
+}
+
+sub _filter_string{
+	my ($tablename,$filter_input, $searchtype,$filtercolumns)=@_;
+	return () unless($filter_input);
+	my @operands=split /\s+/,$filter_input;
+
+    # An act of desperation
+    $searchtype = 'contain' if @operands > 1 && $searchtype =~ /start_with/o;
+
+	my @columns_filtered= _filter_columns($tablename,$searchtype,$filtercolumns);
+	my $columns= _get_columns($tablename);
+	my (@values,@keys);
+	foreach my $operand (@operands){
+		my @localkeys;
+		foreach my $field (@columns_filtered){
+			my ($tmpkeys, $localvalues)=_Process_Operands($operand,"$tablename.$field",$searchtype,$columns);
+			if ($tmpkeys){
+				push @values,@$localvalues;
+				push @localkeys,@$tmpkeys;
+			}
+		}
+		my $sql= join (' OR ', @localkeys);
+		push @keys, $sql;
+	}
+
+	if (@keys){
+		return (\@keys,\@values);
+	}
+	else {
+		return ();
+	}
+}
+sub _Process_Operands{
+	my ($operand, $field, $searchtype,$columns)=@_;
+	my @values;
+	my @tmpkeys;
+	my @localkeys;
+
+    $operand = [$operand] unless ref $operand eq 'ARRAY';
+    foreach (@$operand) {
+	    push @tmpkeys, " $field = ? ";
+	    push @values, $_;
+    }
+	#By default, exact search
+	if (!$searchtype ||$searchtype eq "exact"){
+		return \@tmpkeys,\@values;
+	}
+	my $col_field=(index($field,".")>0?substr($field, index($field,".")+1):$field);
+	if ($field=~/(?<!zip)code|(?<!card)number/ && $searchtype ne "exact"){
+		push @tmpkeys,(" $field= '' ","$field IS NULL");
+	}
+	if ($columns->{$col_field}->{Type}=~/varchar|text/i){
+		my @localvaluesextended;
+		if ($searchtype eq "contain"){
+            foreach (@$operand) {
+			    push @tmpkeys,(" $field LIKE ? ");
+			    push @localvaluesextended,("\%$_\%") ;
+            }
+		}
+		if ($searchtype eq "field_start_with"){
+            foreach (@$operand) {
+			    push @tmpkeys,("$field LIKE ?");
+			    push @localvaluesextended, ("$_\%") ;
+            }
+		}
+		if ($searchtype eq "start_with"){
+            foreach (@$operand) {
+			    push @tmpkeys,("$field LIKE ?","$field LIKE ?");
+			    push @localvaluesextended, ("$_\%", " $_\%") ;
+            }
+		}
+		push @values,@localvaluesextended;
+	}
+	push @localkeys,qq{ (}.join(" OR ",@tmpkeys).qq{) };
+	return (\@localkeys,\@values);
+}
 1;