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( value, COALESCE(SUBSTRING(ind2 FROM '\\\\d+'),'0')::INT )),'zzzzzzzz')) AS title
69 if ($copies_visible) {
76 WHERE rd.record = sm.source
77 AND fr.record = sm.source
79 AND cn.record = sm.source
83 JOIN $cs_table cs ON (cp.status = cs.id)
84 JOIN $cl_table cl ON (cp.location = cl.id)
85 JOIN $descendants d ON (cp.circ_lib = d.id)
86 WHERE cn.id = cp.call_number
93 JOIN $br_table br ON (sm.source = br.id)
94 JOIN $fr_table fr ON (fr.record = br.id)
95 JOIN $rd_table rd ON (rd.record = br.id)
96 WHERE sm.metarecord = ?
102 WHERE cn.record = br.id
103 AND cn.deleted = FALSE
104 AND cp.deleted = FALSE
105 AND cp.circ_lib = d.id
106 AND cn.id = cp.call_number
112 WHERE cn.record = br.id
113 AND cn.deleted = FALSE
114 AND cp.deleted = FALSE
115 AND cn.id = cp.call_number
123 $sql .= ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
127 $sql .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
137 GROUP BY record, item_type, item_form, quality
140 WHEN item_type IS NULL -- default
142 WHEN item_type = '' -- default
144 WHEN item_type IN ('a','t') -- books
146 WHEN item_type = 'g' -- movies
148 WHEN item_type IN ('i','j') -- sound recordings
150 WHEN item_type = 'm' -- software
152 WHEN item_type = 'k' -- images
154 WHEN item_type IN ('e','f') -- maps
156 WHEN item_type IN ('o','p') -- mixed
158 WHEN item_type IN ('c','d') -- music
160 WHEN item_type = 'r' -- 3d
167 my $ids = metabib::metarecord_source_map->db_Main->selectcol_arrayref($sql, {}, "$mr", @types, @forms);
168 return $ids if ($self->api_name =~ /atomic$/o);
170 $client->respond( $_ ) for ( @$ids );
174 __PACKAGE__->register_method(
175 api_name => 'open-ils.storage.ordered.metabib.metarecord.records',
176 method => 'ordered_records_from_metarecord',
180 __PACKAGE__->register_method(
181 api_name => 'open-ils.storage.ordered.metabib.metarecord.records.staff',
182 method => 'ordered_records_from_metarecord',
187 __PACKAGE__->register_method(
188 api_name => 'open-ils.storage.ordered.metabib.metarecord.records.atomic',
189 method => 'ordered_records_from_metarecord',
193 __PACKAGE__->register_method(
194 api_name => 'open-ils.storage.ordered.metabib.metarecord.records.staff.atomic',
195 method => 'ordered_records_from_metarecord',
205 my $tag = ($self->api_name =~ /isbn/o) ? '020' : '022';
207 my $fr_table = metabib::full_rec->table;
216 my $list = metabib::metarecord_source_map->db_Main->selectcol_arrayref($sql, {}, $tag, "$isxn%");
217 $client->respond($_) for (@$list);
220 __PACKAGE__->register_method(
221 api_name => 'open-ils.storage.id_list.biblio.record_entry.search.isbn',
222 method => 'isxn_search',
226 __PACKAGE__->register_method(
227 api_name => 'open-ils.storage.id_list.biblio.record_entry.search.issn',
228 method => 'isxn_search',
233 sub metarecord_copy_count {
239 my $sm_table = metabib::metarecord_source_map->table;
240 my $rd_table = metabib::record_descriptor->table;
241 my $cn_table = asset::call_number->table;
242 my $cp_table = asset::copy->table;
243 my $cl_table = asset::copy_location->table;
244 my $cs_table = config::copy_status->table;
245 my $out_table = actor::org_unit_type->table;
246 my $descendants = "actor.org_unit_descendants(u.id)";
247 my $ancestors = "actor.org_unit_ancestors(?)";
249 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
250 $copies_visible = '' if ($self->api_name =~ /staff/o);
253 my ($t_filter, $f_filter) = ('','');
256 my ($t, $f) = split '-', $args{format};
257 @types = split '', $t;
258 @forms = split '', $f;
260 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
264 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
274 JOIN $cn_table cn ON (cn.record = r.source)
275 JOIN $rd_table rd ON (cn.record = rd.record)
276 JOIN $cp_table cp ON (cn.id = cp.call_number)
277 JOIN $cs_table cs ON (cp.status = cs.id)
278 JOIN $cl_table cl ON (cp.location = cl.id)
279 JOIN $descendants a ON (cp.circ_lib = a.id)
280 WHERE r.metarecord = ?
281 AND cn.deleted IS FALSE
282 AND cp.deleted IS FALSE
291 JOIN $cn_table cn ON (cn.record = r.source)
292 JOIN $rd_table rd ON (cn.record = rd.record)
293 JOIN $cp_table cp ON (cn.id = cp.call_number)
294 JOIN $cs_table cs ON (cp.status = cs.id)
295 JOIN $cl_table cl ON (cp.location = cl.id)
296 JOIN $descendants a ON (cp.circ_lib = a.id)
297 WHERE r.metarecord = ?
299 AND cn.deleted IS FALSE
300 AND cp.deleted IS FALSE
309 JOIN $cn_table cn ON (cn.record = r.source)
310 JOIN $rd_table rd ON (cn.record = rd.record)
311 JOIN $cp_table cp ON (cn.id = cp.call_number)
312 JOIN $cs_table cs ON (cp.status = cs.id)
313 JOIN $cl_table cl ON (cp.location = cl.id)
314 WHERE r.metarecord = ?
315 AND cn.deleted IS FALSE
316 AND cp.deleted IS FALSE
317 AND cp.opac_visible IS TRUE
318 AND cs.holdable IS TRUE
319 AND cl.opac_visible IS TRUE
326 JOIN $out_table t ON (u.ou_type = t.id)
330 my $sth = metabib::metarecord_source_map->db_Main->prepare_cached($sql);
331 $sth->execute( ''.$args{metarecord},
334 ''.$args{metarecord},
337 ''.$args{metarecord},
341 while ( my $row = $sth->fetchrow_hashref ) {
342 $client->respond( $row );
346 __PACKAGE__->register_method(
347 api_name => 'open-ils.storage.metabib.metarecord.copy_count',
348 method => 'metarecord_copy_count',
353 __PACKAGE__->register_method(
354 api_name => 'open-ils.storage.metabib.metarecord.copy_count.staff',
355 method => 'metarecord_copy_count',
361 sub biblio_multi_search_full_rec {
366 my $class_join = $args{class_join} || 'AND';
367 my $limit = $args{limit} || 100;
368 my $offset = $args{offset} || 0;
369 my $sort = $args{'sort'};
370 my $sort_dir = $args{sort_dir} || 'DESC';
375 for my $arg (@{ $args{searches} }) {
376 my $term = $$arg{term};
377 my $limiters = $$arg{restrict};
379 my ($index_col) = metabib::full_rec->columns('FTS');
380 $index_col ||= 'value';
381 my $search_table = metabib::full_rec->table;
383 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
385 my $fts_where = $fts->sql_where_clause();
386 my @fts_ranks = $fts->fts_rank;
388 my $rank = join(' + ', @fts_ranks);
391 for my $limit (@$limiters) {
392 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
393 push @binds, $$limit{tag}, $$limit{subfield};
394 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
396 my $where = join(' OR ', @wheres);
398 push @selects, "SELECT id, record, $rank as sum FROM $search_table WHERE $where";
402 my $descendants = defined($args{depth}) ?
403 "actor.org_unit_descendants($args{org_unit}, $args{depth})" :
404 "actor.org_unit_descendants($args{org_unit})" ;
407 my $metabib_record_descriptor = metabib::record_descriptor->table;
408 my $metabib_full_rec = metabib::full_rec->table;
409 my $asset_call_number_table = asset::call_number->table;
410 my $asset_copy_table = asset::copy->table;
411 my $cs_table = config::copy_status->table;
412 my $cl_table = asset::copy_location->table;
413 my $br_table = biblio::record_entry->table;
415 my $cj = 'HAVING COUNT(x.id) = ' . scalar(@selects) if ($class_join eq 'AND');
417 '(SELECT x.record, sum(x.sum) FROM (('.
418 join(') UNION ALL (', @selects).
419 ")) x GROUP BY 1 $cj ORDER BY 2 DESC )";
421 my $has_vols = 'AND cn.owning_lib = d.id';
422 my $has_copies = 'AND cp.call_number = cn.id';
423 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
425 if ($self->api_name =~ /staff/o) {
426 $copies_visible = '';
427 $has_copies = '' if ($ou_type == 0);
428 $has_vols = '' if ($ou_type == 0);
431 my ($t_filter, $f_filter) = ('','');
432 my ($a_filter, $l_filter, $lf_filter) = ('','','');
434 if (my $a = $args{audience}) {
435 $a = [$a] if (!ref($a));
438 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
442 if (my $l = $args{language}) {
443 $l = [$l] if (!ref($l));
446 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
450 if (my $f = $args{lit_form}) {
451 $f = [$f] if (!ref($f));
454 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
455 push @binds, @lit_form;
458 if (my $f = $args{item_form}) {
459 $f = [$f] if (!ref($f));
462 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
466 if (my $t = $args{item_type}) {
467 $t = [$t] if (!ref($t));
470 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
476 my ($t, $f) = split '-', $args{format};
477 my @types = split '', $t;
478 my @forms = split '', $f;
480 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
484 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
486 push @binds, @types, @forms;
489 my $relevance = 'sum(f.sum)';
490 $relevance = 1 if (!$copies_visible);
492 my $rank = $relevance;
493 if (lc($sort) eq 'pubdate') {
496 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
497 FROM $metabib_full_rec frp
498 WHERE frp.record = f.record
500 AND frp.subfield = 'c'
504 } elsif (lc($sort) eq 'create_date') {
506 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = f.record)) )
508 } elsif (lc($sort) eq 'edit_date') {
510 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = f.record)) )
512 } elsif (lc($sort) eq 'title') {
515 SELECT COALESCE(LTRIM(SUBSTR( frt.value, COALESCE(SUBSTRING(frt.ind2 FROM '\\\\d+'),'0')::INT )),'zzzzzzzz')
516 FROM $metabib_full_rec frt
517 WHERE frt.record = f.record
519 AND frt.subfield = 'a'
523 } elsif (lc($sort) eq 'author') {
526 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
527 FROM $metabib_full_rec fra
528 WHERE fra.record = f.record
529 AND fra.tag LIKE '1%'
530 AND fra.subfield = 'a'
531 ORDER BY fra.tag::text::int
540 if ($copies_visible) {
542 SELECT f.record, $relevance, count(DISTINCT cp.id), $rank
543 FROM $search_table f,
544 $asset_call_number_table cn,
545 $asset_copy_table cp,
549 $metabib_record_descriptor rd,
551 WHERE br.id = f.record
552 AND cn.record = f.record
553 AND rd.record = f.record
554 AND cp.status = cs.id
555 AND cp.location = cl.id
556 AND br.deleted IS FALSE
557 AND cn.deleted IS FALSE
558 AND cp.deleted IS FALSE
567 GROUP BY f.record HAVING count(DISTINCT cp.id) > 0
568 ORDER BY 4 $sort_dir,3 DESC
572 SELECT f.record, 1, 1, $rank
573 FROM $search_table f,
575 $metabib_record_descriptor rd
576 WHERE br.id = f.record
577 AND rd.record = f.record
578 AND br.deleted IS FALSE
590 $log->debug("Search SQL :: [$select]",DEBUG);
592 my $recs = metabib::full_rec->db_Main->selectall_arrayref("$select;", {}, @binds);
593 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
596 $max = 1 if (!@$recs);
598 $max = $$_[1] if ($$_[1] > $max);
602 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
603 next unless ($$rec[0]);
604 my ($rid,$rank,$junk,$skip) = @$rec;
605 $client->respond( [$rid, sprintf('%0.3f',$rank/$max), $count] );
609 __PACKAGE__->register_method(
610 api_name => 'open-ils.storage.biblio.full_rec.multi_search',
611 method => 'biblio_multi_search_full_rec',
616 __PACKAGE__->register_method(
617 api_name => 'open-ils.storage.biblio.full_rec.multi_search.staff',
618 method => 'biblio_multi_search_full_rec',
624 sub search_full_rec {
630 my $term = $args{term};
631 my $limiters = $args{restrict};
633 my ($index_col) = metabib::full_rec->columns('FTS');
634 $index_col ||= 'value';
635 my $search_table = metabib::full_rec->table;
637 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
639 my $fts_where = $fts->sql_where_clause();
640 my @fts_ranks = $fts->fts_rank;
642 my $rank = join(' + ', @fts_ranks);
646 for my $limit (@$limiters) {
647 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
648 push @binds, $$limit{tag}, $$limit{subfield};
649 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
651 my $where = join(' OR ', @wheres);
653 my $select = "SELECT record, sum($rank) FROM $search_table WHERE $where GROUP BY 1 ORDER BY 2 DESC;";
655 $log->debug("Search SQL :: [$select]",DEBUG);
657 my $recs = metabib::full_rec->db_Main->selectall_arrayref($select, {}, @binds);
658 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
660 $client->respond($_) for (@$recs);
663 __PACKAGE__->register_method(
664 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.value',
665 method => 'search_full_rec',
670 __PACKAGE__->register_method(
671 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.index_vector',
672 method => 'search_full_rec',
679 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
680 sub search_class_fts {
685 my $term = $args{term};
686 my $ou = $args{org_unit};
687 my $ou_type = $args{depth};
688 my $limit = $args{limit};
689 my $offset = $args{offset};
691 my $limit_clause = '';
692 my $offset_clause = '';
694 $limit_clause = "LIMIT $limit" if (defined $limit and int($limit) > 0);
695 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
698 my ($t_filter, $f_filter) = ('','');
701 my ($t, $f) = split '-', $args{format};
702 @types = split '', $t;
703 @forms = split '', $f;
705 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
709 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
715 my $descendants = defined($ou_type) ?
716 "actor.org_unit_descendants($ou, $ou_type)" :
717 "actor.org_unit_descendants($ou)";
719 my $class = $self->{cdbi};
720 my $search_table = $class->table;
722 my $metabib_record_descriptor = metabib::record_descriptor->table;
723 my $metabib_metarecord = metabib::metarecord->table;
724 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
725 my $asset_call_number_table = asset::call_number->table;
726 my $asset_copy_table = asset::copy->table;
727 my $cs_table = config::copy_status->table;
728 my $cl_table = asset::copy_location->table;
730 my ($index_col) = $class->columns('FTS');
731 $index_col ||= 'value';
733 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
735 my $fts_where = $fts->sql_where_clause;
736 my @fts_ranks = $fts->fts_rank;
738 my $rank = join(' + ', @fts_ranks);
740 my $has_vols = 'AND cn.owning_lib = d.id';
741 my $has_copies = 'AND cp.call_number = cn.id';
742 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
744 my $visible_count = ', count(DISTINCT cp.id)';
745 my $visible_count_test = 'HAVING count(DISTINCT cp.id) > 0';
747 if ($self->api_name =~ /staff/o) {
748 $copies_visible = '';
749 $visible_count_test = '';
750 $has_copies = '' if ($ou_type == 0);
751 $has_vols = '' if ($ou_type == 0);
754 my $rank_calc = <<" RANK";
756 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
757 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
758 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
759 )/COUNT(m.source)), MIN(COALESCE(CHAR_LENGTH(f.value),1))
762 $rank_calc = ',1 , 1' if ($self->api_name =~ /unordered/o);
764 if ($copies_visible) {
766 SELECT m.metarecord $rank_calc $visible_count, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
767 FROM $search_table f,
768 $metabib_metarecord_source_map_table m,
769 $asset_call_number_table cn,
770 $asset_copy_table cp,
773 $metabib_record_descriptor rd,
776 AND m.source = f.source
777 AND cn.record = m.source
778 AND rd.record = m.source
779 AND cp.status = cs.id
780 AND cp.location = cl.id
786 GROUP BY 1 $visible_count_test
788 $limit_clause $offset_clause
792 SELECT m.metarecord $rank_calc, 0, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
793 FROM $search_table f,
794 $metabib_metarecord_source_map_table m,
795 $metabib_record_descriptor rd
797 AND m.source = f.source
798 AND rd.record = m.source
803 $limit_clause $offset_clause
807 $log->debug("Field Search SQL :: [$select]",DEBUG);
809 my $SQLstring = join('%',$fts->words);
810 my $REstring = join('\\s+',$fts->words);
811 my $first_word = ($fts->words)[0].'%';
812 my $recs = ($self->api_name =~ /unordered/o) ?
813 $class->db_Main->selectall_arrayref($select, {}, @types, @forms) :
814 $class->db_Main->selectall_arrayref($select, {},
815 '%'.lc($SQLstring).'%', # phrase order match
816 lc($first_word), # first word match
817 '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match
821 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
823 $client->respond($_) for (map { [@$_[0,1,3,4]] } @$recs);
827 for my $class ( qw/title author subject keyword series/ ) {
828 __PACKAGE__->register_method(
829 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord",
830 method => 'search_class_fts',
833 cdbi => "metabib::${class}_field_entry",
836 __PACKAGE__->register_method(
837 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.unordered",
838 method => 'search_class_fts',
841 cdbi => "metabib::${class}_field_entry",
844 __PACKAGE__->register_method(
845 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff",
846 method => 'search_class_fts',
849 cdbi => "metabib::${class}_field_entry",
852 __PACKAGE__->register_method(
853 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff.unordered",
854 method => 'search_class_fts',
857 cdbi => "metabib::${class}_field_entry",
862 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
863 sub search_class_fts_count {
868 my $term = $args{term};
869 my $ou = $args{org_unit};
870 my $ou_type = $args{depth};
871 my $limit = $args{limit} || 100;
872 my $offset = $args{offset} || 0;
874 my $descendants = defined($ou_type) ?
875 "actor.org_unit_descendants($ou, $ou_type)" :
876 "actor.org_unit_descendants($ou)";
879 my ($t_filter, $f_filter) = ('','');
882 my ($t, $f) = split '-', $args{format};
883 @types = split '', $t;
884 @forms = split '', $f;
886 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
890 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
895 (my $search_class = $self->api_name) =~ s/.*metabib.(\w+).search_fts.*/$1/o;
897 my $class = $self->{cdbi};
898 my $search_table = $class->table;
900 my $metabib_record_descriptor = metabib::record_descriptor->table;
901 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
902 my $asset_call_number_table = asset::call_number->table;
903 my $asset_copy_table = asset::copy->table;
904 my $cs_table = config::copy_status->table;
905 my $cl_table = asset::copy_location->table;
907 my ($index_col) = $class->columns('FTS');
908 $index_col ||= 'value';
910 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
912 my $fts_where = $fts->sql_where_clause;
914 my $has_vols = 'AND cn.owning_lib = d.id';
915 my $has_copies = 'AND cp.call_number = cn.id';
916 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
917 if ($self->api_name =~ /staff/o) {
918 $copies_visible = '';
919 $has_vols = '' if ($ou_type == 0);
920 $has_copies = '' if ($ou_type == 0);
923 # XXX test an "EXISTS version of descendant checking...
925 if ($copies_visible) {
927 SELECT count(distinct m.metarecord)
928 FROM $search_table f,
929 $metabib_metarecord_source_map_table m,
930 $metabib_metarecord_source_map_table mr,
931 $asset_call_number_table cn,
932 $asset_copy_table cp,
935 $metabib_record_descriptor rd,
938 AND mr.source = f.source
939 AND mr.metarecord = m.metarecord
940 AND cn.record = m.source
941 AND rd.record = m.source
942 AND cp.status = cs.id
943 AND cp.location = cl.id
952 SELECT count(distinct m.metarecord)
953 FROM $search_table f,
954 $metabib_metarecord_source_map_table m,
955 $metabib_metarecord_source_map_table mr,
956 $metabib_record_descriptor rd
958 AND mr.source = f.source
959 AND mr.metarecord = m.metarecord
960 AND rd.record = m.source
966 $log->debug("Field Search Count SQL :: [$select]",DEBUG);
968 my $recs = $class->db_Main->selectrow_arrayref($select, {}, @types, @forms)->[0];
970 $log->debug("Count Search yielded $recs results.",DEBUG);
975 for my $class ( qw/title author subject keyword series/ ) {
976 __PACKAGE__->register_method(
977 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count",
978 method => 'search_class_fts_count',
981 cdbi => "metabib::${class}_field_entry",
984 __PACKAGE__->register_method(
985 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count.staff",
986 method => 'search_class_fts_count',
989 cdbi => "metabib::${class}_field_entry",
995 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
996 sub postfilter_search_class_fts {
1001 my $term = $args{term};
1002 my $sort = $args{'sort'};
1003 my $sort_dir = $args{sort_dir} || 'DESC';
1004 my $ou = $args{org_unit};
1005 my $ou_type = $args{depth};
1006 my $limit = $args{limit} || 10;
1007 my $offset = $args{offset} || 0;
1009 my $outer_limit = 1000;
1011 my $limit_clause = '';
1012 my $offset_clause = '';
1014 $limit_clause = "LIMIT $outer_limit";
1015 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
1017 my (@types,@forms,@lang,@aud,@lit_form);
1018 my ($t_filter, $f_filter) = ('','');
1019 my ($a_filter, $l_filter, $lf_filter) = ('','','');
1020 my ($ot_filter, $of_filter) = ('','');
1021 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
1023 if (my $a = $args{audience}) {
1024 $a = [$a] if (!ref($a));
1027 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
1028 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
1031 if (my $l = $args{language}) {
1032 $l = [$l] if (!ref($l));
1035 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1036 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1039 if (my $f = $args{lit_form}) {
1040 $f = [$f] if (!ref($f));
1043 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1044 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1047 if ($args{format}) {
1048 my ($t, $f) = split '-', $args{format};
1049 @types = split '', $t;
1050 @forms = split '', $f;
1052 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1053 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1057 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1058 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1063 my $descendants = defined($ou_type) ?
1064 "actor.org_unit_descendants($ou, $ou_type)" :
1065 "actor.org_unit_descendants($ou)";
1067 my $class = $self->{cdbi};
1068 my $search_table = $class->table;
1070 my $metabib_full_rec = metabib::full_rec->table;
1071 my $metabib_record_descriptor = metabib::record_descriptor->table;
1072 my $metabib_metarecord = metabib::metarecord->table;
1073 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1074 my $asset_call_number_table = asset::call_number->table;
1075 my $asset_copy_table = asset::copy->table;
1076 my $cs_table = config::copy_status->table;
1077 my $cl_table = asset::copy_location->table;
1078 my $br_table = biblio::record_entry->table;
1080 my ($index_col) = $class->columns('FTS');
1081 $index_col ||= 'value';
1083 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
1085 my $SQLstring = join('%',map { lc($_) } $fts->words);
1086 my $REstring = '^' . join('\s+',map { lc($_) } $fts->words) . '\W*$';
1087 my $first_word = lc(($fts->words)[0]).'%';
1089 my $fts_where = $fts->sql_where_clause;
1090 my @fts_ranks = $fts->fts_rank;
1093 $bonus{'metabib::keyword_field_entry'} = [ { 'CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END' => $SQLstring } ];
1094 $bonus{'metabib::title_field_entry'} =
1095 $bonus{'metabib::series_field_entry'} = [
1096 { 'CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END' => $first_word },
1097 { 'CASE WHEN f.value ~* ? THEN 2 ELSE 1 END' => $REstring },
1098 @{ $bonus{'metabib::keyword_field_entry'} }
1101 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$class} };
1102 $bonus_list ||= '1';
1104 my @bonus_values = map { values %$_ } @{ $bonus{$class} };
1106 my $relevance = join(' + ', @fts_ranks);
1107 $relevance = <<" RANK";
1108 (SUM( ( $relevance ) * ( $bonus_list ) )/COUNT(m.source))
1111 my $rank = $relevance;
1112 if (lc($sort) eq 'pubdate') {
1115 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
1116 FROM $metabib_full_rec frp
1117 WHERE frp.record = mr.master_record
1119 AND frp.subfield = 'c'
1123 } elsif (lc($sort) eq 'create_date') {
1125 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1127 } elsif (lc($sort) eq 'edit_date') {
1129 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1131 } elsif (lc($sort) eq 'title') {
1134 SELECT COALESCE(LTRIM(SUBSTR( frt.value, COALESCE(SUBSTRING(frt.ind2 FROM '\\\\d+'),'0')::INT )),'zzzzzzzz')
1135 FROM $metabib_full_rec frt
1136 WHERE frt.record = mr.master_record
1138 AND frt.subfield = 'a'
1142 } elsif (lc($sort) eq 'author') {
1145 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1146 FROM $metabib_full_rec fra
1147 WHERE fra.record = mr.master_record
1148 AND fra.tag LIKE '1%'
1149 AND fra.subfield = 'a'
1150 ORDER BY fra.tag::text::int
1158 my $select = <<" SQL";
1159 SELECT m.metarecord,
1161 CASE WHEN COUNT(DISTINCT smrs.source) = 1 THEN MIN(m.source) ELSE 0 END,
1163 FROM $search_table f,
1164 $metabib_metarecord_source_map_table m,
1165 $metabib_metarecord_source_map_table smrs,
1166 $metabib_metarecord mr,
1167 $metabib_record_descriptor rd
1169 AND smrs.metarecord = mr.id
1170 AND m.source = f.source
1171 AND m.metarecord = mr.id
1172 AND rd.record = smrs.source
1178 GROUP BY m.metarecord
1179 ORDER BY 4 $sort_dir, MIN(COALESCE(CHAR_LENGTH(f.value),1))
1187 FROM $asset_call_number_table cn,
1188 $metabib_metarecord_source_map_table mrs,
1189 $asset_copy_table cp,
1194 $metabib_record_descriptor ord,
1196 WHERE mrs.metarecord = s.metarecord
1197 AND br.id = mrs.source
1198 AND cn.record = mrs.source
1199 AND cp.status = cs.id
1200 AND cp.location = cl.id
1201 AND cn.owning_lib = d.id
1202 AND cp.call_number = cn.id
1203 AND cp.opac_visible IS TRUE
1204 AND cs.holdable IS TRUE
1205 AND cl.opac_visible IS TRUE
1206 AND br.active IS TRUE
1207 AND br.deleted IS FALSE
1208 AND ord.record = mrs.source
1214 ORDER BY 4 $sort_dir
1216 } elsif ($self->api_name !~ /staff/o) {
1223 FROM $asset_call_number_table cn,
1224 $metabib_metarecord_source_map_table mrs,
1225 $asset_copy_table cp,
1230 $metabib_record_descriptor ord
1232 WHERE mrs.metarecord = s.metarecord
1233 AND br.id = mrs.source
1234 AND cn.record = mrs.source
1235 AND cp.status = cs.id
1236 AND cp.location = cl.id
1237 AND cn.owning_lib = d.id
1238 AND cp.call_number = cn.id
1239 AND cp.opac_visible IS TRUE
1240 AND cs.holdable IS TRUE
1241 AND cl.opac_visible IS TRUE
1242 AND br.active IS TRUE
1243 AND br.deleted IS FALSE
1244 AND ord.record = mrs.source
1252 ORDER BY 4 $sort_dir
1261 FROM $asset_call_number_table cn,
1262 $metabib_metarecord_source_map_table mrs,
1265 $metabib_record_descriptor ord
1267 WHERE mrs.metarecord = s.metarecord
1268 AND br.id = mrs.source
1269 AND cn.record = mrs.source
1270 AND cn.owning_lib = d.id
1271 AND br.deleted IS FALSE
1272 AND ord.record = mrs.source
1282 FROM $asset_call_number_table cn,
1283 $metabib_metarecord_source_map_table mrs,
1284 $metabib_record_descriptor ord
1285 WHERE mrs.metarecord = s.metarecord
1286 AND cn.record = mrs.source
1287 AND ord.record = mrs.source
1295 ORDER BY 4 $sort_dir
1300 $log->debug("Field Search SQL :: [$select]",DEBUG);
1302 my $recs = $class->db_Main->selectall_arrayref(
1304 (@bonus_values > 0 ? @bonus_values : () ),
1305 ( (!$sort && @bonus_values > 0) ? @bonus_values : () ),
1306 @types, @forms, @aud, @lang, @lit_form,
1307 @types, @forms, @aud, @lang, @lit_form,
1308 ($self->api_name =~ /staff/o ? (@types, @forms, @aud, @lang, @lit_form) : () ) );
1310 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1313 $max = 1 if (!@$recs);
1315 $max = $$_[1] if ($$_[1] > $max);
1318 my $count = scalar(@$recs);
1319 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1320 my ($mrid,$rank,$skip) = @$rec;
1321 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $skip, $count] );
1326 for my $class ( qw/title author subject keyword series/ ) {
1327 __PACKAGE__->register_method(
1328 api_name => "open-ils.storage.metabib.$class.post_filter.search_fts.metarecord",
1329 method => 'postfilter_search_class_fts',
1332 cdbi => "metabib::${class}_field_entry",
1335 __PACKAGE__->register_method(
1336 api_name => "open-ils.storage.metabib.$class.post_filter.search_fts.metarecord.staff",
1337 method => 'postfilter_search_class_fts',
1340 cdbi => "metabib::${class}_field_entry",
1347 my $_cdbi = { title => "metabib::title_field_entry",
1348 author => "metabib::author_field_entry",
1349 subject => "metabib::subject_field_entry",
1350 keyword => "metabib::keyword_field_entry",
1351 series => "metabib::series_field_entry",
1354 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
1355 sub postfilter_search_multi_class_fts {
1360 my $sort = $args{'sort'};
1361 my $sort_dir = $args{sort_dir} || 'DESC';
1362 my $ou = $args{org_unit};
1363 my $ou_type = $args{depth};
1364 my $limit = $args{limit} || 10;;
1365 my $offset = $args{offset} || 0;
1368 $ou = actor::org_unit->search( { parent_ou => undef } )->next->id;
1371 if (!defined($args{org_unit})) {
1372 die "No target organizational unit passed to ".$self->api_name;
1375 if (! scalar( keys %{$args{searches}} )) {
1376 die "No search arguments were passed to ".$self->api_name;
1379 my $outer_limit = 1000;
1381 my $limit_clause = '';
1382 my $offset_clause = '';
1384 $limit_clause = "LIMIT $outer_limit";
1385 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
1387 my (@types,@forms,@lang,@aud,@lit_form);
1388 my ($t_filter, $f_filter) = ('','');
1389 my ($a_filter, $l_filter, $lf_filter) = ('','','');
1390 my ($ot_filter, $of_filter) = ('','');
1391 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
1393 if (my $a = $args{audience}) {
1394 $a = [$a] if (!ref($a));
1397 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
1398 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
1401 if (my $l = $args{language}) {
1402 $l = [$l] if (!ref($l));
1405 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1406 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1409 if (my $f = $args{lit_form}) {
1410 $f = [$f] if (!ref($f));
1413 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1414 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1417 if (my $f = $args{item_form}) {
1418 $f = [$f] if (!ref($f));
1421 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1422 $of_filter = ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1425 if (my $t = $args{item_type}) {
1426 $t = [$t] if (!ref($t));
1429 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1430 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1434 # XXX legacy format and item type support
1435 if ($args{format}) {
1436 my ($t, $f) = split '-', $args{format};
1437 @types = split '', $t;
1438 @forms = split '', $f;
1440 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1441 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1445 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1446 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1452 my $descendants = defined($ou_type) ?
1453 "actor.org_unit_descendants($ou, $ou_type)" :
1454 "actor.org_unit_descendants($ou)";
1456 my $search_table_list = '';
1458 my $join_table_list = '';
1463 my $prev_search_class;
1464 my $curr_search_class;
1465 for my $search_class (sort keys %{$args{searches}}) {
1466 $prev_search_class = $curr_search_class if ($curr_search_class);
1468 $curr_search_class = $search_class;
1470 my $class = $_cdbi->{$search_class};
1471 my $search_table = $class->table;
1473 my ($index_col) = $class->columns('FTS');
1474 $index_col ||= 'value';
1477 my $fts = OpenILS::Application::Storage::FTS->compile($args{searches}{$search_class}{term}, $search_class.'.value', "$search_class.$index_col");
1479 my $fts_where = $fts->sql_where_clause;
1480 my @fts_ranks = $fts->fts_rank;
1482 my $SQLstring = join('%',map { lc($_) } $fts->words);
1483 my $REstring = '^' . join('\s+',map { lc($_) } $fts->words) . '\W*$';
1484 my $first_word = lc(($fts->words)[0]).'%';
1486 my $rank = join(' + ', @fts_ranks);
1489 $bonus{'keyword'} = [ { "CASE WHEN $search_class.value LIKE ? THEN 1.2 ELSE 1 END" => $SQLstring } ];
1491 $bonus{'series'} = [
1492 { "CASE WHEN $search_class.value LIKE ? THEN 1.5 ELSE 1 END" => $first_word },
1493 { "CASE WHEN $search_class.value ~ ? THEN 20 ELSE 1 END" => $REstring },
1496 $bonus{'title'} = [ @{ $bonus{'series'} }, @{ $bonus{'keyword'} } ];
1498 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$search_class} };
1499 $bonus_list ||= '1';
1501 push @bonus_lists, $bonus_list;
1502 push @bonus_values, map { values %$_ } @{ $bonus{$search_class} };
1505 #---------------------
1507 $search_table_list .= "$search_table $search_class, ";
1508 push @rank_list,$rank;
1509 $fts_list .= " AND $fts_where AND m.source = $search_class.source";
1511 if ($prev_search_class) {
1512 $join_table_list .= " AND $prev_search_class.source = $curr_search_class.source";
1516 my $metabib_record_descriptor = metabib::record_descriptor->table;
1517 my $metabib_full_rec = metabib::full_rec->table;
1518 my $metabib_metarecord = metabib::metarecord->table;
1519 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1520 my $asset_call_number_table = asset::call_number->table;
1521 my $asset_copy_table = asset::copy->table;
1522 my $cs_table = config::copy_status->table;
1523 my $cl_table = asset::copy_location->table;
1524 my $br_table = biblio::record_entry->table;
1526 my $bonuses = join (' * ', @bonus_lists);
1527 my $relevance = join (' + ', @rank_list);
1528 $relevance = "SUM( ($relevance) * ($bonuses) )/COUNT(DISTINCT smrs.source)";
1531 my $secondary_sort = <<" SORT";
1533 SELECT COALESCE(LTRIM(SUBSTR( sfrt.value, COALESCE(SUBSTRING(sfrt.ind2 FROM '\\\\d+'),'0')::INT )),'zzzzzzzz')
1534 FROM $metabib_full_rec sfrt,
1535 $metabib_metarecord mr
1536 WHERE sfrt.record = mr.master_record
1537 AND sfrt.tag = '245'
1538 AND sfrt.subfield = 'a'
1543 my $rank = $relevance;
1544 if (lc($sort) eq 'pubdate') {
1547 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
1548 FROM $metabib_full_rec frp
1549 WHERE frp.record = mr.master_record
1551 AND frp.subfield = 'c'
1555 } elsif (lc($sort) eq 'create_date') {
1557 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1559 } elsif (lc($sort) eq 'edit_date') {
1561 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1563 } elsif (lc($sort) eq 'title') {
1566 SELECT COALESCE(LTRIM(SUBSTR( frt.value, COALESCE(SUBSTRING(frt.ind2 FROM '\\\\d+'),'0')::INT )),'zzzzzzzz')
1567 FROM $metabib_full_rec frt
1568 WHERE frt.record = mr.master_record
1570 AND frt.subfield = 'a'
1574 $secondary_sort = <<" SORT";
1576 SELECT COALESCE(SUBSTRING(sfrp.value FROM '\\\\d+'),'9999')::INT
1577 FROM $metabib_full_rec sfrp,
1578 $metabib_metarecord mr
1579 WHERE sfrp.record = mr.master_record
1580 AND sfrp.tag = '260'
1581 AND sfrp.subfield = 'c'
1585 } elsif (lc($sort) eq 'author') {
1588 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1589 FROM $metabib_full_rec fra
1590 WHERE fra.record = mr.master_record
1591 AND fra.tag LIKE '1%'
1592 AND fra.subfield = 'a'
1593 ORDER BY fra.tag::text::int
1598 push @bonus_values, @bonus_values;
1603 my $select = <<" SQL";
1604 SELECT m.metarecord,
1606 CASE WHEN COUNT(DISTINCT smrs.source) = 1 THEN FIRST(m.source) ELSE 0 END,
1609 FROM $search_table_list
1610 $metabib_metarecord_source_map_table m,
1611 $metabib_metarecord_source_map_table smrs
1612 WHERE m.metarecord = smrs.metarecord
1615 GROUP BY m.metarecord
1616 -- ORDER BY 4 $sort_dir
1620 if ($self->api_name !~ /staff/o) {
1627 FROM $asset_call_number_table cn,
1628 $metabib_metarecord_source_map_table mrs,
1629 $asset_copy_table cp,
1634 $metabib_record_descriptor ord
1635 WHERE mrs.metarecord = s.metarecord
1636 AND br.id = mrs.source
1637 AND cn.record = mrs.source
1638 AND cp.status = cs.id
1639 AND cp.location = cl.id
1640 AND cn.owning_lib = d.id
1641 AND cp.call_number = cn.id
1642 AND cp.opac_visible IS TRUE
1643 AND cs.holdable IS TRUE
1644 AND cl.opac_visible IS TRUE
1645 AND br.active IS TRUE
1646 AND br.deleted IS FALSE
1647 AND cp.deleted IS FALSE
1648 AND cn.deleted IS FALSE
1649 AND ord.record = mrs.source
1657 ORDER BY 4 $sort_dir, 5
1664 $metabib_metarecord_source_map_table omrs,
1665 $metabib_record_descriptor ord
1666 WHERE omrs.metarecord = s.metarecord
1667 AND ord.record = omrs.source
1670 FROM $asset_call_number_table cn,
1673 WHERE br.id = omrs.source
1674 AND cn.record = omrs.source
1675 AND cn.owning_lib = d.id
1676 AND br.deleted IS FALSE
1677 AND cn.deleted IS FALSE
1682 FROM $asset_call_number_table cn
1683 WHERE cn.record = omrs.source
1684 AND cn.deleted IS FALSE
1694 ORDER BY 4 $sort_dir, 5
1699 $log->debug("Field Search SQL :: [$select]",DEBUG);
1701 my $recs = $_cdbi->{title}->db_Main->selectall_arrayref(
1704 @types, @forms, @aud, @lang, @lit_form,
1705 # @types, @forms, @aud, @lang, @lit_form,
1706 # ($self->api_name =~ /staff/o ? (@types, @forms, @aud, @lang, @lit_form) : () )
1709 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1712 $max = 1 if (!@$recs);
1714 $max = $$_[1] if ($$_[1] > $max);
1717 my $count = scalar(@$recs);
1718 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1719 next unless ($$rec[0]);
1720 my ($mrid,$rank,$skip) = @$rec;
1721 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $skip, $count] );
1726 __PACKAGE__->register_method(
1727 api_name => "open-ils.storage.metabib.post_filter.multiclass.search_fts.metarecord",
1728 method => 'postfilter_search_multi_class_fts',
1733 __PACKAGE__->register_method(
1734 api_name => "open-ils.storage.metabib.post_filter.multiclass.search_fts.metarecord.staff",
1735 method => 'postfilter_search_multi_class_fts',
1741 __PACKAGE__->register_method(
1742 api_name => "open-ils.storage.metabib.multiclass.search_fts",
1743 method => 'postfilter_search_multi_class_fts',
1748 __PACKAGE__->register_method(
1749 api_name => "open-ils.storage.metabib.multiclass.search_fts.staff",
1750 method => 'postfilter_search_multi_class_fts',
1756 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
1757 sub biblio_search_multi_class_fts {
1762 my $sort = $args{'sort'};
1763 my $sort_dir = $args{sort_dir} || 'DESC';
1764 my $ou = $args{org_unit};
1765 my $ou_type = $args{depth};
1766 my $limit = $args{limit} || 10;
1767 my $offset = $args{offset} || 0;
1770 $ou = actor::org_unit->search( { parent_ou => undef } )->next->id;
1773 if (!defined($args{org_unit})) {
1774 die "No target organizational unit passed to ".$self->api_name;
1777 if (! scalar( keys %{$args{searches}} )) {
1778 die "No search arguments were passed to ".$self->api_name;
1781 my $outer_limit = 1000;
1783 my $limit_clause = '';
1784 my $offset_clause = '';
1786 $limit_clause = "LIMIT $outer_limit";
1787 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
1789 my (@types,@forms,@lang,@aud,@lit_form);
1790 my ($t_filter, $f_filter) = ('','');
1791 my ($a_filter, $l_filter, $lf_filter) = ('','','');
1792 my ($ot_filter, $of_filter) = ('','');
1793 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
1795 if (my $a = $args{audience}) {
1796 $a = [$a] if (!ref($a));
1799 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
1800 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
1803 if (my $l = $args{language}) {
1804 $l = [$l] if (!ref($l));
1807 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1808 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1811 if (my $f = $args{lit_form}) {
1812 $f = [$f] if (!ref($f));
1815 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1816 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1819 if (my $f = $args{item_form}) {
1820 $f = [$f] if (!ref($f));
1823 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1824 $of_filter = ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1827 if (my $t = $args{item_type}) {
1828 $t = [$t] if (!ref($t));
1831 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1832 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1836 # XXX legacy format and item type support
1837 if ($args{format}) {
1838 my ($t, $f) = split '-', $args{format};
1839 @types = split '', $t;
1840 @forms = split '', $f;
1842 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1843 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1847 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1848 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1853 my $descendants = defined($ou_type) ?
1854 "actor.org_unit_descendants($ou, $ou_type)" :
1855 "actor.org_unit_descendants($ou)";
1857 my $search_table_list = '';
1859 my $join_table_list = '';
1865 my $prev_search_class;
1866 my $curr_search_class;
1867 for my $search_class (sort keys %{$args{searches}}) {
1868 $prev_search_class = $curr_search_class if ($curr_search_class);
1870 $curr_search_class = $search_class;
1872 my $class = $_cdbi->{$search_class};
1873 my $search_table = $class->table;
1875 my ($index_col) = $class->columns('FTS');
1876 $index_col ||= 'value';
1879 my $fts = OpenILS::Application::Storage::FTS->compile($args{searches}{$search_class}{term}, $search_class.'.value', "$search_class.$index_col");
1881 my $fts_where = $fts->sql_where_clause;
1882 my @fts_ranks = $fts->fts_rank;
1884 my $SQLstring = join('%',map { lc($_) } $fts->words);
1885 my $REstring = '^' . join('\s+',map { lc($_) } $fts->words) . '\W*$';
1886 my $first_word = lc(($fts->words)[0]).'%';
1888 my $rank = join(' + ', @fts_ranks);
1891 $bonus{'keyword'} = [ { "CASE WHEN $search_class.value ILIKE ? THEN 1.2 ELSE 1 END" => $SQLstring } ];
1893 $bonus{'series'} = [
1894 { "CASE WHEN $search_class.value ILIKE ? THEN 1.5 ELSE 1 END" => $first_word },
1895 { "CASE WHEN $search_class.value ~ ? THEN 200 ELSE 1 END" => $REstring },
1898 $bonus{'title'} = [ @{ $bonus{'series'} }, @{ $bonus{'keyword'} } ];
1900 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$search_class} };
1901 $bonus_list ||= '1';
1903 push @bonus_lists, $bonus_list;
1904 push @bonus_values, map { values %$_ } @{ $bonus{$search_class} };
1906 #---------------------
1908 $search_table_list .= "$search_table $search_class, ";
1909 push @rank_list,$rank;
1910 $fts_list .= " AND $fts_where AND b.id = $search_class.source";
1912 if ($prev_search_class) {
1913 $join_table_list .= " AND $prev_search_class.source = $curr_search_class.source";
1917 my $metabib_record_descriptor = metabib::record_descriptor->table;
1918 my $metabib_full_rec = metabib::full_rec->table;
1919 my $metabib_metarecord = metabib::metarecord->table;
1920 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1921 my $asset_call_number_table = asset::call_number->table;
1922 my $asset_copy_table = asset::copy->table;
1923 my $cs_table = config::copy_status->table;
1924 my $cl_table = asset::copy_location->table;
1925 my $br_table = biblio::record_entry->table;
1928 my $bonuses = join (' * ', @bonus_lists);
1929 my $relevance = join (' + ', @rank_list);
1930 $relevance = "AVG( ($relevance) * ($bonuses) )";
1933 my $rank = $relevance;
1934 if (lc($sort) eq 'pubdate') {
1937 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d{4}'),'9999')::INT
1938 FROM $metabib_full_rec frp
1939 WHERE frp.record = b.id
1941 AND frp.subfield = 'c'
1945 } elsif (lc($sort) eq 'create_date') {
1947 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = b.id)) )
1949 } elsif (lc($sort) eq 'edit_date') {
1951 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = b.id)) )
1953 } elsif (lc($sort) eq 'title') {
1956 SELECT COALESCE(LTRIM(SUBSTR( frt.value, COALESCE(SUBSTRING(frt.ind2 FROM '\\\\d+'),'0')::INT )),'zzzzzzzz')
1957 FROM $metabib_full_rec frt
1958 WHERE frt.record = b.id
1960 AND frt.subfield = 'a'
1964 } elsif (lc($sort) eq 'author') {
1967 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1968 FROM $metabib_full_rec fra
1969 WHERE fra.record = b.id
1970 AND fra.tag LIKE '1%'
1971 AND fra.subfield = 'a'
1972 ORDER BY fra.tag::text::int
1977 push @bonus_values, @bonus_values;
1982 my $select = <<" SQL";
1986 FROM $search_table_list
1987 $metabib_record_descriptor rd,
1989 WHERE rd.record = b.id
1990 AND b.active IS TRUE
1991 AND b.deleted IS FALSE
2000 ORDER BY 3 $sort_dir
2004 if ($self->api_name !~ /staff/o) {
2011 FROM $asset_call_number_table cn,
2012 $asset_copy_table cp,
2016 WHERE cn.record = s.id
2017 AND cp.status = cs.id
2018 AND cp.location = cl.id
2019 AND cn.owning_lib = d.id
2020 AND cp.call_number = cn.id
2021 AND cp.opac_visible IS TRUE
2022 AND cs.holdable IS TRUE
2023 AND cl.opac_visible IS TRUE
2024 AND cp.deleted IS FALSE
2025 AND cn.deleted IS FALSE
2028 ORDER BY 3 $sort_dir
2037 FROM $asset_call_number_table cn,
2039 WHERE cn.record = s.id
2040 AND cn.owning_lib = d.id
2041 AND cn.deleted IS FALSE
2046 FROM $asset_call_number_table cn
2047 WHERE cn.record = s.id
2050 ORDER BY 3 $sort_dir
2055 $log->debug("Field Search SQL :: [$select]",DEBUG);
2057 my $recs = $_cdbi->{title}->db_Main->selectall_arrayref(
2059 @bonus_values, @types, @forms, @aud, @lang, @lit_form
2062 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
2065 $max = 1 if (!@$recs);
2067 $max = $$_[1] if ($$_[1] > $max);
2070 my $count = scalar(@$recs);
2071 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
2072 next unless ($$rec[0]);
2073 my ($mrid,$rank) = @$rec;
2074 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $count] );
2079 __PACKAGE__->register_method(
2080 api_name => "open-ils.storage.biblio.multiclass.search_fts.record",
2081 method => 'biblio_search_multi_class_fts',
2086 __PACKAGE__->register_method(
2087 api_name => "open-ils.storage.biblio.multiclass.search_fts.record.staff",
2088 method => 'biblio_search_multi_class_fts',
2096 __PACKAGE__->register_method(
2097 api_name => "open-ils.storage.biblio.multiclass.search_fts",
2098 method => 'biblio_search_multi_class_fts',
2103 __PACKAGE__->register_method(
2104 api_name => "open-ils.storage.biblio.multiclass.search_fts.staff",
2105 method => 'biblio_search_multi_class_fts',