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 $_$
451 parsed_target := vandelay.strip_field( target_xml, ''); -- this dance normalizes the format of the xml for the IF below
453 FOR curr_field IN SELECT UNNEST( STRING_TO_ARRAY(field, ',') ) LOOP -- naive split, but it's the same we use in the perl
455 xml_output := vandelay.strip_field( parsed_target, curr_field);
457 IF xml_output <> parsed_target AND curr_field ~ E'~' THEN
458 -- we removed something, and there was a regexp restriction in the curr_field definition, so proceed
459 xml_output := vandelay.add_field( xml_output, source_xml, curr_field, 1 );
460 ELSIF curr_field !~ E'~' THEN
461 -- No regexp restriction, add the curr_field
462 xml_output := vandelay.add_field( xml_output, source_xml, curr_field, 0 );
465 parsed_target := xml_output; -- in prep for any following loop iterations
471 $_$ LANGUAGE PLPGSQL;
473 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 $_$
474 SELECT vandelay.replace_field( vandelay.add_field( vandelay.strip_field( $1, $5) , $2, $3 ), $2, $4);
477 CREATE TYPE vandelay.compile_profile AS (add_rule TEXT, replace_rule TEXT, preserve_rule TEXT, strip_rule TEXT);
478 CREATE OR REPLACE FUNCTION vandelay.compile_profile ( incoming_xml TEXT ) RETURNS vandelay.compile_profile AS $_$
480 output vandelay.compile_profile%ROWTYPE;
481 profile vandelay.merge_profile%ROWTYPE;
483 profile_tmpl_owner TEXT;
485 strip_rule TEXT := '';
486 replace_rule TEXT := '';
487 preserve_rule TEXT := '';
491 profile_tmpl := (oils_xpath('//*[@tag="905"]/*[@code="t"]/text()',incoming_xml))[1];
492 profile_tmpl_owner := (oils_xpath('//*[@tag="905"]/*[@code="o"]/text()',incoming_xml))[1];
494 IF profile_tmpl IS NOT NULL AND profile_tmpl <> '' AND profile_tmpl_owner IS NOT NULL AND profile_tmpl_owner <> '' THEN
495 SELECT p.* INTO profile
496 FROM vandelay.merge_profile p
497 JOIN actor.org_unit u ON (u.id = p.owner)
498 WHERE p.name = profile_tmpl
499 AND u.shortname = profile_tmpl_owner;
501 IF profile.id IS NOT NULL THEN
502 add_rule := COALESCE(profile.add_spec,'');
503 strip_rule := COALESCE(profile.strip_spec,'');
504 replace_rule := COALESCE(profile.replace_spec,'');
505 preserve_rule := COALESCE(profile.preserve_spec,'');
509 add_rule := add_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="a"]/text()',incoming_xml),','),'');
510 strip_rule := strip_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="d"]/text()',incoming_xml),','),'');
511 replace_rule := replace_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="r"]/text()',incoming_xml),','),'');
512 preserve_rule := preserve_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="p"]/text()',incoming_xml),','),'');
514 output.add_rule := BTRIM(add_rule,',');
515 output.replace_rule := BTRIM(replace_rule,',');
516 output.strip_rule := BTRIM(strip_rule,',');
517 output.preserve_rule := BTRIM(preserve_rule,',');
521 $_$ LANGUAGE PLPGSQL;
523 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
525 merge_profile vandelay.merge_profile%ROWTYPE;
526 dyn_profile vandelay.compile_profile%ROWTYPE;
536 SELECT b.marc INTO eg_marc
537 FROM biblio.record_entry b
541 IF eg_marc IS NULL OR v_marc IS NULL THEN
542 -- RAISE NOTICE 'no marc for template or bib record';
546 dyn_profile := vandelay.compile_profile( v_marc );
548 IF merge_profile_id IS NOT NULL THEN
549 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
551 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
552 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
553 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
554 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
558 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
559 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
563 IF dyn_profile.replace_rule <> '' THEN
564 source_marc = v_marc;
565 target_marc = eg_marc;
566 replace_rule = dyn_profile.replace_rule;
568 source_marc = eg_marc;
569 target_marc = v_marc;
570 replace_rule = dyn_profile.preserve_rule;
573 UPDATE biblio.record_entry
574 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
578 -- RAISE NOTICE 'update of biblio.record_entry failed';
587 CREATE OR REPLACE FUNCTION vandelay.merge_record_xml ( target_marc TEXT, template_marc TEXT ) RETURNS TEXT AS $$
589 dyn_profile vandelay.compile_profile%ROWTYPE;
597 IF target_marc IS NULL OR template_marc IS NULL THEN
598 -- RAISE NOTICE 'no marc for target or template record';
602 dyn_profile := vandelay.compile_profile( template_marc );
604 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
605 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
609 IF dyn_profile.replace_rule <> '' THEN
610 trgt_marc = target_marc;
611 tmpl_marc = template_marc;
612 replace_rule = dyn_profile.replace_rule;
614 tmp_marc = target_marc;
615 trgt_marc = template_marc;
616 tmpl_marc = tmp_marc;
617 replace_rule = dyn_profile.preserve_rule;
620 RETURN vandelay.merge_record_xml( trgt_marc, tmpl_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule );
625 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT) RETURNS BOOL AS $$
626 SELECT vandelay.template_overlay_bib_record( $1, $2, NULL);
629 CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
631 merge_profile vandelay.merge_profile%ROWTYPE;
632 dyn_profile vandelay.compile_profile%ROWTYPE;
643 SELECT q.marc INTO v_marc
644 FROM vandelay.queued_record q
645 JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
648 IF v_marc IS NULL THEN
649 -- RAISE NOTICE 'no marc for vandelay or bib record';
653 IF vandelay.template_overlay_bib_record( v_marc, eg_id, merge_profile_id) THEN
654 UPDATE vandelay.queued_bib_record
655 SET imported_as = eg_id,
657 WHERE id = import_id;
659 editor_string := (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1];
661 IF editor_string IS NOT NULL AND editor_string <> '' THEN
662 SELECT usr INTO editor_id FROM actor.card WHERE barcode = editor_string;
664 IF editor_id IS NULL THEN
665 SELECT id INTO editor_id FROM actor.usr WHERE usrname = editor_string;
668 IF editor_id IS NOT NULL THEN
669 UPDATE biblio.record_entry SET editor = editor_id WHERE id = eg_id;
676 -- RAISE NOTICE 'update of biblio.record_entry failed';
683 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
687 match_attr vandelay.bib_attr_definition%ROWTYPE;
690 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
693 -- RAISE NOTICE 'already imported, cannot auto-overlay'
697 SELECT COUNT(*) INTO match_count FROM vandelay.bib_match WHERE queued_record = import_id;
699 IF match_count <> 1 THEN
700 -- RAISE NOTICE 'not an exact match';
704 SELECT d.* INTO match_attr
705 FROM vandelay.bib_attr_definition d
706 JOIN vandelay.queued_bib_record_attr a ON (a.field = d.id)
707 JOIN vandelay.bib_match m ON (m.matched_attr = a.id)
708 WHERE m.queued_record = import_id;
710 IF NOT (match_attr.xpath ~ '@tag="901"' AND match_attr.xpath ~ '@code="c"') THEN
711 -- RAISE NOTICE 'not a 901c match: %', match_attr.xpath;
715 SELECT m.eg_record INTO eg_id
716 FROM vandelay.bib_match m
717 WHERE m.queued_record = import_id
720 IF eg_id IS NULL THEN
724 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
728 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
730 queued_record vandelay.queued_bib_record%ROWTYPE;
733 FOR queued_record IN SELECT * FROM vandelay.queued_bib_record WHERE queue = queue_id AND import_time IS NULL LOOP
735 IF vandelay.auto_overlay_bib_record( queued_record.id, merge_profile_id ) THEN
736 RETURN NEXT queued_record.id;
746 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
747 SELECT * FROM vandelay.auto_overlay_bib_queue( $1, NULL );
750 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
775 attr_set vandelay.import_item%ROWTYPE;
781 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
785 attr_set.definition := attr_def.id;
787 -- Build the combined XPath
791 WHEN attr_def.owning_lib IS NULL THEN 'null()'
792 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
793 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
798 WHEN attr_def.circ_lib IS NULL THEN 'null()'
799 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
800 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
805 WHEN attr_def.call_number IS NULL THEN 'null()'
806 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
807 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
812 WHEN attr_def.copy_number IS NULL THEN 'null()'
813 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
814 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
819 WHEN attr_def.status IS NULL THEN 'null()'
820 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
821 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
826 WHEN attr_def.location IS NULL THEN 'null()'
827 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
828 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
833 WHEN attr_def.circulate IS NULL THEN 'null()'
834 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
835 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
840 WHEN attr_def.deposit IS NULL THEN 'null()'
841 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
842 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
847 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
848 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
849 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
854 WHEN attr_def.ref IS NULL THEN 'null()'
855 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
856 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
861 WHEN attr_def.holdable IS NULL THEN 'null()'
862 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
863 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
868 WHEN attr_def.price IS NULL THEN 'null()'
869 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
870 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
875 WHEN attr_def.barcode IS NULL THEN 'null()'
876 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
877 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
882 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
883 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
884 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
889 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
890 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
891 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
896 WHEN attr_def.alert_message IS NULL THEN 'null()'
897 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
898 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
903 WHEN attr_def.opac_visible IS NULL THEN 'null()'
904 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
905 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
910 WHEN attr_def.pub_note IS NULL THEN 'null()'
911 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
912 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
916 WHEN attr_def.priv_note IS NULL THEN 'null()'
917 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
918 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
925 call_number || '|' ||
926 copy_number || '|' ||
931 deposit_amount || '|' ||
936 circ_modifier || '|' ||
937 circ_as_type || '|' ||
938 alert_message || '|' ||
943 -- RAISE NOTICE 'XPath: %', xpath;
947 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
948 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
949 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
950 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, opac_vis TEXT )
953 tmp_attr_set.pr = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
954 tmp_attr_set.dep_amount = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
956 tmp_attr_set.pr := NULLIF( tmp_attr_set.pr, '' );
957 tmp_attr_set.dep_amount := NULLIF( tmp_attr_set.dep_amount, '' );
959 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
960 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
961 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
963 SELECT id INTO attr_set.location
964 FROM asset.copy_location
965 WHERE LOWER(name) = LOWER(tmp_attr_set.cl)
966 AND asset.copy_location.owning_lib = COALESCE(attr_set.owning_lib, attr_set.circ_lib); -- INT
968 attr_set.circulate :=
969 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
970 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
973 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
974 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
977 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
978 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
980 attr_set.opac_visible :=
981 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
982 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
985 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
986 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
988 attr_set.copy_number := tmp_attr_set.cnum::INT; -- INT,
989 attr_set.deposit_amount := tmp_attr_set.dep_amount::NUMERIC(6,2); -- NUMERIC(6,2),
990 attr_set.price := tmp_attr_set.pr::NUMERIC(8,2); -- NUMERIC(8,2),
992 attr_set.call_number := tmp_attr_set.cn; -- TEXT
993 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
994 attr_set.circ_modifier := tmp_attr_set.circ_mod; -- TEXT,
995 attr_set.circ_as_type := tmp_attr_set.circ_as; -- TEXT,
996 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
997 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
998 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
999 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
1001 RETURN NEXT attr_set;
1010 $$ LANGUAGE PLPGSQL;
1013 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$
1019 FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP
1021 SELECT extract_marc_field('vandelay.queued_bib_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_bib_record WHERE id = NEW.id;
1022 IF (value IS NOT NULL AND value <> '') THEN
1023 INSERT INTO vandelay.queued_bib_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
1030 $$ LANGUAGE PLPGSQL;
1032 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
1035 item_data vandelay.import_item%ROWTYPE;
1038 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
1040 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
1041 INSERT INTO vandelay.import_item (
1065 item_data.definition,
1066 item_data.owning_lib,
1068 item_data.call_number,
1069 item_data.copy_number,
1072 item_data.circulate,
1074 item_data.deposit_amount,
1079 item_data.circ_modifier,
1080 item_data.circ_as_type,
1081 item_data.alert_message,
1083 item_data.priv_note,
1084 item_data.opac_visible
1090 $func$ LANGUAGE PLPGSQL;
1092 CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
1101 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
1103 SELECT * INTO attr_def FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1;
1105 IF attr_def IS NOT NULL AND attr_def.id IS NOT NULL THEN
1106 id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr_def.xpath, attr_def.remove);
1108 IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN
1109 SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted;
1110 SELECT * INTO attr FROM vandelay.queued_bib_record_attr WHERE record = NEW.id and field = attr_def.id LIMIT 1;
1111 IF exact_id IS NOT NULL THEN
1112 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id);
1117 IF exact_id IS NULL THEN
1118 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
1120 -- All numbers? check for an id match
1121 IF (attr.attr_value ~ $r$^\d+$$r$) THEN
1122 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP
1123 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1127 -- Looks like an ISBN? check for an isbn match
1128 IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN
1129 FOR eg_rec IN EXECUTE $$SELECT * FROM metabib.full_rec fr WHERE fr.value LIKE evergreen.lowercase('$$ || attr.attr_value || $$%') AND fr.tag = '020' AND fr.subfield = 'a'$$ LOOP
1130 PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE;
1132 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
1136 -- subcheck for isbn-as-tcn
1137 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP
1138 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1142 -- check for an OCLC tcn_value match
1143 IF (attr.attr_value ~ $r$^o\d+$$r$) THEN
1144 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP
1145 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1149 -- check for a direct tcn_value match
1150 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP
1151 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1154 -- check for a direct item barcode match
1157 FROM biblio.record_entry b
1158 JOIN asset.call_number cn ON (cn.record = b.id)
1159 JOIN asset.copy cp ON (cp.call_number = cn.id)
1160 WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE
1162 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1170 $func$ LANGUAGE PLPGSQL;
1172 CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$
1174 DELETE FROM vandelay.queued_bib_record_attr WHERE record = OLD.id;
1175 DELETE FROM vandelay.import_item WHERE record = OLD.id;
1177 IF TG_OP = 'UPDATE' THEN
1182 $$ LANGUAGE PLPGSQL;
1184 CREATE TRIGGER cleanup_bib_trigger
1185 BEFORE UPDATE OR DELETE ON vandelay.queued_bib_record
1186 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_bib_marc();
1188 CREATE TRIGGER ingest_bib_trigger
1189 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1190 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_marc();
1192 CREATE TRIGGER ingest_item_trigger
1193 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1194 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_items();
1196 CREATE TRIGGER zz_match_bibs_trigger
1197 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1198 FOR EACH ROW EXECUTE PROCEDURE vandelay.match_bib_record();
1201 /* Authority stuff down here */
1202 ---------------------------------------
1203 CREATE TABLE vandelay.authority_attr_definition (
1204 id SERIAL PRIMARY KEY,
1205 code TEXT UNIQUE NOT NULL,
1207 xpath TEXT NOT NULL,
1208 remove TEXT NOT NULL DEFAULT '',
1209 ident BOOL NOT NULL DEFAULT FALSE
1212 CREATE TABLE vandelay.authority_queue (
1213 queue_type TEXT NOT NULL DEFAULT 'authority' CHECK (queue_type = 'authority'),
1214 CONSTRAINT vand_authority_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
1215 ) INHERITS (vandelay.queue);
1216 ALTER TABLE vandelay.authority_queue ADD PRIMARY KEY (id);
1218 CREATE TABLE vandelay.queued_authority_record (
1219 queue INT NOT NULL REFERENCES vandelay.authority_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1220 imported_as INT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1221 ) INHERITS (vandelay.queued_record);
1222 ALTER TABLE vandelay.queued_authority_record ADD PRIMARY KEY (id);
1223 CREATE INDEX queued_authority_record_queue_idx ON vandelay.queued_authority_record (queue);
1225 CREATE TABLE vandelay.queued_authority_record_attr (
1226 id BIGSERIAL PRIMARY KEY,
1227 record BIGINT NOT NULL REFERENCES vandelay.queued_authority_record (id) DEFERRABLE INITIALLY DEFERRED,
1228 field INT NOT NULL REFERENCES vandelay.authority_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
1229 attr_value TEXT NOT NULL
1231 CREATE INDEX queued_authority_record_attr_record_idx ON vandelay.queued_authority_record_attr (record);
1233 CREATE TABLE vandelay.authority_match (
1234 id BIGSERIAL PRIMARY KEY,
1235 matched_attr INT REFERENCES vandelay.queued_authority_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1236 queued_record BIGINT REFERENCES vandelay.queued_authority_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1237 eg_record BIGINT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1240 CREATE OR REPLACE FUNCTION vandelay.ingest_authority_marc ( ) RETURNS TRIGGER AS $$
1246 FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP
1248 SELECT extract_marc_field('vandelay.queued_authority_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_authority_record WHERE id = NEW.id;
1249 IF (value IS NOT NULL AND value <> '') THEN
1250 INSERT INTO vandelay.queued_authority_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
1257 $$ LANGUAGE PLPGSQL;
1259 CREATE OR REPLACE FUNCTION vandelay.cleanup_authority_marc ( ) RETURNS TRIGGER AS $$
1261 DELETE FROM vandelay.queued_authority_record_attr WHERE record = OLD.id;
1262 IF TG_OP = 'UPDATE' THEN
1267 $$ LANGUAGE PLPGSQL;
1269 CREATE TRIGGER cleanup_authority_trigger
1270 BEFORE UPDATE OR DELETE ON vandelay.queued_authority_record
1271 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_authority_marc();
1273 CREATE TRIGGER ingest_authority_trigger
1274 AFTER INSERT OR UPDATE ON vandelay.queued_authority_record
1275 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_authority_marc();
1277 CREATE OR REPLACE FUNCTION vandelay.overlay_authority_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1279 merge_profile vandelay.merge_profile%ROWTYPE;
1280 dyn_profile vandelay.compile_profile%ROWTYPE;
1289 SELECT b.marc INTO eg_marc
1290 FROM authority.record_entry b
1291 JOIN vandelay.authority_match m ON (m.eg_record = b.id AND m.queued_record = import_id)
1294 SELECT q.marc INTO v_marc
1295 FROM vandelay.queued_record q
1296 JOIN vandelay.authority_match m ON (m.queued_record = q.id AND q.id = import_id)
1299 IF eg_marc IS NULL OR v_marc IS NULL THEN
1300 -- RAISE NOTICE 'no marc for vandelay or authority record';
1304 dyn_profile := vandelay.compile_profile( v_marc );
1306 IF merge_profile_id IS NOT NULL THEN
1307 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
1309 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
1310 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
1311 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
1312 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
1316 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
1317 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
1321 IF dyn_profile.replace_rule <> '' THEN
1322 source_marc = v_marc;
1323 target_marc = eg_marc;
1324 replace_rule = dyn_profile.replace_rule;
1326 source_marc = eg_marc;
1327 target_marc = v_marc;
1328 replace_rule = dyn_profile.preserve_rule;
1331 UPDATE authority.record_entry
1332 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
1336 UPDATE vandelay.queued_authority_record
1337 SET imported_as = eg_id,
1339 WHERE id = import_id;
1343 -- RAISE NOTICE 'update of authority.record_entry failed';
1348 $$ LANGUAGE PLPGSQL;
1350 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1355 SELECT COUNT(*) INTO match_count FROM vandelay.authority_match WHERE queued_record = import_id;
1357 IF match_count <> 1 THEN
1358 -- RAISE NOTICE 'not an exact match';
1362 SELECT m.eg_record INTO eg_id
1363 FROM vandelay.authority_match m
1364 WHERE m.queued_record = import_id
1367 IF eg_id IS NULL THEN
1371 RETURN vandelay.overlay_authority_record( import_id, eg_id, merge_profile_id );
1373 $$ LANGUAGE PLPGSQL;
1375 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
1377 queued_record vandelay.queued_authority_record%ROWTYPE;
1380 FOR queued_record IN SELECT * FROM vandelay.queued_authority_record WHERE queue = queue_id AND import_time IS NULL LOOP
1382 IF vandelay.auto_overlay_authority_record( queued_record.id, merge_profile_id ) THEN
1383 RETURN NEXT queued_record.id;
1391 $$ LANGUAGE PLPGSQL;
1393 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
1394 SELECT * FROM vandelay.auto_overlay_authority_queue( $1, NULL );
1398 -- Vandelay (for importing and exporting records) 012.schema.vandelay.sql
1399 --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)]');
1400 --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)]');
1401 --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]');
1402 --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]');
1403 --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$);
1404 --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$);
1405 --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]');
1406 --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);
1407 --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);
1408 --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);
1409 --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);
1410 --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]');
1411 --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$);
1412 --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]');
1414 --INSERT INTO vandelay.import_item_attr_definition (
1415 -- owner, name, tag, owning_lib, circ_lib, location,
1416 -- call_number, circ_modifier, barcode, price, copy_number,
1417 -- circulate, ref, holdable, opac_visible, status
1420 -- 'Evergreen 852 export format',
1422 -- '[@code = "b"][1]',
1423 -- '[@code = "b"][2]',
1430 -- '[@code = "x" and text() = "circulating"]',
1431 -- '[@code = "x" and text() = "reference"]',
1432 -- '[@code = "x" and text() = "holdable"]',
1433 -- '[@code = "x" and text() = "visible"]',
1437 --INSERT INTO vandelay.import_item_attr_definition (
1450 -- 'Unicorn Import format -- 999',
1461 --INSERT INTO vandelay.authority_attr_definition ( code, description, xpath, ident ) VALUES ('rec_identifier','Identifier','//*[@tag="001"]', TRUE);