3 INSERT INTO config.upgrade_log (version) VALUES ('0206'); -- miker
5 CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry ( rid BIGINT, default_joiner TEXT ) RETURNS SETOF metabib.field_entry_template AS $func$
7 bib biblio.record_entry%ROWTYPE;
8 idx config.metabib_field%ROWTYPE;
9 xfrm config.xml_transform%ROWTYPE;
15 joiner TEXT := default_joiner; -- XXX will index defs supply a joiner?
16 output_row metabib.field_entry_template%ROWTYPE;
20 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
22 -- Loop over the indexing entries
23 FOR idx IN SELECT * FROM config.metabib_field ORDER BY format LOOP
25 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
27 -- See if we can skip the XSLT ... it's expensive
28 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
29 -- Can't skip the transform
30 IF xfrm.xslt <> '---' THEN
31 transformed_xml := oils_xslt_process(bib.marc,xfrm.xslt);
33 transformed_xml := bib.marc;
36 prev_xfrm := xfrm.name;
39 xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
42 FOR xml_node IN SELECT x FROM explode_array(xml_node_list) AS x LOOP
43 CONTINUE WHEN xml_node !~ E'^\\s*<';
44 IF raw_text IS NOT NULL THEN
45 raw_text := raw_text || joiner;
47 raw_text := COALESCE(raw_text,'') || ARRAY_TO_STRING(
48 oils_xpath( '//text()',
49 REGEXP_REPLACE( -- This escapes all &s not followed by "amp;". Data ise returned from oils_xpath (above) in UTF-8, not entity encoded
50 REGEXP_REPLACE( -- This escapes embeded <s
52 $re$(>[^<]+)(<)([^>]+<)$re$,
65 CONTINUE WHEN raw_text IS NULL;
67 output_row.field_class = idx.field_class;
68 output_row.field = idx.id;
69 output_row.source = rid;
70 output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'g'));
72 RETURN NEXT output_row;
77 $func$ LANGUAGE PLPGSQL;
79 CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
81 ind_data metabib.field_entry_template%ROWTYPE;
83 tmp_mr metabib.metarecord%ROWTYPE;
98 IF NEW.deleted IS TRUE THEN
99 DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id; -- Rid ourselves of the search-estimate-killing linkage
100 RETURN NEW; -- and we're done
103 IF TG_OP = 'UPDATE' THEN -- re-ingest?
104 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
106 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
110 DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id; -- Rid ourselves of the search-estimate-killing linkage
114 IF TG_OP = 'UPDATE' THEN -- Clean out the cruft
115 DELETE FROM metabib.title_field_entry WHERE source = NEW.id;
116 DELETE FROM metabib.author_field_entry WHERE source = NEW.id;
117 DELETE FROM metabib.subject_field_entry WHERE source = NEW.id;
118 DELETE FROM metabib.keyword_field_entry WHERE source = NEW.id;
119 DELETE FROM metabib.series_field_entry WHERE source = NEW.id;
120 DELETE FROM metabib.full_rec WHERE record = NEW.id;
121 DELETE FROM metabib.rec_descriptor WHERE record = NEW.id;
125 -- Shove the flattened MARC in
126 INSERT INTO metabib.full_rec (record, tag, ind1, ind2, subfield, value)
127 SELECT record, tag, ind1, ind2, subfield, value FROM biblio.flatten_marc( NEW.id );
129 -- And now the indexing data
130 FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( NEW.id ) LOOP
131 IF ind_data.field_class = 'title' THEN
132 INSERT INTO metabib.title_field_entry (field, source, value)
133 VALUES (ind_data.field, ind_data.source, ind_data.value);
134 ELSIF ind_data.field_class = 'author' THEN
135 INSERT INTO metabib.author_field_entry (field, source, value)
136 VALUES (ind_data.field, ind_data.source, ind_data.value);
137 ELSIF ind_data.field_class = 'subject' THEN
138 INSERT INTO metabib.subject_field_entry (field, source, value)
139 VALUES (ind_data.field, ind_data.source, ind_data.value);
140 ELSIF ind_data.field_class = 'keyword' THEN
141 INSERT INTO metabib.keyword_field_entry (field, source, value)
142 VALUES (ind_data.field, ind_data.source, ind_data.value);
143 ELSIF ind_data.field_class = 'series' THEN
144 INSERT INTO metabib.series_field_entry (field, source, value)
145 VALUES (ind_data.field, ind_data.source, ind_data.value);
149 -- Then, the rec_descriptor
150 INSERT INTO metabib.rec_descriptor (record, item_type, item_form, bib_level, control_type, enc_level, audience, lit_form, type_mat, cat_form, pub_status, item_lang, vr_format, date1, date2)
152 biblio.marc21_extract_fixed_field( NEW.id, 'Type' ),
153 biblio.marc21_extract_fixed_field( NEW.id, 'Form' ),
154 biblio.marc21_extract_fixed_field( NEW.id, 'BLvl' ),
155 biblio.marc21_extract_fixed_field( NEW.id, 'Ctrl' ),
156 biblio.marc21_extract_fixed_field( NEW.id, 'ELvl' ),
157 biblio.marc21_extract_fixed_field( NEW.id, 'Audn' ),
158 biblio.marc21_extract_fixed_field( NEW.id, 'LitF' ),
159 biblio.marc21_extract_fixed_field( NEW.id, 'TMat' ),
160 biblio.marc21_extract_fixed_field( NEW.id, 'Desc' ),
161 biblio.marc21_extract_fixed_field( NEW.id, 'DtSt' ),
162 biblio.marc21_extract_fixed_field( NEW.id, 'Lang' ),
164 FROM biblio.marc21_physical_characteristics( NEW.id) p
165 JOIN config.marc21_physical_characteristic_subfield_map s ON (s.id = p.subfield)
166 JOIN config.marc21_physical_characteristic_value_map v ON (v.id = p.value)
167 WHERE p.ptype = 'v' AND s.subfield = 'e' ),
168 biblio.marc21_extract_fixed_field( NEW.id, 'Date1'),
169 biblio.marc21_extract_fixed_field( NEW.id, 'Date2');
172 uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',NEW.marc);
173 IF ARRAY_UPPER(uris,1) > 0 THEN
174 FOR i IN 1 .. ARRAY_UPPER(uris, 1) LOOP
175 -- First we pull infot out of the 856
178 uri_href := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1];
179 CONTINUE WHEN uri_href IS NULL;
181 uri_label := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()|//*[@code="u"]/text()',uri_xml))[1];
182 CONTINUE WHEN uri_label IS NULL;
184 uri_owner := (oils_xpath('//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()',uri_xml))[1];
185 CONTINUE WHEN uri_owner IS NULL;
187 uri_use := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()',uri_xml))[1];
189 uri_owner := REGEXP_REPLACE(uri_owner, $re$^.*?\((\w+)\).*$$re$, E'\\1');
191 SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = uri_owner;
192 CONTINUE WHEN NOT FOUND;
194 -- now we look for a matching uri
195 SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
196 IF NOT FOUND THEN -- create one
197 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
198 SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
201 -- we need a call number to link through
202 SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = NEW.id AND label = '##URI##' AND NOT deleted;
204 INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label)
205 VALUES (uri_owner_id, NEW.id, 'now', 'now', NEW.editor, NEW.editor, '##URI##');
206 SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = NEW.id AND label = '##URI##' AND NOT deleted;
209 -- now, link them if they're not already
210 SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id;
212 INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id);
218 -- And, finally, metarecord mapping!
220 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled;
222 IF NOT FOUND OR TG_OP = 'UPDATE' THEN
223 FOR tmp_mr IN SELECT m.* FROM metabib.metarecord m JOIN metabib.metarecord_source_map s ON (s.metarecord = m.id) WHERE s.source = NEW.id LOOP
225 IF old_mr IS NULL AND NEW.fingerprint = tmp_mr.fingerprint THEN -- Find the first fingerprint-matching
228 SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = tmp_mr.id;
229 IF source_count = 0 THEN -- No other records
230 deleted_mrs := ARRAY_APPEND(deleted_mrs, tmp_mr.id);
231 DELETE FROM metabib.metarecord WHERE id = tmp_mr.id;
237 IF old_mr IS NULL THEN -- we found no suitable, preexisting MR based on old source maps
238 SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = NEW.fingerprint; -- is there one for our current fingerprint?
239 IF old_mr IS NULL THEN -- nope, create one and grab its id
240 INSERT INTO metabib.metarecord ( fingerprint, master_record ) VALUES ( NEW.fingerprint, NEW.id );
241 SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = NEW.fingerprint;
242 ELSE -- indeed there is. update it with a null cache and recalcualated master record
243 UPDATE metabib.metarecord
245 master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = NEW.fingerprint ORDER BY quality DESC LIMIT 1)
248 ELSE -- there was one we already attached to, update its mods cache and master_record
249 UPDATE metabib.metarecord
251 master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = NEW.fingerprint ORDER BY quality DESC LIMIT 1)
255 INSERT INTO metabib.metarecord_source_map (metarecord, source) VALUES (old_mr, NEW.id); -- new source mapping
257 UPDATE action.hold_request SET target = old_mr WHERE target IN ( SELECT explode_array(deleted_mrs) ) AND hold_type = 'M'; -- if we had to delete any MRs above, make sure their holds are moved
263 $func$ LANGUAGE PLPGSQL;