From 576056b70624582ab50d50204a6ef95d72aa13dd Mon Sep 17 00:00:00 2001 From: Lebbeous Fogle-Weekley Date: Wed, 24 Apr 2013 16:15:27 -0400 Subject: [PATCH] metabib.browse() works well enough to move on, but ... ... does scoping/vis-checking wrong though Signed-off-by: Lebbeous Fogle-Weekley --- .../perlmods/lib/OpenILS/WWW/EGCatLoader/Browse.pm | 20 +++ .../sql/Pg/upgrade/YYYY.schema.bib-auth-browse.sql | 190 +++++++++++++++++++++ Open-ILS/src/templates/opac/browse.tt2 | 6 +- .../src/templates/opac/parts/qtype_selector.tt2 | 13 +- 4 files changed, 219 insertions(+), 10 deletions(-) diff --git a/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Browse.pm b/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Browse.pm index 250f449a3b..8a328b1be5 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Browse.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Browse.pm @@ -21,6 +21,26 @@ my $U = 'OpenILS::Application::AppUtils'; sub load_browse { my ($self) = @_; + + if ($self->cgi->param('qtype') and $self->cgi->param('bterm')) { + # Let's get some browse results! + + $self->ctx->{results} = $self->editor->json_query({ + from => [ + "metabib.browse", + scalar $self->cgi->param('qtype'), + scalar $self->cgi->param('bterm'), + $self->ctx->{copy_location_group_org}, + $self->ctx->{copy_location_group}, + scalar $self->cgi->param('limit'), + scalar $self->cgi->param('offset') + ] + }) or $self->apache->log->warn( + "error in browse: " . $self->editor->event->{textcode} + ); + } + + return Apache2::Const::OK; } 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..53dbe3dc5c 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 @@ -6882,4 +6882,194 @@ Revision 1.2 - Added Log Comment 2003/03/24 19:37:42 ckeith $$ WHERE name = 'mods33'; +-- ---------------------------------------------------- +-- XXX TODO From here down, add this to stock SQL files + +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 ASC); + +CREATE TYPE metabib.slim_browse_entry AS ( + browse_entry BIGINT, + value TEXT, + authority BIGINT +); + + +CREATE OR REPLACE FUNCTION metabib._browse_joins_and_where( + search_field INT[], + browse_term TEXT, + context_org INT DEFAULT NULL, + context_loc_group INT DEFAULT NULL +) RETURNS TEXT[] AS $p$ +DECLARE + joins TEXT; + where_clause TEXT; +BEGIN + joins := ' + JOIN metabib.browse_entry_def_map mbedm ON ( + mbedm.entry = mbe.id AND + mbedm.def = ANY(' || quote_literal(search_field) || ') + ) '; + where_clause := ''; + + IF context_org IS NOT NULL OR context_loc_group IS NOT NULL THEN + -- XXX At some point we surely need more comprehensive/correct + -- holdings testing that just a join on aovc (located URIs, etc). + -- And we probably need to support a browse equivalent of search's + -- #staff modifier, i.e. scope but don't limit on visibility. + joins := joins || ' + JOIN asset.opac_visible_copies aovc ON (aovc.record = mbedm.source) + '; + + IF context_org IS NOT NULL THEN + where_clause := where_clause || + 'aovc.circ_lib IN ( + SELECT id FROM actor.org_unit_descendants(' || + context_org || ')) AND '; + END IF; + + IF context_loc_group IS NOT NULL THEN + joins := joins ||' JOIN asset.copy acp ON (acp.id = aovc.copy_id) '; + where_clause := where_clause || + 'acp.location IN (SELECT location FROM asset.copy_location_group_map WHERE lgroup = ' || + context_loc_group || ') AND '; + END IF; + END IF; + + RETURN ARRAY[joins, where_clause]; +END; + +$p$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION metabib.browse_pivot( + search_field INT[], + browse_term TEXT, + context_org INT DEFAULT NULL, + context_loc_group INT DEFAULT NULL +) RETURNS TEXT AS $p$ +DECLARE + joins TEXT; + where_clause TEXT; + joins_and_where TEXT[]; + result TEXT; +BEGIN + joins_and_where := metabib._browse_joins_and_where( + search_field, browse_term, + context_org, context_loc_group + ); + + joins := joins_and_where[1]; + where_clause := joins_and_where[2]; + + EXECUTE 'SELECT mbe.sort_value FROM metabib.browse_entry mbe ' || + joins || 'WHERE ' || where_clause || + ' mbe.sort_value >= ' || quote_literal(browse_term) || + ' ORDER BY mbe.sort_value LIMIT 1 ' INTO result; + + RETURN result; -- note, can be NULL +END; +$p$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION metabib.browse( + search_field INT[], + browse_term TEXT, + context_org INT DEFAULT NULL, + context_loc_group INT DEFAULT NULL, + result_limit INT DEFAULT 10, + result_offset INT DEFAULT 0 +) RETURNS SETOF metabib.slim_browse_entry AS $p$ +DECLARE + joins TEXT; + where_clause TEXT; + joins_and_where TEXT[]; + result_query TEXT; + pivot_sort_value TEXT; + f RECORD; + r metabib.slim_browse_entry%ROWTYPE; +BEGIN + pivot_sort_value := metabib.browse_pivot( + search_field, browse_term, + context_org, context_loc_group + ); + + IF pivot_sort_value IS NULL THEN + RETURN; + END IF; + + joins_and_where := metabib._browse_joins_and_where( + search_field, browse_term, + context_org, context_loc_group + ); + + joins := joins_and_where[1]; + where_clause := joins_and_where[2]; + + -- Now we query for the "page" of records starting at the pivot. If we + -- wanted to put the pivot in the middle, I guess we'd need two queries? + + -- XXX I really don't see the value of finding the pivot point as we could + -- get the exact same results below by just using the browse_term + -- directly where we use pivot_sort_value. If we wanted before and after + -- context, we'd still need two queries (or maybe fancy windowing?) exactly + -- as we would even if we didn't find the pivot first. + + result_query := + 'SELECT mbe.id, mbe.value, mbedm.authority, mbe.sort_value + FROM metabib.browse_entry mbe ' || + joins || + 'WHERE ' || where_clause || + ' mbe.sort_value >= ' || quote_literal(pivot_sort_value) || + ' GROUP BY 1,2,3,4 ORDER BY mbe.sort_value ' || + ' LIMIT ' || result_limit || + ' OFFSET ' || result_offset; + + FOR f IN EXECUTE result_query LOOP + r.browse_entry := f.id; + r.value := f.value; + r.authority := f.authority; + + RETURN NEXT r; + END LOOP; +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, + result_limit INT DEFAULT 10, + result_offset INT DEFAULT 0 +) RETURNS SETOF metabib.slim_browse_entry 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, + result_limit, + result_offset + ); +END; +$p$ LANGUAGE PLPGSQL; + COMMIT; diff --git a/Open-ILS/src/templates/opac/browse.tt2 b/Open-ILS/src/templates/opac/browse.tt2 index 3c9a925d6b..ef03b1279a 100644 --- a/Open-ILS/src/templates/opac/browse.tt2 +++ b/Open-ILS/src/templates/opac/browse.tt2 @@ -19,10 +19,8 @@
- - + + [% INCLUDE "opac/parts/qtype_selector.tt2" id="browse-search-class" browse_only=1 %] diff --git a/Open-ILS/src/templates/opac/parts/qtype_selector.tt2 b/Open-ILS/src/templates/opac/parts/qtype_selector.tt2 index eda10a330d..30edbc6e9f 100644 --- a/Open-ILS/src/templates/opac/parts/qtype_selector.tt2 +++ b/Open-ILS/src/templates/opac/parts/qtype_selector.tt2 @@ -1,16 +1,17 @@ [% query_types = [ {value => "keyword", label => l("Keyword")}, - {value => "title", label => l("Title")}, + {value => "title", label => l("Title"), browse => 1}, {value => "jtitle", label => l("Journal Title")}, - {value => "author", label => l("Author")}, - {value => "subject", label => l("Subject")}, - {value => "series", label => l("Series")}, + {value => "author", label => l("Author"), browse => 1}, + {value => "subject", label => l("Subject"), browse => 1}, + {value => "series", label => l("Series"), browse => 1}, {value => "id|bibcn", label => l("Bib Call Number")} ] %] -