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     param_pref_ou   INT DEFAULT NULL
  49 ) RETURNS SETOF search.search_result AS $func$
  50 DECLARE
  51
  52     current_res         search.search_result%ROWTYPE;
  53     search_org_list     INT[];
  54     luri_org_list       INT[];
  55     tmp_int_list        INT[];
  56
  57     check_limit         INT;
  58     core_limit          INT;
  59     core_offset         INT;
  60     tmp_int             INT;
  61
  62     core_result         RECORD;
  63     core_cursor         REFCURSOR;
  64     core_rel_query      TEXT;
  65
  66     total_count         INT := 0;
  67     check_count         INT := 0;
  68     deleted_count       INT := 0;
  69     visible_count       INT := 0;
  70     excluded_count      INT := 0;
  71
  72 BEGIN
  73
  74     check_limit := COALESCE( param_check, 1000 );
  75     core_limit  := COALESCE( param_limit, 25000 );
  76     core_offset := COALESCE( param_offset, 0 );
  77
  78     -- core_skip_chk := COALESCE( param_skip_chk, 1 );
  79
  80     IF param_search_ou > 0 THEN
  81         IF param_depth IS NOT NULL THEN
  82             SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
  83         ELSE
  84             SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
  85         END IF;
  86
  87         SELECT array_accum(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou );
  88
  89     ELSIF param_search_ou < 0 THEN
  90         SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
  91
  92         FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP
  93             SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int );
  94             luri_org_list := luri_org_list || tmp_int_list;
  95         END LOOP;
  96
  97         SELECT array_accum(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id);
  98
  99     ELSIF param_search_ou = 0 THEN
 100         -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
 101     END IF;
 102
 103     IF param_pref_ou IS NOT NULL THEN
 104         SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors(param_pref_ou);
 105         luri_org_list := luri_org_list || tmp_int_list;
 106     END IF;
 107
 108     OPEN core_cursor FOR EXECUTE param_query;
 109
 110     LOOP
 111
 112         FETCH core_cursor INTO core_result;
 113         EXIT WHEN NOT FOUND;
 114         EXIT WHEN total_count >= core_limit;
 115
 116         total_count := total_count + 1;
 117
 118         CONTINUE WHEN total_count NOT BETWEEN  core_offset + 1 AND check_limit + core_offset;
 119
 120         check_count := check_count + 1;
 121
 122         PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
 123         IF NOT FOUND THEN
 124             -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
 125             deleted_count := deleted_count + 1;
 126             CONTINUE;
 127         END IF;
 128
 129         PERFORM 1
 130           FROM  biblio.record_entry b
 131                 JOIN config.bib_source s ON (b.source = s.id)
 132           WHERE s.transcendant
 133                 AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
 134
 135         IF FOUND THEN
 136             -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
 137             visible_count := visible_count + 1;
 138
 139             current_res.id = core_result.id;
 140             current_res.rel = core_result.rel;
 141
 142             tmp_int := 1;
 143             IF metarecord THEN
 144                 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
 145             END IF;
 146
 147             IF tmp_int = 1 THEN
 148                 current_res.record = core_result.records[1];
 149             ELSE
 150                 current_res.record = NULL;
 151             END IF;
 152
 153             RETURN NEXT current_res;
 154
 155             CONTINUE;
 156         END IF;
 157
 158         PERFORM 1
 159           FROM  asset.call_number cn
 160                 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
 161                 JOIN asset.uri uri ON (map.uri = uri.id)
 162           WHERE NOT cn.deleted
 163                 AND cn.label = '##URI##'
 164                 AND uri.active
 165                 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
 166                 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
 167                 AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
 168           LIMIT 1;
 169
 170         IF FOUND THEN
 171             -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
 172             visible_count := visible_count + 1;
 173
 174             current_res.id = core_result.id;
 175             current_res.rel = core_result.rel;
 176
 177             tmp_int := 1;
 178             IF metarecord THEN
 179                 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
 180             END IF;
 181
 182             IF tmp_int = 1 THEN
 183                 current_res.record = core_result.records[1];
 184             ELSE
 185                 current_res.record = NULL;
 186             END IF;
 187
 188             RETURN NEXT current_res;
 189
 190             CONTINUE;
 191         END IF;
 192
 193         IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
 194
 195             PERFORM 1
 196               FROM  asset.call_number cn
 197                     JOIN asset.copy cp ON (cp.call_number = cn.id)
 198               WHERE NOT cn.deleted
 199                     AND NOT cp.deleted
 200                     AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
 201                     AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
 202                     AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 203               LIMIT 1;
 204
 205             IF NOT FOUND THEN
 206                 PERFORM 1
 207                   FROM  biblio.peer_bib_copy_map pr
 208                         JOIN asset.copy cp ON (cp.id = pr.target_copy)
 209                   WHERE NOT cp.deleted
 210                         AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
 211                         AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
 212                         AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 213                   LIMIT 1;
 214
 215                 IF NOT FOUND THEN
 216                 -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
 217                     excluded_count := excluded_count + 1;
 218                     CONTINUE;
 219                 END IF;
 220             END IF;
 221
 222         END IF;
 223
 224         IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
 225
 226             PERFORM 1
 227               FROM  asset.call_number cn
 228                     JOIN asset.copy cp ON (cp.call_number = cn.id)
 229               WHERE NOT cn.deleted
 230                     AND NOT cp.deleted
 231                     AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
 232                     AND cn.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                 PERFORM 1
 238                   FROM  biblio.peer_bib_copy_map pr
 239                         JOIN asset.copy cp ON (cp.id = pr.target_copy)
 240                   WHERE NOT cp.deleted
 241                         AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
 242                         AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
 243                         AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 244                   LIMIT 1;
 245
 246                 IF NOT FOUND THEN
 247                     -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
 248                     excluded_count := excluded_count + 1;
 249                     CONTINUE;
 250                 END IF;
 251             END IF;
 252
 253         END IF;
 254
 255         IF staff IS NULL OR NOT staff THEN
 256
 257             PERFORM 1
 258               FROM  asset.opac_visible_copies
 259               WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 260                     AND record IN ( SELECT * FROM unnest( core_result.records ) )
 261               LIMIT 1;
 262
 263             IF NOT FOUND THEN
 264                 PERFORM 1
 265                   FROM  biblio.peer_bib_copy_map pr
 266                         JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
 267                   WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 268                         AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
 269                   LIMIT 1;
 270
 271                 IF NOT FOUND THEN
 272
 273                     -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
 274                     excluded_count := excluded_count + 1;
 275                     CONTINUE;
 276                 END IF;
 277             END IF;
 278
 279         ELSE
 280
 281             PERFORM 1
 282               FROM  asset.call_number cn
 283                     JOIN asset.copy cp ON (cp.call_number = cn.id)
 284               WHERE NOT cn.deleted
 285                     AND NOT cp.deleted
 286                     AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 287                     AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
 288               LIMIT 1;
 289
 290             IF NOT FOUND THEN
 291
 292                 PERFORM 1
 293                   FROM  biblio.peer_bib_copy_map pr
 294                         JOIN asset.copy cp ON (cp.id = pr.target_copy)
 295                   WHERE NOT cp.deleted
 296                         AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 297                         AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
 298                   LIMIT 1;
 299
 300                 IF NOT FOUND THEN
 301
 302                     PERFORM 1
 303                       FROM  asset.call_number cn
 304                             JOIN asset.copy cp ON (cp.call_number = cn.id)
 305                       WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
 306                             AND NOT cp.deleted
 307                       LIMIT 1;
 308
 309                     IF FOUND THEN
 310                         -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
 311                         excluded_count := excluded_count + 1;
 312                         CONTINUE;
 313                     END IF;
 314                 END IF;
 315
 316             END IF;
 317
 318         END IF;
 319
 320         visible_count := visible_count + 1;
 321
 322         current_res.id = core_result.id;
 323         current_res.rel = core_result.rel;
 324
 325         tmp_int := 1;
 326         IF metarecord THEN
 327             SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
 328         END IF;
 329
 330         IF tmp_int = 1 THEN
 331             current_res.record = core_result.records[1];
 332         ELSE
 333             current_res.record = NULL;
 334         END IF;
 335
 336         RETURN NEXT current_res;
 337
 338         IF visible_count % 1000 = 0 THEN
 339             -- RAISE NOTICE ' % visible so far ... ', visible_count;
 340         END IF;
 341
 342     END LOOP;
 343
 344     current_res.id = NULL;
 345     current_res.rel = NULL;
 346     current_res.record = NULL;
 347     current_res.total = total_count;
 348     current_res.checked = check_count;
 349     current_res.deleted = deleted_count;
 350     current_res.visible = visible_count;
 351     current_res.excluded = excluded_count;
 352
 353     CLOSE core_cursor;
 354
 355     RETURN NEXT current_res;
 356
 357 END;
 358 $func$ LANGUAGE PLPGSQL;
 359
 360 COMMIT;
 361