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