1 -- Evergreen DB patch XXXX.vandelay-record-matching-and-quality.sql
3 -- FIXME: insert description of change, if needed
8 -- check whether patch can be applied
9 --SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
11 CREATE OR REPLACE FUNCTION evergreen.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;
13 CREATE TABLE vandelay.match_set (
14 id SERIAL PRIMARY KEY,
16 owner INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE,
17 mtype TEXT NOT NULL DEFAULT 'biblio', -- 'biblio','authority','mfhd'?, others?
18 CONSTRAINT name_once_per_owner_mtype UNIQUE (name, owner, mtype)
21 -- Table to define match points, either FF via SVF or tag+subfield
22 CREATE TABLE vandelay.match_set_point (
23 id SERIAL PRIMARY KEY,
24 match_set INT REFERENCES vandelay.match_set (id) ON DELETE CASCADE,
25 parent INT REFERENCES vandelay.match_set_point (id),
26 bool_op TEXT CHECK (bool_op IS NULL OR (bool_op IN ('AND','OR','NOT'))),
27 svf TEXT REFERENCES config.record_attr_definition (name),
30 negate BOOL DEFAULT FALSE,
31 quality INT NOT NULL DEFAULT 1, -- higher is better
32 CONSTRAINT vmsp_need_a_subfield_with_a_tag CHECK ((tag IS NOT NULL AND subfield IS NOT NULL) OR tag IS NULL),
33 CONSTRAINT vmsp_need_a_tag_or_a_ff_or_a_bo CHECK (
34 (tag IS NOT NULL AND svf IS NULL AND bool_op IS NULL) OR
35 (tag IS NULL AND svf IS NOT NULL AND bool_op IS NULL) OR
36 (tag IS NULL AND svf IS NULL AND bool_op IS NOT NULL)
40 CREATE TABLE vandelay.match_set_quality (
41 id SERIAL PRIMARY KEY,
42 match_set INT NOT NULL REFERENCES vandelay.match_set (id) ON DELETE CASCADE,
43 svf TEXT REFERENCES config.record_attr_definition,
47 quality INT NOT NULL DEFAULT 1, -- higher is better
48 CONSTRAINT vmsq_need_a_subfield_with_a_tag CHECK ((tag IS NOT NULL AND subfield IS NOT NULL) OR tag IS NULL),
49 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))
51 CREATE UNIQUE INDEX vmsq_def_once_per_set ON vandelay.match_set_quality (match_set, COALESCE(tag,''), COALESCE(subfield,''), COALESCE(svf,''), value);
55 ALTER TABLE vandelay.queue ADD COLUMN match_set INT REFERENCES vandelay.match_set (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED;
56 ALTER TABLE vandelay.queued_record ADD COLUMN quality INT NOT NULL DEFAULT 0;
57 ALTER TABLE vandelay.bib_attr_definition DROP COLUMN ident;
59 CREATE TABLE vandelay.import_error (
60 code TEXT PRIMARY KEY,
61 description TEXT NOT NULL -- i18n
64 ALTER TABLE vandelay.queued_bib_record
65 ADD COLUMN import_error TEXT REFERENCES vandelay.import_error (code) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
66 ADD COLUMN error_detail TEXT;
68 ALTER TABLE vandelay.bib_match
69 DROP COLUMN field_type,
70 DROP COLUMN matched_attr,
71 ADD COLUMN quality INT NOT NULL DEFAULT 1,
72 ADD COLUMN match_score INT NOT NULL DEFAULT 0;
74 ALTER TABLE vandelay.import_item
75 ADD COLUMN import_error TEXT REFERENCES vandelay.import_error (code) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
76 ADD COLUMN error_detail TEXT,
77 ADD COLUMN imported_as BIGINT REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED,
78 ADD COLUMN import_time TIMESTAMP WITH TIME ZONE;
80 ALTER TABLE vandelay.merge_profile ADD COLUMN lwm_ratio NUMERIC;
82 CREATE OR REPLACE FUNCTION vandelay.marc21_record_type( marc TEXT ) RETURNS config.marc21_rec_type_map AS $func$
89 retval config.marc21_rec_type_map%ROWTYPE;
91 ldr := oils_xpath_string( '//*[local-name()="leader"]', marc );
93 IF ldr IS NULL OR ldr = '' THEN
94 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE code = 'BKS';
98 SELECT * INTO tval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'Type' LIMIT 1; -- They're all the same
99 SELECT * INTO bval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'BLvl' LIMIT 1; -- They're all the same
102 tval := SUBSTRING( ldr, tval_rec.start_pos + 1, tval_rec.length );
103 bval := SUBSTRING( ldr, bval_rec.start_pos + 1, bval_rec.length );
105 -- RAISE NOTICE 'type %, blvl %, ldr %', tval, bval, ldr;
107 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE type_val LIKE '%' || tval || '%' AND blvl_val LIKE '%' || bval || '%';
110 IF retval.code IS NULL THEN
111 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE code = 'BKS';
116 $func$ LANGUAGE PLPGSQL;
118 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field( marc TEXT, ff TEXT ) RETURNS TEXT AS $func$
125 rtype := (vandelay.marc21_record_type( marc )).code;
126 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = ff AND rec_type = rtype ORDER BY tag DESC LOOP
127 IF ff_pos.tag = 'ldr' THEN
128 val := oils_xpath_string('//*[local-name()="leader"]', marc);
129 IF val IS NOT NULL THEN
130 val := SUBSTRING( val, ff_pos.start_pos + 1, ff_pos.length );
134 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
135 val := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length );
139 val := REPEAT( ff_pos.default_val, ff_pos.length );
145 $func$ LANGUAGE PLPGSQL;
147 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_all_fixed_fields( marc TEXT ) RETURNS SETOF biblio.record_ff_map AS $func$
152 output biblio.record_ff_map%ROWTYPE;
154 rtype := (vandelay.marc21_record_type( marc )).code;
156 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE rec_type = rtype ORDER BY tag DESC LOOP
157 output.ff_name := ff_pos.fixed_field;
158 output.ff_value := NULL;
160 IF ff_pos.tag = 'ldr' THEN
161 output.ff_value := oils_xpath_string('//*[local-name()="leader"]', marc);
162 IF output.ff_value IS NOT NULL THEN
163 output.ff_value := SUBSTRING( output.ff_value, ff_pos.start_pos + 1, ff_pos.length );
165 output.ff_value := NULL;
168 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
169 output.ff_value := SUBSTRING( tag_data, ff_pos.start_pos + 1, ff_pos.length );
170 IF output.ff_value IS NULL THEN output.ff_value := REPEAT( ff_pos.default_val, ff_pos.length ); END IF;
172 output.ff_value := NULL;
180 $func$ LANGUAGE PLPGSQL;
182 CREATE OR REPLACE FUNCTION vandelay.marc21_physical_characteristics( marc TEXT) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$
186 ptype config.marc21_physical_characteristic_type_map%ROWTYPE;
187 psf config.marc21_physical_characteristic_subfield_map%ROWTYPE;
188 pval config.marc21_physical_characteristic_value_map%ROWTYPE;
189 retval biblio.marc21_physical_characteristics%ROWTYPE;
192 _007 := oils_xpath_string( '//*[@tag="007"]', marc );
194 IF _007 IS NOT NULL AND _007 <> '' THEN
195 SELECT * INTO ptype FROM config.marc21_physical_characteristic_type_map WHERE ptype_key = SUBSTRING( _007, 1, 1 );
197 IF ptype.ptype_key IS NOT NULL THEN
198 FOR psf IN SELECT * FROM config.marc21_physical_characteristic_subfield_map WHERE ptype_key = ptype.ptype_key LOOP
199 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 );
201 IF pval.id IS NOT NULL THEN
204 retval.ptype := ptype.ptype_key;
205 retval.subfield := psf.id;
206 retval.value := pval.id;
216 $func$ LANGUAGE PLPGSQL;
218 CREATE TYPE vandelay.flat_marc AS ( tag CHAR(3), ind1 TEXT, ind2 TEXT, subfield TEXT, value TEXT );
219 CREATE OR REPLACE FUNCTION vandelay.flay_marc ( TEXT ) RETURNS SETOF vandelay.flat_marc AS $func$
222 use MARC::File::XML (BinaryEncoding => 'UTF-8');
226 MARC::Charset->assume_unicode(1);
229 my $r = MARC::Record->new_from_xml( $xml );
231 return_next( { tag => 'LDR', value => $r->leader } );
233 for my $f ( $r->fields ) {
234 if ($f->is_control_field) {
235 return_next({ tag => $f->tag, value => $f->data });
237 for my $s ($f->subfields) {
240 ind1 => $f->indicator(1),
241 ind2 => $f->indicator(2),
246 if ( $f->tag eq '245' and $s->[0] eq 'a' ) {
247 my $trim = $f->indicator(2) || 0;
250 ind1 => $f->indicator(1),
251 ind2 => $f->indicator(2),
253 value => substr( $s->[1], $trim )
262 $func$ LANGUAGE PLPERLU;
264 CREATE OR REPLACE FUNCTION vandelay.flatten_marc ( marc TEXT ) RETURNS SETOF vandelay.flat_marc AS $func$
266 output vandelay.flat_marc%ROWTYPE;
269 FOR field IN SELECT * FROM vandelay.flay_marc( marc ) LOOP
270 output.ind1 := field.ind1;
271 output.ind2 := field.ind2;
272 output.tag := field.tag;
273 output.subfield := field.subfield;
274 IF field.subfield IS NOT NULL AND field.tag NOT IN ('020','022','024') THEN -- exclude standard numbers and control fields
275 output.value := naco_normalize(field.value, field.subfield);
277 output.value := field.value;
280 CONTINUE WHEN output.value IS NULL;
285 $func$ LANGUAGE PLPGSQL;
287 CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT, attr_defs TEXT[]) RETURNS hstore AS $_$
289 transformed_xml TEXT;
292 xfrm config.xml_transform%ROWTYPE;
294 new_attrs HSTORE := ''::HSTORE;
295 attr_def config.record_attr_definition%ROWTYPE;
298 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE name IN (SELECT * FROM UNNEST(attr_defs)) ORDER BY format LOOP
300 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
301 SELECT ARRAY_TO_STRING(ARRAY_ACCUM(x.value), COALESCE(attr_def.joiner,' ')) INTO attr_value
302 FROM vandelay.flatten_marc(xml) AS x
303 WHERE x.tag LIKE attr_def.tag
305 WHEN attr_def.sf_list IS NOT NULL
306 THEN POSITION(x.subfield IN attr_def.sf_list) > 0
313 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
314 attr_value := vandelay.marc21_extract_fixed_field(xml, attr_def.fixed_field);
316 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
318 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
320 -- See if we can skip the XSLT ... it's expensive
321 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
322 -- Can't skip the transform
323 IF xfrm.xslt <> '---' THEN
324 transformed_xml := oils_xslt_process(xml,xfrm.xslt);
326 transformed_xml := xml;
329 prev_xfrm := xfrm.name;
332 IF xfrm.name IS NULL THEN
333 -- just grab the marcxml (empty) transform
334 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
335 prev_xfrm := xfrm.name;
338 attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]);
340 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
341 SELECT m.value::TEXT INTO attr_value
342 FROM vandelay.marc21_physical_characteristics(xml) v
343 JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
344 WHERE v.subfield = attr_def.phys_char_sf
345 LIMIT 1; -- Just in case ...
349 -- apply index normalizers to attr_value
351 SELECT n.func AS func,
352 n.param_count AS param_count,
354 FROM config.index_normalizer n
355 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
356 WHERE attr = attr_def.name
358 EXECUTE 'SELECT ' || normalizer.func || '(' ||
359 quote_literal( attr_value ) ||
361 WHEN normalizer.param_count > 0
362 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
369 -- Add the new value to the hstore
370 new_attrs := new_attrs || hstore( attr_def.name, attr_value );
376 $_$ LANGUAGE PLPGSQL;
378 CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT ) RETURNS hstore AS $_$
379 SELECT vandelay.extract_rec_attrs( $1, (SELECT ARRAY_ACCUM(name) FROM config.record_attr_definition));
382 -- Everything between this comment and the beginning of the definition of
383 -- vandelay.match_bib_record() is strictly in service of that function.
384 CREATE TYPE vandelay.match_set_test_result AS (record BIGINT, quality INTEGER);
386 CREATE OR REPLACE FUNCTION vandelay.match_set_test_marcxml(
387 match_set_id INTEGER, record_xml TEXT
388 ) RETURNS SETOF vandelay.match_set_test_result AS $$
399 tags_rstore := vandelay.flatten_marc_hstore(record_xml);
400 svf_rstore := vandelay.extract_rec_attrs(record_xml);
402 CREATE TEMPORARY TABLE _vandelay_tmp_qrows (q INTEGER);
403 CREATE TEMPORARY TABLE _vandelay_tmp_jrows (j TEXT);
405 -- generate the where clause and return that directly (into wq), and as
406 -- a side-effect, populate the _vandelay_tmp_[qj]rows tables.
407 wq := vandelay.get_expr_from_match_set(match_set_id);
409 query_ := 'SELECT bre.id AS record, ';
411 -- qrows table is for the quality bits we add to the SELECT clause
412 SELECT ARRAY_TO_STRING(
413 ARRAY_ACCUM('COALESCE(n' || q::TEXT || '.quality, 0)'), ' + '
414 ) INTO coal FROM _vandelay_tmp_qrows;
416 -- our query string so far is the SELECT clause and the inital FROM.
417 -- no JOINs yet nor the WHERE clause
418 query_ := query_ || coal || ' AS quality ' || E'\n' ||
419 'FROM biblio.record_entry bre ';
421 -- jrows table is for the joins we must make (and the real text conditions)
422 SELECT ARRAY_TO_STRING(ARRAY_ACCUM(j), E'\n') INTO joins
423 FROM _vandelay_tmp_jrows;
425 -- add those joins and the where clause to our query.
426 query_ := query_ || joins || E'\n' || 'WHERE ' || wq || ' AND not bre.deleted';
428 -- this will return rows of record,quality
429 FOR rec IN EXECUTE query_ USING tags_rstore, svf_rstore LOOP
433 DROP TABLE _vandelay_tmp_qrows;
434 DROP TABLE _vandelay_tmp_jrows;
440 CREATE OR REPLACE FUNCTION vandelay.flatten_marc_hstore(
442 ) RETURNS HSTORE AS $$
446 ARRAY_ACCUM(tag || (COALESCE(subfield, ''))),
450 SELECT tag, subfield, ARRAY_ACCUM(value)::TEXT AS value
451 FROM vandelay.flatten_marc(record_xml)
452 GROUP BY tag, subfield ORDER BY tag, subfield
458 CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set(
462 root vandelay.match_set_point;
464 SELECT * INTO root FROM vandelay.match_set_point
465 WHERE parent IS NULL AND match_set = match_set_id;
467 RETURN vandelay.get_expr_from_match_set_point(root);
471 CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set_point(
472 node vandelay.match_set_point
479 child vandelay.match_set_point;
481 SELECT ARRAY_ACCUM(id) INTO children FROM vandelay.match_set_point
482 WHERE parent = node.id;
484 IF ARRAY_LENGTH(children, 1) > 0 THEN
485 this_op := vandelay._get_expr_render_one(node);
488 WHILE children[i] IS NOT NULL LOOP
489 SELECT * INTO child FROM vandelay.match_set_point
490 WHERE id = children[i];
492 q := q || ' ' || this_op || ' ';
495 q := q || vandelay.get_expr_from_match_set_point(child);
499 ELSIF node.bool_op IS NULL THEN
500 PERFORM vandelay._get_expr_push_qrow(node);
501 PERFORM vandelay._get_expr_push_jrow(node);
502 RETURN vandelay._get_expr_render_one(node);
509 CREATE OR REPLACE FUNCTION vandelay._get_expr_push_qrow(
510 node vandelay.match_set_point
514 INSERT INTO _vandelay_tmp_qrows (q) VALUES (node.id);
518 CREATE OR REPLACE FUNCTION vandelay._get_expr_push_jrow(
519 node vandelay.match_set_point
533 IF node.tag IS NOT NULL THEN
535 IF node.subfield IS NOT NULL THEN
536 tagkey := tagkey || node.subfield;
540 my_alias := 'n' || node.id::TEXT;
542 jrow := 'LEFT JOIN (SELECT *, ' || node.quality ||
543 ' AS quality FROM metabib.';
544 IF node.tag IS NOT NULL THEN
545 jrow := jrow || 'full_rec) ' || my_alias || ' ON (' ||
546 my_alias || '.record = bre.id AND ' || my_alias || '.tag = ''' ||
548 IF node.subfield IS NOT NULL THEN
549 jrow := jrow || ' AND ' || my_alias || '.subfield = ''' ||
550 node.subfield || '''';
552 jrow := jrow || ' AND (' || my_alias || '.value ' || op ||
553 ' ANY(($1->''' || tagkey || ''')::TEXT[])))';
555 jrow := jrow || 'record_attr) ' || my_alias || ' ON (' ||
556 my_alias || '.id = bre.id AND (' ||
557 my_alias || '.attrs->''' || node.svf ||
558 ''' ' || op || ' $2->''' || node.svf || '''))';
560 INSERT INTO _vandelay_tmp_jrows (j) VALUES (jrow);
564 CREATE OR REPLACE FUNCTION vandelay._get_expr_render_one(
565 node vandelay.match_set_point
570 IF node.bool_op IS NOT NULL THEN
573 RETURN '(n' || node.id::TEXT || '.id IS NOT NULL)';
578 CREATE OR REPLACE FUNCTION vandelay.match_bib_record() RETURNS TRIGGER AS $func$
580 incoming_existing_id TEXT;
581 test_result vandelay.match_set_test_result%ROWTYPE;
585 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
589 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
591 SELECT q.match_set INTO match_set FROM vandelay.bib_queue q WHERE q.id = NEW.queue;
593 IF match_set IS NOT NULL THEN
594 NEW.quality := vandelay.measure_record_quality( NEW.marc, match_set );
597 -- Perfect matches on 901$c exit early with a match with high quality.
598 incoming_existing_id :=
599 oils_xpath_string('//*[@tag="901"]/*[@code="c"][1]', NEW.marc);
601 IF incoming_existing_id IS NOT NULL AND incoming_existing_id != '' THEN
602 SELECT id INTO tmp_rec FROM biblio.record_entry WHERE id = incoming_existing_id::bigint;
603 IF tmp_rec IS NOT NULL THEN
604 INSERT INTO vandelay.bib_match (queued_record, eg_record, match_score, quality)
609 -- note: no match_set means quality==0
610 vandelay.measure_record_quality( b.marc, match_set )
611 FROM biblio.record_entry b
612 WHERE id = incoming_existing_id::bigint;
616 IF match_set IS NULL THEN
620 FOR test_result IN SELECT * FROM
621 vandelay.match_set_test_marcxml(match_set, NEW.marc) LOOP
623 INSERT INTO vandelay.bib_match ( queued_record, eg_record, match_score, quality )
628 vandelay.measure_record_quality( b.marc, match_set )
629 FROM biblio.record_entry b
630 WHERE id = test_result.record;
636 $func$ LANGUAGE PLPGSQL;
638 CREATE OR REPLACE FUNCTION vandelay.measure_record_quality ( xml TEXT, match_set_id INT ) RETURNS INT AS $_$
642 test vandelay.match_set_quality%ROWTYPE;
645 FOR test IN SELECT * FROM vandelay.match_set_quality WHERE match_set = match_set_id LOOP
646 IF test.tag IS NOT NULL THEN
647 FOR rvalue IN SELECT value FROM vandelay.flatten_marc( xml ) WHERE tag = test.tag AND subfield = test.subfield LOOP
648 IF test.value = rvalue THEN
649 out_q := out_q + test.quality;
653 IF test.value = vandelay.extract_rec_attrs(xml, ARRAY[test.svf]) -> test.svf THEN
654 out_q := out_q + test.quality;
661 $_$ LANGUAGE PLPGSQL;
664 CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
666 merge_profile vandelay.merge_profile%ROWTYPE;
667 dyn_profile vandelay.compile_profile%ROWTYPE;
677 SELECT q.marc INTO v_marc
678 FROM vandelay.queued_record q
679 JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
682 IF v_marc IS NULL THEN
683 -- RAISE NOTICE 'no marc for vandelay or bib record';
687 IF vandelay.template_overlay_bib_record( v_marc, eg_id, merge_profile_id) THEN
688 UPDATE vandelay.queued_bib_record
689 SET imported_as = eg_id,
691 WHERE id = import_id;
693 editor_string := (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1];
695 IF editor_string IS NOT NULL AND editor_string <> '' THEN
696 SELECT usr INTO editor_id FROM actor.card WHERE barcode = editor_string;
698 IF editor_id IS NULL THEN
699 SELECT id INTO editor_id FROM actor.usr WHERE usrname = editor_string;
702 IF editor_id IS NOT NULL THEN
703 UPDATE biblio.record_entry SET editor = editor_id WHERE id = eg_id;
710 -- RAISE NOTICE 'update of biblio.record_entry failed';
718 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record_with_best ( import_id BIGINT, merge_profile_id INT, lwm_ratio_value_p NUMERIC ) RETURNS BOOL AS $$
721 lwm_ratio_value NUMERIC;
724 lwm_ratio_value := COALESCE(lwm_ratio_value_p, 0.0);
726 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
729 -- RAISE NOTICE 'already imported, cannot auto-overlay'
733 SELECT m.eg_record INTO eg_id
734 FROM vandelay.bib_match m
735 JOIN vandelay.queued_bib_record qr ON (m.queued_record = qr.id)
736 JOIN vandelay.bib_queue q ON (qr.queue = q.id)
737 JOIN biblio.record_entry r ON (r.id = m.eg_record)
738 WHERE m.queued_record = import_id
739 AND qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC >= lwm_ratio_value
740 ORDER BY m.match_score DESC, -- required match score
741 qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC DESC, -- quality tie breaker
742 m.id -- when in doubt, use the first match
745 IF eg_id IS NULL THEN
746 -- RAISE NOTICE 'incoming record is not of high enough quality';
750 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
754 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record_with_best ( import_id BIGINT, merge_profile_id INT, lwm_ratio_value_p NUMERIC ) RETURNS BOOL AS $$
757 lwm_ratio_value NUMERIC;
760 lwm_ratio_value := COALESCE(lwm_ratio_value_p, 0.0);
762 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
765 -- RAISE NOTICE 'already imported, cannot auto-overlay'
769 SELECT m.eg_record INTO eg_id
770 FROM vandelay.bib_match m
771 JOIN vandelay.queued_bib_record qr ON (m.queued_record = qr.id)
772 JOIN vandelay.bib_queue q ON (qr.queue = q.id)
773 JOIN biblio.record_entry r ON (r.id = m.eg_record)
774 WHERE m.queued_record = import_id
775 AND qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC >= lwm_ratio_value
776 ORDER BY m.match_score DESC, -- required match score
777 qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC DESC, -- quality tie breaker
778 m.id -- when in doubt, use the first match
781 IF eg_id IS NULL THEN
782 -- RAISE NOTICE 'incoming record is not of high enough quality';
786 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
791 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue_with_best ( queue_id BIGINT, merge_profile_id INT, lwm_ratio_value NUMERIC ) RETURNS SETOF BIGINT AS $$
793 queued_record vandelay.queued_bib_record%ROWTYPE;
796 FOR queued_record IN SELECT * FROM vandelay.queued_bib_record WHERE queue = queue_id AND import_time IS NULL LOOP
798 IF vandelay.auto_overlay_bib_record_with_best( queued_record.id, merge_profile_id, lwm_ratio_value ) THEN
799 RETURN NEXT queued_record.id;
809 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue_with_best ( import_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
810 SELECT vandelay.auto_overlay_bib_queue_with_best( $1, $2, p.lwm_ratio ) FROM vandelay.merge_profile p WHERE id = $2;
813 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$
819 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
823 FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP
825 SELECT extract_marc_field('vandelay.queued_bib_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_bib_record WHERE id = NEW.id;
826 IF (value IS NOT NULL AND value <> '') THEN
827 INSERT INTO vandelay.queued_bib_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
836 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
839 item_data vandelay.import_item%ROWTYPE;
842 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
846 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
848 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
849 INSERT INTO vandelay.import_item (
873 item_data.definition,
874 item_data.owning_lib,
876 item_data.call_number,
877 item_data.copy_number,
882 item_data.deposit_amount,
887 item_data.circ_modifier,
888 item_data.circ_as_type,
889 item_data.alert_message,
892 item_data.opac_visible
898 $func$ LANGUAGE PLPGSQL;
900 CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$
902 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
906 DELETE FROM vandelay.queued_bib_record_attr WHERE record = OLD.id;
907 DELETE FROM vandelay.import_item WHERE record = OLD.id;
909 IF TG_OP = 'UPDATE' THEN
918 DROP TRIGGER zz_match_bibs_trigger ON vandelay.queued_bib_record;
919 CREATE TRIGGER zz_match_bibs_trigger
920 BEFORE INSERT OR UPDATE ON vandelay.queued_bib_record
921 FOR EACH ROW EXECUTE PROCEDURE vandelay.match_bib_record();
923 CREATE OR REPLACE FUNCTION vandelay.ingest_authority_marc ( ) RETURNS TRIGGER AS $$
929 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
933 FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP
935 SELECT extract_marc_field('vandelay.queued_authority_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_authority_record WHERE id = NEW.id;
936 IF (value IS NOT NULL AND value <> '') THEN
937 INSERT INTO vandelay.queued_authority_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
946 ALTER TABLE vandelay.authority_attr_definition DROP COLUMN ident;
947 ALTER TABLE vandelay.queued_authority_record
948 ADD COLUMN import_error TEXT REFERENCES vandelay.import_error (code) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
949 ADD COLUMN error_detail TEXT;
951 ALTER TABLE vandelay.authority_match DROP COLUMN matched_attr;
953 CREATE OR REPLACE FUNCTION vandelay.cleanup_authority_marc ( ) RETURNS TRIGGER AS $$
955 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
959 DELETE FROM vandelay.queued_authority_record_attr WHERE record = OLD.id;
960 IF TG_OP = 'UPDATE' THEN
967 CREATE OR REPLACE FUNCTION authority.flatten_marc ( rid BIGINT ) RETURNS SETOF authority.full_rec AS $func$
969 auth authority.record_entry%ROWTYPE;
970 output authority.full_rec%ROWTYPE;
973 SELECT INTO auth * FROM authority.record_entry WHERE id = rid;
975 FOR field IN SELECT * FROM vandelay.flatten_marc( auth.marc ) LOOP
976 output.record := rid;
977 output.ind1 := field.ind1;
978 output.ind2 := field.ind2;
979 output.tag := field.tag;
980 output.subfield := field.subfield;
981 output.value := field.value;
986 $func$ LANGUAGE PLPGSQL;
988 CREATE OR REPLACE FUNCTION biblio.flatten_marc ( rid BIGINT ) RETURNS SETOF metabib.full_rec AS $func$
990 bib biblio.record_entry%ROWTYPE;
991 output metabib.full_rec%ROWTYPE;
994 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
996 FOR field IN SELECT * FROM vandelay.flatten_marc( bib.marc ) LOOP
997 output.record := rid;
998 output.ind1 := field.ind1;
999 output.ind2 := field.ind2;
1000 output.tag := field.tag;
1001 output.subfield := field.subfield;
1002 output.value := field.value;
1007 $func$ LANGUAGE PLPGSQL;
1009 -----------------------------------------------
1010 -- Seed data for import errors
1011 -----------------------------------------------
1013 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'general.unknown', oils_i18n_gettext('general.unknown', 'Import or Overlay failed', 'vie', 'description') );
1014 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'import.item.duplicate.barcode', oils_i18n_gettext('import.item.duplicate.barcode', 'Import failed due to barcode collision', 'vie', 'description') );
1015 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'import.item.invalid.circ_modifier', oils_i18n_gettext('import.item.invalid.circ_modifier', 'Import failed due to invalid circulation modifier', 'vie', 'description') );
1016 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'import.item.invalid.location', oils_i18n_gettext('import.item.invalid.location', 'Import failed due to invalid copy location', 'vie', 'description') );
1017 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'import.duplicate.sysid', oils_i18n_gettext('import.duplicate.sysid', 'Import failed due to system id collision', 'vie', 'description') );
1018 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'import.duplicate.tcn', oils_i18n_gettext('import.duplicate.sysid', 'Import failed due to system id collision', 'vie', 'description') );
1019 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'overlay.missing.sysid', oils_i18n_gettext('overlay.missing.sysid', 'Overlay failed due to missing system id', 'vie', 'description') );
1020 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'import.auth.duplicate.acn', oils_i18n_gettext('import.auth.duplicate.acn', 'Import failed due to Accession Number collision', 'vie', 'description') );
1021 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'import.xml.malformed', oils_i18n_gettext('import.xml.malformed', 'Malformed record cause Import failure', 'vie', 'description') );
1022 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'overlay.xml.malformed', oils_i18n_gettext('overlay.xml.malformed', 'Malformed record cause Overlay failure', 'vie', 'description') );
1023 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'overlay.record.quality', oils_i18n_gettext('overlay.record.quality', 'New record had insufficient quality', 'vie', 'description') );
1026 ----------------------------------------------------------------
1027 -- Seed data for queued record/item exports
1028 ----------------------------------------------------------------
1030 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES (
1031 'vandelay.queued_bib_record.print',
1034 'vandelay.queued_bib_record.print',
1035 'Print output has been requested for records in an Importer Bib Queue.',
1042 'vandelay.queued_bib_record.csv',
1045 'vandelay.queued_bib_record.csv',
1046 'CSV output has been requested for records in an Importer Bib Queue.',
1053 'vandelay.queued_bib_record.email',
1056 'vandelay.queued_bib_record.email',
1057 'An email has been requested for records in an Importer Bib Queue.',
1064 'vandelay.queued_auth_record.print',
1067 'vandelay.queued_auth_record.print',
1068 'Print output has been requested for records in an Importer Authority Queue.',
1075 'vandelay.queued_auth_record.csv',
1078 'vandelay.queued_auth_record.csv',
1079 'CSV output has been requested for records in an Importer Authority Queue.',
1086 'vandelay.queued_auth_record.email',
1089 'vandelay.queued_auth_record.email',
1090 'An email has been requested for records in an Importer Authority Queue.',
1097 'vandelay.import_items.print',
1100 'vandelay.import_items.print',
1101 'Print output has been requested for Import Items from records in an Importer Bib Queue.',
1108 'vandelay.import_items.csv',
1111 'vandelay.import_items.csv',
1112 'CSV output has been requested for Import Items from records in an Importer Bib Queue.',
1119 'vandelay.import_items.email',
1122 'vandelay.import_items.email',
1123 'An email has been requested for Import Items from records in an Importer Bib Queue.',
1131 INSERT INTO action_trigger.event_definition (
1146 'Print Output for Queued Bib Records',
1147 'vandelay.queued_bib_record.print',
1155 Queue ID: [% target.0.queue.id %]
1156 Queue Name: [% target.0.queue.name %]
1157 Queue Type: [% target.0.queue.queue_type %]
1158 Complete? [% target.0.queue.complete %]
1160 [% FOR vqbr IN target %]
1162 Title of work | [% helpers.get_queued_bib_attr('title',vqbr.attributes) %]
1163 Author of work | [% helpers.get_queued_bib_attr('author',vqbr.attributes) %]
1164 Language of work | [% helpers.get_queued_bib_attr('language',vqbr.attributes) %]
1165 Pagination | [% helpers.get_queued_bib_attr('pagination',vqbr.attributes) %]
1166 ISBN | [% helpers.get_queued_bib_attr('isbn',vqbr.attributes) %]
1167 ISSN | [% helpers.get_queued_bib_attr('issn',vqbr.attributes) %]
1168 Price | [% helpers.get_queued_bib_attr('price',vqbr.attributes) %]
1169 Accession Number | [% helpers.get_queued_bib_attr('rec_identifier',vqbr.attributes) %]
1170 TCN Value | [% helpers.get_queued_bib_attr('eg_tcn',vqbr.attributes) %]
1171 TCN Source | [% helpers.get_queued_bib_attr('eg_tcn_source',vqbr.attributes) %]
1172 Internal ID | [% helpers.get_queued_bib_attr('eg_identifier',vqbr.attributes) %]
1173 Publisher | [% helpers.get_queued_bib_attr('publisher',vqbr.attributes) %]
1174 Publication Date | [% helpers.get_queued_bib_attr('pubdate',vqbr.attributes) %]
1175 Edition | [% helpers.get_queued_bib_attr('edition',vqbr.attributes) %]
1176 Item Barcode | [% helpers.get_queued_bib_attr('item_barcode',vqbr.attributes) %]
1184 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1189 INSERT INTO action_trigger.event_definition (
1204 'CSV Output for Queued Bib Records',
1205 'vandelay.queued_bib_record.csv',
1212 "Title of work","Author of work","Language of work","Pagination","ISBN","ISSN","Price","Accession Number","TCN Value","TCN Source","Internal ID","Publisher","Publication Date","Edition","Item Barcode"
1213 [% FOR vqbr IN target %]"[% helpers.get_queued_bib_attr('title',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('author',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('language',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('pagination',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('isbn',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('issn',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('price',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('rec_identifier',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('eg_tcn',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('eg_tcn_source',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('eg_identifier',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('publisher',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('pubdate',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('edition',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('item_barcode',vqbr.attributes) | replace('"', '""') %]"
1219 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1224 INSERT INTO action_trigger.event_definition (
1239 'Email Output for Queued Bib Records',
1240 'vandelay.queued_bib_record.email',
1247 [%- SET user = target.0.queue.owner -%]
1248 To: [%- params.recipient_email || user.email || 'root@localhost' %]
1249 From: [%- params.sender_email || default_sender %]
1250 Subject: Bibs from Import Queue
1252 Queue ID: [% target.0.queue.id %]
1253 Queue Name: [% target.0.queue.name %]
1254 Queue Type: [% target.0.queue.queue_type %]
1255 Complete? [% target.0.queue.complete %]
1257 [% FOR vqbr IN target %]
1259 Title of work | [% helpers.get_queued_bib_attr('title',vqbr.attributes) %]
1260 Author of work | [% helpers.get_queued_bib_attr('author',vqbr.attributes) %]
1261 Language of work | [% helpers.get_queued_bib_attr('language',vqbr.attributes) %]
1262 Pagination | [% helpers.get_queued_bib_attr('pagination',vqbr.attributes) %]
1263 ISBN | [% helpers.get_queued_bib_attr('isbn',vqbr.attributes) %]
1264 ISSN | [% helpers.get_queued_bib_attr('issn',vqbr.attributes) %]
1265 Price | [% helpers.get_queued_bib_attr('price',vqbr.attributes) %]
1266 Accession Number | [% helpers.get_queued_bib_attr('rec_identifier',vqbr.attributes) %]
1267 TCN Value | [% helpers.get_queued_bib_attr('eg_tcn',vqbr.attributes) %]
1268 TCN Source | [% helpers.get_queued_bib_attr('eg_tcn_source',vqbr.attributes) %]
1269 Internal ID | [% helpers.get_queued_bib_attr('eg_identifier',vqbr.attributes) %]
1270 Publisher | [% helpers.get_queued_bib_attr('publisher',vqbr.attributes) %]
1271 Publication Date | [% helpers.get_queued_bib_attr('pubdate',vqbr.attributes) %]
1272 Edition | [% helpers.get_queued_bib_attr('edition',vqbr.attributes) %]
1273 Item Barcode | [% helpers.get_queued_bib_attr('item_barcode',vqbr.attributes) %]
1281 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1284 ,( 40, 'queue.owner')
1287 INSERT INTO action_trigger.event_definition (
1302 'Print Output for Queued Authority Records',
1303 'vandelay.queued_auth_record.print',
1311 Queue ID: [% target.0.queue.id %]
1312 Queue Name: [% target.0.queue.name %]
1313 Queue Type: [% target.0.queue.queue_type %]
1314 Complete? [% target.0.queue.complete %]
1316 [% FOR vqar IN target %]
1318 Record Identifier | [% helpers.get_queued_auth_attr('rec_identifier',vqar.attributes) %]
1326 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1331 INSERT INTO action_trigger.event_definition (
1346 'CSV Output for Queued Authority Records',
1347 'vandelay.queued_auth_record.csv',
1355 [% FOR vqar IN target %]"[% helpers.get_queued_auth_attr('rec_identifier',vqar.attributes) | replace('"', '""') %]"
1361 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1366 INSERT INTO action_trigger.event_definition (
1381 'Email Output for Queued Authority Records',
1382 'vandelay.queued_auth_record.email',
1389 [%- SET user = target.0.queue.owner -%]
1390 To: [%- params.recipient_email || user.email || 'root@localhost' %]
1391 From: [%- params.sender_email || default_sender %]
1392 Subject: Authorities from Import Queue
1394 Queue ID: [% target.0.queue.id %]
1395 Queue Name: [% target.0.queue.name %]
1396 Queue Type: [% target.0.queue.queue_type %]
1397 Complete? [% target.0.queue.complete %]
1399 [% FOR vqar IN target %]
1401 Record Identifier | [% helpers.get_queued_auth_attr('rec_identifier',vqar.attributes) %]
1409 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1412 ,( 43, 'queue.owner')
1415 INSERT INTO action_trigger.event_definition (
1430 'Print Output for Import Items from Queued Bib Records',
1431 'vandelay.import_items.print',
1434 'record.queue.owner',
1439 Queue ID: [% target.0.record.queue.id %]
1440 Queue Name: [% target.0.record.queue.name %]
1441 Queue Type: [% target.0.record.queue.queue_type %]
1442 Complete? [% target.0.record.queue.complete %]
1444 [% FOR vii IN target %]
1446 Import Item ID | [% vii.id %]
1447 Title of work | [% helpers.get_queued_bib_attr('title',vii.record.attributes) %]
1448 ISBN | [% helpers.get_queued_bib_attr('isbn',vii.record.attributes) %]
1449 Attribute Definition | [% vii.definition %]
1450 Import Error | [% vii.import_error %]
1451 Import Error Detail | [% vii.error_detail %]
1452 Owning Library | [% vii.owning_lib %]
1453 Circulating Library | [% vii.circ_lib %]
1454 Call Number | [% vii.call_number %]
1455 Copy Number | [% vii.copy_number %]
1456 Status | [% vii.status.name %]
1457 Shelving Location | [% vii.location.name %]
1458 Circulate | [% vii.circulate %]
1459 Deposit | [% vii.deposit %]
1460 Deposit Amount | [% vii.deposit_amount %]
1461 Reference | [% vii.ref %]
1462 Holdable | [% vii.holdable %]
1463 Price | [% vii.price %]
1464 Barcode | [% vii.barcode %]
1465 Circulation Modifier | [% vii.circ_modifier %]
1466 Circulate As MARC Type | [% vii.circ_as_type %]
1467 Alert Message | [% vii.alert_message %]
1468 Public Note | [% vii.pub_note %]
1469 Private Note | [% vii.priv_note %]
1470 OPAC Visible | [% vii.opac_visible %]
1478 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1480 ,( 44, 'record.attributes')
1481 ,( 44, 'record.queue')
1482 ,( 44, 'record.queue.owner')
1485 INSERT INTO action_trigger.event_definition (
1500 'CSV Output for Import Items from Queued Bib Records',
1501 'vandelay.import_items.csv',
1504 'record.queue.owner',
1508 "Import Item ID","Title of work","ISBN","Attribute Definition","Import Error","Import Error Detail","Owning Library","Circulating Library","Call Number","Copy Number","Status","Shelving Location","Circulate","Deposit","Deposit Amount","Reference","Holdable","Price","Barcode","Circulation Modifier","Circulate As MARC Type","Alert Message","Public Note","Private Note","OPAC Visible"
1509 [% FOR vii IN target %]"[% vii.id | replace('"', '""') %]","[% helpers.get_queued_bib_attr('title',vii.record.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('isbn',vii.record.attributes) | replace('"', '""') %]","[% vii.definition | replace('"', '""') %]","[% vii.import_error | replace('"', '""') %]","[% vii.error_detail | replace('"', '""') %]","[% vii.owning_lib | replace('"', '""') %]","[% vii.circ_lib | replace('"', '""') %]","[% vii.call_number | replace('"', '""') %]","[% vii.copy_number | replace('"', '""') %]","[% vii.status.name | replace('"', '""') %]","[% vii.location.name | replace('"', '""') %]","[% vii.circulate | replace('"', '""') %]","[% vii.deposit | replace('"', '""') %]","[% vii.deposit_amount | replace('"', '""') %]","[% vii.ref | replace('"', '""') %]","[% vii.holdable | replace('"', '""') %]","[% vii.price | replace('"', '""') %]","[% vii.barcode | replace('"', '""') %]","[% vii.circ_modifier | replace('"', '""') %]","[% vii.circ_as_type | replace('"', '""') %]","[% vii.alert_message | replace('"', '""') %]","[% vii.pub_note | replace('"', '""') %]","[% vii.priv_note | replace('"', '""') %]","[% vii.opac_visible | replace('"', '""') %]"
1515 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1517 ,( 45, 'record.attributes')
1518 ,( 45, 'record.queue')
1519 ,( 45, 'record.queue.owner')
1522 INSERT INTO action_trigger.event_definition (
1537 'Email Output for Import Items from Queued Bib Records',
1538 'vandelay.import_items.email',
1541 'record.queue.owner',
1545 [%- SET user = target.0.record.queue.owner -%]
1546 To: [%- params.recipient_email || user.email || 'root@localhost' %]
1547 From: [%- params.sender_email || default_sender %]
1548 Subject: Import Items from Import Queue
1550 Queue ID: [% target.0.record.queue.id %]
1551 Queue Name: [% target.0.record.queue.name %]
1552 Queue Type: [% target.0.record.queue.queue_type %]
1553 Complete? [% target.0.record.queue.complete %]
1555 [% FOR vii IN target %]
1557 Import Item ID | [% vii.id %]
1558 Title of work | [% helpers.get_queued_bib_attr('title',vii.record.attributes) %]
1559 ISBN | [% helpers.get_queued_bib_attr('isbn',vii.record.attributes) %]
1560 Attribute Definition | [% vii.definition %]
1561 Import Error | [% vii.import_error %]
1562 Import Error Detail | [% vii.error_detail %]
1563 Owning Library | [% vii.owning_lib %]
1564 Circulating Library | [% vii.circ_lib %]
1565 Call Number | [% vii.call_number %]
1566 Copy Number | [% vii.copy_number %]
1567 Status | [% vii.status.name %]
1568 Shelving Location | [% vii.location.name %]
1569 Circulate | [% vii.circulate %]
1570 Deposit | [% vii.deposit %]
1571 Deposit Amount | [% vii.deposit_amount %]
1572 Reference | [% vii.ref %]
1573 Holdable | [% vii.holdable %]
1574 Price | [% vii.price %]
1575 Barcode | [% vii.barcode %]
1576 Circulation Modifier | [% vii.circ_modifier %]
1577 Circulate As MARC Type | [% vii.circ_as_type %]
1578 Alert Message | [% vii.alert_message %]
1579 Public Note | [% vii.pub_note %]
1580 Private Note | [% vii.priv_note %]
1581 OPAC Visible | [% vii.opac_visible %]
1588 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1590 ,( 46, 'record.attributes')
1591 ,( 46, 'record.queue')
1592 ,( 46, 'record.queue.owner')