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 ( cattr_def TEXT ) RETURNS query_int AS $func$
191 my $def = decode_json(shift);
193 die("Composite attribute definition not supplied") unless $def;
200 if (ref $d eq 'HASH') { # node or AND
201 if (exists $d->{_attr}) { # it is a node
202 my $plan = spi_prepare('SELECT * FROM metabib.full_attr_id_map WHERE attr = $1 AND value = $2', qw/TEXT TEXT/);
203 return spi_exec_prepared(
204 $plan, {limit => 1}, $d->{_attr}, $d->{_val}
207 } elsif (exists $d->{_not} && scalar(keys(%$d)) == 1) { # it is a NOT
208 return '!' . recurse($$d{_not});
209 } else { # an AND list
210 @list = map { recurse($$d{$_}) } sort keys %$d;
212 } elsif (ref $d eq 'ARRAY') {
214 @list = map { recurse($_) } @$d;
217 @list = grep { defined && $_ ne '' } @list;
219 return '(' . join($j,@list) . ')' if @list;
223 return recurse($def) || undef;
225 $func$ IMMUTABLE LANGUAGE plperlu;
227 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr ( cattr_id INT ) RETURNS query_int AS $func$
228 SELECT metabib.compile_composite_attr(definition) FROM config.composite_attr_entry_definition WHERE coded_value = $1;
229 $func$ STRICT IMMUTABLE LANGUAGE SQL;
231 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$
233 transformed_xml TEXT;
234 rmarc TEXT := prmarc;
238 xfrm config.xml_transform%ROWTYPE;
239 attr_vector INT[] := '{}'::INT[];
240 attr_vector_tmp INT[];
241 attr_list TEXT[] := pattr_list;
243 norm_attr_value TEXT[];
245 attr_def config.record_attr_definition%ROWTYPE;
246 ccvm_row config.coded_value_map%ROWTYPE;
249 IF attr_list IS NULL OR rdeleted THEN -- need to do the full dance on INSERT or undelete
250 SELECT ARRAY_AGG(name) INTO attr_list FROM config.record_attr_definition;
253 IF rmarc IS NULL THEN
254 SELECT marc INTO rmarc FROM biblio.record_entry WHERE id = rid;
257 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE NOT composite AND name = ANY( attr_list ) ORDER BY format LOOP
259 attr_value := '{}'::TEXT[];
260 norm_attr_value := '{}'::TEXT[];
261 attr_vector_tmp := '{}'::INT[];
263 SELECT * INTO ccvm_row FROM config.coded_value_map c WHERE c.ctype = attr_def.name LIMIT 1;
265 -- tag+sf attrs only support SVF
266 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
267 SELECT ARRAY[ARRAY_TO_STRING(ARRAY_AGG(value), COALESCE(attr_def.joiner,' '))] INTO attr_value
268 FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x
270 AND tag LIKE attr_def.tag
272 WHEN attr_def.sf_list IS NOT NULL
273 THEN POSITION(subfield IN attr_def.sf_list) > 0
280 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
281 attr_value := vandelay.marc21_extract_fixed_field_list(rmarc, attr_def.fixed_field);
283 IF NOT attr_def.multi THEN
284 attr_value := ARRAY[attr_value[1]];
287 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
289 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
291 -- See if we can skip the XSLT ... it's expensive
292 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
293 -- Can't skip the transform
294 IF xfrm.xslt <> '---' THEN
295 transformed_xml := oils_xslt_process(rmarc,xfrm.xslt);
297 transformed_xml := rmarc;
300 prev_xfrm := xfrm.name;
303 IF xfrm.name IS NULL THEN
304 -- just grab the marcxml (empty) transform
305 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
306 prev_xfrm := xfrm.name;
309 FOR tmp_xml IN SELECT XPATH(attr_def.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]) LOOP
310 tmp_val := oils_xpath_string(
313 COALESCE(attr_def.joiner,' '),
314 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
316 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
317 attr_value := attr_value || tmp_val;
318 EXIT WHEN NOT attr_def.multi;
322 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
323 SELECT ARRAY_AGG(m.value) INTO attr_value
324 FROM vandelay.marc21_physical_characteristics(rmarc) v
325 LEFT JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
326 WHERE v.subfield = attr_def.phys_char_sf AND (m.value IS NOT NULL AND BTRIM(m.value) <> '')
327 AND ( ccvm_row.id IS NULL OR ( ccvm_row.id IS NOT NULL AND v.id IS NOT NULL) );
329 IF NOT attr_def.multi THEN
330 attr_value := ARRAY[attr_value[1]];
335 -- apply index normalizers to attr_value
336 FOR tmp_val IN SELECT value FROM UNNEST(attr_value) x(value) LOOP
338 SELECT n.func AS func,
339 n.param_count AS param_count,
341 FROM config.index_normalizer n
342 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
343 WHERE attr = attr_def.name
345 EXECUTE 'SELECT ' || normalizer.func || '(' ||
346 COALESCE( quote_literal( tmp_val ), 'NULL' ) ||
348 WHEN normalizer.param_count > 0
349 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
355 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
356 norm_attr_value := norm_attr_value || tmp_val;
360 IF attr_def.filter THEN
361 -- Create unknown uncontrolled values and find the IDs of the values
362 IF ccvm_row.id IS NULL THEN
363 FOR tmp_val IN SELECT value FROM UNNEST(norm_attr_value) x(value) LOOP
364 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
365 BEGIN -- use subtransaction to isolate unique constraint violations
366 INSERT INTO metabib.uncontrolled_record_attr_value ( attr, value ) VALUES ( attr_def.name, tmp_val );
367 EXCEPTION WHEN unique_violation THEN END;
371 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 );
373 SELECT ARRAY_AGG(id) INTO attr_vector_tmp FROM config.coded_value_map WHERE ctype = attr_def.name AND code = ANY( norm_attr_value );
376 -- Add the new value to the vector
377 attr_vector := attr_vector || attr_vector_tmp;
380 IF attr_def.sorter AND norm_attr_value[1] IS NOT NULL THEN
381 DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
382 INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, norm_attr_value[1]);
387 /* We may need to rewrite the vlist to contain
388 the intersection of new values for requested
389 attrs and old values for ignored attrs. To
390 do this, we take the old attr vlist and
391 subtract any values that are valid for the
392 requested attrs, and then add back the new
393 set of attr values. */
395 IF ARRAY_LENGTH(pattr_list, 1) > 0 THEN
396 SELECT vlist INTO attr_vector_tmp FROM metabib.record_attr_vector_list WHERE source = rid;
397 SELECT attr_vector_tmp - ARRAY_AGG(id) INTO attr_vector_tmp FROM metabib.full_attr_id_map WHERE attr = ANY (pattr_list);
398 attr_vector := attr_vector || attr_vector_tmp;
401 -- On to composite attributes, now that the record attrs have been pulled. Processed in name order, so later composite
402 -- attributes can depend on earlier ones.
403 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE composite AND name = ANY( attr_list ) ORDER BY name LOOP
405 FOR ccvm_row IN SELECT * FROM config.coded_value_map c WHERE c.ctype = attr_def.name ORDER BY value LOOP
407 tmp_val := metabib.compile_composite_attr( ccvm_row.id );
408 CONTINUE WHEN tmp_val IS NULL OR tmp_val = ''; -- nothing to do
410 IF attr_def.filter THEN
411 IF attr_vector @@ tmp_val::query_int THEN
412 attr_vector = attr_vector + intset(ccvm_row.id);
413 EXIT WHEN NOT attr_def.multi;
417 IF attr_def.sorter THEN
418 IF attr_vector ~~ tmp_val THEN
419 DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
420 INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, ccvm_row.code);
428 IF ARRAY_LENGTH(attr_vector, 1) > 0 THEN
429 IF rdeleted THEN -- initial insert OR revivication
430 DELETE FROM metabib.record_attr_vector_list WHERE source = rid;
431 INSERT INTO metabib.record_attr_vector_list (source, vlist) VALUES (rid, attr_vector);
433 UPDATE metabib.record_attr_vector_list SET vlist = attr_vector WHERE source = rid;
439 $func$ LANGUAGE PLPGSQL;
441 -- AFTER UPDATE OR INSERT trigger for biblio.record_entry
442 CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
445 IF NEW.deleted THEN -- If this bib is deleted
446 PERFORM * FROM config.internal_flag WHERE
447 name = 'ingest.metarecord_mapping.preserve_on_delete' AND enabled;
449 -- One needs to keep these around to support searches
450 -- with the #deleted modifier, so one should turn on the named
451 -- internal flag for that functionality.
452 DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id;
453 DELETE FROM metabib.record_attr_vector_list WHERE source = NEW.id;
456 DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible
457 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = NEW.id; -- Separate any multi-homed items
458 DELETE FROM metabib.browse_entry_def_map WHERE source = NEW.id; -- Don't auto-suggest deleted bibs
459 RETURN NEW; -- and we're done
462 IF TG_OP = 'UPDATE' THEN -- re-ingest?
463 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
465 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
470 -- Record authority linking
471 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking' AND enabled;
473 PERFORM biblio.map_authority_linking( NEW.id, NEW.marc );
476 -- Flatten and insert the mfr data
477 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled;
479 PERFORM metabib.reingest_metabib_full_rec(NEW.id);
481 -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields
482 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled;
484 PERFORM metabib.reingest_record_attributes(NEW.id, NULL, NEW.marc, TG_OP = 'INSERT' OR OLD.deleted);
488 -- Gather and insert the field entry data
489 PERFORM metabib.reingest_metabib_field_entries(NEW.id);
492 IF TG_OP = 'INSERT' THEN
493 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
495 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
498 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
500 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
504 -- (re)map metarecord-bib linking
505 IF TG_OP = 'INSERT' THEN -- if not deleted and performing an insert, check for the flag
506 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled;
508 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
510 ELSE -- we're doing an update, and we're not deleted, remap
511 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_update' AND enabled;
513 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
519 $func$ LANGUAGE PLPGSQL;
521 -- add new sr_format attribute definition
523 INSERT INTO config.record_attr_definition (name, label, phys_char_sf)
526 oils_i18n_gettext('sr_format', 'Sound recording format', 'crad', 'label'),
530 INSERT INTO config.coded_value_map (id, ctype, code, value) VALUES
531 (557, 'sr_format', 'a', oils_i18n_gettext(557, '16 rpm', 'ccvm', 'value')),
532 (558, 'sr_format', 'b', oils_i18n_gettext(558, '33 1/3 rpm', 'ccvm', 'value')),
533 (559, 'sr_format', 'c', oils_i18n_gettext(559, '45 rpm', 'ccvm', 'value')),
534 (560, 'sr_format', 'f', oils_i18n_gettext(560, '1.4 m. per second', 'ccvm', 'value')),
535 (561, 'sr_format', 'd', oils_i18n_gettext(561, '78 rpm', 'ccvm', 'value')),
536 (562, 'sr_format', 'e', oils_i18n_gettext(562, '8 rpm', 'ccvm', 'value')),
537 (563, 'sr_format', 'l', oils_i18n_gettext(563, '1 7/8 ips', 'ccvm', 'value')),
538 (586, 'item_form', 'o', oils_i18n_gettext('586', 'Online', 'ccvm', 'value')),
539 (587, 'item_form', 'q', oils_i18n_gettext('587', 'Direct electronic', 'ccvm', 'value'));
541 INSERT INTO config.coded_value_map
542 (id, ctype, code, value, search_label) VALUES
543 (564, 'icon_format', 'book',
544 oils_i18n_gettext(564, 'Book', 'ccvm', 'value'),
545 oils_i18n_gettext(564, 'Book', 'ccvm', 'search_label')),
546 (565, 'icon_format', 'braille',
547 oils_i18n_gettext(565, 'Braille', 'ccvm', 'value'),
548 oils_i18n_gettext(565, 'Braille', 'ccvm', 'search_label')),
549 (566, 'icon_format', 'software',
550 oils_i18n_gettext(566, 'Software and video games', 'ccvm', 'value'),
551 oils_i18n_gettext(566, 'Software and video games', 'ccvm', 'search_label')),
552 (567, 'icon_format', 'dvd',
553 oils_i18n_gettext(567, 'DVD', 'ccvm', 'value'),
554 oils_i18n_gettext(567, 'DVD', 'ccvm', 'search_label')),
555 (568, 'icon_format', 'ebook',
556 oils_i18n_gettext(568, 'E-book', 'ccvm', 'value'),
557 oils_i18n_gettext(568, 'E-book', 'ccvm', 'search_label')),
558 (569, 'icon_format', 'eaudio',
559 oils_i18n_gettext(569, 'E-audio', 'ccvm', 'value'),
560 oils_i18n_gettext(569, 'E-audio', 'ccvm', 'search_label')),
561 (570, 'icon_format', 'kit',
562 oils_i18n_gettext(570, 'Kit', 'ccvm', 'value'),
563 oils_i18n_gettext(570, 'Kit', 'ccvm', 'search_label')),
564 (571, 'icon_format', 'map',
565 oils_i18n_gettext(571, 'Map', 'ccvm', 'value'),
566 oils_i18n_gettext(571, 'Map', 'ccvm', 'search_label')),
567 (572, 'icon_format', 'microform',
568 oils_i18n_gettext(572, 'Microform', 'ccvm', 'value'),
569 oils_i18n_gettext(572, 'Microform', 'ccvm', 'search_label')),
570 (573, 'icon_format', 'score',
571 oils_i18n_gettext(573, 'Music Score', 'ccvm', 'value'),
572 oils_i18n_gettext(573, 'Music Score', 'ccvm', 'search_label')),
573 (574, 'icon_format', 'picture',
574 oils_i18n_gettext(574, 'Picture', 'ccvm', 'value'),
575 oils_i18n_gettext(574, 'Picture', 'ccvm', 'search_label')),
576 (575, 'icon_format', 'equip',
577 oils_i18n_gettext(575, 'Equipment, games, toys', 'ccvm', 'value'),
578 oils_i18n_gettext(575, 'Equipment, games, toys', 'ccvm', 'search_label')),
579 (576, 'icon_format', 'serial',
580 oils_i18n_gettext(576, 'Serials and magazines', 'ccvm', 'value'),
581 oils_i18n_gettext(576, 'Serials and magazines', 'ccvm', 'search_label')),
582 (577, 'icon_format', 'vhs',
583 oils_i18n_gettext(577, 'VHS', 'ccvm', 'value'),
584 oils_i18n_gettext(577, 'VHS', 'ccvm', 'search_label')),
585 (578, 'icon_format', 'evideo',
586 oils_i18n_gettext(578, 'E-video', 'ccvm', 'value'),
587 oils_i18n_gettext(578, 'E-video', 'ccvm', 'search_label')),
588 (579, 'icon_format', 'cdaudiobook',
589 oils_i18n_gettext(579, 'CD Audiobook', 'ccvm', 'value'),
590 oils_i18n_gettext(579, 'CD Audiobook', 'ccvm', 'search_label')),
591 (580, 'icon_format', 'cdmusic',
592 oils_i18n_gettext(580, 'CD Music recording', 'ccvm', 'value'),
593 oils_i18n_gettext(580, 'CD Music recording', 'ccvm', 'search_label')),
594 (581, 'icon_format', 'casaudiobook',
595 oils_i18n_gettext(581, 'Cassette audiobook', 'ccvm', 'value'),
596 oils_i18n_gettext(581, 'Cassette audiobook', 'ccvm', 'search_label')),
597 (582, 'icon_format', 'casmusic',
598 oils_i18n_gettext(582, 'Audiocassette music recording', 'ccvm', 'value'),
599 oils_i18n_gettext(582, 'Audiocassette music recording', 'ccvm', 'search_label')),
600 (583, 'icon_format', 'phonospoken',
601 oils_i18n_gettext(583, 'Phonograph spoken recording', 'ccvm', 'value'),
602 oils_i18n_gettext(583, 'Phonograph spoken recording', 'ccvm', 'search_label')),
603 (584, 'icon_format', 'phonomusic',
604 oils_i18n_gettext(584, 'Phonograph music recording', 'ccvm', 'value'),
605 oils_i18n_gettext(584, 'Phonograph music recording', 'ccvm', 'search_label')),
606 (585, 'icon_format', 'lpbook',
607 oils_i18n_gettext(585, 'Large Print Book', 'ccvm', 'value'),
608 oils_i18n_gettext(585, 'Large Print Book', 'ccvm', 'search_label'))
611 -- add the new icon format attribute definition
613 INSERT INTO config.global_flag (name, label, value, enabled) VALUES (
617 'OPAC Format Icons Attribute',
625 INSERT INTO config.record_attr_definition
626 (name, label, multi, filter, composite) VALUES (
637 -- icon format composite definitions
639 INSERT INTO config.composite_attr_entry_definition
640 (coded_value, definition) VALUES
642 (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":"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"}]}'),
645 (565, '{"0":{"_attr":"item_type","_val":"a"},"1":{"_attr":"item_form","_val":"f"}}'),
648 (566, '{"_attr":"item_type","_val":"m"}'),
651 (567, '{"_attr":"vr_format","_val":"v"}'),
654 (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"}]}'),
657 (569, '{"0":{"_attr":"item_type","_val":"i"},"1":[{"_attr":"item_form","_val":"o"},{"_attr":"item_form","_val":"q"},{"_attr":"item_form","_val":"s"}]}'),
660 (570, '[{"_attr":"item_type","_val":"o"},{"_attr":"item_type","_val":"p"}]'),
663 (571, '[{"_attr":"item_type","_val":"e"},{"_attr":"item_type","_val":"f"}]'),
666 (572, '[{"_attr":"item_form","_val":"a"},{"_attr":"item_form","_val":"b"},{"_attr":"item_form","_val":"c"}]'),
669 (573, '[{"_attr":"item_type","_val":"c"},{"_attr":"item_type","_val":"d"}]'),
672 (574, '{"_attr":"item_type","_val":"k"}'),
675 (575, '{"_attr":"item_type","_val":"r"}'),
678 (576, '[{"_attr":"bib_level","_val":"b"},{"_attr":"bib_level","_val":"s"}]'),
681 (577, '{"_attr":"vr_format","_val":"b"}'),
684 (578, '{"0":{"_attr":"item_type","_val":"g"},"1":[{"_attr":"item_form","_val":"o"},{"_attr":"item_form","_val":"s"},{"_attr":"item_form","_val":"q"}]}'),
687 (579, '{"0":{"_attr":"item_type","_val":"i"},"1":{"_attr":"sr_format","_val":"f"}}'),
690 (580, '{"0":{"_attr":"item_type","_val":"j"},"1":{"_attr":"sr_format","_val":"f"}}'),
693 (581, '{"0":{"_attr":"item_type","_val":"i"},"1":{"_attr":"sr_format","_val":"l"}}'),
696 (582, '{"0":{"_attr":"item_type","_val":"j"},"1":{"_attr":"sr_format","_val":"l"}}'),
699 (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"}]}'),
702 (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"}]}'),
705 (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"}]}');
710 CREATE OR REPLACE FUNCTION unapi.mra (
716 depth INT DEFAULT NULL,
717 slimit HSTORE DEFAULT NULL,
718 soffset HSTORE DEFAULT NULL,
719 include_xmlns BOOL DEFAULT TRUE
724 CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns,
725 'tag:open-ils.org:U2@mra/' || $1 AS id,
726 'tag:open-ils.org:U2@bre/' || $1 AS record
728 (SELECT XMLAGG(foo.y)
734 cvm.value AS "coded-value",
743 FROM metabib.record_attr_flat mra
744 JOIN config.record_attr_definition rad ON (mra.attr = rad.name)
745 LEFT JOIN config.coded_value_map cvm ON (cvm.ctype = mra.attr AND code = mra.value)
750 $F$ LANGUAGE SQL STABLE;