4 INSERT INTO config.upgrade_log (version) VALUES ('0191'); -- miker
6 ALTER TABLE vandelay.merge_profile DROP CONSTRAINT add_replace_strip_or_preserve;
7 ALTER TABLE vandelay.merge_profile ADD CONSTRAINT add_replace_strip_or_preserve CHECK ((preserve_spec IS NOT NULL OR replace_spec IS NOT NULL) OR (preserve_spec IS NULL AND replace_spec IS NULL));
9 CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
17 SELECT * INTO attr FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1;
19 IF attr IS NOT NULL AND attr.id IS NOT NULL THEN
20 id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr.xpath, attr.remove);
22 IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN
23 SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted;
24 IF exact_id IS NOT NULL THEN
25 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id);
30 IF exact_id IS NULL THEN
31 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
33 -- All numbers? check for an id match
34 IF (attr.attr_value ~ $r$^\d+$$r$) THEN
35 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP
36 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
40 -- Looks like an ISBN? check for an isbn match
41 IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN
42 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
43 PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE;
45 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
49 -- subcheck for isbn-as-tcn
50 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP
51 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
55 -- check for an OCLC tcn_value match
56 IF (attr.attr_value ~ $r$^o\d+$$r$) THEN
57 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP
58 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
62 -- check for a direct tcn_value match
63 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP
64 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
67 -- check for a direct item barcode match
70 FROM biblio.record_entry b
71 JOIN asset.call_number cn ON (cn.record = b.id)
72 JOIN asset.copy cp ON (cp.call_number = cn.id)
73 WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE
75 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
83 $func$ LANGUAGE PLPGSQL;
85 CREATE OR REPLACE FUNCTION vandelay.add_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
90 my $target_xml = shift;
91 my $source_xml = shift;
92 my $field_spec = shift;
93 $field_spec =~ s/\s+//sg;
95 my $target_r = MARC::Record->new_from_xml( $target_xml );
96 my $source_r = MARC::Record->new_from_xml( $source_xml );
98 return $target_xml unless ($target_r && $source_r);
100 my @field_list = split(',', $field_spec);
103 for my $f (@field_list) {
104 if ($f =~ /^(.{3})(.*)$/) {
105 $fields{$1} = [ split('', $2) ];
109 for my $f ( keys %fields) {
110 if ( @{$fields{$f}} ) {
111 for my $from_field ($source_r->field( $f )) {
112 for my $to_field ($target_r->field( $f )) {
113 my @new_sf = map { ($_ => $from_field->subfield($_)) } @{$fields{$f}};
114 $to_field->add_subfields( @new_sf );
118 my @new_fields = map { $_->clone } $source_r->field( $f );
119 $target_r->insert_fields_ordered( @new_fields );
123 $target_xml = $target_r->as_xml_record;
124 $target_xml =~ s/^<\?.+?\?>$//mo;
125 $target_xml =~ s/\n//sgo;
126 $target_xml =~ s/>\s+</></sgo;
130 $_$ LANGUAGE PLPERLU;
132 CREATE OR REPLACE FUNCTION vandelay.strip_field ( xml TEXT, field TEXT ) RETURNS TEXT AS $_$
138 my $r = MARC::Record->new_from_xml( $xml );
140 return $xml unless ($r);
142 my $field_spec = shift;
143 $field_spec =~ s/\s+//sg;
145 my @field_list = split(',', $field_spec);
148 for my $f (@field_list) {
149 if ($f =~ /^(.{3})(.*)$/) {
150 $fields{$1} = [ split('', $2) ];
154 for my $f ( keys %fields) {
155 if ( @{$fields{$f}} ) {
156 $_->delete_subfield(code => $fields{$f}) for ($r->field( $f ));
158 $r->delete_field( $_ ) for ( $r->field( $f ) );
162 $xml = $r->as_xml_record;
163 $xml =~ s/^<\?.+?\?>$//mo;
165 $xml =~ s/>\s+</></sgo;
169 $_$ LANGUAGE PLPERLU;
171 CREATE OR REPLACE FUNCTION vandelay.replace_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
172 SELECT vandelay.add_field( vandelay.strip_field( $1, $3), $2, $3 );
175 CREATE OR REPLACE FUNCTION vandelay.merge_record_xml ( target_xml TEXT, source_xml TEXT, add_rule TEXT, replace_preserve_rule TEXT, strip_rule TEXT ) RETURNS TEXT AS $_$
176 SELECT vandelay.replace_field( vandelay.add_field( vandelay.strip_field( $1, $5) , $2, $3 ), $2, $4);
179 CREATE TYPE vandelay.compile_profile AS (add_rule TEXT, replace_rule TEXT, preserve_rule TEXT, strip_rule TEXT);
180 CREATE OR REPLACE FUNCTION vandelay.compile_profile ( incoming_xml TEXT ) RETURNS vandelay.compile_profile AS $_$
182 output vandelay.compile_profile%ROWTYPE;
183 profile vandelay.merge_profile%ROWTYPE;
185 profile_tmpl_owner TEXT;
187 strip_rule TEXT := '';
188 replace_rule TEXT := '';
189 preserve_rule TEXT := '';
193 profile_tmpl := (oils_xpath('//*[@tag="905"]/*[@code="t"]/text()',incoming_xml))[1];
194 profile_tmpl_owner := (oils_xpath('//*[@tag="905"]/*[@code="o"]/text()',incoming_xml))[1];
196 IF profile_tmpl IS NOT NULL AND profile_tmpl <> '' AND profile_tmpl_owner IS NOT NULL AND profile_tmpl_owner <> '' THEN
197 SELECT p.* INTO profile
198 FROM vandelay.merge_profile p
199 JOIN actor.org_unit u ON (u.id = p.owner)
200 WHERE p.name = profile_tmpl
201 AND u.shortname = profile_tmpl_owner;
203 IF profile.id IS NOT NULL THEN
204 add_rule := add_rule || COALESCE(profile.add_spec,'');
205 strip_rule := strip_rule || COALESCE(profile.strip_spec,'');
206 replace_rule := replace_rule || COALESCE(profile.replace_spec,'');
207 preserve_rule := preserve_rule || COALESCE(profile.preserve_spec,'');
211 add_rule := add_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="a"]/text()',incoming_xml),''),'');
212 strip_rule := strip_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="d"]/text()',incoming_xml),''),'');
213 replace_rule := replace_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="r"]/text()',incoming_xml),''),'');
214 preserve_rule := preserve_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="p"]/text()',incoming_xml),''),'');
216 output.add_rule := add_rule;
217 output.replace_rule := replace_rule;
218 output.strip_rule := strip_rule;
219 output.preserve_rule := preserve_rule;
223 $_$ LANGUAGE PLPGSQL;
225 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
227 merge_profile vandelay.merge_profile%ROWTYPE;
228 dyn_profile vandelay.compile_profile%ROWTYPE;
237 SELECT COUNT(*) INTO match_count FROM vandelay.bib_match WHERE queued_record = import_id;
239 IF match_count <> 1 THEN
243 SELECT m.eg_record INTO eg_id
244 FROM vandelay.bib_match m
245 WHERE m.queued_record = import_id
248 SELECT b.marc INTO eg_marc
249 FROM biblio.record_entry b
250 JOIN vandelay.bib_match m ON (m.eg_record = b.id AND m.queued_record = import_id)
253 SELECT q.marc INTO v_marc
254 FROM vandelay.queued_record q
255 JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
258 IF eg_marc IS NULL OR v_marc IS NULL THEN
262 dyn_profile := vandelay.compile_profile( v_marc );
264 IF merge_profile_id IS NOT NULL THEN
265 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
267 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
268 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
269 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
270 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
274 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
278 IF dyn_profile.replace_rule <> '' THEN
279 source_marc = v_marc;
280 target_marc = eg_marc;
281 replace_rule = dyn_profile.replace_rule;
283 source_marc = eg_marc;
284 target_marc = v_marc;
285 replace_rule = dyn_profile.preserve_rule;
288 UPDATE biblio.record_entry
289 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
293 UPDATE vandelay.queued_bib_record
294 SET imported_as = eg_id,
296 WHERE id = import_id;
305 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
307 queued_record vandelay.queued_bib_record%ROWTYPE;
311 FOR queued_record IN SELECT * FROM vandelay.queued_bib_record WHERE queue = queue_id AND import_time IS NULL LOOP
312 success := vandelay.auto_overlay_bib_record( queued_record.id, merge_profile_id );
315 RETURN NEXT queued_record.id;
325 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
326 SELECT * FROM vandelay.auto_overlay_bib_queue( $1, NULL );