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, badges TEXT, popularity NUMERIC );
  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                 current_res.badges = core_result.badges;
 194                 current_res.popularity = core_result.popularity;
 195
 196                 tmp_int := 1;
 197                 IF metarecord THEN
 198                     SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
 199                 END IF;
 200
 201                 IF tmp_int = 1 THEN
 202                     current_res.record = core_result.records[1];
 203                 ELSE
 204                     current_res.record = NULL;
 205                 END IF;
 206
 207                 RETURN NEXT current_res;
 208
 209                 CONTINUE;
 210             END IF;
 211
 212             PERFORM 1
 213               FROM  asset.call_number cn
 214                     JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
 215                     JOIN asset.uri uri ON (map.uri = uri.id)
 216               WHERE NOT cn.deleted
 217                     AND cn.label = '##URI##'
 218                     AND uri.active
 219                     AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
 220                     AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
 221                     AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
 222               LIMIT 1;
 223
 224             IF FOUND THEN
 225                 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
 226                 visible_count := visible_count + 1;
 227
 228                 current_res.id = core_result.id;
 229                 current_res.rel = core_result.rel;
 230                 current_res.badges = core_result.badges;
 231                 current_res.popularity = core_result.popularity;
 232
 233                 tmp_int := 1;
 234                 IF metarecord THEN
 235                     SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
 236                 END IF;
 237
 238                 IF tmp_int = 1 THEN
 239                     current_res.record = core_result.records[1];
 240                 ELSE
 241                     current_res.record = NULL;
 242                 END IF;
 243
 244                 RETURN NEXT current_res;
 245
 246                 CONTINUE;
 247             END IF;
 248
 249             IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
 250
 251                 PERFORM 1
 252                   FROM  asset.call_number cn
 253                         JOIN asset.copy cp ON (cp.call_number = cn.id)
 254                   WHERE NOT cn.deleted
 255                         AND NOT cp.deleted
 256                         AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
 257                         AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
 258                         AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 259                   LIMIT 1;
 260
 261                 IF NOT FOUND THEN
 262                     PERFORM 1
 263                       FROM  biblio.peer_bib_copy_map pr
 264                             JOIN asset.copy cp ON (cp.id = pr.target_copy)
 265                       WHERE NOT cp.deleted
 266                             AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
 267                             AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
 268                             AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 269                       LIMIT 1;
 270
 271                     IF NOT FOUND THEN
 272                     -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
 273                         excluded_count := excluded_count + 1;
 274                         CONTINUE;
 275                     END IF;
 276                 END IF;
 277
 278             END IF;
 279
 280             IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
 281
 282                 PERFORM 1
 283                   FROM  asset.call_number cn
 284                         JOIN asset.copy cp ON (cp.call_number = cn.id)
 285                   WHERE NOT cn.deleted
 286                         AND NOT cp.deleted
 287                         AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
 288                         AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
 289                         AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 290                   LIMIT 1;
 291
 292                 IF NOT FOUND THEN
 293                     PERFORM 1
 294                       FROM  biblio.peer_bib_copy_map pr
 295                             JOIN asset.copy cp ON (cp.id = pr.target_copy)
 296                       WHERE NOT cp.deleted
 297                             AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
 298                             AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
 299                             AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 300                       LIMIT 1;
 301
 302                     IF NOT FOUND THEN
 303                         -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
 304                         excluded_count := excluded_count + 1;
 305                         CONTINUE;
 306                     END IF;
 307                 END IF;
 308
 309             END IF;
 310
 311             IF staff IS NULL OR NOT staff THEN
 312
 313                 PERFORM 1
 314                   FROM  asset.opac_visible_copies
 315                   WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 316                         AND record IN ( SELECT * FROM unnest( core_result.records ) )
 317                   LIMIT 1;
 318
 319                 IF NOT FOUND THEN
 320                     PERFORM 1
 321                       FROM  biblio.peer_bib_copy_map pr
 322                             JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
 323                       WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 324                             AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
 325                       LIMIT 1;
 326
 327                     IF NOT FOUND THEN
 328
 329                         -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
 330                         excluded_count := excluded_count + 1;
 331                         CONTINUE;
 332                     END IF;
 333                 END IF;
 334
 335             ELSE
 336
 337                 PERFORM 1
 338                   FROM  asset.call_number cn
 339                         JOIN asset.copy cp ON (cp.call_number = cn.id)
 340                   WHERE NOT cn.deleted
 341                         AND NOT cp.deleted
 342                         AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 343                         AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
 344                   LIMIT 1;
 345
 346                 IF NOT FOUND THEN
 347
 348                     PERFORM 1
 349                       FROM  biblio.peer_bib_copy_map pr
 350                             JOIN asset.copy cp ON (cp.id = pr.target_copy)
 351                       WHERE NOT cp.deleted
 352                             AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 353                             AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
 354                       LIMIT 1;
 355
 356                     IF NOT FOUND THEN
 357
 358                         PERFORM 1
 359                           FROM  asset.call_number cn
 360                                 JOIN asset.copy cp ON (cp.call_number = cn.id)
 361                           WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
 362                                 AND NOT cp.deleted
 363                           LIMIT 1;
 364
 365                         IF NOT FOUND THEN
 366                             -- Recheck Located URI visibility in the case of no "foreign" copies
 367                             PERFORM 1
 368                               FROM  asset.call_number cn
 369                                     JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
 370                                     JOIN asset.uri uri ON (map.uri = uri.id)
 371                               WHERE NOT cn.deleted
 372                                     AND cn.label = '##URI##'
 373                                     AND uri.active
 374                                     AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
 375                                     AND cn.owning_lib NOT IN ( SELECT * FROM unnest( luri_org_list ) )
 376                               LIMIT 1;
 377
 378                             IF FOUND THEN
 379                                 -- RAISE NOTICE ' % were excluded for foreign located URIs... ', core_result.records;
 380                                 excluded_count := excluded_count + 1;
 381                                 CONTINUE;
 382                             END IF;
 383                         ELSE
 384                             -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
 385                             excluded_count := excluded_count + 1;
 386                             CONTINUE;
 387                         END IF;
 388                     END IF;
 389
 390                 END IF;
 391
 392             END IF;
 393
 394         END IF;
 395
 396         visible_count := visible_count + 1;
 397
 398         current_res.id = core_result.id;
 399         current_res.rel = core_result.rel;
 400         current_res.badges = core_result.badges;
 401         current_res.popularity = core_result.popularity;
 402
 403         tmp_int := 1;
 404         IF metarecord THEN
 405             SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
 406         END IF;
 407
 408         IF tmp_int = 1 THEN
 409             current_res.record = core_result.records[1];
 410         ELSE
 411             current_res.record = NULL;
 412         END IF;
 413
 414         RETURN NEXT current_res;
 415
 416         IF visible_count % 1000 = 0 THEN
 417             -- RAISE NOTICE ' % visible so far ... ', visible_count;
 418         END IF;
 419
 420     END LOOP;
 421
 422     current_res.id = NULL;
 423     current_res.rel = NULL;
 424     current_res.record = NULL;
 425     current_res.badges = NULL;
 426     current_res.popularity = NULL;
 427     current_res.total = total_count;
 428     current_res.checked = check_count;
 429     current_res.deleted = deleted_count;
 430     current_res.visible = visible_count;
 431     current_res.excluded = excluded_count;
 432
 433     CLOSE core_cursor;
 434
 435     RETURN NEXT current_res;
 436
 437 END;
 438 $func$ LANGUAGE PLPGSQL;
 439
 440 CREATE OR REPLACE FUNCTION search.facets_for_record_set(ignore_facet_classes TEXT[], hits BIGINT[]) RETURNS TABLE (id INT, value TEXT, count BIGINT) AS $$
 441     SELECT id, value, count FROM (
 442         SELECT mfae.field AS id,
 443                mfae.value,
 444                COUNT(DISTINCT mmrsm.source),
 445                row_number() OVER (
 446                 PARTITION BY mfae.field ORDER BY COUNT(distinct mmrsm.source) DESC
 447                ) AS rownum
 448         FROM metabib.facet_entry mfae
 449         JOIN metabib.metarecord_source_map mmrsm ON (mfae.source = mmrsm.source)
 450         JOIN config.metabib_field cmf ON (cmf.id = mfae.field)
 451         WHERE mmrsm.source IN (SELECT * FROM unnest($2))
 452         AND cmf.facet_field
 453         AND cmf.field_class NOT IN (SELECT * FROM unnest($1))
 454         GROUP by 1, 2
 455     ) all_facets
 456     WHERE rownum <= (SELECT COALESCE((SELECT value::INT FROM config.global_flag WHERE name = 'search.max_facets_per_field' AND enabled), 1000));
 457 $$ LANGUAGE SQL;
 458
 459 CREATE OR REPLACE FUNCTION search.facets_for_metarecord_set(ignore_facet_classes TEXT[], hits BIGINT[]) RETURNS TABLE (id INT, value TEXT, count BIGINT) AS $$
 460     SELECT id, value, count FROM (
 461         SELECT mfae.field AS id,
 462                mfae.value,
 463                COUNT(DISTINCT mmrsm.metarecord),
 464                row_number() OVER (
 465                 PARTITION BY mfae.field ORDER BY COUNT(distinct mmrsm.metarecord) DESC
 466                ) AS rownum
 467         FROM metabib.facet_entry mfae
 468         JOIN metabib.metarecord_source_map mmrsm ON (mfae.source = mmrsm.source)
 469         JOIN config.metabib_field cmf ON (cmf.id = mfae.field)
 470         WHERE mmrsm.metarecord IN (SELECT * FROM unnest($2))
 471         AND cmf.facet_field
 472         AND cmf.field_class NOT IN (SELECT * FROM unnest($1))
 473         GROUP by 1, 2
 474     ) all_facets
 475     WHERE rownum <= (SELECT COALESCE((SELECT value::INT FROM config.global_flag WHERE name = 'search.max_facets_per_field' AND enabled), 1000));
 476 $$ LANGUAGE SQL;
 477
 478 COMMIT;