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()', -- get the content of all the nodes within the main selected node
610 REGEXP_REPLACE( xml_node, E'\\s+', ' ', 'g' ) -- Translate adjacent whitespace to a single space
611 ), ' '), ''), -- throw away morally empty (bankrupt?) strings
615 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
617 IF raw_text IS NOT NULL THEN
618 raw_text := raw_text || joiner;
621 raw_text := COALESCE(raw_text,'') || curr_text;
623 -- autosuggest/metabib.browse_entry
624 IF idx.browse_field THEN
626 IF idx.browse_xpath IS NOT NULL AND idx.browse_xpath <> '' THEN
627 browse_text := oils_xpath_string( idx.browse_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
629 browse_text := curr_text;
632 IF idx.browse_sort_xpath IS NOT NULL AND
633 idx.browse_sort_xpath <> '' THEN
635 sort_value := oils_xpath_string(
636 idx.browse_sort_xpath, xml_node, joiner,
637 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
640 sort_value := browse_text;
643 output_row.field_class = idx.field_class;
644 output_row.field = idx.id;
645 output_row.source = rid;
646 output_row.value = BTRIM(REGEXP_REPLACE(browse_text, E'\\s+', ' ', 'g'));
647 output_row.sort_value :=
648 public.naco_normalize(sort_value);
650 output_row.authority := NULL;
652 IF idx.authority_xpath IS NOT NULL AND idx.authority_xpath <> '' THEN
653 authority_text := oils_xpath_string(
654 idx.authority_xpath, xml_node, joiner,
656 ARRAY[xfrm.prefix, xfrm.namespace_uri],
657 ARRAY['xlink','http://www.w3.org/1999/xlink']
661 IF authority_text ~ '^\d+$' THEN
662 authority_link := authority_text::BIGINT;
663 PERFORM * FROM authority.record_entry WHERE id = authority_link;
665 output_row.authority := authority_link;
671 output_row.browse_field = TRUE;
672 -- Returning browse rows with search_field = true for search+browse
673 -- configs allows us to retain granularity of being able to search
674 -- browse fields with "starts with" type operators (for example, for
675 -- titles of songs in music albums)
676 IF idx.search_field THEN
677 output_row.search_field = TRUE;
679 RETURN NEXT output_row;
680 output_row.browse_field = FALSE;
681 output_row.search_field = FALSE;
682 output_row.sort_value := NULL;
685 -- insert raw node text for faceting
686 IF idx.facet_field THEN
688 IF idx.facet_xpath IS NOT NULL AND idx.facet_xpath <> '' THEN
689 facet_text := oils_xpath_string( idx.facet_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
691 facet_text := curr_text;
694 output_row.field_class = idx.field_class;
695 output_row.field = -1 * idx.id;
696 output_row.source = rid;
697 output_row.value = BTRIM(REGEXP_REPLACE(facet_text, E'\\s+', ' ', 'g'));
699 output_row.facet_field = TRUE;
700 RETURN NEXT output_row;
701 output_row.facet_field = FALSE;
706 CONTINUE WHEN raw_text IS NULL OR raw_text = '';
708 -- insert combined node text for searching
709 IF idx.search_field THEN
710 output_row.field_class = idx.field_class;
711 output_row.field = idx.id;
712 output_row.source = rid;
713 output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'g'));
715 output_row.search_field = TRUE;
716 RETURN NEXT output_row;
717 output_row.search_field = FALSE;
724 $func$ LANGUAGE PLPGSQL;
726 CREATE OR REPLACE FUNCTION metabib.update_combined_index_vectors(bib_id BIGINT) RETURNS VOID AS $func$
728 DELETE FROM metabib.combined_keyword_field_entry WHERE record = bib_id;
729 INSERT INTO metabib.combined_keyword_field_entry(record, metabib_field, index_vector)
730 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
731 FROM metabib.keyword_field_entry WHERE source = bib_id GROUP BY field;
732 INSERT INTO metabib.combined_keyword_field_entry(record, metabib_field, index_vector)
733 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
734 FROM metabib.keyword_field_entry WHERE source = bib_id;
736 DELETE FROM metabib.combined_title_field_entry WHERE record = bib_id;
737 INSERT INTO metabib.combined_title_field_entry(record, metabib_field, index_vector)
738 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
739 FROM metabib.title_field_entry WHERE source = bib_id GROUP BY field;
740 INSERT INTO metabib.combined_title_field_entry(record, metabib_field, index_vector)
741 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
742 FROM metabib.title_field_entry WHERE source = bib_id;
744 DELETE FROM metabib.combined_author_field_entry WHERE record = bib_id;
745 INSERT INTO metabib.combined_author_field_entry(record, metabib_field, index_vector)
746 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
747 FROM metabib.author_field_entry WHERE source = bib_id GROUP BY field;
748 INSERT INTO metabib.combined_author_field_entry(record, metabib_field, index_vector)
749 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
750 FROM metabib.author_field_entry WHERE source = bib_id;
752 DELETE FROM metabib.combined_subject_field_entry WHERE record = bib_id;
753 INSERT INTO metabib.combined_subject_field_entry(record, metabib_field, index_vector)
754 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
755 FROM metabib.subject_field_entry WHERE source = bib_id GROUP BY field;
756 INSERT INTO metabib.combined_subject_field_entry(record, metabib_field, index_vector)
757 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
758 FROM metabib.subject_field_entry WHERE source = bib_id;
760 DELETE FROM metabib.combined_series_field_entry WHERE record = bib_id;
761 INSERT INTO metabib.combined_series_field_entry(record, metabib_field, index_vector)
762 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
763 FROM metabib.series_field_entry WHERE source = bib_id GROUP BY field;
764 INSERT INTO metabib.combined_series_field_entry(record, metabib_field, index_vector)
765 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
766 FROM metabib.series_field_entry WHERE source = bib_id;
768 DELETE FROM metabib.combined_identifier_field_entry WHERE record = bib_id;
769 INSERT INTO metabib.combined_identifier_field_entry(record, metabib_field, index_vector)
770 SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
771 FROM metabib.identifier_field_entry WHERE source = bib_id GROUP BY field;
772 INSERT INTO metabib.combined_identifier_field_entry(record, metabib_field, index_vector)
773 SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
774 FROM metabib.identifier_field_entry WHERE source = bib_id;
777 $func$ LANGUAGE PLPGSQL;
779 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$
782 ind_data metabib.field_entry_template%ROWTYPE;
783 mbe_row metabib.browse_entry%ROWTYPE;
791 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;
792 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;
793 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;
795 PERFORM * FROM config.internal_flag WHERE name = 'ingest.assume_inserts_only' AND enabled;
797 IF NOT b_skip_search THEN
798 FOR fclass IN SELECT * FROM config.metabib_class LOOP
799 -- RAISE NOTICE 'Emptying out %', fclass.name;
800 EXECUTE $$DELETE FROM metabib.$$ || fclass.name || $$_field_entry WHERE source = $$ || bib_id;
803 IF NOT b_skip_facet THEN
804 DELETE FROM metabib.facet_entry WHERE source = bib_id;
806 IF NOT b_skip_browse THEN
807 DELETE FROM metabib.browse_entry_def_map WHERE source = bib_id;
811 FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( bib_id ) LOOP
813 -- don't store what has been normalized away
814 CONTINUE WHEN ind_data.value IS NULL;
816 IF ind_data.field < 0 THEN
817 ind_data.field = -1 * ind_data.field;
820 IF ind_data.facet_field AND NOT b_skip_facet THEN
821 INSERT INTO metabib.facet_entry (field, source, value)
822 VALUES (ind_data.field, ind_data.source, ind_data.value);
825 IF ind_data.browse_field AND NOT b_skip_browse THEN
826 -- A caveat about this SELECT: this should take care of replacing
827 -- old mbe rows when data changes, but not if normalization (by
828 -- which I mean specifically the output of
829 -- evergreen.oils_tsearch2()) changes. It may or may not be
830 -- expensive to add a comparison of index_vector to index_vector
831 -- to the WHERE clause below.
833 CONTINUE WHEN ind_data.sort_value IS NULL;
835 value_prepped := metabib.browse_normalize(ind_data.value, ind_data.field);
836 SELECT INTO mbe_row * FROM metabib.browse_entry
837 WHERE value = value_prepped AND sort_value = ind_data.sort_value;
840 mbe_id := mbe_row.id;
842 INSERT INTO metabib.browse_entry
843 ( value, sort_value ) VALUES
844 ( value_prepped, ind_data.sort_value );
846 mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS);
849 INSERT INTO metabib.browse_entry_def_map (entry, def, source, authority)
850 VALUES (mbe_id, ind_data.field, ind_data.source, ind_data.authority);
853 IF ind_data.search_field AND NOT b_skip_search THEN
854 -- Avoid inserting duplicate rows
855 EXECUTE 'SELECT 1 FROM metabib.' || ind_data.field_class ||
856 '_field_entry WHERE field = $1 AND source = $2 AND value = $3'
857 INTO mbe_id USING ind_data.field, ind_data.source, ind_data.value;
858 -- RAISE NOTICE 'Search for an already matching row returned %', mbe_id;
859 IF mbe_id IS NULL THEN
861 INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value)
863 quote_literal(ind_data.field) || $$, $$ ||
864 quote_literal(ind_data.source) || $$, $$ ||
865 quote_literal(ind_data.value) ||
872 IF NOT b_skip_search THEN
873 PERFORM metabib.update_combined_index_vectors(bib_id);
878 $func$ LANGUAGE PLPGSQL;
880 -- default to a space joiner
881 CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry ( BIGINT ) RETURNS SETOF metabib.field_entry_template AS $func$
882 SELECT * FROM biblio.extract_metabib_field_entry($1, ' ');
885 CREATE OR REPLACE FUNCTION authority.flatten_marc ( rid BIGINT ) RETURNS SETOF authority.full_rec AS $func$
887 auth authority.record_entry%ROWTYPE;
888 output authority.full_rec%ROWTYPE;
891 SELECT INTO auth * FROM authority.record_entry WHERE id = rid;
893 FOR field IN SELECT * FROM vandelay.flatten_marc( auth.marc ) LOOP
894 output.record := rid;
895 output.ind1 := field.ind1;
896 output.ind2 := field.ind2;
897 output.tag := field.tag;
898 output.subfield := field.subfield;
899 output.value := field.value;
904 $func$ LANGUAGE PLPGSQL;
906 CREATE OR REPLACE FUNCTION biblio.flatten_marc ( rid BIGINT ) RETURNS SETOF metabib.full_rec AS $func$
908 bib biblio.record_entry%ROWTYPE;
909 output metabib.full_rec%ROWTYPE;
912 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
914 FOR field IN SELECT * FROM vandelay.flatten_marc( bib.marc ) LOOP
915 output.record := rid;
916 output.ind1 := field.ind1;
917 output.ind2 := field.ind2;
918 output.tag := field.tag;
919 output.subfield := field.subfield;
920 output.value := field.value;
925 $func$ LANGUAGE PLPGSQL;
927 CREATE OR REPLACE FUNCTION biblio.marc21_extract_fixed_field_list( rid BIGINT, ff TEXT ) RETURNS TEXT[] AS $func$
928 SELECT * FROM vandelay.marc21_extract_fixed_field_list( (SELECT marc FROM biblio.record_entry WHERE id = $1), $2, TRUE );
931 CREATE OR REPLACE FUNCTION biblio.marc21_extract_fixed_field( rid BIGINT, ff TEXT ) RETURNS TEXT AS $func$
932 SELECT * FROM vandelay.marc21_extract_fixed_field( (SELECT marc FROM biblio.record_entry WHERE id = $1), $2, TRUE );
935 CREATE OR REPLACE FUNCTION biblio.marc21_extract_all_fixed_fields( rid BIGINT ) RETURNS SETOF biblio.record_ff_map AS $func$
936 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 );
939 CREATE OR REPLACE FUNCTION biblio.marc21_physical_characteristics( rid BIGINT ) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$
940 SELECT id, $1 AS record, ptype, subfield, value FROM vandelay.marc21_physical_characteristics( (SELECT marc FROM biblio.record_entry WHERE id = $1), TRUE );
943 CREATE OR REPLACE FUNCTION biblio.extract_quality ( marc TEXT, best_lang TEXT, best_type TEXT ) RETURNS INT AS $func$
956 IF marc IS NULL OR marc = '' THEN
960 -- First, the count of tags
961 qual := ARRAY_UPPER(oils_xpath('*[local-name()="datafield"]', marc), 1);
963 -- now go through a bunch of pain to get the record type
964 IF best_type IS NOT NULL THEN
965 ldr := (oils_xpath('//*[local-name()="leader"]/text()', marc))[1];
967 IF ldr IS NOT NULL THEN
968 SELECT * INTO tval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'Type' LIMIT 1; -- They're all the same
969 SELECT * INTO bval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'BLvl' LIMIT 1; -- They're all the same
972 tval := SUBSTRING( ldr, tval_rec.start_pos + 1, tval_rec.length );
973 bval := SUBSTRING( ldr, bval_rec.start_pos + 1, bval_rec.length );
975 -- RAISE NOTICE 'type %, blvl %, ldr %', tval, bval, ldr;
977 SELECT * INTO type_map FROM config.marc21_rec_type_map WHERE type_val LIKE '%' || tval || '%' AND blvl_val LIKE '%' || bval || '%';
979 IF type_map.code IS NOT NULL THEN
980 IF best_type = type_map.code THEN
981 qual := qual + qual / 2;
984 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
985 ff_tag_data := SUBSTRING((oils_xpath('//*[@tag="' || ff_pos.tag || '"]/text()',marc))[1], ff_pos.start_pos + 1, ff_pos.length);
986 IF ff_tag_data = best_lang THEN
994 -- Now look for some quality metrics
996 IF ARRAY_UPPER(oils_xpath('//*[@tag="040"]/*[@code="a" and contains(.,"DLC")]', marc), 1) = 1 THEN
1001 IF (oils_xpath('//*[@tag="003"]/text()', marc))[1] ~* E'oclo?c' THEN
1008 $func$ LANGUAGE PLPGSQL;
1010 CREATE OR REPLACE FUNCTION biblio.extract_fingerprint ( marc text ) RETURNS TEXT AS $func$
1012 idx config.biblio_fingerprint%ROWTYPE;
1013 xfrm config.xml_transform%ROWTYPE;
1015 transformed_xml TEXT;
1017 xml_node_list TEXT[];
1019 output_text TEXT := '';
1022 IF marc IS NULL OR marc = '' THEN
1026 -- Loop over the indexing entries
1027 FOR idx IN SELECT * FROM config.biblio_fingerprint ORDER BY format, id LOOP
1029 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
1031 -- See if we can skip the XSLT ... it's expensive
1032 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
1033 -- Can't skip the transform
1034 IF xfrm.xslt <> '---' THEN
1035 transformed_xml := oils_xslt_process(marc,xfrm.xslt);
1037 transformed_xml := marc;
1040 prev_xfrm := xfrm.name;
1043 raw_text := COALESCE(
1051 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
1060 raw_text := REGEXP_REPLACE(raw_text, E'\\[.+?\\]', E'');
1061 raw_text := REGEXP_REPLACE(raw_text, E'\\mthe\\M|\\man?d?d\\M', E'', 'g'); -- arg! the pain!
1063 IF idx.first_word IS TRUE THEN
1064 raw_text := REGEXP_REPLACE(raw_text, E'^(\\w+).*?$', E'\\1');
1067 output_text := output_text || REGEXP_REPLACE(raw_text, E'\\s+', '', 'g');
1074 $func$ LANGUAGE PLPGSQL;
1076 -- BEFORE UPDATE OR INSERT trigger for biblio.record_entry
1077 CREATE OR REPLACE FUNCTION biblio.fingerprint_trigger () RETURNS TRIGGER AS $func$
1080 -- For TG_ARGV, first param is language (like 'eng'), second is record type (like 'BKS')
1082 IF NEW.deleted IS TRUE THEN -- we don't much care, then, do we?
1086 NEW.fingerprint := biblio.extract_fingerprint(NEW.marc);
1087 NEW.quality := biblio.extract_quality(NEW.marc, TG_ARGV[0], TG_ARGV[1]);
1092 $func$ LANGUAGE PLPGSQL;
1094 CREATE OR REPLACE FUNCTION metabib.reingest_metabib_full_rec( bib_id BIGINT ) RETURNS VOID AS $func$
1096 PERFORM * FROM config.internal_flag WHERE name = 'ingest.assume_inserts_only' AND enabled;
1098 DELETE FROM metabib.real_full_rec WHERE record = bib_id;
1100 INSERT INTO metabib.real_full_rec (record, tag, ind1, ind2, subfield, value)
1101 SELECT record, tag, ind1, ind2, subfield, value FROM biblio.flatten_marc( bib_id );
1105 $func$ LANGUAGE PLPGSQL;
1107 CREATE OR REPLACE FUNCTION biblio.extract_located_uris( bib_id BIGINT, marcxml TEXT, editor_id INT ) RETURNS VOID AS $func$
1114 uri_owner_list TEXT[];
1122 -- Clear any URI mappings and call numbers for this bib.
1123 -- This leads to acn / auricnm inflation, but also enables
1124 -- old acn/auricnm's to go away and for bibs to be deleted.
1125 FOR uri_cn_id IN SELECT id FROM asset.call_number WHERE record = bib_id AND label = '##URI##' AND NOT deleted LOOP
1126 DELETE FROM asset.uri_call_number_map WHERE call_number = uri_cn_id;
1127 DELETE FROM asset.call_number WHERE id = uri_cn_id;
1130 uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',marcxml);
1131 IF ARRAY_UPPER(uris,1) > 0 THEN
1132 FOR i IN 1 .. ARRAY_UPPER(uris, 1) LOOP
1133 -- First we pull info out of the 856
1136 uri_href := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1];
1137 uri_label := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()',uri_xml))[1];
1138 uri_use := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()',uri_xml))[1];
1140 IF uri_label IS NULL THEN
1141 uri_label := uri_href;
1143 CONTINUE WHEN uri_href IS NULL;
1145 -- Get the distinct list of libraries wanting to use
1147 DISTINCT REGEXP_REPLACE(
1149 $re$^.*?\((\w+)\).*$$re$,
1152 ) INTO uri_owner_list
1155 '//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()',
1160 IF ARRAY_UPPER(uri_owner_list,1) > 0 THEN
1162 -- look for a matching uri
1163 IF uri_use IS NULL THEN
1164 SELECT id INTO uri_id
1166 WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active
1167 ORDER BY id LIMIT 1;
1168 IF NOT FOUND THEN -- create one
1169 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
1170 SELECT id INTO uri_id
1172 WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active;
1175 SELECT id INTO uri_id
1177 WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active
1178 ORDER BY id LIMIT 1;
1179 IF NOT FOUND THEN -- create one
1180 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
1181 SELECT id INTO uri_id
1183 WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
1187 FOR j IN 1 .. ARRAY_UPPER(uri_owner_list, 1) LOOP
1188 uri_owner := uri_owner_list[j];
1190 SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = uri_owner;
1191 CONTINUE WHEN NOT FOUND;
1193 -- we need a call number to link through
1194 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;
1196 INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label)
1197 VALUES (uri_owner_id, bib_id, 'now', 'now', editor_id, editor_id, '##URI##');
1198 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;
1201 -- now, link them if they're not already
1202 SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id;
1204 INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id);
1216 $func$ LANGUAGE PLPGSQL;
1218 CREATE OR REPLACE FUNCTION metabib.remap_metarecord_for_bib( bib_id BIGINT, fp TEXT, bib_is_deleted BOOL DEFAULT FALSE, retain_deleted BOOL DEFAULT FALSE ) RETURNS BIGINT AS $func$
1220 new_mapping BOOL := TRUE;
1223 tmp_mr metabib.metarecord%ROWTYPE;
1224 deleted_mrs BIGINT[];
1227 -- We need to make sure we're not a deleted master record of an MR
1228 IF bib_is_deleted THEN
1229 FOR old_mr IN SELECT id FROM metabib.metarecord WHERE master_record = bib_id LOOP
1231 IF NOT retain_deleted THEN -- Go away for any MR that we're master of, unless retained
1232 DELETE FROM metabib.metarecord_source_map WHERE source = bib_id;
1235 -- Now, are there any more sources on this MR?
1236 SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = old_mr;
1238 IF source_count = 0 AND NOT retain_deleted THEN -- No other records
1239 deleted_mrs := ARRAY_APPEND(deleted_mrs, old_mr); -- Just in case...
1240 DELETE FROM metabib.metarecord WHERE id = old_mr;
1242 ELSE -- indeed there are. Update it with a null cache and recalcualated master record
1243 UPDATE metabib.metarecord
1245 master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp AND NOT deleted ORDER BY quality DESC LIMIT 1)
1250 ELSE -- insert or update
1252 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
1254 -- Find the first fingerprint-matching
1255 IF old_mr IS NULL AND fp = tmp_mr.fingerprint THEN
1256 old_mr := tmp_mr.id;
1257 new_mapping := FALSE;
1259 ELSE -- Our fingerprint changed ... maybe remove the old MR
1260 DELETE FROM metabib.metarecord_source_map WHERE metarecord = old_mr AND source = bib_id; -- remove the old source mapping
1261 SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = tmp_mr.id;
1262 IF source_count = 0 THEN -- No other records
1263 deleted_mrs := ARRAY_APPEND(deleted_mrs, tmp_mr.id);
1264 DELETE FROM metabib.metarecord WHERE id = tmp_mr.id;
1270 -- we found no suitable, preexisting MR based on old source maps
1271 IF old_mr IS NULL THEN
1272 SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp; -- is there one for our current fingerprint?
1274 IF old_mr IS NULL THEN -- nope, create one and grab its id
1275 INSERT INTO metabib.metarecord ( fingerprint, master_record ) VALUES ( fp, bib_id );
1276 SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp;
1278 ELSE -- indeed there is. update it with a null cache and recalcualated master record
1279 UPDATE metabib.metarecord
1281 master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp AND NOT deleted ORDER BY quality DESC LIMIT 1)
1285 ELSE -- there was one we already attached to, update its mods cache and master_record
1286 UPDATE metabib.metarecord
1288 master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp AND NOT deleted ORDER BY quality DESC LIMIT 1)
1293 INSERT INTO metabib.metarecord_source_map (metarecord, source) VALUES (old_mr, bib_id); -- new source mapping
1298 IF ARRAY_UPPER(deleted_mrs,1) > 0 THEN
1299 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
1305 $func$ LANGUAGE PLPGSQL;
1308 CREATE OR REPLACE FUNCTION biblio.map_authority_linking (bibid BIGINT, marc TEXT) RETURNS BIGINT AS $func$
1309 DELETE FROM authority.bib_linking WHERE bib = $1;
1310 INSERT INTO authority.bib_linking (bib, authority)
1313 FROM ( SELECT DISTINCT $1 AS bib,
1314 BTRIM(remove_paren_substring(txt))::BIGINT AS authority
1315 FROM unnest(oils_xpath('//*[@code="0"]/text()',$2)) x(txt)
1316 WHERE BTRIM(remove_paren_substring(txt)) ~ $re$^\d+$$re$
1317 ) y JOIN authority.record_entry r ON r.id = y.authority;
1319 $func$ LANGUAGE SQL;
1321 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$
1323 transformed_xml TEXT;
1324 rmarc TEXT := prmarc;
1328 xfrm config.xml_transform%ROWTYPE;
1329 attr_vector INT[] := '{}'::INT[];
1330 attr_vector_tmp INT[];
1331 attr_list TEXT[] := pattr_list;
1333 norm_attr_value TEXT[];
1335 attr_def config.record_attr_definition%ROWTYPE;
1336 ccvm_row config.coded_value_map%ROWTYPE;
1339 IF attr_list IS NULL OR rdeleted THEN -- need to do the full dance on INSERT or undelete
1340 SELECT ARRAY_AGG(name) INTO attr_list FROM config.record_attr_definition;
1343 IF rmarc IS NULL THEN
1344 SELECT marc INTO rmarc FROM biblio.record_entry WHERE id = rid;
1347 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE NOT composite AND name = ANY( attr_list ) ORDER BY format LOOP
1349 attr_value := '{}'::TEXT[];
1350 norm_attr_value := '{}'::TEXT[];
1351 attr_vector_tmp := '{}'::INT[];
1353 SELECT * INTO ccvm_row FROM config.coded_value_map c WHERE c.ctype = attr_def.name LIMIT 1;
1355 -- tag+sf attrs only support SVF
1356 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
1357 SELECT ARRAY[ARRAY_TO_STRING(ARRAY_AGG(value), COALESCE(attr_def.joiner,' '))] INTO attr_value
1358 FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x
1360 AND tag LIKE attr_def.tag
1362 WHEN attr_def.sf_list IS NOT NULL
1363 THEN POSITION(subfield IN attr_def.sf_list) > 0
1370 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
1371 attr_value := vandelay.marc21_extract_fixed_field_list(rmarc, attr_def.fixed_field);
1373 IF NOT attr_def.multi THEN
1374 attr_value := ARRAY[attr_value[1]];
1377 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
1379 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
1381 -- See if we can skip the XSLT ... it's expensive
1382 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
1383 -- Can't skip the transform
1384 IF xfrm.xslt <> '---' THEN
1385 transformed_xml := oils_xslt_process(rmarc,xfrm.xslt);
1387 transformed_xml := rmarc;
1390 prev_xfrm := xfrm.name;
1393 IF xfrm.name IS NULL THEN
1394 -- just grab the marcxml (empty) transform
1395 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
1396 prev_xfrm := xfrm.name;
1399 FOR tmp_xml IN SELECT oils_xpath(attr_def.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]) LOOP
1400 tmp_val := oils_xpath_string(
1403 COALESCE(attr_def.joiner,' '),
1404 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
1406 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
1407 attr_value := attr_value || tmp_val;
1408 EXIT WHEN NOT attr_def.multi;
1412 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
1413 SELECT ARRAY_AGG(m.value) INTO attr_value
1414 FROM vandelay.marc21_physical_characteristics(rmarc) v
1415 LEFT JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
1416 WHERE v.subfield = attr_def.phys_char_sf AND (m.value IS NOT NULL AND BTRIM(m.value) <> '')
1417 AND ( ccvm_row.id IS NULL OR ( ccvm_row.id IS NOT NULL AND v.id IS NOT NULL) );
1419 IF NOT attr_def.multi THEN
1420 attr_value := ARRAY[attr_value[1]];
1425 -- apply index normalizers to attr_value
1426 FOR tmp_val IN SELECT value FROM UNNEST(attr_value) x(value) LOOP
1428 SELECT n.func AS func,
1429 n.param_count AS param_count,
1431 FROM config.index_normalizer n
1432 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
1433 WHERE attr = attr_def.name
1435 EXECUTE 'SELECT ' || normalizer.func || '(' ||
1436 COALESCE( quote_literal( tmp_val ), 'NULL' ) ||
1438 WHEN normalizer.param_count > 0
1439 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
1445 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
1446 norm_attr_value := norm_attr_value || tmp_val;
1450 IF attr_def.filter THEN
1451 -- Create unknown uncontrolled values and find the IDs of the values
1452 IF ccvm_row.id IS NULL THEN
1453 FOR tmp_val IN SELECT value FROM UNNEST(norm_attr_value) x(value) LOOP
1454 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
1455 BEGIN -- use subtransaction to isolate unique constraint violations
1456 INSERT INTO metabib.uncontrolled_record_attr_value ( attr, value ) VALUES ( attr_def.name, tmp_val );
1457 EXCEPTION WHEN unique_violation THEN END;
1461 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 );
1463 SELECT ARRAY_AGG(id) INTO attr_vector_tmp FROM config.coded_value_map WHERE ctype = attr_def.name AND code = ANY( norm_attr_value );
1466 -- Add the new value to the vector
1467 attr_vector := attr_vector || attr_vector_tmp;
1470 IF attr_def.sorter AND norm_attr_value[1] IS NOT NULL THEN
1471 DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
1472 INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, norm_attr_value[1]);
1477 /* We may need to rewrite the vlist to contain
1478 the intersection of new values for requested
1479 attrs and old values for ignored attrs. To
1480 do this, we take the old attr vlist and
1481 subtract any values that are valid for the
1482 requested attrs, and then add back the new
1483 set of attr values. */
1485 IF ARRAY_LENGTH(pattr_list, 1) > 0 THEN
1486 SELECT vlist INTO attr_vector_tmp FROM metabib.record_attr_vector_list WHERE source = rid;
1487 SELECT attr_vector_tmp - ARRAY_AGG(id::INT) INTO attr_vector_tmp FROM metabib.full_attr_id_map WHERE attr = ANY (pattr_list);
1488 attr_vector := attr_vector || attr_vector_tmp;
1491 -- On to composite attributes, now that the record attrs have been pulled. Processed in name order, so later composite
1492 -- attributes can depend on earlier ones.
1493 PERFORM metabib.compile_composite_attr_cache_init();
1494 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE composite AND name = ANY( attr_list ) ORDER BY name LOOP
1496 FOR ccvm_row IN SELECT * FROM config.coded_value_map c WHERE c.ctype = attr_def.name ORDER BY value LOOP
1498 tmp_val := metabib.compile_composite_attr( ccvm_row.id );
1499 CONTINUE WHEN tmp_val IS NULL OR tmp_val = ''; -- nothing to do
1501 IF attr_def.filter THEN
1502 IF attr_vector @@ tmp_val::query_int THEN
1503 attr_vector = attr_vector + intset(ccvm_row.id);
1504 EXIT WHEN NOT attr_def.multi;
1508 IF attr_def.sorter THEN
1509 IF attr_vector @@ tmp_val THEN
1510 DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
1511 INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, ccvm_row.code);
1519 IF ARRAY_LENGTH(attr_vector, 1) > 0 THEN
1520 IF rdeleted THEN -- initial insert OR revivication
1521 DELETE FROM metabib.record_attr_vector_list WHERE source = rid;
1522 INSERT INTO metabib.record_attr_vector_list (source, vlist) VALUES (rid, attr_vector);
1524 UPDATE metabib.record_attr_vector_list SET vlist = attr_vector WHERE source = rid;
1530 $func$ LANGUAGE PLPGSQL;
1533 -- AFTER UPDATE OR INSERT trigger for biblio.record_entry
1534 CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
1539 IF NEW.deleted THEN -- If this bib is deleted
1541 PERFORM * FROM config.internal_flag WHERE
1542 name = 'ingest.metarecord_mapping.preserve_on_delete' AND enabled;
1544 tmp_bool := FOUND; -- Just in case this is changed by some other statement
1546 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint, TRUE, tmp_bool );
1548 IF NOT tmp_bool THEN
1549 -- One needs to keep these around to support searches
1550 -- with the #deleted modifier, so one should turn on the named
1551 -- internal flag for that functionality.
1552 DELETE FROM metabib.record_attr_vector_list WHERE source = NEW.id;
1555 DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible
1556 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = NEW.id; -- Separate any multi-homed items
1557 DELETE FROM metabib.browse_entry_def_map WHERE source = NEW.id; -- Don't auto-suggest deleted bibs
1558 RETURN NEW; -- and we're done
1561 IF TG_OP = 'UPDATE' THEN -- re-ingest?
1562 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
1564 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
1569 -- Record authority linking
1570 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking' AND enabled;
1572 PERFORM biblio.map_authority_linking( NEW.id, NEW.marc );
1575 -- Flatten and insert the mfr data
1576 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled;
1578 PERFORM metabib.reingest_metabib_full_rec(NEW.id);
1580 -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields
1581 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled;
1583 PERFORM metabib.reingest_record_attributes(NEW.id, NULL, NEW.marc, TG_OP = 'INSERT' OR OLD.deleted);
1587 -- Gather and insert the field entry data
1588 PERFORM metabib.reingest_metabib_field_entries(NEW.id);
1590 -- Located URI magic
1591 IF TG_OP = 'INSERT' THEN
1592 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
1594 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
1597 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
1599 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
1603 -- (re)map metarecord-bib linking
1604 IF TG_OP = 'INSERT' THEN -- if not deleted and performing an insert, check for the flag
1605 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled;
1607 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
1609 ELSE -- we're doing an update, and we're not deleted, remap
1610 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_update' AND enabled;
1612 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
1618 $func$ LANGUAGE PLPGSQL;
1620 CREATE OR REPLACE FUNCTION metabib.browse_normalize(facet_text TEXT, mapped_field INT) RETURNS TEXT AS $$
1626 SELECT n.func AS func,
1627 n.param_count AS param_count,
1629 FROM config.index_normalizer n
1630 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
1631 WHERE m.field = mapped_field AND m.pos < 0
1634 EXECUTE 'SELECT ' || normalizer.func || '(' ||
1635 quote_literal( facet_text ) ||
1637 WHEN normalizer.param_count > 0
1638 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
1641 ')' INTO facet_text;
1648 $$ LANGUAGE PLPGSQL;
1651 -- This mimics a specific part of QueryParser, turning the first part of a
1652 -- classed search (search_class) into a set of classes and possibly fields.
1653 -- search_class might look like "author" or "title|proper" or "ti|uniform"
1654 -- or "au" or "au|corporate|personal" or anything like that, where the first
1655 -- element of the list you get by separating on the "|" character is either
1656 -- a registered class (config.metabib_class) or an alias
1657 -- (config.metabib_search_alias), and the rest of any such elements are
1658 -- fields (config.metabib_field).
1660 FUNCTION metabib.search_class_to_registered_components(search_class TEXT)
1661 RETURNS SETOF RECORD AS $func$
1663 search_parts TEXT[];
1665 search_part_count INTEGER;
1667 registered_class config.metabib_class%ROWTYPE;
1668 registered_alias config.metabib_search_alias%ROWTYPE;
1669 registered_field config.metabib_field%ROWTYPE;
1671 search_parts := REGEXP_SPLIT_TO_ARRAY(search_class, E'\\|');
1673 search_part_count := ARRAY_LENGTH(search_parts, 1);
1674 IF search_part_count = 0 THEN
1677 SELECT INTO registered_class
1678 * FROM config.metabib_class WHERE name = search_parts[1];
1680 IF search_part_count < 2 THEN -- all fields
1681 rec := (registered_class.name, NULL::INTEGER);
1685 FOR field_name IN SELECT *
1686 FROM UNNEST(search_parts[2:search_part_count]) LOOP
1687 SELECT INTO registered_field
1688 * FROM config.metabib_field
1689 WHERE name = field_name AND
1690 field_class = registered_class.name;
1692 rec := (registered_class.name, registered_field.id);
1697 -- maybe we have an alias?
1698 SELECT INTO registered_alias
1699 * FROM config.metabib_search_alias WHERE alias=search_parts[1];
1703 IF search_part_count < 2 THEN -- return w/e the alias says
1705 registered_alias.field_class, registered_alias.field
1710 FOR field_name IN SELECT *
1711 FROM UNNEST(search_parts[2:search_part_count]) LOOP
1712 SELECT INTO registered_field
1713 * FROM config.metabib_field
1714 WHERE name = field_name AND
1715 field_class = registered_alias.field_class;
1718 registered_alias.field_class,
1729 $func$ LANGUAGE PLPGSQL ROWS 1;
1732 -- Given a string such as a user might type into a search box, prepare
1733 -- two changed variants for TO_TSQUERY(). See
1734 -- http://www.postgresql.org/docs/9.0/static/textsearch-controls.html
1735 -- The first variant is normalized to match indexed documents regardless
1736 -- of diacritics. The second variant keeps its diacritics for proper
1737 -- highlighting via TS_HEADLINE().
1739 FUNCTION metabib.autosuggest_prepare_tsquery(orig TEXT) RETURNS TEXT[] AS
1742 orig_ended_in_space BOOLEAN;
1747 orig_ended_in_space := orig ~ E'\\s$';
1749 orig := ARRAY_TO_STRING(
1750 evergreen.regexp_split_to_array(orig, E'\\W+'), ' '
1753 normalized := public.naco_normalize(orig); -- also trim()s
1754 plain := trim(orig);
1756 IF NOT orig_ended_in_space THEN
1757 plain := plain || ':*';
1758 normalized := normalized || ':*';
1761 plain := ARRAY_TO_STRING(
1762 evergreen.regexp_split_to_array(plain, E'\\s+'), ' & '
1764 normalized := ARRAY_TO_STRING(
1765 evergreen.regexp_split_to_array(normalized, E'\\s+'), ' & '
1768 RETURN ARRAY[normalized, plain];
1770 $$ LANGUAGE PLPGSQL;
1774 FUNCTION metabib.suggest_browse_entries(
1775 raw_query_text TEXT, -- actually typed by humans at the UI level
1776 search_class TEXT, -- 'alias' or 'class' or 'class|field..', etc
1777 headline_opts TEXT, -- markup options for ts_headline()
1778 visibility_org INTEGER,-- null if you don't want opac visibility test
1779 query_limit INTEGER,-- use in LIMIT clause of interal query
1780 normalization INTEGER -- argument to TS_RANK_CD()
1782 value TEXT, -- plain
1784 buoyant_and_class_match BOOL,
1786 field_weight INTEGER,
1789 match TEXT -- marked up
1792 prepared_query_texts TEXT[];
1794 plain_query TSQUERY;
1795 opac_visibility_join TEXT;
1796 search_class_join TEXT;
1799 prepared_query_texts := metabib.autosuggest_prepare_tsquery(raw_query_text);
1801 query := TO_TSQUERY('keyword', prepared_query_texts[1]);
1802 plain_query := TO_TSQUERY('keyword', prepared_query_texts[2]);
1804 visibility_org := NULLIF(visibility_org,-1);
1805 IF visibility_org IS NOT NULL THEN
1806 opac_visibility_join := '
1807 JOIN asset.opac_visible_copies aovc ON (
1808 aovc.record = x.source AND
1809 aovc.circ_lib IN (SELECT id FROM actor.org_unit_descendants($4))
1812 opac_visibility_join := '';
1815 -- The following determines whether we only provide suggestsons matching
1816 -- the user's selected search_class, or whether we show other suggestions
1817 -- too. The reason for MIN() is that for search_classes like
1818 -- 'title|proper|uniform' you would otherwise get multiple rows. The
1819 -- implication is that if title as a class doesn't have restrict,
1820 -- nor does the proper field, but the uniform field does, you're going
1821 -- to get 'false' for your overall evaluation of 'should we restrict?'
1822 -- To invert that, change from MIN() to MAX().
1826 MIN(cmc.restrict::INT) AS restrict_class,
1827 MIN(cmf.restrict::INT) AS restrict_field
1828 FROM metabib.search_class_to_registered_components(search_class)
1829 AS _registered (field_class TEXT, field INT)
1831 config.metabib_class cmc ON (cmc.name = _registered.field_class)
1833 config.metabib_field cmf ON (cmf.id = _registered.field);
1835 -- evaluate 'should we restrict?'
1836 IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN
1837 search_class_join := '
1839 metabib.search_class_to_registered_components($2)
1840 AS _registered (field_class TEXT, field INT) ON (
1841 (_registered.field IS NULL AND
1842 _registered.field_class = cmf.field_class) OR
1843 (_registered.field = cmf.id)
1847 search_class_join := '
1849 metabib.search_class_to_registered_components($2)
1850 AS _registered (field_class TEXT, field INT) ON (
1851 _registered.field_class = cmc.name
1856 RETURN QUERY EXECUTE '
1865 TS_HEADLINE(value, $7, $3)
1866 FROM (SELECT DISTINCT
1869 cmc.buoyant AND _registered.field_class IS NOT NULL AS push,
1870 _registered.field = cmf.id AS restrict,
1872 TS_RANK_CD(mbe.index_vector, $1, $6),
1875 FROM metabib.browse_entry_def_map mbedm
1876 JOIN (SELECT * FROM metabib.browse_entry WHERE index_vector @@ $1 LIMIT 10000) mbe ON (mbe.id = mbedm.entry)
1877 JOIN config.metabib_field cmf ON (cmf.id = mbedm.def)
1878 JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name)
1879 ' || search_class_join || '
1880 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
1882 ' || opac_visibility_join || '
1883 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
1885 ' -- sic, repeat the order by clause in the outer select too
1887 query, search_class, headline_opts,
1888 visibility_org, query_limit, normalization, plain_query
1892 -- buoyant AND chosen class = match class
1893 -- chosen field = match field
1900 $func$ LANGUAGE PLPGSQL;
1902 CREATE OR REPLACE FUNCTION public.oils_tsearch2 () RETURNS TRIGGER AS $$
1906 temp_vector TEXT := '';
1912 NEW.index_vector = ''::tsvector;
1914 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
1916 SELECT n.func AS func,
1917 n.param_count AS param_count,
1919 FROM config.index_normalizer n
1920 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
1921 WHERE field = NEW.field AND m.pos < 0
1923 EXECUTE 'SELECT ' || normalizer.func || '(' ||
1924 quote_literal( value ) ||
1926 WHEN normalizer.param_count > 0
1927 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
1937 SELECT n.func AS func,
1938 n.param_count AS param_count,
1940 FROM config.index_normalizer n
1941 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
1942 WHERE field = NEW.field AND m.pos >= 0
1944 EXECUTE 'SELECT ' || normalizer.func || '(' ||
1945 quote_literal( value ) ||
1947 WHEN normalizer.param_count > 0
1948 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
1956 IF TG_TABLE_NAME::TEXT ~ 'browse_entry$' THEN
1958 value := ARRAY_TO_STRING(
1959 evergreen.regexp_split_to_array(value, E'\\W+'), ' '
1961 value := public.search_normalize(value);
1962 NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);
1964 ELSIF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
1967 SELECT DISTINCT m.ts_config, m.index_weight
1968 FROM config.metabib_class_ts_map m
1969 LEFT JOIN metabib.record_attr_vector_list r ON (r.source = NEW.source)
1970 LEFT JOIN config.coded_value_map ccvm ON (
1971 ccvm.ctype IN ('item_lang', 'language') AND
1972 ccvm.code = m.index_lang AND
1973 r.vlist @> intset(ccvm.id)
1975 WHERE m.field_class = TG_ARGV[0]
1977 AND (m.always OR NOT EXISTS (SELECT 1 FROM config.metabib_field_ts_map WHERE metabib_field = NEW.field))
1978 AND (m.index_lang IS NULL OR ccvm.id IS NOT NULL)
1980 SELECT DISTINCT m.ts_config, m.index_weight
1981 FROM config.metabib_field_ts_map m
1982 LEFT JOIN metabib.record_attr_vector_list r ON (r.source = NEW.source)
1983 LEFT JOIN config.coded_value_map ccvm ON (
1984 ccvm.ctype IN ('item_lang', 'language') AND
1985 ccvm.code = m.index_lang AND
1986 r.vlist @> intset(ccvm.id)
1988 WHERE m.metabib_field = NEW.field
1990 AND (m.index_lang IS NULL OR ccvm.id IS NOT NULL)
1991 ORDER BY index_weight ASC
1995 IF cur_weight IS NOT NULL AND cur_weight != ts_rec.index_weight THEN
1996 NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
2000 cur_weight = ts_rec.index_weight;
2001 SELECT INTO temp_vector temp_vector || ' ' || to_tsvector(ts_rec.ts_config::regconfig, value)::TEXT;
2004 NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
2006 NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);
2011 $$ LANGUAGE PLPGSQL;
2014 CREATE TYPE metabib.flat_browse_entry_appearance AS (
2015 browse_entry BIGINT,
2020 sources INT, -- visible ones, that is
2021 asources INT, -- visible ones, that is
2022 row_number INT, -- internal use, sort of
2023 accurate BOOL, -- Count in sources field is accurate? Not
2024 -- if we had more than a browse superpage
2025 -- of records to look at.
2026 aaccurate BOOL, -- See previous comment...
2031 CREATE OR REPLACE FUNCTION metabib.browse_bib_pivot(
2034 ) RETURNS BIGINT AS $p$
2036 FROM metabib.browse_entry mbe
2037 JOIN metabib.browse_entry_def_map mbedm ON (
2038 mbedm.entry = mbe.id
2039 AND mbedm.def = ANY($1)
2041 WHERE mbe.sort_value >= public.naco_normalize($2)
2042 ORDER BY mbe.sort_value, mbe.value LIMIT 1;
2043 $p$ LANGUAGE SQL STABLE;
2045 CREATE OR REPLACE FUNCTION metabib.browse_authority_pivot(
2048 ) RETURNS BIGINT AS $p$
2050 FROM metabib.browse_entry mbe
2051 JOIN metabib.browse_entry_simple_heading_map mbeshm ON ( mbeshm.entry = mbe.id )
2052 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
2053 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
2054 ash.atag = map.authority_field
2055 AND map.metabib_field = ANY($1)
2057 WHERE mbe.sort_value >= public.naco_normalize($2)
2058 ORDER BY mbe.sort_value, mbe.value LIMIT 1;
2059 $p$ LANGUAGE SQL STABLE;
2061 CREATE OR REPLACE FUNCTION metabib.browse_authority_refs_pivot(
2064 ) RETURNS BIGINT AS $p$
2066 FROM metabib.browse_entry mbe
2067 JOIN metabib.browse_entry_simple_heading_map mbeshm ON ( mbeshm.entry = mbe.id )
2068 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
2069 JOIN authority.control_set_auth_field_metabib_field_map_refs_only map ON (
2070 ash.atag = map.authority_field
2071 AND map.metabib_field = ANY($1)
2073 WHERE mbe.sort_value >= public.naco_normalize($2)
2074 ORDER BY mbe.sort_value, mbe.value LIMIT 1;
2075 $p$ LANGUAGE SQL STABLE;
2077 CREATE OR REPLACE FUNCTION metabib.browse_pivot(
2080 ) RETURNS BIGINT AS $p$
2081 SELECT id FROM metabib.browse_entry
2083 metabib.browse_bib_pivot($1, $2),
2084 metabib.browse_authority_refs_pivot($1,$2) -- only look in 4xx, 5xx, 7xx of authority
2086 ORDER BY sort_value, value LIMIT 1;
2087 $p$ LANGUAGE SQL STABLE;
2090 CREATE OR REPLACE FUNCTION metabib.staged_browse(
2094 context_locations INT[],
2096 browse_superpage_size INT,
2097 count_up_from_zero BOOL, -- if false, count down from -1
2100 ) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$
2108 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
2109 results_skipped INT := 0;
2110 row_counter INT := 0;
2115 all_records BIGINT[];
2116 all_brecords BIGINT[];
2117 all_arecords BIGINT[];
2118 superpage_of_records BIGINT[];
2121 IF count_up_from_zero THEN
2127 OPEN curs FOR EXECUTE query;
2130 FETCH curs INTO rec;
2132 IF result_row.pivot_point IS NOT NULL THEN
2133 RETURN NEXT result_row;
2139 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
2140 SELECT INTO all_arecords, result_row.sees, afields
2141 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
2142 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
2143 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
2145 FROM metabib.browse_entry_simple_heading_map mbeshm
2146 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
2147 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
2148 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
2149 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
2150 ash.atag = map.authority_field
2151 AND map.metabib_field = ANY(fields)
2153 WHERE mbeshm.entry = rec.id;
2156 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
2157 SELECT INTO all_brecords, result_row.authorities, bfields
2158 ARRAY_AGG(DISTINCT source),
2159 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
2160 ARRAY_AGG(DISTINCT def)
2161 FROM metabib.browse_entry_def_map
2162 WHERE entry = rec.id
2163 AND def = ANY(fields);
2165 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
2167 result_row.sources := 0;
2168 result_row.asources := 0;
2170 -- Bib-linked vis checking
2171 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
2173 full_end := ARRAY_LENGTH(all_brecords, 1);
2174 superpage_size := COALESCE(browse_superpage_size, full_end);
2176 slice_end := superpage_size;
2178 WHILE result_row.sources = 0 AND slice_start <= full_end LOOP
2179 superpage_of_records := all_brecords[slice_start:slice_end];
2181 'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' ||
2182 '1::INT AS rel FROM (SELECT UNNEST(' ||
2183 quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr';
2185 -- We use search.query_parser_fts() for visibility testing.
2186 -- We're calling it once per browse-superpage worth of records
2187 -- out of the set of records related to a given mbe, until we've
2188 -- either exhausted that set of records or found at least 1
2191 SELECT INTO result_row.sources visible
2192 FROM search.query_parser_fts(
2193 context_org, NULL, qpfts_query, NULL,
2194 context_locations, 0, NULL, NULL, FALSE, staff, FALSE
2196 WHERE qpfts.rel IS NULL;
2198 slice_start := slice_start + superpage_size;
2199 slice_end := slice_end + superpage_size;
2202 -- Accurate? Well, probably.
2203 result_row.accurate := browse_superpage_size IS NULL OR
2204 browse_superpage_size >= full_end;
2208 -- Authority-linked vis checking
2209 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
2211 full_end := ARRAY_LENGTH(all_arecords, 1);
2212 superpage_size := COALESCE(browse_superpage_size, full_end);
2214 slice_end := superpage_size;
2216 WHILE result_row.asources = 0 AND slice_start <= full_end LOOP
2217 superpage_of_records := all_arecords[slice_start:slice_end];
2219 'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' ||
2220 '1::INT AS rel FROM (SELECT UNNEST(' ||
2221 quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr';
2223 -- We use search.query_parser_fts() for visibility testing.
2224 -- We're calling it once per browse-superpage worth of records
2225 -- out of the set of records related to a given mbe, via
2226 -- authority until we've either exhausted that set of records
2227 -- or found at least 1 visible record.
2229 SELECT INTO result_row.asources visible
2230 FROM search.query_parser_fts(
2231 context_org, NULL, qpfts_query, NULL,
2232 context_locations, 0, NULL, NULL, FALSE, staff, FALSE
2234 WHERE qpfts.rel IS NULL;
2236 slice_start := slice_start + superpage_size;
2237 slice_end := slice_end + superpage_size;
2241 -- Accurate? Well, probably.
2242 result_row.aaccurate := browse_superpage_size IS NULL OR
2243 browse_superpage_size >= full_end;
2247 IF result_row.sources > 0 OR result_row.asources > 0 THEN
2249 -- The function that calls this function needs row_number in order
2250 -- to correctly order results from two different runs of this
2252 result_row.row_number := row_number;
2254 -- Now, if row_counter is still less than limit, return a row. If
2255 -- not, but it is less than next_pivot_pos, continue on without
2256 -- returning actual result rows until we find
2257 -- that next pivot, and return it.
2259 IF row_counter < result_limit THEN
2260 result_row.browse_entry := rec.id;
2261 result_row.value := rec.value;
2263 RETURN NEXT result_row;
2265 result_row.browse_entry := NULL;
2266 result_row.authorities := NULL;
2267 result_row.fields := NULL;
2268 result_row.value := NULL;
2269 result_row.sources := NULL;
2270 result_row.sees := NULL;
2271 result_row.accurate := NULL;
2272 result_row.aaccurate := NULL;
2273 result_row.pivot_point := rec.id;
2275 IF row_counter >= next_pivot_pos THEN
2276 RETURN NEXT result_row;
2281 IF count_up_from_zero THEN
2282 row_number := row_number + 1;
2284 row_number := row_number - 1;
2287 -- row_counter is different from row_number.
2288 -- It simply counts up from zero so that we know when
2289 -- we've reached our limit.
2290 row_counter := row_counter + 1;
2294 $p$ LANGUAGE PLPGSQL;
2297 CREATE OR REPLACE FUNCTION metabib.browse(
2300 context_org INT DEFAULT NULL,
2301 context_loc_group INT DEFAULT NULL,
2302 staff BOOL DEFAULT FALSE,
2303 pivot_id BIGINT DEFAULT NULL,
2304 result_limit INT DEFAULT 10
2305 ) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$
2310 pivot_sort_value TEXT;
2311 pivot_sort_fallback TEXT;
2312 context_locations INT[];
2313 browse_superpage_size INT;
2314 results_skipped INT := 0;
2318 forward_to_pivot INT;
2320 -- First, find the pivot if we were given a browse term but not a pivot.
2321 IF pivot_id IS NULL THEN
2322 pivot_id := metabib.browse_pivot(search_field, browse_term);
2325 SELECT INTO pivot_sort_value, pivot_sort_fallback
2326 sort_value, value FROM metabib.browse_entry WHERE id = pivot_id;
2328 -- Bail if we couldn't find a pivot.
2329 IF pivot_sort_value IS NULL THEN
2333 -- Transform the context_loc_group argument (if any) (logc at the
2334 -- TPAC layer) into a form we'll be able to use.
2335 IF context_loc_group IS NOT NULL THEN
2336 SELECT INTO context_locations ARRAY_AGG(location)
2337 FROM asset.copy_location_group_map
2338 WHERE lgroup = context_loc_group;
2341 -- Get the configured size of browse superpages.
2342 SELECT INTO browse_superpage_size value -- NULL ok
2343 FROM config.global_flag
2344 WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit';
2346 -- First we're going to search backward from the pivot, then we're going
2347 -- to search forward. In each direction, we need two limits. At the
2348 -- lesser of the two limits, we delineate the edge of the result set
2349 -- we're going to return. At the greater of the two limits, we find the
2350 -- pivot value that would represent an offset from the current pivot
2351 -- at a distance of one "page" in either direction, where a "page" is a
2352 -- result set of the size specified in the "result_limit" argument.
2354 -- The two limits in each direction make four derived values in total,
2355 -- and we calculate them now.
2356 back_limit := CEIL(result_limit::FLOAT / 2);
2357 back_to_pivot := result_limit;
2358 forward_limit := result_limit / 2;
2359 forward_to_pivot := result_limit - 1;
2361 -- This is the meat of the SQL query that finds browse entries. We'll
2362 -- pass this to a function which uses it with a cursor, so that individual
2363 -- rows may be fetched in a loop until some condition is satisfied, without
2364 -- waiting for a result set of fixed size to be collected all at once.
2369 FROM metabib.browse_entry mbe
2371 EXISTS ( -- are there any bibs using this mbe via the requested fields?
2373 FROM metabib.browse_entry_def_map mbedm
2374 WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
2376 ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
2378 FROM metabib.browse_entry_simple_heading_map mbeshm
2379 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
2380 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
2381 ash.atag = map.authority_field
2382 AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
2384 WHERE mbeshm.entry = mbe.id
2388 -- This is the variant of the query for browsing backward.
2389 back_query := core_query ||
2390 ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) ||
2391 ' ORDER BY mbe.sort_value DESC, mbe.value DESC ';
2393 -- This variant browses forward.
2394 forward_query := core_query ||
2395 ' mbe.sort_value > ' || quote_literal(pivot_sort_value) ||
2396 ' ORDER BY mbe.sort_value, mbe.value ';
2398 -- We now call the function which applies a cursor to the provided
2399 -- queries, stopping at the appropriate limits and also giving us
2400 -- the next page's pivot.
2402 SELECT * FROM metabib.staged_browse(
2403 back_query, search_field, context_org, context_locations,
2404 staff, browse_superpage_size, TRUE, back_limit, back_to_pivot
2406 SELECT * FROM metabib.staged_browse(
2407 forward_query, search_field, context_org, context_locations,
2408 staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot
2409 ) ORDER BY row_number DESC;
2412 $p$ LANGUAGE PLPGSQL;
2414 CREATE OR REPLACE FUNCTION metabib.browse(
2417 context_org INT DEFAULT NULL,
2418 context_loc_group INT DEFAULT NULL,
2419 staff BOOL DEFAULT FALSE,
2420 pivot_id BIGINT DEFAULT NULL,
2421 result_limit INT DEFAULT 10
2422 ) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$
2424 RETURN QUERY SELECT * FROM metabib.browse(
2425 (SELECT COALESCE(ARRAY_AGG(id), ARRAY[]::INT[])
2426 FROM config.metabib_field WHERE field_class = search_class),
2435 $p$ LANGUAGE PLPGSQL;