daily pastebin goal
5%
SHARE
TWEET

purge_bibs.sql

Dyrcona Oct 11th, 2018 72 Never
Upgrade to PRO!
ENDING IN00days00hours00mins00secs
 
  1. -- First, we "fix" sources on bib records.
  2. -- OCLC records
  3. UPDATE biblio.record_entry
  4. SET source = 1
  5. WHERE marc ~ '<datafield tag="035" ind1=" " ind2=" "><subfield code="a">\(OCoLC\)'
  6. AND (source = 2 OR source IS NULL)
  7. AND NOT deleted;
  8.  
  9. -- NULL record source records.
  10. UPDATE biblio.record_entry
  11. SET source = 2
  12. WHERE source IS NULL
  13. AND NOT deleted;
  14.  
  15. -- Now, we get to the actual purging of bibs.
  16.  
  17. CREATE TEMP TABLE bib_purge AS
  18. SELECT DISTINCT bre.id, bre.source
  19. FROM biblio.record_entry bre
  20. LEFT JOIN asset.call_number acn
  21. ON acn.record = bre.id AND NOT acn.deleted
  22. WHERE NOT bre.deleted
  23. AND acn IS NULL
  24. AND bre.edit_date < CASE
  25.     WHEN bre.source = 1 THEN
  26.          NOW() - INTERVAL '6 months'
  27.     ELSE
  28.          NOW() - INTERVAL '1 month'
  29.     END;
  30.  
  31. WITH hold_target AS (
  32. SELECT DISTINCT target AS target
  33. FROM action.hold_request
  34. WHERE hold_type = 'T'
  35. AND cancel_cause IS NULL
  36. AND expire_time > NOW()
  37. AND fulfillment_time IS NULL
  38. UNION DISTINCT
  39. SELECT DISTINCT master_record AS target
  40. FROM metabib.metarecord
  41. JOIN action.hold_request
  42. ON hold_request.target = metarecord.id
  43. AND hold_request.hold_type = 'M'
  44. AND hold_request.cancel_cause IS NULL
  45. AND hold_request.expire_time > NOW()
  46. AND hold_request.fulfillment_time IS NULL
  47. UNION DISTINCT
  48. SELECT DISTINCT source AS target
  49. FROM metabib.metarecord_source_map
  50. JOIN metabib.metarecord
  51. ON metarecord_source_map.metarecord = metarecord.id
  52. JOIN action.hold_request
  53. ON hold_request.target = metarecord.id
  54. AND hold_request.hold_type = 'M'
  55. AND hold_request.cancel_cause IS NULL
  56. AND hold_request.expire_time > NOW()
  57. AND hold_request.fulfillment_time IS NULL)
  58. DELETE FROM bib_purge
  59. USING hold_target
  60. WHERE id = target;
  61.  
  62. UPDATE biblio.record_entry
  63. SET deleted = TRUE
  64. WHERE id IN (SELECT id FROM bib_purge);
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top