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.queue_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 TYPE biblio.record_ff_map AS (record BIGINT, ff_name TEXT, ff_value TEXT);
148 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_all_fixed_fields( marc TEXT ) RETURNS SETOF biblio.record_ff_map AS $func$
153 output biblio.record_ff_map%ROWTYPE;
155 rtype := (vandelay.marc21_record_type( marc )).code;
157 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE rec_type = rtype ORDER BY tag DESC LOOP
158 output.ff_name := ff_pos.fixed_field;
159 output.ff_value := NULL;
161 IF ff_pos.tag = 'ldr' THEN
162 output.ff_value := oils_xpath_string('//*[local-name()="leader"]', marc);
163 IF output.ff_value IS NOT NULL THEN
164 output.ff_value := SUBSTRING( output.ff_value, ff_pos.start_pos + 1, ff_pos.length );
166 output.ff_value := NULL;
169 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
170 output.ff_value := SUBSTRING( tag_data, ff_pos.start_pos + 1, ff_pos.length );
171 IF output.ff_value IS NULL THEN output.ff_value := REPEAT( ff_pos.default_val, ff_pos.length ); END IF;
173 output.ff_value := NULL;
181 $func$ LANGUAGE PLPGSQL;
183 CREATE TYPE biblio.marc21_physical_characteristics AS ( id INT, record BIGINT, ptype TEXT, subfield INT, value INT );
184 CREATE OR REPLACE FUNCTION vandelay.marc21_physical_characteristics( marc TEXT) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$
188 ptype config.marc21_physical_characteristic_type_map%ROWTYPE;
189 psf config.marc21_physical_characteristic_subfield_map%ROWTYPE;
190 pval config.marc21_physical_characteristic_value_map%ROWTYPE;
191 retval biblio.marc21_physical_characteristics%ROWTYPE;
194 _007 := oils_xpath_string( '//*[@tag="007"]', marc );
196 IF _007 IS NOT NULL AND _007 <> '' THEN
197 SELECT * INTO ptype FROM config.marc21_physical_characteristic_type_map WHERE ptype_key = SUBSTRING( _007, 1, 1 );
199 IF ptype.ptype_key IS NOT NULL THEN
200 FOR psf IN SELECT * FROM config.marc21_physical_characteristic_subfield_map WHERE ptype_key = ptype.ptype_key LOOP
201 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 );
203 IF pval.id IS NOT NULL THEN
206 retval.ptype := ptype.ptype_key;
207 retval.subfield := psf.id;
208 retval.value := pval.id;
218 $func$ LANGUAGE PLPGSQL;
220 CREATE TYPE vandelay.flat_marc AS ( tag CHAR(3), ind1 TEXT, ind2 TEXT, subfield TEXT, value TEXT );
221 CREATE OR REPLACE FUNCTION vandelay.flay_marc ( TEXT ) RETURNS SETOF vandelay.flat_marc AS $func$
224 use MARC::File::XML (BinaryEncoding => 'UTF-8');
228 MARC::Charset->assume_unicode(1);
231 my $r = MARC::Record->new_from_xml( $xml );
233 return_next( { tag => 'LDR', value => $r->leader } );
235 for my $f ( $r->fields ) {
236 if ($f->is_control_field) {
237 return_next({ tag => $f->tag, value => $f->data });
239 for my $s ($f->subfields) {
242 ind1 => $f->indicator(1),
243 ind2 => $f->indicator(2),
248 if ( $f->tag eq '245' and $s->[0] eq 'a' ) {
249 my $trim = $f->indicator(2) || 0;
252 ind1 => $f->indicator(1),
253 ind2 => $f->indicator(2),
255 value => substr( $s->[1], $trim )
264 $func$ LANGUAGE PLPERLU;
266 CREATE OR REPLACE FUNCTION vandelay.flatten_marc ( marc TEXT ) RETURNS SETOF vandelay.flat_marc AS $func$
268 output vandelay.flat_marc%ROWTYPE;
271 FOR field IN SELECT * FROM vandelay.flay_marc( marc ) LOOP
272 output.ind1 := field.ind1;
273 output.ind2 := field.ind2;
274 output.tag := field.tag;
275 output.subfield := field.subfield;
276 IF field.subfield IS NOT NULL AND field.tag NOT IN ('020','022','024') THEN -- exclude standard numbers and control fields
277 output.value := naco_normalize(field.value, field.subfield);
279 output.value := field.value;
282 CONTINUE WHEN output.value IS NULL;
287 $func$ LANGUAGE PLPGSQL;
289 CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT, attr_defs TEXT[]) RETURNS hstore AS $_$
291 transformed_xml TEXT;
294 xfrm config.xml_transform%ROWTYPE;
296 new_attrs HSTORE := ''::HSTORE;
297 attr_def config.record_attr_definition%ROWTYPE;
300 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE name IN (SELECT * FROM UNNEST(attr_defs)) ORDER BY format LOOP
302 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
303 SELECT ARRAY_TO_STRING(ARRAY_ACCUM(x.value), COALESCE(attr_def.joiner,' ')) INTO attr_value
304 FROM vandelay.flatten_marc(xml) AS x
305 WHERE x.tag LIKE attr_def.tag
307 WHEN attr_def.sf_list IS NOT NULL
308 THEN POSITION(x.subfield IN attr_def.sf_list) > 0
315 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
316 attr_value := vandelay.marc21_extract_fixed_field(xml, attr_def.fixed_field);
318 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
320 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
322 -- See if we can skip the XSLT ... it's expensive
323 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
324 -- Can't skip the transform
325 IF xfrm.xslt <> '---' THEN
326 transformed_xml := oils_xslt_process(xml,xfrm.xslt);
328 transformed_xml := xml;
331 prev_xfrm := xfrm.name;
334 IF xfrm.name IS NULL THEN
335 -- just grab the marcxml (empty) transform
336 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
337 prev_xfrm := xfrm.name;
340 attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]);
342 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
343 SELECT m.value::TEXT INTO attr_value
344 FROM vandelay.marc21_physical_characteristics(xml) v
345 JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
346 WHERE v.subfield = attr_def.phys_char_sf
347 LIMIT 1; -- Just in case ...
351 -- apply index normalizers to attr_value
353 SELECT n.func AS func,
354 n.param_count AS param_count,
356 FROM config.index_normalizer n
357 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
358 WHERE attr = attr_def.name
360 EXECUTE 'SELECT ' || normalizer.func || '(' ||
361 quote_literal( attr_value ) ||
363 WHEN normalizer.param_count > 0
364 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
371 -- Add the new value to the hstore
372 new_attrs := new_attrs || hstore( attr_def.name, attr_value );
378 $_$ LANGUAGE PLPGSQL;
380 CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT ) RETURNS hstore AS $_$
381 SELECT vandelay.extract_rec_attrs( $1, (SELECT ARRAY_ACCUM(name) FROM config.record_attr_definition));
384 -- Everything between this comment and the beginning of the definition of
385 -- vandelay.match_bib_record() is strictly in service of that function.
386 CREATE TYPE vandelay.match_set_test_result AS (record BIGINT, quality INTEGER);
388 CREATE OR REPLACE FUNCTION vandelay.match_set_test_marcxml(
389 match_set_id INTEGER, record_xml TEXT
390 ) RETURNS SETOF vandelay.match_set_test_result AS $$
401 tags_rstore := vandelay.flatten_marc_hstore(record_xml);
402 svf_rstore := vandelay.extract_rec_attrs(record_xml);
404 CREATE TEMPORARY TABLE _vandelay_tmp_qrows (q INTEGER);
405 CREATE TEMPORARY TABLE _vandelay_tmp_jrows (j TEXT);
407 -- generate the where clause and return that directly (into wq), and as
408 -- a side-effect, populate the _vandelay_tmp_[qj]rows tables.
409 wq := vandelay.get_expr_from_match_set(match_set_id);
411 query_ := 'SELECT bre.id AS record, ';
413 -- qrows table is for the quality bits we add to the SELECT clause
414 SELECT ARRAY_TO_STRING(
415 ARRAY_ACCUM('COALESCE(n' || q::TEXT || '.quality, 0)'), ' + '
416 ) INTO coal FROM _vandelay_tmp_qrows;
418 -- our query string so far is the SELECT clause and the inital FROM.
419 -- no JOINs yet nor the WHERE clause
420 query_ := query_ || coal || ' AS quality ' || E'\n' ||
421 'FROM biblio.record_entry bre ';
423 -- jrows table is for the joins we must make (and the real text conditions)
424 SELECT ARRAY_TO_STRING(ARRAY_ACCUM(j), E'\n') INTO joins
425 FROM _vandelay_tmp_jrows;
427 -- add those joins and the where clause to our query.
428 query_ := query_ || joins || E'\n' || 'WHERE ' || wq || ' AND not bre.deleted';
430 -- this will return rows of record,quality
431 FOR rec IN EXECUTE query_ USING tags_rstore, svf_rstore LOOP
435 DROP TABLE _vandelay_tmp_qrows;
436 DROP TABLE _vandelay_tmp_jrows;
442 CREATE OR REPLACE FUNCTION vandelay.flatten_marc_hstore(
444 ) RETURNS HSTORE AS $$
448 ARRAY_ACCUM(tag || (COALESCE(subfield, ''))),
452 SELECT tag, subfield, ARRAY_ACCUM(value)::TEXT AS value
453 FROM vandelay.flatten_marc(record_xml)
454 GROUP BY tag, subfield ORDER BY tag, subfield
460 CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set(
464 root vandelay.match_set_point;
466 SELECT * INTO root FROM vandelay.match_set_point
467 WHERE parent IS NULL AND match_set = match_set_id;
469 RETURN vandelay.get_expr_from_match_set_point(root);
473 CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set_point(
474 node vandelay.match_set_point
481 child vandelay.match_set_point;
483 SELECT ARRAY_ACCUM(id) INTO children FROM vandelay.match_set_point
484 WHERE parent = node.id;
486 IF ARRAY_LENGTH(children, 1) > 0 THEN
487 this_op := vandelay._get_expr_render_one(node);
490 WHILE children[i] IS NOT NULL LOOP
491 SELECT * INTO child FROM vandelay.match_set_point
492 WHERE id = children[i];
494 q := q || ' ' || this_op || ' ';
497 q := q || vandelay.get_expr_from_match_set_point(child);
501 ELSIF node.bool_op IS NULL THEN
502 PERFORM vandelay._get_expr_push_qrow(node);
503 PERFORM vandelay._get_expr_push_jrow(node);
504 RETURN vandelay._get_expr_render_one(node);
511 CREATE OR REPLACE FUNCTION vandelay._get_expr_push_qrow(
512 node vandelay.match_set_point
516 INSERT INTO _vandelay_tmp_qrows (q) VALUES (node.id);
520 CREATE OR REPLACE FUNCTION vandelay._get_expr_push_jrow(
521 node vandelay.match_set_point
535 IF node.tag IS NOT NULL THEN
537 IF node.subfield IS NOT NULL THEN
538 tagkey := tagkey || node.subfield;
542 my_alias := 'n' || node.id::TEXT;
544 jrow := 'LEFT JOIN (SELECT *, ' || node.quality ||
545 ' AS quality FROM metabib.';
546 IF node.tag IS NOT NULL THEN
547 jrow := jrow || 'full_rec) ' || my_alias || ' ON (' ||
548 my_alias || '.record = bre.id AND ' || my_alias || '.tag = ''' ||
550 IF node.subfield IS NOT NULL THEN
551 jrow := jrow || ' AND ' || my_alias || '.subfield = ''' ||
552 node.subfield || '''';
554 jrow := jrow || ' AND (' || my_alias || '.value ' || op ||
555 ' ANY(($1->''' || tagkey || ''')::TEXT[])))';
557 jrow := jrow || 'record_attr) ' || my_alias || ' ON (' ||
558 my_alias || '.id = bre.id AND (' ||
559 my_alias || '.attrs->''' || node.svf ||
560 ''' ' || op || ' $2->''' || node.svf || '''))';
562 INSERT INTO _vandelay_tmp_jrows (j) VALUES (jrow);
566 CREATE OR REPLACE FUNCTION vandelay._get_expr_render_one(
567 node vandelay.match_set_point
572 IF node.bool_op IS NOT NULL THEN
575 RETURN '(n' || node.id::TEXT || '.id IS NOT NULL)';
580 CREATE OR REPLACE FUNCTION vandelay.match_bib_record() RETURNS TRIGGER AS $func$
582 incoming_existing_id TEXT;
583 test_result vandelay.match_set_test_result%ROWTYPE;
587 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
591 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
593 SELECT q.match_set INTO match_set FROM vandelay.bib_queue q WHERE q.id = NEW.queue;
595 IF match_set IS NOT NULL THEN
596 NEW.quality := vandelay.measure_record_quality( NEW.marc, match_set );
599 -- Perfect matches on 901$c exit early with a match with high quality.
600 incoming_existing_id :=
601 oils_xpath_string('//*[@tag="901"]/*[@code="c"][1]', NEW.marc);
603 IF incoming_existing_id IS NOT NULL AND incoming_existing_id != '' THEN
604 SELECT id INTO tmp_rec FROM biblio.record_entry WHERE id = incoming_existing_id::bigint;
605 IF tmp_rec IS NOT NULL THEN
606 INSERT INTO vandelay.bib_match (queued_record, eg_record, match_score, quality)
611 -- note: no match_set means quality==0
612 vandelay.measure_record_quality( b.marc, match_set )
613 FROM biblio.record_entry b
614 WHERE id = incoming_existing_id::bigint;
618 IF match_set IS NULL THEN
622 FOR test_result IN SELECT * FROM
623 vandelay.match_set_test_marcxml(match_set, NEW.marc) LOOP
625 INSERT INTO vandelay.bib_match ( queued_record, eg_record, match_score, quality )
630 vandelay.measure_record_quality( b.marc, match_set )
631 FROM biblio.record_entry b
632 WHERE id = test_result.record;
638 $func$ LANGUAGE PLPGSQL;
640 CREATE OR REPLACE FUNCTION vandelay.measure_record_quality ( xml TEXT, match_set_id INT ) RETURNS INT AS $_$
644 test vandelay.match_set_quality%ROWTYPE;
647 FOR test IN SELECT * FROM vandelay.match_set_quality WHERE match_set = match_set_id LOOP
648 IF test.tag IS NOT NULL THEN
649 FOR rvalue IN SELECT value FROM vandelay.flatten_marc( xml ) WHERE tag = test.tag AND subfield = test.subfield LOOP
650 IF test.value = rvalue THEN
651 out_q := out_q + test.quality;
655 IF test.value = vandelay.extract_rec_attrs(xml, ARRAY[test.svf]) -> test.svf THEN
656 out_q := out_q + test.quality;
663 $_$ LANGUAGE PLPGSQL;
666 CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
668 merge_profile vandelay.merge_profile%ROWTYPE;
669 dyn_profile vandelay.compile_profile%ROWTYPE;
679 SELECT q.marc INTO v_marc
680 FROM vandelay.queued_record q
681 JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
684 IF v_marc IS NULL THEN
685 -- RAISE NOTICE 'no marc for vandelay or bib record';
689 IF vandelay.template_overlay_bib_record( v_marc, eg_id, merge_profile_id) THEN
690 UPDATE vandelay.queued_bib_record
691 SET imported_as = eg_id,
693 WHERE id = import_id;
695 editor_string := (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1];
697 IF editor_string IS NOT NULL AND editor_string <> '' THEN
698 SELECT usr INTO editor_id FROM actor.card WHERE barcode = editor_string;
700 IF editor_id IS NULL THEN
701 SELECT id INTO editor_id FROM actor.usr WHERE usrname = editor_string;
704 IF editor_id IS NOT NULL THEN
705 UPDATE biblio.record_entry SET editor = editor_id WHERE id = eg_id;
712 -- RAISE NOTICE 'update of biblio.record_entry failed';
720 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 $$
723 lwm_ratio_value NUMERIC;
726 lwm_ratio_value := COALESCE(lwm_ratio_value_p, 0.0);
728 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
731 -- RAISE NOTICE 'already imported, cannot auto-overlay'
735 SELECT m.eg_record INTO eg_id
736 FROM vandelay.bib_match m
737 JOIN vandelay.queued_bib_record qr ON (m.queued_record = qr.id)
738 JOIN vandelay.bib_queue q ON (qr.queue = q.id)
739 JOIN biblio.record_entry r ON (r.id = m.eg_record)
740 WHERE m.queued_record = import_id
741 AND qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC >= lwm_ratio_value
742 ORDER BY m.match_score DESC, -- required match score
743 qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC DESC, -- quality tie breaker
744 m.id -- when in doubt, use the first match
747 IF eg_id IS NULL THEN
748 -- RAISE NOTICE 'incoming record is not of high enough quality';
752 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
756 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 $$
759 lwm_ratio_value NUMERIC;
762 lwm_ratio_value := COALESCE(lwm_ratio_value_p, 0.0);
764 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
767 -- RAISE NOTICE 'already imported, cannot auto-overlay'
771 SELECT m.eg_record INTO eg_id
772 FROM vandelay.bib_match m
773 JOIN vandelay.queued_bib_record qr ON (m.queued_record = qr.id)
774 JOIN vandelay.bib_queue q ON (qr.queue = q.id)
775 JOIN biblio.record_entry r ON (r.id = m.eg_record)
776 WHERE m.queued_record = import_id
777 AND qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC >= lwm_ratio_value
778 ORDER BY m.match_score DESC, -- required match score
779 qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC DESC, -- quality tie breaker
780 m.id -- when in doubt, use the first match
783 IF eg_id IS NULL THEN
784 -- RAISE NOTICE 'incoming record is not of high enough quality';
788 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
793 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 $$
795 queued_record vandelay.queued_bib_record%ROWTYPE;
798 FOR queued_record IN SELECT * FROM vandelay.queued_bib_record WHERE queue = queue_id AND import_time IS NULL LOOP
800 IF vandelay.auto_overlay_bib_record_with_best( queued_record.id, merge_profile_id, lwm_ratio_value ) THEN
801 RETURN NEXT queued_record.id;
811 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue_with_best ( import_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
812 SELECT vandelay.auto_overlay_bib_queue_with_best( $1, $2, p.lwm_ratio ) FROM vandelay.merge_profile p WHERE id = $2;
815 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$
821 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
825 FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP
827 SELECT extract_marc_field('vandelay.queued_bib_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_bib_record WHERE id = NEW.id;
828 IF (value IS NOT NULL AND value <> '') THEN
829 INSERT INTO vandelay.queued_bib_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
838 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
841 item_data vandelay.import_item%ROWTYPE;
844 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
848 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
850 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
851 INSERT INTO vandelay.import_item (
875 item_data.definition,
876 item_data.owning_lib,
878 item_data.call_number,
879 item_data.copy_number,
884 item_data.deposit_amount,
889 item_data.circ_modifier,
890 item_data.circ_as_type,
891 item_data.alert_message,
894 item_data.opac_visible
900 $func$ LANGUAGE PLPGSQL;
902 CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$
904 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
908 DELETE FROM vandelay.queued_bib_record_attr WHERE record = OLD.id;
909 DELETE FROM vandelay.import_item WHERE record = OLD.id;
911 IF TG_OP = 'UPDATE' THEN
920 DROP TRIGGER zz_match_bibs_trigger ON vandelay.queued_bib_record;
921 CREATE TRIGGER zz_match_bibs_trigger
922 BEFORE INSERT OR UPDATE ON vandelay.queued_bib_record
923 FOR EACH ROW EXECUTE PROCEDURE vandelay.match_bib_record();
925 CREATE OR REPLACE FUNCTION vandelay.ingest_authority_marc ( ) RETURNS TRIGGER AS $$
931 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
935 FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP
937 SELECT extract_marc_field('vandelay.queued_authority_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_authority_record WHERE id = NEW.id;
938 IF (value IS NOT NULL AND value <> '') THEN
939 INSERT INTO vandelay.queued_authority_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
948 ALTER TABLE vandelay.authority_attr_definition DROP COLUMN ident;
949 ALTER TABLE vandelay.queued_authority_record
950 ADD COLUMN import_error TEXT REFERENCES vandelay.import_error (code) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
951 ADD COLUMN error_detail TEXT;
953 ALTER TABLE vandelay.authority_match DROP COLUMN matched_attr;
955 CREATE OR REPLACE FUNCTION vandelay.cleanup_authority_marc ( ) RETURNS TRIGGER AS $$
957 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
961 DELETE FROM vandelay.queued_authority_record_attr WHERE record = OLD.id;
962 IF TG_OP = 'UPDATE' THEN
969 CREATE OR REPLACE FUNCTION authority.flatten_marc ( rid BIGINT ) RETURNS SETOF authority.full_rec AS $func$
971 auth authority.record_entry%ROWTYPE;
972 output authority.full_rec%ROWTYPE;
975 SELECT INTO auth * FROM authority.record_entry WHERE id = rid;
977 FOR field IN SELECT * FROM vandelay.flatten_marc( auth.marc ) LOOP
978 output.record := rid;
979 output.ind1 := field.ind1;
980 output.ind2 := field.ind2;
981 output.tag := field.tag;
982 output.subfield := field.subfield;
983 output.value := field.value;
988 $func$ LANGUAGE PLPGSQL;
990 CREATE OR REPLACE FUNCTION biblio.flatten_marc ( rid BIGINT ) RETURNS SETOF metabib.full_rec AS $func$
992 bib biblio.record_entry%ROWTYPE;
993 output metabib.full_rec%ROWTYPE;
996 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
998 FOR field IN SELECT * FROM vandelay.flatten_marc( bib.marc ) LOOP
999 output.record := rid;
1000 output.ind1 := field.ind1;
1001 output.ind2 := field.ind2;
1002 output.tag := field.tag;
1003 output.subfield := field.subfield;
1004 output.value := field.value;
1009 $func$ LANGUAGE PLPGSQL;
1011 -----------------------------------------------
1012 -- Seed data for import errors
1013 -----------------------------------------------
1015 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'general.unknown', oils_i18n_gettext('general.unknown', 'Import or Overlay failed', 'vie', 'description') );
1016 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') );
1017 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') );
1018 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') );
1019 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') );
1020 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') );
1021 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') );
1022 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') );
1023 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'import.xml.malformed', oils_i18n_gettext('import.xml.malformed', 'Malformed record cause Import failure', 'vie', 'description') );
1024 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'overlay.xml.malformed', oils_i18n_gettext('overlay.xml.malformed', 'Malformed record cause Overlay failure', 'vie', 'description') );
1025 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'overlay.record.quality', oils_i18n_gettext('overlay.record.quality', 'New record had insufficient quality', 'vie', 'description') );
1028 ----------------------------------------------------------------
1029 -- Seed data for queued record/item exports
1030 ----------------------------------------------------------------
1032 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES (
1033 'vandelay.queued_bib_record.print',
1036 'vandelay.queued_bib_record.print',
1037 'Print output has been requested for records in an Importer Bib Queue.',
1044 'vandelay.queued_bib_record.csv',
1047 'vandelay.queued_bib_record.csv',
1048 'CSV output has been requested for records in an Importer Bib Queue.',
1055 'vandelay.queued_bib_record.email',
1058 'vandelay.queued_bib_record.email',
1059 'An email has been requested for records in an Importer Bib Queue.',
1066 'vandelay.queued_auth_record.print',
1069 'vandelay.queued_auth_record.print',
1070 'Print output has been requested for records in an Importer Authority Queue.',
1077 'vandelay.queued_auth_record.csv',
1080 'vandelay.queued_auth_record.csv',
1081 'CSV output has been requested for records in an Importer Authority Queue.',
1088 'vandelay.queued_auth_record.email',
1091 'vandelay.queued_auth_record.email',
1092 'An email has been requested for records in an Importer Authority Queue.',
1099 'vandelay.import_items.print',
1102 'vandelay.import_items.print',
1103 'Print output has been requested for Import Items from records in an Importer Bib Queue.',
1110 'vandelay.import_items.csv',
1113 'vandelay.import_items.csv',
1114 'CSV output has been requested for Import Items from records in an Importer Bib Queue.',
1121 'vandelay.import_items.email',
1124 'vandelay.import_items.email',
1125 'An email has been requested for Import Items from records in an Importer Bib Queue.',
1133 INSERT INTO action_trigger.event_definition (
1148 'Print Output for Queued Bib Records',
1149 'vandelay.queued_bib_record.print',
1157 Queue ID: [% target.0.queue.id %]
1158 Queue Name: [% target.0.queue.name %]
1159 Queue Type: [% target.0.queue.queue_type %]
1160 Complete? [% target.0.queue.complete %]
1162 [% FOR vqbr IN target %]
1164 Title of work | [% helpers.get_queued_bib_attr('title',vqbr.attributes) %]
1165 Author of work | [% helpers.get_queued_bib_attr('author',vqbr.attributes) %]
1166 Language of work | [% helpers.get_queued_bib_attr('language',vqbr.attributes) %]
1167 Pagination | [% helpers.get_queued_bib_attr('pagination',vqbr.attributes) %]
1168 ISBN | [% helpers.get_queued_bib_attr('isbn',vqbr.attributes) %]
1169 ISSN | [% helpers.get_queued_bib_attr('issn',vqbr.attributes) %]
1170 Price | [% helpers.get_queued_bib_attr('price',vqbr.attributes) %]
1171 Accession Number | [% helpers.get_queued_bib_attr('rec_identifier',vqbr.attributes) %]
1172 TCN Value | [% helpers.get_queued_bib_attr('eg_tcn',vqbr.attributes) %]
1173 TCN Source | [% helpers.get_queued_bib_attr('eg_tcn_source',vqbr.attributes) %]
1174 Internal ID | [% helpers.get_queued_bib_attr('eg_identifier',vqbr.attributes) %]
1175 Publisher | [% helpers.get_queued_bib_attr('publisher',vqbr.attributes) %]
1176 Publication Date | [% helpers.get_queued_bib_attr('pubdate',vqbr.attributes) %]
1177 Edition | [% helpers.get_queued_bib_attr('edition',vqbr.attributes) %]
1178 Item Barcode | [% helpers.get_queued_bib_attr('item_barcode',vqbr.attributes) %]
1186 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1191 INSERT INTO action_trigger.event_definition (
1206 'CSV Output for Queued Bib Records',
1207 'vandelay.queued_bib_record.csv',
1214 "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"
1215 [% 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('"', '""') %]"
1221 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1226 INSERT INTO action_trigger.event_definition (
1241 'Email Output for Queued Bib Records',
1242 'vandelay.queued_bib_record.email',
1249 [%- SET user = target.0.queue.owner -%]
1250 To: [%- params.recipient_email || user.email || 'root@localhost' %]
1251 From: [%- params.sender_email || default_sender %]
1252 Subject: Bibs from Import Queue
1254 Queue ID: [% target.0.queue.id %]
1255 Queue Name: [% target.0.queue.name %]
1256 Queue Type: [% target.0.queue.queue_type %]
1257 Complete? [% target.0.queue.complete %]
1259 [% FOR vqbr IN target %]
1261 Title of work | [% helpers.get_queued_bib_attr('title',vqbr.attributes) %]
1262 Author of work | [% helpers.get_queued_bib_attr('author',vqbr.attributes) %]
1263 Language of work | [% helpers.get_queued_bib_attr('language',vqbr.attributes) %]
1264 Pagination | [% helpers.get_queued_bib_attr('pagination',vqbr.attributes) %]
1265 ISBN | [% helpers.get_queued_bib_attr('isbn',vqbr.attributes) %]
1266 ISSN | [% helpers.get_queued_bib_attr('issn',vqbr.attributes) %]
1267 Price | [% helpers.get_queued_bib_attr('price',vqbr.attributes) %]
1268 Accession Number | [% helpers.get_queued_bib_attr('rec_identifier',vqbr.attributes) %]
1269 TCN Value | [% helpers.get_queued_bib_attr('eg_tcn',vqbr.attributes) %]
1270 TCN Source | [% helpers.get_queued_bib_attr('eg_tcn_source',vqbr.attributes) %]
1271 Internal ID | [% helpers.get_queued_bib_attr('eg_identifier',vqbr.attributes) %]
1272 Publisher | [% helpers.get_queued_bib_attr('publisher',vqbr.attributes) %]
1273 Publication Date | [% helpers.get_queued_bib_attr('pubdate',vqbr.attributes) %]
1274 Edition | [% helpers.get_queued_bib_attr('edition',vqbr.attributes) %]
1275 Item Barcode | [% helpers.get_queued_bib_attr('item_barcode',vqbr.attributes) %]
1283 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1286 ,( 40, 'queue.owner')
1289 INSERT INTO action_trigger.event_definition (
1304 'Print Output for Queued Authority Records',
1305 'vandelay.queued_auth_record.print',
1313 Queue ID: [% target.0.queue.id %]
1314 Queue Name: [% target.0.queue.name %]
1315 Queue Type: [% target.0.queue.queue_type %]
1316 Complete? [% target.0.queue.complete %]
1318 [% FOR vqar IN target %]
1320 Record Identifier | [% helpers.get_queued_auth_attr('rec_identifier',vqar.attributes) %]
1328 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1333 INSERT INTO action_trigger.event_definition (
1348 'CSV Output for Queued Authority Records',
1349 'vandelay.queued_auth_record.csv',
1357 [% FOR vqar IN target %]"[% helpers.get_queued_auth_attr('rec_identifier',vqar.attributes) | replace('"', '""') %]"
1363 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1368 INSERT INTO action_trigger.event_definition (
1383 'Email Output for Queued Authority Records',
1384 'vandelay.queued_auth_record.email',
1391 [%- SET user = target.0.queue.owner -%]
1392 To: [%- params.recipient_email || user.email || 'root@localhost' %]
1393 From: [%- params.sender_email || default_sender %]
1394 Subject: Authorities from Import Queue
1396 Queue ID: [% target.0.queue.id %]
1397 Queue Name: [% target.0.queue.name %]
1398 Queue Type: [% target.0.queue.queue_type %]
1399 Complete? [% target.0.queue.complete %]
1401 [% FOR vqar IN target %]
1403 Record Identifier | [% helpers.get_queued_auth_attr('rec_identifier',vqar.attributes) %]
1411 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1414 ,( 43, 'queue.owner')
1417 INSERT INTO action_trigger.event_definition (
1432 'Print Output for Import Items from Queued Bib Records',
1433 'vandelay.import_items.print',
1436 'record.queue.owner',
1441 Queue ID: [% target.0.record.queue.id %]
1442 Queue Name: [% target.0.record.queue.name %]
1443 Queue Type: [% target.0.record.queue.queue_type %]
1444 Complete? [% target.0.record.queue.complete %]
1446 [% FOR vii IN target %]
1448 Import Item ID | [% vii.id %]
1449 Title of work | [% helpers.get_queued_bib_attr('title',vii.record.attributes) %]
1450 ISBN | [% helpers.get_queued_bib_attr('isbn',vii.record.attributes) %]
1451 Attribute Definition | [% vii.definition %]
1452 Import Error | [% vii.import_error %]
1453 Import Error Detail | [% vii.error_detail %]
1454 Owning Library | [% vii.owning_lib %]
1455 Circulating Library | [% vii.circ_lib %]
1456 Call Number | [% vii.call_number %]
1457 Copy Number | [% vii.copy_number %]
1458 Status | [% vii.status.name %]
1459 Shelving Location | [% vii.location.name %]
1460 Circulate | [% vii.circulate %]
1461 Deposit | [% vii.deposit %]
1462 Deposit Amount | [% vii.deposit_amount %]
1463 Reference | [% vii.ref %]
1464 Holdable | [% vii.holdable %]
1465 Price | [% vii.price %]
1466 Barcode | [% vii.barcode %]
1467 Circulation Modifier | [% vii.circ_modifier %]
1468 Circulate As MARC Type | [% vii.circ_as_type %]
1469 Alert Message | [% vii.alert_message %]
1470 Public Note | [% vii.pub_note %]
1471 Private Note | [% vii.priv_note %]
1472 OPAC Visible | [% vii.opac_visible %]
1480 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1482 ,( 44, 'record.attributes')
1483 ,( 44, 'record.queue')
1484 ,( 44, 'record.queue.owner')
1487 INSERT INTO action_trigger.event_definition (
1502 'CSV Output for Import Items from Queued Bib Records',
1503 'vandelay.import_items.csv',
1506 'record.queue.owner',
1510 "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"
1511 [% 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('"', '""') %]"
1517 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1519 ,( 45, 'record.attributes')
1520 ,( 45, 'record.queue')
1521 ,( 45, 'record.queue.owner')
1524 INSERT INTO action_trigger.event_definition (
1539 'Email Output for Import Items from Queued Bib Records',
1540 'vandelay.import_items.email',
1543 'record.queue.owner',
1547 [%- SET user = target.0.record.queue.owner -%]
1548 To: [%- params.recipient_email || user.email || 'root@localhost' %]
1549 From: [%- params.sender_email || default_sender %]
1550 Subject: Import Items from Import Queue
1552 Queue ID: [% target.0.record.queue.id %]
1553 Queue Name: [% target.0.record.queue.name %]
1554 Queue Type: [% target.0.record.queue.queue_type %]
1555 Complete? [% target.0.record.queue.complete %]
1557 [% FOR vii IN target %]
1559 Import Item ID | [% vii.id %]
1560 Title of work | [% helpers.get_queued_bib_attr('title',vii.record.attributes) %]
1561 ISBN | [% helpers.get_queued_bib_attr('isbn',vii.record.attributes) %]
1562 Attribute Definition | [% vii.definition %]
1563 Import Error | [% vii.import_error %]
1564 Import Error Detail | [% vii.error_detail %]
1565 Owning Library | [% vii.owning_lib %]
1566 Circulating Library | [% vii.circ_lib %]
1567 Call Number | [% vii.call_number %]
1568 Copy Number | [% vii.copy_number %]
1569 Status | [% vii.status.name %]
1570 Shelving Location | [% vii.location.name %]
1571 Circulate | [% vii.circulate %]
1572 Deposit | [% vii.deposit %]
1573 Deposit Amount | [% vii.deposit_amount %]
1574 Reference | [% vii.ref %]
1575 Holdable | [% vii.holdable %]
1576 Price | [% vii.price %]
1577 Barcode | [% vii.barcode %]
1578 Circulation Modifier | [% vii.circ_modifier %]
1579 Circulate As MARC Type | [% vii.circ_as_type %]
1580 Alert Message | [% vii.alert_message %]
1581 Public Note | [% vii.pub_note %]
1582 Private Note | [% vii.priv_note %]
1583 OPAC Visible | [% vii.opac_visible %]
1590 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1592 ,( 46, 'record.attributes')
1593 ,( 46, 'record.queue')
1594 ,( 46, 'record.queue.owner')