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