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 = ?
289 JOIN $cn_table cn ON (cn.record = r.source)
290 JOIN $rd_table rd ON (cn.record = rd.record)
291 JOIN $cp_table cp ON (cn.id = cp.call_number)
292 JOIN $cs_table cs ON (cp.status = cs.id)
293 JOIN $cl_table cl ON (cp.location = cl.id)
294 JOIN $descendants a ON (cp.circ_lib = a.id)
295 WHERE r.metarecord = ?
304 JOIN $out_table t ON (u.ou_type = t.id)
308 my $sth = metabib::metarecord_source_map->db_Main->prepare_cached($sql);
309 $sth->execute( ''.$args{metarecord},
312 ''.$args{metarecord},
318 while ( my $row = $sth->fetchrow_hashref ) {
319 $client->respond( $row );
323 __PACKAGE__->register_method(
324 api_name => 'open-ils.storage.metabib.metarecord.copy_count',
325 method => 'metarecord_copy_count',
330 __PACKAGE__->register_method(
331 api_name => 'open-ils.storage.metabib.metarecord.copy_count.staff',
332 method => 'metarecord_copy_count',
338 sub biblio_multi_search_full_rec {
343 my $class_join = $args{class_join} || 'AND';
344 my $limit = $args{limit} || 100;
345 my $offset = $args{offset} || 0;
346 my $sort = $args{'sort'};
347 my $sort_dir = $args{sort_dir} || 'DESC';
352 for my $arg (@{ $args{searches} }) {
353 my $term = $$arg{term};
354 my $limiters = $$arg{restrict};
356 my ($index_col) = metabib::full_rec->columns('FTS');
357 $index_col ||= 'value';
358 my $search_table = metabib::full_rec->table;
360 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
362 my $fts_where = $fts->sql_where_clause();
363 my @fts_ranks = $fts->fts_rank;
365 my $rank = join(' + ', @fts_ranks);
368 for my $limit (@$limiters) {
369 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
370 push @binds, $$limit{tag}, $$limit{subfield};
371 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
373 my $where = join(' OR ', @wheres);
375 push @selects, "SELECT id, record, $rank as sum FROM $search_table WHERE $where";
379 my $descendants = defined($args{depth}) ?
380 "actor.org_unit_descendants($args{org_unit}, $args{depth})" :
381 "actor.org_unit_descendants($args{org_unit})" ;
384 my $metabib_record_descriptor = metabib::record_descriptor->table;
385 my $metabib_full_rec = metabib::full_rec->table;
386 my $asset_call_number_table = asset::call_number->table;
387 my $asset_copy_table = asset::copy->table;
388 my $cs_table = config::copy_status->table;
389 my $cl_table = asset::copy_location->table;
390 my $br_table = biblio::record_entry->table;
392 my $cj = 'HAVING COUNT(x.id) = ' . scalar(@selects) if ($class_join eq 'AND');
394 '(SELECT x.record, sum(x.sum) FROM (('.
395 join(') UNION ALL (', @selects).
396 ")) x GROUP BY 1 $cj ORDER BY 2 DESC )";
398 my $has_vols = 'AND cn.owning_lib = d.id';
399 my $has_copies = 'AND cp.call_number = cn.id';
400 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
402 if ($self->api_name =~ /staff/o) {
403 $copies_visible = '';
404 $has_copies = '' if ($ou_type == 0);
405 $has_vols = '' if ($ou_type == 0);
408 my ($t_filter, $f_filter) = ('','');
409 my ($a_filter, $l_filter, $lf_filter) = ('','','');
411 if (my $a = $args{audience}) {
412 $a = [$a] if (!ref($a));
415 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
419 if (my $l = $args{language}) {
420 $l = [$l] if (!ref($l));
423 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
427 if (my $f = $args{lit_form}) {
428 $f = [$f] if (!ref($f));
431 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
432 push @binds, @lit_form;
435 if (my $f = $args{item_form}) {
436 $f = [$f] if (!ref($f));
439 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
443 if (my $t = $args{item_type}) {
444 $t = [$t] if (!ref($t));
447 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
453 my ($t, $f) = split '-', $args{format};
454 my @types = split '', $t;
455 my @forms = split '', $f;
457 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
461 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
463 push @binds, @types, @forms;
466 my $relevance = 'sum(f.sum)';
467 $relevance = 1 if (!$copies_visible);
469 my $rank = $relevance;
470 if (lc($sort) eq 'pubdate') {
473 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
474 FROM $metabib_full_rec frp
475 WHERE frp.record = f.record
477 AND frp.subfield = 'c'
481 } elsif (lc($sort) eq 'create_date') {
483 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = f.record)) )
485 } elsif (lc($sort) eq 'edit_date') {
487 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = f.record)) )
489 } elsif (lc($sort) eq 'title') {
492 SELECT COALESCE(LTRIM(SUBSTR( frt.value, COALESCE(SUBSTRING(frt.ind2 FROM '\\\\d+'),'0')::INT )),'zzzzzzzz')
493 FROM $metabib_full_rec frt
494 WHERE frt.record = f.record
496 AND frt.subfield = 'a'
500 } elsif (lc($sort) eq 'author') {
503 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
504 FROM $metabib_full_rec fra
505 WHERE fra.record = f.record
506 AND fra.tag LIKE '1%'
507 AND fra.subfield = 'a'
508 ORDER BY fra.tag::text::int
517 if ($copies_visible) {
519 SELECT f.record, $relevance, count(DISTINCT cp.id), $rank
520 FROM $search_table f,
521 $asset_call_number_table cn,
522 $asset_copy_table cp,
526 $metabib_record_descriptor rd,
528 WHERE br.id = f.record
529 AND cn.record = f.record
530 AND rd.record = f.record
531 AND cp.status = cs.id
532 AND cp.location = cl.id
533 AND br.deleted IS FALSE
534 AND cn.deleted IS FALSE
535 AND cp.deleted IS FALSE
544 GROUP BY f.record HAVING count(DISTINCT cp.id) > 0
545 ORDER BY 4 $sort_dir,3 DESC
549 SELECT f.record, 1, 1, $rank
550 FROM $search_table f,
552 $metabib_record_descriptor rd
553 WHERE br.id = f.record
554 AND rd.record = f.record
555 AND br.deleted IS FALSE
567 $log->debug("Search SQL :: [$select]",DEBUG);
569 my $recs = metabib::full_rec->db_Main->selectall_arrayref("$select;", {}, @binds);
570 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
573 $max = 1 if (!@$recs);
575 $max = $$_[1] if ($$_[1] > $max);
579 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
580 next unless ($$rec[0]);
581 my ($rid,$rank,$junk,$skip) = @$rec;
582 $client->respond( [$rid, sprintf('%0.3f',$rank/$max), $count] );
586 __PACKAGE__->register_method(
587 api_name => 'open-ils.storage.biblio.full_rec.multi_search',
588 method => 'biblio_multi_search_full_rec',
593 __PACKAGE__->register_method(
594 api_name => 'open-ils.storage.biblio.full_rec.multi_search.staff',
595 method => 'biblio_multi_search_full_rec',
601 sub search_full_rec {
607 my $term = $args{term};
608 my $limiters = $args{restrict};
610 my ($index_col) = metabib::full_rec->columns('FTS');
611 $index_col ||= 'value';
612 my $search_table = metabib::full_rec->table;
614 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
616 my $fts_where = $fts->sql_where_clause();
617 my @fts_ranks = $fts->fts_rank;
619 my $rank = join(' + ', @fts_ranks);
623 for my $limit (@$limiters) {
624 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
625 push @binds, $$limit{tag}, $$limit{subfield};
626 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
628 my $where = join(' OR ', @wheres);
630 my $select = "SELECT record, sum($rank) FROM $search_table WHERE $where GROUP BY 1 ORDER BY 2 DESC;";
632 $log->debug("Search SQL :: [$select]",DEBUG);
634 my $recs = metabib::full_rec->db_Main->selectall_arrayref($select, {}, @binds);
635 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
637 $client->respond($_) for (@$recs);
640 __PACKAGE__->register_method(
641 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.value',
642 method => 'search_full_rec',
647 __PACKAGE__->register_method(
648 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.index_vector',
649 method => 'search_full_rec',
656 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
657 sub search_class_fts {
662 my $term = $args{term};
663 my $ou = $args{org_unit};
664 my $ou_type = $args{depth};
665 my $limit = $args{limit};
666 my $offset = $args{offset};
668 my $limit_clause = '';
669 my $offset_clause = '';
671 $limit_clause = "LIMIT $limit" if (defined $limit and int($limit) > 0);
672 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
675 my ($t_filter, $f_filter) = ('','');
678 my ($t, $f) = split '-', $args{format};
679 @types = split '', $t;
680 @forms = split '', $f;
682 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
686 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
692 my $descendants = defined($ou_type) ?
693 "actor.org_unit_descendants($ou, $ou_type)" :
694 "actor.org_unit_descendants($ou)";
696 my $class = $self->{cdbi};
697 my $search_table = $class->table;
699 my $metabib_record_descriptor = metabib::record_descriptor->table;
700 my $metabib_metarecord = metabib::metarecord->table;
701 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
702 my $asset_call_number_table = asset::call_number->table;
703 my $asset_copy_table = asset::copy->table;
704 my $cs_table = config::copy_status->table;
705 my $cl_table = asset::copy_location->table;
707 my ($index_col) = $class->columns('FTS');
708 $index_col ||= 'value';
710 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
712 my $fts_where = $fts->sql_where_clause;
713 my @fts_ranks = $fts->fts_rank;
715 my $rank = join(' + ', @fts_ranks);
717 my $has_vols = 'AND cn.owning_lib = d.id';
718 my $has_copies = 'AND cp.call_number = cn.id';
719 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
721 my $visible_count = ', count(DISTINCT cp.id)';
722 my $visible_count_test = 'HAVING count(DISTINCT cp.id) > 0';
724 if ($self->api_name =~ /staff/o) {
725 $copies_visible = '';
726 $visible_count_test = '';
727 $has_copies = '' if ($ou_type == 0);
728 $has_vols = '' if ($ou_type == 0);
731 my $rank_calc = <<" RANK";
733 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
734 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
735 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
736 )/COUNT(m.source)), MIN(COALESCE(CHAR_LENGTH(f.value),1))
739 $rank_calc = ',1 , 1' if ($self->api_name =~ /unordered/o);
741 if ($copies_visible) {
743 SELECT m.metarecord $rank_calc $visible_count, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
744 FROM $search_table f,
745 $metabib_metarecord_source_map_table m,
746 $asset_call_number_table cn,
747 $asset_copy_table cp,
750 $metabib_record_descriptor rd,
753 AND m.source = f.source
754 AND cn.record = m.source
755 AND rd.record = m.source
756 AND cp.status = cs.id
757 AND cp.location = cl.id
763 GROUP BY 1 $visible_count_test
765 $limit_clause $offset_clause
769 SELECT m.metarecord $rank_calc, 0, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
770 FROM $search_table f,
771 $metabib_metarecord_source_map_table m,
772 $metabib_record_descriptor rd
774 AND m.source = f.source
775 AND rd.record = m.source
780 $limit_clause $offset_clause
784 $log->debug("Field Search SQL :: [$select]",DEBUG);
786 my $SQLstring = join('%',$fts->words);
787 my $REstring = join('\\s+',$fts->words);
788 my $first_word = ($fts->words)[0].'%';
789 my $recs = ($self->api_name =~ /unordered/o) ?
790 $class->db_Main->selectall_arrayref($select, {}, @types, @forms) :
791 $class->db_Main->selectall_arrayref($select, {},
792 '%'.lc($SQLstring).'%', # phrase order match
793 lc($first_word), # first word match
794 '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match
798 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
800 $client->respond($_) for (map { [@$_[0,1,3,4]] } @$recs);
804 for my $class ( qw/title author subject keyword series/ ) {
805 __PACKAGE__->register_method(
806 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord",
807 method => 'search_class_fts',
810 cdbi => "metabib::${class}_field_entry",
813 __PACKAGE__->register_method(
814 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.unordered",
815 method => 'search_class_fts',
818 cdbi => "metabib::${class}_field_entry",
821 __PACKAGE__->register_method(
822 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff",
823 method => 'search_class_fts',
826 cdbi => "metabib::${class}_field_entry",
829 __PACKAGE__->register_method(
830 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff.unordered",
831 method => 'search_class_fts',
834 cdbi => "metabib::${class}_field_entry",
839 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
840 sub search_class_fts_count {
845 my $term = $args{term};
846 my $ou = $args{org_unit};
847 my $ou_type = $args{depth};
848 my $limit = $args{limit} || 100;
849 my $offset = $args{offset} || 0;
851 my $descendants = defined($ou_type) ?
852 "actor.org_unit_descendants($ou, $ou_type)" :
853 "actor.org_unit_descendants($ou)";
856 my ($t_filter, $f_filter) = ('','');
859 my ($t, $f) = split '-', $args{format};
860 @types = split '', $t;
861 @forms = split '', $f;
863 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
867 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
872 (my $search_class = $self->api_name) =~ s/.*metabib.(\w+).search_fts.*/$1/o;
874 my $class = $self->{cdbi};
875 my $search_table = $class->table;
877 my $metabib_record_descriptor = metabib::record_descriptor->table;
878 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
879 my $asset_call_number_table = asset::call_number->table;
880 my $asset_copy_table = asset::copy->table;
881 my $cs_table = config::copy_status->table;
882 my $cl_table = asset::copy_location->table;
884 my ($index_col) = $class->columns('FTS');
885 $index_col ||= 'value';
887 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
889 my $fts_where = $fts->sql_where_clause;
891 my $has_vols = 'AND cn.owning_lib = d.id';
892 my $has_copies = 'AND cp.call_number = cn.id';
893 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
894 if ($self->api_name =~ /staff/o) {
895 $copies_visible = '';
896 $has_vols = '' if ($ou_type == 0);
897 $has_copies = '' if ($ou_type == 0);
900 # XXX test an "EXISTS version of descendant checking...
902 if ($copies_visible) {
904 SELECT count(distinct m.metarecord)
905 FROM $search_table f,
906 $metabib_metarecord_source_map_table m,
907 $metabib_metarecord_source_map_table mr,
908 $asset_call_number_table cn,
909 $asset_copy_table cp,
912 $metabib_record_descriptor rd,
915 AND mr.source = f.source
916 AND mr.metarecord = m.metarecord
917 AND cn.record = m.source
918 AND rd.record = m.source
919 AND cp.status = cs.id
920 AND cp.location = cl.id
929 SELECT count(distinct m.metarecord)
930 FROM $search_table f,
931 $metabib_metarecord_source_map_table m,
932 $metabib_metarecord_source_map_table mr,
933 $metabib_record_descriptor rd
935 AND mr.source = f.source
936 AND mr.metarecord = m.metarecord
937 AND rd.record = m.source
943 $log->debug("Field Search Count SQL :: [$select]",DEBUG);
945 my $recs = $class->db_Main->selectrow_arrayref($select, {}, @types, @forms)->[0];
947 $log->debug("Count Search yielded $recs results.",DEBUG);
952 for my $class ( qw/title author subject keyword series/ ) {
953 __PACKAGE__->register_method(
954 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count",
955 method => 'search_class_fts_count',
958 cdbi => "metabib::${class}_field_entry",
961 __PACKAGE__->register_method(
962 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count.staff",
963 method => 'search_class_fts_count',
966 cdbi => "metabib::${class}_field_entry",
972 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
973 sub postfilter_search_class_fts {
978 my $term = $args{term};
979 my $sort = $args{'sort'};
980 my $sort_dir = $args{sort_dir} || 'DESC';
981 my $ou = $args{org_unit};
982 my $ou_type = $args{depth};
983 my $limit = $args{limit} || 10;
984 my $offset = $args{offset} || 0;
986 my $outer_limit = 1000;
988 my $limit_clause = '';
989 my $offset_clause = '';
991 $limit_clause = "LIMIT $outer_limit";
992 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
994 my (@types,@forms,@lang,@aud,@lit_form);
995 my ($t_filter, $f_filter) = ('','');
996 my ($a_filter, $l_filter, $lf_filter) = ('','','');
997 my ($ot_filter, $of_filter) = ('','');
998 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
1000 if (my $a = $args{audience}) {
1001 $a = [$a] if (!ref($a));
1004 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
1005 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
1008 if (my $l = $args{language}) {
1009 $l = [$l] if (!ref($l));
1012 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1013 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1016 if (my $f = $args{lit_form}) {
1017 $f = [$f] if (!ref($f));
1020 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1021 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1024 if ($args{format}) {
1025 my ($t, $f) = split '-', $args{format};
1026 @types = split '', $t;
1027 @forms = split '', $f;
1029 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1030 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1034 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1035 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1040 my $descendants = defined($ou_type) ?
1041 "actor.org_unit_descendants($ou, $ou_type)" :
1042 "actor.org_unit_descendants($ou)";
1044 my $class = $self->{cdbi};
1045 my $search_table = $class->table;
1047 my $metabib_full_rec = metabib::full_rec->table;
1048 my $metabib_record_descriptor = metabib::record_descriptor->table;
1049 my $metabib_metarecord = metabib::metarecord->table;
1050 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1051 my $asset_call_number_table = asset::call_number->table;
1052 my $asset_copy_table = asset::copy->table;
1053 my $cs_table = config::copy_status->table;
1054 my $cl_table = asset::copy_location->table;
1055 my $br_table = biblio::record_entry->table;
1057 my ($index_col) = $class->columns('FTS');
1058 $index_col ||= 'value';
1060 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
1062 my $SQLstring = join('%',map { lc($_) } $fts->words);
1063 my $REstring = '^' . join('\s+',map { lc($_) } $fts->words) . '\W*$';
1064 my $first_word = lc(($fts->words)[0]).'%';
1066 my $fts_where = $fts->sql_where_clause;
1067 my @fts_ranks = $fts->fts_rank;
1070 $bonus{'metabib::keyword_field_entry'} = [ { 'CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END' => $SQLstring } ];
1071 $bonus{'metabib::title_field_entry'} =
1072 $bonus{'metabib::series_field_entry'} = [
1073 { 'CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END' => $first_word },
1074 { 'CASE WHEN f.value ~* ? THEN 2 ELSE 1 END' => $REstring },
1075 @{ $bonus{'metabib::keyword_field_entry'} }
1078 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$class} };
1079 $bonus_list ||= '1';
1081 my @bonus_values = map { values %$_ } @{ $bonus{$class} };
1083 my $relevance = join(' + ', @fts_ranks);
1084 $relevance = <<" RANK";
1085 (SUM( ( $relevance ) * ( $bonus_list ) )/COUNT(m.source))
1088 my $rank = $relevance;
1089 if (lc($sort) eq 'pubdate') {
1092 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
1093 FROM $metabib_full_rec frp
1094 WHERE frp.record = mr.master_record
1096 AND frp.subfield = 'c'
1100 } elsif (lc($sort) eq 'create_date') {
1102 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1104 } elsif (lc($sort) eq 'edit_date') {
1106 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1108 } elsif (lc($sort) eq 'title') {
1111 SELECT COALESCE(LTRIM(SUBSTR( frt.value, COALESCE(SUBSTRING(frt.ind2 FROM '\\\\d+'),'0')::INT )),'zzzzzzzz')
1112 FROM $metabib_full_rec frt
1113 WHERE frt.record = mr.master_record
1115 AND frt.subfield = 'a'
1119 } elsif (lc($sort) eq 'author') {
1122 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1123 FROM $metabib_full_rec fra
1124 WHERE fra.record = mr.master_record
1125 AND fra.tag LIKE '1%'
1126 AND fra.subfield = 'a'
1127 ORDER BY fra.tag::text::int
1135 my $select = <<" SQL";
1136 SELECT m.metarecord,
1138 CASE WHEN COUNT(DISTINCT smrs.source) = 1 THEN MIN(m.source) ELSE 0 END,
1140 FROM $search_table f,
1141 $metabib_metarecord_source_map_table m,
1142 $metabib_metarecord_source_map_table smrs,
1143 $metabib_metarecord mr,
1144 $metabib_record_descriptor rd
1146 AND smrs.metarecord = mr.id
1147 AND m.source = f.source
1148 AND m.metarecord = mr.id
1149 AND rd.record = smrs.source
1155 GROUP BY m.metarecord
1156 ORDER BY 4 $sort_dir, MIN(COALESCE(CHAR_LENGTH(f.value),1))
1164 FROM $asset_call_number_table cn,
1165 $metabib_metarecord_source_map_table mrs,
1166 $asset_copy_table cp,
1171 $metabib_record_descriptor ord,
1173 WHERE mrs.metarecord = s.metarecord
1174 AND br.id = mrs.source
1175 AND cn.record = mrs.source
1176 AND cp.status = cs.id
1177 AND cp.location = cl.id
1178 AND cn.owning_lib = d.id
1179 AND cp.call_number = cn.id
1180 AND cp.opac_visible IS TRUE
1181 AND cs.holdable IS TRUE
1182 AND cl.opac_visible IS TRUE
1183 AND br.active IS TRUE
1184 AND br.deleted IS FALSE
1185 AND ord.record = mrs.source
1191 ORDER BY 4 $sort_dir
1193 } elsif ($self->api_name !~ /staff/o) {
1200 FROM $asset_call_number_table cn,
1201 $metabib_metarecord_source_map_table mrs,
1202 $asset_copy_table cp,
1207 $metabib_record_descriptor ord
1209 WHERE mrs.metarecord = s.metarecord
1210 AND br.id = mrs.source
1211 AND cn.record = mrs.source
1212 AND cp.status = cs.id
1213 AND cp.location = cl.id
1214 AND cn.owning_lib = d.id
1215 AND cp.call_number = cn.id
1216 AND cp.opac_visible IS TRUE
1217 AND cs.holdable IS TRUE
1218 AND cl.opac_visible IS TRUE
1219 AND br.active IS TRUE
1220 AND br.deleted IS FALSE
1221 AND ord.record = mrs.source
1229 ORDER BY 4 $sort_dir
1238 FROM $asset_call_number_table cn,
1239 $metabib_metarecord_source_map_table mrs,
1242 $metabib_record_descriptor ord
1244 WHERE mrs.metarecord = s.metarecord
1245 AND br.id = mrs.source
1246 AND cn.record = mrs.source
1247 AND cn.owning_lib = d.id
1248 AND br.deleted IS FALSE
1249 AND ord.record = mrs.source
1259 FROM $asset_call_number_table cn,
1260 $metabib_metarecord_source_map_table mrs,
1261 $metabib_record_descriptor ord
1262 WHERE mrs.metarecord = s.metarecord
1263 AND cn.record = mrs.source
1264 AND ord.record = mrs.source
1272 ORDER BY 4 $sort_dir
1277 $log->debug("Field Search SQL :: [$select]",DEBUG);
1279 my $recs = $class->db_Main->selectall_arrayref(
1281 (@bonus_values > 0 ? @bonus_values : () ),
1282 ( (!$sort && @bonus_values > 0) ? @bonus_values : () ),
1283 @types, @forms, @aud, @lang, @lit_form,
1284 @types, @forms, @aud, @lang, @lit_form,
1285 ($self->api_name =~ /staff/o ? (@types, @forms, @aud, @lang, @lit_form) : () ) );
1287 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1290 $max = 1 if (!@$recs);
1292 $max = $$_[1] if ($$_[1] > $max);
1295 my $count = scalar(@$recs);
1296 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1297 my ($mrid,$rank,$skip) = @$rec;
1298 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $skip, $count] );
1303 for my $class ( qw/title author subject keyword series/ ) {
1304 __PACKAGE__->register_method(
1305 api_name => "open-ils.storage.metabib.$class.post_filter.search_fts.metarecord",
1306 method => 'postfilter_search_class_fts',
1309 cdbi => "metabib::${class}_field_entry",
1312 __PACKAGE__->register_method(
1313 api_name => "open-ils.storage.metabib.$class.post_filter.search_fts.metarecord.staff",
1314 method => 'postfilter_search_class_fts',
1317 cdbi => "metabib::${class}_field_entry",
1324 my $_cdbi = { title => "metabib::title_field_entry",
1325 author => "metabib::author_field_entry",
1326 subject => "metabib::subject_field_entry",
1327 keyword => "metabib::keyword_field_entry",
1328 series => "metabib::series_field_entry",
1331 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
1332 sub postfilter_search_multi_class_fts {
1337 my $sort = $args{'sort'};
1338 my $sort_dir = $args{sort_dir} || 'DESC';
1339 my $ou = $args{org_unit};
1340 my $ou_type = $args{depth};
1341 my $limit = $args{limit} || 10;;
1342 my $offset = $args{offset} || 0;
1345 $ou = actor::org_unit->search( { parent_ou => undef } )->next->id;
1348 if (!defined($args{org_unit})) {
1349 die "No target organizational unit passed to ".$self->api_name;
1352 if (! scalar( keys %{$args{searches}} )) {
1353 die "No search arguments were passed to ".$self->api_name;
1356 my $outer_limit = 1000;
1358 my $limit_clause = '';
1359 my $offset_clause = '';
1361 $limit_clause = "LIMIT $outer_limit";
1362 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
1364 my (@types,@forms,@lang,@aud,@lit_form);
1365 my ($t_filter, $f_filter) = ('','');
1366 my ($a_filter, $l_filter, $lf_filter) = ('','','');
1367 my ($ot_filter, $of_filter) = ('','');
1368 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
1370 if (my $a = $args{audience}) {
1371 $a = [$a] if (!ref($a));
1374 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
1375 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
1378 if (my $l = $args{language}) {
1379 $l = [$l] if (!ref($l));
1382 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1383 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1386 if (my $f = $args{lit_form}) {
1387 $f = [$f] if (!ref($f));
1390 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1391 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1394 if (my $f = $args{item_form}) {
1395 $f = [$f] if (!ref($f));
1398 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1399 $of_filter = ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1402 if (my $t = $args{item_type}) {
1403 $t = [$t] if (!ref($t));
1406 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1407 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1411 # XXX legacy format and item type support
1412 if ($args{format}) {
1413 my ($t, $f) = split '-', $args{format};
1414 @types = split '', $t;
1415 @forms = split '', $f;
1417 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1418 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1422 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1423 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1429 my $descendants = defined($ou_type) ?
1430 "actor.org_unit_descendants($ou, $ou_type)" :
1431 "actor.org_unit_descendants($ou)";
1433 my $search_table_list = '';
1435 my $join_table_list = '';
1440 my $prev_search_class;
1441 my $curr_search_class;
1442 for my $search_class (sort keys %{$args{searches}}) {
1443 $prev_search_class = $curr_search_class if ($curr_search_class);
1445 $curr_search_class = $search_class;
1447 my $class = $_cdbi->{$search_class};
1448 my $search_table = $class->table;
1450 my ($index_col) = $class->columns('FTS');
1451 $index_col ||= 'value';
1454 my $fts = OpenILS::Application::Storage::FTS->compile($args{searches}{$search_class}{term}, $search_class.'.value', "$search_class.$index_col");
1456 my $fts_where = $fts->sql_where_clause;
1457 my @fts_ranks = $fts->fts_rank;
1459 my $SQLstring = join('%',map { lc($_) } $fts->words);
1460 my $REstring = '^' . join('\s+',map { lc($_) } $fts->words) . '\W*$';
1461 my $first_word = lc(($fts->words)[0]).'%';
1463 my $rank = join(' + ', @fts_ranks);
1466 $bonus{'keyword'} = [ { "CASE WHEN $search_class.value LIKE ? THEN 1.2 ELSE 1 END" => $SQLstring } ];
1468 $bonus{'series'} = [
1469 { "CASE WHEN $search_class.value LIKE ? THEN 1.5 ELSE 1 END" => $first_word },
1470 { "CASE WHEN $search_class.value ~ ? THEN 20 ELSE 1 END" => $REstring },
1473 $bonus{'title'} = [ @{ $bonus{'series'} }, @{ $bonus{'keyword'} } ];
1475 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$search_class} };
1476 $bonus_list ||= '1';
1478 push @bonus_lists, $bonus_list;
1479 push @bonus_values, map { values %$_ } @{ $bonus{$search_class} };
1482 #---------------------
1484 $search_table_list .= "$search_table $search_class, ";
1485 push @rank_list,$rank;
1486 $fts_list .= " AND $fts_where AND m.source = $search_class.source";
1488 if ($prev_search_class) {
1489 $join_table_list .= " AND $prev_search_class.source = $curr_search_class.source";
1493 my $metabib_record_descriptor = metabib::record_descriptor->table;
1494 my $metabib_full_rec = metabib::full_rec->table;
1495 my $metabib_metarecord = metabib::metarecord->table;
1496 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1497 my $asset_call_number_table = asset::call_number->table;
1498 my $asset_copy_table = asset::copy->table;
1499 my $cs_table = config::copy_status->table;
1500 my $cl_table = asset::copy_location->table;
1501 my $br_table = biblio::record_entry->table;
1503 my $bonuses = join (' * ', @bonus_lists);
1504 my $relevance = join (' + ', @rank_list);
1505 $relevance = "SUM( ($relevance) * ($bonuses) )/COUNT(DISTINCT smrs.source)";
1508 my $secondary_sort = <<" SORT";
1510 SELECT COALESCE(LTRIM(SUBSTR( sfrt.value, COALESCE(SUBSTRING(sfrt.ind2 FROM '\\\\d+'),'0')::INT )),'zzzzzzzz')
1511 FROM $metabib_full_rec sfrt,
1512 $metabib_metarecord mr
1513 WHERE sfrt.record = mr.master_record
1514 AND sfrt.tag = '245'
1515 AND sfrt.subfield = 'a'
1520 my $rank = $relevance;
1521 if (lc($sort) eq 'pubdate') {
1524 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
1525 FROM $metabib_full_rec frp
1526 WHERE frp.record = mr.master_record
1528 AND frp.subfield = 'c'
1532 } elsif (lc($sort) eq 'create_date') {
1534 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1536 } elsif (lc($sort) eq 'edit_date') {
1538 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1540 } elsif (lc($sort) eq 'title') {
1543 SELECT COALESCE(LTRIM(SUBSTR( frt.value, COALESCE(SUBSTRING(frt.ind2 FROM '\\\\d+'),'0')::INT )),'zzzzzzzz')
1544 FROM $metabib_full_rec frt
1545 WHERE frt.record = mr.master_record
1547 AND frt.subfield = 'a'
1551 $secondary_sort = <<" SORT";
1553 SELECT COALESCE(SUBSTRING(sfrp.value FROM '\\\\d+'),'9999')::INT
1554 FROM $metabib_full_rec sfrp,
1555 $metabib_metarecord mr
1556 WHERE sfrp.record = mr.master_record
1557 AND sfrp.tag = '260'
1558 AND sfrp.subfield = 'c'
1562 } elsif (lc($sort) eq 'author') {
1565 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1566 FROM $metabib_full_rec fra
1567 WHERE fra.record = mr.master_record
1568 AND fra.tag LIKE '1%'
1569 AND fra.subfield = 'a'
1570 ORDER BY fra.tag::text::int
1575 push @bonus_values, @bonus_values;
1580 my $select = <<" SQL";
1581 SELECT m.metarecord,
1583 CASE WHEN COUNT(DISTINCT smrs.source) = 1 THEN FIRST(m.source) ELSE 0 END,
1586 FROM $search_table_list
1587 $metabib_metarecord_source_map_table m,
1588 $metabib_metarecord_source_map_table smrs
1589 WHERE m.metarecord = smrs.metarecord
1592 GROUP BY m.metarecord
1593 -- ORDER BY 4 $sort_dir
1597 if ($self->api_name !~ /staff/o) {
1604 FROM $asset_call_number_table cn,
1605 $metabib_metarecord_source_map_table mrs,
1606 $asset_copy_table cp,
1611 $metabib_record_descriptor ord
1612 WHERE mrs.metarecord = s.metarecord
1613 AND br.id = mrs.source
1614 AND cn.record = mrs.source
1615 AND cp.status = cs.id
1616 AND cp.location = cl.id
1617 AND cn.owning_lib = d.id
1618 AND cp.call_number = cn.id
1619 AND cp.opac_visible IS TRUE
1620 AND cs.holdable IS TRUE
1621 AND cl.opac_visible IS TRUE
1622 AND br.active IS TRUE
1623 AND br.deleted IS FALSE
1624 AND cp.deleted IS FALSE
1625 AND cn.deleted IS FALSE
1626 AND ord.record = mrs.source
1634 ORDER BY 4 $sort_dir, 5
1641 $metabib_metarecord_source_map_table omrs,
1642 $metabib_record_descriptor ord
1643 WHERE omrs.metarecord = s.metarecord
1644 AND ord.record = omrs.source
1647 FROM $asset_call_number_table cn,
1650 WHERE br.id = omrs.source
1651 AND cn.record = omrs.source
1652 AND cn.owning_lib = d.id
1653 AND br.deleted IS FALSE
1654 AND cn.deleted IS FALSE
1659 FROM $asset_call_number_table cn
1660 WHERE cn.record = omrs.source
1661 AND cn.deleted IS FALSE
1671 ORDER BY 4 $sort_dir, 5
1676 $log->debug("Field Search SQL :: [$select]",DEBUG);
1678 my $recs = $_cdbi->{title}->db_Main->selectall_arrayref(
1681 @types, @forms, @aud, @lang, @lit_form,
1682 # @types, @forms, @aud, @lang, @lit_form,
1683 # ($self->api_name =~ /staff/o ? (@types, @forms, @aud, @lang, @lit_form) : () )
1686 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1689 $max = 1 if (!@$recs);
1691 $max = $$_[1] if ($$_[1] > $max);
1694 my $count = scalar(@$recs);
1695 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1696 next unless ($$rec[0]);
1697 my ($mrid,$rank,$skip) = @$rec;
1698 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $skip, $count] );
1703 __PACKAGE__->register_method(
1704 api_name => "open-ils.storage.metabib.post_filter.multiclass.search_fts.metarecord",
1705 method => 'postfilter_search_multi_class_fts',
1710 __PACKAGE__->register_method(
1711 api_name => "open-ils.storage.metabib.post_filter.multiclass.search_fts.metarecord.staff",
1712 method => 'postfilter_search_multi_class_fts',
1718 __PACKAGE__->register_method(
1719 api_name => "open-ils.storage.metabib.multiclass.search_fts",
1720 method => 'postfilter_search_multi_class_fts',
1725 __PACKAGE__->register_method(
1726 api_name => "open-ils.storage.metabib.multiclass.search_fts.staff",
1727 method => 'postfilter_search_multi_class_fts',
1733 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
1734 sub biblio_search_multi_class_fts {
1739 my $sort = $args{'sort'};
1740 my $sort_dir = $args{sort_dir} || 'DESC';
1741 my $ou = $args{org_unit};
1742 my $ou_type = $args{depth};
1743 my $limit = $args{limit} || 10;
1744 my $offset = $args{offset} || 0;
1747 $ou = actor::org_unit->search( { parent_ou => undef } )->next->id;
1750 if (!defined($args{org_unit})) {
1751 die "No target organizational unit passed to ".$self->api_name;
1754 if (! scalar( keys %{$args{searches}} )) {
1755 die "No search arguments were passed to ".$self->api_name;
1758 my $outer_limit = 1000;
1760 my $limit_clause = '';
1761 my $offset_clause = '';
1763 $limit_clause = "LIMIT $outer_limit";
1764 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
1766 my (@types,@forms,@lang,@aud,@lit_form);
1767 my ($t_filter, $f_filter) = ('','');
1768 my ($a_filter, $l_filter, $lf_filter) = ('','','');
1769 my ($ot_filter, $of_filter) = ('','');
1770 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
1772 if (my $a = $args{audience}) {
1773 $a = [$a] if (!ref($a));
1776 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
1777 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
1780 if (my $l = $args{language}) {
1781 $l = [$l] if (!ref($l));
1784 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1785 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1788 if (my $f = $args{lit_form}) {
1789 $f = [$f] if (!ref($f));
1792 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1793 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1796 if (my $f = $args{item_form}) {
1797 $f = [$f] if (!ref($f));
1800 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1801 $of_filter = ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1804 if (my $t = $args{item_type}) {
1805 $t = [$t] if (!ref($t));
1808 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1809 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1813 # XXX legacy format and item type support
1814 if ($args{format}) {
1815 my ($t, $f) = split '-', $args{format};
1816 @types = split '', $t;
1817 @forms = split '', $f;
1819 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1820 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1824 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1825 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1830 my $descendants = defined($ou_type) ?
1831 "actor.org_unit_descendants($ou, $ou_type)" :
1832 "actor.org_unit_descendants($ou)";
1834 my $search_table_list = '';
1836 my $join_table_list = '';
1842 my $prev_search_class;
1843 my $curr_search_class;
1844 for my $search_class (sort keys %{$args{searches}}) {
1845 $prev_search_class = $curr_search_class if ($curr_search_class);
1847 $curr_search_class = $search_class;
1849 my $class = $_cdbi->{$search_class};
1850 my $search_table = $class->table;
1852 my ($index_col) = $class->columns('FTS');
1853 $index_col ||= 'value';
1856 my $fts = OpenILS::Application::Storage::FTS->compile($args{searches}{$search_class}{term}, $search_class.'.value', "$search_class.$index_col");
1858 my $fts_where = $fts->sql_where_clause;
1859 my @fts_ranks = $fts->fts_rank;
1861 my $SQLstring = join('%',map { lc($_) } $fts->words);
1862 my $REstring = '^' . join('\s+',map { lc($_) } $fts->words) . '\W*$';
1863 my $first_word = lc(($fts->words)[0]).'%';
1865 my $rank = join(' + ', @fts_ranks);
1868 $bonus{'keyword'} = [ { "CASE WHEN $search_class.value ILIKE ? THEN 1.2 ELSE 1 END" => $SQLstring } ];
1870 $bonus{'series'} = [
1871 { "CASE WHEN $search_class.value ILIKE ? THEN 1.5 ELSE 1 END" => $first_word },
1872 { "CASE WHEN $search_class.value ~ ? THEN 200 ELSE 1 END" => $REstring },
1875 $bonus{'title'} = [ @{ $bonus{'series'} }, @{ $bonus{'keyword'} } ];
1877 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$search_class} };
1878 $bonus_list ||= '1';
1880 push @bonus_lists, $bonus_list;
1881 push @bonus_values, map { values %$_ } @{ $bonus{$search_class} };
1883 #---------------------
1885 $search_table_list .= "$search_table $search_class, ";
1886 push @rank_list,$rank;
1887 $fts_list .= " AND $fts_where AND b.id = $search_class.source";
1889 if ($prev_search_class) {
1890 $join_table_list .= " AND $prev_search_class.source = $curr_search_class.source";
1894 my $metabib_record_descriptor = metabib::record_descriptor->table;
1895 my $metabib_full_rec = metabib::full_rec->table;
1896 my $metabib_metarecord = metabib::metarecord->table;
1897 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1898 my $asset_call_number_table = asset::call_number->table;
1899 my $asset_copy_table = asset::copy->table;
1900 my $cs_table = config::copy_status->table;
1901 my $cl_table = asset::copy_location->table;
1902 my $br_table = biblio::record_entry->table;
1905 my $bonuses = join (' * ', @bonus_lists);
1906 my $relevance = join (' + ', @rank_list);
1907 $relevance = "AVG( ($relevance) * ($bonuses) )";
1910 my $rank = $relevance;
1911 if (lc($sort) eq 'pubdate') {
1914 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d{4}'),'9999')::INT
1915 FROM $metabib_full_rec frp
1916 WHERE frp.record = b.id
1918 AND frp.subfield = 'c'
1922 } elsif (lc($sort) eq 'create_date') {
1924 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = b.id)) )
1926 } elsif (lc($sort) eq 'edit_date') {
1928 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = b.id)) )
1930 } elsif (lc($sort) eq 'title') {
1933 SELECT COALESCE(LTRIM(SUBSTR( frt.value, COALESCE(SUBSTRING(frt.ind2 FROM '\\\\d+'),'0')::INT )),'zzzzzzzz')
1934 FROM $metabib_full_rec frt
1935 WHERE frt.record = b.id
1937 AND frt.subfield = 'a'
1941 } elsif (lc($sort) eq 'author') {
1944 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1945 FROM $metabib_full_rec fra
1946 WHERE fra.record = b.id
1947 AND fra.tag LIKE '1%'
1948 AND fra.subfield = 'a'
1949 ORDER BY fra.tag::text::int
1954 push @bonus_values, @bonus_values;
1959 my $select = <<" SQL";
1963 FROM $search_table_list
1964 $metabib_record_descriptor rd,
1966 WHERE rd.record = b.id
1967 AND b.active IS TRUE
1968 AND b.deleted IS FALSE
1977 ORDER BY 3 $sort_dir
1981 if ($self->api_name !~ /staff/o) {
1988 FROM $asset_call_number_table cn,
1989 $asset_copy_table cp,
1993 WHERE cn.record = s.id
1994 AND cp.status = cs.id
1995 AND cp.location = cl.id
1996 AND cn.owning_lib = d.id
1997 AND cp.call_number = cn.id
1998 AND cp.opac_visible IS TRUE
1999 AND cs.holdable IS TRUE
2000 AND cl.opac_visible IS TRUE
2001 AND cp.deleted IS FALSE
2002 AND cn.deleted IS FALSE
2005 ORDER BY 3 $sort_dir
2014 FROM $asset_call_number_table cn,
2016 WHERE cn.record = s.id
2017 AND cn.owning_lib = d.id
2018 AND cn.deleted IS FALSE
2023 FROM $asset_call_number_table cn
2024 WHERE cn.record = s.id
2027 ORDER BY 3 $sort_dir
2032 $log->debug("Field Search SQL :: [$select]",DEBUG);
2034 my $recs = $_cdbi->{title}->db_Main->selectall_arrayref(
2036 @bonus_values, @types, @forms, @aud, @lang, @lit_form
2039 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
2042 $max = 1 if (!@$recs);
2044 $max = $$_[1] if ($$_[1] > $max);
2047 my $count = scalar(@$recs);
2048 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
2049 next unless ($$rec[0]);
2050 my ($mrid,$rank) = @$rec;
2051 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $count] );
2056 __PACKAGE__->register_method(
2057 api_name => "open-ils.storage.biblio.multiclass.search_fts.record",
2058 method => 'biblio_search_multi_class_fts',
2063 __PACKAGE__->register_method(
2064 api_name => "open-ils.storage.biblio.multiclass.search_fts.record.staff",
2065 method => 'biblio_search_multi_class_fts',
2073 __PACKAGE__->register_method(
2074 api_name => "open-ils.storage.biblio.multiclass.search_fts",
2075 method => 'biblio_search_multi_class_fts',
2080 __PACKAGE__->register_method(
2081 api_name => "open-ils.storage.biblio.multiclass.search_fts.staff",
2082 method => 'biblio_search_multi_class_fts',