3 SELECT evergreen.upgrade_deps_block_check('0800', :eg_version);
5 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$
8 ind_data metabib.field_entry_template%ROWTYPE;
9 mbe_row metabib.browse_entry%ROWTYPE;
16 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;
17 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;
18 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;
20 PERFORM * FROM config.internal_flag WHERE name = 'ingest.assume_inserts_only' AND enabled;
22 IF NOT b_skip_search THEN
23 FOR fclass IN SELECT * FROM config.metabib_class LOOP
24 -- RAISE NOTICE 'Emptying out %', fclass.name;
25 EXECUTE $$DELETE FROM metabib.$$ || fclass.name || $$_field_entry WHERE source = $$ || bib_id;
28 IF NOT b_skip_facet THEN
29 DELETE FROM metabib.facet_entry WHERE source = bib_id;
31 IF NOT b_skip_browse THEN
32 DELETE FROM metabib.browse_entry_def_map WHERE source = bib_id;
36 FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( bib_id ) LOOP
37 IF ind_data.field < 0 THEN
38 ind_data.field = -1 * ind_data.field;
41 IF ind_data.facet_field AND NOT b_skip_facet THEN
42 INSERT INTO metabib.facet_entry (field, source, value)
43 VALUES (ind_data.field, ind_data.source, ind_data.value);
46 IF ind_data.browse_field AND NOT b_skip_browse THEN
47 -- A caveat about this SELECT: this should take care of replacing
48 -- old mbe rows when data changes, but not if normalization (by
49 -- which I mean specifically the output of
50 -- evergreen.oils_tsearch2()) changes. It may or may not be
51 -- expensive to add a comparison of index_vector to index_vector
52 -- to the WHERE clause below.
53 SELECT INTO mbe_row * FROM metabib.browse_entry WHERE value = ind_data.value;
57 INSERT INTO metabib.browse_entry (value) VALUES
58 (metabib.browse_normalize(ind_data.value, ind_data.field));
59 mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS);
62 INSERT INTO metabib.browse_entry_def_map (entry, def, source)
63 VALUES (mbe_id, ind_data.field, ind_data.source);
66 -- Avoid inserting duplicate rows, but retain granularity of being
67 -- able to search browse fields with "starts with" type operators
68 -- (for example, for titles of songs in music albums)
69 IF (ind_data.search_field OR ind_data.browse_field) AND NOT b_skip_search THEN
70 EXECUTE 'SELECT 1 FROM metabib.' || ind_data.field_class ||
71 '_field_entry WHERE field = $1 AND source = $2 AND value = $3'
72 INTO mbe_id USING ind_data.field, ind_data.source, ind_data.value;
73 -- RAISE NOTICE 'Search for an already matching row returned %', mbe_id;
74 IF mbe_id IS NULL THEN
76 INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value)
78 quote_literal(ind_data.field) || $$, $$ ||
79 quote_literal(ind_data.source) || $$, $$ ||
80 quote_literal(ind_data.value) ||
87 IF NOT b_skip_search THEN
88 PERFORM metabib.update_combined_index_vectors(bib_id);
93 $func$ LANGUAGE PLPGSQL;
95 CREATE OR REPLACE FUNCTION public.oils_tsearch2 () RETURNS TRIGGER AS $$
99 temp_vector TEXT := '';
105 NEW.index_vector = ''::tsvector;
107 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
109 SELECT n.func AS func,
110 n.param_count AS param_count,
112 FROM config.index_normalizer n
113 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
114 WHERE field = NEW.field AND m.pos < 0
116 EXECUTE 'SELECT ' || normalizer.func || '(' ||
117 quote_literal( value ) ||
119 WHEN normalizer.param_count > 0
120 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
130 SELECT n.func AS func,
131 n.param_count AS param_count,
133 FROM config.index_normalizer n
134 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
135 WHERE field = NEW.field AND m.pos >= 0
137 EXECUTE 'SELECT ' || normalizer.func || '(' ||
138 quote_literal( value ) ||
140 WHEN normalizer.param_count > 0
141 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
149 IF TG_TABLE_NAME::TEXT ~ 'browse_entry$' THEN
150 value := ARRAY_TO_STRING(
151 evergreen.regexp_split_to_array(value, E'\\W+'), ' '
153 value := public.search_normalize(value);
154 NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);
155 ELSIF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
157 SELECT ts_config, index_weight
158 FROM config.metabib_class_ts_map
159 WHERE field_class = TG_ARGV[0]
160 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'))
161 AND always OR NOT EXISTS (SELECT 1 FROM config.metabib_field_ts_map WHERE metabib_field = NEW.field)
163 SELECT ts_config, index_weight
164 FROM config.metabib_field_ts_map
165 WHERE metabib_field = NEW.field
166 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'))
167 ORDER BY index_weight ASC
169 IF cur_weight IS NOT NULL AND cur_weight != ts_rec.index_weight THEN
170 NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
173 cur_weight = ts_rec.index_weight;
174 SELECT INTO temp_vector temp_vector || ' ' || to_tsvector(ts_rec.ts_config::regconfig, value)::TEXT;
176 NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
178 NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);
187 \qecho **** If upgrading from Evergreen 2.3 or before, now is the time to run
188 \qecho **** Open-ILS/src/sql/Pg/version-upgrade/2.3-2.4-supplemental.sh, which
189 \qecho **** contains additional required SQL to complete your Evergreen upgrade!
191 \qecho **** If upgrading from Evergreen 2.4.0, you will need to reingest your
192 \qecho **** full data set. In order to allow this to continue without locking
193 \qecho **** your entire bibliographic data set, consider generating an SQL script
194 \qecho **** with the following query, and running that via psql:
197 \qecho '\\o /tmp/reingest-2.4.1.sql'
198 \qecho 'SELECT ''select metabib.reingest_metabib_field_entries('' || id || '');'' FROM biblio.record_entry WHERE NOT DELETED AND id > 0;'