3 SELECT evergreen.upgrade_deps_block_check('0864', :eg_version);
5 CREATE EXTENSION intarray;
7 -- while we have this opportunity, and before we start collecting
8 -- CCVM IDs (below) carve out a nice space for stock ccvm values
9 UPDATE config.coded_value_map SET id = id + 10000 WHERE id > 556;
10 SELECT SETVAL('config.coded_value_map_id_seq'::TEXT,
11 (SELECT GREATEST(max(id), 10000) FROM config.coded_value_map));
13 ALTER TABLE config.record_attr_definition ADD COLUMN multi BOOL NOT NULL DEFAULT TRUE, ADD COLUMN composite BOOL NOT NULL DEFAULT FALSE;
15 UPDATE config.record_attr_definition
17 WHERE name IN ('bib_level','control_type','pubdate','cat_form','enc_level','item_type','titlesort','authorsort');
19 CREATE OR REPLACE FUNCTION vandelay.marc21_physical_characteristics( marc TEXT) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$
23 ptype config.marc21_physical_characteristic_type_map%ROWTYPE;
24 psf config.marc21_physical_characteristic_subfield_map%ROWTYPE;
25 pval config.marc21_physical_characteristic_value_map%ROWTYPE;
26 retval biblio.marc21_physical_characteristics%ROWTYPE;
29 FOR _007 IN SELECT oils_xpath_string('//*', value) FROM UNNEST(oils_xpath('//*[@tag="007"]', marc)) x(value) LOOP
30 IF _007 IS NOT NULL AND _007 <> '' THEN
31 SELECT * INTO ptype FROM config.marc21_physical_characteristic_type_map WHERE ptype_key = SUBSTRING( _007, 1, 1 );
33 IF ptype.ptype_key IS NOT NULL THEN
34 FOR psf IN SELECT * FROM config.marc21_physical_characteristic_subfield_map WHERE ptype_key = ptype.ptype_key LOOP
35 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 );
37 IF pval.id IS NOT NULL THEN
40 retval.ptype := ptype.ptype_key;
41 retval.subfield := psf.id;
42 retval.value := pval.id;
53 $func$ LANGUAGE PLPGSQL;
55 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field_list( marc TEXT, ff TEXT ) RETURNS TEXT[] AS $func$
61 collection TEXT[] := '{}'::TEXT[];
63 rtype := (vandelay.marc21_record_type( marc )).code;
64 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = ff AND rec_type = rtype ORDER BY tag DESC LOOP
65 IF ff_pos.tag = 'ldr' THEN
66 val := oils_xpath_string('//*[local-name()="leader"]', marc);
67 IF val IS NOT NULL THEN
68 val := SUBSTRING( val, ff_pos.start_pos + 1, ff_pos.length );
69 collection := collection || val;
72 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
73 val := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length );
74 collection := collection || val;
77 val := REPEAT( ff_pos.default_val, ff_pos.length );
78 collection := collection || val;
83 $func$ LANGUAGE PLPGSQL;
85 CREATE OR REPLACE FUNCTION biblio.marc21_extract_fixed_field_list( rid BIGINT, ff TEXT ) RETURNS TEXT[] AS $func$
86 SELECT * FROM vandelay.marc21_extract_fixed_field_list( (SELECT marc FROM biblio.record_entry WHERE id = $1), $2 );
89 -- DECREMENTING serial starts at -1
90 CREATE SEQUENCE metabib.uncontrolled_record_attr_value_id_seq INCREMENT BY -1;
92 CREATE TABLE metabib.uncontrolled_record_attr_value (
93 id BIGINT PRIMARY KEY DEFAULT nextval('metabib.uncontrolled_record_attr_value_id_seq'),
94 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name),
97 CREATE UNIQUE INDEX muv_once_idx ON metabib.uncontrolled_record_attr_value (attr,value);
99 CREATE TABLE metabib.record_attr_vector_list (
100 source BIGINT PRIMARY KEY REFERENCES biblio.record_entry (id),
101 vlist INT[] NOT NULL -- stores id from ccvm AND murav
103 CREATE INDEX mrca_vlist_idx ON metabib.record_attr_vector_list USING gin ( vlist gin__int_ops );
105 CREATE TABLE metabib.record_sorter (
106 id BIGSERIAL PRIMARY KEY,
107 source BIGINT NOT NULL REFERENCES biblio.record_entry (id) ON DELETE CASCADE,
108 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE,
111 CREATE INDEX metabib_sorter_source_idx ON metabib.record_sorter (source); -- we may not need one of this or the next ... stats will tell
112 CREATE INDEX metabib_sorter_s_a_idx ON metabib.record_sorter (source, attr);
113 CREATE INDEX metabib_sorter_a_v_idx ON metabib.record_sorter (attr, value);
115 CREATE TEMP TABLE attr_set ON COMMIT DROP AS SELECT DISTINCT id AS source, (each(attrs)).key,(each(attrs)).value FROM metabib.record_attr;
116 DELETE FROM attr_set WHERE BTRIM(value) = '';
118 -- Grab sort values for the new sorting mechanism
119 INSERT INTO metabib.record_sorter (source,attr,value)
120 SELECT a.source, a.key, a.value
122 JOIN config.record_attr_definition d ON (d.name = a.key AND d.sorter AND a.value IS NOT NULL);
124 -- Rewrite uncontrolled SVF record attrs as the seeds of an intarray vector
125 INSERT INTO metabib.uncontrolled_record_attr_value (attr,value)
126 SELECT DISTINCT a.key, a.value
128 JOIN config.record_attr_definition d ON (d.name = a.key AND d.filter AND a.value IS NOT NULL)
129 LEFT JOIN config.coded_value_map m ON (m.ctype = a.key)
132 -- Now construct the record-specific vector from the SVF data
133 INSERT INTO metabib.record_attr_vector_list (source,vlist)
134 SELECT a.id, ARRAY_AGG(COALESCE(u.id, c.id))
135 FROM metabib.record_attr a
136 JOIN attr_set ON (a.id = attr_set.source)
137 LEFT JOIN metabib.uncontrolled_record_attr_value u ON (u.attr = attr_set.key AND u.value = attr_set.value)
138 LEFT JOIN config.coded_value_map c ON (c.ctype = attr_set.key AND c.code = attr_set.value)
139 WHERE COALESCE(u.id,c.id) IS NOT NULL
142 DROP VIEW IF EXISTS reporter.classic_current_circ;
143 DROP VIEW metabib.rec_descriptor;
144 DROP TABLE metabib.record_attr;
146 CREATE TYPE metabib.record_attr_type AS (
151 CREATE TABLE config.composite_attr_entry_definition(
152 coded_value INT PRIMARY KEY NOT NULL REFERENCES config.coded_value_map (id) ON UPDATE CASCADE ON DELETE CASCADE,
153 definition TEXT NOT NULL -- JSON
156 CREATE OR REPLACE VIEW metabib.record_attr_id_map AS
157 SELECT id, attr, value FROM metabib.uncontrolled_record_attr_value
159 SELECT c.id, c.ctype AS attr, c.code AS value
160 FROM config.coded_value_map c
161 JOIN config.record_attr_definition d ON (d.name = c.ctype AND NOT d.composite);
163 CREATE VIEW metabib.composite_attr_id_map AS
164 SELECT c.id, c.ctype AS attr, c.code AS value
165 FROM config.coded_value_map c
166 JOIN config.record_attr_definition d ON (d.name = c.ctype AND d.composite);
168 CREATE OR REPLACE VIEW metabib.full_attr_id_map AS
169 SELECT id, attr, value FROM metabib.record_attr_id_map
171 SELECT id, attr, value FROM metabib.composite_attr_id_map;
174 -- Back-compat view ... we're moving to an INTARRAY world
175 CREATE VIEW metabib.record_attr_flat AS
176 SELECT v.source AS id,
179 FROM metabib.full_attr_id_map m
180 JOIN metabib.record_attr_vector_list v ON ( m.id = ANY( v.vlist ) );
182 CREATE VIEW metabib.record_attr AS
183 SELECT id, HSTORE( ARRAY_AGG( attr ), ARRAY_AGG( value ) ) AS attrs FROM metabib.record_attr_flat GROUP BY 1;
185 CREATE VIEW metabib.rec_descriptor AS
188 (populate_record(NULL::metabib.rec_desc_type, attrs)).*
189 FROM metabib.record_attr;
191 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr_cache_init () RETURNS BOOL AS $f$
192 $_SHARED{metabib_compile_composite_attr_cache} = {}
193 if ! exists $_SHARED{metabib_compile_composite_attr_cache};
194 return exists $_SHARED{metabib_compile_composite_attr_cache};
195 $f$ LANGUAGE PLPERLU;
197 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr_cache_disable () RETURNS BOOL AS $f$
198 delete $_SHARED{metabib_compile_composite_attr_cache};
199 return ! exists $_SHARED{metabib_compile_composite_attr_cache};
200 $f$ LANGUAGE PLPERLU;
202 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr_cache_invalidate () RETURNS BOOL AS $f$
203 SELECT metabib.compile_composite_attr_cache_disable() AND metabib.compile_composite_attr_cache_init();
206 CREATE OR REPLACE FUNCTION metabib.composite_attr_def_cache_inval_tgr () RETURNS TRIGGER AS $f$
208 PERFORM metabib.compile_composite_attr_cache_invalidate();
211 $f$ LANGUAGE PLPGSQL;
213 CREATE TRIGGER ccraed_cache_inval_tgr AFTER INSERT OR UPDATE OR DELETE ON config.composite_attr_entry_definition FOR EACH STATEMENT EXECUTE PROCEDURE metabib.composite_attr_def_cache_inval_tgr();
215 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr ( cattr_def TEXT ) RETURNS query_int AS $func$
220 my $def = decode_json($json);
222 die("Composite attribute definition not supplied") unless $def;
224 my $_cache = (exists $_SHARED{metabib_compile_composite_attr_cache}) ? 1 : 0;
226 return $_SHARED{metabib_compile_composite_attr_cache}{$json}
227 if ($_cache && $_SHARED{metabib_compile_composite_attr_cache}{$json});
234 if (ref $d eq 'HASH') { # node or AND
235 if (exists $d->{_attr}) { # it is a node
236 my $plan = spi_prepare('SELECT * FROM metabib.full_attr_id_map WHERE attr = $1 AND value = $2', qw/TEXT TEXT/);
237 my $id = spi_exec_prepared(
238 $plan, {limit => 1}, $d->{_attr}, $d->{_val}
242 } elsif (exists $d->{_not} && scalar(keys(%$d)) == 1) { # it is a NOT
243 return '!' . recurse($$d{_not});
244 } else { # an AND list
245 @list = map { recurse($$d{$_}) } sort keys %$d;
247 } elsif (ref $d eq 'ARRAY') {
249 @list = map { recurse($_) } @$d;
252 @list = grep { defined && $_ ne '' } @list;
254 return '(' . join($j,@list) . ')' if @list;
258 my $val = recurse($def) || undef;
259 $_SHARED{metabib_compile_composite_attr_cache}{$json} = $val if $_cache;
262 $func$ IMMUTABLE LANGUAGE plperlu;
264 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr ( cattr_id INT ) RETURNS query_int AS $func$
265 SELECT metabib.compile_composite_attr(definition) FROM config.composite_attr_entry_definition WHERE coded_value = $1;
266 $func$ STRICT IMMUTABLE LANGUAGE SQL;
268 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$
270 transformed_xml TEXT;
271 rmarc TEXT := prmarc;
275 xfrm config.xml_transform%ROWTYPE;
276 attr_vector INT[] := '{}'::INT[];
277 attr_vector_tmp INT[];
278 attr_list TEXT[] := pattr_list;
280 norm_attr_value TEXT[];
282 attr_def config.record_attr_definition%ROWTYPE;
283 ccvm_row config.coded_value_map%ROWTYPE;
286 IF attr_list IS NULL OR rdeleted THEN -- need to do the full dance on INSERT or undelete
287 SELECT ARRAY_AGG(name) INTO attr_list FROM config.record_attr_definition;
290 IF rmarc IS NULL THEN
291 SELECT marc INTO rmarc FROM biblio.record_entry WHERE id = rid;
294 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE NOT composite AND name = ANY( attr_list ) ORDER BY format LOOP
296 attr_value := '{}'::TEXT[];
297 norm_attr_value := '{}'::TEXT[];
298 attr_vector_tmp := '{}'::INT[];
300 SELECT * INTO ccvm_row FROM config.coded_value_map c WHERE c.ctype = attr_def.name LIMIT 1;
302 -- tag+sf attrs only support SVF
303 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
304 SELECT ARRAY[ARRAY_TO_STRING(ARRAY_AGG(value), COALESCE(attr_def.joiner,' '))] INTO attr_value
305 FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x
307 AND tag LIKE attr_def.tag
309 WHEN attr_def.sf_list IS NOT NULL
310 THEN POSITION(subfield IN attr_def.sf_list) > 0
317 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
318 attr_value := vandelay.marc21_extract_fixed_field_list(rmarc, attr_def.fixed_field);
320 IF NOT attr_def.multi THEN
321 attr_value := ARRAY[attr_value[1]];
324 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
326 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
328 -- See if we can skip the XSLT ... it's expensive
329 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
330 -- Can't skip the transform
331 IF xfrm.xslt <> '---' THEN
332 transformed_xml := oils_xslt_process(rmarc,xfrm.xslt);
334 transformed_xml := rmarc;
337 prev_xfrm := xfrm.name;
340 IF xfrm.name IS NULL THEN
341 -- just grab the marcxml (empty) transform
342 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
343 prev_xfrm := xfrm.name;
346 FOR tmp_xml IN SELECT XPATH(attr_def.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]) LOOP
347 tmp_val := oils_xpath_string(
350 COALESCE(attr_def.joiner,' '),
351 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
353 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
354 attr_value := attr_value || tmp_val;
355 EXIT WHEN NOT attr_def.multi;
359 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
360 SELECT ARRAY_AGG(m.value) INTO attr_value
361 FROM vandelay.marc21_physical_characteristics(rmarc) v
362 LEFT JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
363 WHERE v.subfield = attr_def.phys_char_sf AND (m.value IS NOT NULL AND BTRIM(m.value) <> '')
364 AND ( ccvm_row.id IS NULL OR ( ccvm_row.id IS NOT NULL AND v.id IS NOT NULL) );
366 IF NOT attr_def.multi THEN
367 attr_value := ARRAY[attr_value[1]];
372 -- apply index normalizers to attr_value
373 FOR tmp_val IN SELECT value FROM UNNEST(attr_value) x(value) LOOP
375 SELECT n.func AS func,
376 n.param_count AS param_count,
378 FROM config.index_normalizer n
379 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
380 WHERE attr = attr_def.name
382 EXECUTE 'SELECT ' || normalizer.func || '(' ||
383 COALESCE( quote_literal( tmp_val ), 'NULL' ) ||
385 WHEN normalizer.param_count > 0
386 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
392 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
393 norm_attr_value := norm_attr_value || tmp_val;
397 IF attr_def.filter THEN
398 -- Create unknown uncontrolled values and find the IDs of the values
399 IF ccvm_row.id IS NULL THEN
400 FOR tmp_val IN SELECT value FROM UNNEST(norm_attr_value) x(value) LOOP
401 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
402 BEGIN -- use subtransaction to isolate unique constraint violations
403 INSERT INTO metabib.uncontrolled_record_attr_value ( attr, value ) VALUES ( attr_def.name, tmp_val );
404 EXCEPTION WHEN unique_violation THEN END;
408 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 );
410 SELECT ARRAY_AGG(id) INTO attr_vector_tmp FROM config.coded_value_map WHERE ctype = attr_def.name AND code = ANY( norm_attr_value );
413 -- Add the new value to the vector
414 attr_vector := attr_vector || attr_vector_tmp;
417 IF attr_def.sorter AND norm_attr_value[1] IS NOT NULL THEN
418 DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
419 INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, norm_attr_value[1]);
424 /* We may need to rewrite the vlist to contain
425 the intersection of new values for requested
426 attrs and old values for ignored attrs. To
427 do this, we take the old attr vlist and
428 subtract any values that are valid for the
429 requested attrs, and then add back the new
430 set of attr values. */
432 IF ARRAY_LENGTH(pattr_list, 1) > 0 THEN
433 SELECT vlist INTO attr_vector_tmp FROM metabib.record_attr_vector_list WHERE source = rid;
434 SELECT attr_vector_tmp - ARRAY_AGG(id::INT) INTO attr_vector_tmp FROM metabib.full_attr_id_map WHERE attr = ANY (pattr_list);
435 attr_vector := attr_vector || attr_vector_tmp;
438 -- On to composite attributes, now that the record attrs have been pulled. Processed in name order, so later composite
439 -- attributes can depend on earlier ones.
440 PERFORM metabib.compile_composite_attr_cache_init();
441 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE composite AND name = ANY( attr_list ) ORDER BY name LOOP
443 FOR ccvm_row IN SELECT * FROM config.coded_value_map c WHERE c.ctype = attr_def.name ORDER BY value LOOP
445 tmp_val := metabib.compile_composite_attr( ccvm_row.id );
446 CONTINUE WHEN tmp_val IS NULL OR tmp_val = ''; -- nothing to do
448 IF attr_def.filter THEN
449 IF attr_vector @@ tmp_val::query_int THEN
450 attr_vector = attr_vector + intset(ccvm_row.id);
451 EXIT WHEN NOT attr_def.multi;
455 IF attr_def.sorter THEN
456 IF attr_vector @@ tmp_val THEN
457 DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
458 INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, ccvm_row.code);
466 IF ARRAY_LENGTH(attr_vector, 1) > 0 THEN
467 IF rdeleted THEN -- initial insert OR revivication
468 DELETE FROM metabib.record_attr_vector_list WHERE source = rid;
469 INSERT INTO metabib.record_attr_vector_list (source, vlist) VALUES (rid, attr_vector);
471 UPDATE metabib.record_attr_vector_list SET vlist = attr_vector WHERE source = rid;
477 $func$ LANGUAGE PLPGSQL;
479 -- AFTER UPDATE OR INSERT trigger for biblio.record_entry
480 CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
483 IF NEW.deleted THEN -- If this bib is deleted
484 PERFORM * FROM config.internal_flag WHERE
485 name = 'ingest.metarecord_mapping.preserve_on_delete' AND enabled;
487 -- One needs to keep these around to support searches
488 -- with the #deleted modifier, so one should turn on the named
489 -- internal flag for that functionality.
490 DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id;
491 DELETE FROM metabib.record_attr_vector_list WHERE source = NEW.id;
494 DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible
495 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = NEW.id; -- Separate any multi-homed items
496 DELETE FROM metabib.browse_entry_def_map WHERE source = NEW.id; -- Don't auto-suggest deleted bibs
497 RETURN NEW; -- and we're done
500 IF TG_OP = 'UPDATE' THEN -- re-ingest?
501 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
503 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
508 -- Record authority linking
509 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking' AND enabled;
511 PERFORM biblio.map_authority_linking( NEW.id, NEW.marc );
514 -- Flatten and insert the mfr data
515 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled;
517 PERFORM metabib.reingest_metabib_full_rec(NEW.id);
519 -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields
520 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled;
522 PERFORM metabib.reingest_record_attributes(NEW.id, NULL, NEW.marc, TG_OP = 'INSERT' OR OLD.deleted);
526 -- Gather and insert the field entry data
527 PERFORM metabib.reingest_metabib_field_entries(NEW.id);
530 IF TG_OP = 'INSERT' THEN
531 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
533 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
536 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
538 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
542 -- (re)map metarecord-bib linking
543 IF TG_OP = 'INSERT' THEN -- if not deleted and performing an insert, check for the flag
544 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled;
546 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
548 ELSE -- we're doing an update, and we're not deleted, remap
549 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_update' AND enabled;
551 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
557 $func$ LANGUAGE PLPGSQL;
559 CREATE OR REPLACE FUNCTION public.oils_tsearch2 () RETURNS TRIGGER AS $$
563 temp_vector TEXT := '';
569 NEW.index_vector = ''::tsvector;
571 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
573 SELECT n.func AS func,
574 n.param_count AS param_count,
576 FROM config.index_normalizer n
577 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
578 WHERE field = NEW.field AND m.pos < 0
580 EXECUTE 'SELECT ' || normalizer.func || '(' ||
581 quote_literal( value ) ||
583 WHEN normalizer.param_count > 0
584 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
594 SELECT n.func AS func,
595 n.param_count AS param_count,
597 FROM config.index_normalizer n
598 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
599 WHERE field = NEW.field AND m.pos >= 0
601 EXECUTE 'SELECT ' || normalizer.func || '(' ||
602 quote_literal( value ) ||
604 WHEN normalizer.param_count > 0
605 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
613 IF TG_TABLE_NAME::TEXT ~ 'browse_entry$' THEN
615 value := ARRAY_TO_STRING(
616 evergreen.regexp_split_to_array(value, E'\\W+'), ' '
618 value := public.search_normalize(value);
619 NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);
621 ELSIF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
624 SELECT DISTINCT m.ts_config, m.index_weight
625 FROM config.metabib_class_ts_map m
626 LEFT JOIN metabib.record_attr_vector_list r ON (r.source = NEW.source)
627 LEFT JOIN config.coded_value_map ccvm ON (
628 ccvm.ctype IN ('item_lang', 'language') AND
629 ccvm.code = m.index_lang AND
630 r.vlist @> intset(ccvm.id)
632 WHERE m.field_class = TG_ARGV[0]
634 AND (m.always OR NOT EXISTS (SELECT 1 FROM config.metabib_field_ts_map WHERE metabib_field = NEW.field))
635 AND (m.index_lang IS NULL OR ccvm.id IS NOT NULL)
637 SELECT DISTINCT m.ts_config, m.index_weight
638 FROM config.metabib_field_ts_map m
639 LEFT JOIN metabib.record_attr_vector_list r ON (r.source = NEW.source)
640 LEFT JOIN config.coded_value_map ccvm ON (
641 ccvm.ctype IN ('item_lang', 'language') AND
642 ccvm.code = m.index_lang AND
643 r.vlist @> intset(ccvm.id)
645 WHERE m.metabib_field = NEW.field
647 AND (m.index_lang IS NULL OR ccvm.id IS NOT NULL)
648 ORDER BY index_weight ASC
652 IF cur_weight IS NOT NULL AND cur_weight != ts_rec.index_weight THEN
653 NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
657 cur_weight = ts_rec.index_weight;
658 SELECT INTO temp_vector temp_vector || ' ' || to_tsvector(ts_rec.ts_config::regconfig, value)::TEXT;
661 NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
663 NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);
670 -- add new sr_format attribute definition
672 INSERT INTO config.record_attr_definition (name, label, phys_char_sf)
675 oils_i18n_gettext('sr_format', 'Sound recording format', 'crad', 'label'),
679 INSERT INTO config.coded_value_map (id, ctype, code, value) VALUES
680 (557, 'sr_format', 'a', oils_i18n_gettext(557, '16 rpm', 'ccvm', 'value')),
681 (558, 'sr_format', 'b', oils_i18n_gettext(558, '33 1/3 rpm', 'ccvm', 'value')),
682 (559, 'sr_format', 'c', oils_i18n_gettext(559, '45 rpm', 'ccvm', 'value')),
683 (560, 'sr_format', 'f', oils_i18n_gettext(560, '1.4 m. per second', 'ccvm', 'value')),
684 (561, 'sr_format', 'd', oils_i18n_gettext(561, '78 rpm', 'ccvm', 'value')),
685 (562, 'sr_format', 'e', oils_i18n_gettext(562, '8 rpm', 'ccvm', 'value')),
686 (563, 'sr_format', 'l', oils_i18n_gettext(563, '1 7/8 ips', 'ccvm', 'value')),
687 (586, 'item_form', 'o', oils_i18n_gettext('586', 'Online', 'ccvm', 'value')),
688 (587, 'item_form', 'q', oils_i18n_gettext('587', 'Direct electronic', 'ccvm', 'value'));
690 INSERT INTO config.coded_value_map
691 (id, ctype, code, value, search_label) VALUES
692 (564, 'icon_format', 'book',
693 oils_i18n_gettext(564, 'Book', 'ccvm', 'value'),
694 oils_i18n_gettext(564, 'Book', 'ccvm', 'search_label')),
695 (565, 'icon_format', 'braille',
696 oils_i18n_gettext(565, 'Braille', 'ccvm', 'value'),
697 oils_i18n_gettext(565, 'Braille', 'ccvm', 'search_label')),
698 (566, 'icon_format', 'software',
699 oils_i18n_gettext(566, 'Software and video games', 'ccvm', 'value'),
700 oils_i18n_gettext(566, 'Software and video games', 'ccvm', 'search_label')),
701 (567, 'icon_format', 'dvd',
702 oils_i18n_gettext(567, 'DVD', 'ccvm', 'value'),
703 oils_i18n_gettext(567, 'DVD', 'ccvm', 'search_label')),
704 (568, 'icon_format', 'ebook',
705 oils_i18n_gettext(568, 'E-book', 'ccvm', 'value'),
706 oils_i18n_gettext(568, 'E-book', 'ccvm', 'search_label')),
707 (569, 'icon_format', 'eaudio',
708 oils_i18n_gettext(569, 'E-audio', 'ccvm', 'value'),
709 oils_i18n_gettext(569, 'E-audio', 'ccvm', 'search_label')),
710 (570, 'icon_format', 'kit',
711 oils_i18n_gettext(570, 'Kit', 'ccvm', 'value'),
712 oils_i18n_gettext(570, 'Kit', 'ccvm', 'search_label')),
713 (571, 'icon_format', 'map',
714 oils_i18n_gettext(571, 'Map', 'ccvm', 'value'),
715 oils_i18n_gettext(571, 'Map', 'ccvm', 'search_label')),
716 (572, 'icon_format', 'microform',
717 oils_i18n_gettext(572, 'Microform', 'ccvm', 'value'),
718 oils_i18n_gettext(572, 'Microform', 'ccvm', 'search_label')),
719 (573, 'icon_format', 'score',
720 oils_i18n_gettext(573, 'Music Score', 'ccvm', 'value'),
721 oils_i18n_gettext(573, 'Music Score', 'ccvm', 'search_label')),
722 (574, 'icon_format', 'picture',
723 oils_i18n_gettext(574, 'Picture', 'ccvm', 'value'),
724 oils_i18n_gettext(574, 'Picture', 'ccvm', 'search_label')),
725 (575, 'icon_format', 'equip',
726 oils_i18n_gettext(575, 'Equipment, games, toys', 'ccvm', 'value'),
727 oils_i18n_gettext(575, 'Equipment, games, toys', 'ccvm', 'search_label')),
728 (576, 'icon_format', 'serial',
729 oils_i18n_gettext(576, 'Serials and magazines', 'ccvm', 'value'),
730 oils_i18n_gettext(576, 'Serials and magazines', 'ccvm', 'search_label')),
731 (577, 'icon_format', 'vhs',
732 oils_i18n_gettext(577, 'VHS', 'ccvm', 'value'),
733 oils_i18n_gettext(577, 'VHS', 'ccvm', 'search_label')),
734 (578, 'icon_format', 'evideo',
735 oils_i18n_gettext(578, 'E-video', 'ccvm', 'value'),
736 oils_i18n_gettext(578, 'E-video', 'ccvm', 'search_label')),
737 (579, 'icon_format', 'cdaudiobook',
738 oils_i18n_gettext(579, 'CD Audiobook', 'ccvm', 'value'),
739 oils_i18n_gettext(579, 'CD Audiobook', 'ccvm', 'search_label')),
740 (580, 'icon_format', 'cdmusic',
741 oils_i18n_gettext(580, 'CD Music recording', 'ccvm', 'value'),
742 oils_i18n_gettext(580, 'CD Music recording', 'ccvm', 'search_label')),
743 (581, 'icon_format', 'casaudiobook',
744 oils_i18n_gettext(581, 'Cassette audiobook', 'ccvm', 'value'),
745 oils_i18n_gettext(581, 'Cassette audiobook', 'ccvm', 'search_label')),
746 (582, 'icon_format', 'casmusic',
747 oils_i18n_gettext(582, 'Audiocassette music recording', 'ccvm', 'value'),
748 oils_i18n_gettext(582, 'Audiocassette music recording', 'ccvm', 'search_label')),
749 (583, 'icon_format', 'phonospoken',
750 oils_i18n_gettext(583, 'Phonograph spoken recording', 'ccvm', 'value'),
751 oils_i18n_gettext(583, 'Phonograph spoken recording', 'ccvm', 'search_label')),
752 (584, 'icon_format', 'phonomusic',
753 oils_i18n_gettext(584, 'Phonograph music recording', 'ccvm', 'value'),
754 oils_i18n_gettext(584, 'Phonograph music recording', 'ccvm', 'search_label')),
755 (585, 'icon_format', 'lpbook',
756 oils_i18n_gettext(585, 'Large Print Book', 'ccvm', 'value'),
757 oils_i18n_gettext(585, 'Large Print Book', 'ccvm', 'search_label'))
760 -- add the new icon format attribute definition
762 INSERT INTO config.global_flag (name, label, value, enabled) VALUES (
766 'OPAC Format Icons Attribute',
774 INSERT INTO config.record_attr_definition
775 (name, label, multi, filter, composite) VALUES (
786 -- icon format composite definitions
788 INSERT INTO config.composite_attr_entry_definition
789 (coded_value, definition) VALUES
791 (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"}]}'),
794 (565, '{"0":{"_attr":"item_type","_val":"a"},"1":{"_attr":"item_form","_val":"f"}}'),
797 (566, '{"_attr":"item_type","_val":"m"}'),
800 (567, '{"_attr":"vr_format","_val":"v"}'),
803 (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"}]}'),
806 (569, '{"0":{"_attr":"item_type","_val":"i"},"1":[{"_attr":"item_form","_val":"o"},{"_attr":"item_form","_val":"q"},{"_attr":"item_form","_val":"s"}]}'),
809 (570, '[{"_attr":"item_type","_val":"o"},{"_attr":"item_type","_val":"p"}]'),
812 (571, '[{"_attr":"item_type","_val":"e"},{"_attr":"item_type","_val":"f"}]'),
815 (572, '[{"_attr":"item_form","_val":"a"},{"_attr":"item_form","_val":"b"},{"_attr":"item_form","_val":"c"}]'),
818 (573, '[{"_attr":"item_type","_val":"c"},{"_attr":"item_type","_val":"d"}]'),
821 (574, '{"_attr":"item_type","_val":"k"}'),
824 (575, '{"_attr":"item_type","_val":"r"}'),
827 (576, '[{"_attr":"bib_level","_val":"b"},{"_attr":"bib_level","_val":"s"}]'),
830 (577, '{"_attr":"vr_format","_val":"b"}'),
833 (578, '{"0":{"_attr":"item_type","_val":"g"},"1":[{"_attr":"item_form","_val":"o"},{"_attr":"item_form","_val":"s"},{"_attr":"item_form","_val":"q"}]}'),
836 (579, '{"0":{"_attr":"item_type","_val":"i"},"1":{"_attr":"sr_format","_val":"f"}}'),
839 (580, '{"0":{"_attr":"item_type","_val":"j"},"1":{"_attr":"sr_format","_val":"f"}}'),
842 (581, '{"0":{"_attr":"item_type","_val":"i"},"1":{"_attr":"sr_format","_val":"l"}}'),
845 (582, '{"0":{"_attr":"item_type","_val":"j"},"1":{"_attr":"sr_format","_val":"l"}}'),
848 (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"}]}'),
851 (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"}]}'),
854 (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"}]}');
859 CREATE OR REPLACE FUNCTION unapi.mra (
865 depth INT DEFAULT NULL,
866 slimit HSTORE DEFAULT NULL,
867 soffset HSTORE DEFAULT NULL,
868 include_xmlns BOOL DEFAULT TRUE
873 CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns,
874 'tag:open-ils.org:U2@mra/' || $1 AS id,
875 'tag:open-ils.org:U2@bre/' || $1 AS record
877 (SELECT XMLAGG(foo.y)
883 cvm.value AS "coded-value",
892 FROM metabib.record_attr_flat mra
893 JOIN config.record_attr_definition rad ON (mra.attr = rad.name)
894 LEFT JOIN config.coded_value_map cvm ON (cvm.ctype = mra.attr AND code = mra.value)
899 $F$ LANGUAGE SQL STABLE;
902 \qecho 'We dropped reporter.classic_current_circ earlier from the'
903 \qecho 'example.reporter-extension.sql sample. You will need to'
904 \qecho 'run it again to recreate that custom reporter view.'