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 # need to order record IDs by:
18 # 1) format - text, movie, sound, software, images, maps, mixed, music, 3d
19 # 2) proximity --- XXX Can't do it cheap...
21 sub ordered_records_from_metarecord {
32 my ($t, $f) = split '-', $formats;
33 @types = split '', $t;
34 @forms = split '', $f;
39 "actor.org_unit_descendants($org, $depth)" :
40 "actor.org_unit_descendants($org)" ;
43 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
44 $copies_visible = '' if ($self->api_name =~ /staff/o);
46 my $sm_table = metabib::metarecord_source_map->table;
47 my $rd_table = metabib::record_descriptor->table;
48 my $fr_table = metabib::full_rec->table;
49 my $cn_table = asset::call_number->table;
50 my $cl_table = asset::copy_location->table;
51 my $cp_table = asset::copy->table;
52 my $cs_table = config::copy_status->table;
53 my $out_table = actor::org_unit_type->table;
54 my $br_table = biblio::record_entry->table;
57 SELECT record, item_type, item_form, count, title
63 FIRST(COALESCE(LTRIM(SUBSTR( fr.value, fr.ind2::text::int )),'zzzzzzzz')) AS title,
66 if ($copies_visible) {
68 sum((SELECT count(cp.id)
70 JOIN $cs_table cs ON (cp.status = cs.id)
71 JOIN $cl_table cl ON (cp.location = cl.id)
72 JOIN $descendants d ON (cp.circ_lib = d.id)
73 WHERE cn.id = cp.call_number
81 if ($copies_visible) {
88 WHERE rd.record = sm.source
93 AND cn.record = rd.record
102 WHERE rd.record = sm.source
103 AND fr.record = br.id
105 AND fr.subfield = 'a'
106 AND br.id = rd.record
107 AND sm.metarecord = ?
112 GROUP BY rd.record, rd.item_type, rd.item_form, br.quality
115 WHEN rd.item_type IS NULL -- default
117 WHEN rd.item_type = '' -- default
119 WHEN rd.item_type IN ('a','t') -- books
121 WHEN rd.item_type = 'g' -- movies
123 WHEN rd.item_type IN ('i','j') -- sound recordings
125 WHEN rd.item_type = 'm' -- software
127 WHEN rd.item_type = 'k' -- images
129 WHEN rd.item_type IN ('e','f') -- maps
131 WHEN rd.item_type IN ('o','p') -- mixed
133 WHEN rd.item_type IN ('c','d') -- music
135 WHEN rd.item_type = 'r' -- 3d
144 if ($copies_visible) {
145 $sql .= ' WHERE x.count > 0'
149 $sql .= ' AND x.item_type IN ('.join(',',map{'?'}@types).')';
153 $sql .= ' AND x.item_form IN ('.join(',',map{'?'}@forms).')';
156 my $sth = metabib::metarecord_source_map->db_Main->prepare_cached($sql);
157 $sth->execute("$mr", @types, @forms);
158 while ( my $row = $sth->fetchrow_arrayref ) {
159 $client->respond( $$row[0] );
164 __PACKAGE__->register_method(
165 api_name => 'open-ils.storage.ordered.metabib.metarecord.records',
166 method => 'ordered_records_from_metarecord',
171 __PACKAGE__->register_method(
172 api_name => 'open-ils.storage.ordered.metabib.metarecord.records.staff',
173 method => 'ordered_records_from_metarecord',
184 my $tag = ($self->api_name =~ /isbn/o) ? '020' : '022';
186 my $fr_table = metabib::full_rec->table;
195 my $list = metabib::metarecord_source_map->db_Main->selectcol_arrayref($sql, {}, $tag, "$isxn%");
196 $client->respond($_) for (@$list);
199 __PACKAGE__->register_method(
200 api_name => 'open-ils.storage.id_list.biblio.record_entry.search.isbn',
201 method => 'isxn_search',
205 __PACKAGE__->register_method(
206 api_name => 'open-ils.storage.id_list.biblio.record_entry.search.issn',
207 method => 'isxn_search',
212 sub metarecord_copy_count {
218 my $sm_table = metabib::metarecord_source_map->table;
219 my $rd_table = metabib::record_descriptor->table;
220 my $cn_table = asset::call_number->table;
221 my $cp_table = asset::copy->table;
222 my $cl_table = asset::copy_location->table;
223 my $cs_table = config::copy_status->table;
224 my $out_table = actor::org_unit_type->table;
225 my $descendants = "actor.org_unit_descendants(u.id)";
226 my $ancestors = "actor.org_unit_ancestors(?)";
228 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
229 $copies_visible = '' if ($self->api_name =~ /staff/o);
232 my ($t_filter, $f_filter) = ('','');
235 my ($t, $f) = split '-', $args{format};
236 @types = split '', $t;
237 @forms = split '', $f;
239 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
243 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
253 JOIN $cn_table cn ON (cn.record = r.source)
254 JOIN $rd_table rd ON (cn.record = rd.record)
255 JOIN $cp_table cp ON (cn.id = cp.call_number)
256 JOIN $cs_table cs ON (cp.status = cs.id)
257 JOIN $cl_table cl ON (cp.location = cl.id)
258 JOIN $descendants a ON (cp.circ_lib = a.id)
259 WHERE r.metarecord = ?
268 JOIN $cn_table cn ON (cn.record = r.source)
269 JOIN $rd_table rd ON (cn.record = rd.record)
270 JOIN $cp_table cp ON (cn.id = cp.call_number)
271 JOIN $cs_table cs ON (cp.status = cs.id)
272 JOIN $cl_table cl ON (cp.location = cl.id)
273 JOIN $descendants a ON (cp.circ_lib = a.id)
274 WHERE r.metarecord = ?
283 JOIN $out_table t ON (u.ou_type = t.id)
287 my $sth = metabib::metarecord_source_map->db_Main->prepare_cached($sql);
288 $sth->execute( ''.$args{metarecord},
291 ''.$args{metarecord},
297 while ( my $row = $sth->fetchrow_hashref ) {
298 $client->respond( $row );
302 __PACKAGE__->register_method(
303 api_name => 'open-ils.storage.metabib.metarecord.copy_count',
304 method => 'metarecord_copy_count',
309 __PACKAGE__->register_method(
310 api_name => 'open-ils.storage.metabib.metarecord.copy_count.staff',
311 method => 'metarecord_copy_count',
317 sub biblio_multi_search_full_rec {
322 my $class_join = $args{class_join} || 'AND';
323 my $limit = $args{limit} || 100;
324 my $offset = $args{offset} || 0;
325 my $sort = $args{'sort'};
326 my $sort_dir = $args{sort_dir} || 'DESC';
331 for my $arg (@{ $args{searches} }) {
332 my $term = $$arg{term};
333 my $limiters = $$arg{restrict};
335 my ($index_col) = metabib::full_rec->columns('FTS');
336 $index_col ||= 'value';
337 my $search_table = metabib::full_rec->table;
339 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
341 my $fts_where = $fts->sql_where_clause();
342 my @fts_ranks = $fts->fts_rank;
344 my $rank = join(' + ', @fts_ranks);
347 for my $limit (@$limiters) {
348 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
349 push @binds, $$limit{tag}, $$limit{subfield};
350 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
352 my $where = join(' OR ', @wheres);
354 push @selects, "SELECT id, record, $rank as sum FROM $search_table WHERE $where";
358 my $descendants = defined($args{depth}) ?
359 "actor.org_unit_descendants($args{org_unit}, $args{depth})" :
360 "actor.org_unit_descendants($args{org_unit})" ;
363 my $metabib_record_descriptor = metabib::record_descriptor->table;
364 my $metabib_full_rec = metabib::full_rec->table;
365 my $asset_call_number_table = asset::call_number->table;
366 my $asset_copy_table = asset::copy->table;
367 my $cs_table = config::copy_status->table;
368 my $cl_table = asset::copy_location->table;
369 my $br_table = biblio::record_entry->table;
371 my $cj = 'HAVING COUNT(x.id) = ' . scalar(@selects) if ($class_join eq 'AND');
373 '(SELECT x.record, sum(x.sum) FROM (('.
374 join(') UNION ALL (', @selects).
375 ")) x GROUP BY 1 $cj ORDER BY 2 DESC )";
377 my $has_vols = 'AND cn.owning_lib = d.id';
378 my $has_copies = 'AND cp.call_number = cn.id';
379 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
381 if ($self->api_name =~ /staff/o) {
382 $copies_visible = '';
383 $has_copies = '' if ($ou_type == 0);
384 $has_vols = '' if ($ou_type == 0);
387 my ($t_filter, $f_filter) = ('','');
388 my ($a_filter, $l_filter, $lf_filter) = ('','','');
390 if (my $a = $args{audience}) {
391 $a = [$a] if (!ref($a));
394 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
398 if (my $l = $args{language}) {
399 $l = [$l] if (!ref($l));
402 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
406 if (my $f = $args{lit_form}) {
407 $f = [$f] if (!ref($f));
410 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
411 push @binds, @lit_form;
414 if (my $f = $args{item_form}) {
415 $f = [$f] if (!ref($f));
418 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
422 if (my $t = $args{item_type}) {
423 $t = [$t] if (!ref($t));
426 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
432 my ($t, $f) = split '-', $args{format};
433 my @types = split '', $t;
434 my @forms = split '', $f;
436 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
440 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
442 push @binds, @types, @forms;
445 my $relevance = 'sum(f.sum)';
446 $relevance = 1 if (!$copies_visible);
448 my $rank = $relevance;
449 if (lc($sort) eq 'pubdate') {
452 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
453 FROM $metabib_full_rec frp
454 WHERE frp.record = f.record
456 AND frp.subfield = 'c'
460 } elsif (lc($sort) eq 'create_date') {
462 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = f.record)) )
464 } elsif (lc($sort) eq 'edit_date') {
466 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = f.record)) )
468 } elsif (lc($sort) eq 'title') {
471 SELECT COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz')
472 FROM $metabib_full_rec frt
473 WHERE frt.record = f.record
475 AND frt.subfield = 'a'
479 } elsif (lc($sort) eq 'author') {
482 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
483 FROM $metabib_full_rec fra
484 WHERE fra.record = f.record
485 AND fra.tag LIKE '1%'
486 AND fra.subfield = 'a'
487 ORDER BY fra.tag::text::int
496 if ($copies_visible) {
498 SELECT f.record, $relevance, count(DISTINCT cp.id), $rank
499 FROM $search_table f,
500 $asset_call_number_table cn,
501 $asset_copy_table cp,
505 $metabib_record_descriptor rd,
507 WHERE br.id = f.record
508 AND cn.record = f.record
509 AND rd.record = f.record
510 AND cp.status = cs.id
511 AND cp.location = cl.id
512 AND br.deleted IS FALSE
513 AND cn.deleted IS FALSE
514 AND cp.deleted IS FALSE
523 GROUP BY f.record HAVING count(DISTINCT cp.id) > 0
524 ORDER BY 4 $sort_dir,3 DESC
528 SELECT f.record, 1, 1, $rank
529 FROM $search_table f,
531 $metabib_record_descriptor rd
532 WHERE br.id = f.record
533 AND rd.record = f.record
534 AND br.deleted IS FALSE
546 $log->debug("Search SQL :: [$select]",DEBUG);
548 my $recs = metabib::full_rec->db_Main->selectall_arrayref("$select;", {}, @binds);
549 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
552 $max = 1 if (!@$recs);
554 $max = $$_[1] if ($$_[1] > $max);
558 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
559 next unless ($$rec[0]);
560 my ($rid,$rank,$junk,$skip) = @$rec;
561 $client->respond( [$rid, sprintf('%0.3f',$rank/$max), $count] );
565 __PACKAGE__->register_method(
566 api_name => 'open-ils.storage.biblio.full_rec.multi_search',
567 method => 'biblio_multi_search_full_rec',
572 __PACKAGE__->register_method(
573 api_name => 'open-ils.storage.biblio.full_rec.multi_search.staff',
574 method => 'biblio_multi_search_full_rec',
580 sub search_full_rec {
586 my $term = $args{term};
587 my $limiters = $args{restrict};
589 my ($index_col) = metabib::full_rec->columns('FTS');
590 $index_col ||= 'value';
591 my $search_table = metabib::full_rec->table;
593 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
595 my $fts_where = $fts->sql_where_clause();
596 my @fts_ranks = $fts->fts_rank;
598 my $rank = join(' + ', @fts_ranks);
602 for my $limit (@$limiters) {
603 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
604 push @binds, $$limit{tag}, $$limit{subfield};
605 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
607 my $where = join(' OR ', @wheres);
609 my $select = "SELECT record, sum($rank) FROM $search_table WHERE $where GROUP BY 1 ORDER BY 2 DESC;";
611 $log->debug("Search SQL :: [$select]",DEBUG);
613 my $recs = metabib::full_rec->db_Main->selectall_arrayref($select, {}, @binds);
614 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
616 $client->respond($_) for (@$recs);
619 __PACKAGE__->register_method(
620 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.value',
621 method => 'search_full_rec',
626 __PACKAGE__->register_method(
627 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.index_vector',
628 method => 'search_full_rec',
635 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
636 sub search_class_fts {
641 my $term = $args{term};
642 my $ou = $args{org_unit};
643 my $ou_type = $args{depth};
644 my $limit = $args{limit};
645 my $offset = $args{offset};
647 my $limit_clause = '';
648 my $offset_clause = '';
650 $limit_clause = "LIMIT $limit" if (defined $limit and int($limit) > 0);
651 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
654 my ($t_filter, $f_filter) = ('','');
657 my ($t, $f) = split '-', $args{format};
658 @types = split '', $t;
659 @forms = split '', $f;
661 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
665 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
671 my $descendants = defined($ou_type) ?
672 "actor.org_unit_descendants($ou, $ou_type)" :
673 "actor.org_unit_descendants($ou)";
675 my $class = $self->{cdbi};
676 my $search_table = $class->table;
678 my $metabib_record_descriptor = metabib::record_descriptor->table;
679 my $metabib_metarecord = metabib::metarecord->table;
680 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
681 my $asset_call_number_table = asset::call_number->table;
682 my $asset_copy_table = asset::copy->table;
683 my $cs_table = config::copy_status->table;
684 my $cl_table = asset::copy_location->table;
686 my ($index_col) = $class->columns('FTS');
687 $index_col ||= 'value';
689 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
691 my $fts_where = $fts->sql_where_clause;
692 my @fts_ranks = $fts->fts_rank;
694 my $rank = join(' + ', @fts_ranks);
696 my $has_vols = 'AND cn.owning_lib = d.id';
697 my $has_copies = 'AND cp.call_number = cn.id';
698 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
700 my $visible_count = ', count(DISTINCT cp.id)';
701 my $visible_count_test = 'HAVING count(DISTINCT cp.id) > 0';
703 if ($self->api_name =~ /staff/o) {
704 $copies_visible = '';
705 $visible_count_test = '';
706 $has_copies = '' if ($ou_type == 0);
707 $has_vols = '' if ($ou_type == 0);
710 my $rank_calc = <<" RANK";
712 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
713 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
714 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
715 )/COUNT(m.source)), MIN(COALESCE(CHAR_LENGTH(f.value),1))
718 $rank_calc = ',1 , 1' if ($self->api_name =~ /unordered/o);
720 if ($copies_visible) {
722 SELECT m.metarecord $rank_calc $visible_count, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
723 FROM $search_table f,
724 $metabib_metarecord_source_map_table m,
725 $asset_call_number_table cn,
726 $asset_copy_table cp,
729 $metabib_record_descriptor rd,
732 AND m.source = f.source
733 AND cn.record = m.source
734 AND rd.record = m.source
735 AND cp.status = cs.id
736 AND cp.location = cl.id
742 GROUP BY 1 $visible_count_test
744 $limit_clause $offset_clause
748 SELECT m.metarecord $rank_calc, 0, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
749 FROM $search_table f,
750 $metabib_metarecord_source_map_table m,
751 $metabib_record_descriptor rd
753 AND m.source = f.source
754 AND rd.record = m.source
759 $limit_clause $offset_clause
763 $log->debug("Field Search SQL :: [$select]",DEBUG);
765 my $SQLstring = join('%',$fts->words);
766 my $REstring = join('\\s+',$fts->words);
767 my $first_word = ($fts->words)[0].'%';
768 my $recs = ($self->api_name =~ /unordered/o) ?
769 $class->db_Main->selectall_arrayref($select, {}, @types, @forms) :
770 $class->db_Main->selectall_arrayref($select, {},
771 '%'.lc($SQLstring).'%', # phrase order match
772 lc($first_word), # first word match
773 '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match
777 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
779 $client->respond($_) for (map { [@$_[0,1,3,4]] } @$recs);
783 for my $class ( qw/title author subject keyword series/ ) {
784 __PACKAGE__->register_method(
785 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord",
786 method => 'search_class_fts',
789 cdbi => "metabib::${class}_field_entry",
792 __PACKAGE__->register_method(
793 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.unordered",
794 method => 'search_class_fts',
797 cdbi => "metabib::${class}_field_entry",
800 __PACKAGE__->register_method(
801 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff",
802 method => 'search_class_fts',
805 cdbi => "metabib::${class}_field_entry",
808 __PACKAGE__->register_method(
809 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff.unordered",
810 method => 'search_class_fts',
813 cdbi => "metabib::${class}_field_entry",
818 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
819 sub search_class_fts_count {
824 my $term = $args{term};
825 my $ou = $args{org_unit};
826 my $ou_type = $args{depth};
827 my $limit = $args{limit} || 100;
828 my $offset = $args{offset} || 0;
830 my $descendants = defined($ou_type) ?
831 "actor.org_unit_descendants($ou, $ou_type)" :
832 "actor.org_unit_descendants($ou)";
835 my ($t_filter, $f_filter) = ('','');
838 my ($t, $f) = split '-', $args{format};
839 @types = split '', $t;
840 @forms = split '', $f;
842 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
846 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
851 (my $search_class = $self->api_name) =~ s/.*metabib.(\w+).search_fts.*/$1/o;
853 my $class = $self->{cdbi};
854 my $search_table = $class->table;
856 my $metabib_record_descriptor = metabib::record_descriptor->table;
857 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
858 my $asset_call_number_table = asset::call_number->table;
859 my $asset_copy_table = asset::copy->table;
860 my $cs_table = config::copy_status->table;
861 my $cl_table = asset::copy_location->table;
863 my ($index_col) = $class->columns('FTS');
864 $index_col ||= 'value';
866 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
868 my $fts_where = $fts->sql_where_clause;
870 my $has_vols = 'AND cn.owning_lib = d.id';
871 my $has_copies = 'AND cp.call_number = cn.id';
872 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
873 if ($self->api_name =~ /staff/o) {
874 $copies_visible = '';
875 $has_vols = '' if ($ou_type == 0);
876 $has_copies = '' if ($ou_type == 0);
879 # XXX test an "EXISTS version of descendant checking...
881 if ($copies_visible) {
883 SELECT count(distinct m.metarecord)
884 FROM $search_table f,
885 $metabib_metarecord_source_map_table m,
886 $metabib_metarecord_source_map_table mr,
887 $asset_call_number_table cn,
888 $asset_copy_table cp,
891 $metabib_record_descriptor rd,
894 AND mr.source = f.source
895 AND mr.metarecord = m.metarecord
896 AND cn.record = m.source
897 AND rd.record = m.source
898 AND cp.status = cs.id
899 AND cp.location = cl.id
908 SELECT count(distinct m.metarecord)
909 FROM $search_table f,
910 $metabib_metarecord_source_map_table m,
911 $metabib_metarecord_source_map_table mr,
912 $metabib_record_descriptor rd
914 AND mr.source = f.source
915 AND mr.metarecord = m.metarecord
916 AND rd.record = m.source
922 $log->debug("Field Search Count SQL :: [$select]",DEBUG);
924 my $recs = $class->db_Main->selectrow_arrayref($select, {}, @types, @forms)->[0];
926 $log->debug("Count Search yielded $recs results.",DEBUG);
931 for my $class ( qw/title author subject keyword series/ ) {
932 __PACKAGE__->register_method(
933 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count",
934 method => 'search_class_fts_count',
937 cdbi => "metabib::${class}_field_entry",
940 __PACKAGE__->register_method(
941 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count.staff",
942 method => 'search_class_fts_count',
945 cdbi => "metabib::${class}_field_entry",
951 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
952 sub postfilter_search_class_fts {
957 my $term = $args{term};
958 my $sort = $args{'sort'};
959 my $sort_dir = $args{sort_dir} || 'DESC';
960 my $ou = $args{org_unit};
961 my $ou_type = $args{depth};
962 my $limit = $args{limit} || 10;
963 my $offset = $args{offset} || 0;
965 my $outer_limit = 1000;
967 my $limit_clause = '';
968 my $offset_clause = '';
970 $limit_clause = "LIMIT $outer_limit";
971 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
973 my (@types,@forms,@lang,@aud,@lit_form);
974 my ($t_filter, $f_filter) = ('','');
975 my ($a_filter, $l_filter, $lf_filter) = ('','','');
976 my ($ot_filter, $of_filter) = ('','');
977 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
979 if (my $a = $args{audience}) {
980 $a = [$a] if (!ref($a));
983 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
984 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
987 if (my $l = $args{language}) {
988 $l = [$l] if (!ref($l));
991 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
992 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
995 if (my $f = $args{lit_form}) {
996 $f = [$f] if (!ref($f));
999 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1000 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1003 if ($args{format}) {
1004 my ($t, $f) = split '-', $args{format};
1005 @types = split '', $t;
1006 @forms = split '', $f;
1008 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1009 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1013 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1014 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1019 my $descendants = defined($ou_type) ?
1020 "actor.org_unit_descendants($ou, $ou_type)" :
1021 "actor.org_unit_descendants($ou)";
1023 my $class = $self->{cdbi};
1024 my $search_table = $class->table;
1026 my $metabib_full_rec = metabib::full_rec->table;
1027 my $metabib_record_descriptor = metabib::record_descriptor->table;
1028 my $metabib_metarecord = metabib::metarecord->table;
1029 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1030 my $asset_call_number_table = asset::call_number->table;
1031 my $asset_copy_table = asset::copy->table;
1032 my $cs_table = config::copy_status->table;
1033 my $cl_table = asset::copy_location->table;
1034 my $br_table = biblio::record_entry->table;
1036 my ($index_col) = $class->columns('FTS');
1037 $index_col ||= 'value';
1039 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
1041 my $SQLstring = join('%',map { lc($_) } $fts->words);
1042 my $REstring = '^' . join('\s+',map { lc($_) } $fts->words) . '\W*$';
1043 my $first_word = lc(($fts->words)[0]).'%';
1045 my $fts_where = $fts->sql_where_clause;
1046 my @fts_ranks = $fts->fts_rank;
1049 $bonus{'metabib::keyword_field_entry'} = [ { 'CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END' => $SQLstring } ];
1050 $bonus{'metabib::title_field_entry'} =
1051 $bonus{'metabib::series_field_entry'} = [
1052 { 'CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END' => $first_word },
1053 { 'CASE WHEN f.value ~* ? THEN 2 ELSE 1 END' => $REstring },
1054 @{ $bonus{'metabib::keyword_field_entry'} }
1057 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$class} };
1058 $bonus_list ||= '1';
1060 my @bonus_values = map { values %$_ } @{ $bonus{$class} };
1062 my $relevance = join(' + ', @fts_ranks);
1063 $relevance = <<" RANK";
1064 (SUM( ( $relevance ) * ( $bonus_list ) )/COUNT(m.source))
1067 my $rank = $relevance;
1068 if (lc($sort) eq 'pubdate') {
1071 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
1072 FROM $metabib_full_rec frp
1073 WHERE frp.record = mr.master_record
1075 AND frp.subfield = 'c'
1079 } elsif (lc($sort) eq 'create_date') {
1081 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1083 } elsif (lc($sort) eq 'edit_date') {
1085 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1087 } elsif (lc($sort) eq 'title') {
1090 SELECT COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz')
1091 FROM $metabib_full_rec frt
1092 WHERE frt.record = mr.master_record
1094 AND frt.subfield = 'a'
1098 } elsif (lc($sort) eq 'author') {
1101 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1102 FROM $metabib_full_rec fra
1103 WHERE fra.record = mr.master_record
1104 AND fra.tag LIKE '1%'
1105 AND fra.subfield = 'a'
1106 ORDER BY fra.tag::text::int
1114 my $select = <<" SQL";
1115 SELECT m.metarecord,
1117 CASE WHEN COUNT(DISTINCT smrs.source) = 1 THEN MIN(m.source) ELSE 0 END,
1119 FROM $search_table f,
1120 $metabib_metarecord_source_map_table m,
1121 $metabib_metarecord_source_map_table smrs,
1122 $metabib_metarecord mr,
1123 $metabib_record_descriptor rd
1125 AND smrs.metarecord = mr.id
1126 AND m.source = f.source
1127 AND m.metarecord = mr.id
1128 AND rd.record = smrs.source
1134 GROUP BY m.metarecord
1135 ORDER BY 4 $sort_dir, MIN(COALESCE(CHAR_LENGTH(f.value),1))
1143 FROM $asset_call_number_table cn,
1144 $metabib_metarecord_source_map_table mrs,
1145 $asset_copy_table cp,
1150 $metabib_record_descriptor ord,
1152 WHERE mrs.metarecord = s.metarecord
1153 AND br.id = mrs.source
1154 AND cn.record = mrs.source
1155 AND cp.status = cs.id
1156 AND cp.location = cl.id
1157 AND cn.owning_lib = d.id
1158 AND cp.call_number = cn.id
1159 AND cp.opac_visible IS TRUE
1160 AND cs.holdable IS TRUE
1161 AND cl.opac_visible IS TRUE
1162 AND br.active IS TRUE
1163 AND br.deleted IS FALSE
1164 AND ord.record = mrs.source
1170 ORDER BY 4 $sort_dir
1172 } elsif ($self->api_name !~ /staff/o) {
1179 FROM $asset_call_number_table cn,
1180 $metabib_metarecord_source_map_table mrs,
1181 $asset_copy_table cp,
1186 $metabib_record_descriptor ord
1188 WHERE mrs.metarecord = s.metarecord
1189 AND br.id = mrs.source
1190 AND cn.record = mrs.source
1191 AND cp.status = cs.id
1192 AND cp.location = cl.id
1193 AND cn.owning_lib = d.id
1194 AND cp.call_number = cn.id
1195 AND cp.opac_visible IS TRUE
1196 AND cs.holdable IS TRUE
1197 AND cl.opac_visible IS TRUE
1198 AND br.active IS TRUE
1199 AND br.deleted IS FALSE
1200 AND ord.record = mrs.source
1208 ORDER BY 4 $sort_dir
1217 FROM $asset_call_number_table cn,
1218 $metabib_metarecord_source_map_table mrs,
1221 $metabib_record_descriptor ord
1223 WHERE mrs.metarecord = s.metarecord
1224 AND br.id = mrs.source
1225 AND cn.record = mrs.source
1226 AND cn.owning_lib = d.id
1227 AND br.deleted IS FALSE
1228 AND ord.record = mrs.source
1238 FROM $asset_call_number_table cn,
1239 $metabib_metarecord_source_map_table mrs,
1240 $metabib_record_descriptor ord
1241 WHERE mrs.metarecord = s.metarecord
1242 AND cn.record = mrs.source
1243 AND ord.record = mrs.source
1251 ORDER BY 4 $sort_dir
1256 $log->debug("Field Search SQL :: [$select]",DEBUG);
1258 my $recs = $class->db_Main->selectall_arrayref(
1260 (@bonus_values > 0 ? @bonus_values : () ),
1261 ( (!$sort && @bonus_values > 0) ? @bonus_values : () ),
1262 @types, @forms, @aud, @lang, @lit_form,
1263 @types, @forms, @aud, @lang, @lit_form,
1264 ($self->api_name =~ /staff/o ? (@types, @forms, @aud, @lang, @lit_form) : () ) );
1266 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1269 $max = 1 if (!@$recs);
1271 $max = $$_[1] if ($$_[1] > $max);
1274 my $count = scalar(@$recs);
1275 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1276 my ($mrid,$rank,$skip) = @$rec;
1277 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $skip, $count] );
1282 for my $class ( qw/title author subject keyword series/ ) {
1283 __PACKAGE__->register_method(
1284 api_name => "open-ils.storage.metabib.$class.post_filter.search_fts.metarecord",
1285 method => 'postfilter_search_class_fts',
1288 cdbi => "metabib::${class}_field_entry",
1291 __PACKAGE__->register_method(
1292 api_name => "open-ils.storage.metabib.$class.post_filter.search_fts.metarecord.staff",
1293 method => 'postfilter_search_class_fts',
1296 cdbi => "metabib::${class}_field_entry",
1303 my $_cdbi = { title => "metabib::title_field_entry",
1304 author => "metabib::author_field_entry",
1305 subject => "metabib::subject_field_entry",
1306 keyword => "metabib::keyword_field_entry",
1307 series => "metabib::series_field_entry",
1310 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
1311 sub postfilter_search_multi_class_fts {
1316 my $sort = $args{'sort'};
1317 my $sort_dir = $args{sort_dir} || 'DESC';
1318 my $ou = $args{org_unit};
1319 my $ou_type = $args{depth};
1320 my $limit = $args{limit} || 10;;
1321 my $offset = $args{offset} || 0;
1324 $ou = actor::org_unit->search( { parent_ou => undef } )->next->id;
1327 if (!defined($args{org_unit})) {
1328 die "No target organizational unit passed to ".$self->api_name;
1331 if (! scalar( keys %{$args{searches}} )) {
1332 die "No search arguments were passed to ".$self->api_name;
1335 my $outer_limit = 1000;
1337 my $limit_clause = '';
1338 my $offset_clause = '';
1340 $limit_clause = "LIMIT $outer_limit";
1341 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
1343 my (@types,@forms,@lang,@aud,@lit_form);
1344 my ($t_filter, $f_filter) = ('','');
1345 my ($a_filter, $l_filter, $lf_filter) = ('','','');
1346 my ($ot_filter, $of_filter) = ('','');
1347 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
1349 if (my $a = $args{audience}) {
1350 $a = [$a] if (!ref($a));
1353 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
1354 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
1357 if (my $l = $args{language}) {
1358 $l = [$l] if (!ref($l));
1361 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1362 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1365 if (my $f = $args{lit_form}) {
1366 $f = [$f] if (!ref($f));
1369 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1370 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1373 if (my $f = $args{item_form}) {
1374 $f = [$f] if (!ref($f));
1377 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1378 $of_filter = ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1381 if (my $t = $args{item_type}) {
1382 $t = [$t] if (!ref($t));
1385 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1386 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1390 # XXX legacy format and item type support
1391 if ($args{format}) {
1392 my ($t, $f) = split '-', $args{format};
1393 @types = split '', $t;
1394 @forms = split '', $f;
1396 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1397 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1401 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1402 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1408 my $descendants = defined($ou_type) ?
1409 "actor.org_unit_descendants($ou, $ou_type)" :
1410 "actor.org_unit_descendants($ou)";
1412 my $search_table_list = '';
1414 my $join_table_list = '';
1419 my $prev_search_class;
1420 my $curr_search_class;
1421 for my $search_class (sort keys %{$args{searches}}) {
1422 $prev_search_class = $curr_search_class if ($curr_search_class);
1424 $curr_search_class = $search_class;
1426 my $class = $_cdbi->{$search_class};
1427 my $search_table = $class->table;
1429 my ($index_col) = $class->columns('FTS');
1430 $index_col ||= 'value';
1433 my $fts = OpenILS::Application::Storage::FTS->compile($args{searches}{$search_class}{term}, $search_class.'.value', "$search_class.$index_col");
1435 my $fts_where = $fts->sql_where_clause;
1436 my @fts_ranks = $fts->fts_rank;
1438 my $SQLstring = join('%',map { lc($_) } $fts->words);
1439 my $REstring = '^' . join('\s+',map { lc($_) } $fts->words) . '\W*$';
1440 my $first_word = lc(($fts->words)[0]).'%';
1442 my $rank = join(' + ', @fts_ranks);
1445 $bonus{'keyword'} = [ { "CASE WHEN $search_class.value LIKE ? THEN 1.2 ELSE 1 END" => $SQLstring } ];
1447 $bonus{'series'} = [
1448 { "CASE WHEN $search_class.value LIKE ? THEN 1.5 ELSE 1 END" => $first_word },
1449 { "CASE WHEN $search_class.value ~ ? THEN 1000 ELSE 1 END" => $REstring },
1452 $bonus{'title'} = [ @{ $bonus{'series'} }, @{ $bonus{'keyword'} } ];
1454 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$search_class} };
1455 $bonus_list ||= '1';
1457 push @bonus_lists, $bonus_list;
1458 push @bonus_values, map { values %$_ } @{ $bonus{$search_class} };
1461 #---------------------
1463 $search_table_list .= "$search_table $search_class, ";
1464 push @rank_list,$rank;
1465 $fts_list .= " AND $fts_where AND m.source = $search_class.source";
1467 if ($prev_search_class) {
1468 $join_table_list .= " AND $prev_search_class.source = $curr_search_class.source";
1472 my $metabib_record_descriptor = metabib::record_descriptor->table;
1473 my $metabib_full_rec = metabib::full_rec->table;
1474 my $metabib_metarecord = metabib::metarecord->table;
1475 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1476 my $asset_call_number_table = asset::call_number->table;
1477 my $asset_copy_table = asset::copy->table;
1478 my $cs_table = config::copy_status->table;
1479 my $cl_table = asset::copy_location->table;
1480 my $br_table = biblio::record_entry->table;
1482 my $bonuses = join (' * ', @bonus_lists);
1483 my $relevance = join (' + ', @rank_list);
1484 $relevance = "SUM( ($relevance) * ($bonuses) )/COUNT(DISTINCT m.source)";
1487 my $secondary_sort = <<" SORT";
1489 SELECT COALESCE(LTRIM(SUBSTR( sfrt.value, sfrt.ind2::text::int )),'zzzzzzzz')
1490 FROM $metabib_full_rec sfrt,
1491 $metabib_metarecord mr
1492 WHERE sfrt.record = mr.master_record
1493 AND sfrt.tag = '245'
1494 AND sfrt.subfield = 'a'
1499 my $rank = $relevance;
1500 if (lc($sort) eq 'pubdate') {
1503 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
1504 FROM $metabib_full_rec frp
1505 WHERE frp.record = mr.master_record
1507 AND frp.subfield = 'c'
1511 } elsif (lc($sort) eq 'create_date') {
1513 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1515 } elsif (lc($sort) eq 'edit_date') {
1517 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1519 } elsif (lc($sort) eq 'title') {
1522 SELECT COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz')
1523 FROM $metabib_full_rec frt
1524 WHERE frt.record = mr.master_record
1526 AND frt.subfield = 'a'
1530 $secondary_sort = <<" SORT";
1532 SELECT COALESCE(SUBSTRING(sfrp.value FROM '\\\\d+'),'9999')::INT
1533 FROM $metabib_full_rec sfrp,
1534 $metabib_metarecord mr
1535 WHERE sfrp.record = mr.master_record
1536 AND sfrp.tag = '260'
1537 AND sfrp.subfield = 'c'
1541 } elsif (lc($sort) eq 'author') {
1544 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1545 FROM $metabib_full_rec fra
1546 WHERE fra.record = mr.master_record
1547 AND fra.tag LIKE '1%'
1548 AND fra.subfield = 'a'
1549 ORDER BY fra.tag::text::int
1554 push @bonus_values, @bonus_values;
1559 my $select = <<" SQL";
1560 SELECT m.metarecord,
1562 CASE WHEN COUNT(DISTINCT smrs.source) = 1 THEN FIRST(m.source) ELSE 0 END,
1565 FROM $search_table_list
1566 $metabib_metarecord_source_map_table m,
1567 $metabib_metarecord_source_map_table smrs
1568 WHERE m.metarecord = smrs.metarecord
1571 GROUP BY m.metarecord
1572 -- ORDER BY 4 $sort_dir
1576 if ($self->api_name !~ /staff/o) {
1583 FROM $asset_call_number_table cn,
1584 $metabib_metarecord_source_map_table mrs,
1585 $asset_copy_table cp,
1590 $metabib_record_descriptor ord
1591 WHERE mrs.metarecord = s.metarecord
1592 AND br.id = mrs.source
1593 AND cn.record = mrs.source
1594 AND cp.status = cs.id
1595 AND cp.location = cl.id
1596 AND cn.owning_lib = d.id
1597 AND cp.call_number = cn.id
1598 AND cp.opac_visible IS TRUE
1599 AND cs.holdable IS TRUE
1600 AND cl.opac_visible IS TRUE
1601 AND br.active IS TRUE
1602 AND br.deleted IS FALSE
1603 AND ord.record = mrs.source
1611 ORDER BY 4 $sort_dir, 5
1620 FROM $asset_call_number_table cn,
1621 $metabib_metarecord_source_map_table mrs,
1624 $metabib_record_descriptor ord
1625 WHERE mrs.metarecord = s.metarecord
1626 AND br.id = mrs.source
1627 AND cn.record = mrs.source
1628 AND cn.owning_lib = d.id
1629 AND ord.record = mrs.source
1630 AND br.deleted IS FALSE
1640 FROM $asset_call_number_table cn,
1641 $metabib_metarecord_source_map_table mrs,
1642 $metabib_record_descriptor ord
1643 WHERE mrs.metarecord = s.metarecord
1644 AND cn.record = mrs.source
1645 AND ord.record = mrs.source
1653 ORDER BY 4 $sort_dir, 5
1658 $log->debug("Field Search SQL :: [$select]",DEBUG);
1660 my $recs = $_cdbi->{title}->db_Main->selectall_arrayref(
1663 @types, @forms, @aud, @lang, @lit_form,
1664 # @types, @forms, @aud, @lang, @lit_form,
1665 ($self->api_name =~ /staff/o ? (@types, @forms, @aud, @lang, @lit_form) : () )
1668 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1671 $max = 1 if (!@$recs);
1673 $max = $$_[1] if ($$_[1] > $max);
1676 my $count = scalar(@$recs);
1677 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1678 next unless ($$rec[0]);
1679 my ($mrid,$rank,$skip) = @$rec;
1680 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $skip, $count] );
1685 __PACKAGE__->register_method(
1686 api_name => "open-ils.storage.metabib.post_filter.multiclass.search_fts.metarecord",
1687 method => 'postfilter_search_multi_class_fts',
1692 __PACKAGE__->register_method(
1693 api_name => "open-ils.storage.metabib.post_filter.multiclass.search_fts.metarecord.staff",
1694 method => 'postfilter_search_multi_class_fts',
1700 __PACKAGE__->register_method(
1701 api_name => "open-ils.storage.metabib.multiclass.search_fts",
1702 method => 'postfilter_search_multi_class_fts',
1707 __PACKAGE__->register_method(
1708 api_name => "open-ils.storage.metabib.multiclass.search_fts.staff",
1709 method => 'postfilter_search_multi_class_fts',
1715 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
1716 sub biblio_search_multi_class_fts {
1721 my $sort = $args{'sort'};
1722 my $sort_dir = $args{sort_dir} || 'DESC';
1723 my $ou = $args{org_unit};
1724 my $ou_type = $args{depth};
1725 my $limit = $args{limit} || 10;
1726 my $offset = $args{offset} || 0;
1729 $ou = actor::org_unit->search( { parent_ou => undef } )->next->id;
1732 if (!defined($args{org_unit})) {
1733 die "No target organizational unit passed to ".$self->api_name;
1736 if (! scalar( keys %{$args{searches}} )) {
1737 die "No search arguments were passed to ".$self->api_name;
1740 my $outer_limit = 1000;
1742 my $limit_clause = '';
1743 my $offset_clause = '';
1745 $limit_clause = "LIMIT $outer_limit";
1746 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
1748 my (@types,@forms,@lang,@aud,@lit_form);
1749 my ($t_filter, $f_filter) = ('','');
1750 my ($a_filter, $l_filter, $lf_filter) = ('','','');
1751 my ($ot_filter, $of_filter) = ('','');
1752 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
1754 if (my $a = $args{audience}) {
1755 $a = [$a] if (!ref($a));
1758 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
1759 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
1762 if (my $l = $args{language}) {
1763 $l = [$l] if (!ref($l));
1766 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1767 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1770 if (my $f = $args{lit_form}) {
1771 $f = [$f] if (!ref($f));
1774 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1775 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1778 if (my $f = $args{item_form}) {
1779 $f = [$f] if (!ref($f));
1782 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1783 $of_filter = ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1786 if (my $t = $args{item_type}) {
1787 $t = [$t] if (!ref($t));
1790 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1791 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1795 # XXX legacy format and item type support
1796 if ($args{format}) {
1797 my ($t, $f) = split '-', $args{format};
1798 @types = split '', $t;
1799 @forms = split '', $f;
1801 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1802 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1806 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1807 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1812 my $descendants = defined($ou_type) ?
1813 "actor.org_unit_descendants($ou, $ou_type)" :
1814 "actor.org_unit_descendants($ou)";
1816 my $search_table_list = '';
1818 my $join_table_list = '';
1824 my $prev_search_class;
1825 my $curr_search_class;
1826 for my $search_class (sort keys %{$args{searches}}) {
1827 $prev_search_class = $curr_search_class if ($curr_search_class);
1829 $curr_search_class = $search_class;
1831 my $class = $_cdbi->{$search_class};
1832 my $search_table = $class->table;
1834 my ($index_col) = $class->columns('FTS');
1835 $index_col ||= 'value';
1838 my $fts = OpenILS::Application::Storage::FTS->compile($args{searches}{$search_class}{term}, $search_class.'.value', "$search_class.$index_col");
1840 my $fts_where = $fts->sql_where_clause;
1841 my @fts_ranks = $fts->fts_rank;
1843 my $SQLstring = join('%',map { lc($_) } $fts->words);
1844 my $REstring = '^' . join('\s+',map { lc($_) } $fts->words) . '\W*$';
1845 my $first_word = lc(($fts->words)[0]).'%';
1847 my $rank = join(' + ', @fts_ranks);
1850 $bonus{'keyword'} = [ { "CASE WHEN $search_class.value ILIKE ? THEN 1.2 ELSE 1 END" => $SQLstring } ];
1852 $bonus{'series'} = [
1853 { "CASE WHEN $search_class.value ILIKE ? THEN 1.5 ELSE 1 END" => $first_word },
1854 { "CASE WHEN $search_class.value ~ ? THEN 200 ELSE 1 END" => $REstring },
1857 $bonus{'title'} = [ @{ $bonus{'series'} }, @{ $bonus{'keyword'} } ];
1859 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$search_class} };
1860 $bonus_list ||= '1';
1862 push @bonus_lists, $bonus_list;
1863 push @bonus_values, map { values %$_ } @{ $bonus{$search_class} };
1865 #---------------------
1867 $search_table_list .= "$search_table $search_class, ";
1868 push @rank_list,$rank;
1869 $fts_list .= " AND $fts_where AND b.id = $search_class.source";
1871 if ($prev_search_class) {
1872 $join_table_list .= " AND $prev_search_class.source = $curr_search_class.source";
1876 my $metabib_record_descriptor = metabib::record_descriptor->table;
1877 my $metabib_full_rec = metabib::full_rec->table;
1878 my $metabib_metarecord = metabib::metarecord->table;
1879 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1880 my $asset_call_number_table = asset::call_number->table;
1881 my $asset_copy_table = asset::copy->table;
1882 my $cs_table = config::copy_status->table;
1883 my $cl_table = asset::copy_location->table;
1884 my $br_table = biblio::record_entry->table;
1887 my $bonuses = join (' * ', @bonus_lists);
1888 my $relevance = join (' + ', @rank_list);
1889 $relevance = "AVG( ($relevance) * ($bonuses) )";
1892 my $rank = $relevance;
1893 if (lc($sort) eq 'pubdate') {
1896 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d{4}'),'9999')::INT
1897 FROM $metabib_full_rec frp
1898 WHERE frp.record = b.id
1900 AND frp.subfield = 'c'
1904 } elsif (lc($sort) eq 'create_date') {
1906 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = b.id)) )
1908 } elsif (lc($sort) eq 'edit_date') {
1910 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = b.id)) )
1912 } elsif (lc($sort) eq 'title') {
1915 SELECT COALESCE(LTRIM(SUBSTR( frt.value, frt.ind2::text::int )),'zzzzzzzz')
1916 FROM $metabib_full_rec frt
1917 WHERE frt.record = b.id
1919 AND frt.subfield = 'a'
1923 } elsif (lc($sort) eq 'author') {
1926 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1927 FROM $metabib_full_rec fra
1928 WHERE fra.record = b.id
1929 AND fra.tag LIKE '1%'
1930 AND fra.subfield = 'a'
1931 ORDER BY fra.tag::text::int
1936 push @bonus_values, @bonus_values;
1941 my $select = <<" SQL";
1945 FROM $search_table_list
1946 $metabib_record_descriptor rd,
1948 WHERE rd.record = b.id
1949 AND b.active IS TRUE
1950 AND b.deleted IS FALSE
1959 ORDER BY 3 $sort_dir
1963 if ($self->api_name !~ /staff/o) {
1970 FROM $asset_call_number_table cn,
1971 $asset_copy_table cp,
1975 WHERE cn.record = s.id
1976 AND cp.status = cs.id
1977 AND cp.location = cl.id
1978 AND cn.owning_lib = d.id
1979 AND cp.call_number = cn.id
1980 AND cp.opac_visible IS TRUE
1981 AND cs.holdable IS TRUE
1982 AND cl.opac_visible IS TRUE
1983 AND cp.deleted IS FALSE
1986 ORDER BY 3 $sort_dir
1995 FROM $asset_call_number_table cn,
1997 WHERE cn.record = s.id
1998 AND cn.owning_lib = d.id
2003 FROM $asset_call_number_table cn
2004 WHERE cn.record = s.id
2007 ORDER BY 3 $sort_dir
2012 $log->debug("Field Search SQL :: [$select]",DEBUG);
2014 my $recs = $_cdbi->{title}->db_Main->selectall_arrayref(
2016 @bonus_values, @types, @forms, @aud, @lang, @lit_form
2019 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
2022 $max = 1 if (!@$recs);
2024 $max = $$_[1] if ($$_[1] > $max);
2027 my $count = scalar(@$recs);
2028 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
2029 next unless ($$rec[0]);
2030 my ($mrid,$rank) = @$rec;
2031 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $count] );
2036 __PACKAGE__->register_method(
2037 api_name => "open-ils.storage.biblio.multiclass.search_fts.record",
2038 method => 'biblio_search_multi_class_fts',
2043 __PACKAGE__->register_method(
2044 api_name => "open-ils.storage.biblio.multiclass.search_fts.record.staff",
2045 method => 'biblio_search_multi_class_fts',
2053 __PACKAGE__->register_method(
2054 api_name => "open-ils.storage.biblio.multiclass.search_fts",
2055 method => 'biblio_search_multi_class_fts',
2060 __PACKAGE__->register_method(
2061 api_name => "open-ils.storage.biblio.multiclass.search_fts.staff",
2062 method => 'biblio_search_multi_class_fts',