1 package OpenILS::Application::Storage::Publisher::metabib;
2 use base qw/OpenILS::Application::Storage::Publisher/;
4 use OpenSRF::EX qw/:try/;
5 use OpenILS::Application::Storage::FTS;
6 use OpenILS::Utils::Fieldmapper;
7 use OpenSRF::Utils::Logger qw/:level/;
8 use OpenSRF::Utils::Cache;
10 use Digest::MD5 qw/md5_hex/;
13 my $log = 'OpenSRF::Utils::Logger';
17 # need to order record IDs by:
18 # 1) format - text, movie, sound, software, images, maps, mixed, music, 3d
19 # 2) proximity --- XXX Can't do it cheap...
21 sub ordered_records_from_metarecord {
30 my ($t, $f) = split '-', $formats;
31 @types = split '', $t;
32 @forms = split '', $f;
35 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
36 $copies_visible = '' if ($self->api_name =~ /staff/o);
38 my $sm_table = metabib::metarecord_source_map->table;
39 my $rd_table = metabib::record_descriptor->table;
40 my $cn_table = asset::call_number->table;
41 my $cl_table = asset::copy_location->table;
42 my $cp_table = asset::copy->table;
43 my $cs_table = config::copy_status->table;
44 my $out_table = actor::org_unit_type->table;
54 if ($copies_visible) {
56 sum((SELECT count(cp.id)
58 JOIN $cs_table cs ON (cp.status = cs.id)
59 JOIN $cl_table cl ON (cp.location = cl.id)
60 WHERE cn.id = cp.call_number
68 if ($copies_visible) {
73 WHERE rd.record = sm.source
74 AND cn.record = rd.record
81 WHERE rd.record = sm.source
87 GROUP BY rd.record, rd.item_type, rd.item_form
90 WHEN rd.item_type IS NULL -- default
92 WHEN rd.item_type = '' -- default
94 WHEN rd.item_type IN ('a','t') -- books
96 WHEN rd.item_type = 'g' -- movies
98 WHEN rd.item_type IN ('i','j') -- sound recordings
100 WHEN rd.item_type = 'm' -- software
102 WHEN rd.item_type = 'k' -- images
104 WHEN rd.item_type IN ('e','f') -- maps
106 WHEN rd.item_type IN ('o','p') -- mixed
108 WHEN rd.item_type IN ('c','d') -- music
110 WHEN rd.item_type = 'r' -- 3d
117 if ($copies_visible) {
118 $sql .= ' WHERE x.count > 0'
122 $sql .= ' AND x.item_type IN ('.join(',',map{'?'}@types).')';
126 $sql .= ' AND x.item_form IN ('.join(',',map{'?'}@forms).')';
129 my $sth = metabib::metarecord_source_map->db_Main->prepare_cached($sql);
130 $sth->execute("$mr", @types, @forms);
131 while ( my $row = $sth->fetchrow_arrayref ) {
132 $client->respond( $$row[0] );
137 __PACKAGE__->register_method(
138 api_name => 'open-ils.storage.ordered.metabib.metarecord.records',
139 method => 'ordered_records_from_metarecord',
144 __PACKAGE__->register_method(
145 api_name => 'open-ils.storage.ordered.metabib.metarecord.records.staff',
146 method => 'ordered_records_from_metarecord',
153 sub metarecord_copy_count {
159 my $sm_table = metabib::metarecord_source_map->table;
160 my $cn_table = asset::call_number->table;
161 my $cp_table = asset::copy->table;
162 my $cl_table = asset::copy_location->table;
163 my $cs_table = config::copy_status->table;
164 my $out_table = actor::org_unit_type->table;
165 my $descendants = "actor.org_unit_descendants(u.id)";
166 my $ancestors = "actor.org_unit_ancestors(?)";
168 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
169 $copies_visible = '' if ($self->api_name =~ /staff/o);
177 JOIN $cn_table cn ON (cn.record = r.source)
178 JOIN $cp_table cp ON (cn.id = cp.call_number)
179 JOIN $cs_table cs ON (cp.status = cs.id)
180 JOIN $cl_table cl ON (cp.location = cl.id)
181 JOIN $descendants a ON (cp.circ_lib = a.id)
182 WHERE r.metarecord = ?
189 JOIN $cn_table cn ON (cn.record = r.source)
190 JOIN $cp_table cp ON (cn.id = cp.call_number)
191 JOIN $cs_table cs ON (cp.status = cs.id)
192 JOIN $cl_table cl ON (cp.location = cl.id)
193 JOIN $descendants a ON (cp.circ_lib = a.id)
194 WHERE r.metarecord = ?
201 JOIN $out_table t ON (u.ou_type = t.id)
205 my $sth = metabib::metarecord_source_map->db_Main->prepare_cached($sql);
206 $sth->execute(''.$args{metarecord}, ''.$args{metarecord}, ''.$args{org_unit});
207 while ( my $row = $sth->fetchrow_hashref ) {
208 $client->respond( $row );
212 __PACKAGE__->register_method(
213 api_name => 'open-ils.storage.metabib.metarecord.copy_count',
214 method => 'metarecord_copy_count',
219 __PACKAGE__->register_method(
220 api_name => 'open-ils.storage.metabib.metarecord.copy_count.staff',
221 method => 'metarecord_copy_count',
227 sub search_full_rec {
233 my $term = $args{term};
234 my $limiters = $args{restrict};
236 my ($index_col) = metabib::full_rec->columns('FTS');
237 $index_col ||= 'value';
238 my $search_table = metabib::full_rec->table;
240 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
242 my $fts_where = $fts->sql_where_clause();
243 my @fts_ranks = $fts->fts_rank;
245 my $rank = join(' + ', @fts_ranks);
249 for my $limit (@$limiters) {
250 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
251 push @binds, $$limit{tag}, $$limit{subfield};
252 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
254 my $where = join(' OR ', @wheres);
256 my $select = "SELECT record, sum($rank) FROM $search_table WHERE $where GROUP BY 1 ORDER BY 2 DESC;";
258 $log->debug("Search SQL :: [$select]",DEBUG);
260 my $recs = metabib::full_rec->db_Main->selectall_arrayref($select, {}, @binds);
261 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
263 $client->respond($_) for (@$recs);
266 __PACKAGE__->register_method(
267 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.value',
268 method => 'search_full_rec',
273 __PACKAGE__->register_method(
274 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.index_vector',
275 method => 'search_full_rec',
282 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
283 sub search_class_fts {
288 my $term = $args{term};
289 my $ou = $args{org_unit};
290 my $ou_type = $args{depth};
291 my $limit = $args{limit};
292 my $offset = $args{offset};
294 my $limit_clause = '';
295 my $offset_clause = '';
297 $limit_clause = "LIMIT $limit" if (defined $limit and int($limit) > 0);
298 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
301 my ($t_filter, $f_filter) = ('','');
304 my ($t, $f) = split '-', $args{format};
305 @types = split '', $t;
306 @forms = split '', $f;
308 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
312 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
318 my $descendants = defined($ou_type) ?
319 "actor.org_unit_descendants($ou, $ou_type)" :
320 "actor.org_unit_descendants($ou)";
322 my $class = $self->{cdbi};
323 my $search_table = $class->table;
325 my $metabib_record_descriptor = metabib::record_descriptor->table;
326 my $metabib_metarecord = metabib::metarecord->table;
327 my $metabib_full_rec = metabib::full_rec->table;
328 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
329 my $asset_call_number_table = asset::call_number->table;
330 my $asset_copy_table = asset::copy->table;
331 my $cs_table = config::copy_status->table;
332 my $cl_table = asset::copy_location->table;
334 my ($index_col) = $class->columns('FTS');
335 $index_col ||= 'value';
337 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
339 my $fts_where = $fts->sql_where_clause;
340 my @fts_ranks = $fts->fts_rank;
342 my $rank = join(' + ', @fts_ranks);
344 my $has_vols = 'AND cn.owning_lib = d.id';
345 my $has_copies = 'AND cp.call_number = cn.id';
346 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
348 my $visible_count = ', count(DISTINCT cp.id)';
349 my $visible_count_test = 'HAVING count(DISTINCT cp.id) > 0';
351 if ($self->api_name =~ /staff/o) {
352 $copies_visible = '';
353 $visible_count_test = '';
354 $has_copies = '' if ($ou_type == 0);
355 $has_vols = '' if ($ou_type == 0);
358 my $rank_calc = <<" RANK";
360 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
361 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
362 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
363 )/COUNT(m.source)), MIN(COALESCE(CHAR_LENGTH(f.value),1))
366 $rank_calc = ',1 , 1' if ($self->api_name =~ /unordered/o);
368 if ($copies_visible) {
370 SELECT m.metarecord $rank_calc $visible_count
371 FROM $search_table f,
372 $metabib_metarecord_source_map_table m,
373 $asset_call_number_table cn,
374 $asset_copy_table cp,
377 $metabib_record_descriptor rd,
380 AND m.source = f.source
381 AND cn.record = m.source
382 AND rd.record = m.source
383 AND cp.status = cs.id
384 AND cp.location = cl.id
390 GROUP BY m.metarecord $visible_count_test
392 $limit_clause $offset_clause
396 SELECT m.metarecord $rank_calc, 0
397 FROM $search_table f,
398 $metabib_metarecord_source_map_table m,
399 $metabib_record_descriptor rd
401 AND m.source = f.source
402 AND rd.record = m.source
407 $limit_clause $offset_clause
411 $log->debug("Field Search SQL :: [$select]",DEBUG);
413 my $SQLstring = join('%',$fts->words);
414 my $REstring = join('\\s+',$fts->words);
415 my $first_word = ($fts->words)[0].'%';
416 my $recs = ($self->api_name =~ /unordered/o) ?
417 $class->db_Main->selectall_arrayref($select, {}, @types, @forms) :
418 $class->db_Main->selectall_arrayref($select, {},
419 '%'.lc($SQLstring).'%', # phrase order match
420 lc($first_word), # first word match
421 '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match
425 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
427 $client->respond($_) for (map { [@$_[0,1,3]] } @$recs);
431 for my $class ( qw/title author subject keyword series/ ) {
432 __PACKAGE__->register_method(
433 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord",
434 method => 'search_class_fts',
437 cdbi => "metabib::${class}_field_entry",
440 __PACKAGE__->register_method(
441 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.unordered",
442 method => 'search_class_fts',
445 cdbi => "metabib::${class}_field_entry",
448 __PACKAGE__->register_method(
449 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff",
450 method => 'search_class_fts',
453 cdbi => "metabib::${class}_field_entry",
456 __PACKAGE__->register_method(
457 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff.unordered",
458 method => 'search_class_fts',
461 cdbi => "metabib::${class}_field_entry",
466 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
467 sub search_class_fts_count {
472 my $term = $args{term};
473 my $ou = $args{org_unit};
474 my $ou_type = $args{depth};
475 my $limit = $args{limit} || 100;
476 my $offset = $args{offset} || 0;
478 my $descendants = defined($ou_type) ?
479 "actor.org_unit_descendants($ou, $ou_type)" :
480 "actor.org_unit_descendants($ou)";
483 my ($t_filter, $f_filter) = ('','');
486 my ($t, $f) = split '-', $args{format};
487 @types = split '', $t;
488 @forms = split '', $f;
490 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
494 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
499 (my $search_class = $self->api_name) =~ s/.*metabib.(\w+).search_fts.*/$1/o;
501 my $class = $self->{cdbi};
502 my $search_table = $class->table;
504 my $metabib_record_descriptor = metabib::record_descriptor->table;
505 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
506 my $asset_call_number_table = asset::call_number->table;
507 my $asset_copy_table = asset::copy->table;
508 my $cs_table = config::copy_status->table;
509 my $cl_table = asset::copy_location->table;
511 my ($index_col) = $class->columns('FTS');
512 $index_col ||= 'value';
514 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
516 my $fts_where = $fts->sql_where_clause;
518 my $has_vols = 'AND cn.owning_lib = d.id';
519 my $has_copies = 'AND cp.call_number = cn.id';
520 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
521 if ($self->api_name =~ /staff/o) {
522 $copies_visible = '';
523 $has_vols = '' if ($ou_type == 0);
524 $has_copies = '' if ($ou_type == 0);
527 # XXX test an "EXISTS version of descendant checking...
529 if ($copies_visible) {
531 SELECT count(distinct m.metarecord)
532 FROM $search_table f,
533 $metabib_metarecord_source_map_table m,
534 $asset_call_number_table cn,
535 $asset_copy_table cp,
538 $metabib_record_descriptor rd,
541 AND m.source = f.source
542 AND cn.record = m.source
543 AND rd.record = m.source
544 AND cp.status = cs.id
545 AND cp.location = cl.id
554 SELECT count(distinct m.metarecord)
555 FROM $search_table f,
556 $metabib_metarecord_source_map_table m,
557 $metabib_record_descriptor rd
559 AND m.source = f.source
560 AND rd.record = m.source
566 $log->debug("Field Search Count SQL :: [$select]",DEBUG);
568 my $recs = $class->db_Main->selectrow_arrayref($select, {}, @types, @forms)->[0];
570 $log->debug("Count Search yielded $recs results.",DEBUG);
575 for my $class ( qw/title author subject keyword series/ ) {
576 __PACKAGE__->register_method(
577 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count",
578 method => 'search_class_fts_count',
581 cdbi => "metabib::${class}_field_entry",
584 __PACKAGE__->register_method(
585 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count.staff",
586 method => 'search_class_fts_count',
589 cdbi => "metabib::${class}_field_entry",
598 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
599 sub new_search_class_fts {
604 my $term = $args{term};
605 my $ou = $args{org_unit};
606 my $ou_type = $args{depth};
607 my $limit = $args{limit};
608 my $offset = $args{offset} ||= 0;
610 my $limit_clause = '';
611 my $offset_clause = '';
613 $limit_clause = "LIMIT $limit" if (defined $limit and int($limit) > 0);
614 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
617 my ($t_filter, $f_filter) = ('','');
620 my ($t, $f) = split '-', $args{format};
621 @types = split '', $t;
622 @forms = split '', $f;
624 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
628 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
634 my $descendants = defined($ou_type) ?
635 "actor.org_unit_descendants($ou, $ou_type)" :
636 "actor.org_unit_descendants($ou)";
638 my $class = $self->{cdbi};
639 my $search_table = $class->table;
641 my $metabib_record_descriptor = metabib::record_descriptor->table;
642 my $metabib_metarecord = metabib::metarecord->table;
643 my $metabib_full_rec = metabib::full_rec->table;
644 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
645 my $asset_call_number_table = asset::call_number->table;
646 my $asset_copy_table = asset::copy->table;
647 my $cs_table = config::copy_status->table;
648 my $cl_table = asset::copy_location->table;
650 my ($index_col) = $class->columns('FTS');
651 $index_col ||= 'value';
653 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
655 my $fts_where = $fts->sql_where_clause;
656 my @fts_ranks = $fts->fts_rank;
658 my $rank = join(' + ', @fts_ranks);
660 my $has_vols = 'AND cn.owning_lib = d.id';
661 my $has_copies = 'AND cp.call_number = cn.id';
662 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE';
664 my $visible_count = ', count(DISTINCT cp.id)';
665 my $visible_count_test = 'HAVING count(DISTINCT cp.id) > 0';
667 if ($self->api_name =~ /staff/o) {
668 $copies_visible = '';
669 $visible_count_test = '';
670 $has_copies = '' if ($ou_type == 0);
671 $has_vols = '' if ($ou_type == 0);
674 my $rank_calc = <<" RANK";
676 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
677 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
678 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
679 )/COUNT(m.source)), MIN(COALESCE(CHAR_LENGTH(f.value),1))
682 $rank_calc = ',1 , 1' if ($self->api_name =~ /unordered/o);
684 if ($copies_visible) {
686 SELECT m.metarecord $rank_calc $visible_count
687 FROM $search_table f,
688 $metabib_metarecord_source_map_table m,
689 $asset_call_number_table cn,
690 $asset_copy_table cp,
693 $metabib_record_descriptor rd,
696 AND m.source = f.source
697 AND cn.record = m.source
698 AND rd.record = m.source
699 AND cp.status = cs.id
700 AND cp.location = cl.id
706 GROUP BY m.metarecord $visible_count_test
711 SELECT m.metarecord $rank_calc, 0
712 FROM $search_table f,
713 $metabib_metarecord_source_map_table m,
714 $metabib_record_descriptor rd
716 AND m.source = f.source
717 AND rd.record = m.source
725 $log->debug("Field Search SQL :: [$select]",DEBUG);
727 my $SQLstring = join('%',$fts->words);
728 my $REstring = join('\\s+',$fts->words);
729 my $first_word = ($fts->words)[0].'%';
730 my $recs = ($self->api_name =~ /unordered/o) ?
731 $class->db_Main->selectall_arrayref($select, {}, @types, @forms) :
732 $class->db_Main->selectall_arrayref($select, {},
733 '%'.lc($SQLstring).'%', # phrase order match
734 lc($first_word), # first word match
735 '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match
739 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
741 my $count = scalar(@$recs);
742 $client->respond($_) for (map { [@$_[0,1,3],$count] } @$recs[$offset .. $offset + $limit]);
746 for my $class ( qw/title author subject keyword series/ ) {
747 __PACKAGE__->register_method(
748 api_name => "open-ils.storage.metabib.$class.new_search_fts.metarecord",
749 method => 'new_search_class_fts',
752 cdbi => "metabib::${class}_field_entry",
755 __PACKAGE__->register_method(
756 api_name => "open-ils.storage.metabib.$class.new_search_fts.metarecord.unordered",
757 method => 'new_search_class_fts',
760 cdbi => "metabib::${class}_field_entry",
763 __PACKAGE__->register_method(
764 api_name => "open-ils.storage.metabib.$class.new_search_fts.metarecord.staff",
765 method => 'new_search_class_fts',
768 cdbi => "metabib::${class}_field_entry",
771 __PACKAGE__->register_method(
772 api_name => "open-ils.storage.metabib.$class.new_search_fts.metarecord.staff.unordered",
773 method => 'new_search_class_fts',
776 cdbi => "metabib::${class}_field_entry",