2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2007-2008 Equinox Software, Inc.
4 * Mike Rylander <miker@esilibrary.com>
6 * This program is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU General Public License
8 * as published by the Free Software Foundation; either version 2
9 * of the License, or (at your option) any later version.
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
18 DROP SCHEMA IF EXISTS metabib CASCADE;
21 CREATE SCHEMA metabib;
23 CREATE TABLE metabib.metarecord (
24 id BIGSERIAL PRIMARY KEY,
25 fingerprint TEXT NOT NULL,
29 CREATE INDEX metabib_metarecord_master_record_idx ON metabib.metarecord (master_record);
30 CREATE INDEX metabib_metarecord_fingerprint_idx ON metabib.metarecord (fingerprint);
32 CREATE TABLE metabib.identifier_field_entry (
33 id BIGSERIAL PRIMARY KEY,
34 source BIGINT NOT NULL,
37 index_vector tsvector NOT NULL
39 CREATE TRIGGER metabib_identifier_field_entry_fti_trigger
40 BEFORE UPDATE OR INSERT ON metabib.identifier_field_entry
41 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('identifier');
43 CREATE INDEX metabib_identifier_field_entry_index_vector_idx ON metabib.identifier_field_entry USING GIN (index_vector);
44 CREATE INDEX metabib_identifier_field_entry_value_idx ON metabib.identifier_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR;
45 CREATE INDEX metabib_identifier_field_entry_source_idx ON metabib.identifier_field_entry (source);
47 CREATE TABLE metabib.combined_identifier_field_entry (
48 record BIGINT NOT NULL,
49 metabib_field INT NULL,
50 index_vector tsvector NOT NULL
52 CREATE UNIQUE INDEX metabib_combined_identifier_field_entry_fakepk_idx ON metabib.combined_identifier_field_entry (record, COALESCE(metabib_field::TEXT,''));
53 CREATE INDEX metabib_combined_identifier_field_entry_index_vector_idx ON metabib.combined_identifier_field_entry USING GIN (index_vector);
54 CREATE INDEX metabib_combined_identifier_field_source_idx ON metabib.combined_identifier_field_entry (metabib_field);
56 CREATE TABLE metabib.title_field_entry (
57 id BIGSERIAL PRIMARY KEY,
58 source BIGINT NOT NULL,
61 index_vector tsvector NOT NULL
63 CREATE TRIGGER metabib_title_field_entry_fti_trigger
64 BEFORE UPDATE OR INSERT ON metabib.title_field_entry
65 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('title');
67 CREATE INDEX metabib_title_field_entry_index_vector_idx ON metabib.title_field_entry USING GIN (index_vector);
68 CREATE INDEX metabib_title_field_entry_value_idx ON metabib.title_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR;
69 CREATE INDEX metabib_title_field_entry_source_idx ON metabib.title_field_entry (source);
71 CREATE TABLE metabib.combined_title_field_entry (
72 record BIGINT NOT NULL,
73 metabib_field INT NULL,
74 index_vector tsvector NOT NULL
76 CREATE UNIQUE INDEX metabib_combined_title_field_entry_fakepk_idx ON metabib.combined_title_field_entry (record, COALESCE(metabib_field::TEXT,''));
77 CREATE INDEX metabib_combined_title_field_entry_index_vector_idx ON metabib.combined_title_field_entry USING GIN (index_vector);
78 CREATE INDEX metabib_combined_title_field_source_idx ON metabib.combined_title_field_entry (metabib_field);
80 CREATE TABLE metabib.author_field_entry (
81 id BIGSERIAL PRIMARY KEY,
82 source BIGINT NOT NULL,
85 index_vector tsvector NOT NULL
87 CREATE TRIGGER metabib_author_field_entry_fti_trigger
88 BEFORE UPDATE OR INSERT ON metabib.author_field_entry
89 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('author');
91 CREATE INDEX metabib_author_field_entry_index_vector_idx ON metabib.author_field_entry USING GIN (index_vector);
92 CREATE INDEX metabib_author_field_entry_value_idx ON metabib.author_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR;
93 CREATE INDEX metabib_author_field_entry_source_idx ON metabib.author_field_entry (source);
95 CREATE TABLE metabib.combined_author_field_entry (
96 record BIGINT NOT NULL,
97 metabib_field INT NULL,
98 index_vector tsvector NOT NULL
100 CREATE UNIQUE INDEX metabib_combined_author_field_entry_fakepk_idx ON metabib.combined_author_field_entry (record, COALESCE(metabib_field::TEXT,''));
101 CREATE INDEX metabib_combined_author_field_entry_index_vector_idx ON metabib.combined_author_field_entry USING GIN (index_vector);
102 CREATE INDEX metabib_combined_author_field_source_idx ON metabib.combined_author_field_entry (metabib_field);
104 CREATE TABLE metabib.subject_field_entry (
105 id BIGSERIAL PRIMARY KEY,
106 source BIGINT NOT NULL,
109 index_vector tsvector NOT NULL
111 CREATE TRIGGER metabib_subject_field_entry_fti_trigger
112 BEFORE UPDATE OR INSERT ON metabib.subject_field_entry
113 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('subject');
115 CREATE INDEX metabib_subject_field_entry_index_vector_idx ON metabib.subject_field_entry USING GIN (index_vector);
116 CREATE INDEX metabib_subject_field_entry_value_idx ON metabib.subject_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR;
117 CREATE INDEX metabib_subject_field_entry_source_idx ON metabib.subject_field_entry (source);
119 CREATE TABLE metabib.combined_subject_field_entry (
120 record BIGINT NOT NULL,
121 metabib_field INT NULL,
122 index_vector tsvector NOT NULL
124 CREATE UNIQUE INDEX metabib_combined_subject_field_entry_fakepk_idx ON metabib.combined_subject_field_entry (record, COALESCE(metabib_field::TEXT,''));
125 CREATE INDEX metabib_combined_subject_field_entry_index_vector_idx ON metabib.combined_subject_field_entry USING GIN (index_vector);
126 CREATE INDEX metabib_combined_subject_field_source_idx ON metabib.combined_subject_field_entry (metabib_field);
128 CREATE TABLE metabib.keyword_field_entry (
129 id BIGSERIAL PRIMARY KEY,
130 source BIGINT NOT NULL,
133 index_vector tsvector NOT NULL
135 CREATE TRIGGER metabib_keyword_field_entry_fti_trigger
136 BEFORE UPDATE OR INSERT ON metabib.keyword_field_entry
137 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
139 CREATE INDEX metabib_keyword_field_entry_index_vector_idx ON metabib.keyword_field_entry USING GIN (index_vector);
140 CREATE INDEX metabib_keyword_field_entry_value_idx ON metabib.keyword_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR;
141 CREATE INDEX metabib_keyword_field_entry_source_idx ON metabib.keyword_field_entry (source);
143 CREATE TABLE metabib.combined_keyword_field_entry (
144 record BIGINT NOT NULL,
145 metabib_field INT NULL,
146 index_vector tsvector NOT NULL
148 CREATE UNIQUE INDEX metabib_combined_keyword_field_entry_fakepk_idx ON metabib.combined_keyword_field_entry (record, COALESCE(metabib_field::TEXT,''));
149 CREATE INDEX metabib_combined_keyword_field_entry_index_vector_idx ON metabib.combined_keyword_field_entry USING GIN (index_vector);
150 CREATE INDEX metabib_combined_keyword_field_source_idx ON metabib.combined_keyword_field_entry (metabib_field);
152 CREATE TABLE metabib.series_field_entry (
153 id BIGSERIAL PRIMARY KEY,
154 source BIGINT NOT NULL,
157 index_vector tsvector NOT NULL
159 CREATE TRIGGER metabib_series_field_entry_fti_trigger
160 BEFORE UPDATE OR INSERT ON metabib.series_field_entry
161 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('series');
163 CREATE INDEX metabib_series_field_entry_index_vector_idx ON metabib.series_field_entry USING GIN (index_vector);
164 CREATE INDEX metabib_series_field_entry_value_idx ON metabib.series_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR;
165 CREATE INDEX metabib_series_field_entry_source_idx ON metabib.series_field_entry (source);
167 CREATE TABLE metabib.combined_series_field_entry (
168 record BIGINT NOT NULL,
169 metabib_field INT NULL,
170 index_vector tsvector NOT NULL
172 CREATE UNIQUE INDEX metabib_combined_series_field_entry_fakepk_idx ON metabib.combined_series_field_entry (record, COALESCE(metabib_field::TEXT,''));
173 CREATE INDEX metabib_combined_series_field_entry_index_vector_idx ON metabib.combined_series_field_entry USING GIN (index_vector);
174 CREATE INDEX metabib_combined_series_field_source_idx ON metabib.combined_series_field_entry (metabib_field);
176 CREATE VIEW metabib.combined_all_field_entry AS
177 SELECT * FROM metabib.combined_title_field_entry
179 SELECT * FROM metabib.combined_author_field_entry
181 SELECT * FROM metabib.combined_subject_field_entry
183 SELECT * FROM metabib.combined_keyword_field_entry
185 SELECT * FROM metabib.combined_identifier_field_entry
187 SELECT * FROM metabib.combined_series_field_entry;
189 CREATE TABLE metabib.facet_entry (
190 id BIGSERIAL PRIMARY KEY,
191 source BIGINT NOT NULL,
195 CREATE INDEX metabib_facet_entry_field_idx ON metabib.facet_entry (field);
196 CREATE INDEX metabib_facet_entry_value_idx ON metabib.facet_entry (SUBSTRING(value,1,1024));
197 CREATE INDEX metabib_facet_entry_source_idx ON metabib.facet_entry (source);
199 CREATE TABLE metabib.display_entry (
200 id BIGSERIAL PRIMARY KEY,
201 source BIGINT NOT NULL,
206 CREATE INDEX metabib_display_entry_field_idx
207 ON metabib.display_entry (field);
208 CREATE INDEX metabib_display_entry_source_idx
209 ON metabib.display_entry (source);
211 CREATE VIEW metabib.flat_display_entry AS
212 /* One row per display entry fleshed with field info */
220 FROM metabib.display_entry mde
221 JOIN config.metabib_field cmf ON (cmf.id = mde.field)
222 JOIN config.display_field_map cdfm ON (cdfm.field = mde.field)
225 CREATE VIEW metabib.compressed_display_entry AS
226 /* Like flat_display_entry except values are compressed into
227 one row per display_field_map and JSON-ified. */
235 TO_JSON(ARRAY_AGG(value))
239 FROM metabib.flat_display_entry
240 GROUP BY 1, 2, 3, 4, 5
243 CREATE VIEW metabib.wide_display_entry AS
244 /* Table-like view of well-known display fields.
245 This VIEW expands as well-known display fields are added. */
248 COALESCE(mcde_title.value, 'null')::TEXT AS title,
249 COALESCE(mcde_author.value, 'null')::TEXT AS author,
250 COALESCE(mcde_subject_geographic.value, 'null')::TEXT AS subject_geographic,
251 COALESCE(mcde_subject_name.value, 'null')::TEXT AS subject_name,
252 COALESCE(mcde_subject_temporal.value, 'null')::TEXT AS subject_temporal,
253 COALESCE(mcde_subject_topic.value, 'null')::TEXT AS subject_topic,
254 COALESCE(mcde_creators.value, 'null')::TEXT AS creators,
255 COALESCE(mcde_isbn.value, 'null')::TEXT AS isbn,
256 COALESCE(mcde_issn.value, 'null')::TEXT AS issn,
257 COALESCE(mcde_upc.value, 'null')::TEXT AS upc,
258 COALESCE(mcde_tcn.value, 'null')::TEXT AS tcn,
259 COALESCE(mcde_edition.value, 'null')::TEXT AS edition,
260 COALESCE(mcde_physical_description.value, 'null')::TEXT AS physical_description,
261 COALESCE(mcde_publisher.value, 'null')::TEXT AS publisher,
262 COALESCE(mcde_series_title.value, 'null')::TEXT AS series_title,
263 COALESCE(mcde_abstract.value, 'null')::TEXT AS abstract,
264 COALESCE(mcde_toc.value, 'null')::TEXT AS toc,
265 COALESCE(mcde_pubdate.value, 'null')::TEXT AS pubdate,
266 COALESCE(mcde_type_of_resource.value, 'null')::TEXT AS type_of_resource
267 FROM biblio.record_entry bre
268 LEFT JOIN metabib.compressed_display_entry mcde_title
269 ON (bre.id = mcde_title.source AND mcde_title.name = 'title')
270 LEFT JOIN metabib.compressed_display_entry mcde_author
271 ON (bre.id = mcde_author.source AND mcde_author.name = 'author')
272 LEFT JOIN metabib.compressed_display_entry mcde_subject
273 ON (bre.id = mcde_subject.source AND mcde_subject.name = 'subject')
274 LEFT JOIN metabib.compressed_display_entry mcde_subject_geographic
275 ON (bre.id = mcde_subject_geographic.source
276 AND mcde_subject_geographic.name = 'subject_geographic')
277 LEFT JOIN metabib.compressed_display_entry mcde_subject_name
278 ON (bre.id = mcde_subject_name.source
279 AND mcde_subject_name.name = 'subject_name')
280 LEFT JOIN metabib.compressed_display_entry mcde_subject_temporal
281 ON (bre.id = mcde_subject_temporal.source
282 AND mcde_subject_temporal.name = 'subject_temporal')
283 LEFT JOIN metabib.compressed_display_entry mcde_subject_topic
284 ON (bre.id = mcde_subject_topic.source
285 AND mcde_subject_topic.name = 'subject_topic')
286 LEFT JOIN metabib.compressed_display_entry mcde_creators
287 ON (bre.id = mcde_creators.source AND mcde_creators.name = 'creators')
288 LEFT JOIN metabib.compressed_display_entry mcde_isbn
289 ON (bre.id = mcde_isbn.source AND mcde_isbn.name = 'isbn')
290 LEFT JOIN metabib.compressed_display_entry mcde_issn
291 ON (bre.id = mcde_issn.source AND mcde_issn.name = 'issn')
292 LEFT JOIN metabib.compressed_display_entry mcde_upc
293 ON (bre.id = mcde_upc.source AND mcde_upc.name = 'upc')
294 LEFT JOIN metabib.compressed_display_entry mcde_tcn
295 ON (bre.id = mcde_tcn.source AND mcde_tcn.name = 'tcn')
296 LEFT JOIN metabib.compressed_display_entry mcde_edition
297 ON (bre.id = mcde_edition.source AND mcde_edition.name = 'edition')
298 LEFT JOIN metabib.compressed_display_entry mcde_physical_description
299 ON (bre.id = mcde_physical_description.source
300 AND mcde_physical_description.name = 'physical_description')
301 LEFT JOIN metabib.compressed_display_entry mcde_publisher
302 ON (bre.id = mcde_publisher.source AND mcde_publisher.name = 'publisher')
303 LEFT JOIN metabib.compressed_display_entry mcde_series_title
304 ON (bre.id = mcde_series_title.source AND mcde_series_title.name = 'series_title')
305 LEFT JOIN metabib.compressed_display_entry mcde_abstract
306 ON (bre.id = mcde_abstract.source AND mcde_abstract.name = 'abstract')
307 LEFT JOIN metabib.compressed_display_entry mcde_toc
308 ON (bre.id = mcde_toc.source AND mcde_toc.name = 'toc')
309 LEFT JOIN metabib.compressed_display_entry mcde_pubdate
310 ON (bre.id = mcde_pubdate.source AND mcde_pubdate.name = 'pubdate')
311 LEFT JOIN metabib.compressed_display_entry mcde_type_of_resource
312 ON (bre.id = mcde_type_of_resource.source
313 AND mcde_type_of_resource.name = 'type_of_resource')
316 CREATE TABLE metabib.browse_entry (
317 id BIGSERIAL PRIMARY KEY,
319 index_vector tsvector,
320 sort_value TEXT NOT NULL,
321 UNIQUE(sort_value, value)
325 CREATE INDEX browse_entry_sort_value_idx
326 ON metabib.browse_entry USING BTREE (sort_value);
328 CREATE INDEX metabib_browse_entry_index_vector_idx ON metabib.browse_entry USING GIN (index_vector);
329 CREATE TRIGGER metabib_browse_entry_fti_trigger
330 BEFORE INSERT OR UPDATE ON metabib.browse_entry
331 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
334 CREATE TABLE metabib.browse_entry_def_map (
335 id BIGSERIAL PRIMARY KEY,
336 entry BIGINT REFERENCES metabib.browse_entry (id),
337 def INT REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
338 source BIGINT REFERENCES biblio.record_entry (id),
339 authority BIGINT REFERENCES authority.record_entry (id) ON DELETE SET NULL
341 CREATE INDEX browse_entry_def_map_def_idx ON metabib.browse_entry_def_map (def);
342 CREATE INDEX browse_entry_def_map_entry_idx ON metabib.browse_entry_def_map (entry);
343 CREATE INDEX browse_entry_def_map_source_idx ON metabib.browse_entry_def_map (source);
345 CREATE TABLE metabib.browse_entry_simple_heading_map (
346 id BIGSERIAL PRIMARY KEY,
347 entry BIGINT REFERENCES metabib.browse_entry (id),
348 simple_heading BIGINT REFERENCES authority.simple_heading (id) ON DELETE CASCADE
350 CREATE INDEX browse_entry_sh_map_entry_idx ON metabib.browse_entry_simple_heading_map (entry);
351 CREATE INDEX browse_entry_sh_map_sh_idx ON metabib.browse_entry_simple_heading_map (simple_heading);
353 CREATE OR REPLACE FUNCTION metabib.display_field_normalize_trigger ()
354 RETURNS TRIGGER AS $$
357 display_field_text TEXT;
359 display_field_text := NEW.value;
362 SELECT n.func AS func,
363 n.param_count AS param_count,
365 FROM config.index_normalizer n
366 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
367 WHERE m.field = NEW.field AND m.pos < 0
370 EXECUTE 'SELECT ' || normalizer.func || '(' ||
371 quote_literal( display_field_text ) ||
373 WHEN normalizer.param_count > 0
374 THEN ',' || REPLACE(REPLACE(BTRIM(
375 normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
378 ')' INTO display_field_text;
382 NEW.value = display_field_text;
388 CREATE TRIGGER display_field_normalize_tgr
389 BEFORE UPDATE OR INSERT ON metabib.display_entry
390 FOR EACH ROW EXECUTE PROCEDURE metabib.display_field_normalize_trigger();
392 CREATE OR REPLACE FUNCTION evergreen.display_field_force_nfc()
393 RETURNS TRIGGER AS $$
395 NEW.value := force_unicode_normal_form(NEW.value,'NFC');
400 CREATE TRIGGER display_field_force_nfc_tgr
401 BEFORE UPDATE OR INSERT ON metabib.display_entry
402 FOR EACH ROW EXECUTE PROCEDURE evergreen.display_field_force_nfc();
405 CREATE OR REPLACE FUNCTION metabib.facet_normalize_trigger () RETURNS TRIGGER AS $$
410 facet_text := NEW.value;
413 SELECT n.func AS func,
414 n.param_count AS param_count,
416 FROM config.index_normalizer n
417 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
418 WHERE m.field = NEW.field AND m.pos < 0
421 EXECUTE 'SELECT ' || normalizer.func || '(' ||
422 quote_literal( facet_text ) ||
424 WHEN normalizer.param_count > 0
425 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
432 NEW.value = facet_text;
438 CREATE TRIGGER facet_normalize_tgr
439 BEFORE UPDATE OR INSERT ON metabib.facet_entry
440 FOR EACH ROW EXECUTE PROCEDURE metabib.facet_normalize_trigger();
442 CREATE OR REPLACE FUNCTION evergreen.facet_force_nfc() RETURNS TRIGGER AS $$
444 NEW.value := force_unicode_normal_form(NEW.value,'NFC');
449 CREATE TRIGGER facet_force_nfc_tgr
450 BEFORE UPDATE OR INSERT ON metabib.facet_entry
451 FOR EACH ROW EXECUTE PROCEDURE evergreen.facet_force_nfc();
453 -- DECREMENTING serial starts at -1
454 CREATE SEQUENCE metabib.uncontrolled_record_attr_value_id_seq INCREMENT BY -1;
456 CREATE TABLE metabib.uncontrolled_record_attr_value (
457 id BIGINT PRIMARY KEY DEFAULT nextval('metabib.uncontrolled_record_attr_value_id_seq'),
458 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name),
461 CREATE UNIQUE INDEX muv_once_idx ON metabib.uncontrolled_record_attr_value (attr,value);
463 CREATE VIEW metabib.record_attr_id_map AS
464 SELECT id, attr, value FROM metabib.uncontrolled_record_attr_value
466 SELECT c.id, c.ctype AS attr, c.code AS value
467 FROM config.coded_value_map c
468 JOIN config.record_attr_definition d ON (d.name = c.ctype AND NOT d.composite);
470 CREATE VIEW metabib.composite_attr_id_map AS
471 SELECT c.id, c.ctype AS attr, c.code AS value
472 FROM config.coded_value_map c
473 JOIN config.record_attr_definition d ON (d.name = c.ctype AND d.composite);
475 CREATE VIEW metabib.full_attr_id_map AS
476 SELECT id, attr, value FROM metabib.record_attr_id_map
478 SELECT id, attr, value FROM metabib.composite_attr_id_map;
481 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr_cache_init () RETURNS BOOL AS $f$
482 $_SHARED{metabib_compile_composite_attr_cache} = {}
483 if ! exists $_SHARED{metabib_compile_composite_attr_cache};
484 return exists $_SHARED{metabib_compile_composite_attr_cache};
485 $f$ LANGUAGE PLPERLU;
487 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr_cache_disable () RETURNS BOOL AS $f$
488 delete $_SHARED{metabib_compile_composite_attr_cache};
489 return ! exists $_SHARED{metabib_compile_composite_attr_cache};
490 $f$ LANGUAGE PLPERLU;
492 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr_cache_invalidate () RETURNS BOOL AS $f$
493 SELECT metabib.compile_composite_attr_cache_disable() AND metabib.compile_composite_attr_cache_init();
496 CREATE OR REPLACE FUNCTION metabib.composite_attr_def_cache_inval_tgr () RETURNS TRIGGER AS $f$
498 PERFORM metabib.compile_composite_attr_cache_invalidate();
501 $f$ LANGUAGE PLPGSQL;
503 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();
505 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr ( cattr_def TEXT ) RETURNS query_int AS $func$
510 my $def = decode_json($json);
512 die("Composite attribute definition not supplied") unless $def;
514 my $_cache = (exists $_SHARED{metabib_compile_composite_attr_cache}) ? 1 : 0;
516 return $_SHARED{metabib_compile_composite_attr_cache}{$json}
517 if ($_cache && $_SHARED{metabib_compile_composite_attr_cache}{$json});
524 if (ref $d eq 'HASH') { # node or AND
525 if (exists $d->{_attr}) { # it is a node
526 my $plan = spi_prepare('SELECT * FROM metabib.full_attr_id_map WHERE attr = $1 AND value = $2', qw/TEXT TEXT/);
527 my $id = spi_exec_prepared(
528 $plan, {limit => 1}, $d->{_attr}, $d->{_val}
532 } elsif (exists $d->{_not} && scalar(keys(%$d)) == 1) { # it is a NOT
533 return '!' . recurse($$d{_not});
534 } else { # an AND list
535 @list = map { recurse($$d{$_}) } sort keys %$d;
537 } elsif (ref $d eq 'ARRAY') {
539 @list = map { recurse($_) } @$d;
542 @list = grep { defined && $_ ne '' } @list;
544 return '(' . join($j,@list) . ')' if @list;
548 my $val = recurse($def) || undef;
549 $_SHARED{metabib_compile_composite_attr_cache}{$json} = $val if $_cache;
552 $func$ IMMUTABLE LANGUAGE plperlu;
554 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr ( cattr_id INT ) RETURNS query_int AS $func$
555 SELECT metabib.compile_composite_attr(definition) FROM config.composite_attr_entry_definition WHERE coded_value = $1;
556 $func$ STRICT IMMUTABLE LANGUAGE SQL;
558 CREATE TABLE metabib.record_attr_vector_list (
559 source BIGINT PRIMARY KEY REFERENCES biblio.record_entry (id),
560 vlist INT[] NOT NULL -- stores id from ccvm AND murav
562 CREATE INDEX mrca_vlist_idx ON metabib.record_attr_vector_list USING gin ( vlist gin__int_ops );
564 /* This becomes a view, and we do sorters differently ...
565 CREATE TABLE metabib.record_attr (
566 id BIGINT PRIMARY KEY REFERENCES biblio.record_entry (id) ON DELETE CASCADE,
567 attrs HSTORE NOT NULL DEFAULT ''::HSTORE
569 CREATE INDEX metabib_svf_attrs_idx ON metabib.record_attr USING GIN (attrs);
570 CREATE INDEX metabib_svf_date1_idx ON metabib.record_attr ((attrs->'date1'));
571 CREATE INDEX metabib_svf_dates_idx ON metabib.record_attr ((attrs->'date1'),(attrs->'date2'));
575 CREATE TABLE metabib.record_sorter (
576 id BIGSERIAL PRIMARY KEY,
577 source BIGINT NOT NULL REFERENCES biblio.record_entry (id) ON DELETE CASCADE,
578 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE,
581 CREATE INDEX metabib_sorter_source_idx ON metabib.record_sorter (source); -- we may not need one of this or the next ... stats will tell
582 CREATE INDEX metabib_sorter_s_a_idx ON metabib.record_sorter (source, attr);
583 CREATE INDEX metabib_sorter_a_v_idx ON metabib.record_sorter (attr, value);
586 CREATE TYPE metabib.record_attr_type AS (
591 -- Back-compat view ... we're moving to an INTARRAY world
592 CREATE VIEW metabib.record_attr_flat AS
593 SELECT v.source AS id,
596 FROM metabib.record_attr_vector_list v
597 LEFT JOIN metabib.uncontrolled_record_attr_value m ON ( m.id = ANY( v.vlist ) )
599 SELECT v.source AS id,
602 FROM metabib.record_attr_vector_list v
603 LEFT JOIN config.coded_value_map c ON ( c.id = ANY( v.vlist ) );
605 CREATE VIEW metabib.record_attr AS
606 SELECT id, HSTORE( ARRAY_AGG( attr ), ARRAY_AGG( value ) ) AS attrs
607 FROM metabib.record_attr_flat
608 WHERE attr IS NOT NULL
611 -- Back-back-compat view ... we use to live in an HSTORE world
612 CREATE TYPE metabib.rec_desc_type AS (
630 CREATE VIEW metabib.rec_descriptor AS
633 (populate_record(NULL::metabib.rec_desc_type, attrs)).*
634 FROM metabib.record_attr;
636 -- Use a sequence that matches previous version, for easier upgrading.
637 CREATE SEQUENCE metabib.full_rec_id_seq;
639 CREATE TABLE metabib.real_full_rec (
640 id BIGINT NOT NULL DEFAULT NEXTVAL('metabib.full_rec_id_seq'::REGCLASS),
641 record BIGINT NOT NULL,
642 tag CHAR(3) NOT NULL,
647 index_vector tsvector NOT NULL
649 ALTER TABLE metabib.real_full_rec ADD PRIMARY KEY (id);
651 CREATE INDEX metabib_full_rec_tag_subfield_idx ON metabib.real_full_rec (tag,subfield);
652 CREATE INDEX metabib_full_rec_value_idx ON metabib.real_full_rec (substring(value,1,1024));
653 /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */
654 CREATE INDEX metabib_full_rec_value_tpo_index ON metabib.real_full_rec (substring(value,1,1024) text_pattern_ops);
655 CREATE INDEX metabib_full_rec_record_idx ON metabib.real_full_rec (record);
656 CREATE INDEX metabib_full_rec_index_vector_idx ON metabib.real_full_rec USING GIN (index_vector);
657 CREATE INDEX metabib_full_rec_isxn_caseless_idx
658 ON metabib.real_full_rec (LOWER(value))
659 WHERE tag IN ('020', '022', '024');
660 -- This next index might fully supplant the one above, but leaving both for now.
661 -- (they are not too large)
662 -- The reason we need this index is to ensure that the query parser always
663 -- prefers this index over the simpler tag/subfield index, as this greatly
664 -- increases Vandelay overlay speed for these identifiers, especially when
665 -- a record has many of these fields (around > 4-6 seems like the cutoff
666 -- on at least one PG9.1 system)
667 -- A similar index could be added for other fields (e.g. 010), but one should
668 -- leave out the LOWER() in all other cases.
669 -- TODO: verify whether we can discard the non tag/subfield/substring version
670 -- above (metabib_full_rec_isxn_caseless_idx)
671 CREATE INDEX metabib_full_rec_02x_tag_subfield_lower_substring
672 ON metabib.real_full_rec (tag, subfield, LOWER(substring(value, 1, 1024)))
673 WHERE tag IN ('020', '022', '024');
676 CREATE TRIGGER metabib_full_rec_fti_trigger
677 BEFORE UPDATE OR INSERT ON metabib.real_full_rec
678 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('default');
680 CREATE OR REPLACE VIEW metabib.full_rec AS
687 SUBSTRING(value,1,1024) AS value,
689 FROM metabib.real_full_rec;
691 CREATE OR REPLACE RULE metabib_full_rec_insert_rule
692 AS ON INSERT TO metabib.full_rec
694 INSERT INTO metabib.real_full_rec VALUES (
695 COALESCE(NEW.id, NEXTVAL('metabib.full_rec_id_seq'::REGCLASS)),
705 CREATE OR REPLACE RULE metabib_full_rec_update_rule
706 AS ON UPDATE TO metabib.full_rec
708 UPDATE metabib.real_full_rec SET
714 subfield = NEW.subfield,
716 index_vector = NEW.index_vector
719 CREATE OR REPLACE RULE metabib_full_rec_delete_rule
720 AS ON DELETE TO metabib.full_rec
722 DELETE FROM metabib.real_full_rec WHERE id = OLD.id;
724 CREATE TABLE metabib.metarecord_source_map (
725 id BIGSERIAL PRIMARY KEY,
726 metarecord BIGINT NOT NULL,
727 source BIGINT NOT NULL
729 CREATE INDEX metabib_metarecord_source_map_metarecord_idx ON metabib.metarecord_source_map (metarecord);
730 CREATE INDEX metabib_metarecord_source_map_source_record_idx ON metabib.metarecord_source_map (source);
732 CREATE TYPE metabib.field_entry_template AS (
746 CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry (
751 ) RETURNS SETOF metabib.field_entry_template AS $func$
753 bib biblio.record_entry%ROWTYPE;
754 idx config.metabib_field%ROWTYPE;
755 xfrm config.xml_transform%ROWTYPE;
757 transformed_xml TEXT;
759 xml_node_list TEXT[];
766 joiner TEXT := default_joiner; -- XXX will index defs supply a joiner?
768 authority_link BIGINT;
769 output_row metabib.field_entry_template%ROWTYPE;
773 -- Start out with no field-use bools set
774 output_row.browse_nocase = FALSE;
775 output_row.browse_field = FALSE;
776 output_row.facet_field = FALSE;
777 output_row.display_field = FALSE;
778 output_row.search_field = FALSE;
781 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
783 -- Loop over the indexing entries
784 FOR idx IN SELECT * FROM config.metabib_field WHERE id = ANY (only_fields) ORDER BY format LOOP
785 CONTINUE WHEN idx.xpath IS NULL OR idx.xpath = ''; -- pure virtual field
787 process_idx := FALSE;
788 IF idx.display_field AND 'display' = ANY (field_types) THEN process_idx = TRUE; END IF;
789 IF idx.browse_field AND 'browse' = ANY (field_types) THEN process_idx = TRUE; END IF;
790 IF idx.search_field AND 'search' = ANY (field_types) THEN process_idx = TRUE; END IF;
791 IF idx.facet_field AND 'facet' = ANY (field_types) THEN process_idx = TRUE; END IF;
792 CONTINUE WHEN process_idx = FALSE; -- disabled for all types
794 joiner := COALESCE(idx.joiner, default_joiner);
796 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
798 -- See if we can skip the XSLT ... it's expensive
799 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
800 -- Can't skip the transform
801 IF xfrm.xslt <> '---' THEN
802 transformed_xml := oils_xslt_process(bib.marc,xfrm.xslt);
804 transformed_xml := bib.marc;
807 prev_xfrm := xfrm.name;
810 xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
813 FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP
814 CONTINUE WHEN xml_node !~ E'^\\s*<';
816 -- XXX much of this should be moved into oils_xpath_string...
817 curr_text := ARRAY_TO_STRING(array_remove(array_remove(
818 oils_xpath( '//text()', -- get the content of all the nodes within the main selected node
819 REGEXP_REPLACE( xml_node, E'\\s+', ' ', 'g' ) -- Translate adjacent whitespace to a single space
820 ), ' '), ''), -- throw away morally empty (bankrupt?) strings
824 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
826 IF raw_text IS NOT NULL THEN
827 raw_text := raw_text || joiner;
830 raw_text := COALESCE(raw_text,'') || curr_text;
832 -- autosuggest/metabib.browse_entry
833 IF idx.browse_field THEN
834 output_row.browse_nocase = idx.browse_nocase;
836 IF idx.browse_xpath IS NOT NULL AND idx.browse_xpath <> '' THEN
837 browse_text := oils_xpath_string( idx.browse_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
839 browse_text := curr_text;
842 IF idx.browse_sort_xpath IS NOT NULL AND
843 idx.browse_sort_xpath <> '' THEN
845 sort_value := oils_xpath_string(
846 idx.browse_sort_xpath, xml_node, joiner,
847 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
850 sort_value := browse_text;
853 output_row.field_class = idx.field_class;
854 output_row.field = idx.id;
855 output_row.source = rid;
856 output_row.value = BTRIM(REGEXP_REPLACE(browse_text, E'\\s+', ' ', 'g'));
857 output_row.sort_value :=
858 public.naco_normalize(sort_value);
860 output_row.authority := NULL;
862 IF idx.authority_xpath IS NOT NULL AND idx.authority_xpath <> '' THEN
863 authority_text := oils_xpath_string(
864 idx.authority_xpath, xml_node, joiner,
866 ARRAY[xfrm.prefix, xfrm.namespace_uri],
867 ARRAY['xlink','http://www.w3.org/1999/xlink']
871 IF authority_text ~ '^\d+$' THEN
872 authority_link := authority_text::BIGINT;
873 PERFORM * FROM authority.record_entry WHERE id = authority_link;
875 output_row.authority := authority_link;
881 output_row.browse_field = TRUE;
882 -- Returning browse rows with search_field = true for search+browse
883 -- configs allows us to retain granularity of being able to search
884 -- browse fields with "starts with" type operators (for example, for
885 -- titles of songs in music albums)
886 IF idx.search_field THEN
887 output_row.search_field = TRUE;
889 RETURN NEXT output_row;
890 output_row.browse_nocase = FALSE;
891 output_row.browse_field = FALSE;
892 output_row.search_field = FALSE;
893 output_row.sort_value := NULL;
896 -- insert raw node text for faceting
897 IF idx.facet_field THEN
899 IF idx.facet_xpath IS NOT NULL AND idx.facet_xpath <> '' THEN
900 facet_text := oils_xpath_string( idx.facet_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
902 facet_text := curr_text;
905 output_row.field_class = idx.field_class;
906 output_row.field = -1 * idx.id;
907 output_row.source = rid;
908 output_row.value = BTRIM(REGEXP_REPLACE(facet_text, E'\\s+', ' ', 'g'));
910 output_row.facet_field = TRUE;
911 RETURN NEXT output_row;
912 output_row.facet_field = FALSE;
915 -- insert raw node text for display
916 IF idx.display_field THEN
918 IF idx.display_xpath IS NOT NULL AND idx.display_xpath <> '' THEN
919 display_text := oils_xpath_string( idx.display_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
921 display_text := curr_text;
924 output_row.field_class = idx.field_class;
925 output_row.field = -1 * idx.id;
926 output_row.source = rid;
927 output_row.value = BTRIM(REGEXP_REPLACE(display_text, E'\\s+', ' ', 'g'));
929 output_row.display_field = TRUE;
930 RETURN NEXT output_row;
931 output_row.display_field = FALSE;
936 CONTINUE WHEN raw_text IS NULL OR raw_text = '';
938 -- insert combined node text for searching
939 IF idx.search_field THEN
940 output_row.field_class = idx.field_class;
941 output_row.field = idx.id;
942 output_row.source = rid;
943 output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'g'));
945 output_row.search_field = TRUE;
946 RETURN NEXT output_row;
947 output_row.search_field = FALSE;
953 $func$ LANGUAGE PLPGSQL;
955 CREATE OR REPLACE FUNCTION metabib.update_combined_index_vectors(bib_id BIGINT) RETURNS VOID AS $func$
962 DELETE FROM metabib.combined_keyword_field_entry WHERE record = bib_id;
963 INSERT INTO metabib.combined_keyword_field_entry(record, metabib_field, index_vector)
964 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
965 FROM metabib.keyword_field_entry WHERE source = bib_id GROUP BY field;
966 INSERT INTO metabib.combined_keyword_field_entry(record, metabib_field, index_vector)
967 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
968 FROM metabib.keyword_field_entry WHERE source = bib_id;
970 DELETE FROM metabib.combined_title_field_entry WHERE record = bib_id;
971 INSERT INTO metabib.combined_title_field_entry(record, metabib_field, index_vector)
972 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
973 FROM metabib.title_field_entry WHERE source = bib_id GROUP BY field;
974 INSERT INTO metabib.combined_title_field_entry(record, metabib_field, index_vector)
975 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
976 FROM metabib.title_field_entry WHERE source = bib_id;
978 DELETE FROM metabib.combined_author_field_entry WHERE record = bib_id;
979 INSERT INTO metabib.combined_author_field_entry(record, metabib_field, index_vector)
980 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
981 FROM metabib.author_field_entry WHERE source = bib_id GROUP BY field;
982 INSERT INTO metabib.combined_author_field_entry(record, metabib_field, index_vector)
983 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
984 FROM metabib.author_field_entry WHERE source = bib_id;
986 DELETE FROM metabib.combined_subject_field_entry WHERE record = bib_id;
987 INSERT INTO metabib.combined_subject_field_entry(record, metabib_field, index_vector)
988 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
989 FROM metabib.subject_field_entry WHERE source = bib_id GROUP BY field;
990 INSERT INTO metabib.combined_subject_field_entry(record, metabib_field, index_vector)
991 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
992 FROM metabib.subject_field_entry WHERE source = bib_id;
994 DELETE FROM metabib.combined_series_field_entry WHERE record = bib_id;
995 INSERT INTO metabib.combined_series_field_entry(record, metabib_field, index_vector)
996 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
997 FROM metabib.series_field_entry WHERE source = bib_id GROUP BY field;
998 INSERT INTO metabib.combined_series_field_entry(record, metabib_field, index_vector)
999 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1000 FROM metabib.series_field_entry WHERE source = bib_id;
1002 DELETE FROM metabib.combined_identifier_field_entry WHERE record = bib_id;
1003 INSERT INTO metabib.combined_identifier_field_entry(record, metabib_field, index_vector)
1004 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1005 FROM metabib.identifier_field_entry WHERE source = bib_id GROUP BY field;
1006 INSERT INTO metabib.combined_identifier_field_entry(record, metabib_field, index_vector)
1007 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1008 FROM metabib.identifier_field_entry WHERE source = bib_id;
1010 -- For each virtual def, gather the data from the combined real field
1011 -- entries and append it to the virtual combined entry.
1012 FOR vfield, rfields IN SELECT virtual, ARRAY_AGG(real) FROM config.metabib_field_virtual_map GROUP BY virtual LOOP
1013 SELECT field_class INTO vclass
1014 FROM config.metabib_field
1017 SELECT string_agg(index_vector::TEXT,' ')::tsvector INTO rdata
1018 FROM metabib.combined_all_field_entry
1019 WHERE record = bib_id
1020 AND metabib_field = ANY (rfields);
1022 BEGIN -- I cannot wait for INSERT ON CONFLICT ... 9.5, though
1024 INSERT INTO metabib.combined_$$ || vclass || $$_field_entry
1025 (record, metabib_field, index_vector) VALUES ($1, $2, $3)
1026 $$ USING bib_id, vfield, rdata;
1027 EXCEPTION WHEN unique_violation THEN
1029 UPDATE metabib.combined_$$ || vclass || $$_field_entry
1030 SET index_vector = index_vector || $3
1032 AND metabib_field = $2
1033 $$ USING bib_id, vfield, rdata;
1035 -- ignore and move on
1039 $func$ LANGUAGE PLPGSQL;
1041 CREATE OR REPLACE FUNCTION metabib.reingest_metabib_field_entries(
1043 skip_facet BOOL DEFAULT FALSE,
1044 skip_display BOOL DEFAULT FALSE,
1045 skip_browse BOOL DEFAULT FALSE,
1046 skip_search BOOL DEFAULT FALSE,
1047 only_fields INT[] DEFAULT '{}'::INT[]
1048 ) RETURNS VOID AS $func$
1051 ind_data metabib.field_entry_template%ROWTYPE;
1052 mbe_row metabib.browse_entry%ROWTYPE;
1055 b_skip_display BOOL;
1059 field_list INT[] := only_fields;
1060 field_types TEXT[] := '{}'::TEXT[];
1063 IF field_list = '{}'::INT[] THEN
1064 SELECT ARRAY_AGG(id) INTO field_list FROM config.metabib_field;
1067 SELECT COALESCE(NULLIF(skip_facet, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_facet_indexing' AND enabled)) INTO b_skip_facet;
1068 SELECT COALESCE(NULLIF(skip_display, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_display_indexing' AND enabled)) INTO b_skip_display;
1069 SELECT COALESCE(NULLIF(skip_browse, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_browse_indexing' AND enabled)) INTO b_skip_browse;
1070 SELECT COALESCE(NULLIF(skip_search, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_search_indexing' AND enabled)) INTO b_skip_search;
1072 IF NOT b_skip_facet THEN field_types := field_types || '{facet}'; END IF;
1073 IF NOT b_skip_display THEN field_types := field_types || '{display}'; END IF;
1074 IF NOT b_skip_browse THEN field_types := field_types || '{browse}'; END IF;
1075 IF NOT b_skip_search THEN field_types := field_types || '{search}'; END IF;
1077 PERFORM * FROM config.internal_flag WHERE name = 'ingest.assume_inserts_only' AND enabled;
1079 IF NOT b_skip_search THEN
1080 FOR fclass IN SELECT * FROM config.metabib_class LOOP
1081 EXECUTE $$DELETE FROM metabib.$$ || fclass.name || $$_field_entry WHERE source = $$ || bib_id || $$ AND field = ANY($1)$$ USING field_list;
1084 IF NOT b_skip_facet THEN
1085 DELETE FROM metabib.facet_entry WHERE source = bib_id AND field = ANY(field_list);
1087 IF NOT b_skip_display THEN
1088 DELETE FROM metabib.display_entry WHERE source = bib_id AND field = ANY(field_list);
1090 IF NOT b_skip_browse THEN
1091 DELETE FROM metabib.browse_entry_def_map WHERE source = bib_id AND def = ANY(field_list);
1095 FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( bib_id, ' ', field_types, field_list ) LOOP
1097 -- don't store what has been normalized away
1098 CONTINUE WHEN ind_data.value IS NULL;
1100 IF ind_data.field < 0 THEN
1101 ind_data.field = -1 * ind_data.field;
1104 IF ind_data.facet_field AND NOT b_skip_facet THEN
1105 INSERT INTO metabib.facet_entry (field, source, value)
1106 VALUES (ind_data.field, ind_data.source, ind_data.value);
1109 IF ind_data.display_field AND NOT b_skip_display THEN
1110 INSERT INTO metabib.display_entry (field, source, value)
1111 VALUES (ind_data.field, ind_data.source, ind_data.value);
1115 IF ind_data.browse_field AND NOT b_skip_browse THEN
1116 -- A caveat about this SELECT: this should take care of replacing
1117 -- old mbe rows when data changes, but not if normalization (by
1118 -- which I mean specifically the output of
1119 -- evergreen.oils_tsearch2()) changes. It may or may not be
1120 -- expensive to add a comparison of index_vector to index_vector
1121 -- to the WHERE clause below.
1123 CONTINUE WHEN ind_data.sort_value IS NULL;
1125 value_prepped := metabib.browse_normalize(ind_data.value, ind_data.field);
1126 IF ind_data.browse_nocase THEN -- for "nocase" browse definions, look for a preexisting row that matches case-insensitively on value and use that
1127 SELECT INTO mbe_row * FROM metabib.browse_entry
1128 WHERE evergreen.lowercase(value) = evergreen.lowercase(value_prepped) AND sort_value = ind_data.sort_value
1129 ORDER BY sort_value, value LIMIT 1; -- gotta pick something, I guess
1132 IF mbe_row.id IS NOT NULL THEN -- asked to check for, and found, a "nocase" version to use
1133 mbe_id := mbe_row.id;
1134 ELSE -- otherwise, an UPSERT-protected variant
1135 INSERT INTO metabib.browse_entry
1136 ( value, sort_value ) VALUES
1137 ( value_prepped, ind_data.sort_value )
1138 ON CONFLICT (sort_value, value) DO UPDATE SET sort_value = EXCLUDED.sort_value -- must update a row to return an existing id
1139 RETURNING id INTO mbe_id;
1142 INSERT INTO metabib.browse_entry_def_map (entry, def, source, authority)
1143 VALUES (mbe_id, ind_data.field, ind_data.source, ind_data.authority);
1146 IF ind_data.search_field AND NOT b_skip_search THEN
1147 -- Avoid inserting duplicate rows
1148 EXECUTE 'SELECT 1 FROM metabib.' || ind_data.field_class ||
1149 '_field_entry WHERE field = $1 AND source = $2 AND value = $3'
1150 INTO mbe_id USING ind_data.field, ind_data.source, ind_data.value;
1151 -- RAISE NOTICE 'Search for an already matching row returned %', mbe_id;
1152 IF mbe_id IS NULL THEN
1154 INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value)
1156 quote_literal(ind_data.field) || $$, $$ ||
1157 quote_literal(ind_data.source) || $$, $$ ||
1158 quote_literal(ind_data.value) ||
1165 IF NOT b_skip_search THEN
1166 PERFORM metabib.update_combined_index_vectors(bib_id);
1167 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_symspell_reification' AND enabled;
1169 PERFORM search.symspell_dictionary_reify();
1175 $func$ LANGUAGE PLPGSQL;
1177 CREATE OR REPLACE FUNCTION authority.flatten_marc ( rid BIGINT ) RETURNS SETOF authority.full_rec AS $func$
1179 auth authority.record_entry%ROWTYPE;
1180 output authority.full_rec%ROWTYPE;
1183 SELECT INTO auth * FROM authority.record_entry WHERE id = rid;
1185 FOR field IN SELECT * FROM vandelay.flatten_marc( auth.marc ) LOOP
1186 output.record := rid;
1187 output.ind1 := field.ind1;
1188 output.ind2 := field.ind2;
1189 output.tag := field.tag;
1190 output.subfield := field.subfield;
1191 output.value := field.value;
1196 $func$ LANGUAGE PLPGSQL;
1198 CREATE OR REPLACE FUNCTION biblio.flatten_marc ( rid BIGINT ) RETURNS SETOF metabib.full_rec AS $func$
1200 bib biblio.record_entry%ROWTYPE;
1201 output metabib.full_rec%ROWTYPE;
1204 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
1206 FOR field IN SELECT * FROM vandelay.flatten_marc( bib.marc ) LOOP
1207 output.record := rid;
1208 output.ind1 := field.ind1;
1209 output.ind2 := field.ind2;
1210 output.tag := field.tag;
1211 output.subfield := field.subfield;
1212 output.value := field.value;
1217 $func$ LANGUAGE PLPGSQL;
1219 CREATE OR REPLACE FUNCTION biblio.marc21_extract_fixed_field_list( rid BIGINT, ff TEXT ) RETURNS TEXT[] AS $func$
1220 SELECT * FROM vandelay.marc21_extract_fixed_field_list( (SELECT marc FROM biblio.record_entry WHERE id = $1), $2, TRUE );
1221 $func$ LANGUAGE SQL;
1223 CREATE OR REPLACE FUNCTION biblio.marc21_extract_fixed_field( rid BIGINT, ff TEXT ) RETURNS TEXT AS $func$
1224 SELECT * FROM vandelay.marc21_extract_fixed_field( (SELECT marc FROM biblio.record_entry WHERE id = $1), $2, TRUE );
1225 $func$ LANGUAGE SQL;
1227 CREATE OR REPLACE FUNCTION biblio.marc21_extract_all_fixed_fields( rid BIGINT ) RETURNS SETOF biblio.record_ff_map AS $func$
1228 SELECT $1 AS record, ff_name, ff_value FROM vandelay.marc21_extract_all_fixed_fields( (SELECT marc FROM biblio.record_entry WHERE id = $1), TRUE );
1229 $func$ LANGUAGE SQL;
1231 CREATE OR REPLACE FUNCTION biblio.marc21_physical_characteristics( rid BIGINT ) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$
1232 SELECT id, $1 AS record, ptype, subfield, value FROM vandelay.marc21_physical_characteristics( (SELECT marc FROM biblio.record_entry WHERE id = $1) );
1233 $func$ LANGUAGE SQL;
1235 CREATE OR REPLACE FUNCTION biblio.extract_quality ( marc TEXT, best_lang TEXT, best_type TEXT ) RETURNS INT AS $func$
1248 IF marc IS NULL OR marc = '' THEN
1252 -- First, the count of tags
1253 qual := ARRAY_UPPER(oils_xpath('//*[local-name()="datafield"]', marc), 1);
1255 -- now go through a bunch of pain to get the record type
1256 IF best_type IS NOT NULL THEN
1257 ldr := (oils_xpath('//*[local-name()="leader"]/text()', marc))[1];
1259 IF ldr IS NOT NULL THEN
1260 SELECT * INTO tval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'Type' LIMIT 1; -- They're all the same
1261 SELECT * INTO bval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'BLvl' LIMIT 1; -- They're all the same
1264 tval := SUBSTRING( ldr, tval_rec.start_pos + 1, tval_rec.length );
1265 bval := SUBSTRING( ldr, bval_rec.start_pos + 1, bval_rec.length );
1267 -- RAISE NOTICE 'type %, blvl %, ldr %', tval, bval, ldr;
1269 SELECT * INTO type_map FROM config.marc21_rec_type_map WHERE type_val LIKE '%' || tval || '%' AND blvl_val LIKE '%' || bval || '%';
1271 IF type_map.code IS NOT NULL THEN
1272 IF best_type = type_map.code THEN
1273 qual := qual + qual / 2;
1276 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = 'Lang' AND rec_type = type_map.code ORDER BY tag DESC LOOP
1277 ff_tag_data := SUBSTRING((oils_xpath('//*[@tag="' || ff_pos.tag || '"]/text()',marc))[1], ff_pos.start_pos + 1, ff_pos.length);
1278 IF ff_tag_data = best_lang THEN
1286 -- Now look for some quality metrics
1288 IF ARRAY_UPPER(oils_xpath('//*[@tag="040"]/*[@code="a" and contains(.,"DLC")]', marc), 1) = 1 THEN
1293 IF (oils_xpath('//*[@tag="003"]/text()', marc))[1] ~* E'oclo?c' THEN
1300 $func$ LANGUAGE PLPGSQL;
1302 CREATE OR REPLACE FUNCTION biblio.extract_fingerprint ( marc text ) RETURNS TEXT AS $func$
1304 idx config.biblio_fingerprint%ROWTYPE;
1305 xfrm config.xml_transform%ROWTYPE;
1307 transformed_xml TEXT;
1309 xml_node_list TEXT[];
1311 output_text TEXT := '';
1314 IF marc IS NULL OR marc = '' THEN
1318 -- Loop over the indexing entries
1319 FOR idx IN SELECT * FROM config.biblio_fingerprint ORDER BY format, id LOOP
1321 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
1323 -- See if we can skip the XSLT ... it's expensive
1324 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
1325 -- Can't skip the transform
1326 IF xfrm.xslt <> '---' THEN
1327 transformed_xml := oils_xslt_process(marc,xfrm.xslt);
1329 transformed_xml := marc;
1332 prev_xfrm := xfrm.name;
1335 raw_text := COALESCE(
1343 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
1352 raw_text := REGEXP_REPLACE(raw_text, E'\\[.+?\\]', E'');
1353 raw_text := REGEXP_REPLACE(raw_text, E'\\mthe\\M|\\man?d?d\\M', E'', 'g'); -- arg! the pain!
1355 IF idx.first_word IS TRUE THEN
1356 raw_text := REGEXP_REPLACE(raw_text, E'^(\\w+).*?$', E'\\1');
1359 output_text := output_text || idx.name || ':' ||
1360 REGEXP_REPLACE(raw_text, E'\\s+', '', 'g') || ' ';
1364 RETURN BTRIM(output_text);
1367 $func$ LANGUAGE PLPGSQL;
1369 -- BEFORE UPDATE OR INSERT trigger for biblio.record_entry
1370 CREATE OR REPLACE FUNCTION biblio.fingerprint_trigger () RETURNS TRIGGER AS $func$
1373 -- For TG_ARGV, first param is language (like 'eng'), second is record type (like 'BKS')
1375 IF NEW.deleted IS TRUE THEN -- we don't much care, then, do we?
1379 NEW.fingerprint := biblio.extract_fingerprint(NEW.marc);
1380 NEW.quality := biblio.extract_quality(NEW.marc, TG_ARGV[0], TG_ARGV[1]);
1385 $func$ LANGUAGE PLPGSQL;
1387 CREATE OR REPLACE FUNCTION metabib.reingest_metabib_full_rec( bib_id BIGINT ) RETURNS VOID AS $func$
1389 PERFORM * FROM config.internal_flag WHERE name = 'ingest.assume_inserts_only' AND enabled;
1391 DELETE FROM metabib.real_full_rec WHERE record = bib_id;
1393 INSERT INTO metabib.real_full_rec (record, tag, ind1, ind2, subfield, value)
1394 SELECT record, tag, ind1, ind2, subfield, value FROM biblio.flatten_marc( bib_id );
1398 $func$ LANGUAGE PLPGSQL;
1400 CREATE OR REPLACE FUNCTION biblio.extract_located_uris( bib_id BIGINT, marcxml TEXT, editor_id INT ) RETURNS VOID AS $func$
1407 uri_owner_list TEXT[];
1416 current_uri_map_list INT[];
1417 current_map_owner_list INT[];
1421 uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',marcxml);
1422 IF ARRAY_UPPER(uris,1) > 0 THEN
1423 FOR i IN 1 .. ARRAY_UPPER(uris, 1) LOOP
1424 -- First we pull info out of the 856
1427 uri_href := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1];
1428 uri_label := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()',uri_xml))[1];
1429 uri_use := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()',uri_xml))[1];
1431 IF uri_label IS NULL THEN
1432 uri_label := uri_href;
1434 CONTINUE WHEN uri_href IS NULL;
1436 -- Get the distinct list of libraries wanting to use
1438 DISTINCT REGEXP_REPLACE(
1440 $re$^.*?\((\w+)\).*$$re$,
1443 ) INTO uri_owner_list
1446 '//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()',
1451 IF ARRAY_UPPER(uri_owner_list,1) > 0 THEN
1453 -- look for a matching uri
1454 IF uri_use IS NULL THEN
1455 SELECT id INTO uri_id
1457 WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active
1458 ORDER BY id LIMIT 1;
1459 IF NOT FOUND THEN -- create one
1460 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
1461 SELECT id INTO uri_id
1463 WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active;
1466 SELECT id INTO uri_id
1468 WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active
1469 ORDER BY id LIMIT 1;
1470 IF NOT FOUND THEN -- create one
1471 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
1472 SELECT id INTO uri_id
1474 WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
1478 FOR j IN 1 .. ARRAY_UPPER(uri_owner_list, 1) LOOP
1479 uri_owner := uri_owner_list[j];
1481 SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = BTRIM(REPLACE(uri_owner,chr(160),''));
1482 CONTINUE WHEN NOT FOUND;
1484 -- we need a call number to link through
1485 SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted;
1487 INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label)
1488 VALUES (uri_owner_id, bib_id, 'now', 'now', editor_id, editor_id, '##URI##');
1489 SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted;
1492 -- now, link them if they're not already
1493 SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id;
1495 INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id);
1496 SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id;
1499 current_uri_map_list := current_uri_map_list || uri_map_id;
1500 current_map_owner_list := current_map_owner_list || uri_cn_id;
1509 -- Clear any orphaned URIs, URI mappings and call
1510 -- numbers for this bib that weren't mapped above.
1513 FROM asset.uri_call_number_map m
1514 LEFT JOIN asset.call_number cn ON (cn.id = m.call_number)
1515 WHERE cn.record = bib_id
1516 AND cn.label = '##URI##'
1517 AND (NOT (m.id = ANY (current_uri_map_list))
1518 OR current_uri_map_list is NULL)
1520 SELECT uri INTO current_uri FROM asset.uri_call_number_map WHERE id = current_map;
1521 DELETE FROM asset.uri_call_number_map WHERE id = current_map;
1523 SELECT COUNT(*) INTO uri_map_count FROM asset.uri_call_number_map WHERE uri = current_uri;
1524 IF uri_map_count = 0 THEN
1525 DELETE FROM asset.uri WHERE id = current_uri;
1529 UPDATE asset.call_number
1530 SET deleted = TRUE, edit_date = now(), editor = editor_id
1533 FROM asset.call_number
1534 WHERE record = bib_id
1535 AND label = '##URI##'
1537 AND (NOT (id = ANY (current_map_owner_list))
1538 OR current_map_owner_list is NULL)
1543 $func$ LANGUAGE PLPGSQL;
1545 CREATE OR REPLACE FUNCTION metabib.remap_metarecord_for_bib(
1548 bib_is_deleted boolean DEFAULT false,
1549 retain_deleted boolean DEFAULT false
1550 ) RETURNS bigint AS $function$
1552 new_mapping BOOL := TRUE;
1555 tmp_mr metabib.metarecord%ROWTYPE;
1556 deleted_mrs BIGINT[];
1559 -- We need to make sure we're not a deleted master record of an MR
1560 IF bib_is_deleted THEN
1561 IF NOT retain_deleted THEN -- Go away for any MR that we're master of, unless retained
1562 DELETE FROM metabib.metarecord_source_map WHERE source = bib_id;
1565 FOR old_mr IN SELECT id FROM metabib.metarecord WHERE master_record = bib_id LOOP
1567 -- Now, are there any more sources on this MR?
1568 SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = old_mr;
1570 IF source_count = 0 AND NOT retain_deleted THEN -- No other records
1571 deleted_mrs := ARRAY_APPEND(deleted_mrs, old_mr); -- Just in case...
1572 DELETE FROM metabib.metarecord WHERE id = old_mr;
1574 ELSE -- indeed there are. Update it with a null cache and recalcualated master record
1575 UPDATE metabib.metarecord
1577 master_record = (SELECT id FROM biblio.record_entry WHERE fingerprint = fp AND NOT deleted ORDER BY quality DESC, id ASC LIMIT 1)
1582 ELSE -- insert or update
1584 FOR tmp_mr IN SELECT m.* FROM metabib.metarecord m JOIN metabib.metarecord_source_map s ON (s.metarecord = m.id) WHERE s.source = bib_id LOOP
1586 -- Find the first fingerprint-matching
1587 IF old_mr IS NULL AND fp = tmp_mr.fingerprint THEN
1588 old_mr := tmp_mr.id;
1589 new_mapping := FALSE;
1591 ELSE -- Our fingerprint changed ... maybe remove the old MR
1592 DELETE FROM metabib.metarecord_source_map WHERE metarecord = tmp_mr.id AND source = bib_id; -- remove the old source mapping
1593 SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = tmp_mr.id;
1594 IF source_count = 0 THEN -- No other records
1595 deleted_mrs := ARRAY_APPEND(deleted_mrs, tmp_mr.id);
1596 DELETE FROM metabib.metarecord WHERE id = tmp_mr.id;
1602 -- we found no suitable, preexisting MR based on old source maps
1603 IF old_mr IS NULL THEN
1604 SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp; -- is there one for our current fingerprint?
1606 IF old_mr IS NULL THEN -- nope, create one and grab its id
1607 INSERT INTO metabib.metarecord ( fingerprint, master_record ) VALUES ( fp, bib_id );
1608 SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp;
1610 ELSE -- indeed there is. update it with a null cache and recalcualated master record
1611 UPDATE metabib.metarecord
1613 master_record = (SELECT id FROM biblio.record_entry WHERE fingerprint = fp AND NOT deleted ORDER BY quality DESC, id ASC LIMIT 1)
1617 ELSE -- there was one we already attached to, update its mods cache and master_record
1618 UPDATE metabib.metarecord
1620 master_record = (SELECT id FROM biblio.record_entry WHERE fingerprint = fp AND NOT deleted ORDER BY quality DESC, id ASC LIMIT 1)
1625 INSERT INTO metabib.metarecord_source_map (metarecord, source) VALUES (old_mr, bib_id); -- new source mapping
1630 IF ARRAY_UPPER(deleted_mrs,1) > 0 THEN
1631 UPDATE action.hold_request SET target = old_mr WHERE target IN ( SELECT unnest(deleted_mrs) ) AND hold_type = 'M'; -- if we had to delete any MRs above, make sure their holds are moved
1637 $function$ LANGUAGE plpgsql;
1639 CREATE OR REPLACE FUNCTION biblio.map_authority_linking (bibid BIGINT, marc TEXT) RETURNS BIGINT AS $func$
1640 DELETE FROM authority.bib_linking WHERE bib = $1;
1641 INSERT INTO authority.bib_linking (bib, authority)
1644 FROM ( SELECT DISTINCT $1 AS bib,
1645 BTRIM(remove_paren_substring(txt))::BIGINT AS authority
1646 FROM unnest(oils_xpath('//*[@code="0"]/text()',$2)) x(txt)
1647 WHERE BTRIM(remove_paren_substring(txt)) ~ $re$^\d+$$re$
1648 ) y JOIN authority.record_entry r ON r.id = y.authority;
1650 $func$ LANGUAGE SQL;
1652 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$
1654 transformed_xml TEXT;
1655 rmarc TEXT := prmarc;
1659 xfrm config.xml_transform%ROWTYPE;
1660 attr_vector INT[] := '{}'::INT[];
1661 attr_vector_tmp INT[];
1662 attr_list TEXT[] := pattr_list;
1664 norm_attr_value TEXT[];
1667 attr_def config.record_attr_definition%ROWTYPE;
1668 ccvm_row config.coded_value_map%ROWTYPE;
1672 IF attr_list IS NULL OR rdeleted THEN -- need to do the full dance on INSERT or undelete
1673 SELECT ARRAY_AGG(name) INTO attr_list FROM config.record_attr_definition
1676 fixed_field IS NOT NULL OR
1677 xpath IS NOT NULL OR
1678 phys_char_sf IS NOT NULL OR
1685 IF rmarc IS NULL THEN
1686 SELECT marc INTO rmarc FROM biblio.record_entry WHERE id = rid;
1689 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE NOT composite AND name = ANY( attr_list ) ORDER BY format LOOP
1691 jump_past := FALSE; -- This gets set when we are non-multi and have found something
1692 attr_value := '{}'::TEXT[];
1693 norm_attr_value := '{}'::TEXT[];
1694 attr_vector_tmp := '{}'::INT[];
1696 SELECT * INTO ccvm_row FROM config.coded_value_map c WHERE c.ctype = attr_def.name LIMIT 1;
1698 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
1699 SELECT ARRAY_AGG(value) INTO attr_value
1700 FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x
1702 AND tag LIKE attr_def.tag
1704 WHEN attr_def.sf_list IS NOT NULL
1705 THEN POSITION(subfield IN attr_def.sf_list) > 0
1711 IF NOT attr_def.multi THEN
1712 attr_value := ARRAY[ARRAY_TO_STRING(attr_value, COALESCE(attr_def.joiner,' '))];
1717 IF NOT jump_past AND attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
1718 attr_value := attr_value || vandelay.marc21_extract_fixed_field_list(rmarc, attr_def.fixed_field);
1720 IF NOT attr_def.multi THEN
1721 attr_value := ARRAY[attr_value[1]];
1726 IF NOT jump_past AND attr_def.xpath IS NOT NULL THEN -- and xpath expression
1728 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
1730 -- See if we can skip the XSLT ... it's expensive
1731 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
1732 -- Can't skip the transform
1733 IF xfrm.xslt <> '---' THEN
1734 transformed_xml := oils_xslt_process(rmarc,xfrm.xslt);
1736 transformed_xml := rmarc;
1739 prev_xfrm := xfrm.name;
1742 IF xfrm.name IS NULL THEN
1743 -- just grab the marcxml (empty) transform
1744 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
1745 prev_xfrm := xfrm.name;
1748 FOR tmp_xml IN SELECT UNNEST(oils_xpath(attr_def.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]])) LOOP
1749 tmp_val := oils_xpath_string(
1752 COALESCE(attr_def.joiner,' '),
1753 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
1755 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
1756 attr_value := attr_value || tmp_val;
1757 EXIT WHEN NOT attr_def.multi;
1762 IF NOT jump_past AND attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
1763 SELECT ARRAY_AGG(m.value) INTO tmp_array
1764 FROM vandelay.marc21_physical_characteristics(rmarc) v
1765 LEFT JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
1766 WHERE v.subfield = attr_def.phys_char_sf AND (m.value IS NOT NULL AND BTRIM(m.value) <> '')
1767 AND ( ccvm_row.id IS NULL OR ( ccvm_row.id IS NOT NULL AND v.id IS NOT NULL) );
1769 attr_value := attr_value || tmp_array;
1771 IF NOT attr_def.multi THEN
1772 attr_value := ARRAY[attr_value[1]];
1777 -- apply index normalizers to attr_value
1778 FOR tmp_val IN SELECT value FROM UNNEST(attr_value) x(value) LOOP
1780 SELECT n.func AS func,
1781 n.param_count AS param_count,
1783 FROM config.index_normalizer n
1784 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
1785 WHERE attr = attr_def.name
1787 EXECUTE 'SELECT ' || normalizer.func || '(' ||
1788 COALESCE( quote_literal( tmp_val ), 'NULL' ) ||
1790 WHEN normalizer.param_count > 0
1791 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
1797 IF tmp_val IS NOT NULL AND tmp_val <> '' THEN
1798 -- note that a string that contains only blanks
1799 -- is a valid value for some attributes
1800 norm_attr_value := norm_attr_value || tmp_val;
1804 IF attr_def.filter THEN
1805 -- Create unknown uncontrolled values and find the IDs of the values
1806 IF ccvm_row.id IS NULL THEN
1807 FOR tmp_val IN SELECT value FROM UNNEST(norm_attr_value) x(value) LOOP
1808 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
1809 BEGIN -- use subtransaction to isolate unique constraint violations
1810 INSERT INTO metabib.uncontrolled_record_attr_value ( attr, value ) VALUES ( attr_def.name, tmp_val );
1811 EXCEPTION WHEN unique_violation THEN END;
1815 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 );
1817 SELECT ARRAY_AGG(id) INTO attr_vector_tmp FROM config.coded_value_map WHERE ctype = attr_def.name AND code = ANY( norm_attr_value );
1820 -- Add the new value to the vector
1821 attr_vector := attr_vector || attr_vector_tmp;
1824 IF attr_def.sorter THEN
1825 DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
1826 IF norm_attr_value[1] IS NOT NULL THEN
1827 INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, norm_attr_value[1]);
1833 /* We may need to rewrite the vlist to contain
1834 the intersection of new values for requested
1835 attrs and old values for ignored attrs. To
1836 do this, we take the old attr vlist and
1837 subtract any values that are valid for the
1838 requested attrs, and then add back the new
1839 set of attr values. */
1841 IF ARRAY_LENGTH(pattr_list, 1) > 0 THEN
1842 SELECT vlist INTO attr_vector_tmp FROM metabib.record_attr_vector_list WHERE source = rid;
1843 SELECT attr_vector_tmp - ARRAY_AGG(id::INT) INTO attr_vector_tmp FROM metabib.full_attr_id_map WHERE attr = ANY (pattr_list);
1844 attr_vector := attr_vector || attr_vector_tmp;
1847 -- On to composite attributes, now that the record attrs have been pulled. Processed in name order, so later composite
1848 -- attributes can depend on earlier ones.
1849 PERFORM metabib.compile_composite_attr_cache_init();
1850 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE composite AND name = ANY( attr_list ) ORDER BY name LOOP
1852 FOR ccvm_row IN SELECT * FROM config.coded_value_map c WHERE c.ctype = attr_def.name ORDER BY value LOOP
1854 tmp_val := metabib.compile_composite_attr( ccvm_row.id );
1855 CONTINUE WHEN tmp_val IS NULL OR tmp_val = ''; -- nothing to do
1857 IF attr_def.filter THEN
1858 IF attr_vector @@ tmp_val::query_int THEN
1859 attr_vector = attr_vector + intset(ccvm_row.id);
1860 EXIT WHEN NOT attr_def.multi;
1864 IF attr_def.sorter THEN
1865 IF attr_vector @@ tmp_val THEN
1866 DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
1867 INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, ccvm_row.code);
1875 IF ARRAY_LENGTH(attr_vector, 1) > 0 THEN
1876 INSERT INTO metabib.record_attr_vector_list (source, vlist) VALUES (rid, attr_vector)
1877 ON CONFLICT (source) DO UPDATE SET vlist = EXCLUDED.vlist;
1882 $func$ LANGUAGE PLPGSQL;
1884 CREATE OR REPLACE FUNCTION metabib.indexing_delete (bib biblio.record_entry, extra TEXT DEFAULT NULL) RETURNS BOOL AS $func$
1890 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.preserve_on_delete' AND enabled;
1893 PERFORM metabib.remap_metarecord_for_bib(bib.id, bib.fingerprint, TRUE, tmp_bool);
1895 IF NOT tmp_bool THEN
1896 -- One needs to keep these around to support searches
1897 -- with the #deleted modifier, so one should turn on the named
1898 -- internal flag for that functionality.
1899 DELETE FROM metabib.record_attr_vector_list WHERE source = bib.id;
1902 DELETE FROM authority.bib_linking abl WHERE abl.bib = bib.id; -- Avoid updating fields in bibs that are no longer visible
1903 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = bib.id; -- Separate any multi-homed items
1904 DELETE FROM metabib.browse_entry_def_map WHERE source = bib.id; -- Don't auto-suggest deleted bibs
1907 EXCEPTION WHEN OTHERS THEN
1908 GET STACKED DIAGNOSTICS diag_detail = PG_EXCEPTION_DETAIL,
1909 diag_context = PG_EXCEPTION_CONTEXT;
1910 RAISE WARNING '%\n%', diag_detail, diag_context;
1913 $func$ LANGUAGE PLPGSQL;
1915 CREATE OR REPLACE FUNCTION metabib.indexing_update (bib biblio.record_entry, insert_only BOOL DEFAULT FALSE, extra TEXT DEFAULT NULL) RETURNS BOOL AS $func$
1917 skip_facet BOOL := FALSE;
1918 skip_display BOOL := FALSE;
1919 skip_browse BOOL := FALSE;
1920 skip_search BOOL := FALSE;
1921 skip_auth BOOL := FALSE;
1922 skip_full BOOL := FALSE;
1923 skip_attrs BOOL := FALSE;
1924 skip_luri BOOL := FALSE;
1925 skip_mrmap BOOL := FALSE;
1926 only_attrs TEXT[] := NULL;
1927 only_fields INT[] := '{}'::INT[];
1932 -- Record authority linking
1933 SELECT extra LIKE '%skip_authority%' INTO skip_auth;
1934 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking' AND enabled;
1935 IF NOT FOUND AND NOT skip_auth THEN
1936 PERFORM biblio.map_authority_linking( bib.id, bib.marc );
1939 -- Flatten and insert the mfr data
1940 SELECT extra LIKE '%skip_full_rec%' INTO skip_full;
1941 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled;
1942 IF NOT FOUND AND NOT skip_full THEN
1943 PERFORM metabib.reingest_metabib_full_rec(bib.id);
1946 -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields
1947 SELECT extra LIKE '%skip_attrs%' INTO skip_attrs;
1948 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled;
1949 IF NOT FOUND AND NOT skip_attrs THEN
1950 IF extra ~ 'attr\(\s*(\w[ ,\w]*?)\s*\)' THEN
1951 SELECT REGEXP_SPLIT_TO_ARRAY(
1952 (REGEXP_MATCHES(extra, 'attr\(\s*(\w[ ,\w]*?)\s*\)'))[1],
1957 PERFORM metabib.reingest_record_attributes(bib.id, only_attrs, bib.marc, insert_only);
1960 -- Gather and insert the field entry data
1961 SELECT extra LIKE '%skip_facet%' INTO skip_facet;
1962 SELECT extra LIKE '%skip_display%' INTO skip_display;
1963 SELECT extra LIKE '%skip_browse%' INTO skip_browse;
1964 SELECT extra LIKE '%skip_search%' INTO skip_search;
1966 IF extra ~ 'field_list\(\s*(\d[ ,\d]+)\s*\)' THEN
1967 SELECT REGEXP_SPLIT_TO_ARRAY(
1968 (REGEXP_MATCHES(extra, 'field_list\(\s*(\d[ ,\d]+)\s*\)'))[1],
1970 )::INT[] INTO only_fields;
1973 IF NOT skip_facet OR NOT skip_display OR NOT skip_browse OR NOT skip_search THEN
1974 PERFORM metabib.reingest_metabib_field_entries(bib.id, skip_facet, skip_display, skip_browse, skip_search, only_fields);
1977 -- Located URI magic
1978 SELECT extra LIKE '%skip_luri%' INTO skip_luri;
1979 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
1980 IF NOT FOUND AND NOT skip_luri THEN PERFORM biblio.extract_located_uris( bib.id, bib.marc, bib.editor ); END IF;
1982 -- (re)map metarecord-bib linking
1983 SELECT extra LIKE '%skip_mrmap%' INTO skip_mrmap;
1984 IF insert_only THEN -- if not deleted and performing an insert, check for the flag
1985 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled;
1986 IF NOT FOUND AND NOT skip_mrmap THEN
1987 PERFORM metabib.remap_metarecord_for_bib( bib.id, bib.fingerprint );
1989 ELSE -- we're doing an update, and we're not deleted, remap
1990 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_update' AND enabled;
1991 IF NOT FOUND AND NOT skip_mrmap THEN
1992 PERFORM metabib.remap_metarecord_for_bib( bib.id, bib.fingerprint );
1997 EXCEPTION WHEN OTHERS THEN
1998 GET STACKED DIAGNOSTICS diag_detail = PG_EXCEPTION_DETAIL,
1999 diag_context = PG_EXCEPTION_CONTEXT;
2000 RAISE WARNING '%\n%', diag_detail, diag_context;
2003 $func$ LANGUAGE PLPGSQL;
2005 CREATE OR REPLACE FUNCTION authority.indexing_delete (auth authority.record_entry, extra TEXT DEFAULT NULL) RETURNS BOOL AS $func$
2011 DELETE FROM authority.bib_linking WHERE authority = NEW.id; -- Avoid updating fields in bibs that are no longer visible
2012 DELETE FROM authority.full_rec WHERE record = NEW.id; -- Avoid validating fields against deleted authority records
2013 DELETE FROM authority.simple_heading WHERE record = NEW.id;
2014 -- Should remove matching $0 from controlled fields at the same time?
2016 -- XXX What do we about the actual linking subfields present in
2017 -- authority records that target this one when this happens?
2018 DELETE FROM authority.authority_linking WHERE source = NEW.id OR target = NEW.id;
2021 EXCEPTION WHEN OTHERS THEN
2022 GET STACKED DIAGNOSTICS diag_detail = PG_EXCEPTION_DETAIL,
2023 diag_context = PG_EXCEPTION_CONTEXT;
2024 RAISE WARNING '%\n%', diag_detail, diag_context;
2027 $func$ LANGUAGE PLPGSQL;
2029 CREATE OR REPLACE FUNCTION authority.indexing_update (auth authority.record_entry, insert_only BOOL DEFAULT FALSE, old_heading TEXT DEFAULT NULL) RETURNS BOOL AS $func$
2031 ashs authority.simple_heading%ROWTYPE;
2032 mbe_row metabib.browse_entry%ROWTYPE;
2039 -- Unless there's a setting stopping us, propagate these updates to any linked bib records when the heading changes
2040 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_auto_update' AND enabled;
2042 IF NOT FOUND AND auth.heading <> old_heading THEN
2043 PERFORM authority.propagate_changes(auth.id);
2046 IF NOT insert_only THEN
2047 DELETE FROM authority.authority_linking WHERE source = auth.id;
2048 DELETE FROM authority.simple_heading WHERE record = auth.id;
2051 INSERT INTO authority.authority_linking (source, target, field)
2052 SELECT source, target, field FROM authority.calculate_authority_linking(
2053 auth.id, auth.control_set, auth.marc::XML
2056 FOR ashs IN SELECT * FROM authority.simple_heading_set(auth.marc) LOOP
2058 INSERT INTO authority.simple_heading (record,atag,value,sort_value,thesaurus)
2059 VALUES (ashs.record, ashs.atag, ashs.value, ashs.sort_value, ashs.thesaurus);
2060 ash_id := CURRVAL('authority.simple_heading_id_seq'::REGCLASS);
2062 SELECT INTO mbe_row * FROM metabib.browse_entry
2063 WHERE value = ashs.value AND sort_value = ashs.sort_value;
2066 mbe_id := mbe_row.id;
2068 INSERT INTO metabib.browse_entry
2069 ( value, sort_value ) VALUES
2070 ( ashs.value, ashs.sort_value );
2072 mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS);
2075 INSERT INTO metabib.browse_entry_simple_heading_map (entry,simple_heading) VALUES (mbe_id,ash_id);
2079 -- Flatten and insert the afr data
2080 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_full_rec' AND enabled;
2082 PERFORM authority.reingest_authority_full_rec(auth.id);
2083 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_rec_descriptor' AND enabled;
2085 PERFORM authority.reingest_authority_rec_descriptor(auth.id);
2089 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_symspell_reification' AND enabled;
2091 PERFORM search.symspell_dictionary_reify();
2095 EXCEPTION WHEN OTHERS THEN
2096 GET STACKED DIAGNOSTICS diag_detail = PG_EXCEPTION_DETAIL,
2097 diag_context = PG_EXCEPTION_CONTEXT;
2098 RAISE WARNING '%\n%', diag_detail, diag_context;
2101 $func$ LANGUAGE PLPGSQL;
2103 CREATE OR REPLACE FUNCTION metabib.browse_normalize(facet_text TEXT, mapped_field INT) RETURNS TEXT AS $$
2109 SELECT n.func AS func,
2110 n.param_count AS param_count,
2112 FROM config.index_normalizer n
2113 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
2114 WHERE m.field = mapped_field AND m.pos < 0
2117 EXECUTE 'SELECT ' || normalizer.func || '(' ||
2118 quote_literal( facet_text ) ||
2120 WHEN normalizer.param_count > 0
2121 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
2124 ')' INTO facet_text;
2131 $$ LANGUAGE PLPGSQL;
2134 -- This mimics a specific part of QueryParser, turning the first part of a
2135 -- classed search (search_class) into a set of classes and possibly fields.
2136 -- search_class might look like "author" or "title|proper" or "ti|uniform"
2137 -- or "au" or "au|corporate|personal" or anything like that, where the first
2138 -- element of the list you get by separating on the "|" character is either
2139 -- a registered class (config.metabib_class) or an alias
2140 -- (config.metabib_search_alias), and the rest of any such elements are
2141 -- fields (config.metabib_field).
2143 FUNCTION metabib.search_class_to_registered_components(search_class TEXT)
2144 RETURNS SETOF RECORD AS $func$
2146 search_parts TEXT[];
2148 search_part_count INTEGER;
2150 registered_class config.metabib_class%ROWTYPE;
2151 registered_alias config.metabib_search_alias%ROWTYPE;
2152 registered_field config.metabib_field%ROWTYPE;
2154 search_parts := REGEXP_SPLIT_TO_ARRAY(search_class, E'\\|');
2156 search_part_count := ARRAY_LENGTH(search_parts, 1);
2157 IF search_part_count = 0 THEN
2160 SELECT INTO registered_class
2161 * FROM config.metabib_class WHERE name = search_parts[1];
2163 IF search_part_count < 2 THEN -- all fields
2164 rec := (registered_class.name, NULL::INTEGER);
2168 FOR field_name IN SELECT *
2169 FROM UNNEST(search_parts[2:search_part_count]) LOOP
2170 SELECT INTO registered_field
2171 * FROM config.metabib_field
2172 WHERE name = field_name AND
2173 field_class = registered_class.name;
2175 rec := (registered_class.name, registered_field.id);
2180 -- maybe we have an alias?
2181 SELECT INTO registered_alias
2182 * FROM config.metabib_search_alias WHERE alias=search_parts[1];
2186 IF search_part_count < 2 THEN -- return w/e the alias says
2188 registered_alias.field_class, registered_alias.field
2193 FOR field_name IN SELECT *
2194 FROM UNNEST(search_parts[2:search_part_count]) LOOP
2195 SELECT INTO registered_field
2196 * FROM config.metabib_field
2197 WHERE name = field_name AND
2198 field_class = registered_alias.field_class;
2201 registered_alias.field_class,
2212 $func$ LANGUAGE PLPGSQL ROWS 1;
2215 -- Given a string such as a user might type into a search box, prepare
2216 -- two changed variants for TO_TSQUERY(). See
2217 -- http://www.postgresql.org/docs/9.0/static/textsearch-controls.html
2218 -- The first variant is normalized to match indexed documents regardless
2219 -- of diacritics. The second variant keeps its diacritics for proper
2220 -- highlighting via TS_HEADLINE().
2222 FUNCTION metabib.autosuggest_prepare_tsquery(orig TEXT) RETURNS TEXT[] AS
2225 orig_ended_in_space BOOLEAN;
2230 orig_ended_in_space := orig ~ E'\\s$';
2232 orig := ARRAY_TO_STRING(
2233 evergreen.regexp_split_to_array(orig, E'\\W+'), ' '
2236 normalized := public.naco_normalize(orig); -- also trim()s
2237 plain := trim(orig);
2239 IF NOT orig_ended_in_space THEN
2240 plain := plain || ':*';
2241 normalized := normalized || ':*';
2244 plain := ARRAY_TO_STRING(
2245 evergreen.regexp_split_to_array(plain, E'\\s+'), ' & '
2247 normalized := ARRAY_TO_STRING(
2248 evergreen.regexp_split_to_array(normalized, E'\\s+'), ' & '
2251 RETURN ARRAY[normalized, plain];
2253 $$ LANGUAGE PLPGSQL;
2255 -- Functions metabib.browse, metabib.staged_browse, and metabib.suggest_browse_entries
2256 -- will be created later, after internal dependencies are resolved.
2258 CREATE OR REPLACE FUNCTION public.oils_tsearch2 () RETURNS TRIGGER AS $$
2262 temp_vector TEXT := '';
2268 NEW.index_vector = ''::tsvector;
2270 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
2272 SELECT n.func AS func,
2273 n.param_count AS param_count,
2275 FROM config.index_normalizer n
2276 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
2277 WHERE field = NEW.field AND m.pos < 0
2279 EXECUTE 'SELECT ' || normalizer.func || '(' ||
2280 quote_literal( value ) ||
2282 WHEN normalizer.param_count > 0
2283 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
2293 SELECT n.func AS func,
2294 n.param_count AS param_count,
2296 FROM config.index_normalizer n
2297 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
2298 WHERE field = NEW.field AND m.pos >= 0
2300 EXECUTE 'SELECT ' || normalizer.func || '(' ||
2301 quote_literal( value ) ||
2303 WHEN normalizer.param_count > 0
2304 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
2312 IF TG_TABLE_NAME::TEXT ~ 'browse_entry$' THEN
2314 value := ARRAY_TO_STRING(
2315 evergreen.regexp_split_to_array(value, E'\\W+'), ' '
2317 value := public.search_normalize(value);
2318 NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);
2320 ELSIF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
2323 SELECT DISTINCT m.ts_config, m.index_weight
2324 FROM config.metabib_class_ts_map m
2325 LEFT JOIN metabib.record_attr_vector_list r ON (r.source = NEW.source)
2326 LEFT JOIN config.coded_value_map ccvm ON (
2327 ccvm.ctype IN ('item_lang', 'language') AND
2328 ccvm.code = m.index_lang AND
2329 r.vlist @> intset(ccvm.id)
2331 WHERE m.field_class = TG_ARGV[0]
2333 AND (m.always OR NOT EXISTS (SELECT 1 FROM config.metabib_field_ts_map WHERE metabib_field = NEW.field))
2334 AND (m.index_lang IS NULL OR ccvm.id IS NOT NULL)
2336 SELECT DISTINCT m.ts_config, m.index_weight
2337 FROM config.metabib_field_ts_map m
2338 LEFT JOIN metabib.record_attr_vector_list r ON (r.source = NEW.source)
2339 LEFT JOIN config.coded_value_map ccvm ON (
2340 ccvm.ctype IN ('item_lang', 'language') AND
2341 ccvm.code = m.index_lang AND
2342 r.vlist @> intset(ccvm.id)
2344 WHERE m.metabib_field = NEW.field
2346 AND (m.index_lang IS NULL OR ccvm.id IS NOT NULL)
2347 ORDER BY index_weight ASC
2351 IF cur_weight IS NOT NULL AND cur_weight != ts_rec.index_weight THEN
2352 NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
2356 cur_weight = ts_rec.index_weight;
2357 SELECT INTO temp_vector temp_vector || ' ' || to_tsvector(ts_rec.ts_config::regconfig, value)::TEXT;
2360 NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
2362 NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);
2367 $$ LANGUAGE PLPGSQL;
2370 CREATE TYPE metabib.flat_browse_entry_appearance AS (
2371 browse_entry BIGINT,
2376 sources INT, -- visible ones, that is
2377 asources INT, -- visible ones, that is
2378 row_number INT, -- internal use, sort of
2379 accurate BOOL, -- Count in sources field is accurate? Not
2380 -- if we had more than a browse superpage
2381 -- of records to look at.
2382 aaccurate BOOL, -- See previous comment...
2387 CREATE OR REPLACE FUNCTION metabib.browse_bib_pivot(
2390 ) RETURNS BIGINT AS $p$
2392 FROM metabib.browse_entry mbe
2393 JOIN metabib.browse_entry_def_map mbedm ON (
2394 mbedm.entry = mbe.id
2395 AND mbedm.def = ANY($1)
2397 WHERE mbe.sort_value >= public.naco_normalize($2)
2398 ORDER BY mbe.sort_value, mbe.value LIMIT 1;
2399 $p$ LANGUAGE SQL STABLE;
2401 CREATE OR REPLACE FUNCTION metabib.browse_authority_pivot(
2404 ) RETURNS BIGINT AS $p$
2406 FROM metabib.browse_entry mbe
2407 JOIN metabib.browse_entry_simple_heading_map mbeshm ON ( mbeshm.entry = mbe.id )
2408 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
2409 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
2410 ash.atag = map.authority_field
2411 AND map.metabib_field = ANY($1)
2413 WHERE mbe.sort_value >= public.naco_normalize($2)
2414 ORDER BY mbe.sort_value, mbe.value LIMIT 1;
2415 $p$ LANGUAGE SQL STABLE;
2417 CREATE OR REPLACE FUNCTION metabib.browse_authority_refs_pivot(
2420 ) RETURNS BIGINT AS $p$
2422 FROM metabib.browse_entry mbe
2423 JOIN metabib.browse_entry_simple_heading_map mbeshm ON ( mbeshm.entry = mbe.id )
2424 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
2425 JOIN authority.control_set_auth_field_metabib_field_map_refs_only map ON (
2426 ash.atag = map.authority_field
2427 AND map.metabib_field = ANY($1)
2429 WHERE mbe.sort_value >= public.naco_normalize($2)
2430 ORDER BY mbe.sort_value, mbe.value LIMIT 1;
2431 $p$ LANGUAGE SQL STABLE;
2433 CREATE OR REPLACE FUNCTION metabib.browse_pivot(
2436 ) RETURNS BIGINT AS $p$
2437 SELECT id FROM metabib.browse_entry
2439 metabib.browse_bib_pivot($1, $2),
2440 metabib.browse_authority_refs_pivot($1,$2) -- only look in 4xx, 5xx, 7xx of authority
2442 ORDER BY sort_value, value LIMIT 1;
2443 $p$ LANGUAGE SQL STABLE;
2446 -- This function is used to help clean up facet labels. Due to quirks in
2447 -- MARC parsing, some facet labels may be generated with periods or commas
2448 -- at the end. This will strip a trailing commas off all the time, and
2449 -- periods when they don't look like they are part of initials or dotted
2451 -- Smith, John => no change
2452 -- Smith, John, => Smith, John
2453 -- Smith, John. => Smith, John
2454 -- Public, John Q. => no change
2455 -- Public, John, Ph.D. => no change
2456 -- Atlanta -- Georgia -- U.S. => no change
2457 -- Atlanta -- Georgia. => Atlanta, Georgia
2458 -- The fellowship of the rings / => The fellowship of the rings
2459 -- Some title ; => Some title
2460 CREATE OR REPLACE FUNCTION metabib.trim_trailing_punctuation ( TEXT ) RETURNS TEXT AS $$
2466 last_char = substring(result from '.$');
2468 IF last_char = ',' THEN
2469 result := substring(result from '^(.*),$');
2471 ELSIF last_char = '.' THEN
2472 -- must have a single word-character following at least one non-word character
2473 IF substring(result from '\W\w\.$') IS NULL THEN
2474 result := substring(result from '^(.*)\.$');
2477 ELSIF last_char IN ('/',':',';','=') THEN -- Dangling subtitle/SoR separator
2478 IF substring(result from ' .$') IS NOT NULL THEN -- must have a space before last_char
2479 result := substring(result from '^(.*) .$');
2486 $$ language 'plpgsql';