Schema Changes: 2.12.2

-- Evergreen DB patch XXXX.data.fix_long_overdue_perm.sql
--
-- Update permission 549 to have a "code" value that matches what
-- the Perl code references
--


-- check whether patch can be applied

-- For some time now, the database seed data / upgrade scripts have created
-- a permission with id 549 and code COPY_STATUS_LONGOVERDUE.override, while
-- the Perl code references a permission with code
-- COPY_STATUS_LONG_OVERDUE.override
--
-- Below, we attempt to handle at least three possible database states:
--
-- 1) no corrective action has been taken, permission exists with id 549 and
--    code COPY_STATUS_LONGOVERDUE.override
--
-- 2) permission with id 549 has already been updated to have code
--    COPY_STATUS_LONG_OVERDUE.override
--
-- 3) new permission with unknown id and code COPY_STATUS_LONG_OVERDUE.override
--    has been added, and potentially assigned to users/groups
--
-- In the case of 3, users and groups may have been assigned both perm id 549
-- and the local permission of unknown id.
--
-- The desired end result is that we should have a permission.perm_list
-- entry with id 549 and code COPY_STATUS_LONG_OVERDUE.override,
-- any locally-created permission with that same code but a different id
-- is deleted, and any users or groups that had been granted that locally-created
-- permission (by id) have been granted permission id 549 if not already granted.
--
-- If for some reason the permission at id 549 has an unexpected value for "code",
-- the end result of this upgrade script should be a no-op.

-- grant permission 549 to any group that
-- has a potentially locally-added perm
-- with code COPY_STATUS_LONG_OVERDUE.override
WITH new_grp_perms AS (
SELECT grp, 549 AS perm, depth, grantable
FROM permission.grp_perm_map pgpm
JOIN permission.perm_list ppl ON ppl.id = pgpm.perm
WHERE ppl.code = 'COPY_STATUS_LONG_OVERDUE.override'
-- short circuit if perm id 549 exists and doesn't have the expected code
AND EXISTS (SELECT 1 FROM permission.perm_list ppl WHERE ppl.id = 549 and ppl.code = 'COPY_STATUS_LONGOVERDUE.override')
-- don't try to assign perm 549 if already assigned
AND NOT EXISTS (SELECT 1 FROM permission.grp_perm_map pgpm2 WHERE pgpm2.grp = pgpm.grp AND pgpm2.perm = 549)
)
INSERT INTO permission.grp_perm_map
(grp, perm, depth, grantable)
SELECT grp, perm, depth, grantable
FROM new_grp_perms;

-- grant permission 549 to any user that
-- has a potentially locally-added perm
-- with code COPY_STATUS_LONG_OVERDUE.override
WITH new_usr_perms AS (
SELECT usr, 549 AS perm, depth, grantable
FROM permission.usr_perm_map pupm
JOIN permission.perm_list ppl ON ppl.id = pupm.perm
WHERE ppl.code = 'COPY_STATUS_LONG_OVERDUE.override'
-- short circuit if perm id 549 exists and doesn't have the expected code
AND EXISTS (SELECT 1 FROM permission.perm_list ppl WHERE ppl.id = 549 and ppl.code = 'COPY_STATUS_LONGOVERDUE.override')
-- don't try to assign perm 549 if already assigned
AND NOT EXISTS (SELECT 1 FROM permission.usr_perm_map pupm2 WHERE pupm2.usr = pupm.usr AND pupm2.perm = 549)
)
INSERT INTO permission.usr_perm_map
(usr, perm, depth, grantable)
SELECT usr, perm, depth, grantable
FROM new_usr_perms;

-- delete any group assignments of the locally-added perm
DELETE FROM permission.grp_perm_map
WHERE perm = (SELECT id FROM permission.perm_list WHERE code = 'COPY_STATUS_LONG_OVERDUE.override' AND id <> 549)
-- short circuit if perm id 549 exists and doesn't have the expected code
AND EXISTS (SELECT 1 FROM permission.perm_list ppl WHERE ppl.id = 549 and ppl.code = 'COPY_STATUS_LONGOVERDUE.override');

-- delete any user assignments of the locally-added perm
DELETE FROM permission.usr_perm_map
WHERE perm = (SELECT id FROM permission.perm_list WHERE code = 'COPY_STATUS_LONG_OVERDUE.override' AND id <> 549)
-- short circuit if perm id 549 exists and doesn't have the expected code
AND EXISTS (SELECT 1 FROM permission.perm_list ppl WHERE ppl.id = 549 and ppl.code = 'COPY_STATUS_LONGOVERDUE.override');

-- delete the locally-added perm, if any
DELETE FROM permission.perm_list
WHERE code = 'COPY_STATUS_LONG_OVERDUE.override'
AND id <> 549
-- short circuit if perm id 549 exists and doesn't have the expected code
AND EXISTS (SELECT 1 FROM permission.perm_list ppl WHERE ppl.id = 549 and ppl.code = 'COPY_STATUS_LONGOVERDUE.override');

-- update perm id 549 to the correct code, if not already
UPDATE permission.perm_list
SET code = 'COPY_STATUS_LONG_OVERDUE.override'
WHERE id = 549
AND code = 'COPY_STATUS_LONGOVERDUE.override';

-- This function was replaced back in 2011, but never made it
-- into an upgrade script.  Here it is, nearly 6 years later.

CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
DECLARE
    eg_id           BIGINT;
    match_count     INT;

    PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;

    IF FOUND THEN
        -- RAISE NOTICE 'already imported, cannot auto-overlay'
        RETURN FALSE;
    END IF;

    SELECT COUNT(*) INTO match_count FROM vandelay.bib_match WHERE queued_record = import_id;

    IF match_count <> 1 THEN
        -- RAISE NOTICE 'not an exact match';
        RETURN FALSE;
    END IF;

    -- Check that the one match is on the first 901c
    SELECT  m.eg_record INTO eg_id
      FROM  vandelay.queued_bib_record q
            JOIN vandelay.bib_match m ON (m.queued_record = q.id)
      WHERE q.id = import_id
            AND m.eg_record = oils_xpath_string('//*[@tag="901"]/*[@code="c"][1]',marc)::BIGINT;

    IF NOT FOUND THEN
        -- RAISE NOTICE 'not a 901c match';
        RETURN FALSE;
    END IF;

    RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
END;
$$ LANGUAGE PLPGSQL;

UPDATE config.org_unit_setting_type
SET datatype = 'link', fm_class = 'vms'
WHERE name = 'vandelay.default_match_set'
AND   datatype = 'string'
AND   fm_class IS NULL;

\echo Existing vandelay.default_match_set that do not
\echo correspond to match sets
SELECT aou.shortname, aous.value
FROM   actor.org_unit_setting aous
JOIN   actor.org_unit aou ON (aou.id = aous.org_unit)
WHERE  aous.name = 'vandelay.default_match_set'
AND    (
  value !~ '^"[0-9]+"$'
  OR
    oils_json_to_text(aous.value)::INT NOT IN (
      SELECT id FROM vandelay.match_set
    )
);

\echo And now deleting the bad values, as otherwise they
\echo will break the Library Settings Editor.
DELETE
FROM actor.org_unit_setting aous
WHERE  aous.name = 'vandelay.default_match_set'
AND    (
  value !~ '^"[0-9]+"$'
  OR
    oils_json_to_text(aous.value)::INT NOT IN (
      SELECT id FROM vandelay.match_set
    )
);

CREATE INDEX edi_message_remote_file_idx ON acq.edi_message (evergreen.lowercase(remote_file));