1 DROP SCHEMA 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 INT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED
81 ) INHERITS (vandelay.queued_record);
82 ALTER TABLE vandelay.queued_bib_record ADD PRIMARY KEY (id);
84 CREATE TABLE vandelay.queued_bib_record_attr (
85 id BIGSERIAL PRIMARY KEY,
86 record BIGINT NOT NULL REFERENCES vandelay.queued_bib_record (id) DEFERRABLE INITIALLY DEFERRED,
87 field INT NOT NULL REFERENCES vandelay.bib_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
88 attr_value TEXT NOT NULL
91 CREATE TABLE vandelay.bib_match (
92 id BIGSERIAL PRIMARY KEY,
93 field_type TEXT NOT NULL CHECK (field_type in ('isbn','tcn_value','id')),
94 matched_attr INT REFERENCES vandelay.queued_bib_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
95 queued_record BIGINT REFERENCES vandelay.queued_bib_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
96 eg_record BIGINT REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
99 -- DROP TABLE vandelay.import_item CASCADE;
100 CREATE TABLE vandelay.import_item (
101 id BIGSERIAL PRIMARY KEY,
102 record BIGINT NOT NULL REFERENCES vandelay.queued_bib_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
103 definition BIGINT NOT NULL REFERENCES vandelay.import_item_attr_definition (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
112 deposit_amount NUMERIC(8,2),
125 CREATE TABLE vandelay.import_bib_trash_fields (
126 id BIGSERIAL PRIMARY KEY,
127 owner INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
129 CONSTRAINT vand_import_bib_trash_fields_idx UNIQUE (owner,field)
132 CREATE TABLE vandelay.merge_profile (
133 id BIGSERIAL PRIMARY KEY,
134 owner INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
140 CONSTRAINT vand_merge_prof_owner_name_idx UNIQUE (owner,name),
141 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))
145 CREATE TYPE vandelay.tcn_data AS (tcn TEXT, tcn_source TEXT, used BOOL);
146 CREATE OR REPLACE FUNCTION vandelay.find_bib_tcn_data ( xml TEXT ) RETURNS SETOF vandelay.tcn_data AS $_$
150 output vandelay.tcn_data%ROWTYPE;
154 eg_tcn := BTRIM((oils_xpath('//*[@tag="001"]/text()',xml))[1]);
155 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
157 eg_tcn_source := BTRIM((oils_xpath('//*[@tag="003"]/text()',xml))[1]);
158 IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN
159 eg_tcn_source := 'System Local';
162 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
165 output.used := FALSE;
170 output.tcn := eg_tcn;
171 output.tcn_source := eg_tcn_source;
177 eg_tcn := BTRIM((oils_xpath('//*[@tag="901"]/*[@code="a"]/text()',xml))[1]);
178 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
180 eg_tcn_source := BTRIM((oils_xpath('//*[@tag="901"]/*[@code="b"]/text()',xml))[1]);
181 IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN
182 eg_tcn_source := 'System Local';
185 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
188 output.used := FALSE;
193 output.tcn := eg_tcn;
194 output.tcn_source := eg_tcn_source;
200 eg_tcn := BTRIM((oils_xpath('//*[@tag="039"]/*[@code="a"]/text()',xml))[1]);
201 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
203 eg_tcn_source := BTRIM((oils_xpath('//*[@tag="039"]/*[@code="b"]/text()',xml))[1]);
204 IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN
205 eg_tcn_source := 'System Local';
208 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
211 output.used := FALSE;
216 output.tcn := eg_tcn;
217 output.tcn_source := eg_tcn_source;
223 eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="020"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$);
224 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
226 eg_tcn_source := 'ISBN';
228 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
231 output.used := FALSE;
236 output.tcn := eg_tcn;
237 output.tcn_source := eg_tcn_source;
243 eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="022"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$);
244 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
246 eg_tcn_source := 'ISSN';
248 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
251 output.used := FALSE;
256 output.tcn := eg_tcn;
257 output.tcn_source := eg_tcn_source;
263 eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="010"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$);
264 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
266 eg_tcn_source := 'LCCN';
268 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
271 output.used := FALSE;
276 output.tcn := eg_tcn;
277 output.tcn_source := eg_tcn_source;
283 eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="035"]/*[@code="a"]/text()',xml))[1], $re$^.*?(\w+)$$re$, $re$\1$re$);
284 IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
286 eg_tcn_source := 'System Legacy';
288 PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted;
291 output.used := FALSE;
296 output.tcn := eg_tcn;
297 output.tcn_source := eg_tcn_source;
304 $_$ LANGUAGE PLPGSQL;
306 CREATE OR REPLACE FUNCTION vandelay.add_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
311 my $target_xml = shift;
312 my $source_xml = shift;
313 my $field_spec = shift;
314 $field_spec =~ s/\s+//sg;
316 my $target_r = MARC::Record->new_from_xml( $target_xml );
317 my $source_r = MARC::Record->new_from_xml( $source_xml );
319 return $target_xml unless ($target_r && $source_r);
321 my @field_list = split(',', $field_spec);
324 for my $f (@field_list) {
325 if ($f =~ /^(.{3})(.*)$/) {
326 $fields{$1} = [ split('', $2) ];
330 for my $f ( keys %fields) {
331 if ( @{$fields{$f}} ) {
332 for my $from_field ($source_r->field( $f )) {
333 for my $to_field ($target_r->field( $f )) {
334 my @new_sf = map { ($_ => $from_field->subfield($_)) } @{$fields{$f}};
335 $to_field->add_subfields( @new_sf );
339 my @new_fields = map { $_->clone } $source_r->field( $f );
340 $target_r->insert_fields_ordered( @new_fields );
344 $target_xml = $target_r->as_xml_record;
345 $target_xml =~ s/^<\?.+?\?>$//mo;
346 $target_xml =~ s/\n//sgo;
347 $target_xml =~ s/>\s+</></sgo;
351 $_$ LANGUAGE PLPERLU;
353 CREATE OR REPLACE FUNCTION vandelay.strip_field ( xml TEXT, field TEXT ) RETURNS TEXT AS $_$
359 my $r = MARC::Record->new_from_xml( $xml );
361 return $xml unless ($r);
363 my $field_spec = shift;
364 $field_spec =~ s/\s+//sg;
366 my @field_list = split(',', $field_spec);
369 for my $f (@field_list) {
370 if ($f =~ /^(.{3})(.*)$/) {
371 $fields{$1} = [ split('', $2) ];
375 for my $f ( keys %fields) {
376 if ( @{$fields{$f}} ) {
377 $_->delete_subfield(code => $fields{$f}) for ($r->field( $f ));
379 $r->delete_field( $_ ) for ( $r->field( $f ) );
383 $xml = $r->as_xml_record;
384 $xml =~ s/^<\?.+?\?>$//mo;
386 $xml =~ s/>\s+</></sgo;
390 $_$ LANGUAGE PLPERLU;
392 CREATE OR REPLACE FUNCTION vandelay.replace_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
393 SELECT vandelay.add_field( vandelay.strip_field( $1, $3), $2, $3 );
396 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 $_$
397 SELECT vandelay.replace_field( vandelay.add_field( vandelay.strip_field( $1, $5) , $2, $3 ), $2, $4);
400 CREATE TYPE vandelay.compile_profile AS (add_rule TEXT, replace_rule TEXT, preserve_rule TEXT, strip_rule TEXT);
401 CREATE OR REPLACE FUNCTION vandelay.compile_profile ( incoming_xml TEXT ) RETURNS vandelay.compile_profile AS $_$
403 output vandelay.compile_profile%ROWTYPE;
404 profile vandelay.merge_profile%ROWTYPE;
406 profile_tmpl_owner TEXT;
408 strip_rule TEXT := '';
409 replace_rule TEXT := '';
410 preserve_rule TEXT := '';
414 profile_tmpl := (oils_xpath('//*[@tag="905"]/*[@code="t"]/text()',incoming_xml))[1];
415 profile_tmpl_owner := (oils_xpath('//*[@tag="905"]/*[@code="o"]/text()',incoming_xml))[1];
417 IF profile_tmpl IS NOT NULL AND profile_tmpl <> '' AND profile_tmpl_owner IS NOT NULL AND profile_tmpl_owner <> '' THEN
418 SELECT p.* INTO profile
419 FROM vandelay.merge_profile p
420 JOIN actor.org_unit u ON (u.id = p.owner)
421 WHERE p.name = profile_tmpl
422 AND u.shortname = profile_tmpl_owner;
424 IF profile.id IS NOT NULL THEN
425 add_rule := COALESCE(profile.add_spec,'');
426 strip_rule := COALESCE(profile.strip_spec,'');
427 replace_rule := COALESCE(profile.replace_spec,'');
428 preserve_rule := COALESCE(profile.preserve_spec,'');
432 add_rule := add_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="a"]/text()',incoming_xml),''),'');
433 strip_rule := strip_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="d"]/text()',incoming_xml),''),'');
434 replace_rule := replace_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="r"]/text()',incoming_xml),''),'');
435 preserve_rule := preserve_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="p"]/text()',incoming_xml),''),'');
437 output.add_rule := BTRIM(add_rule,',');
438 output.replace_rule := BTRIM(replace_rule,',');
439 output.strip_rule := BTRIM(strip_rule,',');
440 output.preserve_rule := BTRIM(preserve_rule,',');
444 $_$ LANGUAGE PLPGSQL;
446 CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
448 merge_profile vandelay.merge_profile%ROWTYPE;
449 dyn_profile vandelay.compile_profile%ROWTYPE;
458 SELECT b.marc INTO eg_marc
459 FROM biblio.record_entry b
460 JOIN vandelay.bib_match m ON (m.eg_record = b.id AND m.queued_record = import_id)
463 SELECT q.marc INTO v_marc
464 FROM vandelay.queued_record q
465 JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
468 IF eg_marc IS NULL OR v_marc IS NULL THEN
469 -- RAISE NOTICE 'no marc for vandelay or bib record';
473 dyn_profile := vandelay.compile_profile( v_marc );
475 IF merge_profile_id IS NOT NULL THEN
476 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
478 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
479 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
480 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
481 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
485 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
486 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
490 IF dyn_profile.replace_rule <> '' THEN
491 source_marc = v_marc;
492 target_marc = eg_marc;
493 replace_rule = dyn_profile.replace_rule;
495 source_marc = eg_marc;
496 target_marc = v_marc;
497 replace_rule = dyn_profile.preserve_rule;
500 UPDATE biblio.record_entry
501 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
505 UPDATE vandelay.queued_bib_record
506 SET imported_as = eg_id,
508 WHERE id = import_id;
512 -- RAISE NOTICE 'update of biblio.record_entry failed';
519 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
523 match_attr vandelay.bib_attr_definition%ROWTYPE;
526 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
529 -- RAISE NOTICE 'already imported, cannot auto-overlay'
533 SELECT COUNT(*) INTO match_count FROM vandelay.bib_match WHERE queued_record = import_id;
535 IF match_count <> 1 THEN
536 -- RAISE NOTICE 'not an exact match';
540 SELECT d.* INTO match_attr
541 FROM vandelay.bib_attr_definition d
542 JOIN vandelay.queued_bib_record_attr a ON (a.field = d.id)
543 JOIN vandelay.bib_match m ON (m.matched_attr = a.id)
544 WHERE m.queued_record = import_id;
546 IF NOT (match_attr.xpath ~ '@tag="901"' AND match_attr.xpath ~ '@code="c"') THEN
547 -- RAISE NOTICE 'not a 901c match: %', match_attr.xpath;
551 SELECT m.eg_record INTO eg_id
552 FROM vandelay.bib_match m
553 WHERE m.queued_record = import_id
556 IF eg_id IS NULL THEN
560 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
564 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
566 queued_record vandelay.queued_bib_record%ROWTYPE;
570 FOR queued_record IN SELECT * FROM vandelay.queued_bib_record WHERE queue = queue_id AND import_time IS NULL LOOP
571 success := vandelay.auto_overlay_bib_record( queued_record.id, merge_profile_id );
574 RETURN NEXT queued_record.id;
584 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
585 SELECT * FROM vandelay.auto_overlay_bib_queue( $1, NULL );
588 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
613 attr_set vandelay.import_item%ROWTYPE;
619 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
623 attr_set.definition := attr_def.id;
625 -- Build the combined XPath
629 WHEN attr_def.owning_lib IS NULL THEN 'null()'
630 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
631 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
636 WHEN attr_def.circ_lib IS NULL THEN 'null()'
637 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
638 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
643 WHEN attr_def.call_number IS NULL THEN 'null()'
644 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
645 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
650 WHEN attr_def.copy_number IS NULL THEN 'null()'
651 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
652 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
657 WHEN attr_def.status IS NULL THEN 'null()'
658 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
659 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
664 WHEN attr_def.location IS NULL THEN 'null()'
665 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
666 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
671 WHEN attr_def.circulate IS NULL THEN 'null()'
672 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
673 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
678 WHEN attr_def.deposit IS NULL THEN 'null()'
679 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
680 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
685 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
686 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
687 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
692 WHEN attr_def.ref IS NULL THEN 'null()'
693 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
694 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
699 WHEN attr_def.holdable IS NULL THEN 'null()'
700 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
701 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
706 WHEN attr_def.price IS NULL THEN 'null()'
707 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
708 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
713 WHEN attr_def.barcode IS NULL THEN 'null()'
714 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
715 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
720 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
721 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
722 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
727 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
728 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
729 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
734 WHEN attr_def.alert_message IS NULL THEN 'null()'
735 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
736 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
741 WHEN attr_def.opac_visible IS NULL THEN 'null()'
742 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
743 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
748 WHEN attr_def.pub_note IS NULL THEN 'null()'
749 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
750 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
754 WHEN attr_def.priv_note IS NULL THEN 'null()'
755 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
756 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
763 call_number || '|' ||
764 copy_number || '|' ||
769 deposit_amount || '|' ||
774 circ_modifier || '|' ||
775 circ_as_type || '|' ||
776 alert_message || '|' ||
781 -- RAISE NOTICE 'XPath: %', xpath;
785 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
786 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
787 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
788 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, opac_vis TEXT )
791 tmp_attr_set.pr = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
792 tmp_attr_set.dep_amount = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
794 tmp_attr_set.pr := NULLIF( tmp_attr_set.pr, '' );
795 tmp_attr_set.dep_amount := NULLIF( tmp_attr_set.dep_amount, '' );
797 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
798 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
799 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
801 SELECT id INTO attr_set.location
802 FROM asset.copy_location
803 WHERE LOWER(name) = LOWER(tmp_attr_set.cl)
804 AND asset.copy_location.owning_lib = COALESCE(attr_set.owning_lib, attr_set.circ_lib); -- INT
806 attr_set.circulate :=
807 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
808 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
811 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
812 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
815 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
816 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
818 attr_set.opac_visible :=
819 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
820 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
823 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
824 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
826 attr_set.copy_number := tmp_attr_set.cnum::INT; -- INT,
827 attr_set.deposit_amount := tmp_attr_set.dep_amount::NUMERIC(6,2); -- NUMERIC(6,2),
828 attr_set.price := tmp_attr_set.pr::NUMERIC(8,2); -- NUMERIC(8,2),
830 attr_set.call_number := tmp_attr_set.cn; -- TEXT
831 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
832 attr_set.circ_modifier := tmp_attr_set.circ_mod; -- TEXT,
833 attr_set.circ_as_type := tmp_attr_set.circ_as; -- TEXT,
834 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
835 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
836 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
837 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
839 RETURN NEXT attr_set;
849 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$
855 FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP
857 SELECT extract_marc_field('vandelay.queued_bib_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_bib_record WHERE id = NEW.id;
858 IF (value IS NOT NULL AND value <> '') THEN
859 INSERT INTO vandelay.queued_bib_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
868 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
871 item_data vandelay.import_item%ROWTYPE;
874 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
876 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
877 INSERT INTO vandelay.import_item (
901 item_data.definition,
902 item_data.owning_lib,
904 item_data.call_number,
905 item_data.copy_number,
910 item_data.deposit_amount,
915 item_data.circ_modifier,
916 item_data.circ_as_type,
917 item_data.alert_message,
920 item_data.opac_visible
926 $func$ LANGUAGE PLPGSQL;
928 CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
937 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
939 SELECT * INTO attr_def FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1;
941 IF attr_def IS NOT NULL AND attr_def.id IS NOT NULL THEN
942 id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr_def.xpath, attr_def.remove);
944 IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN
945 SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted;
946 SELECT * INTO attr FROM vandelay.queued_bib_record_attr WHERE record = NEW.id and field = attr_def.id LIMIT 1;
947 IF exact_id IS NOT NULL THEN
948 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id);
953 IF exact_id IS NULL THEN
954 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
956 -- All numbers? check for an id match
957 IF (attr.attr_value ~ $r$^\d+$$r$) THEN
958 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP
959 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
963 -- Looks like an ISBN? check for an isbn match
964 IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN
965 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
966 PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE;
968 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
972 -- subcheck for isbn-as-tcn
973 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP
974 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
978 -- check for an OCLC tcn_value match
979 IF (attr.attr_value ~ $r$^o\d+$$r$) THEN
980 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP
981 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
985 -- check for a direct tcn_value match
986 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP
987 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
990 -- check for a direct item barcode match
993 FROM biblio.record_entry b
994 JOIN asset.call_number cn ON (cn.record = b.id)
995 JOIN asset.copy cp ON (cp.call_number = cn.id)
996 WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE
998 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1006 $func$ LANGUAGE PLPGSQL;
1008 CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$
1010 DELETE FROM vandelay.queued_bib_record_attr WHERE record = OLD.id;
1011 DELETE FROM vandelay.import_item WHERE record = OLD.id;
1013 IF TG_OP = 'UPDATE' THEN
1018 $$ LANGUAGE PLPGSQL;
1020 CREATE TRIGGER cleanup_bib_trigger
1021 BEFORE UPDATE OR DELETE ON vandelay.queued_bib_record
1022 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_bib_marc();
1024 CREATE TRIGGER ingest_bib_trigger
1025 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1026 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_marc();
1028 CREATE TRIGGER ingest_item_trigger
1029 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1030 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_items();
1032 CREATE TRIGGER zz_match_bibs_trigger
1033 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1034 FOR EACH ROW EXECUTE PROCEDURE vandelay.match_bib_record();
1037 /* Authority stuff down here */
1038 ---------------------------------------
1039 CREATE TABLE vandelay.authority_attr_definition (
1040 id SERIAL PRIMARY KEY,
1041 code TEXT UNIQUE NOT NULL,
1043 xpath TEXT NOT NULL,
1044 remove TEXT NOT NULL DEFAULT '',
1045 ident BOOL NOT NULL DEFAULT FALSE
1048 CREATE TABLE vandelay.authority_queue (
1049 queue_type TEXT NOT NULL DEFAULT 'authority' CHECK (queue_type = 'authority'),
1050 CONSTRAINT vand_authority_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
1051 ) INHERITS (vandelay.queue);
1052 ALTER TABLE vandelay.authority_queue ADD PRIMARY KEY (id);
1054 CREATE TABLE vandelay.queued_authority_record (
1055 queue INT NOT NULL REFERENCES vandelay.authority_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1056 imported_as INT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1057 ) INHERITS (vandelay.queued_record);
1058 ALTER TABLE vandelay.queued_authority_record ADD PRIMARY KEY (id);
1060 CREATE TABLE vandelay.queued_authority_record_attr (
1061 id BIGSERIAL PRIMARY KEY,
1062 record BIGINT NOT NULL REFERENCES vandelay.queued_authority_record (id) DEFERRABLE INITIALLY DEFERRED,
1063 field INT NOT NULL REFERENCES vandelay.authority_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
1064 attr_value TEXT NOT NULL
1067 CREATE TABLE vandelay.authority_match (
1068 id BIGSERIAL PRIMARY KEY,
1069 matched_attr INT REFERENCES vandelay.queued_authority_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1070 queued_record BIGINT REFERENCES vandelay.queued_authority_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1071 eg_record BIGINT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1074 CREATE OR REPLACE FUNCTION vandelay.ingest_authority_marc ( ) RETURNS TRIGGER AS $$
1080 FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP
1082 SELECT extract_marc_field('vandelay.queued_authority_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_authority_record WHERE id = NEW.id;
1083 IF (value IS NOT NULL AND value <> '') THEN
1084 INSERT INTO vandelay.queued_authority_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
1091 $$ LANGUAGE PLPGSQL;
1093 CREATE OR REPLACE FUNCTION vandelay.cleanup_authority_marc ( ) RETURNS TRIGGER AS $$
1095 DELETE FROM vandelay.queued_authority_record_attr WHERE record = OLD.id;
1096 IF TG_OP = 'UPDATE' THEN
1101 $$ LANGUAGE PLPGSQL;
1103 CREATE TRIGGER cleanup_authority_trigger
1104 BEFORE UPDATE OR DELETE ON vandelay.queued_authority_record
1105 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_authority_marc();
1107 CREATE TRIGGER ingest_authority_trigger
1108 AFTER INSERT OR UPDATE ON vandelay.queued_authority_record
1109 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_authority_marc();
1111 CREATE OR REPLACE FUNCTION vandelay.overlay_authority_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1113 merge_profile vandelay.merge_profile%ROWTYPE;
1114 dyn_profile vandelay.compile_profile%ROWTYPE;
1123 SELECT b.marc INTO eg_marc
1124 FROM authority.record_entry b
1125 JOIN vandelay.authority_match m ON (m.eg_record = b.id AND m.queued_record = import_id)
1128 SELECT q.marc INTO v_marc
1129 FROM vandelay.queued_record q
1130 JOIN vandelay.authority_match m ON (m.queued_record = q.id AND q.id = import_id)
1133 IF eg_marc IS NULL OR v_marc IS NULL THEN
1134 -- RAISE NOTICE 'no marc for vandelay or authority record';
1138 dyn_profile := vandelay.compile_profile( v_marc );
1140 IF merge_profile_id IS NOT NULL THEN
1141 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
1143 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
1144 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
1145 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
1146 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
1150 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
1151 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
1155 IF dyn_profile.replace_rule <> '' THEN
1156 source_marc = v_marc;
1157 target_marc = eg_marc;
1158 replace_rule = dyn_profile.replace_rule;
1160 source_marc = eg_marc;
1161 target_marc = v_marc;
1162 replace_rule = dyn_profile.preserve_rule;
1165 UPDATE authority.record_entry
1166 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
1170 UPDATE vandelay.queued_authority_record
1171 SET imported_as = eg_id,
1173 WHERE id = import_id;
1177 -- RAISE NOTICE 'update of authority.record_entry failed';
1182 $$ LANGUAGE PLPGSQL;
1184 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1189 SELECT COUNT(*) INTO match_count FROM vandelay.authority_match WHERE queued_record = import_id;
1191 IF match_count <> 1 THEN
1192 -- RAISE NOTICE 'not an exact match';
1196 SELECT m.eg_record INTO eg_id
1197 FROM vandelay.authority_match m
1198 WHERE m.queued_record = import_id
1201 IF eg_id IS NULL THEN
1205 RETURN vandelay.overlay_authority_record( import_id, eg_id, merge_profile_id );
1207 $$ LANGUAGE PLPGSQL;
1209 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
1211 queued_record vandelay.queued_authority_record%ROWTYPE;
1215 FOR queued_record IN SELECT * FROM vandelay.queued_authority_record WHERE queue = queue_id AND import_time IS NULL LOOP
1216 success := vandelay.auto_overlay_authority_record( queued_record.id, merge_profile_id );
1219 RETURN NEXT queued_record.id;
1227 $$ LANGUAGE PLPGSQL;
1229 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
1230 SELECT * FROM vandelay.auto_overlay_authority_queue( $1, NULL );
1234 -- Vandelay (for importing and exporting records) 012.schema.vandelay.sql
1235 --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)]');
1236 --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)]');
1237 --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]');
1238 --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]');
1239 --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$);
1240 --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$);
1241 --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]');
1242 --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);
1243 --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);
1244 --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);
1245 --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);
1246 --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]');
1247 --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$);
1248 --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]');
1250 --INSERT INTO vandelay.import_item_attr_definition (
1251 -- owner, name, tag, owning_lib, circ_lib, location,
1252 -- call_number, circ_modifier, barcode, price, copy_number,
1253 -- circulate, ref, holdable, opac_visible, status
1256 -- 'Evergreen 852 export format',
1258 -- '[@code = "b"][1]',
1259 -- '[@code = "b"][2]',
1266 -- '[@code = "x" and text() = "circulating"]',
1267 -- '[@code = "x" and text() = "reference"]',
1268 -- '[@code = "x" and text() = "holdable"]',
1269 -- '[@code = "x" and text() = "visible"]',
1273 --INSERT INTO vandelay.import_item_attr_definition (
1286 -- 'Unicorn Import format -- 999',
1297 --INSERT INTO vandelay.authority_attr_definition ( code, description, xpath, ident ) VALUES ('rec_identifier','Identifier','//*[@tag="001"]', TRUE);