X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=C4%2FSQLHelper.pm;h=f1fa7b52761ec9704a9a40db47a1b2ea4a9f2fec;hb=08382876306cfda839637c5f72a107b304458a8e;hp=a15beb86b66d6c3686489e3defd91b8961731384;hpb=2cf3e24797d80e266bd2f4bb0a0f5a9ba400b236;p=koha.git

diff --git a/C4/SQLHelper.pm b/C4/SQLHelper.pm
index a15beb86b6..f1fa7b5276 100644
--- a/C4/SQLHelper.pm
+++ b/C4/SQLHelper.pm
@@ -27,9 +27,26 @@ use C4::Debug;
 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;
+    $VERSION = 3.07.00.049;
 	require Exporter;
 	@ISA    = qw(Exporter);
 @EXPORT_OK=qw(
@@ -38,13 +55,14 @@ BEGIN {
 	SearchInTable
 	UpdateInTable
 	GetPrimaryKeys
+        clear_columns_cache
 );
 	%EXPORT_TAGS = ( all =>[qw( InsertInTable DeleteInTable SearchInTable UpdateInTable GetPrimaryKeys)]
 				);
 }
 
 my $tablename;
-my $hash;
+my $hashref;
 
 =head1 NAME
 
@@ -85,6 +103,21 @@ $filtercolums is an array ref on field names : is used to limit expansion of res
 
 $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{
@@ -101,7 +134,7 @@ sub SearchInTable{
 	if ($keys){
 		my @criteria=grep{defined($_) && $_ !~/^\W$/ }@$keys;
 		if (@criteria) { 
-			$sql.= do { local $"=') AND ('; 
+			$sql.= do { local $"=') OR ('; 
 					qq{ WHERE (@criteria) } 
 				   }; 
 		} 
@@ -109,8 +142,12 @@ sub SearchInTable{
     if ($orderby){ 
 		#Order by desc by default
 		my @orders;
-		foreach my $order (@$orderby){
-			push @orders,map{ "$_".($order->{$_}? " DESC " : "") } keys %$order; 
+		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} 
@@ -211,12 +248,29 @@ Get the Primary Key field names of the table
 
 =cut
 
-sub GetPrimaryKeys($) {
+sub GetPrimaryKeys {
 	my $tablename=shift;
 	my $hash_columns=_get_columns($tablename);
 	return  grep { $hash_columns->{$_}->{'Key'} =~/PRI/i}  keys %$hash_columns;
 }
 
+
+=head2 clear_columns_cache
+
+  C4::SQLHelper->clear_columns_cache();
+
+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)
@@ -230,17 +284,25 @@ With
 
 =cut
 
-sub _get_columns($) {
-	my ($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));
+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_columns
 
-    _filter_columns($tablename,$research, $filtercolumns)
+=over 4
+
+_filter_columns($tablename,$research, $filtercolumns)
+
+=back
 
 Given 
 	- a tablename 
@@ -252,7 +314,7 @@ If it is not for research purpose, filter primary keys
 
 =cut
 
-sub _filter_columns ($$;$) {
+sub _filter_columns {
 	my ($tablename,$research, $filtercolumns)=@_;
 	if ($filtercolumns){
 		return (@$filtercolumns);
@@ -287,13 +349,21 @@ sub _filter_fields{
     my @keys; 
 	my @values;
 	if (ref($filter_input) eq "HASH"){
-		my ($keys, $values) = _filter_hash($tablename,$filter_input, $searchtype);
+		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;
+		    my $stringkey="(".join (") AND (",@$keys).")";
+		    return [$stringkey],$values;
 		}
 		else {
-		return ();
+		    return ();
 		}
 	} elsif (ref($filter_input) eq "ARRAY"){
 		foreach my $element_data (@$filter_input){
@@ -334,7 +404,9 @@ sub _filter_hash{
     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"));
+		$filter_input->{$field}=format_date_in_iso($filter_input->{$field})
+          if $columns->{$field}{Type}=~/date/ &&
+             ($filter_input->{$field} && $filter_input->{$field} !~C4::Dates->regexp("iso"));
 		my ($tmpkeys, $localvalues)=_Process_Operands($filter_input->{$field},"$tablename.$field",$searchtype,$columns);
 		if (@$tmpkeys){
 			push @values, @$localvalues;
@@ -352,7 +424,11 @@ sub _filter_hash{
 sub _filter_string{
 	my ($tablename,$filter_input, $searchtype,$filtercolumns)=@_;
 	return () unless($filter_input);
-	my @operands=split / /,$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);
@@ -381,8 +457,12 @@ sub _Process_Operands{
 	my @values;
 	my @tmpkeys;
 	my @localkeys;
-	push @tmpkeys, " $field = ? ";
-	push @values, $operand;
+
+    $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;
@@ -394,16 +474,22 @@ sub _Process_Operands{
 	if ($columns->{$col_field}->{Type}=~/varchar|text/i){
 		my @localvaluesextended;
 		if ($searchtype eq "contain"){
-			push @tmpkeys,(" $field LIKE ? ");
-			push @localvaluesextended,("\%$operand\%") ;
+            foreach (@$operand) {
+			    push @tmpkeys,(" $field LIKE ? ");
+			    push @localvaluesextended,("\%$_\%") ;
+            }
 		}
 		if ($searchtype eq "field_start_with"){
-			push @tmpkeys,("$field LIKE ?");
-			push @localvaluesextended, ("$operand\%") ;
+            foreach (@$operand) {
+			    push @tmpkeys,("$field LIKE ?");
+			    push @localvaluesextended, ("$_\%") ;
+            }
 		}
 		if ($searchtype eq "start_with"){
-			push @tmpkeys,("$field LIKE ?","$field LIKE ?");
-			push @localvaluesextended, ("$operand\%", " $operand\%") ;
+            foreach (@$operand) {
+			    push @tmpkeys,("$field LIKE ?","$field LIKE ?");
+			    push @localvaluesextended, ("$_\%", " $_\%") ;
+            }
 		}
 		push @values,@localvaluesextended;
 	}