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';
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 $cp_table = asset::copy->table;
42 my $cs_table = config::copy_status->table;
43 my $out_table = actor::org_unit_type->table;
51 sum((SELECT count(cp.id)
53 JOIN $cs_table cs ON (cp.status = cs.id)
54 WHERE cn.id = cp.call_number
60 WHERE cn.record = sm.source
61 AND cn.record = rd.record
63 GROUP BY cn.record, rd.item_type, rd.item_form
66 WHEN rd.item_type IS NULL -- default
68 WHEN rd.item_type = '' -- default
70 WHEN rd.item_type IN ('a','t') -- books
72 WHEN rd.item_type = 'g' -- movies
74 WHEN rd.item_type IN ('i','j') -- sound recordings
76 WHEN rd.item_type = 'm' -- software
78 WHEN rd.item_type = 'k' -- images
80 WHEN rd.item_type IN ('e','f') -- maps
82 WHEN rd.item_type IN ('o','p') -- mixed
84 WHEN rd.item_type IN ('c','d') -- music
86 WHEN rd.item_type = 'r' -- 3d
95 $sql .= ' AND x.item_type IN ('.join(',',map{'?'}@types).')';
99 $sql .= ' AND x.item_form IN ('.join(',',map{'?'}@forms).')';
102 my $sth = metabib::metarecord_source_map->db_Main->prepare_cached($sql);
103 $sth->execute("$mr", @types, @forms);
104 while ( my $row = $sth->fetchrow_arrayref ) {
105 $client->respond( $$row[0] );
110 __PACKAGE__->register_method(
111 api_name => 'open-ils.storage.ordered.metabib.metarecord.records',
112 method => 'ordered_records_from_metarecord',
117 __PACKAGE__->register_method(
118 api_name => 'open-ils.storage.ordered.metabib.metarecord.records.staff',
119 method => 'ordered_records_from_metarecord',
126 sub metarecord_copy_count {
132 my $sm_table = metabib::metarecord_source_map->table;
133 my $cn_table = asset::call_number->table;
134 my $cp_table = asset::copy->table;
135 my $cs_table = config::copy_status->table;
136 my $out_table = actor::org_unit_type->table;
137 my $descendants = "actor.org_unit_descendants(u.id)";
138 my $ancestors = "actor.org_unit_ancestors(?)";
140 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE';
141 $copies_visible = '' if ($self->api_name =~ /staff/o);
149 JOIN $cn_table cn ON (cn.record = r.source)
150 JOIN $cp_table cp ON (cn.id = cp.call_number)
151 JOIN $cs_table cs ON (cp.status = cs.id)
152 JOIN $descendants a ON (cp.circ_lib = a.id)
153 WHERE r.metarecord = ?
160 JOIN $cn_table cn ON (cn.record = r.source)
161 JOIN $cp_table cp ON (cn.id = cp.call_number)
162 JOIN $cs_table cs ON (cp.status = cs.id)
163 JOIN $descendants a ON (cp.circ_lib = a.id)
164 WHERE r.metarecord = ?
171 JOIN $out_table t ON (u.ou_type = t.id)
175 my $sth = metabib::metarecord_source_map->db_Main->prepare_cached($sql);
176 $sth->execute(''.$args{metarecord}, ''.$args{metarecord}, ''.$args{org_unit});
177 while ( my $row = $sth->fetchrow_hashref ) {
178 $client->respond( $row );
182 __PACKAGE__->register_method(
183 api_name => 'open-ils.storage.metabib.metarecord.copy_count',
184 method => 'metarecord_copy_count',
189 __PACKAGE__->register_method(
190 api_name => 'open-ils.storage.metabib.metarecord.copy_count.staff',
191 method => 'metarecord_copy_count',
197 sub search_full_rec {
203 my $term = $args{term};
204 my $limiters = $args{restrict};
206 my ($index_col) = metabib::full_rec->columns('FTS');
207 $index_col ||= 'value';
208 my $search_table = metabib::full_rec->table;
210 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
212 my $fts_where = $fts->sql_where_clause();
213 my @fts_ranks = $fts->fts_rank;
215 my $rank = join(' + ', @fts_ranks);
219 for my $limit (@$limiters) {
220 push @wheres, "( tag = ? AND subfield LIKE ? AND $fts_where )";
221 push @binds, $$limit{tag}, $$limit{subfield};
222 $log->debug("Limiting query using { tag => $$limit{tag}, subfield => $$limit{subfield} }", DEBUG);
224 my $where = join(' OR ', @wheres);
226 my $select = "SELECT record, sum($rank) FROM $search_table WHERE $where GROUP BY 1 ORDER BY 2 DESC;";
228 $log->debug("Search SQL :: [$select]",DEBUG);
230 my $recs = metabib::full_rec->db_Main->selectall_arrayref($select, {}, @binds);
231 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
233 $client->respond($_) for (@$recs);
236 __PACKAGE__->register_method(
237 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.value',
238 method => 'search_full_rec',
243 __PACKAGE__->register_method(
244 api_name => 'open-ils.storage.direct.metabib.full_rec.search_fts.index_vector',
245 method => 'search_full_rec',
252 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
253 sub search_class_fts {
258 my $term = $args{term};
259 my $ou = $args{org_unit};
260 my $ou_type = $args{depth};
261 my $limit = $args{limit};
262 my $offset = $args{offset};
264 my $limit_clause = '';
265 my $offset_clause = '';
267 $limit_clause = "LIMIT $limit" if (defined $limit and int($limit) > 0);
268 $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0);
271 my ($t_filter, $f_filter) = ('','');
274 my ($t, $f) = split '-', $args{format};
275 @types = split '', $t;
276 @forms = split '', $f;
278 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
282 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
288 my $descendants = defined($ou_type) ?
289 "actor.org_unit_descendants($ou, $ou_type)" :
290 "actor.org_unit_descendants($ou)";
292 my $class = $self->{cdbi};
293 my $search_table = $class->table;
295 my $metabib_record_descriptor = metabib::record_descriptor->table;
296 my $metabib_metarecord = metabib::metarecord->table;
297 my $metabib_full_rec = metabib::full_rec->table;
298 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
299 my $asset_call_number_table = asset::call_number->table;
300 my $asset_copy_table = asset::copy->table;
301 my $cs_table = config::copy_status->table;
303 my ($index_col) = $class->columns('FTS');
304 $index_col ||= 'value';
306 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col");
308 my $fts_where = $fts->sql_where_clause;
309 my @fts_ranks = $fts->fts_rank;
311 my $rank = join(' + ', @fts_ranks);
313 my $has_vols = 'AND cn.owning_lib = d.id';
314 my $has_copies = 'AND cp.call_number = cn.id';
315 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE';
317 my $visible_count = ', count(DISTINCT cp.id)';
318 my $visible_count_test = 'HAVING count(DISTINCT cp.id) > 0';
320 if ($self->api_name =~ /staff/o) {
321 $copies_visible = '';
322 $visible_count_test = '';
323 $has_copies = '' if ($ou_type == 0);
324 $has_vols = '' if ($ou_type == 0);
327 my $rank_calc = <<" RANK";
329 * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order
330 * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match
331 * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match
332 )/COUNT(m.source)), MIN(COALESCE(CHAR_LENGTH(f.value),1))
335 $rank_calc = ',1 , 1' if ($self->api_name =~ /unordered/o);
337 my $select = <<" SQL";
338 SELECT m.metarecord $rank_calc $visible_count
339 FROM $search_table f,
340 $metabib_metarecord_source_map_table m,
341 $asset_call_number_table cn,
342 $asset_copy_table cp,
344 $metabib_record_descriptor rd,
347 AND m.source = f.source
348 AND cn.record = m.source
349 AND rd.record = m.source
350 AND cp.status = cs.id
356 GROUP BY m.metarecord $visible_count_test
358 $limit_clause $offset_clause
361 $log->debug("Field Search SQL :: [$select]",DEBUG);
363 my $SQLstring = join('%',$fts->words);
364 my $REstring = join('\\s+',$fts->words);
365 my $first_word = ($fts->words)[0].'%';
366 my $recs = ($self->api_name =~ /unordered/o) ?
367 $class->db_Main->selectall_arrayref($select, {}, @types, @forms) :
368 $class->db_Main->selectall_arrayref($select, {},
369 '%'.lc($SQLstring).'%', # phrase order match
370 lc($first_word), # first word match
371 '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match
375 $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG);
377 $client->respond($_) for (map { [@$_[0,1,3]] } @$recs);
381 for my $class ( qw/title author subject keyword series/ ) {
382 __PACKAGE__->register_method(
383 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord",
384 method => 'search_class_fts',
387 cdbi => "metabib::${class}_field_entry",
390 __PACKAGE__->register_method(
391 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.unordered",
392 method => 'search_class_fts',
395 cdbi => "metabib::${class}_field_entry",
398 __PACKAGE__->register_method(
399 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff",
400 method => 'search_class_fts',
403 cdbi => "metabib::${class}_field_entry",
406 __PACKAGE__->register_method(
407 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff.unordered",
408 method => 'search_class_fts',
411 cdbi => "metabib::${class}_field_entry",
416 # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants".
417 sub search_class_fts_count {
422 my $term = $args{term};
423 my $ou = $args{org_unit};
424 my $ou_type = $args{depth};
425 my $limit = $args{limit} || 100;
426 my $offset = $args{offset} || 0;
428 my $descendants = defined($ou_type) ?
429 "actor.org_unit_descendants($ou, $ou_type)" :
430 "actor.org_unit_descendants($ou)";
433 my ($t_filter, $f_filter) = ('','');
436 my ($t, $f) = split '-', $args{format};
437 @types = split '', $t;
438 @forms = split '', $f;
440 $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')';
444 $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')';
449 (my $search_class = $self->api_name) =~ s/.*metabib.(\w+).search_fts.*/$1/o;
451 my $class = $self->{cdbi};
452 my $search_table = $class->table;
454 my $metabib_record_descriptor = metabib::record_descriptor->table;
455 my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table;
456 my $asset_call_number_table = asset::call_number->table;
457 my $asset_copy_table = asset::copy->table;
458 my $cs_table = config::copy_status->table;
460 my ($index_col) = $class->columns('FTS');
461 $index_col ||= 'value';
463 my $fts = OpenILS::Application::Storage::FTS->compile($term, 'value',"$index_col");
465 my $fts_where = $fts->sql_where_clause;
467 my $has_vols = 'AND cn.owning_lib = d.id';
468 my $has_copies = 'AND cp.call_number = cn.id';
469 my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE';
470 if ($self->api_name =~ /staff/o) {
471 $copies_visible = '';
472 $has_vols = '' if ($ou_type == 0);
473 $has_copies = '' if ($ou_type == 0);
476 # XXX test an "EXISTS version of descendant checking...
477 my $select = <<" SQL";
478 SELECT count(distinct m.metarecord)
479 FROM $search_table f,
480 $metabib_metarecord_source_map_table m,
481 $asset_call_number_table cn,
482 $asset_copy_table cp,
484 $metabib_record_descriptor rd,
487 AND m.source = f.source
488 AND cn.record = m.source
489 AND rd.record = m.source
490 AND cp.status = cs.id
498 $log->debug("Field Search Count SQL :: [$select]",DEBUG);
500 my $recs = $class->db_Main->selectrow_arrayref($select, {}, @types, @forms)->[0];
502 $log->debug("Count Search yielded $recs results.",DEBUG);
507 for my $class ( qw/title author subject keyword series/ ) {
508 __PACKAGE__->register_method(
509 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count",
510 method => 'search_class_fts_count',
513 cdbi => "metabib::${class}_field_entry",
516 __PACKAGE__->register_method(
517 api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count.staff",
518 method => 'search_class_fts_count',
521 cdbi => "metabib::${class}_field_entry",