LP#1772028 Add some FK violation functions just in case they are missing

[Evergreen.git] / Open-ILS / src / sql / Pg / version-upgrade / 2.7.2-2.7.3-upgrade-db.sql

--Upgrade Script for 2.7.2 to 2.7.3
\set eg_version '''2.7.3'''
BEGIN;
INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.7.3', :eg_version);

SELECT evergreen.upgrade_deps_block_check('0901', :eg_version);

CREATE OR REPLACE FUNCTION actor.usr_purge_data(
        src_usr  IN INTEGER,
        specified_dest_usr IN INTEGER
) RETURNS VOID AS $$
DECLARE
        suffix TEXT;
        renamable_row RECORD;
        dest_usr INTEGER;
BEGIN

        IF specified_dest_usr IS NULL THEN
                dest_usr := 1; -- Admin user on stock installs
        ELSE
                dest_usr := specified_dest_usr;
        END IF;

        -- acq.*
        UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
        UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
        UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
        UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
        UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
        UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
        DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;

        -- Update with a rename to avoid collisions
        FOR renamable_row in
                SELECT id, name
                FROM   acq.picklist
                WHERE  owner = src_usr
        LOOP
                suffix := ' (' || src_usr || ')';
                LOOP
                        BEGIN
                                UPDATE  acq.picklist
                                SET     owner = dest_usr, name = name || suffix
                                WHERE   id = renamable_row.id;
                        EXCEPTION WHEN unique_violation THEN
                                suffix := suffix || ' ';
                                CONTINUE;
                        END;
                        EXIT;
                END LOOP;
        END LOOP;

        UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
        UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
        UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
        UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
        UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
        UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
        UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
        UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;

        -- action.*
        DELETE FROM action.circulation WHERE usr = src_usr;
        UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
        UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
        UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
        UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
        UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
        DELETE FROM action.hold_request WHERE usr = src_usr;
        UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
        UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
        DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
        UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
        DELETE FROM action.survey_response WHERE usr = src_usr;
        UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;

        -- actor.*
        DELETE FROM actor.card WHERE usr = src_usr;
        DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;

        -- The following update is intended to avoid transient violations of a foreign
        -- key constraint, whereby actor.usr_address references itself.  It may not be
        -- necessary, but it does no harm.
        UPDATE actor.usr_address SET replaces = NULL
                WHERE usr = src_usr AND replaces IS NOT NULL;
        DELETE FROM actor.usr_address WHERE usr = src_usr;
        DELETE FROM actor.usr_note WHERE usr = src_usr;
        UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr;
        DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
        UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
        DELETE FROM actor.usr_setting WHERE usr = src_usr;
        DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
        UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;

        -- asset.*
        UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
        UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
        UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
        UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
        UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
        UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;

        -- auditor.*
        DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
        DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
        UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
        UPDATE auditor.asset_call_number_history SET editor  = dest_usr WHERE editor  = src_usr;
        UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
        UPDATE auditor.asset_copy_history SET editor  = dest_usr WHERE editor  = src_usr;
        UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
        UPDATE auditor.biblio_record_entry_history SET editor  = dest_usr WHERE editor  = src_usr;

        -- biblio.*
        UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
        UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
        UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
        UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;

        -- container.*
        -- Update buckets with a rename to avoid collisions
        FOR renamable_row in
                SELECT id, name
                FROM   container.biblio_record_entry_bucket
                WHERE  owner = src_usr
        LOOP
                suffix := ' (' || src_usr || ')';
                LOOP
                        BEGIN
                                UPDATE  container.biblio_record_entry_bucket
                                SET     owner = dest_usr, name = name || suffix
                                WHERE   id = renamable_row.id;
                        EXCEPTION WHEN unique_violation THEN
                                suffix := suffix || ' ';
                                CONTINUE;
                        END;
                        EXIT;
                END LOOP;
        END LOOP;

        FOR renamable_row in
                SELECT id, name
                FROM   container.call_number_bucket
                WHERE  owner = src_usr
        LOOP
                suffix := ' (' || src_usr || ')';
                LOOP
                        BEGIN
                                UPDATE  container.call_number_bucket
                                SET     owner = dest_usr, name = name || suffix
                                WHERE   id = renamable_row.id;
                        EXCEPTION WHEN unique_violation THEN
                                suffix := suffix || ' ';
                                CONTINUE;
                        END;
                        EXIT;
                END LOOP;
        END LOOP;

        FOR renamable_row in
                SELECT id, name
                FROM   container.copy_bucket
                WHERE  owner = src_usr
        LOOP
                suffix := ' (' || src_usr || ')';
                LOOP
                        BEGIN
                                UPDATE  container.copy_bucket
                                SET     owner = dest_usr, name = name || suffix
                                WHERE   id = renamable_row.id;
                        EXCEPTION WHEN unique_violation THEN
                                suffix := suffix || ' ';
                                CONTINUE;
                        END;
                        EXIT;
                END LOOP;
        END LOOP;

        FOR renamable_row in
                SELECT id, name
                FROM   container.user_bucket
                WHERE  owner = src_usr
        LOOP
                suffix := ' (' || src_usr || ')';
                LOOP
                        BEGIN
                                UPDATE  container.user_bucket
                                SET     owner = dest_usr, name = name || suffix
                                WHERE   id = renamable_row.id;
                        EXCEPTION WHEN unique_violation THEN
                                suffix := suffix || ' ';
                                CONTINUE;
                        END;
                        EXIT;
                END LOOP;
        END LOOP;

        DELETE FROM container.user_bucket_item WHERE target_user = src_usr;

        -- money.*
        DELETE FROM money.billable_xact WHERE usr = src_usr;
        DELETE FROM money.collections_tracker WHERE usr = src_usr;
        UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;

        -- permission.*
        DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
        DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
        DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
        DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;

        -- reporter.*
        -- Update with a rename to avoid collisions
        BEGIN
                FOR renamable_row in
                        SELECT id, name
                        FROM   reporter.output_folder
                        WHERE  owner = src_usr
                LOOP
                        suffix := ' (' || src_usr || ')';
                        LOOP
                                BEGIN
                                        UPDATE  reporter.output_folder
                                        SET     owner = dest_usr, name = name || suffix
                                        WHERE   id = renamable_row.id;
                                EXCEPTION WHEN unique_violation THEN
                                        suffix := suffix || ' ';
                                        CONTINUE;
                                END;
                                EXIT;
                        END LOOP;
                END LOOP;
        EXCEPTION WHEN undefined_table THEN
                -- do nothing
        END;

        BEGIN
                UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
        EXCEPTION WHEN undefined_table THEN
                -- do nothing
        END;

        -- Update with a rename to avoid collisions
        BEGIN
                FOR renamable_row in
                        SELECT id, name
                        FROM   reporter.report_folder
                        WHERE  owner = src_usr
                LOOP
                        suffix := ' (' || src_usr || ')';
                        LOOP
                                BEGIN
                                        UPDATE  reporter.report_folder
                                        SET     owner = dest_usr, name = name || suffix
                                        WHERE   id = renamable_row.id;
                                EXCEPTION WHEN unique_violation THEN
                                        suffix := suffix || ' ';
                                        CONTINUE;
                                END;
                                EXIT;
                        END LOOP;
                END LOOP;
        EXCEPTION WHEN undefined_table THEN
                -- do nothing
        END;

        BEGIN
                UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
        EXCEPTION WHEN undefined_table THEN
                -- do nothing
        END;

        BEGIN
                UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
        EXCEPTION WHEN undefined_table THEN
                -- do nothing
        END;

        -- Update with a rename to avoid collisions
        BEGIN
                FOR renamable_row in
                        SELECT id, name
                        FROM   reporter.template_folder
                        WHERE  owner = src_usr
                LOOP
                        suffix := ' (' || src_usr || ')';
                        LOOP
                                BEGIN
                                        UPDATE  reporter.template_folder
                                        SET     owner = dest_usr, name = name || suffix
                                        WHERE   id = renamable_row.id;
                                EXCEPTION WHEN unique_violation THEN
                                        suffix := suffix || ' ';
                                        CONTINUE;
                                END;
                                EXIT;
                        END LOOP;
                END LOOP;
        EXCEPTION WHEN undefined_table THEN
        -- do nothing
        END;

        -- vandelay.*
        -- Update with a rename to avoid collisions
        FOR renamable_row in
                SELECT id, name
                FROM   vandelay.queue
                WHERE  owner = src_usr
        LOOP
                suffix := ' (' || src_usr || ')';
                LOOP
                        BEGIN
                                UPDATE  vandelay.queue
                                SET     owner = dest_usr, name = name || suffix
                                WHERE   id = renamable_row.id;
                        EXCEPTION WHEN unique_violation THEN
                                suffix := suffix || ' ';
                                CONTINUE;
                        END;
                        EXIT;
                END LOOP;
        END LOOP;

    -- NULL-ify addresses last so other cleanup (e.g. circ anonymization)
    -- can access the information before deletion.
        UPDATE actor.usr SET
                active = FALSE,
                card = NULL,
                mailing_address = NULL,
                billing_address = NULL
        WHERE id = src_usr;

END;
$$ LANGUAGE plpgsql;


-- Evergreen DB patch 0904.schema.allow_spaces_as_ff_attr_values.sql
--
-- LP#1414112 - don't over-normalize record attribute values to
--              exclude all values that contain only blanks
--

-- check whether patch can be applied
SELECT evergreen.upgrade_deps_block_check('0904', :eg_version);

-- The code for "uncoded" audience is a space, but upgraded DBs may have a blank.
UPDATE  config.coded_value_map
  SET   code = ' '
  WHERE ctype = 'audience'
        AND code = ''
        AND (
          SELECT  COUNT(*)
            FROM  config.coded_value_map
            WHERE ctype = 'audience'
            AND code = ' ') = 0;

WITH ccvm AS (
    SELECT  id
      FROM  config.coded_value_map
      WHERE ctype = 'audience'
            AND code = ' '
) UPDATE  metabib.record_attr_vector_list
    SET   vlist = vlist + intset(ccvm.id)
    FROM ccvm
    WHERE source IN (
            SELECT  record
              FROM  metabib.real_full_rec
              WHERE tag = '008'
                    AND substring(value,23,1) = ' '
          );

CREATE OR REPLACE FUNCTION metabib.reingest_record_attributes (rid BIGINT, pattr_list TEXT[] DEFAULT NULL, prmarc TEXT DEFAULT NULL, rdeleted BOOL DEFAULT TRUE) RETURNS VOID AS $func$
DECLARE
    transformed_xml TEXT;
    rmarc           TEXT := prmarc;
    tmp_val         TEXT;
    prev_xfrm       TEXT;
    normalizer      RECORD;
    xfrm            config.xml_transform%ROWTYPE;
    attr_vector     INT[] := '{}'::INT[];
    attr_vector_tmp INT[];
    attr_list       TEXT[] := pattr_list;
    attr_value      TEXT[];
    norm_attr_value TEXT[];
    tmp_xml         TEXT;
    attr_def        config.record_attr_definition%ROWTYPE;
    ccvm_row        config.coded_value_map%ROWTYPE;
BEGIN

    IF attr_list IS NULL OR rdeleted THEN -- need to do the full dance on INSERT or undelete
        SELECT ARRAY_AGG(name) INTO attr_list FROM config.record_attr_definition;
    END IF;

    IF rmarc IS NULL THEN
        SELECT marc INTO rmarc FROM biblio.record_entry WHERE id = rid;
    END IF;

    FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE NOT composite AND name = ANY( attr_list ) ORDER BY format LOOP

        attr_value := '{}'::TEXT[];
        norm_attr_value := '{}'::TEXT[];
        attr_vector_tmp := '{}'::INT[];

        SELECT * INTO ccvm_row FROM config.coded_value_map c WHERE c.ctype = attr_def.name LIMIT 1; 

        -- tag+sf attrs only support SVF
        IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
            SELECT  ARRAY[ARRAY_TO_STRING(ARRAY_AGG(value), COALESCE(attr_def.joiner,' '))] INTO attr_value
              FROM  (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x
              WHERE record = rid
                    AND tag LIKE attr_def.tag
                    AND CASE
                        WHEN attr_def.sf_list IS NOT NULL 
                            THEN POSITION(subfield IN attr_def.sf_list) > 0
                        ELSE TRUE
                    END
              GROUP BY tag
              ORDER BY tag
              LIMIT 1;

        ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
            attr_value := vandelay.marc21_extract_fixed_field_list(rmarc, attr_def.fixed_field);

            IF NOT attr_def.multi THEN
                attr_value := ARRAY[attr_value[1]];
            END IF;

        ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression

            SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
        
            -- See if we can skip the XSLT ... it's expensive
            IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
                -- Can't skip the transform
                IF xfrm.xslt <> '---' THEN
                    transformed_xml := oils_xslt_process(rmarc,xfrm.xslt);
                ELSE
                    transformed_xml := rmarc;
                END IF;
    
                prev_xfrm := xfrm.name;
            END IF;

            IF xfrm.name IS NULL THEN
                -- just grab the marcxml (empty) transform
                SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
                prev_xfrm := xfrm.name;
            END IF;

            FOR tmp_xml IN SELECT oils_xpath(attr_def.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]) LOOP
                tmp_val := oils_xpath_string(
                                '//*',
                                tmp_xml,
                                COALESCE(attr_def.joiner,' '),
                                ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
                            );
                IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
                    attr_value := attr_value || tmp_val;
                    EXIT WHEN NOT attr_def.multi;
                END IF;
            END LOOP;

        ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
            SELECT  ARRAY_AGG(m.value) INTO attr_value
              FROM  vandelay.marc21_physical_characteristics(rmarc) v
                    LEFT JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
              WHERE v.subfield = attr_def.phys_char_sf AND (m.value IS NOT NULL AND BTRIM(m.value) <> '')
                    AND ( ccvm_row.id IS NULL OR ( ccvm_row.id IS NOT NULL AND v.id IS NOT NULL) );

            IF NOT attr_def.multi THEN
                attr_value := ARRAY[attr_value[1]];
            END IF;

        END IF;

                -- apply index normalizers to attr_value
        FOR tmp_val IN SELECT value FROM UNNEST(attr_value) x(value) LOOP
            FOR normalizer IN
                SELECT  n.func AS func,
                        n.param_count AS param_count,
                        m.params AS params
                  FROM  config.index_normalizer n
                        JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
                  WHERE attr = attr_def.name
                  ORDER BY m.pos LOOP
                    EXECUTE 'SELECT ' || normalizer.func || '(' ||
                    COALESCE( quote_literal( tmp_val ), 'NULL' ) ||
                        CASE
                            WHEN normalizer.param_count > 0
                                THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
                                ELSE ''
                            END ||
                    ')' INTO tmp_val;

            END LOOP;
            IF tmp_val IS NOT NULL AND tmp_val <> '' THEN
                -- note that a string that contains only blanks
                -- is a valid value for some attributes
                norm_attr_value := norm_attr_value || tmp_val;
            END IF;
        END LOOP;
        
        IF attr_def.filter THEN
            -- Create unknown uncontrolled values and find the IDs of the values
            IF ccvm_row.id IS NULL THEN
                FOR tmp_val IN SELECT value FROM UNNEST(norm_attr_value) x(value) LOOP
                    IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
                        BEGIN -- use subtransaction to isolate unique constraint violations
                            INSERT INTO metabib.uncontrolled_record_attr_value ( attr, value ) VALUES ( attr_def.name, tmp_val );
                        EXCEPTION WHEN unique_violation THEN END;
                    END IF;
                END LOOP;

                SELECT ARRAY_AGG(id) INTO attr_vector_tmp FROM metabib.uncontrolled_record_attr_value WHERE attr = attr_def.name AND value = ANY( norm_attr_value );
            ELSE
                SELECT ARRAY_AGG(id) INTO attr_vector_tmp FROM config.coded_value_map WHERE ctype = attr_def.name AND code = ANY( norm_attr_value );
            END IF;

            -- Add the new value to the vector
            attr_vector := attr_vector || attr_vector_tmp;
        END IF;

        IF attr_def.sorter AND norm_attr_value[1] IS NOT NULL THEN
            DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
            INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, norm_attr_value[1]);
        END IF;

    END LOOP;

/* We may need to rewrite the vlist to contain
   the intersection of new values for requested
   attrs and old values for ignored attrs. To
   do this, we take the old attr vlist and
   subtract any values that are valid for the
   requested attrs, and then add back the new
   set of attr values. */

    IF ARRAY_LENGTH(pattr_list, 1) > 0 THEN 
        SELECT vlist INTO attr_vector_tmp FROM metabib.record_attr_vector_list WHERE source = rid;
        SELECT attr_vector_tmp - ARRAY_AGG(id::INT) INTO attr_vector_tmp FROM metabib.full_attr_id_map WHERE attr = ANY (pattr_list);
        attr_vector := attr_vector || attr_vector_tmp;
    END IF;

    -- On to composite attributes, now that the record attrs have been pulled.  Processed in name order, so later composite
    -- attributes can depend on earlier ones.
    PERFORM metabib.compile_composite_attr_cache_init();
    FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE composite AND name = ANY( attr_list ) ORDER BY name LOOP

        FOR ccvm_row IN SELECT * FROM config.coded_value_map c WHERE c.ctype = attr_def.name ORDER BY value LOOP

            tmp_val := metabib.compile_composite_attr( ccvm_row.id );
            CONTINUE WHEN tmp_val IS NULL OR tmp_val = ''; -- nothing to do

            IF attr_def.filter THEN
                IF attr_vector @@ tmp_val::query_int THEN
                    attr_vector = attr_vector + intset(ccvm_row.id);
                    EXIT WHEN NOT attr_def.multi;
                END IF;
            END IF;

            IF attr_def.sorter THEN
                IF attr_vector @@ tmp_val THEN
                    DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
                    INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, ccvm_row.code);
                END IF;
            END IF;

        END LOOP;

    END LOOP;

    IF ARRAY_LENGTH(attr_vector, 1) > 0 THEN
        IF rdeleted THEN -- initial insert OR revivication
            DELETE FROM metabib.record_attr_vector_list WHERE source = rid;
            INSERT INTO metabib.record_attr_vector_list (source, vlist) VALUES (rid, attr_vector);
        ELSE
            UPDATE metabib.record_attr_vector_list SET vlist = attr_vector WHERE source = rid;
        END IF;
    END IF;

END;

$func$ LANGUAGE PLPGSQL;

-- Evergreen DB patch 0905.schema.use_current_normalize_heading.sql
--
-- LP#1415572: ensure current version of authority.normalize_heading() is in place
--


-- check whether patch can be applied
SELECT evergreen.upgrade_deps_block_check('0905', :eg_version);

CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
DECLARE
    acsaf           authority.control_set_authority_field%ROWTYPE;
    tag_used        TEXT;
    nfi_used        TEXT;
    sf              TEXT;
    sf_node         TEXT;
    tag_node        TEXT;
    thes_code       TEXT;
    cset            INT;
    heading_text    TEXT;
    tmp_text        TEXT;
    first_sf        BOOL;
    auth_id         INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT; 
BEGIN
    SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;

    IF cset IS NULL THEN
        SELECT  control_set INTO cset
          FROM  authority.control_set_authority_field
          WHERE tag IN ( SELECT  UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
          LIMIT 1;
    END IF;

    thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
    IF thes_code IS NULL THEN
        thes_code := '|';
    ELSIF thes_code = 'z' THEN
        thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), '' );
    END IF;

    heading_text := '';
    FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
        tag_used := acsaf.tag;
        nfi_used := acsaf.nfi;
        first_sf := TRUE;

        FOR tag_node IN SELECT unnest(oils_xpath('//*[@tag="'||tag_used||'"]',marcxml)) LOOP
            FOR sf_node IN SELECT unnest(oils_xpath('./*[contains("'||acsaf.sf_list||'",@code)]',tag_node)) LOOP

                tmp_text := oils_xpath_string('.', sf_node);
                sf := oils_xpath_string('./@code', sf_node);

                IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN

                    tmp_text := SUBSTRING(
                        tmp_text FROM
                        COALESCE(
                            NULLIF(
                                REGEXP_REPLACE(
                                    oils_xpath_string('./@ind'||nfi_used, tag_node),
                                    $$\D+$$,
                                    '',
                                    'g'
                                ),
                                ''
                            )::INT,
                            0
                        ) + 1
                    );

                END IF;

                first_sf := FALSE;

                IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
                    heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
                END IF;
            END LOOP;

            EXIT WHEN heading_text <> '';
        END LOOP;

        EXIT WHEN heading_text <> '';
    END LOOP;

    IF heading_text <> '' THEN
        IF no_thesaurus IS TRUE THEN
            heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
        ELSE
            heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
        END IF;
    ELSE
        heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
    END IF;

    RETURN heading_text;
END;
$func$ LANGUAGE PLPGSQL STABLE STRICT;

-- fix heading and simple_headings columns without
-- causing a full authority reingest
ALTER TABLE authority.record_entry DISABLE TRIGGER a_marcxml_is_well_formed;
ALTER TABLE authority.record_entry DISABLE TRIGGER aaa_auth_ingest_or_delete;
ALTER TABLE authority.record_entry DISABLE TRIGGER b_maintain_901;
ALTER TABLE authority.record_entry DISABLE TRIGGER c_maintain_control_numbers;
ALTER TABLE authority.record_entry DISABLE TRIGGER map_thesaurus_to_control_set;

UPDATE authority.record_entry SET id = id WHERE heading LIKE 'NOHEADING%';


-- check whether patch can be applied
SELECT evergreen.upgrade_deps_block_check('0906', :eg_version);

ALTER FUNCTION evergreen.z3950_attr_name_is_valid (TEXT) STABLE;

COMMIT;

-- These need to happen outside of the transaction to avoid this:
-- ERROR: cannot ALTER TABLE "record_entry" because it has pending trigger
-- events
ALTER TABLE authority.record_entry ENABLE TRIGGER a_marcxml_is_well_formed;
ALTER TABLE authority.record_entry ENABLE TRIGGER aaa_auth_ingest_or_delete;
ALTER TABLE authority.record_entry ENABLE TRIGGER b_maintain_901;
ALTER TABLE authority.record_entry ENABLE TRIGGER c_maintain_control_numbers;
ALTER TABLE authority.record_entry ENABLE TRIGGER map_thesaurus_to_control_set;

\qecho
\qecho
\qecho **** Certain improvements in this upgrade series require a partial reingest of
\qecho **** your bib records.  In order to allow this to continue without locking
\qecho **** your entire bibliographic data set, consider generating an SQL script
\qecho **** with the following queries:
\qecho
\qecho
\qecho '\\t'
\qecho '\\o /tmp/partial_reingest_bib_recs.sql'
\qecho 'SELECT ''select metabib.reingest_record_attributes('' || id || '');'' FROM biblio.record_entry WHERE NOT DELETED AND id > 0;'
\qecho '\\o'
\qecho '\\t'
\qecho
\qecho
\qecho **** then running it via psql:
\qecho
\qecho
\qecho '\\i /tmp/partial_reingest_bib_recs.sql'
\qecho
\qecho
\qecho **** If you require a more responsive catalog/database while reingesting,
\qecho **** consider adding 'pg_sleep()' calls between each reingest select or
\qecho **** update.