3 INSERT INTO config.upgrade_log (version) VALUES ('0267'); -- miker
5 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
6 SELECT isbn FROM (SELECT isn_weak(true), $1 || ' ' || REPLACE( CASE WHEN length($1) = 10 THEN isbn13($1)::TEXT WHEN length($1) = 13 THEN isbn($1)::TEXT ELSE '' END, '-', '') AS isbn)x;
7 $func$ LANGUAGE SQL STRICT IMMUTABLE;
10 UPDATE config.metabib_field SET facet_field = FALSE WHERE id BETWEEN 17 AND 25;
11 UPDATE config.metabib_field SET xpath = REPLACE(xpath,'marcxml','marc') WHERE id BETWEEN 17 AND 25;
12 UPDATE config.metabib_field SET xpath = REPLACE(xpath,'tag','@tag') WHERE id BETWEEN 17 AND 25;
13 UPDATE config.metabib_field SET xpath = REPLACE(xpath,'code','@code') WHERE id BETWEEN 17 AND 25;
14 UPDATE config.metabib_field SET xpath = REPLACE(xpath,'"',E'\'') WHERE id BETWEEN 17 AND 25;
15 UPDATE config.metabib_field SET xpath = REPLACE(xpath,'/text()','') WHERE id BETWEEN 17 AND 24;
18 INSERT INTO config.index_normalizer (name, description, func, param_count) VALUES (
19 'ISBN 10/13 conversion',
20 'Translate ISBN10 to ISBN13, and vice versa, for indexing purposes.',
25 INSERT INTO config.index_normalizer (name, description, func, param_count) VALUES (
27 'Replace all occurances of first parameter in the string with the second parameter.',
32 INSERT INTO config.metabib_field_index_norm_map (field,norm,pos)
34 FROM config.metabib_field m,
35 config.index_normalizer i
36 WHERE i.func IN ('first_word')
39 INSERT INTO config.metabib_field_index_norm_map (field,norm,pos)
41 FROM config.metabib_field m,
42 config.index_normalizer i
43 WHERE i.func IN ('translate_isbn1013')
46 INSERT INTO config.metabib_field_index_norm_map (field,norm,params)
47 SELECT m.id, i.id, $$['-','']$$
48 FROM config.metabib_field m,
49 config.index_normalizer i
50 WHERE i.func IN ('replace')
53 INSERT INTO config.metabib_field_index_norm_map (field,norm,params)
54 SELECT m.id, i.id, $$[' ','']$$
55 FROM config.metabib_field m,
56 config.index_normalizer i
57 WHERE i.func IN ('replace')
60 CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
62 ind_data metabib.field_entry_template%ROWTYPE;
64 tmp_mr metabib.metarecord%ROWTYPE;
83 IF NEW.deleted IS TRUE THEN
84 DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id; -- Rid ourselves of the search-estimate-killing linkage
85 RETURN NEW; -- and we're done
88 IF TG_OP = 'UPDATE' THEN -- re-ingest?
89 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
91 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
95 DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id; -- Rid ourselves of the search-estimate-killing linkage
99 IF TG_OP = 'UPDATE' THEN -- Clean out the cruft
100 FOR fclass IN SELECT * FROM config.metabib_class LOOP
101 -- RAISE NOTICE 'Emptying out %', fclass.name;
102 EXECUTE $$DELETE FROM metabib.$$ || fclass.name || $$_field_entry WHERE source = $$ || NEW.id;
104 DELETE FROM metabib.full_rec WHERE record = NEW.id;
105 DELETE FROM metabib.rec_descriptor WHERE record = NEW.id;
109 -- Shove the flattened MARC in
110 INSERT INTO metabib.full_rec (record, tag, ind1, ind2, subfield, value)
111 SELECT record, tag, ind1, ind2, subfield, value FROM biblio.flatten_marc( NEW.id );
113 -- And now the indexing data
114 FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( NEW.id ) LOOP
115 IF ind_data.field < 0 THEN
117 ind_data.field = -1 * ind_data.field;
123 INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value, index_vector)
125 quote_literal(ind_data.field) || $$, $$ ||
126 quote_literal(ind_data.source) || $$, $$ ||
127 quote_literal(ind_data.value) || $$, $$ ||
128 COALESCE(quote_literal(ind_vector),'NULL'::TEXT) ||
132 -- Then, the rec_descriptor
133 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)
135 biblio.marc21_extract_fixed_field( NEW.id, 'Type' ),
136 biblio.marc21_extract_fixed_field( NEW.id, 'Form' ),
137 biblio.marc21_extract_fixed_field( NEW.id, 'BLvl' ),
138 biblio.marc21_extract_fixed_field( NEW.id, 'Ctrl' ),
139 biblio.marc21_extract_fixed_field( NEW.id, 'ELvl' ),
140 biblio.marc21_extract_fixed_field( NEW.id, 'Audn' ),
141 biblio.marc21_extract_fixed_field( NEW.id, 'LitF' ),
142 biblio.marc21_extract_fixed_field( NEW.id, 'TMat' ),
143 biblio.marc21_extract_fixed_field( NEW.id, 'Desc' ),
144 biblio.marc21_extract_fixed_field( NEW.id, 'DtSt' ),
145 biblio.marc21_extract_fixed_field( NEW.id, 'Lang' ),
147 FROM biblio.marc21_physical_characteristics( NEW.id) p
148 JOIN config.marc21_physical_characteristic_subfield_map s ON (s.id = p.subfield)
149 JOIN config.marc21_physical_characteristic_value_map v ON (v.id = p.value)
150 WHERE p.ptype = 'v' AND s.subfield = 'e' ),
151 biblio.marc21_extract_fixed_field( NEW.id, 'Date1'),
152 biblio.marc21_extract_fixed_field( NEW.id, 'Date2');
155 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.skip_located_uri' AND enabled;
157 IF NOT FOUND OR TG_OP = 'INSERT' THEN
158 uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',NEW.marc);
159 IF ARRAY_UPPER(uris,1) > 0 THEN
160 FOR i IN 1 .. ARRAY_UPPER(uris, 1) LOOP
161 -- First we pull infot out of the 856
164 uri_href := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1];
165 CONTINUE WHEN uri_href IS NULL;
167 uri_label := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()|//*[@code="u"]/text()',uri_xml))[1];
168 CONTINUE WHEN uri_label IS NULL;
170 uri_owner := (oils_xpath('//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()',uri_xml))[1];
171 CONTINUE WHEN uri_owner IS NULL;
173 uri_use := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()',uri_xml))[1];
175 uri_owner := REGEXP_REPLACE(uri_owner, $re$^.*?\((\w+)\).*$$re$, E'\\1');
177 SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = uri_owner;
178 CONTINUE WHEN NOT FOUND;
180 -- now we look for a matching uri
181 SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
182 IF NOT FOUND THEN -- create one
183 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
184 SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
187 -- we need a call number to link through
188 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;
190 INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label)
191 VALUES (uri_owner_id, NEW.id, 'now', 'now', NEW.editor, NEW.editor, '##URI##');
192 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;
195 -- now, link them if they're not already
196 SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id;
198 INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id);
205 -- And, finally, metarecord mapping!
207 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled;
209 IF NOT FOUND OR TG_OP = 'UPDATE' THEN
210 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
212 IF old_mr IS NULL AND NEW.fingerprint = tmp_mr.fingerprint THEN -- Find the first fingerprint-matching
215 SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = tmp_mr.id;
216 IF source_count = 0 THEN -- No other records
217 deleted_mrs := ARRAY_APPEND(deleted_mrs, tmp_mr.id);
218 DELETE FROM metabib.metarecord WHERE id = tmp_mr.id;
224 IF old_mr IS NULL THEN -- we found no suitable, preexisting MR based on old source maps
225 SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = NEW.fingerprint; -- is there one for our current fingerprint?
226 IF old_mr IS NULL THEN -- nope, create one and grab its id
227 INSERT INTO metabib.metarecord ( fingerprint, master_record ) VALUES ( NEW.fingerprint, NEW.id );
228 SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = NEW.fingerprint;
229 ELSE -- indeed there is. update it with a null cache and recalcualated master record
230 UPDATE metabib.metarecord
232 master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = NEW.fingerprint ORDER BY quality DESC LIMIT 1)
235 ELSE -- there was one we already attached to, update its mods cache and master_record
236 UPDATE metabib.metarecord
238 master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = NEW.fingerprint ORDER BY quality DESC LIMIT 1)
242 INSERT INTO metabib.metarecord_source_map (metarecord, source) VALUES (old_mr, NEW.id); -- new source mapping
244 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
250 $func$ LANGUAGE PLPGSQL;