Open-ILS/src/sql/Pg/upgrade/0704.schema.query_parser_fts.sql

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