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 for my $to_field ($target_r->field( $f )) {
351 if (exists($fields{$f}{match})) {
352 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
354 my @new_sf = map { ($_ => $from_field->subfield($_)) } @{$fields{$f}{sf}};
355 $to_field->add_subfields( @new_sf );
359 my @new_fields = map { $_->clone } $source_r->field( $f );
360 $target_r->insert_fields_ordered( @new_fields );
364 $target_xml = $target_r->as_xml_record;
365 $target_xml =~ s/^<\?.+?\?>$//mo;
366 $target_xml =~ s/\n//sgo;
367 $target_xml =~ s/>\s+</></sgo;
371 $_$ LANGUAGE PLPERLU;
373 CREATE OR REPLACE FUNCTION vandelay.strip_field ( xml TEXT, field TEXT ) RETURNS TEXT AS $_$
376 use MARC::File::XML (BinaryEncoding => 'UTF-8');
380 my $r = MARC::Record->new_from_xml( $xml );
382 return $xml unless ($r);
384 my $field_spec = shift;
385 my @field_list = split(',', $field_spec);
388 for my $f (@field_list) {
389 $f =~ s/^\s*//; $f =~ s/\s*$//;
390 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
396 $match =~ s/^\s*//; $match =~ s/\s*$//;
397 $fields{$field} = { sf => [ split('', $sf) ] };
399 my ($msf,$mre) = split('~', $match);
400 if (length($msf) > 0 and length($mre) > 0) {
401 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
402 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
403 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
409 for my $f ( keys %fields) {
410 for my $to_field ($r->field( $f )) {
411 if (exists($fields{$f}{match})) {
412 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
415 if ( @{$fields{$f}{sf}} ) {
416 $to_field->delete_subfield(code => $fields{$f}{sf});
418 $r->delete_field( $to_field );
423 $xml = $r->as_xml_record;
424 $xml =~ s/^<\?.+?\?>$//mo;
426 $xml =~ s/>\s+</></sgo;
430 $_$ LANGUAGE PLPERLU;
432 CREATE OR REPLACE FUNCTION vandelay.replace_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
433 SELECT vandelay.add_field( vandelay.strip_field( $1, $3), $2, $3 );
436 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 $_$
437 SELECT vandelay.replace_field( vandelay.add_field( vandelay.strip_field( $1, $5) , $2, $3 ), $2, $4);
440 CREATE TYPE vandelay.compile_profile AS (add_rule TEXT, replace_rule TEXT, preserve_rule TEXT, strip_rule TEXT);
441 CREATE OR REPLACE FUNCTION vandelay.compile_profile ( incoming_xml TEXT ) RETURNS vandelay.compile_profile AS $_$
443 output vandelay.compile_profile%ROWTYPE;
444 profile vandelay.merge_profile%ROWTYPE;
446 profile_tmpl_owner TEXT;
448 strip_rule TEXT := '';
449 replace_rule TEXT := '';
450 preserve_rule TEXT := '';
454 profile_tmpl := (oils_xpath('//*[@tag="905"]/*[@code="t"]/text()',incoming_xml))[1];
455 profile_tmpl_owner := (oils_xpath('//*[@tag="905"]/*[@code="o"]/text()',incoming_xml))[1];
457 IF profile_tmpl IS NOT NULL AND profile_tmpl <> '' AND profile_tmpl_owner IS NOT NULL AND profile_tmpl_owner <> '' THEN
458 SELECT p.* INTO profile
459 FROM vandelay.merge_profile p
460 JOIN actor.org_unit u ON (u.id = p.owner)
461 WHERE p.name = profile_tmpl
462 AND u.shortname = profile_tmpl_owner;
464 IF profile.id IS NOT NULL THEN
465 add_rule := COALESCE(profile.add_spec,'');
466 strip_rule := COALESCE(profile.strip_spec,'');
467 replace_rule := COALESCE(profile.replace_spec,'');
468 preserve_rule := COALESCE(profile.preserve_spec,'');
472 add_rule := add_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="a"]/text()',incoming_xml),''),'');
473 strip_rule := strip_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="d"]/text()',incoming_xml),''),'');
474 replace_rule := replace_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="r"]/text()',incoming_xml),''),'');
475 preserve_rule := preserve_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="p"]/text()',incoming_xml),''),'');
477 output.add_rule := BTRIM(add_rule,',');
478 output.replace_rule := BTRIM(replace_rule,',');
479 output.strip_rule := BTRIM(strip_rule,',');
480 output.preserve_rule := BTRIM(preserve_rule,',');
484 $_$ LANGUAGE PLPGSQL;
486 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
488 merge_profile vandelay.merge_profile%ROWTYPE;
489 dyn_profile vandelay.compile_profile%ROWTYPE;
499 SELECT b.marc INTO eg_marc
500 FROM biblio.record_entry b
504 IF eg_marc IS NULL OR v_marc IS NULL THEN
505 -- RAISE NOTICE 'no marc for template or bib record';
509 dyn_profile := vandelay.compile_profile( v_marc );
511 IF merge_profile_id IS NOT NULL THEN
512 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
514 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
515 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
516 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
517 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
521 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
522 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
526 IF dyn_profile.replace_rule <> '' THEN
527 source_marc = v_marc;
528 target_marc = eg_marc;
529 replace_rule = dyn_profile.replace_rule;
531 source_marc = eg_marc;
532 target_marc = v_marc;
533 replace_rule = dyn_profile.preserve_rule;
536 UPDATE biblio.record_entry
537 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
541 -- RAISE NOTICE 'update of biblio.record_entry failed';
550 CREATE OR REPLACE FUNCTION vandelay.merge_record_xml ( target_marc TEXT, template_marc TEXT ) RETURNS TEXT AS $$
552 dyn_profile vandelay.compile_profile%ROWTYPE;
560 IF target_marc IS NULL OR template_marc IS NULL THEN
561 -- RAISE NOTICE 'no marc for target or template record';
565 dyn_profile := vandelay.compile_profile( template_marc );
567 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
568 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
572 IF dyn_profile.replace_rule <> '' THEN
573 trgt_marc = target_marc;
574 tmpl_marc = template_marc;
575 replace_rule = dyn_profile.replace_rule;
577 tmp_marc = target_marc;
578 trgt_marc = template_marc;
579 tmpl_marc = tmp_marc;
580 replace_rule = dyn_profile.preserve_rule;
583 RETURN vandelay.merge_record_xml( trgt_marc, tmpl_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule );
588 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT) RETURNS BOOL AS $$
589 SELECT vandelay.template_overlay_bib_record( $1, $2, NULL);
592 CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
594 merge_profile vandelay.merge_profile%ROWTYPE;
595 dyn_profile vandelay.compile_profile%ROWTYPE;
606 SELECT q.marc INTO v_marc
607 FROM vandelay.queued_record q
608 JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
611 IF v_marc IS NULL THEN
612 -- RAISE NOTICE 'no marc for vandelay or bib record';
616 IF vandelay.template_overlay_bib_record( v_marc, eg_id, merge_profile_id) THEN
617 UPDATE vandelay.queued_bib_record
618 SET imported_as = eg_id,
620 WHERE id = import_id;
622 editor_string := (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1];
624 IF editor_string IS NOT NULL AND editor_string <> '' THEN
625 SELECT usr INTO editor_id FROM actor.card WHERE barcode = editor_string;
627 IF editor_id IS NULL THEN
628 SELECT id INTO editor_id FROM actor.usr WHERE usrname = editor_string;
631 IF editor_id IS NOT NULL THEN
632 UPDATE biblio.record_entry SET editor = editor_id WHERE id = eg_id;
639 -- RAISE NOTICE 'update of biblio.record_entry failed';
646 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
650 match_attr vandelay.bib_attr_definition%ROWTYPE;
653 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
656 -- RAISE NOTICE 'already imported, cannot auto-overlay'
660 SELECT COUNT(*) INTO match_count FROM vandelay.bib_match WHERE queued_record = import_id;
662 IF match_count <> 1 THEN
663 -- RAISE NOTICE 'not an exact match';
667 SELECT d.* INTO match_attr
668 FROM vandelay.bib_attr_definition d
669 JOIN vandelay.queued_bib_record_attr a ON (a.field = d.id)
670 JOIN vandelay.bib_match m ON (m.matched_attr = a.id)
671 WHERE m.queued_record = import_id;
673 IF NOT (match_attr.xpath ~ '@tag="901"' AND match_attr.xpath ~ '@code="c"') THEN
674 -- RAISE NOTICE 'not a 901c match: %', match_attr.xpath;
678 SELECT m.eg_record INTO eg_id
679 FROM vandelay.bib_match m
680 WHERE m.queued_record = import_id
683 IF eg_id IS NULL THEN
687 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
691 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
693 queued_record vandelay.queued_bib_record%ROWTYPE;
696 FOR queued_record IN SELECT * FROM vandelay.queued_bib_record WHERE queue = queue_id AND import_time IS NULL LOOP
698 IF vandelay.auto_overlay_bib_record( queued_record.id, merge_profile_id ) THEN
699 RETURN NEXT queued_record.id;
709 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
710 SELECT * FROM vandelay.auto_overlay_bib_queue( $1, NULL );
713 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
738 attr_set vandelay.import_item%ROWTYPE;
744 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
748 attr_set.definition := attr_def.id;
750 -- Build the combined XPath
754 WHEN attr_def.owning_lib IS NULL THEN 'null()'
755 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
756 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
761 WHEN attr_def.circ_lib IS NULL THEN 'null()'
762 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
763 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
768 WHEN attr_def.call_number IS NULL THEN 'null()'
769 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
770 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
775 WHEN attr_def.copy_number IS NULL THEN 'null()'
776 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
777 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
782 WHEN attr_def.status IS NULL THEN 'null()'
783 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
784 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
789 WHEN attr_def.location IS NULL THEN 'null()'
790 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
791 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
796 WHEN attr_def.circulate IS NULL THEN 'null()'
797 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
798 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
803 WHEN attr_def.deposit IS NULL THEN 'null()'
804 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
805 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
810 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
811 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
812 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
817 WHEN attr_def.ref IS NULL THEN 'null()'
818 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
819 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
824 WHEN attr_def.holdable IS NULL THEN 'null()'
825 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
826 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
831 WHEN attr_def.price IS NULL THEN 'null()'
832 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
833 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
838 WHEN attr_def.barcode IS NULL THEN 'null()'
839 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
840 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
845 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
846 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
847 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
852 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
853 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
854 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
859 WHEN attr_def.alert_message IS NULL THEN 'null()'
860 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
861 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
866 WHEN attr_def.opac_visible IS NULL THEN 'null()'
867 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
868 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
873 WHEN attr_def.pub_note IS NULL THEN 'null()'
874 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
875 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
879 WHEN attr_def.priv_note IS NULL THEN 'null()'
880 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
881 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
888 call_number || '|' ||
889 copy_number || '|' ||
894 deposit_amount || '|' ||
899 circ_modifier || '|' ||
900 circ_as_type || '|' ||
901 alert_message || '|' ||
906 -- RAISE NOTICE 'XPath: %', xpath;
910 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
911 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
912 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
913 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, opac_vis TEXT )
916 tmp_attr_set.pr = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
917 tmp_attr_set.dep_amount = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
919 tmp_attr_set.pr := NULLIF( tmp_attr_set.pr, '' );
920 tmp_attr_set.dep_amount := NULLIF( tmp_attr_set.dep_amount, '' );
922 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
923 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
924 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
926 SELECT id INTO attr_set.location
927 FROM asset.copy_location
928 WHERE LOWER(name) = LOWER(tmp_attr_set.cl)
929 AND asset.copy_location.owning_lib = COALESCE(attr_set.owning_lib, attr_set.circ_lib); -- INT
931 attr_set.circulate :=
932 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
933 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
936 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
937 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
940 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
941 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
943 attr_set.opac_visible :=
944 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
945 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
948 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
949 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
951 attr_set.copy_number := tmp_attr_set.cnum::INT; -- INT,
952 attr_set.deposit_amount := tmp_attr_set.dep_amount::NUMERIC(6,2); -- NUMERIC(6,2),
953 attr_set.price := tmp_attr_set.pr::NUMERIC(8,2); -- NUMERIC(8,2),
955 attr_set.call_number := tmp_attr_set.cn; -- TEXT
956 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
957 attr_set.circ_modifier := tmp_attr_set.circ_mod; -- TEXT,
958 attr_set.circ_as_type := tmp_attr_set.circ_as; -- TEXT,
959 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
960 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
961 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
962 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
964 RETURN NEXT attr_set;
976 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$
982 FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP
984 SELECT extract_marc_field('vandelay.queued_bib_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_bib_record WHERE id = NEW.id;
985 IF (value IS NOT NULL AND value <> '') THEN
986 INSERT INTO vandelay.queued_bib_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
995 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
998 item_data vandelay.import_item%ROWTYPE;
1001 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
1003 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
1004 INSERT INTO vandelay.import_item (
1028 item_data.definition,
1029 item_data.owning_lib,
1031 item_data.call_number,
1032 item_data.copy_number,
1035 item_data.circulate,
1037 item_data.deposit_amount,
1042 item_data.circ_modifier,
1043 item_data.circ_as_type,
1044 item_data.alert_message,
1046 item_data.priv_note,
1047 item_data.opac_visible
1053 $func$ LANGUAGE PLPGSQL;
1055 CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
1064 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
1066 SELECT * INTO attr_def FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1;
1068 IF attr_def IS NOT NULL AND attr_def.id IS NOT NULL THEN
1069 id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr_def.xpath, attr_def.remove);
1071 IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN
1072 SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted;
1073 SELECT * INTO attr FROM vandelay.queued_bib_record_attr WHERE record = NEW.id and field = attr_def.id LIMIT 1;
1074 IF exact_id IS NOT NULL THEN
1075 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id);
1080 IF exact_id IS NULL THEN
1081 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
1083 -- All numbers? check for an id match
1084 IF (attr.attr_value ~ $r$^\d+$$r$) THEN
1085 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP
1086 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1090 -- Looks like an ISBN? check for an isbn match
1091 IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN
1092 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
1093 PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE;
1095 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
1099 -- subcheck for isbn-as-tcn
1100 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP
1101 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1105 -- check for an OCLC tcn_value match
1106 IF (attr.attr_value ~ $r$^o\d+$$r$) THEN
1107 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP
1108 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1112 -- check for a direct tcn_value match
1113 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value 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);
1117 -- check for a direct item barcode match
1120 FROM biblio.record_entry b
1121 JOIN asset.call_number cn ON (cn.record = b.id)
1122 JOIN asset.copy cp ON (cp.call_number = cn.id)
1123 WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE
1125 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1133 $func$ LANGUAGE PLPGSQL;
1135 CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$
1137 DELETE FROM vandelay.queued_bib_record_attr WHERE record = OLD.id;
1138 DELETE FROM vandelay.import_item WHERE record = OLD.id;
1140 IF TG_OP = 'UPDATE' THEN
1145 $$ LANGUAGE PLPGSQL;
1147 CREATE TRIGGER cleanup_bib_trigger
1148 BEFORE UPDATE OR DELETE ON vandelay.queued_bib_record
1149 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_bib_marc();
1151 CREATE TRIGGER ingest_bib_trigger
1152 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1153 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_marc();
1155 CREATE TRIGGER ingest_item_trigger
1156 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1157 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_items();
1159 CREATE TRIGGER zz_match_bibs_trigger
1160 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1161 FOR EACH ROW EXECUTE PROCEDURE vandelay.match_bib_record();
1164 /* Authority stuff down here */
1165 ---------------------------------------
1166 CREATE TABLE vandelay.authority_attr_definition (
1167 id SERIAL PRIMARY KEY,
1168 code TEXT UNIQUE NOT NULL,
1170 xpath TEXT NOT NULL,
1171 remove TEXT NOT NULL DEFAULT '',
1172 ident BOOL NOT NULL DEFAULT FALSE
1175 CREATE TABLE vandelay.authority_queue (
1176 queue_type TEXT NOT NULL DEFAULT 'authority' CHECK (queue_type = 'authority'),
1177 CONSTRAINT vand_authority_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
1178 ) INHERITS (vandelay.queue);
1179 ALTER TABLE vandelay.authority_queue ADD PRIMARY KEY (id);
1181 CREATE TABLE vandelay.queued_authority_record (
1182 queue INT NOT NULL REFERENCES vandelay.authority_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1183 imported_as INT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1184 ) INHERITS (vandelay.queued_record);
1185 ALTER TABLE vandelay.queued_authority_record ADD PRIMARY KEY (id);
1186 CREATE INDEX queued_authority_record_queue_idx ON vandelay.queued_authority_record (queue);
1188 CREATE TABLE vandelay.queued_authority_record_attr (
1189 id BIGSERIAL PRIMARY KEY,
1190 record BIGINT NOT NULL REFERENCES vandelay.queued_authority_record (id) DEFERRABLE INITIALLY DEFERRED,
1191 field INT NOT NULL REFERENCES vandelay.authority_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
1192 attr_value TEXT NOT NULL
1194 CREATE INDEX queued_authority_record_attr_record_idx ON vandelay.queued_authority_record_attr (record);
1196 CREATE TABLE vandelay.authority_match (
1197 id BIGSERIAL PRIMARY KEY,
1198 matched_attr INT REFERENCES vandelay.queued_authority_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1199 queued_record BIGINT REFERENCES vandelay.queued_authority_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1200 eg_record BIGINT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1203 CREATE OR REPLACE FUNCTION vandelay.ingest_authority_marc ( ) RETURNS TRIGGER AS $$
1209 FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP
1211 SELECT extract_marc_field('vandelay.queued_authority_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_authority_record WHERE id = NEW.id;
1212 IF (value IS NOT NULL AND value <> '') THEN
1213 INSERT INTO vandelay.queued_authority_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
1220 $$ LANGUAGE PLPGSQL;
1222 CREATE OR REPLACE FUNCTION vandelay.cleanup_authority_marc ( ) RETURNS TRIGGER AS $$
1224 DELETE FROM vandelay.queued_authority_record_attr WHERE record = OLD.id;
1225 IF TG_OP = 'UPDATE' THEN
1230 $$ LANGUAGE PLPGSQL;
1232 CREATE TRIGGER cleanup_authority_trigger
1233 BEFORE UPDATE OR DELETE ON vandelay.queued_authority_record
1234 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_authority_marc();
1236 CREATE TRIGGER ingest_authority_trigger
1237 AFTER INSERT OR UPDATE ON vandelay.queued_authority_record
1238 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_authority_marc();
1240 CREATE OR REPLACE FUNCTION vandelay.overlay_authority_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1242 merge_profile vandelay.merge_profile%ROWTYPE;
1243 dyn_profile vandelay.compile_profile%ROWTYPE;
1252 SELECT b.marc INTO eg_marc
1253 FROM authority.record_entry b
1254 JOIN vandelay.authority_match m ON (m.eg_record = b.id AND m.queued_record = import_id)
1257 SELECT q.marc INTO v_marc
1258 FROM vandelay.queued_record q
1259 JOIN vandelay.authority_match m ON (m.queued_record = q.id AND q.id = import_id)
1262 IF eg_marc IS NULL OR v_marc IS NULL THEN
1263 -- RAISE NOTICE 'no marc for vandelay or authority record';
1267 dyn_profile := vandelay.compile_profile( v_marc );
1269 IF merge_profile_id IS NOT NULL THEN
1270 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
1272 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
1273 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
1274 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
1275 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
1279 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
1280 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
1284 IF dyn_profile.replace_rule <> '' THEN
1285 source_marc = v_marc;
1286 target_marc = eg_marc;
1287 replace_rule = dyn_profile.replace_rule;
1289 source_marc = eg_marc;
1290 target_marc = v_marc;
1291 replace_rule = dyn_profile.preserve_rule;
1294 UPDATE authority.record_entry
1295 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
1299 UPDATE vandelay.queued_authority_record
1300 SET imported_as = eg_id,
1302 WHERE id = import_id;
1306 -- RAISE NOTICE 'update of authority.record_entry failed';
1311 $$ LANGUAGE PLPGSQL;
1313 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1318 SELECT COUNT(*) INTO match_count FROM vandelay.authority_match WHERE queued_record = import_id;
1320 IF match_count <> 1 THEN
1321 -- RAISE NOTICE 'not an exact match';
1325 SELECT m.eg_record INTO eg_id
1326 FROM vandelay.authority_match m
1327 WHERE m.queued_record = import_id
1330 IF eg_id IS NULL THEN
1334 RETURN vandelay.overlay_authority_record( import_id, eg_id, merge_profile_id );
1336 $$ LANGUAGE PLPGSQL;
1338 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
1340 queued_record vandelay.queued_authority_record%ROWTYPE;
1343 FOR queued_record IN SELECT * FROM vandelay.queued_authority_record WHERE queue = queue_id AND import_time IS NULL LOOP
1345 IF vandelay.auto_overlay_authority_record( queued_record.id, merge_profile_id ) THEN
1346 RETURN NEXT queued_record.id;
1354 $$ LANGUAGE PLPGSQL;
1356 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
1357 SELECT * FROM vandelay.auto_overlay_authority_queue( $1, NULL );
1361 -- Vandelay (for importing and exporting records) 012.schema.vandelay.sql
1362 --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)]');
1363 --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)]');
1364 --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]');
1365 --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]');
1366 --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$);
1367 --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$);
1368 --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]');
1369 --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);
1370 --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);
1371 --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);
1372 --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);
1373 --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]');
1374 --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$);
1375 --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]');
1377 --INSERT INTO vandelay.import_item_attr_definition (
1378 -- owner, name, tag, owning_lib, circ_lib, location,
1379 -- call_number, circ_modifier, barcode, price, copy_number,
1380 -- circulate, ref, holdable, opac_visible, status
1383 -- 'Evergreen 852 export format',
1385 -- '[@code = "b"][1]',
1386 -- '[@code = "b"][2]',
1393 -- '[@code = "x" and text() = "circulating"]',
1394 -- '[@code = "x" and text() = "reference"]',
1395 -- '[@code = "x" and text() = "holdable"]',
1396 -- '[@code = "x" and text() = "visible"]',
1400 --INSERT INTO vandelay.import_item_attr_definition (
1413 -- 'Unicorn Import format -- 999',
1424 --INSERT INTO vandelay.authority_attr_definition ( code, description, xpath, ident ) VALUES ('rec_identifier','Identifier','//*[@tag="001"]', TRUE);