From 22ad7ca55b4a3a5b1bc94a55c6eef4fb9647bbbc Mon Sep 17 00:00:00 2001 From: Lebbeous Fogle-Weekley Date: Wed, 17 Apr 2013 18:16:09 -0400 Subject: [PATCH] QP: Repair edit_date, create_date, and bib_source filters And also make records undeletable, where reingestion at undelete time formerly balked in this manner: > ERROR: duplicate key value violates unique constraint > "record_attr_pkey" DETAIL: Key (id)=(1) already exists. CONTEXT: SQL > statement "INSERT INTO metabib.record_attr (id, attrs) VALUES (NEW.id, > new_attrs)" PL/pgSQL function "indexing_ingest_or_delete" line 129 at > SQL statement Signed-off-by: Lebbeous Fogle-Weekley Signed-off-by: Mike Rylander --- .../Storage/Driver/Pg/QueryParser.pm | 21 ++- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 1 + .../XXXX.schema.deleted-search-fts.sql | 171 ++++++++++++++++++ 3 files changed, 189 insertions(+), 4 deletions(-) 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 a1560ce5b7..c202dac4b9 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 @@ -741,9 +741,15 @@ sub toSQL { $rel = "1.0/($rel)::NUMERIC"; my $mra_join = 'INNER JOIN metabib.record_attr mrd ON m.source = mrd.id'; - my $bre_join = $self->find_modifier('deleted') ? - 'INNER JOIN biblio.record_entry bre ON m.source = bre.id AND bre.deleted' : - ''; + + my $bre_join = ''; + if ($self->find_modifier('deleted')) { + $bre_join = 'INNER JOIN biblio.record_entry bre ON m.source = bre.id AND bre.deleted'; + # The above suffices for filters too when the #deleted modifier + # is in use. + } elsif ($$flat_plan{uses_bre}) { + $bre_join = 'INNER JOIN biblio.record_entry bre ON m.source = bre.id'; + } my $rank = $rel; @@ -814,6 +820,7 @@ sub flatten { my $from = shift || ''; my $where = shift || ''; my $with = ''; + my $uses_bre = 0; my @rank_list; for my $node ( @{$self->query_nodes} ) { @@ -972,6 +979,8 @@ sub flatten { $with .= ",\n " if $with; $with .= $$subnode{with}; } + + $uses_bre = $$subnode{uses_bre}; } } else { @@ -1104,6 +1113,8 @@ sub flatten { # bre.create_date and bre.edit_date filtering my $datefilter = $filter->name; + $uses_bre = 1; + if ($filter && $filter->args && scalar(@{$filter->args}) > 0 && scalar(@{$filter->args}) < 3) { my ($cstart, $cend) = @{$filter->args}; @@ -1136,6 +1147,8 @@ sub flatten { } } } elsif ($filter->name eq 'bib_source') { + $uses_bre = 1; + if (@{$filter->args} > 0) { $where .= $joiner if $where ne ''; $where .= "${NOT}COALESCE(bre.source IN (" @@ -1147,7 +1160,7 @@ sub flatten { } warn "flatten(): full filter where => $where\n" if $self->QueryParser->debug; - return { rank_list => \@rank_list, from => $from, where => $where, with => $with }; + return { rank_list => \@rank_list, from => $from, where => $where, with => $with, uses_bre => $uses_bre }; } diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index 9676990fe1..e14c239678 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -1289,6 +1289,7 @@ BEGIN END LOOP; IF TG_OP = 'INSERT' OR OLD.deleted THEN -- initial insert OR revivication + DELETE FROM metabib.record_attr WHERE id = NEW.id; 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; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.deleted-search-fts.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.deleted-search-fts.sql index f98c2a694b..a8fb6b70ba 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.deleted-search-fts.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.deleted-search-fts.sql @@ -334,5 +334,176 @@ BEGIN END; $func$ LANGUAGE PLPGSQL; + +-- 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 + DELETE FROM metabib.record_attr WHERE id = NEW.id; + 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; -- 2.43.2