Open-ILS/src/sql/Pg/300.schema.staged_search.sql

   1 /*
   2  * Copyright (C) 2007-2010  Equinox Software, Inc.
   3  * Mike Rylander <miker@esilibrary.com>
   4  *
   5  * This program is free software; you can redistribute it and/or
   6  * modify it under the terms of the GNU General Public License
   7  * as published by the Free Software Foundation; either version 2
   8  * of the License, or (at your option) any later version.
   9  *
  10  * This program is distributed in the hope that it will be useful,
  11  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  12  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13  * GNU General Public License for more details.
  14  *
  15  */
  16
  17
  18 DROP SCHEMA IF EXISTS search CASCADE;
  19
  20 BEGIN;
  21
  22 CREATE SCHEMA search;
  23
  24 CREATE OR REPLACE FUNCTION evergreen.pg_statistics (tab TEXT, col TEXT) RETURNS TABLE(element TEXT, frequency INT) AS $$
  25 BEGIN
  26     -- This query will die on PG < 9.2, but the function can be created. We just won't use it where we can't.
  27     RETURN QUERY
  28         SELECT  e,
  29                 f
  30           FROM  (SELECT ROW_NUMBER() OVER (),
  31                         (f * 100)::INT AS f
  32                   FROM  (SELECT UNNEST(most_common_elem_freqs) AS f
  33                           FROM  pg_stats
  34                           WHERE tablename = tab
  35                                 AND attname = col
  36                         )x
  37                 ) AS f
  38                 JOIN (SELECT ROW_NUMBER() OVER (),
  39                              e
  40                        FROM (SELECT UNNEST(most_common_elems::text::text[]) AS e
  41                               FROM  pg_stats
  42                               WHERE tablename = tab
  43                                     AND attname = col
  44                             )y
  45                 ) AS elems USING (row_number);
  46 END;
  47 $$ LANGUAGE PLPGSQL;
  48
  49 CREATE OR REPLACE FUNCTION evergreen.query_int_wrapper (INT[],TEXT) RETURNS BOOL AS $$
  50 BEGIN
  51     RETURN $1 @@ $2::query_int;
  52 END;
  53 $$ LANGUAGE PLPGSQL STABLE;
  54
  55 CREATE TABLE search.relevance_adjustment (
  56     id          SERIAL  PRIMARY KEY,
  57     active      BOOL    NOT NULL DEFAULT TRUE,
  58     field       INT     NOT NULL REFERENCES config.metabib_field (id) DEFERRABLE INITIALLY DEFERRED,
  59     bump_type   TEXT    NOT NULL CHECK (bump_type IN ('word_order','first_word','full_match')),
  60     multiplier  NUMERIC NOT NULL DEFAULT 1.0
  61 );
  62 CREATE UNIQUE INDEX bump_once_per_field_idx ON search.relevance_adjustment ( field, bump_type );
  63
  64 CREATE OR REPLACE FUNCTION search.facets_for_record_set(ignore_facet_classes text[], hits bigint[]) RETURNS TABLE(id integer, value text, count bigint)
  65 AS $f$
  66     SELECT id, value, count
  67       FROM (
  68         SELECT  mfae.field AS id,
  69                 mfae.value,
  70                 COUNT(DISTINCT mfae.source),
  71                 row_number() OVER (
  72                     PARTITION BY mfae.field ORDER BY COUNT(DISTINCT mfae.source) DESC
  73                 ) AS rownum
  74           FROM  metabib.facet_entry mfae
  75                 JOIN config.metabib_field cmf ON (cmf.id = mfae.field)
  76           WHERE mfae.source = ANY ($2)
  77                 AND cmf.facet_field
  78                 AND cmf.field_class NOT IN (SELECT * FROM unnest($1))
  79           GROUP by 1, 2
  80       ) all_facets
  81       WHERE rownum <= (
  82         SELECT COALESCE(
  83             (SELECT value::INT FROM config.global_flag WHERE name = 'search.max_facets_per_field' AND enabled),
  84             1000
  85         )
  86       );
  87 $f$ LANGUAGE SQL;
  88
  89 CREATE OR REPLACE FUNCTION search.facets_for_metarecord_set(ignore_facet_classes TEXT[], hits BIGINT[]) RETURNS TABLE (id INT, value TEXT, count BIGINT) AS $$
  90     SELECT id, value, count FROM (
  91         SELECT mfae.field AS id,
  92                mfae.value,
  93                COUNT(DISTINCT mmrsm.metarecord),
  94                row_number() OVER (
  95                 PARTITION BY mfae.field ORDER BY COUNT(distinct mmrsm.metarecord) DESC
  96                ) AS rownum
  97         FROM metabib.facet_entry mfae
  98         JOIN metabib.metarecord_source_map mmrsm ON (mfae.source = mmrsm.source)
  99         JOIN config.metabib_field cmf ON (cmf.id = mfae.field)
 100         WHERE mmrsm.metarecord IN (SELECT * FROM unnest($2))
 101         AND cmf.facet_field
 102         AND cmf.field_class NOT IN (SELECT * FROM unnest($1))
 103         GROUP by 1, 2
 104     ) all_facets
 105     WHERE rownum <= (SELECT COALESCE((SELECT value::INT FROM config.global_flag WHERE name = 'search.max_facets_per_field' AND enabled), 1000));
 106 $$ LANGUAGE SQL;
 107
 108 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute ( value INT, attr TEXT ) RETURNS INT AS $f$
 109 SELECT  ((CASE $2
 110
 111             WHEN 'luri_org'         THEN 0 -- "b" attr
 112             WHEN 'bib_source'       THEN 1 -- "b" attr
 113
 114             WHEN 'copy_flags'       THEN 0 -- "c" attr
 115             WHEN 'owning_lib'       THEN 1 -- "c" attr
 116             WHEN 'circ_lib'         THEN 2 -- "c" attr
 117             WHEN 'status'           THEN 3 -- "c" attr
 118             WHEN 'location'         THEN 4 -- "c" attr
 119             WHEN 'location_group'   THEN 5 -- "c" attr
 120
 121         END) << 28 ) | $1;
 122
 123 /* copy_flags bit positions, LSB-first:
 124
 125  0: asset.copy.opac_visible
 126
 127
 128    When adding flags, you must update asset.all_visible_flags()
 129
 130    Because bib and copy values are stored separately, we can reuse
 131    shifts, saving us some space. We could probably take back a bit
 132    too, but I'm not sure its worth squeezing that last one out. We'd
 133    be left with just 2 slots for copy attrs, rather than 10.
 134 */
 135
 136 $f$ LANGUAGE SQL IMMUTABLE;
 137
 138 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute_list ( attr TEXT, value INT[] ) RETURNS INT[] AS $f$
 139     SELECT ARRAY_AGG(search.calculate_visibility_attribute(x, $1)) FROM UNNEST($2) AS X;
 140 $f$ LANGUAGE SQL IMMUTABLE;
 141
 142 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute_test ( attr TEXT, value INT[], negate BOOL DEFAULT FALSE ) RETURNS TEXT AS $f$
 143     SELECT  CASE WHEN $3 THEN '!' ELSE '' END || '(' || ARRAY_TO_STRING(search.calculate_visibility_attribute_list($1,$2),'|') || ')';
 144 $f$ LANGUAGE SQL IMMUTABLE;
 145
 146 CREATE OR REPLACE FUNCTION asset.calculate_copy_visibility_attribute_set ( copy_id BIGINT ) RETURNS INT[] AS $f$
 147 DECLARE
 148     copy_row    asset.copy%ROWTYPE;
 149     lgroup_map  asset.copy_location_group_map%ROWTYPE;
 150     attr_set    INT[] := '{}'::INT[];
 151 BEGIN
 152     SELECT * INTO copy_row FROM asset.copy WHERE id = copy_id;
 153
 154     attr_set := attr_set || search.calculate_visibility_attribute(copy_row.opac_visible::INT, 'copy_flags');
 155     attr_set := attr_set || search.calculate_visibility_attribute(copy_row.circ_lib, 'circ_lib');
 156     attr_set := attr_set || search.calculate_visibility_attribute(copy_row.status, 'status');
 157     attr_set := attr_set || search.calculate_visibility_attribute(copy_row.location, 'location');
 158
 159     SELECT  ARRAY_APPEND(
 160                 attr_set,
 161                 search.calculate_visibility_attribute(owning_lib, 'owning_lib')
 162             ) INTO attr_set
 163       FROM  asset.call_number
 164       WHERE id = copy_row.call_number;
 165
 166     FOR lgroup_map IN SELECT * FROM asset.copy_location_group_map WHERE location = copy_row.location LOOP
 167         attr_set := attr_set || search.calculate_visibility_attribute(lgroup_map.lgroup, 'location_group');
 168     END LOOP;
 169
 170     RETURN attr_set;
 171 END;
 172 $f$ LANGUAGE PLPGSQL;
 173
 174 CREATE OR REPLACE FUNCTION biblio.calculate_bib_visibility_attribute_set ( bib_id BIGINT, new_source INT DEFAULT NULL, force_source BOOL DEFAULT FALSE ) RETURNS INT[] AS $f$
 175 DECLARE
 176     bib_row     biblio.record_entry%ROWTYPE;
 177     cn_row      asset.call_number%ROWTYPE;
 178     attr_set    INT[] := '{}'::INT[];
 179 BEGIN
 180     SELECT * INTO bib_row FROM biblio.record_entry WHERE id = bib_id;
 181
 182     IF force_source THEN
 183         IF new_source IS NOT NULL THEN
 184             attr_set := attr_set || search.calculate_visibility_attribute(new_source, 'bib_source');
 185         END IF;
 186     ELSIF bib_row.source IS NOT NULL THEN
 187         attr_set := attr_set || search.calculate_visibility_attribute(bib_row.source, 'bib_source');
 188     END IF;
 189
 190     FOR cn_row IN
 191         SELECT  *
 192           FROM  asset.call_number
 193           WHERE record = bib_id
 194                 AND label = '##URI##'
 195                 AND NOT deleted
 196     LOOP
 197         attr_set := attr_set || search.calculate_visibility_attribute(cn_row.owning_lib, 'luri_org');
 198     END LOOP;
 199
 200     RETURN attr_set;
 201 END;
 202 $f$ LANGUAGE PLPGSQL;
 203
 204 CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$
 205 DECLARE
 206     ocn     asset.call_number%ROWTYPE;
 207     ncn     asset.call_number%ROWTYPE;
 208     cid     BIGINT;
 209     dobib   BOOL;
 210 BEGIN
 211
 212     SELECT enabled = FALSE INTO dobib FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc';
 213
 214     IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN -- Only needs ON INSERT OR DELETE, so handle separately
 215         IF TG_OP = 'INSERT' THEN
 216             INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
 217                 NEW.peer_record,
 218                 NEW.target_copy,
 219                 asset.calculate_copy_visibility_attribute_set(NEW.target_copy)
 220             );
 221
 222             RETURN NEW;
 223         ELSIF TG_OP = 'DELETE' THEN
 224             DELETE FROM asset.copy_vis_attr_cache
 225               WHERE record = OLD.peer_record AND target_copy = OLD.target_copy;
 226
 227             RETURN OLD;
 228         END IF;
 229     END IF;
 230
 231     IF TG_OP = 'INSERT' THEN -- Handles ON INSERT. ON UPDATE is below.
 232         IF TG_TABLE_NAME IN ('copy', 'unit') THEN
 233             SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
 234             INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
 235                 ncn.record,
 236                 NEW.id,
 237                 asset.calculate_copy_visibility_attribute_set(NEW.id)
 238             );
 239         ELSIF TG_TABLE_NAME = 'record_entry' THEN
 240             NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id, NEW.source, TRUE);
 241         ELSIF TG_TABLE_NAME = 'call_number' AND NEW.label = '##URI##' AND dobib THEN -- New located URI
 242             UPDATE  biblio.record_entry
 243               SET   vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record)
 244               WHERE id = NEW.record;
 245
 246         END IF;
 247
 248         RETURN NEW;
 249     END IF;
 250
 251     -- handle items first, since with circulation activity
 252     -- their statuses change frequently
 253     IF TG_TABLE_NAME IN ('copy', 'unit') THEN -- This handles ON UPDATE OR DELETE. ON INSERT above
 254
 255         IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
 256             DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
 257             RETURN OLD;
 258         END IF;
 259
 260         SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
 261
 262         IF OLD.deleted <> NEW.deleted THEN
 263             IF NEW.deleted THEN
 264                 DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
 265             ELSE
 266                 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
 267                     ncn.record,
 268                     NEW.id,
 269                     asset.calculate_copy_visibility_attribute_set(NEW.id)
 270                 );
 271             END IF;
 272
 273             RETURN NEW;
 274         ELSIF OLD.location   <> NEW.location OR
 275             OLD.status       <> NEW.status OR
 276             OLD.opac_visible <> NEW.opac_visible OR
 277             OLD.circ_lib     <> NEW.circ_lib OR
 278             OLD.call_number  <> NEW.call_number
 279         THEN
 280             IF OLD.call_number  <> NEW.call_number THEN -- Special check since it's more expensive than the next branch
 281                 SELECT * INTO ocn FROM asset.call_number cn WHERE id = OLD.call_number;
 282
 283                 IF ncn.record <> ocn.record THEN
 284                     -- We have to use a record-specific WHERE clause
 285                     -- to avoid modifying the entries for peer-bib copies.
 286                     UPDATE  asset.copy_vis_attr_cache
 287                       SET   target_copy = NEW.id,
 288                             record = ncn.record
 289                       WHERE target_copy = OLD.id
 290                             AND record = ocn.record;
 291
 292                 END IF;
 293             ELSE
 294                 -- Any of these could change visibility, but
 295                 -- we'll save some queries and not try to calculate
 296                 -- the change directly.  We want to update peer-bib
 297                 -- entries in this case, unlike above.
 298                 UPDATE  asset.copy_vis_attr_cache
 299                   SET   target_copy = NEW.id,
 300                         vis_attr_vector = asset.calculate_copy_visibility_attribute_set(NEW.id)
 301                   WHERE target_copy = OLD.id;
 302             END IF;
 303         END IF;
 304
 305     ELSIF TG_TABLE_NAME = 'call_number' THEN
 306
 307         IF TG_OP = 'DELETE' AND OLD.label = '##URI##' AND dobib THEN -- really deleted located URI, if the delete protection rule is disabled...
 308             UPDATE  biblio.record_entry
 309               SET   vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
 310               WHERE id = OLD.record;
 311             RETURN OLD;
 312         END IF;
 313
 314         IF OLD.label = '##URI##' AND dobib THEN -- Located URI
 315             IF OLD.deleted <> NEW.deleted OR OLD.record <> NEW.record OR OLD.owning_lib <> NEW.owning_lib THEN
 316                 UPDATE  biblio.record_entry
 317                   SET   vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record)
 318                   WHERE id = NEW.record;
 319
 320                 IF OLD.record <> NEW.record THEN -- maybe on merge?
 321                     UPDATE  biblio.record_entry
 322                       SET   vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
 323                       WHERE id = OLD.record;
 324                 END IF;
 325             END IF;
 326
 327         ELSIF OLD.record <> NEW.record OR OLD.owning_lib <> NEW.owning_lib THEN
 328             UPDATE  asset.copy_vis_attr_cache
 329               SET   record = NEW.record,
 330                     vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
 331               WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
 332                     AND record = OLD.record;
 333
 334         END IF;
 335
 336     ELSIF TG_TABLE_NAME = 'record_entry' AND OLD.source IS DISTINCT FROM NEW.source THEN -- Only handles ON UPDATE, INSERT above
 337         NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id, NEW.source, TRUE);
 338     END IF;
 339
 340     RETURN NEW;
 341 END;
 342 $func$ LANGUAGE PLPGSQL;
 343
 344 CREATE TRIGGER z_opac_vis_mat_view_tgr BEFORE INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
 345 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR DELETE ON biblio.peer_bib_copy_map FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
 346 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE OR DELETE ON asset.call_number FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
 347 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
 348 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
 349 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
 350 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
 351
 352 CREATE OR REPLACE FUNCTION asset.all_visible_flags () RETURNS TEXT AS $f$
 353     SELECT  '(' || STRING_AGG(search.calculate_visibility_attribute(1 << x, 'copy_flags')::TEXT,'&') || ')'
 354       FROM  GENERATE_SERIES(0,0) AS x; -- increment as new flags are added.
 355 $f$ LANGUAGE SQL STABLE;
 356
 357 CREATE OR REPLACE FUNCTION asset.visible_orgs (otype TEXT) RETURNS TEXT AS $f$
 358     SELECT  '(' || STRING_AGG(search.calculate_visibility_attribute(id, $1)::TEXT,'|') || ')'
 359       FROM  actor.org_unit
 360       WHERE opac_visible;
 361 $f$ LANGUAGE SQL STABLE;
 362
 363 CREATE OR REPLACE FUNCTION asset.invisible_orgs (otype TEXT) RETURNS TEXT AS $f$
 364     SELECT  '!(' || STRING_AGG(search.calculate_visibility_attribute(id, $1)::TEXT,'|') || ')'
 365       FROM  actor.org_unit
 366       WHERE NOT opac_visible;
 367 $f$ LANGUAGE SQL STABLE;
 368
 369 -- Bib-oriented defaults for search
 370 CREATE OR REPLACE FUNCTION asset.bib_source_default () RETURNS TEXT AS $f$
 371     SELECT  '(' || STRING_AGG(search.calculate_visibility_attribute(id, 'bib_source')::TEXT,'|') || ')'
 372       FROM  config.bib_source
 373       WHERE transcendant;
 374 $f$ LANGUAGE SQL IMMUTABLE;
 375
 376 CREATE OR REPLACE FUNCTION asset.luri_org_default () RETURNS TEXT AS $f$
 377     SELECT  * FROM asset.invisible_orgs('luri_org');
 378 $f$ LANGUAGE SQL STABLE;
 379
 380 -- Copy-oriented defaults for search
 381 CREATE OR REPLACE FUNCTION asset.location_group_default () RETURNS TEXT AS $f$
 382     SELECT '!()'::TEXT; -- For now, as there's no way to cause a location group to hide all copies.
 383 /*
 384     SELECT  '!(' || STRING_AGG(search.calculate_visibility_attribute(id, 'location_group')::TEXT,'|') || ')'
 385       FROM  asset.copy_location_group
 386       WHERE NOT opac_visible;
 387 */
 388 $f$ LANGUAGE SQL IMMUTABLE;
 389
 390 CREATE OR REPLACE FUNCTION asset.location_default () RETURNS TEXT AS $f$
 391     SELECT  '!(' || STRING_AGG(search.calculate_visibility_attribute(id, 'location')::TEXT,'|') || ')'
 392       FROM  asset.copy_location
 393       WHERE NOT opac_visible;
 394 $f$ LANGUAGE SQL STABLE;
 395
 396 CREATE OR REPLACE FUNCTION asset.status_default () RETURNS TEXT AS $f$
 397     SELECT  '!(' || STRING_AGG(search.calculate_visibility_attribute(id, 'status')::TEXT,'|') || ')'
 398       FROM  config.copy_status
 399       WHERE NOT opac_visible;
 400 $f$ LANGUAGE SQL STABLE;
 401
 402 CREATE OR REPLACE FUNCTION asset.owning_lib_default () RETURNS TEXT AS $f$
 403     SELECT  * FROM asset.invisible_orgs('owning_lib');
 404 $f$ LANGUAGE SQL STABLE;
 405
 406 CREATE OR REPLACE FUNCTION asset.circ_lib_default () RETURNS TEXT AS $f$
 407     SELECT  * FROM asset.invisible_orgs('circ_lib');
 408 $f$ LANGUAGE SQL STABLE;
 409
 410 CREATE OR REPLACE FUNCTION asset.patron_default_visibility_mask () RETURNS TABLE (b_attrs TEXT, c_attrs TEXT)  AS $f$
 411 DECLARE
 412     copy_flags      TEXT; -- "c" attr
 413
 414     owning_lib      TEXT; -- "c" attr
 415     circ_lib        TEXT; -- "c" attr
 416     status          TEXT; -- "c" attr
 417     location        TEXT; -- "c" attr
 418     location_group  TEXT; -- "c" attr
 419
 420     luri_org        TEXT; -- "b" attr
 421     bib_sources     TEXT; -- "b" attr
 422
 423     bib_tests       TEXT := '';
 424 BEGIN
 425     copy_flags      := asset.all_visible_flags(); -- Will always have at least one
 426
 427     owning_lib      := NULLIF(asset.owning_lib_default(),'!()');
 428
 429     circ_lib        := NULLIF(asset.circ_lib_default(),'!()');
 430     status          := NULLIF(asset.status_default(),'!()');
 431     location        := NULLIF(asset.location_default(),'!()');
 432     location_group  := NULLIF(asset.location_group_default(),'!()');
 433
 434     -- LURIs will be handled at the perl layer directly
 435     -- luri_org        := NULLIF(asset.luri_org_default(),'!()');
 436     bib_sources     := NULLIF(asset.bib_source_default(),'()');
 437
 438
 439     IF luri_org IS NOT NULL AND bib_sources IS NOT NULL THEN
 440         bib_tests := '('||ARRAY_TO_STRING( ARRAY[luri_org,bib_sources], '|')||')&('||luri_org||')&';
 441     ELSIF luri_org IS NOT NULL THEN
 442         bib_tests := luri_org || '&';
 443     ELSIF bib_sources IS NOT NULL THEN
 444         bib_tests := bib_sources || '|';
 445     END IF;
 446
 447     RETURN QUERY SELECT bib_tests,
 448         '('||ARRAY_TO_STRING(
 449             ARRAY[copy_flags,owning_lib,circ_lib,status,location,location_group]::TEXT[],
 450             '&'
 451         )||')';
 452 END;
 453 $f$ LANGUAGE PLPGSQL STABLE ROWS 1;
 454
 455 CREATE OR REPLACE FUNCTION metabib.suggest_browse_entries(raw_query_text text, search_class text, headline_opts text, visibility_org integer, query_limit integer, normalization integer)
 456  RETURNS TABLE(value text, field integer, buoyant_and_class_match boolean, field_match boolean, field_weight integer, rank real, buoyant boolean, match text)
 457 AS $f$
 458 DECLARE
 459     prepared_query_texts    TEXT[];
 460     query                   TSQUERY;
 461     plain_query             TSQUERY;
 462     opac_visibility_join    TEXT;
 463     search_class_join       TEXT;
 464     r_fields                RECORD;
 465     b_tests                 TEXT := '';
 466 BEGIN
 467     prepared_query_texts := metabib.autosuggest_prepare_tsquery(raw_query_text);
 468
 469     query := TO_TSQUERY('keyword', prepared_query_texts[1]);
 470     plain_query := TO_TSQUERY('keyword', prepared_query_texts[2]);
 471
 472     visibility_org := NULLIF(visibility_org,-1);
 473     IF visibility_org IS NOT NULL THEN
 474         PERFORM FROM actor.org_unit WHERE id = visibility_org AND parent_ou IS NULL;
 475         IF FOUND THEN
 476             opac_visibility_join := '';
 477         ELSE
 478             PERFORM 1 FROM config.internal_flag WHERE enabled AND name = 'opac.located_uri.act_as_copy';
 479             IF FOUND THEN
 480                 b_tests := search.calculate_visibility_attribute_test(
 481                     'luri_org',
 482                     (SELECT ARRAY_AGG(id) FROM actor.org_unit_full_path(visibility_org))
 483                 );
 484             ELSE
 485                 b_tests := search.calculate_visibility_attribute_test(
 486                     'luri_org',
 487                     (SELECT ARRAY_AGG(id) FROM actor.org_unit_ancestors(visibility_org))
 488                 );
 489             END IF;
 490             opac_visibility_join := '
 491     LEFT JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = x.source)
 492     LEFT JOIN biblio.record_entry b ON (b.id = x.source)
 493     JOIN vm ON (acvac.vis_attr_vector @@
 494             (vm.c_attrs || $$&$$ ||
 495                 search.calculate_visibility_attribute_test(
 496                     $$circ_lib$$,
 497                     (SELECT ARRAY_AGG(id) FROM actor.org_unit_descendants($4))
 498                 )
 499             )::query_int
 500          ) OR (b.vis_attr_vector @@ $$' || b_tests || '$$::query_int)
 501 ';
 502         END IF;
 503     ELSE
 504         opac_visibility_join := '';
 505     END IF;
 506
 507     -- The following determines whether we only provide suggestsons matching
 508     -- the user's selected search_class, or whether we show other suggestions
 509     -- too. The reason for MIN() is that for search_classes like
 510     -- 'title|proper|uniform' you would otherwise get multiple rows.  The
 511     -- implication is that if title as a class doesn't have restrict,
 512     -- nor does the proper field, but the uniform field does, you're going
 513     -- to get 'false' for your overall evaluation of 'should we restrict?'
 514     -- To invert that, change from MIN() to MAX().
 515
 516     SELECT
 517         INTO r_fields
 518             MIN(cmc.restrict::INT) AS restrict_class,
 519             MIN(cmf.restrict::INT) AS restrict_field
 520         FROM metabib.search_class_to_registered_components(search_class)
 521             AS _registered (field_class TEXT, field INT)
 522         JOIN
 523             config.metabib_class cmc ON (cmc.name = _registered.field_class)
 524         LEFT JOIN
 525             config.metabib_field cmf ON (cmf.id = _registered.field);
 526
 527     -- evaluate 'should we restrict?'
 528     IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN
 529         search_class_join := '
 530     JOIN
 531         metabib.search_class_to_registered_components($2)
 532         AS _registered (field_class TEXT, field INT) ON (
 533             (_registered.field IS NULL AND
 534                 _registered.field_class = cmf.field_class) OR
 535             (_registered.field = cmf.id)
 536         )
 537     ';
 538     ELSE
 539         search_class_join := '
 540     LEFT JOIN
 541         metabib.search_class_to_registered_components($2)
 542         AS _registered (field_class TEXT, field INT) ON (
 543             _registered.field_class = cmc.name
 544         )
 545     ';
 546     END IF;
 547
 548     RETURN QUERY EXECUTE '
 549 WITH vm AS ( SELECT * FROM asset.patron_default_visibility_mask() ),
 550      mbe AS (SELECT * FROM metabib.browse_entry WHERE index_vector @@ $1 LIMIT 10000)
 551 SELECT  DISTINCT
 552         x.value,
 553         x.id,
 554         x.push,
 555         x.restrict,
 556         x.weight,
 557         x.ts_rank_cd,
 558         x.buoyant,
 559         TS_HEADLINE(value, $7, $3)
 560   FROM  (SELECT DISTINCT
 561                 mbe.value,
 562                 cmf.id,
 563                 cmc.buoyant AND _registered.field_class IS NOT NULL AS push,
 564                 _registered.field = cmf.id AS restrict,
 565                 cmf.weight,
 566                 TS_RANK_CD(mbe.index_vector, $1, $6),
 567                 cmc.buoyant,
 568                 mbedm.source
 569           FROM  metabib.browse_entry_def_map mbedm
 570                 JOIN mbe ON (mbe.id = mbedm.entry)
 571                 JOIN config.metabib_field cmf ON (cmf.id = mbedm.def)
 572                 JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name)
 573                 '  || search_class_join || '
 574           ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
 575           LIMIT 1000) AS x
 576         ' || opac_visibility_join || '
 577   ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
 578   LIMIT $5
 579 '   -- sic, repeat the order by clause in the outer select too
 580     USING
 581         query, search_class, headline_opts,
 582         visibility_org, query_limit, normalization, plain_query
 583         ;
 584
 585     -- sort order:
 586     --  buoyant AND chosen class = match class
 587     --  chosen field = match field
 588     --  field weight
 589     --  rank
 590     --  buoyancy
 591     --  value itself
 592
 593 END;
 594 $f$ LANGUAGE plpgsql ROWS 10;
 595
 596 CREATE OR REPLACE FUNCTION metabib.staged_browse(query text, fields integer[], context_org integer, context_locations integer[], staff boolean, browse_superpage_size integer, count_up_from_zero boolean, result_limit integer, next_pivot_pos integer)
 597  RETURNS SETOF metabib.flat_browse_entry_appearance
 598 AS $f$
 599 DECLARE
 600     curs                    REFCURSOR;
 601     rec                     RECORD;
 602     qpfts_query             TEXT;
 603     aqpfts_query            TEXT;
 604     afields                 INT[];
 605     bfields                 INT[];
 606     result_row              metabib.flat_browse_entry_appearance%ROWTYPE;
 607     results_skipped         INT := 0;
 608     row_counter             INT := 0;
 609     row_number              INT;
 610     slice_start             INT;
 611     slice_end               INT;
 612     full_end                INT;
 613     all_records             BIGINT[];
 614     all_brecords             BIGINT[];
 615     all_arecords            BIGINT[];
 616     superpage_of_records    BIGINT[];
 617     superpage_size          INT;
 618     c_tests                 TEXT := '';
 619     b_tests                 TEXT := '';
 620     c_orgs                  INT[];
 621     unauthorized_entry      RECORD;
 622 BEGIN
 623     IF count_up_from_zero THEN
 624         row_number := 0;
 625     ELSE
 626         row_number := -1;
 627     END IF;
 628
 629     IF NOT staff THEN
 630         SELECT x.c_attrs, x.b_attrs INTO c_tests, b_tests FROM asset.patron_default_visibility_mask() x;
 631     END IF;
 632
 633     -- b_tests supplies its own query_int operator, c_tests does not
 634     IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
 635
 636     SELECT ARRAY_AGG(id) INTO c_orgs FROM actor.org_unit_descendants(context_org);
 637
 638     c_tests := c_tests || search.calculate_visibility_attribute_test('circ_lib',c_orgs)
 639                || '&' || search.calculate_visibility_attribute_test('owning_lib',c_orgs);
 640
 641     PERFORM 1 FROM config.internal_flag WHERE enabled AND name = 'opac.located_uri.act_as_copy';
 642     IF FOUND THEN
 643         b_tests := b_tests || search.calculate_visibility_attribute_test(
 644             'luri_org',
 645             (SELECT ARRAY_AGG(id) FROM actor.org_unit_full_path(context_org) x)
 646         );
 647     ELSE
 648         b_tests := b_tests || search.calculate_visibility_attribute_test(
 649             'luri_org',
 650             (SELECT ARRAY_AGG(id) FROM actor.org_unit_ancestors(context_org) x)
 651         );
 652     END IF;
 653
 654     IF context_locations THEN
 655         IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
 656         c_tests := c_tests || search.calculate_visibility_attribute_test('location',context_locations);
 657     END IF;
 658
 659     OPEN curs NO SCROLL FOR EXECUTE query;
 660
 661     LOOP
 662         FETCH curs INTO rec;
 663         IF NOT FOUND THEN
 664             IF result_row.pivot_point IS NOT NULL THEN
 665                 RETURN NEXT result_row;
 666             END IF;
 667             RETURN;
 668         END IF;
 669
 670         --Is unauthorized?
 671         SELECT INTO unauthorized_entry *
 672         FROM metabib.browse_entry_simple_heading_map mbeshm
 673         INNER JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
 674         INNER JOIN authority.control_set_authority_field acsaf ON ( acsaf.id = ash.atag )
 675         JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
 676         WHERE mbeshm.entry = rec.id
 677         AND   ahf.heading_purpose = 'variant';
 678
 679         -- Gather aggregate data based on the MBE row we're looking at now, authority axis
 680         IF (unauthorized_entry.record IS NOT NULL) THEN
 681             --unauthorized term belongs to an auth linked to a bib?
 682             SELECT INTO all_arecords, result_row.sees, afields
 683                     ARRAY_AGG(DISTINCT abl.bib),
 684                     STRING_AGG(DISTINCT abl.authority::TEXT, $$,$$),
 685                     ARRAY_AGG(DISTINCT map.metabib_field)
 686             FROM authority.bib_linking abl
 687             INNER JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
 688                     map.authority_field = unauthorized_entry.atag
 689                     AND map.metabib_field = ANY(fields)
 690             )
 691             WHERE abl.authority = unauthorized_entry.record;
 692         ELSE
 693             --do usual procedure
 694             SELECT INTO all_arecords, result_row.sees, afields
 695                     ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
 696                     STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
 697                     ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
 698
 699             FROM  metabib.browse_entry_simple_heading_map mbeshm
 700                     JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
 701                     JOIN authority.authority_linking aal ON ( ash.record = aal.source )
 702                     JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
 703                     JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
 704                         ash.atag = map.authority_field
 705                         AND map.metabib_field = ANY(fields)
 706                     )
 707                     JOIN authority.control_set_authority_field acsaf ON (
 708                         map.authority_field = acsaf.id
 709                     )
 710                     JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
 711               WHERE mbeshm.entry = rec.id
 712               AND   ahf.heading_purpose = 'variant';
 713
 714         END IF;
 715
 716         -- Gather aggregate data based on the MBE row we're looking at now, bib axis
 717         SELECT INTO all_brecords, result_row.authorities, bfields
 718                 ARRAY_AGG(DISTINCT source),
 719                 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
 720                 ARRAY_AGG(DISTINCT def)
 721           FROM  metabib.browse_entry_def_map
 722           WHERE entry = rec.id
 723                 AND def = ANY(fields);
 724
 725         SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
 726
 727         result_row.sources := 0;
 728         result_row.asources := 0;
 729
 730         -- Bib-linked vis checking
 731         IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
 732
 733             SELECT  INTO result_row.sources COUNT(DISTINCT b.id)
 734               FROM  biblio.record_entry b
 735                     LEFT JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
 736               WHERE b.id = ANY(all_brecords[1:browse_superpage_size])
 737                     AND (
 738                         acvac.vis_attr_vector @@ c_tests::query_int
 739                         OR b.vis_attr_vector @@ b_tests::query_int
 740                     );
 741
 742             result_row.accurate := TRUE;
 743
 744         END IF;
 745
 746         -- Authority-linked vis checking
 747         IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
 748
 749             SELECT  INTO result_row.asources COUNT(DISTINCT b.id)
 750               FROM  biblio.record_entry b
 751                     LEFT JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
 752               WHERE b.id = ANY(all_arecords[1:browse_superpage_size])
 753                     AND (
 754                         acvac.vis_attr_vector @@ c_tests::query_int
 755                         OR b.vis_attr_vector @@ b_tests::query_int
 756                     );
 757
 758             result_row.aaccurate := TRUE;
 759
 760         END IF;
 761
 762         IF result_row.sources > 0 OR result_row.asources > 0 THEN
 763
 764             -- The function that calls this function needs row_number in order
 765             -- to correctly order results from two different runs of this
 766             -- functions.
 767             result_row.row_number := row_number;
 768
 769             -- Now, if row_counter is still less than limit, return a row.  If
 770             -- not, but it is less than next_pivot_pos, continue on without
 771             -- returning actual result rows until we find
 772             -- that next pivot, and return it.
 773
 774             IF row_counter < result_limit THEN
 775                 result_row.browse_entry := rec.id;
 776                 result_row.value := rec.value;
 777
 778                 RETURN NEXT result_row;
 779             ELSE
 780                 result_row.browse_entry := NULL;
 781                 result_row.authorities := NULL;
 782                 result_row.fields := NULL;
 783                 result_row.value := NULL;
 784                 result_row.sources := NULL;
 785                 result_row.sees := NULL;
 786                 result_row.accurate := NULL;
 787                 result_row.aaccurate := NULL;
 788                 result_row.pivot_point := rec.id;
 789
 790                 IF row_counter >= next_pivot_pos THEN
 791                     RETURN NEXT result_row;
 792                     RETURN;
 793                 END IF;
 794             END IF;
 795
 796             IF count_up_from_zero THEN
 797                 row_number := row_number + 1;
 798             ELSE
 799                 row_number := row_number - 1;
 800             END IF;
 801
 802             -- row_counter is different from row_number.
 803             -- It simply counts up from zero so that we know when
 804             -- we've reached our limit.
 805             row_counter := row_counter + 1;
 806         END IF;
 807     END LOOP;
 808 END;
 809 $f$ LANGUAGE plpgsql ROWS 10;
 810
 811 CREATE OR REPLACE FUNCTION metabib.browse(search_field integer[], browse_term text, context_org integer DEFAULT NULL::integer, context_loc_group integer DEFAULT NULL::integer, staff boolean DEFAULT false, pivot_id bigint DEFAULT NULL::bigint, result_limit integer DEFAULT 10)
 812  RETURNS SETOF metabib.flat_browse_entry_appearance
 813 AS $f$
 814 DECLARE
 815     core_query              TEXT;
 816     back_query              TEXT;
 817     forward_query           TEXT;
 818     pivot_sort_value        TEXT;
 819     pivot_sort_fallback     TEXT;
 820     context_locations       INT[];
 821     browse_superpage_size   INT;
 822     results_skipped         INT := 0;
 823     back_limit              INT;
 824     back_to_pivot           INT;
 825     forward_limit           INT;
 826     forward_to_pivot        INT;
 827 BEGIN
 828     -- First, find the pivot if we were given a browse term but not a pivot.
 829     IF pivot_id IS NULL THEN
 830         pivot_id := metabib.browse_pivot(search_field, browse_term);
 831     END IF;
 832
 833     SELECT INTO pivot_sort_value, pivot_sort_fallback
 834         sort_value, value FROM metabib.browse_entry WHERE id = pivot_id;
 835
 836     -- Bail if we couldn't find a pivot.
 837     IF pivot_sort_value IS NULL THEN
 838         RETURN;
 839     END IF;
 840
 841     -- Transform the context_loc_group argument (if any) (logc at the
 842     -- TPAC layer) into a form we'll be able to use.
 843     IF context_loc_group IS NOT NULL THEN
 844         SELECT INTO context_locations ARRAY_AGG(location)
 845             FROM asset.copy_location_group_map
 846             WHERE lgroup = context_loc_group;
 847     END IF;
 848
 849     -- Get the configured size of browse superpages.
 850     SELECT INTO browse_superpage_size COALESCE(value::INT,100)     -- NULL ok
 851         FROM config.global_flag
 852         WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit';
 853
 854     -- First we're going to search backward from the pivot, then we're going
 855     -- to search forward.  In each direction, we need two limits.  At the
 856     -- lesser of the two limits, we delineate the edge of the result set
 857     -- we're going to return.  At the greater of the two limits, we find the
 858     -- pivot value that would represent an offset from the current pivot
 859     -- at a distance of one "page" in either direction, where a "page" is a
 860     -- result set of the size specified in the "result_limit" argument.
 861     --
 862     -- The two limits in each direction make four derived values in total,
 863     -- and we calculate them now.
 864     back_limit := CEIL(result_limit::FLOAT / 2);
 865     back_to_pivot := result_limit;
 866     forward_limit := result_limit / 2;
 867     forward_to_pivot := result_limit - 1;
 868
 869     -- This is the meat of the SQL query that finds browse entries.  We'll
 870     -- pass this to a function which uses it with a cursor, so that individual
 871     -- rows may be fetched in a loop until some condition is satisfied, without
 872     -- waiting for a result set of fixed size to be collected all at once.
 873     core_query := '
 874 SELECT  mbe.id,
 875         mbe.value,
 876         mbe.sort_value
 877   FROM  metabib.browse_entry mbe
 878   WHERE (
 879             EXISTS ( -- are there any bibs using this mbe via the requested fields?
 880                 SELECT  1
 881                   FROM  metabib.browse_entry_def_map mbedm
 882                   WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
 883             ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
 884                 SELECT  1
 885                   FROM  metabib.browse_entry_simple_heading_map mbeshm
 886                         JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
 887                         JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
 888                             ash.atag = map.authority_field
 889                             AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
 890                         )
 891                         JOIN authority.control_set_authority_field acsaf ON (
 892                             map.authority_field = acsaf.id
 893                         )
 894                         JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
 895                   WHERE mbeshm.entry = mbe.id
 896                     AND ahf.heading_purpose IN (' || $$'variant'$$ || ')
 897                     -- and authority that variant is coming from is linked to a bib
 898                     AND EXISTS (
 899                         SELECT  1
 900                         FROM  metabib.browse_entry_def_map mbedm2
 901                         WHERE mbedm2.authority = ash.record AND mbedm2.def = ANY(' || quote_literal(search_field) || ')
 902                     )
 903
 904             )
 905         ) AND ';
 906
 907     -- This is the variant of the query for browsing backward.
 908     back_query := core_query ||
 909         ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) ||
 910     ' ORDER BY mbe.sort_value DESC, mbe.value DESC LIMIT 1000';
 911
 912     -- This variant browses forward.
 913     forward_query := core_query ||
 914         ' mbe.sort_value > ' || quote_literal(pivot_sort_value) ||
 915     ' ORDER BY mbe.sort_value, mbe.value LIMIT 1000';
 916
 917     -- We now call the function which applies a cursor to the provided
 918     -- queries, stopping at the appropriate limits and also giving us
 919     -- the next page's pivot.
 920     RETURN QUERY
 921         SELECT * FROM metabib.staged_browse(
 922             back_query, search_field, context_org, context_locations,
 923             staff, browse_superpage_size, TRUE, back_limit, back_to_pivot
 924         ) UNION
 925         SELECT * FROM metabib.staged_browse(
 926             forward_query, search_field, context_org, context_locations,
 927             staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot
 928         ) ORDER BY row_number DESC;
 929
 930 END;
 931 $f$ LANGUAGE plpgsql ROWS 10;
 932
 933 CREATE OR REPLACE FUNCTION metabib.browse(
 934     search_class        TEXT,
 935     browse_term         TEXT,
 936     context_org         INT DEFAULT NULL,
 937     context_loc_group   INT DEFAULT NULL,
 938     staff               BOOL DEFAULT FALSE,
 939     pivot_id            BIGINT DEFAULT NULL,
 940     result_limit        INT DEFAULT 10
 941 ) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$
 942 BEGIN
 943     RETURN QUERY SELECT * FROM metabib.browse(
 944         (SELECT COALESCE(ARRAY_AGG(id), ARRAY[]::INT[])
 945             FROM config.metabib_field WHERE field_class = search_class),
 946         browse_term,
 947         context_org,
 948         context_loc_group,
 949         staff,
 950         pivot_id,
 951         result_limit
 952     );
 953 END;
 954 $p$ LANGUAGE PLPGSQL ROWS 10;
 955
 956 CREATE OR REPLACE VIEW search.best_tsconfig AS
 957     SELECT  m.id AS id,
 958             COALESCE(f.ts_config, c.ts_config, 'simple') AS ts_config
 959       FROM  config.metabib_field m
 960             LEFT JOIN config.metabib_class_ts_map c ON (c.field_class = m.field_class AND c.index_weight = 'C')
 961             LEFT JOIN config.metabib_field_ts_map f ON (f.metabib_field = m.id AND f.index_weight = 'C');
 962
 963 CREATE TYPE search.highlight_result AS ( id BIGINT, source BIGINT, field INT, value TEXT, highlight TEXT );
 964
 965 CREATE OR REPLACE FUNCTION search.highlight_display_fields_impl(
 966     rid         BIGINT,
 967     tsq         TEXT,
 968     field_list  INT[] DEFAULT '{}'::INT[],
 969     css_class   TEXT DEFAULT 'oils_SH',
 970     hl_all      BOOL DEFAULT TRUE,
 971     minwords    INT DEFAULT 5,
 972     maxwords    INT DEFAULT 25,
 973     shortwords  INT DEFAULT 0,
 974     maxfrags    INT DEFAULT 0,
 975     delimiter   TEXT DEFAULT ' ... '
 976 ) RETURNS SETOF search.highlight_result AS $f$
 977 DECLARE
 978     opts            TEXT := '';
 979     v_css_class     TEXT := css_class;
 980     v_delimiter     TEXT := delimiter;
 981     v_field_list    INT[] := field_list;
 982     hl_query        TEXT;
 983 BEGIN
 984     IF v_delimiter LIKE $$%'%$$ OR v_delimiter LIKE '%"%' THEN --"
 985         v_delimiter := ' ... ';
 986     END IF;
 987
 988     IF NOT hl_all THEN
 989         opts := opts || 'MinWords=' || minwords;
 990         opts := opts || ', MaxWords=' || maxwords;
 991         opts := opts || ', ShortWords=' || shortwords;
 992         opts := opts || ', MaxFragments=' || maxfrags;
 993         opts := opts || ', FragmentDelimiter="' || delimiter || '"';
 994     ELSE
 995         opts := opts || 'HighlightAll=TRUE';
 996     END IF;
 997
 998     IF v_css_class LIKE $$%'%$$ OR v_css_class LIKE '%"%' THEN -- "
 999         v_css_class := 'oils_SH';
1000     END IF;
1001
1002     opts := opts || $$, StopSel=</mark>, StartSel="<mark class='$$ || v_css_class; -- "
1003
1004     IF v_field_list = '{}'::INT[] THEN
1005         SELECT ARRAY_AGG(id) INTO v_field_list FROM config.metabib_field WHERE display_field;
1006     END IF;
1007
1008     hl_query := $$
1009         SELECT  de.id,
1010                 de.source,
1011                 de.field,
1012                 evergreen.escape_for_html(de.value) AS value,
1013                 ts_headline(
1014                     ts_config::REGCONFIG,
1015                     evergreen.escape_for_html(de.value),
1016                     $$ || quote_literal(tsq) || $$,
1017                     $1 || ' ' || mf.field_class || ' ' || mf.name || $xx$'>"$xx$ -- "'
1018                 ) AS highlight
1019           FROM  metabib.display_entry de
1020                 JOIN config.metabib_field mf ON (mf.id = de.field)
1021                 JOIN search.best_tsconfig t ON (t.id = de.field)
1022           WHERE de.source = $2
1023                 AND field = ANY ($3)
1024           ORDER BY de.id;$$;
1025
1026     RETURN QUERY EXECUTE hl_query USING opts, rid, v_field_list;
1027 END;
1028 $f$ LANGUAGE PLPGSQL;
1029
1030 CREATE OR REPLACE FUNCTION evergreen.escape_for_html (TEXT) RETURNS TEXT AS $$
1031     SELECT  regexp_replace(
1032                 regexp_replace(
1033                     regexp_replace(
1034                         $1,
1035                         '&',
1036                         '&amp;',
1037                         'g'
1038                     ),
1039                     '<',
1040                     '&lt;',
1041                     'g'
1042                 ),
1043                 '>',
1044                 '&gt;',
1045                 'g'
1046             );
1047 $$ LANGUAGE SQL IMMUTABLE LEAKPROOF STRICT COST 10;
1048
1049 CREATE OR REPLACE FUNCTION search.highlight_display_fields(
1050     rid         BIGINT,
1051     tsq_map     TEXT, -- { '(a | b) & c' => '1,2,3,4', ...}
1052     css_class   TEXT DEFAULT 'oils_SH',
1053     hl_all      BOOL DEFAULT TRUE,
1054     minwords    INT DEFAULT 5,
1055     maxwords    INT DEFAULT 25,
1056     shortwords  INT DEFAULT 0,
1057     maxfrags    INT DEFAULT 0,
1058     delimiter   TEXT DEFAULT ' ... '
1059 ) RETURNS SETOF search.highlight_result AS $f$
1060 DECLARE
1061     tsq_hstore  TEXT;
1062     tsq         TEXT;
1063     fields      TEXT;
1064     afields     INT[];
1065     seen        INT[];
1066 BEGIN
1067     IF (tsq_map ILIKE 'hstore%') THEN
1068         EXECUTE 'SELECT ' || tsq_map INTO tsq_hstore;
1069     ELSE
1070         tsq_hstore := tsq_map::HSTORE;
1071     END IF;
1072
1073     FOR tsq, fields IN SELECT key, value FROM each(tsq_hstore::HSTORE) LOOP
1074         SELECT  ARRAY_AGG(unnest::INT) INTO afields
1075           FROM  unnest(regexp_split_to_array(fields,','));
1076         seen := seen || afields;
1077
1078         RETURN QUERY
1079             SELECT * FROM search.highlight_display_fields_impl(
1080                 rid, tsq, afields, css_class, hl_all,minwords,
1081                 maxwords, shortwords, maxfrags, delimiter
1082             );
1083     END LOOP;
1084
1085     RETURN QUERY
1086         SELECT  id,
1087                 source,
1088                 field,
1089                 evergreen.escape_for_html(value) AS value,
1090                 evergreen.escape_for_html(value) AS highlight
1091           FROM  metabib.display_entry
1092           WHERE source = rid
1093                 AND NOT (field = ANY (seen));
1094 END;
1095 $f$ LANGUAGE PLPGSQL ROWS 10;
1096
1097 -- SymSpell implementation follows
1098
1099 -- We don't pass this function arrays with nulls, so we save 5% not testing for that
1100 CREATE OR REPLACE FUNCTION evergreen.text_array_merge_unique (
1101     TEXT[], TEXT[]
1102 ) RETURNS TEXT[] AS $F$
1103     SELECT NULLIF(ARRAY(
1104         SELECT * FROM UNNEST($1) x
1105             UNION
1106         SELECT * FROM UNNEST($2) y
1107     ),'{}');
1108 $F$ LANGUAGE SQL;
1109
1110 CREATE OR REPLACE FUNCTION evergreen.qwerty_keyboard_distance ( a TEXT, b TEXT ) RETURNS NUMERIC AS $F$
1111 use String::KeyboardDistance qw(:all);
1112 return qwerty_keyboard_distance(@_);
1113 $F$ LANGUAGE PLPERLU STRICT IMMUTABLE;
1114
1115 CREATE OR REPLACE FUNCTION evergreen.qwerty_keyboard_distance_match ( a TEXT, b TEXT ) RETURNS NUMERIC AS $F$
1116 use String::KeyboardDistance qw(:all);
1117 return qwerty_keyboard_distance_match(@_);
1118 $F$ LANGUAGE PLPERLU STRICT IMMUTABLE;
1119
1120 CREATE OR REPLACE FUNCTION evergreen.levenshtein_damerau_edistance ( a TEXT, b TEXT, INT ) RETURNS NUMERIC AS $F$
1121 use Text::Levenshtein::Damerau::XS qw/xs_edistance/;
1122 return xs_edistance(@_);
1123 $F$ LANGUAGE PLPERLU STRICT IMMUTABLE;
1124
1125 CREATE TABLE search.symspell_dictionary (
1126     keyword_count           INT     NOT NULL DEFAULT 0,
1127     title_count             INT     NOT NULL DEFAULT 0,
1128     author_count            INT     NOT NULL DEFAULT 0,
1129     subject_count           INT     NOT NULL DEFAULT 0,
1130     series_count            INT     NOT NULL DEFAULT 0,
1131     identifier_count        INT     NOT NULL DEFAULT 0,
1132
1133     prefix_key              TEXT    PRIMARY KEY,
1134
1135     keyword_suggestions     TEXT[],
1136     title_suggestions       TEXT[],
1137     author_suggestions      TEXT[],
1138     subject_suggestions     TEXT[],
1139     series_suggestions      TEXT[],
1140     identifier_suggestions  TEXT[]
1141 ) WITH (fillfactor = 80);
1142
1143 -- INSERT-only table that catches updates to be reconciled
1144 CREATE UNLOGGED TABLE search.symspell_dictionary_updates (
1145     transaction_id          BIGINT,
1146     keyword_count           INT     NOT NULL DEFAULT 0,
1147     title_count             INT     NOT NULL DEFAULT 0,
1148     author_count            INT     NOT NULL DEFAULT 0,
1149     subject_count           INT     NOT NULL DEFAULT 0,
1150     series_count            INT     NOT NULL DEFAULT 0,
1151     identifier_count        INT     NOT NULL DEFAULT 0,
1152
1153     prefix_key              TEXT    NOT NULL,
1154
1155     keyword_suggestions     TEXT[],
1156     title_suggestions       TEXT[],
1157     author_suggestions      TEXT[],
1158     subject_suggestions     TEXT[],
1159     series_suggestions      TEXT[],
1160     identifier_suggestions  TEXT[]
1161 );
1162 CREATE INDEX symspell_dictionary_updates_tid_idx ON search.symspell_dictionary_updates (transaction_id);
1163
1164 CREATE OR REPLACE FUNCTION search.symspell_dictionary_reify () RETURNS SETOF search.symspell_dictionary AS $f$
1165  WITH new_rows AS (
1166     DELETE FROM search.symspell_dictionary_updates WHERE transaction_id = txid_current() RETURNING *
1167  ), computed_rows AS ( -- this collapses the rows deleted into the format we need for UPSERT
1168     SELECT  SUM(keyword_count)    AS keyword_count,
1169             SUM(title_count)      AS title_count,
1170             SUM(author_count)     AS author_count,
1171             SUM(subject_count)    AS subject_count,
1172             SUM(series_count)     AS series_count,
1173             SUM(identifier_count) AS identifier_count,
1174
1175             prefix_key,
1176
1177             ARRAY_REMOVE(ARRAY_AGG(DISTINCT keyword_suggestions[1]), NULL)    AS keyword_suggestions,
1178             ARRAY_REMOVE(ARRAY_AGG(DISTINCT title_suggestions[1]), NULL)      AS title_suggestions,
1179             ARRAY_REMOVE(ARRAY_AGG(DISTINCT author_suggestions[1]), NULL)     AS author_suggestions,
1180             ARRAY_REMOVE(ARRAY_AGG(DISTINCT subject_suggestions[1]), NULL)    AS subject_suggestions,
1181             ARRAY_REMOVE(ARRAY_AGG(DISTINCT series_suggestions[1]), NULL)     AS series_suggestions,
1182             ARRAY_REMOVE(ARRAY_AGG(DISTINCT identifier_suggestions[1]), NULL) AS identifier_suggestions
1183       FROM  new_rows
1184       GROUP BY prefix_key
1185  )
1186  INSERT INTO search.symspell_dictionary AS d SELECT * FROM computed_rows
1187  ON CONFLICT (prefix_key) DO UPDATE SET
1188     keyword_count = GREATEST(0, d.keyword_count + EXCLUDED.keyword_count),
1189     keyword_suggestions = evergreen.text_array_merge_unique(EXCLUDED.keyword_suggestions,d.keyword_suggestions),
1190
1191     title_count = GREATEST(0, d.title_count + EXCLUDED.title_count),
1192     title_suggestions = evergreen.text_array_merge_unique(EXCLUDED.title_suggestions,d.title_suggestions),
1193
1194     author_count = GREATEST(0, d.author_count + EXCLUDED.author_count),
1195     author_suggestions = evergreen.text_array_merge_unique(EXCLUDED.author_suggestions,d.author_suggestions),
1196
1197     subject_count = GREATEST(0, d.subject_count + EXCLUDED.subject_count),
1198     subject_suggestions = evergreen.text_array_merge_unique(EXCLUDED.subject_suggestions,d.subject_suggestions),
1199
1200     series_count = GREATEST(0, d.series_count + EXCLUDED.series_count),
1201     series_suggestions = evergreen.text_array_merge_unique(EXCLUDED.series_suggestions,d.series_suggestions),
1202
1203     identifier_count = GREATEST(0, d.identifier_count + EXCLUDED.identifier_count),
1204     identifier_suggestions = evergreen.text_array_merge_unique(EXCLUDED.identifier_suggestions,d.identifier_suggestions)
1205
1206     WHERE (
1207         EXCLUDED.keyword_count <> 0 OR
1208         EXCLUDED.title_count <> 0 OR
1209         EXCLUDED.author_count <> 0 OR
1210         EXCLUDED.subject_count <> 0 OR
1211         EXCLUDED.series_count <> 0 OR
1212         EXCLUDED.identifier_count <> 0 OR
1213         NOT (EXCLUDED.keyword_suggestions <@ d.keyword_suggestions) OR
1214         NOT (EXCLUDED.title_suggestions <@ d.title_suggestions) OR
1215         NOT (EXCLUDED.author_suggestions <@ d.author_suggestions) OR
1216         NOT (EXCLUDED.subject_suggestions <@ d.subject_suggestions) OR
1217         NOT (EXCLUDED.series_suggestions <@ d.series_suggestions) OR
1218         NOT (EXCLUDED.identifier_suggestions <@ d.identifier_suggestions)
1219     )
1220  RETURNING *;
1221 $f$ LANGUAGE SQL;
1222
1223 CREATE OR REPLACE FUNCTION search.disable_symspell_reification () RETURNS VOID AS $f$
1224     INSERT INTO config.internal_flag (name,enabled)
1225       VALUES ('ingest.disable_symspell_reification',TRUE)
1226     ON CONFLICT (name) DO UPDATE SET enabled = TRUE;
1227 $f$ LANGUAGE SQL;
1228
1229 CREATE OR REPLACE FUNCTION search.enable_symspell_reification () RETURNS VOID AS $f$
1230     UPDATE config.internal_flag SET enabled = FALSE WHERE name = 'ingest.disable_symspell_reification';
1231 $f$ LANGUAGE SQL;
1232
1233 CREATE OR REPLACE FUNCTION search.symspell_dictionary_full_reify () RETURNS SETOF search.symspell_dictionary AS $f$
1234  WITH new_rows AS (
1235     DELETE FROM search.symspell_dictionary_updates RETURNING *
1236  ), computed_rows AS ( -- this collapses the rows deleted into the format we need for UPSERT
1237     SELECT  SUM(keyword_count)    AS keyword_count,
1238             SUM(title_count)      AS title_count,
1239             SUM(author_count)     AS author_count,
1240             SUM(subject_count)    AS subject_count,
1241             SUM(series_count)     AS series_count,
1242             SUM(identifier_count) AS identifier_count,
1243
1244             prefix_key,
1245
1246             ARRAY_REMOVE(ARRAY_AGG(DISTINCT keyword_suggestions[1]), NULL)    AS keyword_suggestions,
1247             ARRAY_REMOVE(ARRAY_AGG(DISTINCT title_suggestions[1]), NULL)      AS title_suggestions,
1248             ARRAY_REMOVE(ARRAY_AGG(DISTINCT author_suggestions[1]), NULL)     AS author_suggestions,
1249             ARRAY_REMOVE(ARRAY_AGG(DISTINCT subject_suggestions[1]), NULL)    AS subject_suggestions,
1250             ARRAY_REMOVE(ARRAY_AGG(DISTINCT series_suggestions[1]), NULL)     AS series_suggestions,
1251             ARRAY_REMOVE(ARRAY_AGG(DISTINCT identifier_suggestions[1]), NULL) AS identifier_suggestions
1252       FROM  new_rows
1253       GROUP BY prefix_key
1254  )
1255  INSERT INTO search.symspell_dictionary AS d SELECT * FROM computed_rows
1256  ON CONFLICT (prefix_key) DO UPDATE SET
1257     keyword_count = GREATEST(0, d.keyword_count + EXCLUDED.keyword_count),
1258     keyword_suggestions = evergreen.text_array_merge_unique(EXCLUDED.keyword_suggestions,d.keyword_suggestions),
1259
1260     title_count = GREATEST(0, d.title_count + EXCLUDED.title_count),
1261     title_suggestions = evergreen.text_array_merge_unique(EXCLUDED.title_suggestions,d.title_suggestions),
1262
1263     author_count = GREATEST(0, d.author_count + EXCLUDED.author_count),
1264     author_suggestions = evergreen.text_array_merge_unique(EXCLUDED.author_suggestions,d.author_suggestions),
1265
1266     subject_count = GREATEST(0, d.subject_count + EXCLUDED.subject_count),
1267     subject_suggestions = evergreen.text_array_merge_unique(EXCLUDED.subject_suggestions,d.subject_suggestions),
1268
1269     series_count = GREATEST(0, d.series_count + EXCLUDED.series_count),
1270     series_suggestions = evergreen.text_array_merge_unique(EXCLUDED.series_suggestions,d.series_suggestions),
1271
1272     identifier_count = GREATEST(0, d.identifier_count + EXCLUDED.identifier_count),
1273     identifier_suggestions = evergreen.text_array_merge_unique(EXCLUDED.identifier_suggestions,d.identifier_suggestions)
1274  RETURNING *;
1275 $f$ LANGUAGE SQL;
1276
1277 CREATE OR REPLACE FUNCTION search.symspell_parse_words ( phrase TEXT )
1278 RETURNS SETOF TEXT AS $F$
1279     SELECT UNNEST(x) FROM regexp_matches($1, '([[:alnum:]]+''*[[:alnum:]]*)', 'g') x;
1280 $F$ LANGUAGE SQL STRICT IMMUTABLE;
1281
1282 -- This version does not preserve input word order!
1283 CREATE OR REPLACE FUNCTION search.symspell_parse_words_distinct ( phrase TEXT )
1284 RETURNS SETOF TEXT AS $F$
1285     SELECT DISTINCT UNNEST(x) FROM regexp_matches($1, '([[:alnum:]]+''*[[:alnum:]]*)', 'g') x;
1286 $F$ LANGUAGE SQL STRICT IMMUTABLE;
1287
1288 CREATE OR REPLACE FUNCTION search.symspell_transfer_casing ( withCase TEXT, withoutCase TEXT )
1289 RETURNS TEXT AS $F$
1290 DECLARE
1291     woChars TEXT[];
1292     curr    TEXT;
1293     ind     INT := 1;
1294 BEGIN
1295     woChars := regexp_split_to_array(withoutCase,'');
1296     FOR curr IN SELECT x FROM regexp_split_to_table(withCase, '') x LOOP
1297         IF curr = evergreen.uppercase(curr) THEN
1298             woChars[ind] := evergreen.uppercase(woChars[ind]);
1299         END IF;
1300         ind := ind + 1;
1301     END LOOP;
1302     RETURN ARRAY_TO_STRING(woChars,'');
1303 END;
1304 $F$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
1305
1306 CREATE OR REPLACE FUNCTION search.symspell_generate_edits (
1307     raw_word    TEXT,
1308     dist        INT DEFAULT 1,
1309     maxED       INT DEFAULT 3
1310 ) RETURNS TEXT[] AS $F$
1311 DECLARE
1312     item    TEXT;
1313     list    TEXT[] := '{}';
1314     sublist TEXT[] := '{}';
1315 BEGIN
1316     FOR I IN 1 .. CHARACTER_LENGTH(raw_word) LOOP
1317         item := SUBSTRING(raw_word FROM 1 FOR I - 1) || SUBSTRING(raw_word FROM I + 1);
1318         IF NOT list @> ARRAY[item] THEN
1319             list := item || list;
1320             IF dist < maxED AND CHARACTER_LENGTH(raw_word) > dist + 1 THEN
1321                 sublist := search.symspell_generate_edits(item, dist + 1, maxED) || sublist;
1322             END IF;
1323         END IF;
1324     END LOOP;
1325
1326     IF dist = 1 THEN
1327         RETURN evergreen.text_array_merge_unique(list, sublist);
1328     ELSE
1329         RETURN list || sublist;
1330     END IF;
1331 END;
1332 $F$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
1333
1334 -- DROP TYPE search.symspell_lookup_output CASCADE;
1335 CREATE TYPE search.symspell_lookup_output AS (
1336     suggestion          TEXT,
1337     suggestion_count    INT,
1338     lev_distance        INT,
1339     pg_trgm_sim         NUMERIC,
1340     qwerty_kb_match     NUMERIC,
1341     soundex_sim         NUMERIC,
1342     input               TEXT,
1343     norm_input          TEXT,
1344     prefix_key          TEXT,
1345     prefix_key_count    INT,
1346     word_pos            INT
1347 );
1348
1349
1350 CREATE OR REPLACE FUNCTION search.symspell_lookup(
1351     raw_input text,
1352     search_class text,
1353     verbosity integer DEFAULT 2,
1354     xfer_case boolean DEFAULT false,
1355     count_threshold integer DEFAULT 1,
1356     soundex_weight integer DEFAULT 0,
1357     pg_trgm_weight integer DEFAULT 0,
1358     kbdist_weight integer DEFAULT 0
1359 ) RETURNS SETOF search.symspell_lookup_output LANGUAGE plpgsql AS $function$
1360 DECLARE
1361     prefix_length INT;
1362     maxED         INT;
1363     good_suggs  HSTORE;
1364     word_list   TEXT[];
1365     edit_list   TEXT[] := '{}';
1366     seen_list   TEXT[] := '{}';
1367     output      search.symspell_lookup_output;
1368     output_list search.symspell_lookup_output[];
1369     entry       RECORD;
1370     entry_key   TEXT;
1371     prefix_key  TEXT;
1372     sugg        TEXT;
1373     input       TEXT;
1374     word        TEXT;
1375     w_pos       INT := -1;
1376     smallest_ed INT := -1;
1377     global_ed   INT;
1378     i_len       INT;
1379     l_maxED     INT;
1380 BEGIN
1381     SELECT value::INT INTO prefix_length FROM config.internal_flag WHERE name = 'symspell.prefix_length' AND enabled;
1382     prefix_length := COALESCE(prefix_length, 6);
1383
1384     SELECT value::INT INTO maxED FROM config.internal_flag WHERE name = 'symspell.max_edit_distance' AND enabled;
1385     maxED := COALESCE(maxED, 3);
1386
1387     word_list := ARRAY_AGG(x) FROM search.symspell_parse_words(raw_input) x;
1388
1389     -- Common case exact match test for preformance
1390     IF verbosity = 0 AND CARDINALITY(word_list) = 1 AND CHARACTER_LENGTH(word_list[1]) <= prefix_length THEN
1391         EXECUTE
1392           'SELECT  '||search_class||'_suggestions AS suggestions,
1393                    '||search_class||'_count AS count,
1394                    prefix_key
1395              FROM  search.symspell_dictionary
1396              WHERE prefix_key = $1
1397                    AND '||search_class||'_count >= $2
1398                    AND '||search_class||'_suggestions @> ARRAY[$1]'
1399           INTO entry USING evergreen.lowercase(word_list[1]), COALESCE(count_threshold,1);
1400         IF entry.prefix_key IS NOT NULL THEN
1401             output.lev_distance := 0; -- definitionally
1402             output.prefix_key := entry.prefix_key;
1403             output.prefix_key_count := entry.count;
1404             output.suggestion_count := entry.count;
1405             output.input := word_list[1];
1406             IF xfer_case THEN
1407                 output.suggestion := search.symspell_transfer_casing(output.input, entry.prefix_key);
1408             ELSE
1409                 output.suggestion := entry.prefix_key;
1410             END IF;
1411             output.norm_input := entry.prefix_key;
1412             output.qwerty_kb_match := 1;
1413             output.pg_trgm_sim := 1;
1414             output.soundex_sim := 1;
1415             RETURN NEXT output;
1416             RETURN;
1417         END IF;
1418     END IF;
1419
1420     <<word_loop>>
1421     FOREACH word IN ARRAY word_list LOOP
1422         w_pos := w_pos + 1;
1423         input := evergreen.lowercase(word);
1424         i_len := CHARACTER_LENGTH(input);
1425         l_maxED := maxED;
1426
1427         IF CHARACTER_LENGTH(input) > prefix_length THEN
1428             prefix_key := SUBSTRING(input FROM 1 FOR prefix_length);
1429             edit_list := ARRAY[input,prefix_key] || search.symspell_generate_edits(prefix_key, 1, l_maxED);
1430         ELSE
1431             edit_list := input || search.symspell_generate_edits(input, 1, l_maxED);
1432         END IF;
1433
1434         SELECT ARRAY_AGG(x ORDER BY CHARACTER_LENGTH(x) DESC) INTO edit_list FROM UNNEST(edit_list) x;
1435
1436         output_list := '{}';
1437         seen_list := '{}';
1438         global_ed := NULL;
1439
1440         <<entry_key_loop>>
1441         FOREACH entry_key IN ARRAY edit_list LOOP
1442             smallest_ed := -1;
1443             IF global_ed IS NOT NULL THEN
1444                 smallest_ed := global_ed;
1445             END IF;
1446
1447             FOR entry IN EXECUTE
1448                 'SELECT  '||search_class||'_suggestions AS suggestions,
1449                          '||search_class||'_count AS count,
1450                          prefix_key
1451                    FROM  search.symspell_dictionary
1452                    WHERE prefix_key = $1
1453                          AND '||search_class||'_suggestions IS NOT NULL'
1454                 USING entry_key
1455             LOOP
1456
1457                 SELECT  HSTORE(
1458                             ARRAY_AGG(
1459                                 ARRAY[s, evergreen.levenshtein_damerau_edistance(input,s,l_maxED)::TEXT]
1460                                     ORDER BY evergreen.levenshtein_damerau_edistance(input,s,l_maxED) DESC
1461                             )
1462                         )
1463                   INTO  good_suggs
1464                   FROM  UNNEST(entry.suggestions) s
1465                   WHERE (ABS(CHARACTER_LENGTH(s) - i_len) <= maxEd AND evergreen.levenshtein_damerau_edistance(input,s,l_maxED) BETWEEN 0 AND l_maxED)
1466                         AND NOT seen_list @> ARRAY[s];
1467
1468                 CONTINUE WHEN good_suggs IS NULL;
1469
1470                 FOR sugg, output.suggestion_count IN EXECUTE
1471                     'SELECT  prefix_key, '||search_class||'_count
1472                        FROM  search.symspell_dictionary
1473                        WHERE prefix_key = ANY ($1)
1474                              AND '||search_class||'_count >= $2'
1475                     USING AKEYS(good_suggs), COALESCE(count_threshold,1)
1476                 LOOP
1477
1478                     output.lev_distance := good_suggs->sugg;
1479                     seen_list := seen_list || sugg;
1480
1481                     -- Track the smallest edit distance among suggestions from this prefix key.
1482                     IF smallest_ed = -1 OR output.lev_distance < smallest_ed THEN
1483                         smallest_ed := output.lev_distance;
1484                     END IF;
1485
1486                     -- Track the smallest edit distance for all prefix keys for this word.
1487                     IF global_ed IS NULL OR smallest_ed < global_ed THEN
1488                         global_ed = smallest_ed;
1489                         -- And if low verbosity, ignore suggs with a larger distance from here on.
1490                         IF verbosity <= 1 THEN
1491                             l_maxED := global_ed;
1492                         END IF;
1493                     END IF;
1494
1495                     -- Lev distance is our main similarity measure. While
1496                     -- trgm or soundex similarity could be the main filter,
1497                     -- Lev is both language agnostic and faster.
1498                     --
1499                     -- Here we will skip suggestions that have a longer edit distance
1500                     -- than the shortest we've already found. This is simply an
1501                     -- optimization that allows us to avoid further processing
1502                     -- of this entry. It would be filtered out later.
1503                     CONTINUE WHEN output.lev_distance > global_ed AND verbosity <= 1;
1504
1505                     -- If we have an exact match on the suggestion key we can also avoid
1506                     -- some function calls.
1507                     IF output.lev_distance = 0 THEN
1508                         output.qwerty_kb_match := 1;
1509                         output.pg_trgm_sim := 1;
1510                         output.soundex_sim := 1;
1511                     ELSE
1512                         IF kbdist_weight THEN
1513                             output.qwerty_kb_match := evergreen.qwerty_keyboard_distance_match(input, sugg);
1514                         ELSE
1515                             output.qwerty_kb_match := 0;
1516                         END IF;
1517                         IF pg_trgm_weight THEN
1518                             output.pg_trgm_sim := similarity(input, sugg);
1519                         ELSE
1520                             output.pg_trgm_sim := 0;
1521                         END IF;
1522                         IF soundex_weight THEN
1523                             output.soundex_sim := difference(input, sugg) / 4.0;
1524                         ELSE
1525                             output.soundex_sim := 0;
1526                         END IF;
1527                     END IF;
1528
1529                     -- Fill in some fields
1530                     IF xfer_case AND input <> word THEN
1531                         output.suggestion := search.symspell_transfer_casing(word, sugg);
1532                     ELSE
1533                         output.suggestion := sugg;
1534                     END IF;
1535                     output.prefix_key := entry.prefix_key;
1536                     output.prefix_key_count := entry.count;
1537                     output.input := word;
1538                     output.norm_input := input;
1539                     output.word_pos := w_pos;
1540
1541                     -- We can't "cache" a set of generated records directly, so
1542                     -- here we build up an array of search.symspell_lookup_output
1543                     -- records that we can revivicate later as a table using UNNEST().
1544                     output_list := output_list || output;
1545
1546                     EXIT entry_key_loop WHEN smallest_ed = 0 AND verbosity = 0; -- exact match early exit
1547                     CONTINUE entry_key_loop WHEN smallest_ed = 0 AND verbosity = 1; -- exact match early jump to the next key
1548
1549                 END LOOP; -- loop over suggestions
1550             END LOOP; -- loop over entries
1551         END LOOP; -- loop over entry_keys
1552
1553         -- Now we're done examining this word
1554         IF verbosity = 0 THEN
1555             -- Return the "best" suggestion from the smallest edit
1556             -- distance group.  We define best based on the weighting
1557             -- of the non-lev similarity measures and use the suggestion
1558             -- use count to break ties.
1559             RETURN QUERY
1560                 SELECT * FROM UNNEST(output_list)
1561                     ORDER BY lev_distance,
1562                         (soundex_sim * COALESCE(soundex_weight,0))
1563                             + (pg_trgm_sim * COALESCE(pg_trgm_weight,0))
1564                             + (qwerty_kb_match * COALESCE(kbdist_weight,0)) DESC,
1565                         suggestion_count DESC
1566                         LIMIT 1;
1567         ELSIF verbosity = 1 THEN
1568             -- Return all suggestions from the smallest
1569             -- edit distance group.
1570             RETURN QUERY
1571                 SELECT * FROM UNNEST(output_list) WHERE lev_distance = smallest_ed
1572                     ORDER BY (soundex_sim * COALESCE(soundex_weight,0))
1573                             + (pg_trgm_sim * COALESCE(pg_trgm_weight,0))
1574                             + (qwerty_kb_match * COALESCE(kbdist_weight,0)) DESC,
1575                         suggestion_count DESC;
1576         ELSIF verbosity = 2 THEN
1577             -- Return everything we find, along with relevant stats
1578             RETURN QUERY
1579                 SELECT * FROM UNNEST(output_list)
1580                     ORDER BY lev_distance,
1581                         (soundex_sim * COALESCE(soundex_weight,0))
1582                             + (pg_trgm_sim * COALESCE(pg_trgm_weight,0))
1583                             + (qwerty_kb_match * COALESCE(kbdist_weight,0)) DESC,
1584                         suggestion_count DESC;
1585         ELSIF verbosity = 3 THEN
1586             -- Return everything we find from the two smallest edit distance groups
1587             RETURN QUERY
1588                 SELECT * FROM UNNEST(output_list)
1589                     WHERE lev_distance IN (SELECT DISTINCT lev_distance FROM UNNEST(output_list) ORDER BY 1 LIMIT 2)
1590                     ORDER BY lev_distance,
1591                         (soundex_sim * COALESCE(soundex_weight,0))
1592                             + (pg_trgm_sim * COALESCE(pg_trgm_weight,0))
1593                             + (qwerty_kb_match * COALESCE(kbdist_weight,0)) DESC,
1594                         suggestion_count DESC;
1595         ELSIF verbosity = 4 THEN
1596             -- Return everything we find from the two smallest edit distance groups that are NOT 0 distance
1597             RETURN QUERY
1598                 SELECT * FROM UNNEST(output_list)
1599                     WHERE lev_distance IN (SELECT DISTINCT lev_distance FROM UNNEST(output_list) WHERE lev_distance > 0 ORDER BY 1 LIMIT 2)
1600                     ORDER BY lev_distance,
1601                         (soundex_sim * COALESCE(soundex_weight,0))
1602                             + (pg_trgm_sim * COALESCE(pg_trgm_weight,0))
1603                             + (qwerty_kb_match * COALESCE(kbdist_weight,0)) DESC,
1604                         suggestion_count DESC;
1605         END IF;
1606     END LOOP; -- loop over words
1607 END;
1608 $function$;
1609
1610 CREATE OR REPLACE FUNCTION search.symspell_build_raw_entry (
1611     raw_input       TEXT,
1612     source_class    TEXT,
1613     no_limit        BOOL DEFAULT FALSE,
1614     prefix_length   INT DEFAULT 6,
1615     maxED           INT DEFAULT 3
1616 ) RETURNS SETOF search.symspell_dictionary AS $F$
1617 DECLARE
1618     key         TEXT;
1619     del_key     TEXT;
1620     key_list    TEXT[];
1621     entry       search.symspell_dictionary%ROWTYPE;
1622 BEGIN
1623     key := raw_input;
1624
1625     IF NOT no_limit AND CHARACTER_LENGTH(raw_input) > prefix_length THEN
1626         key := SUBSTRING(key FROM 1 FOR prefix_length);
1627         key_list := ARRAY[raw_input, key];
1628     ELSE
1629         key_list := ARRAY[key];
1630     END IF;
1631
1632     FOREACH del_key IN ARRAY key_list LOOP
1633         -- skip empty keys
1634         CONTINUE WHEN del_key IS NULL OR CHARACTER_LENGTH(del_key) = 0;
1635
1636         entry.prefix_key := del_key;
1637
1638         entry.keyword_count := 0;
1639         entry.title_count := 0;
1640         entry.author_count := 0;
1641         entry.subject_count := 0;
1642         entry.series_count := 0;
1643         entry.identifier_count := 0;
1644
1645         entry.keyword_suggestions := '{}';
1646         entry.title_suggestions := '{}';
1647         entry.author_suggestions := '{}';
1648         entry.subject_suggestions := '{}';
1649         entry.series_suggestions := '{}';
1650         entry.identifier_suggestions := '{}';
1651
1652         IF source_class = 'keyword' THEN entry.keyword_suggestions := ARRAY[raw_input]; END IF;
1653         IF source_class = 'title' THEN entry.title_suggestions := ARRAY[raw_input]; END IF;
1654         IF source_class = 'author' THEN entry.author_suggestions := ARRAY[raw_input]; END IF;
1655         IF source_class = 'subject' THEN entry.subject_suggestions := ARRAY[raw_input]; END IF;
1656         IF source_class = 'series' THEN entry.series_suggestions := ARRAY[raw_input]; END IF;
1657         IF source_class = 'identifier' THEN entry.identifier_suggestions := ARRAY[raw_input]; END IF;
1658         IF source_class = 'keyword' THEN entry.keyword_suggestions := ARRAY[raw_input]; END IF;
1659
1660         IF del_key = raw_input THEN
1661             IF source_class = 'keyword' THEN entry.keyword_count := 1; END IF;
1662             IF source_class = 'title' THEN entry.title_count := 1; END IF;
1663             IF source_class = 'author' THEN entry.author_count := 1; END IF;
1664             IF source_class = 'subject' THEN entry.subject_count := 1; END IF;
1665             IF source_class = 'series' THEN entry.series_count := 1; END IF;
1666             IF source_class = 'identifier' THEN entry.identifier_count := 1; END IF;
1667         END IF;
1668
1669         RETURN NEXT entry;
1670     END LOOP;
1671
1672     FOR del_key IN SELECT x FROM UNNEST(search.symspell_generate_edits(key, 1, maxED)) x LOOP
1673
1674         -- skip empty keys
1675         CONTINUE WHEN del_key IS NULL OR CHARACTER_LENGTH(del_key) = 0;
1676         -- skip suggestions that are already too long for the prefix key
1677         CONTINUE WHEN CHARACTER_LENGTH(del_key) <= (prefix_length - maxED) AND CHARACTER_LENGTH(raw_input) > prefix_length;
1678
1679         entry.keyword_suggestions := '{}';
1680         entry.title_suggestions := '{}';
1681         entry.author_suggestions := '{}';
1682         entry.subject_suggestions := '{}';
1683         entry.series_suggestions := '{}';
1684         entry.identifier_suggestions := '{}';
1685
1686         IF source_class = 'keyword' THEN entry.keyword_count := 0; END IF;
1687         IF source_class = 'title' THEN entry.title_count := 0; END IF;
1688         IF source_class = 'author' THEN entry.author_count := 0; END IF;
1689         IF source_class = 'subject' THEN entry.subject_count := 0; END IF;
1690         IF source_class = 'series' THEN entry.series_count := 0; END IF;
1691         IF source_class = 'identifier' THEN entry.identifier_count := 0; END IF;
1692
1693         entry.prefix_key := del_key;
1694
1695         IF source_class = 'keyword' THEN entry.keyword_suggestions := ARRAY[raw_input]; END IF;
1696         IF source_class = 'title' THEN entry.title_suggestions := ARRAY[raw_input]; END IF;
1697         IF source_class = 'author' THEN entry.author_suggestions := ARRAY[raw_input]; END IF;
1698         IF source_class = 'subject' THEN entry.subject_suggestions := ARRAY[raw_input]; END IF;
1699         IF source_class = 'series' THEN entry.series_suggestions := ARRAY[raw_input]; END IF;
1700         IF source_class = 'identifier' THEN entry.identifier_suggestions := ARRAY[raw_input]; END IF;
1701         IF source_class = 'keyword' THEN entry.keyword_suggestions := ARRAY[raw_input]; END IF;
1702
1703         RETURN NEXT entry;
1704     END LOOP;
1705
1706 END;
1707 $F$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
1708
1709 CREATE OR REPLACE FUNCTION search.symspell_build_entries (
1710     full_input      TEXT,
1711     source_class    TEXT,
1712     old_input       TEXT DEFAULT NULL,
1713     include_phrases BOOL DEFAULT FALSE
1714 ) RETURNS SETOF search.symspell_dictionary AS $F$
1715 DECLARE
1716     prefix_length   INT;
1717     maxED           INT;
1718     word_list   TEXT[];
1719     input       TEXT;
1720     word        TEXT;
1721     entry       search.symspell_dictionary;
1722 BEGIN
1723     IF full_input IS NOT NULL THEN
1724         SELECT value::INT INTO prefix_length FROM config.internal_flag WHERE name = 'symspell.prefix_length' AND enabled;
1725         prefix_length := COALESCE(prefix_length, 6);
1726
1727         SELECT value::INT INTO maxED FROM config.internal_flag WHERE name = 'symspell.max_edit_distance' AND enabled;
1728         maxED := COALESCE(maxED, 3);
1729
1730         input := evergreen.lowercase(full_input);
1731         word_list := ARRAY_AGG(x) FROM search.symspell_parse_words_distinct(input) x;
1732         IF word_list IS NULL THEN
1733             RETURN;
1734         END IF;
1735
1736         IF CARDINALITY(word_list) > 1 AND include_phrases THEN
1737             RETURN QUERY SELECT * FROM search.symspell_build_raw_entry(input, source_class, TRUE, prefix_length, maxED);
1738         END IF;
1739
1740         FOREACH word IN ARRAY word_list LOOP
1741             -- Skip words that have runs of 5 or more digits (I'm looking at you, ISxNs)
1742             CONTINUE WHEN CHARACTER_LENGTH(word) > 4 AND word ~ '\d{5,}';
1743             RETURN QUERY SELECT * FROM search.symspell_build_raw_entry(word, source_class, FALSE, prefix_length, maxED);
1744         END LOOP;
1745     END IF;
1746
1747     IF old_input IS NOT NULL THEN
1748         input := evergreen.lowercase(old_input);
1749
1750         FOR word IN SELECT x FROM search.symspell_parse_words_distinct(input) x LOOP
1751             -- similarly skip words that have 5 or more digits here to
1752             -- avoid adding erroneous prefix deletion entries to the dictionary
1753             CONTINUE WHEN CHARACTER_LENGTH(word) > 4 AND word ~ '\d{5,}';
1754             entry.prefix_key := word;
1755
1756             entry.keyword_count := 0;
1757             entry.title_count := 0;
1758             entry.author_count := 0;
1759             entry.subject_count := 0;
1760             entry.series_count := 0;
1761             entry.identifier_count := 0;
1762
1763             entry.keyword_suggestions := '{}';
1764             entry.title_suggestions := '{}';
1765             entry.author_suggestions := '{}';
1766             entry.subject_suggestions := '{}';
1767             entry.series_suggestions := '{}';
1768             entry.identifier_suggestions := '{}';
1769
1770             IF source_class = 'keyword' THEN entry.keyword_count := -1; END IF;
1771             IF source_class = 'title' THEN entry.title_count := -1; END IF;
1772             IF source_class = 'author' THEN entry.author_count := -1; END IF;
1773             IF source_class = 'subject' THEN entry.subject_count := -1; END IF;
1774             IF source_class = 'series' THEN entry.series_count := -1; END IF;
1775             IF source_class = 'identifier' THEN entry.identifier_count := -1; END IF;
1776
1777             RETURN NEXT entry;
1778         END LOOP;
1779     END IF;
1780 END;
1781 $F$ LANGUAGE PLPGSQL;
1782
1783 CREATE OR REPLACE FUNCTION search.symspell_maintain_entries () RETURNS TRIGGER AS $f$
1784 DECLARE
1785     search_class    TEXT;
1786     new_value       TEXT := NULL;
1787     old_value       TEXT := NULL;
1788 BEGIN
1789     search_class := COALESCE(TG_ARGV[0], SPLIT_PART(TG_TABLE_NAME,'_',1));
1790
1791     IF TG_OP IN ('INSERT', 'UPDATE') THEN
1792         new_value := NEW.value;
1793     END IF;
1794
1795     IF TG_OP IN ('DELETE', 'UPDATE') THEN
1796         old_value := OLD.value;
1797     END IF;
1798
1799     IF new_value = old_value THEN
1800         -- same, move along
1801     ELSE
1802         INSERT INTO search.symspell_dictionary_updates
1803             SELECT  txid_current(), *
1804               FROM  search.symspell_build_entries(
1805                         new_value,
1806                         search_class,
1807                         old_value
1808                     );
1809     END IF;
1810
1811     RETURN NULL; -- always fired AFTER
1812 END;
1813 $f$ LANGUAGE PLPGSQL;
1814
1815 CREATE TRIGGER maintain_symspell_entries_tgr
1816     AFTER INSERT OR UPDATE OR DELETE ON metabib.title_field_entry
1817     FOR EACH ROW EXECUTE PROCEDURE search.symspell_maintain_entries();
1818
1819 CREATE TRIGGER maintain_symspell_entries_tgr
1820     AFTER INSERT OR UPDATE OR DELETE ON metabib.author_field_entry
1821     FOR EACH ROW EXECUTE PROCEDURE search.symspell_maintain_entries();
1822
1823 CREATE TRIGGER maintain_symspell_entries_tgr
1824     AFTER INSERT OR UPDATE OR DELETE ON metabib.subject_field_entry
1825     FOR EACH ROW EXECUTE PROCEDURE search.symspell_maintain_entries();
1826
1827 CREATE TRIGGER maintain_symspell_entries_tgr
1828     AFTER INSERT OR UPDATE OR DELETE ON metabib.series_field_entry
1829     FOR EACH ROW EXECUTE PROCEDURE search.symspell_maintain_entries();
1830
1831 CREATE TRIGGER maintain_symspell_entries_tgr
1832     AFTER INSERT OR UPDATE OR DELETE ON metabib.keyword_field_entry
1833     FOR EACH ROW EXECUTE PROCEDURE search.symspell_maintain_entries();
1834
1835 CREATE TRIGGER maintain_symspell_entries_tgr
1836     AFTER INSERT OR UPDATE OR DELETE ON metabib.identifier_field_entry
1837     FOR EACH ROW EXECUTE PROCEDURE search.symspell_maintain_entries();
1838
1839 COMMIT;
1840