4 INSERT INTO config.upgrade_log (version) VALUES ('0192'); -- miker
6 CREATE OR REPLACE FUNCTION vandelay.compile_profile ( incoming_xml TEXT ) RETURNS vandelay.compile_profile AS $_$
8 output vandelay.compile_profile%ROWTYPE;
9 profile vandelay.merge_profile%ROWTYPE;
11 profile_tmpl_owner TEXT;
13 strip_rule TEXT := '';
14 replace_rule TEXT := '';
15 preserve_rule TEXT := '';
19 profile_tmpl := (oils_xpath('//*[@tag="905"]/*[@code="t"]/text()',incoming_xml))[1];
20 profile_tmpl_owner := (oils_xpath('//*[@tag="905"]/*[@code="o"]/text()',incoming_xml))[1];
22 IF profile_tmpl IS NOT NULL AND profile_tmpl <> '' AND profile_tmpl_owner IS NOT NULL AND profile_tmpl_owner <> '' THEN
23 SELECT p.* INTO profile
24 FROM vandelay.merge_profile p
25 JOIN actor.org_unit u ON (u.id = p.owner)
26 WHERE p.name = profile_tmpl
27 AND u.shortname = profile_tmpl_owner;
29 IF profile.id IS NOT NULL THEN
30 add_rule := COALESCE(profile.add_spec,'');
31 strip_rule := COALESCE(profile.strip_spec,'');
32 replace_rule := COALESCE(profile.replace_spec,'');
33 preserve_rule := COALESCE(profile.preserve_spec,'');
37 add_rule := add_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="a"]/text()',incoming_xml),''),'');
38 strip_rule := strip_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="d"]/text()',incoming_xml),''),'');
39 replace_rule := replace_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="r"]/text()',incoming_xml),''),'');
40 preserve_rule := preserve_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="p"]/text()',incoming_xml),''),'');
42 output.add_rule := BTRIM(add_rule,',');
43 output.replace_rule := BTRIM(replace_rule,',');
44 output.strip_rule := BTRIM(strip_rule,',');
45 output.preserve_rule := BTRIM(preserve_rule,',');
51 CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
53 merge_profile vandelay.merge_profile%ROWTYPE;
54 dyn_profile vandelay.compile_profile%ROWTYPE;
63 SELECT b.marc INTO eg_marc
64 FROM biblio.record_entry b
65 JOIN vandelay.bib_match m ON (m.eg_record = b.id AND m.queued_record = import_id)
68 SELECT q.marc INTO v_marc
69 FROM vandelay.queued_record q
70 JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
73 IF eg_marc IS NULL OR v_marc IS NULL THEN
74 -- RAISE NOTICE 'no marc for vandelay or bib record';
78 dyn_profile := vandelay.compile_profile( v_marc );
80 IF merge_profile_id IS NOT NULL THEN
81 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
83 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
84 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
85 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
86 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
90 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
91 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
95 IF dyn_profile.replace_rule <> '' THEN
97 target_marc = eg_marc;
98 replace_rule = dyn_profile.replace_rule;
100 source_marc = eg_marc;
101 target_marc = v_marc;
102 replace_rule = dyn_profile.preserve_rule;
105 UPDATE biblio.record_entry
106 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
110 UPDATE vandelay.queued_bib_record
111 SET imported_as = eg_id,
113 WHERE id = import_id;
117 -- RAISE NOTICE 'update of biblio.record_entry failed';
124 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
129 SELECT COUNT(*) INTO match_count FROM vandelay.bib_match WHERE queued_record = import_id;
131 IF match_count <> 1 THEN
132 -- RAISE NOTICE 'not an exact match';
136 SELECT m.eg_record INTO eg_id
137 FROM vandelay.bib_match m
138 WHERE m.queued_record = import_id
141 IF eg_id IS NULL THEN
145 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
149 CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
157 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
159 SELECT * INTO attr FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1;
161 IF attr IS NOT NULL AND attr.id IS NOT NULL THEN
162 id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr.xpath, attr.remove);
164 IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN
165 SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted;
166 IF exact_id IS NOT NULL THEN
167 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id);
172 IF exact_id IS NULL THEN
173 FOR attr IN SELECT a.* FROM vandelay.queued_bib_record_attr a JOIN vandelay.bib_attr_definition d ON (d.id = a.field) WHERE record = NEW.id AND d.ident IS TRUE LOOP
175 -- All numbers? check for an id match
176 IF (attr.attr_value ~ $r$^\d+$$r$) THEN
177 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP
178 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
182 -- Looks like an ISBN? check for an isbn match
183 IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN
184 FOR eg_rec IN EXECUTE $$SELECT * FROM metabib.full_rec fr WHERE fr.value LIKE LOWER('$$ || attr.attr_value || $$%') AND fr.tag = '020' AND fr.subfield = 'a'$$ LOOP
185 PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE;
187 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
191 -- subcheck for isbn-as-tcn
192 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP
193 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
197 -- check for an OCLC tcn_value match
198 IF (attr.attr_value ~ $r$^o\d+$$r$) THEN
199 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP
200 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
204 -- check for a direct tcn_value match
205 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP
206 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
209 -- check for a direct item barcode match
212 FROM biblio.record_entry b
213 JOIN asset.call_number cn ON (cn.record = b.id)
214 JOIN asset.copy cp ON (cp.call_number = cn.id)
215 WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE
217 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
225 $func$ LANGUAGE PLPGSQL;