From 00af8a0afc54d38cdca9534a20c904ccdff3d09a Mon Sep 17 00:00:00 2001 From: Dan Wells Date: Mon, 9 Apr 2018 10:50:34 -0400 Subject: [PATCH] Forward-port 3.1.0 upgrade script Signed-off-by: Dan Wells --- .../3.0.6-3.1.0-upgrade-db.sql | 8959 +++++++++++++++++ 1 file changed, 8959 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/version-upgrade/3.0.6-3.1.0-upgrade-db.sql diff --git a/Open-ILS/src/sql/Pg/version-upgrade/3.0.6-3.1.0-upgrade-db.sql b/Open-ILS/src/sql/Pg/version-upgrade/3.0.6-3.1.0-upgrade-db.sql new file mode 100644 index 0000000000..bc5e5c9868 --- /dev/null +++ b/Open-ILS/src/sql/Pg/version-upgrade/3.0.6-3.1.0-upgrade-db.sql @@ -0,0 +1,8959 @@ +--Upgrade Script for 3.0.6 to 3.1.0 +\set eg_version '''3.1.0''' +BEGIN; +INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.1.0', :eg_version); + +SELECT evergreen.upgrade_deps_block_check('1089', :eg_version); + +-- Add the circ.holds.max_duplicate_holds org. unit setting type. +INSERT into config.org_unit_setting_type +( name, grp, label, description, datatype, fm_class ) +VALUES +( 'circ.holds.max_duplicate_holds', 'holds', + oils_i18n_gettext( + 'circ.holds.max_duplicate_holds', + 'Maximum number of duplicate holds allowed.', + 'coust', 'label'), + oils_i18n_gettext( + 'circ.holds.max_duplicate_holds', + 'Maximum number of duplicate title or metarecord holds allowed per patron.', + 'coust', 'description'), + 'integer', null ); + + + +SELECT evergreen.upgrade_deps_block_check('1090', :eg_version); + +ALTER TABLE biblio.record_entry + ADD COLUMN merge_date TIMESTAMP WITH TIME ZONE, + ADD COLUMN merged_to BIGINT REFERENCES biblio.record_entry(id); + +CREATE OR REPLACE FUNCTION asset.merge_record_assets( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$ +DECLARE + moved_objects INT := 0; + source_cn asset.call_number%ROWTYPE; + target_cn asset.call_number%ROWTYPE; + metarec metabib.metarecord%ROWTYPE; + hold action.hold_request%ROWTYPE; + ser_rec serial.record_entry%ROWTYPE; + ser_sub serial.subscription%ROWTYPE; + acq_lineitem acq.lineitem%ROWTYPE; + acq_request acq.user_request%ROWTYPE; + booking booking.resource_type%ROWTYPE; + source_part biblio.monograph_part%ROWTYPE; + target_part biblio.monograph_part%ROWTYPE; + multi_home biblio.peer_bib_copy_map%ROWTYPE; + uri_count INT := 0; + counter INT := 0; + uri_datafield TEXT; + uri_text TEXT := ''; +BEGIN + + -- move any 856 entries on records that have at least one MARC-mapped URI entry + SELECT INTO uri_count COUNT(*) + FROM asset.uri_call_number_map m + JOIN asset.call_number cn ON (m.call_number = cn.id) + WHERE cn.record = source_record; + + IF uri_count > 0 THEN + + -- This returns more nodes than you might expect: + -- 7 instead of 1 for an 856 with $u $y $9 + SELECT COUNT(*) INTO counter + FROM oils_xpath_table( + 'id', + 'marc', + 'biblio.record_entry', + '//*[@tag="856"]', + 'id=' || source_record + ) as t(i int,c text); + + FOR i IN 1 .. counter LOOP + SELECT '' || + STRING_AGG( + '' || + regexp_replace( + regexp_replace( + regexp_replace(data,'&','&','g'), + '>', '>', 'g' + ), + '<', '<', 'g' + ) || '', '' + ) || '' INTO uri_datafield + FROM oils_xpath_table( + 'id', + 'marc', + 'biblio.record_entry', + '//*[@tag="856"][position()=' || i || ']/@ind1|' || + '//*[@tag="856"][position()=' || i || ']/@ind2|' || + '//*[@tag="856"][position()=' || i || ']/*/@code|' || + '//*[@tag="856"][position()=' || i || ']/*[@code]', + 'id=' || source_record + ) as t(id int,ind1 text, ind2 text,subfield text,data text); + + -- As most of the results will be NULL, protect against NULLifying + -- the valid content that we do generate + uri_text := uri_text || COALESCE(uri_datafield, ''); + END LOOP; + + IF uri_text <> '' THEN + UPDATE biblio.record_entry + SET marc = regexp_replace(marc,'(]*record>)', uri_text || E'\\1') + WHERE id = target_record; + END IF; + + END IF; + + -- Find and move metarecords to the target record + SELECT INTO metarec * + FROM metabib.metarecord + WHERE master_record = source_record; + + IF FOUND THEN + UPDATE metabib.metarecord + SET master_record = target_record, + mods = NULL + WHERE id = metarec.id; + + moved_objects := moved_objects + 1; + END IF; + + -- Find call numbers attached to the source ... + FOR source_cn IN SELECT * FROM asset.call_number WHERE record = source_record LOOP + + SELECT INTO target_cn * + FROM asset.call_number + WHERE label = source_cn.label + AND prefix = source_cn.prefix + AND suffix = source_cn.suffix + AND owning_lib = source_cn.owning_lib + AND record = target_record + AND NOT deleted; + + -- ... and if there's a conflicting one on the target ... + IF FOUND THEN + + -- ... move the copies to that, and ... + UPDATE asset.copy + SET call_number = target_cn.id + WHERE call_number = source_cn.id; + + -- ... move V holds to the move-target call number + FOR hold IN SELECT * FROM action.hold_request WHERE target = source_cn.id AND hold_type = 'V' LOOP + + UPDATE action.hold_request + SET target = target_cn.id + WHERE id = hold.id; + + moved_objects := moved_objects + 1; + END LOOP; + + UPDATE asset.call_number SET deleted = TRUE WHERE id = source_cn.id; + + -- ... if not ... + ELSE + -- ... just move the call number to the target record + UPDATE asset.call_number + SET record = target_record + WHERE id = source_cn.id; + END IF; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find T holds targeting the source record ... + FOR hold IN SELECT * FROM action.hold_request WHERE target = source_record AND hold_type = 'T' LOOP + + -- ... and move them to the target record + UPDATE action.hold_request + SET target = target_record + WHERE id = hold.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find serial records targeting the source record ... + FOR ser_rec IN SELECT * FROM serial.record_entry WHERE record = source_record LOOP + -- ... and move them to the target record + UPDATE serial.record_entry + SET record = target_record + WHERE id = ser_rec.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find serial subscriptions targeting the source record ... + FOR ser_sub IN SELECT * FROM serial.subscription WHERE record_entry = source_record LOOP + -- ... and move them to the target record + UPDATE serial.subscription + SET record_entry = target_record + WHERE id = ser_sub.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find booking resource types targeting the source record ... + FOR booking IN SELECT * FROM booking.resource_type WHERE record = source_record LOOP + -- ... and move them to the target record + UPDATE booking.resource_type + SET record = target_record + WHERE id = booking.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find acq lineitems targeting the source record ... + FOR acq_lineitem IN SELECT * FROM acq.lineitem WHERE eg_bib_id = source_record LOOP + -- ... and move them to the target record + UPDATE acq.lineitem + SET eg_bib_id = target_record + WHERE id = acq_lineitem.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find acq user purchase requests targeting the source record ... + FOR acq_request IN SELECT * FROM acq.user_request WHERE eg_bib = source_record LOOP + -- ... and move them to the target record + UPDATE acq.user_request + SET eg_bib = target_record + WHERE id = acq_request.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find parts attached to the source ... + FOR source_part IN SELECT * FROM biblio.monograph_part WHERE record = source_record LOOP + + SELECT INTO target_part * + FROM biblio.monograph_part + WHERE label = source_part.label + AND record = target_record; + + -- ... and if there's a conflicting one on the target ... + IF FOUND THEN + + -- ... move the copy-part maps to that, and ... + UPDATE asset.copy_part_map + SET part = target_part.id + WHERE part = source_part.id; + + -- ... move P holds to the move-target part + FOR hold IN SELECT * FROM action.hold_request WHERE target = source_part.id AND hold_type = 'P' LOOP + + UPDATE action.hold_request + SET target = target_part.id + WHERE id = hold.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- ... if not ... + ELSE + -- ... just move the part to the target record + UPDATE biblio.monograph_part + SET record = target_record + WHERE id = source_part.id; + END IF; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find multi_home items attached to the source ... + FOR multi_home IN SELECT * FROM biblio.peer_bib_copy_map WHERE peer_record = source_record LOOP + -- ... and move them to the target record + UPDATE biblio.peer_bib_copy_map + SET peer_record = target_record + WHERE id = multi_home.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- And delete mappings where the item's home bib was merged with the peer bib + DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = ( + SELECT (SELECT record FROM asset.call_number WHERE id = call_number) + FROM asset.copy WHERE id = target_copy + ); + + -- Apply merge tracking + UPDATE biblio.record_entry + SET merge_date = NOW() WHERE id = target_record; + + UPDATE biblio.record_entry + SET merge_date = NOW(), merged_to = target_record + WHERE id = source_record; + + -- Finally, "delete" the source record + DELETE FROM biblio.record_entry WHERE id = source_record; + + -- That's all, folks! + RETURN moved_objects; +END; +$func$ LANGUAGE plpgsql; + + + +SELECT evergreen.upgrade_deps_block_check('1091', :eg_version); + +ALTER TABLE acq.funding_source DROP CONSTRAINT funding_source_code_key; +ALTER TABLE acq.funding_source ALTER COLUMN code SET NOT NULL; +ALTER TABLE acq.funding_source ADD CONSTRAINT funding_source_code_once_per_owner UNIQUE (code,owner); + + +SELECT evergreen.upgrade_deps_block_check('1092', :eg_version); + +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; + tmp_array TEXT[]; + attr_def config.record_attr_definition%ROWTYPE; + ccvm_row config.coded_value_map%ROWTYPE; + jump_past BOOL; +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 + WHERE ( + tag IS NOT NULL OR + fixed_field IS NOT NULL OR + xpath IS NOT NULL OR + phys_char_sf IS NOT NULL OR + composite + ) AND ( + filter OR sorter + ); + 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 + + jump_past := FALSE; -- This gets set when we are non-multi and have found something + 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; + + IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection + SELECT ARRAY_AGG(value) 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; + + IF NOT attr_def.multi THEN + attr_value := ARRAY[ARRAY_TO_STRING(attr_value, COALESCE(attr_def.joiner,' '))]; + jump_past := TRUE; + END IF; + END IF; + + IF NOT jump_past AND attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field + attr_value := 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]]; + jump_past := TRUE; + END IF; + END IF; + + IF NOT jump_past AND 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 UNNEST(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; + END IF; + + IF NOT jump_past AND 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 tmp_array + 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) ); + + attr_value := attr_value || tmp_array; + + 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 THEN + DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name; + IF norm_attr_value[1] IS NOT NULL THEN + INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, norm_attr_value[1]); + END IF; + 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; + + + +SELECT evergreen.upgrade_deps_block_check('1093', :eg_version); + +UPDATE config.record_attr_definition SET tag = '041', sf_list = 'abdefgm' where name = 'item_lang'; + + + +SELECT evergreen.upgrade_deps_block_check('1094', :eg_version); + +SELECT metabib.reingest_record_attributes (record, '{item_lang}'::TEXT[]) + FROM (SELECT DISTINCT record + FROM metabib.real_full_rec + WHERE tag = '041' + AND subfield IN ('a','b','d','e','f','g','m') + ) x; + + + +SELECT evergreen.upgrade_deps_block_check('1095', :eg_version); + +CREATE OR REPLACE FUNCTION asset.copy_state (cid BIGINT) RETURNS TEXT AS $$ +DECLARE + last_circ_stop TEXT; + the_copy asset.copy%ROWTYPE; +BEGIN + + SELECT * INTO the_copy FROM asset.copy WHERE id = cid; + IF NOT FOUND THEN RETURN NULL; END IF; + + IF the_copy.status = 3 THEN -- Lost + RETURN 'LOST'; + ELSIF the_copy.status = 4 THEN -- Missing + RETURN 'MISSING'; + ELSIF the_copy.status = 14 THEN -- Damaged + RETURN 'DAMAGED'; + ELSIF the_copy.status = 17 THEN -- Lost and paid + RETURN 'LOST_AND_PAID'; + END IF; + + SELECT stop_fines INTO last_circ_stop + FROM action.circulation + WHERE target_copy = cid + ORDER BY xact_start DESC LIMIT 1; + + IF FOUND THEN + IF last_circ_stop IN ( + 'CLAIMSNEVERCHECKEDOUT', + 'CLAIMSRETURNED', + 'LONGOVERDUE' + ) THEN + RETURN last_circ_stop; + END IF; + END IF; + + RETURN 'NORMAL'; +END; +$$ LANGUAGE PLPGSQL; + +CREATE TYPE config.copy_alert_type_state AS ENUM ( + 'NORMAL', + 'LOST', + 'LOST_AND_PAID', + 'MISSING', + 'DAMAGED', + 'CLAIMSRETURNED', + 'LONGOVERDUE', + 'CLAIMSNEVERCHECKEDOUT' +); + +CREATE TYPE config.copy_alert_type_event AS ENUM ( + 'CHECKIN', + 'CHECKOUT' +); + +CREATE TABLE config.copy_alert_type ( + id serial primary key, -- reserve 1-100 for system + scope_org int not null references actor.org_unit (id) on delete cascade, + active bool not null default true, + name text not null unique, + state config.copy_alert_type_state, + event config.copy_alert_type_event, + in_renew bool, + invert_location bool not null default false, + at_circ bool, + at_owning bool, + next_status int[] +); +SELECT SETVAL('config.copy_alert_type_id_seq'::TEXT, 100); + +CREATE OR REPLACE FUNCTION evergreen.asset_copy_alert_copy_inh_fkey() RETURNS TRIGGER AS $f$ +BEGIN + PERFORM 1 FROM asset.copy WHERE id = NEW.copy; + IF NOT FOUND THEN + RAISE foreign_key_violation USING MESSAGE = FORMAT( + $$Referenced asset.copy id not found, copy:%s$$, NEW.copy + ); + END IF; + RETURN NEW; +END; +$f$ LANGUAGE PLPGSQL VOLATILE COST 50; + +CREATE TABLE actor.copy_alert_suppress ( + id serial primary key, + org int not null references actor.org_unit (id) on delete cascade, + alert_type int not null references config.copy_alert_type (id) on delete cascade +); + +CREATE TABLE asset.copy_alert ( + id bigserial primary key, + alert_type int not null references config.copy_alert_type (id) on delete cascade, + copy bigint not null, + temp bool not null default false, + create_time timestamptz not null default now(), + create_staff bigint not null references actor.usr (id) on delete set null, + note text, + ack_time timestamptz, + ack_staff bigint references actor.usr (id) on delete set null +); + +CREATE CONSTRAINT TRIGGER inherit_asset_copy_alert_copy_fkey + AFTER UPDATE OR INSERT ON asset.copy_alert + DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_copy_alert_copy_inh_fkey(); + +CREATE VIEW asset.active_copy_alert AS + SELECT * + FROM asset.copy_alert + WHERE ack_time IS NULL; + + + +SELECT evergreen.upgrade_deps_block_check('1096', :eg_version); + +-- staff-usable alert types with no location awareness +INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew) +VALUES (1, 1, TRUE, 'Normal checkout', 'NORMAL', 'CHECKOUT', FALSE); +INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew) +VALUES (2, 1, TRUE, 'Normal checkin', 'NORMAL', 'CHECKIN', FALSE); +INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew) +VALUES (3, 1, FALSE, 'Normal renewal', 'NORMAL', 'CHECKIN', TRUE); + +-- copy alerts upon checkin or renewal of exceptional copy statuses are not active by +-- default; they're meant to be turned once a site is ready to fully +-- commit to using the webstaff client for circulation +INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event) +VALUES (4, 1, FALSE, 'Checkin of lost copy', 'LOST', 'CHECKIN'); +INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event) +VALUES (5, 1, FALSE, 'Checkin of missing copy', 'MISSING', 'CHECKIN'); +INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event) +VALUES (6, 1, FALSE, 'Checkin of lost-and-paid copy', 'LOST_AND_PAID', 'CHECKIN'); +INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event) +VALUES (7, 1, FALSE, 'Checkin of damaged copy', 'DAMAGED', 'CHECKIN'); +INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event) +VALUES (8, 1, FALSE, 'Checkin of claims-returned copy', 'CLAIMSRETURNED', 'CHECKIN'); +INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event) +VALUES (9, 1, FALSE, 'Checkin of long overdue copy', 'LONGOVERDUE', 'CHECKIN'); +INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event) +VALUES (10, 1, FALSE, 'Checkin of claims-never-checked-out copy', 'CLAIMSNEVERCHECKEDOUT', 'CHECKIN'); + +-- copy alerts upon checkout of exceptional copy statuses are not active by +-- default; they're meant to be turned once a site is ready to fully +-- commit to using the webstaff client for circulation +INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event) +VALUES (11, 1, FALSE, 'Checkout of lost copy', 'LOST', 'CHECKOUT'); +INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event) +VALUES (12, 1, FALSE, 'Checkout of missing copy', 'MISSING', 'CHECKOUT'); +INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event) +VALUES (13, 1, FALSE, 'Checkout of lost-and-paid copy', 'LOST_AND_PAID', 'CHECKOUT'); +INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event) +VALUES (14, 1, FALSE, 'Checkout of damaged copy', 'DAMAGED', 'CHECKOUT'); +INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event) +VALUES (15, 1, FALSE, 'Checkout of claims-returned copy', 'CLAIMSRETURNED', 'CHECKOUT'); +INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event) +VALUES (16, 1, FALSE, 'Checkout of long overdue copy', 'LONGOVERDUE', 'CHECKOUT'); +INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event) +VALUES (17, 1, FALSE, 'Checkout of claims-never-checked-out copy', 'CLAIMSNEVERCHECKEDOUT', 'CHECKOUT'); + +-- staff-usable alert types based on location +INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew, at_circ) +VALUES (18, 1, FALSE, 'Normal checkout at circ lib', 'NORMAL', 'CHECKOUT', FALSE, TRUE); +INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew, at_circ) +VALUES (19, 1, FALSE, 'Normal checkin at circ lib', 'NORMAL', 'CHECKIN', FALSE, TRUE); +INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew, at_circ) +VALUES (20, 1, FALSE, 'Normal renewal at circ lib', 'NORMAL', 'CHECKIN', TRUE, TRUE); + +INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew, at_owning) +VALUES (21, 1, FALSE, 'Normal checkout at owning lib', 'NORMAL', 'CHECKOUT', FALSE, TRUE); +INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew, at_owning) +VALUES (22, 1, FALSE, 'Normal checkin at owning lib', 'NORMAL', 'CHECKIN', FALSE, TRUE); +INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew, at_owning) +VALUES (23, 1, FALSE, 'Normal renewal at owning lib', 'NORMAL', 'CHECKIN', TRUE, TRUE); + + +SELECT evergreen.upgrade_deps_block_check('1097', :eg_version); + +INSERT INTO config.org_unit_setting_type + (name, grp, label, description, datatype) + VALUES + ('circ.copy_alerts.forgive_fines_on_lost_checkin', + 'circ', + oils_i18n_gettext('circ.copy_alerts.forgive_fines_on_lost_checkin', + 'Forgive fines when checking out a lost item and copy alert is suppressed?', + 'coust', 'label'), + oils_i18n_gettext('circ.copy_alerts.forgive_fines_on_lost_checkin', + 'Controls whether fines are automatically forgiven when checking out an '|| + 'item that has been marked as lost, and the corresponding copy alert has been '|| + 'suppressed.', + 'coust', 'description'), + 'bool'); + +INSERT INTO config.org_unit_setting_type + (name, grp, label, description, datatype) + VALUES + ('circ.copy_alerts.forgive_fines_on_long_overdue_checkin', + 'circ', + oils_i18n_gettext('circ.copy_alerts.forgive_fines_on_long_overdue_checkin', + 'Forgive fines when checking out a long-overdue item and copy alert is suppressed?', + 'coust', 'label'), + oils_i18n_gettext('circ.copy_alerts.forgive_fines_on_lost_checkin', + 'Controls whether fines are automatically forgiven when checking out an '|| + 'item that has been marked as lost, and the corresponding copy alert has been '|| + 'suppressed.', + 'coust', 'description'), + 'bool'); + + +SELECT evergreen.upgrade_deps_block_check('1098', :eg_version); + +\qecho Copying copy alert messages to normal checkout copy alerts... +INSERT INTO asset.copy_alert (alert_type, copy, note, create_staff) +SELECT 1, id, alert_message, 1 +FROM asset.copy +WHERE alert_message IS NOT NULL +AND alert_message <> ''; + +\qecho Copying copy alert messages to normal checkin copy alerts... +INSERT INTO asset.copy_alert (alert_type, copy, note, create_staff) +SELECT 2, id, alert_message, 1 +FROM asset.copy +WHERE alert_message IS NOT NULL +AND alert_message <> ''; + +\qecho Clearing legacy copy alert field; this may take a while +UPDATE asset.copy SET alert_message = NULL +WHERE alert_message IS NOT NULL; + + +SELECT evergreen.upgrade_deps_block_check('1099', :eg_version); + +\qecho Making the following copy alert types active by default; if you +\qecho are not using the web staff client yet, you may want to disable +\qecho them. +\qecho - Checkin of lost, missing, lost-and-paid, damaged, claims returned, +\qecho long overdue, and claims never checked out items. +\qecho - Checkout of lost, missing, lost-and-paid, damaged, claims returned, +\qecho long overdue, and claims never checked out items. + +UPDATE config.copy_alert_type +SET active = TRUE +WHERE id IN (4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17); + + +SELECT evergreen.upgrade_deps_block_check('1100', :eg_version); + +-- NEW config.metabib_field entries + +UPDATE config.metabib_field SET display_xpath = facet_xpath, display_field = TRUE WHERE id = 33; + +INSERT INTO config.metabib_field (id, field_class, name, + label, xpath, display_field, search_field, browse_field) +VALUES ( + 38, 'identifier', 'edition', + oils_i18n_gettext(38, 'Edition', 'cmf', 'label'), + $$//mods33:mods/mods33:originInfo//mods33:edition[1]$$, + TRUE, TRUE, FALSE +); + +INSERT INTO config.metabib_field (id, field_class, name, + label, xpath, display_field, search_field, browse_field) +VALUES ( + 39, 'keyword', 'physical_description', + oils_i18n_gettext(39, 'Physical Descrption', 'cmf', 'label'), + $$(//mods33:mods/mods33:physicalDescription/mods33:form|//mods33:mods/mods33:physicalDescription/mods33:extent|//mods33:mods/mods33:physicalDescription/mods33:reformattingQuality|//mods33:mods/mods33:physicalDescription/mods33:internetMediaType|//mods33:mods/mods33:physicalDescription/mods33:digitalOrigin)$$, + TRUE, TRUE, FALSE +); + +INSERT INTO config.metabib_field (id, field_class, name, + label, xpath, display_field, search_field, browse_field) +VALUES ( + 40, 'identifier', 'publisher', + oils_i18n_gettext(40, 'Publisher', 'cmf', 'label'), + $$//mods33:mods/mods33:originInfo//mods33:publisher[1]$$, + TRUE, TRUE, FALSE +); + +INSERT INTO config.metabib_field (id, field_class, name, + label, xpath, display_field, search_field, browse_field) +VALUES ( + 41, 'keyword', 'abstract', + oils_i18n_gettext(41, 'Abstract', 'cmf', 'label'), + $$//mods33:mods/mods33:abstract$$, + TRUE, TRUE, FALSE +); + +INSERT INTO config.metabib_field (id, field_class, name, + label, xpath, display_field, search_field, browse_field) +VALUES ( + 42, 'keyword', 'toc', + oils_i18n_gettext(42, 'Table of Contents', 'cmf', 'label'), + $$//mods33:tableOfContents$$, + TRUE, TRUE, FALSE +); + +INSERT INTO config.metabib_field (id, field_class, name, + label, xpath, display_field, search_field, browse_field) +VALUES ( + 43, 'identifier', 'type_of_resource', + oils_i18n_gettext(43, 'Type of Resource', 'cmf', 'label'), + $$//mods33:mods/mods33:typeOfResource$$, + TRUE, FALSE, FALSE +); + +INSERT INTO config.metabib_field (id, field_class, name, + label, xpath, display_field, search_field, browse_field) +VALUES ( + 44, 'identifier', 'pubdate', + oils_i18n_gettext(44, 'Publication Date', 'cmf', 'label'), + $$//mods33:mods/mods33:originInfo//mods33:dateIssued[@encoding="marc"]|//mods33:mods/mods33:originInfo//mods33:dateIssued[1]$$, + TRUE, FALSE, FALSE +); + +INSERT INTO config.metabib_field (id, field_class, name, + label, xpath, display_field, search_field, browse_field) +VALUES ( + 46, 'keyword', 'bibliography', + oils_i18n_gettext(46, 'Bibliography', 'cmf', 'label'), + $$//mods33:note[@type='bibliography']$$, + TRUE, TRUE, FALSE +),( + 47, 'keyword', 'thesis', + oils_i18n_gettext(47, 'Thesis', 'cmf', 'label'), + $$//mods33:note[@type='thesis']$$, + TRUE, TRUE, FALSE +),( + 48, 'keyword', 'production_credits', + oils_i18n_gettext(48, 'Creation/Production Credits', 'cmf', 'label'), + $$//mods33:note[@type='creation/production credits']$$, + TRUE, TRUE, FALSE +),( + 49, 'keyword', 'performers', + oils_i18n_gettext(49, 'Performers', 'cmf', 'label'), + $$//mods33:note[@type='performers']$$, + TRUE, TRUE, FALSE +),( + 50, 'keyword', 'general_note', + oils_i18n_gettext(50, 'General Note', 'cmf', 'label'), + $$//mods33:note[not(@type)]$$, + TRUE, TRUE, FALSE +) +; + +INSERT INTO config.metabib_field (id, field_class, name, format, + label, xpath, display_xpath, display_field, search_field, browse_field) +VALUES ( + 51, 'author', 'first_author', 'mods32', + oils_i18n_gettext(51, 'Author', 'cmf', 'label'), + $$//mods32:mods/mods32:name[mods32:role/mods32:roleTerm[text()='creator']][1]$$, + $$//*[local-name()='namePart']$$, + TRUE, TRUE, FALSE +); + +INSERT INTO config.metabib_field (id, field_class, name, format, + label, xpath, display_xpath, display_field, search_field, browse_field) +VALUES ( + 52, 'identifier', 'origin_info', 'marcxml', + oils_i18n_gettext(52, 'Origin Info', 'cmf', 'label'), + $$//*[@tag='260']$$, + $$//*[local-name()='subfield' and contains('abc',@code)]$$, + TRUE, FALSE, FALSE +); + + +-- Modify existing config.metabib_field entries + +UPDATE config.metabib_field SET display_field = TRUE WHERE id IN ( + 1, -- seriestitle + 11, -- subject_geographic + 12, -- subject_name + 13, -- subject_temporal + 14, -- subject_topic + 19, -- ISSN + 20, -- UPC + 26 -- TCN +); + +-- Map display field names to config.metabib_field entries + +INSERT INTO config.display_field_map (name, field, multi) VALUES + ('series_title', 1, TRUE), + ('subject_geographic', 11, TRUE), + ('subject_name', 12, TRUE), + ('subject_temporal', 13, TRUE), + ('subject_topic', 14, TRUE), + ('issn', 19, TRUE), + ('upc', 20, TRUE), + ('tcn', 26, FALSE), + ('edition', 38, FALSE), + ('physical_description',39, TRUE), + ('genre', 33, TRUE), + ('bibliography', 46, TRUE), + ('thesis', 47, TRUE), + ('performers', 49, TRUE), + ('production_credits', 48, TRUE), + ('general_note', 50, TRUE), + ('publisher', 52, FALSE), + ('abstract', 41, FALSE), + ('toc', 42, FALSE), + ('type_of_resource', 43, FALSE), + ('pubdate', 44, FALSE) +; + +UPDATE config.display_field_map SET field = 51 WHERE name = 'author'; + +-- Add a column to wide-display-entry per well-known field + +DROP VIEW IF EXISTS metabib.wide_display_entry; +CREATE VIEW metabib.wide_display_entry AS + SELECT + bre.id AS source, + COALESCE(mcde_title.value, 'null')::TEXT AS title, + COALESCE(mcde_author.value, 'null')::TEXT AS author, + COALESCE(mcde_subject_geographic.value, 'null')::TEXT AS subject_geographic, + COALESCE(mcde_subject_name.value, 'null')::TEXT AS subject_name, + COALESCE(mcde_subject_temporal.value, 'null')::TEXT AS subject_temporal, + COALESCE(mcde_subject_topic.value, 'null')::TEXT AS subject_topic, + COALESCE(mcde_creators.value, 'null')::TEXT AS creators, + COALESCE(mcde_isbn.value, 'null')::TEXT AS isbn, + COALESCE(mcde_issn.value, 'null')::TEXT AS issn, + COALESCE(mcde_upc.value, 'null')::TEXT AS upc, + COALESCE(mcde_tcn.value, 'null')::TEXT AS tcn, + COALESCE(mcde_edition.value, 'null')::TEXT AS edition, + COALESCE(mcde_physical_description.value, 'null')::TEXT AS physical_description, + COALESCE(mcde_publisher.value, 'null')::TEXT AS publisher, + COALESCE(mcde_series_title.value, 'null')::TEXT AS series_title, + COALESCE(mcde_abstract.value, 'null')::TEXT AS abstract, + COALESCE(mcde_toc.value, 'null')::TEXT AS toc, + COALESCE(mcde_pubdate.value, 'null')::TEXT AS pubdate, + COALESCE(mcde_type_of_resource.value, 'null')::TEXT AS type_of_resource + FROM biblio.record_entry bre + LEFT JOIN metabib.compressed_display_entry mcde_title + ON (bre.id = mcde_title.source AND mcde_title.name = 'title') + LEFT JOIN metabib.compressed_display_entry mcde_author + ON (bre.id = mcde_author.source AND mcde_author.name = 'author') + LEFT JOIN metabib.compressed_display_entry mcde_subject + ON (bre.id = mcde_subject.source AND mcde_subject.name = 'subject') + LEFT JOIN metabib.compressed_display_entry mcde_subject_geographic + ON (bre.id = mcde_subject_geographic.source + AND mcde_subject_geographic.name = 'subject_geographic') + LEFT JOIN metabib.compressed_display_entry mcde_subject_name + ON (bre.id = mcde_subject_name.source + AND mcde_subject_name.name = 'subject_name') + LEFT JOIN metabib.compressed_display_entry mcde_subject_temporal + ON (bre.id = mcde_subject_temporal.source + AND mcde_subject_temporal.name = 'subject_temporal') + LEFT JOIN metabib.compressed_display_entry mcde_subject_topic + ON (bre.id = mcde_subject_topic.source + AND mcde_subject_topic.name = 'subject_topic') + LEFT JOIN metabib.compressed_display_entry mcde_creators + ON (bre.id = mcde_creators.source AND mcde_creators.name = 'creators') + LEFT JOIN metabib.compressed_display_entry mcde_isbn + ON (bre.id = mcde_isbn.source AND mcde_isbn.name = 'isbn') + LEFT JOIN metabib.compressed_display_entry mcde_issn + ON (bre.id = mcde_issn.source AND mcde_issn.name = 'issn') + LEFT JOIN metabib.compressed_display_entry mcde_upc + ON (bre.id = mcde_upc.source AND mcde_upc.name = 'upc') + LEFT JOIN metabib.compressed_display_entry mcde_tcn + ON (bre.id = mcde_tcn.source AND mcde_tcn.name = 'tcn') + LEFT JOIN metabib.compressed_display_entry mcde_edition + ON (bre.id = mcde_edition.source AND mcde_edition.name = 'edition') + LEFT JOIN metabib.compressed_display_entry mcde_physical_description + ON (bre.id = mcde_physical_description.source + AND mcde_physical_description.name = 'physical_description') + LEFT JOIN metabib.compressed_display_entry mcde_publisher + ON (bre.id = mcde_publisher.source AND mcde_publisher.name = 'publisher') + LEFT JOIN metabib.compressed_display_entry mcde_series_title + ON (bre.id = mcde_series_title.source AND mcde_series_title.name = 'series_title') + LEFT JOIN metabib.compressed_display_entry mcde_abstract + ON (bre.id = mcde_abstract.source AND mcde_abstract.name = 'abstract') + LEFT JOIN metabib.compressed_display_entry mcde_toc + ON (bre.id = mcde_toc.source AND mcde_toc.name = 'toc') + LEFT JOIN metabib.compressed_display_entry mcde_pubdate + ON (bre.id = mcde_pubdate.source AND mcde_pubdate.name = 'pubdate') + LEFT JOIN metabib.compressed_display_entry mcde_type_of_resource + ON (bre.id = mcde_type_of_resource.source + AND mcde_type_of_resource.name = 'type_of_resource') +; + +CREATE OR REPLACE VIEW reporter.old_super_simple_record AS +SELECT r.id, + r.fingerprint, + r.quality, + r.tcn_source, + r.tcn_value, + evergreen.oils_json_to_text(d.title) AS title, + evergreen.oils_json_to_text(d.author) AS author, + evergreen.oils_json_to_text(d.publisher) AS publisher, + evergreen.oils_json_to_text(d.pubdate) AS pubdate, + CASE WHEN d.isbn = 'null' + THEN NULL + ELSE (SELECT ARRAY(SELECT json_array_elements_text(d.isbn::JSON))) + END AS isbn, + CASE WHEN d.issn = 'null' + THEN NULL + ELSE (SELECT ARRAY(SELECT json_array_elements_text(d.issn::JSON))) + END AS issn + FROM biblio.record_entry r + JOIN metabib.wide_display_entry d ON (r.id = d.source); + + + +SELECT evergreen.upgrade_deps_block_check('1101', :eg_version); + +ALTER TABLE config.metabib_field ALTER COLUMN xpath DROP NOT NULL; + +CREATE TABLE config.metabib_field_virtual_map ( + id SERIAL PRIMARY KEY, + real INT NOT NULL REFERENCES config.metabib_field (id), + virtual INT NOT NULL REFERENCES config.metabib_field (id), + weight INT NOT NULL DEFAULT 1 +); +COMMENT ON TABLE config.metabib_field_virtual_map IS $$ +Maps between real (physically extracted) index definitions +and virtual (target sync, no required extraction of its own) +index definitions. + +The virtual side may not extract any data of its own, but +will collect data from all of the real fields. This reduces +extraction (ingest) overhead by eliminating duplcated extraction, +and allows for searching across novel combinations of fields, such +as names used as either subjects or authors. By preserving this +mapping rather than defining duplicate extractions, information +about the originating, "real" index definitions can be used +in interesting ways, such as highlighting in search results. +$$; + +CREATE OR REPLACE VIEW metabib.combined_all_field_entry AS + SELECT * FROM metabib.combined_title_field_entry + UNION ALL + SELECT * FROM metabib.combined_author_field_entry + UNION ALL + SELECT * FROM metabib.combined_subject_field_entry + UNION ALL + SELECT * FROM metabib.combined_keyword_field_entry + UNION ALL + SELECT * FROM metabib.combined_identifier_field_entry + UNION ALL + SELECT * FROM metabib.combined_series_field_entry; + + +CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry ( + rid BIGINT, + default_joiner TEXT, + field_types TEXT[], + only_fields INT[] +) RETURNS SETOF metabib.field_entry_template AS $func$ +DECLARE + bib biblio.record_entry%ROWTYPE; + idx config.metabib_field%ROWTYPE; + xfrm config.xml_transform%ROWTYPE; + prev_xfrm TEXT; + transformed_xml TEXT; + xml_node TEXT; + xml_node_list TEXT[]; + facet_text TEXT; + display_text TEXT; + browse_text TEXT; + sort_value TEXT; + raw_text TEXT; + curr_text TEXT; + joiner TEXT := default_joiner; -- XXX will index defs supply a joiner? + authority_text TEXT; + authority_link BIGINT; + output_row metabib.field_entry_template%ROWTYPE; + process_idx BOOL; +BEGIN + + -- Start out with no field-use bools set + output_row.browse_field = FALSE; + output_row.facet_field = FALSE; + output_row.display_field = FALSE; + output_row.search_field = FALSE; + + -- Get the record + SELECT INTO bib * FROM biblio.record_entry WHERE id = rid; + + -- Loop over the indexing entries + FOR idx IN SELECT * FROM config.metabib_field WHERE id = ANY (only_fields) ORDER BY format LOOP + CONTINUE WHEN idx.xpath IS NULL OR idx.xpath = ''; -- pure virtual field + + process_idx := FALSE; + IF idx.display_field AND 'display' = ANY (field_types) THEN process_idx = TRUE; END IF; + IF idx.browse_field AND 'browse' = ANY (field_types) THEN process_idx = TRUE; END IF; + IF idx.search_field AND 'search' = ANY (field_types) THEN process_idx = TRUE; END IF; + IF idx.facet_field AND 'facet' = ANY (field_types) THEN process_idx = TRUE; END IF; + CONTINUE WHEN process_idx = FALSE; -- disabled for all types + + joiner := COALESCE(idx.joiner, default_joiner); + + SELECT INTO xfrm * from config.xml_transform WHERE name = idx.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(bib.marc,xfrm.xslt); + ELSE + transformed_xml := bib.marc; + END IF; + + prev_xfrm := xfrm.name; + END IF; + + xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] ); + + raw_text := NULL; + FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP + CONTINUE WHEN xml_node !~ E'^\\s*<'; + + -- XXX much of this should be moved into oils_xpath_string... + curr_text := ARRAY_TO_STRING(evergreen.array_remove_item_by_value(evergreen.array_remove_item_by_value( + oils_xpath( '//text()', -- get the content of all the nodes within the main selected node + REGEXP_REPLACE( xml_node, E'\\s+', ' ', 'g' ) -- Translate adjacent whitespace to a single space + ), ' '), ''), -- throw away morally empty (bankrupt?) strings + joiner + ); + + CONTINUE WHEN curr_text IS NULL OR curr_text = ''; + + IF raw_text IS NOT NULL THEN + raw_text := raw_text || joiner; + END IF; + + raw_text := COALESCE(raw_text,'') || curr_text; + + -- autosuggest/metabib.browse_entry + IF idx.browse_field THEN + + IF idx.browse_xpath IS NOT NULL AND idx.browse_xpath <> '' THEN + browse_text := oils_xpath_string( idx.browse_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] ); + ELSE + browse_text := curr_text; + END IF; + + IF idx.browse_sort_xpath IS NOT NULL AND + idx.browse_sort_xpath <> '' THEN + + sort_value := oils_xpath_string( + idx.browse_sort_xpath, xml_node, joiner, + ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] + ); + ELSE + sort_value := browse_text; + END IF; + + output_row.field_class = idx.field_class; + output_row.field = idx.id; + output_row.source = rid; + output_row.value = BTRIM(REGEXP_REPLACE(browse_text, E'\\s+', ' ', 'g')); + output_row.sort_value := + public.naco_normalize(sort_value); + + output_row.authority := NULL; + + IF idx.authority_xpath IS NOT NULL AND idx.authority_xpath <> '' THEN + authority_text := oils_xpath_string( + idx.authority_xpath, xml_node, joiner, + ARRAY[ + ARRAY[xfrm.prefix, xfrm.namespace_uri], + ARRAY['xlink','http://www.w3.org/1999/xlink'] + ] + ); + + IF authority_text ~ '^\d+$' THEN + authority_link := authority_text::BIGINT; + PERFORM * FROM authority.record_entry WHERE id = authority_link; + IF FOUND THEN + output_row.authority := authority_link; + END IF; + END IF; + + END IF; + + output_row.browse_field = TRUE; + -- Returning browse rows with search_field = true for search+browse + -- configs allows us to retain granularity of being able to search + -- browse fields with "starts with" type operators (for example, for + -- titles of songs in music albums) + IF idx.search_field THEN + output_row.search_field = TRUE; + END IF; + RETURN NEXT output_row; + output_row.browse_field = FALSE; + output_row.search_field = FALSE; + output_row.sort_value := NULL; + END IF; + + -- insert raw node text for faceting + IF idx.facet_field THEN + + IF idx.facet_xpath IS NOT NULL AND idx.facet_xpath <> '' THEN + facet_text := oils_xpath_string( idx.facet_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] ); + ELSE + facet_text := curr_text; + END IF; + + output_row.field_class = idx.field_class; + output_row.field = -1 * idx.id; + output_row.source = rid; + output_row.value = BTRIM(REGEXP_REPLACE(facet_text, E'\\s+', ' ', 'g')); + + output_row.facet_field = TRUE; + RETURN NEXT output_row; + output_row.facet_field = FALSE; + END IF; + + -- insert raw node text for display + IF idx.display_field THEN + + IF idx.display_xpath IS NOT NULL AND idx.display_xpath <> '' THEN + display_text := oils_xpath_string( idx.display_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] ); + ELSE + display_text := curr_text; + END IF; + + output_row.field_class = idx.field_class; + output_row.field = -1 * idx.id; + output_row.source = rid; + output_row.value = BTRIM(REGEXP_REPLACE(display_text, E'\\s+', ' ', 'g')); + + output_row.display_field = TRUE; + RETURN NEXT output_row; + output_row.display_field = FALSE; + END IF; + + END LOOP; + + CONTINUE WHEN raw_text IS NULL OR raw_text = ''; + + -- insert combined node text for searching + IF idx.search_field THEN + output_row.field_class = idx.field_class; + output_row.field = idx.id; + output_row.source = rid; + output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'g')); + + output_row.search_field = TRUE; + RETURN NEXT output_row; + output_row.search_field = FALSE; + END IF; + + END LOOP; + +END; +$func$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION metabib.update_combined_index_vectors(bib_id BIGINT) RETURNS VOID AS $func$ +DECLARE + rdata TSVECTOR; + vclass TEXT; + vfield INT; + rfields INT[]; +BEGIN + DELETE FROM metabib.combined_keyword_field_entry WHERE record = bib_id; + INSERT INTO metabib.combined_keyword_field_entry(record, metabib_field, index_vector) + SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector) + FROM metabib.keyword_field_entry WHERE source = bib_id GROUP BY field; + INSERT INTO metabib.combined_keyword_field_entry(record, metabib_field, index_vector) + SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector) + FROM metabib.keyword_field_entry WHERE source = bib_id; + + DELETE FROM metabib.combined_title_field_entry WHERE record = bib_id; + INSERT INTO metabib.combined_title_field_entry(record, metabib_field, index_vector) + SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector) + FROM metabib.title_field_entry WHERE source = bib_id GROUP BY field; + INSERT INTO metabib.combined_title_field_entry(record, metabib_field, index_vector) + SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector) + FROM metabib.title_field_entry WHERE source = bib_id; + + DELETE FROM metabib.combined_author_field_entry WHERE record = bib_id; + INSERT INTO metabib.combined_author_field_entry(record, metabib_field, index_vector) + SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector) + FROM metabib.author_field_entry WHERE source = bib_id GROUP BY field; + INSERT INTO metabib.combined_author_field_entry(record, metabib_field, index_vector) + SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector) + FROM metabib.author_field_entry WHERE source = bib_id; + + DELETE FROM metabib.combined_subject_field_entry WHERE record = bib_id; + INSERT INTO metabib.combined_subject_field_entry(record, metabib_field, index_vector) + SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector) + FROM metabib.subject_field_entry WHERE source = bib_id GROUP BY field; + INSERT INTO metabib.combined_subject_field_entry(record, metabib_field, index_vector) + SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector) + FROM metabib.subject_field_entry WHERE source = bib_id; + + DELETE FROM metabib.combined_series_field_entry WHERE record = bib_id; + INSERT INTO metabib.combined_series_field_entry(record, metabib_field, index_vector) + SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector) + FROM metabib.series_field_entry WHERE source = bib_id GROUP BY field; + INSERT INTO metabib.combined_series_field_entry(record, metabib_field, index_vector) + SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector) + FROM metabib.series_field_entry WHERE source = bib_id; + + DELETE FROM metabib.combined_identifier_field_entry WHERE record = bib_id; + INSERT INTO metabib.combined_identifier_field_entry(record, metabib_field, index_vector) + SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector) + FROM metabib.identifier_field_entry WHERE source = bib_id GROUP BY field; + INSERT INTO metabib.combined_identifier_field_entry(record, metabib_field, index_vector) + SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector) + FROM metabib.identifier_field_entry WHERE source = bib_id; + + -- For each virtual def, gather the data from the combined real field + -- entries and append it to the virtual combined entry. + FOR vfield, rfields IN SELECT virtual, ARRAY_AGG(real) FROM config.metabib_field_virtual_map GROUP BY virtual LOOP + SELECT field_class INTO vclass + FROM config.metabib_field + WHERE id = vfield; + + SELECT string_agg(index_vector::TEXT,' ')::tsvector INTO rdata + FROM metabib.combined_all_field_entry + WHERE record = bib_id + AND metabib_field = ANY (rfields); + + BEGIN -- I cannot wait for INSERT ON CONFLICT ... 9.5, though + EXECUTE $$ + INSERT INTO metabib.combined_$$ || vclass || $$_field_entry + (record, metabib_field, index_vector) VALUES ($1, $2, $3) + $$ USING bib_id, vfield, rdata; + EXCEPTION WHEN unique_violation THEN + EXECUTE $$ + UPDATE metabib.combined_$$ || vclass || $$_field_entry + SET index_vector = index_vector || $3 + WHERE record = $1 + AND metabib_field = $2 + $$ USING bib_id, vfield, rdata; + WHEN OTHERS THEN + -- ignore and move on + END; + END LOOP; +END; +$func$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE VIEW search.best_tsconfig AS + SELECT m.id AS id, + COALESCE(f.ts_config, c.ts_config, 'simple') AS ts_config + FROM config.metabib_field m + LEFT JOIN config.metabib_class_ts_map c ON (c.field_class = m.field_class AND c.index_weight = 'C') + LEFT JOIN config.metabib_field_ts_map f ON (f.metabib_field = m.id AND f.index_weight = 'C'); + +CREATE TYPE search.highlight_result AS ( id BIGINT, source BIGINT, field INT, value TEXT, highlight TEXT ); + +CREATE OR REPLACE FUNCTION search.highlight_display_fields_impl( + rid BIGINT, + tsq TEXT, + field_list INT[] DEFAULT '{}'::INT[], + css_class TEXT DEFAULT 'oils_SH', + hl_all BOOL DEFAULT TRUE, + minwords INT DEFAULT 5, + maxwords INT DEFAULT 25, + shortwords INT DEFAULT 0, + maxfrags INT DEFAULT 0, + delimiter TEXT DEFAULT ' ... ' +) RETURNS SETOF search.highlight_result AS $f$ +DECLARE + opts TEXT := ''; + v_css_class TEXT := css_class; + v_delimiter TEXT := delimiter; + v_field_list INT[] := field_list; + hl_query TEXT; +BEGIN + IF v_delimiter LIKE $$%'%$$ OR v_delimiter LIKE '%"%' THEN --" + v_delimiter := ' ... '; + END IF; + + IF NOT hl_all THEN + opts := opts || 'MinWords=' || minwords; + opts := opts || ', MaxWords=' || maxwords; + opts := opts || ', ShortWords=' || shortwords; + opts := opts || ', MaxFragments=' || maxfrags; + opts := opts || ', FragmentDelimiter="' || delimiter || '"'; + ELSE + opts := opts || 'HighlightAll=TRUE'; + END IF; + + IF v_css_class LIKE $$%'%$$ OR v_css_class LIKE '%"%' THEN -- " + v_css_class := 'oils_SH'; + END IF; + + opts := opts || $$, StopSel=, StartSel=""$xx$ -- "' + ) AS highlight + FROM metabib.display_entry de + JOIN config.metabib_field mf ON (mf.id = de.field) + JOIN search.best_tsconfig t ON (t.id = de.field) + WHERE de.source = $2 + AND field = ANY ($3) + ORDER BY de.id;$$; + + RETURN QUERY EXECUTE hl_query USING opts, rid, v_field_list; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION evergreen.escape_for_html (TEXT) RETURNS TEXT AS $$ + SELECT regexp_replace( + regexp_replace( + regexp_replace( + $1, + '&', + '&', + 'g' + ), + '<', + '<', + 'g' + ), + '>', + '>', + 'g' + ); +$$ LANGUAGE SQL IMMUTABLE LEAKPROOF STRICT COST 10; + +CREATE OR REPLACE FUNCTION search.highlight_display_fields( + rid BIGINT, + tsq_map TEXT, -- { '(a | b) & c' => '1,2,3,4', ...} + css_class TEXT DEFAULT 'oils_SH', + hl_all BOOL DEFAULT TRUE, + minwords INT DEFAULT 5, + maxwords INT DEFAULT 25, + shortwords INT DEFAULT 0, + maxfrags INT DEFAULT 0, + delimiter TEXT DEFAULT ' ... ' +) RETURNS SETOF search.highlight_result AS $f$ +DECLARE + tsq_hstore HSTORE; + tsq TEXT; + fields TEXT; + afields INT[]; + seen INT[]; +BEGIN + + IF (tsq_map ILIKE 'hstore%') THEN + EXECUTE 'SELECT ' || tsq_map INTO tsq_hstore; + ELSE + tsq_hstore := tsq_map::HSTORE; + END IF; + + FOR tsq, fields IN SELECT key, value FROM each(tsq_hstore::HSTORE) LOOP + SELECT ARRAY_AGG(unnest::INT) INTO afields + FROM unnest(regexp_split_to_array(fields,',')); + seen := seen || afields; + + RETURN QUERY + SELECT * FROM search.highlight_display_fields_impl( + rid, tsq, afields, css_class, hl_all,minwords, + maxwords, shortwords, maxfrags, delimiter + ); + END LOOP; + + RETURN QUERY + SELECT id, + source, + field, + value, + value AS highlight + FROM metabib.display_entry + WHERE source = rid + AND NOT (field = ANY (seen)); +END; +$f$ LANGUAGE PLPGSQL ROWS 10; + +CREATE OR REPLACE FUNCTION metabib.remap_metarecord_for_bib( + bib_id bigint, + fp text, + bib_is_deleted boolean DEFAULT false, + retain_deleted boolean DEFAULT false +) RETURNS bigint AS $function$ +DECLARE + new_mapping BOOL := TRUE; + source_count INT; + old_mr BIGINT; + tmp_mr metabib.metarecord%ROWTYPE; + deleted_mrs BIGINT[]; +BEGIN + + -- We need to make sure we're not a deleted master record of an MR + IF bib_is_deleted THEN + IF NOT retain_deleted THEN -- Go away for any MR that we're master of, unless retained + DELETE FROM metabib.metarecord_source_map WHERE source = bib_id; + END IF; + + FOR old_mr IN SELECT id FROM metabib.metarecord WHERE master_record = bib_id LOOP + + -- Now, are there any more sources on this MR? + SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = old_mr; + + IF source_count = 0 AND NOT retain_deleted THEN -- No other records + deleted_mrs := ARRAY_APPEND(deleted_mrs, old_mr); -- Just in case... + DELETE FROM metabib.metarecord WHERE id = old_mr; + + ELSE -- indeed there are. Update it with a null cache and recalcualated master record + UPDATE metabib.metarecord + SET mods = NULL, + master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp AND NOT deleted ORDER BY quality DESC LIMIT 1) + WHERE id = old_mr; + END IF; + END LOOP; + + ELSE -- insert or update + + FOR tmp_mr IN SELECT m.* FROM metabib.metarecord m JOIN metabib.metarecord_source_map s ON (s.metarecord = m.id) WHERE s.source = bib_id LOOP + + -- Find the first fingerprint-matching + IF old_mr IS NULL AND fp = tmp_mr.fingerprint THEN + old_mr := tmp_mr.id; + new_mapping := FALSE; + + ELSE -- Our fingerprint changed ... maybe remove the old MR + DELETE FROM metabib.metarecord_source_map WHERE metarecord = tmp_mr.id AND source = bib_id; -- remove the old source mapping + SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = tmp_mr.id; + IF source_count = 0 THEN -- No other records + deleted_mrs := ARRAY_APPEND(deleted_mrs, tmp_mr.id); + DELETE FROM metabib.metarecord WHERE id = tmp_mr.id; + END IF; + END IF; + + END LOOP; + + -- we found no suitable, preexisting MR based on old source maps + IF old_mr IS NULL THEN + SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp; -- is there one for our current fingerprint? + + IF old_mr IS NULL THEN -- nope, create one and grab its id + INSERT INTO metabib.metarecord ( fingerprint, master_record ) VALUES ( fp, bib_id ); + SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp; + + ELSE -- indeed there is. update it with a null cache and recalcualated master record + UPDATE metabib.metarecord + SET mods = NULL, + master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp AND NOT deleted ORDER BY quality DESC LIMIT 1) + WHERE id = old_mr; + END IF; + + ELSE -- there was one we already attached to, update its mods cache and master_record + UPDATE metabib.metarecord + SET mods = NULL, + master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp AND NOT deleted ORDER BY quality DESC LIMIT 1) + WHERE id = old_mr; + END IF; + + IF new_mapping THEN + INSERT INTO metabib.metarecord_source_map (metarecord, source) VALUES (old_mr, bib_id); -- new source mapping + END IF; + + END IF; + + IF ARRAY_UPPER(deleted_mrs,1) > 0 THEN + UPDATE action.hold_request SET target = old_mr WHERE target IN ( SELECT unnest(deleted_mrs) ) AND hold_type = 'M'; -- if we had to delete any MRs above, make sure their holds are moved + END IF; + + RETURN old_mr; + +END; +$function$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION evergreen.marc_to (marc text, xfrm text) RETURNS TEXT AS $$ + SELECT evergreen.xml_pretty_print(xslt_process($1,xslt)::XML)::TEXT FROM config.xml_transform WHERE name = $2; +$$ LANGUAGE SQL; + + + +SELECT evergreen.upgrade_deps_block_check('1102', :eg_version); + +update config.xml_transform set xslt = $XXXX$ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + BK + SE + + + BK + MM + CF + MP + VM + MU + + + + + + + + + b + afgk + + + + + abfgk + + + + + + + + + + + ,;/ + + + + + + + + + + <xsl:value-of select="substring($titleChop,@ind2+1)"/> + + + + + <xsl:value-of select="$titleChop"/> + + + + + + + + + b + b + afgk + + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="subfieldSelect"> + <xsl:with-param name="codes">abfgk</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + abfgk + + + + + + + + + + + <xsl:value-of select="substring($titleBrowseChop,@ind2+1)"/> + + + + + <xsl:value-of select="$titleBrowseChop"/> + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="subfieldSelect"> + <xsl:with-param name="codes">a</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + + a + + + + + + + + + + + + + <xsl:value-of select="$titleChop" /> + + + + + + + + + + + + + + + + + + <xsl:value-of select="substring($titleChop,@ind2+1)"/> + + + + + <xsl:value-of select="$titleChop" /> + + + + + + + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="subfieldSelect"> + <!-- 1/04 removed $h, $b --> + <xsl:with-param name="codes">af</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <xsl:value-of select="$titleChop"/> + + + + + + + + + + + <xsl:value-of select="substring($titleChop,$nfi+1)"/> + + + + + <xsl:value-of select="$titleChop"/> + + + + + + + + + + + + ah + + + + + + + <xsl:value-of select="$titleChop" /> + + + + + + + + + + + <xsl:value-of select="substring($titleChop,@ind1+1)"/> + + + + + <xsl:value-of select="$titleChop" /> + + + + + + + + + + + + + creator + + + + + + + + + + creator + + + + + + + + + + creator + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + personal + + + + + + + + + + + yes + + + yes + + + text + cartographic + notated music + sound recording-nonmusical + sound recording-musical + still image + moving image + three dimensional object + software, multimedia + mixed material + + + + globe + + + remote sensing image + + + + + + map + + + atlas + + + + + + + + database + + + loose-leaf + + + series + + + newspaper + + + periodical + + + web site + + + + + + + + abstract or summary + + + bibliography + + + catalog + + + dictionary + + + encyclopedia + + + handbook + + + legal article + + + index + + + discography + + + legislation + + + theses + + + survey of literature + + + review + + + programmed text + + + filmography + + + directory + + + statistics + + + technical report + + + legal case and case notes + + + law report or digest + + + treaty + + + + + + conference publication + + + + + + + + numeric data + + + database + + + font + + + game + + + + + + patent + + + festschrift + + + + biography + + + + + essay + + + drama + + + comic strip + + + fiction + + + humor, satire + + + letter + + + novel + + + short story + + + speech + + + + + + + biography + + + conference publication + + + drama + + + essay + + + fiction + + + folktale + + + history + + + humor, satire + + + memoir + + + poetry + + + rehearsal + + + reporting + + + sound + + + speech + + + + + + + art original + + + kit + + + art reproduction + + + diorama + + + filmstrip + + + legal article + + + picture + + + graphic + + + technical drawing + + + motion picture + + + chart + + + flash card + + + microscope slide + + + model + + + realia + + + slide + + + transparency + + + videorecording + + + toy + + + + + + + + + + abvxyz + - + + + + + + + + + code + marccountry + + + + + + + + code + iso3166 + + + + + + + + text + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + :,;/ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + monographic + continuing + + + + + + + ab + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + reformatted digital + + + digitized microfilm + + + digitized other analog + + + + + + + + + + + + + + + +
braille
+ + +
print
+ + +
electronic
+ + +
microfiche
+ + +
microfilm
+ + + + +
+ + + + + +
+ + +
+ + + + + +
+ + +
+ + + + + +
+ + +
+ + + + + +
+ + +
+ + + + + +
+ + +
+ + + + + +
+ + +
+ +
+ + + + + access + + + preservation + + + replacement + + + + + +
chip cartridge
+ + +
computer optical disc cartridge
+ + +
magnetic disc
+ + +
magneto-optical disc
+ + +
optical disc
+ + +
remote
+ + +
tape cartridge
+ + +
tape cassette
+ + +
tape reel
+ + + +
celestial globe
+ + +
earth moon globe
+ + +
planetary or lunar globe
+ + +
terrestrial globe
+ + + +
kit
+ + + +
atlas
+ + +
diagram
+ + +
map
+ + +
model
+ + +
profile
+ + +
remote-sensing image
+ + +
section
+ + +
view
+ + + +
aperture card
+ + +
microfiche
+ + +
microfiche cassette
+ + +
microfilm cartridge
+ + +
microfilm cassette
+ + +
microfilm reel
+ + +
microopaque
+ + + +
film cartridge
+ + +
film cassette
+ + +
film reel
+ + + +
chart
+ + +
collage
+ + +
drawing
+ + +
flash card
+ + +
painting
+ + +
photomechanical print
+ + +
photonegative
+ + +
photoprint
+ + +
picture
+ + +
print
+ + +
technical drawing
+ + + +
notated music
+ + + +
filmslip
+ + +
filmstrip cartridge
+ + +
filmstrip roll
+ + +
other filmstrip type
+ + +
slide
+ + +
transparency
+ + +
remote-sensing image
+ + +
cylinder
+ + +
roll
+ + +
sound cartridge
+ + +
sound cassette
+ + +
sound disc
+ + +
sound-tape reel
+ + +
sound-track film
+ + +
wire recording
+ + + +
braille
+ + +
combination
+ + +
moon
+ + +
tactile, with no writing system
+ + + +
braille
+ + +
large print
+ + +
regular print
+ + +
text in looseleaf binder
+ + + +
videocartridge
+ + +
videocassette
+ + +
videodisc
+ + +
videoreel
+ + + + + + + + + + + abce + + + + + + + + + + + + + + ab + + + + + + + + agrt + + + + + + + ab + + + + + + + + + adolescent + + + adult + + + general + + + juvenile + + + preschool + + + specialized + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + defg + + + + + + + + + + + + marcgac + + + + + + iso3166 + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + ab + + + + + + + abx + + + + + + + ab + + + + + + + + + + + + + + + + + + + + + + + + + + + + ab + + + + + + + + + + av + + + + + + + <xsl:value-of select="$titleChop" /> + + + + + + + + + + + <xsl:value-of select="substring($titleChop,@ind2+1)"/> + + + + + + <xsl:value-of select="$titleChop" /> + + + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="subfieldSelect"> + <xsl:with-param name="codes">av</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + abcx3 + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="specialSubfieldSelect"> + <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param> + <xsl:with-param name="axis">t</xsl:with-param> + <xsl:with-param name="afterCodes">g</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + aq + t + g + + + + + + + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="specialSubfieldSelect"> + <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param> + <xsl:with-param name="axis">t</xsl:with-param> + <xsl:with-param name="afterCodes">dg</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + + + + + + + + c + t + dgn + + + + + + + + + + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="specialSubfieldSelect"> + <xsl:with-param name="anyCodes">tfklsv</xsl:with-param> + <xsl:with-param name="axis">t</xsl:with-param> + <xsl:with-param name="afterCodes">g</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + aqdc + t + gn + + + + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="subfieldSelect"> + <xsl:with-param name="codes">adfgklmorsv</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + + + + + + + + + + + <xsl:value-of select="$titleChop" /> + + + + + + + + + + + <xsl:value-of select="substring($titleChop,@ind1+1)"/> + + + + + <xsl:value-of select="$titleChop" /> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="specialSubfieldSelect"> + <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param> + <xsl:with-param name="axis">t</xsl:with-param> + <xsl:with-param name="afterCodes">g</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + aq + t + g + + + + + + + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="specialSubfieldSelect"> + <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param> + <xsl:with-param name="axis">t</xsl:with-param> + <xsl:with-param name="afterCodes">dg</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + + + + + + + + c + t + dgn + + + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="specialSubfieldSelect"> + <xsl:with-param name="anyCodes">tfklsv</xsl:with-param> + <xsl:with-param name="axis">t</xsl:with-param> + <xsl:with-param name="afterCodes">g</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + aqdc + t + gn + + + + + + + + + + + + + + adfgklmorsv + + + + + + + <xsl:value-of select="$titleChop" /> + + + + + + + + + + + <xsl:value-of select="substring($titleChop,@ind2+1)"/> + + + + + <xsl:value-of select="$titleChop" /> + + + + + + + + + + + + + + + + + + isbn + + + + + + + + + + isrc + + + + + + + + + + ismn + + + + + + + + + + sici + + + + ab + + + + + + issn + + + + + + + + lccn + + + + + + + + + + issue number + matrix number + music plate + music publisher + videorecording identifier + + + + + + + ba + ab + + + + + + + + + + ab + + + + + + + + doi + hdl + uri + + + + + + + + + + + + + + + + + y3z + + + + + + + + + + + + + + + + + + + + + y3 + + + + + + + z + + + + + + + + + + + + + + + + + + abje + + + + + + + + abcd35 + + + + + + + abcde35 + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + n + n + fgkdlmor + + + + + p + p + fgkdlmor + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + g + g + pst + + + + + p + p + fgkdlmor + + + + + + + + + + + + + + + + + + + + + + + + + +
+ +
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + cdn + + + + + + + + + + aq + + + + :,;/ + + + + + + + + + + acdeq + + + + + + constituent + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:value-of select="."></xsl:value-of> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:value-of select="."></xsl:value-of> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:value-of select="."></xsl:value-of> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:value-of select="."></xsl:value-of> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + + + + + + code + marcgac + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + lcsh + lcshac + mesh + + nal + csh + rvm + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + aq + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + cdnp + + + + + + + + + + + + + + + + abcdeqnp + + + + + + + + + + + + + + + + + + + + + adfhklor + + + + + + + <xsl:value-of select="$titleChop" /> + + + + + + + + + + + <xsl:value-of select="substring($titleChop,@ind1+1)"/> + + + + + + <xsl:value-of select="$titleChop" /> + + + + + + + + + + + + + + + + + abcd + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + bc + + + + + + + + + + + + + + + + + + + + + + + + + + + yes + + + + + + + + + + + + + + + + + + + + + + + + + + + Arabic + Latin + Chinese, Japanese, Korean + Cyrillic + Hebrew + Greek + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + summary or subtitle + sung or spoken text + libretto + table of contents + accompanying material + translation + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + summary or subtitle + sung or spoken text + libretto + table of contents + accompanying material + translation + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + .:,;/ + + + + + + + + + + + + + + + + + + + + + + + + + + + + +$XXXX$ where name = $$mods32$$; + +update config.xml_transform set xslt = $XXXX$ + + + + !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~ + + + + ¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ + + + + !'()*-.0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwxyz~ + + + 0123456789ABCDEF + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + BK + SE + + + BK + MM + CF + MP + VM + MU + + + + + + + + + b + afgk + + + + + abfgk + + + + + + + + + + + ,;/ + + + + + + + + + + <xsl:value-of select="substring($titleChop,@ind2+1)"/> + + + + + <xsl:value-of select="$titleChop"/> + + + + + + + + + b + b + afgk + + + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="subfieldSelect"> + <xsl:with-param name="codes">a</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="subfieldSelect"> + <!-- 1/04 removed $h, b --> + <xsl:with-param name="codes">a</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="subfieldSelect"> + <!-- 1/04 removed $h, $b --> + <xsl:with-param name="codes">af</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + <xsl:call-template name="uri"/> + + <xsl:variable name="str"> + <xsl:for-each select="marc:subfield"> + <xsl:if + test="(contains('adfklmors',@code) and (not(../marc:subfield[@code='n' or @code='p']) or (following-sibling::marc:subfield[@code='n' or @code='p'])))"> + <xsl:value-of select="text()"/> + <xsl:text> </xsl:text> + </xsl:if> + </xsl:for-each> + </xsl:variable> + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:value-of select="substring($str,1,string-length($str)-1)"/> + </xsl:with-param> + </xsl:call-template> + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="subfieldSelect"> + <xsl:with-param name="codes">ah</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + + + + creator + + + + + + + + + + + + creator + + + + + + + + + + + + creator + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + personal + + + + + + + + + + + yes + + + yes + + + text + cartographic + notated music + sound recording-nonmusical + sound recording-musical + still image + moving image + three dimensional object + software, multimedia + mixed material + + + + globe + + + remote-sensing image + + + + + + map + + + atlas + + + + + + + + database + + + loose-leaf + + + series + + + newspaper + + + periodical + + + web site + + + + + + + + abstract or summary + + + bibliography + + + catalog + + + dictionary + + + encyclopedia + + + handbook + + + legal article + + + index + + + discography + + + legislation + + + theses + + + survey of literature + + + review + + + programmed text + + + filmography + + + directory + + + statistics + + + technical report + + + legal case and case notes + + + law report or digest + + + treaty + + + + + + conference publication + + + + + + + + numeric data + + + database + + + font + + + game + + + + + + patent + + + offprint + + + festschrift + + + + biography + + + + + essay + + + drama + + + comic strip + + + fiction + + + humor, satire + + + letter + + + novel + + + short story + + + speech + + + + + + + biography + + + conference publication + + + drama + + + essay + + + fiction + + + folktale + + + history + + + humor, satire + + + memoir + + + poetry + + + rehearsal + + + reporting + + + sound + + + speech + + + + + + + art original + + + kit + + + art reproduction + + + diorama + + + filmstrip + + + legal article + + + picture + + + graphic + + + technical drawing + + + motion picture + + + chart + + + flash card + + + microscope slide + + + model + + + realia + + + slide + + + transparency + + + videorecording + + + toy + + + + + + + + + + + + + abcdef + - + + + + + + + + + + abvxyz + - + + + + + + + + + code + marccountry + + + + + + + + code + iso3166 + + + + + + + + text + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + :,;/ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + monographic + continuing + + + + + + + ab + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + reformatted digital + + + digitized microfilm + + + digitized other analog + + + + + + + + + + + + + + + +
braille
+ + +
print
+ + +
electronic
+ + +
microfiche
+ + +
microfilm
+ + + + +
+ + + + + +
+ + +
+ + + + + +
+ + +
+ + + + + +
+ + +
+ + + + + +
+ + +
+ + + + + +
+ + +
+ + + + + +
+ + +
+ + +
+ + + + access + + + preservation + + + replacement + + + + + +
chip cartridge
+ + +
computer optical disc cartridge
+ + +
magnetic disc
+ + +
magneto-optical disc
+ + +
optical disc
+ + +
remote
+ + +
tape cartridge
+ + +
tape cassette
+ + +
tape reel
+ + + +
celestial globe
+ + +
earth moon globe
+ + +
planetary or lunar globe
+ + +
terrestrial globe
+ + + +
kit
+ + + +
atlas
+ + +
diagram
+ + +
map
+ + +
model
+ + +
profile
+ + +
remote-sensing image
+ + +
section
+ + +
view
+ + + +
aperture card
+ + +
microfiche
+ + +
microfiche cassette
+ + +
microfilm cartridge
+ + +
microfilm cassette
+ + +
microfilm reel
+ + +
microopaque
+ + + +
film cartridge
+ + +
film cassette
+ + +
film reel
+ + + +
chart
+ + +
collage
+ + +
drawing
+ + +
flash card
+ + +
painting
+ + +
photomechanical print
+ + +
photonegative
+ + +
photoprint
+ + +
picture
+ + +
print
+ + +
technical drawing
+ + + +
notated music
+ + + +
filmslip
+ + +
filmstrip cartridge
+ + +
filmstrip roll
+ + +
other filmstrip type
+ + +
slide
+ + +
transparency
+ + +
remote-sensing image
+ + +
cylinder
+ + +
roll
+ + +
sound cartridge
+ + +
sound cassette
+ + +
sound disc
+ + +
sound-tape reel
+ + +
sound-track film
+ + +
wire recording
+ + + +
braille
+ + +
combination
+ + +
moon
+ + +
tactile, with no writing system
+ + + +
braille
+ + +
large print
+ + +
regular print
+ + +
text in looseleaf binder
+ + + +
videocartridge
+ + +
videocassette
+ + +
videodisc
+ + +
videoreel
+ + + + + + + + + + + abce + + + + + + + + + + + + + + ab + + + + + + + + agrt + + + + + + + ab + + + + + + + + + adolescent + + + adult + + + general + + + juvenile + + + preschool + + + specialized + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + defg + + + + + + + + + + + + marcgac + + + + + + iso3166 + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + ab + + + + + + + abx + + + + + + + ab + + + + + + + + + + + + + + + + + + + + + + + + + + + + ab + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="subfieldSelect"> + <xsl:with-param name="codes">av</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="subfieldSelect"> + <xsl:with-param name="codes">av</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + abcx3 + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="specialSubfieldSelect"> + <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param> + <xsl:with-param name="axis">t</xsl:with-param> + <xsl:with-param name="afterCodes">g</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + aq + t + g + + + + + + + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="specialSubfieldSelect"> + <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param> + <xsl:with-param name="axis">t</xsl:with-param> + <xsl:with-param name="afterCodes">dg</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + + + + + + + + c + t + dgn + + + + + + + + + + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="specialSubfieldSelect"> + <xsl:with-param name="anyCodes">tfklsv</xsl:with-param> + <xsl:with-param name="axis">t</xsl:with-param> + <xsl:with-param name="afterCodes">g</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + aqdc + t + gn + + + + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="subfieldSelect"> + <xsl:with-param name="codes">adfgklmorsv</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:value-of select="marc:subfield[@code='a']"/> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="specialSubfieldSelect"> + <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param> + <xsl:with-param name="axis">t</xsl:with-param> + <xsl:with-param name="afterCodes">g</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + aq + t + g + + + + + + + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="specialSubfieldSelect"> + <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param> + <xsl:with-param name="axis">t</xsl:with-param> + <xsl:with-param name="afterCodes">dg</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + + + + + + + + c + t + dgn + + + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="specialSubfieldSelect"> + <xsl:with-param name="anyCodes">tfklsv</xsl:with-param> + <xsl:with-param name="axis">t</xsl:with-param> + <xsl:with-param name="afterCodes">g</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + aqdc + t + gn + + + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="subfieldSelect"> + <xsl:with-param name="codes">adfgklmorsv</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + + + + + + + isbn + + + + + + + + + + isrc + + + + + + + + + + ismn + + + + + + + + + + sici + + + + ab + + + + + + + issn + + + + + + + + issn-l + + + + + + + + + + + + lccn + + + + + + + + + + issue number + matrix number + music plate + music publisher + videorecording identifier + + + + + + + + ba + ab + + + + + + + + + + + ab + + + + + + + + doi + hdl + uri + + + + + + + + + + + + + + + + + y3z + + + + + + + + + + + + + + + + + + + + + + + + + y3 + + + + + + + z + + + + + + + + + + + + + + + + y3 + + + + + + + z + + + + + + + + + + + + + + + + + + abe + + + + + + + + + u + + + + + + + + hijklmt + + + + + + + + + + abcd35 + + + + + + + abcde35 + + + + + + + + + + aacr2 + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + n + n + fgkdlmor + + + + + p + p + fgkdlmor + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + g + g + pst + + + + + p + p + fgkdlmor + + + + + + + + + + + + + + + + + + + + + + + + + +
+ + +
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + cdn + + + + + + + + + + aq + + + + :,;/ + + + + + + + + + + acdeq + + + + + + constituent + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:value-of select="."/> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:value-of select="."/> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:value-of select="."/> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:value-of select="."/> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + + + + + + code + marcgac + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + lcsh + lcshac + mesh + + nal + csh + rvm + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + aq + + + + + + + + + + + + + + + + + + + + + + + + + + + + + cdnp + + + + + + + + + + + + + + + abcdeqnp + + + + + + + + + + + + + + + + + + + <xsl:call-template name="chopPunctuation"> + <xsl:with-param name="chopString"> + <xsl:call-template name="subfieldSelect"> + <xsl:with-param name="codes">adfhklor</xsl:with-param> + </xsl:call-template> + </xsl:with-param> + </xsl:call-template> + + + + + + + + + + + + + + + + + + + + + + abcd + + + + + + + + + + + + + + + + abcd + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + bc + + + + + + + + + + + + + + + + + + + + + + + + + + + yes + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + Arabic + Latin + Chinese, Japanese, Korean + Cyrillic + Hebrew + Greek + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + summary or subtitle + sung or spoken text + libretto + table of contents + accompanying material + translation + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + summary or subtitle + sung or spoken text + libretto + table of contents + accompanying material + translation + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + abcdefghijklmnopqrstuvwxyz + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + .:,;/ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + .:,;/] + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + Warning: string contains a character + that is out of range! Substituting "?". + 63 + + + + + + + + + + + + + + + + +$XXXX$ where name = $$mods33$$; + + + +SELECT evergreen.upgrade_deps_block_check('1103', :eg_version); + +INSERT INTO config.metabib_field (id, field_class, name, label, browse_field) + VALUES (45, 'keyword', 'blob', 'All searchable fields', FALSE); + +INSERT INTO config.metabib_field (id, field_class, name, format, weight, + label, xpath, display_field, search_field, browse_field, facet_field) +VALUES ( + 53, 'title', 'maintitle', 'marcxml', 10, + oils_i18n_gettext(53, 'Main Title', 'cmf', 'label'), + $$//*[@tag='245']/*[@code='a']$$, + FALSE, TRUE, FALSE, FALSE +); + +INSERT INTO config.metabib_field_virtual_map (real, virtual) + SELECT id, + 45 + FROM config.metabib_field + WHERE search_field + AND id NOT IN (15, 45, 38, 40) -- keyword|keyword, self, edition, publisher + AND id NOT IN (SELECT real FROM config.metabib_field_virtual_map); + +UPDATE config.metabib_field SET xpath=$$//mods32:mods/mods32:subject[not(descendant::mods32:geographicCode)]$$ WHERE id = 16; + +UPDATE config.metabib_field_virtual_map SET weight = -1 WHERE real = 39; +UPDATE config.metabib_field_virtual_map SET weight = 0 WHERE real = 41; +UPDATE config.metabib_field_virtual_map SET weight = 0 WHERE real = 42; +UPDATE config.metabib_field_virtual_map SET weight = 0 WHERE real = 46; +UPDATE config.metabib_field_virtual_map SET weight = 0 WHERE real = 47; +UPDATE config.metabib_field_virtual_map SET weight = 0 WHERE real = 48; +UPDATE config.metabib_field_virtual_map SET weight = 0 WHERE real = 50; +UPDATE config.metabib_field_virtual_map SET weight = 8 WHERE real = 6; +UPDATE config.metabib_field_virtual_map SET weight = 8 WHERE real = 8; +UPDATE config.metabib_field_virtual_map SET weight = 8 WHERE real = 16; +UPDATE config.metabib_field_virtual_map SET weight = 12 WHERE real = 53; + +-- Stemming for genre +INSERT INTO config.metabib_field_ts_map (metabib_field, ts_config) + SELECT 33, 'english_nostop' WHERE NOT EXISTS ( + SELECT 1 FROM config.metabib_field_ts_map WHERE metabib_field = 33 AND ts_config = 'english_nostop' + ) +; + + +SELECT evergreen.upgrade_deps_block_check('1104', :eg_version); + +INSERT INTO config.org_unit_setting_type +( name, grp, label, description, datatype ) +VALUES + ('circ.clear_hold_on_checkout', + 'circ', + oils_i18n_gettext('circ.clear_hold_on_checkout', + 'Clear hold when other patron checks out item', + 'coust', 'label'), + oils_i18n_gettext('circ.clear_hold_on_checkout', + 'Default to cancel the hold when patron A checks out item on hold for patron B.', + 'coust', 'description'), + 'bool'); + + + +SELECT evergreen.upgrade_deps_block_check('1105', :eg_version); + +INSERT into config.org_unit_setting_type (name, label, grp, description, datatype) +values ('webstaff.circ.itemsout_notice_count_excludes_courtesies','Exclude Courtesy Notices from Patrons Itemsout Notices Count', + 'circ', 'Exclude Courtesy Notices from Patron Itemsout Notices Count', 'bool'); + + +SELECT evergreen.upgrade_deps_block_check('1106', :eg_version); + +ALTER TABLE money.billing + ADD COLUMN create_date TIMESTAMP WITH TIME ZONE, + ADD COLUMN period_start TIMESTAMP WITH TIME ZONE, + ADD COLUMN period_end TIMESTAMP WITH TIME ZONE; + +--Disable materialized update trigger +--It takes forever, and doesn't matter yet for what we are doing, as the +--view definition is unchanged (still using billing_ts) +ALTER TABLE money.billing DISABLE TRIGGER mat_summary_upd_tgr; + +--Limit to btype=1 / 'Overdue Materials' +--Update day-granular fines first (i.e. 24 hour, 1 day, 2 day, etc., all of which are multiples of 86400 seconds), and simply remove the time portion of timestamp +UPDATE money.billing mb + SET period_start = date_trunc('day', billing_ts), period_end = date_trunc('day', billing_ts) + (ac.fine_interval - '1 second') + FROM action.circulation ac +WHERE mb.xact = ac.id + AND mb.btype = 1 + AND (EXTRACT(EPOCH FROM ac.fine_interval))::integer % 86400 = 0; + +--Update fines for non-day intervals +UPDATE money.billing mb + SET period_start = billing_ts - ac.fine_interval + interval '1 sec', period_end = billing_ts + FROM action.circulation ac +WHERE mb.xact = ac.id + AND mb.btype = 1 + AND (EXTRACT(EPOCH FROM ac.fine_interval))::integer % 86400 > 0; + +SET CONSTRAINTS ALL IMMEDIATE; +UPDATE money.billing SET create_date = COALESCE(period_start, billing_ts); + +--Re-enable update trigger +ALTER TABLE money.billing ENABLE TRIGGER mat_summary_upd_tgr; + +ALTER TABLE money.billing ALTER COLUMN create_date SET DEFAULT NOW(); +ALTER TABLE money.billing ALTER COLUMN create_date SET NOT NULL; + +CREATE INDEX m_b_create_date_idx ON money.billing (create_date); +CREATE INDEX m_b_period_start_idx ON money.billing (period_start); +CREATE INDEX m_b_period_end_idx ON money.billing (period_end); + +CREATE OR REPLACE FUNCTION money.maintain_billing_ts () RETURNS TRIGGER AS $$ +BEGIN + NEW.billing_ts := COALESCE(NEW.period_end, NEW.create_date); + RETURN NEW; +END; +$$ LANGUAGE PLPGSQL; +CREATE TRIGGER maintain_billing_ts_tgr BEFORE INSERT OR UPDATE ON money.billing FOR EACH ROW EXECUTE PROCEDURE money.maintain_billing_ts(); + + +SELECT evergreen.upgrade_deps_block_check('1108', :eg_version); + +CREATE OR REPLACE FUNCTION asset.merge_record_assets( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$ +DECLARE + moved_objects INT := 0; + source_cn asset.call_number%ROWTYPE; + target_cn asset.call_number%ROWTYPE; + metarec metabib.metarecord%ROWTYPE; + hold action.hold_request%ROWTYPE; + ser_rec serial.record_entry%ROWTYPE; + ser_sub serial.subscription%ROWTYPE; + acq_lineitem acq.lineitem%ROWTYPE; + acq_request acq.user_request%ROWTYPE; + booking booking.resource_type%ROWTYPE; + source_part biblio.monograph_part%ROWTYPE; + target_part biblio.monograph_part%ROWTYPE; + multi_home biblio.peer_bib_copy_map%ROWTYPE; + uri_count INT := 0; + counter INT := 0; + uri_datafield TEXT; + uri_text TEXT := ''; +BEGIN + + -- move any 856 entries on records that have at least one MARC-mapped URI entry + SELECT INTO uri_count COUNT(*) + FROM asset.uri_call_number_map m + JOIN asset.call_number cn ON (m.call_number = cn.id) + WHERE cn.record = source_record; + + IF uri_count > 0 THEN + + -- This returns more nodes than you might expect: + -- 7 instead of 1 for an 856 with $u $y $9 + SELECT COUNT(*) INTO counter + FROM oils_xpath_table( + 'id', + 'marc', + 'biblio.record_entry', + '//*[@tag="856"]', + 'id=' || source_record + ) as t(i int,c text); + + FOR i IN 1 .. counter LOOP + SELECT '' || + STRING_AGG( + '' || + regexp_replace( + regexp_replace( + regexp_replace(data,'&','&','g'), + '>', '>', 'g' + ), + '<', '<', 'g' + ) || '', '' + ) || '' INTO uri_datafield + FROM oils_xpath_table( + 'id', + 'marc', + 'biblio.record_entry', + '//*[@tag="856"][position()=' || i || ']/@ind1|' || + '//*[@tag="856"][position()=' || i || ']/@ind2|' || + '//*[@tag="856"][position()=' || i || ']/*/@code|' || + '//*[@tag="856"][position()=' || i || ']/*[@code]', + 'id=' || source_record + ) as t(id int,ind1 text, ind2 text,subfield text,data text); + + -- As most of the results will be NULL, protect against NULLifying + -- the valid content that we do generate + uri_text := uri_text || COALESCE(uri_datafield, ''); + END LOOP; + + IF uri_text <> '' THEN + UPDATE biblio.record_entry + SET marc = regexp_replace(marc,'(]*record>)', uri_text || E'\\1') + WHERE id = target_record; + END IF; + + END IF; + + -- Find and move metarecords to the target record + SELECT INTO metarec * + FROM metabib.metarecord + WHERE master_record = source_record; + + IF FOUND THEN + UPDATE metabib.metarecord + SET master_record = target_record, + mods = NULL + WHERE id = metarec.id; + + moved_objects := moved_objects + 1; + END IF; + + -- Find call numbers attached to the source ... + FOR source_cn IN SELECT * FROM asset.call_number WHERE record = source_record LOOP + + SELECT INTO target_cn * + FROM asset.call_number + WHERE label = source_cn.label + AND prefix = source_cn.prefix + AND suffix = source_cn.suffix + AND owning_lib = source_cn.owning_lib + AND record = target_record + AND NOT deleted; + + -- ... and if there's a conflicting one on the target ... + IF FOUND THEN + + -- ... move the copies to that, and ... + UPDATE asset.copy + SET call_number = target_cn.id + WHERE call_number = source_cn.id; + + -- ... move V holds to the move-target call number + FOR hold IN SELECT * FROM action.hold_request WHERE target = source_cn.id AND hold_type = 'V' LOOP + + UPDATE action.hold_request + SET target = target_cn.id + WHERE id = hold.id; + + moved_objects := moved_objects + 1; + END LOOP; + + UPDATE asset.call_number SET deleted = TRUE WHERE id = source_cn.id; + + -- ... if not ... + ELSE + -- ... just move the call number to the target record + UPDATE asset.call_number + SET record = target_record + WHERE id = source_cn.id; + END IF; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find T holds targeting the source record ... + FOR hold IN SELECT * FROM action.hold_request WHERE target = source_record AND hold_type = 'T' LOOP + + -- ... and move them to the target record + UPDATE action.hold_request + SET target = target_record + WHERE id = hold.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find serial records targeting the source record ... + FOR ser_rec IN SELECT * FROM serial.record_entry WHERE record = source_record LOOP + -- ... and move them to the target record + UPDATE serial.record_entry + SET record = target_record + WHERE id = ser_rec.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find serial subscriptions targeting the source record ... + FOR ser_sub IN SELECT * FROM serial.subscription WHERE record_entry = source_record LOOP + -- ... and move them to the target record + UPDATE serial.subscription + SET record_entry = target_record + WHERE id = ser_sub.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find booking resource types targeting the source record ... + FOR booking IN SELECT * FROM booking.resource_type WHERE record = source_record LOOP + -- ... and move them to the target record + UPDATE booking.resource_type + SET record = target_record + WHERE id = booking.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find acq lineitems targeting the source record ... + FOR acq_lineitem IN SELECT * FROM acq.lineitem WHERE eg_bib_id = source_record LOOP + -- ... and move them to the target record + UPDATE acq.lineitem + SET eg_bib_id = target_record + WHERE id = acq_lineitem.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find acq user purchase requests targeting the source record ... + FOR acq_request IN SELECT * FROM acq.user_request WHERE eg_bib = source_record LOOP + -- ... and move them to the target record + UPDATE acq.user_request + SET eg_bib = target_record + WHERE id = acq_request.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find parts attached to the source ... + FOR source_part IN SELECT * FROM biblio.monograph_part WHERE record = source_record LOOP + + SELECT INTO target_part * + FROM biblio.monograph_part + WHERE label = source_part.label + AND record = target_record; + + -- ... and if there's a conflicting one on the target ... + IF FOUND THEN + + -- ... move the copy-part maps to that, and ... + UPDATE asset.copy_part_map + SET part = target_part.id + WHERE part = source_part.id; + + -- ... move P holds to the move-target part + FOR hold IN SELECT * FROM action.hold_request WHERE target = source_part.id AND hold_type = 'P' LOOP + + UPDATE action.hold_request + SET target = target_part.id + WHERE id = hold.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- ... if not ... + ELSE + -- ... just move the part to the target record + UPDATE biblio.monograph_part + SET record = target_record + WHERE id = source_part.id; + END IF; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find multi_home items attached to the source ... + FOR multi_home IN SELECT * FROM biblio.peer_bib_copy_map WHERE peer_record = source_record LOOP + -- ... and move them to the target record + UPDATE biblio.peer_bib_copy_map + SET peer_record = target_record + WHERE id = multi_home.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- And delete mappings where the item's home bib was merged with the peer bib + DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = ( + SELECT (SELECT record FROM asset.call_number WHERE id = call_number) + FROM asset.copy WHERE id = target_copy + ); + + -- Apply merge tracking + UPDATE biblio.record_entry + SET merge_date = NOW() WHERE id = target_record; + + UPDATE biblio.record_entry + SET merge_date = NOW(), merged_to = target_record + WHERE id = source_record; + + -- replace book bag entries of source_record with target_record + UPDATE container.biblio_record_entry_bucket_item + SET target_biblio_record_entry = target_record + WHERE bucket IN (SELECT id FROM container.biblio_record_entry_bucket WHERE btype = 'bookbag') + AND target_biblio_record_entry = source_record; + + -- Finally, "delete" the source record + DELETE FROM biblio.record_entry WHERE id = source_record; + + -- That's all, folks! + RETURN moved_objects; +END; +$func$ LANGUAGE plpgsql; + + +SELECT evergreen.upgrade_deps_block_check('1109', :eg_version); + +INSERT into config.org_unit_setting_type (name, label, grp, description, datatype) +values ('circ.staff_placed_holds_fallback_to_ws_ou','Workstation OU fallback for staff-placed holds', + 'circ', 'For staff-placed holds, in the absence of a patron preferred pickup location, fall back to using the staff workstation OU (rather than patron home OU)', 'bool'); + + +COMMIT; + +\qecho +\qecho Reingesting all records. This may take a while. +\qecho This command can be stopped (control-c) and rerun later if needed: +\qecho +\qecho DO $FUNC$ +\qecho DECLARE +\qecho same_marc BOOL; +\qecho BEGIN +\qecho SELECT INTO same_marc enabled FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc'; +\qecho UPDATE config.internal_flag SET enabled = true WHERE name = 'ingest.reingest.force_on_same_marc'; +\qecho UPDATE biblio.record_entry SET id=id WHERE not deleted AND id > 0; +\qecho UPDATE config.internal_flag SET enabled = same_marc WHERE name = 'ingest.reingest.force_on_same_marc'; +\qecho END; +\qecho $FUNC$; + +DO $FUNC$ +DECLARE + same_marc BOOL; +BEGIN + SELECT INTO same_marc enabled FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc'; + UPDATE config.internal_flag SET enabled = true WHERE name = 'ingest.reingest.force_on_same_marc'; + UPDATE biblio.record_entry SET id=id WHERE not deleted AND id > 0; + UPDATE config.internal_flag SET enabled = same_marc WHERE name = 'ingest.reingest.force_on_same_marc'; +END; +$FUNC$; -- 2.43.2