Fixer Script

-- Period used to determine how long call numbers and bibs without
-- copies stick around in the nontranscendental sources.
\set period '''30 days'''::INTERVAL

-- CALL NUMBERS SANS COPIES/URIS
WITH empty_acn AS (
SELECT DISTINCT acn.id
FROM asset.call_number acn
LEFT JOIN asset.copy acp ON acp.call_number = acn.id AND NOT acp.deleted
LEFT JOIN asset.uri_call_number_map aucnm ON aucnm.call_number = acn.id
WHERE acn.id > 0 AND acp IS NULL AND aucnm IS NULL AND NOT acn.deleted AND acn.create_date < NOW() - :period
)
DELETE FROM asset.call_number
WHERE id IN
(SELECT id
 FROM empty_acn);

-- BIBS WITH NO CALL NUMBERS
WITH empty_bib AS (
SELECT DISTINCT bre.id
FROM biblio.record_entry bre
LEFT JOIN asset.call_number acn ON acn.record = bre.id AND NOT acn.deleted
WHERE bre.id > 0 AND acn IS NULL AND (bre.source IS NULL OR bre.source IN (1,2)) AND NOT bre.deleted AND bre.create_date < NOW() - :period
)
DELETE FROM biblio.record_entry
WHERE id IN
(SELECT id
 FROM empty_bib);