From e99cb02ae04f7e3fdc379459ed60f1ca81937260 Mon Sep 17 00:00:00 2001 From: Lebbeous Fogle-Weekley Date: Wed, 24 Apr 2013 10:46:27 -0400 Subject: [PATCH] Bib record browser with 'see also', etc from linked authority headings This feature provides a patron-oriented OPAC interface for browsing bibliographic records. Users choose to browse by Author, Title, Subject, or Series. They then enter a browse term, and the nearest match from a left-anchored search on the headings extracted for browse purposes will be displayed in a typical backwards/forwards paging display. Headings link to search results pages showing the related records. If the browse heading is linked to any authority records, and if any *other* authority records point to those with "See also" or other non-main entry headings, those alternative headings are displayed a linked to a search results page showing related bib records related to the alternate heading. The counts of holdings displayed next to headings from bibliographic records are subject to the same visiibility tests as search. This means that the org unit (and copy location group) dropdown on the browse interface affects counds, and it further means that whether or not you're looking at the browse interface through the staff client makes a difference. This builds on the two previous commits that provide inter-authority linking and the linking of metabib.browse_entry rows to authority records. Signed-off-by: Lebbeous Fogle-Weekley --- Open-ILS/examples/fm_IDL.xml | 13 + .../Application/Storage/Driver/Pg/QueryParser.pm | 8 + .../src/perlmods/lib/OpenILS/WWW/EGCatLoader.pm | 2 + .../perlmods/lib/OpenILS/WWW/EGCatLoader/Browse.pm | 331 +++++++++++++++++++++ .../perlmods/lib/OpenILS/WWW/EGCatLoader/Util.pm | 18 +- Open-ILS/src/sql/Pg/002.schema.config.sql | 1 + Open-ILS/src/sql/Pg/030.schema.metabib.sql | 228 +++++++++++++- Open-ILS/src/sql/Pg/950.data.seed-values.sql | 75 +++-- Open-ILS/src/sql/Pg/999.functions.global.sql | 2 +- .../XXXX.schema.config-metabib-interauthority.sql | 2 +- .../sql/Pg/upgrade/YYYY.schema.bib-auth-browse.sql | 252 +++++++++++++++- Open-ILS/src/templates/opac/advanced.tt2 | 11 +- Open-ILS/src/templates/opac/browse.tt2 | 128 ++++++++ Open-ILS/src/templates/opac/css/style.css.tt2 | 28 +- Open-ILS/src/templates/opac/parts/config.tt2 | 9 + .../src/templates/opac/parts/qtype_selector.tt2 | 13 +- Open-ILS/src/templates/opac/parts/searchbar.tt2 | 6 +- Open-ILS/web/css/skin/default/opac/semiauto.css | 1 - docs/RELEASE_NOTES_NEXT/OPAC/BibAuthBrowse.txt | 45 +++ 19 files changed, 1116 insertions(+), 57 deletions(-) create mode 100644 Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Browse.pm create mode 100644 Open-ILS/src/templates/opac/browse.tt2 create mode 100644 docs/RELEASE_NOTES_NEXT/OPAC/BibAuthBrowse.txt diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index ccc7117f63..a76a1740a3 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -5679,6 +5679,19 @@ SELECT usr, + + + + + + + + + + + + + diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm index c202dac4b9..27f0ff5b78 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm @@ -665,6 +665,8 @@ __PACKAGE__->add_search_filter( 'container' ); # Start from a list of record ids, either bre or metarecords, depending on the #metabib modifier __PACKAGE__->add_search_filter( 'record_list' ); +__PACKAGE__->add_search_filter( 'has_browse_entry' ); + # used internally, but generally not user-settable __PACKAGE__->add_search_filter( 'preferred_language' ); __PACKAGE__->add_search_filter( 'preferred_language_weight' ); @@ -1109,6 +1111,12 @@ sub flatten { $where .= "$key ${NOT}IN (" . join(',', map { $self->QueryParser->quote_value($_) } @{$filter->args}) . ')'; } + } elsif ($filter->name eq 'has_browse_entry') { + if (@{$filter->args} >= 2) { + my $entry = int(shift @{$filter->args}); + my $fields = join(",", map(int, @{$filter->args})); + $from .= "\n" . $spc x 3 . sprintf("INNER JOIN metabib.browse_entry_def_map mbedm ON (mbedm.source = m.source AND mbedm.entry = %d AND mbedm.def IN (%s))", $entry, $fields); + } } elsif ($filter->name eq 'edit_date' or $filter->name eq 'create_date') { # bre.create_date and bre.edit_date filtering my $datefilter = $filter->name; diff --git a/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader.pm b/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader.pm index 0838a29eb5..244afa206b 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader.pm @@ -19,6 +19,7 @@ use Time::HiRes; # EGCatLoader sub-modules use OpenILS::WWW::EGCatLoader::Util; use OpenILS::WWW::EGCatLoader::Account; +use OpenILS::WWW::EGCatLoader::Browse; use OpenILS::WWW::EGCatLoader::Search; use OpenILS::WWW::EGCatLoader::Record; use OpenILS::WWW::EGCatLoader::Container; @@ -123,6 +124,7 @@ sub load { return $self->load_print_record if $path =~ m|opac/record/print|; return $self->load_record if $path =~ m|opac/record/\d|; return $self->load_cnbrowse if $path =~ m|opac/cnbrowse|; + return $self->load_browse if $path =~ m|opac/browse|; return $self->load_mylist_add if $path =~ m|opac/mylist/add|; return $self->load_mylist_delete if $path =~ m|opac/mylist/delete|; diff --git a/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Browse.pm b/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Browse.pm new file mode 100644 index 0000000000..059bad1f7f --- /dev/null +++ b/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Browse.pm @@ -0,0 +1,331 @@ +package OpenILS::WWW::EGCatLoader; + +use strict; +use warnings; + +use OpenSRF::Utils::Logger qw/$logger/; +use OpenILS::Utils::CStoreEditor qw/:funcs/; +use OpenILS::Utils::Fieldmapper; +use OpenILS::Application::AppUtils; +use OpenSRF::Utils::JSON; +use OpenSRF::Utils::Cache; +use OpenSRF::Utils::SettingsClient; + +use Digest::MD5 qw/md5_hex/; +use Apache2::Const -compile => qw/OK/; +use MARC::Record; +#use Data::Dumper; +#$Data::Dumper::Indent = 0; + +my $U = 'OpenILS::Application::AppUtils'; +my $browse_cache; +my $browse_timeout; + +sub _init_browse_cache { + if (not defined $browse_cache) { + my $conf = new OpenSRF::Utils::SettingsClient; + + $browse_timeout = $conf->config_value( + "apps", "open-ils.search", "app_settings", "cache_timeout" + ) || 300; + $browse_cache = new OpenSRF::Utils::Cache("global"); + } +} + +# Returns cache key and a list of parameters for DB proc metabib.browse(). +sub prepare_browse_parameters { + my ($self) = @_; + + no warnings 'uninitialized'; + + # XXX TODO add config.global_flag rows for browse limit-limit and + # browse offset-limit? + + my $limit = int($self->cgi->param('blimit') || 10); + my $offset = int($self->cgi->param('boffset') || 0); + my $force_backward = scalar($self->cgi->param('bback')); + + my @params = ( + scalar($self->cgi->param('qtype')), + scalar($self->cgi->param('bterm')), + $self->ctx->{copy_location_group_org} || + $self->ctx->{aou_tree}->()->id, + $self->ctx->{copy_location_group}, + $self->ctx->{is_staff} ? 't' : 'f', + scalar($self->cgi->param('bpivot')), + $force_backward ? 't' : 'f' + ); + + # We do need $limit, $offset, and $force_backward as part of the + # cache key, but we also need to keep them separate from other + # parameters for purposes of paging link generation. + return ( + "oils_browse_" . md5_hex( + OpenSRF::Utils::JSON->perl2JSON( + [@params, $limit, $offset, $force_backward] + ) + ), + $limit, $offset, $force_backward, @params + ); +} + +sub find_authority_headings { + my ($self, $row) = @_; + + my $acsaf_table = + $self->ctx->{get_authority_fields}->($row->{control_set}); + + $row->{headings} = []; + + my $record; + eval { + $record = new_from_xml MARC::Record($row->{marc}); + }; + if ($@) { + $logger->warn("Problem with MARC from authority record #" . + $row->{id} . ": $@"); + return $row; # We're called in map(), so we must move on without + # a fuss. + } + + foreach my $acsaf (values(%$acsaf_table)) { + my @fields = $record->field($acsaf->tag); + my @headings; + + foreach (@fields) { + my $heading = ""; + foreach my $sf (split "", $acsaf->sf_list) { + $heading .= $_->subfield($sf) || ""; + } + push @headings, $heading; + } + + # Remember: main_entry is a link field, so for it to evaluate + # to true means that we *have* (and therefore *aren't*) a main + # entry. The rest of the time when main_entry is undef we + # *are* a main entry. + # + # For this, we only want non-main entries. + push @{$row->{headings}}, {$acsaf->id => \@headings} + if @headings and $acsaf->main_entry; + } + + return $row; +} + +# flesh_browse_results() attaches data from authority records. It +# changes $results and returns 1 for success, undef for failure (in which +# case $self->editor->event should always point to the reason for failure). +# $results must be an arrayref of result rows from the DB's metabib.browse() +sub flesh_browse_results { + my ($self, $results) = @_; + + # Turn comma-seprated strings of numbers in "authorities" column + # into arrays. + $_->{authorities} = [split /,/, $_->{authorities}] foreach @$results; + + # Group them in one arrray, not worrying about dupes because we're about + # to use them in an IN () comparison in a SQL query. + my @auth_ids = map { @{$_->{authorities}} } @$results; + + if (@auth_ids) { + # Get all linked authority records themselves + my $linked = $self->editor->json_query({ + select => {are => [qw/id marc control_set/], aalink => ["target"]}, + from => { + aalink => { + are => { field => "id", fkey => "source" } + } + }, + where => {"+aalink" => {target => \@auth_ids}} + }) or return; + + # Then use the linked authority records' control sets to find and + # pick out non-main-entry headings. Build the headings and make a + # combined data structure for the template's use. + my %linked_headings_by_auth_id = + map { $_->{id} => $self->find_authority_headings($_) } @$linked; + + # Graft this authority heading data onto our main result set at the + # "authorities" column. + foreach my $row (@$results) { + $row->{authorities} = [ + map { $linked_headings_by_auth_id{$_} } @{$row->{authorities}} + ]; + } + + # Get use counts of authority records, i.e. number of bibs linked to + # them. - XXX refine later to consider holdings visibility. + my $counts = $self->editor->json_query({ + select => { + abl => [ + {column => "id", transform => "count", + alias => "count", aggregate => 1}, + "authority" + ] + }, + from => {abl => {}}, + where => {"+abl" => {authority => \@auth_ids}} + }) or return; + + my %counts_by_authority = + map { $_->{authority} => $_->{count} } @$counts; + foreach my $row(@$results) { + foreach my $auth (@{$row->{authorities}}) { + $auth->{count} = $counts_by_authority{$auth->{id}}; + } + } + } + + return 1; +} + +sub load_browse_impl { + my ($self, $limit, $offset, $force_backward, @params) = @_; + + my $inner_limit = ($offset >= 0 and not $force_backward) ? + $limit + 1 : $limit; + + my $results = $self->editor->json_query({ + from => [ + "metabib.browse", (@params, $inner_limit, $offset) + ] + }); + + if (not $results) { # DB error, not empty result set. + $logger->warn( + "error in browse (direct): " . $self->editor->event->{textcode} + ); + $self->ctx->{browse_error} = 1; + + return; + } elsif (not $self->flesh_browse_results($results)) { + $logger->warn( + "error in browse (flesh): " . $self->editor->event->{textcode} + ); + $self->ctx->{browse_error} = 1; + + return; + } + + return $results; +} + +# $results can be modified by this function. This would be simpler +# but for the moving pivot concept that helps us avoid paging with +# large offsets (slow). +sub infer_browse_paging { + my ($self, $results, $limit, $offset, $force_backward) = @_; + + # (All these comments assume a default limit of 10). For typical + # not-backwards requests not at the end of the result set, we + # should have an eleventh result that tells us what's next. + while (scalar @$results > $limit) { + $self->ctx->{forward_pivot} = (pop @$results)->{browse_entry}; + $self->ctx->{more_forward} = 1; + } + + # If we're going backwards by pivot id, we don't have an eleventh + # result to tell us we can page forward, but we can assume we can + # go forward because duh, we followed a link backward to get here. + if ($force_backward and $self->cgi->param('bpivot')) { + $self->ctx->{forward_pivot} = scalar($self->cgi->param('bpivot')); + $self->ctx->{more_forward} = 1; + } + + # The pivot that the user can use for going backwards is the first + # of the result set. + if (@$results) { + $self->ctx->{back_pivot} = $results->[0]->{browse_entry}; + } + + # The result of these tests relate to basic limit/offset paging. + + # This comparison for setting more_forward does not fold into + # those for setting more_back. + if ($offset < 0 || $force_backward) { + $self->ctx->{more_forward} = 1; + } + + if ($offset > 0) { + $self->ctx->{more_back} = 1; + } elsif (scalar @$results < $limit) { + $self->ctx->{more_back} = 0; + } else { + $self->ctx->{more_back} = 1; + } +} + +sub leading_article_test { + my ($self, $qtype, $bterm) = @_; + + my $flag_name = "opac.browse.warnable_regexp_per_class"; + my $flag = $self->ctx->{get_cgf}->($flag_name); + + return unless $flag->enabled; + + my $map; + + eval { $map = OpenSRF::Utils::JSON->JSON2perl($flag->value); }; + if ($@) { + $logger->warn("cgf '$flag_name' enabled but value is invalid JSON? $@"); + return; + } + + # Don't crash over any of the things that could go wrong in here: + eval { + if ($map->{$qtype}) { + if ($bterm =~ qr/$map->{$qtype}/i) { + $self->ctx->{browse_leading_article_warning} = 1; + } + } + }; + if ($@) { + $logger->warn("cgf '$flag_name' has valid JSON in value, but: $@"); + } +} + +sub load_browse { + my ($self) = @_; + + _init_browse_cache(); + + $self->ctx->{more_forward} = 0; + $self->ctx->{more_back} = 0; + + if ($self->cgi->param('qtype') and $self->cgi->param('bterm')) { + + $self->leading_article_test( + $self->cgi->param('qtype'), + $self->cgi->param('bterm') + ); + + my ($cache_key, $limit, $offset, $force_backward, @params) = + $self->prepare_browse_parameters; + + my $results = $browse_cache->get_cache($cache_key); + if (not $results) { + $results = $self->load_browse_impl( + $limit, $offset, $force_backward, @params + ); + if ($results) { + $browse_cache->put_cache($cache_key, $results, $browse_timeout); + } + } + + if ($results) { + $self->infer_browse_paging( + $results, $limit, $offset, $force_backward + ); + $self->ctx->{browse_results} = $results; + } + + # We don't need an else clause to send the user a 5XX error or + # anything. Errors will have been logged, and $ctx will be + # prepared so a template can show a nicer error to the user. + } + + return Apache2::Const::OK; +} + +1; diff --git a/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Util.pm b/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Util.pm index 93cc3e120d..8e7a1ec434 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Util.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Util.pm @@ -18,7 +18,8 @@ our %cache = ( # cached data search_filter_groups => {en_us => {}}, aou_tree => {en_us => undef}, aouct_tree => {}, - eg_cache_hash => undef + eg_cache_hash => undef, + authority_fields => {en_us => {}} ); sub init_ro_object_cache { @@ -227,6 +228,21 @@ sub init_ro_object_cache { return $cache{org_settings}{$ctx->{locale}}{$org_id}{$setting}; }; + # retrieve and cache acsaf values + $ro_object_subs->{get_authority_fields} = sub { + my ($control_set) = @_; + + if (not exists $cache{authority_fields}{$ctx->{locale}}{$control_set}) { + my $acs = $e->search_authority_control_set_authority_field( + {control_set => $control_set} + ) or return; + $cache{authority_fields}{$ctx->{locale}}{$control_set} = + +{ map { $_->id => $_ } @$acs }; + } + + return $cache{authority_fields}{$ctx->{locale}}{$control_set}; + }; + $ctx->{$_} = $ro_object_subs->{$_} for keys %$ro_object_subs; } diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 5a955d4866..2738748af1 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -194,6 +194,7 @@ CREATE TABLE config.metabib_field ( browse_field BOOL NOT NULL DEFAULT TRUE, browse_xpath TEXT, facet_xpath TEXT, + authority_xpath TEXT, restrict BOOL DEFAULT FALSE NOT NULL ); COMMENT ON TABLE config.metabib_field IS $$ diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index 8dbac47557..fd32f3de35 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -186,8 +186,25 @@ CREATE INDEX metabib_facet_entry_source_idx ON metabib.facet_entry (source); CREATE TABLE metabib.browse_entry ( id BIGSERIAL PRIMARY KEY, value TEXT unique, - index_vector tsvector + index_vector tsvector, + sort_value TEXT NOT NULL ); + +CREATE INDEX browse_entry_sort_value_idx + ON metabib.browse_entry USING BTREE (sort_value); + +CREATE OR REPLACE FUNCTION metabib.browse_entry_sort_value() +RETURNS TRIGGER AS $$ + BEGIN + NEW.sort_value = public.search_normalize(NEW.value); + RETURN NEW; + END; +$$ LANGUAGE PLPGSQL; + +CREATE TRIGGER mbe_sort_value +BEFORE INSERT OR UPDATE ON metabib.browse_entry +FOR EACH ROW EXECUTE PROCEDURE metabib.browse_entry_sort_value(); + CREATE INDEX metabib_browse_entry_index_vector_idx ON metabib.browse_entry USING GIN (index_vector); CREATE TRIGGER metabib_browse_entry_fti_trigger BEFORE INSERT OR UPDATE ON metabib.browse_entry @@ -198,7 +215,8 @@ CREATE TABLE metabib.browse_entry_def_map ( id BIGSERIAL PRIMARY KEY, entry BIGINT REFERENCES metabib.browse_entry (id), def INT REFERENCES config.metabib_field (id), - source BIGINT REFERENCES biblio.record_entry (id) + source BIGINT REFERENCES biblio.record_entry (id), + authority BIGINT REFERENCES authority.record_entry (id) ON DELETE SET NULL ); CREATE INDEX browse_entry_def_map_def_idx ON metabib.browse_entry_def_map (def); CREATE INDEX browse_entry_def_map_entry_idx ON metabib.browse_entry_def_map (entry); @@ -478,6 +496,7 @@ BEGIN output_row.field = idx.id; output_row.source = rid; output_row.value = BTRIM(REGEXP_REPLACE(browse_text, E'\\s+', ' ', 'g')); + output_row.authority := NULL; IF idx.authority_xpath IS NOT NULL AND idx.authority_xpath <> '' THEN authority_text := oils_xpath_string( @@ -1705,4 +1724,209 @@ BEGIN END; $$ LANGUAGE PLPGSQL; + +CREATE TYPE metabib.flat_browse_entry_appearance AS ( + browse_entry BIGINT, + value TEXT, + fields TEXT, + authorities TEXT, + sources INT, -- visible ones, that is + row_number INT -- internal use, sort of +); + + +CREATE OR REPLACE FUNCTION metabib.browse_pivot( + search_field INT[], + browse_term TEXT +) RETURNS BIGINT AS $p$ +DECLARE + id BIGINT; +BEGIN + SELECT INTO id mbe.id FROM metabib.browse_entry mbe + JOIN metabib.browse_entry_def_map mbedm ON ( + mbedm.entry = mbe.id AND + mbedm.def = ANY(search_field) + ) + WHERE mbe.sort_value >= public.search_normalize(browse_term) + ORDER BY mbe.sort_value, mbe.value LIMIT 1; + + RETURN id; +END; +$p$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION metabib.staged_browse( + core_query TEXT, + context_org INT, + context_locations INT[], + staff BOOL, + result_limit INT, + use_offset INT +) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$ +DECLARE + core_cursor REFCURSOR; + core_record RECORD; + qpfts_query TEXT; + result_row metabib.flat_browse_entry_appearance%ROWTYPE; + results_skipped INT := 0; + results_returned INT := 0; +BEGIN + OPEN core_cursor FOR EXECUTE core_query; + + LOOP + FETCH core_cursor INTO core_record; + EXIT WHEN NOT FOUND; + + qpfts_query := + 'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, 1::INT AS rel ' || + 'FROM (SELECT UNNEST(' || + quote_literal(core_record.records) || '::BIGINT[]) AS r) rr'; + + -- We use search.query_parser_fts() for visibility testing. Yes there + -- is a reason we feed it the records for one mbe at a time instead of + -- the records for `result_limit` mbe's at a time. + SELECT INTO result_row.sources visible + FROM search.query_parser_fts( + context_org, NULL, qpfts_query, NULL, + context_locations, 0, NULL, NULL, FALSE, staff, FALSE + ) qpfts + WHERE qpfts.rel IS NULL; + + IF result_row.sources > 0 THEN + IF results_skipped < use_offset THEN + results_skipped := results_skipped + 1; + CONTINUE; + END IF; + + result_row.browse_entry := core_record.id; + result_row.authorities := core_record.authorities; + result_row.fields := core_record.fields; + result_row.value := core_record.value; + + -- This is needed so our caller can flip it and reverse it. + result_row.row_number := results_returned; + + RETURN NEXT result_row; + + results_returned := results_returned + 1; + + EXIT WHEN results_returned >= result_limit; + END IF; + END LOOP; +END; +$p$ LANGUAGE PLPGSQL; + +-- This is optimized to be fast for values of result_offset near zero. +CREATE OR REPLACE FUNCTION metabib.browse( + search_field INT[], + browse_term TEXT, + context_org INT DEFAULT NULL, + context_loc_group INT DEFAULT NULL, + staff BOOL DEFAULT FALSE, + pivot_id BIGINT DEFAULT NULL, + force_backward BOOL DEFAULT FALSE, + result_limit INT DEFAULT 10, + result_offset INT DEFAULT 0 -- Can be negative! +) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$ +DECLARE + core_query TEXT; + whole_query TEXT; + pivot_sort_value TEXT; + pivot_sort_fallback TEXT; + context_locations INT[]; + use_offset INT; + results_skipped INT := 0; +BEGIN + IF pivot_id IS NULL THEN + pivot_id := metabib.browse_pivot(search_field, browse_term); + END IF; + + SELECT INTO pivot_sort_value, pivot_sort_fallback + sort_value, value FROM metabib.browse_entry where id = pivot_id; + + IF pivot_sort_value IS NULL THEN + RETURN; + END IF; + + IF context_loc_group IS NOT NULL THEN + SELECT INTO context_locations ARRAY_AGG(location) + FROM asset.copy_location_group_map + WHERE lgroup = context_loc_group; + END IF; + + core_query := ' + SELECT + mbe.id, + mbe.value, + mbe.sort_value, + (SELECT ARRAY_AGG(src) FROM ( + SELECT DISTINCT UNNEST(ARRAY_AGG(mbedm.source)) AS src + ) ss) AS records, + (SELECT ARRAY_TO_STRING(ARRAY_AGG(authority), $$,$$) FROM ( + SELECT DISTINCT UNNEST(ARRAY_AGG(mbedm.authority)) AS authority + ) au) AS authorities, + (SELECT ARRAY_TO_STRING(ARRAY_AGG(field), $$,$$) FROM ( + SELECT DISTINCT UNNEST(ARRAY_AGG(mbedm.def)) AS field + ) fi) AS fields + FROM metabib.browse_entry mbe + JOIN metabib.browse_entry_def_map mbedm ON ( + mbedm.entry = mbe.id AND + mbedm.def = ANY(' || quote_literal(search_field) || ') + ) + WHERE '; + + -- PostgreSQL is not magic. We can't actually pass a negative offset. + IF result_offset >= 0 AND NOT force_backward THEN + use_offset := result_offset; + core_query := core_query || + ' mbe.sort_value >= ' || quote_literal(pivot_sort_value) || + ' GROUP BY 1,2,3 ORDER BY mbe.sort_value, mbe.value '; + + RETURN QUERY SELECT * FROM metabib.staged_browse( + core_query, context_org, context_locations, + staff, result_limit, use_offset + ); + ELSE + -- Part 1 of 2 to deliver what the user wants with a negative offset: + core_query := core_query || + ' mbe.sort_value < ' || quote_literal(pivot_sort_value) || + ' GROUP BY 1,2,3 ORDER BY mbe.sort_value DESC, mbe.value DESC '; + + -- Part 2 of 2 to deliver what the user wants with a negative offset: + RETURN QUERY SELECT * FROM (SELECT * FROM metabib.staged_browse( + core_query, context_org, context_locations, + staff, result_limit, use_offset + )) sb ORDER BY row_number DESC; + + END IF; +END; +$p$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION metabib.browse( + search_class TEXT, + browse_term TEXT, + context_org INT DEFAULT NULL, + context_loc_group INT DEFAULT NULL, + staff BOOL DEFAULT FALSE, + pivot_id BIGINT DEFAULT NULL, + force_backward BOOL DEFAULT FALSE, + result_limit INT DEFAULT 10, + result_offset INT DEFAULT 0 -- Can be negative, implying backward! +) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$ +BEGIN + RETURN QUERY SELECT * FROM metabib.browse( + (SELECT COALESCE(ARRAY_AGG(id), ARRAY[]::INT[]) + FROM config.metabib_field WHERE field_class = search_class), + browse_term, + context_org, + context_loc_group, + staff, + pivot_id, + force_backward, + result_limit, + result_offset + ); +END; +$p$ LANGUAGE PLPGSQL; + + COMMIT; diff --git a/Open-ILS/src/sql/Pg/950.data.seed-values.sql b/Open-ILS/src/sql/Pg/950.data.seed-values.sql index 56e4f7e4b0..d96528fe1a 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -108,37 +108,37 @@ INSERT INTO config.xml_transform VALUES ( 'mods33', 'http://www.loc.gov/mods/v3' INSERT INTO config.xml_transform VALUES ( 'marc21expand880', 'http://www.loc.gov/MARC21/slim', 'marc', '' ); -- Index Definitions -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES - (1, 'series', 'seriestitle', oils_i18n_gettext(1, 'Series Title', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:relatedItem[@type="series"]/mods32:titleInfo$$, TRUE ); - -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath ) VALUES - (2, 'title', 'abbreviated', oils_i18n_gettext(2, 'Abbreviated Title', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:titleInfo[mods32:title and (@type='abbreviated')]$$ ); -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath ) VALUES - (3, 'title', 'translated', oils_i18n_gettext(3, 'Translated Title', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:titleInfo[mods32:title and (@type='translated')]$$ ); -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath ) VALUES - (4, 'title', 'alternative', oils_i18n_gettext(4, 'Alternate Title', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:titleInfo[mods32:title and (@type='alternative')]$$ ); -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath ) VALUES - (5, 'title', 'uniform', oils_i18n_gettext(5, 'Uniform Title', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:titleInfo[mods32:title and (@type='uniform')]$$ ); -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath ) VALUES - (6, 'title', 'proper', oils_i18n_gettext(6, 'Title Proper', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:titleNonfiling[mods32:title and not (@type)]$$ ); - -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_xpath, facet_field ) VALUES - (7, 'author', 'corporate', oils_i18n_gettext(7, 'Corporate Author', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:name[@type='corporate' and (mods32:role/mods32:roleTerm[text()='creator'] or mods32:role/mods32:roleTerm[text()='aut'] or mods32:role/mods32:roleTerm[text()='cre'])]$$, $$//*[local-name()='namePart']$$, TRUE ); -- /* to fool vim */; -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_xpath, facet_field ) VALUES - (8, 'author', 'personal', oils_i18n_gettext(8, 'Personal Author', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:name[@type='personal' and mods32:role/mods32:roleTerm[text()='creator']]$$, $$//*[local-name()='namePart']$$, TRUE ); -- /* to fool vim */; -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_xpath, facet_field ) VALUES - (9, 'author', 'conference', oils_i18n_gettext(9, 'Conference Author', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:name[@type='conference' and mods32:role/mods32:roleTerm[text()='creator']]$$, $$//*[local-name()='namePart']$$, TRUE ); -- /* to fool vim */; -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_xpath, facet_field ) VALUES - (10, 'author', 'other', oils_i18n_gettext(10, 'Other Author', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:name[@type='personal' and not(mods32:role/mods32:roleTerm[text()='creator'])]$$, $$//*[local-name()='namePart']$$, TRUE ); -- /* to fool vim */; - -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES - (11, 'subject', 'geographic', oils_i18n_gettext(11, 'Geographic Subject', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:subject/mods32:geographic$$, TRUE ); -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_xpath, facet_field ) VALUES - (12, 'subject', 'name', oils_i18n_gettext(12, 'Name Subject', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:subject/mods32:name$$, $$//*[local-name()='namePart']$$, TRUE ); -- /* to fool vim */; -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES - (13, 'subject', 'temporal', oils_i18n_gettext(13, 'Temporal Subject', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:subject/mods32:temporal$$, TRUE ); -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES - (14, 'subject', 'topic', oils_i18n_gettext(14, 'Topic Subject', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:subject/mods32:topic$$, TRUE ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field, authority_xpath ) VALUES + (1, 'series', 'seriestitle', oils_i18n_gettext(1, 'Series Title', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:relatedItem[@type="series"]/mods32:titleInfo$$, TRUE, '//@xlink:href' ); + +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, authority_xpath ) VALUES + (2, 'title', 'abbreviated', oils_i18n_gettext(2, 'Abbreviated Title', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:titleInfo[mods32:title and (@type='abbreviated')]$$, '//@xlink:href' ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, authority_xpath ) VALUES + (3, 'title', 'translated', oils_i18n_gettext(3, 'Translated Title', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:titleInfo[mods32:title and (@type='translated')]$$, '//@xlink:href' ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, authority_xpath ) VALUES + (4, 'title', 'alternative', oils_i18n_gettext(4, 'Alternate Title', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:titleInfo[mods32:title and (@type='alternative')]$$, '//@xlink:href' ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, authority_xpath ) VALUES + (5, 'title', 'uniform', oils_i18n_gettext(5, 'Uniform Title', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:titleInfo[mods32:title and (@type='uniform')]$$, '//@xlink:href' ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, authority_xpath ) VALUES + (6, 'title', 'proper', oils_i18n_gettext(6, 'Title Proper', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:titleNonfiling[mods32:title and not (@type)]$$, '//@xlink:href' ); + +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_xpath, facet_field , authority_xpath) VALUES + (7, 'author', 'corporate', oils_i18n_gettext(7, 'Corporate Author', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:name[@type='corporate' and (mods32:role/mods32:roleTerm[text()='creator'] or mods32:role/mods32:roleTerm[text()='aut'] or mods32:role/mods32:roleTerm[text()='cre'])]$$, $$//*[local-name()='namePart']$$, TRUE, '//@xlink:href' ); -- /* to fool vim */; +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_xpath, facet_field, authority_xpath ) VALUES + (8, 'author', 'personal', oils_i18n_gettext(8, 'Personal Author', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:name[@type='personal' and mods32:role/mods32:roleTerm[text()='creator']]$$, $$//*[local-name()='namePart']$$, TRUE, '//@xlink:href' ); -- /* to fool vim */; +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_xpath, facet_field, authority_xpath ) VALUES + (9, 'author', 'conference', oils_i18n_gettext(9, 'Conference Author', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:name[@type='conference' and mods32:role/mods32:roleTerm[text()='creator']]$$, $$//*[local-name()='namePart']$$, TRUE, '//@xlink:href' ); -- /* to fool vim */; +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_xpath, facet_field, authority_xpath ) VALUES + (10, 'author', 'other', oils_i18n_gettext(10, 'Other Author', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:name[@type='personal' and not(mods32:role/mods32:roleTerm[text()='creator'])]$$, $$//*[local-name()='namePart']$$, TRUE, '//@xlink:href' ); -- /* to fool vim */; + +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field, authority_xpath ) VALUES + (11, 'subject', 'geographic', oils_i18n_gettext(11, 'Geographic Subject', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:subject/mods32:geographic$$, TRUE, '//@xlink:href' ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_xpath, facet_field, authority_xpath ) VALUES + (12, 'subject', 'name', oils_i18n_gettext(12, 'Name Subject', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:subject/mods32:name$$, $$//*[local-name()='namePart']$$, TRUE, '//@xlink:href' ); -- /* to fool vim */; +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field, authority_xpath ) VALUES + (13, 'subject', 'temporal', oils_i18n_gettext(13, 'Temporal Subject', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:subject/mods32:temporal$$, TRUE, '//@xlink:href' ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field, authority_xpath ) VALUES + (14, 'subject', 'topic', oils_i18n_gettext(14, 'Topic Subject', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:subject/mods32:topic$$, TRUE, '//@xlink:href' ); --INSERT INTO config.metabib_field ( id, field_class, name, format, xpath ) VALUES -- ( id, field_class, name, xpath ) VALUES ( 'subject', 'genre', 'mods32', $$//mods32:mods/mods32:genre$$ ); INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, browse_field ) VALUES @@ -9023,6 +9023,19 @@ INSERT INTO config.global_flag (name, label, value, enabled) TRUE ); +INSERT INTO config.global_flag (name, value, enabled, label) +VALUES ( + 'opac.browse.warnable_regexp_per_class', + '{"title": "^(a|the|an)\\s"}', + FALSE, + oils_i18n_gettext( + 'opac.browse.warnable_regexp_per_class', + 'Map of search classes to regular expressions to warn user about leading articles.', + 'cgf', + 'label' + ) +); + INSERT INTO config.usr_setting_type (name,opac_visible,label,description,datatype) VALUES ( diff --git a/Open-ILS/src/sql/Pg/999.functions.global.sql b/Open-ILS/src/sql/Pg/999.functions.global.sql index 404480f018..e419dae15c 100644 --- a/Open-ILS/src/sql/Pg/999.functions.global.sql +++ b/Open-ILS/src/sql/Pg/999.functions.global.sql @@ -1537,7 +1537,7 @@ BEGIN FROM authority.control_set_authority_field WHERE tag IN ( SELECT UNNEST( - XPATH('//*[starts-with(@tag,"1")]/@tag',rec_marc::XML)::TEXT[] + XPATH('//*[starts-with(@tag,"1")]/@tag',rec_marc_xml)::TEXT[] ) ) LIMIT 1; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.config-metabib-interauthority.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.config-metabib-interauthority.sql index 0424a77272..790187692d 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.config-metabib-interauthority.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.config-metabib-interauthority.sql @@ -33,7 +33,7 @@ BEGIN FROM authority.control_set_authority_field WHERE tag IN ( SELECT UNNEST( - XPATH('//*[starts-with(@tag,"1")]/@tag',rec_marc::XML)::TEXT[] + XPATH('//*[starts-with(@tag,"1")]/@tag',rec_marc_xml::XML)::TEXT[] ) ) LIMIT 1; diff --git a/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.bib-auth-browse.sql b/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.bib-auth-browse.sql index ba30e46e15..7669d93ff6 100644 --- a/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.bib-auth-browse.sql +++ b/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.bib-auth-browse.sql @@ -2,9 +2,7 @@ BEGIN; -- check whether patch can be applied -- SELECT evergreen.upgrade_deps_block_check('YYYY', :eg_version); - -ALTER TABLE metabib.browse_entry_def_map - ADD COLUMN authority BIGINT REFERENCES authority.record_entry (id) +ALTER TABLE metabib.browse_entry_def_map ADD COLUMN authority BIGINT REFERENCES authority.record_entry (id) ON DELETE SET NULL; ALTER TABLE config.metabib_field ADD COLUMN authority_xpath TEXT; @@ -184,6 +182,7 @@ BEGIN output_row.field = idx.id; output_row.source = rid; output_row.value = BTRIM(REGEXP_REPLACE(browse_text, E'\\s+', ' ', 'g')); + output_row.authority := NULL; IF idx.authority_xpath IS NOT NULL AND idx.authority_xpath <> '' THEN authority_text := oils_xpath_string( @@ -6882,4 +6881,251 @@ Revision 1.2 - Added Log Comment 2003/03/24 19:37:42 ckeith $$ WHERE name = 'mods33'; + +INSERT INTO config.global_flag (name, value, enabled, label) +VALUES ( + 'opac.browse.warnable_regexp_per_class', + '{"title": "^(a|the|an)\\s"}', + FALSE, + oils_i18n_gettext( + 'opac.browse.warnable_regexp_per_class', + 'Map of search classes to regular expressions to warn user about leading articles.', + 'cgf', + 'label' + ) +); + +ALTER TABLE metabib.browse_entry ADD COLUMN sort_value TEXT; + +CREATE OR REPLACE FUNCTION metabib.browse_entry_sort_value() +RETURNS TRIGGER AS $$ + BEGIN + NEW.sort_value = public.search_normalize(NEW.value); + RETURN NEW; + END; +$$ LANGUAGE PLPGSQL; + +CREATE TRIGGER mbe_sort_value +BEFORE INSERT OR UPDATE ON metabib.browse_entry +FOR EACH ROW EXECUTE PROCEDURE metabib.browse_entry_sort_value(); + +UPDATE metabib.browse_entry SET value = value; + +ALTER TABLE metabib.browse_entry ALTER COLUMN sort_value SET NOT NULL; + +CREATE INDEX browse_entry_sort_value_idx + ON metabib.browse_entry USING BTREE (sort_value); + +-- NOTE If I understand ordered indices correctly, an index on sort_value DESC +-- is not actually needed, even though we do have a query that does ORDER BY +-- on this column in that direction. The previous index serves for both +-- directions, and ordering in an index is only helpful for multi-column +-- indices, I think. See http://www.postgresql.org/docs/9.1/static/indexes-ordering.html + +-- CREATE INDEX CONCURRENTLY browse_entry_sort_value_idx_desc +-- ON metabib.browse_entry USING BTREE (sort_value DESC); + +CREATE TYPE metabib.flat_browse_entry_appearance AS ( + browse_entry BIGINT, + value TEXT, + fields TEXT, + authorities TEXT, + sources INT, -- visible ones, that is + row_number INT -- internal use, sort of +); + + +CREATE OR REPLACE FUNCTION metabib.browse_pivot( + search_field INT[], + browse_term TEXT +) RETURNS BIGINT AS $p$ +DECLARE + id BIGINT; +BEGIN + SELECT INTO id mbe.id FROM metabib.browse_entry mbe + JOIN metabib.browse_entry_def_map mbedm ON ( + mbedm.entry = mbe.id AND + mbedm.def = ANY(search_field) + ) + WHERE mbe.sort_value >= public.search_normalize(browse_term) + ORDER BY mbe.sort_value, mbe.value LIMIT 1; + + RETURN id; +END; +$p$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION metabib.staged_browse( + core_query TEXT, + context_org INT, + context_locations INT[], + staff BOOL, + result_limit INT, + use_offset INT +) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$ +DECLARE + core_cursor REFCURSOR; + core_record RECORD; + qpfts_query TEXT; + result_row metabib.flat_browse_entry_appearance%ROWTYPE; + results_skipped INT := 0; + results_returned INT := 0; +BEGIN + OPEN core_cursor FOR EXECUTE core_query; + + LOOP + FETCH core_cursor INTO core_record; + EXIT WHEN NOT FOUND; + + qpfts_query := + 'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, 1::INT AS rel ' || + 'FROM (SELECT UNNEST(' || + quote_literal(core_record.records) || '::BIGINT[]) AS r) rr'; + + -- We use search.query_parser_fts() for visibility testing. Yes there + -- is a reason we feed it the records for one mbe at a time instead of + -- the records for `result_limit` mbe's at a time. + SELECT INTO result_row.sources visible + FROM search.query_parser_fts( + context_org, NULL, qpfts_query, NULL, + context_locations, 0, NULL, NULL, FALSE, staff, FALSE + ) qpfts + WHERE qpfts.rel IS NULL; + + IF result_row.sources > 0 THEN + IF results_skipped < use_offset THEN + results_skipped := results_skipped + 1; + CONTINUE; + END IF; + + result_row.browse_entry := core_record.id; + result_row.authorities := core_record.authorities; + result_row.fields := core_record.fields; + result_row.value := core_record.value; + + -- This is needed so our caller can flip it and reverse it. + result_row.row_number := results_returned; + + RETURN NEXT result_row; + + results_returned := results_returned + 1; + + EXIT WHEN results_returned >= result_limit; + END IF; + END LOOP; +END; +$p$ LANGUAGE PLPGSQL; + +-- This is optimized to be fast for values of result_offset near zero. +CREATE OR REPLACE FUNCTION metabib.browse( + search_field INT[], + browse_term TEXT, + context_org INT DEFAULT NULL, + context_loc_group INT DEFAULT NULL, + staff BOOL DEFAULT FALSE, + pivot_id BIGINT DEFAULT NULL, + force_backward BOOL DEFAULT FALSE, + result_limit INT DEFAULT 10, + result_offset INT DEFAULT 0 -- Can be negative! +) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$ +DECLARE + core_query TEXT; + whole_query TEXT; + pivot_sort_value TEXT; + pivot_sort_fallback TEXT; + context_locations INT[]; + use_offset INT; + results_skipped INT := 0; +BEGIN + IF pivot_id IS NULL THEN + pivot_id := metabib.browse_pivot(search_field, browse_term); + END IF; + + SELECT INTO pivot_sort_value, pivot_sort_fallback + sort_value, value FROM metabib.browse_entry where id = pivot_id; + + IF pivot_sort_value IS NULL THEN + RETURN; + END IF; + + IF context_loc_group IS NOT NULL THEN + SELECT INTO context_locations ARRAY_AGG(location) + FROM asset.copy_location_group_map + WHERE lgroup = context_loc_group; + END IF; + + core_query := ' + SELECT + mbe.id, + mbe.value, + mbe.sort_value, + (SELECT ARRAY_AGG(src) FROM ( + SELECT DISTINCT UNNEST(ARRAY_AGG(mbedm.source)) AS src + ) ss) AS records, + (SELECT ARRAY_TO_STRING(ARRAY_AGG(authority), $$,$$) FROM ( + SELECT DISTINCT UNNEST(ARRAY_AGG(mbedm.authority)) AS authority + ) au) AS authorities, + (SELECT ARRAY_TO_STRING(ARRAY_AGG(field), $$,$$) FROM ( + SELECT DISTINCT UNNEST(ARRAY_AGG(mbedm.def)) AS field + ) fi) AS fields + FROM metabib.browse_entry mbe + JOIN metabib.browse_entry_def_map mbedm ON ( + mbedm.entry = mbe.id AND + mbedm.def = ANY(' || quote_literal(search_field) || ') + ) + WHERE '; + + -- PostgreSQL is not magic. We can't actually pass a negative offset. + IF result_offset >= 0 AND NOT force_backward THEN + use_offset := result_offset; + core_query := core_query || + ' mbe.sort_value >= ' || quote_literal(pivot_sort_value) || + ' GROUP BY 1,2,3 ORDER BY mbe.sort_value, mbe.value '; + + RETURN QUERY SELECT * FROM metabib.staged_browse( + core_query, context_org, context_locations, + staff, result_limit, use_offset + ); + ELSE + -- Part 1 of 2 to deliver what the user wants with a negative offset: + core_query := core_query || + ' mbe.sort_value < ' || quote_literal(pivot_sort_value) || + ' GROUP BY 1,2,3 ORDER BY mbe.sort_value DESC, mbe.value DESC '; + + -- Part 2 of 2 to deliver what the user wants with a negative offset: + RETURN QUERY SELECT * FROM (SELECT * FROM metabib.staged_browse( + core_query, context_org, context_locations, + staff, result_limit, use_offset + )) sb ORDER BY row_number DESC; + + END IF; +END; +$p$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION metabib.browse( + search_class TEXT, + browse_term TEXT, + context_org INT DEFAULT NULL, + context_loc_group INT DEFAULT NULL, + staff BOOL DEFAULT FALSE, + pivot_id BIGINT DEFAULT NULL, + force_backward BOOL DEFAULT FALSE, + result_limit INT DEFAULT 10, + result_offset INT DEFAULT 0 -- Can be negative, implying backward! +) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$ +BEGIN + RETURN QUERY SELECT * FROM metabib.browse( + (SELECT COALESCE(ARRAY_AGG(id), ARRAY[]::INT[]) + FROM config.metabib_field WHERE field_class = search_class), + browse_term, + context_org, + context_loc_group, + staff, + pivot_id, + force_backward, + result_limit, + result_offset + ); +END; +$p$ LANGUAGE PLPGSQL; + COMMIT; diff --git a/Open-ILS/src/templates/opac/advanced.tt2 b/Open-ILS/src/templates/opac/advanced.tt2 index 220c56fd20..5c5a2c9930 100644 --- a/Open-ILS/src/templates/opac/advanced.tt2 +++ b/Open-ILS/src/templates/opac/advanced.tt2 @@ -6,11 +6,14 @@ loc = ctx.search_ou; -%]
-