From b6cc6680dc1fb96787d0dffbbb7dda8ed04cc273 Mon Sep 17 00:00:00 2001
From: tipaul <tipaul>
Date: Thu, 17 Jun 2004 08:16:32 +0000
Subject: [PATCH] merging tag & subfield in marc_word for better perfs

---
 C4/Biblio.pm     | 15 +++++++++------
 C4/SearchMarc.pm | 32 +++++++++++++++++---------------
 2 files changed, 26 insertions(+), 21 deletions(-)

diff --git a/C4/Biblio.pm b/C4/Biblio.pm
index 51d431e27c..efb49e3715 100644
--- a/C4/Biblio.pm
+++ b/C4/Biblio.pm
@@ -551,7 +551,7 @@ sub MARCdelbiblio {
 		$sth->execute;
 		my $itemtag = $sth->fetchrow_hashref->{tagfield};
 		$dbh->do("delete from marc_subfield_table where bibid=$bibid and tag<>$itemtag");
-		$dbh->do("delete from marc_word where bibid=$bibid and tag<>$itemtag");
+		$dbh->do("delete from marc_word where bibid=$bibid and not (tagsubfield like \"$itemtag%\")");
 	} else {
 		$dbh->do("delete from marc_biblio where bibid=$bibid");
 		$dbh->do("delete from marc_subfield_table where bibid=$bibid");
@@ -947,14 +947,14 @@ sub MARCaddword {
     $sentence =~ s/(\.|\?|\:|\!|\'|,|\-|\"|\(|\)|\[|\]|\{|\})/ /g;
     my @words = split / /,$sentence;
     my $stopwords= C4::Context->stopwords;
-    my $sth=$dbh->prepare("insert into marc_word (bibid, tag, tagorder, subfieldid, subfieldorder, word, sndx_word)
-			values (?,?,?,?,?,?,soundex(?))");
+    my $sth=$dbh->prepare("insert into marc_word (bibid, tagsubfield, tagorder, subfieldorder, word, sndx_word)
+			values (?,concat(?,?),?,?,?,soundex(?))");
     foreach my $word (@words) {
 # we record only words one char long and not in stopwords hash
 	if (length($word)>=1 and !($stopwords->{uc($word)})) {
 	    $sth->execute($bibid,$tag,$tagorder,$subfieldid,$subfieldorder,$word,$word);
 	    if ($sth->err()) {
-		warn "ERROR ==> insert into marc_word (bibid, tag, tagorder, subfieldid, subfieldorder, word, sndx_word) values ($bibid,$tag,$tagorder,$subfieldid,$subfieldorder,$word,soundex($word))\n";
+		warn "ERROR ==> insert into marc_word (bibid, tagsubfield, tagorder, subfieldorder, word, sndx_word) values ($bibid,concat($tag,$subfieldid),$tagorder,$subfieldorder,$word,soundex($word))\n";
 	    }
 	}
     }
@@ -963,8 +963,8 @@ sub MARCaddword {
 sub MARCdelword {
 # delete words. this sub deletes all the words from a sentence. a subfield modif is done by a delete then a add
     my ($dbh,$bibid,$tag,$tagorder,$subfield,$subfieldorder) = @_;
-    my $sth=$dbh->prepare("delete from marc_word where bibid=? and tag=? and tagorder=? and subfieldid=? and subfieldorder=?");
-    $sth->execute($bibid,$tag,$tagorder,$subfield,$subfieldorder);
+    my $sth=$dbh->prepare("delete from marc_word where bibid=? and tagsubfield=concat(?,?) and tagorder=? and subfieldorder=?");
+    $sth->execute($bibid,$tag,$subfield,$tagorder,$subfieldorder);
 }
 
 #
@@ -2191,6 +2191,9 @@ Paul POULAIN paul.poulain@free.fr
 
 # $Id$
 # $Log$
+# Revision 1.94  2004/06/17 08:16:32  tipaul
+# merging tag & subfield in marc_word for better perfs
+#
 # Revision 1.93  2004/06/11 15:38:06  joshferraro
 # Changes MARCaddword to index words >= 1 char ... needed for more accurate
 # searches using SearchMarc routines.
diff --git a/C4/SearchMarc.pm b/C4/SearchMarc.pm
index 2d6e80cf14..5491822f13 100644
--- a/C4/SearchMarc.pm
+++ b/C4/SearchMarc.pm
@@ -274,18 +274,20 @@ sub catalogsearch {
 	}
 # add the last line, that is not reached byt the loop / if ($oldbiblionumber...)
 	my %newline;
-	%newline = %$oldline;
-	$newline{totitem} = $totalitems;
-	$newline{biblionumber} = $oldbiblionumber;
-	my @CNresults2= @CNresults;
-	$newline{CN} = \@CNresults2;
-	$newline{'even'} = 1 if $counter % 2 == 0;
-	$newline{'odd'} = 1 if $counter % 2 == 1;
-	@CNresults = ();
-	my @CNresults2= @CNresults;
-	$newline{CN} = \@CNresults2;
-	@CNresults = ();
-	push @finalresult, \%newline;
+	if ($oldline) {
+		%newline = %$oldline;
+		$newline{totitem} = $totalitems;
+		$newline{biblionumber} = $oldbiblionumber;
+		my @CNresults2= @CNresults;
+		$newline{CN} = \@CNresults2;
+		$newline{'even'} = 1 if $counter % 2 == 0;
+		$newline{'odd'} = 1 if $counter % 2 == 1;
+		@CNresults = ();
+		my @CNresults2= @CNresults;
+		$newline{CN} = \@CNresults2;
+		@CNresults = ();
+		push @finalresult, \%newline;
+	}
 	my $nbresults = $#result + 1;
 	return (\@finalresult, $nbresults);
 }
@@ -316,7 +318,7 @@ sub create_request {
 					$sql_tables .= "marc_word as m$nb_table,";
 					$sql_where1 .= "(m1.word  like ".$dbh->quote("@$value[$i]%");
 					if (@$tags[$i]) {
-						 $sql_where1 .=" and m1.tag+m1.subfieldid in (@$tags[$i])";
+						 $sql_where1 .=" and m1.tagsubfield in (@$tags[$i])";
 					}
 					$sql_where1.=")";
 				} else {
@@ -343,14 +345,14 @@ sub create_request {
 						$sql_tables .= "marc_word as m$nb_table,";
 						$sql_where1 .= "@$and_or[$i] (m$nb_table.word like ".$dbh->quote("@$value[$i]%");
 						if (@$tags[$i]) {
-							$sql_where1 .=" and m$nb_table.tag+m$nb_table.subfieldid in(@$tags[$i])";
+							$sql_where1 .=" and m$nb_table.tagsubfield in(@$tags[$i])";
 						}
 						$sql_where1.=")";
 						$sql_where2 .= "m1.bibid=m$nb_table.bibid and ";
 					} else {
 						$sql_where1 .= "@$and_or[$i] (m$nb_table.word like ".$dbh->quote("@$value[$i]%");
 						if (@$tags[$i]) {
-							$sql_where1 .="  and m$nb_table.tag+m$nb_table.subfieldid in (@$tags[$i])";
+							$sql_where1 .="  and m$nb_table.tagsubfield in (@$tags[$i])";
 						}
 						$sql_where1.=")";
 						$sql_where2 .= "m1.bibid=m$nb_table.bibid and ";
-- 
2.39.5