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 $_$
309 use MARC::File::XML (BinaryEncoding => 'UTF-8');
312 my $target_xml = shift;
313 my $source_xml = shift;
314 my $field_spec = shift;
316 my $target_r = MARC::Record->new_from_xml( $target_xml );
317 my $source_r = MARC::Record->new_from_xml( $source_xml );
319 return $target_xml unless ($target_r && $source_r);
321 my @field_list = split(',', $field_spec);
324 for my $f (@field_list) {
325 $f =~ s/^\s*//; $f =~ s/\s*$//;
326 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
332 $match =~ s/^\s*//; $match =~ s/\s*$//;
333 $fields{$field} = { sf => [ split('', $sf) ] };
335 my ($msf,$mre) = split('~', $match);
336 if (length($msf) > 0 and length($mre) > 0) {
337 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
338 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
339 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
345 for my $f ( keys %fields) {
346 if ( @{$fields{$f}{sf}} ) {
347 for my $from_field ($source_r->field( $f )) {
348 for my $to_field ($target_r->field( $f )) {
349 if (exists($fields{$f}{match})) {
350 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
352 my @new_sf = map { ($_ => $from_field->subfield($_)) } @{$fields{$f}{sf}};
353 $to_field->add_subfields( @new_sf );
357 my @new_fields = map { $_->clone } $source_r->field( $f );
358 $target_r->insert_fields_ordered( @new_fields );
362 $target_xml = $target_r->as_xml_record;
363 $target_xml =~ s/^<\?.+?\?>$//mo;
364 $target_xml =~ s/\n//sgo;
365 $target_xml =~ s/>\s+</></sgo;
369 $_$ LANGUAGE PLPERLU;
371 CREATE OR REPLACE FUNCTION vandelay.strip_field ( xml TEXT, field TEXT ) RETURNS TEXT AS $_$
374 use MARC::File::XML (BinaryEncoding => 'UTF-8');
378 my $r = MARC::Record->new_from_xml( $xml );
380 return $xml unless ($r);
382 my $field_spec = shift;
383 my @field_list = split(',', $field_spec);
386 for my $f (@field_list) {
387 $f =~ s/^\s*//; $f =~ s/\s*$//;
388 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
394 $match =~ s/^\s*//; $match =~ s/\s*$//;
395 $fields{$field} = { sf => [ split('', $sf) ] };
397 my ($msf,$mre) = split('~', $match);
398 if (length($msf) > 0 and length($mre) > 0) {
399 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
400 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
401 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
407 for my $f ( keys %fields) {
408 for my $to_field ($r->field( $f )) {
409 if (exists($fields{$f}{match})) {
410 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
413 if ( @{$fields{$f}{sf}} ) {
414 $to_field->delete_subfield(code => $fields{$f}{sf});
416 $r->delete_field( $to_field );
421 $xml = $r->as_xml_record;
422 $xml =~ s/^<\?.+?\?>$//mo;
424 $xml =~ s/>\s+</></sgo;
428 $_$ LANGUAGE PLPERLU;
430 CREATE OR REPLACE FUNCTION vandelay.replace_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
431 SELECT vandelay.add_field( vandelay.strip_field( $1, $3), $2, $3 );
434 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 $_$
435 SELECT vandelay.replace_field( vandelay.add_field( vandelay.strip_field( $1, $5) , $2, $3 ), $2, $4);
438 CREATE TYPE vandelay.compile_profile AS (add_rule TEXT, replace_rule TEXT, preserve_rule TEXT, strip_rule TEXT);
439 CREATE OR REPLACE FUNCTION vandelay.compile_profile ( incoming_xml TEXT ) RETURNS vandelay.compile_profile AS $_$
441 output vandelay.compile_profile%ROWTYPE;
442 profile vandelay.merge_profile%ROWTYPE;
444 profile_tmpl_owner TEXT;
446 strip_rule TEXT := '';
447 replace_rule TEXT := '';
448 preserve_rule TEXT := '';
452 profile_tmpl := (oils_xpath('//*[@tag="905"]/*[@code="t"]/text()',incoming_xml))[1];
453 profile_tmpl_owner := (oils_xpath('//*[@tag="905"]/*[@code="o"]/text()',incoming_xml))[1];
455 IF profile_tmpl IS NOT NULL AND profile_tmpl <> '' AND profile_tmpl_owner IS NOT NULL AND profile_tmpl_owner <> '' THEN
456 SELECT p.* INTO profile
457 FROM vandelay.merge_profile p
458 JOIN actor.org_unit u ON (u.id = p.owner)
459 WHERE p.name = profile_tmpl
460 AND u.shortname = profile_tmpl_owner;
462 IF profile.id IS NOT NULL THEN
463 add_rule := COALESCE(profile.add_spec,'');
464 strip_rule := COALESCE(profile.strip_spec,'');
465 replace_rule := COALESCE(profile.replace_spec,'');
466 preserve_rule := COALESCE(profile.preserve_spec,'');
470 add_rule := add_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="a"]/text()',incoming_xml),''),'');
471 strip_rule := strip_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="d"]/text()',incoming_xml),''),'');
472 replace_rule := replace_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="r"]/text()',incoming_xml),''),'');
473 preserve_rule := preserve_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="p"]/text()',incoming_xml),''),'');
475 output.add_rule := BTRIM(add_rule,',');
476 output.replace_rule := BTRIM(replace_rule,',');
477 output.strip_rule := BTRIM(strip_rule,',');
478 output.preserve_rule := BTRIM(preserve_rule,',');
482 $_$ LANGUAGE PLPGSQL;
484 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
486 merge_profile vandelay.merge_profile%ROWTYPE;
487 dyn_profile vandelay.compile_profile%ROWTYPE;
497 SELECT b.marc INTO eg_marc
498 FROM biblio.record_entry b
502 IF eg_marc IS NULL OR v_marc IS NULL THEN
503 -- RAISE NOTICE 'no marc for template or bib record';
507 dyn_profile := vandelay.compile_profile( v_marc );
509 IF merge_profile_id IS NOT NULL THEN
510 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
512 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
513 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
514 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
515 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
519 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
520 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
524 IF dyn_profile.replace_rule <> '' THEN
525 source_marc = v_marc;
526 target_marc = eg_marc;
527 replace_rule = dyn_profile.replace_rule;
529 source_marc = eg_marc;
530 target_marc = v_marc;
531 replace_rule = dyn_profile.preserve_rule;
534 UPDATE biblio.record_entry
535 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
539 -- RAISE NOTICE 'update of biblio.record_entry failed';
548 CREATE OR REPLACE FUNCTION vandelay.merge_record_xml ( target_marc TEXT, template_marc TEXT ) RETURNS TEXT AS $$
550 dyn_profile vandelay.compile_profile%ROWTYPE;
558 IF target_marc IS NULL OR template_marc IS NULL THEN
559 -- RAISE NOTICE 'no marc for target or template record';
563 dyn_profile := vandelay.compile_profile( template_marc );
565 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
566 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
570 IF dyn_profile.replace_rule <> '' THEN
571 trgt_marc = target_marc;
572 tmpl_marc = template_marc;
573 replace_rule = dyn_profile.replace_rule;
575 tmp_marc = target_marc;
576 trgt_marc = template_marc;
577 tmpl_marc = tmp_marc;
578 replace_rule = dyn_profile.preserve_rule;
581 RETURN vandelay.merge_record_xml( trgt_marc, tmpl_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule );
586 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT) RETURNS BOOL AS $$
587 SELECT vandelay.template_overlay_bib_record( $1, $2, NULL);
590 CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
592 merge_profile vandelay.merge_profile%ROWTYPE;
593 dyn_profile vandelay.compile_profile%ROWTYPE;
604 SELECT q.marc INTO v_marc
605 FROM vandelay.queued_record q
606 JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
609 IF v_marc IS NULL THEN
610 -- RAISE NOTICE 'no marc for vandelay or bib record';
614 IF vandelay.template_overlay_bib_record( v_marc, eg_id, merge_profile_id) THEN
615 UPDATE vandelay.queued_bib_record
616 SET imported_as = eg_id,
618 WHERE id = import_id;
620 editor_string := (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1];
622 IF editor_string IS NOT NULL AND editor_string <> '' THEN
623 SELECT usr INTO editor_id FROM actor.card WHERE barcode = editor_string;
625 IF editor_id IS NULL THEN
626 SELECT id INTO editor_id FROM actor.usr WHERE usrname = editor_string;
629 IF editor_id IS NOT NULL THEN
630 UPDATE biblio.record_entry SET editor = editor_id WHERE id = eg_id;
637 -- RAISE NOTICE 'update of biblio.record_entry failed';
644 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
648 match_attr vandelay.bib_attr_definition%ROWTYPE;
651 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
654 -- RAISE NOTICE 'already imported, cannot auto-overlay'
658 SELECT COUNT(*) INTO match_count FROM vandelay.bib_match WHERE queued_record = import_id;
660 IF match_count <> 1 THEN
661 -- RAISE NOTICE 'not an exact match';
665 SELECT d.* INTO match_attr
666 FROM vandelay.bib_attr_definition d
667 JOIN vandelay.queued_bib_record_attr a ON (a.field = d.id)
668 JOIN vandelay.bib_match m ON (m.matched_attr = a.id)
669 WHERE m.queued_record = import_id;
671 IF NOT (match_attr.xpath ~ '@tag="901"' AND match_attr.xpath ~ '@code="c"') THEN
672 -- RAISE NOTICE 'not a 901c match: %', match_attr.xpath;
676 SELECT m.eg_record INTO eg_id
677 FROM vandelay.bib_match m
678 WHERE m.queued_record = import_id
681 IF eg_id IS NULL THEN
685 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
689 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
691 queued_record vandelay.queued_bib_record%ROWTYPE;
694 FOR queued_record IN SELECT * FROM vandelay.queued_bib_record WHERE queue = queue_id AND import_time IS NULL LOOP
696 IF vandelay.auto_overlay_bib_record( queued_record.id, merge_profile_id ) THEN
697 RETURN NEXT queued_record.id;
707 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
708 SELECT * FROM vandelay.auto_overlay_bib_queue( $1, NULL );
711 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
736 attr_set vandelay.import_item%ROWTYPE;
742 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
746 attr_set.definition := attr_def.id;
748 -- Build the combined XPath
752 WHEN attr_def.owning_lib IS NULL THEN 'null()'
753 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
754 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
759 WHEN attr_def.circ_lib IS NULL THEN 'null()'
760 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
761 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
766 WHEN attr_def.call_number IS NULL THEN 'null()'
767 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
768 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
773 WHEN attr_def.copy_number IS NULL THEN 'null()'
774 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
775 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
780 WHEN attr_def.status IS NULL THEN 'null()'
781 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
782 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
787 WHEN attr_def.location IS NULL THEN 'null()'
788 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
789 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
794 WHEN attr_def.circulate IS NULL THEN 'null()'
795 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
796 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
801 WHEN attr_def.deposit IS NULL THEN 'null()'
802 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
803 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
808 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
809 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
810 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
815 WHEN attr_def.ref IS NULL THEN 'null()'
816 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
817 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
822 WHEN attr_def.holdable IS NULL THEN 'null()'
823 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
824 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
829 WHEN attr_def.price IS NULL THEN 'null()'
830 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
831 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
836 WHEN attr_def.barcode IS NULL THEN 'null()'
837 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
838 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
843 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
844 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
845 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
850 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
851 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
852 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
857 WHEN attr_def.alert_message IS NULL THEN 'null()'
858 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
859 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
864 WHEN attr_def.opac_visible IS NULL THEN 'null()'
865 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
866 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
871 WHEN attr_def.pub_note IS NULL THEN 'null()'
872 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
873 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
877 WHEN attr_def.priv_note IS NULL THEN 'null()'
878 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
879 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
886 call_number || '|' ||
887 copy_number || '|' ||
892 deposit_amount || '|' ||
897 circ_modifier || '|' ||
898 circ_as_type || '|' ||
899 alert_message || '|' ||
904 -- RAISE NOTICE 'XPath: %', xpath;
908 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
909 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
910 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
911 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, opac_vis TEXT )
914 tmp_attr_set.pr = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
915 tmp_attr_set.dep_amount = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
917 tmp_attr_set.pr := NULLIF( tmp_attr_set.pr, '' );
918 tmp_attr_set.dep_amount := NULLIF( tmp_attr_set.dep_amount, '' );
920 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
921 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
922 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
924 SELECT id INTO attr_set.location
925 FROM asset.copy_location
926 WHERE LOWER(name) = LOWER(tmp_attr_set.cl)
927 AND asset.copy_location.owning_lib = COALESCE(attr_set.owning_lib, attr_set.circ_lib); -- INT
929 attr_set.circulate :=
930 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
931 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
934 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
935 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
938 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
939 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
941 attr_set.opac_visible :=
942 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
943 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
946 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
947 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
949 attr_set.copy_number := tmp_attr_set.cnum::INT; -- INT,
950 attr_set.deposit_amount := tmp_attr_set.dep_amount::NUMERIC(6,2); -- NUMERIC(6,2),
951 attr_set.price := tmp_attr_set.pr::NUMERIC(8,2); -- NUMERIC(8,2),
953 attr_set.call_number := tmp_attr_set.cn; -- TEXT
954 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
955 attr_set.circ_modifier := tmp_attr_set.circ_mod; -- TEXT,
956 attr_set.circ_as_type := tmp_attr_set.circ_as; -- TEXT,
957 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
958 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
959 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
960 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
962 RETURN NEXT attr_set;
974 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$
980 FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP
982 SELECT extract_marc_field('vandelay.queued_bib_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_bib_record WHERE id = NEW.id;
983 IF (value IS NOT NULL AND value <> '') THEN
984 INSERT INTO vandelay.queued_bib_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
993 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
996 item_data vandelay.import_item%ROWTYPE;
999 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
1001 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
1002 INSERT INTO vandelay.import_item (
1026 item_data.definition,
1027 item_data.owning_lib,
1029 item_data.call_number,
1030 item_data.copy_number,
1033 item_data.circulate,
1035 item_data.deposit_amount,
1040 item_data.circ_modifier,
1041 item_data.circ_as_type,
1042 item_data.alert_message,
1044 item_data.priv_note,
1045 item_data.opac_visible
1051 $func$ LANGUAGE PLPGSQL;
1053 CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
1062 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
1064 SELECT * INTO attr_def FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1;
1066 IF attr_def IS NOT NULL AND attr_def.id IS NOT NULL THEN
1067 id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr_def.xpath, attr_def.remove);
1069 IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN
1070 SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted;
1071 SELECT * INTO attr FROM vandelay.queued_bib_record_attr WHERE record = NEW.id and field = attr_def.id LIMIT 1;
1072 IF exact_id IS NOT NULL THEN
1073 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id);
1078 IF exact_id IS NULL THEN
1079 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
1081 -- All numbers? check for an id match
1082 IF (attr.attr_value ~ $r$^\d+$$r$) THEN
1083 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP
1084 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1088 -- Looks like an ISBN? check for an isbn match
1089 IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN
1090 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
1091 PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE;
1093 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
1097 -- subcheck for isbn-as-tcn
1098 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP
1099 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1103 -- check for an OCLC tcn_value match
1104 IF (attr.attr_value ~ $r$^o\d+$$r$) THEN
1105 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP
1106 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1110 -- check for a direct tcn_value match
1111 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP
1112 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1115 -- check for a direct item barcode match
1118 FROM biblio.record_entry b
1119 JOIN asset.call_number cn ON (cn.record = b.id)
1120 JOIN asset.copy cp ON (cp.call_number = cn.id)
1121 WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE
1123 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1131 $func$ LANGUAGE PLPGSQL;
1133 CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$
1135 DELETE FROM vandelay.queued_bib_record_attr WHERE record = OLD.id;
1136 DELETE FROM vandelay.import_item WHERE record = OLD.id;
1138 IF TG_OP = 'UPDATE' THEN
1143 $$ LANGUAGE PLPGSQL;
1145 CREATE TRIGGER cleanup_bib_trigger
1146 BEFORE UPDATE OR DELETE ON vandelay.queued_bib_record
1147 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_bib_marc();
1149 CREATE TRIGGER ingest_bib_trigger
1150 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1151 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_marc();
1153 CREATE TRIGGER ingest_item_trigger
1154 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1155 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_items();
1157 CREATE TRIGGER zz_match_bibs_trigger
1158 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1159 FOR EACH ROW EXECUTE PROCEDURE vandelay.match_bib_record();
1162 /* Authority stuff down here */
1163 ---------------------------------------
1164 CREATE TABLE vandelay.authority_attr_definition (
1165 id SERIAL PRIMARY KEY,
1166 code TEXT UNIQUE NOT NULL,
1168 xpath TEXT NOT NULL,
1169 remove TEXT NOT NULL DEFAULT '',
1170 ident BOOL NOT NULL DEFAULT FALSE
1173 CREATE TABLE vandelay.authority_queue (
1174 queue_type TEXT NOT NULL DEFAULT 'authority' CHECK (queue_type = 'authority'),
1175 CONSTRAINT vand_authority_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
1176 ) INHERITS (vandelay.queue);
1177 ALTER TABLE vandelay.authority_queue ADD PRIMARY KEY (id);
1179 CREATE TABLE vandelay.queued_authority_record (
1180 queue INT NOT NULL REFERENCES vandelay.authority_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1181 imported_as INT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1182 ) INHERITS (vandelay.queued_record);
1183 ALTER TABLE vandelay.queued_authority_record ADD PRIMARY KEY (id);
1185 CREATE TABLE vandelay.queued_authority_record_attr (
1186 id BIGSERIAL PRIMARY KEY,
1187 record BIGINT NOT NULL REFERENCES vandelay.queued_authority_record (id) DEFERRABLE INITIALLY DEFERRED,
1188 field INT NOT NULL REFERENCES vandelay.authority_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
1189 attr_value TEXT NOT NULL
1192 CREATE TABLE vandelay.authority_match (
1193 id BIGSERIAL PRIMARY KEY,
1194 matched_attr INT REFERENCES vandelay.queued_authority_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1195 queued_record BIGINT REFERENCES vandelay.queued_authority_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1196 eg_record BIGINT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1199 CREATE OR REPLACE FUNCTION vandelay.ingest_authority_marc ( ) RETURNS TRIGGER AS $$
1205 FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP
1207 SELECT extract_marc_field('vandelay.queued_authority_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_authority_record WHERE id = NEW.id;
1208 IF (value IS NOT NULL AND value <> '') THEN
1209 INSERT INTO vandelay.queued_authority_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
1216 $$ LANGUAGE PLPGSQL;
1218 CREATE OR REPLACE FUNCTION vandelay.cleanup_authority_marc ( ) RETURNS TRIGGER AS $$
1220 DELETE FROM vandelay.queued_authority_record_attr WHERE record = OLD.id;
1221 IF TG_OP = 'UPDATE' THEN
1226 $$ LANGUAGE PLPGSQL;
1228 CREATE TRIGGER cleanup_authority_trigger
1229 BEFORE UPDATE OR DELETE ON vandelay.queued_authority_record
1230 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_authority_marc();
1232 CREATE TRIGGER ingest_authority_trigger
1233 AFTER INSERT OR UPDATE ON vandelay.queued_authority_record
1234 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_authority_marc();
1236 CREATE OR REPLACE FUNCTION vandelay.overlay_authority_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1238 merge_profile vandelay.merge_profile%ROWTYPE;
1239 dyn_profile vandelay.compile_profile%ROWTYPE;
1248 SELECT b.marc INTO eg_marc
1249 FROM authority.record_entry b
1250 JOIN vandelay.authority_match m ON (m.eg_record = b.id AND m.queued_record = import_id)
1253 SELECT q.marc INTO v_marc
1254 FROM vandelay.queued_record q
1255 JOIN vandelay.authority_match m ON (m.queued_record = q.id AND q.id = import_id)
1258 IF eg_marc IS NULL OR v_marc IS NULL THEN
1259 -- RAISE NOTICE 'no marc for vandelay or authority record';
1263 dyn_profile := vandelay.compile_profile( v_marc );
1265 IF merge_profile_id IS NOT NULL THEN
1266 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
1268 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
1269 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
1270 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
1271 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
1275 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
1276 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
1280 IF dyn_profile.replace_rule <> '' THEN
1281 source_marc = v_marc;
1282 target_marc = eg_marc;
1283 replace_rule = dyn_profile.replace_rule;
1285 source_marc = eg_marc;
1286 target_marc = v_marc;
1287 replace_rule = dyn_profile.preserve_rule;
1290 UPDATE authority.record_entry
1291 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
1295 UPDATE vandelay.queued_authority_record
1296 SET imported_as = eg_id,
1298 WHERE id = import_id;
1302 -- RAISE NOTICE 'update of authority.record_entry failed';
1307 $$ LANGUAGE PLPGSQL;
1309 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1314 SELECT COUNT(*) INTO match_count FROM vandelay.authority_match WHERE queued_record = import_id;
1316 IF match_count <> 1 THEN
1317 -- RAISE NOTICE 'not an exact match';
1321 SELECT m.eg_record INTO eg_id
1322 FROM vandelay.authority_match m
1323 WHERE m.queued_record = import_id
1326 IF eg_id IS NULL THEN
1330 RETURN vandelay.overlay_authority_record( import_id, eg_id, merge_profile_id );
1332 $$ LANGUAGE PLPGSQL;
1334 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
1336 queued_record vandelay.queued_authority_record%ROWTYPE;
1339 FOR queued_record IN SELECT * FROM vandelay.queued_authority_record WHERE queue = queue_id AND import_time IS NULL LOOP
1341 IF vandelay.auto_overlay_authority_record( queued_record.id, merge_profile_id ) THEN
1342 RETURN NEXT queued_record.id;
1350 $$ LANGUAGE PLPGSQL;
1352 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
1353 SELECT * FROM vandelay.auto_overlay_authority_queue( $1, NULL );
1357 -- Vandelay (for importing and exporting records) 012.schema.vandelay.sql
1358 --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)]');
1359 --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)]');
1360 --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]');
1361 --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]');
1362 --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$);
1363 --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$);
1364 --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]');
1365 --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);
1366 --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);
1367 --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);
1368 --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);
1369 --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]');
1370 --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$);
1371 --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]');
1373 --INSERT INTO vandelay.import_item_attr_definition (
1374 -- owner, name, tag, owning_lib, circ_lib, location,
1375 -- call_number, circ_modifier, barcode, price, copy_number,
1376 -- circulate, ref, holdable, opac_visible, status
1379 -- 'Evergreen 852 export format',
1381 -- '[@code = "b"][1]',
1382 -- '[@code = "b"][2]',
1389 -- '[@code = "x" and text() = "circulating"]',
1390 -- '[@code = "x" and text() = "reference"]',
1391 -- '[@code = "x" and text() = "holdable"]',
1392 -- '[@code = "x" and text() = "visible"]',
1396 --INSERT INTO vandelay.import_item_attr_definition (
1409 -- 'Unicorn Import format -- 999',
1420 --INSERT INTO vandelay.authority_attr_definition ( code, description, xpath, ident ) VALUES ('rec_identifier','Identifier','//*[@tag="001"]', TRUE);