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
 139         IF CARDINALITY(word_list) > 1 AND include_phrases THEN
 140             RETURN QUERY SELECT * FROM search.symspell_build_raw_entry(input, source_class, TRUE, prefix_length, maxED);
 141         END IF;
 142
 143         FOREACH word IN ARRAY word_list LOOP
 144             -- Skip words that have runs of 5 or more digits (I'm looking at you, ISxNs)
 145             CONTINUE WHEN CHARACTER_LENGTH(word) > 4 AND word ~ '\d{5,}';
 146             RETURN QUERY SELECT * FROM search.symspell_build_raw_entry(word, source_class, FALSE, prefix_length, maxED);
 147         END LOOP;
 148     END IF;
 149
 150     IF old_input IS NOT NULL THEN
 151         input := evergreen.lowercase(old_input);
 152
 153         FOR word IN SELECT x FROM search.symspell_parse_words_distinct(input) x LOOP
 154             -- similarly skip words that have 5 or more digits here to
 155             -- avoid adding erroneous prefix deletion entries to the dictionary
 156             CONTINUE WHEN CHARACTER_LENGTH(word) > 4 AND word ~ '\d{5,}';
 157             entry.prefix_key := word;
 158
 159             entry.keyword_count := 0;
 160             entry.title_count := 0;
 161             entry.author_count := 0;
 162             entry.subject_count := 0;
 163             entry.series_count := 0;
 164             entry.identifier_count := 0;
 165
 166             entry.keyword_suggestions := '{}';
 167             entry.title_suggestions := '{}';
 168             entry.author_suggestions := '{}';
 169             entry.subject_suggestions := '{}';
 170             entry.series_suggestions := '{}';
 171             entry.identifier_suggestions := '{}';
 172
 173             IF source_class = 'keyword' THEN entry.keyword_count := -1; END IF;
 174             IF source_class = 'title' THEN entry.title_count := -1; END IF;
 175             IF source_class = 'author' THEN entry.author_count := -1; END IF;
 176             IF source_class = 'subject' THEN entry.subject_count := -1; END IF;
 177             IF source_class = 'series' THEN entry.series_count := -1; END IF;
 178             IF source_class = 'identifier' THEN entry.identifier_count := -1; END IF;
 179
 180             RETURN NEXT entry;
 181         END LOOP;
 182     END IF;
 183 END;
 184 $F$ LANGUAGE PLPGSQL;
 185
 186 CREATE OR REPLACE FUNCTION search.symspell_build_and_merge_entries (
 187     full_input      TEXT,
 188     source_class    TEXT,
 189     old_input       TEXT DEFAULT NULL,
 190     include_phrases BOOL DEFAULT FALSE
 191 ) RETURNS SETOF search.symspell_dictionary AS $F$
 192 DECLARE
 193     new_entry       RECORD;
 194     conflict_entry  RECORD;
 195 BEGIN
 196
 197     IF full_input = old_input THEN -- neither NULL, and are the same
 198         RETURN;
 199     END IF;
 200
 201     FOR new_entry IN EXECUTE $q$
 202         SELECT  count,
 203                 prefix_key,
 204                 s AS suggestions
 205           FROM  (SELECT prefix_key,
 206                         ARRAY_AGG(DISTINCT $q$ || source_class || $q$_suggestions[1]) s,
 207                         SUM($q$ || source_class || $q$_count) count
 208                   FROM  search.symspell_build_entries($1, $2, $3, $4)
 209                   GROUP BY 1) x
 210         $q$ USING full_input, source_class, old_input, include_phrases
 211     LOOP
 212         EXECUTE $q$
 213             SELECT  prefix_key,
 214                     $q$ || source_class || $q$_suggestions suggestions,
 215                     $q$ || source_class || $q$_count count
 216               FROM  search.symspell_dictionary
 217               WHERE prefix_key = $1 $q$
 218             INTO conflict_entry
 219             USING new_entry.prefix_key;
 220
 221         IF new_entry.count <> 0 THEN -- Real word, and count changed
 222             IF conflict_entry.prefix_key IS NOT NULL THEN -- we'll be updating
 223                 IF conflict_entry.count > 0 THEN -- it's a real word
 224                     RETURN QUERY EXECUTE $q$
 225                         UPDATE  search.symspell_dictionary
 226                            SET  $q$ || source_class || $q$_count = $2
 227                           WHERE prefix_key = $1
 228                           RETURNING * $q$
 229                         USING new_entry.prefix_key, GREATEST(0, new_entry.count + conflict_entry.count);
 230                 ELSE -- it was a prefix key or delete-emptied word before
 231                     IF conflict_entry.suggestions @> new_entry.suggestions THEN -- already have all suggestions here...
 232                         RETURN QUERY EXECUTE $q$
 233                             UPDATE  search.symspell_dictionary
 234                                SET  $q$ || source_class || $q$_count = $2
 235                               WHERE prefix_key = $1
 236                               RETURNING * $q$
 237                             USING new_entry.prefix_key, GREATEST(0, new_entry.count);
 238                     ELSE -- new suggestion!
 239                         RETURN QUERY EXECUTE $q$
 240                             UPDATE  search.symspell_dictionary
 241                                SET  $q$ || source_class || $q$_count = $2,
 242                                     $q$ || source_class || $q$_suggestions = $3
 243                               WHERE prefix_key = $1
 244                               RETURNING * $q$
 245                             USING new_entry.prefix_key, GREATEST(0, new_entry.count), evergreen.text_array_merge_unique(conflict_entry.suggestions,new_entry.suggestions);
 246                     END IF;
 247                 END IF;
 248             ELSE
 249                 -- We keep the on-conflict clause just in case...
 250                 RETURN QUERY EXECUTE $q$
 251                     INSERT INTO search.symspell_dictionary AS d (
 252                         $q$ || source_class || $q$_count,
 253                         prefix_key,
 254                         $q$ || source_class || $q$_suggestions
 255                     ) VALUES ( $1, $2, $3 ) ON CONFLICT (prefix_key) DO
 256                         UPDATE SET  $q$ || source_class || $q$_count = d.$q$ || source_class || $q$_count + EXCLUDED.$q$ || source_class || $q$_count,
 257                                     $q$ || source_class || $q$_suggestions = evergreen.text_array_merge_unique(d.$q$ || source_class || $q$_suggestions, EXCLUDED.$q$ || source_class || $q$_suggestions)
 258                         RETURNING * $q$
 259                     USING new_entry.count, new_entry.prefix_key, new_entry.suggestions;
 260             END IF;
 261         ELSE -- key only, or no change
 262             IF conflict_entry.prefix_key IS NOT NULL THEN -- we'll be updating
 263                 IF NOT conflict_entry.suggestions @> new_entry.suggestions THEN -- There are new suggestions
 264                     RETURN QUERY EXECUTE $q$
 265                         UPDATE  search.symspell_dictionary
 266                            SET  $q$ || source_class || $q$_suggestions = $2
 267                           WHERE prefix_key = $1
 268                           RETURNING * $q$
 269                         USING new_entry.prefix_key, evergreen.text_array_merge_unique(conflict_entry.suggestions,new_entry.suggestions);
 270                 END IF;
 271             ELSE
 272                 RETURN QUERY EXECUTE $q$
 273                     INSERT INTO search.symspell_dictionary AS d (
 274                         $q$ || source_class || $q$_count,
 275                         prefix_key,
 276                         $q$ || source_class || $q$_suggestions
 277                     ) VALUES ( $1, $2, $3 ) ON CONFLICT (prefix_key) DO -- key exists, suggestions may be added due to this entry
 278                         UPDATE SET  $q$ || source_class || $q$_suggestions = evergreen.text_array_merge_unique(d.$q$ || source_class || $q$_suggestions, EXCLUDED.$q$ || source_class || $q$_suggestions)
 279                     RETURNING * $q$
 280                     USING new_entry.count, new_entry.prefix_key, new_entry.suggestions;
 281             END IF;
 282         END IF;
 283     END LOOP;
 284 END;
 285 $F$ LANGUAGE PLPGSQL;
 286
 287 COMMIT;
 288
 289 \qecho ''
 290 \qecho 'The following should be run at the end of the upgrade before any'
 291 \qecho 'reingest occurs.  Because new triggers are installed already,'
 292 \qecho 'updates to indexed strings will cause zero-count dictionary entries'
 293 \qecho 'to be recorded which will require updating every row again (or'
 294 \qecho 'starting from scratch) so best to do this before other batch'
 295 \qecho 'changes.  A later reingest that does not significantly change'
 296 \qecho 'indexed strings will /not/ cause table bloat here, and will be'
 297 \qecho 'as fast as normal.  A copy of the SQL in a ready-to-use, non-escaped'
 298 \qecho 'form is available inside a comment at the end of this upgrade sub-'
 299 \qecho 'script so you do not need to copy this comment from the psql ouptut.'
 300 \qecho ''
 301 \qecho '\\a'
 302 \qecho '\\t'
 303 \qecho ''
 304 \qecho '\\o title'
 305 \qecho 'select value from metabib.title_field_entry where source in (select id from biblio.record_entry where not deleted);'
 306 \qecho '\\o author'
 307 \qecho 'select value from metabib.author_field_entry where source in (select id from biblio.record_entry where not deleted);'
 308 \qecho '\\o subject'
 309 \qecho 'select value from metabib.subject_field_entry where source in (select id from biblio.record_entry where not deleted);'
 310 \qecho '\\o series'
 311 \qecho 'select value from metabib.series_field_entry where source in (select id from biblio.record_entry where not deleted);'
 312 \qecho '\\o identifier'
 313 \qecho 'select value from metabib.identifier_field_entry where source in (select id from biblio.record_entry where not deleted);'
 314 \qecho '\\o keyword'
 315 \qecho 'select value from metabib.keyword_field_entry where source in (select id from biblio.record_entry where not deleted);'
 316 \qecho ''
 317 \qecho '\\o'
 318 \qecho '\\a'
 319 \qecho '\\t'
 320 \qecho ''
 321 \qecho '// Then, at the command line:'
 322 \qecho ''
 323 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl title > title.sql'
 324 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl author > author.sql'
 325 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl subject > subject.sql'
 326 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl series > series.sql'
 327 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl identifier > identifier.sql'
 328 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl keyword > keyword.sql'
 329 \qecho ''
 330 \qecho '// And, back in psql'
 331 \qecho ''
 332 \qecho 'ALTER TABLE search.symspell_dictionary SET UNLOGGED;'
 333 \qecho 'TRUNCATE search.symspell_dictionary;'
 334 \qecho ''
 335 \qecho '\\i identifier.sql'
 336 \qecho '\\i author.sql'
 337 \qecho '\\i title.sql'
 338 \qecho '\\i subject.sql'
 339 \qecho '\\i series.sql'
 340 \qecho '\\i keyword.sql'
 341 \qecho ''
 342 \qecho 'CLUSTER search.symspell_dictionary USING symspell_dictionary_pkey;'
 343 \qecho 'REINDEX TABLE search.symspell_dictionary;'
 344 \qecho 'ALTER TABLE search.symspell_dictionary SET LOGGED;'
 345 \qecho 'VACUUM ANALYZE search.symspell_dictionary;'
 346 \qecho ''
 347 \qecho 'DROP TABLE search.symspell_dictionary_partial_title;'
 348 \qecho 'DROP TABLE search.symspell_dictionary_partial_author;'
 349 \qecho 'DROP TABLE search.symspell_dictionary_partial_subject;'
 350 \qecho 'DROP TABLE search.symspell_dictionary_partial_series;'
 351 \qecho 'DROP TABLE search.symspell_dictionary_partial_identifier;'
 352 \qecho 'DROP TABLE search.symspell_dictionary_partial_keyword;'
 353
 354 /* To run by hand:
 355
 356 \a
 357 \t
 358
 359 \o title
 360 select value from metabib.title_field_entry where source in (select id from biblio.record_entry where not deleted);
 361
 362 \o author
 363 select value from metabib.author_field_entry where source in (select id from biblio.record_entry where not deleted);
 364
 365 \o subject
 366 select value from metabib.subject_field_entry where source in (select id from biblio.record_entry where not deleted);
 367
 368 \o series
 369 select value from metabib.series_field_entry where source in (select id from biblio.record_entry where not deleted);
 370
 371 \o identifier
 372 select value from metabib.identifier_field_entry where source in (select id from biblio.record_entry where not deleted);
 373
 374 \o keyword
 375 select value from metabib.keyword_field_entry where source in (select id from biblio.record_entry where not deleted);
 376
 377 \o
 378 \a
 379 \t
 380
 381 // Then, at the command line:
 382
 383 $ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl title > title.sql
 384 $ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl author > author.sql
 385 $ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl subject > subject.sql
 386 $ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl series > series.sql
 387 $ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl identifier > identifier.sql
 388 $ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl keyword > keyword.sql
 389
 390 // To the extent your hardware allows, the above commands can be run in
 391 // in parallel, in different shells.  Each will use a full CPU, and RAM
 392 // may be a limiting resource, so keep an eye on that with `top`.
 393
 394
 395 // And, back in psql
 396
 397 ALTER TABLE search.symspell_dictionary SET UNLOGGED;
 398 TRUNCATE search.symspell_dictionary;
 399
 400 \i identifier.sql
 401 \i author.sql
 402 \i title.sql
 403 \i subject.sql
 404 \i series.sql
 405 \i keyword.sql
 406
 407 CLUSTER search.symspell_dictionary USING symspell_dictionary_pkey;
 408 REINDEX TABLE search.symspell_dictionary;
 409 ALTER TABLE search.symspell_dictionary SET LOGGED;
 410 VACUUM ANALYZE search.symspell_dictionary;
 411
 412 DROP TABLE search.symspell_dictionary_partial_title;
 413 DROP TABLE search.symspell_dictionary_partial_author;
 414 DROP TABLE search.symspell_dictionary_partial_subject;
 415 DROP TABLE search.symspell_dictionary_partial_series;
 416 DROP TABLE search.symspell_dictionary_partial_identifier;
 417 DROP TABLE search.symspell_dictionary_partial_keyword;
 418
 419 */
 420