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