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 GIST (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 GIST (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 GIST (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 GIST (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 GIST (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 GIST (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 GIST (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 GIST (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 GIST (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 GIST (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 GIST (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 GIST (index_vector);
174 CREATE INDEX metabib_combined_series_field_source_idx ON metabib.combined_series_field_entry (metabib_field);
176 CREATE TABLE metabib.facet_entry (
177 id BIGSERIAL PRIMARY KEY,
178 source BIGINT NOT NULL,
182 CREATE INDEX metabib_facet_entry_field_idx ON metabib.facet_entry (field);
183 CREATE INDEX metabib_facet_entry_value_idx ON metabib.facet_entry (SUBSTRING(value,1,1024));
184 CREATE INDEX metabib_facet_entry_source_idx ON metabib.facet_entry (source);
186 CREATE TABLE metabib.browse_entry (
187 id BIGSERIAL PRIMARY KEY,
189 index_vector tsvector,
190 sort_value TEXT NOT NULL,
191 UNIQUE(sort_value, value)
195 CREATE INDEX browse_entry_sort_value_idx
196 ON metabib.browse_entry USING BTREE (sort_value);
198 CREATE INDEX metabib_browse_entry_index_vector_idx ON metabib.browse_entry USING GIN (index_vector);
199 CREATE TRIGGER metabib_browse_entry_fti_trigger
200 BEFORE INSERT OR UPDATE ON metabib.browse_entry
201 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
204 CREATE TABLE metabib.browse_entry_def_map (
205 id BIGSERIAL PRIMARY KEY,
206 entry BIGINT REFERENCES metabib.browse_entry (id),
207 def INT REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
208 source BIGINT REFERENCES biblio.record_entry (id),
209 authority BIGINT REFERENCES authority.record_entry (id) ON DELETE SET NULL
211 CREATE INDEX browse_entry_def_map_def_idx ON metabib.browse_entry_def_map (def);
212 CREATE INDEX browse_entry_def_map_entry_idx ON metabib.browse_entry_def_map (entry);
213 CREATE INDEX browse_entry_def_map_source_idx ON metabib.browse_entry_def_map (source);
215 CREATE TABLE metabib.browse_entry_simple_heading_map (
216 id BIGSERIAL PRIMARY KEY,
217 entry BIGINT REFERENCES metabib.browse_entry (id),
218 simple_heading BIGINT REFERENCES authority.simple_heading (id) ON DELETE CASCADE
220 CREATE INDEX browse_entry_sh_map_entry_idx ON metabib.browse_entry_simple_heading_map (entry);
221 CREATE INDEX browse_entry_sh_map_sh_idx ON metabib.browse_entry_simple_heading_map (simple_heading);
223 CREATE OR REPLACE FUNCTION metabib.facet_normalize_trigger () RETURNS TRIGGER AS $$
228 facet_text := NEW.value;
231 SELECT n.func AS func,
232 n.param_count AS param_count,
234 FROM config.index_normalizer n
235 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
236 WHERE m.field = NEW.field AND m.pos < 0
239 EXECUTE 'SELECT ' || normalizer.func || '(' ||
240 quote_literal( facet_text ) ||
242 WHEN normalizer.param_count > 0
243 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
250 NEW.value = facet_text;
256 CREATE TRIGGER facet_normalize_tgr
257 BEFORE UPDATE OR INSERT ON metabib.facet_entry
258 FOR EACH ROW EXECUTE PROCEDURE metabib.facet_normalize_trigger();
260 CREATE OR REPLACE FUNCTION evergreen.facet_force_nfc() RETURNS TRIGGER AS $$
262 NEW.value := force_unicode_normal_form(NEW.value,'NFC');
267 CREATE TRIGGER facet_force_nfc_tgr
268 BEFORE UPDATE OR INSERT ON metabib.facet_entry
269 FOR EACH ROW EXECUTE PROCEDURE evergreen.facet_force_nfc();
271 -- DECREMENTING serial starts at -1
272 CREATE SEQUENCE metabib.uncontrolled_record_attr_value_id_seq INCREMENT BY -1;
274 CREATE TABLE metabib.uncontrolled_record_attr_value (
275 id BIGINT PRIMARY KEY DEFAULT nextval('metabib.uncontrolled_record_attr_value_id_seq'),
276 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name),
279 CREATE UNIQUE INDEX muv_once_idx ON metabib.uncontrolled_record_attr_value (attr,value);
281 CREATE VIEW metabib.record_attr_id_map AS
282 SELECT id, attr, value FROM metabib.uncontrolled_record_attr_value
284 SELECT c.id, c.ctype AS attr, c.code AS value
285 FROM config.coded_value_map c
286 JOIN config.record_attr_definition d ON (d.name = c.ctype AND NOT d.composite);
288 CREATE VIEW metabib.composite_attr_id_map AS
289 SELECT c.id, c.ctype AS attr, c.code AS value
290 FROM config.coded_value_map c
291 JOIN config.record_attr_definition d ON (d.name = c.ctype AND d.composite);
293 CREATE VIEW metabib.full_attr_id_map AS
294 SELECT id, attr, value FROM metabib.record_attr_id_map
296 SELECT id, attr, value FROM metabib.composite_attr_id_map;
299 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr_cache_init () RETURNS BOOL AS $f$
300 $_SHARED{metabib_compile_composite_attr_cache} = {}
301 if ! exists $_SHARED{metabib_compile_composite_attr_cache};
302 return exists $_SHARED{metabib_compile_composite_attr_cache};
303 $f$ LANGUAGE PLPERLU;
305 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr_cache_disable () RETURNS BOOL AS $f$
306 delete $_SHARED{metabib_compile_composite_attr_cache};
307 return ! exists $_SHARED{metabib_compile_composite_attr_cache};
308 $f$ LANGUAGE PLPERLU;
310 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr_cache_invalidate () RETURNS BOOL AS $f$
311 SELECT metabib.compile_composite_attr_cache_disable() AND metabib.compile_composite_attr_cache_init();
314 CREATE OR REPLACE FUNCTION metabib.composite_attr_def_cache_inval_tgr () RETURNS TRIGGER AS $f$
316 PERFORM metabib.compile_composite_attr_cache_invalidate();
319 $f$ LANGUAGE PLPGSQL;
321 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();
323 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr ( cattr_def TEXT ) RETURNS query_int AS $func$
328 my $def = decode_json($json);
330 die("Composite attribute definition not supplied") unless $def;
332 my $_cache = (exists $_SHARED{metabib_compile_composite_attr_cache}) ? 1 : 0;
334 return $_SHARED{metabib_compile_composite_attr_cache}{$json}
335 if ($_cache && $_SHARED{metabib_compile_composite_attr_cache}{$json});
342 if (ref $d eq 'HASH') { # node or AND
343 if (exists $d->{_attr}) { # it is a node
344 my $plan = spi_prepare('SELECT * FROM metabib.full_attr_id_map WHERE attr = $1 AND value = $2', qw/TEXT TEXT/);
345 my $id = spi_exec_prepared(
346 $plan, {limit => 1}, $d->{_attr}, $d->{_val}
350 } elsif (exists $d->{_not} && scalar(keys(%$d)) == 1) { # it is a NOT
351 return '!' . recurse($$d{_not});
352 } else { # an AND list
353 @list = map { recurse($$d{$_}) } sort keys %$d;
355 } elsif (ref $d eq 'ARRAY') {
357 @list = map { recurse($_) } @$d;
360 @list = grep { defined && $_ ne '' } @list;
362 return '(' . join($j,@list) . ')' if @list;
366 my $val = recurse($def) || undef;
367 $_SHARED{metabib_compile_composite_attr_cache}{$json} = $val if $_cache;
370 $func$ IMMUTABLE LANGUAGE plperlu;
372 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr ( cattr_id INT ) RETURNS query_int AS $func$
373 SELECT metabib.compile_composite_attr(definition) FROM config.composite_attr_entry_definition WHERE coded_value = $1;
374 $func$ STRICT IMMUTABLE LANGUAGE SQL;
376 CREATE TABLE metabib.record_attr_vector_list (
377 source BIGINT PRIMARY KEY REFERENCES biblio.record_entry (id),
378 vlist INT[] NOT NULL -- stores id from ccvm AND murav
380 CREATE INDEX mrca_vlist_idx ON metabib.record_attr_vector_list USING gin ( vlist gin__int_ops );
382 /* This becomes a view, and we do sorters differently ...
383 CREATE TABLE metabib.record_attr (
384 id BIGINT PRIMARY KEY REFERENCES biblio.record_entry (id) ON DELETE CASCADE,
385 attrs HSTORE NOT NULL DEFAULT ''::HSTORE
387 CREATE INDEX metabib_svf_attrs_idx ON metabib.record_attr USING GIST (attrs);
388 CREATE INDEX metabib_svf_date1_idx ON metabib.record_attr ((attrs->'date1'));
389 CREATE INDEX metabib_svf_dates_idx ON metabib.record_attr ((attrs->'date1'),(attrs->'date2'));
393 CREATE TABLE metabib.record_sorter (
394 id BIGSERIAL PRIMARY KEY,
395 source BIGINT NOT NULL REFERENCES biblio.record_entry (id) ON DELETE CASCADE,
396 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE,
399 CREATE INDEX metabib_sorter_source_idx ON metabib.record_sorter (source); -- we may not need one of this or the next ... stats will tell
400 CREATE INDEX metabib_sorter_s_a_idx ON metabib.record_sorter (source, attr);
401 CREATE INDEX metabib_sorter_a_v_idx ON metabib.record_sorter (attr, value);
404 CREATE TYPE metabib.record_attr_type AS (
409 -- Back-compat view ... we're moving to an INTARRAY world
410 CREATE VIEW metabib.record_attr_flat AS
411 SELECT v.source AS id,
414 FROM metabib.full_attr_id_map m
415 JOIN metabib.record_attr_vector_list v ON ( m.id = ANY( v.vlist ) );
417 CREATE VIEW metabib.record_attr AS
418 SELECT id, HSTORE( ARRAY_AGG( attr ), ARRAY_AGG( value ) ) AS attrs FROM metabib.record_attr_flat GROUP BY 1;
420 -- Back-back-compat view ... we use to live in an HSTORE world
421 CREATE TYPE metabib.rec_desc_type AS (
439 CREATE VIEW metabib.rec_descriptor AS
442 (populate_record(NULL::metabib.rec_desc_type, attrs)).*
443 FROM metabib.record_attr;
445 -- Use a sequence that matches previous version, for easier upgrading.
446 CREATE SEQUENCE metabib.full_rec_id_seq;
448 CREATE TABLE metabib.real_full_rec (
449 id BIGINT NOT NULL DEFAULT NEXTVAL('metabib.full_rec_id_seq'::REGCLASS),
450 record BIGINT NOT NULL,
451 tag CHAR(3) NOT NULL,
456 index_vector tsvector NOT NULL
458 ALTER TABLE metabib.real_full_rec ADD PRIMARY KEY (id);
460 CREATE INDEX metabib_full_rec_tag_subfield_idx ON metabib.real_full_rec (tag,subfield);
461 CREATE INDEX metabib_full_rec_value_idx ON metabib.real_full_rec (substring(value,1,1024));
462 /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */
463 CREATE INDEX metabib_full_rec_value_tpo_index ON metabib.real_full_rec (substring(value,1,1024) text_pattern_ops);
464 CREATE INDEX metabib_full_rec_record_idx ON metabib.real_full_rec (record);
465 CREATE INDEX metabib_full_rec_index_vector_idx ON metabib.real_full_rec USING GIST (index_vector);
466 CREATE INDEX metabib_full_rec_isxn_caseless_idx
467 ON metabib.real_full_rec (LOWER(value))
468 WHERE tag IN ('020', '022', '024');
469 -- This next index might fully supplant the one above, but leaving both for now.
470 -- (they are not too large)
471 -- The reason we need this index is to ensure that the query parser always
472 -- prefers this index over the simpler tag/subfield index, as this greatly
473 -- increases Vandelay overlay speed for these identifiers, especially when
474 -- a record has many of these fields (around > 4-6 seems like the cutoff
475 -- on at least one PG9.1 system)
476 -- A similar index could be added for other fields (e.g. 010), but one should
477 -- leave out the LOWER() in all other cases.
478 -- TODO: verify whether we can discard the non tag/subfield/substring version
479 -- above (metabib_full_rec_isxn_caseless_idx)
480 CREATE INDEX metabib_full_rec_02x_tag_subfield_lower_substring
481 ON metabib.real_full_rec (tag, subfield, LOWER(substring(value, 1, 1024)))
482 WHERE tag IN ('020', '022', '024');
485 CREATE TRIGGER metabib_full_rec_fti_trigger
486 BEFORE UPDATE OR INSERT ON metabib.real_full_rec
487 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('default');
489 CREATE OR REPLACE VIEW metabib.full_rec AS
496 SUBSTRING(value,1,1024) AS value,
498 FROM metabib.real_full_rec;
500 CREATE OR REPLACE RULE metabib_full_rec_insert_rule
501 AS ON INSERT TO metabib.full_rec
503 INSERT INTO metabib.real_full_rec VALUES (
504 COALESCE(NEW.id, NEXTVAL('metabib.full_rec_id_seq'::REGCLASS)),
514 CREATE OR REPLACE RULE metabib_full_rec_update_rule
515 AS ON UPDATE TO metabib.full_rec
517 UPDATE metabib.real_full_rec SET
523 subfield = NEW.subfield,
525 index_vector = NEW.index_vector
528 CREATE OR REPLACE RULE metabib_full_rec_delete_rule
529 AS ON DELETE TO metabib.full_rec
531 DELETE FROM metabib.real_full_rec WHERE id = OLD.id;
533 CREATE TABLE metabib.metarecord_source_map (
534 id BIGSERIAL PRIMARY KEY,
535 metarecord BIGINT NOT NULL,
536 source BIGINT NOT NULL
538 CREATE INDEX metabib_metarecord_source_map_metarecord_idx ON metabib.metarecord_source_map (metarecord);
539 CREATE INDEX metabib_metarecord_source_map_source_record_idx ON metabib.metarecord_source_map (source);
541 CREATE TYPE metabib.field_entry_template AS (
554 CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry ( rid BIGINT, default_joiner TEXT ) RETURNS SETOF metabib.field_entry_template AS $func$
556 bib biblio.record_entry%ROWTYPE;
557 idx config.metabib_field%ROWTYPE;
558 xfrm config.xml_transform%ROWTYPE;
560 transformed_xml TEXT;
562 xml_node_list TEXT[];
568 joiner TEXT := default_joiner; -- XXX will index defs supply a joiner?
570 authority_link BIGINT;
571 output_row metabib.field_entry_template%ROWTYPE;
574 -- Start out with no field-use bools set
575 output_row.browse_field = FALSE;
576 output_row.facet_field = FALSE;
577 output_row.search_field = FALSE;
580 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
582 -- Loop over the indexing entries
583 FOR idx IN SELECT * FROM config.metabib_field ORDER BY format LOOP
585 joiner := COALESCE(idx.joiner, default_joiner);
587 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
589 -- See if we can skip the XSLT ... it's expensive
590 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
591 -- Can't skip the transform
592 IF xfrm.xslt <> '---' THEN
593 transformed_xml := oils_xslt_process(bib.marc,xfrm.xslt);
595 transformed_xml := bib.marc;
598 prev_xfrm := xfrm.name;
601 xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
604 FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP
605 CONTINUE WHEN xml_node !~ E'^\\s*<';
607 -- XXX much of this should be moved into oils_xpath_string...
608 curr_text := ARRAY_TO_STRING(evergreen.array_remove_item_by_value(evergreen.array_remove_item_by_value(
609 oils_xpath( '//text()',
611 REGEXP_REPLACE( -- This escapes all &s not followed by "amp;". Data ise returned from oils_xpath (above) in UTF-8, not entity encoded
612 REGEXP_REPLACE( -- This escapes embeded <s
614 $re$(>[^<]+)(<)([^>]+<)$re$,
630 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
632 IF raw_text IS NOT NULL THEN
633 raw_text := raw_text || joiner;
636 raw_text := COALESCE(raw_text,'') || curr_text;
638 -- autosuggest/metabib.browse_entry
639 IF idx.browse_field THEN
641 IF idx.browse_xpath IS NOT NULL AND idx.browse_xpath <> '' THEN
642 browse_text := oils_xpath_string( idx.browse_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
644 browse_text := curr_text;
647 IF idx.browse_sort_xpath IS NOT NULL AND
648 idx.browse_sort_xpath <> '' THEN
650 sort_value := oils_xpath_string(
651 idx.browse_sort_xpath, xml_node, joiner,
652 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
655 sort_value := browse_text;
658 output_row.field_class = idx.field_class;
659 output_row.field = idx.id;
660 output_row.source = rid;
661 output_row.value = BTRIM(REGEXP_REPLACE(browse_text, E'\\s+', ' ', 'g'));
662 output_row.sort_value :=
663 public.naco_normalize(sort_value);
665 output_row.authority := NULL;
667 IF idx.authority_xpath IS NOT NULL AND idx.authority_xpath <> '' THEN
668 authority_text := oils_xpath_string(
669 idx.authority_xpath, xml_node, joiner,
671 ARRAY[xfrm.prefix, xfrm.namespace_uri],
672 ARRAY['xlink','http://www.w3.org/1999/xlink']
676 IF authority_text ~ '^\d+$' THEN
677 authority_link := authority_text::BIGINT;
678 PERFORM * FROM authority.record_entry WHERE id = authority_link;
680 output_row.authority := authority_link;
686 output_row.browse_field = TRUE;
687 -- Returning browse rows with search_field = true for search+browse
688 -- configs allows us to retain granularity of being able to search
689 -- browse fields with "starts with" type operators (for example, for
690 -- titles of songs in music albums)
691 IF idx.search_field THEN
692 output_row.search_field = TRUE;
694 RETURN NEXT output_row;
695 output_row.browse_field = FALSE;
696 output_row.search_field = FALSE;
697 output_row.sort_value := NULL;
700 -- insert raw node text for faceting
701 IF idx.facet_field THEN
703 IF idx.facet_xpath IS NOT NULL AND idx.facet_xpath <> '' THEN
704 facet_text := oils_xpath_string( idx.facet_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
706 facet_text := curr_text;
709 output_row.field_class = idx.field_class;
710 output_row.field = -1 * idx.id;
711 output_row.source = rid;
712 output_row.value = BTRIM(REGEXP_REPLACE(facet_text, E'\\s+', ' ', 'g'));
714 output_row.facet_field = TRUE;
715 RETURN NEXT output_row;
716 output_row.facet_field = FALSE;
721 CONTINUE WHEN raw_text IS NULL OR raw_text = '';
723 -- insert combined node text for searching
724 IF idx.search_field THEN
725 output_row.field_class = idx.field_class;
726 output_row.field = idx.id;
727 output_row.source = rid;
728 output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'g'));
730 output_row.search_field = TRUE;
731 RETURN NEXT output_row;
732 output_row.search_field = FALSE;
739 $func$ LANGUAGE PLPGSQL;
741 CREATE OR REPLACE FUNCTION metabib.update_combined_index_vectors(bib_id BIGINT) RETURNS VOID AS $func$
743 DELETE FROM metabib.combined_keyword_field_entry WHERE record = bib_id;
744 INSERT INTO metabib.combined_keyword_field_entry(record, metabib_field, index_vector)
745 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
746 FROM metabib.keyword_field_entry WHERE source = bib_id GROUP BY field;
747 INSERT INTO metabib.combined_keyword_field_entry(record, metabib_field, index_vector)
748 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
749 FROM metabib.keyword_field_entry WHERE source = bib_id;
751 DELETE FROM metabib.combined_title_field_entry WHERE record = bib_id;
752 INSERT INTO metabib.combined_title_field_entry(record, metabib_field, index_vector)
753 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
754 FROM metabib.title_field_entry WHERE source = bib_id GROUP BY field;
755 INSERT INTO metabib.combined_title_field_entry(record, metabib_field, index_vector)
756 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
757 FROM metabib.title_field_entry WHERE source = bib_id;
759 DELETE FROM metabib.combined_author_field_entry WHERE record = bib_id;
760 INSERT INTO metabib.combined_author_field_entry(record, metabib_field, index_vector)
761 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
762 FROM metabib.author_field_entry WHERE source = bib_id GROUP BY field;
763 INSERT INTO metabib.combined_author_field_entry(record, metabib_field, index_vector)
764 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
765 FROM metabib.author_field_entry WHERE source = bib_id;
767 DELETE FROM metabib.combined_subject_field_entry WHERE record = bib_id;
768 INSERT INTO metabib.combined_subject_field_entry(record, metabib_field, index_vector)
769 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
770 FROM metabib.subject_field_entry WHERE source = bib_id GROUP BY field;
771 INSERT INTO metabib.combined_subject_field_entry(record, metabib_field, index_vector)
772 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
773 FROM metabib.subject_field_entry WHERE source = bib_id;
775 DELETE FROM metabib.combined_series_field_entry WHERE record = bib_id;
776 INSERT INTO metabib.combined_series_field_entry(record, metabib_field, index_vector)
777 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
778 FROM metabib.series_field_entry WHERE source = bib_id GROUP BY field;
779 INSERT INTO metabib.combined_series_field_entry(record, metabib_field, index_vector)
780 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
781 FROM metabib.series_field_entry WHERE source = bib_id;
783 DELETE FROM metabib.combined_identifier_field_entry WHERE record = bib_id;
784 INSERT INTO metabib.combined_identifier_field_entry(record, metabib_field, index_vector)
785 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
786 FROM metabib.identifier_field_entry WHERE source = bib_id GROUP BY field;
787 INSERT INTO metabib.combined_identifier_field_entry(record, metabib_field, index_vector)
788 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
789 FROM metabib.identifier_field_entry WHERE source = bib_id;
792 $func$ LANGUAGE PLPGSQL;
794 CREATE OR REPLACE FUNCTION metabib.reingest_metabib_field_entries( bib_id BIGINT, skip_facet BOOL DEFAULT FALSE, skip_browse BOOL DEFAULT FALSE, skip_search BOOL DEFAULT FALSE ) RETURNS VOID AS $func$
797 ind_data metabib.field_entry_template%ROWTYPE;
798 mbe_row metabib.browse_entry%ROWTYPE;
806 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;
807 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;
808 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;
810 PERFORM * FROM config.internal_flag WHERE name = 'ingest.assume_inserts_only' AND enabled;
812 IF NOT b_skip_search THEN
813 FOR fclass IN SELECT * FROM config.metabib_class LOOP
814 -- RAISE NOTICE 'Emptying out %', fclass.name;
815 EXECUTE $$DELETE FROM metabib.$$ || fclass.name || $$_field_entry WHERE source = $$ || bib_id;
818 IF NOT b_skip_facet THEN
819 DELETE FROM metabib.facet_entry WHERE source = bib_id;
821 IF NOT b_skip_browse THEN
822 DELETE FROM metabib.browse_entry_def_map WHERE source = bib_id;
826 FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( bib_id ) LOOP
827 IF ind_data.field < 0 THEN
828 ind_data.field = -1 * ind_data.field;
831 IF ind_data.facet_field AND NOT b_skip_facet THEN
832 INSERT INTO metabib.facet_entry (field, source, value)
833 VALUES (ind_data.field, ind_data.source, ind_data.value);
836 IF ind_data.browse_field AND NOT b_skip_browse THEN
837 -- A caveat about this SELECT: this should take care of replacing
838 -- old mbe rows when data changes, but not if normalization (by
839 -- which I mean specifically the output of
840 -- evergreen.oils_tsearch2()) changes. It may or may not be
841 -- expensive to add a comparison of index_vector to index_vector
842 -- to the WHERE clause below.
844 value_prepped := metabib.browse_normalize(ind_data.value, ind_data.field);
845 SELECT INTO mbe_row * FROM metabib.browse_entry
846 WHERE value = value_prepped AND sort_value = ind_data.sort_value;
849 mbe_id := mbe_row.id;
851 INSERT INTO metabib.browse_entry
852 ( value, sort_value ) VALUES
853 ( value_prepped, ind_data.sort_value );
855 mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS);
858 INSERT INTO metabib.browse_entry_def_map (entry, def, source, authority)
859 VALUES (mbe_id, ind_data.field, ind_data.source, ind_data.authority);
862 IF ind_data.search_field AND NOT b_skip_search THEN
863 -- Avoid inserting duplicate rows
864 EXECUTE 'SELECT 1 FROM metabib.' || ind_data.field_class ||
865 '_field_entry WHERE field = $1 AND source = $2 AND value = $3'
866 INTO mbe_id USING ind_data.field, ind_data.source, ind_data.value;
867 -- RAISE NOTICE 'Search for an already matching row returned %', mbe_id;
868 IF mbe_id IS NULL THEN
870 INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value)
872 quote_literal(ind_data.field) || $$, $$ ||
873 quote_literal(ind_data.source) || $$, $$ ||
874 quote_literal(ind_data.value) ||
881 IF NOT b_skip_search THEN
882 PERFORM metabib.update_combined_index_vectors(bib_id);
887 $func$ LANGUAGE PLPGSQL;
889 -- default to a space joiner
890 CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry ( BIGINT ) RETURNS SETOF metabib.field_entry_template AS $func$
891 SELECT * FROM biblio.extract_metabib_field_entry($1, ' ');
894 CREATE OR REPLACE FUNCTION authority.flatten_marc ( rid BIGINT ) RETURNS SETOF authority.full_rec AS $func$
896 auth authority.record_entry%ROWTYPE;
897 output authority.full_rec%ROWTYPE;
900 SELECT INTO auth * FROM authority.record_entry WHERE id = rid;
902 FOR field IN SELECT * FROM vandelay.flatten_marc( auth.marc ) LOOP
903 output.record := rid;
904 output.ind1 := field.ind1;
905 output.ind2 := field.ind2;
906 output.tag := field.tag;
907 output.subfield := field.subfield;
908 output.value := field.value;
913 $func$ LANGUAGE PLPGSQL;
915 CREATE OR REPLACE FUNCTION biblio.flatten_marc ( rid BIGINT ) RETURNS SETOF metabib.full_rec AS $func$
917 bib biblio.record_entry%ROWTYPE;
918 output metabib.full_rec%ROWTYPE;
921 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
923 FOR field IN SELECT * FROM vandelay.flatten_marc( bib.marc ) LOOP
924 output.record := rid;
925 output.ind1 := field.ind1;
926 output.ind2 := field.ind2;
927 output.tag := field.tag;
928 output.subfield := field.subfield;
929 output.value := field.value;
934 $func$ LANGUAGE PLPGSQL;
936 CREATE OR REPLACE FUNCTION vandelay.marc21_record_type( marc TEXT ) RETURNS config.marc21_rec_type_map AS $func$
943 retval config.marc21_rec_type_map%ROWTYPE;
945 ldr := oils_xpath_string( '//*[local-name()="leader"]', marc );
947 IF ldr IS NULL OR ldr = '' THEN
948 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE code = 'BKS';
952 SELECT * INTO tval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'Type' LIMIT 1; -- They're all the same
953 SELECT * INTO bval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'BLvl' LIMIT 1; -- They're all the same
956 tval := SUBSTRING( ldr, tval_rec.start_pos + 1, tval_rec.length );
957 bval := SUBSTRING( ldr, bval_rec.start_pos + 1, bval_rec.length );
959 -- RAISE NOTICE 'type %, blvl %, ldr %', tval, bval, ldr;
961 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE type_val LIKE '%' || tval || '%' AND blvl_val LIKE '%' || bval || '%';
964 IF retval.code IS NULL THEN
965 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE code = 'BKS';
970 $func$ LANGUAGE PLPGSQL;
972 CREATE OR REPLACE FUNCTION biblio.marc21_record_type( rid BIGINT ) RETURNS config.marc21_rec_type_map AS $func$
973 SELECT * FROM vandelay.marc21_record_type( (SELECT marc FROM biblio.record_entry WHERE id = $1) );
976 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field_list( marc TEXT, ff TEXT ) RETURNS TEXT[] AS $func$
982 collection TEXT[] := '{}'::TEXT[];
984 rtype := (vandelay.marc21_record_type( marc )).code;
985 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = ff AND rec_type = rtype ORDER BY tag DESC LOOP
986 IF ff_pos.tag = 'ldr' THEN
987 val := oils_xpath_string('//*[local-name()="leader"]', marc);
988 IF val IS NOT NULL THEN
989 val := SUBSTRING( val, ff_pos.start_pos + 1, ff_pos.length );
990 collection := collection || val;
993 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
994 val := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length );
995 collection := collection || val;
998 val := REPEAT( ff_pos.default_val, ff_pos.length );
999 collection := collection || val;
1004 $func$ LANGUAGE PLPGSQL;
1006 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field( marc TEXT, ff TEXT ) RETURNS TEXT AS $func$
1013 rtype := (vandelay.marc21_record_type( marc )).code;
1014 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = ff AND rec_type = rtype ORDER BY tag DESC LOOP
1015 IF ff_pos.tag = 'ldr' THEN
1016 val := oils_xpath_string('//*[local-name()="leader"]', marc);
1017 IF val IS NOT NULL THEN
1018 val := SUBSTRING( val, ff_pos.start_pos + 1, ff_pos.length );
1022 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
1023 val := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length );
1027 val := REPEAT( ff_pos.default_val, ff_pos.length );
1033 $func$ LANGUAGE PLPGSQL;
1035 CREATE OR REPLACE FUNCTION biblio.marc21_extract_fixed_field_list( rid BIGINT, ff TEXT ) RETURNS TEXT[] AS $func$
1036 SELECT * FROM vandelay.marc21_extract_fixed_field_list( (SELECT marc FROM biblio.record_entry WHERE id = $1), $2 );
1037 $func$ LANGUAGE SQL;
1039 CREATE OR REPLACE FUNCTION biblio.marc21_extract_fixed_field( rid BIGINT, ff TEXT ) RETURNS TEXT AS $func$
1040 SELECT * FROM vandelay.marc21_extract_fixed_field( (SELECT marc FROM biblio.record_entry WHERE id = $1), $2 );
1041 $func$ LANGUAGE SQL;
1043 -- CREATE TYPE biblio.record_ff_map AS (record BIGINT, ff_name TEXT, ff_value TEXT);
1044 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_all_fixed_fields( marc TEXT ) RETURNS SETOF biblio.record_ff_map AS $func$
1049 output biblio.record_ff_map%ROWTYPE;
1051 rtype := (vandelay.marc21_record_type( marc )).code;
1053 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE rec_type = rtype ORDER BY tag DESC LOOP
1054 output.ff_name := ff_pos.fixed_field;
1055 output.ff_value := NULL;
1057 IF ff_pos.tag = 'ldr' THEN
1058 output.ff_value := oils_xpath_string('//*[local-name()="leader"]', marc);
1059 IF output.ff_value IS NOT NULL THEN
1060 output.ff_value := SUBSTRING( output.ff_value, ff_pos.start_pos + 1, ff_pos.length );
1062 output.ff_value := NULL;
1065 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
1066 output.ff_value := SUBSTRING( tag_data, ff_pos.start_pos + 1, ff_pos.length );
1067 IF output.ff_value IS NULL THEN output.ff_value := REPEAT( ff_pos.default_val, ff_pos.length ); END IF;
1069 output.ff_value := NULL;
1077 $func$ LANGUAGE PLPGSQL;
1079 CREATE OR REPLACE FUNCTION biblio.marc21_extract_all_fixed_fields( rid BIGINT ) RETURNS SETOF biblio.record_ff_map AS $func$
1080 SELECT $1 AS record, ff_name, ff_value FROM vandelay.marc21_extract_all_fixed_fields( (SELECT marc FROM biblio.record_entry WHERE id = $1) );
1081 $func$ LANGUAGE SQL;
1083 CREATE OR REPLACE FUNCTION biblio.marc21_physical_characteristics( rid BIGINT ) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$
1084 SELECT id, $1 AS record, ptype, subfield, value FROM vandelay.marc21_physical_characteristics( (SELECT marc FROM biblio.record_entry WHERE id = $1) );
1085 $func$ LANGUAGE SQL;
1087 CREATE OR REPLACE FUNCTION biblio.extract_quality ( marc TEXT, best_lang TEXT, best_type TEXT ) RETURNS INT AS $func$
1100 IF marc IS NULL OR marc = '' THEN
1104 -- First, the count of tags
1105 qual := ARRAY_UPPER(oils_xpath('*[local-name()="datafield"]', marc), 1);
1107 -- now go through a bunch of pain to get the record type
1108 IF best_type IS NOT NULL THEN
1109 ldr := (oils_xpath('//*[local-name()="leader"]/text()', marc))[1];
1111 IF ldr IS NOT NULL THEN
1112 SELECT * INTO tval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'Type' LIMIT 1; -- They're all the same
1113 SELECT * INTO bval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'BLvl' LIMIT 1; -- They're all the same
1116 tval := SUBSTRING( ldr, tval_rec.start_pos + 1, tval_rec.length );
1117 bval := SUBSTRING( ldr, bval_rec.start_pos + 1, bval_rec.length );
1119 -- RAISE NOTICE 'type %, blvl %, ldr %', tval, bval, ldr;
1121 SELECT * INTO type_map FROM config.marc21_rec_type_map WHERE type_val LIKE '%' || tval || '%' AND blvl_val LIKE '%' || bval || '%';
1123 IF type_map.code IS NOT NULL THEN
1124 IF best_type = type_map.code THEN
1125 qual := qual + qual / 2;
1128 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
1129 ff_tag_data := SUBSTRING((oils_xpath('//*[@tag="' || ff_pos.tag || '"]/text()',marc))[1], ff_pos.start_pos + 1, ff_pos.length);
1130 IF ff_tag_data = best_lang THEN
1138 -- Now look for some quality metrics
1140 IF ARRAY_UPPER(oils_xpath('//*[@tag="040"]/*[@code="a" and contains(.,"DLC")]', marc), 1) = 1 THEN
1145 IF (oils_xpath('//*[@tag="003"]/text()', marc))[1] ~* E'oclo?c' THEN
1152 $func$ LANGUAGE PLPGSQL;
1154 CREATE OR REPLACE FUNCTION biblio.extract_fingerprint ( marc text ) RETURNS TEXT AS $func$
1156 idx config.biblio_fingerprint%ROWTYPE;
1157 xfrm config.xml_transform%ROWTYPE;
1159 transformed_xml TEXT;
1161 xml_node_list TEXT[];
1163 output_text TEXT := '';
1166 IF marc IS NULL OR marc = '' THEN
1170 -- Loop over the indexing entries
1171 FOR idx IN SELECT * FROM config.biblio_fingerprint ORDER BY format, id LOOP
1173 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
1175 -- See if we can skip the XSLT ... it's expensive
1176 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
1177 -- Can't skip the transform
1178 IF xfrm.xslt <> '---' THEN
1179 transformed_xml := oils_xslt_process(marc,xfrm.xslt);
1181 transformed_xml := marc;
1184 prev_xfrm := xfrm.name;
1187 raw_text := COALESCE(
1195 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
1204 raw_text := REGEXP_REPLACE(raw_text, E'\\[.+?\\]', E'');
1205 raw_text := REGEXP_REPLACE(raw_text, E'\\mthe\\M|\\man?d?d\\M', E'', 'g'); -- arg! the pain!
1207 IF idx.first_word IS TRUE THEN
1208 raw_text := REGEXP_REPLACE(raw_text, E'^(\\w+).*?$', E'\\1');
1211 output_text := output_text || REGEXP_REPLACE(raw_text, E'\\s+', '', 'g');
1218 $func$ LANGUAGE PLPGSQL;
1220 -- BEFORE UPDATE OR INSERT trigger for biblio.record_entry
1221 CREATE OR REPLACE FUNCTION biblio.fingerprint_trigger () RETURNS TRIGGER AS $func$
1224 -- For TG_ARGV, first param is language (like 'eng'), second is record type (like 'BKS')
1226 IF NEW.deleted IS TRUE THEN -- we don't much care, then, do we?
1230 NEW.fingerprint := biblio.extract_fingerprint(NEW.marc);
1231 NEW.quality := biblio.extract_quality(NEW.marc, TG_ARGV[0], TG_ARGV[1]);
1236 $func$ LANGUAGE PLPGSQL;
1238 CREATE OR REPLACE FUNCTION metabib.reingest_metabib_full_rec( bib_id BIGINT ) RETURNS VOID AS $func$
1240 PERFORM * FROM config.internal_flag WHERE name = 'ingest.assume_inserts_only' AND enabled;
1242 DELETE FROM metabib.real_full_rec WHERE record = bib_id;
1244 INSERT INTO metabib.real_full_rec (record, tag, ind1, ind2, subfield, value)
1245 SELECT record, tag, ind1, ind2, subfield, value FROM biblio.flatten_marc( bib_id );
1249 $func$ LANGUAGE PLPGSQL;
1251 CREATE OR REPLACE FUNCTION biblio.extract_located_uris( bib_id BIGINT, marcxml TEXT, editor_id INT ) RETURNS VOID AS $func$
1258 uri_owner_list TEXT[];
1266 -- Clear any URI mappings and call numbers for this bib.
1267 -- This leads to acn / auricnm inflation, but also enables
1268 -- old acn/auricnm's to go away and for bibs to be deleted.
1269 FOR uri_cn_id IN SELECT id FROM asset.call_number WHERE record = bib_id AND label = '##URI##' AND NOT deleted LOOP
1270 DELETE FROM asset.uri_call_number_map WHERE call_number = uri_cn_id;
1271 DELETE FROM asset.call_number WHERE id = uri_cn_id;
1274 uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',marcxml);
1275 IF ARRAY_UPPER(uris,1) > 0 THEN
1276 FOR i IN 1 .. ARRAY_UPPER(uris, 1) LOOP
1277 -- First we pull info out of the 856
1280 uri_href := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1];
1281 uri_label := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()',uri_xml))[1];
1282 uri_use := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()',uri_xml))[1];
1284 IF uri_label IS NULL THEN
1285 uri_label := uri_href;
1287 CONTINUE WHEN uri_href IS NULL;
1289 -- Get the distinct list of libraries wanting to use
1291 DISTINCT REGEXP_REPLACE(
1293 $re$^.*?\((\w+)\).*$$re$,
1296 ) INTO uri_owner_list
1299 '//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()',
1304 IF ARRAY_UPPER(uri_owner_list,1) > 0 THEN
1306 -- look for a matching uri
1307 IF uri_use IS NULL THEN
1308 SELECT id INTO uri_id
1310 WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active
1311 ORDER BY id LIMIT 1;
1312 IF NOT FOUND THEN -- create one
1313 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
1314 SELECT id INTO uri_id
1316 WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active;
1319 SELECT id INTO uri_id
1321 WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active
1322 ORDER BY id LIMIT 1;
1323 IF NOT FOUND THEN -- create one
1324 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
1325 SELECT id INTO uri_id
1327 WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
1331 FOR j IN 1 .. ARRAY_UPPER(uri_owner_list, 1) LOOP
1332 uri_owner := uri_owner_list[j];
1334 SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = uri_owner;
1335 CONTINUE WHEN NOT FOUND;
1337 -- we need a call number to link through
1338 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;
1340 INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label)
1341 VALUES (uri_owner_id, bib_id, 'now', 'now', editor_id, editor_id, '##URI##');
1342 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;
1345 -- now, link them if they're not already
1346 SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id;
1348 INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id);
1360 $func$ LANGUAGE PLPGSQL;
1362 CREATE OR REPLACE FUNCTION metabib.remap_metarecord_for_bib( bib_id BIGINT, fp TEXT ) RETURNS BIGINT AS $func$
1366 tmp_mr metabib.metarecord%ROWTYPE;
1367 deleted_mrs BIGINT[];
1370 DELETE FROM metabib.metarecord_source_map WHERE source = bib_id; -- Rid ourselves of the search-estimate-killing linkage
1372 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
1374 IF old_mr IS NULL AND fp = tmp_mr.fingerprint THEN -- Find the first fingerprint-matching
1375 old_mr := tmp_mr.id;
1377 SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = tmp_mr.id;
1378 IF source_count = 0 THEN -- No other records
1379 deleted_mrs := ARRAY_APPEND(deleted_mrs, tmp_mr.id);
1380 DELETE FROM metabib.metarecord WHERE id = tmp_mr.id;
1386 IF old_mr IS NULL THEN -- we found no suitable, preexisting MR based on old source maps
1387 SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp; -- is there one for our current fingerprint?
1388 IF old_mr IS NULL THEN -- nope, create one and grab its id
1389 INSERT INTO metabib.metarecord ( fingerprint, master_record ) VALUES ( fp, bib_id );
1390 SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp;
1391 ELSE -- indeed there is. update it with a null cache and recalcualated master record
1392 UPDATE metabib.metarecord
1394 master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp ORDER BY quality DESC LIMIT 1)
1397 ELSE -- there was one we already attached to, update its mods cache and master_record
1398 UPDATE metabib.metarecord
1400 master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp ORDER BY quality DESC LIMIT 1)
1404 INSERT INTO metabib.metarecord_source_map (metarecord, source) VALUES (old_mr, bib_id); -- new source mapping
1406 IF ARRAY_UPPER(deleted_mrs,1) > 0 THEN
1407 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
1413 $func$ LANGUAGE PLPGSQL;
1415 CREATE OR REPLACE FUNCTION biblio.map_authority_linking (bibid BIGINT, marc TEXT) RETURNS BIGINT AS $func$
1416 DELETE FROM authority.bib_linking WHERE bib = $1;
1417 INSERT INTO authority.bib_linking (bib, authority)
1420 FROM ( SELECT DISTINCT $1 AS bib,
1421 BTRIM(remove_paren_substring(txt))::BIGINT AS authority
1422 FROM unnest(oils_xpath('//*[@code="0"]/text()',$2)) x(txt)
1423 WHERE BTRIM(remove_paren_substring(txt)) ~ $re$^\d+$$re$
1424 ) y JOIN authority.record_entry r ON r.id = y.authority;
1426 $func$ LANGUAGE SQL;
1428 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$
1430 transformed_xml TEXT;
1431 rmarc TEXT := prmarc;
1435 xfrm config.xml_transform%ROWTYPE;
1436 attr_vector INT[] := '{}'::INT[];
1437 attr_vector_tmp INT[];
1438 attr_list TEXT[] := pattr_list;
1440 norm_attr_value TEXT[];
1442 attr_def config.record_attr_definition%ROWTYPE;
1443 ccvm_row config.coded_value_map%ROWTYPE;
1446 IF attr_list IS NULL OR rdeleted THEN -- need to do the full dance on INSERT or undelete
1447 SELECT ARRAY_AGG(name) INTO attr_list FROM config.record_attr_definition;
1450 IF rmarc IS NULL THEN
1451 SELECT marc INTO rmarc FROM biblio.record_entry WHERE id = rid;
1454 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE NOT composite AND name = ANY( attr_list ) ORDER BY format LOOP
1456 attr_value := '{}'::TEXT[];
1457 norm_attr_value := '{}'::TEXT[];
1458 attr_vector_tmp := '{}'::INT[];
1460 SELECT * INTO ccvm_row FROM config.coded_value_map c WHERE c.ctype = attr_def.name LIMIT 1;
1462 -- tag+sf attrs only support SVF
1463 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
1464 SELECT ARRAY[ARRAY_TO_STRING(ARRAY_AGG(value), COALESCE(attr_def.joiner,' '))] INTO attr_value
1465 FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x
1467 AND tag LIKE attr_def.tag
1469 WHEN attr_def.sf_list IS NOT NULL
1470 THEN POSITION(subfield IN attr_def.sf_list) > 0
1477 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
1478 attr_value := vandelay.marc21_extract_fixed_field_list(rmarc, attr_def.fixed_field);
1480 IF NOT attr_def.multi THEN
1481 attr_value := ARRAY[attr_value[1]];
1484 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
1486 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
1488 -- See if we can skip the XSLT ... it's expensive
1489 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
1490 -- Can't skip the transform
1491 IF xfrm.xslt <> '---' THEN
1492 transformed_xml := oils_xslt_process(rmarc,xfrm.xslt);
1494 transformed_xml := rmarc;
1497 prev_xfrm := xfrm.name;
1500 IF xfrm.name IS NULL THEN
1501 -- just grab the marcxml (empty) transform
1502 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
1503 prev_xfrm := xfrm.name;
1506 FOR tmp_xml IN SELECT XPATH(attr_def.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]) LOOP
1507 tmp_val := oils_xpath_string(
1510 COALESCE(attr_def.joiner,' '),
1511 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
1513 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
1514 attr_value := attr_value || tmp_val;
1515 EXIT WHEN NOT attr_def.multi;
1519 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
1520 SELECT ARRAY_AGG(m.value) INTO attr_value
1521 FROM vandelay.marc21_physical_characteristics(rmarc) v
1522 LEFT JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
1523 WHERE v.subfield = attr_def.phys_char_sf AND (m.value IS NOT NULL AND BTRIM(m.value) <> '')
1524 AND ( ccvm_row.id IS NULL OR ( ccvm_row.id IS NOT NULL AND v.id IS NOT NULL) );
1526 IF NOT attr_def.multi THEN
1527 attr_value := ARRAY[attr_value[1]];
1532 -- apply index normalizers to attr_value
1533 FOR tmp_val IN SELECT value FROM UNNEST(attr_value) x(value) LOOP
1535 SELECT n.func AS func,
1536 n.param_count AS param_count,
1538 FROM config.index_normalizer n
1539 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
1540 WHERE attr = attr_def.name
1542 EXECUTE 'SELECT ' || normalizer.func || '(' ||
1543 COALESCE( quote_literal( tmp_val ), 'NULL' ) ||
1545 WHEN normalizer.param_count > 0
1546 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
1552 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
1553 norm_attr_value := norm_attr_value || tmp_val;
1557 IF attr_def.filter THEN
1558 -- Create unknown uncontrolled values and find the IDs of the values
1559 IF ccvm_row.id IS NULL THEN
1560 FOR tmp_val IN SELECT value FROM UNNEST(norm_attr_value) x(value) LOOP
1561 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
1562 BEGIN -- use subtransaction to isolate unique constraint violations
1563 INSERT INTO metabib.uncontrolled_record_attr_value ( attr, value ) VALUES ( attr_def.name, tmp_val );
1564 EXCEPTION WHEN unique_violation THEN END;
1568 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 );
1570 SELECT ARRAY_AGG(id) INTO attr_vector_tmp FROM config.coded_value_map WHERE ctype = attr_def.name AND code = ANY( norm_attr_value );
1573 -- Add the new value to the vector
1574 attr_vector := attr_vector || attr_vector_tmp;
1577 IF attr_def.sorter AND norm_attr_value[1] IS NOT NULL THEN
1578 DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
1579 INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, norm_attr_value[1]);
1584 /* We may need to rewrite the vlist to contain
1585 the intersection of new values for requested
1586 attrs and old values for ignored attrs. To
1587 do this, we take the old attr vlist and
1588 subtract any values that are valid for the
1589 requested attrs, and then add back the new
1590 set of attr values. */
1592 IF ARRAY_LENGTH(pattr_list, 1) > 0 THEN
1593 SELECT vlist INTO attr_vector_tmp FROM metabib.record_attr_vector_list WHERE source = rid;
1594 SELECT attr_vector_tmp - ARRAY_AGG(id::INT) INTO attr_vector_tmp FROM metabib.full_attr_id_map WHERE attr = ANY (pattr_list);
1595 attr_vector := attr_vector || attr_vector_tmp;
1598 -- On to composite attributes, now that the record attrs have been pulled. Processed in name order, so later composite
1599 -- attributes can depend on earlier ones.
1600 PERFORM metabib.compile_composite_attr_cache_init();
1601 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE composite AND name = ANY( attr_list ) ORDER BY name LOOP
1603 FOR ccvm_row IN SELECT * FROM config.coded_value_map c WHERE c.ctype = attr_def.name ORDER BY value LOOP
1605 tmp_val := metabib.compile_composite_attr( ccvm_row.id );
1606 CONTINUE WHEN tmp_val IS NULL OR tmp_val = ''; -- nothing to do
1608 IF attr_def.filter THEN
1609 IF attr_vector @@ tmp_val::query_int THEN
1610 attr_vector = attr_vector + intset(ccvm_row.id);
1611 EXIT WHEN NOT attr_def.multi;
1615 IF attr_def.sorter THEN
1616 IF attr_vector @@ tmp_val THEN
1617 DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
1618 INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, ccvm_row.code);
1626 IF ARRAY_LENGTH(attr_vector, 1) > 0 THEN
1627 IF rdeleted THEN -- initial insert OR revivication
1628 DELETE FROM metabib.record_attr_vector_list WHERE source = rid;
1629 INSERT INTO metabib.record_attr_vector_list (source, vlist) VALUES (rid, attr_vector);
1631 UPDATE metabib.record_attr_vector_list SET vlist = attr_vector WHERE source = rid;
1637 $func$ LANGUAGE PLPGSQL;
1640 -- AFTER UPDATE OR INSERT trigger for biblio.record_entry
1641 CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
1644 IF NEW.deleted THEN -- If this bib is deleted
1645 PERFORM * FROM config.internal_flag WHERE
1646 name = 'ingest.metarecord_mapping.preserve_on_delete' AND enabled;
1648 -- One needs to keep these around to support searches
1649 -- with the #deleted modifier, so one should turn on the named
1650 -- internal flag for that functionality.
1651 DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id;
1652 DELETE FROM metabib.record_attr_vector_list WHERE source = NEW.id;
1655 DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible
1656 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = NEW.id; -- Separate any multi-homed items
1657 DELETE FROM metabib.browse_entry_def_map WHERE source = NEW.id; -- Don't auto-suggest deleted bibs
1658 RETURN NEW; -- and we're done
1661 IF TG_OP = 'UPDATE' THEN -- re-ingest?
1662 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
1664 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
1669 -- Record authority linking
1670 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking' AND enabled;
1672 PERFORM biblio.map_authority_linking( NEW.id, NEW.marc );
1675 -- Flatten and insert the mfr data
1676 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled;
1678 PERFORM metabib.reingest_metabib_full_rec(NEW.id);
1680 -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields
1681 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled;
1683 PERFORM metabib.reingest_record_attributes(NEW.id, NULL, NEW.marc, TG_OP = 'INSERT' OR OLD.deleted);
1687 -- Gather and insert the field entry data
1688 PERFORM metabib.reingest_metabib_field_entries(NEW.id);
1690 -- Located URI magic
1691 IF TG_OP = 'INSERT' THEN
1692 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
1694 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
1697 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
1699 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
1703 -- (re)map metarecord-bib linking
1704 IF TG_OP = 'INSERT' THEN -- if not deleted and performing an insert, check for the flag
1705 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled;
1707 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
1709 ELSE -- we're doing an update, and we're not deleted, remap
1710 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_update' AND enabled;
1712 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
1718 $func$ LANGUAGE PLPGSQL;
1720 CREATE OR REPLACE FUNCTION metabib.browse_normalize(facet_text TEXT, mapped_field INT) RETURNS TEXT AS $$
1726 SELECT n.func AS func,
1727 n.param_count AS param_count,
1729 FROM config.index_normalizer n
1730 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
1731 WHERE m.field = mapped_field AND m.pos < 0
1734 EXECUTE 'SELECT ' || normalizer.func || '(' ||
1735 quote_literal( facet_text ) ||
1737 WHEN normalizer.param_count > 0
1738 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
1741 ')' INTO facet_text;
1748 $$ LANGUAGE PLPGSQL;
1751 -- This mimics a specific part of QueryParser, turning the first part of a
1752 -- classed search (search_class) into a set of classes and possibly fields.
1753 -- search_class might look like "author" or "title|proper" or "ti|uniform"
1754 -- or "au" or "au|corporate|personal" or anything like that, where the first
1755 -- element of the list you get by separating on the "|" character is either
1756 -- a registered class (config.metabib_class) or an alias
1757 -- (config.metabib_search_alias), and the rest of any such elements are
1758 -- fields (config.metabib_field).
1760 FUNCTION metabib.search_class_to_registered_components(search_class TEXT)
1761 RETURNS SETOF RECORD AS $func$
1763 search_parts TEXT[];
1765 search_part_count INTEGER;
1767 registered_class config.metabib_class%ROWTYPE;
1768 registered_alias config.metabib_search_alias%ROWTYPE;
1769 registered_field config.metabib_field%ROWTYPE;
1771 search_parts := REGEXP_SPLIT_TO_ARRAY(search_class, E'\\|');
1773 search_part_count := ARRAY_LENGTH(search_parts, 1);
1774 IF search_part_count = 0 THEN
1777 SELECT INTO registered_class
1778 * FROM config.metabib_class WHERE name = search_parts[1];
1780 IF search_part_count < 2 THEN -- all fields
1781 rec := (registered_class.name, NULL::INTEGER);
1785 FOR field_name IN SELECT *
1786 FROM UNNEST(search_parts[2:search_part_count]) LOOP
1787 SELECT INTO registered_field
1788 * FROM config.metabib_field
1789 WHERE name = field_name AND
1790 field_class = registered_class.name;
1792 rec := (registered_class.name, registered_field.id);
1797 -- maybe we have an alias?
1798 SELECT INTO registered_alias
1799 * FROM config.metabib_search_alias WHERE alias=search_parts[1];
1803 IF search_part_count < 2 THEN -- return w/e the alias says
1805 registered_alias.field_class, registered_alias.field
1810 FOR field_name IN SELECT *
1811 FROM UNNEST(search_parts[2:search_part_count]) LOOP
1812 SELECT INTO registered_field
1813 * FROM config.metabib_field
1814 WHERE name = field_name AND
1815 field_class = registered_alias.field_class;
1818 registered_alias.field_class,
1829 $func$ LANGUAGE PLPGSQL ROWS 1;
1832 -- Given a string such as a user might type into a search box, prepare
1833 -- two changed variants for TO_TSQUERY(). See
1834 -- http://www.postgresql.org/docs/9.0/static/textsearch-controls.html
1835 -- The first variant is normalized to match indexed documents regardless
1836 -- of diacritics. The second variant keeps its diacritics for proper
1837 -- highlighting via TS_HEADLINE().
1839 FUNCTION metabib.autosuggest_prepare_tsquery(orig TEXT) RETURNS TEXT[] AS
1842 orig_ended_in_space BOOLEAN;
1847 orig_ended_in_space := orig ~ E'\\s$';
1849 orig := ARRAY_TO_STRING(
1850 evergreen.regexp_split_to_array(orig, E'\\W+'), ' '
1853 normalized := public.naco_normalize(orig); -- also trim()s
1854 plain := trim(orig);
1856 IF NOT orig_ended_in_space THEN
1857 plain := plain || ':*';
1858 normalized := normalized || ':*';
1861 plain := ARRAY_TO_STRING(
1862 evergreen.regexp_split_to_array(plain, E'\\s+'), ' & '
1864 normalized := ARRAY_TO_STRING(
1865 evergreen.regexp_split_to_array(normalized, E'\\s+'), ' & '
1868 RETURN ARRAY[normalized, plain];
1870 $$ LANGUAGE PLPGSQL;
1874 FUNCTION metabib.suggest_browse_entries(
1875 raw_query_text TEXT, -- actually typed by humans at the UI level
1876 search_class TEXT, -- 'alias' or 'class' or 'class|field..', etc
1877 headline_opts TEXT, -- markup options for ts_headline()
1878 visibility_org INTEGER,-- null if you don't want opac visibility test
1879 query_limit INTEGER,-- use in LIMIT clause of interal query
1880 normalization INTEGER -- argument to TS_RANK_CD()
1882 value TEXT, -- plain
1884 buoyant_and_class_match BOOL,
1886 field_weight INTEGER,
1889 match TEXT -- marked up
1892 prepared_query_texts TEXT[];
1894 plain_query TSQUERY;
1895 opac_visibility_join TEXT;
1896 search_class_join TEXT;
1899 prepared_query_texts := metabib.autosuggest_prepare_tsquery(raw_query_text);
1901 query := TO_TSQUERY('keyword', prepared_query_texts[1]);
1902 plain_query := TO_TSQUERY('keyword', prepared_query_texts[2]);
1904 visibility_org := NULLIF(visibility_org,-1);
1905 IF visibility_org IS NOT NULL THEN
1906 opac_visibility_join := '
1907 JOIN asset.opac_visible_copies aovc ON (
1908 aovc.record = x.source AND
1909 aovc.circ_lib IN (SELECT id FROM actor.org_unit_descendants($4))
1912 opac_visibility_join := '';
1915 -- The following determines whether we only provide suggestsons matching
1916 -- the user's selected search_class, or whether we show other suggestions
1917 -- too. The reason for MIN() is that for search_classes like
1918 -- 'title|proper|uniform' you would otherwise get multiple rows. The
1919 -- implication is that if title as a class doesn't have restrict,
1920 -- nor does the proper field, but the uniform field does, you're going
1921 -- to get 'false' for your overall evaluation of 'should we restrict?'
1922 -- To invert that, change from MIN() to MAX().
1926 MIN(cmc.restrict::INT) AS restrict_class,
1927 MIN(cmf.restrict::INT) AS restrict_field
1928 FROM metabib.search_class_to_registered_components(search_class)
1929 AS _registered (field_class TEXT, field INT)
1931 config.metabib_class cmc ON (cmc.name = _registered.field_class)
1933 config.metabib_field cmf ON (cmf.id = _registered.field);
1935 -- evaluate 'should we restrict?'
1936 IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN
1937 search_class_join := '
1939 metabib.search_class_to_registered_components($2)
1940 AS _registered (field_class TEXT, field INT) ON (
1941 (_registered.field IS NULL AND
1942 _registered.field_class = cmf.field_class) OR
1943 (_registered.field = cmf.id)
1947 search_class_join := '
1949 metabib.search_class_to_registered_components($2)
1950 AS _registered (field_class TEXT, field INT) ON (
1951 _registered.field_class = cmc.name
1956 RETURN QUERY EXECUTE '
1965 TS_HEADLINE(value, $7, $3)
1966 FROM (SELECT DISTINCT
1969 cmc.buoyant AND _registered.field_class IS NOT NULL AS push,
1970 _registered.field = cmf.id AS restrict,
1972 TS_RANK_CD(mbe.index_vector, $1, $6),
1975 FROM metabib.browse_entry_def_map mbedm
1976 JOIN (SELECT * FROM metabib.browse_entry WHERE index_vector @@ $1 LIMIT 10000) mbe ON (mbe.id = mbedm.entry)
1977 JOIN config.metabib_field cmf ON (cmf.id = mbedm.def)
1978 JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name)
1979 ' || search_class_join || '
1980 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
1982 ' || opac_visibility_join || '
1983 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
1985 ' -- sic, repeat the order by clause in the outer select too
1987 query, search_class, headline_opts,
1988 visibility_org, query_limit, normalization, plain_query
1992 -- buoyant AND chosen class = match class
1993 -- chosen field = match field
2000 $func$ LANGUAGE PLPGSQL;
2002 CREATE OR REPLACE FUNCTION public.oils_tsearch2 () RETURNS TRIGGER AS $$
2006 temp_vector TEXT := '';
2012 NEW.index_vector = ''::tsvector;
2014 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
2016 SELECT n.func AS func,
2017 n.param_count AS param_count,
2019 FROM config.index_normalizer n
2020 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
2021 WHERE field = NEW.field AND m.pos < 0
2023 EXECUTE 'SELECT ' || normalizer.func || '(' ||
2024 quote_literal( value ) ||
2026 WHEN normalizer.param_count > 0
2027 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
2037 SELECT n.func AS func,
2038 n.param_count AS param_count,
2040 FROM config.index_normalizer n
2041 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
2042 WHERE field = NEW.field AND m.pos >= 0
2044 EXECUTE 'SELECT ' || normalizer.func || '(' ||
2045 quote_literal( value ) ||
2047 WHEN normalizer.param_count > 0
2048 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
2056 IF TG_TABLE_NAME::TEXT ~ 'browse_entry$' THEN
2057 value := ARRAY_TO_STRING(
2058 evergreen.regexp_split_to_array(value, E'\\W+'), ' '
2060 value := public.search_normalize(value);
2061 NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);
2062 ELSIF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
2064 SELECT ts_config, index_weight
2065 FROM config.metabib_class_ts_map
2066 WHERE field_class = TG_ARGV[0]
2067 AND index_lang IS NULL OR EXISTS (SELECT 1 FROM metabib.record_attr WHERE id = NEW.source AND index_lang IN(attrs->'item_lang',attrs->'language'))
2068 AND always OR NOT EXISTS (SELECT 1 FROM config.metabib_field_ts_map WHERE metabib_field = NEW.field)
2070 SELECT ts_config, index_weight
2071 FROM config.metabib_field_ts_map
2072 WHERE metabib_field = NEW.field
2073 AND index_lang IS NULL OR EXISTS (SELECT 1 FROM metabib.record_attr WHERE id = NEW.source AND index_lang IN(attrs->'item_lang',attrs->'language'))
2074 ORDER BY index_weight ASC
2076 IF cur_weight IS NOT NULL AND cur_weight != ts_rec.index_weight THEN
2077 NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
2080 cur_weight = ts_rec.index_weight;
2081 SELECT INTO temp_vector temp_vector || ' ' || to_tsvector(ts_rec.ts_config::regconfig, value)::TEXT;
2083 NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
2085 NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);
2090 $$ LANGUAGE PLPGSQL;
2093 CREATE TYPE metabib.flat_browse_entry_appearance AS (
2094 browse_entry BIGINT,
2099 sources INT, -- visible ones, that is
2100 asources INT, -- visible ones, that is
2101 row_number INT, -- internal use, sort of
2102 accurate BOOL, -- Count in sources field is accurate? Not
2103 -- if we had more than a browse superpage
2104 -- of records to look at.
2105 aaccurate BOOL, -- See previous comment...
2110 CREATE OR REPLACE FUNCTION metabib.browse_bib_pivot(
2113 ) RETURNS BIGINT AS $p$
2115 FROM metabib.browse_entry mbe
2116 JOIN metabib.browse_entry_def_map mbedm ON (
2117 mbedm.entry = mbe.id
2118 AND mbedm.def = ANY($1)
2120 WHERE mbe.sort_value >= public.naco_normalize($2)
2121 ORDER BY mbe.sort_value, mbe.value LIMIT 1;
2122 $p$ LANGUAGE SQL STABLE;
2124 CREATE OR REPLACE FUNCTION metabib.browse_authority_pivot(
2127 ) RETURNS BIGINT AS $p$
2129 FROM metabib.browse_entry mbe
2130 JOIN metabib.browse_entry_simple_heading_map mbeshm ON ( mbeshm.entry = mbe.id )
2131 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
2132 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
2133 ash.atag = map.authority_field
2134 AND map.metabib_field = ANY($1)
2136 WHERE mbe.sort_value >= public.naco_normalize($2)
2137 ORDER BY mbe.sort_value, mbe.value LIMIT 1;
2138 $p$ LANGUAGE SQL STABLE;
2140 CREATE OR REPLACE FUNCTION metabib.browse_authority_refs_pivot(
2143 ) RETURNS BIGINT AS $p$
2145 FROM metabib.browse_entry mbe
2146 JOIN metabib.browse_entry_simple_heading_map mbeshm ON ( mbeshm.entry = mbe.id )
2147 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
2148 JOIN authority.control_set_auth_field_metabib_field_map_refs_only map ON (
2149 ash.atag = map.authority_field
2150 AND map.metabib_field = ANY($1)
2152 WHERE mbe.sort_value >= public.naco_normalize($2)
2153 ORDER BY mbe.sort_value, mbe.value LIMIT 1;
2154 $p$ LANGUAGE SQL STABLE;
2156 CREATE OR REPLACE FUNCTION metabib.browse_pivot(
2159 ) RETURNS BIGINT AS $p$
2160 SELECT id FROM metabib.browse_entry
2162 metabib.browse_bib_pivot($1, $2),
2163 metabib.browse_authority_refs_pivot($1,$2) -- only look in 4xx, 5xx, 7xx of authority
2165 ORDER BY sort_value, value LIMIT 1;
2166 $p$ LANGUAGE SQL STABLE;
2169 CREATE OR REPLACE FUNCTION metabib.staged_browse(
2173 context_locations INT[],
2175 browse_superpage_size INT,
2176 count_up_from_zero BOOL, -- if false, count down from -1
2179 ) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$
2187 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
2188 results_skipped INT := 0;
2189 row_counter INT := 0;
2194 all_records BIGINT[];
2195 all_brecords BIGINT[];
2196 all_arecords BIGINT[];
2197 superpage_of_records BIGINT[];
2200 IF count_up_from_zero THEN
2206 OPEN curs FOR EXECUTE query;
2209 FETCH curs INTO rec;
2211 IF result_row.pivot_point IS NOT NULL THEN
2212 RETURN NEXT result_row;
2218 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
2219 SELECT INTO all_arecords, result_row.sees, afields
2220 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
2221 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
2222 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
2224 FROM metabib.browse_entry_simple_heading_map mbeshm
2225 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
2226 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
2227 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
2228 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
2229 ash.atag = map.authority_field
2230 AND map.metabib_field = ANY(fields)
2232 WHERE mbeshm.entry = rec.id;
2235 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
2236 SELECT INTO all_brecords, result_row.authorities, bfields
2237 ARRAY_AGG(DISTINCT source),
2238 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
2239 ARRAY_AGG(DISTINCT def)
2240 FROM metabib.browse_entry_def_map
2241 WHERE entry = rec.id
2242 AND def = ANY(fields);
2244 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
2246 result_row.sources := 0;
2247 result_row.asources := 0;
2249 -- Bib-linked vis checking
2250 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
2252 full_end := ARRAY_LENGTH(all_brecords, 1);
2253 superpage_size := COALESCE(browse_superpage_size, full_end);
2255 slice_end := superpage_size;
2257 WHILE result_row.sources = 0 AND slice_start <= full_end LOOP
2258 superpage_of_records := all_brecords[slice_start:slice_end];
2260 'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' ||
2261 '1::INT AS rel FROM (SELECT UNNEST(' ||
2262 quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr';
2264 -- We use search.query_parser_fts() for visibility testing.
2265 -- We're calling it once per browse-superpage worth of records
2266 -- out of the set of records related to a given mbe, until we've
2267 -- either exhausted that set of records or found at least 1
2270 SELECT INTO result_row.sources visible
2271 FROM search.query_parser_fts(
2272 context_org, NULL, qpfts_query, NULL,
2273 context_locations, 0, NULL, NULL, FALSE, staff, FALSE
2275 WHERE qpfts.rel IS NULL;
2277 slice_start := slice_start + superpage_size;
2278 slice_end := slice_end + superpage_size;
2281 -- Accurate? Well, probably.
2282 result_row.accurate := browse_superpage_size IS NULL OR
2283 browse_superpage_size >= full_end;
2287 -- Authority-linked vis checking
2288 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
2290 full_end := ARRAY_LENGTH(all_arecords, 1);
2291 superpage_size := COALESCE(browse_superpage_size, full_end);
2293 slice_end := superpage_size;
2295 WHILE result_row.asources = 0 AND slice_start <= full_end LOOP
2296 superpage_of_records := all_arecords[slice_start:slice_end];
2298 'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' ||
2299 '1::INT AS rel FROM (SELECT UNNEST(' ||
2300 quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr';
2302 -- We use search.query_parser_fts() for visibility testing.
2303 -- We're calling it once per browse-superpage worth of records
2304 -- out of the set of records related to a given mbe, via
2305 -- authority until we've either exhausted that set of records
2306 -- or found at least 1 visible record.
2308 SELECT INTO result_row.asources visible
2309 FROM search.query_parser_fts(
2310 context_org, NULL, qpfts_query, NULL,
2311 context_locations, 0, NULL, NULL, FALSE, staff, FALSE
2313 WHERE qpfts.rel IS NULL;
2315 slice_start := slice_start + superpage_size;
2316 slice_end := slice_end + superpage_size;
2320 -- Accurate? Well, probably.
2321 result_row.aaccurate := browse_superpage_size IS NULL OR
2322 browse_superpage_size >= full_end;
2326 IF result_row.sources > 0 OR result_row.asources > 0 THEN
2328 -- The function that calls this function needs row_number in order
2329 -- to correctly order results from two different runs of this
2331 result_row.row_number := row_number;
2333 -- Now, if row_counter is still less than limit, return a row. If
2334 -- not, but it is less than next_pivot_pos, continue on without
2335 -- returning actual result rows until we find
2336 -- that next pivot, and return it.
2338 IF row_counter < result_limit THEN
2339 result_row.browse_entry := rec.id;
2340 result_row.value := rec.value;
2342 RETURN NEXT result_row;
2344 result_row.browse_entry := NULL;
2345 result_row.authorities := NULL;
2346 result_row.fields := NULL;
2347 result_row.value := NULL;
2348 result_row.sources := NULL;
2349 result_row.sees := NULL;
2350 result_row.accurate := NULL;
2351 result_row.aaccurate := NULL;
2352 result_row.pivot_point := rec.id;
2354 IF row_counter >= next_pivot_pos THEN
2355 RETURN NEXT result_row;
2360 IF count_up_from_zero THEN
2361 row_number := row_number + 1;
2363 row_number := row_number - 1;
2366 -- row_counter is different from row_number.
2367 -- It simply counts up from zero so that we know when
2368 -- we've reached our limit.
2369 row_counter := row_counter + 1;
2373 $p$ LANGUAGE PLPGSQL;
2376 CREATE OR REPLACE FUNCTION metabib.browse(
2379 context_org INT DEFAULT NULL,
2380 context_loc_group INT DEFAULT NULL,
2381 staff BOOL DEFAULT FALSE,
2382 pivot_id BIGINT DEFAULT NULL,
2383 result_limit INT DEFAULT 10
2384 ) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$
2389 pivot_sort_value TEXT;
2390 pivot_sort_fallback TEXT;
2391 context_locations INT[];
2392 browse_superpage_size INT;
2393 results_skipped INT := 0;
2397 forward_to_pivot INT;
2399 -- First, find the pivot if we were given a browse term but not a pivot.
2400 IF pivot_id IS NULL THEN
2401 pivot_id := metabib.browse_pivot(search_field, browse_term);
2404 SELECT INTO pivot_sort_value, pivot_sort_fallback
2405 sort_value, value FROM metabib.browse_entry WHERE id = pivot_id;
2407 -- Bail if we couldn't find a pivot.
2408 IF pivot_sort_value IS NULL THEN
2412 -- Transform the context_loc_group argument (if any) (logc at the
2413 -- TPAC layer) into a form we'll be able to use.
2414 IF context_loc_group IS NOT NULL THEN
2415 SELECT INTO context_locations ARRAY_AGG(location)
2416 FROM asset.copy_location_group_map
2417 WHERE lgroup = context_loc_group;
2420 -- Get the configured size of browse superpages.
2421 SELECT INTO browse_superpage_size value -- NULL ok
2422 FROM config.global_flag
2423 WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit';
2425 -- First we're going to search backward from the pivot, then we're going
2426 -- to search forward. In each direction, we need two limits. At the
2427 -- lesser of the two limits, we delineate the edge of the result set
2428 -- we're going to return. At the greater of the two limits, we find the
2429 -- pivot value that would represent an offset from the current pivot
2430 -- at a distance of one "page" in either direction, where a "page" is a
2431 -- result set of the size specified in the "result_limit" argument.
2433 -- The two limits in each direction make four derived values in total,
2434 -- and we calculate them now.
2435 back_limit := CEIL(result_limit::FLOAT / 2);
2436 back_to_pivot := result_limit;
2437 forward_limit := result_limit / 2;
2438 forward_to_pivot := result_limit - 1;
2440 -- This is the meat of the SQL query that finds browse entries. We'll
2441 -- pass this to a function which uses it with a cursor, so that individual
2442 -- rows may be fetched in a loop until some condition is satisfied, without
2443 -- waiting for a result set of fixed size to be collected all at once.
2448 FROM metabib.browse_entry mbe
2450 EXISTS ( -- are there any bibs using this mbe via the requested fields?
2452 FROM metabib.browse_entry_def_map mbedm
2453 WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
2455 ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
2457 FROM metabib.browse_entry_simple_heading_map mbeshm
2458 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
2459 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
2460 ash.atag = map.authority_field
2461 AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
2463 WHERE mbeshm.entry = mbe.id
2467 -- This is the variant of the query for browsing backward.
2468 back_query := core_query ||
2469 ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) ||
2470 ' ORDER BY mbe.sort_value DESC, mbe.value DESC ';
2472 -- This variant browses forward.
2473 forward_query := core_query ||
2474 ' mbe.sort_value > ' || quote_literal(pivot_sort_value) ||
2475 ' ORDER BY mbe.sort_value, mbe.value ';
2477 -- We now call the function which applies a cursor to the provided
2478 -- queries, stopping at the appropriate limits and also giving us
2479 -- the next page's pivot.
2481 SELECT * FROM metabib.staged_browse(
2482 back_query, search_field, context_org, context_locations,
2483 staff, browse_superpage_size, TRUE, back_limit, back_to_pivot
2485 SELECT * FROM metabib.staged_browse(
2486 forward_query, search_field, context_org, context_locations,
2487 staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot
2488 ) ORDER BY row_number DESC;
2491 $p$ LANGUAGE PLPGSQL;
2493 CREATE OR REPLACE FUNCTION metabib.browse(
2496 context_org INT DEFAULT NULL,
2497 context_loc_group INT DEFAULT NULL,
2498 staff BOOL DEFAULT FALSE,
2499 pivot_id BIGINT DEFAULT NULL,
2500 result_limit INT DEFAULT 10
2501 ) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$
2503 RETURN QUERY SELECT * FROM metabib.browse(
2504 (SELECT COALESCE(ARRAY_AGG(id), ARRAY[]::INT[])
2505 FROM config.metabib_field WHERE field_class = search_class),
2514 $p$ LANGUAGE PLPGSQL;