1 DROP SCHEMA IF EXISTS vandelay CASCADE;
5 CREATE OR REPLACE FUNCTION array_remove_item_by_value(inp ANYARRAY, el ANYELEMENT) RETURNS anyarray AS $$ SELECT ARRAY_ACCUM(x.e) FROM UNNEST( $1 ) x(e) WHERE x.e <> $2; $$ LANGUAGE SQL;
7 CREATE SCHEMA vandelay;
9 CREATE TABLE vandelay.match_set (
10 id SERIAL PRIMARY KEY,
12 owner INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE,
13 mtype TEXT NOT NULL DEFAULT 'biblio', -- 'biblio','authority','mfhd'?, others?
14 CONSTRAINT name_once_per_owner_mtype UNIQUE (name, owner, mtype)
17 -- Table to define match points, either FF via SVF or tag+subfield
18 CREATE TABLE vandelay.match_set_point (
19 id SERIAL PRIMARY KEY,
20 match_set INT REFERENCES vandelay.match_set (id),
21 parent INT REFERENCES vandelay.match_set_point (id),
22 bool_op TEXT CHECK (bool_op IS NULL OR (bool_op IN ('AND','OR','NOT'))),
23 svf TEXT REFERENCES config.record_attr_definition (name),
26 negate BOOL DEFAULT FALSE,
27 quality INT NOT NULL DEFAULT 1, -- higher is better
28 CONSTRAINT vmsp_need_a_subfield_with_a_tag CHECK ((tag IS NOT NULL AND subfield IS NOT NULL) OR tag IS NULL),
29 CONSTRAINT vmsp_need_a_tag_or_a_ff_or_a_bo CHECK (
30 (tag IS NOT NULL AND svf IS NULL AND bool_op IS NULL) OR
31 (tag IS NULL AND svf IS NOT NULL AND bool_op IS NULL) OR
32 (tag IS NULL AND svf IS NULL AND bool_op IS NOT NULL)
36 CREATE TABLE vandelay.match_set_quality (
37 id SERIAL PRIMARY KEY,
38 match_set INT NOT NULL REFERENCES vandelay.match_set (id),
39 svf TEXT REFERENCES config.record_attr_definition,
43 quality INT NOT NULL DEFAULT 1, -- higher is better
44 CONSTRAINT vmsq_need_a_subfield_with_a_tag CHECK ((tag IS NOT NULL AND subfield IS NOT NULL) OR tag IS NULL),
45 CONSTRAINT vmsq_need_a_tag_or_a_ff CHECK ((tag IS NOT NULL AND svf IS NULL) OR (tag IS NULL AND svf IS NOT NULL))
47 CREATE UNIQUE INDEX vmsq_def_once_per_set ON vandelay.match_set_quality (match_set, COALESCE(tag,''), COALESCE(subfield,''), COALESCE(svf,''));
50 CREATE TABLE vandelay.queue (
51 id BIGSERIAL PRIMARY KEY,
52 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
54 complete BOOL NOT NULL DEFAULT FALSE,
55 queue_type TEXT NOT NULL DEFAULT 'bib' CHECK (queue_type IN ('bib','authority')),
56 match_set INT REFERENCES vandelay.match_set (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
57 CONSTRAINT vand_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
60 CREATE TABLE vandelay.queued_record (
61 id BIGSERIAL PRIMARY KEY,
62 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
63 import_time TIMESTAMP WITH TIME ZONE,
64 purpose TEXT NOT NULL DEFAULT 'import' CHECK (purpose IN ('import','overlay')),
66 quality INT NOT NULL DEFAULT 0
71 /* Bib stuff at the top */
72 ----------------------------------------------------
74 CREATE TABLE vandelay.bib_attr_definition (
75 id SERIAL PRIMARY KEY,
76 code TEXT UNIQUE NOT NULL,
79 remove TEXT NOT NULL DEFAULT ''
82 -- Each TEXT field (other than 'name') should hold an XPath predicate for pulling the data needed
83 -- DROP TABLE vandelay.import_item_attr_definition CASCADE;
84 CREATE TABLE vandelay.import_item_attr_definition (
85 id BIGSERIAL PRIMARY KEY,
86 owner INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
89 keep BOOL NOT NULL DEFAULT FALSE,
109 priv_note_title TEXT,
111 CONSTRAINT vand_import_item_attr_def_idx UNIQUE (owner,name)
114 CREATE TABLE vandelay.import_error (
115 code TEXT PRIMARY KEY,
116 description TEXT NOT NULL -- i18n
119 CREATE TABLE vandelay.bib_queue (
120 queue_type TEXT NOT NULL DEFAULT 'bib' CHECK (queue_type = 'bib'),
121 item_attr_def BIGINT REFERENCES vandelay.import_item_attr_definition (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
122 CONSTRAINT vand_bib_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
123 ) INHERITS (vandelay.queue);
124 ALTER TABLE vandelay.bib_queue ADD PRIMARY KEY (id);
126 CREATE TABLE vandelay.queued_bib_record (
127 queue INT NOT NULL REFERENCES vandelay.bib_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
128 bib_source INT REFERENCES config.bib_source (id) DEFERRABLE INITIALLY DEFERRED,
129 imported_as BIGINT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
130 import_error TEXT REFERENCES vandelay.import_error (code) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
132 ) INHERITS (vandelay.queued_record);
133 ALTER TABLE vandelay.queued_bib_record ADD PRIMARY KEY (id);
134 CREATE INDEX queued_bib_record_queue_idx ON vandelay.queued_bib_record (queue);
136 CREATE TABLE vandelay.queued_bib_record_attr (
137 id BIGSERIAL PRIMARY KEY,
138 record BIGINT NOT NULL REFERENCES vandelay.queued_bib_record (id) DEFERRABLE INITIALLY DEFERRED,
139 field INT NOT NULL REFERENCES vandelay.bib_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
140 attr_value TEXT NOT NULL
142 CREATE INDEX queued_bib_record_attr_record_idx ON vandelay.queued_bib_record_attr (record);
144 CREATE TABLE vandelay.bib_match (
145 id BIGSERIAL PRIMARY KEY,
146 matched_set INT REFERENCES vandelay.match_set (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
147 queued_record BIGINT REFERENCES vandelay.queued_bib_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
148 eg_record BIGINT REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
149 quality INT NOT NULL DEFAULT 0
152 CREATE TABLE vandelay.import_item (
153 id BIGSERIAL PRIMARY KEY,
154 record BIGINT NOT NULL REFERENCES vandelay.queued_bib_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
155 definition BIGINT NOT NULL REFERENCES vandelay.import_item_attr_definition (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
156 import_error TEXT REFERENCES vandelay.import_error (code) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
166 deposit_amount NUMERIC(8,2),
179 CREATE TABLE vandelay.import_bib_trash_fields (
180 id BIGSERIAL PRIMARY KEY,
181 owner INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
183 CONSTRAINT vand_import_bib_trash_fields_idx UNIQUE (owner,field)
186 CREATE TABLE vandelay.merge_profile (
187 id BIGSERIAL PRIMARY KEY,
188 owner INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
194 CONSTRAINT vand_merge_prof_owner_name_idx UNIQUE (owner,name),
195 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))
198 CREATE OR REPLACE FUNCTION vandelay.marc21_record_type( marc TEXT ) RETURNS config.marc21_rec_type_map AS $func$
205 retval config.marc21_rec_type_map%ROWTYPE;
207 ldr := oils_xpath_string( '//*[local-name()="leader"]', marc );
209 IF ldr IS NULL OR ldr = '' THEN
210 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE code = 'BKS';
214 SELECT * INTO tval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'Type' LIMIT 1; -- They're all the same
215 SELECT * INTO bval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'BLvl' LIMIT 1; -- They're all the same
218 tval := SUBSTRING( ldr, tval_rec.start_pos + 1, tval_rec.length );
219 bval := SUBSTRING( ldr, bval_rec.start_pos + 1, bval_rec.length );
221 -- RAISE NOTICE 'type %, blvl %, ldr %', tval, bval, ldr;
223 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE type_val LIKE '%' || tval || '%' AND blvl_val LIKE '%' || bval || '%';
226 IF retval.code IS NULL THEN
227 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE code = 'BKS';
232 $func$ LANGUAGE PLPGSQL;
234 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field( marc TEXT, ff TEXT ) RETURNS TEXT AS $func$
241 rtype := (vandelay.marc21_record_type( marc )).code;
242 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = ff AND rec_type = rtype ORDER BY tag DESC LOOP
243 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
244 val := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length );
247 val := REPEAT( ff_pos.default_val, ff_pos.length );
253 $func$ LANGUAGE PLPGSQL;
255 CREATE TYPE biblio.record_ff_map AS (record BIGINT, ff_name TEXT, ff_value TEXT);
256 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_all_fixed_fields( marc TEXT ) RETURNS SETOF biblio.record_ff_map AS $func$
261 output biblio.record_ff_map%ROWTYPE;
263 rtype := (vandelay.marc21_record_type( marc )).code;
265 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE rec_type = rtype ORDER BY tag DESC LOOP
266 output.ff_name := ff_pos.fixed_field;
267 output.ff_value := NULL;
269 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(tag) || '"]/text()', marc ) ) x(value) LOOP
270 output.ff_value := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length );
271 IF output.ff_value IS NULL THEN output.ff_value := REPEAT( ff_pos.default_val, ff_pos.length ); END IF;
273 output.ff_value := NULL;
280 $func$ LANGUAGE PLPGSQL;
282 CREATE TYPE biblio.marc21_physical_characteristics AS ( id INT, record BIGINT, ptype TEXT, subfield INT, value INT );
283 CREATE OR REPLACE FUNCTION vandelay.marc21_physical_characteristics( marc TEXT) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$
287 ptype config.marc21_physical_characteristic_type_map%ROWTYPE;
288 psf config.marc21_physical_characteristic_subfield_map%ROWTYPE;
289 pval config.marc21_physical_characteristic_value_map%ROWTYPE;
290 retval biblio.marc21_physical_characteristics%ROWTYPE;
293 _007 := oils_xpath_string( '//*[@tag="007"]', marc );
295 IF _007 IS NOT NULL AND _007 <> '' THEN
296 SELECT * INTO ptype FROM config.marc21_physical_characteristic_type_map WHERE ptype_key = SUBSTRING( _007, 1, 1 );
298 IF ptype.ptype_key IS NOT NULL THEN
299 FOR psf IN SELECT * FROM config.marc21_physical_characteristic_subfield_map WHERE ptype_key = ptype.ptype_key LOOP
300 SELECT * INTO pval FROM config.marc21_physical_characteristic_value_map WHERE ptype_subfield = psf.id AND value = SUBSTRING( _007, psf.start_pos + 1, psf.length );
302 IF pval.id IS NOT NULL THEN
305 retval.ptype := ptype.ptype_key;
306 retval.subfield := psf.id;
307 retval.value := pval.id;
317 $func$ LANGUAGE PLPGSQL;
319 CREATE TYPE vandelay.flat_marc AS ( tag CHAR(3), ind1 TEXT, ind2 TEXT, subfield TEXT, value TEXT );
320 CREATE OR REPLACE FUNCTION vandelay.flay_marc ( TEXT ) RETURNS SETOF vandelay.flat_marc AS $func$
323 use MARC::File::XML (BinaryEncoding => 'UTF-8');
326 my $r = MARC::Record->new_from_xml( $xml );
328 return_next( { tag => 'LDR', value => $r->leader } );
330 for my $f ( $r->fields ) {
331 if ($f->is_control_field) {
332 return_next({ tag => $f->tag, value => $f->data });
334 for my $s ($f->subfields) {
337 ind1 => $f->indicator(1),
338 ind2 => $f->indicator(2),
343 if ( $f->tag eq '245' and $s->[0] eq 'a' ) {
344 my $trim = $f->indicator(2) || 0;
347 ind1 => $f->indicator(1),
348 ind2 => $f->indicator(2),
350 value => substr( $s->[1], $trim )
359 $func$ LANGUAGE PLPERLU;
361 CREATE OR REPLACE FUNCTION vandelay.flatten_marc ( marc TEXT ) RETURNS SETOF vandelay.flat_marc AS $func$
363 output vandelay.flat_marc%ROWTYPE;
366 FOR field IN SELECT * FROM vandelay.flay_marc( marc ) LOOP
367 output.ind1 := field.ind1;
368 output.ind2 := field.ind2;
369 output.tag := field.tag;
370 output.subfield := field.subfield;
371 IF field.subfield IS NOT NULL AND field.tag NOT IN ('020','022','024') THEN -- exclude standard numbers and control fields
372 output.value := naco_normalize(field.value, field.subfield);
374 output.value := field.value;
377 CONTINUE WHEN output.value IS NULL;
382 $func$ LANGUAGE PLPGSQL;
384 CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT, attr_defs TEXT[]) RETURNS hstore AS $_$
386 transformed_xml TEXT;
389 xfrm config.xml_transform%ROWTYPE;
391 new_attrs HSTORE := ''::HSTORE;
392 attr_def config.record_attr_definition%ROWTYPE;
395 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE name IN (SELECT * FROM UNNEST(attr_defs)) ORDER BY format LOOP
397 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
398 SELECT ARRAY_TO_STRING(ARRAY_ACCUM(x.value), COALESCE(attr_def.joiner,' ')) INTO attr_value
399 FROM vandelay.flatten_marc(xml) AS x
400 WHERE x.tag LIKE attr_def.tag
402 WHEN attr_def.sf_list IS NOT NULL
403 THEN POSITION(x.subfield IN attr_def.sf_list) > 0
410 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
411 attr_value := vandelay.marc21_extract_fixed_field(xml, attr_def.fixed_field);
413 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
415 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
417 -- See if we can skip the XSLT ... it's expensive
418 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
419 -- Can't skip the transform
420 IF xfrm.xslt <> '---' THEN
421 transformed_xml := oils_xslt_process(xml,xfrm.xslt);
423 transformed_xml := xml;
426 prev_xfrm := xfrm.name;
429 IF xfrm.name IS NULL THEN
430 -- just grab the marcxml (empty) transform
431 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
432 prev_xfrm := xfrm.name;
435 attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]);
437 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
438 SELECT value::TEXT INTO attr_value
439 FROM vandelay.marc21_physical_characteristics(xml)
440 WHERE subfield = attr_def.phys_char_sf
441 LIMIT 1; -- Just in case ...
445 -- apply index normalizers to attr_value
447 SELECT n.func AS func,
448 n.param_count AS param_count,
450 FROM config.index_normalizer n
451 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
452 WHERE attr = attr_def.name
454 EXECUTE 'SELECT ' || normalizer.func || '(' ||
455 quote_literal( attr_value ) ||
457 WHEN normalizer.param_count > 0
458 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
465 -- Add the new value to the hstore
466 new_attrs := new_attrs || hstore( attr_def.name, attr_value );
472 $_$ LANGUAGE PLPGSQL;
474 CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT ) RETURNS hstore AS $_$
475 SELECT vandelay.extract_rec_attrs( $1, (SELECT ARRAY_ACCUM(name) FROM config.record_attr_definition));
478 CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
480 incoming_existing_id TEXT;
481 my_bib_queue vandelay.bib_queue%ROWTYPE;
482 my_match_set vandelay.match_set%ROWTYPE;
483 test vandelay.match_set_point%ROWTYPE;
484 potential_matches BIGINT[];
492 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
494 incoming_existing_id := oils_xpath_string('//*[@tag="901"]/*[@code="c"][1]',NEW.marc);
496 IF incoming_existing_id IS NOT NULL THEN
497 SELECT id INTO tmp_rec FROM biblio.record_entry WHERE id = exact_id;
498 IF tmp_rec IS NOT NULL THEN
499 INSERT INTO vandelay.bib_match (queued_record, eg_record, quality) VALUES ( NEW.id, exact_id, 9999);
504 SELECT * INTO my_bib_queue FROM vandelay.bib_queue WHERE id = NEW.queue;
507 -- whew ... here we go ...
510 -- Commented out until replaced by tree-ish version
512 FOR test IN SELECT * FROM vandelay.match_set_point WHERE match_set = my_bib_queue.match_set ORDER BY required DESC LOOP
513 IF test.tag IS NOT NULL THEN
514 FOR rvalue IN SELECT value FROM vandelay.flatten_marc( xml ) WHERE tag = test.tag AND subfield = test.subfield LOOP
515 SELECT ARRAY_ACCUM(DISTINCT record) INTO potential_matches FROM metabib.real_full_rec WHERE tag = test.tag AND subfield = test.subfield AND value = rvalue;
518 matches := potential_matches;
519 first_round := FALSE;
520 ELSIF test.required THEN
521 FOR tmp_rec IN SELECT * FROM UNNEST(matches) LOOP
522 IF tmp_rec NOT IN (SELECT * FROM UNNEST(potential_matches)) THEN
523 matches := array_remove_item_by_value(matches, tmp_rec);
524 potential_matches := array_remove_item_by_value(potential_matches, tmp_rec);
529 -- add the quality for this match
530 FOR tmp_rec IN SELECT * FROM UNNEST(potential_matches) LOOP
531 tmp_quality := COALESCE((quality_set -> tmp_rec::TEXT)::INT, 0);
532 quality_set := quality_set || hstore(tmp_rec::TEXT, (tmp_quality + test.quality)::TEXT);
537 rvalue := vandelay.vandelay.extract_rec_attrs(xml, ARRAY[test.svf]);
540 matches := potential_matches;
541 first_round := FALSE;
542 ELSIF test.required THEN
543 FOR tmp_rec IN SELECT * FROM UNNEST(matches) LOOP
544 IF tmp_rec NOT IN (SELECT * FROM UNNEST(potential_matches)) THEN
545 matches := array_remove_item_by_value(matches, tmp_rec);
546 potential_matches := array_remove_item_by_value(potential_matches, tmp_rec);
551 -- add the quality for this match
552 FOR tmp_rec IN SELECT * FROM UNNEST(potential_matches) LOOP
553 tmp_quality := COALESCE((quality_set -> tmp_rec::TEXT)::INT, 0);
554 quality_set := quality_set || hstore(tmp_rec::TEXT, (tmp_quality + test.quality)::TEXT);
560 FOR tmp_rec IN SELECT * FROM UNNEST(matches) LOOP
561 INSERT INTO vandelay.bib_match (matched_set, queued_record, eg_record, quality) VALUES (my_bib_queue.match_set, NEW.id, tmp_rec, (quality_set -> tmp_rec::TEXT));
567 $func$ LANGUAGE PLPGSQL;
569 CREATE OR REPLACE FUNCTION vandelay.incoming_record_quality ( xml TEXT, match_set_id INT ) RETURNS INT AS $_$
573 test vandelay.match_set_quality%ROWTYPE;
576 FOR test IN SELECT * FROM vandelay.match_set_quality WHERE match_set = match_set_id LOOP
577 IF test.tag IS NOT NULL THEN
578 FOR rvalue IN SELECT value FROM vandelay.flatten_marc( xml ) WHERE tag = test.tag AND subfield = test.subfield LOOP
579 IF test.value = rvalue THEN
580 out_q := out_q + test.quality;
584 IF test.value = vandelay.extract_rec_attrs(xml, ARRAY[test.svf]) THEN
585 out_q := out_q + test.quality;
592 $_$ LANGUAGE PLPGSQL;
594 CREATE TYPE vandelay.tcn_data AS (tcn TEXT, tcn_source TEXT, used BOOL);
595 CREATE OR REPLACE FUNCTION vandelay.find_bib_tcn_data ( xml TEXT ) RETURNS SETOF vandelay.tcn_data AS $_$
599 output vandelay.tcn_data%ROWTYPE;
603 eg_tcn := BTRIM((oils_xpath('//*[@tag="001"]/text()',xml))[1]);
604 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
606 eg_tcn_source := BTRIM((oils_xpath('//*[@tag="003"]/text()',xml))[1]);
607 IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN
608 eg_tcn_source := 'System Local';
611 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
614 output.used := FALSE;
619 output.tcn := eg_tcn;
620 output.tcn_source := eg_tcn_source;
626 eg_tcn := BTRIM((oils_xpath('//*[@tag="901"]/*[@code="a"]/text()',xml))[1]);
627 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
629 eg_tcn_source := BTRIM((oils_xpath('//*[@tag="901"]/*[@code="b"]/text()',xml))[1]);
630 IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN
631 eg_tcn_source := 'System Local';
634 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
637 output.used := FALSE;
642 output.tcn := eg_tcn;
643 output.tcn_source := eg_tcn_source;
649 eg_tcn := BTRIM((oils_xpath('//*[@tag="039"]/*[@code="a"]/text()',xml))[1]);
650 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
652 eg_tcn_source := BTRIM((oils_xpath('//*[@tag="039"]/*[@code="b"]/text()',xml))[1]);
653 IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN
654 eg_tcn_source := 'System Local';
657 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
660 output.used := FALSE;
665 output.tcn := eg_tcn;
666 output.tcn_source := eg_tcn_source;
672 eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="020"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$);
673 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
675 eg_tcn_source := 'ISBN';
677 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
680 output.used := FALSE;
685 output.tcn := eg_tcn;
686 output.tcn_source := eg_tcn_source;
692 eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="022"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$);
693 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
695 eg_tcn_source := 'ISSN';
697 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
700 output.used := FALSE;
705 output.tcn := eg_tcn;
706 output.tcn_source := eg_tcn_source;
712 eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="010"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$);
713 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
715 eg_tcn_source := 'LCCN';
717 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
720 output.used := FALSE;
725 output.tcn := eg_tcn;
726 output.tcn_source := eg_tcn_source;
732 eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="035"]/*[@code="a"]/text()',xml))[1], $re$^.*?(\w+)$$re$, $re$\1$re$);
733 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
735 eg_tcn_source := 'System Legacy';
737 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
740 output.used := FALSE;
745 output.tcn := eg_tcn;
746 output.tcn_source := eg_tcn_source;
753 $_$ LANGUAGE PLPGSQL;
755 CREATE OR REPLACE FUNCTION vandelay.add_field ( target_xml TEXT, source_xml TEXT, field TEXT, force_add INT ) RETURNS TEXT AS $_$
758 use MARC::File::XML (BinaryEncoding => 'UTF-8');
762 MARC::Charset->assume_unicode(1);
764 my $target_xml = shift;
765 my $source_xml = shift;
766 my $field_spec = shift;
767 my $force_add = shift || 0;
769 my $target_r = MARC::Record->new_from_xml( $target_xml );
770 my $source_r = MARC::Record->new_from_xml( $source_xml );
772 return $target_xml unless ($target_r && $source_r);
774 my @field_list = split(',', $field_spec);
777 for my $f (@field_list) {
778 $f =~ s/^\s*//; $f =~ s/\s*$//;
779 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
785 $match =~ s/^\s*//; $match =~ s/\s*$//;
786 $fields{$field} = { sf => [ split('', $sf) ] };
788 my ($msf,$mre) = split('~', $match);
789 if (length($msf) > 0 and length($mre) > 0) {
790 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
791 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
792 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
798 for my $f ( keys %fields) {
799 if ( @{$fields{$f}{sf}} ) {
800 for my $from_field ($source_r->field( $f )) {
801 my @tos = $target_r->field( $f );
803 next if (exists($fields{$f}{match}) and !$force_add);
804 my @new_fields = map { $_->clone } $source_r->field( $f );
805 $target_r->insert_fields_ordered( @new_fields );
807 for my $to_field (@tos) {
808 if (exists($fields{$f}{match})) {
809 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
811 my @new_sf = map { ($_ => $from_field->subfield($_)) } @{$fields{$f}{sf}};
812 $to_field->add_subfields( @new_sf );
817 my @new_fields = map { $_->clone } $source_r->field( $f );
818 $target_r->insert_fields_ordered( @new_fields );
822 $target_xml = $target_r->as_xml_record;
823 $target_xml =~ s/^<\?.+?\?>$//mo;
824 $target_xml =~ s/\n//sgo;
825 $target_xml =~ s/>\s+</></sgo;
829 $_$ LANGUAGE PLPERLU;
831 CREATE OR REPLACE FUNCTION vandelay.add_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
832 SELECT vandelay.add_field( $1, $2, $3, 0 );
835 CREATE OR REPLACE FUNCTION vandelay.strip_field ( xml TEXT, field TEXT ) RETURNS TEXT AS $_$
838 use MARC::File::XML (BinaryEncoding => 'UTF-8');
842 MARC::Charset->assume_unicode(1);
845 my $r = MARC::Record->new_from_xml( $xml );
847 return $xml unless ($r);
849 my $field_spec = shift;
850 my @field_list = split(',', $field_spec);
853 for my $f (@field_list) {
854 $f =~ s/^\s*//; $f =~ s/\s*$//;
855 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
861 $match =~ s/^\s*//; $match =~ s/\s*$//;
862 $fields{$field} = { sf => [ split('', $sf) ] };
864 my ($msf,$mre) = split('~', $match);
865 if (length($msf) > 0 and length($mre) > 0) {
866 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
867 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
868 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
874 for my $f ( keys %fields) {
875 for my $to_field ($r->field( $f )) {
876 if (exists($fields{$f}{match})) {
877 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
880 if ( @{$fields{$f}{sf}} ) {
881 $to_field->delete_subfield(code => $fields{$f}{sf});
883 $r->delete_field( $to_field );
888 $xml = $r->as_xml_record;
889 $xml =~ s/^<\?.+?\?>$//mo;
891 $xml =~ s/>\s+</></sgo;
895 $_$ LANGUAGE PLPERLU;
897 CREATE OR REPLACE FUNCTION vandelay.replace_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
904 parsed_target := vandelay.strip_field( target_xml, ''); -- this dance normalizes the format of the xml for the IF below
906 FOR curr_field IN SELECT UNNEST( STRING_TO_ARRAY(field, ',') ) LOOP -- naive split, but it's the same we use in the perl
908 xml_output := vandelay.strip_field( parsed_target, curr_field);
910 IF xml_output <> parsed_target AND curr_field ~ E'~' THEN
911 -- we removed something, and there was a regexp restriction in the curr_field definition, so proceed
912 xml_output := vandelay.add_field( xml_output, source_xml, curr_field, 1 );
913 ELSIF curr_field !~ E'~' THEN
914 -- No regexp restriction, add the curr_field
915 xml_output := vandelay.add_field( xml_output, source_xml, curr_field, 0 );
918 parsed_target := xml_output; -- in prep for any following loop iterations
924 $_$ LANGUAGE PLPGSQL;
926 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 $_$
927 SELECT vandelay.replace_field( vandelay.add_field( vandelay.strip_field( $1, $5) , $2, $3 ), $2, $4);
930 CREATE TYPE vandelay.compile_profile AS (add_rule TEXT, replace_rule TEXT, preserve_rule TEXT, strip_rule TEXT);
931 CREATE OR REPLACE FUNCTION vandelay.compile_profile ( incoming_xml TEXT ) RETURNS vandelay.compile_profile AS $_$
933 output vandelay.compile_profile%ROWTYPE;
934 profile vandelay.merge_profile%ROWTYPE;
936 profile_tmpl_owner TEXT;
938 strip_rule TEXT := '';
939 replace_rule TEXT := '';
940 preserve_rule TEXT := '';
944 profile_tmpl := (oils_xpath('//*[@tag="905"]/*[@code="t"]/text()',incoming_xml))[1];
945 profile_tmpl_owner := (oils_xpath('//*[@tag="905"]/*[@code="o"]/text()',incoming_xml))[1];
947 IF profile_tmpl IS NOT NULL AND profile_tmpl <> '' AND profile_tmpl_owner IS NOT NULL AND profile_tmpl_owner <> '' THEN
948 SELECT p.* INTO profile
949 FROM vandelay.merge_profile p
950 JOIN actor.org_unit u ON (u.id = p.owner)
951 WHERE p.name = profile_tmpl
952 AND u.shortname = profile_tmpl_owner;
954 IF profile.id IS NOT NULL THEN
955 add_rule := COALESCE(profile.add_spec,'');
956 strip_rule := COALESCE(profile.strip_spec,'');
957 replace_rule := COALESCE(profile.replace_spec,'');
958 preserve_rule := COALESCE(profile.preserve_spec,'');
962 add_rule := add_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="a"]/text()',incoming_xml),','),'');
963 strip_rule := strip_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="d"]/text()',incoming_xml),','),'');
964 replace_rule := replace_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="r"]/text()',incoming_xml),','),'');
965 preserve_rule := preserve_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="p"]/text()',incoming_xml),','),'');
967 output.add_rule := BTRIM(add_rule,',');
968 output.replace_rule := BTRIM(replace_rule,',');
969 output.strip_rule := BTRIM(strip_rule,',');
970 output.preserve_rule := BTRIM(preserve_rule,',');
974 $_$ LANGUAGE PLPGSQL;
976 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
978 merge_profile vandelay.merge_profile%ROWTYPE;
979 dyn_profile vandelay.compile_profile%ROWTYPE;
989 SELECT b.marc INTO eg_marc
990 FROM biblio.record_entry b
994 IF eg_marc IS NULL OR v_marc IS NULL THEN
995 -- RAISE NOTICE 'no marc for template or bib record';
999 dyn_profile := vandelay.compile_profile( v_marc );
1001 IF merge_profile_id IS NOT NULL THEN
1002 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
1004 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
1005 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
1006 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
1007 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
1011 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
1012 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
1016 IF dyn_profile.replace_rule <> '' THEN
1017 source_marc = v_marc;
1018 target_marc = eg_marc;
1019 replace_rule = dyn_profile.replace_rule;
1021 source_marc = eg_marc;
1022 target_marc = v_marc;
1023 replace_rule = dyn_profile.preserve_rule;
1026 UPDATE biblio.record_entry
1027 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
1031 -- RAISE NOTICE 'update of biblio.record_entry failed';
1038 $$ LANGUAGE PLPGSQL;
1040 CREATE OR REPLACE FUNCTION vandelay.merge_record_xml ( target_marc TEXT, template_marc TEXT ) RETURNS TEXT AS $$
1042 dyn_profile vandelay.compile_profile%ROWTYPE;
1050 IF target_marc IS NULL OR template_marc IS NULL THEN
1051 -- RAISE NOTICE 'no marc for target or template record';
1055 dyn_profile := vandelay.compile_profile( template_marc );
1057 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
1058 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
1062 IF dyn_profile.replace_rule <> '' THEN
1063 trgt_marc = target_marc;
1064 tmpl_marc = template_marc;
1065 replace_rule = dyn_profile.replace_rule;
1067 tmp_marc = target_marc;
1068 trgt_marc = template_marc;
1069 tmpl_marc = tmp_marc;
1070 replace_rule = dyn_profile.preserve_rule;
1073 RETURN vandelay.merge_record_xml( trgt_marc, tmpl_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule );
1076 $$ LANGUAGE PLPGSQL;
1078 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT) RETURNS BOOL AS $$
1079 SELECT vandelay.template_overlay_bib_record( $1, $2, NULL);
1082 CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1084 merge_profile vandelay.merge_profile%ROWTYPE;
1085 dyn_profile vandelay.compile_profile%ROWTYPE;
1096 SELECT q.marc INTO v_marc
1097 FROM vandelay.queued_record q
1098 JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
1101 IF v_marc IS NULL THEN
1102 -- RAISE NOTICE 'no marc for vandelay or bib record';
1106 IF vandelay.template_overlay_bib_record( v_marc, eg_id, merge_profile_id) THEN
1107 UPDATE vandelay.queued_bib_record
1108 SET imported_as = eg_id,
1110 WHERE id = import_id;
1112 editor_string := (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1];
1114 IF editor_string IS NOT NULL AND editor_string <> '' THEN
1115 SELECT usr INTO editor_id FROM actor.card WHERE barcode = editor_string;
1117 IF editor_id IS NULL THEN
1118 SELECT id INTO editor_id FROM actor.usr WHERE usrname = editor_string;
1121 IF editor_id IS NOT NULL THEN
1122 UPDATE biblio.record_entry SET editor = editor_id WHERE id = eg_id;
1129 -- RAISE NOTICE 'update of biblio.record_entry failed';
1134 $$ LANGUAGE PLPGSQL;
1136 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1140 match_attr vandelay.bib_attr_definition%ROWTYPE;
1143 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
1146 -- RAISE NOTICE 'already imported, cannot auto-overlay'
1150 SELECT COUNT(*) INTO match_count FROM vandelay.bib_match WHERE queued_record = import_id;
1152 IF match_count <> 1 THEN
1153 -- RAISE NOTICE 'not an exact match';
1157 SELECT d.* INTO match_attr
1158 FROM vandelay.bib_attr_definition d
1159 JOIN vandelay.queued_bib_record_attr a ON (a.field = d.id)
1160 JOIN vandelay.bib_match m ON (m.matched_attr = a.id)
1161 WHERE m.queued_record = import_id;
1163 IF NOT (match_attr.xpath ~ '@tag="901"' AND match_attr.xpath ~ '@code="c"') THEN
1164 -- RAISE NOTICE 'not a 901c match: %', match_attr.xpath;
1168 SELECT m.eg_record INTO eg_id
1169 FROM vandelay.bib_match m
1170 WHERE m.queued_record = import_id
1173 IF eg_id IS NULL THEN
1177 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
1179 $$ LANGUAGE PLPGSQL;
1181 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
1183 queued_record vandelay.queued_bib_record%ROWTYPE;
1186 FOR queued_record IN SELECT * FROM vandelay.queued_bib_record WHERE queue = queue_id AND import_time IS NULL LOOP
1188 IF vandelay.auto_overlay_bib_record( queued_record.id, merge_profile_id ) THEN
1189 RETURN NEXT queued_record.id;
1197 $$ LANGUAGE PLPGSQL;
1199 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
1200 SELECT * FROM vandelay.auto_overlay_bib_queue( $1, NULL );
1203 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
1214 deposit_amount TEXT;
1227 tmp_attr_set RECORD;
1228 attr_set vandelay.import_item%ROWTYPE;
1234 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
1238 attr_set.definition := attr_def.id;
1240 -- Build the combined XPath
1244 WHEN attr_def.owning_lib IS NULL THEN 'null()'
1245 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
1246 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
1251 WHEN attr_def.circ_lib IS NULL THEN 'null()'
1252 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
1253 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
1258 WHEN attr_def.call_number IS NULL THEN 'null()'
1259 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
1260 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
1265 WHEN attr_def.copy_number IS NULL THEN 'null()'
1266 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
1267 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
1272 WHEN attr_def.status IS NULL THEN 'null()'
1273 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
1274 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
1279 WHEN attr_def.location IS NULL THEN 'null()'
1280 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
1281 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
1286 WHEN attr_def.circulate IS NULL THEN 'null()'
1287 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
1288 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
1293 WHEN attr_def.deposit IS NULL THEN 'null()'
1294 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
1295 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
1300 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
1301 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
1302 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
1307 WHEN attr_def.ref IS NULL THEN 'null()'
1308 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
1309 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
1314 WHEN attr_def.holdable IS NULL THEN 'null()'
1315 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
1316 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
1321 WHEN attr_def.price IS NULL THEN 'null()'
1322 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
1323 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
1328 WHEN attr_def.barcode IS NULL THEN 'null()'
1329 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
1330 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
1335 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
1336 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
1337 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
1342 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
1343 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
1344 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
1349 WHEN attr_def.alert_message IS NULL THEN 'null()'
1350 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
1351 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
1356 WHEN attr_def.opac_visible IS NULL THEN 'null()'
1357 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
1358 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
1363 WHEN attr_def.pub_note IS NULL THEN 'null()'
1364 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
1365 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
1369 WHEN attr_def.priv_note IS NULL THEN 'null()'
1370 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
1371 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
1376 owning_lib || '|' ||
1378 call_number || '|' ||
1379 copy_number || '|' ||
1384 deposit_amount || '|' ||
1389 circ_modifier || '|' ||
1390 circ_as_type || '|' ||
1391 alert_message || '|' ||
1396 -- RAISE NOTICE 'XPath: %', xpath;
1400 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
1401 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
1402 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
1403 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, opac_vis TEXT )
1406 tmp_attr_set.pr = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
1407 tmp_attr_set.dep_amount = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
1409 tmp_attr_set.pr := NULLIF( tmp_attr_set.pr, '' );
1410 tmp_attr_set.dep_amount := NULLIF( tmp_attr_set.dep_amount, '' );
1412 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
1413 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
1414 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
1416 SELECT id INTO attr_set.location
1417 FROM asset.copy_location
1418 WHERE LOWER(name) = LOWER(tmp_attr_set.cl)
1419 AND asset.copy_location.owning_lib = COALESCE(attr_set.owning_lib, attr_set.circ_lib); -- INT
1421 attr_set.circulate :=
1422 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
1423 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
1426 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
1427 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
1429 attr_set.holdable :=
1430 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
1431 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
1433 attr_set.opac_visible :=
1434 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
1435 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
1438 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
1439 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
1441 attr_set.copy_number := tmp_attr_set.cnum::INT; -- INT,
1442 attr_set.deposit_amount := tmp_attr_set.dep_amount::NUMERIC(6,2); -- NUMERIC(6,2),
1443 attr_set.price := tmp_attr_set.pr::NUMERIC(8,2); -- NUMERIC(8,2),
1445 attr_set.call_number := tmp_attr_set.cn; -- TEXT
1446 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
1447 attr_set.circ_modifier := tmp_attr_set.circ_mod; -- TEXT,
1448 attr_set.circ_as_type := tmp_attr_set.circ_as; -- TEXT,
1449 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
1450 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
1451 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
1452 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
1454 RETURN NEXT attr_set;
1463 $$ LANGUAGE PLPGSQL;
1466 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$
1472 FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP
1474 SELECT extract_marc_field('vandelay.queued_bib_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_bib_record WHERE id = NEW.id;
1475 IF (value IS NOT NULL AND value <> '') THEN
1476 INSERT INTO vandelay.queued_bib_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
1483 $$ LANGUAGE PLPGSQL;
1485 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
1488 item_data vandelay.import_item%ROWTYPE;
1491 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
1493 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
1494 INSERT INTO vandelay.import_item (
1518 item_data.definition,
1519 item_data.owning_lib,
1521 item_data.call_number,
1522 item_data.copy_number,
1525 item_data.circulate,
1527 item_data.deposit_amount,
1532 item_data.circ_modifier,
1533 item_data.circ_as_type,
1534 item_data.alert_message,
1536 item_data.priv_note,
1537 item_data.opac_visible
1543 $func$ LANGUAGE PLPGSQL;
1545 CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
1554 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
1556 SELECT * INTO attr_def FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1;
1558 IF attr_def IS NOT NULL AND attr_def.id IS NOT NULL THEN
1559 id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr_def.xpath, attr_def.remove);
1561 IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN
1562 SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted;
1563 SELECT * INTO attr FROM vandelay.queued_bib_record_attr WHERE record = NEW.id and field = attr_def.id LIMIT 1;
1564 IF exact_id IS NOT NULL THEN
1565 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id);
1570 IF exact_id IS NULL THEN
1571 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
1573 -- All numbers? check for an id match
1574 IF (attr.attr_value ~ $r$^\d+$$r$) THEN
1575 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP
1576 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1580 -- Looks like an ISBN? check for an isbn match
1581 IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN
1582 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
1583 PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE;
1585 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
1589 -- subcheck for isbn-as-tcn
1590 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP
1591 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1595 -- check for an OCLC tcn_value match
1596 IF (attr.attr_value ~ $r$^o\d+$$r$) THEN
1597 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP
1598 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1602 -- check for a direct tcn_value match
1603 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP
1604 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1607 -- check for a direct item barcode match
1610 FROM biblio.record_entry b
1611 JOIN asset.call_number cn ON (cn.record = b.id)
1612 JOIN asset.copy cp ON (cp.call_number = cn.id)
1613 WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE
1615 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1623 $func$ LANGUAGE PLPGSQL;
1625 CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$
1627 DELETE FROM vandelay.queued_bib_record_attr WHERE record = OLD.id;
1628 DELETE FROM vandelay.import_item WHERE record = OLD.id;
1630 IF TG_OP = 'UPDATE' THEN
1635 $$ LANGUAGE PLPGSQL;
1637 CREATE TRIGGER cleanup_bib_trigger
1638 BEFORE UPDATE OR DELETE ON vandelay.queued_bib_record
1639 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_bib_marc();
1641 CREATE TRIGGER ingest_bib_trigger
1642 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1643 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_marc();
1645 CREATE TRIGGER ingest_item_trigger
1646 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1647 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_items();
1649 CREATE TRIGGER zz_match_bibs_trigger
1650 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1651 FOR EACH ROW EXECUTE PROCEDURE vandelay.match_bib_record();
1654 /* Authority stuff down here */
1655 ---------------------------------------
1656 CREATE TABLE vandelay.authority_attr_definition (
1657 id SERIAL PRIMARY KEY,
1658 code TEXT UNIQUE NOT NULL,
1660 xpath TEXT NOT NULL,
1661 remove TEXT NOT NULL DEFAULT ''
1664 CREATE TABLE vandelay.authority_queue (
1665 queue_type TEXT NOT NULL DEFAULT 'authority' CHECK (queue_type = 'authority'),
1666 CONSTRAINT vand_authority_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
1667 ) INHERITS (vandelay.queue);
1668 ALTER TABLE vandelay.authority_queue ADD PRIMARY KEY (id);
1670 CREATE TABLE vandelay.queued_authority_record (
1671 queue INT NOT NULL REFERENCES vandelay.authority_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1672 imported_as INT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
1673 import_error TEXT REFERENCES vandelay.import_error (code) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1675 ) INHERITS (vandelay.queued_record);
1676 ALTER TABLE vandelay.queued_authority_record ADD PRIMARY KEY (id);
1677 CREATE INDEX queued_authority_record_queue_idx ON vandelay.queued_authority_record (queue);
1679 CREATE TABLE vandelay.queued_authority_record_attr (
1680 id BIGSERIAL PRIMARY KEY,
1681 record BIGINT NOT NULL REFERENCES vandelay.queued_authority_record (id) DEFERRABLE INITIALLY DEFERRED,
1682 field INT NOT NULL REFERENCES vandelay.authority_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
1683 attr_value TEXT NOT NULL
1685 CREATE INDEX queued_authority_record_attr_record_idx ON vandelay.queued_authority_record_attr (record);
1687 CREATE TABLE vandelay.authority_match (
1688 id BIGSERIAL PRIMARY KEY,
1689 matched_set INT REFERENCES vandelay.match_set (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1690 queued_record BIGINT REFERENCES vandelay.queued_authority_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1691 eg_record BIGINT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
1692 quality INT NOT NULL DEFAULT 0
1695 CREATE OR REPLACE FUNCTION vandelay.ingest_authority_marc ( ) RETURNS TRIGGER AS $$
1701 FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP
1703 SELECT extract_marc_field('vandelay.queued_authority_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_authority_record WHERE id = NEW.id;
1704 IF (value IS NOT NULL AND value <> '') THEN
1705 INSERT INTO vandelay.queued_authority_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
1712 $$ LANGUAGE PLPGSQL;
1714 CREATE OR REPLACE FUNCTION vandelay.cleanup_authority_marc ( ) RETURNS TRIGGER AS $$
1716 DELETE FROM vandelay.queued_authority_record_attr WHERE record = OLD.id;
1717 IF TG_OP = 'UPDATE' THEN
1722 $$ LANGUAGE PLPGSQL;
1724 CREATE TRIGGER cleanup_authority_trigger
1725 BEFORE UPDATE OR DELETE ON vandelay.queued_authority_record
1726 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_authority_marc();
1728 CREATE TRIGGER ingest_authority_trigger
1729 AFTER INSERT OR UPDATE ON vandelay.queued_authority_record
1730 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_authority_marc();
1732 CREATE OR REPLACE FUNCTION vandelay.overlay_authority_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1734 merge_profile vandelay.merge_profile%ROWTYPE;
1735 dyn_profile vandelay.compile_profile%ROWTYPE;
1744 SELECT b.marc INTO eg_marc
1745 FROM authority.record_entry b
1746 JOIN vandelay.authority_match m ON (m.eg_record = b.id AND m.queued_record = import_id)
1749 SELECT q.marc INTO v_marc
1750 FROM vandelay.queued_record q
1751 JOIN vandelay.authority_match m ON (m.queued_record = q.id AND q.id = import_id)
1754 IF eg_marc IS NULL OR v_marc IS NULL THEN
1755 -- RAISE NOTICE 'no marc for vandelay or authority record';
1759 dyn_profile := vandelay.compile_profile( v_marc );
1761 IF merge_profile_id IS NOT NULL THEN
1762 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
1764 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
1765 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
1766 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
1767 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
1771 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
1772 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
1776 IF dyn_profile.replace_rule <> '' THEN
1777 source_marc = v_marc;
1778 target_marc = eg_marc;
1779 replace_rule = dyn_profile.replace_rule;
1781 source_marc = eg_marc;
1782 target_marc = v_marc;
1783 replace_rule = dyn_profile.preserve_rule;
1786 UPDATE authority.record_entry
1787 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
1791 UPDATE vandelay.queued_authority_record
1792 SET imported_as = eg_id,
1794 WHERE id = import_id;
1798 -- RAISE NOTICE 'update of authority.record_entry failed';
1803 $$ LANGUAGE PLPGSQL;
1805 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1810 SELECT COUNT(*) INTO match_count FROM vandelay.authority_match WHERE queued_record = import_id;
1812 IF match_count <> 1 THEN
1813 -- RAISE NOTICE 'not an exact match';
1817 SELECT m.eg_record INTO eg_id
1818 FROM vandelay.authority_match m
1819 WHERE m.queued_record = import_id
1822 IF eg_id IS NULL THEN
1826 RETURN vandelay.overlay_authority_record( import_id, eg_id, merge_profile_id );
1828 $$ LANGUAGE PLPGSQL;
1830 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
1832 queued_record vandelay.queued_authority_record%ROWTYPE;
1835 FOR queued_record IN SELECT * FROM vandelay.queued_authority_record WHERE queue = queue_id AND import_time IS NULL LOOP
1837 IF vandelay.auto_overlay_authority_record( queued_record.id, merge_profile_id ) THEN
1838 RETURN NEXT queued_record.id;
1846 $$ LANGUAGE PLPGSQL;
1848 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
1849 SELECT * FROM vandelay.auto_overlay_authority_queue( $1, NULL );
1853 -- Vandelay (for importing and exporting records) 012.schema.vandelay.sql
1854 --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)]');
1855 --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)]');
1856 --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]');
1857 --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]');
1858 --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$);
1859 --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$);
1860 --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]');
1861 --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);
1862 --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);
1863 --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);
1864 --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);
1865 --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]');
1866 --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$);
1867 --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]');
1869 --INSERT INTO vandelay.import_item_attr_definition (
1870 -- owner, name, tag, owning_lib, circ_lib, location,
1871 -- call_number, circ_modifier, barcode, price, copy_number,
1872 -- circulate, ref, holdable, opac_visible, status
1875 -- 'Evergreen 852 export format',
1877 -- '[@code = "b"][1]',
1878 -- '[@code = "b"][2]',
1885 -- '[@code = "x" and text() = "circulating"]',
1886 -- '[@code = "x" and text() = "reference"]',
1887 -- '[@code = "x" and text() = "holdable"]',
1888 -- '[@code = "x" and text() = "visible"]',
1892 --INSERT INTO vandelay.import_item_attr_definition (
1905 -- 'Unicorn Import format -- 999',
1916 --INSERT INTO vandelay.authority_attr_definition ( code, description, xpath, ident ) VALUES ('rec_identifier','Identifier','//*[@tag="001"]', TRUE);