1 -- Evergreen DB patch 0572.vandelay-record-matching-and-quality.sql
6 -- check whether patch can be applied
7 SELECT evergreen.upgrade_deps_block_check('0572', :eg_version);
9 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;
11 CREATE TABLE vandelay.match_set (
12 id SERIAL PRIMARY KEY,
14 owner INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE,
15 mtype TEXT NOT NULL DEFAULT 'biblio', -- 'biblio','authority','mfhd'?, others?
16 CONSTRAINT name_once_per_owner_mtype UNIQUE (name, owner, mtype)
19 -- Table to define match points, either FF via SVF or tag+subfield
20 CREATE TABLE vandelay.match_set_point (
21 id SERIAL PRIMARY KEY,
22 match_set INT REFERENCES vandelay.match_set (id) ON DELETE CASCADE,
23 parent INT REFERENCES vandelay.match_set_point (id),
24 bool_op TEXT CHECK (bool_op IS NULL OR (bool_op IN ('AND','OR','NOT'))),
25 svf TEXT REFERENCES config.record_attr_definition (name),
28 negate BOOL DEFAULT FALSE,
29 quality INT NOT NULL DEFAULT 1, -- higher is better
30 CONSTRAINT vmsp_need_a_subfield_with_a_tag CHECK ((tag IS NOT NULL AND subfield IS NOT NULL) OR tag IS NULL),
31 CONSTRAINT vmsp_need_a_tag_or_a_ff_or_a_bo CHECK (
32 (tag IS NOT NULL AND svf IS NULL AND bool_op IS NULL) OR
33 (tag IS NULL AND svf IS NOT NULL AND bool_op IS NULL) OR
34 (tag IS NULL AND svf IS NULL AND bool_op IS NOT NULL)
38 CREATE TABLE vandelay.match_set_quality (
39 id SERIAL PRIMARY KEY,
40 match_set INT NOT NULL REFERENCES vandelay.match_set (id) ON DELETE CASCADE,
41 svf TEXT REFERENCES config.record_attr_definition,
45 quality INT NOT NULL DEFAULT 1, -- higher is better
46 CONSTRAINT vmsq_need_a_subfield_with_a_tag CHECK ((tag IS NOT NULL AND subfield IS NOT NULL) OR tag IS NULL),
47 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))
49 CREATE UNIQUE INDEX vmsq_def_once_per_set ON vandelay.match_set_quality (match_set, COALESCE(tag,''), COALESCE(subfield,''), COALESCE(svf,''), value);
53 ALTER TABLE vandelay.queue ADD COLUMN match_set INT REFERENCES vandelay.match_set (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED;
54 ALTER TABLE vandelay.queued_record ADD COLUMN quality INT NOT NULL DEFAULT 0;
55 ALTER TABLE vandelay.bib_attr_definition DROP COLUMN ident;
57 CREATE TABLE vandelay.import_error (
58 code TEXT PRIMARY KEY,
59 description TEXT NOT NULL -- i18n
62 ALTER TABLE vandelay.queued_bib_record
63 ADD COLUMN import_error TEXT REFERENCES vandelay.import_error (code) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
64 ADD COLUMN error_detail TEXT;
66 ALTER TABLE vandelay.bib_match
67 DROP COLUMN field_type,
68 DROP COLUMN matched_attr,
69 ADD COLUMN quality INT NOT NULL DEFAULT 1,
70 ADD COLUMN match_score INT NOT NULL DEFAULT 0;
72 ALTER TABLE vandelay.import_item
73 ADD COLUMN import_error TEXT REFERENCES vandelay.import_error (code) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
74 ADD COLUMN error_detail TEXT,
75 ADD COLUMN imported_as BIGINT REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED,
76 ADD COLUMN import_time TIMESTAMP WITH TIME ZONE;
78 ALTER TABLE vandelay.merge_profile ADD COLUMN lwm_ratio NUMERIC;
80 CREATE OR REPLACE FUNCTION vandelay.marc21_record_type( marc TEXT ) RETURNS config.marc21_rec_type_map AS $func$
87 retval config.marc21_rec_type_map%ROWTYPE;
89 ldr := oils_xpath_string( '//*[local-name()="leader"]', marc );
91 IF ldr IS NULL OR ldr = '' THEN
92 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE code = 'BKS';
96 SELECT * INTO tval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'Type' LIMIT 1; -- They're all the same
97 SELECT * INTO bval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'BLvl' LIMIT 1; -- They're all the same
100 tval := SUBSTRING( ldr, tval_rec.start_pos + 1, tval_rec.length );
101 bval := SUBSTRING( ldr, bval_rec.start_pos + 1, bval_rec.length );
103 -- RAISE NOTICE 'type %, blvl %, ldr %', tval, bval, ldr;
105 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE type_val LIKE '%' || tval || '%' AND blvl_val LIKE '%' || bval || '%';
108 IF retval.code IS NULL THEN
109 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE code = 'BKS';
114 $func$ LANGUAGE PLPGSQL;
116 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field( marc TEXT, ff TEXT ) RETURNS TEXT AS $func$
123 rtype := (vandelay.marc21_record_type( marc )).code;
124 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = ff AND rec_type = rtype ORDER BY tag DESC LOOP
125 IF ff_pos.tag = 'ldr' THEN
126 val := oils_xpath_string('//*[local-name()="leader"]', marc);
127 IF val IS NOT NULL THEN
128 val := SUBSTRING( val, ff_pos.start_pos + 1, ff_pos.length );
132 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
133 val := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length );
137 val := REPEAT( ff_pos.default_val, ff_pos.length );
143 $func$ LANGUAGE PLPGSQL;
145 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_all_fixed_fields( marc TEXT ) RETURNS SETOF biblio.record_ff_map AS $func$
150 output biblio.record_ff_map%ROWTYPE;
152 rtype := (vandelay.marc21_record_type( marc )).code;
154 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE rec_type = rtype ORDER BY tag DESC LOOP
155 output.ff_name := ff_pos.fixed_field;
156 output.ff_value := NULL;
158 IF ff_pos.tag = 'ldr' THEN
159 output.ff_value := oils_xpath_string('//*[local-name()="leader"]', marc);
160 IF output.ff_value IS NOT NULL THEN
161 output.ff_value := SUBSTRING( output.ff_value, ff_pos.start_pos + 1, ff_pos.length );
163 output.ff_value := NULL;
166 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
167 output.ff_value := SUBSTRING( tag_data, ff_pos.start_pos + 1, ff_pos.length );
168 IF output.ff_value IS NULL THEN output.ff_value := REPEAT( ff_pos.default_val, ff_pos.length ); END IF;
170 output.ff_value := NULL;
178 $func$ LANGUAGE PLPGSQL;
180 CREATE OR REPLACE FUNCTION vandelay.marc21_physical_characteristics( marc TEXT) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$
184 ptype config.marc21_physical_characteristic_type_map%ROWTYPE;
185 psf config.marc21_physical_characteristic_subfield_map%ROWTYPE;
186 pval config.marc21_physical_characteristic_value_map%ROWTYPE;
187 retval biblio.marc21_physical_characteristics%ROWTYPE;
190 _007 := oils_xpath_string( '//*[@tag="007"]', marc );
192 IF _007 IS NOT NULL AND _007 <> '' THEN
193 SELECT * INTO ptype FROM config.marc21_physical_characteristic_type_map WHERE ptype_key = SUBSTRING( _007, 1, 1 );
195 IF ptype.ptype_key IS NOT NULL THEN
196 FOR psf IN SELECT * FROM config.marc21_physical_characteristic_subfield_map WHERE ptype_key = ptype.ptype_key LOOP
197 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 );
199 IF pval.id IS NOT NULL THEN
202 retval.ptype := ptype.ptype_key;
203 retval.subfield := psf.id;
204 retval.value := pval.id;
214 $func$ LANGUAGE PLPGSQL;
216 CREATE TYPE vandelay.flat_marc AS ( tag CHAR(3), ind1 TEXT, ind2 TEXT, subfield TEXT, value TEXT );
217 CREATE OR REPLACE FUNCTION vandelay.flay_marc ( TEXT ) RETURNS SETOF vandelay.flat_marc AS $func$
220 use MARC::File::XML (BinaryEncoding => 'UTF-8');
224 MARC::Charset->assume_unicode(1);
227 my $r = MARC::Record->new_from_xml( $xml );
229 return_next( { tag => 'LDR', value => $r->leader } );
231 for my $f ( $r->fields ) {
232 if ($f->is_control_field) {
233 return_next({ tag => $f->tag, value => $f->data });
235 for my $s ($f->subfields) {
238 ind1 => $f->indicator(1),
239 ind2 => $f->indicator(2),
244 if ( $f->tag eq '245' and $s->[0] eq 'a' ) {
245 my $trim = $f->indicator(2) || 0;
248 ind1 => $f->indicator(1),
249 ind2 => $f->indicator(2),
251 value => substr( $s->[1], $trim )
260 $func$ LANGUAGE PLPERLU;
262 CREATE OR REPLACE FUNCTION vandelay.flatten_marc ( marc TEXT ) RETURNS SETOF vandelay.flat_marc AS $func$
264 output vandelay.flat_marc%ROWTYPE;
267 FOR field IN SELECT * FROM vandelay.flay_marc( marc ) LOOP
268 output.ind1 := field.ind1;
269 output.ind2 := field.ind2;
270 output.tag := field.tag;
271 output.subfield := field.subfield;
272 IF field.subfield IS NOT NULL AND field.tag NOT IN ('020','022','024') THEN -- exclude standard numbers and control fields
273 output.value := naco_normalize(field.value, field.subfield);
275 output.value := field.value;
278 CONTINUE WHEN output.value IS NULL;
283 $func$ LANGUAGE PLPGSQL;
285 CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT, attr_defs TEXT[]) RETURNS hstore AS $_$
287 transformed_xml TEXT;
290 xfrm config.xml_transform%ROWTYPE;
292 new_attrs HSTORE := ''::HSTORE;
293 attr_def config.record_attr_definition%ROWTYPE;
296 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE name IN (SELECT * FROM UNNEST(attr_defs)) ORDER BY format LOOP
298 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
299 SELECT ARRAY_TO_STRING(ARRAY_ACCUM(x.value), COALESCE(attr_def.joiner,' ')) INTO attr_value
300 FROM vandelay.flatten_marc(xml) AS x
301 WHERE x.tag LIKE attr_def.tag
303 WHEN attr_def.sf_list IS NOT NULL
304 THEN POSITION(x.subfield IN attr_def.sf_list) > 0
311 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
312 attr_value := vandelay.marc21_extract_fixed_field(xml, attr_def.fixed_field);
314 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
316 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
318 -- See if we can skip the XSLT ... it's expensive
319 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
320 -- Can't skip the transform
321 IF xfrm.xslt <> '---' THEN
322 transformed_xml := oils_xslt_process(xml,xfrm.xslt);
324 transformed_xml := xml;
327 prev_xfrm := xfrm.name;
330 IF xfrm.name IS NULL THEN
331 -- just grab the marcxml (empty) transform
332 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
333 prev_xfrm := xfrm.name;
336 attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]);
338 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
339 SELECT m.value::TEXT INTO attr_value
340 FROM vandelay.marc21_physical_characteristics(xml) v
341 JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
342 WHERE v.subfield = attr_def.phys_char_sf
343 LIMIT 1; -- Just in case ...
347 -- apply index normalizers to attr_value
349 SELECT n.func AS func,
350 n.param_count AS param_count,
352 FROM config.index_normalizer n
353 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
354 WHERE attr = attr_def.name
356 EXECUTE 'SELECT ' || normalizer.func || '(' ||
357 quote_literal( attr_value ) ||
359 WHEN normalizer.param_count > 0
360 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
367 -- Add the new value to the hstore
368 new_attrs := new_attrs || hstore( attr_def.name, attr_value );
374 $_$ LANGUAGE PLPGSQL;
376 CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT ) RETURNS hstore AS $_$
377 SELECT vandelay.extract_rec_attrs( $1, (SELECT ARRAY_ACCUM(name) FROM config.record_attr_definition));
380 -- Everything between this comment and the beginning of the definition of
381 -- vandelay.match_bib_record() is strictly in service of that function.
382 CREATE TYPE vandelay.match_set_test_result AS (record BIGINT, quality INTEGER);
384 CREATE OR REPLACE FUNCTION vandelay.match_set_test_marcxml(
385 match_set_id INTEGER, record_xml TEXT
386 ) RETURNS SETOF vandelay.match_set_test_result AS $$
397 tags_rstore := vandelay.flatten_marc_hstore(record_xml);
398 svf_rstore := vandelay.extract_rec_attrs(record_xml);
400 CREATE TEMPORARY TABLE _vandelay_tmp_qrows (q INTEGER);
401 CREATE TEMPORARY TABLE _vandelay_tmp_jrows (j TEXT);
403 -- generate the where clause and return that directly (into wq), and as
404 -- a side-effect, populate the _vandelay_tmp_[qj]rows tables.
405 wq := vandelay.get_expr_from_match_set(match_set_id);
407 query_ := 'SELECT bre.id AS record, ';
409 -- qrows table is for the quality bits we add to the SELECT clause
410 SELECT ARRAY_TO_STRING(
411 ARRAY_ACCUM('COALESCE(n' || q::TEXT || '.quality, 0)'), ' + '
412 ) INTO coal FROM _vandelay_tmp_qrows;
414 -- our query string so far is the SELECT clause and the inital FROM.
415 -- no JOINs yet nor the WHERE clause
416 query_ := query_ || coal || ' AS quality ' || E'\n' ||
417 'FROM biblio.record_entry bre ';
419 -- jrows table is for the joins we must make (and the real text conditions)
420 SELECT ARRAY_TO_STRING(ARRAY_ACCUM(j), E'\n') INTO joins
421 FROM _vandelay_tmp_jrows;
423 -- add those joins and the where clause to our query.
424 query_ := query_ || joins || E'\n' || 'WHERE ' || wq || ' AND not bre.deleted';
426 -- this will return rows of record,quality
427 FOR rec IN EXECUTE query_ USING tags_rstore, svf_rstore LOOP
431 DROP TABLE _vandelay_tmp_qrows;
432 DROP TABLE _vandelay_tmp_jrows;
438 CREATE OR REPLACE FUNCTION vandelay.flatten_marc_hstore(
440 ) RETURNS HSTORE AS $$
444 ARRAY_ACCUM(tag || (COALESCE(subfield, ''))),
448 SELECT tag, subfield, ARRAY_ACCUM(value)::TEXT AS value
449 FROM vandelay.flatten_marc(record_xml)
450 GROUP BY tag, subfield ORDER BY tag, subfield
456 CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set(
460 root vandelay.match_set_point;
462 SELECT * INTO root FROM vandelay.match_set_point
463 WHERE parent IS NULL AND match_set = match_set_id;
465 RETURN vandelay.get_expr_from_match_set_point(root);
469 CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set_point(
470 node vandelay.match_set_point
477 child vandelay.match_set_point;
479 SELECT ARRAY_ACCUM(id) INTO children FROM vandelay.match_set_point
480 WHERE parent = node.id;
482 IF ARRAY_LENGTH(children, 1) > 0 THEN
483 this_op := vandelay._get_expr_render_one(node);
486 WHILE children[i] IS NOT NULL LOOP
487 SELECT * INTO child FROM vandelay.match_set_point
488 WHERE id = children[i];
490 q := q || ' ' || this_op || ' ';
493 q := q || vandelay.get_expr_from_match_set_point(child);
497 ELSIF node.bool_op IS NULL THEN
498 PERFORM vandelay._get_expr_push_qrow(node);
499 PERFORM vandelay._get_expr_push_jrow(node);
500 RETURN vandelay._get_expr_render_one(node);
507 CREATE OR REPLACE FUNCTION vandelay._get_expr_push_qrow(
508 node vandelay.match_set_point
512 INSERT INTO _vandelay_tmp_qrows (q) VALUES (node.id);
516 CREATE OR REPLACE FUNCTION vandelay._get_expr_push_jrow(
517 node vandelay.match_set_point
531 IF node.tag IS NOT NULL THEN
533 IF node.subfield IS NOT NULL THEN
534 tagkey := tagkey || node.subfield;
538 my_alias := 'n' || node.id::TEXT;
540 jrow := 'LEFT JOIN (SELECT *, ' || node.quality ||
541 ' AS quality FROM metabib.';
542 IF node.tag IS NOT NULL THEN
543 jrow := jrow || 'full_rec) ' || my_alias || ' ON (' ||
544 my_alias || '.record = bre.id AND ' || my_alias || '.tag = ''' ||
546 IF node.subfield IS NOT NULL THEN
547 jrow := jrow || ' AND ' || my_alias || '.subfield = ''' ||
548 node.subfield || '''';
550 jrow := jrow || ' AND (' || my_alias || '.value ' || op ||
551 ' ANY(($1->''' || tagkey || ''')::TEXT[])))';
553 jrow := jrow || 'record_attr) ' || my_alias || ' ON (' ||
554 my_alias || '.id = bre.id AND (' ||
555 my_alias || '.attrs->''' || node.svf ||
556 ''' ' || op || ' $2->''' || node.svf || '''))';
558 INSERT INTO _vandelay_tmp_jrows (j) VALUES (jrow);
562 CREATE OR REPLACE FUNCTION vandelay._get_expr_render_one(
563 node vandelay.match_set_point
568 IF node.bool_op IS NOT NULL THEN
571 RETURN '(n' || node.id::TEXT || '.id IS NOT NULL)';
576 CREATE OR REPLACE FUNCTION vandelay.match_bib_record() RETURNS TRIGGER AS $func$
578 incoming_existing_id TEXT;
579 test_result vandelay.match_set_test_result%ROWTYPE;
583 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
587 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
589 SELECT q.match_set INTO match_set FROM vandelay.bib_queue q WHERE q.id = NEW.queue;
591 IF match_set IS NOT NULL THEN
592 NEW.quality := vandelay.measure_record_quality( NEW.marc, match_set );
595 -- Perfect matches on 901$c exit early with a match with high quality.
596 incoming_existing_id :=
597 oils_xpath_string('//*[@tag="901"]/*[@code="c"][1]', NEW.marc);
599 IF incoming_existing_id IS NOT NULL AND incoming_existing_id != '' THEN
600 SELECT id INTO tmp_rec FROM biblio.record_entry WHERE id = incoming_existing_id::bigint;
601 IF tmp_rec IS NOT NULL THEN
602 INSERT INTO vandelay.bib_match (queued_record, eg_record, match_score, quality)
607 -- note: no match_set means quality==0
608 vandelay.measure_record_quality( b.marc, match_set )
609 FROM biblio.record_entry b
610 WHERE id = incoming_existing_id::bigint;
614 IF match_set IS NULL THEN
618 FOR test_result IN SELECT * FROM
619 vandelay.match_set_test_marcxml(match_set, NEW.marc) LOOP
621 INSERT INTO vandelay.bib_match ( queued_record, eg_record, match_score, quality )
626 vandelay.measure_record_quality( b.marc, match_set )
627 FROM biblio.record_entry b
628 WHERE id = test_result.record;
634 $func$ LANGUAGE PLPGSQL;
636 CREATE OR REPLACE FUNCTION vandelay.measure_record_quality ( xml TEXT, match_set_id INT ) RETURNS INT AS $_$
640 test vandelay.match_set_quality%ROWTYPE;
643 FOR test IN SELECT * FROM vandelay.match_set_quality WHERE match_set = match_set_id LOOP
644 IF test.tag IS NOT NULL THEN
645 FOR rvalue IN SELECT value FROM vandelay.flatten_marc( xml ) WHERE tag = test.tag AND subfield = test.subfield LOOP
646 IF test.value = rvalue THEN
647 out_q := out_q + test.quality;
651 IF test.value = vandelay.extract_rec_attrs(xml, ARRAY[test.svf]) -> test.svf THEN
652 out_q := out_q + test.quality;
659 $_$ LANGUAGE PLPGSQL;
662 CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
664 merge_profile vandelay.merge_profile%ROWTYPE;
665 dyn_profile vandelay.compile_profile%ROWTYPE;
675 SELECT q.marc INTO v_marc
676 FROM vandelay.queued_record q
677 JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
680 IF v_marc IS NULL THEN
681 -- RAISE NOTICE 'no marc for vandelay or bib record';
685 IF vandelay.template_overlay_bib_record( v_marc, eg_id, merge_profile_id) THEN
686 UPDATE vandelay.queued_bib_record
687 SET imported_as = eg_id,
689 WHERE id = import_id;
691 editor_string := (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1];
693 IF editor_string IS NOT NULL AND editor_string <> '' THEN
694 SELECT usr INTO editor_id FROM actor.card WHERE barcode = editor_string;
696 IF editor_id IS NULL THEN
697 SELECT id INTO editor_id FROM actor.usr WHERE usrname = editor_string;
700 IF editor_id IS NOT NULL THEN
701 UPDATE biblio.record_entry SET editor = editor_id WHERE id = eg_id;
708 -- RAISE NOTICE 'update of biblio.record_entry failed';
716 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 $$
719 lwm_ratio_value NUMERIC;
722 lwm_ratio_value := COALESCE(lwm_ratio_value_p, 0.0);
724 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
727 -- RAISE NOTICE 'already imported, cannot auto-overlay'
731 SELECT m.eg_record INTO eg_id
732 FROM vandelay.bib_match m
733 JOIN vandelay.queued_bib_record qr ON (m.queued_record = qr.id)
734 JOIN vandelay.bib_queue q ON (qr.queue = q.id)
735 JOIN biblio.record_entry r ON (r.id = m.eg_record)
736 WHERE m.queued_record = import_id
737 AND qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC >= lwm_ratio_value
738 ORDER BY m.match_score DESC, -- required match score
739 qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC DESC, -- quality tie breaker
740 m.id -- when in doubt, use the first match
743 IF eg_id IS NULL THEN
744 -- RAISE NOTICE 'incoming record is not of high enough quality';
748 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
752 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 $$
755 lwm_ratio_value NUMERIC;
758 lwm_ratio_value := COALESCE(lwm_ratio_value_p, 0.0);
760 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
763 -- RAISE NOTICE 'already imported, cannot auto-overlay'
767 SELECT m.eg_record INTO eg_id
768 FROM vandelay.bib_match m
769 JOIN vandelay.queued_bib_record qr ON (m.queued_record = qr.id)
770 JOIN vandelay.bib_queue q ON (qr.queue = q.id)
771 JOIN biblio.record_entry r ON (r.id = m.eg_record)
772 WHERE m.queued_record = import_id
773 AND qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC >= lwm_ratio_value
774 ORDER BY m.match_score DESC, -- required match score
775 qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC DESC, -- quality tie breaker
776 m.id -- when in doubt, use the first match
779 IF eg_id IS NULL THEN
780 -- RAISE NOTICE 'incoming record is not of high enough quality';
784 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
789 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 $$
791 queued_record vandelay.queued_bib_record%ROWTYPE;
794 FOR queued_record IN SELECT * FROM vandelay.queued_bib_record WHERE queue = queue_id AND import_time IS NULL LOOP
796 IF vandelay.auto_overlay_bib_record_with_best( queued_record.id, merge_profile_id, lwm_ratio_value ) THEN
797 RETURN NEXT queued_record.id;
807 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue_with_best ( import_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
808 SELECT vandelay.auto_overlay_bib_queue_with_best( $1, $2, p.lwm_ratio ) FROM vandelay.merge_profile p WHERE id = $2;
811 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$
817 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
821 FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP
823 SELECT extract_marc_field('vandelay.queued_bib_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_bib_record WHERE id = NEW.id;
824 IF (value IS NOT NULL AND value <> '') THEN
825 INSERT INTO vandelay.queued_bib_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
834 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
837 item_data vandelay.import_item%ROWTYPE;
840 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
844 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
846 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
847 INSERT INTO vandelay.import_item (
871 item_data.definition,
872 item_data.owning_lib,
874 item_data.call_number,
875 item_data.copy_number,
880 item_data.deposit_amount,
885 item_data.circ_modifier,
886 item_data.circ_as_type,
887 item_data.alert_message,
890 item_data.opac_visible
896 $func$ LANGUAGE PLPGSQL;
898 CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$
900 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
904 DELETE FROM vandelay.queued_bib_record_attr WHERE record = OLD.id;
905 DELETE FROM vandelay.import_item WHERE record = OLD.id;
907 IF TG_OP = 'UPDATE' THEN
916 DROP TRIGGER zz_match_bibs_trigger ON vandelay.queued_bib_record;
917 CREATE TRIGGER zz_match_bibs_trigger
918 BEFORE INSERT OR UPDATE ON vandelay.queued_bib_record
919 FOR EACH ROW EXECUTE PROCEDURE vandelay.match_bib_record();
921 CREATE OR REPLACE FUNCTION vandelay.ingest_authority_marc ( ) RETURNS TRIGGER AS $$
927 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
931 FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP
933 SELECT extract_marc_field('vandelay.queued_authority_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_authority_record WHERE id = NEW.id;
934 IF (value IS NOT NULL AND value <> '') THEN
935 INSERT INTO vandelay.queued_authority_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
944 ALTER TABLE vandelay.authority_attr_definition DROP COLUMN ident;
945 ALTER TABLE vandelay.queued_authority_record
946 ADD COLUMN import_error TEXT REFERENCES vandelay.import_error (code) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
947 ADD COLUMN error_detail TEXT;
949 ALTER TABLE vandelay.authority_match DROP COLUMN matched_attr;
951 CREATE OR REPLACE FUNCTION vandelay.cleanup_authority_marc ( ) RETURNS TRIGGER AS $$
953 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
957 DELETE FROM vandelay.queued_authority_record_attr WHERE record = OLD.id;
958 IF TG_OP = 'UPDATE' THEN
965 CREATE OR REPLACE FUNCTION authority.flatten_marc ( rid BIGINT ) RETURNS SETOF authority.full_rec AS $func$
967 auth authority.record_entry%ROWTYPE;
968 output authority.full_rec%ROWTYPE;
971 SELECT INTO auth * FROM authority.record_entry WHERE id = rid;
973 FOR field IN SELECT * FROM vandelay.flatten_marc( auth.marc ) LOOP
974 output.record := rid;
975 output.ind1 := field.ind1;
976 output.ind2 := field.ind2;
977 output.tag := field.tag;
978 output.subfield := field.subfield;
979 output.value := field.value;
984 $func$ LANGUAGE PLPGSQL;
986 CREATE OR REPLACE FUNCTION biblio.flatten_marc ( rid BIGINT ) RETURNS SETOF metabib.full_rec AS $func$
988 bib biblio.record_entry%ROWTYPE;
989 output metabib.full_rec%ROWTYPE;
992 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
994 FOR field IN SELECT * FROM vandelay.flatten_marc( bib.marc ) LOOP
995 output.record := rid;
996 output.ind1 := field.ind1;
997 output.ind2 := field.ind2;
998 output.tag := field.tag;
999 output.subfield := field.subfield;
1000 output.value := field.value;
1005 $func$ LANGUAGE PLPGSQL;
1007 -----------------------------------------------
1008 -- Seed data for import errors
1009 -----------------------------------------------
1011 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'general.unknown', oils_i18n_gettext('general.unknown', 'Import or Overlay failed', 'vie', 'description') );
1012 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') );
1013 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') );
1014 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') );
1015 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') );
1016 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') );
1017 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') );
1018 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') );
1019 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'import.xml.malformed', oils_i18n_gettext('import.xml.malformed', 'Malformed record cause Import failure', 'vie', 'description') );
1020 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'overlay.xml.malformed', oils_i18n_gettext('overlay.xml.malformed', 'Malformed record cause Overlay failure', 'vie', 'description') );
1021 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'overlay.record.quality', oils_i18n_gettext('overlay.record.quality', 'New record had insufficient quality', 'vie', 'description') );
1024 ----------------------------------------------------------------
1025 -- Seed data for queued record/item exports
1026 ----------------------------------------------------------------
1028 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES (
1029 'vandelay.queued_bib_record.print',
1032 'vandelay.queued_bib_record.print',
1033 'Print output has been requested for records in an Importer Bib Queue.',
1040 'vandelay.queued_bib_record.csv',
1043 'vandelay.queued_bib_record.csv',
1044 'CSV output has been requested for records in an Importer Bib Queue.',
1051 'vandelay.queued_bib_record.email',
1054 'vandelay.queued_bib_record.email',
1055 'An email has been requested for records in an Importer Bib Queue.',
1062 'vandelay.queued_auth_record.print',
1065 'vandelay.queued_auth_record.print',
1066 'Print output has been requested for records in an Importer Authority Queue.',
1073 'vandelay.queued_auth_record.csv',
1076 'vandelay.queued_auth_record.csv',
1077 'CSV output has been requested for records in an Importer Authority Queue.',
1084 'vandelay.queued_auth_record.email',
1087 'vandelay.queued_auth_record.email',
1088 'An email has been requested for records in an Importer Authority Queue.',
1095 'vandelay.import_items.print',
1098 'vandelay.import_items.print',
1099 'Print output has been requested for Import Items from records in an Importer Bib Queue.',
1106 'vandelay.import_items.csv',
1109 'vandelay.import_items.csv',
1110 'CSV output has been requested for Import Items from records in an Importer Bib Queue.',
1117 'vandelay.import_items.email',
1120 'vandelay.import_items.email',
1121 'An email has been requested for Import Items from records in an Importer Bib Queue.',
1129 INSERT INTO action_trigger.event_definition (
1144 'Print Output for Queued Bib Records',
1145 'vandelay.queued_bib_record.print',
1153 Queue ID: [% target.0.queue.id %]
1154 Queue Name: [% target.0.queue.name %]
1155 Queue Type: [% target.0.queue.queue_type %]
1156 Complete? [% target.0.queue.complete %]
1158 [% FOR vqbr IN target %]
1160 Title of work | [% helpers.get_queued_bib_attr('title',vqbr.attributes) %]
1161 Author of work | [% helpers.get_queued_bib_attr('author',vqbr.attributes) %]
1162 Language of work | [% helpers.get_queued_bib_attr('language',vqbr.attributes) %]
1163 Pagination | [% helpers.get_queued_bib_attr('pagination',vqbr.attributes) %]
1164 ISBN | [% helpers.get_queued_bib_attr('isbn',vqbr.attributes) %]
1165 ISSN | [% helpers.get_queued_bib_attr('issn',vqbr.attributes) %]
1166 Price | [% helpers.get_queued_bib_attr('price',vqbr.attributes) %]
1167 Accession Number | [% helpers.get_queued_bib_attr('rec_identifier',vqbr.attributes) %]
1168 TCN Value | [% helpers.get_queued_bib_attr('eg_tcn',vqbr.attributes) %]
1169 TCN Source | [% helpers.get_queued_bib_attr('eg_tcn_source',vqbr.attributes) %]
1170 Internal ID | [% helpers.get_queued_bib_attr('eg_identifier',vqbr.attributes) %]
1171 Publisher | [% helpers.get_queued_bib_attr('publisher',vqbr.attributes) %]
1172 Publication Date | [% helpers.get_queued_bib_attr('pubdate',vqbr.attributes) %]
1173 Edition | [% helpers.get_queued_bib_attr('edition',vqbr.attributes) %]
1174 Item Barcode | [% helpers.get_queued_bib_attr('item_barcode',vqbr.attributes) %]
1182 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1187 INSERT INTO action_trigger.event_definition (
1202 'CSV Output for Queued Bib Records',
1203 'vandelay.queued_bib_record.csv',
1210 "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"
1211 [% 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('"', '""') %]"
1217 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1222 INSERT INTO action_trigger.event_definition (
1237 'Email Output for Queued Bib Records',
1238 'vandelay.queued_bib_record.email',
1245 [%- SET user = target.0.queue.owner -%]
1246 To: [%- params.recipient_email || user.email || 'root@localhost' %]
1247 From: [%- params.sender_email || default_sender %]
1248 Subject: Bibs from Import Queue
1250 Queue ID: [% target.0.queue.id %]
1251 Queue Name: [% target.0.queue.name %]
1252 Queue Type: [% target.0.queue.queue_type %]
1253 Complete? [% target.0.queue.complete %]
1255 [% FOR vqbr IN target %]
1257 Title of work | [% helpers.get_queued_bib_attr('title',vqbr.attributes) %]
1258 Author of work | [% helpers.get_queued_bib_attr('author',vqbr.attributes) %]
1259 Language of work | [% helpers.get_queued_bib_attr('language',vqbr.attributes) %]
1260 Pagination | [% helpers.get_queued_bib_attr('pagination',vqbr.attributes) %]
1261 ISBN | [% helpers.get_queued_bib_attr('isbn',vqbr.attributes) %]
1262 ISSN | [% helpers.get_queued_bib_attr('issn',vqbr.attributes) %]
1263 Price | [% helpers.get_queued_bib_attr('price',vqbr.attributes) %]
1264 Accession Number | [% helpers.get_queued_bib_attr('rec_identifier',vqbr.attributes) %]
1265 TCN Value | [% helpers.get_queued_bib_attr('eg_tcn',vqbr.attributes) %]
1266 TCN Source | [% helpers.get_queued_bib_attr('eg_tcn_source',vqbr.attributes) %]
1267 Internal ID | [% helpers.get_queued_bib_attr('eg_identifier',vqbr.attributes) %]
1268 Publisher | [% helpers.get_queued_bib_attr('publisher',vqbr.attributes) %]
1269 Publication Date | [% helpers.get_queued_bib_attr('pubdate',vqbr.attributes) %]
1270 Edition | [% helpers.get_queued_bib_attr('edition',vqbr.attributes) %]
1271 Item Barcode | [% helpers.get_queued_bib_attr('item_barcode',vqbr.attributes) %]
1279 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1282 ,( 41, 'queue.owner')
1285 INSERT INTO action_trigger.event_definition (
1300 'Print Output for Queued Authority Records',
1301 'vandelay.queued_auth_record.print',
1309 Queue ID: [% target.0.queue.id %]
1310 Queue Name: [% target.0.queue.name %]
1311 Queue Type: [% target.0.queue.queue_type %]
1312 Complete? [% target.0.queue.complete %]
1314 [% FOR vqar IN target %]
1316 Record Identifier | [% helpers.get_queued_auth_attr('rec_identifier',vqar.attributes) %]
1324 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1329 INSERT INTO action_trigger.event_definition (
1344 'CSV Output for Queued Authority Records',
1345 'vandelay.queued_auth_record.csv',
1353 [% FOR vqar IN target %]"[% helpers.get_queued_auth_attr('rec_identifier',vqar.attributes) | replace('"', '""') %]"
1359 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1364 INSERT INTO action_trigger.event_definition (
1379 'Email Output for Queued Authority Records',
1380 'vandelay.queued_auth_record.email',
1387 [%- SET user = target.0.queue.owner -%]
1388 To: [%- params.recipient_email || user.email || 'root@localhost' %]
1389 From: [%- params.sender_email || default_sender %]
1390 Subject: Authorities from Import Queue
1392 Queue ID: [% target.0.queue.id %]
1393 Queue Name: [% target.0.queue.name %]
1394 Queue Type: [% target.0.queue.queue_type %]
1395 Complete? [% target.0.queue.complete %]
1397 [% FOR vqar IN target %]
1399 Record Identifier | [% helpers.get_queued_auth_attr('rec_identifier',vqar.attributes) %]
1407 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1410 ,( 44, 'queue.owner')
1413 INSERT INTO action_trigger.event_definition (
1428 'Print Output for Import Items from Queued Bib Records',
1429 'vandelay.import_items.print',
1432 'record.queue.owner',
1437 Queue ID: [% target.0.record.queue.id %]
1438 Queue Name: [% target.0.record.queue.name %]
1439 Queue Type: [% target.0.record.queue.queue_type %]
1440 Complete? [% target.0.record.queue.complete %]
1442 [% FOR vii IN target %]
1444 Import Item ID | [% vii.id %]
1445 Title of work | [% helpers.get_queued_bib_attr('title',vii.record.attributes) %]
1446 ISBN | [% helpers.get_queued_bib_attr('isbn',vii.record.attributes) %]
1447 Attribute Definition | [% vii.definition %]
1448 Import Error | [% vii.import_error %]
1449 Import Error Detail | [% vii.error_detail %]
1450 Owning Library | [% vii.owning_lib %]
1451 Circulating Library | [% vii.circ_lib %]
1452 Call Number | [% vii.call_number %]
1453 Copy Number | [% vii.copy_number %]
1454 Status | [% vii.status.name %]
1455 Shelving Location | [% vii.location.name %]
1456 Circulate | [% vii.circulate %]
1457 Deposit | [% vii.deposit %]
1458 Deposit Amount | [% vii.deposit_amount %]
1459 Reference | [% vii.ref %]
1460 Holdable | [% vii.holdable %]
1461 Price | [% vii.price %]
1462 Barcode | [% vii.barcode %]
1463 Circulation Modifier | [% vii.circ_modifier %]
1464 Circulate As MARC Type | [% vii.circ_as_type %]
1465 Alert Message | [% vii.alert_message %]
1466 Public Note | [% vii.pub_note %]
1467 Private Note | [% vii.priv_note %]
1468 OPAC Visible | [% vii.opac_visible %]
1476 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1478 ,( 45, 'record.attributes')
1479 ,( 45, 'record.queue')
1480 ,( 45, 'record.queue.owner')
1483 INSERT INTO action_trigger.event_definition (
1498 'CSV Output for Import Items from Queued Bib Records',
1499 'vandelay.import_items.csv',
1502 'record.queue.owner',
1506 "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"
1507 [% 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('"', '""') %]"
1513 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1515 ,( 46, 'record.attributes')
1516 ,( 46, 'record.queue')
1517 ,( 46, 'record.queue.owner')
1520 INSERT INTO action_trigger.event_definition (
1535 'Email Output for Import Items from Queued Bib Records',
1536 'vandelay.import_items.email',
1539 'record.queue.owner',
1543 [%- SET user = target.0.record.queue.owner -%]
1544 To: [%- params.recipient_email || user.email || 'root@localhost' %]
1545 From: [%- params.sender_email || default_sender %]
1546 Subject: Import Items from Import Queue
1548 Queue ID: [% target.0.record.queue.id %]
1549 Queue Name: [% target.0.record.queue.name %]
1550 Queue Type: [% target.0.record.queue.queue_type %]
1551 Complete? [% target.0.record.queue.complete %]
1553 [% FOR vii IN target %]
1555 Import Item ID | [% vii.id %]
1556 Title of work | [% helpers.get_queued_bib_attr('title',vii.record.attributes) %]
1557 ISBN | [% helpers.get_queued_bib_attr('isbn',vii.record.attributes) %]
1558 Attribute Definition | [% vii.definition %]
1559 Import Error | [% vii.import_error %]
1560 Import Error Detail | [% vii.error_detail %]
1561 Owning Library | [% vii.owning_lib %]
1562 Circulating Library | [% vii.circ_lib %]
1563 Call Number | [% vii.call_number %]
1564 Copy Number | [% vii.copy_number %]
1565 Status | [% vii.status.name %]
1566 Shelving Location | [% vii.location.name %]
1567 Circulate | [% vii.circulate %]
1568 Deposit | [% vii.deposit %]
1569 Deposit Amount | [% vii.deposit_amount %]
1570 Reference | [% vii.ref %]
1571 Holdable | [% vii.holdable %]
1572 Price | [% vii.price %]
1573 Barcode | [% vii.barcode %]
1574 Circulation Modifier | [% vii.circ_modifier %]
1575 Circulate As MARC Type | [% vii.circ_as_type %]
1576 Alert Message | [% vii.alert_message %]
1577 Public Note | [% vii.pub_note %]
1578 Private Note | [% vii.priv_note %]
1579 OPAC Visible | [% vii.opac_visible %]
1586 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1588 ,( 47, 'record.attributes')
1589 ,( 47, 'record.queue')
1590 ,( 47, 'record.queue.owner')