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
99 WHERE rd.record = sm.source
100 AND cp.circ_lib = d.id
101 AND cn.id = cp.call_number
102 AND cn.record = sm.source
103 AND fr.record = sm.source
104 AND br.id = sm.source
105 AND sm.metarecord = ?
110 $sql .= ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
114 $sql .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
124 GROUP BY record, item_type, item_form, quality
127 WHEN item_type IS NULL -- default
129 WHEN item_type = '' -- default
131 WHEN item_type IN ('a','t') -- books
133 WHEN item_type = 'g' -- movies
135 WHEN item_type IN ('i','j') -- sound recordings
137 WHEN item_type = 'm' -- software
139 WHEN item_type = 'k' -- images
141 WHEN item_type IN ('e','f') -- maps
143 WHEN item_type IN ('o','p') -- mixed
145 WHEN item_type IN ('c','d') -- music
147 WHEN item_type = 'r' -- 3d
154 my $ids = metabib::metarecord_source_map->db_Main->selectcol_arrayref($sql, {}, "$mr", @types, @forms);
155 return $ids if ($self->api_name =~ /atomic$/o);
157 $client->respond( $_ ) for ( @$ids );
161 __PACKAGE__->register_method(
162 api_name => 'open-ils.storage.ordered.metabib.metarecord.records',
163 method => 'ordered_records_from_metarecord',
167 __PACKAGE__->register_method(
168 api_name => 'open-ils.storage.ordered.metabib.metarecord.records.staff',
169 method => 'ordered_records_from_metarecord',
174 __PACKAGE__->register_method(
175 api_name => 'open-ils.storage.ordered.metabib.metarecord.records.atomic',
176 method => 'ordered_records_from_metarecord',
180 __PACKAGE__->register_method(
181 api_name => 'open-ils.storage.ordered.metabib.metarecord.records.staff.atomic',
182 method => 'ordered_records_from_metarecord',
192 my $tag = ($self->api_name =~ /isbn/o) ? '020' : '022';
194 my $fr_table = metabib::full_rec->table;
203 my $list = metabib::metarecord_source_map->db_Main->selectcol_arrayref($sql, {}, $tag, "$isxn%");
204 $client->respond($_) for (@$list);
207 __PACKAGE__->register_method(
208 api_name => 'open-ils.storage.id_list.biblio.record_entry.search.isbn',
209 method => 'isxn_search',
213 __PACKAGE__->register_method(
214 api_name => 'open-ils.storage.id_list.biblio.record_entry.search.issn',
215 method => 'isxn_search',
220 sub metarecord_copy_count {
226 my $sm_table = metabib::metarecord_source_map->table;
227 my $rd_table = metabib::record_descriptor->table;
228 my $cn_table = asset::call_number->table;
229 my $cp_table = asset::copy->table;
230 my $cl_table = asset::copy_location->table;
231 my $cs_table = config::copy_status->table;
232 my $out_table = actor::org_unit_type->table;
233 my $descendants = "actor.org_unit_descendants(u.id)";
234 my $ancestors = "actor.org_unit_ancestors(?)";
236 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
237 $copies_visible = '' if ($self->api_name =~ /staff/o);
240 my ($t_filter, $f_filter) = ('','');
243 my ($t, $f) = split '-', $args{format};
244 @types = split '', $t;
245 @forms = split '', $f;
247 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
251 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
261 JOIN $cn_table cn ON (cn.record = r.source)
262 JOIN $rd_table rd ON (cn.record = rd.record)
263 JOIN $cp_table cp ON (cn.id = cp.call_number)
264 JOIN $cs_table cs ON (cp.status = cs.id)
265 JOIN $cl_table cl ON (cp.location = cl.id)
266 JOIN $descendants a ON (cp.circ_lib = a.id)
267 WHERE r.metarecord = ?
276 JOIN $cn_table cn ON (cn.record = r.source)
277 JOIN $rd_table rd ON (cn.record = rd.record)
278 JOIN $cp_table cp ON (cn.id = cp.call_number)
279 JOIN $cs_table cs ON (cp.status = cs.id)
280 JOIN $cl_table cl ON (cp.location = cl.id)
281 JOIN $descendants a ON (cp.circ_lib = a.id)
282 WHERE r.metarecord = ?
291 JOIN $out_table t ON (u.ou_type = t.id)
295 my $sth = metabib::metarecord_source_map->db_Main->prepare_cached($sql);
296 $sth->execute( ''.$args{metarecord},
299 ''.$args{metarecord},
305 while ( my $row = $sth->fetchrow_hashref ) {
306 $client->respond( $row );
310 __PACKAGE__->register_method(
311 api_name => 'open-ils.storage.metabib.metarecord.copy_count',
312 method => 'metarecord_copy_count',
317 __PACKAGE__->register_method(
318 api_name => 'open-ils.storage.metabib.metarecord.copy_count.staff',
319 method => 'metarecord_copy_count',
325 sub biblio_multi_search_full_rec {
330 my $class_join = $args{class_join} || 'AND';
331 my $limit = $args{limit} || 100;
332 my $offset = $args{offset} || 0;
333 my $sort = $args{'sort'};
334 my $sort_dir = $args{sort_dir} || 'DESC';
339 for my $arg (@{ $args{searches} }) {
340 my $term = $$arg{term};
341 my $limiters = $$arg{restrict};
343 my ($index_col) = metabib::full_rec->columns('FTS');
344 $index_col ||= 'value';
345 my $search_table = metabib::full_rec->table;
347 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
349 my $fts_where = $fts->sql_where_clause();
350 my @fts_ranks = $fts->fts_rank;
352 my $rank = join(' + ', @fts_ranks);
355 for my $limit (@$limiters) {
356 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
357 push @binds, $$limit{tag}, $$limit{subfield};
358 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
360 my $where = join(' OR ', @wheres);
362 push @selects, "SELECT id, record, $rank as sum FROM $search_table WHERE $where";
366 my $descendants = defined($args{depth}) ?
367 "actor.org_unit_descendants($args{org_unit}, $args{depth})" :
368 "actor.org_unit_descendants($args{org_unit})" ;
371 my $metabib_record_descriptor = metabib::record_descriptor->table;
372 my $metabib_full_rec = metabib::full_rec->table;
373 my $asset_call_number_table = asset::call_number->table;
374 my $asset_copy_table = asset::copy->table;
375 my $cs_table = config::copy_status->table;
376 my $cl_table = asset::copy_location->table;
377 my $br_table = biblio::record_entry->table;
379 my $cj = 'HAVING COUNT(x.id) = ' . scalar(@selects) if ($class_join eq 'AND');
381 '(SELECT x.record, sum(x.sum) FROM (('.
382 join(') UNION ALL (', @selects).
383 ")) x GROUP BY 1 $cj ORDER BY 2 DESC )";
385 my $has_vols = 'AND cn.owning_lib = d.id';
386 my $has_copies = 'AND cp.call_number = cn.id';
387 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
389 if ($self->api_name =~ /staff/o) {
390 $copies_visible = '';
391 $has_copies = '' if ($ou_type == 0);
392 $has_vols = '' if ($ou_type == 0);
395 my ($t_filter, $f_filter) = ('','');
396 my ($a_filter, $l_filter, $lf_filter) = ('','','');
398 if (my $a = $args{audience}) {
399 $a = [$a] if (!ref($a));
402 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
406 if (my $l = $args{language}) {
407 $l = [$l] if (!ref($l));
410 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
414 if (my $f = $args{lit_form}) {
415 $f = [$f] if (!ref($f));
418 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
419 push @binds, @lit_form;
422 if (my $f = $args{item_form}) {
423 $f = [$f] if (!ref($f));
426 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
430 if (my $t = $args{item_type}) {
431 $t = [$t] if (!ref($t));
434 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
440 my ($t, $f) = split '-', $args{format};
441 my @types = split '', $t;
442 my @forms = split '', $f;
444 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
448 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
450 push @binds, @types, @forms;
453 my $relevance = 'sum(f.sum)';
454 $relevance = 1 if (!$copies_visible);
456 my $rank = $relevance;
457 if (lc($sort) eq 'pubdate') {
460 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
461 FROM $metabib_full_rec frp
462 WHERE frp.record = f.record
464 AND frp.subfield = 'c'
468 } elsif (lc($sort) eq 'create_date') {
470 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = f.record)) )
472 } elsif (lc($sort) eq 'edit_date') {
474 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = f.record)) )
476 } elsif (lc($sort) eq 'title') {
479 SELECT COALESCE(LTRIM(SUBSTR( frt.value, COALESCE(SUBSTRING(frt.ind2 FROM '\\\\d+'),'0')::INT )),'zzzzzzzz')
480 FROM $metabib_full_rec frt
481 WHERE frt.record = f.record
483 AND frt.subfield = 'a'
487 } elsif (lc($sort) eq 'author') {
490 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
491 FROM $metabib_full_rec fra
492 WHERE fra.record = f.record
493 AND fra.tag LIKE '1%'
494 AND fra.subfield = 'a'
495 ORDER BY fra.tag::text::int
504 if ($copies_visible) {
506 SELECT f.record, $relevance, count(DISTINCT cp.id), $rank
507 FROM $search_table f,
508 $asset_call_number_table cn,
509 $asset_copy_table cp,
513 $metabib_record_descriptor rd,
515 WHERE br.id = f.record
516 AND cn.record = f.record
517 AND rd.record = f.record
518 AND cp.status = cs.id
519 AND cp.location = cl.id
520 AND br.deleted IS FALSE
521 AND cn.deleted IS FALSE
522 AND cp.deleted IS FALSE
531 GROUP BY f.record HAVING count(DISTINCT cp.id) > 0
532 ORDER BY 4 $sort_dir,3 DESC
536 SELECT f.record, 1, 1, $rank
537 FROM $search_table f,
539 $metabib_record_descriptor rd
540 WHERE br.id = f.record
541 AND rd.record = f.record
542 AND br.deleted IS FALSE
554 $log->debug("Search SQL :: [$select]",DEBUG);
556 my $recs = metabib::full_rec->db_Main->selectall_arrayref("$select;", {}, @binds);
557 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
560 $max = 1 if (!@$recs);
562 $max = $$_[1] if ($$_[1] > $max);
566 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
567 next unless ($$rec[0]);
568 my ($rid,$rank,$junk,$skip) = @$rec;
569 $client->respond( [$rid, sprintf('%0.3f',$rank/$max), $count] );
573 __PACKAGE__->register_method(
574 api_name => 'open-ils.storage.biblio.full_rec.multi_search',
575 method => 'biblio_multi_search_full_rec',
580 __PACKAGE__->register_method(
581 api_name => 'open-ils.storage.biblio.full_rec.multi_search.staff',
582 method => 'biblio_multi_search_full_rec',
588 sub search_full_rec {
594 my $term = $args{term};
595 my $limiters = $args{restrict};
597 my ($index_col) = metabib::full_rec->columns('FTS');
598 $index_col ||= 'value';
599 my $search_table = metabib::full_rec->table;
601 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
603 my $fts_where = $fts->sql_where_clause();
604 my @fts_ranks = $fts->fts_rank;
606 my $rank = join(' + ', @fts_ranks);
610 for my $limit (@$limiters) {
611 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
612 push @binds, $$limit{tag}, $$limit{subfield};
613 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
615 my $where = join(' OR ', @wheres);
617 my $select = "SELECT record, sum($rank) FROM $search_table WHERE $where GROUP BY 1 ORDER BY 2 DESC;";
619 $log->debug("Search SQL :: [$select]",DEBUG);
621 my $recs = metabib::full_rec->db_Main->selectall_arrayref($select, {}, @binds);
622 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
624 $client->respond($_) for (@$recs);
627 __PACKAGE__->register_method(
628 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.value',
629 method => 'search_full_rec',
634 __PACKAGE__->register_method(
635 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.index_vector',
636 method => 'search_full_rec',
643 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
644 sub search_class_fts {
649 my $term = $args{term};
650 my $ou = $args{org_unit};
651 my $ou_type = $args{depth};
652 my $limit = $args{limit};
653 my $offset = $args{offset};
655 my $limit_clause = '';
656 my $offset_clause = '';
658 $limit_clause = "LIMIT $limit" if (defined $limit and int($limit) > 0);
659 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
662 my ($t_filter, $f_filter) = ('','');
665 my ($t, $f) = split '-', $args{format};
666 @types = split '', $t;
667 @forms = split '', $f;
669 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
673 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
679 my $descendants = defined($ou_type) ?
680 "actor.org_unit_descendants($ou, $ou_type)" :
681 "actor.org_unit_descendants($ou)";
683 my $class = $self->{cdbi};
684 my $search_table = $class->table;
686 my $metabib_record_descriptor = metabib::record_descriptor->table;
687 my $metabib_metarecord = metabib::metarecord->table;
688 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
689 my $asset_call_number_table = asset::call_number->table;
690 my $asset_copy_table = asset::copy->table;
691 my $cs_table = config::copy_status->table;
692 my $cl_table = asset::copy_location->table;
694 my ($index_col) = $class->columns('FTS');
695 $index_col ||= 'value';
697 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
699 my $fts_where = $fts->sql_where_clause;
700 my @fts_ranks = $fts->fts_rank;
702 my $rank = join(' + ', @fts_ranks);
704 my $has_vols = 'AND cn.owning_lib = d.id';
705 my $has_copies = 'AND cp.call_number = cn.id';
706 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
708 my $visible_count = ', count(DISTINCT cp.id)';
709 my $visible_count_test = 'HAVING count(DISTINCT cp.id) > 0';
711 if ($self->api_name =~ /staff/o) {
712 $copies_visible = '';
713 $visible_count_test = '';
714 $has_copies = '' if ($ou_type == 0);
715 $has_vols = '' if ($ou_type == 0);
718 my $rank_calc = <<" RANK";
720 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
721 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
722 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
723 )/COUNT(m.source)), MIN(COALESCE(CHAR_LENGTH(f.value),1))
726 $rank_calc = ',1 , 1' if ($self->api_name =~ /unordered/o);
728 if ($copies_visible) {
730 SELECT m.metarecord $rank_calc $visible_count, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
731 FROM $search_table f,
732 $metabib_metarecord_source_map_table m,
733 $asset_call_number_table cn,
734 $asset_copy_table cp,
737 $metabib_record_descriptor rd,
740 AND m.source = f.source
741 AND cn.record = m.source
742 AND rd.record = m.source
743 AND cp.status = cs.id
744 AND cp.location = cl.id
750 GROUP BY 1 $visible_count_test
752 $limit_clause $offset_clause
756 SELECT m.metarecord $rank_calc, 0, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
757 FROM $search_table f,
758 $metabib_metarecord_source_map_table m,
759 $metabib_record_descriptor rd
761 AND m.source = f.source
762 AND rd.record = m.source
767 $limit_clause $offset_clause
771 $log->debug("Field Search SQL :: [$select]",DEBUG);
773 my $SQLstring = join('%',$fts->words);
774 my $REstring = join('\\s+',$fts->words);
775 my $first_word = ($fts->words)[0].'%';
776 my $recs = ($self->api_name =~ /unordered/o) ?
777 $class->db_Main->selectall_arrayref($select, {}, @types, @forms) :
778 $class->db_Main->selectall_arrayref($select, {},
779 '%'.lc($SQLstring).'%', # phrase order match
780 lc($first_word), # first word match
781 '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match
785 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
787 $client->respond($_) for (map { [@$_[0,1,3,4]] } @$recs);
791 for my $class ( qw/title author subject keyword series/ ) {
792 __PACKAGE__->register_method(
793 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord",
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.unordered",
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",
810 method => 'search_class_fts',
813 cdbi => "metabib::${class}_field_entry",
816 __PACKAGE__->register_method(
817 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff.unordered",
818 method => 'search_class_fts',
821 cdbi => "metabib::${class}_field_entry",
826 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
827 sub search_class_fts_count {
832 my $term = $args{term};
833 my $ou = $args{org_unit};
834 my $ou_type = $args{depth};
835 my $limit = $args{limit} || 100;
836 my $offset = $args{offset} || 0;
838 my $descendants = defined($ou_type) ?
839 "actor.org_unit_descendants($ou, $ou_type)" :
840 "actor.org_unit_descendants($ou)";
843 my ($t_filter, $f_filter) = ('','');
846 my ($t, $f) = split '-', $args{format};
847 @types = split '', $t;
848 @forms = split '', $f;
850 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
854 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
859 (my $search_class = $self->api_name) =~ s/.*metabib.(\w+).search_fts.*/$1/o;
861 my $class = $self->{cdbi};
862 my $search_table = $class->table;
864 my $metabib_record_descriptor = metabib::record_descriptor->table;
865 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
866 my $asset_call_number_table = asset::call_number->table;
867 my $asset_copy_table = asset::copy->table;
868 my $cs_table = config::copy_status->table;
869 my $cl_table = asset::copy_location->table;
871 my ($index_col) = $class->columns('FTS');
872 $index_col ||= 'value';
874 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
876 my $fts_where = $fts->sql_where_clause;
878 my $has_vols = 'AND cn.owning_lib = d.id';
879 my $has_copies = 'AND cp.call_number = cn.id';
880 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
881 if ($self->api_name =~ /staff/o) {
882 $copies_visible = '';
883 $has_vols = '' if ($ou_type == 0);
884 $has_copies = '' if ($ou_type == 0);
887 # XXX test an "EXISTS version of descendant checking...
889 if ($copies_visible) {
891 SELECT count(distinct m.metarecord)
892 FROM $search_table f,
893 $metabib_metarecord_source_map_table m,
894 $metabib_metarecord_source_map_table mr,
895 $asset_call_number_table cn,
896 $asset_copy_table cp,
899 $metabib_record_descriptor rd,
902 AND mr.source = f.source
903 AND mr.metarecord = m.metarecord
904 AND cn.record = m.source
905 AND rd.record = m.source
906 AND cp.status = cs.id
907 AND cp.location = cl.id
916 SELECT count(distinct m.metarecord)
917 FROM $search_table f,
918 $metabib_metarecord_source_map_table m,
919 $metabib_metarecord_source_map_table mr,
920 $metabib_record_descriptor rd
922 AND mr.source = f.source
923 AND mr.metarecord = m.metarecord
924 AND rd.record = m.source
930 $log->debug("Field Search Count SQL :: [$select]",DEBUG);
932 my $recs = $class->db_Main->selectrow_arrayref($select, {}, @types, @forms)->[0];
934 $log->debug("Count Search yielded $recs results.",DEBUG);
939 for my $class ( qw/title author subject keyword series/ ) {
940 __PACKAGE__->register_method(
941 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count",
942 method => 'search_class_fts_count',
945 cdbi => "metabib::${class}_field_entry",
948 __PACKAGE__->register_method(
949 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count.staff",
950 method => 'search_class_fts_count',
953 cdbi => "metabib::${class}_field_entry",
959 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
960 sub postfilter_search_class_fts {
965 my $term = $args{term};
966 my $sort = $args{'sort'};
967 my $sort_dir = $args{sort_dir} || 'DESC';
968 my $ou = $args{org_unit};
969 my $ou_type = $args{depth};
970 my $limit = $args{limit} || 10;
971 my $offset = $args{offset} || 0;
973 my $outer_limit = 1000;
975 my $limit_clause = '';
976 my $offset_clause = '';
978 $limit_clause = "LIMIT $outer_limit";
979 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
981 my (@types,@forms,@lang,@aud,@lit_form);
982 my ($t_filter, $f_filter) = ('','');
983 my ($a_filter, $l_filter, $lf_filter) = ('','','');
984 my ($ot_filter, $of_filter) = ('','');
985 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
987 if (my $a = $args{audience}) {
988 $a = [$a] if (!ref($a));
991 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
992 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
995 if (my $l = $args{language}) {
996 $l = [$l] if (!ref($l));
999 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1000 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1003 if (my $f = $args{lit_form}) {
1004 $f = [$f] if (!ref($f));
1007 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1008 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1011 if ($args{format}) {
1012 my ($t, $f) = split '-', $args{format};
1013 @types = split '', $t;
1014 @forms = split '', $f;
1016 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1017 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1021 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1022 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1027 my $descendants = defined($ou_type) ?
1028 "actor.org_unit_descendants($ou, $ou_type)" :
1029 "actor.org_unit_descendants($ou)";
1031 my $class = $self->{cdbi};
1032 my $search_table = $class->table;
1034 my $metabib_full_rec = metabib::full_rec->table;
1035 my $metabib_record_descriptor = metabib::record_descriptor->table;
1036 my $metabib_metarecord = metabib::metarecord->table;
1037 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1038 my $asset_call_number_table = asset::call_number->table;
1039 my $asset_copy_table = asset::copy->table;
1040 my $cs_table = config::copy_status->table;
1041 my $cl_table = asset::copy_location->table;
1042 my $br_table = biblio::record_entry->table;
1044 my ($index_col) = $class->columns('FTS');
1045 $index_col ||= 'value';
1047 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
1049 my $SQLstring = join('%',map { lc($_) } $fts->words);
1050 my $REstring = '^' . join('\s+',map { lc($_) } $fts->words) . '\W*$';
1051 my $first_word = lc(($fts->words)[0]).'%';
1053 my $fts_where = $fts->sql_where_clause;
1054 my @fts_ranks = $fts->fts_rank;
1057 $bonus{'metabib::keyword_field_entry'} = [ { 'CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END' => $SQLstring } ];
1058 $bonus{'metabib::title_field_entry'} =
1059 $bonus{'metabib::series_field_entry'} = [
1060 { 'CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END' => $first_word },
1061 { 'CASE WHEN f.value ~* ? THEN 2 ELSE 1 END' => $REstring },
1062 @{ $bonus{'metabib::keyword_field_entry'} }
1065 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$class} };
1066 $bonus_list ||= '1';
1068 my @bonus_values = map { values %$_ } @{ $bonus{$class} };
1070 my $relevance = join(' + ', @fts_ranks);
1071 $relevance = <<" RANK";
1072 (SUM( ( $relevance ) * ( $bonus_list ) )/COUNT(m.source))
1075 my $rank = $relevance;
1076 if (lc($sort) eq 'pubdate') {
1079 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
1080 FROM $metabib_full_rec frp
1081 WHERE frp.record = mr.master_record
1083 AND frp.subfield = 'c'
1087 } elsif (lc($sort) eq 'create_date') {
1089 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1091 } elsif (lc($sort) eq 'edit_date') {
1093 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1095 } elsif (lc($sort) eq 'title') {
1098 SELECT COALESCE(LTRIM(SUBSTR( frt.value, COALESCE(SUBSTRING(frt.ind2 FROM '\\\\d+'),'0')::INT )),'zzzzzzzz')
1099 FROM $metabib_full_rec frt
1100 WHERE frt.record = mr.master_record
1102 AND frt.subfield = 'a'
1106 } elsif (lc($sort) eq 'author') {
1109 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1110 FROM $metabib_full_rec fra
1111 WHERE fra.record = mr.master_record
1112 AND fra.tag LIKE '1%'
1113 AND fra.subfield = 'a'
1114 ORDER BY fra.tag::text::int
1122 my $select = <<" SQL";
1123 SELECT m.metarecord,
1125 CASE WHEN COUNT(DISTINCT smrs.source) = 1 THEN MIN(m.source) ELSE 0 END,
1127 FROM $search_table f,
1128 $metabib_metarecord_source_map_table m,
1129 $metabib_metarecord_source_map_table smrs,
1130 $metabib_metarecord mr,
1131 $metabib_record_descriptor rd
1133 AND smrs.metarecord = mr.id
1134 AND m.source = f.source
1135 AND m.metarecord = mr.id
1136 AND rd.record = smrs.source
1142 GROUP BY m.metarecord
1143 ORDER BY 4 $sort_dir, MIN(COALESCE(CHAR_LENGTH(f.value),1))
1151 FROM $asset_call_number_table cn,
1152 $metabib_metarecord_source_map_table mrs,
1153 $asset_copy_table cp,
1158 $metabib_record_descriptor ord,
1160 WHERE mrs.metarecord = s.metarecord
1161 AND br.id = mrs.source
1162 AND cn.record = mrs.source
1163 AND cp.status = cs.id
1164 AND cp.location = cl.id
1165 AND cn.owning_lib = d.id
1166 AND cp.call_number = cn.id
1167 AND cp.opac_visible IS TRUE
1168 AND cs.holdable IS TRUE
1169 AND cl.opac_visible IS TRUE
1170 AND br.active IS TRUE
1171 AND br.deleted IS FALSE
1172 AND ord.record = mrs.source
1178 ORDER BY 4 $sort_dir
1180 } elsif ($self->api_name !~ /staff/o) {
1187 FROM $asset_call_number_table cn,
1188 $metabib_metarecord_source_map_table mrs,
1189 $asset_copy_table cp,
1194 $metabib_record_descriptor ord
1196 WHERE mrs.metarecord = s.metarecord
1197 AND br.id = mrs.source
1198 AND cn.record = mrs.source
1199 AND cp.status = cs.id
1200 AND cp.location = cl.id
1201 AND cn.owning_lib = d.id
1202 AND cp.call_number = cn.id
1203 AND cp.opac_visible IS TRUE
1204 AND cs.holdable IS TRUE
1205 AND cl.opac_visible IS TRUE
1206 AND br.active IS TRUE
1207 AND br.deleted IS FALSE
1208 AND ord.record = mrs.source
1216 ORDER BY 4 $sort_dir
1225 FROM $asset_call_number_table cn,
1226 $metabib_metarecord_source_map_table mrs,
1229 $metabib_record_descriptor ord
1231 WHERE mrs.metarecord = s.metarecord
1232 AND br.id = mrs.source
1233 AND cn.record = mrs.source
1234 AND cn.owning_lib = d.id
1235 AND br.deleted IS FALSE
1236 AND ord.record = mrs.source
1246 FROM $asset_call_number_table cn,
1247 $metabib_metarecord_source_map_table mrs,
1248 $metabib_record_descriptor ord
1249 WHERE mrs.metarecord = s.metarecord
1250 AND cn.record = mrs.source
1251 AND ord.record = mrs.source
1259 ORDER BY 4 $sort_dir
1264 $log->debug("Field Search SQL :: [$select]",DEBUG);
1266 my $recs = $class->db_Main->selectall_arrayref(
1268 (@bonus_values > 0 ? @bonus_values : () ),
1269 ( (!$sort && @bonus_values > 0) ? @bonus_values : () ),
1270 @types, @forms, @aud, @lang, @lit_form,
1271 @types, @forms, @aud, @lang, @lit_form,
1272 ($self->api_name =~ /staff/o ? (@types, @forms, @aud, @lang, @lit_form) : () ) );
1274 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1277 $max = 1 if (!@$recs);
1279 $max = $$_[1] if ($$_[1] > $max);
1282 my $count = scalar(@$recs);
1283 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1284 my ($mrid,$rank,$skip) = @$rec;
1285 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $skip, $count] );
1290 for my $class ( qw/title author subject keyword series/ ) {
1291 __PACKAGE__->register_method(
1292 api_name => "open-ils.storage.metabib.$class.post_filter.search_fts.metarecord",
1293 method => 'postfilter_search_class_fts',
1296 cdbi => "metabib::${class}_field_entry",
1299 __PACKAGE__->register_method(
1300 api_name => "open-ils.storage.metabib.$class.post_filter.search_fts.metarecord.staff",
1301 method => 'postfilter_search_class_fts',
1304 cdbi => "metabib::${class}_field_entry",
1311 my $_cdbi = { title => "metabib::title_field_entry",
1312 author => "metabib::author_field_entry",
1313 subject => "metabib::subject_field_entry",
1314 keyword => "metabib::keyword_field_entry",
1315 series => "metabib::series_field_entry",
1318 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
1319 sub postfilter_search_multi_class_fts {
1324 my $sort = $args{'sort'};
1325 my $sort_dir = $args{sort_dir} || 'DESC';
1326 my $ou = $args{org_unit};
1327 my $ou_type = $args{depth};
1328 my $limit = $args{limit} || 10;;
1329 my $offset = $args{offset} || 0;
1332 $ou = actor::org_unit->search( { parent_ou => undef } )->next->id;
1335 if (!defined($args{org_unit})) {
1336 die "No target organizational unit passed to ".$self->api_name;
1339 if (! scalar( keys %{$args{searches}} )) {
1340 die "No search arguments were passed to ".$self->api_name;
1343 my $outer_limit = 1000;
1345 my $limit_clause = '';
1346 my $offset_clause = '';
1348 $limit_clause = "LIMIT $outer_limit";
1349 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
1351 my (@types,@forms,@lang,@aud,@lit_form);
1352 my ($t_filter, $f_filter) = ('','');
1353 my ($a_filter, $l_filter, $lf_filter) = ('','','');
1354 my ($ot_filter, $of_filter) = ('','');
1355 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
1357 if (my $a = $args{audience}) {
1358 $a = [$a] if (!ref($a));
1361 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
1362 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
1365 if (my $l = $args{language}) {
1366 $l = [$l] if (!ref($l));
1369 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1370 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1373 if (my $f = $args{lit_form}) {
1374 $f = [$f] if (!ref($f));
1377 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1378 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1381 if (my $f = $args{item_form}) {
1382 $f = [$f] if (!ref($f));
1385 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1386 $of_filter = ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1389 if (my $t = $args{item_type}) {
1390 $t = [$t] if (!ref($t));
1393 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1394 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1398 # XXX legacy format and item type support
1399 if ($args{format}) {
1400 my ($t, $f) = split '-', $args{format};
1401 @types = split '', $t;
1402 @forms = split '', $f;
1404 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1405 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1409 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1410 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1416 my $descendants = defined($ou_type) ?
1417 "actor.org_unit_descendants($ou, $ou_type)" :
1418 "actor.org_unit_descendants($ou)";
1420 my $search_table_list = '';
1422 my $join_table_list = '';
1427 my $prev_search_class;
1428 my $curr_search_class;
1429 for my $search_class (sort keys %{$args{searches}}) {
1430 $prev_search_class = $curr_search_class if ($curr_search_class);
1432 $curr_search_class = $search_class;
1434 my $class = $_cdbi->{$search_class};
1435 my $search_table = $class->table;
1437 my ($index_col) = $class->columns('FTS');
1438 $index_col ||= 'value';
1441 my $fts = OpenILS::Application::Storage::FTS->compile($args{searches}{$search_class}{term}, $search_class.'.value', "$search_class.$index_col");
1443 my $fts_where = $fts->sql_where_clause;
1444 my @fts_ranks = $fts->fts_rank;
1446 my $SQLstring = join('%',map { lc($_) } $fts->words);
1447 my $REstring = '^' . join('\s+',map { lc($_) } $fts->words) . '\W*$';
1448 my $first_word = lc(($fts->words)[0]).'%';
1450 my $rank = join(' + ', @fts_ranks);
1453 $bonus{'keyword'} = [ { "CASE WHEN $search_class.value LIKE ? THEN 1.2 ELSE 1 END" => $SQLstring } ];
1455 $bonus{'series'} = [
1456 { "CASE WHEN $search_class.value LIKE ? THEN 1.5 ELSE 1 END" => $first_word },
1457 { "CASE WHEN $search_class.value ~ ? THEN 20 ELSE 1 END" => $REstring },
1460 $bonus{'title'} = [ @{ $bonus{'series'} }, @{ $bonus{'keyword'} } ];
1462 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$search_class} };
1463 $bonus_list ||= '1';
1465 push @bonus_lists, $bonus_list;
1466 push @bonus_values, map { values %$_ } @{ $bonus{$search_class} };
1469 #---------------------
1471 $search_table_list .= "$search_table $search_class, ";
1472 push @rank_list,$rank;
1473 $fts_list .= " AND $fts_where AND m.source = $search_class.source";
1475 if ($prev_search_class) {
1476 $join_table_list .= " AND $prev_search_class.source = $curr_search_class.source";
1480 my $metabib_record_descriptor = metabib::record_descriptor->table;
1481 my $metabib_full_rec = metabib::full_rec->table;
1482 my $metabib_metarecord = metabib::metarecord->table;
1483 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1484 my $asset_call_number_table = asset::call_number->table;
1485 my $asset_copy_table = asset::copy->table;
1486 my $cs_table = config::copy_status->table;
1487 my $cl_table = asset::copy_location->table;
1488 my $br_table = biblio::record_entry->table;
1490 my $bonuses = join (' * ', @bonus_lists);
1491 my $relevance = join (' + ', @rank_list);
1492 $relevance = "SUM( ($relevance) * ($bonuses) )/COUNT(DISTINCT smrs.source)";
1495 my $secondary_sort = <<" SORT";
1497 SELECT COALESCE(LTRIM(SUBSTR( sfrt.value, COALESCE(SUBSTRING(sfrt.ind2 FROM '\\\\d+'),'0')::INT )),'zzzzzzzz')
1498 FROM $metabib_full_rec sfrt,
1499 $metabib_metarecord mr
1500 WHERE sfrt.record = mr.master_record
1501 AND sfrt.tag = '245'
1502 AND sfrt.subfield = 'a'
1507 my $rank = $relevance;
1508 if (lc($sort) eq 'pubdate') {
1511 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
1512 FROM $metabib_full_rec frp
1513 WHERE frp.record = mr.master_record
1515 AND frp.subfield = 'c'
1519 } elsif (lc($sort) eq 'create_date') {
1521 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1523 } elsif (lc($sort) eq 'edit_date') {
1525 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1527 } elsif (lc($sort) eq 'title') {
1530 SELECT COALESCE(LTRIM(SUBSTR( frt.value, COALESCE(SUBSTRING(frt.ind2 FROM '\\\\d+'),'0')::INT )),'zzzzzzzz')
1531 FROM $metabib_full_rec frt
1532 WHERE frt.record = mr.master_record
1534 AND frt.subfield = 'a'
1538 $secondary_sort = <<" SORT";
1540 SELECT COALESCE(SUBSTRING(sfrp.value FROM '\\\\d+'),'9999')::INT
1541 FROM $metabib_full_rec sfrp,
1542 $metabib_metarecord mr
1543 WHERE sfrp.record = mr.master_record
1544 AND sfrp.tag = '260'
1545 AND sfrp.subfield = 'c'
1549 } elsif (lc($sort) eq 'author') {
1552 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1553 FROM $metabib_full_rec fra
1554 WHERE fra.record = mr.master_record
1555 AND fra.tag LIKE '1%'
1556 AND fra.subfield = 'a'
1557 ORDER BY fra.tag::text::int
1562 push @bonus_values, @bonus_values;
1567 my $select = <<" SQL";
1568 SELECT m.metarecord,
1570 CASE WHEN COUNT(DISTINCT smrs.source) = 1 THEN FIRST(m.source) ELSE 0 END,
1573 FROM $search_table_list
1574 $metabib_metarecord_source_map_table m,
1575 $metabib_metarecord_source_map_table smrs
1576 WHERE m.metarecord = smrs.metarecord
1579 GROUP BY m.metarecord
1580 -- ORDER BY 4 $sort_dir
1584 if ($self->api_name !~ /staff/o) {
1591 FROM $asset_call_number_table cn,
1592 $metabib_metarecord_source_map_table mrs,
1593 $asset_copy_table cp,
1598 $metabib_record_descriptor ord
1599 WHERE mrs.metarecord = s.metarecord
1600 AND br.id = mrs.source
1601 AND cn.record = mrs.source
1602 AND cp.status = cs.id
1603 AND cp.location = cl.id
1604 AND cn.owning_lib = d.id
1605 AND cp.call_number = cn.id
1606 AND cp.opac_visible IS TRUE
1607 AND cs.holdable IS TRUE
1608 AND cl.opac_visible IS TRUE
1609 AND br.active IS TRUE
1610 AND br.deleted IS FALSE
1611 AND cp.deleted IS FALSE
1612 AND cn.deleted IS FALSE
1613 AND ord.record = mrs.source
1621 ORDER BY 4 $sort_dir, 5
1628 $metabib_metarecord_source_map_table omrs,
1629 $metabib_record_descriptor ord
1630 WHERE omrs.metarecord = s.metarecord
1631 AND ord.record = omrs.source
1634 FROM $asset_call_number_table cn,
1637 WHERE br.id = omrs.source
1638 AND cn.record = omrs.source
1639 AND cn.owning_lib = d.id
1640 AND br.deleted IS FALSE
1641 AND cn.deleted IS FALSE
1646 FROM $asset_call_number_table cn
1647 WHERE cn.record = omrs.source
1648 AND cn.deleted IS FALSE
1658 ORDER BY 4 $sort_dir, 5
1663 $log->debug("Field Search SQL :: [$select]",DEBUG);
1665 my $recs = $_cdbi->{title}->db_Main->selectall_arrayref(
1668 @types, @forms, @aud, @lang, @lit_form,
1669 # @types, @forms, @aud, @lang, @lit_form,
1670 # ($self->api_name =~ /staff/o ? (@types, @forms, @aud, @lang, @lit_form) : () )
1673 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1676 $max = 1 if (!@$recs);
1678 $max = $$_[1] if ($$_[1] > $max);
1681 my $count = scalar(@$recs);
1682 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1683 next unless ($$rec[0]);
1684 my ($mrid,$rank,$skip) = @$rec;
1685 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $skip, $count] );
1690 __PACKAGE__->register_method(
1691 api_name => "open-ils.storage.metabib.post_filter.multiclass.search_fts.metarecord",
1692 method => 'postfilter_search_multi_class_fts',
1697 __PACKAGE__->register_method(
1698 api_name => "open-ils.storage.metabib.post_filter.multiclass.search_fts.metarecord.staff",
1699 method => 'postfilter_search_multi_class_fts',
1705 __PACKAGE__->register_method(
1706 api_name => "open-ils.storage.metabib.multiclass.search_fts",
1707 method => 'postfilter_search_multi_class_fts',
1712 __PACKAGE__->register_method(
1713 api_name => "open-ils.storage.metabib.multiclass.search_fts.staff",
1714 method => 'postfilter_search_multi_class_fts',
1720 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
1721 sub biblio_search_multi_class_fts {
1726 my $sort = $args{'sort'};
1727 my $sort_dir = $args{sort_dir} || 'DESC';
1728 my $ou = $args{org_unit};
1729 my $ou_type = $args{depth};
1730 my $limit = $args{limit} || 10;
1731 my $offset = $args{offset} || 0;
1734 $ou = actor::org_unit->search( { parent_ou => undef } )->next->id;
1737 if (!defined($args{org_unit})) {
1738 die "No target organizational unit passed to ".$self->api_name;
1741 if (! scalar( keys %{$args{searches}} )) {
1742 die "No search arguments were passed to ".$self->api_name;
1745 my $outer_limit = 1000;
1747 my $limit_clause = '';
1748 my $offset_clause = '';
1750 $limit_clause = "LIMIT $outer_limit";
1751 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
1753 my (@types,@forms,@lang,@aud,@lit_form);
1754 my ($t_filter, $f_filter) = ('','');
1755 my ($a_filter, $l_filter, $lf_filter) = ('','','');
1756 my ($ot_filter, $of_filter) = ('','');
1757 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
1759 if (my $a = $args{audience}) {
1760 $a = [$a] if (!ref($a));
1763 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
1764 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
1767 if (my $l = $args{language}) {
1768 $l = [$l] if (!ref($l));
1771 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1772 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1775 if (my $f = $args{lit_form}) {
1776 $f = [$f] if (!ref($f));
1779 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1780 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1783 if (my $f = $args{item_form}) {
1784 $f = [$f] if (!ref($f));
1787 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1788 $of_filter = ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1791 if (my $t = $args{item_type}) {
1792 $t = [$t] if (!ref($t));
1795 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1796 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1800 # XXX legacy format and item type support
1801 if ($args{format}) {
1802 my ($t, $f) = split '-', $args{format};
1803 @types = split '', $t;
1804 @forms = split '', $f;
1806 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1807 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1811 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1812 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1817 my $descendants = defined($ou_type) ?
1818 "actor.org_unit_descendants($ou, $ou_type)" :
1819 "actor.org_unit_descendants($ou)";
1821 my $search_table_list = '';
1823 my $join_table_list = '';
1829 my $prev_search_class;
1830 my $curr_search_class;
1831 for my $search_class (sort keys %{$args{searches}}) {
1832 $prev_search_class = $curr_search_class if ($curr_search_class);
1834 $curr_search_class = $search_class;
1836 my $class = $_cdbi->{$search_class};
1837 my $search_table = $class->table;
1839 my ($index_col) = $class->columns('FTS');
1840 $index_col ||= 'value';
1843 my $fts = OpenILS::Application::Storage::FTS->compile($args{searches}{$search_class}{term}, $search_class.'.value', "$search_class.$index_col");
1845 my $fts_where = $fts->sql_where_clause;
1846 my @fts_ranks = $fts->fts_rank;
1848 my $SQLstring = join('%',map { lc($_) } $fts->words);
1849 my $REstring = '^' . join('\s+',map { lc($_) } $fts->words) . '\W*$';
1850 my $first_word = lc(($fts->words)[0]).'%';
1852 my $rank = join(' + ', @fts_ranks);
1855 $bonus{'keyword'} = [ { "CASE WHEN $search_class.value ILIKE ? THEN 1.2 ELSE 1 END" => $SQLstring } ];
1857 $bonus{'series'} = [
1858 { "CASE WHEN $search_class.value ILIKE ? THEN 1.5 ELSE 1 END" => $first_word },
1859 { "CASE WHEN $search_class.value ~ ? THEN 200 ELSE 1 END" => $REstring },
1862 $bonus{'title'} = [ @{ $bonus{'series'} }, @{ $bonus{'keyword'} } ];
1864 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$search_class} };
1865 $bonus_list ||= '1';
1867 push @bonus_lists, $bonus_list;
1868 push @bonus_values, map { values %$_ } @{ $bonus{$search_class} };
1870 #---------------------
1872 $search_table_list .= "$search_table $search_class, ";
1873 push @rank_list,$rank;
1874 $fts_list .= " AND $fts_where AND b.id = $search_class.source";
1876 if ($prev_search_class) {
1877 $join_table_list .= " AND $prev_search_class.source = $curr_search_class.source";
1881 my $metabib_record_descriptor = metabib::record_descriptor->table;
1882 my $metabib_full_rec = metabib::full_rec->table;
1883 my $metabib_metarecord = metabib::metarecord->table;
1884 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1885 my $asset_call_number_table = asset::call_number->table;
1886 my $asset_copy_table = asset::copy->table;
1887 my $cs_table = config::copy_status->table;
1888 my $cl_table = asset::copy_location->table;
1889 my $br_table = biblio::record_entry->table;
1892 my $bonuses = join (' * ', @bonus_lists);
1893 my $relevance = join (' + ', @rank_list);
1894 $relevance = "AVG( ($relevance) * ($bonuses) )";
1897 my $rank = $relevance;
1898 if (lc($sort) eq 'pubdate') {
1901 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d{4}'),'9999')::INT
1902 FROM $metabib_full_rec frp
1903 WHERE frp.record = b.id
1905 AND frp.subfield = 'c'
1909 } elsif (lc($sort) eq 'create_date') {
1911 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = b.id)) )
1913 } elsif (lc($sort) eq 'edit_date') {
1915 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = b.id)) )
1917 } elsif (lc($sort) eq 'title') {
1920 SELECT COALESCE(LTRIM(SUBSTR( frt.value, COALESCE(SUBSTRING(frt.ind2 FROM '\\\\d+'),'0')::INT )),'zzzzzzzz')
1921 FROM $metabib_full_rec frt
1922 WHERE frt.record = b.id
1924 AND frt.subfield = 'a'
1928 } elsif (lc($sort) eq 'author') {
1931 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
1932 FROM $metabib_full_rec fra
1933 WHERE fra.record = b.id
1934 AND fra.tag LIKE '1%'
1935 AND fra.subfield = 'a'
1936 ORDER BY fra.tag::text::int
1941 push @bonus_values, @bonus_values;
1946 my $select = <<" SQL";
1950 FROM $search_table_list
1951 $metabib_record_descriptor rd,
1953 WHERE rd.record = b.id
1954 AND b.active IS TRUE
1955 AND b.deleted IS FALSE
1964 ORDER BY 3 $sort_dir
1968 if ($self->api_name !~ /staff/o) {
1975 FROM $asset_call_number_table cn,
1976 $asset_copy_table cp,
1980 WHERE cn.record = s.id
1981 AND cp.status = cs.id
1982 AND cp.location = cl.id
1983 AND cn.owning_lib = d.id
1984 AND cp.call_number = cn.id
1985 AND cp.opac_visible IS TRUE
1986 AND cs.holdable IS TRUE
1987 AND cl.opac_visible IS TRUE
1988 AND cp.deleted IS FALSE
1989 AND cn.deleted IS FALSE
1992 ORDER BY 3 $sort_dir
2001 FROM $asset_call_number_table cn,
2003 WHERE cn.record = s.id
2004 AND cn.owning_lib = d.id
2005 AND cn.deleted IS FALSE
2010 FROM $asset_call_number_table cn
2011 WHERE cn.record = s.id
2014 ORDER BY 3 $sort_dir
2019 $log->debug("Field Search SQL :: [$select]",DEBUG);
2021 my $recs = $_cdbi->{title}->db_Main->selectall_arrayref(
2023 @bonus_values, @types, @forms, @aud, @lang, @lit_form
2026 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
2029 $max = 1 if (!@$recs);
2031 $max = $$_[1] if ($$_[1] > $max);
2034 my $count = scalar(@$recs);
2035 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
2036 next unless ($$rec[0]);
2037 my ($mrid,$rank) = @$rec;
2038 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $count] );
2043 __PACKAGE__->register_method(
2044 api_name => "open-ils.storage.biblio.multiclass.search_fts.record",
2045 method => 'biblio_search_multi_class_fts',
2050 __PACKAGE__->register_method(
2051 api_name => "open-ils.storage.biblio.multiclass.search_fts.record.staff",
2052 method => 'biblio_search_multi_class_fts',
2060 __PACKAGE__->register_method(
2061 api_name => "open-ils.storage.biblio.multiclass.search_fts",
2062 method => 'biblio_search_multi_class_fts',
2067 __PACKAGE__->register_method(
2068 api_name => "open-ils.storage.biblio.multiclass.search_fts.staff",
2069 method => 'biblio_search_multi_class_fts',