From d2b047058e99da0ce2c944643a03b432beb4dda5 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Tue, 14 Jan 2014 16:28:04 -0500 Subject: [PATCH] LP#1269911: Database elements of MVF and CRA * Teach vandelay.marc21_physical_characteristics() to see all 007's We use vandelay.marc21_physical_characteristics() to extract fixed field data that lives in the 007. Before this change, it would only look at the first 007 in the record. Now it will look at all of them in turn, supporting configurations such as DVD+BluRay. * Add intarray extension We need intarray for GIN index support of integer arrays, which is how we'll be storing the in-use record attribute value identifiers. * Hidy hole in which to stick "uncontrolled" values In order to make use of the massive speed increases provided by intarray indexing, we need to use (you guessed it) integers. But uncontrolled record attributes are not necessarily (or even very often) numbers. We will store them in a table of unique (per attribute) values, and use the id from that table in our intarray indexing. That id comes from a DECREMENTING serial that starts at -1 and counts downward. This avoids collision with the other set of integers (the id from config.coded_value_map) that we will use for controlled record attribute values. * Add a multi flag for record attrs We pre-coordinate which record attrs are allowed to be multi-valued with this new bool. Most can be, we set the default to true and adjust the seed data for those that should be false (sorters and fields in the leader). * New intarray-focused attribute extraction We rewrite the record attribute extraction to capture all the record-supplied values for each attribute (where multi is true) and store that list in the new (fkey-corrected) metabib.record_attr_vector_list table. Only filters make it into this table. We also insert a parameter after the record id to accept a list of record attributes we want to rewrite. This defaults to NULL to rewrite all of them. Sorters are stored in a new, separate table built specifically for them. metabib.record_attr becomes a vew atop metabib.record_attr_vector_list which expands the intarray stored therein into an hstore. For multi=true attributes, only one will be returned through this view, as is HSTORE's way, and which will be returned is undefined. However this view is only provided for the purpose of backward compat with reports or other locally defined logic. And, finally, baseline seed data Signed-off-by: Mike Rylander Signed-off-by: Dan Wells --- Open-ILS/src/sql/Pg/002.schema.config.sql | 7 + Open-ILS/src/sql/Pg/012.schema.vandelay.sql | 40 +- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 433 ++++++++++++++---- Open-ILS/src/sql/Pg/950.data.seed-values.sql | 197 +++++++- .../src/sql/Pg/create_database_extensions.sql | 1 + 5 files changed, 560 insertions(+), 118 deletions(-) diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 8e196d3c08..ef517e93c2 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -770,8 +770,10 @@ CREATE TABLE config.record_attr_definition ( name TEXT PRIMARY KEY, label TEXT NOT NULL, -- I18N description TEXT, + multi BOOL NOT NULL DEFAULT TRUE, -- will store all values from a record filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true + composite BOOL NOT NULL DEFAULT FALSE, -- its values are derived from others -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering tag TEXT, -- LIKE format @@ -849,6 +851,11 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; +CREATE TABLE config.composite_attr_entry_definition( + coded_value PRIMARY KEY NOT NULL REFERENCES config.coded_value_map (id) ON UPDATE CASCADE ON DELETE CASCADE, + definition TEXT NOT NULL -- JSON +); + -- List applied db patches that are deprecated by (and block the application of) my_db_patch CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$ SELECT DISTINCT l.version diff --git a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql index a57c6f96de..5076037d52 100644 --- a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql +++ b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql @@ -302,27 +302,27 @@ DECLARE 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; + FOR _007 IN SELECT oils_xpath_string('//*', value) FROM UNNEST(oils_xpath('//*[@tag="007"]', marc)) x(value) LOOP + 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; - END IF; + END LOOP; RETURN; END; diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index d81525e8cd..fc0e454c39 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -268,6 +268,82 @@ CREATE TRIGGER facet_force_nfc_tgr BEFORE UPDATE OR INSERT ON metabib.facet_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.facet_force_nfc(); +-- DECREMENTING serial starts at -1 +CREATE SEQUENCE metabib.uncontrolled_record_attr_value_id_seq INCREMENT BY -1; + +CREATE TABLE metabib.uncontrolled_record_attr_value ( + id BIGINT PRIMARY KEY DEFAULT nextval('metabib.uncontrolled_record_attr_value_id_seq'), + attr TEXT NOT NULL REFERENCES config.record_attr_definition (name), + value TEXT NOT NULL +); +CREATE UNIQUE INDEX muv_once_idx ON metabib.uncontrolled_record_attr_value (attr,value); + +CREATE VIEW metabib.record_attr_id_map AS + SELECT id, attr, value FROM metabib.uncontrolled_record_attr_value + UNION + SELECT c.id, c.ctype AS attr, c.code AS value + FROM config.coded_value_map c + JOIN config.record_attr_definition d ON (d.name = c.ctype AND NOT d.composite); + +CREATE VIEW metabib.composite_attr_id_map AS + SELECT c.id, c.ctype AS attr, c.code AS value + FROM config.coded_value_map c + JOIN config.record_attr_definition d ON (d.name = c.ctype AND d.composite); + +CREATE VIEW metabib.full_attr_id_map AS + SELECT id, attr, value FROM metabib.record_attr_id_map + UNION + SELECT id, attr, value FROM metabib.composite_attr_id_map; + + +CREATE FUNCTION metabib.compile_composite_attr ( cattr_id INT ) RETURNS query_int AS $func$ + + use JSON; + my $cid = shift; + + my $cattr = spi_exec_query( + "SELECT * FROM config.composite_attr_entry_defintion WHERE id = $cid" + )->{rows}[0]; + + die("Composite attribute not found with an id of $cid") unless $cattr; + + my $plan = spi_prepare('SELECT * FROM metabib.full_attr_id_map WHERE attr = $1 AND value = $2', qw/TEXT TEXT/); + my $def = from_json $cattr->{definition}; + + sub recurse { + my $d = shift; + my $j = '&'; + my @list; + + if (ref $d eq 'HASH') { # node or AND + if (exists $d->{_attr}) { # it is a node + return spi_query_prepared( + $plan, {limit => 1}, $d->{_attr}, $d->{_val} + )->{rows}[0]{id}; + } elsif (exists $d->{_not} && scalar(keys(%$d)) == 1) { # it is a NOT + return '!' . recurse($$d{_not}); + } else { # an AND list + @list = map { recurse($$d{$_}) } sort keys %$d; + } + } elsif (ref $d eq 'ARRAY') + $j = '|' + @list = map { recurse($_) } @$d; + } + return '(' . join($j,@list) . ')' if @list; + return ''; + } + + return recurse($def); + +$func$ STRICT STABLE IMMUTABLE LANGUAGE plperlu; + +CREATE TABLE metabib.record_attr_vector_list ( + source BIGINT PRIMARY KEY REFERNECES biblio.record_entry (id), + vlist INT[] NOT NULL -- stores id from ccvm AND murav +); +CREATE INDEX mrca_vlist_idx ON metabib.record_attr_vector_list USING gin ( vlist gin__int_ops ); + +/* This becomes a view, and we do sorters differently ... CREATE TABLE metabib.record_attr ( id BIGINT PRIMARY KEY REFERENCES biblio.record_entry (id) ON DELETE CASCADE, attrs HSTORE NOT NULL DEFAULT ''::HSTORE @@ -275,8 +351,37 @@ CREATE TABLE metabib.record_attr ( CREATE INDEX metabib_svf_attrs_idx ON metabib.record_attr USING GIST (attrs); CREATE INDEX metabib_svf_date1_idx ON metabib.record_attr ((attrs->'date1')); CREATE INDEX metabib_svf_dates_idx ON metabib.record_attr ((attrs->'date1'),(attrs->'date2')); +*/ + +/* ... like this */ +CREATE TABLE metabib.record_sorter ( + id BIGSERIAL PRIMARY KEY, + source BIGINT NOT NULL REFERENCES biblio.record_entry (id) ON DELETE CASCADE, + attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE, + value TEXT NOT NULL +); +CREATE INDEX metabib_sorter_source_idx ON metabib.record_sorter (source); -- we may not need one of this or the next ... stats will tell +CREATE INDEX metabib_sorter_s_a_idx ON metabib.record_sorter (source, attr); +CREATE INDEX metabib_sorter_a_v_idx ON metabib.record_sorter (attr, value); + + +CREATE TYPE metabib.record_attr_type AS ( + id BIGINT, + attrs HSTORE +); --- Back-compat view ... we're moving to an HSTORE world +-- Back-compat view ... we're moving to an INTARRAY world +CREATE VIEW metabib.record_attr_flat AS + SELECT v.source AS id, + m.attr, + m.value + FROM metabib.full_attr_id_map m + JOIN metabib.record_attr_vector_list v ( m.id = ANY( v.vlist ) ); + +CREATE VIEW metabib.record_attr AS + SELECT id, HSTORE( ARRAY_AGG( attr ), ARRAY_AGG( value ) ) AS attrs FROM metabib.record_attr_flat GROUP BY 1; + +-- Back-back-compat view ... we use to live in an HSTORE world CREATE TYPE metabib.rec_desc_type AS ( item_type TEXT, item_form TEXT, @@ -832,6 +937,36 @@ CREATE OR REPLACE FUNCTION biblio.marc21_record_type( rid BIGINT ) RETURNS confi SELECT * FROM vandelay.marc21_record_type( (SELECT marc FROM biblio.record_entry WHERE id = $1) ); $func$ LANGUAGE SQL; +CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field_list( marc TEXT, ff TEXT ) RETURNS TEXT[] AS $func$ +DECLARE + rtype TEXT; + ff_pos RECORD; + tag_data RECORD; + val TEXT; + collection TEXT[] := '{}'::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 ); + collection := collection || 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 ); + collection := collection || val; + END LOOP; + END IF; + val := REPEAT( ff_pos.default_val, ff_pos.length ); + collection := collection || val; + END LOOP; + + RETURN collection; +END; +$func$ LANGUAGE PLPGSQL; + CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field( marc TEXT, ff TEXT ) RETURNS TEXT AS $func$ DECLARE rtype TEXT; @@ -861,6 +996,10 @@ BEGIN END; $func$ LANGUAGE PLPGSQL; +CREATE OR REPLACE FUNCTION biblio.marc21_extract_fixed_field_list( rid BIGINT, ff TEXT ) RETURNS TEXT[] AS $func$ + SELECT * FROM vandelay.marc21_extract_fixed_field_list( (SELECT marc FROM biblio.record_entry WHERE id = $1), $2 ); +$func$ LANGUAGE SQL; + CREATE OR REPLACE FUNCTION biblio.marc21_extract_fixed_field( rid BIGINT, ff TEXT ) RETURNS TEXT AS $func$ SELECT * FROM vandelay.marc21_extract_fixed_field( (SELECT marc FROM biblio.record_entry WHERE id = $1), $2 ); $func$ LANGUAGE SQL; @@ -1250,16 +1389,213 @@ CREATE OR REPLACE FUNCTION biblio.map_authority_linking (bibid BIGINT, marc TEXT SELECT $1; $func$ LANGUAGE SQL; --- AFTER UPDATE OR INSERT trigger for biblio.record_entry -CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$ +CREATE OR REPLACE FUNCTION metabib.reingest_record_attributes (rid BIGINT, pattr_list TEXT[] DEFAULT NULL, prmarc TEXT DEFAULT NULL, rdeleted BOOL DEFAULT TRUE) RETURNS VOID AS $func$ DECLARE transformed_xml TEXT; + rmarc TEXT := prmarc; + tmp_val TEXT; prev_xfrm TEXT; normalizer RECORD; xfrm config.xml_transform%ROWTYPE; - attr_value TEXT; - new_attrs HSTORE := ''::HSTORE; + attr_vector INT[] := '{}'::INT[]; + attr_vector_tmp INT[]; + attr_list TEXT[] := pattr_list; + attr_value TEXT[]; + norm_attr_value TEXT[]; + tmp_xml XML; attr_def config.record_attr_definition%ROWTYPE; + ccvm_row config.code_value_map%ROWTYPE; +BEGIN + + IF attr_list IS NULL OR rdeleted THEN -- need to do the full dance on INSERT or undelete + SELECT ARRAY_AGG(name) INTO attr_list FROM config.record_attr_definition; + END IF; + + IF rmarc IS NULL THEN + SELECT marc INTO rmarc FROM biblio.record_entry WHERE id = rid; + END IF; + + FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE NOT composite AND name = ANY( attr_list ) ORDER BY format LOOP + + attr_value := '{}'::TEXT[] + norm_attr_value := '{}'::TEXT[] + attr_vector_tmp := '{}'::INT[] + + SELECT * INTO ccvm_row FROM config.code_value_map c WHERE c.ctype = attr_def.name; + + -- tag+sf attrs only support SVF + IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection + SELECT ARRAY[ARRAY_TO_STRING(ARRAY_AGG(value), COALESCE(attr_def.joiner,' '))] INTO attr_value + FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x + WHERE record = rid + AND tag LIKE attr_def.tag + AND CASE + WHEN attr_def.sf_list IS NOT NULL + THEN POSITION(subfield IN attr_def.sf_list) > 0 + ELSE TRUE + END + GROUP BY tag + ORDER BY 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_list(rmarc, attr_def.fixed_field); + + IF NOT attr_def.multi THEN + attr_value := ARRAY[attr_value[1]]; + END IF; + + 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(rmarc,xfrm.xslt); + ELSE + transformed_xml := rmarc; + 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; + + FOR tmp_xml IN SELECT XPATH(attr_def.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]) LOOP + attr_value := attr_value || + oils_xpath_string( + '//*', + tmp_xml::TEXT, + COALESCE(attr_def.joiner,' '), + ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] + ); + EXIT WHEN NOT attr_def.multi; + END LOOP; + + ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map + SELECT ARRAY_AGG(m.value) INTO attr_vlue + FROM vandelay.marc21_physical_characteristics(rmarc) v + LEFT JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value) + WHERE v.subfield = attr_def.phys_char_sf + AND ( ccvm.id IS NULL OR ( ccvm.id IS NOT NULL AND v.id IS NOT NULL) ); + + IF NOT attr_def.multi THEN + attr_value := ARRAY[attr_value[1]]; + END IF; + + END IF; + + -- apply index normalizers to attr_value + FOR tmp_val IN SELECT value FROM UNNEST(attr_value) x(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 || '(' || + COALESCE( quote_literal( tmp_val ), 'NULL' ) || + CASE + WHEN normalizer.param_count > 0 + THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'') + ELSE '' + END || + ')' INTO tmp_val; + + END LOOP; + norm_attr_value := norm_attr_value || tmp_val; + END LOOP; + + IF attr_def.filter THEN + -- Create unknown uncontrolled values and find the IDs of the values + IF ccvm.id IS NULL THEN + FOR tmp_val FROM SELECT value FROM UNNEST(norm_attr_value) x(value) LOOP + BEGIN; -- use subtransaction to isolate unique constraint violations + INSERT INTO metabib.uncontrolled_record_attr_value ( attr, value ) VALUES ( attr_def.name, tmp_val ); + EXCEPTION WHEN unique_violation THEN END; + END LOOP; + + SELECT ARRAY_AGG(id) INTO attr_vector_tmp FROM metabib.uncontrolled_record_attr_value WHERE attr = attr_def.name AND value = ANY( norm_attr_value ); + ELSE + SELECT ARRAY_AGG(id) INTO attr_vector_tmp FROM config.coded_value_map WHERE ctype = attr_def.name AND value = ANY( norm_attr_value ); + END IF; + + -- Add the new value to the vector + attr_vector := attr_vector || attr_vector_tmp; + END IF; + + IF attr_def.sorter THEN + DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name; + INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, norm_attr_value[1]); + END IF; + + END LOOP; + +/* We may need to rewrite the vlist to contain + the intersection of new values for requested + attrs and old values for ignored attrs. To + do this, we take the old attr vlist and + subtract any values that are valid for the + requested attrs, and then add back the new + set of attr values. */ + + IF ARRAY_LENGTH(pattr_list, 1) > 0 THEN + SELECT vlist INTO attr_vector_tmp FROM metabib.record_attr_vector_list WHERE source = rid; + SELECT attr_vector_tmp - ARRAY_AGG(id) INTO attr_vector_tmp FROM metabib.full_attr_id_map WHERE attr = ANY (pattr_list); + attr_vector := attr_vector || attr_vector_tmp; + END IF; + + -- On to composite attributes, now that the record attrs have been pulled. Processed in name order, so later composite + -- attributes can depend on earlier ones. + FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE composite AND name = ANY( attr_list ) ORDER BY name LOOP + + FOR ccvm_row IN SELECT * FROM config.coded_value_map c WHERE c.ctype = attr_def.name ORDER BY value LOOP + + tmp_val := metabib.compile_composite_attr( ccvm_row.id ); + NEXT WHEN tmp_val IS NULL OR tmp_val = ''; -- nothing to do + + IF attr_def.filter THEN + IF attr_vector @@ tmp_val::query_int THEN + attr_vector = attr_vector + intset(ccvm_row.id); + EXIT WHEN NOT attr_def.multi; + END IF; + END IF; + + IF attr_def.sorter THEN + IF attr_vector ~~ tmp_val THEN + DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name; + INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, ccvm_row.code); + END IF; + END IF; + + END LOOP; + + END LOOP; + + IF ARRAY_LENGTH(attr_vector, 1) > 0 THEN + IF rdeleted THEN -- initial insert OR revivication + DELETE FROM metabib.record_attr_vector_list WHERE source = rid; + INSERT INTO metabib.record_attr_vector_list (source, vlist) VALUES (rid, attr_vector); + ELSE + UPDATE metabib.record_attr_vector_list SET vlist = attr_vector WHERE source = rid; + END IF; + END IF; + +END; + +$$ LANGUAGE PLPGSQL; + + +-- AFTER UPDATE OR INSERT trigger for biblio.record_entry +CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$ BEGIN IF NEW.deleted IS TRUE THEN -- If this bib is deleted @@ -1270,7 +1606,7 @@ BEGIN -- with the #deleted modifier, so one should turn on the named -- internal flag for that functionality. DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id; - DELETE FROM metabib.record_attr WHERE id = NEW.id; + DELETE FROM metabib.record_attr_vector_list WHERE source = NEW.id; END IF; DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible @@ -1301,90 +1637,7 @@ BEGIN -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled; IF NOT FOUND THEN - FOR attr_def IN SELECT * FROM config.record_attr_definition ORDER BY format LOOP - - IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection - SELECT STRING_AGG(value, COALESCE(attr_def.joiner,' ')) INTO attr_value - FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x - WHERE record = NEW.id - AND tag LIKE attr_def.tag - AND CASE - WHEN attr_def.sf_list IS NOT NULL - THEN POSITION(subfield IN attr_def.sf_list) > 0 - ELSE TRUE - END - GROUP BY tag - ORDER BY 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 := biblio.marc21_extract_fixed_field(NEW.id, 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(NEW.marc,xfrm.xslt); - ELSE - transformed_xml := NEW.marc; - 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 INTO attr_value - FROM biblio.marc21_physical_characteristics(NEW.id) 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 || '(' || - COALESCE( quote_literal( attr_value ), 'NULL' ) || - 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; - - IF TG_OP = 'INSERT' OR OLD.deleted THEN -- initial insert OR revivication - DELETE FROM metabib.record_attr WHERE id = NEW.id; - INSERT INTO metabib.record_attr (id, attrs) VALUES (NEW.id, new_attrs); - ELSE - UPDATE metabib.record_attr SET attrs = new_attrs WHERE id = NEW.id; - END IF; - + PERFORM metabib.reingest_record_attributes(NEW.id, NULL, NEW.marc, TG_OP = 'INSERT' OR OLD.deleted); END IF; END IF; diff --git a/Open-ILS/src/sql/Pg/950.data.seed-values.sql b/Open-ILS/src/sql/Pg/950.data.seed-values.sql index 2e3d1afee5..32fd3ad50e 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -6059,17 +6059,17 @@ INSERT INTO config.marc21_ff_pos_map (fixed_field, tag, rec_type,start_pos, leng -- record attributes INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('alph','Alph','Alph'); INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('audience','Audn','Audn'); -INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('bib_level','BLvl','BLvl'); +INSERT INTO config.record_attr_definition (name,label,fixed_field,multi) values ('bib_level','BLvl','BLvl',FALSE); INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('biog','Biog','Biog'); INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('conf','Conf','Conf'); -INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('control_type','Ctrl','Ctrl'); +INSERT INTO config.record_attr_definition (name,label,fixed_field,multi) values ('control_type','Ctrl','Ctrl',FALSE); INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('ctry','Ctry','Ctry'); INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('date1','Date1','Date1'); -INSERT INTO config.record_attr_definition (name,label,fixed_field,sorter,filter) values ('pubdate','Pub Date','Date1',TRUE,FALSE); +INSERT INTO config.record_attr_definition (name,label,fixed_field,sorter,filter,multi) values ('pubdate','Pub Date','Date1',TRUE,FALSE,FALSE); INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('date2','Date2','Date2'); -INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('cat_form','Desc','Desc'); +INSERT INTO config.record_attr_definition (name,label,fixed_field,multi) values ('cat_form','Desc','Desc',FALSE); INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('pub_status','DtSt','DtSt'); -INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('enc_level','ELvl','ELvl'); +INSERT INTO config.record_attr_definition (name,label,fixed_field,multi) values ('enc_level','ELvl','ELvl',FALSE); INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('fest','Fest','Fest'); INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('item_form','Form','Form'); INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('gpub','GPub','GPub'); @@ -6081,10 +6081,10 @@ INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('lit_ INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('mrec','MRec','MRec'); INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('ff_sl','S/L','S/L'); INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('type_mat','TMat','TMat'); -INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('item_type','Type','Type'); +INSERT INTO config.record_attr_definition (name,label,fixed_field,multi) values ('item_type','Type','Type',FALSE); INSERT INTO config.record_attr_definition (name,label,phys_char_sf) values ('vr_format','Videorecording format',72); -INSERT INTO config.record_attr_definition (name,label,sorter,filter,tag) values ('titlesort','Title',TRUE,FALSE,'tnf'); -INSERT INTO config.record_attr_definition (name,label,sorter,filter,tag,sf_list) values ('authorsort','Author',TRUE,FALSE,'1%','abcdefgklmnopqrstvxyz'); +INSERT INTO config.record_attr_definition (name,label,sorter,filter,tag,multi) values ('titlesort','Title',TRUE,FALSE,'tnf',FALSE); +INSERT INTO config.record_attr_definition (name,label,sorter,filter,tag,sf_list,multi) values ('authorsort','Author',TRUE,FALSE,'1%','abcdefgklmnopqrstvxyz',FALSE); -- TO-DO: Auto-generate these values from CLDR -- XXX These are the values used in MARC records ... does that match CLDR, including deprecated languages? @@ -6659,6 +6659,94 @@ INSERT INTO config.coded_value_map(id, ctype, code, value) VALUES (555, 'vr_format', 'z', oils_i18n_gettext('555', 'Other', 'ccvm', 'value')), (556, 'vr_format', ' ', oils_i18n_gettext('556', 'Unspecified', 'ccvm', 'value')); +INSERT INTO config.record_attr_definition (name, label, phys_char_sf) +VALUES ( + 'sr_format', + oils_i18n_gettext('sr_format', 'Sound recording format', 'crad', 'label'), + '62' +); + +INSERT INTO config.coded_value_map (id, ctype, code, value) VALUES +(557, 'sr_format', 'a', oils_i18n_gettext(557, '16 rpm', 'ccvm', 'value')), +(558, 'sr_format', 'b', oils_i18n_gettext(558, '33 1/3 rpm', 'ccvm', 'value')), +(559, 'sr_format', 'c', oils_i18n_gettext(559, '45 rpm', 'ccvm', 'value')), +(560, 'sr_format', 'f', oils_i18n_gettext(560, '1.4 m. per second', 'ccvm', 'value')), +(561, 'sr_format', 'd', oils_i18n_gettext(561, '78 rpm', 'ccvm', 'value')), +(562, 'sr_format', 'e', oils_i18n_gettext(562, '8 rpm', 'ccvm', 'value')), +(563, 'sr_format', 'l', oils_i18n_gettext(563, '1 7/8 ips', 'ccvm', 'value')), +(586, 'item_form', 'o', oils_i18n_gettext('586', 'Online', 'ccvm', 'value')), +(587, 'item_form', 'q', oils_i18n_gettext('587', 'Direct electronic', 'ccvm', 'value')); + +INSERT INTO config.coded_value_map + (id, ctype, code, value, search_label) VALUES +(564, 'icon_format', 'book', + oils_i18n_gettext(564, 'Book', 'ccvm', 'value'), + oils_i18n_gettext(564, 'Book', 'ccvm', 'search_label')), +(565, 'icon_format', 'braille', + oils_i18n_gettext(565, 'Braille', 'ccvm', 'value'), + oils_i18n_gettext(565, 'Braille', 'ccvm', 'search_label')), +(566, 'icon_format', 'software', + oils_i18n_gettext(566, 'Software and video games', 'ccvm', 'value'), + oils_i18n_gettext(566, 'Software and video games', 'ccvm', 'search_label')), +(567, 'icon_format', 'dvd', + oils_i18n_gettext(567, 'DVD', 'ccvm', 'value'), + oils_i18n_gettext(567, 'DVD', 'ccvm', 'search_label')), +(568, 'icon_format', 'ebook', + oils_i18n_gettext(568, 'E-book', 'ccvm', 'value'), + oils_i18n_gettext(568, 'E-book', 'ccvm', 'search_label')), +(569, 'icon_format', 'eaudio', + oils_i18n_gettext(569, 'E-audio', 'ccvm', 'value'), + oils_i18n_gettext(569, 'E-audio', 'ccvm', 'search_label')), +(570, 'icon_format', 'kit', + oils_i18n_gettext(570, 'Kit', 'ccvm', 'value'), + oils_i18n_gettext(570, 'Kit', 'ccvm', 'search_label')), +(571, 'icon_format', 'map', + oils_i18n_gettext(571, 'Map', 'ccvm', 'value'), + oils_i18n_gettext(571, 'Map', 'ccvm', 'search_label')), +(572, 'icon_format', 'microform', + oils_i18n_gettext(572, 'Microform', 'ccvm', 'value'), + oils_i18n_gettext(572, 'Microform', 'ccvm', 'search_label')), +(573, 'icon_format', 'score', + oils_i18n_gettext(573, 'Music Score', 'ccvm', 'value'), + oils_i18n_gettext(573, 'Music Score', 'ccvm', 'search_label')), +(574, 'icon_format', 'picture', + oils_i18n_gettext(574, 'Picture', 'ccvm', 'value'), + oils_i18n_gettext(574, 'Picture', 'ccvm', 'search_label')), +(575, 'icon_format', 'equip', + oils_i18n_gettext(575, 'Equipment, games, toys', 'ccvm', 'value'), + oils_i18n_gettext(575, 'Equipment, games, toys', 'ccvm', 'search_label')), +(576, 'icon_format', 'serial', + oils_i18n_gettext(576, 'Serials and magazines', 'ccvm', 'value'), + oils_i18n_gettext(576, 'Serials and magazines', 'ccvm', 'search_label')), +(577, 'icon_format', 'vhs', + oils_i18n_gettext(577, 'VHS', 'ccvm', 'value'), + oils_i18n_gettext(577, 'VHS', 'ccvm', 'search_label')), +(578, 'icon_format', 'evideo', + oils_i18n_gettext(578, 'E-video', 'ccvm', 'value'), + oils_i18n_gettext(578, 'E-video', 'ccvm', 'search_label')), +(579, 'icon_format', 'cdaudiobook', + oils_i18n_gettext(579, 'CD Audiobook', 'ccvm', 'value'), + oils_i18n_gettext(579, 'CD Audiobook', 'ccvm', 'search_label')), +(580, 'icon_format', 'cdmusic', + oils_i18n_gettext(580, 'CD Music recording', 'ccvm', 'value'), + oils_i18n_gettext(580, 'CD Music recording', 'ccvm', 'search_label')), +(581, 'icon_format', 'casaudiobook', + oils_i18n_gettext(581, 'Cassette audiobook', 'ccvm', 'value'), + oils_i18n_gettext(581, 'Cassette audiobook', 'ccvm', 'search_label')), +(582, 'icon_format', 'casmusic', + oils_i18n_gettext(582, 'Audiocassette music recording', 'ccvm', 'value'), + oils_i18n_gettext(582, 'Audiocassette music recording', 'ccvm', 'search_label')), +(583, 'icon_format', 'phonospoken', + oils_i18n_gettext(583, 'Phonograph spoken recording', 'ccvm', 'value'), + oils_i18n_gettext(583, 'Phonograph spoken recording', 'ccvm', 'search_label')), +(584, 'icon_format', 'phonomusic', + oils_i18n_gettext(584, 'Phonograph music recording', 'ccvm', 'value'), + oils_i18n_gettext(584, 'Phonograph music recording', 'ccvm', 'search_label')), +(585, 'icon_format', 'lpbook', + oils_i18n_gettext(585, 'Large Print Book', 'ccvm', 'value'), + oils_i18n_gettext(585, 'Large Print Book', 'ccvm', 'search_label')) +; + SELECT SETVAL('config.coded_value_map_id_seq'::TEXT, (SELECT max(id) FROM config.coded_value_map)); -- Trigger Event Definitions ------------------------------------------------- @@ -9422,6 +9510,99 @@ VALUES ( ) ); +INSERT INTO config.global_flag (name, label, value, enabled) VALUES ( + 'opac.icon_attr', + oils_i18n_gettext( + 'opac.icon_attr', + 'OPAC Format Icons Attribute', + 'cgf', + 'label' + ), + 'icon_format', + TRUE +); + +INSERT INTO config.record_attr_definition + (name, label, multi, filter, composite) VALUES ( + 'icon_format', + oils_i18n_gettext( + 'icon_format', + 'OPAC Format Icons', + 'crad', + 'label' + ), + TRUE, TRUE, TRUE +); + +INSERT INTO config.composite_attr_entry_definition + (coded_value, definition) VALUES +--book +(564, '{"0":[{"_attr":"item_type","_val":"a"},{"_attr":"item_type","_val":"t"}],"1":{"_not":[{"_attr":"item_form","_val":"a"},{"_attr":"item_form","_val":"b"},{"_attr":"item_form","_val":"c"},{"_attr":"item_form","_val":"d"},{"_attr":"item_form","_val":"f"},{"_attr":"item_form","_val":"o"},{"_attr":"item_form","_val":"q"},{"_attr":"item_form","_val":"r"},{"_attr":"item_form","_val":"s"}]},"2":[{"_attr":"bib_level","_val":"a"},{"_attr":"bib_level","_val":"c"},{"_attr":"bib_level","_val":"d"},{"_attr":"bib_level","_val":"m"}]}'), + +-- braille +(565, '{"0":{"_attr":"item_type","_val":"a"},"1":{"_attr":"item_form","_val":"f"}}'), + +-- software +(566, '{"_attr":"item_type","_val":"m"}'), + +-- dvd +(567, '{"_attr":"vr_format","_val":"v"}'), + +-- ebook +(568, '{"0":[{"_attr":"item_type","_val":"a"},{"_attr":"item_type","_val":"t"}],"1":[{"_attr":"item_form","_val":"o"},{"_attr":"item_form","_val":"s"},{"_attr":"item_form","_val":"q"}],"2":[{"_attr":"bib_level","_val":"a"},{"_attr":"bib_level","_val":"c"},{"_attr":"bib_level","_val":"d"},{"_attr":"bib_level","_val":"m"}]}'), + +-- eaudio +(569, '{"0":{"_attr":"item_type","_val":"i"},"1":[{"_attr":"item_form","_val":"o"},{"_attr":"item_form","_val":"q"},{"_attr":"item_form","_val":"s"}]}'), + +-- kit +(570, '[{"_attr":"item_type","_val":"o"},{"_attr":"item_type","_val":"p"}]'), + +-- map +(571, '[{"_attr":"item_type","_val":"e"},{"_attr":"item_type","_val":"f"}]'), + +-- microform +(572, '[{"_attr":"item_form","_val":"a"},{"_attr":"item_form","_val":"b"},{"_attr":"item_form","_val":"c"}]'), + +-- score +(573, '[{"_attr":"item_type","_val":"c"},{"_attr":"item_type","_val":"d"}]'), + +-- picture +(574, '{"_attr":"item_type","_val":"k"}'), + +-- equip +(575, '{"_attr":"item_type","_val":"r"}'), + +-- serial +(576, '[{"_attr":"bib_level","_val":"b"},{"_attr":"bib_level","_val":"s"}]'), + +-- vhs +(577, '{"_attr":"vr_format","_val":"b"}'), + +-- evideo +(578, '{"0":{"_attr":"item_type","_val":"g"},"1":[{"_attr":"item_form","_val":"o"},{"_attr":"item_form","_val":"s"},{"_attr":"item_form","_val":"q"}]}'), + +-- cdaudiobook +(579, '{"0":{"_attr":"item_type","_val":"i"},"1":{"_attr":"sr_format","_val":"f"}}'), + +-- cdmusic +(580, '{"0":{"_attr":"item_type","_val":"j"},"1":{"_attr":"sr_format","_val":"f"}}'), + +-- casaudiobook +(581, '{"0":{"_attr":"item_type","_val":"i"},"1":{"_attr":"sr_format","_val":"l"}}'), + +-- casmusic +(582, '{"0":{"_attr":"item_type","_val":"j"},"1":{"_attr":"sr_format","_val":"l"}}'), + +-- phonospoken +(583, '{"0":{"_attr":"item_type","_val":"i"},"1":[{"_attr":"sr_format","_val":"a"},{"_attr":"sr_format","_val":"b"},{"_attr":"sr_format","_val":"c"},{"_attr":"sr_format","_val":"d"},{"_attr":"sr_format","_val":"e"}]}'), + +-- phonomusic +(584, '{"0":{"_attr":"item_type","_val":"j"},"1":[{"_attr":"sr_format","_val":"a"},{"_attr":"sr_format","_val":"b"},{"_attr":"sr_format","_val":"c"},{"_attr":"sr_format","_val":"d"},{"_attr":"sr_format","_val":"e"}]}'), + +-- lpbook +(585, '{"0":[{"_attr":"item_type","_val":"a"},{"_attr":"item_type","_val":"t"}],"1":{"_attr":"item_form","_val":"d"},"2":[{"_attr":"bib_level","_val":"a"},{"_attr":"bib_level","_val":"c"},{"_attr":"bib_level","_val":"d"},{"_attr":"bib_level","_val":"m"}]}'); + + INSERT INTO config.usr_setting_type (name,opac_visible,label,description,datatype) VALUES ( 'history.circ.retention_age', diff --git a/Open-ILS/src/sql/Pg/create_database_extensions.sql b/Open-ILS/src/sql/Pg/create_database_extensions.sql index e06ac598b9..b73a87168e 100644 --- a/Open-ILS/src/sql/Pg/create_database_extensions.sql +++ b/Open-ILS/src/sql/Pg/create_database_extensions.sql @@ -19,3 +19,4 @@ CREATE LANGUAGE plperlu; CREATE EXTENSION tablefunc; CREATE EXTENSION xml2; CREATE EXTENSION hstore; +CREATE EXTENSION intarray; -- 2.43.2