[Koha-cvs] CVS: koha/C4 SearchMarc.pm,1.5,1.6

[Paul POULAIN]

Update of /cvsroot/koha/koha/C4
In directory sc8-pr-cvs1:/tmp/cvs-serv32531/C4

Modified Files:
	SearchMarc.pm 
Log Message:
dramatically improves search speed when searching something OR somethingelse

Index: SearchMarc.pm
===================================================================
RCS file: /cvsroot/koha/koha/C4/SearchMarc.pm,v
retrieving revision 1.5
retrieving revision 1.6
diff -C2 -r1.5 -r1.6
*** SearchMarc.pm	24 Apr 2003 16:28:47 -0000	1.5
--- SearchMarc.pm	3 Jul 2003 12:48:33 -0000	1.6
***************
*** 69,78 ****
  	my $sql_where1; # will contain the "true" where
  	my $sql_where2; # will contain m1.bibid=m2.bibid
! 	my $nb=1;
  	for(my $i=0; $i<=@$value;$i++) {
  		if (@$value[$i]) {
! 			if ($nb==1) {
  				if (@$operator[$i] eq "start") {
! 					$sql_tables .= "marc_subfield_table as m$nb,";
  					$sql_where1 .= "@$excluding[$i](m1.subfieldvalue like '@$value[$i]%'";
  					if (@$tags[$i]) {
--- 69,81 ----
  	my $sql_where1; # will contain the "true" where
  	my $sql_where2; # will contain m1.bibid=m2.bibid
! 	my $nb_active=0; # will contain the number of "active" entries. and entry is active is a value is provided.
! 	my $nb_table=1; # will contain the number of table. ++ on each entry EXCEPT when an OR  is provided.
! 
  	for(my $i=0; $i<=@$value;$i++) {
  		if (@$value[$i]) {
! 			$nb_active++;
! 			if ($nb_active==1) {
  				if (@$operator[$i] eq "start") {
! 					$sql_tables .= "marc_subfield_table as m$nb_table,";
  					$sql_where1 .= "@$excluding[$i](m1.subfieldvalue like '@$value[$i]%'";
  					if (@$tags[$i]) {
***************
*** 81,86 ****
  					$sql_where1.=")";
  				} elsif (@$operator[$i] eq "contains") {
! 					$sql_tables .= "marc_word as m$nb,";
! 					$sql_where1 .= "@$excluding[$i](m1.word ='@$value[$i]'";
  					if (@$tags[$i]) {
  						 $sql_where1 .=" and m1.tag=@$tags[$i] and m1.subfieldid='@$subfields[$i]'";
--- 84,89 ----
  					$sql_where1.=")";
  				} elsif (@$operator[$i] eq "contains") {
! 					$sql_tables .= "marc_word as m$nb_table,";
! 					$sql_where1 .= "@$excluding[$i](m1.word  like '@$value[$i]%'";
  					if (@$tags[$i]) {
  						 $sql_where1 .=" and m1.tag=@$tags[$i] and m1.subfieldid='@$subfields[$i]'";
***************
*** 88,92 ****
  					$sql_where1.=")";
  				} else {
! 					$sql_tables .= "marc_subfield_table as m$nb,";
  					$sql_where1 .= "@$excluding[$i](m1.subfieldvalue @$operator[$i] '@$value[$i]' ";
  					if (@$tags[$i]) {
--- 91,95 ----
  					$sql_where1.=")";
  				} else {
! 					$sql_tables .= "marc_subfield_table as m$nb_table,";
  					$sql_where1 .= "@$excluding[$i](m1.subfieldvalue @$operator[$i] '@$value[$i]' ";
  					if (@$tags[$i]) {
***************
*** 97,126 ****
  			} else {
  				if (@$operator[$i] eq "start") {
! 					$sql_tables .= "marc_subfield_table as m$nb,";
! 					$sql_where1 .= "@$and_or[$i] @$excluding[$i](m$nb.subfieldvalue like '@$value[$i]%'";
  					if (@$tags[$i]) {
! 						 $sql_where1 .=" and m$nb.tag=@$tags[$i] and m$nb.subfieldcode='@$subfields[$i])";
  					}
  					$sql_where1.=")";
! 					$sql_where2 .= "m1.bibid=m$nb.bibid";
  				} elsif (@$operator[$i] eq "contains") {
! 					$sql_tables .= "marc_word as m$nb,";
! 					$sql_where1 .= "@$and_or[$i] @$excluding[$i](m$nb.word='@$value[$i]'";
! 					if (@$tags[$i]) {
! 						 $sql_where1 .="  and m$nb.tag=@$tags[$i] and m$nb.subfieldid='@$subfields[$i]'";
  					}
- 					$sql_where1.=")";
- 					$sql_where2 .= "m1.bibid=m$nb.bibid";
  				} else {
! 					$sql_tables .= "marc_subfield_table as m$nb,";
! 					$sql_where1 .= "@$and_or[$i] @$excluding[$i](m$nb.subfieldvalue @$operator[$i] '@$value[$i]'";
  					if (@$tags[$i]) {
! 						 $sql_where1 .="  and m$nb.tag=@$tags[$i] and m$nb.subfieldcode='@$subfields[$i]'";
  					}
! 					$sql_where2 .= "m1.bibid=m$nb.bibid";
  					$sql_where1.=")";
  				}
  			}
- 			$nb++;
  		}
  	}
--- 100,140 ----
  			} else {
  				if (@$operator[$i] eq "start") {
! 					$nb_table++;
! 					$sql_tables .= "marc_subfield_table as m$nb_table,";
! 					$sql_where1 .= "@$and_or[$i] @$excluding[$i](m$nb_table.subfieldvalue like '@$value[$i]%'";
  					if (@$tags[$i]) {
! 						 $sql_where1 .=" and m$nb_table.tag=@$tags[$i] and m$nb_table.subfieldcode='@$subfields[$i])";
  					}
  					$sql_where1.=")";
! 					$sql_where2 .= "m1.bibid=m$nb_table.bibid";
  				} elsif (@$operator[$i] eq "contains") {
! 					if (@$and_or[$i] eq 'and') {
! 						$nb_table++;
! 						$sql_tables .= "marc_word as m$nb_table,";
! 						$sql_where1 .= "@$and_or[$i] @$excluding[$i](m$nb_table.word like '@$value[$i]%'";
! 						if (@$tags[$i]) {
! 							$sql_where1 .="  and m$nb_table.tag=@$tags[$i] and m$nb_table.subfieldid='@$subfields[$i]'";
! 						}
! 						$sql_where1.=")";
! 						$sql_where2 .= "m1.bibid=m$nb_table.bibid";
! 					} else {
! 						$sql_where1 .= "@$and_or[$i] @$excluding[$i](m$nb_table.word like '@$value[$i]%'";
! 						if (@$tags[$i]) {
! 							$sql_where1 .="  and m$nb_table.tag=@$tags[$i] and m$nb_table.subfieldid='@$subfields[$i]'";
! 						}
! 						$sql_where1.=")";
! 						$sql_where2 .= "m1.bibid=m$nb_table.bibid";
  					}
  				} else {
! 					$nb_table++;
! 					$sql_tables .= "marc_subfield_table as m$nb_table,";
! 					$sql_where1 .= "@$and_or[$i] @$excluding[$i](m$nb_table.subfieldvalue @$operator[$i] '@$value[$i]'";
  					if (@$tags[$i]) {
! 						 $sql_where1 .="  and m$nb_table.tag=@$tags[$i] and m$nb_table.subfieldcode='@$subfields[$i]'";
  					}
! 					$sql_where2 .= "m1.bibid=m$nb_table.bibid";
  					$sql_where1.=")";
  				}
  			}
  		}
  	}