MODSOURCE-300 - Scripts for identifying and fixing invalid instanceIDs in SRS

These scripts allow for:

1) identifying Source Record Storage records that do not have an instance ID in the external_id column but have it in 999 ff $i subfield.

2) correcting that problem, by retrieving that value from the 999 ff $i value of the SRS MARC Bibliographic record and populate it into the instanceID field.

These scripts are relevant for Lotus version of mod-source-record-storage database schema

Script 1. Retrieve IDs of source records that contain instance ID in 999 ff $i subfield but do not have it in "external_id" column.

SELECT records_lb.id AS record_id, records_lb.external_id, record_instance_ids.instance_id
FROM {tenantId}_mod_source_record_storage.records_lb
JOIN (
    SELECT id, ids_subfields ->> 'i' AS instance_id
    FROM (
        SELECT id, fields
        FROM {tenantId}_mod_source_record_storage.marc_records_lb, jsonb_array_elements(content -> 'fields') as fields
        WHERE fields ? '999'
    ) AS ids_fields, jsonb_array_elements(fields -> '999' -> 'subfields') AS ids_subfields
    WHERE ids_subfields ? 'i'
) AS record_instance_ids ON records_lb.id = record_instance_ids.id
WHERE external_id IS NULL;

Script 2. Populate instance ID from 999 ff $i subfield of parsed content into the "external_id" column.

UPDATE {tenantId}_mod_source_record_storage.records_lb as rec
SET external_id = (record_instance_ids.instance_id)::uuid
FROM (
    SELECT id, ids_subfields ->> 'i' AS instance_id
    FROM (
        SELECT id, fields
        FROM {tenantId}_mod_source_record_storage.marc_records_lb, jsonb_array_elements(content -> 'fields') as fields
        WHERE fields ? '999'
    ) AS ids_fields, jsonb_array_elements(fields -> '999' -> 'subfields') AS ids_subfields
    WHERE ids_subfields ? 'i'
) AS record_instance_ids
WHERE rec.id = record_instance_ids.id AND external_id IS NULL;