From c168a3df32fead3bf664789cc3fde637642939c8 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Tue, 30 Oct 2018 09:12:19 -0700 Subject: [PATCH] JBAS-1839 Browse entries index md5 vals; other SQL Signed-off-by: Bill Erickson --- KCLS/sql/schema/deploy/stock-browse-schema.sql | 91 +++++++++++++++++--------- 1 file changed, 60 insertions(+), 31 deletions(-) diff --git a/KCLS/sql/schema/deploy/stock-browse-schema.sql b/KCLS/sql/schema/deploy/stock-browse-schema.sql index d73c21fe41..cd9897eb5d 100644 --- a/KCLS/sql/schema/deploy/stock-browse-schema.sql +++ b/KCLS/sql/schema/deploy/stock-browse-schema.sql @@ -24,7 +24,7 @@ ALTER TABLE metabib.browse_entry -- prevent dupes CREATE UNIQUE INDEX browse_entry_sort_value_value_key - ON metabib.browse_entry USING btree (DIGEST((value || sort_value), 'sha1')); + ON metabib.browse_entry USING btree (MD5(value || sort_value)); -- KCLS has this index already -- drop now for speed, add back later. @@ -33,11 +33,11 @@ DROP INDEX IF EXISTS metabib.browse_entry_combo_sort_value_idx; -- substr the indexes to avoid insert failures on excessively long values DROP INDEX IF EXISTS metabib.browse_entry_sort_value_idx; CREATE INDEX browse_entry_sort_value_idx - ON metabib.browse_entry (SUBSTR(sort_value, 1, 2048)); + ON metabib.browse_entry (MD5(sort_value)); DROP INDEX IF EXISTS metabib.browse_entry_value_idx; CREATE INDEX browse_entry_value_idx - ON metabib.browse_entry (SUBSTR(value, 1, 2048)); + ON metabib.browse_entry (MD5(value)); -- KCLS custom CREATE OR REPLACE FUNCTION @@ -84,14 +84,6 @@ BEGIN END; $FUNK$ LANGUAGE PLPGSQL; -CREATE TRIGGER maintain_browse_metabib_fields_cache_bib_tgr - AFTER INSERT OR DELETE ON metabib.browse_entry_def_map - FOR EACH ROW EXECUTE PROCEDURE metabib.maintain_browse_metabib_fields_cache(); - -CREATE TRIGGER maintain_browse_metabib_fields_cache_auth_tgr - AFTER INSERT OR DELETE ON metabib.browse_entry_simple_heading_map - FOR EACH ROW EXECUTE PROCEDURE metabib.maintain_browse_metabib_fields_cache(); - CREATE OR REPLACE FUNCTION metabib.browse_pivot( search_field INT[], browse_term TEXT ) RETURNS BIGINT AS $p$ @@ -142,7 +134,6 @@ DECLARE 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; @@ -191,19 +182,22 @@ BEGIN 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; + WHERE + MD5(value) = MD5(value_prepped) AND + MD5(sort_value) = MD5(ind_data.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, '{}' ); + VALUES ( + value_prepped, + ind_data.sort_value, + SUBSTR((ind_data.sort_value || value_prepped), 1, 2048) + '{}' + ); mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS); END IF; @@ -634,7 +628,6 @@ DECLARE mbe_row metabib.browse_entry%ROWTYPE; mbe_id BIGINT; ash_id BIGINT; - combo_sort_val TEXT; BEGIN IF NEW.deleted IS TRUE THEN -- If this authority is deleted @@ -681,17 +674,20 @@ BEGIN ash_id := CURRVAL('authority.simple_heading_id_seq'::REGCLASS); - combo_sort_val := SUBSTR((ashs.sort_value || ashs.value), 1, 2048); - SELECT INTO mbe_row * FROM metabib.browse_entry - WHERE value = ashs.value AND sort_value = ashs.sort_value; + WHERE + MD5(value) = MD5(ashs.value) AND + MD5(sort_value) = MD5(ashs.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 - ( ashs.value, ashs.sort_value, combo_sort_val, '{}' ); + ( ashs.value, ashs.sort_value, + SUBSTR((ashs.sort_value || ashs.value), 1, 2048), + '{}' + ); mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS); END IF; @@ -755,8 +751,8 @@ BEGIN IF NOT force THEN SELECT INTO existing_entry * FROM metabib.browse_entry entry - WHERE entry.value = legacy_entry.value - AND entry.sort_value = legacy_entry.sort_value; + WHERE MD5(entry.value) = MD5(legacy_entry.value) AND + MD5(entry.sort_value) = MD5(legacy_entry.sort_value); END IF; IF existing_entry.id IS NOT NULL THEN @@ -776,7 +772,7 @@ BEGIN INSERT INTO metabib.browse_entry (value, index_vector, sort_value, combo_sort_value) SELECT - legacy_entry.value, + legacy_entry.value, legacy_entry.index_vector, legacy_entry.sort_value, SUBSTR((legacy_entry.sort_value || legacy_entry.value), 1, 2048) @@ -959,7 +955,7 @@ BEGIN PERFORM metabib.migrate_legacy_browse_entry(legacy_entry, 'title', TRUE); counter := counter + 1; IF (counter % 50000) = 0 THEN - RAISE NOTICE 'Migrated % entries.', counter; + RAISE NOTICE 'Migrated % entries. %', counter, CLOCK_TIMESTAMP(); END IF; END LOOP; @@ -970,7 +966,7 @@ BEGIN PERFORM metabib.migrate_legacy_browse_entry(legacy_entry, 'author'); counter := counter + 1; IF (counter % 50000) = 0 THEN - RAISE NOTICE 'Migrated % entries.', counter; + RAISE NOTICE 'Migrated % entries. %', counter, CLOCK_TIMESTAMP(); END IF; END LOOP; @@ -981,7 +977,7 @@ BEGIN PERFORM metabib.migrate_legacy_browse_entry(legacy_entry, 'subject'); counter := counter + 1; IF (counter % 50000) = 0 THEN - RAISE NOTICE 'Migrated % entries.', counter; + RAISE NOTICE 'Migrated % entries. %', counter, CLOCK_TIMESTAMP(); END IF; END LOOP; @@ -992,7 +988,7 @@ BEGIN PERFORM metabib.migrate_legacy_browse_entry(legacy_entry, 'series'); counter := counter + 1; IF (counter % 50000) = 0 THEN - RAISE NOTICE 'Migrated % entries.', counter; + RAISE NOTICE 'Migrated % entries. %', counter, CLOCK_TIMESTAMP(); END IF; END LOOP; @@ -1003,12 +999,41 @@ BEGIN PERFORM metabib.migrate_legacy_browse_entry(legacy_entry, 'call_number'); counter := counter + 1; IF (counter % 50000) = 0 THEN - RAISE NOTICE 'Migrated % entries.', counter; + RAISE NOTICE 'Migrated % entries. %', counter, CLOCK_TIMESTAMP(); END IF; END LOOP; END $MIGRATE$; + +SELECT CLOCK_TIMESTAMP(), 'Populating browse entry metabib_fields_cache'; + +UPDATE metabib.browse_entry mbe +SET metabib_fields_cache = ARRAY( + SELECT DISTINCT(x.def_id) FROM ( + SELECT mbedm.def AS def_id + FROM metabib.browse_entry_def_map mbedm + WHERE mbedm.entry = mbe.id + UNION + SELECT map.metabib_field AS def_id + 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) + WHERE mbeshm.entry = mbe.id + )x +); + +SELECT CLOCK_TIMESTAMP(), 'Apply metabib_fields_cache trigger'; + +CREATE TRIGGER maintain_browse_metabib_fields_cache_bib_tgr + AFTER INSERT OR DELETE ON metabib.browse_entry_def_map + FOR EACH ROW EXECUTE PROCEDURE metabib.maintain_browse_metabib_fields_cache(); + +CREATE TRIGGER maintain_browse_metabib_fields_cache_auth_tgr + AFTER INSERT OR DELETE ON metabib.browse_entry_simple_heading_map + FOR EACH ROW EXECUTE PROCEDURE metabib.maintain_browse_metabib_fields_cache(); + -- function is unused DROP FUNCTION authority.unlinked_bibs_to_given_auth_text(TEXT, TEXT); @@ -1020,12 +1045,16 @@ DROP FUNCTION metabib.browse_table_bounds(TEXT, TEXT, INTEGER); ALTER TABLE metabib.browse_entry DROP COLUMN truncated_sort_value; +SELECT CLOCK_TIMESTAMP(), 'Create combo_sort_value index'; + CREATE INDEX browse_entry_combo_sort_value_idx ON metabib.browse_entry USING btree(combo_sort_value); DROP FUNCTION metabib.migrate_legacy_browse_entry( metabib.browse_entry, TEXT, BOOLEAN); +SELECT CLOCK_TIMESTAMP(), 'Authority refactor complete'; + COMMIT; --ROLLBACK; -- 2.11.0