1 DROP SCHEMA IF EXISTS vandelay CASCADE;
5 CREATE SCHEMA vandelay;
7 CREATE TABLE vandelay.queue (
8 id BIGSERIAL PRIMARY KEY,
9 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
11 complete BOOL NOT NULL DEFAULT FALSE,
12 queue_type TEXT NOT NULL DEFAULT 'bib' CHECK (queue_type IN ('bib','authority')),
13 CONSTRAINT vand_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
16 CREATE TABLE vandelay.queued_record (
17 id BIGSERIAL PRIMARY KEY,
18 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
19 import_time TIMESTAMP WITH TIME ZONE,
20 purpose TEXT NOT NULL DEFAULT 'import' CHECK (purpose IN ('import','overlay')),
22 quality INT NOT NULL DEFAULT 0
27 /* Bib stuff at the top */
28 ----------------------------------------------------
30 CREATE TABLE vandelay.bib_attr_definition (
31 id SERIAL PRIMARY KEY,
32 code TEXT UNIQUE NOT NULL,
35 remove TEXT NOT NULL DEFAULT '',
36 ident BOOL NOT NULL DEFAULT FALSE
39 -- Each TEXT field (other than 'name') should hold an XPath predicate for pulling the data needed
40 -- DROP TABLE vandelay.import_item_attr_definition CASCADE;
41 CREATE TABLE vandelay.import_item_attr_definition (
42 id BIGSERIAL PRIMARY KEY,
43 owner INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
46 keep BOOL NOT NULL DEFAULT FALSE,
68 CONSTRAINT vand_import_item_attr_def_idx UNIQUE (owner,name)
71 CREATE TABLE vandelay.bib_queue (
72 queue_type TEXT NOT NULL DEFAULT 'bib' CHECK (queue_type = 'bib'),
73 item_attr_def BIGINT REFERENCES vandelay.import_item_attr_definition (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
74 CONSTRAINT vand_bib_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
75 ) INHERITS (vandelay.queue);
76 ALTER TABLE vandelay.bib_queue ADD PRIMARY KEY (id);
78 CREATE TABLE vandelay.queued_bib_record (
79 queue INT NOT NULL REFERENCES vandelay.bib_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
80 bib_source INT REFERENCES config.bib_source (id) DEFERRABLE INITIALLY DEFERRED,
81 imported_as BIGINT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED
82 ) INHERITS (vandelay.queued_record);
83 ALTER TABLE vandelay.queued_bib_record ADD PRIMARY KEY (id);
84 CREATE INDEX queued_bib_record_queue_idx ON vandelay.queued_bib_record (queue);
86 CREATE TABLE vandelay.queued_bib_record_attr (
87 id BIGSERIAL PRIMARY KEY,
88 record BIGINT NOT NULL REFERENCES vandelay.queued_bib_record (id) DEFERRABLE INITIALLY DEFERRED,
89 field INT NOT NULL REFERENCES vandelay.bib_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
90 attr_value TEXT NOT NULL
92 CREATE INDEX queued_bib_record_attr_record_idx ON vandelay.queued_bib_record_attr (record);
94 CREATE TABLE vandelay.bib_match (
95 id BIGSERIAL PRIMARY KEY,
96 field_type TEXT NOT NULL CHECK (field_type in ('isbn','tcn_value','id')),
97 matched_attr INT REFERENCES vandelay.queued_bib_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
98 queued_record BIGINT REFERENCES vandelay.queued_bib_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
99 eg_record BIGINT REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
100 quality INT NOT NULL DEFAULT 0
103 -- DROP TABLE vandelay.import_item CASCADE;
104 CREATE TABLE vandelay.import_item (
105 id BIGSERIAL PRIMARY KEY,
106 record BIGINT NOT NULL REFERENCES vandelay.queued_bib_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
107 definition BIGINT NOT NULL REFERENCES vandelay.import_item_attr_definition (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
116 deposit_amount NUMERIC(8,2),
129 CREATE TABLE vandelay.import_bib_trash_fields (
130 id BIGSERIAL PRIMARY KEY,
131 owner INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
133 CONSTRAINT vand_import_bib_trash_fields_idx UNIQUE (owner,field)
136 CREATE TABLE vandelay.merge_profile (
137 id BIGSERIAL PRIMARY KEY,
138 owner INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
144 CONSTRAINT vand_merge_prof_owner_name_idx UNIQUE (owner,name),
145 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))
148 CREATE TABLE vandelay.match_set (
149 id SERIAL PRIMARY KEY,
151 owner INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE,
152 mtype TEXT NOT NULL DEFAULT 'biblio', -- 'biblio','authority','mfhd'?, others?
153 CONSTRAINT name_once_per_owner_mtype UNIQUE (name, owner, mtype)
156 -- Table to define match points, either FF via SVF or tag+subfield
157 CREATE TABLE vandelay.match_set_point (
158 id SERIAL PRIMARY KEY,
159 match_set INT NOT NULL REFERENCES vandelay.match_set (id),
160 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
163 required BOOL NOT NULL DEFAULT TRUE,
164 quality INT NOT NULL DEFAULT 1, -- higher is better
165 CONSTRAINT vmsp_need_a_subfield_with_a_tag CHECK ((tag IS NOT NULL AND subfield IS NOT NULL) OR tag IS NULL),
166 CONSTRAINT vmsp_need_a_tag_or_a_ff CHECK (tag IS NOT NULL AND fixed_field IS NULL) OR (tag IS NULL AND fixed_field IS NOT NULL)),
167 CONSTRAINT vmsp_def_once_per_set UNIQUE (match_set, COALESCE(tag,''), COALESCE(subfield,''), COALESCE(fixed_field,''))
170 CREATE TABLE vandelay.match_set_quality (
171 id SERIAL PRIMARY KEY,
172 match_set INT NOT NULL REFERENCES vandelay.match_set (id),
173 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
177 quality INT NOT NULL DEFAULT 1, -- higher is better
178 CONSTRAINT vmsq_need_a_subfield_with_a_tag CHECK ((tag IS NOT NULL AND subfield IS NOT NULL) OR tag IS NULL),
179 CONSTRAINT vmsq_need_a_tag_or_a_ff CHECK (tag IS NOT NULL AND fixed_field IS NULL) OR (tag IS NULL AND fixed_field IS NOT NULL)),
180 CONSTRAINT vmsq_def_once_per_set UNIQUE (match_set, COALESCE(tag,''), COALESCE(subfield,''), COALESCE(fixed_field,''))
183 CREATE OR REPLACE FUNCTION vandelay.marc21_record_type( marc TEXT ) RETURNS config.marc21_rec_type_map AS $func$
190 retval config.marc21_rec_type_map%ROWTYPE;
192 ldr := oils_xpath_string( '//*[local-name()="leader"]', marc );
194 IF ldr IS NULL OR ldr = '' THEN
195 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE code = 'BKS';
199 SELECT * INTO tval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'Type' LIMIT 1; -- They're all the same
200 SELECT * INTO bval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'BLvl' LIMIT 1; -- They're all the same
203 tval := SUBSTRING( ldr, tval_rec.start_pos + 1, tval_rec.length );
204 bval := SUBSTRING( ldr, bval_rec.start_pos + 1, bval_rec.length );
206 -- RAISE NOTICE 'type %, blvl %, ldr %', tval, bval, ldr;
208 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE type_val LIKE '%' || tval || '%' AND blvl_val LIKE '%' || bval || '%';
211 IF retval.code IS NULL THEN
212 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE code = 'BKS';
217 $func$ LANGUAGE PLPGSQL;
219 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field( marc TEXT, ff TEXT ) RETURNS TEXT AS $func$
226 rtype := (vandelay.marc21_record_type( marc )).code;
227 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = ff AND rec_type = rtype ORDER BY tag DESC LOOP
228 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
229 val := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length );
232 val := REPEAT( ff_pos.default_val, ff_pos.length );
238 $func$ LANGUAGE PLPGSQL;
240 CREATE TYPE biblio.record_ff_map AS (record BIGINT, ff_name TEXT, ff_value TEXT);
241 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_all_fixed_fields( marc TEXT ) RETURNS SETOF biblio.record_ff_map AS $func$
246 output biblio.record_ff_map%ROWTYPE;
248 rtype := (vandelay.marc21_record_type( marc )).code;
250 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE rec_type = rtype ORDER BY tag DESC LOOP
251 output.ff_name := ff_pos.fixed_field;
252 output.ff_value := NULL;
254 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(tag) || '"]/text()', marc ) ) x(value) LOOP
255 output.ff_value := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length );
256 IF output.ff_value IS NULL THEN output.ff_value := REPEAT( ff_pos.default_val, ff_pos.length ); END IF;
258 output.ff_value := NULL;
265 $func$ LANGUAGE PLPGSQL;
267 CREATE TYPE biblio.marc21_physical_characteristics AS ( id INT, record BIGINT, ptype TEXT, subfield INT, value INT );
268 CREATE OR REPLACE FUNCTION vandelay.marc21_physical_characteristics( marc TEXT) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$
272 ptype config.marc21_physical_characteristic_type_map%ROWTYPE;
273 psf config.marc21_physical_characteristic_subfield_map%ROWTYPE;
274 pval config.marc21_physical_characteristic_value_map%ROWTYPE;
275 retval biblio.marc21_physical_characteristics%ROWTYPE;
278 _007 := oils_xpath_string( '//*[@tag="007"]', marc );
280 IF _007 IS NOT NULL AND _007 <> '' THEN
281 SELECT * INTO ptype FROM config.marc21_physical_characteristic_type_map WHERE ptype_key = SUBSTRING( _007, 1, 1 );
283 IF ptype.ptype_key IS NOT NULL THEN
284 FOR psf IN SELECT * FROM config.marc21_physical_characteristic_subfield_map WHERE ptype_key = ptype.ptype_key LOOP
285 SELECT * INTO pval FROM config.marc21_physical_characteristic_value_map WHERE ptype_subfield = psf.id AND value = SUBSTRING( _007, psf.start_pos + 1, psf.length );
287 IF pval.id IS NOT NULL THEN
290 retval.ptype := ptype.ptype_key;
291 retval.subfield := psf.id;
292 retval.value := pval.id;
302 $func$ LANGUAGE PLPGSQL;
304 CREAT TYPE vandelay.flat_marc AS ( tag CHAR(3), ind1 TEXT, ind2 TEXT, subfield TEXT, value TEXT );
305 CREATE OR REPLACE FUNCTION vandelay.flay_marc ( TEXT ) RETURNS SETOF vandelay.flat_marc AS $func$
308 use MARC::File::XML (BinaryEncoding => 'UTF-8');
311 my $r = MARC::Record->new_from_xml( $xml );
313 return_next( { tag => 'LDR', value => $r->leader } );
315 for my $f ( $r->fields ) {
316 if ($f->is_control_field) {
317 return_next({ tag => $f->tag, value => $f->data });
319 for my $s ($f->subfields) {
322 ind1 => $f->indicator(1),
323 ind2 => $f->indicator(2),
328 if ( $f->tag eq '245' and $s->[0] eq 'a' ) {
329 my $trim = $f->indicator(2) || 0;
332 ind1 => $f->indicator(1),
333 ind2 => $f->indicator(2),
335 value => substr( $s->[1], $trim )
344 $func$ LANGUAGE PLPERLU;
346 CREATE OR REPLACE FUNCTION vandelay.flatten_marc ( marc TEXT ) RETURNS SETOF vandelay.flat_marc AS $func$
348 output vandelay.flat_marc%ROWTYPE;
351 FOR field IN SELECT * FROM vandelay.flay_marc( marc ) LOOP
352 output.ind1 := field.ind1;
353 output.ind2 := field.ind2;
354 output.tag := field.tag;
355 output.subfield := field.subfield;
356 IF field.subfield IS NOT NULL AND field.tag NOT IN ('020','022','024') THEN -- exclude standard numbers and control fields
357 output.value := naco_normalize(field.value, field.subfield);
359 output.value := field.value;
362 CONTINUE WHEN output.value IS NULL;
367 $func$ LANGUAGE PLPGSQL;
369 CREATE OR REPLACE FUNCTION vandelay.incoming_record_quality ( xml TEXT, match_set_id INT ) RETURNS INT AS $_$
373 test vandelay.match_set_quality%ROWTYPE;
376 FOR test IN SELECT * FROM vandelay.match_set_quality WHERE match_set = match_set_id LOOP
377 IF test.tag IS NOT NULL THEN
378 FOR rvalue IN SELECT value FROM vandelay.flatten_marc( xml ) WHERE tag = test.tag AND subfield = test.subfield LOOP
379 IF test.value = rvalue THEN
380 out_q := out_q + test.quality;
383 ELSIF test.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
384 IF test.value = vandelay.marc21_extract_fixed_field(xml, test.fixed_field) THEN
385 out_q := out_q + test.quality;
392 $_$ LANGUAGE PLPGSQL;
394 CREATE TYPE vandelay.tcn_data AS (tcn TEXT, tcn_source TEXT, used BOOL);
395 CREATE OR REPLACE FUNCTION vandelay.find_bib_tcn_data ( xml TEXT ) RETURNS SETOF vandelay.tcn_data AS $_$
399 output vandelay.tcn_data%ROWTYPE;
403 eg_tcn := BTRIM((oils_xpath('//*[@tag="001"]/text()',xml))[1]);
404 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
406 eg_tcn_source := BTRIM((oils_xpath('//*[@tag="003"]/text()',xml))[1]);
407 IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN
408 eg_tcn_source := 'System Local';
411 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
414 output.used := FALSE;
419 output.tcn := eg_tcn;
420 output.tcn_source := eg_tcn_source;
426 eg_tcn := BTRIM((oils_xpath('//*[@tag="901"]/*[@code="a"]/text()',xml))[1]);
427 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
429 eg_tcn_source := BTRIM((oils_xpath('//*[@tag="901"]/*[@code="b"]/text()',xml))[1]);
430 IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN
431 eg_tcn_source := 'System Local';
434 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
437 output.used := FALSE;
442 output.tcn := eg_tcn;
443 output.tcn_source := eg_tcn_source;
449 eg_tcn := BTRIM((oils_xpath('//*[@tag="039"]/*[@code="a"]/text()',xml))[1]);
450 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
452 eg_tcn_source := BTRIM((oils_xpath('//*[@tag="039"]/*[@code="b"]/text()',xml))[1]);
453 IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN
454 eg_tcn_source := 'System Local';
457 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
460 output.used := FALSE;
465 output.tcn := eg_tcn;
466 output.tcn_source := eg_tcn_source;
472 eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="020"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$);
473 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
475 eg_tcn_source := 'ISBN';
477 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
480 output.used := FALSE;
485 output.tcn := eg_tcn;
486 output.tcn_source := eg_tcn_source;
492 eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="022"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$);
493 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
495 eg_tcn_source := 'ISSN';
497 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
500 output.used := FALSE;
505 output.tcn := eg_tcn;
506 output.tcn_source := eg_tcn_source;
512 eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="010"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$);
513 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
515 eg_tcn_source := 'LCCN';
517 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
520 output.used := FALSE;
525 output.tcn := eg_tcn;
526 output.tcn_source := eg_tcn_source;
532 eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="035"]/*[@code="a"]/text()',xml))[1], $re$^.*?(\w+)$$re$, $re$\1$re$);
533 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
535 eg_tcn_source := 'System Legacy';
537 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
540 output.used := FALSE;
545 output.tcn := eg_tcn;
546 output.tcn_source := eg_tcn_source;
553 $_$ LANGUAGE PLPGSQL;
555 CREATE OR REPLACE FUNCTION vandelay.add_field ( target_xml TEXT, source_xml TEXT, field TEXT, force_add INT ) RETURNS TEXT AS $_$
558 use MARC::File::XML (BinaryEncoding => 'UTF-8');
562 MARC::Charset->assume_unicode(1);
564 my $target_xml = shift;
565 my $source_xml = shift;
566 my $field_spec = shift;
567 my $force_add = shift || 0;
569 my $target_r = MARC::Record->new_from_xml( $target_xml );
570 my $source_r = MARC::Record->new_from_xml( $source_xml );
572 return $target_xml unless ($target_r && $source_r);
574 my @field_list = split(',', $field_spec);
577 for my $f (@field_list) {
578 $f =~ s/^\s*//; $f =~ s/\s*$//;
579 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
585 $match =~ s/^\s*//; $match =~ s/\s*$//;
586 $fields{$field} = { sf => [ split('', $sf) ] };
588 my ($msf,$mre) = split('~', $match);
589 if (length($msf) > 0 and length($mre) > 0) {
590 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
591 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
592 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
598 for my $f ( keys %fields) {
599 if ( @{$fields{$f}{sf}} ) {
600 for my $from_field ($source_r->field( $f )) {
601 my @tos = $target_r->field( $f );
603 next if (exists($fields{$f}{match}) and !$force_add);
604 my @new_fields = map { $_->clone } $source_r->field( $f );
605 $target_r->insert_fields_ordered( @new_fields );
607 for my $to_field (@tos) {
608 if (exists($fields{$f}{match})) {
609 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
611 my @new_sf = map { ($_ => $from_field->subfield($_)) } @{$fields{$f}{sf}};
612 $to_field->add_subfields( @new_sf );
617 my @new_fields = map { $_->clone } $source_r->field( $f );
618 $target_r->insert_fields_ordered( @new_fields );
622 $target_xml = $target_r->as_xml_record;
623 $target_xml =~ s/^<\?.+?\?>$//mo;
624 $target_xml =~ s/\n//sgo;
625 $target_xml =~ s/>\s+</></sgo;
629 $_$ LANGUAGE PLPERLU;
631 CREATE OR REPLACE FUNCTION vandelay.add_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
632 SELECT vandelay.add_field( $1, $2, $3, 0 );
635 CREATE OR REPLACE FUNCTION vandelay.strip_field ( xml TEXT, field TEXT ) RETURNS TEXT AS $_$
638 use MARC::File::XML (BinaryEncoding => 'UTF-8');
642 MARC::Charset->assume_unicode(1);
645 my $r = MARC::Record->new_from_xml( $xml );
647 return $xml unless ($r);
649 my $field_spec = shift;
650 my @field_list = split(',', $field_spec);
653 for my $f (@field_list) {
654 $f =~ s/^\s*//; $f =~ s/\s*$//;
655 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
661 $match =~ s/^\s*//; $match =~ s/\s*$//;
662 $fields{$field} = { sf => [ split('', $sf) ] };
664 my ($msf,$mre) = split('~', $match);
665 if (length($msf) > 0 and length($mre) > 0) {
666 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
667 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
668 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
674 for my $f ( keys %fields) {
675 for my $to_field ($r->field( $f )) {
676 if (exists($fields{$f}{match})) {
677 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
680 if ( @{$fields{$f}{sf}} ) {
681 $to_field->delete_subfield(code => $fields{$f}{sf});
683 $r->delete_field( $to_field );
688 $xml = $r->as_xml_record;
689 $xml =~ s/^<\?.+?\?>$//mo;
691 $xml =~ s/>\s+</></sgo;
695 $_$ LANGUAGE PLPERLU;
697 CREATE OR REPLACE FUNCTION vandelay.replace_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
704 parsed_target := vandelay.strip_field( target_xml, ''); -- this dance normalizes the format of the xml for the IF below
706 FOR curr_field IN SELECT UNNEST( STRING_TO_ARRAY(field, ',') ) LOOP -- naive split, but it's the same we use in the perl
708 xml_output := vandelay.strip_field( parsed_target, curr_field);
710 IF xml_output <> parsed_target AND curr_field ~ E'~' THEN
711 -- we removed something, and there was a regexp restriction in the curr_field definition, so proceed
712 xml_output := vandelay.add_field( xml_output, source_xml, curr_field, 1 );
713 ELSIF curr_field !~ E'~' THEN
714 -- No regexp restriction, add the curr_field
715 xml_output := vandelay.add_field( xml_output, source_xml, curr_field, 0 );
718 parsed_target := xml_output; -- in prep for any following loop iterations
724 $_$ LANGUAGE PLPGSQL;
726 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 $_$
727 SELECT vandelay.replace_field( vandelay.add_field( vandelay.strip_field( $1, $5) , $2, $3 ), $2, $4);
730 CREATE TYPE vandelay.compile_profile AS (add_rule TEXT, replace_rule TEXT, preserve_rule TEXT, strip_rule TEXT);
731 CREATE OR REPLACE FUNCTION vandelay.compile_profile ( incoming_xml TEXT ) RETURNS vandelay.compile_profile AS $_$
733 output vandelay.compile_profile%ROWTYPE;
734 profile vandelay.merge_profile%ROWTYPE;
736 profile_tmpl_owner TEXT;
738 strip_rule TEXT := '';
739 replace_rule TEXT := '';
740 preserve_rule TEXT := '';
744 profile_tmpl := (oils_xpath('//*[@tag="905"]/*[@code="t"]/text()',incoming_xml))[1];
745 profile_tmpl_owner := (oils_xpath('//*[@tag="905"]/*[@code="o"]/text()',incoming_xml))[1];
747 IF profile_tmpl IS NOT NULL AND profile_tmpl <> '' AND profile_tmpl_owner IS NOT NULL AND profile_tmpl_owner <> '' THEN
748 SELECT p.* INTO profile
749 FROM vandelay.merge_profile p
750 JOIN actor.org_unit u ON (u.id = p.owner)
751 WHERE p.name = profile_tmpl
752 AND u.shortname = profile_tmpl_owner;
754 IF profile.id IS NOT NULL THEN
755 add_rule := COALESCE(profile.add_spec,'');
756 strip_rule := COALESCE(profile.strip_spec,'');
757 replace_rule := COALESCE(profile.replace_spec,'');
758 preserve_rule := COALESCE(profile.preserve_spec,'');
762 add_rule := add_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="a"]/text()',incoming_xml),','),'');
763 strip_rule := strip_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="d"]/text()',incoming_xml),','),'');
764 replace_rule := replace_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="r"]/text()',incoming_xml),','),'');
765 preserve_rule := preserve_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="p"]/text()',incoming_xml),','),'');
767 output.add_rule := BTRIM(add_rule,',');
768 output.replace_rule := BTRIM(replace_rule,',');
769 output.strip_rule := BTRIM(strip_rule,',');
770 output.preserve_rule := BTRIM(preserve_rule,',');
774 $_$ LANGUAGE PLPGSQL;
776 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
778 merge_profile vandelay.merge_profile%ROWTYPE;
779 dyn_profile vandelay.compile_profile%ROWTYPE;
789 SELECT b.marc INTO eg_marc
790 FROM biblio.record_entry b
794 IF eg_marc IS NULL OR v_marc IS NULL THEN
795 -- RAISE NOTICE 'no marc for template or bib record';
799 dyn_profile := vandelay.compile_profile( v_marc );
801 IF merge_profile_id IS NOT NULL THEN
802 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
804 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
805 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
806 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
807 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
811 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
812 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
816 IF dyn_profile.replace_rule <> '' THEN
817 source_marc = v_marc;
818 target_marc = eg_marc;
819 replace_rule = dyn_profile.replace_rule;
821 source_marc = eg_marc;
822 target_marc = v_marc;
823 replace_rule = dyn_profile.preserve_rule;
826 UPDATE biblio.record_entry
827 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
831 -- RAISE NOTICE 'update of biblio.record_entry failed';
840 CREATE OR REPLACE FUNCTION vandelay.merge_record_xml ( target_marc TEXT, template_marc TEXT ) RETURNS TEXT AS $$
842 dyn_profile vandelay.compile_profile%ROWTYPE;
850 IF target_marc IS NULL OR template_marc IS NULL THEN
851 -- RAISE NOTICE 'no marc for target or template record';
855 dyn_profile := vandelay.compile_profile( template_marc );
857 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
858 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
862 IF dyn_profile.replace_rule <> '' THEN
863 trgt_marc = target_marc;
864 tmpl_marc = template_marc;
865 replace_rule = dyn_profile.replace_rule;
867 tmp_marc = target_marc;
868 trgt_marc = template_marc;
869 tmpl_marc = tmp_marc;
870 replace_rule = dyn_profile.preserve_rule;
873 RETURN vandelay.merge_record_xml( trgt_marc, tmpl_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule );
878 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT) RETURNS BOOL AS $$
879 SELECT vandelay.template_overlay_bib_record( $1, $2, NULL);
882 CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
884 merge_profile vandelay.merge_profile%ROWTYPE;
885 dyn_profile vandelay.compile_profile%ROWTYPE;
896 SELECT q.marc INTO v_marc
897 FROM vandelay.queued_record q
898 JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
901 IF v_marc IS NULL THEN
902 -- RAISE NOTICE 'no marc for vandelay or bib record';
906 IF vandelay.template_overlay_bib_record( v_marc, eg_id, merge_profile_id) THEN
907 UPDATE vandelay.queued_bib_record
908 SET imported_as = eg_id,
910 WHERE id = import_id;
912 editor_string := (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1];
914 IF editor_string IS NOT NULL AND editor_string <> '' THEN
915 SELECT usr INTO editor_id FROM actor.card WHERE barcode = editor_string;
917 IF editor_id IS NULL THEN
918 SELECT id INTO editor_id FROM actor.usr WHERE usrname = editor_string;
921 IF editor_id IS NOT NULL THEN
922 UPDATE biblio.record_entry SET editor = editor_id WHERE id = eg_id;
929 -- RAISE NOTICE 'update of biblio.record_entry failed';
936 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
940 match_attr vandelay.bib_attr_definition%ROWTYPE;
943 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
946 -- RAISE NOTICE 'already imported, cannot auto-overlay'
950 SELECT COUNT(*) INTO match_count FROM vandelay.bib_match WHERE queued_record = import_id;
952 IF match_count <> 1 THEN
953 -- RAISE NOTICE 'not an exact match';
957 SELECT d.* INTO match_attr
958 FROM vandelay.bib_attr_definition d
959 JOIN vandelay.queued_bib_record_attr a ON (a.field = d.id)
960 JOIN vandelay.bib_match m ON (m.matched_attr = a.id)
961 WHERE m.queued_record = import_id;
963 IF NOT (match_attr.xpath ~ '@tag="901"' AND match_attr.xpath ~ '@code="c"') THEN
964 -- RAISE NOTICE 'not a 901c match: %', match_attr.xpath;
968 SELECT m.eg_record INTO eg_id
969 FROM vandelay.bib_match m
970 WHERE m.queued_record = import_id
973 IF eg_id IS NULL THEN
977 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
981 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
983 queued_record vandelay.queued_bib_record%ROWTYPE;
986 FOR queued_record IN SELECT * FROM vandelay.queued_bib_record WHERE queue = queue_id AND import_time IS NULL LOOP
988 IF vandelay.auto_overlay_bib_record( queued_record.id, merge_profile_id ) THEN
989 RETURN NEXT queued_record.id;
999 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
1000 SELECT * FROM vandelay.auto_overlay_bib_queue( $1, NULL );
1003 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
1014 deposit_amount TEXT;
1027 tmp_attr_set RECORD;
1028 attr_set vandelay.import_item%ROWTYPE;
1034 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
1038 attr_set.definition := attr_def.id;
1040 -- Build the combined XPath
1044 WHEN attr_def.owning_lib IS NULL THEN 'null()'
1045 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
1046 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
1051 WHEN attr_def.circ_lib IS NULL THEN 'null()'
1052 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
1053 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
1058 WHEN attr_def.call_number IS NULL THEN 'null()'
1059 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
1060 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
1065 WHEN attr_def.copy_number IS NULL THEN 'null()'
1066 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
1067 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
1072 WHEN attr_def.status IS NULL THEN 'null()'
1073 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
1074 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
1079 WHEN attr_def.location IS NULL THEN 'null()'
1080 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
1081 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
1086 WHEN attr_def.circulate IS NULL THEN 'null()'
1087 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
1088 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
1093 WHEN attr_def.deposit IS NULL THEN 'null()'
1094 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
1095 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
1100 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
1101 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
1102 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
1107 WHEN attr_def.ref IS NULL THEN 'null()'
1108 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
1109 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
1114 WHEN attr_def.holdable IS NULL THEN 'null()'
1115 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
1116 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
1121 WHEN attr_def.price IS NULL THEN 'null()'
1122 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
1123 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
1128 WHEN attr_def.barcode IS NULL THEN 'null()'
1129 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
1130 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
1135 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
1136 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
1137 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
1142 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
1143 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
1144 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
1149 WHEN attr_def.alert_message IS NULL THEN 'null()'
1150 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
1151 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
1156 WHEN attr_def.opac_visible IS NULL THEN 'null()'
1157 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
1158 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
1163 WHEN attr_def.pub_note IS NULL THEN 'null()'
1164 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
1165 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
1169 WHEN attr_def.priv_note IS NULL THEN 'null()'
1170 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
1171 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
1176 owning_lib || '|' ||
1178 call_number || '|' ||
1179 copy_number || '|' ||
1184 deposit_amount || '|' ||
1189 circ_modifier || '|' ||
1190 circ_as_type || '|' ||
1191 alert_message || '|' ||
1196 -- RAISE NOTICE 'XPath: %', xpath;
1200 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
1201 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
1202 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
1203 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, opac_vis TEXT )
1206 tmp_attr_set.pr = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
1207 tmp_attr_set.dep_amount = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
1209 tmp_attr_set.pr := NULLIF( tmp_attr_set.pr, '' );
1210 tmp_attr_set.dep_amount := NULLIF( tmp_attr_set.dep_amount, '' );
1212 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
1213 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
1214 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
1216 SELECT id INTO attr_set.location
1217 FROM asset.copy_location
1218 WHERE LOWER(name) = LOWER(tmp_attr_set.cl)
1219 AND asset.copy_location.owning_lib = COALESCE(attr_set.owning_lib, attr_set.circ_lib); -- INT
1221 attr_set.circulate :=
1222 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
1223 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
1226 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
1227 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
1229 attr_set.holdable :=
1230 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
1231 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
1233 attr_set.opac_visible :=
1234 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
1235 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
1238 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
1239 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
1241 attr_set.copy_number := tmp_attr_set.cnum::INT; -- INT,
1242 attr_set.deposit_amount := tmp_attr_set.dep_amount::NUMERIC(6,2); -- NUMERIC(6,2),
1243 attr_set.price := tmp_attr_set.pr::NUMERIC(8,2); -- NUMERIC(8,2),
1245 attr_set.call_number := tmp_attr_set.cn; -- TEXT
1246 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
1247 attr_set.circ_modifier := tmp_attr_set.circ_mod; -- TEXT,
1248 attr_set.circ_as_type := tmp_attr_set.circ_as; -- TEXT,
1249 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
1250 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
1251 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
1252 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
1254 RETURN NEXT attr_set;
1263 $$ LANGUAGE PLPGSQL;
1266 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$
1272 FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP
1274 SELECT extract_marc_field('vandelay.queued_bib_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_bib_record WHERE id = NEW.id;
1275 IF (value IS NOT NULL AND value <> '') THEN
1276 INSERT INTO vandelay.queued_bib_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
1283 $$ LANGUAGE PLPGSQL;
1285 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
1288 item_data vandelay.import_item%ROWTYPE;
1291 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
1293 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
1294 INSERT INTO vandelay.import_item (
1318 item_data.definition,
1319 item_data.owning_lib,
1321 item_data.call_number,
1322 item_data.copy_number,
1325 item_data.circulate,
1327 item_data.deposit_amount,
1332 item_data.circ_modifier,
1333 item_data.circ_as_type,
1334 item_data.alert_message,
1336 item_data.priv_note,
1337 item_data.opac_visible
1343 $func$ LANGUAGE PLPGSQL;
1345 CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
1354 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
1356 SELECT * INTO attr_def FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1;
1358 IF attr_def IS NOT NULL AND attr_def.id IS NOT NULL THEN
1359 id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr_def.xpath, attr_def.remove);
1361 IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN
1362 SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted;
1363 SELECT * INTO attr FROM vandelay.queued_bib_record_attr WHERE record = NEW.id and field = attr_def.id LIMIT 1;
1364 IF exact_id IS NOT NULL THEN
1365 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id);
1370 IF exact_id IS NULL THEN
1371 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
1373 -- All numbers? check for an id match
1374 IF (attr.attr_value ~ $r$^\d+$$r$) THEN
1375 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP
1376 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1380 -- Looks like an ISBN? check for an isbn match
1381 IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN
1382 FOR eg_rec IN EXECUTE $$SELECT * FROM metabib.full_rec fr WHERE fr.value LIKE evergreen.lowercase('$$ || attr.attr_value || $$%') AND fr.tag = '020' AND fr.subfield = 'a'$$ LOOP
1383 PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE;
1385 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
1389 -- subcheck for isbn-as-tcn
1390 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP
1391 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1395 -- check for an OCLC tcn_value match
1396 IF (attr.attr_value ~ $r$^o\d+$$r$) THEN
1397 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP
1398 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1402 -- check for a direct tcn_value match
1403 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP
1404 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1407 -- check for a direct item barcode match
1410 FROM biblio.record_entry b
1411 JOIN asset.call_number cn ON (cn.record = b.id)
1412 JOIN asset.copy cp ON (cp.call_number = cn.id)
1413 WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE
1415 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1423 $func$ LANGUAGE PLPGSQL;
1425 CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$
1427 DELETE FROM vandelay.queued_bib_record_attr WHERE record = OLD.id;
1428 DELETE FROM vandelay.import_item WHERE record = OLD.id;
1430 IF TG_OP = 'UPDATE' THEN
1435 $$ LANGUAGE PLPGSQL;
1437 CREATE TRIGGER cleanup_bib_trigger
1438 BEFORE UPDATE OR DELETE ON vandelay.queued_bib_record
1439 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_bib_marc();
1441 CREATE TRIGGER ingest_bib_trigger
1442 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1443 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_marc();
1445 CREATE TRIGGER ingest_item_trigger
1446 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1447 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_items();
1449 CREATE TRIGGER zz_match_bibs_trigger
1450 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1451 FOR EACH ROW EXECUTE PROCEDURE vandelay.match_bib_record();
1454 /* Authority stuff down here */
1455 ---------------------------------------
1456 CREATE TABLE vandelay.authority_attr_definition (
1457 id SERIAL PRIMARY KEY,
1458 code TEXT UNIQUE NOT NULL,
1460 xpath TEXT NOT NULL,
1461 remove TEXT NOT NULL DEFAULT '',
1462 ident BOOL NOT NULL DEFAULT FALSE
1465 CREATE TABLE vandelay.authority_queue (
1466 queue_type TEXT NOT NULL DEFAULT 'authority' CHECK (queue_type = 'authority'),
1467 CONSTRAINT vand_authority_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
1468 ) INHERITS (vandelay.queue);
1469 ALTER TABLE vandelay.authority_queue ADD PRIMARY KEY (id);
1471 CREATE TABLE vandelay.queued_authority_record (
1472 queue INT NOT NULL REFERENCES vandelay.authority_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1473 imported_as INT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1474 ) INHERITS (vandelay.queued_record);
1475 ALTER TABLE vandelay.queued_authority_record ADD PRIMARY KEY (id);
1476 CREATE INDEX queued_authority_record_queue_idx ON vandelay.queued_authority_record (queue);
1478 CREATE TABLE vandelay.queued_authority_record_attr (
1479 id BIGSERIAL PRIMARY KEY,
1480 record BIGINT NOT NULL REFERENCES vandelay.queued_authority_record (id) DEFERRABLE INITIALLY DEFERRED,
1481 field INT NOT NULL REFERENCES vandelay.authority_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
1482 attr_value TEXT NOT NULL
1484 CREATE INDEX queued_authority_record_attr_record_idx ON vandelay.queued_authority_record_attr (record);
1486 CREATE TABLE vandelay.authority_match (
1487 id BIGSERIAL PRIMARY KEY,
1488 matched_attr INT REFERENCES vandelay.queued_authority_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1489 queued_record BIGINT REFERENCES vandelay.queued_authority_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1490 eg_record BIGINT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1493 CREATE OR REPLACE FUNCTION vandelay.ingest_authority_marc ( ) RETURNS TRIGGER AS $$
1499 FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP
1501 SELECT extract_marc_field('vandelay.queued_authority_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_authority_record WHERE id = NEW.id;
1502 IF (value IS NOT NULL AND value <> '') THEN
1503 INSERT INTO vandelay.queued_authority_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
1510 $$ LANGUAGE PLPGSQL;
1512 CREATE OR REPLACE FUNCTION vandelay.cleanup_authority_marc ( ) RETURNS TRIGGER AS $$
1514 DELETE FROM vandelay.queued_authority_record_attr WHERE record = OLD.id;
1515 IF TG_OP = 'UPDATE' THEN
1520 $$ LANGUAGE PLPGSQL;
1522 CREATE TRIGGER cleanup_authority_trigger
1523 BEFORE UPDATE OR DELETE ON vandelay.queued_authority_record
1524 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_authority_marc();
1526 CREATE TRIGGER ingest_authority_trigger
1527 AFTER INSERT OR UPDATE ON vandelay.queued_authority_record
1528 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_authority_marc();
1530 CREATE OR REPLACE FUNCTION vandelay.overlay_authority_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1532 merge_profile vandelay.merge_profile%ROWTYPE;
1533 dyn_profile vandelay.compile_profile%ROWTYPE;
1542 SELECT b.marc INTO eg_marc
1543 FROM authority.record_entry b
1544 JOIN vandelay.authority_match m ON (m.eg_record = b.id AND m.queued_record = import_id)
1547 SELECT q.marc INTO v_marc
1548 FROM vandelay.queued_record q
1549 JOIN vandelay.authority_match m ON (m.queued_record = q.id AND q.id = import_id)
1552 IF eg_marc IS NULL OR v_marc IS NULL THEN
1553 -- RAISE NOTICE 'no marc for vandelay or authority record';
1557 dyn_profile := vandelay.compile_profile( v_marc );
1559 IF merge_profile_id IS NOT NULL THEN
1560 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
1562 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
1563 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
1564 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
1565 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
1569 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
1570 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
1574 IF dyn_profile.replace_rule <> '' THEN
1575 source_marc = v_marc;
1576 target_marc = eg_marc;
1577 replace_rule = dyn_profile.replace_rule;
1579 source_marc = eg_marc;
1580 target_marc = v_marc;
1581 replace_rule = dyn_profile.preserve_rule;
1584 UPDATE authority.record_entry
1585 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
1589 UPDATE vandelay.queued_authority_record
1590 SET imported_as = eg_id,
1592 WHERE id = import_id;
1596 -- RAISE NOTICE 'update of authority.record_entry failed';
1601 $$ LANGUAGE PLPGSQL;
1603 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1608 SELECT COUNT(*) INTO match_count FROM vandelay.authority_match WHERE queued_record = import_id;
1610 IF match_count <> 1 THEN
1611 -- RAISE NOTICE 'not an exact match';
1615 SELECT m.eg_record INTO eg_id
1616 FROM vandelay.authority_match m
1617 WHERE m.queued_record = import_id
1620 IF eg_id IS NULL THEN
1624 RETURN vandelay.overlay_authority_record( import_id, eg_id, merge_profile_id );
1626 $$ LANGUAGE PLPGSQL;
1628 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
1630 queued_record vandelay.queued_authority_record%ROWTYPE;
1633 FOR queued_record IN SELECT * FROM vandelay.queued_authority_record WHERE queue = queue_id AND import_time IS NULL LOOP
1635 IF vandelay.auto_overlay_authority_record( queued_record.id, merge_profile_id ) THEN
1636 RETURN NEXT queued_record.id;
1644 $$ LANGUAGE PLPGSQL;
1646 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
1647 SELECT * FROM vandelay.auto_overlay_authority_queue( $1, NULL );
1651 -- Vandelay (for importing and exporting records) 012.schema.vandelay.sql
1652 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES (1, 'title', oils_i18n_gettext(1, 'vqbrad', 'Title of work', 'description'),'//*[@tag="245"]/*[contains("abcmnopr",@code)]');
1653 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES (2, 'author', oils_i18n_gettext(1, 'vqbrad', 'Author of work', 'description'),'//*[@tag="100" or @tag="110" or @tag="113"]/*[contains("ad",@code)]');
1654 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES (3, 'language', oils_i18n_gettext(3, 'vqbrad', 'Language of work', 'description'),'//*[@tag="240"]/*[@code="l"][1]');
1655 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES (4, 'pagination', oils_i18n_gettext(4, 'vqbrad', 'Pagination', 'description'),'//*[@tag="300"]/*[@code="a"][1]');
1656 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath, ident, remove ) VALUES (5, 'isbn',oils_i18n_gettext(5, 'vqbrad', 'ISBN', 'description'),'//*[@tag="020"]/*[@code="a"]', TRUE, $r$(?:-|\s.+$)$r$);
1657 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath, ident, remove ) VALUES (6, 'issn',oils_i18n_gettext(6, 'vqbrad', 'ISSN', 'description'),'//*[@tag="022"]/*[@code="a"]', TRUE, $r$(?:-|\s.+$)$r$);
1658 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES (7, 'price',oils_i18n_gettext(7, 'vqbrad', 'Price', 'description'),'//*[@tag="020" or @tag="022"]/*[@code="c"][1]');
1659 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath, ident ) VALUES (8, 'rec_identifier',oils_i18n_gettext(8, 'vqbrad', 'Accession Number', 'description'),'//*[@tag="001"]', TRUE);
1660 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath, ident ) VALUES (9, 'eg_tcn',oils_i18n_gettext(9, 'vqbrad', 'TCN Value', 'description'),'//*[@tag="901"]/*[@code="a"]', TRUE);
1661 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath, ident ) VALUES (10, 'eg_tcn_source',oils_i18n_gettext(10, 'vqbrad', 'TCN Source', 'description'),'//*[@tag="901"]/*[@code="b"]', TRUE);
1662 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath, ident ) VALUES (11, 'eg_identifier',oils_i18n_gettext(11, 'vqbrad', 'Internal ID', 'description'),'//*[@tag="901"]/*[@code="c"]', TRUE);
1663 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES (12, 'publisher',oils_i18n_gettext(12, 'vqbrad', 'Publisher', 'description'),'//*[@tag="260"]/*[@code="b"][1]');
1664 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath, remove ) VALUES (13, 'pubdate',oils_i18n_gettext(13, 'vqbrad', 'Publication Date', 'description'),'//*[@tag="260"]/*[@code="c"][1]',$r$\D$r$);
1665 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES (14, 'edition',oils_i18n_gettext(14, 'vqbrad', 'Edition', 'description'),'//*[@tag="250"]/*[@code="a"][1]');
1667 --INSERT INTO vandelay.import_item_attr_definition (
1668 -- owner, name, tag, owning_lib, circ_lib, location,
1669 -- call_number, circ_modifier, barcode, price, copy_number,
1670 -- circulate, ref, holdable, opac_visible, status
1673 -- 'Evergreen 852 export format',
1675 -- '[@code = "b"][1]',
1676 -- '[@code = "b"][2]',
1683 -- '[@code = "x" and text() = "circulating"]',
1684 -- '[@code = "x" and text() = "reference"]',
1685 -- '[@code = "x" and text() = "holdable"]',
1686 -- '[@code = "x" and text() = "visible"]',
1690 --INSERT INTO vandelay.import_item_attr_definition (
1703 -- 'Unicorn Import format -- 999',
1714 --INSERT INTO vandelay.authority_attr_definition ( code, description, xpath, ident ) VALUES ('rec_identifier','Identifier','//*[@tag="001"]', TRUE);