4 ALTER TABLE config.metabib_field
5 ADD COLUMN display_xpath TEXT,
6 ADD COLUMN display_field BOOL NOT NULL DEFAULT FALSE;
8 CREATE TABLE metabib.display_entry (
9 id BIGSERIAL PRIMARY KEY,
10 source BIGINT NOT NULL REFERENCES biblio.record_entry (id),
11 field INT NOT NULL REFERENCES config.metabib_field (id),
15 CREATE TABLE config.display_field_map (
16 name TEXT PRIMARY KEY,
17 field INTEGER REFERENCES config.metabib_field (id),
18 multi BOOLEAN DEFAULT FALSE
21 CREATE VIEW metabib.flat_display_entry AS
26 CASE WHEN cdfm.multi THEN
27 TO_JSON(ARRAY_AGG(value))
31 FROM metabib.display_entry mde
32 JOIN config.display_field_map cdfm ON (cdfm.field = mde.field)
35 CREATE INDEX metabib_display_entry_field_idx
36 ON metabib.display_entry (field);
37 CREATE INDEX metabib_display_entry_source_idx
38 ON metabib.display_entry (source);
40 CREATE OR REPLACE FUNCTION metabib.display_field_normalize_trigger ()
44 display_field_text TEXT;
46 display_field_text := NEW.value;
49 SELECT n.func AS func,
50 n.param_count AS param_count,
52 FROM config.index_normalizer n
53 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
54 WHERE m.field = NEW.field AND m.pos < 0
57 EXECUTE 'SELECT ' || normalizer.func || '(' ||
58 quote_literal( display_field_text ) ||
60 WHEN normalizer.param_count > 0
61 THEN ',' || REPLACE(REPLACE(BTRIM(
62 normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
65 ')' INTO display_field_text;
69 NEW.value = display_field_text;
75 CREATE TRIGGER display_field_normalize_tgr
76 BEFORE UPDATE OR INSERT ON metabib.display_entry
77 FOR EACH ROW EXECUTE PROCEDURE metabib.display_field_normalize_trigger();
79 CREATE OR REPLACE FUNCTION evergreen.display_field_force_nfc()
82 NEW.value := force_unicode_normal_form(NEW.value,'NFC');
87 CREATE TRIGGER display_field_force_nfc_tgr
88 BEFORE UPDATE OR INSERT ON metabib.display_entry
89 FOR EACH ROW EXECUTE PROCEDURE evergreen.display_field_force_nfc();
91 ALTER TYPE metabib.field_entry_template ADD ATTRIBUTE display_field BOOL;
93 CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry ( rid BIGINT, default_joiner TEXT ) RETURNS SETOF metabib.field_entry_template AS $func$
95 bib biblio.record_entry%ROWTYPE;
96 idx config.metabib_field%ROWTYPE;
97 xfrm config.xml_transform%ROWTYPE;
101 xml_node_list TEXT[];
108 joiner TEXT := default_joiner; -- XXX will index defs supply a joiner?
110 authority_link BIGINT;
111 output_row metabib.field_entry_template%ROWTYPE;
114 -- Start out with no field-use bools set
115 output_row.browse_field = FALSE;
116 output_row.facet_field = FALSE;
117 output_row.display_field = FALSE;
118 output_row.search_field = FALSE;
121 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
123 -- Loop over the indexing entries
124 FOR idx IN SELECT * FROM config.metabib_field ORDER BY format LOOP
126 joiner := COALESCE(idx.joiner, default_joiner);
128 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
130 -- See if we can skip the XSLT ... it's expensive
131 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
132 -- Can't skip the transform
133 IF xfrm.xslt <> '---' THEN
134 transformed_xml := oils_xslt_process(bib.marc,xfrm.xslt);
136 transformed_xml := bib.marc;
139 prev_xfrm := xfrm.name;
142 xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
145 FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP
146 CONTINUE WHEN xml_node !~ E'^\\s*<';
148 -- XXX much of this should be moved into oils_xpath_string...
149 curr_text := ARRAY_TO_STRING(evergreen.array_remove_item_by_value(evergreen.array_remove_item_by_value(
150 oils_xpath( '//text()', -- get the content of all the nodes within the main selected node
151 REGEXP_REPLACE( xml_node, E'\\s+', ' ', 'g' ) -- Translate adjacent whitespace to a single space
152 ), ' '), ''), -- throw away morally empty (bankrupt?) strings
156 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
158 IF raw_text IS NOT NULL THEN
159 raw_text := raw_text || joiner;
162 raw_text := COALESCE(raw_text,'') || curr_text;
164 -- autosuggest/metabib.browse_entry
165 IF idx.browse_field THEN
167 IF idx.browse_xpath IS NOT NULL AND idx.browse_xpath <> '' THEN
168 browse_text := oils_xpath_string( idx.browse_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
170 browse_text := curr_text;
173 IF idx.browse_sort_xpath IS NOT NULL AND
174 idx.browse_sort_xpath <> '' THEN
176 sort_value := oils_xpath_string(
177 idx.browse_sort_xpath, xml_node, joiner,
178 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
181 sort_value := browse_text;
184 output_row.field_class = idx.field_class;
185 output_row.field = idx.id;
186 output_row.source = rid;
187 output_row.value = BTRIM(REGEXP_REPLACE(browse_text, E'\\s+', ' ', 'g'));
188 output_row.sort_value :=
189 public.naco_normalize(sort_value);
191 output_row.authority := NULL;
193 IF idx.authority_xpath IS NOT NULL AND idx.authority_xpath <> '' THEN
194 authority_text := oils_xpath_string(
195 idx.authority_xpath, xml_node, joiner,
197 ARRAY[xfrm.prefix, xfrm.namespace_uri],
198 ARRAY['xlink','http://www.w3.org/1999/xlink']
202 IF authority_text ~ '^\d+$' THEN
203 authority_link := authority_text::BIGINT;
204 PERFORM * FROM authority.record_entry WHERE id = authority_link;
206 output_row.authority := authority_link;
212 output_row.browse_field = TRUE;
213 -- Returning browse rows with search_field = true for search+browse
214 -- configs allows us to retain granularity of being able to search
215 -- browse fields with "starts with" type operators (for example, for
216 -- titles of songs in music albums)
217 IF idx.search_field THEN
218 output_row.search_field = TRUE;
220 RETURN NEXT output_row;
221 output_row.browse_field = FALSE;
222 output_row.search_field = FALSE;
223 output_row.sort_value := NULL;
226 -- insert raw node text for faceting
227 IF idx.facet_field THEN
229 IF idx.facet_xpath IS NOT NULL AND idx.facet_xpath <> '' THEN
230 facet_text := oils_xpath_string( idx.facet_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
232 facet_text := curr_text;
235 output_row.field_class = idx.field_class;
236 output_row.field = -1 * idx.id;
237 output_row.source = rid;
238 output_row.value = BTRIM(REGEXP_REPLACE(facet_text, E'\\s+', ' ', 'g'));
240 output_row.facet_field = TRUE;
241 RETURN NEXT output_row;
242 output_row.facet_field = FALSE;
245 -- insert raw node text for display
246 IF idx.display_field THEN
248 IF idx.display_xpath IS NOT NULL AND idx.display_xpath <> '' THEN
249 display_text := oils_xpath_string( idx.display_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
251 display_text := curr_text;
254 output_row.field_class = idx.field_class;
255 output_row.field = -1 * idx.id;
256 output_row.source = rid;
257 output_row.value = BTRIM(REGEXP_REPLACE(display_text, E'\\s+', ' ', 'g'));
259 output_row.display_field = TRUE;
260 RETURN NEXT output_row;
261 output_row.display_field = FALSE;
266 CONTINUE WHEN raw_text IS NULL OR raw_text = '';
268 -- insert combined node text for searching
269 IF idx.search_field THEN
270 output_row.field_class = idx.field_class;
271 output_row.field = idx.id;
272 output_row.source = rid;
273 output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'g'));
275 output_row.search_field = TRUE;
276 RETURN NEXT output_row;
277 output_row.search_field = FALSE;
284 $func$ LANGUAGE PLPGSQL;
286 DROP FUNCTION metabib.reingest_metabib_field_entries(BIGINT, BOOL, BOOL, BOOL);
288 CREATE OR REPLACE FUNCTION metabib.reingest_metabib_field_entries(
289 bib_id BIGINT, skip_facet BOOL DEFAULT FALSE,
290 skip_display BOOL DEFAULT FALSE, skip_browse BOOL DEFAULT FALSE,
291 skip_search BOOL DEFAULT FALSE ) RETURNS VOID AS $func$
294 ind_data metabib.field_entry_template%ROWTYPE;
295 mbe_row metabib.browse_entry%ROWTYPE;
304 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;
305 SELECT COALESCE(NULLIF(skip_display, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_display_indexing' AND enabled)) INTO b_skip_display;
306 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;
307 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;
309 PERFORM * FROM config.internal_flag WHERE name = 'ingest.assume_inserts_only' AND enabled;
311 IF NOT b_skip_search THEN
312 FOR fclass IN SELECT * FROM config.metabib_class LOOP
313 -- RAISE NOTICE 'Emptying out %', fclass.name;
314 EXECUTE $$DELETE FROM metabib.$$ || fclass.name || $$_field_entry WHERE source = $$ || bib_id;
317 IF NOT b_skip_facet THEN
318 DELETE FROM metabib.facet_entry WHERE source = bib_id;
320 IF NOT b_skip_display THEN
321 DELETE FROM metabib.display_entry WHERE source = bib_id;
323 IF NOT b_skip_browse THEN
324 DELETE FROM metabib.browse_entry_def_map WHERE source = bib_id;
328 FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( bib_id ) LOOP
330 -- don't store what has been normalized away
331 CONTINUE WHEN ind_data.value IS NULL;
333 IF ind_data.field < 0 THEN
334 ind_data.field = -1 * ind_data.field;
337 IF ind_data.facet_field AND NOT b_skip_facet THEN
338 INSERT INTO metabib.facet_entry (field, source, value)
339 VALUES (ind_data.field, ind_data.source, ind_data.value);
342 IF ind_data.display_field AND NOT b_skip_display THEN
343 INSERT INTO metabib.display_entry (field, source, value)
344 VALUES (ind_data.field, ind_data.source, ind_data.value);
348 IF ind_data.browse_field AND NOT b_skip_browse THEN
349 -- A caveat about this SELECT: this should take care of replacing
350 -- old mbe rows when data changes, but not if normalization (by
351 -- which I mean specifically the output of
352 -- evergreen.oils_tsearch2()) changes. It may or may not be
353 -- expensive to add a comparison of index_vector to index_vector
354 -- to the WHERE clause below.
356 CONTINUE WHEN ind_data.sort_value IS NULL;
358 value_prepped := metabib.browse_normalize(ind_data.value, ind_data.field);
359 SELECT INTO mbe_row * FROM metabib.browse_entry
360 WHERE value = value_prepped AND sort_value = ind_data.sort_value;
363 mbe_id := mbe_row.id;
365 INSERT INTO metabib.browse_entry
366 ( value, sort_value ) VALUES
367 ( value_prepped, ind_data.sort_value );
369 mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS);
372 INSERT INTO metabib.browse_entry_def_map (entry, def, source, authority)
373 VALUES (mbe_id, ind_data.field, ind_data.source, ind_data.authority);
376 IF ind_data.search_field AND NOT b_skip_search THEN
377 -- Avoid inserting duplicate rows
378 EXECUTE 'SELECT 1 FROM metabib.' || ind_data.field_class ||
379 '_field_entry WHERE field = $1 AND source = $2 AND value = $3'
380 INTO mbe_id USING ind_data.field, ind_data.source, ind_data.value;
381 -- RAISE NOTICE 'Search for an already matching row returned %', mbe_id;
382 IF mbe_id IS NULL THEN
384 INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value)
386 quote_literal(ind_data.field) || $$, $$ ||
387 quote_literal(ind_data.source) || $$, $$ ||
388 quote_literal(ind_data.value) ||
395 IF NOT b_skip_search THEN
396 PERFORM metabib.update_combined_index_vectors(bib_id);
401 $func$ LANGUAGE PLPGSQL;