3 CREATE EXTENSION intarray;
5 -- while we have this opportunity, and before we start collecting
6 -- CCVM IDs (below) carve out a nice space for stock ccvm values
7 UPDATE config.coded_value_map SET id = id + 10000 WHERE id > 556;
8 SELECT SETVAL('config.coded_value_map_id_seq'::TEXT,
9 (SELECT GREATEST(max(id), 10000) FROM config.coded_value_map));
11 ALTER TABLE config.record_attr_definition ADD COLUMN multi BOOL NOT NULL DEFAULT TRUE, ADD COLUMN composite BOOL NOT NULL DEFAULT FALSE;
13 UPDATE config.record_attr_definition
15 WHERE name IN ('bib_level','control_type','pubdate','cat_form','enc_level','item_type','titlesort','authorsort');
17 CREATE OR REPLACE FUNCTION vandelay.marc21_physical_characteristics( marc TEXT) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$
21 ptype config.marc21_physical_characteristic_type_map%ROWTYPE;
22 psf config.marc21_physical_characteristic_subfield_map%ROWTYPE;
23 pval config.marc21_physical_characteristic_value_map%ROWTYPE;
24 retval biblio.marc21_physical_characteristics%ROWTYPE;
27 FOR _007 IN SELECT oils_xpath_string('//*', value) FROM UNNEST(oils_xpath('//*[@tag="007"]', marc)) x(value) LOOP
28 IF _007 IS NOT NULL AND _007 <> '' THEN
29 SELECT * INTO ptype FROM config.marc21_physical_characteristic_type_map WHERE ptype_key = SUBSTRING( _007, 1, 1 );
31 IF ptype.ptype_key IS NOT NULL THEN
32 FOR psf IN SELECT * FROM config.marc21_physical_characteristic_subfield_map WHERE ptype_key = ptype.ptype_key LOOP
33 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 );
35 IF pval.id IS NOT NULL THEN
38 retval.ptype := ptype.ptype_key;
39 retval.subfield := psf.id;
40 retval.value := pval.id;
51 $func$ LANGUAGE PLPGSQL;
53 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field_list( marc TEXT, ff TEXT ) RETURNS TEXT[] AS $func$
59 collection TEXT[] := '{}'::TEXT[];
61 rtype := (vandelay.marc21_record_type( marc )).code;
62 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = ff AND rec_type = rtype ORDER BY tag DESC LOOP
63 IF ff_pos.tag = 'ldr' THEN
64 val := oils_xpath_string('//*[local-name()="leader"]', marc);
65 IF val IS NOT NULL THEN
66 val := SUBSTRING( val, ff_pos.start_pos + 1, ff_pos.length );
67 collection := collection || val;
70 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
71 val := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length );
72 collection := collection || val;
75 val := REPEAT( ff_pos.default_val, ff_pos.length );
76 collection := collection || val;
81 $func$ LANGUAGE PLPGSQL;
83 CREATE OR REPLACE FUNCTION biblio.marc21_extract_fixed_field_list( rid BIGINT, ff TEXT ) RETURNS TEXT[] AS $func$
84 SELECT * FROM vandelay.marc21_extract_fixed_field_list( (SELECT marc FROM biblio.record_entry WHERE id = $1), $2 );
87 -- DECREMENTING serial starts at -1
88 CREATE SEQUENCE metabib.uncontrolled_record_attr_value_id_seq INCREMENT BY -1;
90 CREATE TABLE metabib.uncontrolled_record_attr_value (
91 id BIGINT PRIMARY KEY DEFAULT nextval('metabib.uncontrolled_record_attr_value_id_seq'),
92 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name),
95 CREATE UNIQUE INDEX muv_once_idx ON metabib.uncontrolled_record_attr_value (attr,value);
97 CREATE TABLE metabib.record_attr_vector_list (
98 source BIGINT PRIMARY KEY REFERENCES biblio.record_entry (id),
99 vlist INT[] NOT NULL -- stores id from ccvm AND murav
101 CREATE INDEX mrca_vlist_idx ON metabib.record_attr_vector_list USING gin ( vlist gin__int_ops );
103 CREATE TABLE metabib.record_sorter (
104 id BIGSERIAL PRIMARY KEY,
105 source BIGINT NOT NULL REFERENCES biblio.record_entry (id) ON DELETE CASCADE,
106 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE,
109 CREATE INDEX metabib_sorter_source_idx ON metabib.record_sorter (source); -- we may not need one of this or the next ... stats will tell
110 CREATE INDEX metabib_sorter_s_a_idx ON metabib.record_sorter (source, attr);
111 CREATE INDEX metabib_sorter_a_v_idx ON metabib.record_sorter (attr, value);
113 CREATE TEMP TABLE attr_set ON COMMIT DROP AS SELECT DISTINCT id AS source, (each(attrs)).key,(each(attrs)).value FROM metabib.record_attr;
114 DELETE FROM attr_set WHERE BTRIM(value) = '';
116 -- Grab sort values for the new sorting mechanism
117 INSERT INTO metabib.record_sorter (source,attr,value)
118 SELECT a.source, a.key, a.value
120 JOIN config.record_attr_definition d ON (d.name = a.key AND d.sorter AND a.value IS NOT NULL);
122 -- Rewrite uncontrolled SVF record attrs as the seeds of an intarray vector
123 INSERT INTO metabib.uncontrolled_record_attr_value (attr,value)
124 SELECT DISTINCT a.key, a.value
126 JOIN config.record_attr_definition d ON (d.name = a.key AND d.filter AND a.value IS NOT NULL)
127 LEFT JOIN config.coded_value_map m ON (m.ctype = a.key)
130 -- Now construct the record-specific vector from the SVF data
131 INSERT INTO metabib.record_attr_vector_list (source,vlist)
132 SELECT a.id, ARRAY_AGG(COALESCE(u.id, c.id))
133 FROM metabib.record_attr a
134 JOIN attr_set ON (a.id = attr_set.source)
135 LEFT JOIN metabib.uncontrolled_record_attr_value u ON (u.attr = attr_set.key AND u.value = attr_set.value)
136 LEFT JOIN config.coded_value_map c ON (c.ctype = attr_set.key AND c.code = attr_set.value)
137 WHERE COALESCE(u.id,c.id) IS NOT NULL
140 DROP VIEW metabib.rec_descriptor;
141 DROP TABLE metabib.record_attr;
143 CREATE TYPE metabib.record_attr_type AS (
148 CREATE TABLE config.composite_attr_entry_definition(
149 coded_value INT PRIMARY KEY NOT NULL REFERENCES config.coded_value_map (id) ON UPDATE CASCADE ON DELETE CASCADE,
150 definition TEXT NOT NULL -- JSON
153 CREATE OR REPLACE VIEW metabib.record_attr_id_map AS
154 SELECT id, attr, value FROM metabib.uncontrolled_record_attr_value
156 SELECT c.id, c.ctype AS attr, c.code AS value
157 FROM config.coded_value_map c
158 JOIN config.record_attr_definition d ON (d.name = c.ctype AND NOT d.composite);
160 CREATE VIEW metabib.composite_attr_id_map AS
161 SELECT c.id, c.ctype AS attr, c.code AS value
162 FROM config.coded_value_map c
163 JOIN config.record_attr_definition d ON (d.name = c.ctype AND d.composite);
165 CREATE OR REPLACE VIEW metabib.full_attr_id_map AS
166 SELECT id, attr, value FROM metabib.record_attr_id_map
168 SELECT id, attr, value FROM metabib.composite_attr_id_map;
171 -- Back-compat view ... we're moving to an INTARRAY world
172 CREATE VIEW metabib.record_attr_flat AS
173 SELECT v.source AS id,
176 FROM metabib.full_attr_id_map m
177 JOIN metabib.record_attr_vector_list v ON ( m.id = ANY( v.vlist ) );
179 CREATE VIEW metabib.record_attr AS
180 SELECT id, HSTORE( ARRAY_AGG( attr ), ARRAY_AGG( value ) ) AS attrs FROM metabib.record_attr_flat GROUP BY 1;
182 CREATE VIEW metabib.rec_descriptor AS
185 (populate_record(NULL::metabib.rec_desc_type, attrs)).*
186 FROM metabib.record_attr;
188 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr_cache_init () RETURNS BOOL AS $f$
189 $_SHARED{metabib_compile_composite_attr_cache} = {}
190 if ! exists $_SHARED{metabib_compile_composite_attr_cache};
191 return exists $_SHARED{metabib_compile_composite_attr_cache};
192 $f$ LANGUAGE PLPERLU;
194 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr_cache_disable () RETURNS BOOL AS $f$
195 delete $_SHARED{metabib_compile_composite_attr_cache};
196 return ! exists $_SHARED{metabib_compile_composite_attr_cache};
197 $f$ LANGUAGE PLPERLU;
199 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr_cache_invalidate () RETURNS BOOL AS $f$
200 SELECT metabib.compile_composite_attr_cache_disable() AND metabib.compile_composite_attr_cache_init();
203 CREATE OR REPLACE FUNCTION metabib.composite_attr_def_cache_inval_tgr () RETURNS TRIGGER AS $f$
205 PERFORM metabib.compile_composite_attr_cache_invalidate();
208 $f$ LANGUAGE PLPGSQL;
210 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();
212 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr ( cattr_def TEXT ) RETURNS query_int AS $func$
217 my $def = decode_json($json);
219 die("Composite attribute definition not supplied") unless $def;
221 my $_cache = (exists $_SHARED{metabib_compile_composite_attr_cache}) ? 1 : 0;
223 return $_SHARED{metabib_compile_composite_attr_cache}{$json}
224 if ($_cache && $_SHARED{metabib_compile_composite_attr_cache}{$json});
231 if (ref $d eq 'HASH') { # node or AND
232 if (exists $d->{_attr}) { # it is a node
233 my $plan = spi_prepare('SELECT * FROM metabib.full_attr_id_map WHERE attr = $1 AND value = $2', qw/TEXT TEXT/);
234 my $id = spi_exec_prepared(
235 $plan, {limit => 1}, $d->{_attr}, $d->{_val}
239 } elsif (exists $d->{_not} && scalar(keys(%$d)) == 1) { # it is a NOT
240 return '!' . recurse($$d{_not});
241 } else { # an AND list
242 @list = map { recurse($$d{$_}) } sort keys %$d;
244 } elsif (ref $d eq 'ARRAY') {
246 @list = map { recurse($_) } @$d;
249 @list = grep { defined && $_ ne '' } @list;
251 return '(' . join($j,@list) . ')' if @list;
255 my $val = recurse($def) || undef;
256 $_SHARED{metabib_compile_composite_attr_cache}{$json} = $val if $_cache;
259 $func$ IMMUTABLE LANGUAGE plperlu;
261 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr ( cattr_id INT ) RETURNS query_int AS $func$
262 SELECT metabib.compile_composite_attr(definition) FROM config.composite_attr_entry_definition WHERE coded_value = $1;
263 $func$ STRICT IMMUTABLE LANGUAGE SQL;
265 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$
267 transformed_xml TEXT;
268 rmarc TEXT := prmarc;
272 xfrm config.xml_transform%ROWTYPE;
273 attr_vector INT[] := '{}'::INT[];
274 attr_vector_tmp INT[];
275 attr_list TEXT[] := pattr_list;
277 norm_attr_value TEXT[];
279 attr_def config.record_attr_definition%ROWTYPE;
280 ccvm_row config.coded_value_map%ROWTYPE;
283 IF attr_list IS NULL OR rdeleted THEN -- need to do the full dance on INSERT or undelete
284 SELECT ARRAY_AGG(name) INTO attr_list FROM config.record_attr_definition;
287 IF rmarc IS NULL THEN
288 SELECT marc INTO rmarc FROM biblio.record_entry WHERE id = rid;
291 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE NOT composite AND name = ANY( attr_list ) ORDER BY format LOOP
293 attr_value := '{}'::TEXT[];
294 norm_attr_value := '{}'::TEXT[];
295 attr_vector_tmp := '{}'::INT[];
297 SELECT * INTO ccvm_row FROM config.coded_value_map c WHERE c.ctype = attr_def.name LIMIT 1;
299 -- tag+sf attrs only support SVF
300 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
301 SELECT ARRAY[ARRAY_TO_STRING(ARRAY_AGG(value), COALESCE(attr_def.joiner,' '))] INTO attr_value
302 FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x
304 AND tag LIKE attr_def.tag
306 WHEN attr_def.sf_list IS NOT NULL
307 THEN POSITION(subfield IN attr_def.sf_list) > 0
314 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
315 attr_value := vandelay.marc21_extract_fixed_field_list(rmarc, attr_def.fixed_field);
317 IF NOT attr_def.multi THEN
318 attr_value := ARRAY[attr_value[1]];
321 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
323 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
325 -- See if we can skip the XSLT ... it's expensive
326 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
327 -- Can't skip the transform
328 IF xfrm.xslt <> '---' THEN
329 transformed_xml := oils_xslt_process(rmarc,xfrm.xslt);
331 transformed_xml := rmarc;
334 prev_xfrm := xfrm.name;
337 IF xfrm.name IS NULL THEN
338 -- just grab the marcxml (empty) transform
339 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
340 prev_xfrm := xfrm.name;
343 FOR tmp_xml IN SELECT XPATH(attr_def.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]) LOOP
344 tmp_val := oils_xpath_string(
347 COALESCE(attr_def.joiner,' '),
348 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
350 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
351 attr_value := attr_value || tmp_val;
352 EXIT WHEN NOT attr_def.multi;
356 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
357 SELECT ARRAY_AGG(m.value) INTO attr_value
358 FROM vandelay.marc21_physical_characteristics(rmarc) v
359 LEFT JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
360 WHERE v.subfield = attr_def.phys_char_sf AND (m.value IS NOT NULL AND BTRIM(m.value) <> '')
361 AND ( ccvm_row.id IS NULL OR ( ccvm_row.id IS NOT NULL AND v.id IS NOT NULL) );
363 IF NOT attr_def.multi THEN
364 attr_value := ARRAY[attr_value[1]];
369 -- apply index normalizers to attr_value
370 FOR tmp_val IN SELECT value FROM UNNEST(attr_value) x(value) LOOP
372 SELECT n.func AS func,
373 n.param_count AS param_count,
375 FROM config.index_normalizer n
376 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
377 WHERE attr = attr_def.name
379 EXECUTE 'SELECT ' || normalizer.func || '(' ||
380 COALESCE( quote_literal( tmp_val ), 'NULL' ) ||
382 WHEN normalizer.param_count > 0
383 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
389 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
390 norm_attr_value := norm_attr_value || tmp_val;
394 IF attr_def.filter THEN
395 -- Create unknown uncontrolled values and find the IDs of the values
396 IF ccvm_row.id IS NULL THEN
397 FOR tmp_val IN SELECT value FROM UNNEST(norm_attr_value) x(value) LOOP
398 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
399 BEGIN -- use subtransaction to isolate unique constraint violations
400 INSERT INTO metabib.uncontrolled_record_attr_value ( attr, value ) VALUES ( attr_def.name, tmp_val );
401 EXCEPTION WHEN unique_violation THEN END;
405 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 );
407 SELECT ARRAY_AGG(id) INTO attr_vector_tmp FROM config.coded_value_map WHERE ctype = attr_def.name AND code = ANY( norm_attr_value );
410 -- Add the new value to the vector
411 attr_vector := attr_vector || attr_vector_tmp;
414 IF attr_def.sorter AND norm_attr_value[1] IS NOT NULL THEN
415 DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
416 INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, norm_attr_value[1]);
421 /* We may need to rewrite the vlist to contain
422 the intersection of new values for requested
423 attrs and old values for ignored attrs. To
424 do this, we take the old attr vlist and
425 subtract any values that are valid for the
426 requested attrs, and then add back the new
427 set of attr values. */
429 IF ARRAY_LENGTH(pattr_list, 1) > 0 THEN
430 SELECT vlist INTO attr_vector_tmp FROM metabib.record_attr_vector_list WHERE source = rid;
431 SELECT attr_vector_tmp - ARRAY_AGG(id::INT) INTO attr_vector_tmp FROM metabib.full_attr_id_map WHERE attr = ANY (pattr_list);
432 attr_vector := attr_vector || attr_vector_tmp;
435 -- On to composite attributes, now that the record attrs have been pulled. Processed in name order, so later composite
436 -- attributes can depend on earlier ones.
437 PERFORM metabib.compile_composite_attr_cache_init();
438 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE composite AND name = ANY( attr_list ) ORDER BY name LOOP
440 FOR ccvm_row IN SELECT * FROM config.coded_value_map c WHERE c.ctype = attr_def.name ORDER BY value LOOP
442 tmp_val := metabib.compile_composite_attr( ccvm_row.id );
443 CONTINUE WHEN tmp_val IS NULL OR tmp_val = ''; -- nothing to do
445 IF attr_def.filter THEN
446 IF attr_vector @@ tmp_val::query_int THEN
447 attr_vector = attr_vector + intset(ccvm_row.id);
448 EXIT WHEN NOT attr_def.multi;
452 IF attr_def.sorter THEN
453 IF attr_vector @@ tmp_val THEN
454 DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
455 INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, ccvm_row.code);
463 IF ARRAY_LENGTH(attr_vector, 1) > 0 THEN
464 IF rdeleted THEN -- initial insert OR revivication
465 DELETE FROM metabib.record_attr_vector_list WHERE source = rid;
466 INSERT INTO metabib.record_attr_vector_list (source, vlist) VALUES (rid, attr_vector);
468 UPDATE metabib.record_attr_vector_list SET vlist = attr_vector WHERE source = rid;
474 $func$ LANGUAGE PLPGSQL;
476 -- AFTER UPDATE OR INSERT trigger for biblio.record_entry
477 CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
480 IF NEW.deleted THEN -- If this bib is deleted
481 PERFORM * FROM config.internal_flag WHERE
482 name = 'ingest.metarecord_mapping.preserve_on_delete' AND enabled;
484 -- One needs to keep these around to support searches
485 -- with the #deleted modifier, so one should turn on the named
486 -- internal flag for that functionality.
487 DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id;
488 DELETE FROM metabib.record_attr_vector_list WHERE source = NEW.id;
491 DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible
492 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = NEW.id; -- Separate any multi-homed items
493 DELETE FROM metabib.browse_entry_def_map WHERE source = NEW.id; -- Don't auto-suggest deleted bibs
494 RETURN NEW; -- and we're done
497 IF TG_OP = 'UPDATE' THEN -- re-ingest?
498 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
500 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
505 -- Record authority linking
506 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking' AND enabled;
508 PERFORM biblio.map_authority_linking( NEW.id, NEW.marc );
511 -- Flatten and insert the mfr data
512 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled;
514 PERFORM metabib.reingest_metabib_full_rec(NEW.id);
516 -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields
517 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled;
519 PERFORM metabib.reingest_record_attributes(NEW.id, NULL, NEW.marc, TG_OP = 'INSERT' OR OLD.deleted);
523 -- Gather and insert the field entry data
524 PERFORM metabib.reingest_metabib_field_entries(NEW.id);
527 IF TG_OP = 'INSERT' THEN
528 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
530 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
533 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
535 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
539 -- (re)map metarecord-bib linking
540 IF TG_OP = 'INSERT' THEN -- if not deleted and performing an insert, check for the flag
541 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled;
543 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
545 ELSE -- we're doing an update, and we're not deleted, remap
546 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_update' AND enabled;
548 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
554 $func$ LANGUAGE PLPGSQL;
556 CREATE OR REPLACE FUNCTION public.oils_tsearch2 () RETURNS TRIGGER AS $$
560 temp_vector TEXT := '';
566 NEW.index_vector = ''::tsvector;
568 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
570 SELECT n.func AS func,
571 n.param_count AS param_count,
573 FROM config.index_normalizer n
574 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
575 WHERE field = NEW.field AND m.pos < 0
577 EXECUTE 'SELECT ' || normalizer.func || '(' ||
578 quote_literal( value ) ||
580 WHEN normalizer.param_count > 0
581 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
591 SELECT n.func AS func,
592 n.param_count AS param_count,
594 FROM config.index_normalizer n
595 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
596 WHERE field = NEW.field AND m.pos >= 0
598 EXECUTE 'SELECT ' || normalizer.func || '(' ||
599 quote_literal( value ) ||
601 WHEN normalizer.param_count > 0
602 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
610 IF TG_TABLE_NAME::TEXT ~ 'browse_entry$' THEN
612 value := ARRAY_TO_STRING(
613 evergreen.regexp_split_to_array(value, E'\\W+'), ' '
615 value := public.search_normalize(value);
616 NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);
618 ELSIF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
621 SELECT DISTINCT m.ts_config, m.index_weight
622 FROM config.metabib_class_ts_map m
623 LEFT JOIN metabib.record_attr_vector_list r ON (r.source = NEW.source)
624 LEFT JOIN config.coded_value_map ccvm ON (
625 ccvm.ctype IN ('item_lang', 'language') AND
626 ccvm.code = m.index_lang AND
627 r.vlist @> intset(ccvm.id)
629 WHERE m.field_class = TG_ARGV[0]
631 AND (m.always OR NOT EXISTS (SELECT 1 FROM config.metabib_field_ts_map WHERE metabib_field = NEW.field))
632 AND (m.index_lang IS NULL OR ccvm.id IS NOT NULL)
634 SELECT DISTINCT m.ts_config, m.index_weight
635 FROM config.metabib_field_ts_map m
636 LEFT JOIN metabib.record_attr_vector_list r ON (r.source = NEW.source)
637 LEFT JOIN config.coded_value_map ccvm ON (
638 ccvm.ctype IN ('item_lang', 'language') AND
639 ccvm.code = m.index_lang AND
640 r.vlist @> intset(ccvm.id)
642 WHERE m.metabib_field = NEW.field
644 AND (m.index_lang IS NULL OR ccvm.id IS NOT NULL)
645 ORDER BY index_weight ASC
649 IF cur_weight IS NOT NULL AND cur_weight != ts_rec.index_weight THEN
650 NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
654 cur_weight = ts_rec.index_weight;
655 SELECT INTO temp_vector temp_vector || ' ' || to_tsvector(ts_rec.ts_config::regconfig, value)::TEXT;
658 NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
660 NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);
667 -- add new sr_format attribute definition
669 INSERT INTO config.record_attr_definition (name, label, phys_char_sf)
672 oils_i18n_gettext('sr_format', 'Sound recording format', 'crad', 'label'),
676 INSERT INTO config.coded_value_map (id, ctype, code, value) VALUES
677 (557, 'sr_format', 'a', oils_i18n_gettext(557, '16 rpm', 'ccvm', 'value')),
678 (558, 'sr_format', 'b', oils_i18n_gettext(558, '33 1/3 rpm', 'ccvm', 'value')),
679 (559, 'sr_format', 'c', oils_i18n_gettext(559, '45 rpm', 'ccvm', 'value')),
680 (560, 'sr_format', 'f', oils_i18n_gettext(560, '1.4 m. per second', 'ccvm', 'value')),
681 (561, 'sr_format', 'd', oils_i18n_gettext(561, '78 rpm', 'ccvm', 'value')),
682 (562, 'sr_format', 'e', oils_i18n_gettext(562, '8 rpm', 'ccvm', 'value')),
683 (563, 'sr_format', 'l', oils_i18n_gettext(563, '1 7/8 ips', 'ccvm', 'value')),
684 (586, 'item_form', 'o', oils_i18n_gettext('586', 'Online', 'ccvm', 'value')),
685 (587, 'item_form', 'q', oils_i18n_gettext('587', 'Direct electronic', 'ccvm', 'value'));
687 INSERT INTO config.coded_value_map
688 (id, ctype, code, value, search_label) VALUES
689 (564, 'icon_format', 'book',
690 oils_i18n_gettext(564, 'Book', 'ccvm', 'value'),
691 oils_i18n_gettext(564, 'Book', 'ccvm', 'search_label')),
692 (565, 'icon_format', 'braille',
693 oils_i18n_gettext(565, 'Braille', 'ccvm', 'value'),
694 oils_i18n_gettext(565, 'Braille', 'ccvm', 'search_label')),
695 (566, 'icon_format', 'software',
696 oils_i18n_gettext(566, 'Software and video games', 'ccvm', 'value'),
697 oils_i18n_gettext(566, 'Software and video games', 'ccvm', 'search_label')),
698 (567, 'icon_format', 'dvd',
699 oils_i18n_gettext(567, 'DVD', 'ccvm', 'value'),
700 oils_i18n_gettext(567, 'DVD', 'ccvm', 'search_label')),
701 (568, 'icon_format', 'ebook',
702 oils_i18n_gettext(568, 'E-book', 'ccvm', 'value'),
703 oils_i18n_gettext(568, 'E-book', 'ccvm', 'search_label')),
704 (569, 'icon_format', 'eaudio',
705 oils_i18n_gettext(569, 'E-audio', 'ccvm', 'value'),
706 oils_i18n_gettext(569, 'E-audio', 'ccvm', 'search_label')),
707 (570, 'icon_format', 'kit',
708 oils_i18n_gettext(570, 'Kit', 'ccvm', 'value'),
709 oils_i18n_gettext(570, 'Kit', 'ccvm', 'search_label')),
710 (571, 'icon_format', 'map',
711 oils_i18n_gettext(571, 'Map', 'ccvm', 'value'),
712 oils_i18n_gettext(571, 'Map', 'ccvm', 'search_label')),
713 (572, 'icon_format', 'microform',
714 oils_i18n_gettext(572, 'Microform', 'ccvm', 'value'),
715 oils_i18n_gettext(572, 'Microform', 'ccvm', 'search_label')),
716 (573, 'icon_format', 'score',
717 oils_i18n_gettext(573, 'Music Score', 'ccvm', 'value'),
718 oils_i18n_gettext(573, 'Music Score', 'ccvm', 'search_label')),
719 (574, 'icon_format', 'picture',
720 oils_i18n_gettext(574, 'Picture', 'ccvm', 'value'),
721 oils_i18n_gettext(574, 'Picture', 'ccvm', 'search_label')),
722 (575, 'icon_format', 'equip',
723 oils_i18n_gettext(575, 'Equipment, games, toys', 'ccvm', 'value'),
724 oils_i18n_gettext(575, 'Equipment, games, toys', 'ccvm', 'search_label')),
725 (576, 'icon_format', 'serial',
726 oils_i18n_gettext(576, 'Serials and magazines', 'ccvm', 'value'),
727 oils_i18n_gettext(576, 'Serials and magazines', 'ccvm', 'search_label')),
728 (577, 'icon_format', 'vhs',
729 oils_i18n_gettext(577, 'VHS', 'ccvm', 'value'),
730 oils_i18n_gettext(577, 'VHS', 'ccvm', 'search_label')),
731 (578, 'icon_format', 'evideo',
732 oils_i18n_gettext(578, 'E-video', 'ccvm', 'value'),
733 oils_i18n_gettext(578, 'E-video', 'ccvm', 'search_label')),
734 (579, 'icon_format', 'cdaudiobook',
735 oils_i18n_gettext(579, 'CD Audiobook', 'ccvm', 'value'),
736 oils_i18n_gettext(579, 'CD Audiobook', 'ccvm', 'search_label')),
737 (580, 'icon_format', 'cdmusic',
738 oils_i18n_gettext(580, 'CD Music recording', 'ccvm', 'value'),
739 oils_i18n_gettext(580, 'CD Music recording', 'ccvm', 'search_label')),
740 (581, 'icon_format', 'casaudiobook',
741 oils_i18n_gettext(581, 'Cassette audiobook', 'ccvm', 'value'),
742 oils_i18n_gettext(581, 'Cassette audiobook', 'ccvm', 'search_label')),
743 (582, 'icon_format', 'casmusic',
744 oils_i18n_gettext(582, 'Audiocassette music recording', 'ccvm', 'value'),
745 oils_i18n_gettext(582, 'Audiocassette music recording', 'ccvm', 'search_label')),
746 (583, 'icon_format', 'phonospoken',
747 oils_i18n_gettext(583, 'Phonograph spoken recording', 'ccvm', 'value'),
748 oils_i18n_gettext(583, 'Phonograph spoken recording', 'ccvm', 'search_label')),
749 (584, 'icon_format', 'phonomusic',
750 oils_i18n_gettext(584, 'Phonograph music recording', 'ccvm', 'value'),
751 oils_i18n_gettext(584, 'Phonograph music recording', 'ccvm', 'search_label')),
752 (585, 'icon_format', 'lpbook',
753 oils_i18n_gettext(585, 'Large Print Book', 'ccvm', 'value'),
754 oils_i18n_gettext(585, 'Large Print Book', 'ccvm', 'search_label'))
757 -- add the new icon format attribute definition
759 INSERT INTO config.global_flag (name, label, value, enabled) VALUES (
763 'OPAC Format Icons Attribute',
771 INSERT INTO config.record_attr_definition
772 (name, label, multi, filter, composite) VALUES (
783 -- icon format composite definitions
785 INSERT INTO config.composite_attr_entry_definition
786 (coded_value, definition) VALUES
788 (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"}]}'),
791 (565, '{"0":{"_attr":"item_type","_val":"a"},"1":{"_attr":"item_form","_val":"f"}}'),
794 (566, '{"_attr":"item_type","_val":"m"}'),
797 (567, '{"_attr":"vr_format","_val":"v"}'),
800 (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"}]}'),
803 (569, '{"0":{"_attr":"item_type","_val":"i"},"1":[{"_attr":"item_form","_val":"o"},{"_attr":"item_form","_val":"q"},{"_attr":"item_form","_val":"s"}]}'),
806 (570, '[{"_attr":"item_type","_val":"o"},{"_attr":"item_type","_val":"p"}]'),
809 (571, '[{"_attr":"item_type","_val":"e"},{"_attr":"item_type","_val":"f"}]'),
812 (572, '[{"_attr":"item_form","_val":"a"},{"_attr":"item_form","_val":"b"},{"_attr":"item_form","_val":"c"}]'),
815 (573, '[{"_attr":"item_type","_val":"c"},{"_attr":"item_type","_val":"d"}]'),
818 (574, '{"_attr":"item_type","_val":"k"}'),
821 (575, '{"_attr":"item_type","_val":"r"}'),
824 (576, '[{"_attr":"bib_level","_val":"b"},{"_attr":"bib_level","_val":"s"}]'),
827 (577, '{"_attr":"vr_format","_val":"b"}'),
830 (578, '{"0":{"_attr":"item_type","_val":"g"},"1":[{"_attr":"item_form","_val":"o"},{"_attr":"item_form","_val":"s"},{"_attr":"item_form","_val":"q"}]}'),
833 (579, '{"0":{"_attr":"item_type","_val":"i"},"1":{"_attr":"sr_format","_val":"f"}}'),
836 (580, '{"0":{"_attr":"item_type","_val":"j"},"1":{"_attr":"sr_format","_val":"f"}}'),
839 (581, '{"0":{"_attr":"item_type","_val":"i"},"1":{"_attr":"sr_format","_val":"l"}}'),
842 (582, '{"0":{"_attr":"item_type","_val":"j"},"1":{"_attr":"sr_format","_val":"l"}}'),
845 (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"}]}'),
848 (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"}]}'),
851 (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"}]}');
856 CREATE OR REPLACE FUNCTION unapi.mra (
862 depth INT DEFAULT NULL,
863 slimit HSTORE DEFAULT NULL,
864 soffset HSTORE DEFAULT NULL,
865 include_xmlns BOOL DEFAULT TRUE
870 CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns,
871 'tag:open-ils.org:U2@mra/' || $1 AS id,
872 'tag:open-ils.org:U2@bre/' || $1 AS record
874 (SELECT XMLAGG(foo.y)
880 cvm.value AS "coded-value",
889 FROM metabib.record_attr_flat mra
890 JOIN config.record_attr_definition rad ON (mra.attr = rad.name)
891 LEFT JOIN config.coded_value_map cvm ON (cvm.ctype = mra.attr AND code = mra.value)
896 $F$ LANGUAGE SQL STABLE;