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');
314 my $target_xml = shift;
315 my $source_xml = shift;
316 my $field_spec = shift;
317 my $force_add = shift || 0;
319 my $target_r = MARC::Record->new_from_xml( $target_xml );
320 my $source_r = MARC::Record->new_from_xml( $source_xml );
322 return $target_xml unless ($target_r && $source_r);
324 my @field_list = split(',', $field_spec);
327 for my $f (@field_list) {
328 $f =~ s/^\s*//; $f =~ s/\s*$//;
329 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
335 $match =~ s/^\s*//; $match =~ s/\s*$//;
336 $fields{$field} = { sf => [ split('', $sf) ] };
338 my ($msf,$mre) = split('~', $match);
339 if (length($msf) > 0 and length($mre) > 0) {
340 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
341 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
342 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
348 for my $f ( keys %fields) {
349 if ( @{$fields{$f}{sf}} ) {
350 for my $from_field ($source_r->field( $f )) {
351 my @tos = $target_r->field( $f );
353 next if (exists($fields{$f}{match}) and !$force_add);
354 my @new_fields = map { $_->clone } $source_r->field( $f );
355 $target_r->insert_fields_ordered( @new_fields );
357 for my $to_field (@tos) {
358 if (exists($fields{$f}{match})) {
359 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
361 my @new_sf = map { ($_ => $from_field->subfield($_)) } @{$fields{$f}{sf}};
362 $to_field->add_subfields( @new_sf );
367 my @new_fields = map { $_->clone } $source_r->field( $f );
368 $target_r->insert_fields_ordered( @new_fields );
372 $target_xml = $target_r->as_xml_record;
373 $target_xml =~ s/^<\?.+?\?>$//mo;
374 $target_xml =~ s/\n//sgo;
375 $target_xml =~ s/>\s+</></sgo;
379 $_$ LANGUAGE PLPERLU;
381 CREATE OR REPLACE FUNCTION vandelay.add_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
382 SELECT vandelay.add_field( $1, $2 $3, 0 );
385 CREATE OR REPLACE FUNCTION vandelay.strip_field ( xml TEXT, field TEXT ) RETURNS TEXT AS $_$
388 use MARC::File::XML (BinaryEncoding => 'UTF-8');
392 my $r = MARC::Record->new_from_xml( $xml );
394 return $xml unless ($r);
396 my $field_spec = shift;
397 my @field_list = split(',', $field_spec);
400 for my $f (@field_list) {
401 $f =~ s/^\s*//; $f =~ s/\s*$//;
402 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
408 $match =~ s/^\s*//; $match =~ s/\s*$//;
409 $fields{$field} = { sf => [ split('', $sf) ] };
411 my ($msf,$mre) = split('~', $match);
412 if (length($msf) > 0 and length($mre) > 0) {
413 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
414 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
415 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
421 for my $f ( keys %fields) {
422 for my $to_field ($r->field( $f )) {
423 if (exists($fields{$f}{match})) {
424 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
427 if ( @{$fields{$f}{sf}} ) {
428 $to_field->delete_subfield(code => $fields{$f}{sf});
430 $r->delete_field( $to_field );
435 $xml = $r->as_xml_record;
436 $xml =~ s/^<\?.+?\?>$//mo;
438 $xml =~ s/>\s+</></sgo;
442 $_$ LANGUAGE PLPERLU;
444 CREATE OR REPLACE FUNCTION vandelay.replace_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
448 xml_output := vandelay.strip_field( target_xml, field);
450 IF xml_output <> target_xml AND field ~ E'~' THEN
451 -- we removed something, and there was a regexp restriction in the field definition, so proceed
452 xml_output := vandelay.add_field( xml_output, source_xml, field, 1 );
453 ELSIF field !~ E'~' THEN
454 -- No regexp restriction, add the field
455 xml_output := vandelay.add_field( xml_output, source_xml, field, 0 );
460 $_$ LANGUAGE PLPGSQL;
462 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 $_$
463 SELECT vandelay.replace_field( vandelay.add_field( vandelay.strip_field( $1, $5) , $2, $3 ), $2, $4);
466 CREATE TYPE vandelay.compile_profile AS (add_rule TEXT, replace_rule TEXT, preserve_rule TEXT, strip_rule TEXT);
467 CREATE OR REPLACE FUNCTION vandelay.compile_profile ( incoming_xml TEXT ) RETURNS vandelay.compile_profile AS $_$
469 output vandelay.compile_profile%ROWTYPE;
470 profile vandelay.merge_profile%ROWTYPE;
472 profile_tmpl_owner TEXT;
474 strip_rule TEXT := '';
475 replace_rule TEXT := '';
476 preserve_rule TEXT := '';
480 profile_tmpl := (oils_xpath('//*[@tag="905"]/*[@code="t"]/text()',incoming_xml))[1];
481 profile_tmpl_owner := (oils_xpath('//*[@tag="905"]/*[@code="o"]/text()',incoming_xml))[1];
483 IF profile_tmpl IS NOT NULL AND profile_tmpl <> '' AND profile_tmpl_owner IS NOT NULL AND profile_tmpl_owner <> '' THEN
484 SELECT p.* INTO profile
485 FROM vandelay.merge_profile p
486 JOIN actor.org_unit u ON (u.id = p.owner)
487 WHERE p.name = profile_tmpl
488 AND u.shortname = profile_tmpl_owner;
490 IF profile.id IS NOT NULL THEN
491 add_rule := COALESCE(profile.add_spec,'');
492 strip_rule := COALESCE(profile.strip_spec,'');
493 replace_rule := COALESCE(profile.replace_spec,'');
494 preserve_rule := COALESCE(profile.preserve_spec,'');
498 add_rule := add_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="a"]/text()',incoming_xml),','),'');
499 strip_rule := strip_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="d"]/text()',incoming_xml),','),'');
500 replace_rule := replace_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="r"]/text()',incoming_xml),','),'');
501 preserve_rule := preserve_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="p"]/text()',incoming_xml),','),'');
503 output.add_rule := BTRIM(add_rule,',');
504 output.replace_rule := BTRIM(replace_rule,',');
505 output.strip_rule := BTRIM(strip_rule,',');
506 output.preserve_rule := BTRIM(preserve_rule,',');
510 $_$ LANGUAGE PLPGSQL;
512 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
514 merge_profile vandelay.merge_profile%ROWTYPE;
515 dyn_profile vandelay.compile_profile%ROWTYPE;
525 SELECT b.marc INTO eg_marc
526 FROM biblio.record_entry b
530 IF eg_marc IS NULL OR v_marc IS NULL THEN
531 -- RAISE NOTICE 'no marc for template or bib record';
535 dyn_profile := vandelay.compile_profile( v_marc );
537 IF merge_profile_id IS NOT NULL THEN
538 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
540 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
541 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
542 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
543 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
547 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
548 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
552 IF dyn_profile.replace_rule <> '' THEN
553 source_marc = v_marc;
554 target_marc = eg_marc;
555 replace_rule = dyn_profile.replace_rule;
557 source_marc = eg_marc;
558 target_marc = v_marc;
559 replace_rule = dyn_profile.preserve_rule;
562 UPDATE biblio.record_entry
563 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
567 -- RAISE NOTICE 'update of biblio.record_entry failed';
576 CREATE OR REPLACE FUNCTION vandelay.merge_record_xml ( target_marc TEXT, template_marc TEXT ) RETURNS TEXT AS $$
578 dyn_profile vandelay.compile_profile%ROWTYPE;
586 IF target_marc IS NULL OR template_marc IS NULL THEN
587 -- RAISE NOTICE 'no marc for target or template record';
591 dyn_profile := vandelay.compile_profile( template_marc );
593 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
594 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
598 IF dyn_profile.replace_rule <> '' THEN
599 trgt_marc = target_marc;
600 tmpl_marc = template_marc;
601 replace_rule = dyn_profile.replace_rule;
603 tmp_marc = target_marc;
604 trgt_marc = template_marc;
605 tmpl_marc = tmp_marc;
606 replace_rule = dyn_profile.preserve_rule;
609 RETURN vandelay.merge_record_xml( trgt_marc, tmpl_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule );
614 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT) RETURNS BOOL AS $$
615 SELECT vandelay.template_overlay_bib_record( $1, $2, NULL);
618 CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
620 merge_profile vandelay.merge_profile%ROWTYPE;
621 dyn_profile vandelay.compile_profile%ROWTYPE;
632 SELECT q.marc INTO v_marc
633 FROM vandelay.queued_record q
634 JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
637 IF v_marc IS NULL THEN
638 -- RAISE NOTICE 'no marc for vandelay or bib record';
642 IF vandelay.template_overlay_bib_record( v_marc, eg_id, merge_profile_id) THEN
643 UPDATE vandelay.queued_bib_record
644 SET imported_as = eg_id,
646 WHERE id = import_id;
648 editor_string := (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1];
650 IF editor_string IS NOT NULL AND editor_string <> '' THEN
651 SELECT usr INTO editor_id FROM actor.card WHERE barcode = editor_string;
653 IF editor_id IS NULL THEN
654 SELECT id INTO editor_id FROM actor.usr WHERE usrname = editor_string;
657 IF editor_id IS NOT NULL THEN
658 UPDATE biblio.record_entry SET editor = editor_id WHERE id = eg_id;
665 -- RAISE NOTICE 'update of biblio.record_entry failed';
672 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
676 match_attr vandelay.bib_attr_definition%ROWTYPE;
679 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
682 -- RAISE NOTICE 'already imported, cannot auto-overlay'
686 SELECT COUNT(*) INTO match_count FROM vandelay.bib_match WHERE queued_record = import_id;
688 IF match_count <> 1 THEN
689 -- RAISE NOTICE 'not an exact match';
693 SELECT d.* INTO match_attr
694 FROM vandelay.bib_attr_definition d
695 JOIN vandelay.queued_bib_record_attr a ON (a.field = d.id)
696 JOIN vandelay.bib_match m ON (m.matched_attr = a.id)
697 WHERE m.queued_record = import_id;
699 IF NOT (match_attr.xpath ~ '@tag="901"' AND match_attr.xpath ~ '@code="c"') THEN
700 -- RAISE NOTICE 'not a 901c match: %', match_attr.xpath;
704 SELECT m.eg_record INTO eg_id
705 FROM vandelay.bib_match m
706 WHERE m.queued_record = import_id
709 IF eg_id IS NULL THEN
713 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
717 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
719 queued_record vandelay.queued_bib_record%ROWTYPE;
722 FOR queued_record IN SELECT * FROM vandelay.queued_bib_record WHERE queue = queue_id AND import_time IS NULL LOOP
724 IF vandelay.auto_overlay_bib_record( queued_record.id, merge_profile_id ) THEN
725 RETURN NEXT queued_record.id;
735 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
736 SELECT * FROM vandelay.auto_overlay_bib_queue( $1, NULL );
739 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
764 attr_set vandelay.import_item%ROWTYPE;
770 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
774 attr_set.definition := attr_def.id;
776 -- Build the combined XPath
780 WHEN attr_def.owning_lib IS NULL THEN 'null()'
781 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
782 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
787 WHEN attr_def.circ_lib IS NULL THEN 'null()'
788 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
789 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
794 WHEN attr_def.call_number IS NULL THEN 'null()'
795 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
796 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
801 WHEN attr_def.copy_number IS NULL THEN 'null()'
802 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
803 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
808 WHEN attr_def.status IS NULL THEN 'null()'
809 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
810 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
815 WHEN attr_def.location IS NULL THEN 'null()'
816 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
817 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
822 WHEN attr_def.circulate IS NULL THEN 'null()'
823 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
824 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
829 WHEN attr_def.deposit IS NULL THEN 'null()'
830 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
831 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
836 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
837 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
838 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
843 WHEN attr_def.ref IS NULL THEN 'null()'
844 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
845 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
850 WHEN attr_def.holdable IS NULL THEN 'null()'
851 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
852 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
857 WHEN attr_def.price IS NULL THEN 'null()'
858 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
859 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
864 WHEN attr_def.barcode IS NULL THEN 'null()'
865 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
866 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
871 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
872 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
873 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
878 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
879 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
880 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
885 WHEN attr_def.alert_message IS NULL THEN 'null()'
886 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
887 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
892 WHEN attr_def.opac_visible IS NULL THEN 'null()'
893 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
894 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
899 WHEN attr_def.pub_note IS NULL THEN 'null()'
900 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
901 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
905 WHEN attr_def.priv_note IS NULL THEN 'null()'
906 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
907 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
914 call_number || '|' ||
915 copy_number || '|' ||
920 deposit_amount || '|' ||
925 circ_modifier || '|' ||
926 circ_as_type || '|' ||
927 alert_message || '|' ||
932 -- RAISE NOTICE 'XPath: %', xpath;
936 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
937 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
938 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
939 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, opac_vis TEXT )
942 tmp_attr_set.pr = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
943 tmp_attr_set.dep_amount = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
945 tmp_attr_set.pr := NULLIF( tmp_attr_set.pr, '' );
946 tmp_attr_set.dep_amount := NULLIF( tmp_attr_set.dep_amount, '' );
948 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
949 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
950 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
952 SELECT id INTO attr_set.location
953 FROM asset.copy_location
954 WHERE LOWER(name) = LOWER(tmp_attr_set.cl)
955 AND asset.copy_location.owning_lib = COALESCE(attr_set.owning_lib, attr_set.circ_lib); -- INT
957 attr_set.circulate :=
958 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
959 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
962 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
963 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
966 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
967 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
969 attr_set.opac_visible :=
970 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
971 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
974 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
975 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
977 attr_set.copy_number := tmp_attr_set.cnum::INT; -- INT,
978 attr_set.deposit_amount := tmp_attr_set.dep_amount::NUMERIC(6,2); -- NUMERIC(6,2),
979 attr_set.price := tmp_attr_set.pr::NUMERIC(8,2); -- NUMERIC(8,2),
981 attr_set.call_number := tmp_attr_set.cn; -- TEXT
982 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
983 attr_set.circ_modifier := tmp_attr_set.circ_mod; -- TEXT,
984 attr_set.circ_as_type := tmp_attr_set.circ_as; -- TEXT,
985 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
986 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
987 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
988 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
990 RETURN NEXT attr_set;
1002 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$
1008 FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP
1010 SELECT extract_marc_field('vandelay.queued_bib_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_bib_record WHERE id = NEW.id;
1011 IF (value IS NOT NULL AND value <> '') THEN
1012 INSERT INTO vandelay.queued_bib_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
1019 $$ LANGUAGE PLPGSQL;
1021 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
1024 item_data vandelay.import_item%ROWTYPE;
1027 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
1029 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
1030 INSERT INTO vandelay.import_item (
1054 item_data.definition,
1055 item_data.owning_lib,
1057 item_data.call_number,
1058 item_data.copy_number,
1061 item_data.circulate,
1063 item_data.deposit_amount,
1068 item_data.circ_modifier,
1069 item_data.circ_as_type,
1070 item_data.alert_message,
1072 item_data.priv_note,
1073 item_data.opac_visible
1079 $func$ LANGUAGE PLPGSQL;
1081 CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
1090 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
1092 SELECT * INTO attr_def FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1;
1094 IF attr_def IS NOT NULL AND attr_def.id IS NOT NULL THEN
1095 id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr_def.xpath, attr_def.remove);
1097 IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN
1098 SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted;
1099 SELECT * INTO attr FROM vandelay.queued_bib_record_attr WHERE record = NEW.id and field = attr_def.id LIMIT 1;
1100 IF exact_id IS NOT NULL THEN
1101 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id);
1106 IF exact_id IS NULL THEN
1107 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
1109 -- All numbers? check for an id match
1110 IF (attr.attr_value ~ $r$^\d+$$r$) THEN
1111 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP
1112 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1116 -- Looks like an ISBN? check for an isbn match
1117 IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN
1118 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
1119 PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE;
1121 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
1125 -- subcheck for isbn-as-tcn
1126 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP
1127 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1131 -- check for an OCLC tcn_value match
1132 IF (attr.attr_value ~ $r$^o\d+$$r$) THEN
1133 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP
1134 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1138 -- check for a direct tcn_value match
1139 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP
1140 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1143 -- check for a direct item barcode match
1146 FROM biblio.record_entry b
1147 JOIN asset.call_number cn ON (cn.record = b.id)
1148 JOIN asset.copy cp ON (cp.call_number = cn.id)
1149 WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE
1151 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1159 $func$ LANGUAGE PLPGSQL;
1161 CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$
1163 DELETE FROM vandelay.queued_bib_record_attr WHERE record = OLD.id;
1164 DELETE FROM vandelay.import_item WHERE record = OLD.id;
1166 IF TG_OP = 'UPDATE' THEN
1171 $$ LANGUAGE PLPGSQL;
1173 CREATE TRIGGER cleanup_bib_trigger
1174 BEFORE UPDATE OR DELETE ON vandelay.queued_bib_record
1175 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_bib_marc();
1177 CREATE TRIGGER ingest_bib_trigger
1178 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1179 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_marc();
1181 CREATE TRIGGER ingest_item_trigger
1182 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1183 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_items();
1185 CREATE TRIGGER zz_match_bibs_trigger
1186 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1187 FOR EACH ROW EXECUTE PROCEDURE vandelay.match_bib_record();
1190 /* Authority stuff down here */
1191 ---------------------------------------
1192 CREATE TABLE vandelay.authority_attr_definition (
1193 id SERIAL PRIMARY KEY,
1194 code TEXT UNIQUE NOT NULL,
1196 xpath TEXT NOT NULL,
1197 remove TEXT NOT NULL DEFAULT '',
1198 ident BOOL NOT NULL DEFAULT FALSE
1201 CREATE TABLE vandelay.authority_queue (
1202 queue_type TEXT NOT NULL DEFAULT 'authority' CHECK (queue_type = 'authority'),
1203 CONSTRAINT vand_authority_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
1204 ) INHERITS (vandelay.queue);
1205 ALTER TABLE vandelay.authority_queue ADD PRIMARY KEY (id);
1207 CREATE TABLE vandelay.queued_authority_record (
1208 queue INT NOT NULL REFERENCES vandelay.authority_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1209 imported_as INT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1210 ) INHERITS (vandelay.queued_record);
1211 ALTER TABLE vandelay.queued_authority_record ADD PRIMARY KEY (id);
1212 CREATE INDEX queued_authority_record_queue_idx ON vandelay.queued_authority_record (queue);
1214 CREATE TABLE vandelay.queued_authority_record_attr (
1215 id BIGSERIAL PRIMARY KEY,
1216 record BIGINT NOT NULL REFERENCES vandelay.queued_authority_record (id) DEFERRABLE INITIALLY DEFERRED,
1217 field INT NOT NULL REFERENCES vandelay.authority_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
1218 attr_value TEXT NOT NULL
1220 CREATE INDEX queued_authority_record_attr_record_idx ON vandelay.queued_authority_record_attr (record);
1222 CREATE TABLE vandelay.authority_match (
1223 id BIGSERIAL PRIMARY KEY,
1224 matched_attr INT REFERENCES vandelay.queued_authority_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1225 queued_record BIGINT REFERENCES vandelay.queued_authority_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1226 eg_record BIGINT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1229 CREATE OR REPLACE FUNCTION vandelay.ingest_authority_marc ( ) RETURNS TRIGGER AS $$
1235 FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP
1237 SELECT extract_marc_field('vandelay.queued_authority_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_authority_record WHERE id = NEW.id;
1238 IF (value IS NOT NULL AND value <> '') THEN
1239 INSERT INTO vandelay.queued_authority_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
1246 $$ LANGUAGE PLPGSQL;
1248 CREATE OR REPLACE FUNCTION vandelay.cleanup_authority_marc ( ) RETURNS TRIGGER AS $$
1250 DELETE FROM vandelay.queued_authority_record_attr WHERE record = OLD.id;
1251 IF TG_OP = 'UPDATE' THEN
1256 $$ LANGUAGE PLPGSQL;
1258 CREATE TRIGGER cleanup_authority_trigger
1259 BEFORE UPDATE OR DELETE ON vandelay.queued_authority_record
1260 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_authority_marc();
1262 CREATE TRIGGER ingest_authority_trigger
1263 AFTER INSERT OR UPDATE ON vandelay.queued_authority_record
1264 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_authority_marc();
1266 CREATE OR REPLACE FUNCTION vandelay.overlay_authority_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1268 merge_profile vandelay.merge_profile%ROWTYPE;
1269 dyn_profile vandelay.compile_profile%ROWTYPE;
1278 SELECT b.marc INTO eg_marc
1279 FROM authority.record_entry b
1280 JOIN vandelay.authority_match m ON (m.eg_record = b.id AND m.queued_record = import_id)
1283 SELECT q.marc INTO v_marc
1284 FROM vandelay.queued_record q
1285 JOIN vandelay.authority_match m ON (m.queued_record = q.id AND q.id = import_id)
1288 IF eg_marc IS NULL OR v_marc IS NULL THEN
1289 -- RAISE NOTICE 'no marc for vandelay or authority record';
1293 dyn_profile := vandelay.compile_profile( v_marc );
1295 IF merge_profile_id IS NOT NULL THEN
1296 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
1298 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
1299 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
1300 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
1301 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
1305 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
1306 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
1310 IF dyn_profile.replace_rule <> '' THEN
1311 source_marc = v_marc;
1312 target_marc = eg_marc;
1313 replace_rule = dyn_profile.replace_rule;
1315 source_marc = eg_marc;
1316 target_marc = v_marc;
1317 replace_rule = dyn_profile.preserve_rule;
1320 UPDATE authority.record_entry
1321 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
1325 UPDATE vandelay.queued_authority_record
1326 SET imported_as = eg_id,
1328 WHERE id = import_id;
1332 -- RAISE NOTICE 'update of authority.record_entry failed';
1337 $$ LANGUAGE PLPGSQL;
1339 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1344 SELECT COUNT(*) INTO match_count FROM vandelay.authority_match WHERE queued_record = import_id;
1346 IF match_count <> 1 THEN
1347 -- RAISE NOTICE 'not an exact match';
1351 SELECT m.eg_record INTO eg_id
1352 FROM vandelay.authority_match m
1353 WHERE m.queued_record = import_id
1356 IF eg_id IS NULL THEN
1360 RETURN vandelay.overlay_authority_record( import_id, eg_id, merge_profile_id );
1362 $$ LANGUAGE PLPGSQL;
1364 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
1366 queued_record vandelay.queued_authority_record%ROWTYPE;
1369 FOR queued_record IN SELECT * FROM vandelay.queued_authority_record WHERE queue = queue_id AND import_time IS NULL LOOP
1371 IF vandelay.auto_overlay_authority_record( queued_record.id, merge_profile_id ) THEN
1372 RETURN NEXT queued_record.id;
1380 $$ LANGUAGE PLPGSQL;
1382 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
1383 SELECT * FROM vandelay.auto_overlay_authority_queue( $1, NULL );
1387 -- Vandelay (for importing and exporting records) 012.schema.vandelay.sql
1388 --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)]');
1389 --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)]');
1390 --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]');
1391 --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]');
1392 --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$);
1393 --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$);
1394 --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]');
1395 --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);
1396 --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);
1397 --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);
1398 --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);
1399 --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]');
1400 --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$);
1401 --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]');
1403 --INSERT INTO vandelay.import_item_attr_definition (
1404 -- owner, name, tag, owning_lib, circ_lib, location,
1405 -- call_number, circ_modifier, barcode, price, copy_number,
1406 -- circulate, ref, holdable, opac_visible, status
1409 -- 'Evergreen 852 export format',
1411 -- '[@code = "b"][1]',
1412 -- '[@code = "b"][2]',
1419 -- '[@code = "x" and text() = "circulating"]',
1420 -- '[@code = "x" and text() = "reference"]',
1421 -- '[@code = "x" and text() = "holdable"]',
1422 -- '[@code = "x" and text() = "visible"]',
1426 --INSERT INTO vandelay.import_item_attr_definition (
1439 -- 'Unicorn Import format -- 999',
1450 --INSERT INTO vandelay.authority_attr_definition ( code, description, xpath, ident ) VALUES ('rec_identifier','Identifier','//*[@tag="001"]', TRUE);