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;
460 SELECT b.marc INTO eg_marc
461 FROM biblio.record_entry b
462 JOIN vandelay.bib_match m ON (m.eg_record = b.id AND m.queued_record = import_id)
465 SELECT q.marc INTO v_marc
466 FROM vandelay.queued_record q
467 JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
470 IF eg_marc IS NULL OR v_marc IS NULL THEN
471 -- RAISE NOTICE 'no marc for vandelay or bib record';
475 dyn_profile := vandelay.compile_profile( v_marc );
477 IF merge_profile_id IS NOT NULL THEN
478 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
480 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
481 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
482 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
483 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
487 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
488 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
492 IF dyn_profile.replace_rule <> '' THEN
493 source_marc = v_marc;
494 target_marc = eg_marc;
495 replace_rule = dyn_profile.replace_rule;
497 source_marc = eg_marc;
498 target_marc = v_marc;
499 replace_rule = dyn_profile.preserve_rule;
502 UPDATE biblio.record_entry
503 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
507 UPDATE vandelay.queued_bib_record
508 SET imported_as = eg_id,
510 WHERE id = import_id;
512 editor_string := (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1];
514 IF editor_string IS NOT NULL AND editor_string <> '' THEN
515 SELECT usr INTO editor_id FROM actor.card WHERE barcode = editor_string;
517 IF editor_id IS NULL THEN
518 SELECT id INTO editor_id FROM actor.usr WHERE usrname = editor_string;
521 IF editor_id IS NOT NULL THEN
522 UPDATE biblio.record_entry SET editor = editor_id WHERE id = eg_id;
529 -- RAISE NOTICE 'update of biblio.record_entry failed';
536 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
540 match_attr vandelay.bib_attr_definition%ROWTYPE;
543 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
546 -- RAISE NOTICE 'already imported, cannot auto-overlay'
550 SELECT COUNT(*) INTO match_count FROM vandelay.bib_match WHERE queued_record = import_id;
552 IF match_count <> 1 THEN
553 -- RAISE NOTICE 'not an exact match';
557 SELECT d.* INTO match_attr
558 FROM vandelay.bib_attr_definition d
559 JOIN vandelay.queued_bib_record_attr a ON (a.field = d.id)
560 JOIN vandelay.bib_match m ON (m.matched_attr = a.id)
561 WHERE m.queued_record = import_id;
563 IF NOT (match_attr.xpath ~ '@tag="901"' AND match_attr.xpath ~ '@code="c"') THEN
564 -- RAISE NOTICE 'not a 901c match: %', match_attr.xpath;
568 SELECT m.eg_record INTO eg_id
569 FROM vandelay.bib_match m
570 WHERE m.queued_record = import_id
573 IF eg_id IS NULL THEN
577 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
581 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
583 queued_record vandelay.queued_bib_record%ROWTYPE;
587 FOR queued_record IN SELECT * FROM vandelay.queued_bib_record WHERE queue = queue_id AND import_time IS NULL LOOP
588 success := vandelay.auto_overlay_bib_record( queued_record.id, merge_profile_id );
591 RETURN NEXT queued_record.id;
601 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
602 SELECT * FROM vandelay.auto_overlay_bib_queue( $1, NULL );
605 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
630 attr_set vandelay.import_item%ROWTYPE;
636 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
640 attr_set.definition := attr_def.id;
642 -- Build the combined XPath
646 WHEN attr_def.owning_lib IS NULL THEN 'null()'
647 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
648 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
653 WHEN attr_def.circ_lib IS NULL THEN 'null()'
654 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
655 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
660 WHEN attr_def.call_number IS NULL THEN 'null()'
661 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
662 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
667 WHEN attr_def.copy_number IS NULL THEN 'null()'
668 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
669 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
674 WHEN attr_def.status IS NULL THEN 'null()'
675 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
676 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
681 WHEN attr_def.location IS NULL THEN 'null()'
682 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
683 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
688 WHEN attr_def.circulate IS NULL THEN 'null()'
689 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
690 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
695 WHEN attr_def.deposit IS NULL THEN 'null()'
696 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
697 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
702 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
703 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
704 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
709 WHEN attr_def.ref IS NULL THEN 'null()'
710 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
711 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
716 WHEN attr_def.holdable IS NULL THEN 'null()'
717 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
718 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
723 WHEN attr_def.price IS NULL THEN 'null()'
724 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
725 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
730 WHEN attr_def.barcode IS NULL THEN 'null()'
731 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
732 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
737 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
738 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
739 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
744 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
745 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
746 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
751 WHEN attr_def.alert_message IS NULL THEN 'null()'
752 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
753 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
758 WHEN attr_def.opac_visible IS NULL THEN 'null()'
759 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
760 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
765 WHEN attr_def.pub_note IS NULL THEN 'null()'
766 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
767 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
771 WHEN attr_def.priv_note IS NULL THEN 'null()'
772 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
773 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
780 call_number || '|' ||
781 copy_number || '|' ||
786 deposit_amount || '|' ||
791 circ_modifier || '|' ||
792 circ_as_type || '|' ||
793 alert_message || '|' ||
798 -- RAISE NOTICE 'XPath: %', xpath;
802 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
803 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
804 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
805 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, opac_vis TEXT )
808 tmp_attr_set.pr = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
809 tmp_attr_set.dep_amount = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
811 tmp_attr_set.pr := NULLIF( tmp_attr_set.pr, '' );
812 tmp_attr_set.dep_amount := NULLIF( tmp_attr_set.dep_amount, '' );
814 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
815 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
816 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
818 SELECT id INTO attr_set.location
819 FROM asset.copy_location
820 WHERE LOWER(name) = LOWER(tmp_attr_set.cl)
821 AND asset.copy_location.owning_lib = COALESCE(attr_set.owning_lib, attr_set.circ_lib); -- INT
823 attr_set.circulate :=
824 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
825 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
828 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
829 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
832 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
833 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
835 attr_set.opac_visible :=
836 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
837 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
840 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
841 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
843 attr_set.copy_number := tmp_attr_set.cnum::INT; -- INT,
844 attr_set.deposit_amount := tmp_attr_set.dep_amount::NUMERIC(6,2); -- NUMERIC(6,2),
845 attr_set.price := tmp_attr_set.pr::NUMERIC(8,2); -- NUMERIC(8,2),
847 attr_set.call_number := tmp_attr_set.cn; -- TEXT
848 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
849 attr_set.circ_modifier := tmp_attr_set.circ_mod; -- TEXT,
850 attr_set.circ_as_type := tmp_attr_set.circ_as; -- TEXT,
851 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
852 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
853 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
854 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
856 RETURN NEXT attr_set;
866 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$
872 FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP
874 SELECT extract_marc_field('vandelay.queued_bib_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_bib_record WHERE id = NEW.id;
875 IF (value IS NOT NULL AND value <> '') THEN
876 INSERT INTO vandelay.queued_bib_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
885 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
888 item_data vandelay.import_item%ROWTYPE;
891 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
893 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
894 INSERT INTO vandelay.import_item (
918 item_data.definition,
919 item_data.owning_lib,
921 item_data.call_number,
922 item_data.copy_number,
927 item_data.deposit_amount,
932 item_data.circ_modifier,
933 item_data.circ_as_type,
934 item_data.alert_message,
937 item_data.opac_visible
943 $func$ LANGUAGE PLPGSQL;
945 CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
954 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
956 SELECT * INTO attr_def FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1;
958 IF attr_def IS NOT NULL AND attr_def.id IS NOT NULL THEN
959 id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr_def.xpath, attr_def.remove);
961 IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN
962 SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted;
963 SELECT * INTO attr FROM vandelay.queued_bib_record_attr WHERE record = NEW.id and field = attr_def.id LIMIT 1;
964 IF exact_id IS NOT NULL THEN
965 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id);
970 IF exact_id IS NULL THEN
971 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
973 -- All numbers? check for an id match
974 IF (attr.attr_value ~ $r$^\d+$$r$) THEN
975 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP
976 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
980 -- Looks like an ISBN? check for an isbn match
981 IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN
982 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
983 PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE;
985 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
989 -- subcheck for isbn-as-tcn
990 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP
991 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
995 -- check for an OCLC tcn_value match
996 IF (attr.attr_value ~ $r$^o\d+$$r$) THEN
997 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP
998 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1002 -- check for a direct tcn_value match
1003 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP
1004 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1007 -- check for a direct item barcode match
1010 FROM biblio.record_entry b
1011 JOIN asset.call_number cn ON (cn.record = b.id)
1012 JOIN asset.copy cp ON (cp.call_number = cn.id)
1013 WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE
1015 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1023 $func$ LANGUAGE PLPGSQL;
1025 CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$
1027 DELETE FROM vandelay.queued_bib_record_attr WHERE record = OLD.id;
1028 DELETE FROM vandelay.import_item WHERE record = OLD.id;
1030 IF TG_OP = 'UPDATE' THEN
1035 $$ LANGUAGE PLPGSQL;
1037 CREATE TRIGGER cleanup_bib_trigger
1038 BEFORE UPDATE OR DELETE ON vandelay.queued_bib_record
1039 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_bib_marc();
1041 CREATE TRIGGER ingest_bib_trigger
1042 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1043 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_marc();
1045 CREATE TRIGGER ingest_item_trigger
1046 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1047 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_items();
1049 CREATE TRIGGER zz_match_bibs_trigger
1050 AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1051 FOR EACH ROW EXECUTE PROCEDURE vandelay.match_bib_record();
1054 /* Authority stuff down here */
1055 ---------------------------------------
1056 CREATE TABLE vandelay.authority_attr_definition (
1057 id SERIAL PRIMARY KEY,
1058 code TEXT UNIQUE NOT NULL,
1060 xpath TEXT NOT NULL,
1061 remove TEXT NOT NULL DEFAULT '',
1062 ident BOOL NOT NULL DEFAULT FALSE
1065 CREATE TABLE vandelay.authority_queue (
1066 queue_type TEXT NOT NULL DEFAULT 'authority' CHECK (queue_type = 'authority'),
1067 CONSTRAINT vand_authority_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
1068 ) INHERITS (vandelay.queue);
1069 ALTER TABLE vandelay.authority_queue ADD PRIMARY KEY (id);
1071 CREATE TABLE vandelay.queued_authority_record (
1072 queue INT NOT NULL REFERENCES vandelay.authority_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1073 imported_as INT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1074 ) INHERITS (vandelay.queued_record);
1075 ALTER TABLE vandelay.queued_authority_record ADD PRIMARY KEY (id);
1077 CREATE TABLE vandelay.queued_authority_record_attr (
1078 id BIGSERIAL PRIMARY KEY,
1079 record BIGINT NOT NULL REFERENCES vandelay.queued_authority_record (id) DEFERRABLE INITIALLY DEFERRED,
1080 field INT NOT NULL REFERENCES vandelay.authority_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
1081 attr_value TEXT NOT NULL
1084 CREATE TABLE vandelay.authority_match (
1085 id BIGSERIAL PRIMARY KEY,
1086 matched_attr INT REFERENCES vandelay.queued_authority_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1087 queued_record BIGINT REFERENCES vandelay.queued_authority_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1088 eg_record BIGINT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1091 CREATE OR REPLACE FUNCTION vandelay.ingest_authority_marc ( ) RETURNS TRIGGER AS $$
1097 FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP
1099 SELECT extract_marc_field('vandelay.queued_authority_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_authority_record WHERE id = NEW.id;
1100 IF (value IS NOT NULL AND value <> '') THEN
1101 INSERT INTO vandelay.queued_authority_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
1108 $$ LANGUAGE PLPGSQL;
1110 CREATE OR REPLACE FUNCTION vandelay.cleanup_authority_marc ( ) RETURNS TRIGGER AS $$
1112 DELETE FROM vandelay.queued_authority_record_attr WHERE record = OLD.id;
1113 IF TG_OP = 'UPDATE' THEN
1118 $$ LANGUAGE PLPGSQL;
1120 CREATE TRIGGER cleanup_authority_trigger
1121 BEFORE UPDATE OR DELETE ON vandelay.queued_authority_record
1122 FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_authority_marc();
1124 CREATE TRIGGER ingest_authority_trigger
1125 AFTER INSERT OR UPDATE ON vandelay.queued_authority_record
1126 FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_authority_marc();
1128 CREATE OR REPLACE FUNCTION vandelay.overlay_authority_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1130 merge_profile vandelay.merge_profile%ROWTYPE;
1131 dyn_profile vandelay.compile_profile%ROWTYPE;
1140 SELECT b.marc INTO eg_marc
1141 FROM authority.record_entry b
1142 JOIN vandelay.authority_match m ON (m.eg_record = b.id AND m.queued_record = import_id)
1145 SELECT q.marc INTO v_marc
1146 FROM vandelay.queued_record q
1147 JOIN vandelay.authority_match m ON (m.queued_record = q.id AND q.id = import_id)
1150 IF eg_marc IS NULL OR v_marc IS NULL THEN
1151 -- RAISE NOTICE 'no marc for vandelay or authority record';
1155 dyn_profile := vandelay.compile_profile( v_marc );
1157 IF merge_profile_id IS NOT NULL THEN
1158 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
1160 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
1161 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
1162 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
1163 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
1167 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
1168 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
1172 IF dyn_profile.replace_rule <> '' THEN
1173 source_marc = v_marc;
1174 target_marc = eg_marc;
1175 replace_rule = dyn_profile.replace_rule;
1177 source_marc = eg_marc;
1178 target_marc = v_marc;
1179 replace_rule = dyn_profile.preserve_rule;
1182 UPDATE authority.record_entry
1183 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
1187 UPDATE vandelay.queued_authority_record
1188 SET imported_as = eg_id,
1190 WHERE id = import_id;
1194 -- RAISE NOTICE 'update of authority.record_entry failed';
1199 $$ LANGUAGE PLPGSQL;
1201 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1206 SELECT COUNT(*) INTO match_count FROM vandelay.authority_match WHERE queued_record = import_id;
1208 IF match_count <> 1 THEN
1209 -- RAISE NOTICE 'not an exact match';
1213 SELECT m.eg_record INTO eg_id
1214 FROM vandelay.authority_match m
1215 WHERE m.queued_record = import_id
1218 IF eg_id IS NULL THEN
1222 RETURN vandelay.overlay_authority_record( import_id, eg_id, merge_profile_id );
1224 $$ LANGUAGE PLPGSQL;
1226 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
1228 queued_record vandelay.queued_authority_record%ROWTYPE;
1232 FOR queued_record IN SELECT * FROM vandelay.queued_authority_record WHERE queue = queue_id AND import_time IS NULL LOOP
1233 success := vandelay.auto_overlay_authority_record( queued_record.id, merge_profile_id );
1236 RETURN NEXT queued_record.id;
1244 $$ LANGUAGE PLPGSQL;
1246 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
1247 SELECT * FROM vandelay.auto_overlay_authority_queue( $1, NULL );
1251 -- Vandelay (for importing and exporting records) 012.schema.vandelay.sql
1252 --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)]');
1253 --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)]');
1254 --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]');
1255 --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]');
1256 --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$);
1257 --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$);
1258 --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]');
1259 --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);
1260 --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);
1261 --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);
1262 --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);
1263 --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]');
1264 --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$);
1265 --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]');
1267 --INSERT INTO vandelay.import_item_attr_definition (
1268 -- owner, name, tag, owning_lib, circ_lib, location,
1269 -- call_number, circ_modifier, barcode, price, copy_number,
1270 -- circulate, ref, holdable, opac_visible, status
1273 -- 'Evergreen 852 export format',
1275 -- '[@code = "b"][1]',
1276 -- '[@code = "b"][2]',
1283 -- '[@code = "x" and text() = "circulating"]',
1284 -- '[@code = "x" and text() = "reference"]',
1285 -- '[@code = "x" and text() = "holdable"]',
1286 -- '[@code = "x" and text() = "visible"]',
1290 --INSERT INTO vandelay.import_item_attr_definition (
1303 -- 'Unicorn Import format -- 999',
1314 --INSERT INTO vandelay.authority_attr_definition ( code, description, xpath, ident ) VALUES ('rec_identifier','Identifier','//*[@tag="001"]', TRUE);