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, force_add INT ) RETURNS TEXT AS $_$
311 use MARC::File::XML (BinaryEncoding => 'UTF-8');
315 MARC::Charset->assume_unicode(1);
317 my $target_xml = shift;
318 my $source_xml = shift;
319 my $field_spec = shift;
320 my $force_add = shift || 0;
322 my $target_r = MARC::Record->new_from_xml( $target_xml );
323 my $source_r = MARC::Record->new_from_xml( $source_xml );
325 return $target_xml unless ($target_r && $source_r);
327 my @field_list = split(',', $field_spec);
330 for my $f (@field_list) {
331 $f =~ s/^\s*//; $f =~ s/\s*$//;
332 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
338 $match =~ s/^\s*//; $match =~ s/\s*$//;
339 $fields{$field} = { sf => [ split('', $sf) ] };
341 my ($msf,$mre) = split('~', $match);
342 if (length($msf) > 0 and length($mre) > 0) {
343 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
344 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
345 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
351 for my $f ( keys %fields) {
352 if ( @{$fields{$f}{sf}} ) {
353 for my $from_field ($source_r->field( $f )) {
354 my @tos = $target_r->field( $f );
356 next if (exists($fields{$f}{match}) and !$force_add);
357 my @new_fields = map { $_->clone } $source_r->field( $f );
358 $target_r->insert_fields_ordered( @new_fields );
360 for my $to_field (@tos) {
361 if (exists($fields{$f}{match})) {
362 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
364 my @new_sf = map { ($_ => $from_field->subfield($_)) } @{$fields{$f}{sf}};
365 $to_field->add_subfields( @new_sf );
370 my @new_fields = map { $_->clone } $source_r->field( $f );
371 $target_r->insert_fields_ordered( @new_fields );
375 $target_xml = $target_r->as_xml_record;
376 $target_xml =~ s/^<\?.+?\?>$//mo;
377 $target_xml =~ s/\n//sgo;
378 $target_xml =~ s/>\s+</></sgo;
382 $_$ LANGUAGE PLPERLU;
384 CREATE OR REPLACE FUNCTION vandelay.add_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
385 SELECT vandelay.add_field( $1, $2, $3, 0 );
388 CREATE OR REPLACE FUNCTION vandelay.strip_field ( xml TEXT, field TEXT ) RETURNS TEXT AS $_$
391 use MARC::File::XML (BinaryEncoding => 'UTF-8');
395 MARC::Charset->assume_unicode(1);
398 my $r = MARC::Record->new_from_xml( $xml );
400 return $xml unless ($r);
402 my $field_spec = shift;
403 my @field_list = split(',', $field_spec);
406 for my $f (@field_list) {
407 $f =~ s/^\s*//; $f =~ s/\s*$//;
408 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
414 $match =~ s/^\s*//; $match =~ s/\s*$//;
415 $fields{$field} = { sf => [ split('', $sf) ] };
417 my ($msf,$mre) = split('~', $match);
418 if (length($msf) > 0 and length($mre) > 0) {
419 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
420 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
421 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
427 for my $f ( keys %fields) {
428 for my $to_field ($r->field( $f )) {
429 if (exists($fields{$f}{match})) {
430 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
433 if ( @{$fields{$f}{sf}} ) {
434 $to_field->delete_subfield(code => $fields{$f}{sf});
436 $r->delete_field( $to_field );
441 $xml = $r->as_xml_record;
442 $xml =~ s/^<\?.+?\?>$//mo;
444 $xml =~ s/>\s+</></sgo;
448 $_$ LANGUAGE PLPERLU;
450 CREATE OR REPLACE FUNCTION vandelay.replace_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
457 parsed_target := vandelay.strip_field( target_xml, ''); -- this dance normalizes the format of the xml for the IF below
459 FOR curr_field IN SELECT UNNEST( STRING_TO_ARRAY(field, ',') ) LOOP -- naive split, but it's the same we use in the perl
461 xml_output := vandelay.strip_field( parsed_target, curr_field);
463 IF xml_output <> parsed_target AND curr_field ~ E'~' THEN
464 -- we removed something, and there was a regexp restriction in the curr_field definition, so proceed
465 xml_output := vandelay.add_field( xml_output, source_xml, curr_field, 1 );
466 ELSIF curr_field !~ E'~' THEN
467 -- No regexp restriction, add the curr_field
468 xml_output := vandelay.add_field( xml_output, source_xml, curr_field, 0 );
471 parsed_target := xml_output; -- in prep for any following loop iterations
477 $_$ LANGUAGE PLPGSQL;
479 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 $_$
480 SELECT vandelay.replace_field( vandelay.add_field( vandelay.strip_field( $1, $5) , $2, $3 ), $2, $4);
483 CREATE TYPE vandelay.compile_profile AS (add_rule TEXT, replace_rule TEXT, preserve_rule TEXT, strip_rule TEXT);
484 CREATE OR REPLACE FUNCTION vandelay.compile_profile ( incoming_xml TEXT ) RETURNS vandelay.compile_profile AS $_$
486 output vandelay.compile_profile%ROWTYPE;
487 profile vandelay.merge_profile%ROWTYPE;
489 profile_tmpl_owner TEXT;
491 strip_rule TEXT := '';
492 replace_rule TEXT := '';
493 preserve_rule TEXT := '';
497 profile_tmpl := (oils_xpath('//*[@tag="905"]/*[@code="t"]/text()',incoming_xml))[1];
498 profile_tmpl_owner := (oils_xpath('//*[@tag="905"]/*[@code="o"]/text()',incoming_xml))[1];
500 IF profile_tmpl IS NOT NULL AND profile_tmpl <> '' AND profile_tmpl_owner IS NOT NULL AND profile_tmpl_owner <> '' THEN
501 SELECT p.* INTO profile
502 FROM vandelay.merge_profile p
503 JOIN actor.org_unit u ON (u.id = p.owner)
504 WHERE p.name = profile_tmpl
505 AND u.shortname = profile_tmpl_owner;
507 IF profile.id IS NOT NULL THEN
508 add_rule := COALESCE(profile.add_spec,'');
509 strip_rule := COALESCE(profile.strip_spec,'');
510 replace_rule := COALESCE(profile.replace_spec,'');
511 preserve_rule := COALESCE(profile.preserve_spec,'');
515 add_rule := add_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="a"]/text()',incoming_xml),','),'');
516 strip_rule := strip_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="d"]/text()',incoming_xml),','),'');
517 replace_rule := replace_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="r"]/text()',incoming_xml),','),'');
518 preserve_rule := preserve_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="p"]/text()',incoming_xml),','),'');
520 output.add_rule := BTRIM(add_rule,',');
521 output.replace_rule := BTRIM(replace_rule,',');
522 output.strip_rule := BTRIM(strip_rule,',');
523 output.preserve_rule := BTRIM(preserve_rule,',');
527 $_$ LANGUAGE PLPGSQL;
529 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
531 merge_profile vandelay.merge_profile%ROWTYPE;
532 dyn_profile vandelay.compile_profile%ROWTYPE;
542 SELECT b.marc INTO eg_marc
543 FROM biblio.record_entry b
547 IF eg_marc IS NULL OR v_marc IS NULL THEN
548 -- RAISE NOTICE 'no marc for template or bib record';
552 dyn_profile := vandelay.compile_profile( v_marc );
554 IF merge_profile_id IS NOT NULL THEN
555 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
557 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
558 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
559 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
560 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
564 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
565 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
569 IF dyn_profile.replace_rule <> '' THEN
570 source_marc = v_marc;
571 target_marc = eg_marc;
572 replace_rule = dyn_profile.replace_rule;
574 source_marc = eg_marc;
575 target_marc = v_marc;
576 replace_rule = dyn_profile.preserve_rule;
579 UPDATE biblio.record_entry
580 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
584 -- RAISE NOTICE 'update of biblio.record_entry failed';
593 CREATE OR REPLACE FUNCTION vandelay.merge_record_xml ( target_marc TEXT, template_marc TEXT ) RETURNS TEXT AS $$
595 dyn_profile vandelay.compile_profile%ROWTYPE;
603 IF target_marc IS NULL OR template_marc IS NULL THEN
604 -- RAISE NOTICE 'no marc for target or template record';
608 dyn_profile := vandelay.compile_profile( template_marc );
610 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
611 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
615 IF dyn_profile.replace_rule <> '' THEN
616 trgt_marc = target_marc;
617 tmpl_marc = template_marc;
618 replace_rule = dyn_profile.replace_rule;
620 tmp_marc = target_marc;
621 trgt_marc = template_marc;
622 tmpl_marc = tmp_marc;
623 replace_rule = dyn_profile.preserve_rule;
626 RETURN vandelay.merge_record_xml( trgt_marc, tmpl_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule );
631 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT) RETURNS BOOL AS $$
632 SELECT vandelay.template_overlay_bib_record( $1, $2, NULL);
635 CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
637 merge_profile vandelay.merge_profile%ROWTYPE;
638 dyn_profile vandelay.compile_profile%ROWTYPE;
649 SELECT q.marc INTO v_marc
650 FROM vandelay.queued_record q
651 JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
654 IF v_marc IS NULL THEN
655 -- RAISE NOTICE 'no marc for vandelay or bib record';
659 IF vandelay.template_overlay_bib_record( v_marc, eg_id, merge_profile_id) THEN
660 UPDATE vandelay.queued_bib_record
661 SET imported_as = eg_id,
663 WHERE id = import_id;
665 editor_string := (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1];
667 IF editor_string IS NOT NULL AND editor_string <> '' THEN
668 SELECT usr INTO editor_id FROM actor.card WHERE barcode = editor_string;
670 IF editor_id IS NULL THEN
671 SELECT id INTO editor_id FROM actor.usr WHERE usrname = editor_string;
674 IF editor_id IS NOT NULL THEN
675 UPDATE biblio.record_entry SET editor = editor_id WHERE id = eg_id;
682 -- RAISE NOTICE 'update of biblio.record_entry failed';
689 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
693 match_attr vandelay.bib_attr_definition%ROWTYPE;
696 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
699 -- RAISE NOTICE 'already imported, cannot auto-overlay'
703 SELECT COUNT(*) INTO match_count FROM vandelay.bib_match WHERE queued_record = import_id;
705 IF match_count <> 1 THEN
706 -- RAISE NOTICE 'not an exact match';
710 SELECT d.* INTO match_attr
711 FROM vandelay.bib_attr_definition d
712 JOIN vandelay.queued_bib_record_attr a ON (a.field = d.id)
713 JOIN vandelay.bib_match m ON (m.matched_attr = a.id)
714 WHERE m.queued_record = import_id;
716 IF NOT (match_attr.xpath ~ '@tag="901"' AND match_attr.xpath ~ '@code="c"') THEN
717 -- RAISE NOTICE 'not a 901c match: %', match_attr.xpath;
721 SELECT m.eg_record INTO eg_id
722 FROM vandelay.bib_match m
723 WHERE m.queued_record = import_id
726 IF eg_id IS NULL THEN
730 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
734 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
736 queued_record vandelay.queued_bib_record%ROWTYPE;
739 FOR queued_record IN SELECT * FROM vandelay.queued_bib_record WHERE queue = queue_id AND import_time IS NULL LOOP
741 IF vandelay.auto_overlay_bib_record( queued_record.id, merge_profile_id ) THEN
742 RETURN NEXT queued_record.id;
752 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
753 SELECT * FROM vandelay.auto_overlay_bib_queue( $1, NULL );
756 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
781 attr_set vandelay.import_item%ROWTYPE;
787 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
791 attr_set.definition := attr_def.id;
793 -- Build the combined XPath
797 WHEN attr_def.owning_lib IS NULL THEN 'null()'
798 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
799 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
804 WHEN attr_def.circ_lib IS NULL THEN 'null()'
805 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
806 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
811 WHEN attr_def.call_number IS NULL THEN 'null()'
812 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
813 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
818 WHEN attr_def.copy_number IS NULL THEN 'null()'
819 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
820 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
825 WHEN attr_def.status IS NULL THEN 'null()'
826 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
827 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
832 WHEN attr_def.location IS NULL THEN 'null()'
833 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
834 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
839 WHEN attr_def.circulate IS NULL THEN 'null()'
840 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
841 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
846 WHEN attr_def.deposit IS NULL THEN 'null()'
847 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
848 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
853 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
854 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
855 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
860 WHEN attr_def.ref IS NULL THEN 'null()'
861 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
862 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
867 WHEN attr_def.holdable IS NULL THEN 'null()'
868 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
869 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
874 WHEN attr_def.price IS NULL THEN 'null()'
875 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
876 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
881 WHEN attr_def.barcode IS NULL THEN 'null()'
882 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
883 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
888 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
889 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
890 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
895 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
896 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
897 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
902 WHEN attr_def.alert_message IS NULL THEN 'null()'
903 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
904 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
909 WHEN attr_def.opac_visible IS NULL THEN 'null()'
910 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
911 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
916 WHEN attr_def.pub_note IS NULL THEN 'null()'
917 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
918 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
922 WHEN attr_def.priv_note IS NULL THEN 'null()'
923 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
924 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
931 call_number || '|' ||
932 copy_number || '|' ||
937 deposit_amount || '|' ||
942 circ_modifier || '|' ||
943 circ_as_type || '|' ||
944 alert_message || '|' ||
949 -- RAISE NOTICE 'XPath: %', xpath;
953 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
954 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
955 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
956 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, opac_vis TEXT )
959 tmp_attr_set.pr = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
960 tmp_attr_set.dep_amount = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
962 tmp_attr_set.pr := NULLIF( tmp_attr_set.pr, '' );
963 tmp_attr_set.dep_amount := NULLIF( tmp_attr_set.dep_amount, '' );
965 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
966 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
967 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
969 SELECT id INTO attr_set.location
970 FROM asset.copy_location
971 WHERE LOWER(name) = LOWER(tmp_attr_set.cl)
972 AND asset.copy_location.owning_lib = COALESCE(attr_set.owning_lib, attr_set.circ_lib); -- INT
974 attr_set.circulate :=
975 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
976 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
979 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
980 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
983 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
984 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
986 attr_set.opac_visible :=
987 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
988 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
991 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
992 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
994 attr_set.copy_number := tmp_attr_set.cnum::INT; -- INT,
995 attr_set.deposit_amount := tmp_attr_set.dep_amount::NUMERIC(6,2); -- NUMERIC(6,2),
996 attr_set.price := tmp_attr_set.pr::NUMERIC(8,2); -- NUMERIC(8,2),
998 attr_set.call_number := tmp_attr_set.cn; -- TEXT
999 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
1000 attr_set.circ_modifier := tmp_attr_set.circ_mod; -- TEXT,
1001 attr_set.circ_as_type := tmp_attr_set.circ_as; -- TEXT,
1002 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
1003 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
1004 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
1005 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
1007 RETURN NEXT attr_set;
1016 $$ LANGUAGE PLPGSQL;
1019 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$
1025 FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP
1027 SELECT extract_marc_field('vandelay.queued_bib_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_bib_record WHERE id = NEW.id;
1028 IF (value IS NOT NULL AND value <> '') THEN
1029 INSERT INTO vandelay.queued_bib_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
1036 $$ LANGUAGE PLPGSQL;
1038 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
1041 item_data vandelay.import_item%ROWTYPE;
1044 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
1046 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
1047 INSERT INTO vandelay.import_item (
1071 item_data.definition,
1072 item_data.owning_lib,
1074 item_data.call_number,
1075 item_data.copy_number,
1078 item_data.circulate,
1080 item_data.deposit_amount,
1085 item_data.circ_modifier,
1086 item_data.circ_as_type,
1087 item_data.alert_message,
1089 item_data.priv_note,
1090 item_data.opac_visible
1096 $func$ LANGUAGE PLPGSQL;
1098 CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
1107 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
1109 SELECT * INTO attr_def FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1;
1111 IF attr_def IS NOT NULL AND attr_def.id IS NOT NULL THEN
1112 id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr_def.xpath, attr_def.remove);
1114 IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN
1115 SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted;
1116 SELECT * INTO attr FROM vandelay.queued_bib_record_attr WHERE record = NEW.id and field = attr_def.id LIMIT 1;
1117 IF exact_id IS NOT NULL THEN
1118 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id);
1123 IF exact_id IS NULL THEN
1124 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
1126 -- All numbers? check for an id match
1127 IF (attr.attr_value ~ $r$^\d+$$r$) THEN
1128 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP
1129 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1133 -- Looks like an ISBN? check for an isbn match
1134 IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN
1135 FOR eg_rec IN EXECUTE $$SELECT * FROM metabib.full_rec fr WHERE fr.value LIKE evergreen.lowercase('$$ || attr.attr_value || $$%') AND fr.tag = '020' AND fr.subfield = 'a'$$ LOOP
1136 PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE;
1138 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
1142 -- subcheck for isbn-as-tcn
1143 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP
1144 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1148 -- check for an OCLC tcn_value match
1149 IF (attr.attr_value ~ $r$^o\d+$$r$) THEN
1150 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP
1151 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1155 -- check for a direct tcn_value match
1156 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP
1157 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1160 -- check for a direct item barcode match
1163 FROM biblio.record_entry b
1164 JOIN asset.call_number cn ON (cn.record = b.id)
1165 JOIN asset.copy cp ON (cp.call_number = cn.id)
1166 WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE
1168 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1176 $func$ LANGUAGE PLPGSQL;
1178 CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$
1180 DELETE FROM vandelay.queued_bib_record_attr WHERE record = OLD.id;
1181 DELETE FROM vandelay.import_item WHERE record = OLD.id;
1183 IF TG_OP = 'UPDATE' THEN
1188 $$ LANGUAGE PLPGSQL;
1190 CREATE TRIGGER cleanup_bib_trigger
1191 BEFORE UPDATE OR DELETE ON vandelay.queued_bib_record
1192 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_bib_marc();
1194 CREATE TRIGGER ingest_bib_trigger
1195 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1196 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_marc();
1198 CREATE TRIGGER ingest_item_trigger
1199 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1200 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_items();
1202 CREATE TRIGGER zz_match_bibs_trigger
1203 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1204 FOR EACH ROW EXECUTE PROCEDURE vandelay.match_bib_record();
1207 /* Authority stuff down here */
1208 ---------------------------------------
1209 CREATE TABLE vandelay.authority_attr_definition (
1210 id SERIAL PRIMARY KEY,
1211 code TEXT UNIQUE NOT NULL,
1213 xpath TEXT NOT NULL,
1214 remove TEXT NOT NULL DEFAULT '',
1215 ident BOOL NOT NULL DEFAULT FALSE
1218 CREATE TABLE vandelay.authority_queue (
1219 queue_type TEXT NOT NULL DEFAULT 'authority' CHECK (queue_type = 'authority'),
1220 CONSTRAINT vand_authority_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
1221 ) INHERITS (vandelay.queue);
1222 ALTER TABLE vandelay.authority_queue ADD PRIMARY KEY (id);
1224 CREATE TABLE vandelay.queued_authority_record (
1225 queue INT NOT NULL REFERENCES vandelay.authority_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1226 imported_as INT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1227 ) INHERITS (vandelay.queued_record);
1228 ALTER TABLE vandelay.queued_authority_record ADD PRIMARY KEY (id);
1229 CREATE INDEX queued_authority_record_queue_idx ON vandelay.queued_authority_record (queue);
1231 CREATE TABLE vandelay.queued_authority_record_attr (
1232 id BIGSERIAL PRIMARY KEY,
1233 record BIGINT NOT NULL REFERENCES vandelay.queued_authority_record (id) DEFERRABLE INITIALLY DEFERRED,
1234 field INT NOT NULL REFERENCES vandelay.authority_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
1235 attr_value TEXT NOT NULL
1237 CREATE INDEX queued_authority_record_attr_record_idx ON vandelay.queued_authority_record_attr (record);
1239 CREATE TABLE vandelay.authority_match (
1240 id BIGSERIAL PRIMARY KEY,
1241 matched_attr INT REFERENCES vandelay.queued_authority_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1242 queued_record BIGINT REFERENCES vandelay.queued_authority_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1243 eg_record BIGINT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1246 CREATE OR REPLACE FUNCTION vandelay.ingest_authority_marc ( ) RETURNS TRIGGER AS $$
1252 FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP
1254 SELECT extract_marc_field('vandelay.queued_authority_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_authority_record WHERE id = NEW.id;
1255 IF (value IS NOT NULL AND value <> '') THEN
1256 INSERT INTO vandelay.queued_authority_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
1263 $$ LANGUAGE PLPGSQL;
1265 CREATE OR REPLACE FUNCTION vandelay.cleanup_authority_marc ( ) RETURNS TRIGGER AS $$
1267 DELETE FROM vandelay.queued_authority_record_attr WHERE record = OLD.id;
1268 IF TG_OP = 'UPDATE' THEN
1273 $$ LANGUAGE PLPGSQL;
1275 CREATE TRIGGER cleanup_authority_trigger
1276 BEFORE UPDATE OR DELETE ON vandelay.queued_authority_record
1277 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_authority_marc();
1279 CREATE TRIGGER ingest_authority_trigger
1280 AFTER INSERT OR UPDATE ON vandelay.queued_authority_record
1281 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_authority_marc();
1283 CREATE OR REPLACE FUNCTION vandelay.overlay_authority_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1285 merge_profile vandelay.merge_profile%ROWTYPE;
1286 dyn_profile vandelay.compile_profile%ROWTYPE;
1295 SELECT b.marc INTO eg_marc
1296 FROM authority.record_entry b
1297 JOIN vandelay.authority_match m ON (m.eg_record = b.id AND m.queued_record = import_id)
1300 SELECT q.marc INTO v_marc
1301 FROM vandelay.queued_record q
1302 JOIN vandelay.authority_match m ON (m.queued_record = q.id AND q.id = import_id)
1305 IF eg_marc IS NULL OR v_marc IS NULL THEN
1306 -- RAISE NOTICE 'no marc for vandelay or authority record';
1310 dyn_profile := vandelay.compile_profile( v_marc );
1312 IF merge_profile_id IS NOT NULL THEN
1313 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
1315 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
1316 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
1317 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
1318 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
1322 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
1323 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
1327 IF dyn_profile.replace_rule <> '' THEN
1328 source_marc = v_marc;
1329 target_marc = eg_marc;
1330 replace_rule = dyn_profile.replace_rule;
1332 source_marc = eg_marc;
1333 target_marc = v_marc;
1334 replace_rule = dyn_profile.preserve_rule;
1337 UPDATE authority.record_entry
1338 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
1342 UPDATE vandelay.queued_authority_record
1343 SET imported_as = eg_id,
1345 WHERE id = import_id;
1349 -- RAISE NOTICE 'update of authority.record_entry failed';
1354 $$ LANGUAGE PLPGSQL;
1356 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1361 SELECT COUNT(*) INTO match_count FROM vandelay.authority_match WHERE queued_record = import_id;
1363 IF match_count <> 1 THEN
1364 -- RAISE NOTICE 'not an exact match';
1368 SELECT m.eg_record INTO eg_id
1369 FROM vandelay.authority_match m
1370 WHERE m.queued_record = import_id
1373 IF eg_id IS NULL THEN
1377 RETURN vandelay.overlay_authority_record( import_id, eg_id, merge_profile_id );
1379 $$ LANGUAGE PLPGSQL;
1381 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
1383 queued_record vandelay.queued_authority_record%ROWTYPE;
1386 FOR queued_record IN SELECT * FROM vandelay.queued_authority_record WHERE queue = queue_id AND import_time IS NULL LOOP
1388 IF vandelay.auto_overlay_authority_record( queued_record.id, merge_profile_id ) THEN
1389 RETURN NEXT queued_record.id;
1397 $$ LANGUAGE PLPGSQL;
1399 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
1400 SELECT * FROM vandelay.auto_overlay_authority_queue( $1, NULL );
1404 -- Vandelay (for importing and exporting records) 012.schema.vandelay.sql
1405 --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)]');
1406 --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)]');
1407 --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]');
1408 --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]');
1409 --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$);
1410 --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$);
1411 --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]');
1412 --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);
1413 --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);
1414 --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);
1415 --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);
1416 --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]');
1417 --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$);
1418 --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]');
1420 --INSERT INTO vandelay.import_item_attr_definition (
1421 -- owner, name, tag, owning_lib, circ_lib, location,
1422 -- call_number, circ_modifier, barcode, price, copy_number,
1423 -- circulate, ref, holdable, opac_visible, status
1426 -- 'Evergreen 852 export format',
1428 -- '[@code = "b"][1]',
1429 -- '[@code = "b"][2]',
1436 -- '[@code = "x" and text() = "circulating"]',
1437 -- '[@code = "x" and text() = "reference"]',
1438 -- '[@code = "x" and text() = "holdable"]',
1439 -- '[@code = "x" and text() = "visible"]',
1443 --INSERT INTO vandelay.import_item_attr_definition (
1456 -- 'Unicorn Import format -- 999',
1467 --INSERT INTO vandelay.authority_attr_definition ( code, description, xpath, ident ) VALUES ('rec_identifier','Identifier','//*[@tag="001"]', TRUE);