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 TYPE search.search_result AS ( id BIGINT, rel NUMERIC, record INT, total INT, checked INT, visible INT, deleted INT, excluded INT );
  65 CREATE TYPE search.search_args AS ( id INT, field_class TEXT, field_name TEXT, table_alias TEXT, term TEXT, term_type TEXT );
  66
  67 CREATE OR REPLACE FUNCTION search.query_parser_fts (
  68
  69     param_search_ou INT,
  70     param_depth     INT,
  71     param_query     TEXT,
  72     param_statuses  INT[],
  73     param_locations INT[],
  74     param_offset    INT,
  75     param_check     INT,
  76     param_limit     INT,
  77     metarecord      BOOL,
  78     staff           BOOL,
  79     deleted_search  BOOL,
  80     param_pref_ou   INT DEFAULT NULL
  81 ) RETURNS SETOF search.search_result AS $func$
  82 DECLARE
  83
  84     current_res         search.search_result%ROWTYPE;
  85     search_org_list     INT[];
  86     luri_org_list       INT[];
  87     tmp_int_list        INT[];
  88
  89     check_limit         INT;
  90     core_limit          INT;
  91     core_offset         INT;
  92     tmp_int             INT;
  93
  94     core_result         RECORD;
  95     core_cursor         REFCURSOR;
  96     core_rel_query      TEXT;
  97
  98     total_count         INT := 0;
  99     check_count         INT := 0;
 100     deleted_count       INT := 0;
 101     visible_count       INT := 0;
 102     excluded_count      INT := 0;
 103
 104     luri_as_copy        BOOL;
 105 BEGIN
 106
 107     check_limit := COALESCE( param_check, 1000 );
 108     core_limit  := COALESCE( param_limit, 25000 );
 109     core_offset := COALESCE( param_offset, 0 );
 110
 111     SELECT COALESCE( enabled, FALSE ) INTO luri_as_copy FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy';
 112
 113     -- core_skip_chk := COALESCE( param_skip_chk, 1 );
 114
 115     IF param_search_ou > 0 THEN
 116         IF param_depth IS NOT NULL THEN
 117             SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
 118         ELSE
 119             SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
 120         END IF;
 121
 122         IF luri_as_copy THEN
 123             SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_full_path( param_search_ou );
 124         ELSE
 125             SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou );
 126         END IF;
 127
 128     ELSIF param_search_ou < 0 THEN
 129         SELECT ARRAY_AGG(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
 130
 131         FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP
 132
 133             IF luri_as_copy THEN
 134                 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( tmp_int );
 135             ELSE
 136                 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int );
 137             END IF;
 138
 139             luri_org_list := luri_org_list || tmp_int_list;
 140         END LOOP;
 141
 142         SELECT ARRAY_AGG(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id);
 143
 144     ELSIF param_search_ou = 0 THEN
 145         -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
 146     END IF;
 147
 148     IF param_pref_ou IS NOT NULL THEN
 149             IF luri_as_copy THEN
 150                 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( param_pref_ou );
 151             ELSE
 152                 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( param_pref_ou );
 153             END IF;
 154
 155         luri_org_list := luri_org_list || tmp_int_list;
 156     END IF;
 157
 158     OPEN core_cursor FOR EXECUTE param_query;
 159
 160     LOOP
 161
 162         FETCH core_cursor INTO core_result;
 163         EXIT WHEN NOT FOUND;
 164         EXIT WHEN total_count >= core_limit;
 165
 166         total_count := total_count + 1;
 167
 168         CONTINUE WHEN total_count NOT BETWEEN  core_offset + 1 AND check_limit + core_offset;
 169
 170         check_count := check_count + 1;
 171
 172         IF NOT deleted_search THEN
 173
 174             PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
 175             IF NOT FOUND THEN
 176                 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
 177                 deleted_count := deleted_count + 1;
 178                 CONTINUE;
 179             END IF;
 180
 181             PERFORM 1
 182               FROM  biblio.record_entry b
 183                     JOIN config.bib_source s ON (b.source = s.id)
 184               WHERE s.transcendant
 185                     AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
 186
 187             IF FOUND THEN
 188                 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
 189                 visible_count := visible_count + 1;
 190
 191                 current_res.id = core_result.id;
 192                 current_res.rel = core_result.rel;
 193
 194                 tmp_int := 1;
 195                 IF metarecord THEN
 196                     SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
 197                 END IF;
 198
 199                 IF tmp_int = 1 THEN
 200                     current_res.record = core_result.records[1];
 201                 ELSE
 202                     current_res.record = NULL;
 203                 END IF;
 204
 205                 RETURN NEXT current_res;
 206
 207                 CONTINUE;
 208             END IF;
 209
 210             PERFORM 1
 211               FROM  asset.call_number cn
 212                     JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
 213                     JOIN asset.uri uri ON (map.uri = uri.id)
 214               WHERE NOT cn.deleted
 215                     AND cn.label = '##URI##'
 216                     AND uri.active
 217                     AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
 218                     AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
 219                     AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
 220               LIMIT 1;
 221
 222             IF FOUND THEN
 223                 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
 224                 visible_count := visible_count + 1;
 225
 226                 current_res.id = core_result.id;
 227                 current_res.rel = core_result.rel;
 228
 229                 tmp_int := 1;
 230                 IF metarecord THEN
 231                     SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
 232                 END IF;
 233
 234                 IF tmp_int = 1 THEN
 235                     current_res.record = core_result.records[1];
 236                 ELSE
 237                     current_res.record = NULL;
 238                 END IF;
 239
 240                 RETURN NEXT current_res;
 241
 242                 CONTINUE;
 243             END IF;
 244
 245             IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
 246
 247                 PERFORM 1
 248                   FROM  asset.call_number cn
 249                         JOIN asset.copy cp ON (cp.call_number = cn.id)
 250                   WHERE NOT cn.deleted
 251                         AND NOT cp.deleted
 252                         AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
 253                         AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
 254                         AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 255                   LIMIT 1;
 256
 257                 IF NOT FOUND THEN
 258                     PERFORM 1
 259                       FROM  biblio.peer_bib_copy_map pr
 260                             JOIN asset.copy cp ON (cp.id = pr.target_copy)
 261                       WHERE NOT cp.deleted
 262                             AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
 263                             AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
 264                             AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 265                       LIMIT 1;
 266
 267                     IF NOT FOUND THEN
 268                     -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
 269                         excluded_count := excluded_count + 1;
 270                         CONTINUE;
 271                     END IF;
 272                 END IF;
 273
 274             END IF;
 275
 276             IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
 277
 278                 PERFORM 1
 279                   FROM  asset.call_number cn
 280                         JOIN asset.copy cp ON (cp.call_number = cn.id)
 281                   WHERE NOT cn.deleted
 282                         AND NOT cp.deleted
 283                         AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
 284                         AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
 285                         AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 286                   LIMIT 1;
 287
 288                 IF NOT FOUND THEN
 289                     PERFORM 1
 290                       FROM  biblio.peer_bib_copy_map pr
 291                             JOIN asset.copy cp ON (cp.id = pr.target_copy)
 292                       WHERE NOT cp.deleted
 293                             AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
 294                             AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
 295                             AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 296                       LIMIT 1;
 297
 298                     IF NOT FOUND THEN
 299                         -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
 300                         excluded_count := excluded_count + 1;
 301                         CONTINUE;
 302                     END IF;
 303                 END IF;
 304
 305             END IF;
 306
 307             IF staff IS NULL OR NOT staff THEN
 308
 309                 PERFORM 1
 310                   FROM  asset.opac_visible_copies
 311                   WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 312                         AND record IN ( SELECT * FROM unnest( core_result.records ) )
 313                   LIMIT 1;
 314
 315                 IF NOT FOUND THEN
 316                     PERFORM 1
 317                       FROM  biblio.peer_bib_copy_map pr
 318                             JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
 319                       WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 320                             AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
 321                       LIMIT 1;
 322
 323                     IF NOT FOUND THEN
 324
 325                         -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
 326                         excluded_count := excluded_count + 1;
 327                         CONTINUE;
 328                     END IF;
 329                 END IF;
 330
 331             ELSE
 332
 333                 PERFORM 1
 334                   FROM  asset.call_number cn
 335                         JOIN asset.copy cp ON (cp.call_number = cn.id)
 336                   WHERE NOT cn.deleted
 337                         AND NOT cp.deleted
 338                         AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 339                         AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
 340                   LIMIT 1;
 341
 342                 IF NOT FOUND THEN
 343
 344                     PERFORM 1
 345                       FROM  biblio.peer_bib_copy_map pr
 346                             JOIN asset.copy cp ON (cp.id = pr.target_copy)
 347                       WHERE NOT cp.deleted
 348                             AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 349                             AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
 350                       LIMIT 1;
 351
 352                     IF NOT FOUND THEN
 353
 354                         PERFORM 1
 355                           FROM  asset.call_number cn
 356                                 JOIN asset.copy cp ON (cp.call_number = cn.id)
 357                           WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
 358                                 AND NOT cp.deleted
 359                           LIMIT 1;
 360
 361                         IF NOT FOUND THEN
 362                             -- Recheck Located URI visibility in the case of no "foreign" copies
 363                             PERFORM 1
 364                               FROM  asset.call_number cn
 365                                     JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
 366                                     JOIN asset.uri uri ON (map.uri = uri.id)
 367                               WHERE NOT cn.deleted
 368                                     AND cn.label = '##URI##'
 369                                     AND uri.active
 370                                     AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
 371                                     AND cn.owning_lib NOT IN ( SELECT * FROM unnest( luri_org_list ) )
 372                               LIMIT 1;
 373
 374                             IF FOUND THEN
 375                                 -- RAISE NOTICE ' % were excluded for foreign located URIs... ', core_result.records;
 376                                 excluded_count := excluded_count + 1;
 377                                 CONTINUE;
 378                             END IF;
 379                         ELSE
 380                             -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
 381                             excluded_count := excluded_count + 1;
 382                             CONTINUE;
 383                         END IF;
 384                     END IF;
 385
 386                 END IF;
 387
 388             END IF;
 389
 390         END IF;
 391
 392         visible_count := visible_count + 1;
 393
 394         current_res.id = core_result.id;
 395         current_res.rel = core_result.rel;
 396
 397         tmp_int := 1;
 398         IF metarecord THEN
 399             SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
 400         END IF;
 401
 402         IF tmp_int = 1 THEN
 403             current_res.record = core_result.records[1];
 404         ELSE
 405             current_res.record = NULL;
 406         END IF;
 407
 408         RETURN NEXT current_res;
 409
 410         IF visible_count % 1000 = 0 THEN
 411             -- RAISE NOTICE ' % visible so far ... ', visible_count;
 412         END IF;
 413
 414     END LOOP;
 415
 416     current_res.id = NULL;
 417     current_res.rel = NULL;
 418     current_res.record = NULL;
 419     current_res.total = total_count;
 420     current_res.checked = check_count;
 421     current_res.deleted = deleted_count;
 422     current_res.visible = visible_count;
 423     current_res.excluded = excluded_count;
 424
 425     CLOSE core_cursor;
 426
 427     RETURN NEXT current_res;
 428
 429 END;
 430 $func$ LANGUAGE PLPGSQL;
 431
 432 CREATE OR REPLACE FUNCTION search.facets_for_record_set(ignore_facet_classes TEXT[], hits BIGINT[]) RETURNS TABLE (id INT, value TEXT, count BIGINT) AS $$
 433     SELECT id, value, count FROM (
 434         SELECT mfae.field AS id,
 435                mfae.value,
 436                COUNT(DISTINCT mmrsm.source),
 437                row_number() OVER (
 438                 PARTITION BY mfae.field ORDER BY COUNT(distinct mmrsm.source) DESC
 439                ) AS rownum
 440         FROM metabib.facet_entry mfae
 441         JOIN metabib.metarecord_source_map mmrsm ON (mfae.source = mmrsm.source)
 442         JOIN config.metabib_field cmf ON (cmf.id = mfae.field)
 443         WHERE mmrsm.source IN (SELECT * FROM unnest($2))
 444         AND cmf.facet_field
 445         AND cmf.field_class NOT IN (SELECT * FROM unnest($1))
 446         GROUP by 1, 2
 447     ) all_facets
 448     WHERE rownum <= (SELECT COALESCE((SELECT value::INT FROM config.global_flag WHERE name = 'search.max_facets_per_field' AND enabled), 1000));
 449 $$ LANGUAGE SQL;
 450
 451 CREATE OR REPLACE FUNCTION search.facets_for_metarecord_set(ignore_facet_classes TEXT[], hits BIGINT[]) RETURNS TABLE (id INT, value TEXT, count BIGINT) AS $$
 452     SELECT id, value, count FROM (
 453         SELECT mfae.field AS id,
 454                mfae.value,
 455                COUNT(DISTINCT mmrsm.metarecord),
 456                row_number() OVER (
 457                 PARTITION BY mfae.field ORDER BY COUNT(distinct mmrsm.metarecord) DESC
 458                ) AS rownum
 459         FROM metabib.facet_entry mfae
 460         JOIN metabib.metarecord_source_map mmrsm ON (mfae.source = mmrsm.source)
 461         JOIN config.metabib_field cmf ON (cmf.id = mfae.field)
 462         WHERE mmrsm.metarecord IN (SELECT * FROM unnest($2))
 463         AND cmf.facet_field
 464         AND cmf.field_class NOT IN (SELECT * FROM unnest($1))
 465         GROUP by 1, 2
 466     ) all_facets
 467     WHERE rownum <= (SELECT COALESCE((SELECT value::INT FROM config.global_flag WHERE name = 'search.max_facets_per_field' AND enabled), 1000));
 468 $$ LANGUAGE SQL;
 469
 470 COMMIT;