Re: [Koha-devel] new features to test

[paul POULAIN]

Stephen Hedges wrote:

> Paul -
>
> I've been testing the changes you made to search.marc/search.pl and
> SearchMarc.pm and thought you would like to see some results.
yep, thanks.

> First some questions.  The "query" that gets displayed as you are building
> the search is confusing.  It always seems to start with "biblioitems.illus
> contains ''."  Is that actually correct?  And does the query start by
> using the koha fields?  Is that where it's finding the tags to search on? 
> (I should have taken a closer look at your code.)
a bug, probably. that's why it's in HEAD branch :-D

> Also three general problems.  The marclist seems to be broken, since it is
> not available in the pull-down menu (neither in the default template nor
> the npl template).  And MARCdetail.pl never seems to display any results. 
> And all of the queries are followed in the log with this error:
>
> DBD::mysql::st execute failed: Unknown column 'seealso' in 'field list' at
> /usr/local/koha/intranet/modules//C4/Biblio.pm line 235.
> [sounds like we need to updatedatabase]
yes ;-)

> So now for some search results --
>
> * Search on keyword "dog" (worked OK):
> explain select distinct m1.bibid from
> biblio,biblioitems,marc_biblio,marc_word as m1 where
> biblio.biblionumber=marc_biblio.biblionumber and
> biblio.biblionumber=biblioitems.biblionumber and
> m1.bibid=marc_biblio.bibid and (m1.word  like 'dog%' and
> m1.tag+m1.subfieldid in ('')) order by biblio.title;
> +-------------+--------+----------------------+----------+---------+--------------------------+------+----------------------------------------------+
> | table       | type   | possible_keys        | key      | key_len | ref
>              | rows | Extra                                        |
> +-------------+--------+----------------------+----------+---------+--------------------------+------+----------------------------------------------+
> | m1          | range  | bibid,word           | word     |     255 | NULL
>              | 2200 | Using where; Using temporary; Using filesort |
> | marc_biblio | eq_ref | PRIMARY,biblionumber | PRIMARY  |       8 | m1.bibid
>              |    1 | Using where; Distinct                        |
> | biblio      | eq_ref | PRIMARY,blbnoidx     | PRIMARY  |       4 |
> marc_biblio.biblionumber |    1 | Distinct                                
>    |
> | biblioitems | ref    | bibnoidx             | bibnoidx |       4 |
> biblio.biblionumber      |   12 | Using index; Distinct                   
>    |
> +-------------+--------+----------------------+----------+---------+--------------------------+------+----------------------------------------------+
Nice result set : 2200 x 1 x 1 x12 rows to parse. Not too many.

> * Search on author "Henry" (worked OK):
<snip>

> * Search on author "Sue Henry" order by author (worked _very_ well!)
<snip>

> * Search on author "Sue Henry" order by title (worked _very_ well!)
<snip>

> * BUT NOTE -- Search on author "Henry, Sue" did NOT work:
> select distinct m1.bibid from biblio,biblioitems,marc_biblio,marc_word as
> m1,marc_word as m2 where biblio.biblionumber=marc_biblio.biblionumber and
> biblio.biblionumber=biblioitems.biblionumber and
> m1.bibid=marc_biblio.bibid and (m1.bibid=m2.bibid) and ((m1.word  like
> 'henry,%' and m1.tag+m1.subfieldid in ('100a'))and (m2.word like 'sue%'
> and m2.tag+m2.subfieldid in('100a'))) order by biblio.author
> [no results returned]
> * Search on title "I'll be home for Christmas" (failed):
> select distinct m1.bibid from biblio,biblioitems,marc_biblio,marc_word as
> m1,marc_word as m2,marc_word as m3 where
> biblio.biblionumber=marc_biblio.biblionumber and
> biblio.biblionumber=biblioitems.biblionumber and
> m1.bibid=marc_biblio.bibid and (m1.bibid=m2.bibid and m1.bibid=m3.bibid)
> and ((m1.word  like 'I'll%' and m1.tag+m1.subfieldid in ('245a'))and
> (m2.word like 'home%' and m2.tag+m2.subfieldid in('245a'))and (m3.word
> like 'Christmas%' and m3.tag+m3.subfieldid in('245a'))) order by
> biblio.title
> DBD::mysql::st execute failed: You have an error in your SQL syntax. 
> Check the manual that corresponds to your MySQL server version for the
> right syntax to use near 'll%' and m1.tag+m1.subfieldid in ('245a'))and
> (m2.word like 'ho
> [we need to escape apostrophes]
we need to remove ' and , and other useless characters. enter a 
bugs.koha.org pls

> * Search on barcode (worked very well):
> +-------------+--------+----------------------+----------+---------+--------------------------+------+----------------------------------------------+
> | table       | type   | possible_keys        | key      | key_len | ref
>              | rows | Extra                                        |
> +-------------+--------+----------------------+----------+---------+--------------------------+------+----------------------------------------------+
> | m1          | range  | bibid,word           | word     |     255 | NULL
>              |    1 | Using where; Using temporary; Using filesort |
> | marc_biblio | eq_ref | PRIMARY,biblionumber | PRIMARY  |       8 | m1.bibid
>              |    1 | Using where; Distinct                        |
> | biblio      | eq_ref | PRIMARY,blbnoidx     | PRIMARY  |       4 |
> marc_biblio.biblionumber |    1 | Distinct                                
>    |
> | biblioitems | ref    | bibnoidx             | bibnoidx |       4 |
> biblio.biblionumber      |   12 | Using index; Distinct                   
>    |
> +-------------+--------+----------------------+----------+---------+--------------------------+------+----------------------------------------------+
wonderful :1 x 1 x 1 x 12 rows to parse in the DB !!! For a 1Gb DB if 
your test DB is a copy of your real one ?

> * Search ("Q2") on keyword "England" and itemtype "AB" (failed--returned
> no results);
> explain select distinct m1.bibid from
> biblio,biblioitems,marc_biblio,marc_word as m1,marc_word as m2 where
> biblio.biblionumber=marc_biblio.biblionumber and
> biblio.biblionumber=biblioitems.biblionumber and
> m1.bibid=marc_biblio.bibid and (m1.bibid=m2.bibid) and ((m1.word  like
> 'england%' and m1.tag+m1.subfieldid in (''))and (m2.word like 'AB%' and
> m2.tag+m2.subfieldid in('item'))) order by biblio.title;
> +-------------+--------+----------------------+----------+---------+--------------------------+------+----------------------------------------------+
> | table       | type   | possible_keys        | key      | key_len | ref
>              | rows | Extra                                        |
> +-------------+--------+----------------------+----------+---------+--------------------------+------+----------------------------------------------+
> | m1          | range  | bibid,word           | word     |     255 | NULL
>              | 5377 | Using where; Using temporary; Using filesort |
> | marc_biblio | eq_ref | PRIMARY,biblionumber | PRIMARY  |       8 | m1.bibid
>              |    1 | Using where; Distinct                        |
> | biblio      | eq_ref | PRIMARY,blbnoidx     | PRIMARY  |       4 |
> marc_biblio.biblionumber |    1 | Distinct                                
>    |
> | biblioitems | ref    | bibnoidx             | bibnoidx |       4 |
> biblio.biblionumber      |   12 | Using index; Distinct                   
>    |
> | m2          | ref    | bibid,word           | bibid    |       8 | m1.bibid
>              |   20 | Using where; Distinct                        |
> +-------------+--------+----------------------+----------+---------+--------------------------+------+----------------------------------------------+
mmm... this one is important : the marc_word does NOT store words <= 2 
letters.

sub MARCaddword {
# split a subfield string and adds it into the word table.
# removes stopwords
   my ($dbh,$bibid,$tag,$tagorder,$subfieldid,$subfieldorder,$sentence) 
address@hidden;
   $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(?))");
   foreach my $word (@words) {
# we record only words longer than 2 car and not in stopwords hash
   if (length($word)>2 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";
       }
   }
   }
}

What should we decide here ?
* also store <=2 letters word.
* say that itemtype & other fields must be >2 chars ?

> Hope this helps --- Stephen
yes, thanks a lot.

--
Paul POULAIN
Consultant indépendant en logiciels libres
responsable francophone de koha (SIGB libre http://www.koha-fr.org)