Handling missing MARC Authority shadow copies.
Issue: https://folio-org.atlassian.net/browse/MODELINKS-374
Notice {member_tenant_id}, {central_tenant_id} placeholders in insert queries.
This query shows the count of missing authority shadow copies in the member tenant. If the result is “0”, it means there are no missing shadow copies for this member tenant, and there is no need to run the following two scripts.
SELECT COUNT(cent.*)
FROM {central_tenant_id}_mod_entities_links.authority cent
LEFT JOIN {member_tenant_id}_mod_entities_links.authority mem ON mem.id = cent.id
WHERE mem.id IS NULL;Created a script to insert missing authority shadow copies into the member tenant. It needs to be run for each member tenant where the shadow copies are missing.
DO
$$
DECLARE
arr UUID[] DEFAULT ARRAY[
'10000000-0000-0000-0000-000000000000',
'20000000-0000-0000-0000-000000000000',
'30000000-0000-0000-0000-000000000000',
'40000000-0000-0000-0000-000000000000',
'50000000-0000-0000-0000-000000000000',
'60000000-0000-0000-0000-000000000000',
'70000000-0000-0000-0000-000000000000',
'80000000-0000-0000-0000-000000000000',
'90000000-0000-0000-0000-000000000000',
'a0000000-0000-0000-0000-000000000000',
'b0000000-0000-0000-0000-000000000000',
'c0000000-0000-0000-0000-000000000000',
'd0000000-0000-0000-0000-000000000000',
'e0000000-0000-0000-0000-000000000000',
'f0000000-0000-0000-0000-000000000000',
'ffffffff-ffff-ffff-ffff-ffffffffffff'
];
lower UUID;
cur UUID;
rowcount BIGINT;
BEGIN
lower := '00000000-0000-0000-0000-000000000000';
FOREACH cur IN ARRAY arr LOOP
RAISE INFO 'range: % - %', lower, cur;
EXECUTE format($q$
INSERT INTO {member_tenant_id}_mod_entities_links.authority(
SELECT id, natural_id, source_file_id, 'CONSORTIUM-' || source, heading, heading_type, _version, subject_heading_code, sft_headings, saft_headings, identifiers, notes, deleted, created_date, updated_date, created_by_user_id, updated_by_user_id
FROM {central_tenant_id}_mod_entities_links.authority
WHERE (id > %L AND id <= %L))
ON CONFLICT (id) DO NOTHING;
$q$, lower, cur);
GET DIAGNOSTICS rowcount = ROW_COUNT;
RAISE INFO 'updated % authorities', rowcount;
lower := cur;
END LOOP;
END;
$$ LANGUAGE 'plpgsql';Created a script to insert authority statistics for member tenants. This data is used for the Authority report, which displays information about shadow authorities that were previously missing and have now been added by the first script. It needs to be run for each member tenant where the shadow copies were added by the previous script
DO
$$
DECLARE
arr UUID[] DEFAULT ARRAY[
'10000000-0000-0000-0000-000000000000',
'20000000-0000-0000-0000-000000000000',
'30000000-0000-0000-0000-000000000000',
'40000000-0000-0000-0000-000000000000',
'50000000-0000-0000-0000-000000000000',
'60000000-0000-0000-0000-000000000000',
'70000000-0000-0000-0000-000000000000',
'80000000-0000-0000-0000-000000000000',
'90000000-0000-0000-0000-000000000000',
'a0000000-0000-0000-0000-000000000000',
'b0000000-0000-0000-0000-000000000000',
'c0000000-0000-0000-0000-000000000000',
'd0000000-0000-0000-0000-000000000000',
'e0000000-0000-0000-0000-000000000000',
'f0000000-0000-0000-0000-000000000000',
'ffffffff-ffff-ffff-ffff-ffffffffffff'
];
lower UUID;
cur UUID;
rowcount BIGINT;
BEGIN
lower := '00000000-0000-0000-0000-000000000000';
FOREACH cur IN ARRAY arr LOOP
RAISE INFO 'range: % - %', lower, cur;
EXECUTE format($q$
INSERT INTO {member_tenant_id}_mod_entities_links.authority_data_stat(
SELECT id, authority_id, action::text::{member_tenant_id}_mod_entities_links.authoritydatastataction, authority_natural_id_old, authority_natural_id_new, heading_old, heading_new, heading_type_old, heading_type_new, authority_source_file_old, authority_source_file_new, lb_total, lb_updated, lb_failed,
status::text::{member_tenant_id}_mod_entities_links.authoritydatastatstatus, fail_cause, started_by_user_id, started_at, completed_at, updated_at
FROM {central_tenant_id}_mod_entities_links.authority_data_stat cs
WHERE NOT EXISTS (
SELECT 1 FROM {member_tenant_id}_mod_entities_links.authority_data_stat ms
WHERE
ms.authority_id = cs.authority_id AND
ms.action = cs.action::text::{member_tenant_id}_mod_entities_links.authoritydatastataction AND
ms.authority_natural_id_old IS NOT DISTINCT FROM cs.authority_natural_id_old AND
ms.authority_natural_id_new IS NOT DISTINCT FROM cs.authority_natural_id_new AND
ms.heading_old IS NOT DISTINCT FROM cs.heading_old AND
ms.heading_new IS NOT DISTINCT FROM cs.heading_new AND
ms.heading_type_old IS NOT DISTINCT FROM cs.heading_type_old AND
ms.heading_type_new IS NOT DISTINCT FROM cs.heading_type_new AND
ms.authority_source_file_old IS NOT DISTINCT FROM cs.authority_source_file_old AND
ms.authority_source_file_new IS NOT DISTINCT FROM cs.authority_source_file_new AND
ms.lb_total IS NOT DISTINCT FROM cs.lb_total AND
ms.lb_updated IS NOT DISTINCT FROM cs.lb_updated AND
ms.lb_failed IS NOT DISTINCT FROM cs.lb_failed AND
ms.status = cs.status::text::{member_tenant_id}_mod_entities_links.authoritydatastatstatus AND
ms.fail_cause IS NOT DISTINCT FROM cs.fail_cause
) AND (cs.id > %L AND cs.id <= %L));
$q$, lower, cur);
GET DIAGNOSTICS rowcount = ROW_COUNT;
RAISE INFO 'updated % authority stats', rowcount;
lower := cur;
END LOOP;
END;
$$ LANGUAGE 'plpgsql';