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