LP#1947173: Clean up bad cataloging pot hole
[Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / XXXX.schema.symspell-speed-ingest.sql
1 BEGIN;
2
3 SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
4
5 -- We don't pass this function arrays with nulls, so we save 5% not testing for that
6 CREATE OR REPLACE FUNCTION evergreen.text_array_merge_unique (
7     TEXT[], TEXT[]
8 ) RETURNS TEXT[] AS $F$
9     SELECT NULLIF(ARRAY(
10         SELECT * FROM UNNEST($1) x
11             UNION
12         SELECT * FROM UNNEST($2) y
13     ),'{}');
14 $F$ LANGUAGE SQL;
15
16 CREATE OR REPLACE FUNCTION search.symspell_build_raw_entry (
17     raw_input       TEXT,
18     source_class    TEXT,
19     no_limit        BOOL DEFAULT FALSE,
20     prefix_length   INT DEFAULT 6,
21     maxED           INT DEFAULT 3
22 ) RETURNS SETOF search.symspell_dictionary AS $F$
23 DECLARE
24     key         TEXT;
25     del_key     TEXT;
26     key_list    TEXT[];
27     entry       search.symspell_dictionary%ROWTYPE;
28 BEGIN
29     key := raw_input;
30
31     IF NOT no_limit AND CHARACTER_LENGTH(raw_input) > prefix_length THEN
32         key := SUBSTRING(key FROM 1 FOR prefix_length);
33         key_list := ARRAY[raw_input, key];
34     ELSE
35         key_list := ARRAY[key];
36     END IF;
37
38     FOREACH del_key IN ARRAY key_list LOOP
39         -- skip empty keys
40         CONTINUE WHEN del_key IS NULL OR CHARACTER_LENGTH(del_key) = 0;
41
42         entry.prefix_key := del_key;
43
44         entry.keyword_count := 0;
45         entry.title_count := 0;
46         entry.author_count := 0;
47         entry.subject_count := 0;
48         entry.series_count := 0;
49         entry.identifier_count := 0;
50
51         entry.keyword_suggestions := '{}';
52         entry.title_suggestions := '{}';
53         entry.author_suggestions := '{}';
54         entry.subject_suggestions := '{}';
55         entry.series_suggestions := '{}';
56         entry.identifier_suggestions := '{}';
57
58         IF source_class = 'keyword' THEN entry.keyword_suggestions := ARRAY[raw_input]; END IF;
59         IF source_class = 'title' THEN entry.title_suggestions := ARRAY[raw_input]; END IF;
60         IF source_class = 'author' THEN entry.author_suggestions := ARRAY[raw_input]; END IF;
61         IF source_class = 'subject' THEN entry.subject_suggestions := ARRAY[raw_input]; END IF;
62         IF source_class = 'series' THEN entry.series_suggestions := ARRAY[raw_input]; END IF;
63         IF source_class = 'identifier' THEN entry.identifier_suggestions := ARRAY[raw_input]; END IF;
64         IF source_class = 'keyword' THEN entry.keyword_suggestions := ARRAY[raw_input]; END IF;
65
66         IF del_key = raw_input THEN
67             IF source_class = 'keyword' THEN entry.keyword_count := 1; END IF;
68             IF source_class = 'title' THEN entry.title_count := 1; END IF;
69             IF source_class = 'author' THEN entry.author_count := 1; END IF;
70             IF source_class = 'subject' THEN entry.subject_count := 1; END IF;
71             IF source_class = 'series' THEN entry.series_count := 1; END IF;
72             IF source_class = 'identifier' THEN entry.identifier_count := 1; END IF;
73         END IF;
74
75         RETURN NEXT entry;
76     END LOOP;
77
78     FOR del_key IN SELECT x FROM UNNEST(search.symspell_generate_edits(key, 1, maxED)) x LOOP
79
80         -- skip empty keys
81         CONTINUE WHEN del_key IS NULL OR CHARACTER_LENGTH(del_key) = 0;
82         -- skip suggestions that are already too long for the prefix key
83         CONTINUE WHEN CHARACTER_LENGTH(del_key) <= (prefix_length - maxED) AND CHARACTER_LENGTH(raw_input) > prefix_length;
84
85         entry.keyword_suggestions := '{}';
86         entry.title_suggestions := '{}';
87         entry.author_suggestions := '{}';
88         entry.subject_suggestions := '{}';
89         entry.series_suggestions := '{}';
90         entry.identifier_suggestions := '{}';
91
92         IF source_class = 'keyword' THEN entry.keyword_count := 0; END IF;
93         IF source_class = 'title' THEN entry.title_count := 0; END IF;
94         IF source_class = 'author' THEN entry.author_count := 0; END IF;
95         IF source_class = 'subject' THEN entry.subject_count := 0; END IF;
96         IF source_class = 'series' THEN entry.series_count := 0; END IF;
97         IF source_class = 'identifier' THEN entry.identifier_count := 0; END IF;
98
99         entry.prefix_key := del_key;
100
101         IF source_class = 'keyword' THEN entry.keyword_suggestions := ARRAY[raw_input]; END IF;
102         IF source_class = 'title' THEN entry.title_suggestions := ARRAY[raw_input]; END IF;
103         IF source_class = 'author' THEN entry.author_suggestions := ARRAY[raw_input]; END IF;
104         IF source_class = 'subject' THEN entry.subject_suggestions := ARRAY[raw_input]; END IF;
105         IF source_class = 'series' THEN entry.series_suggestions := ARRAY[raw_input]; END IF;
106         IF source_class = 'identifier' THEN entry.identifier_suggestions := ARRAY[raw_input]; END IF;
107         IF source_class = 'keyword' THEN entry.keyword_suggestions := ARRAY[raw_input]; END IF;
108
109         RETURN NEXT entry;
110     END LOOP;
111
112 END;
113 $F$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
114
115 CREATE OR REPLACE FUNCTION search.symspell_build_entries (
116     full_input      TEXT,
117     source_class    TEXT,
118     old_input       TEXT DEFAULT NULL,
119     include_phrases BOOL DEFAULT FALSE
120 ) RETURNS SETOF search.symspell_dictionary AS $F$
121 DECLARE
122     prefix_length   INT;
123     maxED           INT;
124     word_list   TEXT[];
125     input       TEXT;
126     word        TEXT;
127     entry       search.symspell_dictionary;
128 BEGIN
129     IF full_input IS NOT NULL THEN
130         SELECT value::INT INTO prefix_length FROM config.internal_flag WHERE name = 'symspell.prefix_length' AND enabled;
131         prefix_length := COALESCE(prefix_length, 6);
132
133         SELECT value::INT INTO maxED FROM config.internal_flag WHERE name = 'symspell.max_edit_distance' AND enabled;
134         maxED := COALESCE(maxED, 3);
135
136         input := evergreen.lowercase(full_input);
137         word_list := ARRAY_AGG(x) FROM search.symspell_parse_words_distinct(input) x;
138         IF word_list IS NULL THEN
139             RETURN;
140         END IF;
141     
142         IF CARDINALITY(word_list) > 1 AND include_phrases THEN
143             RETURN QUERY SELECT * FROM search.symspell_build_raw_entry(input, source_class, TRUE, prefix_length, maxED);
144         END IF;
145
146         FOREACH word IN ARRAY word_list LOOP
147             -- Skip words that have runs of 5 or more digits (I'm looking at you, ISxNs)
148             CONTINUE WHEN CHARACTER_LENGTH(word) > 4 AND word ~ '\d{5,}';
149             RETURN QUERY SELECT * FROM search.symspell_build_raw_entry(word, source_class, FALSE, prefix_length, maxED);
150         END LOOP;
151     END IF;
152
153     IF old_input IS NOT NULL THEN
154         input := evergreen.lowercase(old_input);
155
156         FOR word IN SELECT x FROM search.symspell_parse_words_distinct(input) x LOOP
157             -- similarly skip words that have 5 or more digits here to
158             -- avoid adding erroneous prefix deletion entries to the dictionary
159             CONTINUE WHEN CHARACTER_LENGTH(word) > 4 AND word ~ '\d{5,}';
160             entry.prefix_key := word;
161
162             entry.keyword_count := 0;
163             entry.title_count := 0;
164             entry.author_count := 0;
165             entry.subject_count := 0;
166             entry.series_count := 0;
167             entry.identifier_count := 0;
168
169             entry.keyword_suggestions := '{}';
170             entry.title_suggestions := '{}';
171             entry.author_suggestions := '{}';
172             entry.subject_suggestions := '{}';
173             entry.series_suggestions := '{}';
174             entry.identifier_suggestions := '{}';
175
176             IF source_class = 'keyword' THEN entry.keyword_count := -1; END IF;
177             IF source_class = 'title' THEN entry.title_count := -1; END IF;
178             IF source_class = 'author' THEN entry.author_count := -1; END IF;
179             IF source_class = 'subject' THEN entry.subject_count := -1; END IF;
180             IF source_class = 'series' THEN entry.series_count := -1; END IF;
181             IF source_class = 'identifier' THEN entry.identifier_count := -1; END IF;
182
183             RETURN NEXT entry;
184         END LOOP;
185     END IF;
186 END;
187 $F$ LANGUAGE PLPGSQL;
188
189 CREATE OR REPLACE FUNCTION search.symspell_build_and_merge_entries (
190     full_input      TEXT,
191     source_class    TEXT,
192     old_input       TEXT DEFAULT NULL,
193     include_phrases BOOL DEFAULT FALSE
194 ) RETURNS SETOF search.symspell_dictionary AS $F$
195 DECLARE
196     new_entry       RECORD;
197     conflict_entry  RECORD;
198 BEGIN
199
200     IF full_input = old_input THEN -- neither NULL, and are the same
201         RETURN;
202     END IF;
203
204     FOR new_entry IN EXECUTE $q$
205         SELECT  count,
206                 prefix_key,
207                 s AS suggestions
208           FROM  (SELECT prefix_key,
209                         ARRAY_AGG(DISTINCT $q$ || source_class || $q$_suggestions[1]) s,
210                         SUM($q$ || source_class || $q$_count) count
211                   FROM  search.symspell_build_entries($1, $2, $3, $4)
212                   GROUP BY 1) x
213         $q$ USING full_input, source_class, old_input, include_phrases
214     LOOP
215         EXECUTE $q$
216             SELECT  prefix_key,
217                     $q$ || source_class || $q$_suggestions suggestions,
218                     $q$ || source_class || $q$_count count
219               FROM  search.symspell_dictionary
220               WHERE prefix_key = $1 $q$
221             INTO conflict_entry
222             USING new_entry.prefix_key;
223
224         IF new_entry.count <> 0 THEN -- Real word, and count changed
225             IF conflict_entry.prefix_key IS NOT NULL THEN -- we'll be updating
226                 IF conflict_entry.count > 0 THEN -- it's a real word
227                     RETURN QUERY EXECUTE $q$
228                         UPDATE  search.symspell_dictionary
229                            SET  $q$ || source_class || $q$_count = $2
230                           WHERE prefix_key = $1
231                           RETURNING * $q$
232                         USING new_entry.prefix_key, GREATEST(0, new_entry.count + conflict_entry.count);
233                 ELSE -- it was a prefix key or delete-emptied word before
234                     IF conflict_entry.suggestions @> new_entry.suggestions THEN -- already have all suggestions here...
235                         RETURN QUERY EXECUTE $q$
236                             UPDATE  search.symspell_dictionary
237                                SET  $q$ || source_class || $q$_count = $2
238                               WHERE prefix_key = $1
239                               RETURNING * $q$
240                             USING new_entry.prefix_key, GREATEST(0, new_entry.count);
241                     ELSE -- new suggestion!
242                         RETURN QUERY EXECUTE $q$
243                             UPDATE  search.symspell_dictionary
244                                SET  $q$ || source_class || $q$_count = $2,
245                                     $q$ || source_class || $q$_suggestions = $3
246                               WHERE prefix_key = $1
247                               RETURNING * $q$
248                             USING new_entry.prefix_key, GREATEST(0, new_entry.count), evergreen.text_array_merge_unique(conflict_entry.suggestions,new_entry.suggestions);
249                     END IF;
250                 END IF;
251             ELSE
252                 -- We keep the on-conflict clause just in case...
253                 RETURN QUERY EXECUTE $q$
254                     INSERT INTO search.symspell_dictionary AS d (
255                         $q$ || source_class || $q$_count,
256                         prefix_key,
257                         $q$ || source_class || $q$_suggestions
258                     ) VALUES ( $1, $2, $3 ) ON CONFLICT (prefix_key) DO
259                         UPDATE SET  $q$ || source_class || $q$_count = d.$q$ || source_class || $q$_count + EXCLUDED.$q$ || source_class || $q$_count,
260                                     $q$ || source_class || $q$_suggestions = evergreen.text_array_merge_unique(d.$q$ || source_class || $q$_suggestions, EXCLUDED.$q$ || source_class || $q$_suggestions)
261                         RETURNING * $q$
262                     USING new_entry.count, new_entry.prefix_key, new_entry.suggestions;
263             END IF;
264         ELSE -- key only, or no change
265             IF conflict_entry.prefix_key IS NOT NULL THEN -- we'll be updating
266                 IF NOT conflict_entry.suggestions @> new_entry.suggestions THEN -- There are new suggestions
267                     RETURN QUERY EXECUTE $q$
268                         UPDATE  search.symspell_dictionary
269                            SET  $q$ || source_class || $q$_suggestions = $2
270                           WHERE prefix_key = $1
271                           RETURNING * $q$
272                         USING new_entry.prefix_key, evergreen.text_array_merge_unique(conflict_entry.suggestions,new_entry.suggestions);
273                 END IF;
274             ELSE
275                 RETURN QUERY EXECUTE $q$
276                     INSERT INTO search.symspell_dictionary AS d (
277                         $q$ || source_class || $q$_count,
278                         prefix_key,
279                         $q$ || source_class || $q$_suggestions
280                     ) VALUES ( $1, $2, $3 ) ON CONFLICT (prefix_key) DO -- key exists, suggestions may be added due to this entry
281                         UPDATE SET  $q$ || source_class || $q$_suggestions = evergreen.text_array_merge_unique(d.$q$ || source_class || $q$_suggestions, EXCLUDED.$q$ || source_class || $q$_suggestions)
282                     RETURNING * $q$
283                     USING new_entry.count, new_entry.prefix_key, new_entry.suggestions;
284             END IF;
285         END IF;
286     END LOOP;
287 END;
288 $F$ LANGUAGE PLPGSQL;
289
290 COMMIT;
291
292 \qecho ''
293 \qecho 'The following should be run at the end of the upgrade before any'
294 \qecho 'reingest occurs.  Because new triggers are installed already,'
295 \qecho 'updates to indexed strings will cause zero-count dictionary entries'
296 \qecho 'to be recorded which will require updating every row again (or'
297 \qecho 'starting from scratch) so best to do this before other batch'
298 \qecho 'changes.  A later reingest that does not significantly change'
299 \qecho 'indexed strings will /not/ cause table bloat here, and will be'
300 \qecho 'as fast as normal.  A copy of the SQL in a ready-to-use, non-escaped'
301 \qecho 'form is available inside a comment at the end of this upgrade sub-'
302 \qecho 'script so you do not need to copy this comment from the psql ouptut.'
303 \qecho ''
304 \qecho '\\a'
305 \qecho '\\t'
306 \qecho ''
307 \qecho '\\o title'
308 \qecho 'select value from metabib.title_field_entry where source in (select id from biblio.record_entry where not deleted);'
309 \qecho '\\o author'
310 \qecho 'select value from metabib.author_field_entry where source in (select id from biblio.record_entry where not deleted);'
311 \qecho '\\o subject'
312 \qecho 'select value from metabib.subject_field_entry where source in (select id from biblio.record_entry where not deleted);'
313 \qecho '\\o series'
314 \qecho 'select value from metabib.series_field_entry where source in (select id from biblio.record_entry where not deleted);'
315 \qecho '\\o identifier'
316 \qecho 'select value from metabib.identifier_field_entry where source in (select id from biblio.record_entry where not deleted);'
317 \qecho '\\o keyword'
318 \qecho 'select value from metabib.keyword_field_entry where source in (select id from biblio.record_entry where not deleted);'
319 \qecho ''
320 \qecho '\\o'
321 \qecho '\\a'
322 \qecho '\\t'
323 \qecho ''
324 \qecho '// Then, at the command line:'
325 \qecho ''
326 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl title > title.sql'
327 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl author > author.sql'
328 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl subject > subject.sql'
329 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl series > series.sql'
330 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl identifier > identifier.sql'
331 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl keyword > keyword.sql'
332 \qecho ''
333 \qecho '// And, back in psql'
334 \qecho ''
335 \qecho 'ALTER TABLE search.symspell_dictionary SET UNLOGGED;'
336 \qecho 'TRUNCATE search.symspell_dictionary;'
337 \qecho ''
338 \qecho '\\i identifier.sql'
339 \qecho '\\i author.sql'
340 \qecho '\\i title.sql'
341 \qecho '\\i subject.sql'
342 \qecho '\\i series.sql'
343 \qecho '\\i keyword.sql'
344 \qecho ''
345 \qecho 'CLUSTER search.symspell_dictionary USING symspell_dictionary_pkey;'
346 \qecho 'REINDEX TABLE search.symspell_dictionary;'
347 \qecho 'ALTER TABLE search.symspell_dictionary SET LOGGED;'
348 \qecho 'VACUUM ANALYZE search.symspell_dictionary;'
349 \qecho ''
350 \qecho 'DROP TABLE search.symspell_dictionary_partial_title;'
351 \qecho 'DROP TABLE search.symspell_dictionary_partial_author;'
352 \qecho 'DROP TABLE search.symspell_dictionary_partial_subject;'
353 \qecho 'DROP TABLE search.symspell_dictionary_partial_series;'
354 \qecho 'DROP TABLE search.symspell_dictionary_partial_identifier;'
355 \qecho 'DROP TABLE search.symspell_dictionary_partial_keyword;'
356
357 /* To run by hand:
358
359 \a
360 \t
361
362 \o title
363 select value from metabib.title_field_entry where source in (select id from biblio.record_entry where not deleted);
364
365 \o author
366 select value from metabib.author_field_entry where source in (select id from biblio.record_entry where not deleted);
367
368 \o subject
369 select value from metabib.subject_field_entry where source in (select id from biblio.record_entry where not deleted);
370
371 \o series
372 select value from metabib.series_field_entry where source in (select id from biblio.record_entry where not deleted);
373
374 \o identifier
375 select value from metabib.identifier_field_entry where source in (select id from biblio.record_entry where not deleted);
376
377 \o keyword
378 select value from metabib.keyword_field_entry where source in (select id from biblio.record_entry where not deleted);
379
380 \o
381 \a
382 \t
383
384 // Then, at the command line:
385
386 $ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl title > title.sql
387 $ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl author > author.sql
388 $ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl subject > subject.sql
389 $ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl series > series.sql
390 $ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl identifier > identifier.sql
391 $ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl keyword > keyword.sql
392
393 // To the extent your hardware allows, the above commands can be run in 
394 // in parallel, in different shells.  Each will use a full CPU, and RAM
395 // may be a limiting resource, so keep an eye on that with `top`.
396
397
398 // And, back in psql
399
400 ALTER TABLE search.symspell_dictionary SET UNLOGGED;
401 TRUNCATE search.symspell_dictionary;
402
403 \i identifier.sql
404 \i author.sql
405 \i title.sql
406 \i subject.sql
407 \i series.sql
408 \i keyword.sql
409
410 CLUSTER search.symspell_dictionary USING symspell_dictionary_pkey;
411 REINDEX TABLE search.symspell_dictionary;
412 ALTER TABLE search.symspell_dictionary SET LOGGED;
413 VACUUM ANALYZE search.symspell_dictionary;
414
415 DROP TABLE search.symspell_dictionary_partial_title;
416 DROP TABLE search.symspell_dictionary_partial_author;
417 DROP TABLE search.symspell_dictionary_partial_subject;
418 DROP TABLE search.symspell_dictionary_partial_series;
419 DROP TABLE search.symspell_dictionary_partial_identifier;
420 DROP TABLE search.symspell_dictionary_partial_keyword;
421
422 */
423