From 28824da5d12fe2357e97806326ba7c2cffe99cac Mon Sep 17 00:00:00 2001 From: Srey Seng Date: Fri, 5 Sep 2014 14:28:07 -0700 Subject: [PATCH] LP#1358392: See references not always displaying on browse search When browsing for an unauthorized heading, See references are not always displaying on browse search. Those that do display surface due to the existence of cross-reference(s). Desired behaviour is that unauthorized headings should also surface in browse results, even without the existence of cross-reference(s), as long as the authority record containing the unauthorized heading controls at least one bib. There are many authority records that do not have cross-references but do contain unauthorized headings, so browse results that would otherwise be hidden would now be exposed. This fix adds an additional logic to check if the browse term is an unauthorized term and if so, perform a slightly modified procedure but otherwise, perform the usual procedure so as not to break any existing functionality. Signed-off-by: Srey Seng --- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 49 +++-- .../XXXX.function.metabib.staged_browse.sql | 234 +++++++++++++++++++++ 2 files changed, 269 insertions(+), 14 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.function.metabib.staged_browse.sql diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index f79d09e3a8..8980f108b0 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -2140,6 +2140,7 @@ DECLARE all_arecords BIGINT[]; superpage_of_records BIGINT[]; superpage_size INT; + unauthorized_entry RECORD; BEGIN IF count_up_from_zero THEN row_number := 0; @@ -2158,23 +2159,43 @@ BEGIN RETURN; END IF; + --Is unauthorized? + SELECT INTO unauthorized_entry * + FROM metabib.browse_entry_simple_heading_map mbeshm + INNER JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id ) + INNER JOIN authority.control_set_authority_field acsaf ON ( acsaf.id = ash.atag AND acsaf.tag like '4__') + WHERE mbeshm.entry = rec.id; -- Gather aggregate data based on the MBE row we're looking at now, authority axis - SELECT INTO all_arecords, result_row.sees, afields - ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility - STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids - ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows - - FROM metabib.browse_entry_simple_heading_map mbeshm - JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id ) - JOIN authority.authority_linking aal ON ( ash.record = aal.source ) - JOIN authority.bib_linking abl ON ( aal.target = abl.authority ) - JOIN authority.control_set_auth_field_metabib_field_map_refs map ON ( - ash.atag = map.authority_field + IF (unauthorized_entry.record IS NOT NULL) THEN + --unauthorized term belongs to an auth linked to a bib? + SELECT INTO all_arecords, result_row.sees, afields + ARRAY_AGG(DISTINCT abl.bib), + STRING_AGG(DISTINCT abl.authority::TEXT, $$,$$), + ARRAY_AGG(DISTINCT map.metabib_field) + FROM authority.bib_linking abl + INNER JOIN authority.control_set_auth_field_metabib_field_map_refs map ON ( + map.authority_field = unauthorized_entry.atag AND map.metabib_field = ANY(fields) - ) - WHERE mbeshm.entry = rec.id; - + ) + WHERE abl.authority = unauthorized_entry.record; + ELSE + --do usual procedure + SELECT INTO all_arecords, result_row.sees, afields + ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility + STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids + ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows + + FROM metabib.browse_entry_simple_heading_map mbeshm + JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id ) + JOIN authority.authority_linking aal ON ( ash.record = aal.source ) + JOIN authority.bib_linking abl ON ( aal.target = abl.authority ) + JOIN authority.control_set_auth_field_metabib_field_map_refs map ON ( + ash.atag = map.authority_field + AND map.metabib_field = ANY(fields) + ) + WHERE mbeshm.entry = rec.id; + END IF; -- Gather aggregate data based on the MBE row we're looking at now, bib axis SELECT INTO all_brecords, result_row.authorities, bfields diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.metabib.staged_browse.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.metabib.staged_browse.sql new file mode 100644 index 0000000000..eabde2677a --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.metabib.staged_browse.sql @@ -0,0 +1,234 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('0880', :eg_version); + +-- Function: metabib.staged_browse(text, integer[], integer, integer[], boolean, integer, boolean, integer, integer) + +-- DROP FUNCTION metabib.staged_browse(text, integer[], integer, integer[], boolean, integer, boolean, integer, integer); + +CREATE OR REPLACE FUNCTION metabib.staged_browse(query text, fields integer[], context_org integer, context_locations integer[], staff boolean, browse_superpage_size integer, count_up_from_zero boolean, result_limit integer, next_pivot_pos integer) + RETURNS SETOF metabib.flat_browse_entry_appearance AS +$BODY$ +DECLARE + curs REFCURSOR; + rec RECORD; + qpfts_query TEXT; + aqpfts_query TEXT; + afields INT[]; + bfields INT[]; + result_row metabib.flat_browse_entry_appearance%ROWTYPE; + results_skipped INT := 0; + row_counter INT := 0; + row_number INT; + slice_start INT; + slice_end INT; + full_end INT; + all_records BIGINT[]; + all_brecords BIGINT[]; + all_arecords BIGINT[]; + superpage_of_records BIGINT[]; + superpage_size INT; + unauthorized_entry RECORD; +BEGIN + IF count_up_from_zero THEN + row_number := 0; + ELSE + row_number := -1; + END IF; + + OPEN curs FOR EXECUTE query; + + LOOP + FETCH curs INTO rec; + IF NOT FOUND THEN + IF result_row.pivot_point IS NOT NULL THEN + RETURN NEXT result_row; + END IF; + RETURN; + END IF; + -- Gather aggregate data based on the MBE row we're looking at now, authority axis + + --Is unauthorized, i.e., 4xx on an auth record? + SELECT INTO unauthorized_entry * + FROM metabib.browse_entry_simple_heading_map mbeshm + INNER JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id ) + INNER JOIN authority.control_set_authority_field acsaf ON ( acsaf.id = ash.atag AND acsaf.tag like '4__') + WHERE mbeshm.entry = rec.id; + + IF (unauthorized_entry.record IS NOT NULL) THEN + --Do unauthorized procedure, use the authorized term's auth record and it's bibs + SELECT INTO all_arecords, result_row.sees, afields + ARRAY_AGG(DISTINCT abl.bib), + STRING_AGG(DISTINCT abl.authority::TEXT, $$,$$), + ARRAY_AGG(DISTINCT map.metabib_field) + FROM authority.bib_linking abl + INNER JOIN authority.control_set_auth_field_metabib_field_map_refs map ON ( + map.authority_field = unauthorized_entry.atag + AND map.metabib_field = ANY(fields) + ) + WHERE abl.authority = unauthorized_entry.record; + ELSE + --Do 'usual' procedures + SELECT INTO all_arecords, result_row.sees, afields + ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility + STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids + ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows + + FROM metabib.browse_entry_simple_heading_map mbeshm + JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id ) + JOIN authority.authority_linking aal ON ( ash.record = aal.source ) + JOIN authority.bib_linking abl ON ( aal.target = abl.authority ) + JOIN authority.control_set_auth_field_metabib_field_map_refs map ON ( + ash.atag = map.authority_field + AND map.metabib_field = ANY(fields) + ) + WHERE mbeshm.entry = rec.id; + END IF; + + -- Gather aggregate data based on the MBE row we're looking at now, bib axis + SELECT INTO all_brecords, result_row.authorities, bfields + ARRAY_AGG(DISTINCT source), + STRING_AGG(DISTINCT authority::TEXT, $$,$$), + ARRAY_AGG(DISTINCT def) + FROM metabib.browse_entry_def_map + WHERE entry = rec.id + AND def = ANY(fields); + + SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x; + + result_row.sources := 0; + result_row.asources := 0; + + -- Bib-linked vis checking + IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN + + full_end := ARRAY_LENGTH(all_brecords, 1); + superpage_size := COALESCE(browse_superpage_size, full_end); + slice_start := 1; + slice_end := superpage_size; + + WHILE result_row.sources = 0 AND slice_start <= full_end LOOP + superpage_of_records := all_brecords[slice_start:slice_end]; + qpfts_query := + 'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' || + '1::INT AS rel FROM (SELECT UNNEST(' || + quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr'; + + -- We use search.query_parser_fts() for visibility testing. + -- We're calling it once per browse-superpage worth of records + -- out of the set of records related to a given mbe, until we've + -- either exhausted that set of records or found at least 1 + -- visible record. + + 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; + + slice_start := slice_start + superpage_size; + slice_end := slice_end + superpage_size; + END LOOP; + + -- Accurate? Well, probably. + result_row.accurate := browse_superpage_size IS NULL OR + browse_superpage_size >= full_end; + + END IF; + + -- Authority-linked vis checking + IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN + + full_end := ARRAY_LENGTH(all_arecords, 1); + superpage_size := COALESCE(browse_superpage_size, full_end); + slice_start := 1; + slice_end := superpage_size; + + WHILE result_row.asources = 0 AND slice_start <= full_end LOOP + superpage_of_records := all_arecords[slice_start:slice_end]; + qpfts_query := + 'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' || + '1::INT AS rel FROM (SELECT UNNEST(' || + quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr'; + + -- We use search.query_parser_fts() for visibility testing. + -- We're calling it once per browse-superpage worth of records + -- out of the set of records related to a given mbe, via + -- authority until we've either exhausted that set of records + -- or found at least 1 visible record. + + SELECT INTO result_row.asources 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; + + slice_start := slice_start + superpage_size; + slice_end := slice_end + superpage_size; + END LOOP; + + + -- Accurate? Well, probably. + result_row.aaccurate := browse_superpage_size IS NULL OR + browse_superpage_size >= full_end; + + END IF; + + IF result_row.sources > 0 OR result_row.asources > 0 THEN + -- The function that calls this function needs row_number in order + -- to correctly order results from two different runs of this + -- functions. + result_row.row_number := row_number; + + -- Now, if row_counter is still less than limit, return a row. If + -- not, but it is less than next_pivot_pos, continue on without + -- returning actual result rows until we find + -- that next pivot, and return it. + + IF row_counter < result_limit THEN + result_row.browse_entry := rec.id; + result_row.value := rec.value; + + RETURN NEXT result_row; + ELSE + result_row.browse_entry := NULL; + result_row.authorities := NULL; + result_row.fields := NULL; + result_row.value := NULL; + result_row.sources := NULL; + result_row.sees := NULL; + result_row.accurate := NULL; + result_row.aaccurate := NULL; + result_row.pivot_point := rec.id; + + IF row_counter >= next_pivot_pos THEN + RETURN NEXT result_row; + RETURN; + END IF; + END IF; + + IF count_up_from_zero THEN + row_number := row_number + 1; + ELSE + row_number := row_number - 1; + END IF; + + -- row_counter is different from row_number. + -- It simply counts up from zero so that we know when + -- we've reached our limit. + row_counter := row_counter + 1; + END IF; + END LOOP; +END; +$BODY$ + LANGUAGE plpgsql VOLATILE + COST 100 + ROWS 1000; +ALTER FUNCTION metabib.staged_browse(text, integer[], integer, integer[], boolean, integer, boolean, integer, integer) + OWNER TO evergreen; + + +COMMIT; + -- 2.11.0