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 $_$
449 parsed_target := vandelay.strip_field( target_xml, ''); -- this dance normalized the format of the xml for the IF below
450 xml_output := vandelay.strip_field( parsed_target, field);
452 IF xml_output <> parsed_target AND field ~ E'~' THEN
453 -- we removed something, and there was a regexp restriction in the field definition, so proceed
454 xml_output := vandelay.add_field( xml_output, source_xml, field, 1 );
455 ELSIF field !~ E'~' THEN
456 -- No regexp restriction, add the field
457 xml_output := vandelay.add_field( xml_output, source_xml, field, 0 );
462 $_$ LANGUAGE PLPGSQL;
464 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 $_$
465 SELECT vandelay.replace_field( vandelay.add_field( vandelay.strip_field( $1, $5) , $2, $3 ), $2, $4);
468 CREATE TYPE vandelay.compile_profile AS (add_rule TEXT, replace_rule TEXT, preserve_rule TEXT, strip_rule TEXT);
469 CREATE OR REPLACE FUNCTION vandelay.compile_profile ( incoming_xml TEXT ) RETURNS vandelay.compile_profile AS $_$
471 output vandelay.compile_profile%ROWTYPE;
472 profile vandelay.merge_profile%ROWTYPE;
474 profile_tmpl_owner TEXT;
476 strip_rule TEXT := '';
477 replace_rule TEXT := '';
478 preserve_rule TEXT := '';
482 profile_tmpl := (oils_xpath('//*[@tag="905"]/*[@code="t"]/text()',incoming_xml))[1];
483 profile_tmpl_owner := (oils_xpath('//*[@tag="905"]/*[@code="o"]/text()',incoming_xml))[1];
485 IF profile_tmpl IS NOT NULL AND profile_tmpl <> '' AND profile_tmpl_owner IS NOT NULL AND profile_tmpl_owner <> '' THEN
486 SELECT p.* INTO profile
487 FROM vandelay.merge_profile p
488 JOIN actor.org_unit u ON (u.id = p.owner)
489 WHERE p.name = profile_tmpl
490 AND u.shortname = profile_tmpl_owner;
492 IF profile.id IS NOT NULL THEN
493 add_rule := COALESCE(profile.add_spec,'');
494 strip_rule := COALESCE(profile.strip_spec,'');
495 replace_rule := COALESCE(profile.replace_spec,'');
496 preserve_rule := COALESCE(profile.preserve_spec,'');
500 add_rule := add_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="a"]/text()',incoming_xml),','),'');
501 strip_rule := strip_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="d"]/text()',incoming_xml),','),'');
502 replace_rule := replace_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="r"]/text()',incoming_xml),','),'');
503 preserve_rule := preserve_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="p"]/text()',incoming_xml),','),'');
505 output.add_rule := BTRIM(add_rule,',');
506 output.replace_rule := BTRIM(replace_rule,',');
507 output.strip_rule := BTRIM(strip_rule,',');
508 output.preserve_rule := BTRIM(preserve_rule,',');
512 $_$ LANGUAGE PLPGSQL;
514 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
516 merge_profile vandelay.merge_profile%ROWTYPE;
517 dyn_profile vandelay.compile_profile%ROWTYPE;
527 SELECT b.marc INTO eg_marc
528 FROM biblio.record_entry b
532 IF eg_marc IS NULL OR v_marc IS NULL THEN
533 -- RAISE NOTICE 'no marc for template or bib record';
537 dyn_profile := vandelay.compile_profile( v_marc );
539 IF merge_profile_id IS NOT NULL THEN
540 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
542 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
543 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
544 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
545 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
549 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
550 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
554 IF dyn_profile.replace_rule <> '' THEN
555 source_marc = v_marc;
556 target_marc = eg_marc;
557 replace_rule = dyn_profile.replace_rule;
559 source_marc = eg_marc;
560 target_marc = v_marc;
561 replace_rule = dyn_profile.preserve_rule;
564 UPDATE biblio.record_entry
565 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
569 -- RAISE NOTICE 'update of biblio.record_entry failed';
578 CREATE OR REPLACE FUNCTION vandelay.merge_record_xml ( target_marc TEXT, template_marc TEXT ) RETURNS TEXT AS $$
580 dyn_profile vandelay.compile_profile%ROWTYPE;
588 IF target_marc IS NULL OR template_marc IS NULL THEN
589 -- RAISE NOTICE 'no marc for target or template record';
593 dyn_profile := vandelay.compile_profile( template_marc );
595 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
596 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
600 IF dyn_profile.replace_rule <> '' THEN
601 trgt_marc = target_marc;
602 tmpl_marc = template_marc;
603 replace_rule = dyn_profile.replace_rule;
605 tmp_marc = target_marc;
606 trgt_marc = template_marc;
607 tmpl_marc = tmp_marc;
608 replace_rule = dyn_profile.preserve_rule;
611 RETURN vandelay.merge_record_xml( trgt_marc, tmpl_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule );
616 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT) RETURNS BOOL AS $$
617 SELECT vandelay.template_overlay_bib_record( $1, $2, NULL);
620 CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
622 merge_profile vandelay.merge_profile%ROWTYPE;
623 dyn_profile vandelay.compile_profile%ROWTYPE;
634 SELECT q.marc INTO v_marc
635 FROM vandelay.queued_record q
636 JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
639 IF v_marc IS NULL THEN
640 -- RAISE NOTICE 'no marc for vandelay or bib record';
644 IF vandelay.template_overlay_bib_record( v_marc, eg_id, merge_profile_id) THEN
645 UPDATE vandelay.queued_bib_record
646 SET imported_as = eg_id,
648 WHERE id = import_id;
650 editor_string := (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1];
652 IF editor_string IS NOT NULL AND editor_string <> '' THEN
653 SELECT usr INTO editor_id FROM actor.card WHERE barcode = editor_string;
655 IF editor_id IS NULL THEN
656 SELECT id INTO editor_id FROM actor.usr WHERE usrname = editor_string;
659 IF editor_id IS NOT NULL THEN
660 UPDATE biblio.record_entry SET editor = editor_id WHERE id = eg_id;
667 -- RAISE NOTICE 'update of biblio.record_entry failed';
674 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
678 match_attr vandelay.bib_attr_definition%ROWTYPE;
681 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
684 -- RAISE NOTICE 'already imported, cannot auto-overlay'
688 SELECT COUNT(*) INTO match_count FROM vandelay.bib_match WHERE queued_record = import_id;
690 IF match_count <> 1 THEN
691 -- RAISE NOTICE 'not an exact match';
695 SELECT d.* INTO match_attr
696 FROM vandelay.bib_attr_definition d
697 JOIN vandelay.queued_bib_record_attr a ON (a.field = d.id)
698 JOIN vandelay.bib_match m ON (m.matched_attr = a.id)
699 WHERE m.queued_record = import_id;
701 IF NOT (match_attr.xpath ~ '@tag="901"' AND match_attr.xpath ~ '@code="c"') THEN
702 -- RAISE NOTICE 'not a 901c match: %', match_attr.xpath;
706 SELECT m.eg_record INTO eg_id
707 FROM vandelay.bib_match m
708 WHERE m.queued_record = import_id
711 IF eg_id IS NULL THEN
715 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
719 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
721 queued_record vandelay.queued_bib_record%ROWTYPE;
724 FOR queued_record IN SELECT * FROM vandelay.queued_bib_record WHERE queue = queue_id AND import_time IS NULL LOOP
726 IF vandelay.auto_overlay_bib_record( queued_record.id, merge_profile_id ) THEN
727 RETURN NEXT queued_record.id;
737 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
738 SELECT * FROM vandelay.auto_overlay_bib_queue( $1, NULL );
741 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
766 attr_set vandelay.import_item%ROWTYPE;
772 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
776 attr_set.definition := attr_def.id;
778 -- Build the combined XPath
782 WHEN attr_def.owning_lib IS NULL THEN 'null()'
783 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
784 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
789 WHEN attr_def.circ_lib IS NULL THEN 'null()'
790 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
791 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
796 WHEN attr_def.call_number IS NULL THEN 'null()'
797 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
798 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
803 WHEN attr_def.copy_number IS NULL THEN 'null()'
804 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
805 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
810 WHEN attr_def.status IS NULL THEN 'null()'
811 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
812 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
817 WHEN attr_def.location IS NULL THEN 'null()'
818 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
819 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
824 WHEN attr_def.circulate IS NULL THEN 'null()'
825 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
826 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
831 WHEN attr_def.deposit IS NULL THEN 'null()'
832 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
833 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
838 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
839 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
840 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
845 WHEN attr_def.ref IS NULL THEN 'null()'
846 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
847 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
852 WHEN attr_def.holdable IS NULL THEN 'null()'
853 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
854 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
859 WHEN attr_def.price IS NULL THEN 'null()'
860 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
861 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
866 WHEN attr_def.barcode IS NULL THEN 'null()'
867 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
868 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
873 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
874 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
875 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
880 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
881 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
882 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
887 WHEN attr_def.alert_message IS NULL THEN 'null()'
888 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
889 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
894 WHEN attr_def.opac_visible IS NULL THEN 'null()'
895 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
896 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
901 WHEN attr_def.pub_note IS NULL THEN 'null()'
902 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
903 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
907 WHEN attr_def.priv_note IS NULL THEN 'null()'
908 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
909 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
916 call_number || '|' ||
917 copy_number || '|' ||
922 deposit_amount || '|' ||
927 circ_modifier || '|' ||
928 circ_as_type || '|' ||
929 alert_message || '|' ||
934 -- RAISE NOTICE 'XPath: %', xpath;
938 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
939 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
940 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
941 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, opac_vis TEXT )
944 tmp_attr_set.pr = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
945 tmp_attr_set.dep_amount = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
947 tmp_attr_set.pr := NULLIF( tmp_attr_set.pr, '' );
948 tmp_attr_set.dep_amount := NULLIF( tmp_attr_set.dep_amount, '' );
950 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
951 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
952 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
954 SELECT id INTO attr_set.location
955 FROM asset.copy_location
956 WHERE LOWER(name) = LOWER(tmp_attr_set.cl)
957 AND asset.copy_location.owning_lib = COALESCE(attr_set.owning_lib, attr_set.circ_lib); -- INT
959 attr_set.circulate :=
960 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
961 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
964 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
965 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
968 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
969 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
971 attr_set.opac_visible :=
972 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
973 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
976 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
977 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
979 attr_set.copy_number := tmp_attr_set.cnum::INT; -- INT,
980 attr_set.deposit_amount := tmp_attr_set.dep_amount::NUMERIC(6,2); -- NUMERIC(6,2),
981 attr_set.price := tmp_attr_set.pr::NUMERIC(8,2); -- NUMERIC(8,2),
983 attr_set.call_number := tmp_attr_set.cn; -- TEXT
984 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
985 attr_set.circ_modifier := tmp_attr_set.circ_mod; -- TEXT,
986 attr_set.circ_as_type := tmp_attr_set.circ_as; -- TEXT,
987 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
988 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
989 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
990 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
992 RETURN NEXT attr_set;
1001 $$ LANGUAGE PLPGSQL;
1004 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$
1010 FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP
1012 SELECT extract_marc_field('vandelay.queued_bib_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_bib_record WHERE id = NEW.id;
1013 IF (value IS NOT NULL AND value <> '') THEN
1014 INSERT INTO vandelay.queued_bib_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
1021 $$ LANGUAGE PLPGSQL;
1023 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
1026 item_data vandelay.import_item%ROWTYPE;
1029 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
1031 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
1032 INSERT INTO vandelay.import_item (
1056 item_data.definition,
1057 item_data.owning_lib,
1059 item_data.call_number,
1060 item_data.copy_number,
1063 item_data.circulate,
1065 item_data.deposit_amount,
1070 item_data.circ_modifier,
1071 item_data.circ_as_type,
1072 item_data.alert_message,
1074 item_data.priv_note,
1075 item_data.opac_visible
1081 $func$ LANGUAGE PLPGSQL;
1083 CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
1092 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
1094 SELECT * INTO attr_def FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1;
1096 IF attr_def IS NOT NULL AND attr_def.id IS NOT NULL THEN
1097 id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr_def.xpath, attr_def.remove);
1099 IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN
1100 SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted;
1101 SELECT * INTO attr FROM vandelay.queued_bib_record_attr WHERE record = NEW.id and field = attr_def.id LIMIT 1;
1102 IF exact_id IS NOT NULL THEN
1103 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id);
1108 IF exact_id IS NULL THEN
1109 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
1111 -- All numbers? check for an id match
1112 IF (attr.attr_value ~ $r$^\d+$$r$) THEN
1113 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP
1114 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1118 -- Looks like an ISBN? check for an isbn match
1119 IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN
1120 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
1121 PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE;
1123 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
1127 -- subcheck for isbn-as-tcn
1128 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP
1129 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1133 -- check for an OCLC tcn_value match
1134 IF (attr.attr_value ~ $r$^o\d+$$r$) THEN
1135 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP
1136 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1140 -- check for a direct tcn_value match
1141 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP
1142 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1145 -- check for a direct item barcode match
1148 FROM biblio.record_entry b
1149 JOIN asset.call_number cn ON (cn.record = b.id)
1150 JOIN asset.copy cp ON (cp.call_number = cn.id)
1151 WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE
1153 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1161 $func$ LANGUAGE PLPGSQL;
1163 CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$
1165 DELETE FROM vandelay.queued_bib_record_attr WHERE record = OLD.id;
1166 DELETE FROM vandelay.import_item WHERE record = OLD.id;
1168 IF TG_OP = 'UPDATE' THEN
1173 $$ LANGUAGE PLPGSQL;
1175 CREATE TRIGGER cleanup_bib_trigger
1176 BEFORE UPDATE OR DELETE ON vandelay.queued_bib_record
1177 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_bib_marc();
1179 CREATE TRIGGER ingest_bib_trigger
1180 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1181 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_marc();
1183 CREATE TRIGGER ingest_item_trigger
1184 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1185 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_items();
1187 CREATE TRIGGER zz_match_bibs_trigger
1188 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1189 FOR EACH ROW EXECUTE PROCEDURE vandelay.match_bib_record();
1192 /* Authority stuff down here */
1193 ---------------------------------------
1194 CREATE TABLE vandelay.authority_attr_definition (
1195 id SERIAL PRIMARY KEY,
1196 code TEXT UNIQUE NOT NULL,
1198 xpath TEXT NOT NULL,
1199 remove TEXT NOT NULL DEFAULT '',
1200 ident BOOL NOT NULL DEFAULT FALSE
1203 CREATE TABLE vandelay.authority_queue (
1204 queue_type TEXT NOT NULL DEFAULT 'authority' CHECK (queue_type = 'authority'),
1205 CONSTRAINT vand_authority_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
1206 ) INHERITS (vandelay.queue);
1207 ALTER TABLE vandelay.authority_queue ADD PRIMARY KEY (id);
1209 CREATE TABLE vandelay.queued_authority_record (
1210 queue INT NOT NULL REFERENCES vandelay.authority_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1211 imported_as INT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1212 ) INHERITS (vandelay.queued_record);
1213 ALTER TABLE vandelay.queued_authority_record ADD PRIMARY KEY (id);
1214 CREATE INDEX queued_authority_record_queue_idx ON vandelay.queued_authority_record (queue);
1216 CREATE TABLE vandelay.queued_authority_record_attr (
1217 id BIGSERIAL PRIMARY KEY,
1218 record BIGINT NOT NULL REFERENCES vandelay.queued_authority_record (id) DEFERRABLE INITIALLY DEFERRED,
1219 field INT NOT NULL REFERENCES vandelay.authority_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
1220 attr_value TEXT NOT NULL
1222 CREATE INDEX queued_authority_record_attr_record_idx ON vandelay.queued_authority_record_attr (record);
1224 CREATE TABLE vandelay.authority_match (
1225 id BIGSERIAL PRIMARY KEY,
1226 matched_attr INT REFERENCES vandelay.queued_authority_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1227 queued_record BIGINT REFERENCES vandelay.queued_authority_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1228 eg_record BIGINT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1231 CREATE OR REPLACE FUNCTION vandelay.ingest_authority_marc ( ) RETURNS TRIGGER AS $$
1237 FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP
1239 SELECT extract_marc_field('vandelay.queued_authority_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_authority_record WHERE id = NEW.id;
1240 IF (value IS NOT NULL AND value <> '') THEN
1241 INSERT INTO vandelay.queued_authority_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
1248 $$ LANGUAGE PLPGSQL;
1250 CREATE OR REPLACE FUNCTION vandelay.cleanup_authority_marc ( ) RETURNS TRIGGER AS $$
1252 DELETE FROM vandelay.queued_authority_record_attr WHERE record = OLD.id;
1253 IF TG_OP = 'UPDATE' THEN
1258 $$ LANGUAGE PLPGSQL;
1260 CREATE TRIGGER cleanup_authority_trigger
1261 BEFORE UPDATE OR DELETE ON vandelay.queued_authority_record
1262 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_authority_marc();
1264 CREATE TRIGGER ingest_authority_trigger
1265 AFTER INSERT OR UPDATE ON vandelay.queued_authority_record
1266 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_authority_marc();
1268 CREATE OR REPLACE FUNCTION vandelay.overlay_authority_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1270 merge_profile vandelay.merge_profile%ROWTYPE;
1271 dyn_profile vandelay.compile_profile%ROWTYPE;
1280 SELECT b.marc INTO eg_marc
1281 FROM authority.record_entry b
1282 JOIN vandelay.authority_match m ON (m.eg_record = b.id AND m.queued_record = import_id)
1285 SELECT q.marc INTO v_marc
1286 FROM vandelay.queued_record q
1287 JOIN vandelay.authority_match m ON (m.queued_record = q.id AND q.id = import_id)
1290 IF eg_marc IS NULL OR v_marc IS NULL THEN
1291 -- RAISE NOTICE 'no marc for vandelay or authority record';
1295 dyn_profile := vandelay.compile_profile( v_marc );
1297 IF merge_profile_id IS NOT NULL THEN
1298 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
1300 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
1301 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
1302 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
1303 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
1307 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
1308 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
1312 IF dyn_profile.replace_rule <> '' THEN
1313 source_marc = v_marc;
1314 target_marc = eg_marc;
1315 replace_rule = dyn_profile.replace_rule;
1317 source_marc = eg_marc;
1318 target_marc = v_marc;
1319 replace_rule = dyn_profile.preserve_rule;
1322 UPDATE authority.record_entry
1323 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
1327 UPDATE vandelay.queued_authority_record
1328 SET imported_as = eg_id,
1330 WHERE id = import_id;
1334 -- RAISE NOTICE 'update of authority.record_entry failed';
1339 $$ LANGUAGE PLPGSQL;
1341 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1346 SELECT COUNT(*) INTO match_count FROM vandelay.authority_match WHERE queued_record = import_id;
1348 IF match_count <> 1 THEN
1349 -- RAISE NOTICE 'not an exact match';
1353 SELECT m.eg_record INTO eg_id
1354 FROM vandelay.authority_match m
1355 WHERE m.queued_record = import_id
1358 IF eg_id IS NULL THEN
1362 RETURN vandelay.overlay_authority_record( import_id, eg_id, merge_profile_id );
1364 $$ LANGUAGE PLPGSQL;
1366 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
1368 queued_record vandelay.queued_authority_record%ROWTYPE;
1371 FOR queued_record IN SELECT * FROM vandelay.queued_authority_record WHERE queue = queue_id AND import_time IS NULL LOOP
1373 IF vandelay.auto_overlay_authority_record( queued_record.id, merge_profile_id ) THEN
1374 RETURN NEXT queued_record.id;
1382 $$ LANGUAGE PLPGSQL;
1384 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
1385 SELECT * FROM vandelay.auto_overlay_authority_queue( $1, NULL );
1389 -- Vandelay (for importing and exporting records) 012.schema.vandelay.sql
1390 --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)]');
1391 --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)]');
1392 --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]');
1393 --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]');
1394 --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$);
1395 --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$);
1396 --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]');
1397 --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);
1398 --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);
1399 --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);
1400 --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);
1401 --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]');
1402 --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$);
1403 --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]');
1405 --INSERT INTO vandelay.import_item_attr_definition (
1406 -- owner, name, tag, owning_lib, circ_lib, location,
1407 -- call_number, circ_modifier, barcode, price, copy_number,
1408 -- circulate, ref, holdable, opac_visible, status
1411 -- 'Evergreen 852 export format',
1413 -- '[@code = "b"][1]',
1414 -- '[@code = "b"][2]',
1421 -- '[@code = "x" and text() = "circulating"]',
1422 -- '[@code = "x" and text() = "reference"]',
1423 -- '[@code = "x" and text() = "holdable"]',
1424 -- '[@code = "x" and text() = "visible"]',
1428 --INSERT INTO vandelay.import_item_attr_definition (
1441 -- 'Unicorn Import format -- 999',
1452 --INSERT INTO vandelay.authority_attr_definition ( code, description, xpath, ident ) VALUES ('rec_identifier','Identifier','//*[@tag="001"]', TRUE);