1 package OpenILS::Application::Storage::Publisher::metabib;
2 use base qw/OpenILS::Application::Storage::Publisher/;
4 use OpenSRF::EX qw/:try/;
5 use OpenILS::Application::Storage::FTS;
6 use OpenILS::Utils::Fieldmapper;
7 use OpenSRF::Utils::Logger qw/:level/;
8 use OpenSRF::Utils::Cache;
10 use Digest::MD5 qw/md5_hex/;
13 my $log = 'OpenSRF::Utils::Logger';
17 sub ordered_records_from_metarecord {
28 my ($t, $f) = split '-', $formats;
29 @types = split '', $t;
30 @forms = split '', $f;
35 "actor.org_unit_descendants($org, $depth)" :
36 "actor.org_unit_descendants($org)" ;
39 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
40 $copies_visible = '' if ($self->api_name =~ /staff/o);
42 my $sm_table = metabib::metarecord_source_map->table;
43 my $rd_table = metabib::record_descriptor->table;
44 my $fr_table = metabib::full_rec->table;
45 my $cn_table = asset::call_number->table;
46 my $cl_table = asset::copy_location->table;
47 my $cp_table = asset::copy->table;
48 my $cs_table = config::copy_status->table;
49 my $out_table = actor::org_unit_type->table;
50 my $br_table = biblio::record_entry->table;
57 FIRST(COALESCE(LTRIM(SUBSTR( fr.value, COALESCE(SUBSTRING(fr.ind2 FROM '\\\\d+'),'0')::INT )),'zzzzzzzz')) AS title
60 if ($copies_visible) {
67 WHERE rd.record = sm.source
72 AND cn.record = rd.record
76 JOIN $cs_table cs ON (cp.status = cs.id)
77 JOIN $cl_table cl ON (cp.location = cl.id)
78 JOIN $descendants d ON (cp.circ_lib = d.id)
79 WHERE cn.id = cp.call_number
90 WHERE rd.record = sm.source
100 $sql .= ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
104 $sql .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
109 GROUP BY rd.record, rd.item_type, rd.item_form, br.quality
112 WHEN rd.item_type IS NULL -- default
114 WHEN rd.item_type = '' -- default
116 WHEN rd.item_type IN ('a','t') -- books
118 WHEN rd.item_type = 'g' -- movies
120 WHEN rd.item_type IN ('i','j') -- sound recordings
122 WHEN rd.item_type = 'm' -- software
124 WHEN rd.item_type = 'k' -- images
126 WHEN rd.item_type IN ('e','f') -- maps
128 WHEN rd.item_type IN ('o','p') -- mixed
130 WHEN rd.item_type IN ('c','d') -- music
132 WHEN rd.item_type = 'r' -- 3d
139 my $ids = metabib::metarecord_source_map->db_Main->selectcol_arrayref($sql, {}, "$mr", @types, @forms);
140 return $ids if ($self->api_name =~ /atomic$/o);
142 $client->respond( $_ ) for ( @$ids );
146 __PACKAGE__->register_method(
147 api_name => 'open-ils.storage.ordered.metabib.metarecord.records',
148 method => 'ordered_records_from_metarecord',
152 __PACKAGE__->register_method(
153 api_name => 'open-ils.storage.ordered.metabib.metarecord.records.staff',
154 method => 'ordered_records_from_metarecord',
159 __PACKAGE__->register_method(
160 api_name => 'open-ils.storage.ordered.metabib.metarecord.records.atomic',
161 method => 'ordered_records_from_metarecord',
165 __PACKAGE__->register_method(
166 api_name => 'open-ils.storage.ordered.metabib.metarecord.records.staff.atomic',
167 method => 'ordered_records_from_metarecord',
177 my $tag = ($self->api_name =~ /isbn/o) ? '020' : '022';
179 my $fr_table = metabib::full_rec->table;
188 my $list = metabib::metarecord_source_map->db_Main->selectcol_arrayref($sql, {}, $tag, "$isxn%");
189 $client->respond($_) for (@$list);
192 __PACKAGE__->register_method(
193 api_name => 'open-ils.storage.id_list.biblio.record_entry.search.isbn',
194 method => 'isxn_search',
198 __PACKAGE__->register_method(
199 api_name => 'open-ils.storage.id_list.biblio.record_entry.search.issn',
200 method => 'isxn_search',
205 sub metarecord_copy_count {
211 my $sm_table = metabib::metarecord_source_map->table;
212 my $rd_table = metabib::record_descriptor->table;
213 my $cn_table = asset::call_number->table;
214 my $cp_table = asset::copy->table;
215 my $cl_table = asset::copy_location->table;
216 my $cs_table = config::copy_status->table;
217 my $out_table = actor::org_unit_type->table;
218 my $descendants = "actor.org_unit_descendants(u.id)";
219 my $ancestors = "actor.org_unit_ancestors(?)";
221 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
222 $copies_visible = '' if ($self->api_name =~ /staff/o);
225 my ($t_filter, $f_filter) = ('','');
228 my ($t, $f) = split '-', $args{format};
229 @types = split '', $t;
230 @forms = split '', $f;
232 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
236 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
246 JOIN $cn_table cn ON (cn.record = r.source)
247 JOIN $rd_table rd ON (cn.record = rd.record)
248 JOIN $cp_table cp ON (cn.id = cp.call_number)
249 JOIN $cs_table cs ON (cp.status = cs.id)
250 JOIN $cl_table cl ON (cp.location = cl.id)
251 JOIN $descendants a ON (cp.circ_lib = a.id)
252 WHERE r.metarecord = ?
261 JOIN $cn_table cn ON (cn.record = r.source)
262 JOIN $rd_table rd ON (cn.record = rd.record)
263 JOIN $cp_table cp ON (cn.id = cp.call_number)
264 JOIN $cs_table cs ON (cp.status = cs.id)
265 JOIN $cl_table cl ON (cp.location = cl.id)
266 JOIN $descendants a ON (cp.circ_lib = a.id)
267 WHERE r.metarecord = ?
276 JOIN $out_table t ON (u.ou_type = t.id)
280 my $sth = metabib::metarecord_source_map->db_Main->prepare_cached($sql);
281 $sth->execute( ''.$args{metarecord},
284 ''.$args{metarecord},
290 while ( my $row = $sth->fetchrow_hashref ) {
291 $client->respond( $row );
295 __PACKAGE__->register_method(
296 api_name => 'open-ils.storage.metabib.metarecord.copy_count',
297 method => 'metarecord_copy_count',
302 __PACKAGE__->register_method(
303 api_name => 'open-ils.storage.metabib.metarecord.copy_count.staff',
304 method => 'metarecord_copy_count',
310 sub biblio_multi_search_full_rec {
315 my $class_join = $args{class_join} || 'AND';
316 my $limit = $args{limit} || 100;
317 my $offset = $args{offset} || 0;
318 my $sort = $args{'sort'};
319 my $sort_dir = $args{sort_dir} || 'DESC';
324 for my $arg (@{ $args{searches} }) {
325 my $term = $$arg{term};
326 my $limiters = $$arg{restrict};
328 my ($index_col) = metabib::full_rec->columns('FTS');
329 $index_col ||= 'value';
330 my $search_table = metabib::full_rec->table;
332 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
334 my $fts_where = $fts->sql_where_clause();
335 my @fts_ranks = $fts->fts_rank;
337 my $rank = join(' + ', @fts_ranks);
340 for my $limit (@$limiters) {
341 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
342 push @binds, $$limit{tag}, $$limit{subfield};
343 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
345 my $where = join(' OR ', @wheres);
347 push @selects, "SELECT id, record, $rank as sum FROM $search_table WHERE $where";
351 my $descendants = defined($args{depth}) ?
352 "actor.org_unit_descendants($args{org_unit}, $args{depth})" :
353 "actor.org_unit_descendants($args{org_unit})" ;
356 my $metabib_record_descriptor = metabib::record_descriptor->table;
357 my $metabib_full_rec = metabib::full_rec->table;
358 my $asset_call_number_table = asset::call_number->table;
359 my $asset_copy_table = asset::copy->table;
360 my $cs_table = config::copy_status->table;
361 my $cl_table = asset::copy_location->table;
362 my $br_table = biblio::record_entry->table;
364 my $cj = 'HAVING COUNT(x.id) = ' . scalar(@selects) if ($class_join eq 'AND');
366 '(SELECT x.record, sum(x.sum) FROM (('.
367 join(') UNION ALL (', @selects).
368 ")) x GROUP BY 1 $cj ORDER BY 2 DESC )";
370 my $has_vols = 'AND cn.owning_lib = d.id';
371 my $has_copies = 'AND cp.call_number = cn.id';
372 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
374 if ($self->api_name =~ /staff/o) {
375 $copies_visible = '';
376 $has_copies = '' if ($ou_type == 0);
377 $has_vols = '' if ($ou_type == 0);
380 my ($t_filter, $f_filter) = ('','');
381 my ($a_filter, $l_filter, $lf_filter) = ('','','');
383 if (my $a = $args{audience}) {
384 $a = [$a] if (!ref($a));
387 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
391 if (my $l = $args{language}) {
392 $l = [$l] if (!ref($l));
395 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
399 if (my $f = $args{lit_form}) {
400 $f = [$f] if (!ref($f));
403 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
404 push @binds, @lit_form;
407 if (my $f = $args{item_form}) {
408 $f = [$f] if (!ref($f));
411 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
415 if (my $t = $args{item_type}) {
416 $t = [$t] if (!ref($t));
419 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
425 my ($t, $f) = split '-', $args{format};
426 my @types = split '', $t;
427 my @forms = split '', $f;
429 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
433 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
435 push @binds, @types, @forms;
438 my $relevance = 'sum(f.sum)';
439 $relevance = 1 if (!$copies_visible);
441 my $rank = $relevance;
442 if (lc($sort) eq 'pubdate') {
445 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
446 FROM $metabib_full_rec frp
447 WHERE frp.record = f.record
449 AND frp.subfield = 'c'
453 } elsif (lc($sort) eq 'create_date') {
455 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = f.record)) )
457 } elsif (lc($sort) eq 'edit_date') {
459 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = f.record)) )
461 } elsif (lc($sort) eq 'title') {
464 SELECT COALESCE(LTRIM(SUBSTR( frt.value, COALESCE(SUBSTRING(frt.ind2 FROM '\\\\d+'),'0')::INT )),'zzzzzzzz')
465 FROM $metabib_full_rec frt
466 WHERE frt.record = f.record
468 AND frt.subfield = 'a'
472 } elsif (lc($sort) eq 'author') {
475 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
476 FROM $metabib_full_rec fra
477 WHERE fra.record = f.record
478 AND fra.tag LIKE '1%'
479 AND fra.subfield = 'a'
480 ORDER BY fra.tag::text::int
489 if ($copies_visible) {
491 SELECT f.record, $relevance, count(DISTINCT cp.id), $rank
492 FROM $search_table f,
493 $asset_call_number_table cn,
494 $asset_copy_table cp,
498 $metabib_record_descriptor rd,
500 WHERE br.id = f.record
501 AND cn.record = f.record
502 AND rd.record = f.record
503 AND cp.status = cs.id
504 AND cp.location = cl.id
505 AND br.deleted IS FALSE
506 AND cn.deleted IS FALSE
507 AND cp.deleted IS FALSE
516 GROUP BY f.record HAVING count(DISTINCT cp.id) > 0
517 ORDER BY 4 $sort_dir,3 DESC
521 SELECT f.record, 1, 1, $rank
522 FROM $search_table f,
524 $metabib_record_descriptor rd
525 WHERE br.id = f.record
526 AND rd.record = f.record
527 AND br.deleted IS FALSE
539 $log->debug("Search SQL :: [$select]",DEBUG);
541 my $recs = metabib::full_rec->db_Main->selectall_arrayref("$select;", {}, @binds);
542 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
545 $max = 1 if (!@$recs);
547 $max = $$_[1] if ($$_[1] > $max);
551 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
552 next unless ($$rec[0]);
553 my ($rid,$rank,$junk,$skip) = @$rec;
554 $client->respond( [$rid, sprintf('%0.3f',$rank/$max), $count] );
558 __PACKAGE__->register_method(
559 api_name => 'open-ils.storage.biblio.full_rec.multi_search',
560 method => 'biblio_multi_search_full_rec',
565 __PACKAGE__->register_method(
566 api_name => 'open-ils.storage.biblio.full_rec.multi_search.staff',
567 method => 'biblio_multi_search_full_rec',
573 sub search_full_rec {
579 my $term = $args{term};
580 my $limiters = $args{restrict};
582 my ($index_col) = metabib::full_rec->columns('FTS');
583 $index_col ||= 'value';
584 my $search_table = metabib::full_rec->table;
586 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
588 my $fts_where = $fts->sql_where_clause();
589 my @fts_ranks = $fts->fts_rank;
591 my $rank = join(' + ', @fts_ranks);
595 for my $limit (@$limiters) {
596 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
597 push @binds, $$limit{tag}, $$limit{subfield};
598 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
600 my $where = join(' OR ', @wheres);
602 my $select = "SELECT record, sum($rank) FROM $search_table WHERE $where GROUP BY 1 ORDER BY 2 DESC;";
604 $log->debug("Search SQL :: [$select]",DEBUG);
606 my $recs = metabib::full_rec->db_Main->selectall_arrayref($select, {}, @binds);
607 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
609 $client->respond($_) for (@$recs);
612 __PACKAGE__->register_method(
613 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.value',
614 method => 'search_full_rec',
619 __PACKAGE__->register_method(
620 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.index_vector',
621 method => 'search_full_rec',
628 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
629 sub search_class_fts {
634 my $term = $args{term};
635 my $ou = $args{org_unit};
636 my $ou_type = $args{depth};
637 my $limit = $args{limit};
638 my $offset = $args{offset};
640 my $limit_clause = '';
641 my $offset_clause = '';
643 $limit_clause = "LIMIT $limit" if (defined $limit and int($limit) > 0);
644 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
647 my ($t_filter, $f_filter) = ('','');
650 my ($t, $f) = split '-', $args{format};
651 @types = split '', $t;
652 @forms = split '', $f;
654 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
658 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
664 my $descendants = defined($ou_type) ?
665 "actor.org_unit_descendants($ou, $ou_type)" :
666 "actor.org_unit_descendants($ou)";
668 my $class = $self->{cdbi};
669 my $search_table = $class->table;
671 my $metabib_record_descriptor = metabib::record_descriptor->table;
672 my $metabib_metarecord = metabib::metarecord->table;
673 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
674 my $asset_call_number_table = asset::call_number->table;
675 my $asset_copy_table = asset::copy->table;
676 my $cs_table = config::copy_status->table;
677 my $cl_table = asset::copy_location->table;
679 my ($index_col) = $class->columns('FTS');
680 $index_col ||= 'value';
682 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
684 my $fts_where = $fts->sql_where_clause;
685 my @fts_ranks = $fts->fts_rank;
687 my $rank = join(' + ', @fts_ranks);
689 my $has_vols = 'AND cn.owning_lib = d.id';
690 my $has_copies = 'AND cp.call_number = cn.id';
691 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
693 my $visible_count = ', count(DISTINCT cp.id)';
694 my $visible_count_test = 'HAVING count(DISTINCT cp.id) > 0';
696 if ($self->api_name =~ /staff/o) {
697 $copies_visible = '';
698 $visible_count_test = '';
699 $has_copies = '' if ($ou_type == 0);
700 $has_vols = '' if ($ou_type == 0);
703 my $rank_calc = <<" RANK";
705 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
706 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
707 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
708 )/COUNT(m.source)), MIN(COALESCE(CHAR_LENGTH(f.value),1))
711 $rank_calc = ',1 , 1' if ($self->api_name =~ /unordered/o);
713 if ($copies_visible) {
715 SELECT m.metarecord $rank_calc $visible_count, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
716 FROM $search_table f,
717 $metabib_metarecord_source_map_table m,
718 $asset_call_number_table cn,
719 $asset_copy_table cp,
722 $metabib_record_descriptor rd,
725 AND m.source = f.source
726 AND cn.record = m.source
727 AND rd.record = m.source
728 AND cp.status = cs.id
729 AND cp.location = cl.id
735 GROUP BY 1 $visible_count_test
737 $limit_clause $offset_clause
741 SELECT m.metarecord $rank_calc, 0, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
742 FROM $search_table f,
743 $metabib_metarecord_source_map_table m,
744 $metabib_record_descriptor rd
746 AND m.source = f.source
747 AND rd.record = m.source
752 $limit_clause $offset_clause
756 $log->debug("Field Search SQL :: [$select]",DEBUG);
758 my $SQLstring = join('%',$fts->words);
759 my $REstring = join('\\s+',$fts->words);
760 my $first_word = ($fts->words)[0].'%';
761 my $recs = ($self->api_name =~ /unordered/o) ?
762 $class->db_Main->selectall_arrayref($select, {}, @types, @forms) :
763 $class->db_Main->selectall_arrayref($select, {},
764 '%'.lc($SQLstring).'%', # phrase order match
765 lc($first_word), # first word match
766 '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match
770 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
772 $client->respond($_) for (map { [@$_[0,1,3,4]] } @$recs);
776 for my $class ( qw/title author subject keyword series/ ) {
777 __PACKAGE__->register_method(
778 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord",
779 method => 'search_class_fts',
782 cdbi => "metabib::${class}_field_entry",
785 __PACKAGE__->register_method(
786 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.unordered",
787 method => 'search_class_fts',
790 cdbi => "metabib::${class}_field_entry",
793 __PACKAGE__->register_method(
794 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff",
795 method => 'search_class_fts',
798 cdbi => "metabib::${class}_field_entry",
801 __PACKAGE__->register_method(
802 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff.unordered",
803 method => 'search_class_fts',
806 cdbi => "metabib::${class}_field_entry",
811 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
812 sub search_class_fts_count {
817 my $term = $args{term};
818 my $ou = $args{org_unit};
819 my $ou_type = $args{depth};
820 my $limit = $args{limit} || 100;
821 my $offset = $args{offset} || 0;
823 my $descendants = defined($ou_type) ?
824 "actor.org_unit_descendants($ou, $ou_type)" :
825 "actor.org_unit_descendants($ou)";
828 my ($t_filter, $f_filter) = ('','');
831 my ($t, $f) = split '-', $args{format};
832 @types = split '', $t;
833 @forms = split '', $f;
835 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
839 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
844 (my $search_class = $self->api_name) =~ s/.*metabib.(\w+).search_fts.*/$1/o;
846 my $class = $self->{cdbi};
847 my $search_table = $class->table;
849 my $metabib_record_descriptor = metabib::record_descriptor->table;
850 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
851 my $asset_call_number_table = asset::call_number->table;
852 my $asset_copy_table = asset::copy->table;
853 my $cs_table = config::copy_status->table;
854 my $cl_table = asset::copy_location->table;
856 my ($index_col) = $class->columns('FTS');
857 $index_col ||= 'value';
859 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
861 my $fts_where = $fts->sql_where_clause;
863 my $has_vols = 'AND cn.owning_lib = d.id';
864 my $has_copies = 'AND cp.call_number = cn.id';
865 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
866 if ($self->api_name =~ /staff/o) {
867 $copies_visible = '';
868 $has_vols = '' if ($ou_type == 0);
869 $has_copies = '' if ($ou_type == 0);
872 # XXX test an "EXISTS version of descendant checking...
874 if ($copies_visible) {
876 SELECT count(distinct m.metarecord)
877 FROM $search_table f,
878 $metabib_metarecord_source_map_table m,
879 $metabib_metarecord_source_map_table mr,
880 $asset_call_number_table cn,
881 $asset_copy_table cp,
884 $metabib_record_descriptor rd,
887 AND mr.source = f.source
888 AND mr.metarecord = m.metarecord
889 AND cn.record = m.source
890 AND rd.record = m.source
891 AND cp.status = cs.id
892 AND cp.location = cl.id
901 SELECT count(distinct m.metarecord)
902 FROM $search_table f,
903 $metabib_metarecord_source_map_table m,
904 $metabib_metarecord_source_map_table mr,
905 $metabib_record_descriptor rd
907 AND mr.source = f.source
908 AND mr.metarecord = m.metarecord
909 AND rd.record = m.source
915 $log->debug("Field Search Count SQL :: [$select]",DEBUG);
917 my $recs = $class->db_Main->selectrow_arrayref($select, {}, @types, @forms)->[0];
919 $log->debug("Count Search yielded $recs results.",DEBUG);
924 for my $class ( qw/title author subject keyword series/ ) {
925 __PACKAGE__->register_method(
926 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count",
927 method => 'search_class_fts_count',
930 cdbi => "metabib::${class}_field_entry",
933 __PACKAGE__->register_method(
934 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count.staff",
935 method => 'search_class_fts_count',
938 cdbi => "metabib::${class}_field_entry",
944 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
945 sub postfilter_search_class_fts {
950 my $term = $args{term};
951 my $sort = $args{'sort'};
952 my $sort_dir = $args{sort_dir} || 'DESC';
953 my $ou = $args{org_unit};
954 my $ou_type = $args{depth};
955 my $limit = $args{limit} || 10;
956 my $offset = $args{offset} || 0;
958 my $outer_limit = 1000;
960 my $limit_clause = '';
961 my $offset_clause = '';
963 $limit_clause = "LIMIT $outer_limit";
964 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
966 my (@types,@forms,@lang,@aud,@lit_form);
967 my ($t_filter, $f_filter) = ('','');
968 my ($a_filter, $l_filter, $lf_filter) = ('','','');
969 my ($ot_filter, $of_filter) = ('','');
970 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
972 if (my $a = $args{audience}) {
973 $a = [$a] if (!ref($a));
976 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
977 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
980 if (my $l = $args{language}) {
981 $l = [$l] if (!ref($l));
984 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
985 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
988 if (my $f = $args{lit_form}) {
989 $f = [$f] if (!ref($f));
992 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
993 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
997 my ($t, $f) = split '-', $args{format};
998 @types = split '', $t;
999 @forms = split '', $f;
1001 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1002 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1006 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1007 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1012 my $descendants = defined($ou_type) ?
1013 "actor.org_unit_descendants($ou, $ou_type)" :
1014 "actor.org_unit_descendants($ou)";
1016 my $class = $self->{cdbi};
1017 my $search_table = $class->table;
1019 my $metabib_full_rec = metabib::full_rec->table;
1020 my $metabib_record_descriptor = metabib::record_descriptor->table;
1021 my $metabib_metarecord = metabib::metarecord->table;
1022 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1023 my $asset_call_number_table = asset::call_number->table;
1024 my $asset_copy_table = asset::copy->table;
1025 my $cs_table = config::copy_status->table;
1026 my $cl_table = asset::copy_location->table;
1027 my $br_table = biblio::record_entry->table;
1029 my ($index_col) = $class->columns('FTS');
1030 $index_col ||= 'value';
1032 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
1034 my $SQLstring = join('%',map { lc($_) } $fts->words);
1035 my $REstring = '^' . join('\s+',map { lc($_) } $fts->words) . '\W*$';
1036 my $first_word = lc(($fts->words)[0]).'%';
1038 my $fts_where = $fts->sql_where_clause;
1039 my @fts_ranks = $fts->fts_rank;
1042 $bonus{'metabib::keyword_field_entry'} = [ { 'CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END' => $SQLstring } ];
1043 $bonus{'metabib::title_field_entry'} =
1044 $bonus{'metabib::series_field_entry'} = [
1045 { 'CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END' => $first_word },
1046 { 'CASE WHEN f.value ~* ? THEN 2 ELSE 1 END' => $REstring },
1047 @{ $bonus{'metabib::keyword_field_entry'} }
1050 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$class} };
1051 $bonus_list ||= '1';
1053 my @bonus_values = map { values %$_ } @{ $bonus{$class} };
1055 my $relevance = join(' + ', @fts_ranks);
1056 $relevance = <<" RANK";
1057 (SUM( ( $relevance ) * ( $bonus_list ) )/COUNT(m.source))
1060 my $rank = $relevance;
1061 if (lc($sort) eq 'pubdate') {
1064 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
1065 FROM $metabib_full_rec frp
1066 WHERE frp.record = mr.master_record
1068 AND frp.subfield = 'c'
1072 } elsif (lc($sort) eq 'create_date') {
1074 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1076 } elsif (lc($sort) eq 'edit_date') {
1078 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1080 } elsif (lc($sort) eq 'title') {
1083 SELECT COALESCE(LTRIM(SUBSTR( frt.value, COALESCE(SUBSTRING(frt.ind2 FROM '\\\\d+'),'0')::INT )),'zzzzzzzz')
1084 FROM $metabib_full_rec frt
1085 WHERE frt.record = mr.master_record
1087 AND frt.subfield = 'a'
1091 } elsif (lc($sort) eq 'author') {
1094 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1095 FROM $metabib_full_rec fra
1096 WHERE fra.record = mr.master_record
1097 AND fra.tag LIKE '1%'
1098 AND fra.subfield = 'a'
1099 ORDER BY fra.tag::text::int
1107 my $select = <<" SQL";
1108 SELECT m.metarecord,
1110 CASE WHEN COUNT(DISTINCT smrs.source) = 1 THEN MIN(m.source) ELSE 0 END,
1112 FROM $search_table f,
1113 $metabib_metarecord_source_map_table m,
1114 $metabib_metarecord_source_map_table smrs,
1115 $metabib_metarecord mr,
1116 $metabib_record_descriptor rd
1118 AND smrs.metarecord = mr.id
1119 AND m.source = f.source
1120 AND m.metarecord = mr.id
1121 AND rd.record = smrs.source
1127 GROUP BY m.metarecord
1128 ORDER BY 4 $sort_dir, MIN(COALESCE(CHAR_LENGTH(f.value),1))
1136 FROM $asset_call_number_table cn,
1137 $metabib_metarecord_source_map_table mrs,
1138 $asset_copy_table cp,
1143 $metabib_record_descriptor ord,
1145 WHERE mrs.metarecord = s.metarecord
1146 AND br.id = mrs.source
1147 AND cn.record = mrs.source
1148 AND cp.status = cs.id
1149 AND cp.location = cl.id
1150 AND cn.owning_lib = d.id
1151 AND cp.call_number = cn.id
1152 AND cp.opac_visible IS TRUE
1153 AND cs.holdable IS TRUE
1154 AND cl.opac_visible IS TRUE
1155 AND br.active IS TRUE
1156 AND br.deleted IS FALSE
1157 AND ord.record = mrs.source
1163 ORDER BY 4 $sort_dir
1165 } elsif ($self->api_name !~ /staff/o) {
1172 FROM $asset_call_number_table cn,
1173 $metabib_metarecord_source_map_table mrs,
1174 $asset_copy_table cp,
1179 $metabib_record_descriptor ord
1181 WHERE mrs.metarecord = s.metarecord
1182 AND br.id = mrs.source
1183 AND cn.record = mrs.source
1184 AND cp.status = cs.id
1185 AND cp.location = cl.id
1186 AND cn.owning_lib = d.id
1187 AND cp.call_number = cn.id
1188 AND cp.opac_visible IS TRUE
1189 AND cs.holdable IS TRUE
1190 AND cl.opac_visible IS TRUE
1191 AND br.active IS TRUE
1192 AND br.deleted IS FALSE
1193 AND ord.record = mrs.source
1201 ORDER BY 4 $sort_dir
1210 FROM $asset_call_number_table cn,
1211 $metabib_metarecord_source_map_table mrs,
1214 $metabib_record_descriptor ord
1216 WHERE mrs.metarecord = s.metarecord
1217 AND br.id = mrs.source
1218 AND cn.record = mrs.source
1219 AND cn.owning_lib = d.id
1220 AND br.deleted IS FALSE
1221 AND ord.record = mrs.source
1231 FROM $asset_call_number_table cn,
1232 $metabib_metarecord_source_map_table mrs,
1233 $metabib_record_descriptor ord
1234 WHERE mrs.metarecord = s.metarecord
1235 AND cn.record = mrs.source
1236 AND ord.record = mrs.source
1244 ORDER BY 4 $sort_dir
1249 $log->debug("Field Search SQL :: [$select]",DEBUG);
1251 my $recs = $class->db_Main->selectall_arrayref(
1253 (@bonus_values > 0 ? @bonus_values : () ),
1254 ( (!$sort && @bonus_values > 0) ? @bonus_values : () ),
1255 @types, @forms, @aud, @lang, @lit_form,
1256 @types, @forms, @aud, @lang, @lit_form,
1257 ($self->api_name =~ /staff/o ? (@types, @forms, @aud, @lang, @lit_form) : () ) );
1259 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1262 $max = 1 if (!@$recs);
1264 $max = $$_[1] if ($$_[1] > $max);
1267 my $count = scalar(@$recs);
1268 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1269 my ($mrid,$rank,$skip) = @$rec;
1270 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $skip, $count] );
1275 for my $class ( qw/title author subject keyword series/ ) {
1276 __PACKAGE__->register_method(
1277 api_name => "open-ils.storage.metabib.$class.post_filter.search_fts.metarecord",
1278 method => 'postfilter_search_class_fts',
1281 cdbi => "metabib::${class}_field_entry",
1284 __PACKAGE__->register_method(
1285 api_name => "open-ils.storage.metabib.$class.post_filter.search_fts.metarecord.staff",
1286 method => 'postfilter_search_class_fts',
1289 cdbi => "metabib::${class}_field_entry",
1296 my $_cdbi = { title => "metabib::title_field_entry",
1297 author => "metabib::author_field_entry",
1298 subject => "metabib::subject_field_entry",
1299 keyword => "metabib::keyword_field_entry",
1300 series => "metabib::series_field_entry",
1303 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
1304 sub postfilter_search_multi_class_fts {
1309 my $sort = $args{'sort'};
1310 my $sort_dir = $args{sort_dir} || 'DESC';
1311 my $ou = $args{org_unit};
1312 my $ou_type = $args{depth};
1313 my $limit = $args{limit} || 10;;
1314 my $offset = $args{offset} || 0;
1317 $ou = actor::org_unit->search( { parent_ou => undef } )->next->id;
1320 if (!defined($args{org_unit})) {
1321 die "No target organizational unit passed to ".$self->api_name;
1324 if (! scalar( keys %{$args{searches}} )) {
1325 die "No search arguments were passed to ".$self->api_name;
1328 my $outer_limit = 1000;
1330 my $limit_clause = '';
1331 my $offset_clause = '';
1333 $limit_clause = "LIMIT $outer_limit";
1334 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
1336 my (@types,@forms,@lang,@aud,@lit_form);
1337 my ($t_filter, $f_filter) = ('','');
1338 my ($a_filter, $l_filter, $lf_filter) = ('','','');
1339 my ($ot_filter, $of_filter) = ('','');
1340 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
1342 if (my $a = $args{audience}) {
1343 $a = [$a] if (!ref($a));
1346 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
1347 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
1350 if (my $l = $args{language}) {
1351 $l = [$l] if (!ref($l));
1354 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1355 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1358 if (my $f = $args{lit_form}) {
1359 $f = [$f] if (!ref($f));
1362 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1363 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1366 if (my $f = $args{item_form}) {
1367 $f = [$f] if (!ref($f));
1370 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1371 $of_filter = ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1374 if (my $t = $args{item_type}) {
1375 $t = [$t] if (!ref($t));
1378 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1379 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1383 # XXX legacy format and item type support
1384 if ($args{format}) {
1385 my ($t, $f) = split '-', $args{format};
1386 @types = split '', $t;
1387 @forms = split '', $f;
1389 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1390 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1394 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1395 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1401 my $descendants = defined($ou_type) ?
1402 "actor.org_unit_descendants($ou, $ou_type)" :
1403 "actor.org_unit_descendants($ou)";
1405 my $search_table_list = '';
1407 my $join_table_list = '';
1412 my $prev_search_class;
1413 my $curr_search_class;
1414 for my $search_class (sort keys %{$args{searches}}) {
1415 $prev_search_class = $curr_search_class if ($curr_search_class);
1417 $curr_search_class = $search_class;
1419 my $class = $_cdbi->{$search_class};
1420 my $search_table = $class->table;
1422 my ($index_col) = $class->columns('FTS');
1423 $index_col ||= 'value';
1426 my $fts = OpenILS::Application::Storage::FTS->compile($args{searches}{$search_class}{term}, $search_class.'.value', "$search_class.$index_col");
1428 my $fts_where = $fts->sql_where_clause;
1429 my @fts_ranks = $fts->fts_rank;
1431 my $SQLstring = join('%',map { lc($_) } $fts->words);
1432 my $REstring = '^' . join('\s+',map { lc($_) } $fts->words) . '\W*$';
1433 my $first_word = lc(($fts->words)[0]).'%';
1435 my $rank = join(' + ', @fts_ranks);
1438 $bonus{'keyword'} = [ { "CASE WHEN $search_class.value LIKE ? THEN 1.2 ELSE 1 END" => $SQLstring } ];
1440 $bonus{'series'} = [
1441 { "CASE WHEN $search_class.value LIKE ? THEN 1.5 ELSE 1 END" => $first_word },
1442 { "CASE WHEN $search_class.value ~ ? THEN 1000 ELSE 1 END" => $REstring },
1445 $bonus{'title'} = [ @{ $bonus{'series'} }, @{ $bonus{'keyword'} } ];
1447 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$search_class} };
1448 $bonus_list ||= '1';
1450 push @bonus_lists, $bonus_list;
1451 push @bonus_values, map { values %$_ } @{ $bonus{$search_class} };
1454 #---------------------
1456 $search_table_list .= "$search_table $search_class, ";
1457 push @rank_list,$rank;
1458 $fts_list .= " AND $fts_where AND m.source = $search_class.source";
1460 if ($prev_search_class) {
1461 $join_table_list .= " AND $prev_search_class.source = $curr_search_class.source";
1465 my $metabib_record_descriptor = metabib::record_descriptor->table;
1466 my $metabib_full_rec = metabib::full_rec->table;
1467 my $metabib_metarecord = metabib::metarecord->table;
1468 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1469 my $asset_call_number_table = asset::call_number->table;
1470 my $asset_copy_table = asset::copy->table;
1471 my $cs_table = config::copy_status->table;
1472 my $cl_table = asset::copy_location->table;
1473 my $br_table = biblio::record_entry->table;
1475 my $bonuses = join (' * ', @bonus_lists);
1476 my $relevance = join (' + ', @rank_list);
1477 $relevance = "SUM( ($relevance) * ($bonuses) )/COUNT(DISTINCT m.source)";
1480 my $secondary_sort = <<" SORT";
1482 SELECT COALESCE(LTRIM(SUBSTR( sfrt.value, COALESCE(SUBSTRING(sfrt.ind2 FROM '\\\\d+'),'0')::INT )),'zzzzzzzz')
1483 FROM $metabib_full_rec sfrt,
1484 $metabib_metarecord mr
1485 WHERE sfrt.record = mr.master_record
1486 AND sfrt.tag = '245'
1487 AND sfrt.subfield = 'a'
1492 my $rank = $relevance;
1493 if (lc($sort) eq 'pubdate') {
1496 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
1497 FROM $metabib_full_rec frp
1498 WHERE frp.record = mr.master_record
1500 AND frp.subfield = 'c'
1504 } elsif (lc($sort) eq 'create_date') {
1506 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1508 } elsif (lc($sort) eq 'edit_date') {
1510 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1512 } elsif (lc($sort) eq 'title') {
1515 SELECT COALESCE(LTRIM(SUBSTR( frt.value, COALESCE(SUBSTRING(frt.ind2 FROM '\\\\d+'),'0')::INT )),'zzzzzzzz')
1516 FROM $metabib_full_rec frt
1517 WHERE frt.record = mr.master_record
1519 AND frt.subfield = 'a'
1523 $secondary_sort = <<" SORT";
1525 SELECT COALESCE(SUBSTRING(sfrp.value FROM '\\\\d+'),'9999')::INT
1526 FROM $metabib_full_rec sfrp,
1527 $metabib_metarecord mr
1528 WHERE sfrp.record = mr.master_record
1529 AND sfrp.tag = '260'
1530 AND sfrp.subfield = 'c'
1534 } elsif (lc($sort) eq 'author') {
1537 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1538 FROM $metabib_full_rec fra
1539 WHERE fra.record = mr.master_record
1540 AND fra.tag LIKE '1%'
1541 AND fra.subfield = 'a'
1542 ORDER BY fra.tag::text::int
1547 push @bonus_values, @bonus_values;
1552 my $select = <<" SQL";
1553 SELECT m.metarecord,
1555 CASE WHEN COUNT(DISTINCT smrs.source) = 1 THEN FIRST(m.source) ELSE 0 END,
1558 FROM $search_table_list
1559 $metabib_metarecord_source_map_table m,
1560 $metabib_metarecord_source_map_table smrs
1561 WHERE m.metarecord = smrs.metarecord
1564 GROUP BY m.metarecord
1565 -- ORDER BY 4 $sort_dir
1569 if ($self->api_name !~ /staff/o) {
1576 FROM $asset_call_number_table cn,
1577 $metabib_metarecord_source_map_table mrs,
1578 $asset_copy_table cp,
1583 $metabib_record_descriptor ord
1584 WHERE mrs.metarecord = s.metarecord
1585 AND br.id = mrs.source
1586 AND cn.record = mrs.source
1587 AND cp.status = cs.id
1588 AND cp.location = cl.id
1589 AND cn.owning_lib = d.id
1590 AND cp.call_number = cn.id
1591 AND cp.opac_visible IS TRUE
1592 AND cs.holdable IS TRUE
1593 AND cl.opac_visible IS TRUE
1594 AND br.active IS TRUE
1595 AND br.deleted IS FALSE
1596 AND ord.record = mrs.source
1604 ORDER BY 4 $sort_dir, 5
1613 FROM $asset_call_number_table cn,
1614 $metabib_metarecord_source_map_table mrs,
1617 $metabib_record_descriptor ord
1618 WHERE mrs.metarecord = s.metarecord
1619 AND br.id = mrs.source
1620 AND cn.record = mrs.source
1621 AND cn.owning_lib = d.id
1622 AND ord.record = mrs.source
1623 AND br.deleted IS FALSE
1633 FROM $asset_call_number_table cn,
1634 $metabib_metarecord_source_map_table mrs,
1635 $metabib_record_descriptor ord
1636 WHERE mrs.metarecord = s.metarecord
1637 AND cn.record = mrs.source
1638 AND ord.record = mrs.source
1646 ORDER BY 4 $sort_dir, 5
1651 $log->debug("Field Search SQL :: [$select]",DEBUG);
1653 my $recs = $_cdbi->{title}->db_Main->selectall_arrayref(
1656 @types, @forms, @aud, @lang, @lit_form,
1657 # @types, @forms, @aud, @lang, @lit_form,
1658 ($self->api_name =~ /staff/o ? (@types, @forms, @aud, @lang, @lit_form) : () )
1661 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1664 $max = 1 if (!@$recs);
1666 $max = $$_[1] if ($$_[1] > $max);
1669 my $count = scalar(@$recs);
1670 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1671 next unless ($$rec[0]);
1672 my ($mrid,$rank,$skip) = @$rec;
1673 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $skip, $count] );
1678 __PACKAGE__->register_method(
1679 api_name => "open-ils.storage.metabib.post_filter.multiclass.search_fts.metarecord",
1680 method => 'postfilter_search_multi_class_fts',
1685 __PACKAGE__->register_method(
1686 api_name => "open-ils.storage.metabib.post_filter.multiclass.search_fts.metarecord.staff",
1687 method => 'postfilter_search_multi_class_fts',
1693 __PACKAGE__->register_method(
1694 api_name => "open-ils.storage.metabib.multiclass.search_fts",
1695 method => 'postfilter_search_multi_class_fts',
1700 __PACKAGE__->register_method(
1701 api_name => "open-ils.storage.metabib.multiclass.search_fts.staff",
1702 method => 'postfilter_search_multi_class_fts',
1708 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
1709 sub biblio_search_multi_class_fts {
1714 my $sort = $args{'sort'};
1715 my $sort_dir = $args{sort_dir} || 'DESC';
1716 my $ou = $args{org_unit};
1717 my $ou_type = $args{depth};
1718 my $limit = $args{limit} || 10;
1719 my $offset = $args{offset} || 0;
1722 $ou = actor::org_unit->search( { parent_ou => undef } )->next->id;
1725 if (!defined($args{org_unit})) {
1726 die "No target organizational unit passed to ".$self->api_name;
1729 if (! scalar( keys %{$args{searches}} )) {
1730 die "No search arguments were passed to ".$self->api_name;
1733 my $outer_limit = 1000;
1735 my $limit_clause = '';
1736 my $offset_clause = '';
1738 $limit_clause = "LIMIT $outer_limit";
1739 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
1741 my (@types,@forms,@lang,@aud,@lit_form);
1742 my ($t_filter, $f_filter) = ('','');
1743 my ($a_filter, $l_filter, $lf_filter) = ('','','');
1744 my ($ot_filter, $of_filter) = ('','');
1745 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
1747 if (my $a = $args{audience}) {
1748 $a = [$a] if (!ref($a));
1751 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
1752 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
1755 if (my $l = $args{language}) {
1756 $l = [$l] if (!ref($l));
1759 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1760 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1763 if (my $f = $args{lit_form}) {
1764 $f = [$f] if (!ref($f));
1767 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1768 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1771 if (my $f = $args{item_form}) {
1772 $f = [$f] if (!ref($f));
1775 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1776 $of_filter = ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1779 if (my $t = $args{item_type}) {
1780 $t = [$t] if (!ref($t));
1783 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1784 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1788 # XXX legacy format and item type support
1789 if ($args{format}) {
1790 my ($t, $f) = split '-', $args{format};
1791 @types = split '', $t;
1792 @forms = split '', $f;
1794 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1795 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1799 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1800 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1805 my $descendants = defined($ou_type) ?
1806 "actor.org_unit_descendants($ou, $ou_type)" :
1807 "actor.org_unit_descendants($ou)";
1809 my $search_table_list = '';
1811 my $join_table_list = '';
1817 my $prev_search_class;
1818 my $curr_search_class;
1819 for my $search_class (sort keys %{$args{searches}}) {
1820 $prev_search_class = $curr_search_class if ($curr_search_class);
1822 $curr_search_class = $search_class;
1824 my $class = $_cdbi->{$search_class};
1825 my $search_table = $class->table;
1827 my ($index_col) = $class->columns('FTS');
1828 $index_col ||= 'value';
1831 my $fts = OpenILS::Application::Storage::FTS->compile($args{searches}{$search_class}{term}, $search_class.'.value', "$search_class.$index_col");
1833 my $fts_where = $fts->sql_where_clause;
1834 my @fts_ranks = $fts->fts_rank;
1836 my $SQLstring = join('%',map { lc($_) } $fts->words);
1837 my $REstring = '^' . join('\s+',map { lc($_) } $fts->words) . '\W*$';
1838 my $first_word = lc(($fts->words)[0]).'%';
1840 my $rank = join(' + ', @fts_ranks);
1843 $bonus{'keyword'} = [ { "CASE WHEN $search_class.value ILIKE ? THEN 1.2 ELSE 1 END" => $SQLstring } ];
1845 $bonus{'series'} = [
1846 { "CASE WHEN $search_class.value ILIKE ? THEN 1.5 ELSE 1 END" => $first_word },
1847 { "CASE WHEN $search_class.value ~ ? THEN 200 ELSE 1 END" => $REstring },
1850 $bonus{'title'} = [ @{ $bonus{'series'} }, @{ $bonus{'keyword'} } ];
1852 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$search_class} };
1853 $bonus_list ||= '1';
1855 push @bonus_lists, $bonus_list;
1856 push @bonus_values, map { values %$_ } @{ $bonus{$search_class} };
1858 #---------------------
1860 $search_table_list .= "$search_table $search_class, ";
1861 push @rank_list,$rank;
1862 $fts_list .= " AND $fts_where AND b.id = $search_class.source";
1864 if ($prev_search_class) {
1865 $join_table_list .= " AND $prev_search_class.source = $curr_search_class.source";
1869 my $metabib_record_descriptor = metabib::record_descriptor->table;
1870 my $metabib_full_rec = metabib::full_rec->table;
1871 my $metabib_metarecord = metabib::metarecord->table;
1872 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1873 my $asset_call_number_table = asset::call_number->table;
1874 my $asset_copy_table = asset::copy->table;
1875 my $cs_table = config::copy_status->table;
1876 my $cl_table = asset::copy_location->table;
1877 my $br_table = biblio::record_entry->table;
1880 my $bonuses = join (' * ', @bonus_lists);
1881 my $relevance = join (' + ', @rank_list);
1882 $relevance = "AVG( ($relevance) * ($bonuses) )";
1885 my $rank = $relevance;
1886 if (lc($sort) eq 'pubdate') {
1889 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d{4}'),'9999')::INT
1890 FROM $metabib_full_rec frp
1891 WHERE frp.record = b.id
1893 AND frp.subfield = 'c'
1897 } elsif (lc($sort) eq 'create_date') {
1899 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = b.id)) )
1901 } elsif (lc($sort) eq 'edit_date') {
1903 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = b.id)) )
1905 } elsif (lc($sort) eq 'title') {
1908 SELECT COALESCE(LTRIM(SUBSTR( frt.value, COALESCE(SUBSTRING(frt.ind2 FROM '\\\\d+'),'0')::INT )),'zzzzzzzz')
1909 FROM $metabib_full_rec frt
1910 WHERE frt.record = b.id
1912 AND frt.subfield = 'a'
1916 } elsif (lc($sort) eq 'author') {
1919 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1920 FROM $metabib_full_rec fra
1921 WHERE fra.record = b.id
1922 AND fra.tag LIKE '1%'
1923 AND fra.subfield = 'a'
1924 ORDER BY fra.tag::text::int
1929 push @bonus_values, @bonus_values;
1934 my $select = <<" SQL";
1938 FROM $search_table_list
1939 $metabib_record_descriptor rd,
1941 WHERE rd.record = b.id
1942 AND b.active IS TRUE
1943 AND b.deleted IS FALSE
1952 ORDER BY 3 $sort_dir
1956 if ($self->api_name !~ /staff/o) {
1963 FROM $asset_call_number_table cn,
1964 $asset_copy_table cp,
1968 WHERE cn.record = s.id
1969 AND cp.status = cs.id
1970 AND cp.location = cl.id
1971 AND cn.owning_lib = d.id
1972 AND cp.call_number = cn.id
1973 AND cp.opac_visible IS TRUE
1974 AND cs.holdable IS TRUE
1975 AND cl.opac_visible IS TRUE
1976 AND cp.deleted IS FALSE
1979 ORDER BY 3 $sort_dir
1988 FROM $asset_call_number_table cn,
1990 WHERE cn.record = s.id
1991 AND cn.owning_lib = d.id
1996 FROM $asset_call_number_table cn
1997 WHERE cn.record = s.id
2000 ORDER BY 3 $sort_dir
2005 $log->debug("Field Search SQL :: [$select]",DEBUG);
2007 my $recs = $_cdbi->{title}->db_Main->selectall_arrayref(
2009 @bonus_values, @types, @forms, @aud, @lang, @lit_form
2012 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
2015 $max = 1 if (!@$recs);
2017 $max = $$_[1] if ($$_[1] > $max);
2020 my $count = scalar(@$recs);
2021 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
2022 next unless ($$rec[0]);
2023 my ($mrid,$rank) = @$rec;
2024 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $count] );
2029 __PACKAGE__->register_method(
2030 api_name => "open-ils.storage.biblio.multiclass.search_fts.record",
2031 method => 'biblio_search_multi_class_fts',
2036 __PACKAGE__->register_method(
2037 api_name => "open-ils.storage.biblio.multiclass.search_fts.record.staff",
2038 method => 'biblio_search_multi_class_fts',
2046 __PACKAGE__->register_method(
2047 api_name => "open-ils.storage.biblio.multiclass.search_fts",
2048 method => 'biblio_search_multi_class_fts',
2053 __PACKAGE__->register_method(
2054 api_name => "open-ils.storage.biblio.multiclass.search_fts.staff",
2055 method => 'biblio_search_multi_class_fts',