Skip to end of banner
Go to start of banner

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

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 5 Current »

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 module 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;
  • No labels