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