-- Evergreen DB patch 0572.vandelay-record-matching-and-quality.sql -- BEGIN; -- check whether patch can be applied SELECT evergreen.upgrade_deps_block_check('0572', :eg_version); 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; CREATE TABLE vandelay.match_set ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, owner INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE, mtype TEXT NOT NULL DEFAULT 'biblio', -- 'biblio','authority','mfhd'?, others? CONSTRAINT name_once_per_owner_mtype UNIQUE (name, owner, mtype) ); -- Table to define match points, either FF via SVF or tag+subfield CREATE TABLE vandelay.match_set_point ( id SERIAL PRIMARY KEY, match_set INT REFERENCES vandelay.match_set (id) ON DELETE CASCADE, parent INT REFERENCES vandelay.match_set_point (id), bool_op TEXT CHECK (bool_op IS NULL OR (bool_op IN ('AND','OR','NOT'))), svf TEXT REFERENCES config.record_attr_definition (name), tag TEXT, subfield TEXT, negate BOOL DEFAULT FALSE, quality INT NOT NULL DEFAULT 1, -- higher is better CONSTRAINT vmsp_need_a_subfield_with_a_tag CHECK ((tag IS NOT NULL AND subfield IS NOT NULL) OR tag IS NULL), CONSTRAINT vmsp_need_a_tag_or_a_ff_or_a_bo CHECK ( (tag IS NOT NULL AND svf IS NULL AND bool_op IS NULL) OR (tag IS NULL AND svf IS NOT NULL AND bool_op IS NULL) OR (tag IS NULL AND svf IS NULL AND bool_op IS NOT NULL) ) ); CREATE TABLE vandelay.match_set_quality ( id SERIAL PRIMARY KEY, match_set INT NOT NULL REFERENCES vandelay.match_set (id) ON DELETE CASCADE, svf TEXT REFERENCES config.record_attr_definition, tag TEXT, subfield TEXT, value TEXT NOT NULL, quality INT NOT NULL DEFAULT 1, -- higher is better CONSTRAINT vmsq_need_a_subfield_with_a_tag CHECK ((tag IS NOT NULL AND subfield IS NOT NULL) OR tag IS NULL), 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)) ); CREATE UNIQUE INDEX vmsq_def_once_per_set ON vandelay.match_set_quality (match_set, COALESCE(tag,''), COALESCE(subfield,''), COALESCE(svf,''), value); -- ALTER TABLEs... ALTER TABLE vandelay.queue ADD COLUMN match_set INT REFERENCES vandelay.match_set (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; ALTER TABLE vandelay.queued_record ADD COLUMN quality INT NOT NULL DEFAULT 0; ALTER TABLE vandelay.bib_attr_definition DROP COLUMN ident; CREATE TABLE vandelay.import_error ( code TEXT PRIMARY KEY, description TEXT NOT NULL -- i18n ); ALTER TABLE vandelay.queued_bib_record ADD COLUMN import_error TEXT REFERENCES vandelay.import_error (code) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, ADD COLUMN error_detail TEXT; ALTER TABLE vandelay.bib_match DROP COLUMN field_type, DROP COLUMN matched_attr, ADD COLUMN quality INT NOT NULL DEFAULT 1, ADD COLUMN match_score INT NOT NULL DEFAULT 0; ALTER TABLE vandelay.import_item ADD COLUMN import_error TEXT REFERENCES vandelay.import_error (code) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, ADD COLUMN error_detail TEXT, ADD COLUMN imported_as BIGINT REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED, ADD COLUMN import_time TIMESTAMP WITH TIME ZONE; ALTER TABLE vandelay.merge_profile ADD COLUMN lwm_ratio NUMERIC; CREATE OR REPLACE FUNCTION vandelay.marc21_record_type( marc TEXT ) RETURNS config.marc21_rec_type_map AS $func$ DECLARE ldr TEXT; tval TEXT; tval_rec RECORD; bval TEXT; bval_rec RECORD; retval config.marc21_rec_type_map%ROWTYPE; BEGIN ldr := oils_xpath_string( '//*[local-name()="leader"]', marc ); IF ldr IS NULL OR ldr = '' THEN SELECT * INTO retval FROM config.marc21_rec_type_map WHERE code = 'BKS'; RETURN retval; END IF; SELECT * INTO tval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'Type' LIMIT 1; -- They're all the same SELECT * INTO bval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'BLvl' LIMIT 1; -- They're all the same tval := SUBSTRING( ldr, tval_rec.start_pos + 1, tval_rec.length ); bval := SUBSTRING( ldr, bval_rec.start_pos + 1, bval_rec.length ); -- RAISE NOTICE 'type %, blvl %, ldr %', tval, bval, ldr; SELECT * INTO retval FROM config.marc21_rec_type_map WHERE type_val LIKE '%' || tval || '%' AND blvl_val LIKE '%' || bval || '%'; IF retval.code IS NULL THEN SELECT * INTO retval FROM config.marc21_rec_type_map WHERE code = 'BKS'; END IF; RETURN retval; END; $func$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field( marc TEXT, ff TEXT ) RETURNS TEXT AS $func$ DECLARE rtype TEXT; ff_pos RECORD; tag_data RECORD; val TEXT; BEGIN rtype := (vandelay.marc21_record_type( marc )).code; FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = ff AND rec_type = rtype ORDER BY tag DESC LOOP IF ff_pos.tag = 'ldr' THEN val := oils_xpath_string('//*[local-name()="leader"]', marc); IF val IS NOT NULL THEN val := SUBSTRING( val, ff_pos.start_pos + 1, ff_pos.length ); RETURN val; END IF; ELSE FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP val := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length ); RETURN val; END LOOP; END IF; val := REPEAT( ff_pos.default_val, ff_pos.length ); RETURN val; END LOOP; RETURN NULL; END; $func$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION vandelay.marc21_extract_all_fixed_fields( marc TEXT ) RETURNS SETOF biblio.record_ff_map AS $func$ DECLARE tag_data TEXT; rtype TEXT; ff_pos RECORD; output biblio.record_ff_map%ROWTYPE; BEGIN rtype := (vandelay.marc21_record_type( marc )).code; FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE rec_type = rtype ORDER BY tag DESC LOOP output.ff_name := ff_pos.fixed_field; output.ff_value := NULL; IF ff_pos.tag = 'ldr' THEN output.ff_value := oils_xpath_string('//*[local-name()="leader"]', marc); IF output.ff_value IS NOT NULL THEN output.ff_value := SUBSTRING( output.ff_value, ff_pos.start_pos + 1, ff_pos.length ); RETURN NEXT output; output.ff_value := NULL; END IF; ELSE FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP output.ff_value := SUBSTRING( tag_data, ff_pos.start_pos + 1, ff_pos.length ); IF output.ff_value IS NULL THEN output.ff_value := REPEAT( ff_pos.default_val, ff_pos.length ); END IF; RETURN NEXT output; output.ff_value := NULL; END LOOP; END IF; END LOOP; RETURN; END; $func$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION vandelay.marc21_physical_characteristics( marc TEXT) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$ DECLARE rowid INT := 0; _007 TEXT; ptype config.marc21_physical_characteristic_type_map%ROWTYPE; psf config.marc21_physical_characteristic_subfield_map%ROWTYPE; pval config.marc21_physical_characteristic_value_map%ROWTYPE; retval biblio.marc21_physical_characteristics%ROWTYPE; BEGIN _007 := oils_xpath_string( '//*[@tag="007"]', marc ); IF _007 IS NOT NULL AND _007 <> '' THEN SELECT * INTO ptype FROM config.marc21_physical_characteristic_type_map WHERE ptype_key = SUBSTRING( _007, 1, 1 ); IF ptype.ptype_key IS NOT NULL THEN FOR psf IN SELECT * FROM config.marc21_physical_characteristic_subfield_map WHERE ptype_key = ptype.ptype_key LOOP 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 ); IF pval.id IS NOT NULL THEN rowid := rowid + 1; retval.id := rowid; retval.ptype := ptype.ptype_key; retval.subfield := psf.id; retval.value := pval.id; RETURN NEXT retval; END IF; END LOOP; END IF; END IF; RETURN; END; $func$ LANGUAGE PLPGSQL; CREATE TYPE vandelay.flat_marc AS ( tag CHAR(3), ind1 TEXT, ind2 TEXT, subfield TEXT, value TEXT ); CREATE OR REPLACE FUNCTION vandelay.flay_marc ( TEXT ) RETURNS SETOF vandelay.flat_marc AS $func$ use MARC::Record; use MARC::File::XML (BinaryEncoding => 'UTF-8'); use MARC::Charset; use strict; MARC::Charset->assume_unicode(1); my $xml = shift; my $r = MARC::Record->new_from_xml( $xml ); return_next( { tag => 'LDR', value => $r->leader } ); for my $f ( $r->fields ) { if ($f->is_control_field) { return_next({ tag => $f->tag, value => $f->data }); } else { for my $s ($f->subfields) { return_next({ tag => $f->tag, ind1 => $f->indicator(1), ind2 => $f->indicator(2), subfield => $s->[0], value => $s->[1] }); if ( $f->tag eq '245' and $s->[0] eq 'a' ) { my $trim = $f->indicator(2) || 0; return_next({ tag => 'tnf', ind1 => $f->indicator(1), ind2 => $f->indicator(2), subfield => 'a', value => substr( $s->[1], $trim ) }); } } } } return undef; $func$ LANGUAGE PLPERLU; CREATE OR REPLACE FUNCTION vandelay.flatten_marc ( marc TEXT ) RETURNS SETOF vandelay.flat_marc AS $func$ DECLARE output vandelay.flat_marc%ROWTYPE; field RECORD; BEGIN FOR field IN SELECT * FROM vandelay.flay_marc( marc ) LOOP output.ind1 := field.ind1; output.ind2 := field.ind2; output.tag := field.tag; output.subfield := field.subfield; IF field.subfield IS NOT NULL AND field.tag NOT IN ('020','022','024') THEN -- exclude standard numbers and control fields output.value := naco_normalize(field.value, field.subfield); ELSE output.value := field.value; END IF; CONTINUE WHEN output.value IS NULL; RETURN NEXT output; END LOOP; END; $func$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT, attr_defs TEXT[]) RETURNS hstore AS $_$ DECLARE transformed_xml TEXT; prev_xfrm TEXT; normalizer RECORD; xfrm config.xml_transform%ROWTYPE; attr_value TEXT; new_attrs HSTORE := ''::HSTORE; attr_def config.record_attr_definition%ROWTYPE; BEGIN FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE name IN (SELECT * FROM UNNEST(attr_defs)) ORDER BY format LOOP IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection SELECT ARRAY_TO_STRING(ARRAY_ACCUM(x.value), COALESCE(attr_def.joiner,' ')) INTO attr_value FROM vandelay.flatten_marc(xml) AS x WHERE x.tag LIKE attr_def.tag AND CASE WHEN attr_def.sf_list IS NOT NULL THEN POSITION(x.subfield IN attr_def.sf_list) > 0 ELSE TRUE END GROUP BY x.tag ORDER BY x.tag LIMIT 1; ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field attr_value := vandelay.marc21_extract_fixed_field(xml, attr_def.fixed_field); ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format; -- See if we can skip the XSLT ... it's expensive IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN -- Can't skip the transform IF xfrm.xslt <> '---' THEN transformed_xml := oils_xslt_process(xml,xfrm.xslt); ELSE transformed_xml := xml; END IF; prev_xfrm := xfrm.name; END IF; IF xfrm.name IS NULL THEN -- just grab the marcxml (empty) transform SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1; prev_xfrm := xfrm.name; END IF; attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]); ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map SELECT m.value::TEXT INTO attr_value FROM vandelay.marc21_physical_characteristics(xml) v JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value) WHERE v.subfield = attr_def.phys_char_sf LIMIT 1; -- Just in case ... END IF; -- apply index normalizers to attr_value FOR normalizer IN SELECT n.func AS func, n.param_count AS param_count, m.params AS params FROM config.index_normalizer n JOIN config.record_attr_index_norm_map m ON (m.norm = n.id) WHERE attr = attr_def.name ORDER BY m.pos LOOP EXECUTE 'SELECT ' || normalizer.func || '(' || quote_literal( attr_value ) || CASE WHEN normalizer.param_count > 0 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'') ELSE '' END || ')' INTO attr_value; END LOOP; -- Add the new value to the hstore new_attrs := new_attrs || hstore( attr_def.name, attr_value ); END LOOP; RETURN new_attrs; END; $_$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT ) RETURNS hstore AS $_$ SELECT vandelay.extract_rec_attrs( $1, (SELECT ARRAY_ACCUM(name) FROM config.record_attr_definition)); $_$ LANGUAGE SQL; -- Everything between this comment and the beginning of the definition of -- vandelay.match_bib_record() is strictly in service of that function. CREATE TYPE vandelay.match_set_test_result AS (record BIGINT, quality INTEGER); CREATE OR REPLACE FUNCTION vandelay.match_set_test_marcxml( match_set_id INTEGER, record_xml TEXT ) RETURNS SETOF vandelay.match_set_test_result AS $$ DECLARE tags_rstore HSTORE; svf_rstore HSTORE; coal TEXT; joins TEXT; query_ TEXT; wq TEXT; qvalue INTEGER; rec RECORD; BEGIN tags_rstore := vandelay.flatten_marc_hstore(record_xml); svf_rstore := vandelay.extract_rec_attrs(record_xml); CREATE TEMPORARY TABLE _vandelay_tmp_qrows (q INTEGER); CREATE TEMPORARY TABLE _vandelay_tmp_jrows (j TEXT); -- generate the where clause and return that directly (into wq), and as -- a side-effect, populate the _vandelay_tmp_[qj]rows tables. wq := vandelay.get_expr_from_match_set(match_set_id); query_ := 'SELECT bre.id AS record, '; -- qrows table is for the quality bits we add to the SELECT clause SELECT ARRAY_TO_STRING( ARRAY_ACCUM('COALESCE(n' || q::TEXT || '.quality, 0)'), ' + ' ) INTO coal FROM _vandelay_tmp_qrows; -- our query string so far is the SELECT clause and the inital FROM. -- no JOINs yet nor the WHERE clause query_ := query_ || coal || ' AS quality ' || E'\n' || 'FROM biblio.record_entry bre '; -- jrows table is for the joins we must make (and the real text conditions) SELECT ARRAY_TO_STRING(ARRAY_ACCUM(j), E'\n') INTO joins FROM _vandelay_tmp_jrows; -- add those joins and the where clause to our query. query_ := query_ || joins || E'\n' || 'WHERE ' || wq || ' AND not bre.deleted'; -- this will return rows of record,quality FOR rec IN EXECUTE query_ USING tags_rstore, svf_rstore LOOP RETURN NEXT rec; END LOOP; DROP TABLE _vandelay_tmp_qrows; DROP TABLE _vandelay_tmp_jrows; RETURN; END; $$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION vandelay.flatten_marc_hstore( record_xml TEXT ) RETURNS HSTORE AS $$ BEGIN RETURN (SELECT HSTORE( ARRAY_ACCUM(tag || (COALESCE(subfield, ''))), ARRAY_ACCUM(value) ) FROM ( SELECT tag, subfield, ARRAY_ACCUM(value)::TEXT AS value FROM vandelay.flatten_marc(record_xml) GROUP BY tag, subfield ORDER BY tag, subfield ) subquery ); END; $$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set( match_set_id INTEGER ) RETURNS TEXT AS $$ DECLARE root vandelay.match_set_point; BEGIN SELECT * INTO root FROM vandelay.match_set_point WHERE parent IS NULL AND match_set = match_set_id; RETURN vandelay.get_expr_from_match_set_point(root); END; $$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set_point( node vandelay.match_set_point ) RETURNS TEXT AS $$ DECLARE q TEXT; i INTEGER; this_op TEXT; children INTEGER[]; child vandelay.match_set_point; BEGIN SELECT ARRAY_ACCUM(id) INTO children FROM vandelay.match_set_point WHERE parent = node.id; IF ARRAY_LENGTH(children, 1) > 0 THEN this_op := vandelay._get_expr_render_one(node); q := '('; i := 1; WHILE children[i] IS NOT NULL LOOP SELECT * INTO child FROM vandelay.match_set_point WHERE id = children[i]; IF i > 1 THEN q := q || ' ' || this_op || ' '; END IF; i := i + 1; q := q || vandelay.get_expr_from_match_set_point(child); END LOOP; q := q || ')'; RETURN q; ELSIF node.bool_op IS NULL THEN PERFORM vandelay._get_expr_push_qrow(node); PERFORM vandelay._get_expr_push_jrow(node); RETURN vandelay._get_expr_render_one(node); ELSE RETURN ''; END IF; END; $$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION vandelay._get_expr_push_qrow( node vandelay.match_set_point ) RETURNS VOID AS $$ DECLARE BEGIN INSERT INTO _vandelay_tmp_qrows (q) VALUES (node.id); END; $$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION vandelay._get_expr_push_jrow( node vandelay.match_set_point ) RETURNS VOID AS $$ DECLARE jrow TEXT; my_alias TEXT; op TEXT; tagkey TEXT; BEGIN IF node.negate THEN op := '<>'; ELSE op := '='; END IF; IF node.tag IS NOT NULL THEN tagkey := node.tag; IF node.subfield IS NOT NULL THEN tagkey := tagkey || node.subfield; END IF; END IF; my_alias := 'n' || node.id::TEXT; jrow := 'LEFT JOIN (SELECT *, ' || node.quality || ' AS quality FROM metabib.'; IF node.tag IS NOT NULL THEN jrow := jrow || 'full_rec) ' || my_alias || ' ON (' || my_alias || '.record = bre.id AND ' || my_alias || '.tag = ''' || node.tag || ''''; IF node.subfield IS NOT NULL THEN jrow := jrow || ' AND ' || my_alias || '.subfield = ''' || node.subfield || ''''; END IF; jrow := jrow || ' AND (' || my_alias || '.value ' || op || ' ANY(($1->''' || tagkey || ''')::TEXT[])))'; ELSE -- svf jrow := jrow || 'record_attr) ' || my_alias || ' ON (' || my_alias || '.id = bre.id AND (' || my_alias || '.attrs->''' || node.svf || ''' ' || op || ' $2->''' || node.svf || '''))'; END IF; INSERT INTO _vandelay_tmp_jrows (j) VALUES (jrow); END; $$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION vandelay._get_expr_render_one( node vandelay.match_set_point ) RETURNS TEXT AS $$ DECLARE s TEXT; BEGIN IF node.bool_op IS NOT NULL THEN RETURN node.bool_op; ELSE RETURN '(n' || node.id::TEXT || '.id IS NOT NULL)'; END IF; END; $$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION vandelay.match_bib_record() RETURNS TRIGGER AS $func$ DECLARE incoming_existing_id TEXT; test_result vandelay.match_set_test_result%ROWTYPE; tmp_rec BIGINT; match_set INT; BEGIN IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN RETURN NEW; END IF; DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id; SELECT q.match_set INTO match_set FROM vandelay.bib_queue q WHERE q.id = NEW.queue; IF match_set IS NOT NULL THEN NEW.quality := vandelay.measure_record_quality( NEW.marc, match_set ); END IF; -- Perfect matches on 901$c exit early with a match with high quality. incoming_existing_id := oils_xpath_string('//*[@tag="901"]/*[@code="c"][1]', NEW.marc); IF incoming_existing_id IS NOT NULL AND incoming_existing_id != '' THEN SELECT id INTO tmp_rec FROM biblio.record_entry WHERE id = incoming_existing_id::bigint; IF tmp_rec IS NOT NULL THEN INSERT INTO vandelay.bib_match (queued_record, eg_record, match_score, quality) SELECT NEW.id, b.id, 9999, -- note: no match_set means quality==0 vandelay.measure_record_quality( b.marc, match_set ) FROM biblio.record_entry b WHERE id = incoming_existing_id::bigint; END IF; END IF; IF match_set IS NULL THEN RETURN NEW; END IF; FOR test_result IN SELECT * FROM vandelay.match_set_test_marcxml(match_set, NEW.marc) LOOP INSERT INTO vandelay.bib_match ( queued_record, eg_record, match_score, quality ) SELECT NEW.id, test_result.record, test_result.quality, vandelay.measure_record_quality( b.marc, match_set ) FROM biblio.record_entry b WHERE id = test_result.record; END LOOP; RETURN NEW; END; $func$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION vandelay.measure_record_quality ( xml TEXT, match_set_id INT ) RETURNS INT AS $_$ DECLARE out_q INT := 0; rvalue TEXT; test vandelay.match_set_quality%ROWTYPE; BEGIN FOR test IN SELECT * FROM vandelay.match_set_quality WHERE match_set = match_set_id LOOP IF test.tag IS NOT NULL THEN FOR rvalue IN SELECT value FROM vandelay.flatten_marc( xml ) WHERE tag = test.tag AND subfield = test.subfield LOOP IF test.value = rvalue THEN out_q := out_q + test.quality; END IF; END LOOP; ELSE IF test.value = vandelay.extract_rec_attrs(xml, ARRAY[test.svf]) -> test.svf THEN out_q := out_q + test.quality; END IF; END IF; END LOOP; RETURN out_q; END; $_$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$ DECLARE merge_profile vandelay.merge_profile%ROWTYPE; dyn_profile vandelay.compile_profile%ROWTYPE; editor_string TEXT; editor_id INT; source_marc TEXT; target_marc TEXT; eg_marc TEXT; v_marc TEXT; replace_rule TEXT; BEGIN SELECT q.marc INTO v_marc FROM vandelay.queued_record q JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id) LIMIT 1; IF v_marc IS NULL THEN -- RAISE NOTICE 'no marc for vandelay or bib record'; RETURN FALSE; END IF; IF vandelay.template_overlay_bib_record( v_marc, eg_id, merge_profile_id) THEN UPDATE vandelay.queued_bib_record SET imported_as = eg_id, import_time = NOW() WHERE id = import_id; editor_string := (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1]; IF editor_string IS NOT NULL AND editor_string <> '' THEN SELECT usr INTO editor_id FROM actor.card WHERE barcode = editor_string; IF editor_id IS NULL THEN SELECT id INTO editor_id FROM actor.usr WHERE usrname = editor_string; END IF; IF editor_id IS NOT NULL THEN UPDATE biblio.record_entry SET editor = editor_id WHERE id = eg_id; END IF; END IF; RETURN TRUE; END IF; -- RAISE NOTICE 'update of biblio.record_entry failed'; RETURN FALSE; END; $$ LANGUAGE PLPGSQL; 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 $$ DECLARE eg_id BIGINT; lwm_ratio_value NUMERIC; BEGIN lwm_ratio_value := COALESCE(lwm_ratio_value_p, 0.0); PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id; IF FOUND THEN -- RAISE NOTICE 'already imported, cannot auto-overlay' RETURN FALSE; END IF; SELECT m.eg_record INTO eg_id FROM vandelay.bib_match m JOIN vandelay.queued_bib_record qr ON (m.queued_record = qr.id) JOIN vandelay.bib_queue q ON (qr.queue = q.id) JOIN biblio.record_entry r ON (r.id = m.eg_record) WHERE m.queued_record = import_id AND qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC >= lwm_ratio_value ORDER BY m.match_score DESC, -- required match score qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC DESC, -- quality tie breaker m.id -- when in doubt, use the first match LIMIT 1; IF eg_id IS NULL THEN -- RAISE NOTICE 'incoming record is not of high enough quality'; RETURN FALSE; END IF; RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id ); END; $$ LANGUAGE PLPGSQL; 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 $$ DECLARE eg_id BIGINT; lwm_ratio_value NUMERIC; BEGIN lwm_ratio_value := COALESCE(lwm_ratio_value_p, 0.0); PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id; IF FOUND THEN -- RAISE NOTICE 'already imported, cannot auto-overlay' RETURN FALSE; END IF; SELECT m.eg_record INTO eg_id FROM vandelay.bib_match m JOIN vandelay.queued_bib_record qr ON (m.queued_record = qr.id) JOIN vandelay.bib_queue q ON (qr.queue = q.id) JOIN biblio.record_entry r ON (r.id = m.eg_record) WHERE m.queued_record = import_id AND qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC >= lwm_ratio_value ORDER BY m.match_score DESC, -- required match score qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC DESC, -- quality tie breaker m.id -- when in doubt, use the first match LIMIT 1; IF eg_id IS NULL THEN -- RAISE NOTICE 'incoming record is not of high enough quality'; RETURN FALSE; END IF; RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id ); END; $$ LANGUAGE PLPGSQL; 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 $$ DECLARE queued_record vandelay.queued_bib_record%ROWTYPE; BEGIN FOR queued_record IN SELECT * FROM vandelay.queued_bib_record WHERE queue = queue_id AND import_time IS NULL LOOP IF vandelay.auto_overlay_bib_record_with_best( queued_record.id, merge_profile_id, lwm_ratio_value ) THEN RETURN NEXT queued_record.id; END IF; END LOOP; RETURN; END; $$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue_with_best ( import_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$ SELECT vandelay.auto_overlay_bib_queue_with_best( $1, $2, p.lwm_ratio ) FROM vandelay.merge_profile p WHERE id = $2; $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$ DECLARE value TEXT; atype TEXT; adef RECORD; BEGIN IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN RETURN NEW; END IF; FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP SELECT extract_marc_field('vandelay.queued_bib_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_bib_record WHERE id = NEW.id; IF (value IS NOT NULL AND value <> '') THEN INSERT INTO vandelay.queued_bib_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value); END IF; END LOOP; RETURN NULL; END; $$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$ DECLARE attr_def BIGINT; item_data vandelay.import_item%ROWTYPE; BEGIN IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN RETURN NEW; END IF; SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue; FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP INSERT INTO vandelay.import_item ( record, definition, owning_lib, circ_lib, call_number, copy_number, status, location, circulate, deposit, deposit_amount, ref, holdable, price, barcode, circ_modifier, circ_as_type, alert_message, pub_note, priv_note, opac_visible ) VALUES ( NEW.id, item_data.definition, item_data.owning_lib, item_data.circ_lib, item_data.call_number, item_data.copy_number, item_data.status, item_data.location, item_data.circulate, item_data.deposit, item_data.deposit_amount, item_data.ref, item_data.holdable, item_data.price, item_data.barcode, item_data.circ_modifier, item_data.circ_as_type, item_data.alert_message, item_data.pub_note, item_data.priv_note, item_data.opac_visible ); END LOOP; RETURN NULL; END; $func$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$ BEGIN IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN RETURN NEW; END IF; DELETE FROM vandelay.queued_bib_record_attr WHERE record = OLD.id; DELETE FROM vandelay.import_item WHERE record = OLD.id; IF TG_OP = 'UPDATE' THEN RETURN NEW; END IF; RETURN OLD; END; $$ LANGUAGE PLPGSQL; -- ALTER TABLEs... DROP TRIGGER zz_match_bibs_trigger ON vandelay.queued_bib_record; CREATE TRIGGER zz_match_bibs_trigger BEFORE INSERT OR UPDATE ON vandelay.queued_bib_record FOR EACH ROW EXECUTE PROCEDURE vandelay.match_bib_record(); CREATE OR REPLACE FUNCTION vandelay.ingest_authority_marc ( ) RETURNS TRIGGER AS $$ DECLARE value TEXT; atype TEXT; adef RECORD; BEGIN IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN RETURN NEW; END IF; FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP SELECT extract_marc_field('vandelay.queued_authority_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_authority_record WHERE id = NEW.id; IF (value IS NOT NULL AND value <> '') THEN INSERT INTO vandelay.queued_authority_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value); END IF; END LOOP; RETURN NULL; END; $$ LANGUAGE PLPGSQL; ALTER TABLE vandelay.authority_attr_definition DROP COLUMN ident; ALTER TABLE vandelay.queued_authority_record ADD COLUMN import_error TEXT REFERENCES vandelay.import_error (code) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, ADD COLUMN error_detail TEXT; ALTER TABLE vandelay.authority_match DROP COLUMN matched_attr; CREATE OR REPLACE FUNCTION vandelay.cleanup_authority_marc ( ) RETURNS TRIGGER AS $$ BEGIN IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN RETURN NEW; END IF; DELETE FROM vandelay.queued_authority_record_attr WHERE record = OLD.id; IF TG_OP = 'UPDATE' THEN RETURN NEW; END IF; RETURN OLD; END; $$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION authority.flatten_marc ( rid BIGINT ) RETURNS SETOF authority.full_rec AS $func$ DECLARE auth authority.record_entry%ROWTYPE; output authority.full_rec%ROWTYPE; field RECORD; BEGIN SELECT INTO auth * FROM authority.record_entry WHERE id = rid; FOR field IN SELECT * FROM vandelay.flatten_marc( auth.marc ) LOOP output.record := rid; output.ind1 := field.ind1; output.ind2 := field.ind2; output.tag := field.tag; output.subfield := field.subfield; output.value := field.value; RETURN NEXT output; END LOOP; END; $func$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION biblio.flatten_marc ( rid BIGINT ) RETURNS SETOF metabib.full_rec AS $func$ DECLARE bib biblio.record_entry%ROWTYPE; output metabib.full_rec%ROWTYPE; field RECORD; BEGIN SELECT INTO bib * FROM biblio.record_entry WHERE id = rid; FOR field IN SELECT * FROM vandelay.flatten_marc( bib.marc ) LOOP output.record := rid; output.ind1 := field.ind1; output.ind2 := field.ind2; output.tag := field.tag; output.subfield := field.subfield; output.value := field.value; RETURN NEXT output; END LOOP; END; $func$ LANGUAGE PLPGSQL; ----------------------------------------------- -- Seed data for import errors ----------------------------------------------- INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'general.unknown', oils_i18n_gettext('general.unknown', 'Import or Overlay failed', 'vie', 'description') ); 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') ); 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') ); 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') ); 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') ); 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') ); 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') ); 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') ); INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'import.xml.malformed', oils_i18n_gettext('import.xml.malformed', 'Malformed record cause Import failure', 'vie', 'description') ); INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'overlay.xml.malformed', oils_i18n_gettext('overlay.xml.malformed', 'Malformed record cause Overlay failure', 'vie', 'description') ); INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'overlay.record.quality', oils_i18n_gettext('overlay.record.quality', 'New record had insufficient quality', 'vie', 'description') ); ---------------------------------------------------------------- -- Seed data for queued record/item exports ---------------------------------------------------------------- INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ( 'vandelay.queued_bib_record.print', 'vqbr', oils_i18n_gettext( 'vandelay.queued_bib_record.print', 'Print output has been requested for records in an Importer Bib Queue.', 'ath', 'description' ), FALSE ) ,( 'vandelay.queued_bib_record.csv', 'vqbr', oils_i18n_gettext( 'vandelay.queued_bib_record.csv', 'CSV output has been requested for records in an Importer Bib Queue.', 'ath', 'description' ), FALSE ) ,( 'vandelay.queued_bib_record.email', 'vqbr', oils_i18n_gettext( 'vandelay.queued_bib_record.email', 'An email has been requested for records in an Importer Bib Queue.', 'ath', 'description' ), FALSE ) ,( 'vandelay.queued_auth_record.print', 'vqar', oils_i18n_gettext( 'vandelay.queued_auth_record.print', 'Print output has been requested for records in an Importer Authority Queue.', 'ath', 'description' ), FALSE ) ,( 'vandelay.queued_auth_record.csv', 'vqar', oils_i18n_gettext( 'vandelay.queued_auth_record.csv', 'CSV output has been requested for records in an Importer Authority Queue.', 'ath', 'description' ), FALSE ) ,( 'vandelay.queued_auth_record.email', 'vqar', oils_i18n_gettext( 'vandelay.queued_auth_record.email', 'An email has been requested for records in an Importer Authority Queue.', 'ath', 'description' ), FALSE ) ,( 'vandelay.import_items.print', 'vii', oils_i18n_gettext( 'vandelay.import_items.print', 'Print output has been requested for Import Items from records in an Importer Bib Queue.', 'ath', 'description' ), FALSE ) ,( 'vandelay.import_items.csv', 'vii', oils_i18n_gettext( 'vandelay.import_items.csv', 'CSV output has been requested for Import Items from records in an Importer Bib Queue.', 'ath', 'description' ), FALSE ) ,( 'vandelay.import_items.email', 'vii', oils_i18n_gettext( 'vandelay.import_items.email', 'An email has been requested for Import Items from records in an Importer Bib Queue.', 'ath', 'description' ), FALSE ) ; INSERT INTO action_trigger.event_definition ( id, active, owner, name, hook, validator, reactor, group_field, granularity, template ) VALUES ( 39, TRUE, 1, 'Print Output for Queued Bib Records', 'vandelay.queued_bib_record.print', 'NOOP_True', 'ProcessTemplate', 'queue.owner', 'print-on-demand', $$ [%- USE date -%]
Queue ID: [% target.0.queue.id %]
Queue Name: [% target.0.queue.name %]
Queue Type: [% target.0.queue.queue_type %]
Complete? [% target.0.queue.complete %]

    [% FOR vqbr IN target %]
=-=-=
 Title of work    | [% helpers.get_queued_bib_attr('title',vqbr.attributes) %]
 Author of work   | [% helpers.get_queued_bib_attr('author',vqbr.attributes) %]
 Language of work | [% helpers.get_queued_bib_attr('language',vqbr.attributes) %]
 Pagination       | [% helpers.get_queued_bib_attr('pagination',vqbr.attributes) %]
 ISBN             | [% helpers.get_queued_bib_attr('isbn',vqbr.attributes) %]
 ISSN             | [% helpers.get_queued_bib_attr('issn',vqbr.attributes) %]
 Price            | [% helpers.get_queued_bib_attr('price',vqbr.attributes) %]
 Accession Number | [% helpers.get_queued_bib_attr('rec_identifier',vqbr.attributes) %]
 TCN Value        | [% helpers.get_queued_bib_attr('eg_tcn',vqbr.attributes) %]
 TCN Source       | [% helpers.get_queued_bib_attr('eg_tcn_source',vqbr.attributes) %]
 Internal ID      | [% helpers.get_queued_bib_attr('eg_identifier',vqbr.attributes) %]
 Publisher        | [% helpers.get_queued_bib_attr('publisher',vqbr.attributes) %]
 Publication Date | [% helpers.get_queued_bib_attr('pubdate',vqbr.attributes) %]
 Edition          | [% helpers.get_queued_bib_attr('edition',vqbr.attributes) %]
 Item Barcode     | [% helpers.get_queued_bib_attr('item_barcode',vqbr.attributes) %]

    [% END %]
$$ ) ; INSERT INTO action_trigger.environment ( event_def, path) VALUES ( 39, 'attributes') ,( 39, 'queue') ; INSERT INTO action_trigger.event_definition ( id, active, owner, name, hook, validator, reactor, group_field, granularity, template ) VALUES ( 40, TRUE, 1, 'CSV Output for Queued Bib Records', 'vandelay.queued_bib_record.csv', 'NOOP_True', 'ProcessTemplate', 'queue.owner', 'print-on-demand', $$ [%- USE date -%] "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" [% 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('"', '""') %]" [% END %] $$ ) ; INSERT INTO action_trigger.environment ( event_def, path) VALUES ( 40, 'attributes') ,( 40, 'queue') ; INSERT INTO action_trigger.event_definition ( id, active, owner, name, hook, validator, reactor, group_field, granularity, template ) VALUES ( 41, TRUE, 1, 'Email Output for Queued Bib Records', 'vandelay.queued_bib_record.email', 'NOOP_True', 'SendEmail', 'queue.owner', NULL, $$ [%- USE date -%] [%- SET user = target.0.queue.owner -%] To: [%- params.recipient_email || user.email || 'root@localhost' %] From: [%- params.sender_email || default_sender %] Subject: Bibs from Import Queue Queue ID: [% target.0.queue.id %] Queue Name: [% target.0.queue.name %] Queue Type: [% target.0.queue.queue_type %] Complete? [% target.0.queue.complete %] [% FOR vqbr IN target %] =-=-= Title of work | [% helpers.get_queued_bib_attr('title',vqbr.attributes) %] Author of work | [% helpers.get_queued_bib_attr('author',vqbr.attributes) %] Language of work | [% helpers.get_queued_bib_attr('language',vqbr.attributes) %] Pagination | [% helpers.get_queued_bib_attr('pagination',vqbr.attributes) %] ISBN | [% helpers.get_queued_bib_attr('isbn',vqbr.attributes) %] ISSN | [% helpers.get_queued_bib_attr('issn',vqbr.attributes) %] Price | [% helpers.get_queued_bib_attr('price',vqbr.attributes) %] Accession Number | [% helpers.get_queued_bib_attr('rec_identifier',vqbr.attributes) %] TCN Value | [% helpers.get_queued_bib_attr('eg_tcn',vqbr.attributes) %] TCN Source | [% helpers.get_queued_bib_attr('eg_tcn_source',vqbr.attributes) %] Internal ID | [% helpers.get_queued_bib_attr('eg_identifier',vqbr.attributes) %] Publisher | [% helpers.get_queued_bib_attr('publisher',vqbr.attributes) %] Publication Date | [% helpers.get_queued_bib_attr('pubdate',vqbr.attributes) %] Edition | [% helpers.get_queued_bib_attr('edition',vqbr.attributes) %] Item Barcode | [% helpers.get_queued_bib_attr('item_barcode',vqbr.attributes) %] [% END %] $$ ) ; INSERT INTO action_trigger.environment ( event_def, path) VALUES ( 41, 'attributes') ,( 41, 'queue') ,( 41, 'queue.owner') ; INSERT INTO action_trigger.event_definition ( id, active, owner, name, hook, validator, reactor, group_field, granularity, template ) VALUES ( 42, TRUE, 1, 'Print Output for Queued Authority Records', 'vandelay.queued_auth_record.print', 'NOOP_True', 'ProcessTemplate', 'queue.owner', 'print-on-demand', $$ [%- USE date -%]
Queue ID: [% target.0.queue.id %]
Queue Name: [% target.0.queue.name %]
Queue Type: [% target.0.queue.queue_type %]
Complete? [% target.0.queue.complete %]

    [% FOR vqar IN target %]
=-=-=
 Record Identifier | [% helpers.get_queued_auth_attr('rec_identifier',vqar.attributes) %]

    [% END %]
$$ ) ; INSERT INTO action_trigger.environment ( event_def, path) VALUES ( 42, 'attributes') ,( 42, 'queue') ; INSERT INTO action_trigger.event_definition ( id, active, owner, name, hook, validator, reactor, group_field, granularity, template ) VALUES ( 43, TRUE, 1, 'CSV Output for Queued Authority Records', 'vandelay.queued_auth_record.csv', 'NOOP_True', 'ProcessTemplate', 'queue.owner', 'print-on-demand', $$ [%- USE date -%] "Record Identifier" [% FOR vqar IN target %]"[% helpers.get_queued_auth_attr('rec_identifier',vqar.attributes) | replace('"', '""') %]" [% END %] $$ ) ; INSERT INTO action_trigger.environment ( event_def, path) VALUES ( 43, 'attributes') ,( 43, 'queue') ; INSERT INTO action_trigger.event_definition ( id, active, owner, name, hook, validator, reactor, group_field, granularity, template ) VALUES ( 44, TRUE, 1, 'Email Output for Queued Authority Records', 'vandelay.queued_auth_record.email', 'NOOP_True', 'SendEmail', 'queue.owner', NULL, $$ [%- USE date -%] [%- SET user = target.0.queue.owner -%] To: [%- params.recipient_email || user.email || 'root@localhost' %] From: [%- params.sender_email || default_sender %] Subject: Authorities from Import Queue Queue ID: [% target.0.queue.id %] Queue Name: [% target.0.queue.name %] Queue Type: [% target.0.queue.queue_type %] Complete? [% target.0.queue.complete %] [% FOR vqar IN target %] =-=-= Record Identifier | [% helpers.get_queued_auth_attr('rec_identifier',vqar.attributes) %] [% END %] $$ ) ; INSERT INTO action_trigger.environment ( event_def, path) VALUES ( 44, 'attributes') ,( 44, 'queue') ,( 44, 'queue.owner') ; INSERT INTO action_trigger.event_definition ( id, active, owner, name, hook, validator, reactor, group_field, granularity, template ) VALUES ( 45, TRUE, 1, 'Print Output for Import Items from Queued Bib Records', 'vandelay.import_items.print', 'NOOP_True', 'ProcessTemplate', 'record.queue.owner', 'print-on-demand', $$ [%- USE date -%]
Queue ID: [% target.0.record.queue.id %]
Queue Name: [% target.0.record.queue.name %]
Queue Type: [% target.0.record.queue.queue_type %]
Complete? [% target.0.record.queue.complete %]

    [% FOR vii IN target %]
=-=-=
 Import Item ID         | [% vii.id %]
 Title of work          | [% helpers.get_queued_bib_attr('title',vii.record.attributes) %]
 ISBN                   | [% helpers.get_queued_bib_attr('isbn',vii.record.attributes) %]
 Attribute Definition   | [% vii.definition %]
 Import Error           | [% vii.import_error %]
 Import Error Detail    | [% vii.error_detail %]
 Owning Library         | [% vii.owning_lib %]
 Circulating Library    | [% vii.circ_lib %]
 Call Number            | [% vii.call_number %]
 Copy Number            | [% vii.copy_number %]
 Status                 | [% vii.status.name %]
 Shelving Location      | [% vii.location.name %]
 Circulate              | [% vii.circulate %]
 Deposit                | [% vii.deposit %]
 Deposit Amount         | [% vii.deposit_amount %]
 Reference              | [% vii.ref %]
 Holdable               | [% vii.holdable %]
 Price                  | [% vii.price %]
 Barcode                | [% vii.barcode %]
 Circulation Modifier   | [% vii.circ_modifier %]
 Circulate As MARC Type | [% vii.circ_as_type %]
 Alert Message          | [% vii.alert_message %]
 Public Note            | [% vii.pub_note %]
 Private Note           | [% vii.priv_note %]
 OPAC Visible           | [% vii.opac_visible %]

    [% END %]
$$ ) ; INSERT INTO action_trigger.environment ( event_def, path) VALUES ( 45, 'record') ,( 45, 'record.attributes') ,( 45, 'record.queue') ,( 45, 'record.queue.owner') ; INSERT INTO action_trigger.event_definition ( id, active, owner, name, hook, validator, reactor, group_field, granularity, template ) VALUES ( 46, TRUE, 1, 'CSV Output for Import Items from Queued Bib Records', 'vandelay.import_items.csv', 'NOOP_True', 'ProcessTemplate', 'record.queue.owner', 'print-on-demand', $$ [%- USE date -%] "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" [% 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('"', '""') %]" [% END %] $$ ) ; INSERT INTO action_trigger.environment ( event_def, path) VALUES ( 46, 'record') ,( 46, 'record.attributes') ,( 46, 'record.queue') ,( 46, 'record.queue.owner') ; INSERT INTO action_trigger.event_definition ( id, active, owner, name, hook, validator, reactor, group_field, granularity, template ) VALUES ( 47, TRUE, 1, 'Email Output for Import Items from Queued Bib Records', 'vandelay.import_items.email', 'NOOP_True', 'SendEmail', 'record.queue.owner', NULL, $$ [%- USE date -%] [%- SET user = target.0.record.queue.owner -%] To: [%- params.recipient_email || user.email || 'root@localhost' %] From: [%- params.sender_email || default_sender %] Subject: Import Items from Import Queue Queue ID: [% target.0.record.queue.id %] Queue Name: [% target.0.record.queue.name %] Queue Type: [% target.0.record.queue.queue_type %] Complete? [% target.0.record.queue.complete %] [% FOR vii IN target %] =-=-= Import Item ID | [% vii.id %] Title of work | [% helpers.get_queued_bib_attr('title',vii.record.attributes) %] ISBN | [% helpers.get_queued_bib_attr('isbn',vii.record.attributes) %] Attribute Definition | [% vii.definition %] Import Error | [% vii.import_error %] Import Error Detail | [% vii.error_detail %] Owning Library | [% vii.owning_lib %] Circulating Library | [% vii.circ_lib %] Call Number | [% vii.call_number %] Copy Number | [% vii.copy_number %] Status | [% vii.status.name %] Shelving Location | [% vii.location.name %] Circulate | [% vii.circulate %] Deposit | [% vii.deposit %] Deposit Amount | [% vii.deposit_amount %] Reference | [% vii.ref %] Holdable | [% vii.holdable %] Price | [% vii.price %] Barcode | [% vii.barcode %] Circulation Modifier | [% vii.circ_modifier %] Circulate As MARC Type | [% vii.circ_as_type %] Alert Message | [% vii.alert_message %] Public Note | [% vii.pub_note %] Private Note | [% vii.priv_note %] OPAC Visible | [% vii.opac_visible %] [% END %] $$ ) ; INSERT INTO action_trigger.environment ( event_def, path) VALUES ( 47, 'record') ,( 47, 'record.attributes') ,( 47, 'record.queue') ,( 47, 'record.queue.owner') ; COMMIT;