QP search modifier '#deleted'
[working/Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / XXXX.schema.search-deleted.sql
1 BEGIN;
2
3 -- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
4
5 INSERT INTO config.internal_flag (name) VALUES ('ingest.metarecord_mapping.preserve_on_delete');  -- defaults to false/off
6
7 DROP RULE protect_bib_rec_delete ON biblio.record_entry;
8 CREATE RULE protect_bib_rec_delete AS
9     ON DELETE TO biblio.record_entry DO INSTEAD (
10         UPDATE biblio.record_entry
11             SET deleted = TRUE
12             WHERE OLD.id = biblio.record_entry.id
13     );
14
15
16 -- AFTER UPDATE OR INSERT trigger for biblio.record_entry
17 CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
18 DECLARE
19     transformed_xml TEXT;
20     prev_xfrm       TEXT;
21     normalizer      RECORD;
22     xfrm            config.xml_transform%ROWTYPE;
23     attr_value      TEXT;
24     new_attrs       HSTORE := ''::HSTORE;
25     attr_def        config.record_attr_definition%ROWTYPE;
26 BEGIN
27
28     IF NEW.deleted IS TRUE THEN -- If this bib is deleted
29         PERFORM * FROM config.internal_flag WHERE
30             name = 'ingest.metarecord_mapping.preserve_on_delete' AND enabled;
31         IF NOT FOUND THEN
32             -- One needs to keep these around to support searches
33             -- with the #deleted modifier, so one should turn on the named
34             -- internal flag for that functionality.
35             DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id;
36             DELETE FROM metabib.record_attr WHERE id = NEW.id;
37         END IF;
38
39         DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible
40         DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = NEW.id; -- Separate any multi-homed items
41         DELETE FROM metabib.browse_entry_def_map WHERE source = NEW.id; -- Don't auto-suggest deleted bibs
42         RETURN NEW; -- and we're done
43     END IF;
44
45     IF TG_OP = 'UPDATE' THEN -- re-ingest?
46         PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
47
48         IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
49             RETURN NEW;
50         END IF;
51     END IF;
52
53     -- Record authority linking
54     PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking' AND enabled;
55     IF NOT FOUND THEN
56         PERFORM biblio.map_authority_linking( NEW.id, NEW.marc );
57     END IF;
58
59     -- Flatten and insert the mfr data
60     PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled;
61     IF NOT FOUND THEN
62         PERFORM metabib.reingest_metabib_full_rec(NEW.id);
63
64         -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields
65         PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled;
66         IF NOT FOUND THEN
67             FOR attr_def IN SELECT * FROM config.record_attr_definition ORDER BY format LOOP
68
69                 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
70                     SELECT  ARRAY_TO_STRING(ARRAY_ACCUM(value), COALESCE(attr_def.joiner,' ')) INTO attr_value
71                       FROM  (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x
72                       WHERE record = NEW.id
73                             AND tag LIKE attr_def.tag
74                             AND CASE
75                                 WHEN attr_def.sf_list IS NOT NULL 
76                                     THEN POSITION(subfield IN attr_def.sf_list) > 0
77                                 ELSE TRUE
78                                 END
79                       GROUP BY tag
80                       ORDER BY tag
81                       LIMIT 1;
82
83                 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
84                     attr_value := biblio.marc21_extract_fixed_field(NEW.id, attr_def.fixed_field);
85
86                 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
87
88                     SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
89             
90                     -- See if we can skip the XSLT ... it's expensive
91                     IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
92                         -- Can't skip the transform
93                         IF xfrm.xslt <> '---' THEN
94                             transformed_xml := oils_xslt_process(NEW.marc,xfrm.xslt);
95                         ELSE
96                             transformed_xml := NEW.marc;
97                         END IF;
98             
99                         prev_xfrm := xfrm.name;
100                     END IF;
101
102                     IF xfrm.name IS NULL THEN
103                         -- just grab the marcxml (empty) transform
104                         SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
105                         prev_xfrm := xfrm.name;
106                     END IF;
107
108                     attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]);
109
110                 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
111                     SELECT  m.value INTO attr_value
112                       FROM  biblio.marc21_physical_characteristics(NEW.id) v
113                             JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
114                       WHERE v.subfield = attr_def.phys_char_sf
115                       LIMIT 1; -- Just in case ...
116
117                 END IF;
118
119                 -- apply index normalizers to attr_value
120                 FOR normalizer IN
121                     SELECT  n.func AS func,
122                             n.param_count AS param_count,
123                             m.params AS params
124                       FROM  config.index_normalizer n
125                             JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
126                       WHERE attr = attr_def.name
127                       ORDER BY m.pos LOOP
128                         EXECUTE 'SELECT ' || normalizer.func || '(' ||
129                             COALESCE( quote_literal( attr_value ), 'NULL' ) ||
130                             CASE
131                                 WHEN normalizer.param_count > 0
132                                     THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
133                                     ELSE ''
134                                 END ||
135                             ')' INTO attr_value;
136         
137                 END LOOP;
138
139                 -- Add the new value to the hstore
140                 new_attrs := new_attrs || hstore( attr_def.name, attr_value );
141
142             END LOOP;
143
144             IF TG_OP = 'INSERT' OR OLD.deleted THEN -- initial insert OR revivication
145                 INSERT INTO metabib.record_attr (id, attrs) VALUES (NEW.id, new_attrs);
146             ELSE
147                 UPDATE metabib.record_attr SET attrs = new_attrs WHERE id = NEW.id;
148             END IF;
149
150         END IF;
151     END IF;
152
153     -- Gather and insert the field entry data
154     PERFORM metabib.reingest_metabib_field_entries(NEW.id);
155
156     -- Located URI magic
157     IF TG_OP = 'INSERT' THEN
158         PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
159         IF NOT FOUND THEN
160             PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
161         END IF;
162     ELSE
163         PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
164         IF NOT FOUND THEN
165             PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
166         END IF;
167     END IF;
168
169     -- (re)map metarecord-bib linking
170     IF TG_OP = 'INSERT' THEN -- if not deleted and performing an insert, check for the flag
171         PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled;
172         IF NOT FOUND THEN
173             PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
174         END IF;
175     ELSE -- we're doing an update, and we're not deleted, remap
176         PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_update' AND enabled;
177         IF NOT FOUND THEN
178             PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
179         END IF;
180     END IF;
181
182     RETURN NEW;
183 END;
184 $func$ LANGUAGE PLPGSQL;
185
186
187 COMMIT;