From 79624d89db0e38308b591f3b76808e4f5a7d62d1 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Wed, 2 Mar 2016 16:34:50 -0500 Subject: [PATCH] JBAS-1263 Browse entry cleanup utility SQL SQL to delete un-referenced (dangling) browse entries. Signed-off-by: Bill Erickson --- .../browse_cleanup/delete-stale-browse-entries.sql | 60 ++++++++++++++++++++++ 1 file changed, 60 insertions(+) create mode 100644 KCLS/utility-scripts/browse_cleanup/delete-stale-browse-entries.sql diff --git a/KCLS/utility-scripts/browse_cleanup/delete-stale-browse-entries.sql b/KCLS/utility-scripts/browse_cleanup/delete-stale-browse-entries.sql new file mode 100644 index 0000000000..a46672dda1 --- /dev/null +++ b/KCLS/utility-scripts/browse_cleanup/delete-stale-browse-entries.sql @@ -0,0 +1,60 @@ +/* +Remove all browse entries that are not linked to either a bib record +(via metabib.browse_*_entry_def_map) or an authority record (via +(metabib.browse_*_entry_simple_heading_map). + +Stale browse entries are never removed by the code. They just languish. +Run this script after a browse entry re-ingest, particularly if browse +indexes change, resulting in a lot of new browse entries. +*/ + +DELETE FROM metabib.browse_author_entry WHERE id IN ( + SELECT DISTINCT(entry.id) + FROM metabib.browse_author_entry entry + LEFT JOIN metabib.browse_author_entry_def_map map + ON (map.entry = entry.id) + LEFT JOIN metabib.browse_author_entry_simple_heading_map hmap + ON (hmap.entry = entry.id) + WHERE map.id IS NULL AND hmap.id IS NULL +); + +DELETE FROM metabib.browse_author_entry WHERE id IN ( + SELECT DISTINCT(entry.id) + FROM metabib.browse_author_entry entry + LEFT JOIN metabib.browse_author_entry_def_map map + ON (map.entry = entry.id) + LEFT JOIN metabib.browse_author_entry_simple_heading_map hmap + ON (hmap.entry = entry.id) + WHERE map.id IS NULL AND hmap.id IS NULL +); + +DELETE FROM metabib.browse_series_entry WHERE id IN ( + SELECT DISTINCT(entry.id) + FROM metabib.browse_series_entry entry + LEFT JOIN metabib.browse_series_entry_def_map map + ON (map.entry = entry.id) + LEFT JOIN metabib.browse_series_entry_simple_heading_map hmap + ON (hmap.entry = entry.id) + WHERE map.id IS NULL AND hmap.id IS NULL +); + +DELETE FROM metabib.browse_title_entry WHERE id IN ( + SELECT DISTINCT(entry.id) + FROM metabib.browse_title_entry entry + LEFT JOIN metabib.browse_title_entry_def_map map + ON (map.entry = entry.id) + LEFT JOIN metabib.browse_title_entry_simple_heading_map hmap + ON (hmap.entry = entry.id) + WHERE map.id IS NULL AND hmap.id IS NULL +); + + +DELETE FROM metabib.browse_call_number_entry WHERE id IN ( + SELECT DISTINCT(entry.id) + FROM metabib.browse_call_number_entry entry + LEFT JOIN metabib.browse_call_number_entry_def_map map + ON (map.entry = entry.id) + WHERE map.id IS NULL + -- no simple_heading_map for call numbers +); + -- 2.11.0