1 --Upgrade Script for 2.4.0 to 2.4.1
2 \set eg_version '''2.4.1'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.4.1', :eg_version);
6 SELECT evergreen.upgrade_deps_block_check('0800', :eg_version);
8 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$
11 ind_data metabib.field_entry_template%ROWTYPE;
12 mbe_row metabib.browse_entry%ROWTYPE;
19 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;
20 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;
21 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;
23 PERFORM * FROM config.internal_flag WHERE name = 'ingest.assume_inserts_only' AND enabled;
25 IF NOT b_skip_search THEN
26 FOR fclass IN SELECT * FROM config.metabib_class LOOP
27 -- RAISE NOTICE 'Emptying out %', fclass.name;
28 EXECUTE $$DELETE FROM metabib.$$ || fclass.name || $$_field_entry WHERE source = $$ || bib_id;
31 IF NOT b_skip_facet THEN
32 DELETE FROM metabib.facet_entry WHERE source = bib_id;
34 IF NOT b_skip_browse THEN
35 DELETE FROM metabib.browse_entry_def_map WHERE source = bib_id;
39 FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( bib_id ) LOOP
40 IF ind_data.field < 0 THEN
41 ind_data.field = -1 * ind_data.field;
44 IF ind_data.facet_field AND NOT b_skip_facet THEN
45 INSERT INTO metabib.facet_entry (field, source, value)
46 VALUES (ind_data.field, ind_data.source, ind_data.value);
49 IF ind_data.browse_field AND NOT b_skip_browse THEN
50 -- A caveat about this SELECT: this should take care of replacing
51 -- old mbe rows when data changes, but not if normalization (by
52 -- which I mean specifically the output of
53 -- evergreen.oils_tsearch2()) changes. It may or may not be
54 -- expensive to add a comparison of index_vector to index_vector
55 -- to the WHERE clause below.
56 SELECT INTO mbe_row * FROM metabib.browse_entry WHERE value = ind_data.value;
60 INSERT INTO metabib.browse_entry (value) VALUES
61 (metabib.browse_normalize(ind_data.value, ind_data.field));
62 mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS);
65 INSERT INTO metabib.browse_entry_def_map (entry, def, source)
66 VALUES (mbe_id, ind_data.field, ind_data.source);
69 -- Avoid inserting duplicate rows, but retain granularity of being
70 -- able to search browse fields with "starts with" type operators
71 -- (for example, for titles of songs in music albums)
72 IF (ind_data.search_field OR ind_data.browse_field) AND NOT b_skip_search THEN
73 EXECUTE 'SELECT 1 FROM metabib.' || ind_data.field_class ||
74 '_field_entry WHERE field = $1 AND source = $2 AND value = $3'
75 INTO mbe_id USING ind_data.field, ind_data.source, ind_data.value;
76 -- RAISE NOTICE 'Search for an already matching row returned %', mbe_id;
77 IF mbe_id IS NULL THEN
79 INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value)
81 quote_literal(ind_data.field) || $$, $$ ||
82 quote_literal(ind_data.source) || $$, $$ ||
83 quote_literal(ind_data.value) ||
90 IF NOT b_skip_search THEN
91 PERFORM metabib.update_combined_index_vectors(bib_id);
96 $func$ LANGUAGE PLPGSQL;
98 CREATE OR REPLACE FUNCTION public.oils_tsearch2 () RETURNS TRIGGER AS $$
102 temp_vector TEXT := '';
108 NEW.index_vector = ''::tsvector;
110 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
112 SELECT n.func AS func,
113 n.param_count AS param_count,
115 FROM config.index_normalizer n
116 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
117 WHERE field = NEW.field AND m.pos < 0
119 EXECUTE 'SELECT ' || normalizer.func || '(' ||
120 quote_literal( value ) ||
122 WHEN normalizer.param_count > 0
123 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
133 SELECT n.func AS func,
134 n.param_count AS param_count,
136 FROM config.index_normalizer n
137 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
138 WHERE field = NEW.field AND m.pos >= 0
140 EXECUTE 'SELECT ' || normalizer.func || '(' ||
141 quote_literal( value ) ||
143 WHEN normalizer.param_count > 0
144 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
152 IF TG_TABLE_NAME::TEXT ~ 'browse_entry$' THEN
153 value := ARRAY_TO_STRING(
154 evergreen.regexp_split_to_array(value, E'\\W+'), ' '
156 value := public.search_normalize(value);
157 NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);
158 ELSIF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
160 SELECT ts_config, index_weight
161 FROM config.metabib_class_ts_map
162 WHERE field_class = TG_ARGV[0]
163 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'))
164 AND always OR NOT EXISTS (SELECT 1 FROM config.metabib_field_ts_map WHERE metabib_field = NEW.field)
166 SELECT ts_config, index_weight
167 FROM config.metabib_field_ts_map
168 WHERE metabib_field = NEW.field
169 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'))
170 ORDER BY index_weight ASC
172 IF cur_weight IS NOT NULL AND cur_weight != ts_rec.index_weight THEN
173 NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
176 cur_weight = ts_rec.index_weight;
177 SELECT INTO temp_vector temp_vector || ' ' || to_tsvector(ts_rec.ts_config::regconfig, value)::TEXT;
179 NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
181 NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);
189 SELECT evergreen.upgrade_deps_block_check('0803', :eg_version);
191 UPDATE config.org_unit_setting_type
192 SET description = oils_i18n_gettext('circ.holds.default_shelf_expire_interval',
193 'The amount of time an item will be held on the shelf before the hold expires. For example: "2 weeks" or "5 days"',
194 'coust', 'description')
195 WHERE name = 'circ.holds.default_shelf_expire_interval';
198 SELECT evergreen.upgrade_deps_block_check('0804', :eg_version);
200 UPDATE config.coded_value_map
201 SET value = oils_i18n_gettext('169', 'Gwich''in', 'ccvm', 'value')
202 WHERE ctype = 'item_lang' AND code = 'gwi';
204 -- Evergreen DB patch XXXX.schema.usrname_index.sql
206 -- Create search index on actor.usr.usrname
209 SELECT evergreen.upgrade_deps_block_check('0808', :eg_version);
211 CREATE INDEX actor_usr_usrname_idx ON actor.usr (evergreen.lowercase(usrname));
214 -- check whether patch can be applied
215 SELECT evergreen.upgrade_deps_block_check('0810', :eg_version);
217 UPDATE authority.control_set_authority_field
218 SET name = REGEXP_REPLACE(name, '^See Also', 'See From')
219 WHERE tag LIKE '4__' AND control_set = 1;
222 -- check whether patch can be applied
223 SELECT evergreen.upgrade_deps_block_check('0811', :eg_version);
225 DROP FUNCTION action.copy_related_hold_stats(integer);
227 CREATE OR REPLACE FUNCTION action.copy_related_hold_stats(copy_id bigint)
228 RETURNS action.hold_stats AS
231 output action.hold_stats%ROWTYPE;
234 available_count INT := 0;
235 hold_map_data RECORD;
238 output.hold_count := 0;
239 output.copy_count := 0;
240 output.available_count := 0;
242 SELECT COUNT( DISTINCT m.hold ) INTO hold_count
243 FROM action.hold_copy_map m
244 JOIN action.hold_request h ON (m.hold = h.id)
245 WHERE m.target_copy = copy_id
248 output.hold_count := hold_count;
250 IF output.hold_count > 0 THEN
252 SELECT DISTINCT m.target_copy,
254 FROM action.hold_copy_map m
255 JOIN asset.copy acp ON (m.target_copy = acp.id)
256 JOIN action.hold_request h ON (m.hold = h.id)
257 WHERE m.hold IN ( SELECT DISTINCT hold FROM action.hold_copy_map WHERE target_copy = copy_id ) AND NOT h.frozen
259 output.copy_count := output.copy_count + 1;
260 IF hold_map_data.status IN (0,7,12) THEN
261 output.available_count := output.available_count + 1;
264 output.total_copy_ratio = output.copy_count::FLOAT / output.hold_count::FLOAT;
265 output.available_copy_ratio = output.available_count::FLOAT / output.hold_count::FLOAT;
273 LANGUAGE plpgsql VOLATILE
279 \qecho **** If upgrading from Evergreen 2.3 or before, now is the time to run
280 \qecho **** Open-ILS/src/sql/Pg/version-upgrade/2.3-2.4-supplemental.sh, which
281 \qecho **** contains additional required SQL to complete your Evergreen upgrade!
283 \qecho **** If upgrading from Evergreen 2.4.0, you will need to reingest your
284 \qecho **** full data set. In order to allow this to continue without locking
285 \qecho **** your entire bibliographic data set, consider generating an SQL script
286 \qecho **** with the following query, and running that via psql:
289 \qecho '\\o /tmp/reingest-2.4.1.sql'
290 \qecho 'SELECT ''select metabib.reingest_metabib_field_entries('' || id || '');'' FROM biblio.record_entry WHERE NOT DELETED AND id > 0;'