purge_bibs.sql

-- First, we "fix" sources on bib records.
-- OCLC records
UPDATE biblio.record_entry
SET source = 1
WHERE marc ~ '<datafield tag="035" ind1=" " ind2=" "><subfield code="a">\(OCoLC\)'
AND (source = 2 OR source IS NULL)
AND NOT deleted;

-- NULL record source records.
UPDATE biblio.record_entry
SET source = 2
WHERE source IS NULL
AND NOT deleted;

-- Now, we get to the actual purging of bibs.

CREATE TEMP TABLE bib_purge AS
SELECT DISTINCT bre.id, bre.source
FROM biblio.record_entry bre
LEFT JOIN asset.call_number acn
ON acn.record = bre.id AND NOT acn.deleted
WHERE NOT bre.deleted
AND acn IS NULL
AND bre.edit_date < CASE
    WHEN bre.source = 1 THEN
         NOW() - INTERVAL '6 months'
    ELSE
         NOW() - INTERVAL '1 month'
    END;

WITH hold_target AS (
SELECT DISTINCT target AS target
FROM action.hold_request
WHERE hold_type = 'T'
AND cancel_cause IS NULL
AND expire_time > NOW()
AND fulfillment_time IS NULL
UNION DISTINCT
SELECT DISTINCT master_record AS target
FROM metabib.metarecord
JOIN action.hold_request
ON hold_request.target = metarecord.id
AND hold_request.hold_type = 'M'
AND hold_request.cancel_cause IS NULL
AND hold_request.expire_time > NOW()
AND hold_request.fulfillment_time IS NULL
UNION DISTINCT
SELECT DISTINCT source AS target
FROM metabib.metarecord_source_map
JOIN metabib.metarecord
ON metarecord_source_map.metarecord = metarecord.id
JOIN action.hold_request
ON hold_request.target = metarecord.id
AND hold_request.hold_type = 'M'
AND hold_request.cancel_cause IS NULL
AND hold_request.expire_time > NOW()
AND hold_request.fulfillment_time IS NULL)
DELETE FROM bib_purge
USING hold_target
WHERE id = target;

UPDATE biblio.record_entry
SET deleted = TRUE
WHERE id IN (SELECT id FROM bib_purge);