From 2faa568ac6dd2ae0c21c75515fc2c48f79704508 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Fri, 8 Mar 2013 14:26:17 -0500 Subject: [PATCH] QP search modifier '#deleted' These tweaks involve an internal flag that's off by default. If you want '#deleted', you'll have to turn it on explicitly at your site (and reingest existing records) to get the functionality. If you don't need '#deleted', you may prefer to leave the setting off so that your system will be purged (as before) of useless (to you) metarecord mappings taking up room in the database for deleted bibs. [LFW] upgrade script; commit message; release note Signed-off-by: Mike Rylander Signed-off-by: Lebbeous Fogle-Weekley Signed-off-by: Mike Rylander --- .../Application/Storage/Driver/Pg/QueryParser.pm | 27 ++- Open-ILS/src/sql/Pg/002.schema.config.sql | 1 + Open-ILS/src/sql/Pg/030.schema.metabib.sql | 12 +- Open-ILS/src/sql/Pg/800.fkeys.sql | 7 +- .../sql/Pg/upgrade/XXXX.schema.search-deleted.sql | 187 +++++++++++++++++++++ docs/RELEASE_NOTES_NEXT/search-deleted.txt | 11 ++ 6 files changed, 236 insertions(+), 9 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.search-deleted.sql create mode 100644 docs/RELEASE_NOTES_NEXT/search-deleted.txt diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm index fb49bbef21..3fa1330510 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm @@ -660,6 +660,7 @@ __PACKAGE__->add_search_filter( 'superpage_size' ); __PACKAGE__->add_search_filter( 'estimation_strategy' ); __PACKAGE__->add_search_modifier( 'available' ); __PACKAGE__->add_search_modifier( 'staff' ); +__PACKAGE__->add_search_modifier( 'deleted' ); __PACKAGE__->add_search_modifier( 'lucky' ); # Start from container data (bre, acn, acp): container(bre,bookbag,123,deadb33fdeadb33fdeadb33fdeadb33f) @@ -867,11 +868,17 @@ sub toSQL { # Limit stuff my $limit_where = <<" SQL"; -- Filter records based on visibility + AND NOT bre.deleted AND ( cbs.transcendant IS TRUE OR SQL - if ($self->find_modifier('staff')) { + + if ($self->find_modifier('deleted')) { + $limit_where = <<" SQL"; + AND bre.deleted + SQL + } elsif ($self->find_modifier('staff')) { $limit_where .= <<" SQL"; EXISTS( SELECT 1 FROM asset.call_number cn @@ -918,6 +925,17 @@ sub toSQL { LIMIT 1 ) ) + OR + EXISTS( + SELECT 1 FROM asset.call_number acn + JOIN asset.uri_call_number_map aucnm ON acn.id = aucnm.call_number + JOIN asset.uri uri ON aucnm.uri = uri.id + WHERE NOT acn.deleted AND uri.active AND acn.record = m.source AND acn.owning_lib IN ( + SELECT * FROM luri_org_list + ) + LIMIT 1 + ) + ) SQL } else { $limit_where .= <<" SQL"; @@ -935,9 +953,6 @@ sub toSQL { AND pr.peer_record = m.source LIMIT 1 ) - SQL - } - $limit_where .= <<" SQL"; OR EXISTS( SELECT 1 FROM asset.call_number acn @@ -949,8 +964,8 @@ sub toSQL { LIMIT 1 ) ) - SQL - + SQL + } # For single records we want the record id # For metarecords we want NULL or the only record ID. my $agg_record = 'm.source AS record'; diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index b087bfd39e..4f84418839 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -50,6 +50,7 @@ INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_browse_indexing'); INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_search_indexing'); INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_facet_indexing'); INSERT INTO config.internal_flag (name) VALUES ('serial.rematerialize_on_same_holding_code'); +INSERT INTO config.internal_flag (name) VALUES ('ingest.metarecord_mapping.preserve_on_delete'); CREATE TABLE config.global_flag ( label TEXT NOT NULL diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index 6d96938972..9676990fe1 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -1173,8 +1173,16 @@ DECLARE BEGIN IF NEW.deleted IS TRUE THEN -- If this bib is deleted - DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id; -- Rid ourselves of the search-estimate-killing linkage - DELETE FROM metabib.record_attr WHERE id = NEW.id; -- Kill the attrs hash, useless on deleted records + PERFORM * FROM config.internal_flag WHERE + name = 'ingest.metarecord_mapping.preserve_on_delete' AND enabled; + IF NOT FOUND THEN + -- One needs to keep these around to support searches + -- with the #deleted modifier, so one should turn on the named + -- internal flag for that functionality. + DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id; + DELETE FROM metabib.record_attr WHERE id = NEW.id; + END IF; + DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = NEW.id; -- Separate any multi-homed items DELETE FROM metabib.browse_entry_def_map WHERE source = NEW.id; -- Don't auto-suggest deleted bibs diff --git a/Open-ILS/src/sql/Pg/800.fkeys.sql b/Open-ILS/src/sql/Pg/800.fkeys.sql index fb7b07d9c7..5314b0ee05 100644 --- a/Open-ILS/src/sql/Pg/800.fkeys.sql +++ b/Open-ILS/src/sql/Pg/800.fkeys.sql @@ -19,7 +19,12 @@ BEGIN; -CREATE RULE protect_bib_rec_delete AS ON DELETE TO biblio.record_entry DO INSTEAD (UPDATE biblio.record_entry SET deleted = TRUE WHERE OLD.id = biblio.record_entry.id; DELETE FROM metabib.metarecord_source_map WHERE source = OLD.id); +CREATE RULE protect_bib_rec_delete AS + ON DELETE TO biblio.record_entry DO INSTEAD ( + UPDATE biblio.record_entry + SET deleted = TRUE + WHERE OLD.id = biblio.record_entry.id + ); ALTER TABLE actor.usr ADD CONSTRAINT actor_usr_mailing_address_fkey FOREIGN KEY (mailing_address) REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE actor.usr ADD CONSTRAINT actor_usr_billing_address_fkey FOREIGN KEY (billing_address) REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.search-deleted.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.search-deleted.sql new file mode 100644 index 0000000000..f14bfa1188 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.search-deleted.sql @@ -0,0 +1,187 @@ +BEGIN; + +-- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +INSERT INTO config.internal_flag (name) VALUES ('ingest.metarecord_mapping.preserve_on_delete'); -- defaults to false/off + +DROP RULE protect_bib_rec_delete ON biblio.record_entry; +CREATE RULE protect_bib_rec_delete AS + ON DELETE TO biblio.record_entry DO INSTEAD ( + UPDATE biblio.record_entry + SET deleted = TRUE + WHERE OLD.id = biblio.record_entry.id + ); + + +-- AFTER UPDATE OR INSERT trigger for biblio.record_entry +CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$ +DECLARE + transformed_xml TEXT; + prev_xfrm TEXT; + normalizer RECORD; + xfrm config.xml_transform%ROWTYPE; + attr_value TEXT; + new_attrs HSTORE := ''::HSTORE; + attr_def config.record_attr_definition%ROWTYPE; +BEGIN + + IF NEW.deleted IS TRUE THEN -- If this bib is deleted + PERFORM * FROM config.internal_flag WHERE + name = 'ingest.metarecord_mapping.preserve_on_delete' AND enabled; + IF NOT FOUND THEN + -- One needs to keep these around to support searches + -- with the #deleted modifier, so one should turn on the named + -- internal flag for that functionality. + DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id; + DELETE FROM metabib.record_attr WHERE id = NEW.id; + END IF; + + DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible + DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = NEW.id; -- Separate any multi-homed items + DELETE FROM metabib.browse_entry_def_map WHERE source = NEW.id; -- Don't auto-suggest deleted bibs + RETURN NEW; -- and we're done + END IF; + + IF TG_OP = 'UPDATE' THEN -- re-ingest? + PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled; + + IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change + RETURN NEW; + END IF; + END IF; + + -- Record authority linking + PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking' AND enabled; + IF NOT FOUND THEN + PERFORM biblio.map_authority_linking( NEW.id, NEW.marc ); + END IF; + + -- Flatten and insert the mfr data + PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled; + IF NOT FOUND THEN + PERFORM metabib.reingest_metabib_full_rec(NEW.id); + + -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields + PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled; + IF NOT FOUND THEN + FOR attr_def IN SELECT * FROM config.record_attr_definition ORDER BY format LOOP + + IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection + SELECT ARRAY_TO_STRING(ARRAY_ACCUM(value), COALESCE(attr_def.joiner,' ')) INTO attr_value + FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x + WHERE record = NEW.id + AND tag LIKE attr_def.tag + AND CASE + WHEN attr_def.sf_list IS NOT NULL + THEN POSITION(subfield IN attr_def.sf_list) > 0 + ELSE TRUE + END + GROUP BY tag + ORDER BY tag + LIMIT 1; + + ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field + attr_value := biblio.marc21_extract_fixed_field(NEW.id, attr_def.fixed_field); + + ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression + + SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format; + + -- See if we can skip the XSLT ... it's expensive + IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN + -- Can't skip the transform + IF xfrm.xslt <> '---' THEN + transformed_xml := oils_xslt_process(NEW.marc,xfrm.xslt); + ELSE + transformed_xml := NEW.marc; + 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; + + attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]); + + ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map + SELECT m.value INTO attr_value + FROM biblio.marc21_physical_characteristics(NEW.id) v + JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value) + WHERE v.subfield = attr_def.phys_char_sf + LIMIT 1; -- Just in case ... + + END IF; + + -- apply index normalizers to attr_value + 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( attr_value ), 'NULL' ) || + CASE + WHEN normalizer.param_count > 0 + THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'') + ELSE '' + END || + ')' INTO attr_value; + + END LOOP; + + -- Add the new value to the hstore + new_attrs := new_attrs || hstore( attr_def.name, attr_value ); + + END LOOP; + + IF TG_OP = 'INSERT' OR OLD.deleted THEN -- initial insert OR revivication + INSERT INTO metabib.record_attr (id, attrs) VALUES (NEW.id, new_attrs); + ELSE + UPDATE metabib.record_attr SET attrs = new_attrs WHERE id = NEW.id; + END IF; + + END IF; + END IF; + + -- Gather and insert the field entry data + PERFORM metabib.reingest_metabib_field_entries(NEW.id); + + -- Located URI magic + IF TG_OP = 'INSERT' THEN + PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled; + IF NOT FOUND THEN + PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor ); + END IF; + ELSE + PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled; + IF NOT FOUND THEN + PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor ); + END IF; + END IF; + + -- (re)map metarecord-bib linking + IF TG_OP = 'INSERT' THEN -- if not deleted and performing an insert, check for the flag + PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled; + IF NOT FOUND THEN + PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint ); + END IF; + ELSE -- we're doing an update, and we're not deleted, remap + PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_update' AND enabled; + IF NOT FOUND THEN + PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint ); + END IF; + END IF; + + RETURN NEW; +END; +$func$ LANGUAGE PLPGSQL; + + +COMMIT; diff --git a/docs/RELEASE_NOTES_NEXT/search-deleted.txt b/docs/RELEASE_NOTES_NEXT/search-deleted.txt new file mode 100644 index 0000000000..5e1d8a0735 --- /dev/null +++ b/docs/RELEASE_NOTES_NEXT/search-deleted.txt @@ -0,0 +1,11 @@ +Searching for deleted records +============================= + +Evergreen now supports searching for deleted records via the '#deleted' +QP modifier. + +In order to support this, sites must enable the +'ingest.metarecord_mapping.preserve_on_delete' internal flag. It is off by +default since the ability to search for deleted records requires keeping +metarecord mappings around when bibs are deleted, which may not be desirable +for the typical site. -- 2.11.0