MODSOURMAN-627 - Script for retrieving holding by specific conditions.

Regarding comments from this ticket [MODSOURMAN-627] Kiwi bugfest - Data integrity issues with instances/holdings records - FOLIO Issue Tracker:

If the holdings source is anything other than FOLIO or MARC (e.g. -), then change to FOLIO.
If the holdings source = MARC, check to see if there is a linked SRS MARC Holdings record.
1. If yes, then keep the Holdings source as MARC, and ensure that View source and Edit via QM in the Actions menu work for the Inventory Holdings record.
2. If no, then change the Holdings source to FOLIO, and ensure that View source/Edit in QM do not display in the Inventory Holdings Action menu.

These scripts retrieve holdings that should be reviewed. Only scripts 1b and 3b update those holdings. These scripts show valid/invalid Holdings from DB by conditions from comment.

NOTE: Update scripts 1b and 3b should be executed during non-working hours with no imports running in parallel.

Script 1a: Retrieve holdings where the source is anything other than FOLIO or MARC (e.g. -):

Select Holdings where sourceName are not FOLIO and MARC

SELECT 
*
FROM ${tenant}_mod_inventory_storage.holdings_record
WHERE ${tenant}_mod_inventory_storage.holdings_record.jsonb ->> 'sourceId' NOT IN (
SELECT 
id::text
FROM ${tenant}_mod_inventory_storage.holdings_records_source) OR 
${tenant}_mod_inventory_storage.holdings_record.jsonb ->> 'sourceId' IS NULL;

Script 1b: Update holdings where the source is anything other than FOLIO or MARC (e.g. -):

Update Holdings where sourceId are not FOLIO and MARC or Null

BEGIN;
WITH source_id AS (
 SELECT id::text FROM ${tenant}_mod_inventory_storage.holdings_records_source 
 WHERE ${tenant}_mod_inventory_storage.holdings_records_source.jsonb ->> 'name' = 'FOLIO'
)
UPDATE ${tenant}_mod_inventory_storage.holdings_record 
 SET sourceid = (SELECT * FROM source_id)::uuid, jsonb = jsonb_set(jsonb, '{sourceId}', to_jsonb((SELECT * FROM source_id)), true)
WHERE ${tenant}_mod_inventory_storage.holdings_record.jsonb ->> 'sourceId' 
 NOT IN (SELECT id::text FROM ${tenant}_mod_inventory_storage.holdings_records_source)
OR ${tenant}_mod_inventory_storage.holdings_record.jsonb ->> 'sourceId' IS NULL;
COMMIT;

Script 2: Retrieve holdings where source = MARC and there is existing linked SRS MARC HOLDINGS record (by external_hrid) from records_lb from source-record-storage schema.

Select Holdings where sourceName=MARC and external_hrid exists in SRS

SELECT * 
FROM ${tenant}_mod_inventory_storage.holdings_record RIGHT JOIN ${tenant}_mod_source_record_storage.records_lb
ON ${tenant}_mod_inventory_storage.holdings_record.jsonb->>'hrid' = ${tenant}_mod_source_record_storage.records_lb.external_hrid
WHERE ${tenant}_mod_inventory_storage.holdings_record.jsonb ->> 'sourceId' = (
SELECT 
id::text
FROM ${tenant}_mod_inventory_storage.holdings_records_source
WHERE ${tenant}_mod_inventory_storage.holdings_records_source.jsonb ->> 'name' = 'MARC');

Script 3a: Retrieve holdings where source = MARC and there is NO existing SRS MARC HOLDINGS record (by external_hrid) from records_lb from source-record-storage schema.

Select Holdings where sourceName=MARC and external_hrid NOT exists in SRS

SELECT  *
FROM    ${tenant}_mod_inventory_storage.holdings_record
WHERE   ${tenant}_mod_inventory_storage.holdings_record.jsonb ->> 'sourceId' = (
SELECT 
id::text
FROM ${tenant}_mod_inventory_storage.holdings_records_source
WHERE ${tenant}_mod_inventory_storage.holdings_records_source.jsonb ->> 'name' = 'MARC')
AND NOT EXISTS
(SELECT  *
 FROM    ${tenant}_mod_source_record_storage.records_lb
 WHERE   ${tenant}_mod_source_record_storage.records_lb.external_hrid = ${tenant}_mod_inventory_storage.holdings_record.jsonb->>'hrid');

Script 3b: Update holdings where source = MARC and there is NO existing SRS MARC HOLDINGS record (by external_hrid) from records_lb from source-record-storage schema.

Update Holdings where sourceName=MARC and external_hrid NOT exists in SRS

BEGIN;
WITH source_id AS (
 SELECT id::text FROM ${tenant}_mod_inventory_storage.holdings_records_source 
 WHERE ${tenant}_mod_inventory_storage.holdings_records_source.jsonb ->> 'name' = 'FOLIO'
)
UPDATE ${tenant}_mod_inventory_storage.holdings_record 
 SET sourceid = (SELECT * FROM source_id)::uuid, jsonb = jsonb_set(jsonb, '{sourceId}', to_jsonb((SELECT * FROM source_id)), true)
WHERE ${tenant}_mod_inventory_storage.holdings_record.jsonb ->> 'sourceId' = (
 SELECT id::text FROM ${tenant}_mod_inventory_storage.holdings_records_source 
 WHERE ${tenant}_mod_inventory_storage.holdings_records_source.jsonb ->> 'name' = 'MARC')
AND NOT EXISTS (SELECT * FROM ${tenant}_mod_source_record_storage.records_lb 
 WHERE ${tenant}_mod_source_record_storage.records_lb.external_hrid = ${tenant}_mod_inventory_storage.holdings_record.jsonb->>'hrid');
COMMIT;