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')),
26 /* Bib stuff at the top */
27 ----------------------------------------------------
29 CREATE TABLE vandelay.bib_attr_definition (
30 id SERIAL PRIMARY KEY,
31 code TEXT UNIQUE NOT NULL,
34 remove TEXT NOT NULL DEFAULT '',
35 ident BOOL NOT NULL DEFAULT FALSE
38 -- Each TEXT field (other than 'name') should hold an XPath predicate for pulling the data needed
39 -- DROP TABLE vandelay.import_item_attr_definition CASCADE;
40 CREATE TABLE vandelay.import_item_attr_definition (
41 id BIGSERIAL PRIMARY KEY,
42 owner INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
45 keep BOOL NOT NULL DEFAULT FALSE,
67 CONSTRAINT vand_import_item_attr_def_idx UNIQUE (owner,name)
70 CREATE TABLE vandelay.bib_queue (
71 queue_type TEXT NOT NULL DEFAULT 'bib' CHECK (queue_type = 'bib'),
72 item_attr_def BIGINT REFERENCES vandelay.import_item_attr_definition (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
73 CONSTRAINT vand_bib_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
74 ) INHERITS (vandelay.queue);
75 ALTER TABLE vandelay.bib_queue ADD PRIMARY KEY (id);
77 CREATE TABLE vandelay.queued_bib_record (
78 queue INT NOT NULL REFERENCES vandelay.bib_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
79 bib_source INT REFERENCES config.bib_source (id) DEFERRABLE INITIALLY DEFERRED,
80 imported_as BIGINT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED
81 ) INHERITS (vandelay.queued_record);
82 ALTER TABLE vandelay.queued_bib_record ADD PRIMARY KEY (id);
83 CREATE INDEX queued_bib_record_queue_idx ON vandelay.queued_bib_record (queue);
85 CREATE TABLE vandelay.queued_bib_record_attr (
86 id BIGSERIAL PRIMARY KEY,
87 record BIGINT NOT NULL REFERENCES vandelay.queued_bib_record (id) DEFERRABLE INITIALLY DEFERRED,
88 field INT NOT NULL REFERENCES vandelay.bib_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
89 attr_value TEXT NOT NULL
91 CREATE INDEX queued_bib_record_attr_record_idx ON vandelay.queued_bib_record_attr (record);
93 CREATE TABLE vandelay.bib_match (
94 id BIGSERIAL PRIMARY KEY,
95 field_type TEXT NOT NULL CHECK (field_type in ('isbn','tcn_value','id')),
96 matched_attr INT REFERENCES vandelay.queued_bib_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
97 queued_record BIGINT REFERENCES vandelay.queued_bib_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
98 eg_record BIGINT REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
101 -- DROP TABLE vandelay.import_item CASCADE;
102 CREATE TABLE vandelay.import_item (
103 id BIGSERIAL PRIMARY KEY,
104 record BIGINT NOT NULL REFERENCES vandelay.queued_bib_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
105 definition BIGINT NOT NULL REFERENCES vandelay.import_item_attr_definition (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
114 deposit_amount NUMERIC(8,2),
127 CREATE TABLE vandelay.import_bib_trash_fields (
128 id BIGSERIAL PRIMARY KEY,
129 owner INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
131 CONSTRAINT vand_import_bib_trash_fields_idx UNIQUE (owner,field)
134 CREATE TABLE vandelay.merge_profile (
135 id BIGSERIAL PRIMARY KEY,
136 owner INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
142 CONSTRAINT vand_merge_prof_owner_name_idx UNIQUE (owner,name),
143 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))
147 CREATE TYPE vandelay.tcn_data AS (tcn TEXT, tcn_source TEXT, used BOOL);
148 CREATE OR REPLACE FUNCTION vandelay.find_bib_tcn_data ( xml TEXT ) RETURNS SETOF vandelay.tcn_data AS $_$
152 output vandelay.tcn_data%ROWTYPE;
156 eg_tcn := BTRIM((oils_xpath('//*[@tag="001"]/text()',xml))[1]);
157 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
159 eg_tcn_source := BTRIM((oils_xpath('//*[@tag="003"]/text()',xml))[1]);
160 IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN
161 eg_tcn_source := 'System Local';
164 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
167 output.used := FALSE;
172 output.tcn := eg_tcn;
173 output.tcn_source := eg_tcn_source;
179 eg_tcn := BTRIM((oils_xpath('//*[@tag="901"]/*[@code="a"]/text()',xml))[1]);
180 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
182 eg_tcn_source := BTRIM((oils_xpath('//*[@tag="901"]/*[@code="b"]/text()',xml))[1]);
183 IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN
184 eg_tcn_source := 'System Local';
187 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
190 output.used := FALSE;
195 output.tcn := eg_tcn;
196 output.tcn_source := eg_tcn_source;
202 eg_tcn := BTRIM((oils_xpath('//*[@tag="039"]/*[@code="a"]/text()',xml))[1]);
203 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
205 eg_tcn_source := BTRIM((oils_xpath('//*[@tag="039"]/*[@code="b"]/text()',xml))[1]);
206 IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN
207 eg_tcn_source := 'System Local';
210 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
213 output.used := FALSE;
218 output.tcn := eg_tcn;
219 output.tcn_source := eg_tcn_source;
225 eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="020"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$);
226 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
228 eg_tcn_source := 'ISBN';
230 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
233 output.used := FALSE;
238 output.tcn := eg_tcn;
239 output.tcn_source := eg_tcn_source;
245 eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="022"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$);
246 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
248 eg_tcn_source := 'ISSN';
250 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
253 output.used := FALSE;
258 output.tcn := eg_tcn;
259 output.tcn_source := eg_tcn_source;
265 eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="010"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$);
266 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
268 eg_tcn_source := 'LCCN';
270 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
273 output.used := FALSE;
278 output.tcn := eg_tcn;
279 output.tcn_source := eg_tcn_source;
285 eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="035"]/*[@code="a"]/text()',xml))[1], $re$^.*?(\w+)$$re$, $re$\1$re$);
286 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
288 eg_tcn_source := 'System Legacy';
290 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
293 output.used := FALSE;
298 output.tcn := eg_tcn;
299 output.tcn_source := eg_tcn_source;
306 $_$ LANGUAGE PLPGSQL;
308 CREATE OR REPLACE FUNCTION vandelay.add_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
311 use MARC::File::XML (BinaryEncoding => 'UTF-8');
314 my $target_xml = shift;
315 my $source_xml = shift;
316 my $field_spec = shift;
318 my $target_r = MARC::Record->new_from_xml( $target_xml );
319 my $source_r = MARC::Record->new_from_xml( $source_xml );
321 return $target_xml unless ($target_r && $source_r);
323 my @field_list = split(',', $field_spec);
326 for my $f (@field_list) {
327 $f =~ s/^\s*//; $f =~ s/\s*$//;
328 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
334 $match =~ s/^\s*//; $match =~ s/\s*$//;
335 $fields{$field} = { sf => [ split('', $sf) ] };
337 my ($msf,$mre) = split('~', $match);
338 if (length($msf) > 0 and length($mre) > 0) {
339 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
340 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
341 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
347 for my $f ( keys %fields) {
348 if ( @{$fields{$f}{sf}} ) {
349 for my $from_field ($source_r->field( $f )) {
350 my @tos = $target_r->field( $f );
352 next if (exists($fields{$f}{match}));
353 my @new_fields = map { $_->clone } $source_r->field( $f );
354 $target_r->insert_fields_ordered( @new_fields );
356 for my $to_field (@tos) {
357 if (exists($fields{$f}{match})) {
358 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
360 my @new_sf = map { ($_ => $from_field->subfield($_)) } @{$fields{$f}{sf}};
361 $to_field->add_subfields( @new_sf );
366 my @new_fields = map { $_->clone } $source_r->field( $f );
367 $target_r->insert_fields_ordered( @new_fields );
371 $target_xml = $target_r->as_xml_record;
372 $target_xml =~ s/^<\?.+?\?>$//mo;
373 $target_xml =~ s/\n//sgo;
374 $target_xml =~ s/>\s+</></sgo;
378 $_$ LANGUAGE PLPERLU;
380 CREATE OR REPLACE FUNCTION vandelay.strip_field ( xml TEXT, field TEXT ) RETURNS TEXT AS $_$
383 use MARC::File::XML (BinaryEncoding => 'UTF-8');
387 my $r = MARC::Record->new_from_xml( $xml );
389 return $xml unless ($r);
391 my $field_spec = shift;
392 my @field_list = split(',', $field_spec);
395 for my $f (@field_list) {
396 $f =~ s/^\s*//; $f =~ s/\s*$//;
397 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
403 $match =~ s/^\s*//; $match =~ s/\s*$//;
404 $fields{$field} = { sf => [ split('', $sf) ] };
406 my ($msf,$mre) = split('~', $match);
407 if (length($msf) > 0 and length($mre) > 0) {
408 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
409 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
410 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
416 for my $f ( keys %fields) {
417 for my $to_field ($r->field( $f )) {
418 if (exists($fields{$f}{match})) {
419 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
422 if ( @{$fields{$f}{sf}} ) {
423 $to_field->delete_subfield(code => $fields{$f}{sf});
425 $r->delete_field( $to_field );
430 $xml = $r->as_xml_record;
431 $xml =~ s/^<\?.+?\?>$//mo;
433 $xml =~ s/>\s+</></sgo;
437 $_$ LANGUAGE PLPERLU;
439 CREATE OR REPLACE FUNCTION vandelay.replace_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
440 SELECT vandelay.add_field( vandelay.strip_field( $1, $3), $2, $3 );
443 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 $_$
444 SELECT vandelay.replace_field( vandelay.add_field( vandelay.strip_field( $1, $5) , $2, $3 ), $2, $4);
447 CREATE TYPE vandelay.compile_profile AS (add_rule TEXT, replace_rule TEXT, preserve_rule TEXT, strip_rule TEXT);
448 CREATE OR REPLACE FUNCTION vandelay.compile_profile ( incoming_xml TEXT ) RETURNS vandelay.compile_profile AS $_$
450 output vandelay.compile_profile%ROWTYPE;
451 profile vandelay.merge_profile%ROWTYPE;
453 profile_tmpl_owner TEXT;
455 strip_rule TEXT := '';
456 replace_rule TEXT := '';
457 preserve_rule TEXT := '';
461 profile_tmpl := (oils_xpath('//*[@tag="905"]/*[@code="t"]/text()',incoming_xml))[1];
462 profile_tmpl_owner := (oils_xpath('//*[@tag="905"]/*[@code="o"]/text()',incoming_xml))[1];
464 IF profile_tmpl IS NOT NULL AND profile_tmpl <> '' AND profile_tmpl_owner IS NOT NULL AND profile_tmpl_owner <> '' THEN
465 SELECT p.* INTO profile
466 FROM vandelay.merge_profile p
467 JOIN actor.org_unit u ON (u.id = p.owner)
468 WHERE p.name = profile_tmpl
469 AND u.shortname = profile_tmpl_owner;
471 IF profile.id IS NOT NULL THEN
472 add_rule := COALESCE(profile.add_spec,'');
473 strip_rule := COALESCE(profile.strip_spec,'');
474 replace_rule := COALESCE(profile.replace_spec,'');
475 preserve_rule := COALESCE(profile.preserve_spec,'');
479 add_rule := add_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="a"]/text()',incoming_xml),','),'');
480 strip_rule := strip_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="d"]/text()',incoming_xml),','),'');
481 replace_rule := replace_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="r"]/text()',incoming_xml),','),'');
482 preserve_rule := preserve_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="p"]/text()',incoming_xml),','),'');
484 output.add_rule := BTRIM(add_rule,',');
485 output.replace_rule := BTRIM(replace_rule,',');
486 output.strip_rule := BTRIM(strip_rule,',');
487 output.preserve_rule := BTRIM(preserve_rule,',');
491 $_$ LANGUAGE PLPGSQL;
493 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
495 merge_profile vandelay.merge_profile%ROWTYPE;
496 dyn_profile vandelay.compile_profile%ROWTYPE;
506 SELECT b.marc INTO eg_marc
507 FROM biblio.record_entry b
511 IF eg_marc IS NULL OR v_marc IS NULL THEN
512 -- RAISE NOTICE 'no marc for template or bib record';
516 dyn_profile := vandelay.compile_profile( v_marc );
518 IF merge_profile_id IS NOT NULL THEN
519 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
521 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
522 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
523 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
524 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
528 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
529 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
533 IF dyn_profile.replace_rule <> '' THEN
534 source_marc = v_marc;
535 target_marc = eg_marc;
536 replace_rule = dyn_profile.replace_rule;
538 source_marc = eg_marc;
539 target_marc = v_marc;
540 replace_rule = dyn_profile.preserve_rule;
543 UPDATE biblio.record_entry
544 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
548 -- RAISE NOTICE 'update of biblio.record_entry failed';
557 CREATE OR REPLACE FUNCTION vandelay.merge_record_xml ( target_marc TEXT, template_marc TEXT ) RETURNS TEXT AS $$
559 dyn_profile vandelay.compile_profile%ROWTYPE;
567 IF target_marc IS NULL OR template_marc IS NULL THEN
568 -- RAISE NOTICE 'no marc for target or template record';
572 dyn_profile := vandelay.compile_profile( template_marc );
574 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
575 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
579 IF dyn_profile.replace_rule <> '' THEN
580 trgt_marc = target_marc;
581 tmpl_marc = template_marc;
582 replace_rule = dyn_profile.replace_rule;
584 tmp_marc = target_marc;
585 trgt_marc = template_marc;
586 tmpl_marc = tmp_marc;
587 replace_rule = dyn_profile.preserve_rule;
590 RETURN vandelay.merge_record_xml( trgt_marc, tmpl_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule );
595 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT) RETURNS BOOL AS $$
596 SELECT vandelay.template_overlay_bib_record( $1, $2, NULL);
599 CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
601 merge_profile vandelay.merge_profile%ROWTYPE;
602 dyn_profile vandelay.compile_profile%ROWTYPE;
613 SELECT q.marc INTO v_marc
614 FROM vandelay.queued_record q
615 JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
618 IF v_marc IS NULL THEN
619 -- RAISE NOTICE 'no marc for vandelay or bib record';
623 IF vandelay.template_overlay_bib_record( v_marc, eg_id, merge_profile_id) THEN
624 UPDATE vandelay.queued_bib_record
625 SET imported_as = eg_id,
627 WHERE id = import_id;
629 editor_string := (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1];
631 IF editor_string IS NOT NULL AND editor_string <> '' THEN
632 SELECT usr INTO editor_id FROM actor.card WHERE barcode = editor_string;
634 IF editor_id IS NULL THEN
635 SELECT id INTO editor_id FROM actor.usr WHERE usrname = editor_string;
638 IF editor_id IS NOT NULL THEN
639 UPDATE biblio.record_entry SET editor = editor_id WHERE id = eg_id;
646 -- RAISE NOTICE 'update of biblio.record_entry failed';
653 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
657 match_attr vandelay.bib_attr_definition%ROWTYPE;
660 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
663 -- RAISE NOTICE 'already imported, cannot auto-overlay'
667 SELECT COUNT(*) INTO match_count FROM vandelay.bib_match WHERE queued_record = import_id;
669 IF match_count <> 1 THEN
670 -- RAISE NOTICE 'not an exact match';
674 SELECT d.* INTO match_attr
675 FROM vandelay.bib_attr_definition d
676 JOIN vandelay.queued_bib_record_attr a ON (a.field = d.id)
677 JOIN vandelay.bib_match m ON (m.matched_attr = a.id)
678 WHERE m.queued_record = import_id;
680 IF NOT (match_attr.xpath ~ '@tag="901"' AND match_attr.xpath ~ '@code="c"') THEN
681 -- RAISE NOTICE 'not a 901c match: %', match_attr.xpath;
685 SELECT m.eg_record INTO eg_id
686 FROM vandelay.bib_match m
687 WHERE m.queued_record = import_id
690 IF eg_id IS NULL THEN
694 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
698 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
700 queued_record vandelay.queued_bib_record%ROWTYPE;
703 FOR queued_record IN SELECT * FROM vandelay.queued_bib_record WHERE queue = queue_id AND import_time IS NULL LOOP
705 IF vandelay.auto_overlay_bib_record( queued_record.id, merge_profile_id ) THEN
706 RETURN NEXT queued_record.id;
716 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
717 SELECT * FROM vandelay.auto_overlay_bib_queue( $1, NULL );
720 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
745 attr_set vandelay.import_item%ROWTYPE;
751 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
755 attr_set.definition := attr_def.id;
757 -- Build the combined XPath
761 WHEN attr_def.owning_lib IS NULL THEN 'null()'
762 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
763 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
768 WHEN attr_def.circ_lib IS NULL THEN 'null()'
769 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
770 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
775 WHEN attr_def.call_number IS NULL THEN 'null()'
776 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
777 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
782 WHEN attr_def.copy_number IS NULL THEN 'null()'
783 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
784 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
789 WHEN attr_def.status IS NULL THEN 'null()'
790 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
791 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
796 WHEN attr_def.location IS NULL THEN 'null()'
797 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
798 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
803 WHEN attr_def.circulate IS NULL THEN 'null()'
804 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
805 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
810 WHEN attr_def.deposit IS NULL THEN 'null()'
811 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
812 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
817 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
818 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
819 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
824 WHEN attr_def.ref IS NULL THEN 'null()'
825 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
826 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
831 WHEN attr_def.holdable IS NULL THEN 'null()'
832 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
833 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
838 WHEN attr_def.price IS NULL THEN 'null()'
839 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
840 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
845 WHEN attr_def.barcode IS NULL THEN 'null()'
846 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
847 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
852 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
853 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
854 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
859 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
860 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
861 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
866 WHEN attr_def.alert_message IS NULL THEN 'null()'
867 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
868 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
873 WHEN attr_def.opac_visible IS NULL THEN 'null()'
874 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
875 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
880 WHEN attr_def.pub_note IS NULL THEN 'null()'
881 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
882 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
886 WHEN attr_def.priv_note IS NULL THEN 'null()'
887 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
888 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
895 call_number || '|' ||
896 copy_number || '|' ||
901 deposit_amount || '|' ||
906 circ_modifier || '|' ||
907 circ_as_type || '|' ||
908 alert_message || '|' ||
913 -- RAISE NOTICE 'XPath: %', xpath;
917 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
918 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
919 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
920 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, opac_vis TEXT )
923 tmp_attr_set.pr = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
924 tmp_attr_set.dep_amount = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
926 tmp_attr_set.pr := NULLIF( tmp_attr_set.pr, '' );
927 tmp_attr_set.dep_amount := NULLIF( tmp_attr_set.dep_amount, '' );
929 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
930 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
931 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
933 SELECT id INTO attr_set.location
934 FROM asset.copy_location
935 WHERE LOWER(name) = LOWER(tmp_attr_set.cl)
936 AND asset.copy_location.owning_lib = COALESCE(attr_set.owning_lib, attr_set.circ_lib); -- INT
938 attr_set.circulate :=
939 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
940 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
943 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
944 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
947 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
948 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
950 attr_set.opac_visible :=
951 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
952 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
955 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
956 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
958 attr_set.copy_number := tmp_attr_set.cnum::INT; -- INT,
959 attr_set.deposit_amount := tmp_attr_set.dep_amount::NUMERIC(6,2); -- NUMERIC(6,2),
960 attr_set.price := tmp_attr_set.pr::NUMERIC(8,2); -- NUMERIC(8,2),
962 attr_set.call_number := tmp_attr_set.cn; -- TEXT
963 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
964 attr_set.circ_modifier := tmp_attr_set.circ_mod; -- TEXT,
965 attr_set.circ_as_type := tmp_attr_set.circ_as; -- TEXT,
966 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
967 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
968 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
969 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
971 RETURN NEXT attr_set;
983 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$
989 FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP
991 SELECT extract_marc_field('vandelay.queued_bib_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_bib_record WHERE id = NEW.id;
992 IF (value IS NOT NULL AND value <> '') THEN
993 INSERT INTO vandelay.queued_bib_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
1000 $$ LANGUAGE PLPGSQL;
1002 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
1005 item_data vandelay.import_item%ROWTYPE;
1008 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
1010 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
1011 INSERT INTO vandelay.import_item (
1035 item_data.definition,
1036 item_data.owning_lib,
1038 item_data.call_number,
1039 item_data.copy_number,
1042 item_data.circulate,
1044 item_data.deposit_amount,
1049 item_data.circ_modifier,
1050 item_data.circ_as_type,
1051 item_data.alert_message,
1053 item_data.priv_note,
1054 item_data.opac_visible
1060 $func$ LANGUAGE PLPGSQL;
1062 CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
1071 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
1073 SELECT * INTO attr_def FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1;
1075 IF attr_def IS NOT NULL AND attr_def.id IS NOT NULL THEN
1076 id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr_def.xpath, attr_def.remove);
1078 IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN
1079 SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted;
1080 SELECT * INTO attr FROM vandelay.queued_bib_record_attr WHERE record = NEW.id and field = attr_def.id LIMIT 1;
1081 IF exact_id IS NOT NULL THEN
1082 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id);
1087 IF exact_id IS NULL THEN
1088 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
1090 -- All numbers? check for an id match
1091 IF (attr.attr_value ~ $r$^\d+$$r$) THEN
1092 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP
1093 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1097 -- Looks like an ISBN? check for an isbn match
1098 IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN
1099 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
1100 PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE;
1102 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
1106 -- subcheck for isbn-as-tcn
1107 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP
1108 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1112 -- check for an OCLC tcn_value match
1113 IF (attr.attr_value ~ $r$^o\d+$$r$) THEN
1114 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP
1115 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1119 -- check for a direct tcn_value match
1120 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP
1121 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1124 -- check for a direct item barcode match
1127 FROM biblio.record_entry b
1128 JOIN asset.call_number cn ON (cn.record = b.id)
1129 JOIN asset.copy cp ON (cp.call_number = cn.id)
1130 WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE
1132 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1140 $func$ LANGUAGE PLPGSQL;
1142 CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$
1144 DELETE FROM vandelay.queued_bib_record_attr WHERE record = OLD.id;
1145 DELETE FROM vandelay.import_item WHERE record = OLD.id;
1147 IF TG_OP = 'UPDATE' THEN
1152 $$ LANGUAGE PLPGSQL;
1154 CREATE TRIGGER cleanup_bib_trigger
1155 BEFORE UPDATE OR DELETE ON vandelay.queued_bib_record
1156 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_bib_marc();
1158 CREATE TRIGGER ingest_bib_trigger
1159 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1160 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_marc();
1162 CREATE TRIGGER ingest_item_trigger
1163 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1164 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_items();
1166 CREATE TRIGGER zz_match_bibs_trigger
1167 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1168 FOR EACH ROW EXECUTE PROCEDURE vandelay.match_bib_record();
1171 /* Authority stuff down here */
1172 ---------------------------------------
1173 CREATE TABLE vandelay.authority_attr_definition (
1174 id SERIAL PRIMARY KEY,
1175 code TEXT UNIQUE NOT NULL,
1177 xpath TEXT NOT NULL,
1178 remove TEXT NOT NULL DEFAULT '',
1179 ident BOOL NOT NULL DEFAULT FALSE
1182 CREATE TABLE vandelay.authority_queue (
1183 queue_type TEXT NOT NULL DEFAULT 'authority' CHECK (queue_type = 'authority'),
1184 CONSTRAINT vand_authority_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
1185 ) INHERITS (vandelay.queue);
1186 ALTER TABLE vandelay.authority_queue ADD PRIMARY KEY (id);
1188 CREATE TABLE vandelay.queued_authority_record (
1189 queue INT NOT NULL REFERENCES vandelay.authority_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1190 imported_as INT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1191 ) INHERITS (vandelay.queued_record);
1192 ALTER TABLE vandelay.queued_authority_record ADD PRIMARY KEY (id);
1193 CREATE INDEX queued_authority_record_queue_idx ON vandelay.queued_authority_record (queue);
1195 CREATE TABLE vandelay.queued_authority_record_attr (
1196 id BIGSERIAL PRIMARY KEY,
1197 record BIGINT NOT NULL REFERENCES vandelay.queued_authority_record (id) DEFERRABLE INITIALLY DEFERRED,
1198 field INT NOT NULL REFERENCES vandelay.authority_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
1199 attr_value TEXT NOT NULL
1201 CREATE INDEX queued_authority_record_attr_record_idx ON vandelay.queued_authority_record_attr (record);
1203 CREATE TABLE vandelay.authority_match (
1204 id BIGSERIAL PRIMARY KEY,
1205 matched_attr INT REFERENCES vandelay.queued_authority_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1206 queued_record BIGINT REFERENCES vandelay.queued_authority_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1207 eg_record BIGINT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1210 CREATE OR REPLACE FUNCTION vandelay.ingest_authority_marc ( ) RETURNS TRIGGER AS $$
1216 FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP
1218 SELECT extract_marc_field('vandelay.queued_authority_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_authority_record WHERE id = NEW.id;
1219 IF (value IS NOT NULL AND value <> '') THEN
1220 INSERT INTO vandelay.queued_authority_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
1227 $$ LANGUAGE PLPGSQL;
1229 CREATE OR REPLACE FUNCTION vandelay.cleanup_authority_marc ( ) RETURNS TRIGGER AS $$
1231 DELETE FROM vandelay.queued_authority_record_attr WHERE record = OLD.id;
1232 IF TG_OP = 'UPDATE' THEN
1237 $$ LANGUAGE PLPGSQL;
1239 CREATE TRIGGER cleanup_authority_trigger
1240 BEFORE UPDATE OR DELETE ON vandelay.queued_authority_record
1241 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_authority_marc();
1243 CREATE TRIGGER ingest_authority_trigger
1244 AFTER INSERT OR UPDATE ON vandelay.queued_authority_record
1245 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_authority_marc();
1247 CREATE OR REPLACE FUNCTION vandelay.overlay_authority_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1249 merge_profile vandelay.merge_profile%ROWTYPE;
1250 dyn_profile vandelay.compile_profile%ROWTYPE;
1259 SELECT b.marc INTO eg_marc
1260 FROM authority.record_entry b
1261 JOIN vandelay.authority_match m ON (m.eg_record = b.id AND m.queued_record = import_id)
1264 SELECT q.marc INTO v_marc
1265 FROM vandelay.queued_record q
1266 JOIN vandelay.authority_match m ON (m.queued_record = q.id AND q.id = import_id)
1269 IF eg_marc IS NULL OR v_marc IS NULL THEN
1270 -- RAISE NOTICE 'no marc for vandelay or authority record';
1274 dyn_profile := vandelay.compile_profile( v_marc );
1276 IF merge_profile_id IS NOT NULL THEN
1277 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
1279 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
1280 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
1281 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
1282 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
1286 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
1287 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
1291 IF dyn_profile.replace_rule <> '' THEN
1292 source_marc = v_marc;
1293 target_marc = eg_marc;
1294 replace_rule = dyn_profile.replace_rule;
1296 source_marc = eg_marc;
1297 target_marc = v_marc;
1298 replace_rule = dyn_profile.preserve_rule;
1301 UPDATE authority.record_entry
1302 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
1306 UPDATE vandelay.queued_authority_record
1307 SET imported_as = eg_id,
1309 WHERE id = import_id;
1313 -- RAISE NOTICE 'update of authority.record_entry failed';
1318 $$ LANGUAGE PLPGSQL;
1320 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1325 SELECT COUNT(*) INTO match_count FROM vandelay.authority_match WHERE queued_record = import_id;
1327 IF match_count <> 1 THEN
1328 -- RAISE NOTICE 'not an exact match';
1332 SELECT m.eg_record INTO eg_id
1333 FROM vandelay.authority_match m
1334 WHERE m.queued_record = import_id
1337 IF eg_id IS NULL THEN
1341 RETURN vandelay.overlay_authority_record( import_id, eg_id, merge_profile_id );
1343 $$ LANGUAGE PLPGSQL;
1345 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
1347 queued_record vandelay.queued_authority_record%ROWTYPE;
1350 FOR queued_record IN SELECT * FROM vandelay.queued_authority_record WHERE queue = queue_id AND import_time IS NULL LOOP
1352 IF vandelay.auto_overlay_authority_record( queued_record.id, merge_profile_id ) THEN
1353 RETURN NEXT queued_record.id;
1361 $$ LANGUAGE PLPGSQL;
1363 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
1364 SELECT * FROM vandelay.auto_overlay_authority_queue( $1, NULL );
1368 -- Vandelay (for importing and exporting records) 012.schema.vandelay.sql
1369 --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)]');
1370 --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)]');
1371 --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]');
1372 --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]');
1373 --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$);
1374 --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$);
1375 --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]');
1376 --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);
1377 --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);
1378 --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);
1379 --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);
1380 --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]');
1381 --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$);
1382 --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]');
1384 --INSERT INTO vandelay.import_item_attr_definition (
1385 -- owner, name, tag, owning_lib, circ_lib, location,
1386 -- call_number, circ_modifier, barcode, price, copy_number,
1387 -- circulate, ref, holdable, opac_visible, status
1390 -- 'Evergreen 852 export format',
1392 -- '[@code = "b"][1]',
1393 -- '[@code = "b"][2]',
1400 -- '[@code = "x" and text() = "circulating"]',
1401 -- '[@code = "x" and text() = "reference"]',
1402 -- '[@code = "x" and text() = "holdable"]',
1403 -- '[@code = "x" and text() = "visible"]',
1407 --INSERT INTO vandelay.import_item_attr_definition (
1420 -- 'Unicorn Import format -- 999',
1431 --INSERT INTO vandelay.authority_attr_definition ( code, description, xpath, ident ) VALUES ('rec_identifier','Identifier','//*[@tag="001"]', TRUE);