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
  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     OPEN core_cursor FOR EXECUTE param_query;
 104
 105     LOOP
 106
 107         FETCH core_cursor INTO core_result;
 108         EXIT WHEN NOT FOUND;
 109         EXIT WHEN total_count >= core_limit;
 110
 111         total_count := total_count + 1;
 112
 113         CONTINUE WHEN total_count NOT BETWEEN  core_offset + 1 AND check_limit + core_offset;
 114
 115         check_count := check_count + 1;
 116
 117         PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
 118         IF NOT FOUND THEN
 119             -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
 120             deleted_count := deleted_count + 1;
 121             CONTINUE;
 122         END IF;
 123
 124         PERFORM 1
 125           FROM  biblio.record_entry b
 126                 JOIN config.bib_source s ON (b.source = s.id)
 127           WHERE s.transcendant
 128                 AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
 129
 130         IF FOUND THEN
 131             -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
 132             visible_count := visible_count + 1;
 133
 134             current_res.id = core_result.id;
 135             current_res.rel = core_result.rel;
 136
 137             tmp_int := 1;
 138             IF metarecord THEN
 139                 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
 140             END IF;
 141
 142             IF tmp_int = 1 THEN
 143                 current_res.record = core_result.records[1];
 144             ELSE
 145                 current_res.record = NULL;
 146             END IF;
 147
 148             RETURN NEXT current_res;
 149
 150             CONTINUE;
 151         END IF;
 152
 153         PERFORM 1
 154           FROM  asset.call_number cn
 155                 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
 156                 JOIN asset.uri uri ON (map.uri = uri.id)
 157           WHERE NOT cn.deleted
 158                 AND cn.label = '##URI##'
 159                 AND uri.active
 160                 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
 161                 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
 162                 AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
 163           LIMIT 1;
 164
 165         IF FOUND THEN
 166             -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
 167             visible_count := visible_count + 1;
 168
 169             current_res.id = core_result.id;
 170             current_res.rel = core_result.rel;
 171
 172             tmp_int := 1;
 173             IF metarecord THEN
 174                 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
 175             END IF;
 176
 177             IF tmp_int = 1 THEN
 178                 current_res.record = core_result.records[1];
 179             ELSE
 180                 current_res.record = NULL;
 181             END IF;
 182
 183             RETURN NEXT current_res;
 184
 185             CONTINUE;
 186         END IF;
 187
 188         IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
 189
 190             PERFORM 1
 191               FROM  asset.call_number cn
 192                     JOIN asset.copy cp ON (cp.call_number = cn.id)
 193               WHERE NOT cn.deleted
 194                     AND NOT cp.deleted
 195                     AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
 196                     AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
 197                     AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 198               LIMIT 1;
 199
 200             IF NOT FOUND THEN
 201                 PERFORM 1
 202                   FROM  biblio.peer_bib_copy_map pr
 203                         JOIN asset.copy cp ON (cp.id = pr.target_copy)
 204                   WHERE NOT cp.deleted
 205                         AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
 206                         AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
 207                         AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 208                   LIMIT 1;
 209
 210                 IF NOT FOUND THEN
 211                 -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
 212                     excluded_count := excluded_count + 1;
 213                     CONTINUE;
 214                 END IF;
 215             END IF;
 216
 217         END IF;
 218
 219         IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
 220
 221             PERFORM 1
 222               FROM  asset.call_number cn
 223                     JOIN asset.copy cp ON (cp.call_number = cn.id)
 224               WHERE NOT cn.deleted
 225                     AND NOT cp.deleted
 226                     AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
 227                     AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
 228                     AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 229               LIMIT 1;
 230
 231             IF NOT FOUND THEN
 232                 PERFORM 1
 233                   FROM  biblio.peer_bib_copy_map pr
 234                         JOIN asset.copy cp ON (cp.id = pr.target_copy)
 235                   WHERE NOT cp.deleted
 236                         AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
 237                         AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
 238                         AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 239                   LIMIT 1;
 240
 241                 IF NOT FOUND THEN
 242                     -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
 243                     excluded_count := excluded_count + 1;
 244                     CONTINUE;
 245                 END IF;
 246             END IF;
 247
 248         END IF;
 249
 250         IF staff IS NULL OR NOT staff THEN
 251
 252             PERFORM 1
 253               FROM  asset.opac_visible_copies
 254               WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 255                     AND record IN ( SELECT * FROM unnest( core_result.records ) )
 256               LIMIT 1;
 257
 258             IF NOT FOUND THEN
 259                 PERFORM 1
 260                   FROM  biblio.peer_bib_copy_map pr
 261                         JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
 262                   WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 263                         AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
 264                   LIMIT 1;
 265
 266                 IF NOT FOUND THEN
 267
 268                     -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
 269                     excluded_count := excluded_count + 1;
 270                     CONTINUE;
 271                 END IF;
 272             END IF;
 273
 274         ELSE
 275
 276             PERFORM 1
 277               FROM  asset.call_number cn
 278                     JOIN asset.copy cp ON (cp.call_number = cn.id)
 279               WHERE NOT cn.deleted
 280                     AND NOT cp.deleted
 281                     AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 282                     AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
 283               LIMIT 1;
 284
 285             IF NOT FOUND THEN
 286
 287                 PERFORM 1
 288                   FROM  biblio.peer_bib_copy_map pr
 289                         JOIN asset.copy cp ON (cp.id = pr.target_copy)
 290                   WHERE NOT cp.deleted
 291                         AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
 292                         AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
 293                   LIMIT 1;
 294
 295                 IF NOT FOUND THEN
 296
 297                     PERFORM 1
 298                       FROM  asset.call_number cn
 299                             JOIN asset.copy cp ON (cp.call_number = cn.id)
 300                       WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
 301                             AND NOT cp.deleted
 302                       LIMIT 1;
 303
 304                     IF FOUND THEN
 305                         -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
 306                         excluded_count := excluded_count + 1;
 307                         CONTINUE;
 308                     END IF;
 309                 END IF;
 310
 311             END IF;
 312
 313         END IF;
 314
 315         visible_count := visible_count + 1;
 316
 317         current_res.id = core_result.id;
 318         current_res.rel = core_result.rel;
 319
 320         tmp_int := 1;
 321         IF metarecord THEN
 322             SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
 323         END IF;
 324
 325         IF tmp_int = 1 THEN
 326             current_res.record = core_result.records[1];
 327         ELSE
 328             current_res.record = NULL;
 329         END IF;
 330
 331         RETURN NEXT current_res;
 332
 333         IF visible_count % 1000 = 0 THEN
 334             -- RAISE NOTICE ' % visible so far ... ', visible_count;
 335         END IF;
 336
 337     END LOOP;
 338
 339     current_res.id = NULL;
 340     current_res.rel = NULL;
 341     current_res.record = NULL;
 342     current_res.total = total_count;
 343     current_res.checked = check_count;
 344     current_res.deleted = deleted_count;
 345     current_res.visible = visible_count;
 346     current_res.excluded = excluded_count;
 347
 348     CLOSE core_cursor;
 349
 350     RETURN NEXT current_res;
 351
 352 END;
 353 $func$ LANGUAGE PLPGSQL;
 354
 355 COMMIT;
 356