[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<address@hidden;$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<address@hidden;$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 
address@hidden 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 
address@hidden 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 
address@hidden 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 
address@hidden 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 
address@hidden 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 
address@hidden 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 
address@hidden 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 
address@hidden 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 
address@hidden and m$nb_table.subfieldcode='@$subfields[$i]'";
                                        }
!                                       $sql_where2 .= 
"m1.bibid=m$nb_table.bibid";
                                        $sql_where1.=")";
                                }
                        }
                }
        }