Open-ILS/src/sql/Pg/upgrade/0554.unnest_search_query_parser_fts.sql

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