MODSOURCE-666 - Getting issue details... STATUS
if 999 ff s UUID is different from matched_id and status ACTUAL - get highest generation for matched_id and overwrite matched_id to be the same as 999 ff s with incremented generation and also If more than 1 ACTUAL record with the same matched_id - then to those records status will be set as 'OLD'.
1. Update records containing inconsistent matched_id with generation and state
Update unmatched matched_id and generation and update status
DO
$$
DECLARE
rec record;
updated_generation numeric := 0;
BEGIN
-- temporary table is designated to hold records when matched_id and 999 ff $s values are mismatched
CREATE TEMP TABLE value_matched_id_inconsistency ON COMMIT DROP
AS
SELECT rl.id, rl.matched_id, rl.generation, mi.value, mi.version
FROM {tenantId}_mod_source_record_storage.records_lb rl,
{tenantId}_mod_source_record_storage.marc_indexers mi,
(
SELECT mi.value, max(mi.version) AS max_version
FROM {tenantId}_mod_source_record_storage.records_lb rl,
{tenantId}_mod_source_record_storage.marc_indexers mi
WHERE rl.id = mi.marc_id AND rl.state='ACTUAL'
AND mi.field_no='999' AND mi.ind1='f' AND mi.ind2='f' AND mi.subfield_no='s' AND rl.matched_id::text != mi.value
GROUP BY mi.value
) AS nested_query
WHERE rl.id = mi.marc_id AND rl.state='ACTUAL' AND mi.value = nested_query.value AND mi.field_no='999' AND mi.ind1='f' AND mi.ind2='f' AND mi.subfield_no='s' AND rl.matched_id::text != mi.value AND mi.version = nested_query.max_version;
-- temporary table (matched_id_generation_table) is for holding pair matched_id, generation
CREATE TEMP TABLE matched_id_generation_table ON COMMIT DROP AS
SELECT rl.matched_id, MAX(rl.generation)+1 AS max_gen
FROM {tenantId}_mod_source_record_storage.records_lb rl,
value_matched_id_inconsistency val_match
WHERE rl.matched_id::text = val_match.value
GROUP by rl.matched_id;
-- iteration through records with mismatched matched_id and value
FOR rec IN SELECT * FROM value_matched_id_inconsistency
LOOP
updated_generation := (SELECT max_gen
FROM matched_id_generation_table matched_gen
WHERE matched_gen.matched_id = rec.value::uuid);
if updated_generation is null then
updated_generation := 0;
end if;
UPDATE {tenantId}_mod_source_record_storage.records_lb rl
SET
generation = updated_generation,
matched_id = rec.value::uuid,
state = 'OLD'
WHERE rl.id = rec.id;
UPDATE matched_id_generation_table matched_gen
SET max_gen = updated_generation + 1
WHERE matched_gen.matched_id = rec.value::uuid;
END LOOP;
-- update records with greatest generation to 'ACTUAL' state
UPDATE {tenantId}_mod_source_record_storage.records_lb rl
SET state='ACTUAL'
FROM matched_id_generation_table
WHERE rl.matched_id = matched_id_generation_table.matched_id AND rl.generation = matched_id_generation_table.max_gen - 1;
END;
$$
LANGUAGE plpgsql;
2. Update multiple ACTUAL records containing the same generation
DO
$$
BEGIN
CREATE TEMP TABLE external_id_max_created_date_table ON COMMIT DROP AS
SELECT rl.external_id, MAX(rl.created_date) AS last_creation_date
FROM {tenantId}_mod_source_record_storage.records_lb rl,
(
SELECT external_id
FROM {tenantId}_mod_source_record_storage.records_lb
WHERE state = 'ACTUAL'
GROUP BY external_id, generation
HAVING COUNT(external_id) > 1
) AS external_id_of_duplicates_with_same_generation
WHERE rl.external_id = external_id_of_duplicates_with_same_generation.external_id
GROUP by rl.external_id;
UPDATE {tenantId}_mod_source_record_storage.records_lb r
SET state = 'OLD'
FROM external_id_max_created_date_table
WHERE r.external_id = external_id_max_created_date_table.external_id
AND r.created_date < external_id_max_created_date_table.last_creation_date;
END;
$$;
3. Update the remaining multiple ACTUAL records
DO
$$
BEGIN
CREATE TEMP TABLE external_id_max_generation_table ON COMMIT DROP AS
SELECT r.external_id, MAX(r.generation) AS max_generation
FROM {tenantId}_mod_source_record_storage.records_lb r,
(
SELECT external_id
FROM {tenantId}_mod_source_record_storage.records_lb
WHERE state = 'ACTUAL'
GROUP BY external_id
HAVING COUNT(external_id) > 1
) AS external_id_of_duplicates
WHERE r.external_id = external_id_of_duplicates.external_id
GROUP BY r.external_id;
UPDATE {tenantId}_mod_source_record_storage.records_lb r
SET state = 'OLD'
FROM external_id_max_generation_table
WHERE r.external_id = external_id_max_generation_table.external_id
AND r.generation < external_id_max_generation_table.max_generation;
END;
$$;