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);
56 match_set INT REFERENCES vandelay.match_set (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
57 @@ -18,7 +60,8 @@ CREATE TABLE vandelay.queued_record (
60 quality INT NOT NULL DEFAULT 0
61 @@ -31,8 +74,7 @@ CREATE TABLE vandelay.bib_attr_definition (
62 - remove TEXT NOT NULL DEFAULT '',
63 - ident BOOL NOT NULL DEFAULT FALSE
64 remove TEXT NOT NULL DEFAULT ''
65 @@ -67,6 +109,11 @@ CREATE TABLE vandelay.import_item_attr_definition (
66 CREATE TABLE vandelay.import_error (
67 code TEXT PRIMARY KEY,
68 description TEXT NOT NULL -- i18n
71 @@ -75,9 +122,11 @@ CREATE TABLE vandelay.bib_queue (
72 - queue INT NOT NULL REFERENCES vandelay.bib_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
73 - bib_source INT REFERENCES config.bib_source (id) DEFERRABLE INITIALLY DEFERRED,
74 - imported_as BIGINT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED
75 queue INT NOT NULL REFERENCES vandelay.bib_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
76 bib_source INT REFERENCES config.bib_source (id) DEFERRABLE INITIALLY DEFERRED,
77 imported_as BIGINT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
78 import_error TEXT REFERENCES vandelay.import_error (code) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
80 @@ -92,17 +141,20 @@ CREATE INDEX queued_bib_record_attr_record_idx ON vandelay.queued_bib_record_att
81 - field_type TEXT NOT NULL CHECK (field_type in ('isbn','tcn_value','id')),
82 - matched_attr INT REFERENCES vandelay.queued_bib_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
83 - eg_record BIGINT REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
84 eg_record BIGINT REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
85 quality INT NOT NULL DEFAULT 1,
86 match_score INT NOT NULL DEFAULT 0
87 --- DROP TABLE vandelay.import_item CASCADE;
88 import_error TEXT REFERENCES vandelay.import_error (code) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
90 imported_as BIGINT REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED,
91 import_time TIMESTAMP WITH TIME ZONE,
92 @@ -139,10 +191,592 @@ CREATE TABLE vandelay.merge_profile (
97 CREATE OR REPLACE FUNCTION vandelay.marc21_record_type( marc TEXT ) RETURNS config.marc21_rec_type_map AS $func$
104 retval config.marc21_rec_type_map%ROWTYPE;
106 ldr := oils_xpath_string( '//*[local-name()="leader"]', marc );
108 IF ldr IS NULL OR ldr = '' THEN
109 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE code = 'BKS';
113 SELECT * INTO tval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'Type' LIMIT 1; -- They're all the same
114 SELECT * INTO bval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'BLvl' LIMIT 1; -- They're all the same
117 tval := SUBSTRING( ldr, tval_rec.start_pos + 1, tval_rec.length );
118 bval := SUBSTRING( ldr, bval_rec.start_pos + 1, bval_rec.length );
120 -- RAISE NOTICE 'type %, blvl %, ldr %', tval, bval, ldr;
122 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE type_val LIKE '%' || tval || '%' AND blvl_val LIKE '%' || bval || '%';
125 IF retval.code IS NULL THEN
126 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE code = 'BKS';
131 $func$ LANGUAGE PLPGSQL;
133 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field( marc TEXT, ff TEXT ) RETURNS TEXT AS $func$
140 rtype := (vandelay.marc21_record_type( marc )).code;
141 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = ff AND rec_type = rtype ORDER BY tag DESC LOOP
142 IF ff_pos.tag = 'ldr' THEN
143 val := oils_xpath_string('//*[local-name()="leader"]', marc);
144 IF val IS NOT NULL THEN
145 val := SUBSTRING( val, ff_pos.start_pos + 1, ff_pos.length );
149 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
150 val := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length );
154 val := REPEAT( ff_pos.default_val, ff_pos.length );
160 $func$ LANGUAGE PLPGSQL;
162 CREATE TYPE biblio.record_ff_map AS (record BIGINT, ff_name TEXT, ff_value TEXT);
163 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_all_fixed_fields( marc TEXT ) RETURNS SETOF biblio.record_ff_map AS $func$
168 output biblio.record_ff_map%ROWTYPE;
170 rtype := (vandelay.marc21_record_type( marc )).code;
172 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE rec_type = rtype ORDER BY tag DESC LOOP
173 output.ff_name := ff_pos.fixed_field;
174 output.ff_value := NULL;
176 IF ff_pos.tag = 'ldr' THEN
177 output.ff_value := oils_xpath_string('//*[local-name()="leader"]', marc);
178 IF output.ff_value IS NOT NULL THEN
179 output.ff_value := SUBSTRING( output.ff_value, ff_pos.start_pos + 1, ff_pos.length );
181 output.ff_value := NULL;
184 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
185 output.ff_value := SUBSTRING( tag_data, ff_pos.start_pos + 1, ff_pos.length );
186 IF output.ff_value IS NULL THEN output.ff_value := REPEAT( ff_pos.default_val, ff_pos.length ); END IF;
188 output.ff_value := NULL;
196 $func$ LANGUAGE PLPGSQL;
198 CREATE TYPE biblio.marc21_physical_characteristics AS ( id INT, record BIGINT, ptype TEXT, subfield INT, value INT );
199 CREATE OR REPLACE FUNCTION vandelay.marc21_physical_characteristics( marc TEXT) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$
203 ptype config.marc21_physical_characteristic_type_map%ROWTYPE;
204 psf config.marc21_physical_characteristic_subfield_map%ROWTYPE;
205 pval config.marc21_physical_characteristic_value_map%ROWTYPE;
206 retval biblio.marc21_physical_characteristics%ROWTYPE;
209 _007 := oils_xpath_string( '//*[@tag="007"]', marc );
211 IF _007 IS NOT NULL AND _007 <> '' THEN
212 SELECT * INTO ptype FROM config.marc21_physical_characteristic_type_map WHERE ptype_key = SUBSTRING( _007, 1, 1 );
214 IF ptype.ptype_key IS NOT NULL THEN
215 FOR psf IN SELECT * FROM config.marc21_physical_characteristic_subfield_map WHERE ptype_key = ptype.ptype_key LOOP
216 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 );
218 IF pval.id IS NOT NULL THEN
221 retval.ptype := ptype.ptype_key;
222 retval.subfield := psf.id;
223 retval.value := pval.id;
233 $func$ LANGUAGE PLPGSQL;
235 CREATE TYPE vandelay.flat_marc AS ( tag CHAR(3), ind1 TEXT, ind2 TEXT, subfield TEXT, value TEXT );
236 CREATE OR REPLACE FUNCTION vandelay.flay_marc ( TEXT ) RETURNS SETOF vandelay.flat_marc AS $func$
239 use MARC::File::XML (BinaryEncoding => 'UTF-8');
243 MARC::Charset->assume_unicode(1);
246 my $r = MARC::Record->new_from_xml( $xml );
248 return_next( { tag => 'LDR', value => $r->leader } );
250 for my $f ( $r->fields ) {
251 if ($f->is_control_field) {
252 return_next({ tag => $f->tag, value => $f->data });
254 for my $s ($f->subfields) {
257 ind1 => $f->indicator(1),
258 ind2 => $f->indicator(2),
263 if ( $f->tag eq '245' and $s->[0] eq 'a' ) {
264 my $trim = $f->indicator(2) || 0;
267 ind1 => $f->indicator(1),
268 ind2 => $f->indicator(2),
270 value => substr( $s->[1], $trim )
279 $func$ LANGUAGE PLPERLU;
281 CREATE OR REPLACE FUNCTION vandelay.flatten_marc ( marc TEXT ) RETURNS SETOF vandelay.flat_marc AS $func$
283 output vandelay.flat_marc%ROWTYPE;
286 FOR field IN SELECT * FROM vandelay.flay_marc( marc ) LOOP
287 output.ind1 := field.ind1;
288 output.ind2 := field.ind2;
289 output.tag := field.tag;
290 output.subfield := field.subfield;
291 IF field.subfield IS NOT NULL AND field.tag NOT IN ('020','022','024') THEN -- exclude standard numbers and control fields
292 output.value := naco_normalize(field.value, field.subfield);
294 output.value := field.value;
297 CONTINUE WHEN output.value IS NULL;
302 $func$ LANGUAGE PLPGSQL;
304 CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT, attr_defs TEXT[]) RETURNS hstore AS $_$
306 transformed_xml TEXT;
309 xfrm config.xml_transform%ROWTYPE;
311 new_attrs HSTORE := ''::HSTORE;
312 attr_def config.record_attr_definition%ROWTYPE;
315 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE name IN (SELECT * FROM UNNEST(attr_defs)) ORDER BY format LOOP
317 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
318 SELECT ARRAY_TO_STRING(ARRAY_ACCUM(x.value), COALESCE(attr_def.joiner,' ')) INTO attr_value
319 FROM vandelay.flatten_marc(xml) AS x
320 WHERE x.tag LIKE attr_def.tag
322 WHEN attr_def.sf_list IS NOT NULL
323 THEN POSITION(x.subfield IN attr_def.sf_list) > 0
330 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
331 attr_value := vandelay.marc21_extract_fixed_field(xml, attr_def.fixed_field);
333 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
335 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
337 -- See if we can skip the XSLT ... it's expensive
338 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
339 -- Can't skip the transform
340 IF xfrm.xslt <> '---' THEN
341 transformed_xml := oils_xslt_process(xml,xfrm.xslt);
343 transformed_xml := xml;
346 prev_xfrm := xfrm.name;
349 IF xfrm.name IS NULL THEN
350 -- just grab the marcxml (empty) transform
351 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
352 prev_xfrm := xfrm.name;
355 attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]);
357 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
358 SELECT value::TEXT INTO attr_value
359 FROM vandelay.marc21_physical_characteristics(xml)
360 WHERE subfield = attr_def.phys_char_sf
361 LIMIT 1; -- Just in case ...
365 -- apply index normalizers to attr_value
367 SELECT n.func AS func,
368 n.param_count AS param_count,
370 FROM config.index_normalizer n
371 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
372 WHERE attr = attr_def.name
374 EXECUTE 'SELECT ' || normalizer.func || '(' ||
375 quote_literal( attr_value ) ||
377 WHEN normalizer.param_count > 0
378 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
385 -- Add the new value to the hstore
386 new_attrs := new_attrs || hstore( attr_def.name, attr_value );
392 $_$ LANGUAGE PLPGSQL;
394 CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT ) RETURNS hstore AS $_$
395 SELECT vandelay.extract_rec_attrs( $1, (SELECT ARRAY_ACCUM(name) FROM config.record_attr_definition));
398 -- Everything between this comment and the beginning of the definition of
399 -- vandelay.match_bib_record() is strictly in service of that function.
400 CREATE TYPE vandelay.match_set_test_result AS (record BIGINT, quality INTEGER);
402 CREATE OR REPLACE FUNCTION vandelay.match_set_test_marcxml(
403 match_set_id INTEGER, record_xml TEXT
404 ) RETURNS SETOF vandelay.match_set_test_result AS $$
415 tags_rstore := vandelay.flatten_marc_hstore(record_xml);
416 svf_rstore := vandelay.extract_rec_attrs(record_xml);
418 CREATE TEMPORARY TABLE _vandelay_tmp_qrows (q INTEGER);
419 CREATE TEMPORARY TABLE _vandelay_tmp_jrows (j TEXT);
421 -- generate the where clause and return that directly (into wq), and as
422 -- a side-effect, populate the _vandelay_tmp_[qj]rows tables.
423 wq := vandelay.get_expr_from_match_set(match_set_id);
425 query_ := 'SELECT bre.id AS record, ';
427 -- qrows table is for the quality bits we add to the SELECT clause
428 SELECT ARRAY_TO_STRING(
429 ARRAY_ACCUM('COALESCE(n' || q::TEXT || '.quality, 0)'), ' + '
430 ) INTO coal FROM _vandelay_tmp_qrows;
432 -- our query string so far is the SELECT clause and the inital FROM.
433 -- no JOINs yet nor the WHERE clause
434 query_ := query_ || coal || ' AS quality ' || E'\n' ||
435 'FROM biblio.record_entry bre ';
437 -- jrows table is for the joins we must make (and the real text conditions)
438 SELECT ARRAY_TO_STRING(ARRAY_ACCUM(j), E'\n') INTO joins
439 FROM _vandelay_tmp_jrows;
441 -- add those joins and the where clause to our query.
442 query_ := query_ || joins || E'\n' || 'WHERE ' || wq || ' AND not bre.deleted';
444 -- this will return rows of record,quality
445 FOR rec IN EXECUTE query_ USING tags_rstore, svf_rstore LOOP
449 DROP TABLE _vandelay_tmp_qrows;
450 DROP TABLE _vandelay_tmp_jrows;
456 CREATE OR REPLACE FUNCTION vandelay.flatten_marc_hstore(
458 ) RETURNS HSTORE AS $$
462 ARRAY_ACCUM(tag || (COALESCE(subfield, ''))),
466 SELECT tag, subfield, ARRAY_ACCUM(value)::TEXT AS value
467 FROM vandelay.flatten_marc(record_xml)
468 GROUP BY tag, subfield ORDER BY tag, subfield
474 CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set(
478 root vandelay.match_set_point;
480 SELECT * INTO root FROM vandelay.match_set_point
481 WHERE parent IS NULL AND match_set = match_set_id;
483 RETURN vandelay.get_expr_from_match_set_point(root);
487 CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set_point(
488 node vandelay.match_set_point
495 child vandelay.match_set_point;
497 SELECT ARRAY_ACCUM(id) INTO children FROM vandelay.match_set_point
498 WHERE parent = node.id;
500 IF ARRAY_LENGTH(children, 1) > 0 THEN
501 this_op := vandelay._get_expr_render_one(node);
504 WHILE children[i] IS NOT NULL LOOP
505 SELECT * INTO child FROM vandelay.match_set_point
506 WHERE id = children[i];
508 q := q || ' ' || this_op || ' ';
511 q := q || vandelay.get_expr_from_match_set_point(child);
515 ELSIF node.bool_op IS NULL THEN
516 PERFORM vandelay._get_expr_push_qrow(node);
517 PERFORM vandelay._get_expr_push_jrow(node);
518 RETURN vandelay._get_expr_render_one(node);
525 CREATE OR REPLACE FUNCTION vandelay._get_expr_push_qrow(
526 node vandelay.match_set_point
530 INSERT INTO _vandelay_tmp_qrows (q) VALUES (node.id);
534 CREATE OR REPLACE FUNCTION vandelay._get_expr_push_jrow(
535 node vandelay.match_set_point
549 IF node.tag IS NOT NULL THEN
551 IF node.subfield IS NOT NULL THEN
552 tagkey := tagkey || node.subfield;
556 my_alias := 'n' || node.id::TEXT;
558 jrow := 'LEFT JOIN (SELECT *, ' || node.quality ||
559 ' AS quality FROM metabib.';
560 IF node.tag IS NOT NULL THEN
561 jrow := jrow || 'full_rec) ' || my_alias || ' ON (' ||
562 my_alias || '.record = bre.id AND ' || my_alias || '.tag = ''' ||
564 IF node.subfield IS NOT NULL THEN
565 jrow := jrow || ' AND ' || my_alias || '.subfield = ''' ||
566 node.subfield || '''';
568 jrow := jrow || ' AND (' || my_alias || '.value ' || op ||
569 ' ANY(($1->''' || tagkey || ''')::TEXT[])))';
571 jrow := jrow || 'record_attr) ' || my_alias || ' ON (' ||
572 my_alias || '.id = bre.id AND (' ||
573 my_alias || '.attrs->''' || node.svf ||
574 ''' ' || op || ' $2->''' || node.svf || '''))';
576 INSERT INTO _vandelay_tmp_jrows (j) VALUES (jrow);
580 CREATE OR REPLACE FUNCTION vandelay._get_expr_render_one(
581 node vandelay.match_set_point
586 IF node.bool_op IS NOT NULL THEN
589 RETURN '(n' || node.id::TEXT || '.id IS NOT NULL)';
594 CREATE OR REPLACE FUNCTION vandelay.match_bib_record() RETURNS TRIGGER AS $func$
596 incoming_existing_id TEXT;
597 test_result vandelay.match_set_test_result%ROWTYPE;
601 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
605 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
607 SELECT q.match_set INTO match_set FROM vandelay.bib_queue q WHERE q.id = NEW.queue;
609 IF match_set IS NOT NULL THEN
610 NEW.quality := vandelay.measure_record_quality( NEW.marc, match_set );
613 -- Perfect matches on 901$c exit early with a match with high quality.
614 incoming_existing_id :=
615 oils_xpath_string('//*[@tag="901"]/*[@code="c"][1]', NEW.marc);
617 IF incoming_existing_id IS NOT NULL AND incoming_existing_id != '' THEN
618 SELECT id INTO tmp_rec FROM biblio.record_entry WHERE id = incoming_existing_id::bigint;
619 IF tmp_rec IS NOT NULL THEN
620 INSERT INTO vandelay.bib_match (queued_record, eg_record, match_score, quality)
625 -- note: no match_set means quality==0
626 vandelay.measure_record_quality( b.marc, match_set )
627 FROM biblio.record_entry b
628 WHERE id = incoming_existing_id::bigint;
632 IF match_set IS NULL THEN
636 FOR test_result IN SELECT * FROM
637 vandelay.match_set_test_marcxml(match_set, NEW.marc) LOOP
639 INSERT INTO vandelay.bib_match ( queued_record, eg_record, match_score, quality )
644 vandelay.measure_record_quality( b.marc, match_set )
645 FROM biblio.record_entry b
646 WHERE id = test_result.record;
652 $func$ LANGUAGE PLPGSQL;
654 CREATE OR REPLACE FUNCTION vandelay.measure_record_quality ( xml TEXT, match_set_id INT ) RETURNS INT AS $_$
658 test vandelay.match_set_quality%ROWTYPE;
661 FOR test IN SELECT * FROM vandelay.match_set_quality WHERE match_set = match_set_id LOOP
662 IF test.tag IS NOT NULL THEN
663 FOR rvalue IN SELECT value FROM vandelay.flatten_marc( xml ) WHERE tag = test.tag AND subfield = test.subfield LOOP
664 IF test.value = rvalue THEN
665 out_q := out_q + test.quality;
669 IF test.value = vandelay.extract_rec_attrs(xml, ARRAY[test.svf]) -> test.svf THEN
670 out_q := out_q + test.quality;
677 $_$ LANGUAGE PLPGSQL;
680 CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
682 merge_profile vandelay.merge_profile%ROWTYPE;
683 dyn_profile vandelay.compile_profile%ROWTYPE;
693 SELECT q.marc INTO v_marc
694 FROM vandelay.queued_record q
695 JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
698 IF v_marc IS NULL THEN
699 -- RAISE NOTICE 'no marc for vandelay or bib record';
703 IF vandelay.template_overlay_bib_record( v_marc, eg_id, merge_profile_id) THEN
704 UPDATE vandelay.queued_bib_record
705 SET imported_as = eg_id,
707 WHERE id = import_id;
709 editor_string := (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1];
711 IF editor_string IS NOT NULL AND editor_string <> '' THEN
712 SELECT usr INTO editor_id FROM actor.card WHERE barcode = editor_string;
714 IF editor_id IS NULL THEN
715 SELECT id INTO editor_id FROM actor.usr WHERE usrname = editor_string;
718 IF editor_id IS NOT NULL THEN
719 UPDATE biblio.record_entry SET editor = editor_id WHERE id = eg_id;
726 -- RAISE NOTICE 'update of biblio.record_entry failed';
734 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 $$
737 lwm_ratio_value NUMERIC;
740 lwm_ratio_value := COALESCE(lwm_ratio_value_p, 0.0);
742 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
745 -- RAISE NOTICE 'already imported, cannot auto-overlay'
749 SELECT m.eg_record INTO eg_id
750 FROM vandelay.bib_match m
751 JOIN vandelay.queued_bib_record qr ON (m.queued_record = qr.id)
752 JOIN vandelay.bib_queue q ON (qr.queue = q.id)
753 JOIN biblio.record_entry r ON (r.id = m.eg_record)
754 WHERE m.queued_record = import_id
755 AND qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC >= lwm_ratio_value
756 ORDER BY m.match_score DESC, -- required match score
757 qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC DESC, -- quality tie breaker
758 m.id -- when in doubt, use the first match
761 IF eg_id IS NULL THEN
762 -- RAISE NOTICE 'incoming record is not of high enough quality';
766 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
770 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 $$
773 lwm_ratio_value NUMERIC;
776 lwm_ratio_value := COALESCE(lwm_ratio_value_p, 0.0);
778 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
781 -- RAISE NOTICE 'already imported, cannot auto-overlay'
785 SELECT m.eg_record INTO eg_id
786 FROM vandelay.bib_match m
787 JOIN vandelay.queued_bib_record qr ON (m.queued_record = qr.id)
788 JOIN vandelay.bib_queue q ON (qr.queue = q.id)
789 JOIN biblio.record_entry r ON (r.id = m.eg_record)
790 WHERE m.queued_record = import_id
791 AND qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC >= lwm_ratio_value
792 ORDER BY m.match_score DESC, -- required match score
793 qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC DESC, -- quality tie breaker
794 m.id -- when in doubt, use the first match
797 IF eg_id IS NULL THEN
798 -- RAISE NOTICE 'incoming record is not of high enough quality';
802 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
807 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 $$
809 queued_record vandelay.queued_bib_record%ROWTYPE;
812 FOR queued_record IN SELECT * FROM vandelay.queued_bib_record WHERE queue = queue_id AND import_time IS NULL LOOP
814 IF vandelay.auto_overlay_bib_record_with_best( queued_record.id, merge_profile_id, lwm_ratio_value ) THEN
815 RETURN NEXT queued_record.id;
825 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue_with_best ( import_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
826 SELECT vandelay.auto_overlay_bib_queue_with_best( $1, $2, p.lwm_ratio ) FROM vandelay.merge_profile p WHERE id = $2;
829 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$
835 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
839 FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP
841 SELECT extract_marc_field('vandelay.queued_bib_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_bib_record WHERE id = NEW.id;
842 IF (value IS NOT NULL AND value <> '') THEN
843 INSERT INTO vandelay.queued_bib_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
852 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
855 item_data vandelay.import_item%ROWTYPE;
858 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
862 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
864 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
865 INSERT INTO vandelay.import_item (
889 item_data.definition,
890 item_data.owning_lib,
892 item_data.call_number,
893 item_data.copy_number,
898 item_data.deposit_amount,
903 item_data.circ_modifier,
904 item_data.circ_as_type,
905 item_data.alert_message,
908 item_data.opac_visible
914 $func$ LANGUAGE PLPGSQL;
916 CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$
918 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
922 DELETE FROM vandelay.queued_bib_record_attr WHERE record = OLD.id;
923 DELETE FROM vandelay.import_item WHERE record = OLD.id;
925 IF TG_OP = 'UPDATE' THEN
936 -CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$
938 @@ -1200,7 +1818,7 @@ CREATE TRIGGER ingest_item_trigger
939 - AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
940 BEFORE INSERT OR UPDATE ON vandelay.queued_bib_record
941 @@ -1211,8 +1829,7 @@ CREATE TABLE vandelay.authority_attr_definition (
942 - remove TEXT NOT NULL DEFAULT '',
943 - ident BOOL NOT NULL DEFAULT FALSE
944 remove TEXT NOT NULL DEFAULT ''
945 @@ -1223,7 +1840,9 @@ ALTER TABLE vandelay.authority_queue ADD PRIMARY KEY (id);
946 - imported_as INT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
947 imported_as INT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
948 import_error TEXT REFERENCES vandelay.import_error (code) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
950 @@ -1238,9 +1857,9 @@ CREATE INDEX queued_authority_record_attr_record_idx ON vandelay.queued_authorit
951 - matched_attr INT REFERENCES vandelay.queued_authority_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
952 - eg_record BIGINT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
953 eg_record BIGINT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
954 quality INT NOT NULL DEFAULT 0
955 @@ -1249,6 +1868,10 @@ DECLARE
956 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
960 @@ -1264,6 +1887,10 @@ $$ LANGUAGE PLPGSQL;
961 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
964 @@ -372,6 +372,27 @@ CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry ( BIGINT ) RETURNS
966 CREATE OR REPLACE FUNCTION authority.flatten_marc ( rid BIGINT ) RETURNS SETOF authority.full_rec AS $func$
968 auth authority.record_entry%ROWTYPE;
969 output authority.full_rec%ROWTYPE;
972 SELECT INTO auth * FROM authority.record_entry WHERE id = rid;
974 FOR field IN SELECT * FROM vandelay.flatten_marc( auth.marc ) LOOP
975 output.record := rid;
976 output.ind1 := field.ind1;
977 output.ind2 := field.ind2;
978 output.tag := field.tag;
979 output.subfield := field.subfield;
980 output.value := field.value;
985 $func$ LANGUAGE PLPGSQL;
987 CREATE OR REPLACE FUNCTION biblio.flatten_marc ( rid BIGINT ) RETURNS SETOF metabib.full_rec AS $func$
989 bib biblio.record_entry%ROWTYPE;
990 output metabib.full_rec%ROWTYPE;
993 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
995 FOR field IN SELECT * FROM vandelay.flatten_marc( bib.marc ) LOOP
996 output.record := rid;
997 output.ind1 := field.ind1;
998 output.ind2 := field.ind2;
999 output.tag := field.tag;
1000 output.subfield := field.subfield;
1001 output.value := field.value;
1006 $func$ LANGUAGE PLPGSQL;
1009 CREATE OR REPLACE FUNCTION authority.flatten_marc ( rid BIGINT ) RETURNS SETOF authority.full_rec AS $func$
1011 auth authority.record_entry%ROWTYPE;
1012 output authority.full_rec%ROWTYPE;
1015 SELECT INTO auth * FROM authority.record_entry WHERE id = rid;
1017 FOR field IN SELECT * FROM vandelay.flatten_marc( auth.marc ) LOOP
1018 output.record := rid;
1019 output.ind1 := field.ind1;
1020 output.ind2 := field.ind2;
1021 output.tag := field.tag;
1022 output.subfield := field.subfield;
1023 output.value := field.value;
1028 $func$ LANGUAGE PLPGSQL;
1031 -----------------------------------------------
1032 -- Seed data for import errors
1033 -----------------------------------------------
1035 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'general.unknown', oils_i18n_gettext('general.unknown', 'Import or Overlay failed', 'vie', 'description') );
1036 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') );
1037 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') );
1038 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') );
1039 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') );
1040 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') );
1041 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') );
1042 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') );
1043 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'import.xml.malformed', oils_i18n_gettext('import.xml.malformed', 'Malformed record cause Import failure', 'vie', 'description') );
1044 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'overlay.xml.malformed', oils_i18n_gettext('overlay.xml.malformed', 'Malformed record cause Overlay failure', 'vie', 'description') );
1045 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'overlay.record.quality', oils_i18n_gettext('overlay.record.quality', 'New record had insufficient quality', 'vie', 'description') );
1048 ----------------------------------------------------------------
1049 -- Seed data for queued record/item exports
1050 ----------------------------------------------------------------
1052 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES (
1053 'vandelay.queued_bib_record.print',
1056 'vandelay.queued_bib_record.print',
1057 'Print output has been requested for records in an Importer Bib Queue.',
1064 'vandelay.queued_bib_record.csv',
1067 'vandelay.queued_bib_record.csv',
1068 'CSV output has been requested for records in an Importer Bib Queue.',
1075 'vandelay.queued_bib_record.email',
1078 'vandelay.queued_bib_record.email',
1079 'An email has been requested for records in an Importer Bib Queue.',
1086 'vandelay.queued_auth_record.print',
1089 'vandelay.queued_auth_record.print',
1090 'Print output has been requested for records in an Importer Authority Queue.',
1097 'vandelay.queued_auth_record.csv',
1100 'vandelay.queued_auth_record.csv',
1101 'CSV output has been requested for records in an Importer Authority Queue.',
1108 'vandelay.queued_auth_record.email',
1111 'vandelay.queued_auth_record.email',
1112 'An email has been requested for records in an Importer Authority Queue.',
1119 'vandelay.import_items.print',
1122 'vandelay.import_items.print',
1123 'Print output has been requested for Import Items from records in an Importer Bib Queue.',
1130 'vandelay.import_items.csv',
1133 'vandelay.import_items.csv',
1134 'CSV output has been requested for Import Items from records in an Importer Bib Queue.',
1141 'vandelay.import_items.email',
1144 'vandelay.import_items.email',
1145 'An email has been requested for Import Items from records in an Importer Bib Queue.',
1153 INSERT INTO action_trigger.event_definition (
1168 'Print Output for Queued Bib Records',
1169 'vandelay.queued_bib_record.print',
1177 Queue ID: [% target.0.queue.id %]
1178 Queue Name: [% target.0.queue.name %]
1179 Queue Type: [% target.0.queue.queue_type %]
1180 Complete? [% target.0.queue.complete %]
1182 [% FOR vqbr IN target %]
1184 Title of work | [% helpers.get_queued_bib_attr('title',vqbr.attributes) %]
1185 Author of work | [% helpers.get_queued_bib_attr('author',vqbr.attributes) %]
1186 Language of work | [% helpers.get_queued_bib_attr('language',vqbr.attributes) %]
1187 Pagination | [% helpers.get_queued_bib_attr('pagination',vqbr.attributes) %]
1188 ISBN | [% helpers.get_queued_bib_attr('isbn',vqbr.attributes) %]
1189 ISSN | [% helpers.get_queued_bib_attr('issn',vqbr.attributes) %]
1190 Price | [% helpers.get_queued_bib_attr('price',vqbr.attributes) %]
1191 Accession Number | [% helpers.get_queued_bib_attr('rec_identifier',vqbr.attributes) %]
1192 TCN Value | [% helpers.get_queued_bib_attr('eg_tcn',vqbr.attributes) %]
1193 TCN Source | [% helpers.get_queued_bib_attr('eg_tcn_source',vqbr.attributes) %]
1194 Internal ID | [% helpers.get_queued_bib_attr('eg_identifier',vqbr.attributes) %]
1195 Publisher | [% helpers.get_queued_bib_attr('publisher',vqbr.attributes) %]
1196 Publication Date | [% helpers.get_queued_bib_attr('pubdate',vqbr.attributes) %]
1197 Edition | [% helpers.get_queued_bib_attr('edition',vqbr.attributes) %]
1198 Item Barcode | [% helpers.get_queued_bib_attr('item_barcode',vqbr.attributes) %]
1206 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1211 INSERT INTO action_trigger.event_definition (
1226 'CSV Output for Queued Bib Records',
1227 'vandelay.queued_bib_record.csv',
1234 "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"
1235 [% 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('"', '""') %]"
1241 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1246 INSERT INTO action_trigger.event_definition (
1261 'Email Output for Queued Bib Records',
1262 'vandelay.queued_bib_record.email',
1269 [%- SET user = target.0.queue.owner -%]
1270 To: [%- params.recipient_email || user.email || 'root@localhost' %]
1271 From: [%- params.sender_email || default_sender %]
1272 Subject: Bibs from Import Queue
1274 Queue ID: [% target.0.queue.id %]
1275 Queue Name: [% target.0.queue.name %]
1276 Queue Type: [% target.0.queue.queue_type %]
1277 Complete? [% target.0.queue.complete %]
1279 [% FOR vqbr IN target %]
1281 Title of work | [% helpers.get_queued_bib_attr('title',vqbr.attributes) %]
1282 Author of work | [% helpers.get_queued_bib_attr('author',vqbr.attributes) %]
1283 Language of work | [% helpers.get_queued_bib_attr('language',vqbr.attributes) %]
1284 Pagination | [% helpers.get_queued_bib_attr('pagination',vqbr.attributes) %]
1285 ISBN | [% helpers.get_queued_bib_attr('isbn',vqbr.attributes) %]
1286 ISSN | [% helpers.get_queued_bib_attr('issn',vqbr.attributes) %]
1287 Price | [% helpers.get_queued_bib_attr('price',vqbr.attributes) %]
1288 Accession Number | [% helpers.get_queued_bib_attr('rec_identifier',vqbr.attributes) %]
1289 TCN Value | [% helpers.get_queued_bib_attr('eg_tcn',vqbr.attributes) %]
1290 TCN Source | [% helpers.get_queued_bib_attr('eg_tcn_source',vqbr.attributes) %]
1291 Internal ID | [% helpers.get_queued_bib_attr('eg_identifier',vqbr.attributes) %]
1292 Publisher | [% helpers.get_queued_bib_attr('publisher',vqbr.attributes) %]
1293 Publication Date | [% helpers.get_queued_bib_attr('pubdate',vqbr.attributes) %]
1294 Edition | [% helpers.get_queued_bib_attr('edition',vqbr.attributes) %]
1295 Item Barcode | [% helpers.get_queued_bib_attr('item_barcode',vqbr.attributes) %]
1303 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1306 ,( 40, 'queue.owner')
1309 INSERT INTO action_trigger.event_definition (
1324 'Print Output for Queued Authority Records',
1325 'vandelay.queued_auth_record.print',
1333 Queue ID: [% target.0.queue.id %]
1334 Queue Name: [% target.0.queue.name %]
1335 Queue Type: [% target.0.queue.queue_type %]
1336 Complete? [% target.0.queue.complete %]
1338 [% FOR vqar IN target %]
1340 Record Identifier | [% helpers.get_queued_auth_attr('rec_identifier',vqar.attributes) %]
1348 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1353 INSERT INTO action_trigger.event_definition (
1368 'CSV Output for Queued Authority Records',
1369 'vandelay.queued_auth_record.csv',
1377 [% FOR vqar IN target %]"[% helpers.get_queued_auth_attr('rec_identifier',vqar.attributes) | replace('"', '""') %]"
1383 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1388 INSERT INTO action_trigger.event_definition (
1403 'Email Output for Queued Authority Records',
1404 'vandelay.queued_auth_record.email',
1411 [%- SET user = target.0.queue.owner -%]
1412 To: [%- params.recipient_email || user.email || 'root@localhost' %]
1413 From: [%- params.sender_email || default_sender %]
1414 Subject: Authorities from Import Queue
1416 Queue ID: [% target.0.queue.id %]
1417 Queue Name: [% target.0.queue.name %]
1418 Queue Type: [% target.0.queue.queue_type %]
1419 Complete? [% target.0.queue.complete %]
1421 [% FOR vqar IN target %]
1423 Record Identifier | [% helpers.get_queued_auth_attr('rec_identifier',vqar.attributes) %]
1431 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1434 ,( 43, 'queue.owner')
1437 INSERT INTO action_trigger.event_definition (
1452 'Print Output for Import Items from Queued Bib Records',
1453 'vandelay.import_items.print',
1456 'record.queue.owner',
1461 Queue ID: [% target.0.record.queue.id %]
1462 Queue Name: [% target.0.record.queue.name %]
1463 Queue Type: [% target.0.record.queue.queue_type %]
1464 Complete? [% target.0.record.queue.complete %]
1466 [% FOR vii IN target %]
1468 Import Item ID | [% vii.id %]
1469 Title of work | [% helpers.get_queued_bib_attr('title',vii.record.attributes) %]
1470 ISBN | [% helpers.get_queued_bib_attr('isbn',vii.record.attributes) %]
1471 Attribute Definition | [% vii.definition %]
1472 Import Error | [% vii.import_error %]
1473 Import Error Detail | [% vii.error_detail %]
1474 Owning Library | [% vii.owning_lib %]
1475 Circulating Library | [% vii.circ_lib %]
1476 Call Number | [% vii.call_number %]
1477 Copy Number | [% vii.copy_number %]
1478 Status | [% vii.status.name %]
1479 Shelving Location | [% vii.location.name %]
1480 Circulate | [% vii.circulate %]
1481 Deposit | [% vii.deposit %]
1482 Deposit Amount | [% vii.deposit_amount %]
1483 Reference | [% vii.ref %]
1484 Holdable | [% vii.holdable %]
1485 Price | [% vii.price %]
1486 Barcode | [% vii.barcode %]
1487 Circulation Modifier | [% vii.circ_modifier %]
1488 Circulate As MARC Type | [% vii.circ_as_type %]
1489 Alert Message | [% vii.alert_message %]
1490 Public Note | [% vii.pub_note %]
1491 Private Note | [% vii.priv_note %]
1492 OPAC Visible | [% vii.opac_visible %]
1500 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1502 ,( 44, 'record.attributes')
1503 ,( 44, 'record.queue')
1504 ,( 44, 'record.queue.owner')
1507 INSERT INTO action_trigger.event_definition (
1522 'CSV Output for Import Items from Queued Bib Records',
1523 'vandelay.import_items.csv',
1526 'record.queue.owner',
1530 "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"
1531 [% 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('"', '""') %]"
1537 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1539 ,( 45, 'record.attributes')
1540 ,( 45, 'record.queue')
1541 ,( 45, 'record.queue.owner')
1544 INSERT INTO action_trigger.event_definition (
1559 'Email Output for Import Items from Queued Bib Records',
1560 'vandelay.import_items.email',
1563 'record.queue.owner',
1567 [%- SET user = target.0.record.queue.owner -%]
1568 To: [%- params.recipient_email || user.email || 'root@localhost' %]
1569 From: [%- params.sender_email || default_sender %]
1570 Subject: Import Items from Import Queue
1572 Queue ID: [% target.0.record.queue.id %]
1573 Queue Name: [% target.0.record.queue.name %]
1574 Queue Type: [% target.0.record.queue.queue_type %]
1575 Complete? [% target.0.record.queue.complete %]
1577 [% FOR vii IN target %]
1579 Import Item ID | [% vii.id %]
1580 Title of work | [% helpers.get_queued_bib_attr('title',vii.record.attributes) %]
1581 ISBN | [% helpers.get_queued_bib_attr('isbn',vii.record.attributes) %]
1582 Attribute Definition | [% vii.definition %]
1583 Import Error | [% vii.import_error %]
1584 Import Error Detail | [% vii.error_detail %]
1585 Owning Library | [% vii.owning_lib %]
1586 Circulating Library | [% vii.circ_lib %]
1587 Call Number | [% vii.call_number %]
1588 Copy Number | [% vii.copy_number %]
1589 Status | [% vii.status.name %]
1590 Shelving Location | [% vii.location.name %]
1591 Circulate | [% vii.circulate %]
1592 Deposit | [% vii.deposit %]
1593 Deposit Amount | [% vii.deposit_amount %]
1594 Reference | [% vii.ref %]
1595 Holdable | [% vii.holdable %]
1596 Price | [% vii.price %]
1597 Barcode | [% vii.barcode %]
1598 Circulation Modifier | [% vii.circ_modifier %]
1599 Circulate As MARC Type | [% vii.circ_as_type %]
1600 Alert Message | [% vii.alert_message %]
1601 Public Note | [% vii.pub_note %]
1602 Private Note | [% vii.priv_note %]
1603 OPAC Visible | [% vii.opac_visible %]
1610 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
1612 ,( 46, 'record.attributes')
1613 ,( 46, 'record.queue')
1614 ,( 46, 'record.queue.owner')