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;
9 use OpenSRF::Utils::JSON;
11 use Digest::MD5 qw/md5_hex/;
14 my $log = 'OpenSRF::Utils::Logger';
18 sub ordered_records_from_metarecord {
26 my (@types,@forms,@blvl);
29 my ($t, $f, $b) = split '-', $formats;
30 @types = split '', $t;
31 @forms = split '', $f;
37 "actor.org_unit_descendants($org, $depth)" :
38 "actor.org_unit_descendants($org)" ;
41 my $copies_visible = 'AND d.opac_visible IS TRUE AND cp.opac_visible IS TRUE AND cs.opac_visible IS TRUE AND cl.opac_visible IS TRUE';
42 $copies_visible = '' if ($self->api_name =~ /staff/o);
44 my $sm_table = metabib::metarecord_source_map->table;
45 my $rd_table = metabib::record_descriptor->table;
46 my $fr_table = metabib::full_rec->table;
47 my $cn_table = asset::call_number->table;
48 my $cl_table = asset::copy_location->table;
49 my $cp_table = asset::copy->table;
50 my $cs_table = config::copy_status->table;
51 my $src_table = config::bib_source->table;
52 my $out_table = actor::org_unit_type->table;
53 my $br_table = biblio::record_entry->table;
60 FIRST(COALESCE(LTRIM(SUBSTR( value, COALESCE(SUBSTRING(ind2 FROM '\\\\d+'),'0')::INT + 1 )),'zzzzzzzz')) AS title
72 if ($copies_visible) {
78 WHERE rd.record = sm.source
79 AND fr.record = sm.source
82 AND (EXISTS ((SELECT 1
84 JOIN $cn_table cn ON (cp.call_number = cn.id)
85 JOIN $cs_table cs ON (cp.status = cs.id)
86 JOIN $cl_table cl ON (cp.location = cl.id)
87 JOIN $descendants d ON (cp.circ_lib = d.id)
88 WHERE cn.record = sm.source
94 WHERE src.id = br.source
95 AND src.transcendant IS TRUE))
102 JOIN $br_table br ON (sm.source = br.id)
103 JOIN $fr_table fr ON (fr.record = br.id)
104 JOIN $rd_table rd ON (rd.record = br.id)
105 WHERE sm.metarecord = ?
111 WHERE cn.record = br.id
112 AND cn.deleted = FALSE
113 AND cp.deleted = FALSE
114 AND cp.circ_lib = d.id
115 AND cn.id = cp.call_number
121 WHERE cn.record = br.id
122 AND cn.deleted = FALSE
123 AND cp.deleted = FALSE
124 AND cn.id = cp.call_number
130 WHERE src.id = br.source
131 AND src.transcendant IS TRUE))
137 $sql .= ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
141 $sql .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
145 $sql .= ' AND rd.bib_level IN ('.join(',',map{'?'}@blvl).')';
155 GROUP BY record, item_type, item_form, quality
158 WHEN item_type IS NULL -- default
160 WHEN item_type = '' -- default
162 WHEN item_type IN ('a','t') -- books
164 WHEN item_type = 'g' -- movies
166 WHEN item_type IN ('i','j') -- sound recordings
168 WHEN item_type = 'm' -- software
170 WHEN item_type = 'k' -- images
172 WHEN item_type IN ('e','f') -- maps
174 WHEN item_type IN ('o','p') -- mixed
176 WHEN item_type IN ('c','d') -- music
178 WHEN item_type = 'r' -- 3d
185 my $ids = metabib::metarecord_source_map->db_Main->selectcol_arrayref($sql, {}, "$mr", @types, @forms, @blvl);
186 return $ids if ($self->api_name =~ /atomic$/o);
188 $client->respond( $_ ) for ( @$ids );
192 __PACKAGE__->register_method(
193 api_name => 'open-ils.storage.ordered.metabib.metarecord.records',
194 method => 'ordered_records_from_metarecord',
198 __PACKAGE__->register_method(
199 api_name => 'open-ils.storage.ordered.metabib.metarecord.records.staff',
200 method => 'ordered_records_from_metarecord',
205 __PACKAGE__->register_method(
206 api_name => 'open-ils.storage.ordered.metabib.metarecord.records.atomic',
207 method => 'ordered_records_from_metarecord',
211 __PACKAGE__->register_method(
212 api_name => 'open-ils.storage.ordered.metabib.metarecord.records.staff.atomic',
213 method => 'ordered_records_from_metarecord',
221 my $isxn = lc(shift());
225 $isxn =~ s/-//o if ($self->api_name =~ /isbn/o);
227 my $tag = ($self->api_name =~ /isbn/o) ? "'020' OR f.tag = '024'" : "'022'";
229 my $fr_table = metabib::full_rec->table;
230 my $bib_table = biblio::record_entry->table;
233 SELECT DISTINCT f.record
235 JOIN $bib_table b ON (b.id = f.record)
238 AND b.deleted IS FALSE
241 my $list = metabib::full_rec->db_Main->selectcol_arrayref($sql, {}, "$isxn%");
242 $client->respond($_) for (@$list);
245 __PACKAGE__->register_method(
246 api_name => 'open-ils.storage.id_list.biblio.record_entry.search.isbn',
247 method => 'isxn_search',
251 __PACKAGE__->register_method(
252 api_name => 'open-ils.storage.id_list.biblio.record_entry.search.issn',
253 method => 'isxn_search',
258 sub metarecord_copy_count {
264 my $sm_table = metabib::metarecord_source_map->table;
265 my $rd_table = metabib::record_descriptor->table;
266 my $cn_table = asset::call_number->table;
267 my $cp_table = asset::copy->table;
268 my $br_table = biblio::record_entry->table;
269 my $src_table = config::bib_source->table;
270 my $cl_table = asset::copy_location->table;
271 my $cs_table = config::copy_status->table;
272 my $out_table = actor::org_unit_type->table;
274 my $descendants = "actor.org_unit_descendants(u.id)";
275 my $ancestors = "actor.org_unit_ancestors(?) u JOIN $out_table t ON (u.ou_type = t.id)";
277 if ($args{org_unit} < 0) {
278 $args{org_unit} *= -1;
279 $ancestors = "(select org_unit as id from actor.org_lasso_map where lasso = ?) u CROSS JOIN (SELECT -1 AS depth) t";
282 my $copies_visible = 'AND a.opac_visible IS TRUE AND cp.opac_visible IS TRUE AND cs.opac_visible IS TRUE AND cl.opac_visible IS TRUE';
283 $copies_visible = '' if ($self->api_name =~ /staff/o);
285 my (@types,@forms,@blvl);
286 my ($t_filter, $f_filter, $b_filter) = ('','','');
289 my ($t, $f, $b) = split '-', $args{format};
290 @types = split '', $t;
291 @forms = split '', $f;
292 @blvl = split '', $b;
295 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
299 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
303 $b_filter .= ' AND rd.bib_level IN ('.join(',',map{'?'}@blvl).')';
313 JOIN $cn_table cn ON (cn.record = r.source)
314 JOIN $rd_table rd ON (cn.record = rd.record)
315 JOIN $cp_table cp ON (cn.id = cp.call_number)
316 JOIN $cs_table cs ON (cp.status = cs.id)
317 JOIN $cl_table cl ON (cp.location = cl.id)
318 JOIN $descendants a ON (cp.circ_lib = a.id)
319 WHERE r.metarecord = ?
320 AND cn.deleted IS FALSE
321 AND cp.deleted IS FALSE
331 JOIN $cn_table cn ON (cn.record = r.source)
332 JOIN $rd_table rd ON (cn.record = rd.record)
333 JOIN $cp_table cp ON (cn.id = cp.call_number)
334 JOIN $cs_table cs ON (cp.status = cs.id)
335 JOIN $cl_table cl ON (cp.location = cl.id)
336 JOIN $descendants a ON (cp.circ_lib = a.id)
337 WHERE r.metarecord = ?
338 AND cp.status IN (0,7,12)
339 AND cn.deleted IS FALSE
340 AND cp.deleted IS FALSE
350 JOIN $cn_table cn ON (cn.record = r.source)
351 JOIN $rd_table rd ON (cn.record = rd.record)
352 JOIN $cp_table cp ON (cn.id = cp.call_number)
353 JOIN $cs_table cs ON (cp.status = cs.id)
354 JOIN $cl_table cl ON (cp.location = cl.id)
355 WHERE r.metarecord = ?
356 AND cn.deleted IS FALSE
357 AND cp.deleted IS FALSE
358 AND cp.opac_visible IS TRUE
359 AND cs.opac_visible IS TRUE
360 AND cl.opac_visible IS TRUE
369 JOIN $br_table br ON (br.id = r.source)
370 JOIN $src_table src ON (src.id = br.source)
371 WHERE r.metarecord = ?
372 AND src.transcendant IS TRUE
380 my $sth = metabib::metarecord_source_map->db_Main->prepare_cached($sql);
381 $sth->execute( ''.$args{metarecord},
385 ''.$args{metarecord},
389 ''.$args{metarecord},
393 ''.$args{metarecord},
397 while ( my $row = $sth->fetchrow_hashref ) {
398 $client->respond( $row );
402 __PACKAGE__->register_method(
403 api_name => 'open-ils.storage.metabib.metarecord.copy_count',
404 method => 'metarecord_copy_count',
409 __PACKAGE__->register_method(
410 api_name => 'open-ils.storage.metabib.metarecord.copy_count.staff',
411 method => 'metarecord_copy_count',
417 sub biblio_multi_search_full_rec {
422 my $class_join = $args{class_join} || 'AND';
423 my $limit = $args{limit} || 100;
424 my $offset = $args{offset} || 0;
425 my $sort = $args{'sort'};
426 my $sort_dir = $args{sort_dir} || 'DESC';
431 for my $arg (@{ $args{searches} }) {
432 my $term = $$arg{term};
433 my $limiters = $$arg{restrict};
435 my ($index_col) = metabib::full_rec->columns('FTS');
436 $index_col ||= 'value';
437 my $search_table = metabib::full_rec->table;
439 my $fts = OpenILS::Application::Storage::FTS->compile('default' => $term, 'value',"$index_col");
441 my $fts_where = $fts->sql_where_clause();
442 my @fts_ranks = $fts->fts_rank;
444 my $rank = join(' + ', @fts_ranks);
447 for my $limit (@$limiters) {
448 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
449 push @binds, $$limit{tag}, $$limit{subfield};
450 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
452 my $where = join(' OR ', @wheres);
454 push @selects, "SELECT id, record, $rank as sum FROM $search_table WHERE $where";
458 my $descendants = defined($args{depth}) ?
459 "actor.org_unit_descendants($args{org_unit}, $args{depth})" :
460 "actor.org_unit_descendants($args{org_unit})" ;
463 my $metabib_record_descriptor = metabib::record_descriptor->table;
464 my $metabib_full_rec = metabib::full_rec->table;
465 my $asset_call_number_table = asset::call_number->table;
466 my $asset_copy_table = asset::copy->table;
467 my $cs_table = config::copy_status->table;
468 my $cl_table = asset::copy_location->table;
469 my $br_table = biblio::record_entry->table;
471 my $cj = 'HAVING COUNT(x.id) = ' . scalar(@selects) if ($class_join eq 'AND');
473 '(SELECT x.record, sum(x.sum) FROM (('.
474 join(') UNION ALL (', @selects).
475 ")) x GROUP BY 1 $cj ORDER BY 2 DESC )";
477 my $has_vols = 'AND cn.owning_lib = d.id';
478 my $has_copies = 'AND cp.call_number = cn.id';
479 my $copies_visible = 'AND d.opac_visible IS TRUE AND cp.opac_visible IS TRUE AND cs.opac_visible IS TRUE AND cl.opac_visible IS TRUE';
481 if ($self->api_name =~ /staff/o) {
482 $copies_visible = '';
483 $has_copies = '' if ($ou_type == 0);
484 $has_vols = '' if ($ou_type == 0);
487 my ($t_filter, $f_filter) = ('','');
488 my ($a_filter, $l_filter, $lf_filter) = ('','','');
490 if (my $a = $args{audience}) {
491 $a = [$a] if (!ref($a));
494 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
498 if (my $l = $args{language}) {
499 $l = [$l] if (!ref($l));
502 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
506 if (my $f = $args{lit_form}) {
507 $f = [$f] if (!ref($f));
510 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
511 push @binds, @lit_form;
514 if (my $f = $args{item_form}) {
515 $f = [$f] if (!ref($f));
518 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
522 if (my $t = $args{item_type}) {
523 $t = [$t] if (!ref($t));
526 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
532 my ($t, $f) = split '-', $args{format};
533 my @types = split '', $t;
534 my @forms = split '', $f;
536 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
540 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
542 push @binds, @types, @forms;
545 my $relevance = 'sum(f.sum)';
546 $relevance = 1 if (!$copies_visible);
548 my $rank = $relevance;
549 if (lc($sort) eq 'pubdate') {
552 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'9999')::INT
553 FROM $metabib_full_rec frp
554 WHERE frp.record = f.record
556 AND frp.subfield = 'c'
560 } elsif (lc($sort) eq 'create_date') {
562 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = f.record)) )
564 } elsif (lc($sort) eq 'edit_date') {
566 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = f.record)) )
568 } elsif (lc($sort) eq 'title') {
571 SELECT COALESCE(LTRIM(SUBSTR( frt.value, COALESCE(SUBSTRING(frt.ind2 FROM '\\\\d+'),'0')::INT + 1 )),'zzzzzzzz')
572 FROM $metabib_full_rec frt
573 WHERE frt.record = f.record
575 AND frt.subfield = 'a'
579 } elsif (lc($sort) eq 'author') {
582 SELECT COALESCE(LTRIM(fra.value),'zzzzzzzz')
583 FROM $metabib_full_rec fra
584 WHERE fra.record = f.record
585 AND fra.tag LIKE '1%'
586 AND fra.subfield = 'a'
587 ORDER BY fra.tag::text::int
596 if ($copies_visible) {
598 SELECT f.record, $relevance, count(DISTINCT cp.id), $rank
599 FROM $search_table f,
600 $asset_call_number_table cn,
601 $asset_copy_table cp,
605 $metabib_record_descriptor rd,
607 WHERE br.id = f.record
608 AND cn.record = f.record
609 AND rd.record = f.record
610 AND cp.status = cs.id
611 AND cp.location = cl.id
612 AND br.deleted IS FALSE
613 AND cn.deleted IS FALSE
614 AND cp.deleted IS FALSE
623 GROUP BY f.record HAVING count(DISTINCT cp.id) > 0
624 ORDER BY 4 $sort_dir,3 DESC
628 SELECT f.record, 1, 1, $rank
629 FROM $search_table f,
631 $metabib_record_descriptor rd
632 WHERE br.id = f.record
633 AND rd.record = f.record
634 AND br.deleted IS FALSE
646 $log->debug("Search SQL :: [$select]",DEBUG);
648 my $recs = metabib::full_rec->db_Main->selectall_arrayref("$select;", {}, @binds);
649 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
652 $max = 1 if (!@$recs);
654 $max = $$_[1] if ($$_[1] > $max);
658 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
659 next unless ($$rec[0]);
660 my ($rid,$rank,$junk,$skip) = @$rec;
661 $client->respond( [$rid, sprintf('%0.3f',$rank/$max), $count] );
665 __PACKAGE__->register_method(
666 api_name => 'open-ils.storage.biblio.full_rec.multi_search',
667 method => 'biblio_multi_search_full_rec',
672 __PACKAGE__->register_method(
673 api_name => 'open-ils.storage.biblio.full_rec.multi_search.staff',
674 method => 'biblio_multi_search_full_rec',
680 sub search_full_rec {
686 my $term = $args{term};
687 my $limiters = $args{restrict};
689 my ($index_col) = metabib::full_rec->columns('FTS');
690 $index_col ||= 'value';
691 my $search_table = metabib::full_rec->table;
693 my $fts = OpenILS::Application::Storage::FTS->compile('default' => $term, 'value',"$index_col");
695 my $fts_where = $fts->sql_where_clause();
696 my @fts_ranks = $fts->fts_rank;
698 my $rank = join(' + ', @fts_ranks);
702 for my $limit (@$limiters) {
703 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
704 push @binds, $$limit{tag}, $$limit{subfield};
705 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
707 my $where = join(' OR ', @wheres);
709 my $select = "SELECT record, sum($rank) FROM $search_table WHERE $where GROUP BY 1 ORDER BY 2 DESC;";
711 $log->debug("Search SQL :: [$select]",DEBUG);
713 my $recs = metabib::full_rec->db_Main->selectall_arrayref($select, {}, @binds);
714 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
716 $client->respond($_) for (@$recs);
719 __PACKAGE__->register_method(
720 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.value',
721 method => 'search_full_rec',
726 __PACKAGE__->register_method(
727 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.index_vector',
728 method => 'search_full_rec',
735 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
736 sub search_class_fts {
741 my $term = $args{term};
742 my $ou = $args{org_unit};
743 my $ou_type = $args{depth};
744 my $limit = $args{limit};
745 my $offset = $args{offset};
747 my $limit_clause = '';
748 my $offset_clause = '';
750 $limit_clause = "LIMIT $limit" if (defined $limit and int($limit) > 0);
751 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
754 my ($t_filter, $f_filter) = ('','');
757 my ($t, $f) = split '-', $args{format};
758 @types = split '', $t;
759 @forms = split '', $f;
761 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
765 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
771 my $descendants = defined($ou_type) ?
772 "actor.org_unit_descendants($ou, $ou_type)" :
773 "actor.org_unit_descendants($ou)";
775 my $class = $self->{cdbi};
776 my $search_table = $class->table;
778 my $metabib_record_descriptor = metabib::record_descriptor->table;
779 my $metabib_metarecord = metabib::metarecord->table;
780 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
781 my $asset_call_number_table = asset::call_number->table;
782 my $asset_copy_table = asset::copy->table;
783 my $cs_table = config::copy_status->table;
784 my $cl_table = asset::copy_location->table;
786 my ($index_col) = $class->columns('FTS');
787 $index_col ||= 'value';
789 (my $search_class = $self->api_name) =~ s/.*metabib.(\w+).search_fts.*/$1/o;
790 my $fts = OpenILS::Application::Storage::FTS->compile($search_class => $term, 'f.value', "f.$index_col");
792 my $fts_where = $fts->sql_where_clause;
793 my @fts_ranks = $fts->fts_rank;
795 my $rank = join(' + ', @fts_ranks);
797 my $has_vols = 'AND cn.owning_lib = d.id';
798 my $has_copies = 'AND cp.call_number = cn.id';
799 my $copies_visible = 'AND d.opac_visible IS TRUE AND cp.opac_visible IS TRUE AND cs.opac_visible IS TRUE AND cl.opac_visible IS TRUE';
801 my $visible_count = ', count(DISTINCT cp.id)';
802 my $visible_count_test = 'HAVING count(DISTINCT cp.id) > 0';
804 if ($self->api_name =~ /staff/o) {
805 $copies_visible = '';
806 $visible_count_test = '';
807 $has_copies = '' if ($ou_type == 0);
808 $has_vols = '' if ($ou_type == 0);
811 my $rank_calc = <<" RANK";
813 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
814 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
815 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
816 )/COUNT(m.source)), MIN(COALESCE(CHAR_LENGTH(f.value),1))
819 $rank_calc = ',1 , 1' if ($self->api_name =~ /unordered/o);
821 if ($copies_visible) {
823 SELECT m.metarecord $rank_calc $visible_count, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
824 FROM $search_table f,
825 $metabib_metarecord_source_map_table m,
826 $asset_call_number_table cn,
827 $asset_copy_table cp,
830 $metabib_record_descriptor rd,
833 AND m.source = f.source
834 AND cn.record = m.source
835 AND rd.record = m.source
836 AND cp.status = cs.id
837 AND cp.location = cl.id
843 GROUP BY 1 $visible_count_test
845 $limit_clause $offset_clause
849 SELECT m.metarecord $rank_calc, 0, CASE WHEN COUNT(DISTINCT m.source) = 1 THEN MAX(m.source) ELSE MAX(0) END
850 FROM $search_table f,
851 $metabib_metarecord_source_map_table m,
852 $metabib_record_descriptor rd
854 AND m.source = f.source
855 AND rd.record = m.source
860 $limit_clause $offset_clause
864 $log->debug("Field Search SQL :: [$select]",DEBUG);
866 my $SQLstring = join('%',$fts->words);
867 my $REstring = join('\\s+',$fts->words);
868 my $first_word = ($fts->words)[0].'%';
869 my $recs = ($self->api_name =~ /unordered/o) ?
870 $class->db_Main->selectall_arrayref($select, {}, @types, @forms) :
871 $class->db_Main->selectall_arrayref($select, {},
872 '%'.lc($SQLstring).'%', # phrase order match
873 lc($first_word), # first word match
874 '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match
878 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
880 $client->respond($_) for (map { [@$_[0,1,3,4]] } @$recs);
884 for my $class ( qw/title author subject keyword series/ ) {
885 __PACKAGE__->register_method(
886 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord",
887 method => 'search_class_fts',
890 cdbi => "metabib::${class}_field_entry",
893 __PACKAGE__->register_method(
894 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.unordered",
895 method => 'search_class_fts',
898 cdbi => "metabib::${class}_field_entry",
901 __PACKAGE__->register_method(
902 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff",
903 method => 'search_class_fts',
906 cdbi => "metabib::${class}_field_entry",
909 __PACKAGE__->register_method(
910 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff.unordered",
911 method => 'search_class_fts',
914 cdbi => "metabib::${class}_field_entry",
919 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
920 sub search_class_fts_count {
925 my $term = $args{term};
926 my $ou = $args{org_unit};
927 my $ou_type = $args{depth};
928 my $limit = $args{limit} || 100;
929 my $offset = $args{offset} || 0;
931 my $descendants = defined($ou_type) ?
932 "actor.org_unit_descendants($ou, $ou_type)" :
933 "actor.org_unit_descendants($ou)";
936 my ($t_filter, $f_filter) = ('','');
939 my ($t, $f) = split '-', $args{format};
940 @types = split '', $t;
941 @forms = split '', $f;
943 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
947 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
952 (my $search_class = $self->api_name) =~ s/.*metabib.(\w+).search_fts.*/$1/o;
954 my $class = $self->{cdbi};
955 my $search_table = $class->table;
957 my $metabib_record_descriptor = metabib::record_descriptor->table;
958 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
959 my $asset_call_number_table = asset::call_number->table;
960 my $asset_copy_table = asset::copy->table;
961 my $cs_table = config::copy_status->table;
962 my $cl_table = asset::copy_location->table;
964 my ($index_col) = $class->columns('FTS');
965 $index_col ||= 'value';
967 my $fts = OpenILS::Application::Storage::FTS->compile($search_class => $term, 'value',"$index_col");
969 my $fts_where = $fts->sql_where_clause;
971 my $has_vols = 'AND cn.owning_lib = d.id';
972 my $has_copies = 'AND cp.call_number = cn.id';
973 my $copies_visible = 'AND d.opac_visible IS TRUE AND cp.opac_visible IS TRUE AND cs.opac_visible IS TRUE AND cl.opac_visible IS TRUE';
974 if ($self->api_name =~ /staff/o) {
975 $copies_visible = '';
976 $has_vols = '' if ($ou_type == 0);
977 $has_copies = '' if ($ou_type == 0);
980 # XXX test an "EXISTS version of descendant checking...
982 if ($copies_visible) {
984 SELECT count(distinct m.metarecord)
985 FROM $search_table f,
986 $metabib_metarecord_source_map_table m,
987 $metabib_metarecord_source_map_table mr,
988 $asset_call_number_table cn,
989 $asset_copy_table cp,
992 $metabib_record_descriptor rd,
995 AND mr.source = f.source
996 AND mr.metarecord = m.metarecord
997 AND cn.record = m.source
998 AND rd.record = m.source
999 AND cp.status = cs.id
1000 AND cp.location = cl.id
1009 SELECT count(distinct m.metarecord)
1010 FROM $search_table f,
1011 $metabib_metarecord_source_map_table m,
1012 $metabib_metarecord_source_map_table mr,
1013 $metabib_record_descriptor rd
1015 AND mr.source = f.source
1016 AND mr.metarecord = m.metarecord
1017 AND rd.record = m.source
1023 $log->debug("Field Search Count SQL :: [$select]",DEBUG);
1025 my $recs = $class->db_Main->selectrow_arrayref($select, {}, @types, @forms)->[0];
1027 $log->debug("Count Search yielded $recs results.",DEBUG);
1032 for my $class ( qw/title author subject keyword series/ ) {
1033 __PACKAGE__->register_method(
1034 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count",
1035 method => 'search_class_fts_count',
1038 cdbi => "metabib::${class}_field_entry",
1041 __PACKAGE__->register_method(
1042 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count.staff",
1043 method => 'search_class_fts_count',
1046 cdbi => "metabib::${class}_field_entry",
1052 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
1053 sub postfilter_search_class_fts {
1058 my $term = $args{term};
1059 my $sort = $args{'sort'};
1060 my $sort_dir = $args{sort_dir} || 'DESC';
1061 my $ou = $args{org_unit};
1062 my $ou_type = $args{depth};
1063 my $limit = $args{limit} || 10;
1064 my $visibility_limit = $args{visibility_limit} || 5000;
1065 my $offset = $args{offset} || 0;
1067 my $outer_limit = 1000;
1069 my $limit_clause = '';
1070 my $offset_clause = '';
1072 $limit_clause = "LIMIT $outer_limit";
1073 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
1075 my (@types,@forms,@lang,@aud,@lit_form);
1076 my ($t_filter, $f_filter) = ('','');
1077 my ($a_filter, $l_filter, $lf_filter) = ('','','');
1078 my ($ot_filter, $of_filter) = ('','');
1079 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
1081 if (my $a = $args{audience}) {
1082 $a = [$a] if (!ref($a));
1085 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
1086 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
1089 if (my $l = $args{language}) {
1090 $l = [$l] if (!ref($l));
1093 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1094 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1097 if (my $f = $args{lit_form}) {
1098 $f = [$f] if (!ref($f));
1101 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1102 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1105 if ($args{format}) {
1106 my ($t, $f) = split '-', $args{format};
1107 @types = split '', $t;
1108 @forms = split '', $f;
1110 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1111 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1115 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1116 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1121 my $descendants = defined($ou_type) ?
1122 "actor.org_unit_descendants($ou, $ou_type)" :
1123 "actor.org_unit_descendants($ou)";
1125 my $class = $self->{cdbi};
1126 my $search_table = $class->table;
1128 my $metabib_full_rec = metabib::full_rec->table;
1129 my $metabib_record_descriptor = metabib::record_descriptor->table;
1130 my $metabib_metarecord = metabib::metarecord->table;
1131 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1132 my $asset_call_number_table = asset::call_number->table;
1133 my $asset_copy_table = asset::copy->table;
1134 my $cs_table = config::copy_status->table;
1135 my $cl_table = asset::copy_location->table;
1136 my $br_table = biblio::record_entry->table;
1138 my ($index_col) = $class->columns('FTS');
1139 $index_col ||= 'value';
1141 (my $search_class = $self->api_name) =~ s/.*metabib.(\w+).post_filter.*/$1/o;
1143 my $fts = OpenILS::Application::Storage::FTS->compile($search_class => $term, 'f.value', "f.$index_col");
1145 my $SQLstring = join('%',map { lc($_) } $fts->words);
1146 my $REstring = '^' . join('\s+',map { lc($_) } $fts->words) . '\W*$';
1147 my $first_word = lc(($fts->words)[0]).'%';
1149 my $fts_where = $fts->sql_where_clause;
1150 my @fts_ranks = $fts->fts_rank;
1153 $bonus{'metabib::keyword_field_entry'} = [ { 'CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END' => $SQLstring } ];
1154 $bonus{'metabib::title_field_entry'} =
1155 $bonus{'metabib::series_field_entry'} = [
1156 { 'CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END' => $first_word },
1157 { 'CASE WHEN f.value ~* ? THEN 2 ELSE 1 END' => $REstring },
1158 @{ $bonus{'metabib::keyword_field_entry'} }
1161 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$class} };
1162 $bonus_list ||= '1';
1164 my @bonus_values = map { values %$_ } @{ $bonus{$class} };
1166 my $relevance = join(' + ', @fts_ranks);
1167 $relevance = <<" RANK";
1168 (SUM( ( $relevance ) * ( $bonus_list ) )/COUNT(m.source))
1171 my $string_default_sort = 'zzzz';
1172 $string_default_sort = 'AAAA' if ($sort_dir eq 'DESC');
1174 my $number_default_sort = '9999';
1175 $number_default_sort = '0000' if ($sort_dir eq 'DESC');
1177 my $rank = $relevance;
1178 if (lc($sort) eq 'pubdate') {
1181 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'$number_default_sort')::INT
1182 FROM $metabib_full_rec frp
1183 WHERE frp.record = mr.master_record
1185 AND frp.subfield = 'c'
1189 } elsif (lc($sort) eq 'create_date') {
1191 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1193 } elsif (lc($sort) eq 'edit_date') {
1195 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1197 } elsif (lc($sort) eq 'title') {
1200 SELECT COALESCE(LTRIM(SUBSTR( frt.value, COALESCE(SUBSTRING(frt.ind2 FROM '\\\\d+'),'0')::INT + 1 )),'$string_default_sort')
1201 FROM $metabib_full_rec frt
1202 WHERE frt.record = mr.master_record
1204 AND frt.subfield = 'a'
1208 } elsif (lc($sort) eq 'author') {
1211 SELECT COALESCE(LTRIM(fra.value),'$string_default_sort')
1212 FROM $metabib_full_rec fra
1213 WHERE fra.record = mr.master_record
1214 AND fra.tag LIKE '1%'
1215 AND fra.subfield = 'a'
1216 ORDER BY fra.tag::text::int
1224 my $select = <<" SQL";
1225 SELECT m.metarecord,
1227 CASE WHEN COUNT(DISTINCT smrs.source) = 1 THEN MIN(m.source) ELSE 0 END,
1229 FROM $search_table f,
1230 $metabib_metarecord_source_map_table m,
1231 $metabib_metarecord_source_map_table smrs,
1232 $metabib_metarecord mr,
1233 $metabib_record_descriptor rd
1235 AND smrs.metarecord = mr.id
1236 AND m.source = f.source
1237 AND m.metarecord = mr.id
1238 AND rd.record = smrs.source
1244 GROUP BY m.metarecord
1245 ORDER BY 4 $sort_dir, MIN(COALESCE(CHAR_LENGTH(f.value),1))
1246 LIMIT $visibility_limit
1253 FROM $asset_call_number_table cn,
1254 $metabib_metarecord_source_map_table mrs,
1255 $asset_copy_table cp,
1260 $metabib_record_descriptor ord,
1262 WHERE mrs.metarecord = s.metarecord
1263 AND br.id = mrs.source
1264 AND cn.record = mrs.source
1265 AND cp.status = cs.id
1266 AND cp.location = cl.id
1267 AND cn.owning_lib = d.id
1268 AND cp.call_number = cn.id
1269 AND cp.opac_visible IS TRUE
1270 AND cs.opac_visible IS TRUE
1271 AND cl.opac_visible IS TRUE
1272 AND d.opac_visible IS TRUE
1273 AND br.active IS TRUE
1274 AND br.deleted IS FALSE
1275 AND ord.record = mrs.source
1281 ORDER BY 4 $sort_dir
1283 } elsif ($self->api_name !~ /staff/o) {
1290 FROM $asset_call_number_table cn,
1291 $metabib_metarecord_source_map_table mrs,
1292 $asset_copy_table cp,
1297 $metabib_record_descriptor ord
1299 WHERE mrs.metarecord = s.metarecord
1300 AND br.id = mrs.source
1301 AND cn.record = mrs.source
1302 AND cp.status = cs.id
1303 AND cp.location = cl.id
1304 AND cp.circ_lib = d.id
1305 AND cp.call_number = cn.id
1306 AND cp.opac_visible IS TRUE
1307 AND cs.opac_visible IS TRUE
1308 AND cl.opac_visible IS TRUE
1309 AND d.opac_visible IS TRUE
1310 AND br.active IS TRUE
1311 AND br.deleted IS FALSE
1312 AND ord.record = mrs.source
1320 ORDER BY 4 $sort_dir
1329 FROM $asset_call_number_table cn,
1330 $asset_copy_table cp,
1331 $metabib_metarecord_source_map_table mrs,
1334 $metabib_record_descriptor ord
1336 WHERE mrs.metarecord = s.metarecord
1337 AND br.id = mrs.source
1338 AND cn.record = mrs.source
1339 AND cn.id = cp.call_number
1340 AND br.deleted IS FALSE
1341 AND cn.deleted IS FALSE
1342 AND ord.record = mrs.source
1343 AND ( cn.owning_lib = d.id
1344 OR ( cp.circ_lib = d.id
1345 AND cp.deleted IS FALSE
1357 FROM $asset_call_number_table cn,
1358 $metabib_metarecord_source_map_table mrs,
1359 $metabib_record_descriptor ord
1360 WHERE mrs.metarecord = s.metarecord
1361 AND cn.record = mrs.source
1362 AND ord.record = mrs.source
1370 ORDER BY 4 $sort_dir
1375 $log->debug("Field Search SQL :: [$select]",DEBUG);
1377 my $recs = $class->db_Main->selectall_arrayref(
1379 (@bonus_values > 0 ? @bonus_values : () ),
1380 ( (!$sort && @bonus_values > 0) ? @bonus_values : () ),
1381 @types, @forms, @aud, @lang, @lit_form,
1382 @types, @forms, @aud, @lang, @lit_form,
1383 ($self->api_name =~ /staff/o ? (@types, @forms, @aud, @lang, @lit_form) : () ) );
1385 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1388 $max = 1 if (!@$recs);
1390 $max = $$_[1] if ($$_[1] > $max);
1393 my $count = scalar(@$recs);
1394 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1395 my ($mrid,$rank,$skip) = @$rec;
1396 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $skip, $count] );
1401 for my $class ( qw/title author subject keyword series/ ) {
1402 __PACKAGE__->register_method(
1403 api_name => "open-ils.storage.metabib.$class.post_filter.search_fts.metarecord",
1404 method => 'postfilter_search_class_fts',
1407 cdbi => "metabib::${class}_field_entry",
1410 __PACKAGE__->register_method(
1411 api_name => "open-ils.storage.metabib.$class.post_filter.search_fts.metarecord.staff",
1412 method => 'postfilter_search_class_fts',
1415 cdbi => "metabib::${class}_field_entry",
1422 my $_cdbi = { title => "metabib::title_field_entry",
1423 author => "metabib::author_field_entry",
1424 subject => "metabib::subject_field_entry",
1425 keyword => "metabib::keyword_field_entry",
1426 series => "metabib::series_field_entry",
1429 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
1430 sub postfilter_search_multi_class_fts {
1435 my $sort = $args{'sort'};
1436 my $sort_dir = $args{sort_dir} || 'DESC';
1437 my $ou = $args{org_unit};
1438 my $ou_type = $args{depth};
1439 my $limit = $args{limit} || 10;;
1440 my $visibility_limit = $args{visibility_limit} || 5000;;
1441 my $offset = $args{offset} || 0;
1444 $ou = actor::org_unit->search( { parent_ou => undef } )->next->id;
1447 if (!defined($args{org_unit})) {
1448 die "No target organizational unit passed to ".$self->api_name;
1451 if (! scalar( keys %{$args{searches}} )) {
1452 die "No search arguments were passed to ".$self->api_name;
1455 my $outer_limit = 1000;
1457 my $limit_clause = '';
1458 my $offset_clause = '';
1460 $limit_clause = "LIMIT $outer_limit";
1461 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
1463 my ($avail_filter,@types,@forms,@lang,@aud,@lit_form,@vformats) = ('');
1464 my ($t_filter, $f_filter, $v_filter) = ('','','');
1465 my ($a_filter, $l_filter, $lf_filter) = ('','','');
1466 my ($ot_filter, $of_filter, $ov_filter) = ('','','');
1467 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
1469 if ($args{available}) {
1470 $avail_filter = ' AND cp.status IN (0,7,12)';
1473 if (my $a = $args{audience}) {
1474 $a = [$a] if (!ref($a));
1477 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
1478 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
1481 if (my $l = $args{language}) {
1482 $l = [$l] if (!ref($l));
1485 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1486 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1489 if (my $f = $args{lit_form}) {
1490 $f = [$f] if (!ref($f));
1493 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1494 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1497 if (my $f = $args{item_form}) {
1498 $f = [$f] if (!ref($f));
1501 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1502 $of_filter = ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1505 if (my $t = $args{item_type}) {
1506 $t = [$t] if (!ref($t));
1509 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1510 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1513 if (my $v = $args{vr_format}) {
1514 $v = [$v] if (!ref($v));
1517 $v_filter = ' AND rd.vr_format IN ('.join(',',map{'?'}@vformats).')';
1518 $ov_filter = ' AND ord.vr_format IN ('.join(',',map{'?'}@vformats).')';
1522 # XXX legacy format and item type support
1523 if ($args{format}) {
1524 my ($t, $f) = split '-', $args{format};
1525 @types = split '', $t;
1526 @forms = split '', $f;
1528 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
1529 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
1533 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1534 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1540 my $descendants = defined($ou_type) ?
1541 "actor.org_unit_descendants($ou, $ou_type)" :
1542 "actor.org_unit_descendants($ou)";
1544 my $search_table_list = '';
1546 my $join_table_list = '';
1549 my $field_table = config::metabib_field->table;
1553 my $prev_search_group;
1554 my $curr_search_group;
1558 for my $search_group (sort keys %{$args{searches}}) {
1559 (my $search_group_name = $search_group) =~ s/\|/_/gso;
1560 ($search_class,$search_field) = split /\|/, $search_group;
1561 $log->debug("Searching class [$search_class] and field [$search_field]",DEBUG);
1563 if ($search_field) {
1564 unless ( $metabib_field = config::metabib_field->search( field_class => $search_class, name => $search_field )->next ) {
1565 $log->warn("Requested class [$search_class] or field [$search_field] does not exist!");
1570 $prev_search_group = $curr_search_group if ($curr_search_group);
1572 $curr_search_group = $search_group_name;
1574 my $class = $_cdbi->{$search_class};
1575 my $search_table = $class->table;
1577 my ($index_col) = $class->columns('FTS');
1578 $index_col ||= 'value';
1581 my $fts = OpenILS::Application::Storage::FTS->compile($search_class => $args{searches}{$search_group}{term}, $search_group_name.'.value', "$search_group_name.$index_col");
1583 my $fts_where = $fts->sql_where_clause;
1584 my @fts_ranks = $fts->fts_rank;
1586 my $SQLstring = join('%',map { lc($_) } $fts->words);
1587 my $REstring = '^' . join('\s+',map { lc($_) } $fts->words) . '\W*$';
1588 my $first_word = lc(($fts->words)[0]).'%';
1590 $_.=" * (SELECT weight FROM $field_table WHERE $search_group_name.field = id)" for (@fts_ranks);
1591 my $rank = join(' + ', @fts_ranks);
1594 $bonus{'keyword'} = [ { "CASE WHEN $search_group_name.value LIKE ? THEN 10 ELSE 1 END" => $SQLstring } ];
1595 $bonus{'author'} = [ { "CASE WHEN $search_group_name.value ILIKE ? THEN 10 ELSE 1 END" => $first_word } ];
1597 $bonus{'series'} = [
1598 { "CASE WHEN $search_group_name.value LIKE ? THEN 1.5 ELSE 1 END" => $first_word },
1599 { "CASE WHEN $search_group_name.value ~ ? THEN 20 ELSE 1 END" => $REstring },
1602 $bonus{'title'} = [ @{ $bonus{'series'} }, @{ $bonus{'keyword'} } ];
1604 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$search_class} };
1605 $bonus_list ||= '1';
1607 push @bonus_lists, $bonus_list;
1608 push @bonus_values, map { values %$_ } @{ $bonus{$search_class} };
1611 #---------------------
1613 $search_table_list .= "$search_table $search_group_name, ";
1614 push @rank_list,$rank;
1615 $fts_list .= " AND $fts_where AND m.source = $search_group_name.source";
1617 if ($metabib_field) {
1618 $join_table_list .= " AND $search_group_name.field = " . $metabib_field->id;
1619 $metabib_field = undef;
1622 if ($prev_search_group) {
1623 $join_table_list .= " AND $prev_search_group.source = $curr_search_group.source";
1627 my $metabib_record_descriptor = metabib::record_descriptor->table;
1628 my $metabib_full_rec = metabib::full_rec->table;
1629 my $metabib_metarecord = metabib::metarecord->table;
1630 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
1631 my $asset_call_number_table = asset::call_number->table;
1632 my $asset_copy_table = asset::copy->table;
1633 my $cs_table = config::copy_status->table;
1634 my $cl_table = asset::copy_location->table;
1635 my $br_table = biblio::record_entry->table;
1636 my $source_table = config::bib_source->table;
1638 my $bonuses = join (' * ', @bonus_lists);
1639 my $relevance = join (' + ', @rank_list);
1640 $relevance = "SUM( ($relevance) * ($bonuses) )/COUNT(DISTINCT smrs.source)";
1642 my $string_default_sort = 'zzzz';
1643 $string_default_sort = 'AAAA' if ($sort_dir eq 'DESC');
1645 my $number_default_sort = '9999';
1646 $number_default_sort = '0000' if ($sort_dir eq 'DESC');
1650 my $secondary_sort = <<" SORT";
1652 SELECT COALESCE(LTRIM(SUBSTR( sfrt.value, COALESCE(SUBSTRING(sfrt.ind2 FROM '\\\\d+'),'0')::INT + 1 )),'$string_default_sort')
1653 FROM $metabib_full_rec sfrt,
1654 $metabib_metarecord mr
1655 WHERE sfrt.record = mr.master_record
1656 AND sfrt.tag = '245'
1657 AND sfrt.subfield = 'a'
1662 my $rank = $relevance;
1663 if (lc($sort) eq 'pubdate') {
1666 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d+'),'$number_default_sort')::INT
1667 FROM $metabib_full_rec frp
1668 WHERE frp.record = mr.master_record
1670 AND frp.subfield = 'c'
1674 } elsif (lc($sort) eq 'create_date') {
1676 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1678 } elsif (lc($sort) eq 'edit_date') {
1680 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = mr.master_record)) )
1682 } elsif (lc($sort) eq 'title') {
1685 SELECT COALESCE(LTRIM(SUBSTR( frt.value, COALESCE(SUBSTRING(frt.ind2 FROM '\\\\d+'),'0')::INT + 1 )),'$string_default_sort')
1686 FROM $metabib_full_rec frt
1687 WHERE frt.record = mr.master_record
1689 AND frt.subfield = 'a'
1693 $secondary_sort = <<" SORT";
1695 SELECT COALESCE(SUBSTRING(sfrp.value FROM '\\\\d+'),'$number_default_sort')::INT
1696 FROM $metabib_full_rec sfrp
1697 WHERE sfrp.record = mr.master_record
1698 AND sfrp.tag = '260'
1699 AND sfrp.subfield = 'c'
1703 } elsif (lc($sort) eq 'author') {
1706 SELECT COALESCE(LTRIM(fra.value),'$string_default_sort')
1707 FROM $metabib_full_rec fra
1708 WHERE fra.record = mr.master_record
1709 AND fra.tag LIKE '1%'
1710 AND fra.subfield = 'a'
1711 ORDER BY fra.tag::text::int
1716 push @bonus_values, @bonus_values;
1721 my $select = <<" SQL";
1722 SELECT m.metarecord,
1724 CASE WHEN COUNT(DISTINCT smrs.source) = 1 THEN FIRST(m.source) ELSE 0 END,
1727 FROM $search_table_list
1728 $metabib_metarecord mr,
1729 $metabib_metarecord_source_map_table m,
1730 $metabib_metarecord_source_map_table smrs
1731 WHERE m.metarecord = smrs.metarecord
1732 AND mr.id = m.metarecord
1735 GROUP BY m.metarecord
1736 -- ORDER BY 4 $sort_dir
1737 LIMIT $visibility_limit
1740 if ($self->api_name !~ /staff/o) {
1747 FROM $asset_call_number_table cn,
1748 $metabib_metarecord_source_map_table mrs,
1749 $asset_copy_table cp,
1754 $metabib_record_descriptor ord
1755 WHERE mrs.metarecord = s.metarecord
1756 AND br.id = mrs.source
1757 AND cn.record = mrs.source
1758 AND cp.status = cs.id
1759 AND cp.location = cl.id
1760 AND cp.circ_lib = d.id
1761 AND cp.call_number = cn.id
1762 AND cp.opac_visible IS TRUE
1763 AND cs.opac_visible IS TRUE
1764 AND cl.opac_visible IS TRUE
1765 AND d.opac_visible IS TRUE
1766 AND br.active IS TRUE
1767 AND br.deleted IS FALSE
1768 AND cp.deleted IS FALSE
1769 AND cn.deleted IS FALSE
1770 AND ord.record = mrs.source
1783 $metabib_metarecord_source_map_table mrs,
1784 $metabib_record_descriptor ord,
1786 WHERE mrs.metarecord = s.metarecord
1787 AND ord.record = mrs.source
1788 AND br.id = mrs.source
1789 AND br.source = src.id
1790 AND src.transcendant IS TRUE
1798 ORDER BY 4 $sort_dir, 5
1805 $metabib_metarecord_source_map_table omrs,
1806 $metabib_record_descriptor ord
1807 WHERE omrs.metarecord = s.metarecord
1808 AND ord.record = omrs.source
1811 FROM $asset_call_number_table cn,
1812 $asset_copy_table cp,
1815 WHERE br.id = omrs.source
1816 AND cn.record = omrs.source
1817 AND br.deleted IS FALSE
1818 AND cn.deleted IS FALSE
1819 AND cp.call_number = cn.id
1820 AND ( cn.owning_lib = d.id
1821 OR ( cp.circ_lib = d.id
1822 AND cp.deleted IS FALSE
1830 FROM $asset_call_number_table cn
1831 WHERE cn.record = omrs.source
1832 AND cn.deleted IS FALSE
1838 $metabib_metarecord_source_map_table mrs,
1839 $metabib_record_descriptor ord,
1841 WHERE mrs.metarecord = s.metarecord
1842 AND br.id = mrs.source
1843 AND br.source = src.id
1844 AND src.transcendant IS TRUE
1860 ORDER BY 4 $sort_dir, 5
1865 $log->debug("Field Search SQL :: [$select]",DEBUG);
1867 my $recs = $_cdbi->{title}->db_Main->selectall_arrayref(
1870 @types, @forms, @vformats, @aud, @lang, @lit_form,
1871 @types, @forms, @vformats, @aud, @lang, @lit_form,
1872 # ($self->api_name =~ /staff/o ? (@types, @forms, @aud, @lang, @lit_form) : () )
1875 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
1878 $max = 1 if (!@$recs);
1880 $max = $$_[1] if ($$_[1] > $max);
1883 my $count = scalar(@$recs);
1884 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
1885 next unless ($$rec[0]);
1886 my ($mrid,$rank,$skip) = @$rec;
1887 $client->respond( [$mrid, sprintf('%0.3f',$rank/$max), $skip, $count] );
1892 __PACKAGE__->register_method(
1893 api_name => "open-ils.storage.metabib.post_filter.multiclass.search_fts.metarecord",
1894 method => 'postfilter_search_multi_class_fts',
1899 __PACKAGE__->register_method(
1900 api_name => "open-ils.storage.metabib.post_filter.multiclass.search_fts.metarecord.staff",
1901 method => 'postfilter_search_multi_class_fts',
1907 __PACKAGE__->register_method(
1908 api_name => "open-ils.storage.metabib.multiclass.search_fts",
1909 method => 'postfilter_search_multi_class_fts',
1914 __PACKAGE__->register_method(
1915 api_name => "open-ils.storage.metabib.multiclass.search_fts.staff",
1916 method => 'postfilter_search_multi_class_fts',
1922 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
1923 sub biblio_search_multi_class_fts {
1928 my $sort = $args{'sort'};
1929 my $sort_dir = $args{sort_dir} || 'DESC';
1930 my $ou = $args{org_unit};
1931 my $ou_type = $args{depth};
1932 my $limit = $args{limit} || 10;
1933 my $pref_lang = $args{prefered_language} || 'eng';
1934 my $visibility_limit = $args{visibility_limit} || 5000;
1935 my $offset = $args{offset} || 0;
1938 $ou = actor::org_unit->search( { parent_ou => undef } )->next->id;
1941 if (! scalar( keys %{$args{searches}} )) {
1942 die "No search arguments were passed to ".$self->api_name;
1945 my $outer_limit = 1000;
1947 my $limit_clause = '';
1948 my $offset_clause = '';
1950 $limit_clause = "LIMIT $outer_limit";
1951 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
1953 my ($avail_filter,@types,@forms,@lang,@aud,@lit_form,@vformats) = ('');
1954 my ($t_filter, $f_filter, $v_filter) = ('','','');
1955 my ($a_filter, $l_filter, $lf_filter) = ('','','');
1956 my ($ot_filter, $of_filter, $ov_filter) = ('','','');
1957 my ($oa_filter, $ol_filter, $olf_filter) = ('','','');
1959 if ($args{available}) {
1960 $avail_filter = ' AND cp.status IN (0,7,12)';
1963 if (my $a = $args{audience}) {
1964 $a = [$a] if (!ref($a));
1967 $a_filter = ' AND rd.audience IN ('.join(',',map{'?'}@aud).')';
1968 $oa_filter = ' AND ord.audience IN ('.join(',',map{'?'}@aud).')';
1971 if (my $l = $args{language}) {
1972 $l = [$l] if (!ref($l));
1975 $l_filter = ' AND rd.item_lang IN ('.join(',',map{'?'}@lang).')';
1976 $ol_filter = ' AND ord.item_lang IN ('.join(',',map{'?'}@lang).')';
1979 if (my $f = $args{lit_form}) {
1980 $f = [$f] if (!ref($f));
1983 $lf_filter = ' AND rd.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1984 $olf_filter = ' AND ord.lit_form IN ('.join(',',map{'?'}@lit_form).')';
1987 if (my $f = $args{item_form}) {
1988 $f = [$f] if (!ref($f));
1991 $f_filter = ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
1992 $of_filter = ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
1995 if (my $t = $args{item_type}) {
1996 $t = [$t] if (!ref($t));
1999 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
2000 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
2003 if (my $v = $args{vr_format}) {
2004 $v = [$v] if (!ref($v));
2007 $v_filter = ' AND rd.vr_format IN ('.join(',',map{'?'}@vformats).')';
2008 $ov_filter = ' AND ord.vr_format IN ('.join(',',map{'?'}@vformats).')';
2011 # XXX legacy format and item type support
2012 if ($args{format}) {
2013 my ($t, $f) = split '-', $args{format};
2014 @types = split '', $t;
2015 @forms = split '', $f;
2017 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
2018 $ot_filter = ' AND ord.item_type IN ('.join(',',map{'?'}@types).')';
2022 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
2023 $of_filter .= ' AND ord.item_form IN ('.join(',',map{'?'}@forms).')';
2028 my $descendants = defined($ou_type) ?
2029 "actor.org_unit_descendants($ou, $ou_type)" :
2030 "actor.org_unit_descendants($ou)";
2032 my $search_table_list = '';
2034 my $join_table_list = '';
2037 my $field_table = config::metabib_field->table;
2041 my $prev_search_group;
2042 my $curr_search_group;
2046 for my $search_group (sort keys %{$args{searches}}) {
2047 (my $search_group_name = $search_group) =~ s/\|/_/gso;
2048 ($search_class,$search_field) = split /\|/, $search_group;
2049 $log->debug("Searching class [$search_class] and field [$search_field]",DEBUG);
2051 if ($search_field) {
2052 unless ( $metabib_field = config::metabib_field->search( field_class => $search_class, name => $search_field )->next ) {
2053 $log->warn("Requested class [$search_class] or field [$search_field] does not exist!");
2058 $prev_search_group = $curr_search_group if ($curr_search_group);
2060 $curr_search_group = $search_group_name;
2062 my $class = $_cdbi->{$search_class};
2063 my $search_table = $class->table;
2065 my ($index_col) = $class->columns('FTS');
2066 $index_col ||= 'value';
2069 my $fts = OpenILS::Application::Storage::FTS->compile($search_class => $args{searches}{$search_group}{term}, $search_group_name.'.value', "$search_group_name.$index_col");
2071 my $fts_where = $fts->sql_where_clause;
2072 my @fts_ranks = $fts->fts_rank;
2074 my $SQLstring = join('%',map { lc($_) } $fts->words) .'%';
2075 my $REstring = '^' . join('\s+',map { lc($_) } $fts->words) . '\W*$';
2076 my $first_word = lc(($fts->words)[0]).'%';
2078 $_.=" * (SELECT weight FROM $field_table WHERE $search_group_name.field = id)" for (@fts_ranks);
2079 my $rank = join(' + ', @fts_ranks);
2082 $bonus{'subject'} = [];
2083 $bonus{'author'} = [ { "CASE WHEN $search_group_name.value ILIKE ? THEN 1.5 ELSE 1 END" => $first_word } ];
2085 $bonus{'keyword'} = [ { "CASE WHEN $search_group_name.value ILIKE ? THEN 10 ELSE 1 END" => $SQLstring } ];
2087 $bonus{'series'} = [
2088 { "CASE WHEN $search_group_name.value ILIKE ? THEN 1.5 ELSE 1 END" => $first_word },
2089 { "CASE WHEN $search_group_name.value ~ ? THEN 20 ELSE 1 END" => $REstring },
2092 $bonus{'title'} = [ @{ $bonus{'series'} }, @{ $bonus{'keyword'} } ];
2095 push @{ $bonus{'title'} }, { "CASE WHEN rd.item_lang = ? THEN 10 ELSE 1 END" => $pref_lang };
2096 push @{ $bonus{'author'} }, { "CASE WHEN rd.item_lang = ? THEN 10 ELSE 1 END" => $pref_lang };
2097 push @{ $bonus{'subject'} }, { "CASE WHEN rd.item_lang = ? THEN 10 ELSE 1 END" => $pref_lang };
2098 push @{ $bonus{'keyword'} }, { "CASE WHEN rd.item_lang = ? THEN 10 ELSE 1 END" => $pref_lang };
2099 push @{ $bonus{'series'} }, { "CASE WHEN rd.item_lang = ? THEN 10 ELSE 1 END" => $pref_lang };
2102 my $bonus_list = join ' * ', map { keys %$_ } @{ $bonus{$search_class} };
2103 $bonus_list ||= '1';
2105 push @bonus_lists, $bonus_list;
2106 push @bonus_values, map { values %$_ } @{ $bonus{$search_class} };
2108 #---------------------
2110 $search_table_list .= "$search_table $search_group_name, ";
2111 push @rank_list,$rank;
2112 $fts_list .= " AND $fts_where AND b.id = $search_group_name.source";
2114 if ($metabib_field) {
2115 $fts_list .= " AND $curr_search_group.field = " . $metabib_field->id;
2116 $metabib_field = undef;
2119 if ($prev_search_group) {
2120 $join_table_list .= " AND $prev_search_group.source = $curr_search_group.source";
2124 my $metabib_record_descriptor = metabib::record_descriptor->table;
2125 my $metabib_full_rec = metabib::full_rec->table;
2126 my $metabib_metarecord = metabib::metarecord->table;
2127 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
2128 my $asset_call_number_table = asset::call_number->table;
2129 my $asset_copy_table = asset::copy->table;
2130 my $cs_table = config::copy_status->table;
2131 my $cl_table = asset::copy_location->table;
2132 my $br_table = biblio::record_entry->table;
2133 my $source_table = config::bib_source->table;
2136 my $bonuses = join (' * ', @bonus_lists);
2137 my $relevance = join (' + ', @rank_list);
2138 $relevance = "AVG( ($relevance) * ($bonuses) )";
2140 my $string_default_sort = 'zzzz';
2141 $string_default_sort = 'AAAA' if ($sort_dir eq 'DESC');
2143 my $number_default_sort = '9999';
2144 $number_default_sort = '0000' if ($sort_dir eq 'DESC');
2146 my $rank = $relevance;
2147 if (lc($sort) eq 'pubdate') {
2150 SELECT COALESCE(SUBSTRING(frp.value FROM '\\\\d{4}'),'$number_default_sort')::INT
2151 FROM $metabib_full_rec frp
2152 WHERE frp.record = b.id
2154 AND frp.subfield = 'c'
2158 } elsif (lc($sort) eq 'create_date') {
2160 ( FIRST (( SELECT create_date FROM $br_table rbr WHERE rbr.id = b.id)) )
2162 } elsif (lc($sort) eq 'edit_date') {
2164 ( FIRST (( SELECT edit_date FROM $br_table rbr WHERE rbr.id = b.id)) )
2166 } elsif (lc($sort) eq 'title') {
2169 SELECT COALESCE(LTRIM(SUBSTR( frt.value, COALESCE(SUBSTRING(frt.ind2 FROM '\\\\d+'),'0')::INT + 1 )),'$string_default_sort')
2170 FROM $metabib_full_rec frt
2171 WHERE frt.record = b.id
2173 AND frt.subfield = 'a'
2177 } elsif (lc($sort) eq 'author') {
2180 SELECT COALESCE(LTRIM(fra.value),'$string_default_sort')
2181 FROM $metabib_full_rec fra
2182 WHERE fra.record = b.id
2183 AND fra.tag LIKE '1%'
2184 AND fra.subfield = 'a'
2185 ORDER BY fra.tag::text::int
2190 push @bonus_values, @bonus_values;
2195 my $select = <<" SQL";
2200 FROM $search_table_list
2201 $metabib_record_descriptor rd,
2204 WHERE rd.record = b.id
2205 AND b.active IS TRUE
2206 AND b.deleted IS FALSE
2215 GROUP BY b.id, b.source
2216 ORDER BY 3 $sort_dir
2217 LIMIT $visibility_limit
2220 if ($self->api_name !~ /staff/o) {
2225 LEFT OUTER JOIN $source_table src ON (s.source = src.id)
2228 FROM $asset_call_number_table cn,
2229 $asset_copy_table cp,
2233 WHERE cn.record = s.id
2234 AND cp.status = cs.id
2235 AND cp.location = cl.id
2236 AND cp.call_number = cn.id
2237 AND cp.opac_visible IS TRUE
2238 AND cs.opac_visible IS TRUE
2239 AND cl.opac_visible IS TRUE
2240 AND d.opac_visible IS TRUE
2241 AND cp.deleted IS FALSE
2242 AND cn.deleted IS FALSE
2243 AND cp.circ_lib = d.id
2247 OR src.transcendant IS TRUE
2248 ORDER BY 3 $sort_dir
2255 LEFT OUTER JOIN $source_table src ON (s.source = src.id)
2258 FROM $asset_call_number_table cn,
2259 $asset_copy_table cp,
2261 WHERE cn.record = s.id
2262 AND cp.call_number = cn.id
2263 AND cn.deleted IS FALSE
2264 AND cp.circ_lib = d.id
2265 AND cp.deleted IS FALSE
2271 FROM $asset_call_number_table cn
2272 WHERE cn.record = s.id
2275 OR src.transcendant IS TRUE
2276 ORDER BY 3 $sort_dir
2281 $log->debug("Field Search SQL :: [$select]",DEBUG);
2283 my $recs = $_cdbi->{title}->db_Main->selectall_arrayref(
2285 @bonus_values, @types, @forms, @vformats, @aud, @lang, @lit_form
2288 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
2290 my $count = scalar(@$recs);
2291 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
2292 next unless ($$rec[0]);
2293 my ($mrid,$rank) = @$rec;
2294 $client->respond( [$mrid, sprintf('%0.3f',$rank), $count] );
2299 __PACKAGE__->register_method(
2300 api_name => "open-ils.storage.biblio.multiclass.search_fts.record",
2301 method => 'biblio_search_multi_class_fts',
2306 __PACKAGE__->register_method(
2307 api_name => "open-ils.storage.biblio.multiclass.search_fts.record.staff",
2308 method => 'biblio_search_multi_class_fts',
2316 __PACKAGE__->register_method(
2317 api_name => "open-ils.storage.biblio.multiclass.search_fts",
2318 method => 'biblio_search_multi_class_fts',
2323 __PACKAGE__->register_method(
2324 api_name => "open-ils.storage.biblio.multiclass.search_fts.staff",
2325 method => 'biblio_search_multi_class_fts',
2333 my $default_preferred_language;
2334 my $default_preferred_language_weight;
2336 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
2342 if (!$locale_map{COMPLETE}) {
2344 my @locales = config::i18n_locale->search_where({ code => { '<>' => '' } });
2345 for my $locale ( @locales ) {
2346 $locale_map{$locale->code} = $locale->marc_code;
2348 $locale_map{COMPLETE} = 1;
2352 if (!$default_preferred_language) {
2354 $default_preferred_language = OpenSRF::Utils::SettingsClient
2357 apps => 'open-ils.storage' => app_settings => 'default_preferred_language'
2362 if (!$default_preferred_language_weight) {
2364 $default_preferred_language_weight = OpenSRF::Utils::SettingsClient
2367 apps => 'open-ils.storage' => app_settings => 'default_preferred_language_weight'
2372 # inclusion, exclusion, delete_adjusted_inclusion, delete_adjusted_exclusion
2373 my $estimation_strategy = $args{estimation_strategy} || 'inclusion';
2375 my $ou = $args{org_unit};
2376 my $limit = $args{limit} || 10;
2377 my $offset = $args{offset} || 0;
2380 $ou = actor::org_unit->search( { parent_ou => undef } )->next->id;
2383 if (! scalar( keys %{$args{searches}} )) {
2384 die "No search arguments were passed to ".$self->api_name;
2387 my (@between,@statuses,@locations,@types,@forms,@lang,@aud,@lit_form,@vformats,@bib_level);
2389 if (!defined($args{preferred_language})) {
2390 my $ses_locale = $client->session ? $client->session->session_locale : $default_preferred_language;
2391 $args{preferred_language} =
2392 $locale_map{ $ses_locale } || 'eng';
2395 if (!defined($args{preferred_language_weight})) {
2396 $args{preferred_language_weight} = $default_preferred_language_weight || 2;
2399 if ($args{available}) {
2400 @statuses = (0,7,12);
2403 if (my $s = $args{locations}) {
2404 $s = [$s] if (!ref($s));
2408 if (my $b = $args{between}) {
2409 if (ref($b) && @$b == 2) {
2414 if (my $s = $args{statuses}) {
2415 $s = [$s] if (!ref($s));
2419 if (my $a = $args{audience}) {
2420 $a = [$a] if (!ref($a));
2424 if (my $l = $args{language}) {
2425 $l = [$l] if (!ref($l));
2429 if (my $f = $args{lit_form}) {
2430 $f = [$f] if (!ref($f));
2434 if (my $f = $args{item_form}) {
2435 $f = [$f] if (!ref($f));
2439 if (my $t = $args{item_type}) {
2440 $t = [$t] if (!ref($t));
2444 if (my $b = $args{bib_level}) {
2445 $b = [$b] if (!ref($b));
2449 if (my $v = $args{vr_format}) {
2450 $v = [$v] if (!ref($v));
2454 # XXX legacy format and item type support
2455 if ($args{format}) {
2456 my ($t, $f) = split '-', $args{format};
2457 @types = split '', $t;
2458 @forms = split '', $f;
2461 my %stored_proc_search_args;
2462 for my $search_group (sort keys %{$args{searches}}) {
2463 (my $search_group_name = $search_group) =~ s/\|/_/gso;
2464 my ($search_class,$search_field) = split /\|/, $search_group;
2465 $log->debug("Searching class [$search_class] and field [$search_field]",DEBUG);
2467 if ($search_field) {
2468 unless ( config::metabib_field->search( field_class => $search_class, name => $search_field )->next ) {
2469 $log->warn("Requested class [$search_class] or field [$search_field] does not exist!");
2474 my $class = $_cdbi->{$search_class};
2475 my $search_table = $class->table;
2477 my ($index_col) = $class->columns('FTS');
2478 $index_col ||= 'value';
2481 my $fts = OpenILS::Application::Storage::FTS->compile(
2482 $search_class => $args{searches}{$search_group}{term},
2483 $search_group_name.'.value',
2484 "$search_group_name.$index_col"
2486 $fts->sql_where_clause; # this builds the ranks for us
2488 my @fts_ranks = $fts->fts_rank;
2489 my @fts_queries = $fts->fts_query;
2490 my @phrases = map { lc($_) } $fts->phrases;
2491 my @words = map { lc($_) } $fts->words;
2493 $stored_proc_search_args{$search_group} = {
2494 fts_rank => \@fts_ranks,
2495 fts_query => \@fts_queries,
2496 phrase => \@phrases,
2502 my $param_search_ou = $ou;
2503 my $param_depth = $args{depth}; $param_depth = 'NULL' unless (defined($param_depth) and length($param_depth) > 0 );
2504 my $param_searches = OpenSRF::Utils::JSON->perl2JSON( \%stored_proc_search_args ); $param_searches =~ s/\$//go; $param_searches = '$$'.$param_searches.'$$';
2505 my $param_statuses = '$${' . join(',', map { s/\$//go; "\"$_\""} @statuses) . '}$$';
2506 my $param_locations = '$${' . join(',', map { s/\$//go; "\"$_\""} @locations) . '}$$';
2507 my $param_audience = '$${' . join(',', map { s/\$//go; "\"$_\"" } @aud) . '}$$';
2508 my $param_language = '$${' . join(',', map { s/\$//go; "\"$_\""} @lang) . '}$$';
2509 my $param_lit_form = '$${' . join(',', map { s/\$//go; "\"$_\"" } @lit_form) . '}$$';
2510 my $param_types = '$${' . join(',', map { s/\$//go; "\"$_\""} @types) . '}$$';
2511 my $param_forms = '$${' . join(',', map { s/\$//go; "\"$_\""} @forms) . '}$$';
2512 my $param_vformats = '$${' . join(',', map { s/\$//go; "\"$_\"" } @vformats) . '}$$';
2513 my $param_bib_level = '$${' . join(',', map { s/\$//go; "\"$_\"" } @bib_level) . '}$$';
2514 my $param_before = $args{before}; $param_before = 'NULL' unless (defined($param_before) and length($param_before) > 0 );
2515 my $param_after = $args{after}; $param_after = 'NULL' unless (defined($param_after) and length($param_after) > 0 );
2516 my $param_during = $args{during}; $param_during = 'NULL' unless (defined($param_during) and length($param_during) > 0 );
2517 my $param_between = '$${"' . join('","', map { int($_) } @between) . '"}$$';
2518 my $param_pref_lang = $args{preferred_language}; $param_pref_lang =~ s/\$//go; $param_pref_lang = '$$'.$param_pref_lang.'$$';
2519 my $param_pref_lang_multiplier = $args{preferred_language_weight}; $param_pref_lang_multiplier ||= 'NULL';
2520 my $param_sort = $args{'sort'}; $param_sort =~ s/\$//go; $param_sort = '$$'.$param_sort.'$$';
2521 my $param_sort_desc = defined($args{sort_dir}) && $args{sort_dir} =~ /^d/io ? "'t'" : "'f'";
2522 my $metarecord = $self->api_name =~ /metabib/o ? "'t'" : "'f'";
2523 my $staff = $self->api_name =~ /staff/o ? "'t'" : "'f'";
2524 my $param_rel_limit = $args{core_limit}; $param_rel_limit ||= 'NULL';
2525 my $param_chk_limit = $args{check_limit}; $param_chk_limit ||= 'NULL';
2526 my $param_skip_chk = $args{skip_check}; $param_skip_chk ||= 'NULL';
2528 my $sth = metabib::metarecord_source_map->db_Main->prepare(<<" SQL");
2530 FROM search.staged_fts(
2531 $param_search_ou\:\:INT,
2532 $param_depth\:\:INT,
2533 $param_searches\:\:TEXT,
2534 $param_statuses\:\:INT[],
2535 $param_locations\:\:INT[],
2536 $param_audience\:\:TEXT[],
2537 $param_language\:\:TEXT[],
2538 $param_lit_form\:\:TEXT[],
2539 $param_types\:\:TEXT[],
2540 $param_forms\:\:TEXT[],
2541 $param_vformats\:\:TEXT[],
2542 $param_bib_level\:\:TEXT[],
2543 $param_before\:\:TEXT,
2544 $param_after\:\:TEXT,
2545 $param_during\:\:TEXT,
2546 $param_between\:\:TEXT[],
2547 $param_pref_lang\:\:TEXT,
2548 $param_pref_lang_multiplier\:\:REAL,
2549 $param_sort\:\:TEXT,
2550 $param_sort_desc\:\:BOOL,
2551 $metarecord\:\:BOOL,
2553 $param_rel_limit\:\:INT,
2554 $param_chk_limit\:\:INT,
2555 $param_skip_chk\:\:INT
2561 my $recs = $sth->fetchall_arrayref({});
2562 my $summary_row = pop @$recs;
2564 my $total = $$summary_row{total};
2565 my $checked = $$summary_row{checked};
2566 my $visible = $$summary_row{visible};
2567 my $deleted = $$summary_row{deleted};
2568 my $excluded = $$summary_row{excluded};
2570 my $estimate = $visible;
2571 if ( $total > $checked && $checked ) {
2573 $$summary_row{hit_estimate} = FTS_paging_estimate($self, $client, $checked, $visible, $excluded, $deleted, $total);
2574 $estimate = $$summary_row{estimated_hit_count} = $$summary_row{hit_estimate}{$estimation_strategy};
2578 delete $$summary_row{id};
2579 delete $$summary_row{rel};
2580 delete $$summary_row{record};
2582 $client->respond( $summary_row );
2584 $log->debug("Search yielded ".scalar(@$recs)." checked, visible results with an approximate visible total of $estimate.",DEBUG);
2586 for my $rec (@$recs[$offset .. $offset + $limit - 1]) {
2587 delete $$rec{checked};
2588 delete $$rec{visible};
2589 delete $$rec{excluded};
2590 delete $$rec{deleted};
2591 delete $$rec{total};
2592 $$rec{rel} = sprintf('%0.3f',$$rec{rel});
2594 $client->respond( $rec );
2598 __PACKAGE__->register_method(
2599 api_name => "open-ils.storage.biblio.multiclass.staged.search_fts",
2600 method => 'staged_fts',
2605 __PACKAGE__->register_method(
2606 api_name => "open-ils.storage.biblio.multiclass.staged.search_fts.staff",
2607 method => 'staged_fts',
2612 __PACKAGE__->register_method(
2613 api_name => "open-ils.storage.metabib.multiclass.staged.search_fts",
2614 method => 'staged_fts',
2619 __PACKAGE__->register_method(
2620 api_name => "open-ils.storage.metabib.multiclass.staged.search_fts.staff",
2621 method => 'staged_fts',
2627 sub FTS_paging_estimate {
2631 my $checked = shift;
2632 my $visible = shift;
2633 my $excluded = shift;
2634 my $deleted = shift;
2637 my $deleted_ratio = $deleted / $checked;
2638 my $delete_adjusted_total = $total - ( $total * $deleted_ratio );
2640 my $exclusion_ratio = $excluded / $checked;
2641 my $delete_adjusted_exclusion_ratio = $excluded / ($checked - $deleted);
2643 my $inclusion_ratio = $visible / $checked;
2644 my $delete_adjusted_inclusion_ratio = $visible / ($checked - $deleted);
2647 exclusion => int($delete_adjusted_total - ( $delete_adjusted_total * $exclusion_ratio )),
2648 inclusion => int($delete_adjusted_total * $inclusion_ratio),
2649 delete_adjusted_exclusion => int($delete_adjusted_total - ( $delete_adjusted_total * $delete_adjusted_exclusion_ratio )),
2650 delete_adjusted_inclusion => int($delete_adjusted_total * $delete_adjusted_inclusion_ratio)
2653 __PACKAGE__->register_method(
2654 api_name => "open-ils.storage.fts_paging_estimate",
2655 method => 'staged_fts',
2661 Hash of estimation values based on four variant estimation strategies:
2662 exclusion -- Estimate based on the ratio of excluded records on the current superpage;
2663 inclusion -- Estimate based on the ratio of visible records on the current superpage;
2664 delete_adjusted_exclusion -- Same as exclusion strategy, but the ratio is adjusted by deleted count;
2665 delete_adjusted_inclusion -- Same as inclusion strategy, but the ratio is adjusted by deleted count;
2668 Helper method used to determin the approximate number of
2669 hits for a search that spans multiple superpages. For
2670 sparse superpages, the inclusion estimate will likely be the
2671 best estimate. The exclusion strategy is the original, but
2672 inclusion is the default.
2675 { name => 'checked',
2676 desc => 'Number of records check -- nominally the size of a superpage, or a remaining amount from the last superpage.',
2679 { name => 'visible',
2680 desc => 'Number of records visible to the search location on the current superpage.',
2683 { name => 'excluded',
2684 desc => 'Number of records excluded from the search location on the current superpage.',
2687 { name => 'deleted',
2688 desc => 'Number of deleted records on the current superpage.',
2692 desc => 'Total number of records up to check_limit (superpage_size * max_superpages).',
2705 my $term = $$args{term};
2706 my $limit = $$args{max} || 1;
2707 my $min = $$args{min} || 1;
2708 my @classes = @{$$args{class}};
2710 $limit = $min if ($min > $limit);
2713 @classes = ( qw/ title author subject series keyword / );
2717 my $bre_table = biblio::record_entry->table;
2718 my $cn_table = asset::call_number->table;
2719 my $cp_table = asset::copy->table;
2721 for my $search_class ( @classes ) {
2723 my $class = $_cdbi->{$search_class};
2724 my $search_table = $class->table;
2726 my ($index_col) = $class->columns('FTS');
2727 $index_col ||= 'value';
2730 my $where = OpenILS::Application::Storage::FTS
2731 ->compile($search_class => $term, $search_class.'.value', "$search_class.$index_col")
2735 SELECT COUNT(DISTINCT X.source)
2736 FROM (SELECT $search_class.source
2737 FROM $search_table $search_class
2738 JOIN $bre_table b ON (b.id = $search_class.source)
2743 HAVING COUNT(DISTINCT X.source) >= $min;
2746 my $res = $class->db_Main->selectrow_arrayref( $SQL );
2747 $matches{$search_class} = $res ? $res->[0] : 0;
2752 __PACKAGE__->register_method(
2753 api_name => "open-ils.storage.search.xref",
2754 method => 'xref_count',