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 INT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED
81 ) INHERITS (vandelay.queued_record);
82 ALTER TABLE vandelay.queued_bib_record ADD PRIMARY KEY (id);
84 CREATE TABLE vandelay.queued_bib_record_attr (
85 id BIGSERIAL PRIMARY KEY,
86 record BIGINT NOT NULL REFERENCES vandelay.queued_bib_record (id) DEFERRABLE INITIALLY DEFERRED,
87 field INT NOT NULL REFERENCES vandelay.bib_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
88 attr_value TEXT NOT NULL
91 CREATE TABLE vandelay.bib_match (
92 id BIGSERIAL PRIMARY KEY,
93 field_type TEXT NOT NULL CHECK (field_type in ('isbn','tcn_value','id')),
94 matched_attr INT REFERENCES vandelay.queued_bib_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
95 queued_record BIGINT REFERENCES vandelay.queued_bib_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
96 eg_record BIGINT REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
99 -- DROP TABLE vandelay.import_item CASCADE;
100 CREATE TABLE vandelay.import_item (
101 id BIGSERIAL PRIMARY KEY,
102 record BIGINT NOT NULL REFERENCES vandelay.queued_bib_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
103 definition BIGINT NOT NULL REFERENCES vandelay.import_item_attr_definition (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
112 deposit_amount NUMERIC(8,2),
125 CREATE TABLE vandelay.import_bib_trash_fields (
126 id BIGSERIAL PRIMARY KEY,
127 owner INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
129 CONSTRAINT vand_import_bib_trash_fields_idx UNIQUE (owner,field)
132 CREATE TABLE vandelay.merge_profile (
133 id BIGSERIAL PRIMARY KEY,
134 owner INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
140 CONSTRAINT vand_merge_prof_owner_name_idx UNIQUE (owner,name),
141 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))
145 CREATE TYPE vandelay.tcn_data AS (tcn TEXT, tcn_source TEXT, used BOOL);
146 CREATE OR REPLACE FUNCTION vandelay.find_bib_tcn_data ( xml TEXT ) RETURNS SETOF vandelay.tcn_data AS $_$
150 output vandelay.tcn_data%ROWTYPE;
154 eg_tcn := BTRIM((oils_xpath('//*[@tag="001"]/text()',xml))[1]);
155 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
157 eg_tcn_source := BTRIM((oils_xpath('//*[@tag="003"]/text()',xml))[1]);
158 IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN
159 eg_tcn_source := 'System Local';
162 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
165 output.used := FALSE;
170 output.tcn := eg_tcn;
171 output.tcn_source := eg_tcn_source;
177 eg_tcn := BTRIM((oils_xpath('//*[@tag="901"]/*[@code="a"]/text()',xml))[1]);
178 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
180 eg_tcn_source := BTRIM((oils_xpath('//*[@tag="901"]/*[@code="b"]/text()',xml))[1]);
181 IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN
182 eg_tcn_source := 'System Local';
185 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
188 output.used := FALSE;
193 output.tcn := eg_tcn;
194 output.tcn_source := eg_tcn_source;
200 eg_tcn := BTRIM((oils_xpath('//*[@tag="039"]/*[@code="a"]/text()',xml))[1]);
201 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
203 eg_tcn_source := BTRIM((oils_xpath('//*[@tag="039"]/*[@code="b"]/text()',xml))[1]);
204 IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN
205 eg_tcn_source := 'System Local';
208 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
211 output.used := FALSE;
216 output.tcn := eg_tcn;
217 output.tcn_source := eg_tcn_source;
223 eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="020"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$);
224 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
226 eg_tcn_source := 'ISBN';
228 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
231 output.used := FALSE;
236 output.tcn := eg_tcn;
237 output.tcn_source := eg_tcn_source;
243 eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="022"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$);
244 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
246 eg_tcn_source := 'ISSN';
248 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
251 output.used := FALSE;
256 output.tcn := eg_tcn;
257 output.tcn_source := eg_tcn_source;
263 eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="010"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$);
264 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
266 eg_tcn_source := 'LCCN';
268 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
271 output.used := FALSE;
276 output.tcn := eg_tcn;
277 output.tcn_source := eg_tcn_source;
283 eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="035"]/*[@code="a"]/text()',xml))[1], $re$^.*?(\w+)$$re$, $re$\1$re$);
284 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
286 eg_tcn_source := 'System Legacy';
288 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
291 output.used := FALSE;
296 output.tcn := eg_tcn;
297 output.tcn_source := eg_tcn_source;
304 $_$ LANGUAGE PLPGSQL;
306 CREATE OR REPLACE FUNCTION vandelay.add_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
311 my $target_xml = shift;
312 my $source_xml = shift;
313 my $field_spec = shift;
315 my $target_r = MARC::Record->new_from_xml( $target_xml );
316 my $source_r = MARC::Record->new_from_xml( $source_xml );
318 return $target_xml unless ($target_r && $source_r);
320 my @field_list = split(',', $field_spec);
323 for my $f (@field_list) {
324 $f =~ s/^\s*//; $f =~ s/\s*$//;
325 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
331 $match =~ s/^\s*//; $match =~ s/\s*$//;
332 $fields{$field} = { sf => [ split('', $sf) ] };
334 my ($msf,$mre) = split('~', $match);
335 if (length($msf) > 0 and length($mre) > 0) {
336 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
337 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
338 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
344 for my $f ( keys %fields) {
345 if ( @{$fields{$f}{sf}} ) {
346 for my $from_field ($source_r->field( $f )) {
347 for my $to_field ($target_r->field( $f )) {
348 if (exists($fields{$f}{match})) {
349 next unless (grep { $_ =~ $field{$f}{match}{re} } $to_field->subfield($field{$f}{match}{sf}));
351 my @new_sf = map { ($_ => $from_field->subfield($_)) } @{$fields{$f}};
352 $to_field->add_subfields( @new_sf );
356 my @new_fields = map { $_->clone } $source_r->field( $f );
357 $target_r->insert_fields_ordered( @new_fields );
361 $target_xml = $target_r->as_xml_record;
362 $target_xml =~ s/^<\?.+?\?>$//mo;
363 $target_xml =~ s/\n//sgo;
364 $target_xml =~ s/>\s+</></sgo;
368 $_$ LANGUAGE PLPERLU;
370 CREATE OR REPLACE FUNCTION vandelay.strip_field ( xml TEXT, field TEXT ) RETURNS TEXT AS $_$
376 my $r = MARC::Record->new_from_xml( $xml );
378 return $xml unless ($r);
380 my $field_spec = shift;
381 my @field_list = split(',', $field_spec);
384 for my $f (@field_list) {
385 $f =~ s/^\s*//; $f =~ s/\s*$//;
386 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
392 $match =~ s/^\s*//; $match =~ s/\s*$//;
393 $fields{$field} = { sf => [ split('', $sf) ] };
395 my ($msf,$mre) = split('~', $match);
396 if (length($msf) > 0 and length($mre) > 0) {
397 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
398 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
399 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
405 for my $f ( keys %fields) {
406 for my $to_field ($r->field( $f )) {
407 if (exists($fields{$f}{match})) {
408 next unless (grep { $_ =~ $field{$f}{match}{re} } $to_field->subfield($field{$f}{match}{sf}));
411 if ( @{$fields{$f}} ) {
412 $to_field->delete_subfield(code => $fields{$f}{sf});
414 $r->delete_field( $to_field );
419 $xml = $r->as_xml_record;
420 $xml =~ s/^<\?.+?\?>$//mo;
422 $xml =~ s/>\s+</></sgo;
426 $_$ LANGUAGE PLPERLU;
428 CREATE OR REPLACE FUNCTION vandelay.replace_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
429 SELECT vandelay.add_field( vandelay.strip_field( $1, $3), $2, $3 );
432 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 $_$
433 SELECT vandelay.replace_field( vandelay.add_field( vandelay.strip_field( $1, $5) , $2, $3 ), $2, $4);
436 CREATE TYPE vandelay.compile_profile AS (add_rule TEXT, replace_rule TEXT, preserve_rule TEXT, strip_rule TEXT);
437 CREATE OR REPLACE FUNCTION vandelay.compile_profile ( incoming_xml TEXT ) RETURNS vandelay.compile_profile AS $_$
439 output vandelay.compile_profile%ROWTYPE;
440 profile vandelay.merge_profile%ROWTYPE;
442 profile_tmpl_owner TEXT;
444 strip_rule TEXT := '';
445 replace_rule TEXT := '';
446 preserve_rule TEXT := '';
450 profile_tmpl := (oils_xpath('//*[@tag="905"]/*[@code="t"]/text()',incoming_xml))[1];
451 profile_tmpl_owner := (oils_xpath('//*[@tag="905"]/*[@code="o"]/text()',incoming_xml))[1];
453 IF profile_tmpl IS NOT NULL AND profile_tmpl <> '' AND profile_tmpl_owner IS NOT NULL AND profile_tmpl_owner <> '' THEN
454 SELECT p.* INTO profile
455 FROM vandelay.merge_profile p
456 JOIN actor.org_unit u ON (u.id = p.owner)
457 WHERE p.name = profile_tmpl
458 AND u.shortname = profile_tmpl_owner;
460 IF profile.id IS NOT NULL THEN
461 add_rule := COALESCE(profile.add_spec,'');
462 strip_rule := COALESCE(profile.strip_spec,'');
463 replace_rule := COALESCE(profile.replace_spec,'');
464 preserve_rule := COALESCE(profile.preserve_spec,'');
468 add_rule := add_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="a"]/text()',incoming_xml),''),'');
469 strip_rule := strip_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="d"]/text()',incoming_xml),''),'');
470 replace_rule := replace_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="r"]/text()',incoming_xml),''),'');
471 preserve_rule := preserve_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="p"]/text()',incoming_xml),''),'');
473 output.add_rule := BTRIM(add_rule,',');
474 output.replace_rule := BTRIM(replace_rule,',');
475 output.strip_rule := BTRIM(strip_rule,',');
476 output.preserve_rule := BTRIM(preserve_rule,',');
480 $_$ LANGUAGE PLPGSQL;
482 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
484 merge_profile vandelay.merge_profile%ROWTYPE;
485 dyn_profile vandelay.compile_profile%ROWTYPE;
495 SELECT b.marc INTO eg_marc
496 FROM biblio.record_entry b
497 JOIN vandelay.bib_match m ON (m.eg_record = b.id AND m.queued_record = import_id)
500 IF eg_marc IS NULL OR v_marc IS NULL THEN
501 -- RAISE NOTICE 'no marc for template or bib record';
505 dyn_profile := vandelay.compile_profile( v_marc );
507 IF merge_profile_id IS NOT NULL THEN
508 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
510 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
511 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
512 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
513 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
517 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
518 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
522 IF dyn_profile.replace_rule <> '' THEN
523 source_marc = v_marc;
524 target_marc = eg_marc;
525 replace_rule = dyn_profile.replace_rule;
527 source_marc = eg_marc;
528 target_marc = v_marc;
529 replace_rule = dyn_profile.preserve_rule;
532 UPDATE biblio.record_entry
533 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
537 -- RAISE NOTICE 'update of biblio.record_entry failed';
546 CREATE OR REPLACE FUNCTION vandelay.merge_record_xml ( target_marc TEXT, template_marc TEXT ) RETURNS TEXT AS $$
548 dyn_profile vandelay.compile_profile%ROWTYPE;
556 IF target_marc IS NULL OR template_marc IS NULL THEN
557 -- RAISE NOTICE 'no marc for target or template record';
561 dyn_profile := vandelay.compile_profile( template_marc );
563 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
564 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
568 IF dyn_profile.replace_rule <> '' THEN
569 trgt_marc = target_marc;
570 tmpl_marc = template_marc;
571 replace_rule = dyn_profile.replace_rule;
573 tmp_marc = target_marc;
574 trgt_marc = template_marc;
575 tmpl_marc = tmp_marc;
576 replace_rule = dyn_profile.preserve_rule;
579 RETURN vandelay.merge_record_xml( trgt_marc, tmpl_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule );
584 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT) RETURNS BOOL AS $$
585 SELECT vandelay.template_overlay_bib_record( $1, $2, NULL);
588 CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
590 merge_profile vandelay.merge_profile%ROWTYPE;
591 dyn_profile vandelay.compile_profile%ROWTYPE;
602 SELECT q.marc INTO v_marc
603 FROM vandelay.queued_record q
604 JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
607 IF v_marc IS NULL THEN
608 -- RAISE NOTICE 'no marc for vandelay or bib record';
612 IF vandelay.template_overlay_bib_record( v_marc, eg_id, merge_profile_id) THEN
613 UPDATE vandelay.queued_bib_record
614 SET imported_as = eg_id,
616 WHERE id = import_id;
618 editor_string := (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1];
620 IF editor_string IS NOT NULL AND editor_string <> '' THEN
621 SELECT usr INTO editor_id FROM actor.card WHERE barcode = editor_string;
623 IF editor_id IS NULL THEN
624 SELECT id INTO editor_id FROM actor.usr WHERE usrname = editor_string;
627 IF editor_id IS NOT NULL THEN
628 UPDATE biblio.record_entry SET editor = editor_id WHERE id = eg_id;
635 -- RAISE NOTICE 'update of biblio.record_entry failed';
642 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
646 match_attr vandelay.bib_attr_definition%ROWTYPE;
649 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
652 -- RAISE NOTICE 'already imported, cannot auto-overlay'
656 SELECT COUNT(*) INTO match_count FROM vandelay.bib_match WHERE queued_record = import_id;
658 IF match_count <> 1 THEN
659 -- RAISE NOTICE 'not an exact match';
663 SELECT d.* INTO match_attr
664 FROM vandelay.bib_attr_definition d
665 JOIN vandelay.queued_bib_record_attr a ON (a.field = d.id)
666 JOIN vandelay.bib_match m ON (m.matched_attr = a.id)
667 WHERE m.queued_record = import_id;
669 IF NOT (match_attr.xpath ~ '@tag="901"' AND match_attr.xpath ~ '@code="c"') THEN
670 -- RAISE NOTICE 'not a 901c match: %', match_attr.xpath;
674 SELECT m.eg_record INTO eg_id
675 FROM vandelay.bib_match m
676 WHERE m.queued_record = import_id
679 IF eg_id IS NULL THEN
683 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
687 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
689 queued_record vandelay.queued_bib_record%ROWTYPE;
692 FOR queued_record IN SELECT * FROM vandelay.queued_bib_record WHERE queue = queue_id AND import_time IS NULL LOOP
694 IF vandelay.auto_overlay_bib_record( queued_record.id, merge_profile_id ) THEN
695 RETURN NEXT queued_record.id;
705 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
706 SELECT * FROM vandelay.auto_overlay_bib_queue( $1, NULL );
709 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
734 attr_set vandelay.import_item%ROWTYPE;
740 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
744 attr_set.definition := attr_def.id;
746 -- Build the combined XPath
750 WHEN attr_def.owning_lib IS NULL THEN 'null()'
751 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
752 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
757 WHEN attr_def.circ_lib IS NULL THEN 'null()'
758 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
759 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
764 WHEN attr_def.call_number IS NULL THEN 'null()'
765 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
766 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
771 WHEN attr_def.copy_number IS NULL THEN 'null()'
772 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
773 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
778 WHEN attr_def.status IS NULL THEN 'null()'
779 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
780 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
785 WHEN attr_def.location IS NULL THEN 'null()'
786 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
787 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
792 WHEN attr_def.circulate IS NULL THEN 'null()'
793 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
794 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
799 WHEN attr_def.deposit IS NULL THEN 'null()'
800 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
801 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
806 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
807 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
808 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
813 WHEN attr_def.ref IS NULL THEN 'null()'
814 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
815 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
820 WHEN attr_def.holdable IS NULL THEN 'null()'
821 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
822 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
827 WHEN attr_def.price IS NULL THEN 'null()'
828 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
829 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
834 WHEN attr_def.barcode IS NULL THEN 'null()'
835 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
836 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
841 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
842 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
843 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
848 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
849 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
850 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
855 WHEN attr_def.alert_message IS NULL THEN 'null()'
856 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
857 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
862 WHEN attr_def.opac_visible IS NULL THEN 'null()'
863 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
864 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
869 WHEN attr_def.pub_note IS NULL THEN 'null()'
870 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
871 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
875 WHEN attr_def.priv_note IS NULL THEN 'null()'
876 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
877 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
884 call_number || '|' ||
885 copy_number || '|' ||
890 deposit_amount || '|' ||
895 circ_modifier || '|' ||
896 circ_as_type || '|' ||
897 alert_message || '|' ||
902 -- RAISE NOTICE 'XPath: %', xpath;
906 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
907 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
908 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
909 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, opac_vis TEXT )
912 tmp_attr_set.pr = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
913 tmp_attr_set.dep_amount = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
915 tmp_attr_set.pr := NULLIF( tmp_attr_set.pr, '' );
916 tmp_attr_set.dep_amount := NULLIF( tmp_attr_set.dep_amount, '' );
918 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
919 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
920 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
922 SELECT id INTO attr_set.location
923 FROM asset.copy_location
924 WHERE LOWER(name) = LOWER(tmp_attr_set.cl)
925 AND asset.copy_location.owning_lib = COALESCE(attr_set.owning_lib, attr_set.circ_lib); -- INT
927 attr_set.circulate :=
928 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
929 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
932 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
933 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
936 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
937 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
939 attr_set.opac_visible :=
940 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
941 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
944 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
945 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
947 attr_set.copy_number := tmp_attr_set.cnum::INT; -- INT,
948 attr_set.deposit_amount := tmp_attr_set.dep_amount::NUMERIC(6,2); -- NUMERIC(6,2),
949 attr_set.price := tmp_attr_set.pr::NUMERIC(8,2); -- NUMERIC(8,2),
951 attr_set.call_number := tmp_attr_set.cn; -- TEXT
952 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
953 attr_set.circ_modifier := tmp_attr_set.circ_mod; -- TEXT,
954 attr_set.circ_as_type := tmp_attr_set.circ_as; -- TEXT,
955 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
956 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
957 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
958 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
960 RETURN NEXT attr_set;
972 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$
978 FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP
980 SELECT extract_marc_field('vandelay.queued_bib_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_bib_record WHERE id = NEW.id;
981 IF (value IS NOT NULL AND value <> '') THEN
982 INSERT INTO vandelay.queued_bib_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
991 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
994 item_data vandelay.import_item%ROWTYPE;
997 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
999 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
1000 INSERT INTO vandelay.import_item (
1024 item_data.definition,
1025 item_data.owning_lib,
1027 item_data.call_number,
1028 item_data.copy_number,
1031 item_data.circulate,
1033 item_data.deposit_amount,
1038 item_data.circ_modifier,
1039 item_data.circ_as_type,
1040 item_data.alert_message,
1042 item_data.priv_note,
1043 item_data.opac_visible
1049 $func$ LANGUAGE PLPGSQL;
1051 CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
1060 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
1062 SELECT * INTO attr_def FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1;
1064 IF attr_def IS NOT NULL AND attr_def.id IS NOT NULL THEN
1065 id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr_def.xpath, attr_def.remove);
1067 IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN
1068 SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted;
1069 SELECT * INTO attr FROM vandelay.queued_bib_record_attr WHERE record = NEW.id and field = attr_def.id LIMIT 1;
1070 IF exact_id IS NOT NULL THEN
1071 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id);
1076 IF exact_id IS NULL THEN
1077 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
1079 -- All numbers? check for an id match
1080 IF (attr.attr_value ~ $r$^\d+$$r$) THEN
1081 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP
1082 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1086 -- Looks like an ISBN? check for an isbn match
1087 IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN
1088 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
1089 PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE;
1091 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
1095 -- subcheck for isbn-as-tcn
1096 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP
1097 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1101 -- check for an OCLC tcn_value match
1102 IF (attr.attr_value ~ $r$^o\d+$$r$) THEN
1103 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP
1104 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1108 -- check for a direct tcn_value match
1109 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP
1110 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1113 -- check for a direct item barcode match
1116 FROM biblio.record_entry b
1117 JOIN asset.call_number cn ON (cn.record = b.id)
1118 JOIN asset.copy cp ON (cp.call_number = cn.id)
1119 WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE
1121 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1129 $func$ LANGUAGE PLPGSQL;
1131 CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$
1133 DELETE FROM vandelay.queued_bib_record_attr WHERE record = OLD.id;
1134 DELETE FROM vandelay.import_item WHERE record = OLD.id;
1136 IF TG_OP = 'UPDATE' THEN
1141 $$ LANGUAGE PLPGSQL;
1143 CREATE TRIGGER cleanup_bib_trigger
1144 BEFORE UPDATE OR DELETE ON vandelay.queued_bib_record
1145 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_bib_marc();
1147 CREATE TRIGGER ingest_bib_trigger
1148 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1149 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_marc();
1151 CREATE TRIGGER ingest_item_trigger
1152 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1153 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_items();
1155 CREATE TRIGGER zz_match_bibs_trigger
1156 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1157 FOR EACH ROW EXECUTE PROCEDURE vandelay.match_bib_record();
1160 /* Authority stuff down here */
1161 ---------------------------------------
1162 CREATE TABLE vandelay.authority_attr_definition (
1163 id SERIAL PRIMARY KEY,
1164 code TEXT UNIQUE NOT NULL,
1166 xpath TEXT NOT NULL,
1167 remove TEXT NOT NULL DEFAULT '',
1168 ident BOOL NOT NULL DEFAULT FALSE
1171 CREATE TABLE vandelay.authority_queue (
1172 queue_type TEXT NOT NULL DEFAULT 'authority' CHECK (queue_type = 'authority'),
1173 CONSTRAINT vand_authority_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
1174 ) INHERITS (vandelay.queue);
1175 ALTER TABLE vandelay.authority_queue ADD PRIMARY KEY (id);
1177 CREATE TABLE vandelay.queued_authority_record (
1178 queue INT NOT NULL REFERENCES vandelay.authority_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1179 imported_as INT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1180 ) INHERITS (vandelay.queued_record);
1181 ALTER TABLE vandelay.queued_authority_record ADD PRIMARY KEY (id);
1183 CREATE TABLE vandelay.queued_authority_record_attr (
1184 id BIGSERIAL PRIMARY KEY,
1185 record BIGINT NOT NULL REFERENCES vandelay.queued_authority_record (id) DEFERRABLE INITIALLY DEFERRED,
1186 field INT NOT NULL REFERENCES vandelay.authority_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
1187 attr_value TEXT NOT NULL
1190 CREATE TABLE vandelay.authority_match (
1191 id BIGSERIAL PRIMARY KEY,
1192 matched_attr INT REFERENCES vandelay.queued_authority_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1193 queued_record BIGINT REFERENCES vandelay.queued_authority_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1194 eg_record BIGINT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1197 CREATE OR REPLACE FUNCTION vandelay.ingest_authority_marc ( ) RETURNS TRIGGER AS $$
1203 FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP
1205 SELECT extract_marc_field('vandelay.queued_authority_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_authority_record WHERE id = NEW.id;
1206 IF (value IS NOT NULL AND value <> '') THEN
1207 INSERT INTO vandelay.queued_authority_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
1214 $$ LANGUAGE PLPGSQL;
1216 CREATE OR REPLACE FUNCTION vandelay.cleanup_authority_marc ( ) RETURNS TRIGGER AS $$
1218 DELETE FROM vandelay.queued_authority_record_attr WHERE record = OLD.id;
1219 IF TG_OP = 'UPDATE' THEN
1224 $$ LANGUAGE PLPGSQL;
1226 CREATE TRIGGER cleanup_authority_trigger
1227 BEFORE UPDATE OR DELETE ON vandelay.queued_authority_record
1228 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_authority_marc();
1230 CREATE TRIGGER ingest_authority_trigger
1231 AFTER INSERT OR UPDATE ON vandelay.queued_authority_record
1232 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_authority_marc();
1234 CREATE OR REPLACE FUNCTION vandelay.overlay_authority_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1236 merge_profile vandelay.merge_profile%ROWTYPE;
1237 dyn_profile vandelay.compile_profile%ROWTYPE;
1246 SELECT b.marc INTO eg_marc
1247 FROM authority.record_entry b
1248 JOIN vandelay.authority_match m ON (m.eg_record = b.id AND m.queued_record = import_id)
1251 SELECT q.marc INTO v_marc
1252 FROM vandelay.queued_record q
1253 JOIN vandelay.authority_match m ON (m.queued_record = q.id AND q.id = import_id)
1256 IF eg_marc IS NULL OR v_marc IS NULL THEN
1257 -- RAISE NOTICE 'no marc for vandelay or authority record';
1261 dyn_profile := vandelay.compile_profile( v_marc );
1263 IF merge_profile_id IS NOT NULL THEN
1264 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
1266 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
1267 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
1268 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
1269 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
1273 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
1274 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
1278 IF dyn_profile.replace_rule <> '' THEN
1279 source_marc = v_marc;
1280 target_marc = eg_marc;
1281 replace_rule = dyn_profile.replace_rule;
1283 source_marc = eg_marc;
1284 target_marc = v_marc;
1285 replace_rule = dyn_profile.preserve_rule;
1288 UPDATE authority.record_entry
1289 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
1293 UPDATE vandelay.queued_authority_record
1294 SET imported_as = eg_id,
1296 WHERE id = import_id;
1300 -- RAISE NOTICE 'update of authority.record_entry failed';
1305 $$ LANGUAGE PLPGSQL;
1307 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1312 SELECT COUNT(*) INTO match_count FROM vandelay.authority_match WHERE queued_record = import_id;
1314 IF match_count <> 1 THEN
1315 -- RAISE NOTICE 'not an exact match';
1319 SELECT m.eg_record INTO eg_id
1320 FROM vandelay.authority_match m
1321 WHERE m.queued_record = import_id
1324 IF eg_id IS NULL THEN
1328 RETURN vandelay.overlay_authority_record( import_id, eg_id, merge_profile_id );
1330 $$ LANGUAGE PLPGSQL;
1332 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
1334 queued_record vandelay.queued_authority_record%ROWTYPE;
1337 FOR queued_record IN SELECT * FROM vandelay.queued_authority_record WHERE queue = queue_id AND import_time IS NULL LOOP
1339 IF vandelay.auto_overlay_authority_record( queued_record.id, merge_profile_id ) THEN
1340 RETURN NEXT queued_record.id;
1348 $$ LANGUAGE PLPGSQL;
1350 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
1351 SELECT * FROM vandelay.auto_overlay_authority_queue( $1, NULL );
1355 -- Vandelay (for importing and exporting records) 012.schema.vandelay.sql
1356 --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)]');
1357 --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)]');
1358 --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]');
1359 --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]');
1360 --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$);
1361 --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$);
1362 --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]');
1363 --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);
1364 --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);
1365 --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);
1366 --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);
1367 --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]');
1368 --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$);
1369 --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]');
1371 --INSERT INTO vandelay.import_item_attr_definition (
1372 -- owner, name, tag, owning_lib, circ_lib, location,
1373 -- call_number, circ_modifier, barcode, price, copy_number,
1374 -- circulate, ref, holdable, opac_visible, status
1377 -- 'Evergreen 852 export format',
1379 -- '[@code = "b"][1]',
1380 -- '[@code = "b"][2]',
1387 -- '[@code = "x" and text() = "circulating"]',
1388 -- '[@code = "x" and text() = "reference"]',
1389 -- '[@code = "x" and text() = "holdable"]',
1390 -- '[@code = "x" and text() = "visible"]',
1394 --INSERT INTO vandelay.import_item_attr_definition (
1407 -- 'Unicorn Import format -- 999',
1418 --INSERT INTO vandelay.authority_attr_definition ( code, description, xpath, ident ) VALUES ('rec_identifier','Identifier','//*[@tag="001"]', TRUE);