From 9a6e434ac6df77ae1f6ed14d47365036eacf40d9 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Wed, 10 Oct 2018 13:57:06 -0400 Subject: [PATCH] JBAS-1832 Integrate browse SQL diffs to 3.2 SQL Signed-off-by: Bill Erickson --- .../schema/deploy/2.12-3.2-db-upgrade-schema.sql | 369 +++++++++++---------- 1 file changed, 194 insertions(+), 175 deletions(-) diff --git a/KCLS/sql/schema/deploy/2.12-3.2-db-upgrade-schema.sql b/KCLS/sql/schema/deploy/2.12-3.2-db-upgrade-schema.sql index 884cc01fbd..dc8d6d2122 100644 --- a/KCLS/sql/schema/deploy/2.12-3.2-db-upgrade-schema.sql +++ b/KCLS/sql/schema/deploy/2.12-3.2-db-upgrade-schema.sql @@ -5661,7 +5661,7 @@ BEGIN END; $f$ LANGUAGE plpgsql ROWS 10; -CREATE OR REPLACE FUNCTION metabib.browse(search_field integer[], browse_term text, context_org integer DEFAULT NULL::integer, context_loc_group integer DEFAULT NULL::integer, staff boolean DEFAULT false, pivot_id bigint DEFAULT NULL::bigint, result_limit integer DEFAULT 10) +CREATE OR REPLACE FUNCTION metabib.browse(search_field integer[], browse_term text, context_org integer DEFAULT NULL::integer, context_loc_group integer DEFAULT NULL::integer, staff boolean DEFAULT false, pivot_id bigint DEFAULT NULL::bigint, result_limit integer DEFAULT 10, ccvm_ctype TEXT DEFAULT NULL, ccvm_code TEXT DEFAULT NULL) RETURNS SETOF metabib.flat_browse_entry_appearance AS $f$ DECLARE @@ -5677,6 +5677,7 @@ DECLARE back_to_pivot INT; forward_limit INT; forward_to_pivot INT; + ccvm_filter_join TEXT := ''; BEGIN -- First, find the pivot if we were given a browse term but not a pivot. IF pivot_id IS NULL THEN @@ -5719,6 +5720,18 @@ BEGIN forward_limit := result_limit / 2; forward_to_pivot := result_limit - 1; + IF ccvm_ctype IS NOT NULL THEN + ccvm_filter_join := $x$ + JOIN metabib.browse_entry_def_map mbedm ON (mbedm.entry = mbe.id) + JOIN metabib.record_attr_vector_list vec ON (vec.source = mbedm.source) + JOIN config.coded_value_map ccvm ON ( + ccvm.ctype = $x$ || QUOTE_LITERAL(ccvm_ctype) || $x$ + AND ccvm.id = ANY (vec.vlist) + AND ccvm.code = $x$ || QUOTE_LITERAL(ccvm_code) || $x$ + ) + $x$; + END IF; + -- This is the meat of the SQL query that finds browse entries. We'll -- pass this to a function which uses it with a cursor, so that individual -- rows may be fetched in a loop until some condition is satisfied, without @@ -5728,43 +5741,19 @@ SELECT mbe.id, mbe.value, mbe.sort_value FROM metabib.browse_entry mbe - WHERE ( - EXISTS ( -- are there any bibs using this mbe via the requested fields? - SELECT 1 - FROM metabib.browse_entry_def_map mbedm - WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ') - ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields? - SELECT 1 - FROM metabib.browse_entry_simple_heading_map mbeshm - JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id ) - JOIN authority.control_set_auth_field_metabib_field_map_refs map ON ( - ash.atag = map.authority_field - AND map.metabib_field = ANY(' || quote_literal(search_field) || ') - ) - JOIN authority.control_set_authority_field acsaf ON ( - map.authority_field = acsaf.id - ) - JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field) - WHERE mbeshm.entry = mbe.id - AND ahf.heading_purpose IN (' || $$'variant'$$ || ') - -- and authority that variant is coming from is linked to a bib - AND EXISTS ( - SELECT 1 - FROM metabib.browse_entry_def_map mbedm2 - WHERE mbedm2.authority = ash.record AND mbedm2.def = ANY(' || quote_literal(search_field) || ') - ) - ) - ) AND '; + ' || ccvm_filter_join || ' + WHERE mbe.metabib_fields_cache && ' || quote_literal(search_field) || + ' AND '; -- This is the variant of the query for browsing backward. back_query := core_query || - ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) || - ' ORDER BY mbe.sort_value DESC, mbe.value DESC LIMIT 1000'; + ' mbe.combo_sort_value <= ' || quote_literal(pivot_sort_value) || + ' ORDER BY mbe.combo_sort_value DESC '; -- This variant browses forward. forward_query := core_query || - ' mbe.sort_value > ' || quote_literal(pivot_sort_value) || - ' ORDER BY mbe.sort_value, mbe.value LIMIT 1000'; + ' mbe.combo_sort_value > ' || quote_literal(pivot_sort_value) || + ' ORDER BY mbe.combo_sort_value '; -- We now call the function which applies a cursor to the provided -- queries, stopping at the appropriate limits and also giving us @@ -5772,16 +5761,45 @@ SELECT mbe.id, RETURN QUERY SELECT * FROM metabib.staged_browse( back_query, search_field, context_org, context_locations, - staff, browse_superpage_size, TRUE, back_limit, back_to_pivot + staff, browse_superpage_size, TRUE, back_limit, back_to_pivot, + ccvm_ctype, ccvm_code ) UNION SELECT * FROM metabib.staged_browse( forward_query, search_field, context_org, context_locations, - staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot + staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot, + ccvm_ctype, ccvm_code ) ORDER BY row_number DESC; - END; $f$ LANGUAGE plpgsql ROWS 10; +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, + result_limit INT DEFAULT 10, + ccvm_ctype TEXT DEFAULT NULL, + ccvm_code TEXT DEFAULT NULL +) 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, + result_limit, + ccvm_ctype, + ccvm_code + ); +END; +$p$ LANGUAGE PLPGSQL; + + -- SELECT evergreen.upgrade_deps_block_check('1072', :eg_version); --gmcharlt/kmlussier @@ -5935,6 +5953,114 @@ DROP FUNCTION metabib.reingest_metabib_field_entries(BIGINT, BOOL, BOOL, BOOL); DROP FUNCTION biblio.extract_metabib_field_entry(BIGINT); DROP FUNCTION biblio.extract_metabib_field_entry(BIGINT, TEXT); +CREATE OR REPLACE FUNCTION metabib.reingest_metabib_field_entries( bib_id BIGINT, skip_facet BOOL DEFAULT FALSE, skip_browse BOOL DEFAULT FALSE, skip_search BOOL DEFAULT FALSE ) RETURNS VOID AS $func$ +DECLARE + fclass RECORD; + ind_data metabib.field_entry_template%ROWTYPE; + mbe_row metabib.browse_entry%ROWTYPE; + mbe_id BIGINT; + b_skip_facet BOOL; + b_skip_browse BOOL; + b_skip_search BOOL; + value_prepped TEXT; + combo_sort_val TEXT; +BEGIN + + SELECT COALESCE(NULLIF(skip_facet, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_facet_indexing' AND enabled)) INTO b_skip_facet; + SELECT COALESCE(NULLIF(skip_browse, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_browse_indexing' AND enabled)) INTO b_skip_browse; + SELECT COALESCE(NULLIF(skip_search, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_search_indexing' AND enabled)) INTO b_skip_search; + + PERFORM * FROM config.internal_flag WHERE name = 'ingest.assume_inserts_only' AND enabled; + IF NOT FOUND THEN + IF NOT b_skip_search THEN + FOR fclass IN SELECT * FROM config.metabib_class LOOP + -- RAISE NOTICE 'Emptying out %', fclass.name; + EXECUTE $$DELETE FROM metabib.$$ || fclass.name || $$_field_entry WHERE source = $$ || bib_id; + END LOOP; + END IF; + IF NOT b_skip_facet THEN + DELETE FROM metabib.facet_entry WHERE source = bib_id; + END IF; + IF NOT b_skip_browse THEN + DELETE FROM metabib.browse_entry_def_map WHERE source = bib_id; + END IF; + END IF; + + FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( bib_id ) LOOP + + -- don't store what has been normalized away + CONTINUE WHEN ind_data.value IS NULL; + + IF ind_data.field < 0 THEN + ind_data.field = -1 * ind_data.field; + END IF; + + IF ind_data.facet_field AND NOT b_skip_facet THEN + INSERT INTO metabib.facet_entry (field, source, value) + VALUES (ind_data.field, ind_data.source, ind_data.value); + END IF; + + IF ind_data.browse_field AND NOT b_skip_browse THEN + -- A caveat about this SELECT: this should take care of replacing + -- old mbe rows when data changes, but not if normalization (by + -- which I mean specifically the output of + -- evergreen.oils_tsearch2()) changes. It may or may not be + -- expensive to add a comparison of index_vector to index_vector + -- to the WHERE clause below. + + CONTINUE WHEN ind_data.sort_value IS NULL; + + value_prepped := metabib.browse_normalize(ind_data.value, ind_data.field); + + combo_sort_val := + SUBSTR((ind_data.sort_value || value_prepped), 1, 2048); + + SELECT INTO mbe_row * FROM metabib.browse_entry + WHERE value = value_prepped AND sort_value = sort_value; + + IF FOUND THEN + mbe_id := mbe_row.id; + ELSE + INSERT INTO metabib.browse_entry + ( value, sort_value, combo_sort_value, metabib_fields_cache) + VALUES + ( value_prepped, sort_value_prepped, combo_sort_val, '{}' ); + + mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS); + END IF; + + INSERT INTO metabib.browse_entry_def_map (entry, def, source, authority) + VALUES (mbe_id, ind_data.field, ind_data.source, ind_data.authority); + END IF; + + IF ind_data.search_field AND NOT b_skip_search THEN + -- Avoid inserting duplicate rows + EXECUTE 'SELECT 1 FROM metabib.' || ind_data.field_class || + '_field_entry WHERE field = $1 AND source = $2 AND value = $3' + INTO mbe_id USING ind_data.field, ind_data.source, ind_data.value; + -- RAISE NOTICE 'Search for an already matching row returned %', mbe_id; + IF mbe_id IS NULL THEN + EXECUTE $$ + INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value) + VALUES ($$ || + quote_literal(ind_data.field) || $$, $$ || + quote_literal(ind_data.source) || $$, $$ || + quote_literal(ind_data.value) || + $$);$$; + END IF; + END IF; + + END LOOP; + + IF NOT b_skip_search THEN + PERFORM metabib.update_combined_index_vectors(bib_id); + END IF; + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + + CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry ( rid BIGINT, default_joiner TEXT, @@ -6162,136 +6288,6 @@ END; $func$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION metabib.reingest_metabib_field_entries( - bib_id BIGINT, - skip_facet BOOL DEFAULT FALSE, - skip_display BOOL DEFAULT FALSE, - skip_browse BOOL DEFAULT FALSE, - skip_search BOOL DEFAULT FALSE, - only_fields INT[] DEFAULT '{}'::INT[] -) RETURNS VOID AS $func$ -DECLARE - fclass RECORD; - ind_data metabib.field_entry_template%ROWTYPE; - mbe_row metabib.browse_entry%ROWTYPE; - mbe_id BIGINT; - b_skip_facet BOOL; - b_skip_display BOOL; - b_skip_browse BOOL; - b_skip_search BOOL; - value_prepped TEXT; - field_list INT[] := only_fields; - field_types TEXT[] := '{}'::TEXT[]; -BEGIN - - IF field_list = '{}'::INT[] THEN - SELECT ARRAY_AGG(id) INTO field_list FROM config.metabib_field; - END IF; - - SELECT COALESCE(NULLIF(skip_facet, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_facet_indexing' AND enabled)) INTO b_skip_facet; - SELECT COALESCE(NULLIF(skip_display, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_display_indexing' AND enabled)) INTO b_skip_display; - SELECT COALESCE(NULLIF(skip_browse, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_browse_indexing' AND enabled)) INTO b_skip_browse; - SELECT COALESCE(NULLIF(skip_search, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_search_indexing' AND enabled)) INTO b_skip_search; - - IF NOT b_skip_facet THEN field_types := field_types || '{facet}'; END IF; - IF NOT b_skip_display THEN field_types := field_types || '{display}'; END IF; - IF NOT b_skip_browse THEN field_types := field_types || '{browse}'; END IF; - IF NOT b_skip_search THEN field_types := field_types || '{search}'; END IF; - - PERFORM * FROM config.internal_flag WHERE name = 'ingest.assume_inserts_only' AND enabled; - IF NOT FOUND THEN - IF NOT b_skip_search THEN - FOR fclass IN SELECT * FROM config.metabib_class LOOP - -- RAISE NOTICE 'Emptying out %', fclass.name; - EXECUTE $$DELETE FROM metabib.$$ || fclass.name || $$_field_entry WHERE source = $$ || bib_id; - END LOOP; - END IF; - IF NOT b_skip_facet THEN - DELETE FROM metabib.facet_entry WHERE source = bib_id; - END IF; - IF NOT b_skip_display THEN - DELETE FROM metabib.display_entry WHERE source = bib_id; - END IF; - IF NOT b_skip_browse THEN - DELETE FROM metabib.browse_entry_def_map WHERE source = bib_id; - END IF; - END IF; - - FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( bib_id, ' ', field_types, field_list ) LOOP - - -- don't store what has been normalized away - CONTINUE WHEN ind_data.value IS NULL; - - IF ind_data.field < 0 THEN - ind_data.field = -1 * ind_data.field; - END IF; - - IF ind_data.facet_field AND NOT b_skip_facet THEN - INSERT INTO metabib.facet_entry (field, source, value) - VALUES (ind_data.field, ind_data.source, ind_data.value); - END IF; - - IF ind_data.display_field AND NOT b_skip_display THEN - INSERT INTO metabib.display_entry (field, source, value) - VALUES (ind_data.field, ind_data.source, ind_data.value); - END IF; - - - IF ind_data.browse_field AND NOT b_skip_browse THEN - -- A caveat about this SELECT: this should take care of replacing - -- old mbe rows when data changes, but not if normalization (by - -- which I mean specifically the output of - -- evergreen.oils_tsearch2()) changes. It may or may not be - -- expensive to add a comparison of index_vector to index_vector - -- to the WHERE clause below. - - CONTINUE WHEN ind_data.sort_value IS NULL; - - value_prepped := metabib.browse_normalize(ind_data.value, ind_data.field); - SELECT INTO mbe_row * FROM metabib.browse_entry - WHERE value = value_prepped AND sort_value = ind_data.sort_value; - - IF FOUND THEN - mbe_id := mbe_row.id; - ELSE - INSERT INTO metabib.browse_entry - ( value, sort_value ) VALUES - ( value_prepped, ind_data.sort_value ); - - mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS); - END IF; - - INSERT INTO metabib.browse_entry_def_map (entry, def, source, authority) - VALUES (mbe_id, ind_data.field, ind_data.source, ind_data.authority); - END IF; - - IF ind_data.search_field AND NOT b_skip_search THEN - -- Avoid inserting duplicate rows - EXECUTE 'SELECT 1 FROM metabib.' || ind_data.field_class || - '_field_entry WHERE field = $1 AND source = $2 AND value = $3' - INTO mbe_id USING ind_data.field, ind_data.source, ind_data.value; - -- RAISE NOTICE 'Search for an already matching row returned %', mbe_id; - IF mbe_id IS NULL THEN - EXECUTE $$ - INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value) - VALUES ($$ || - quote_literal(ind_data.field) || $$, $$ || - quote_literal(ind_data.source) || $$, $$ || - quote_literal(ind_data.value) || - $$);$$; - END IF; - END IF; - - END LOOP; - - IF NOT b_skip_search THEN - PERFORM metabib.update_combined_index_vectors(bib_id); - END IF; - - RETURN; -END; -$func$ LANGUAGE PLPGSQL; - -- AFTER UPDATE OR INSERT trigger for biblio.record_entry CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$ DECLARE @@ -6316,15 +6312,7 @@ BEGIN DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = NEW.id; -- Separate any multi-homed items - -- DELETE FROM metabib.browse_entry_def_map WHERE source = NEW.id; -- Don't auto-suggest deleted bibs - -- KCLS - -- DELETE FROM metabib.browse_entry_def_map WHERE source = NEW.id; -- Don't auto-suggest deleted bibs - DELETE FROM metabib.browse_author_entry_def_map WHERE source = NEW.id; - DELETE FROM metabib.browse_series_entry_def_map WHERE source = NEW.id; - DELETE FROM metabib.browse_subject_entry_def_map WHERE source = NEW.id; - DELETE FROM metabib.browse_title_entry_def_map WHERE source = NEW.id; - DELETE FROM metabib.browse_call_number_entry_def_map WHERE source = NEW.id; - -- /KCLS + DELETE FROM metabib.browse_entry_def_map WHERE source = NEW.id; -- Don't auto-suggest deleted bibs RETURN NEW; -- and we're done END IF; @@ -7341,7 +7329,7 @@ $f$ LANGUAGE PLPGSQL STABLE ROWS 1; --INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.0.4', :eg_version); --SELECT evergreen.upgrade_deps_block_check('1088', :eg_version); -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) +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, ccvm_ctype TEXT DEFAULT NULL, ccvm_code TEXT DEFAULT NULL) RETURNS SETOF metabib.flat_browse_entry_appearance AS $f$ DECLARE @@ -7475,6 +7463,37 @@ BEGIN result_row.sources := 0; result_row.asources := 0; + /** Remove refs to linked bib records that do not + match the requested ccvm filter */ + IF ccvm_ctype IS NOT NULL THEN + + -- bib-linked bibs + IF ARRAY_UPPER(all_brecords, 1) IS NOT NULL THEN + SELECT INTO all_brecords ARRAY_AGG(DISTINCT vec.source) + FROM metabib.record_attr_vector_list vec + JOIN config.coded_value_map ccvm + ON ( + ccvm.ctype = ccvm_ctype + AND ccvm.code = ccvm_code + AND ccvm.id = ANY (vec.vlist) + ) + WHERE vec.source = ANY(all_brecords); + END IF; + + -- auth-linked bibs + IF ARRAY_UPPER(all_arecords, 1) IS NOT NULL THEN + SELECT INTO all_arecords ARRAY_AGG(DISTINCT vec.source) + FROM metabib.record_attr_vector_list vec + JOIN config.coded_value_map ccvm + ON ( + ccvm.ctype = ccvm_ctype + AND ccvm.code = ccvm_code + AND ccvm.id = ANY (vec.vlist) + ) + WHERE vec.source = ANY(all_arecords); + END IF; + END IF; + -- Bib-linked vis checking IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN -- 2.11.0