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                 -- RAISE NOTICE ' % were all status-excluded ... ', core_result.records;
 194                 excluded_count := excluded_count + 1;
 195                 CONTINUE;
 196             END IF;
 197
 198         END IF;
 199
 200         IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
 201
 202             PERFORM 1
 203               FROM  asset.call_number cn
 204                     JOIN asset.copy cp ON (cp.call_number = cn.id)
 205               WHERE NOT cn.deleted
 206                     AND NOT cp.deleted
 207                     AND cp.location IN ( SELECT * FROM search.explode_array( param_locations ) )
 208                     AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
 209                     AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
 210               LIMIT 1;
 211
 212             IF NOT FOUND THEN
 213                 -- RAISE NOTICE ' % were all copy_location-excluded ... ', core_result.records;
 214                 excluded_count := excluded_count + 1;
 215                 CONTINUE;
 216             END IF;
 217
 218         END IF;
 219
 220         IF staff IS NULL OR NOT staff THEN
 221
 222             PERFORM 1
 223               FROM  asset.opac_visible_copies
 224               WHERE circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
 225                     AND record IN ( SELECT * FROM search.explode_array( core_result.records ) )
 226               LIMIT 1;
 227
 228             IF NOT FOUND THEN
 229                 -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
 230                 excluded_count := excluded_count + 1;
 231                 CONTINUE;
 232             END IF;
 233
 234         ELSE
 235
 236             PERFORM 1
 237               FROM  asset.call_number cn
 238                     JOIN asset.copy cp ON (cp.call_number = cn.id)
 239                     JOIN actor.org_unit a ON (cp.circ_lib = a.id)
 240               WHERE NOT cn.deleted
 241                     AND NOT cp.deleted
 242                     AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
 243                     AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
 244               LIMIT 1;
 245
 246             IF NOT FOUND THEN
 247
 248                 PERFORM 1
 249                   FROM  asset.call_number cn
 250                   WHERE cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
 251                   LIMIT 1;
 252
 253                 IF FOUND THEN
 254                     -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
 255                     excluded_count := excluded_count + 1;
 256                     CONTINUE;
 257                 END IF;
 258
 259             END IF;
 260
 261         END IF;
 262
 263         visible_count := visible_count + 1;
 264
 265         current_res.id = core_result.id;
 266         current_res.rel = core_result.rel;
 267
 268         tmp_int := 1;
 269         IF metarecord THEN
 270             SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
 271         END IF;
 272
 273         IF tmp_int = 1 THEN
 274             current_res.record = core_result.records[1];
 275         ELSE
 276             current_res.record = NULL;
 277         END IF;
 278
 279         RETURN NEXT current_res;
 280
 281         IF visible_count % 1000 = 0 THEN
 282             -- RAISE NOTICE ' % visible so far ... ', visible_count;
 283         END IF;
 284
 285     END LOOP;
 286
 287     current_res.id = NULL;
 288     current_res.rel = NULL;
 289     current_res.record = NULL;
 290     current_res.total = total_count;
 291     current_res.checked = check_count;
 292     current_res.deleted = deleted_count;
 293     current_res.visible = visible_count;
 294     current_res.excluded = excluded_count;
 295
 296     CLOSE core_cursor;
 297
 298     RETURN NEXT current_res;
 299
 300 END;
 301 $func$ LANGUAGE PLPGSQL;
 302
 303 COMMIT;
 304