From 53bf0638a8b3e26b2cd06200f8488d0bfae49dce Mon Sep 17 00:00:00 2001 From: miker Date: Fri, 12 Aug 2005 16:44:43 +0000 Subject: [PATCH] added combined count and id search git-svn-id: svn://svn.open-ils.org/ILS/trunk@1648 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- .../Application/Storage/Publisher/metabib.pm | 189 ++++++++++++++++++ 1 file changed, 189 insertions(+) diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm index 423d6a7f07..def071785a 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm @@ -592,4 +592,193 @@ for my $class ( qw/title author subject keyword series/ ) { } + + + +# XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants". +sub new_search_class_fts { + my $self = shift; + my $client = shift; + my %args = @_; + + my $term = $args{term}; + my $ou = $args{org_unit}; + my $ou_type = $args{depth}; + my $limit = $args{limit}; + my $offset = $args{offset} ||= 0; + + my $limit_clause = ''; + my $offset_clause = ''; + + $limit_clause = "LIMIT $limit" if (defined $limit and int($limit) > 0); + $offset_clause = "OFFSET $offset" if (defined $offset and int($offset) > 0); + + my (@types,@forms); + my ($t_filter, $f_filter) = ('',''); + + if ($args{format}) { + my ($t, $f) = split '-', $args{format}; + @types = split '', $t; + @forms = split '', $f; + if (@types) { + $t_filter = ' AND rd.item_type IN ('.join(',',map{'?'}@types).')'; + } + + if (@forms) { + $f_filter .= ' AND rd.item_form IN ('.join(',',map{'?'}@forms).')'; + } + } + + + + my $descendants = defined($ou_type) ? + "actor.org_unit_descendants($ou, $ou_type)" : + "actor.org_unit_descendants($ou)"; + + my $class = $self->{cdbi}; + my $search_table = $class->table; + + my $metabib_record_descriptor = metabib::record_descriptor->table; + my $metabib_metarecord = metabib::metarecord->table; + my $metabib_full_rec = metabib::full_rec->table; + my $metabib_metarecord_source_map_table = metabib::metarecord_source_map->table; + my $asset_call_number_table = asset::call_number->table; + my $asset_copy_table = asset::copy->table; + my $cs_table = config::copy_status->table; + my $cl_table = asset::copy_location->table; + + my ($index_col) = $class->columns('FTS'); + $index_col ||= 'value'; + + my $fts = OpenILS::Application::Storage::FTS->compile($term, 'f.value', "f.$index_col"); + + my $fts_where = $fts->sql_where_clause; + my @fts_ranks = $fts->fts_rank; + + my $rank = join(' + ', @fts_ranks); + + my $has_vols = 'AND cn.owning_lib = d.id'; + my $has_copies = 'AND cp.call_number = cn.id'; + my $copies_visible = 'AND cp.opac_visible IS TRUE AND cs.holdable IS TRUE AND cl.opac_visible IS TRUE'; + + my $visible_count = ', count(DISTINCT cp.id)'; + my $visible_count_test = 'HAVING count(DISTINCT cp.id) > 0'; + + if ($self->api_name =~ /staff/o) { + $copies_visible = ''; + $visible_count_test = ''; + $has_copies = '' if ($ou_type == 0); + $has_vols = '' if ($ou_type == 0); + } + + my $rank_calc = <<" RANK"; + , (SUM( $rank + * CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END -- phrase order + * CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END -- first word match + * CASE WHEN f.value ~* ? THEN 2 ELSE 1 END -- only word match + )/COUNT(m.source)), MIN(COALESCE(CHAR_LENGTH(f.value),1)) + RANK + + $rank_calc = ',1 , 1' if ($self->api_name =~ /unordered/o); + + if ($copies_visible) { + $select = <<" SQL"; + SELECT m.metarecord $rank_calc $visible_count + FROM $search_table f, + $metabib_metarecord_source_map_table m, + $asset_call_number_table cn, + $asset_copy_table cp, + $cs_table cs, + $cl_table cl, + $metabib_record_descriptor rd, + $descendants d + WHERE $fts_where + AND m.source = f.source + AND cn.record = m.source + AND rd.record = m.source + AND cp.status = cs.id + AND cp.location = cl.id + $has_vols + $has_copies + $copies_visible + $t_filter + $f_filter + GROUP BY m.metarecord $visible_count_test + ORDER BY 2 DESC,3 + SQL + } else { + $select = <<" SQL"; + SELECT m.metarecord $rank_calc, 0 + FROM $search_table f, + $metabib_metarecord_source_map_table m, + $metabib_record_descriptor rd + WHERE $fts_where + AND m.source = f.source + AND rd.record = m.source + $t_filter + $f_filter + GROUP BY 1, 4 + ORDER BY 2 DESC,3 + SQL + } + + $log->debug("Field Search SQL :: [$select]",DEBUG); + + my $SQLstring = join('%',$fts->words); + my $REstring = join('\\s+',$fts->words); + my $first_word = ($fts->words)[0].'%'; + my $recs = ($self->api_name =~ /unordered/o) ? + $class->db_Main->selectall_arrayref($select, {}, @types, @forms) : + $class->db_Main->selectall_arrayref($select, {}, + '%'.lc($SQLstring).'%', # phrase order match + lc($first_word), # first word match + '^\\s*'.lc($REstring).'\\s*/?\s*$', # full exact match + @types, @forms + ); + + $log->debug("Search yielded ".scalar(@$recs)." results.",DEBUG); + + my $count = scalar(@$recs); + $client->respond($_) for (map { [@$_[0,1,3],$count] } @$recs[$offset .. $offset + $limit]); + return undef; +} + +for my $class ( qw/title author subject keyword series/ ) { + __PACKAGE__->register_method( + api_name => "open-ils.storage.metabib.$class.new_search_fts.metarecord", + method => 'new_search_class_fts', + api_level => 1, + stream => 1, + cdbi => "metabib::${class}_field_entry", + cachable => 1, + ); + __PACKAGE__->register_method( + api_name => "open-ils.storage.metabib.$class.new_search_fts.metarecord.unordered", + method => 'new_search_class_fts', + api_level => 1, + stream => 1, + cdbi => "metabib::${class}_field_entry", + cachable => 1, + ); + __PACKAGE__->register_method( + api_name => "open-ils.storage.metabib.$class.new_search_fts.metarecord.staff", + method => 'new_search_class_fts', + api_level => 1, + stream => 1, + cdbi => "metabib::${class}_field_entry", + cachable => 1, + ); + __PACKAGE__->register_method( + api_name => "open-ils.storage.metabib.$class.new_search_fts.metarecord.staff.unordered", + method => 'new_search_class_fts', + api_level => 1, + stream => 1, + cdbi => "metabib::${class}_field_entry", + cachable => 1, + ); +} + + + + 1; -- 2.43.2