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 ) 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;
318 my $target_r = MARC::Record->new_from_xml( $target_xml );
319 my $source_r = MARC::Record->new_from_xml( $source_xml );
321 return $target_xml unless ($target_r && $source_r);
323 my @field_list = split(',', $field_spec);
326 for my $f (@field_list) {
327 $f =~ s/^\s*//; $f =~ s/\s*$//;
328 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
334 $match =~ s/^\s*//; $match =~ s/\s*$//;
335 $fields{$field} = { sf => [ split('', $sf) ] };
337 my ($msf,$mre) = split('~', $match);
338 if (length($msf) > 0 and length($mre) > 0) {
339 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
340 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
341 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
347 for my $f ( keys %fields) {
348 if ( @{$fields{$f}{sf}} ) {
349 for my $from_field ($source_r->field( $f )) {
350 my @tos = $target_r->field( $f );
352 my @new_fields = map { $_->clone } $source_r->field( $f );
353 $target_r->insert_fields_ordered( @new_fields );
355 for my $to_field (@tos) {
356 if (exists($fields{$f}{match})) {
357 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
359 my @new_sf = map { ($_ => $from_field->subfield($_)) } @{$fields{$f}{sf}};
360 $to_field->add_subfields( @new_sf );
365 my @new_fields = map { $_->clone } $source_r->field( $f );
366 $target_r->insert_fields_ordered( @new_fields );
370 $target_xml = $target_r->as_xml_record;
371 $target_xml =~ s/^<\?.+?\?>$//mo;
372 $target_xml =~ s/\n//sgo;
373 $target_xml =~ s/>\s+</></sgo;
377 $_$ LANGUAGE PLPERLU;
379 CREATE OR REPLACE FUNCTION vandelay.strip_field ( xml TEXT, field TEXT ) RETURNS TEXT AS $_$
382 use MARC::File::XML (BinaryEncoding => 'UTF-8');
386 my $r = MARC::Record->new_from_xml( $xml );
388 return $xml unless ($r);
390 my $field_spec = shift;
391 my @field_list = split(',', $field_spec);
394 for my $f (@field_list) {
395 $f =~ s/^\s*//; $f =~ s/\s*$//;
396 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
402 $match =~ s/^\s*//; $match =~ s/\s*$//;
403 $fields{$field} = { sf => [ split('', $sf) ] };
405 my ($msf,$mre) = split('~', $match);
406 if (length($msf) > 0 and length($mre) > 0) {
407 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
408 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
409 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
415 for my $f ( keys %fields) {
416 for my $to_field ($r->field( $f )) {
417 if (exists($fields{$f}{match})) {
418 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
421 if ( @{$fields{$f}{sf}} ) {
422 $to_field->delete_subfield(code => $fields{$f}{sf});
424 $r->delete_field( $to_field );
429 $xml = $r->as_xml_record;
430 $xml =~ s/^<\?.+?\?>$//mo;
432 $xml =~ s/>\s+</></sgo;
436 $_$ LANGUAGE PLPERLU;
438 CREATE OR REPLACE FUNCTION vandelay.replace_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
439 SELECT vandelay.add_field( vandelay.strip_field( $1, $3), $2, $3 );
442 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 $_$
443 SELECT vandelay.replace_field( vandelay.add_field( vandelay.strip_field( $1, $5) , $2, $3 ), $2, $4);
446 CREATE TYPE vandelay.compile_profile AS (add_rule TEXT, replace_rule TEXT, preserve_rule TEXT, strip_rule TEXT);
447 CREATE OR REPLACE FUNCTION vandelay.compile_profile ( incoming_xml TEXT ) RETURNS vandelay.compile_profile AS $_$
449 output vandelay.compile_profile%ROWTYPE;
450 profile vandelay.merge_profile%ROWTYPE;
452 profile_tmpl_owner TEXT;
454 strip_rule TEXT := '';
455 replace_rule TEXT := '';
456 preserve_rule TEXT := '';
460 profile_tmpl := (oils_xpath('//*[@tag="905"]/*[@code="t"]/text()',incoming_xml))[1];
461 profile_tmpl_owner := (oils_xpath('//*[@tag="905"]/*[@code="o"]/text()',incoming_xml))[1];
463 IF profile_tmpl IS NOT NULL AND profile_tmpl <> '' AND profile_tmpl_owner IS NOT NULL AND profile_tmpl_owner <> '' THEN
464 SELECT p.* INTO profile
465 FROM vandelay.merge_profile p
466 JOIN actor.org_unit u ON (u.id = p.owner)
467 WHERE p.name = profile_tmpl
468 AND u.shortname = profile_tmpl_owner;
470 IF profile.id IS NOT NULL THEN
471 add_rule := COALESCE(profile.add_spec,'');
472 strip_rule := COALESCE(profile.strip_spec,'');
473 replace_rule := COALESCE(profile.replace_spec,'');
474 preserve_rule := COALESCE(profile.preserve_spec,'');
478 add_rule := add_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="a"]/text()',incoming_xml),','),'');
479 strip_rule := strip_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="d"]/text()',incoming_xml),','),'');
480 replace_rule := replace_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="r"]/text()',incoming_xml),','),'');
481 preserve_rule := preserve_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="p"]/text()',incoming_xml),','),'');
483 output.add_rule := BTRIM(add_rule,',');
484 output.replace_rule := BTRIM(replace_rule,',');
485 output.strip_rule := BTRIM(strip_rule,',');
486 output.preserve_rule := BTRIM(preserve_rule,',');
490 $_$ LANGUAGE PLPGSQL;
492 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
494 merge_profile vandelay.merge_profile%ROWTYPE;
495 dyn_profile vandelay.compile_profile%ROWTYPE;
505 SELECT b.marc INTO eg_marc
506 FROM biblio.record_entry b
510 IF eg_marc IS NULL OR v_marc IS NULL THEN
511 -- RAISE NOTICE 'no marc for template or bib record';
515 dyn_profile := vandelay.compile_profile( v_marc );
517 IF merge_profile_id IS NOT NULL THEN
518 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
520 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
521 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
522 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
523 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
527 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
528 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
532 IF dyn_profile.replace_rule <> '' THEN
533 source_marc = v_marc;
534 target_marc = eg_marc;
535 replace_rule = dyn_profile.replace_rule;
537 source_marc = eg_marc;
538 target_marc = v_marc;
539 replace_rule = dyn_profile.preserve_rule;
542 UPDATE biblio.record_entry
543 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
547 -- RAISE NOTICE 'update of biblio.record_entry failed';
556 CREATE OR REPLACE FUNCTION vandelay.merge_record_xml ( target_marc TEXT, template_marc TEXT ) RETURNS TEXT AS $$
558 dyn_profile vandelay.compile_profile%ROWTYPE;
566 IF target_marc IS NULL OR template_marc IS NULL THEN
567 -- RAISE NOTICE 'no marc for target or template record';
571 dyn_profile := vandelay.compile_profile( template_marc );
573 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
574 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
578 IF dyn_profile.replace_rule <> '' THEN
579 trgt_marc = target_marc;
580 tmpl_marc = template_marc;
581 replace_rule = dyn_profile.replace_rule;
583 tmp_marc = target_marc;
584 trgt_marc = template_marc;
585 tmpl_marc = tmp_marc;
586 replace_rule = dyn_profile.preserve_rule;
589 RETURN vandelay.merge_record_xml( trgt_marc, tmpl_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule );
594 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT) RETURNS BOOL AS $$
595 SELECT vandelay.template_overlay_bib_record( $1, $2, NULL);
598 CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
600 merge_profile vandelay.merge_profile%ROWTYPE;
601 dyn_profile vandelay.compile_profile%ROWTYPE;
612 SELECT q.marc INTO v_marc
613 FROM vandelay.queued_record q
614 JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
617 IF v_marc IS NULL THEN
618 -- RAISE NOTICE 'no marc for vandelay or bib record';
622 IF vandelay.template_overlay_bib_record( v_marc, eg_id, merge_profile_id) THEN
623 UPDATE vandelay.queued_bib_record
624 SET imported_as = eg_id,
626 WHERE id = import_id;
628 editor_string := (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1];
630 IF editor_string IS NOT NULL AND editor_string <> '' THEN
631 SELECT usr INTO editor_id FROM actor.card WHERE barcode = editor_string;
633 IF editor_id IS NULL THEN
634 SELECT id INTO editor_id FROM actor.usr WHERE usrname = editor_string;
637 IF editor_id IS NOT NULL THEN
638 UPDATE biblio.record_entry SET editor = editor_id WHERE id = eg_id;
645 -- RAISE NOTICE 'update of biblio.record_entry failed';
652 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
656 match_attr vandelay.bib_attr_definition%ROWTYPE;
659 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
662 -- RAISE NOTICE 'already imported, cannot auto-overlay'
666 SELECT COUNT(*) INTO match_count FROM vandelay.bib_match WHERE queued_record = import_id;
668 IF match_count <> 1 THEN
669 -- RAISE NOTICE 'not an exact match';
673 SELECT d.* INTO match_attr
674 FROM vandelay.bib_attr_definition d
675 JOIN vandelay.queued_bib_record_attr a ON (a.field = d.id)
676 JOIN vandelay.bib_match m ON (m.matched_attr = a.id)
677 WHERE m.queued_record = import_id;
679 IF NOT (match_attr.xpath ~ '@tag="901"' AND match_attr.xpath ~ '@code="c"') THEN
680 -- RAISE NOTICE 'not a 901c match: %', match_attr.xpath;
684 SELECT m.eg_record INTO eg_id
685 FROM vandelay.bib_match m
686 WHERE m.queued_record = import_id
689 IF eg_id IS NULL THEN
693 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
697 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
699 queued_record vandelay.queued_bib_record%ROWTYPE;
702 FOR queued_record IN SELECT * FROM vandelay.queued_bib_record WHERE queue = queue_id AND import_time IS NULL LOOP
704 IF vandelay.auto_overlay_bib_record( queued_record.id, merge_profile_id ) THEN
705 RETURN NEXT queued_record.id;
715 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
716 SELECT * FROM vandelay.auto_overlay_bib_queue( $1, NULL );
719 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
744 attr_set vandelay.import_item%ROWTYPE;
750 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
754 attr_set.definition := attr_def.id;
756 -- Build the combined XPath
760 WHEN attr_def.owning_lib IS NULL THEN 'null()'
761 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
762 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
767 WHEN attr_def.circ_lib IS NULL THEN 'null()'
768 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
769 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
774 WHEN attr_def.call_number IS NULL THEN 'null()'
775 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
776 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
781 WHEN attr_def.copy_number IS NULL THEN 'null()'
782 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
783 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
788 WHEN attr_def.status IS NULL THEN 'null()'
789 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
790 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
795 WHEN attr_def.location IS NULL THEN 'null()'
796 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
797 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
802 WHEN attr_def.circulate IS NULL THEN 'null()'
803 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
804 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
809 WHEN attr_def.deposit IS NULL THEN 'null()'
810 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
811 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
816 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
817 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
818 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
823 WHEN attr_def.ref IS NULL THEN 'null()'
824 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
825 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
830 WHEN attr_def.holdable IS NULL THEN 'null()'
831 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
832 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
837 WHEN attr_def.price IS NULL THEN 'null()'
838 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
839 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
844 WHEN attr_def.barcode IS NULL THEN 'null()'
845 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
846 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
851 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
852 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
853 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
858 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
859 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
860 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
865 WHEN attr_def.alert_message IS NULL THEN 'null()'
866 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
867 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
872 WHEN attr_def.opac_visible IS NULL THEN 'null()'
873 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
874 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
879 WHEN attr_def.pub_note IS NULL THEN 'null()'
880 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
881 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
885 WHEN attr_def.priv_note IS NULL THEN 'null()'
886 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
887 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
894 call_number || '|' ||
895 copy_number || '|' ||
900 deposit_amount || '|' ||
905 circ_modifier || '|' ||
906 circ_as_type || '|' ||
907 alert_message || '|' ||
912 -- RAISE NOTICE 'XPath: %', xpath;
916 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
917 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
918 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
919 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, opac_vis TEXT )
922 tmp_attr_set.pr = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
923 tmp_attr_set.dep_amount = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
925 tmp_attr_set.pr := NULLIF( tmp_attr_set.pr, '' );
926 tmp_attr_set.dep_amount := NULLIF( tmp_attr_set.dep_amount, '' );
928 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
929 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
930 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
932 SELECT id INTO attr_set.location
933 FROM asset.copy_location
934 WHERE LOWER(name) = LOWER(tmp_attr_set.cl)
935 AND asset.copy_location.owning_lib = COALESCE(attr_set.owning_lib, attr_set.circ_lib); -- INT
937 attr_set.circulate :=
938 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
939 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
942 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
943 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
946 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
947 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
949 attr_set.opac_visible :=
950 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
951 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
954 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
955 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
957 attr_set.copy_number := tmp_attr_set.cnum::INT; -- INT,
958 attr_set.deposit_amount := tmp_attr_set.dep_amount::NUMERIC(6,2); -- NUMERIC(6,2),
959 attr_set.price := tmp_attr_set.pr::NUMERIC(8,2); -- NUMERIC(8,2),
961 attr_set.call_number := tmp_attr_set.cn; -- TEXT
962 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
963 attr_set.circ_modifier := tmp_attr_set.circ_mod; -- TEXT,
964 attr_set.circ_as_type := tmp_attr_set.circ_as; -- TEXT,
965 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
966 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
967 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
968 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
970 RETURN NEXT attr_set;
982 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$
988 FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP
990 SELECT extract_marc_field('vandelay.queued_bib_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_bib_record WHERE id = NEW.id;
991 IF (value IS NOT NULL AND value <> '') THEN
992 INSERT INTO vandelay.queued_bib_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
1001 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
1004 item_data vandelay.import_item%ROWTYPE;
1007 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
1009 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
1010 INSERT INTO vandelay.import_item (
1034 item_data.definition,
1035 item_data.owning_lib,
1037 item_data.call_number,
1038 item_data.copy_number,
1041 item_data.circulate,
1043 item_data.deposit_amount,
1048 item_data.circ_modifier,
1049 item_data.circ_as_type,
1050 item_data.alert_message,
1052 item_data.priv_note,
1053 item_data.opac_visible
1059 $func$ LANGUAGE PLPGSQL;
1061 CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
1070 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
1072 SELECT * INTO attr_def FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1;
1074 IF attr_def IS NOT NULL AND attr_def.id IS NOT NULL THEN
1075 id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr_def.xpath, attr_def.remove);
1077 IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN
1078 SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted;
1079 SELECT * INTO attr FROM vandelay.queued_bib_record_attr WHERE record = NEW.id and field = attr_def.id LIMIT 1;
1080 IF exact_id IS NOT NULL THEN
1081 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id);
1086 IF exact_id IS NULL THEN
1087 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
1089 -- All numbers? check for an id match
1090 IF (attr.attr_value ~ $r$^\d+$$r$) THEN
1091 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP
1092 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1096 -- Looks like an ISBN? check for an isbn match
1097 IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN
1098 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
1099 PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE;
1101 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
1105 -- subcheck for isbn-as-tcn
1106 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP
1107 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1111 -- check for an OCLC tcn_value match
1112 IF (attr.attr_value ~ $r$^o\d+$$r$) THEN
1113 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP
1114 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1118 -- check for a direct tcn_value match
1119 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP
1120 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1123 -- check for a direct item barcode match
1126 FROM biblio.record_entry b
1127 JOIN asset.call_number cn ON (cn.record = b.id)
1128 JOIN asset.copy cp ON (cp.call_number = cn.id)
1129 WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE
1131 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1139 $func$ LANGUAGE PLPGSQL;
1141 CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$
1143 DELETE FROM vandelay.queued_bib_record_attr WHERE record = OLD.id;
1144 DELETE FROM vandelay.import_item WHERE record = OLD.id;
1146 IF TG_OP = 'UPDATE' THEN
1151 $$ LANGUAGE PLPGSQL;
1153 CREATE TRIGGER cleanup_bib_trigger
1154 BEFORE UPDATE OR DELETE ON vandelay.queued_bib_record
1155 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_bib_marc();
1157 CREATE TRIGGER ingest_bib_trigger
1158 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1159 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_marc();
1161 CREATE TRIGGER ingest_item_trigger
1162 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1163 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_items();
1165 CREATE TRIGGER zz_match_bibs_trigger
1166 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1167 FOR EACH ROW EXECUTE PROCEDURE vandelay.match_bib_record();
1170 /* Authority stuff down here */
1171 ---------------------------------------
1172 CREATE TABLE vandelay.authority_attr_definition (
1173 id SERIAL PRIMARY KEY,
1174 code TEXT UNIQUE NOT NULL,
1176 xpath TEXT NOT NULL,
1177 remove TEXT NOT NULL DEFAULT '',
1178 ident BOOL NOT NULL DEFAULT FALSE
1181 CREATE TABLE vandelay.authority_queue (
1182 queue_type TEXT NOT NULL DEFAULT 'authority' CHECK (queue_type = 'authority'),
1183 CONSTRAINT vand_authority_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
1184 ) INHERITS (vandelay.queue);
1185 ALTER TABLE vandelay.authority_queue ADD PRIMARY KEY (id);
1187 CREATE TABLE vandelay.queued_authority_record (
1188 queue INT NOT NULL REFERENCES vandelay.authority_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1189 imported_as INT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1190 ) INHERITS (vandelay.queued_record);
1191 ALTER TABLE vandelay.queued_authority_record ADD PRIMARY KEY (id);
1192 CREATE INDEX queued_authority_record_queue_idx ON vandelay.queued_authority_record (queue);
1194 CREATE TABLE vandelay.queued_authority_record_attr (
1195 id BIGSERIAL PRIMARY KEY,
1196 record BIGINT NOT NULL REFERENCES vandelay.queued_authority_record (id) DEFERRABLE INITIALLY DEFERRED,
1197 field INT NOT NULL REFERENCES vandelay.authority_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
1198 attr_value TEXT NOT NULL
1200 CREATE INDEX queued_authority_record_attr_record_idx ON vandelay.queued_authority_record_attr (record);
1202 CREATE TABLE vandelay.authority_match (
1203 id BIGSERIAL PRIMARY KEY,
1204 matched_attr INT REFERENCES vandelay.queued_authority_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1205 queued_record BIGINT REFERENCES vandelay.queued_authority_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1206 eg_record BIGINT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1209 CREATE OR REPLACE FUNCTION vandelay.ingest_authority_marc ( ) RETURNS TRIGGER AS $$
1215 FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP
1217 SELECT extract_marc_field('vandelay.queued_authority_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_authority_record WHERE id = NEW.id;
1218 IF (value IS NOT NULL AND value <> '') THEN
1219 INSERT INTO vandelay.queued_authority_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
1226 $$ LANGUAGE PLPGSQL;
1228 CREATE OR REPLACE FUNCTION vandelay.cleanup_authority_marc ( ) RETURNS TRIGGER AS $$
1230 DELETE FROM vandelay.queued_authority_record_attr WHERE record = OLD.id;
1231 IF TG_OP = 'UPDATE' THEN
1236 $$ LANGUAGE PLPGSQL;
1238 CREATE TRIGGER cleanup_authority_trigger
1239 BEFORE UPDATE OR DELETE ON vandelay.queued_authority_record
1240 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_authority_marc();
1242 CREATE TRIGGER ingest_authority_trigger
1243 AFTER INSERT OR UPDATE ON vandelay.queued_authority_record
1244 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_authority_marc();
1246 CREATE OR REPLACE FUNCTION vandelay.overlay_authority_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1248 merge_profile vandelay.merge_profile%ROWTYPE;
1249 dyn_profile vandelay.compile_profile%ROWTYPE;
1258 SELECT b.marc INTO eg_marc
1259 FROM authority.record_entry b
1260 JOIN vandelay.authority_match m ON (m.eg_record = b.id AND m.queued_record = import_id)
1263 SELECT q.marc INTO v_marc
1264 FROM vandelay.queued_record q
1265 JOIN vandelay.authority_match m ON (m.queued_record = q.id AND q.id = import_id)
1268 IF eg_marc IS NULL OR v_marc IS NULL THEN
1269 -- RAISE NOTICE 'no marc for vandelay or authority record';
1273 dyn_profile := vandelay.compile_profile( v_marc );
1275 IF merge_profile_id IS NOT NULL THEN
1276 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
1278 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
1279 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
1280 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
1281 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
1285 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
1286 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
1290 IF dyn_profile.replace_rule <> '' THEN
1291 source_marc = v_marc;
1292 target_marc = eg_marc;
1293 replace_rule = dyn_profile.replace_rule;
1295 source_marc = eg_marc;
1296 target_marc = v_marc;
1297 replace_rule = dyn_profile.preserve_rule;
1300 UPDATE authority.record_entry
1301 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
1305 UPDATE vandelay.queued_authority_record
1306 SET imported_as = eg_id,
1308 WHERE id = import_id;
1312 -- RAISE NOTICE 'update of authority.record_entry failed';
1317 $$ LANGUAGE PLPGSQL;
1319 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1324 SELECT COUNT(*) INTO match_count FROM vandelay.authority_match WHERE queued_record = import_id;
1326 IF match_count <> 1 THEN
1327 -- RAISE NOTICE 'not an exact match';
1331 SELECT m.eg_record INTO eg_id
1332 FROM vandelay.authority_match m
1333 WHERE m.queued_record = import_id
1336 IF eg_id IS NULL THEN
1340 RETURN vandelay.overlay_authority_record( import_id, eg_id, merge_profile_id );
1342 $$ LANGUAGE PLPGSQL;
1344 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
1346 queued_record vandelay.queued_authority_record%ROWTYPE;
1349 FOR queued_record IN SELECT * FROM vandelay.queued_authority_record WHERE queue = queue_id AND import_time IS NULL LOOP
1351 IF vandelay.auto_overlay_authority_record( queued_record.id, merge_profile_id ) THEN
1352 RETURN NEXT queued_record.id;
1360 $$ LANGUAGE PLPGSQL;
1362 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
1363 SELECT * FROM vandelay.auto_overlay_authority_queue( $1, NULL );
1367 -- Vandelay (for importing and exporting records) 012.schema.vandelay.sql
1368 --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)]');
1369 --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)]');
1370 --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]');
1371 --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]');
1372 --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$);
1373 --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$);
1374 --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]');
1375 --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);
1376 --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);
1377 --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);
1378 --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);
1379 --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]');
1380 --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$);
1381 --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]');
1383 --INSERT INTO vandelay.import_item_attr_definition (
1384 -- owner, name, tag, owning_lib, circ_lib, location,
1385 -- call_number, circ_modifier, barcode, price, copy_number,
1386 -- circulate, ref, holdable, opac_visible, status
1389 -- 'Evergreen 852 export format',
1391 -- '[@code = "b"][1]',
1392 -- '[@code = "b"][2]',
1399 -- '[@code = "x" and text() = "circulating"]',
1400 -- '[@code = "x" and text() = "reference"]',
1401 -- '[@code = "x" and text() = "holdable"]',
1402 -- '[@code = "x" and text() = "visible"]',
1406 --INSERT INTO vandelay.import_item_attr_definition (
1419 -- 'Unicorn Import format -- 999',
1430 --INSERT INTO vandelay.authority_attr_definition ( code, description, xpath, ident ) VALUES ('rec_identifier','Identifier','//*[@tag="001"]', TRUE);