Open-ILS/src/sql/Pg/upgrade/0332.schema.opac_visible_copies.sql

   1 BEGIN;
   2
   3 INSERT INTO config.upgrade_log (version) VALUES ('0332'); -- gmc
   4
   5 CREATE TABLE asset.opac_visible_copies (
   6   id        BIGINT primary key, -- copy id
   7   record    BIGINT,
   8   circ_lib  INTEGER
   9 );
  10 COMMENT ON TABLE asset.opac_visible_copies IS $$
  11 Materialized view of copies that are visible in the OPAC, used by
  12 search.query_parser_fts() to speed up OPAC visibility checks on large
  13 databases.  Contents are maintained by a set of triggers.
  14 $$;
  15 CREATE INDEX opac_visible_copies_idx1 on asset.opac_visible_copies (record, circ_lib);
  16
  17 -- copy OPAC visibility materialized view
  18 CREATE OR REPLACE FUNCTION asset.refresh_opac_visible_copies_mat_view () RETURNS VOID AS $$
  19
  20     TRUNCATE TABLE asset.opac_visible_copies;
  21
  22     INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
  23     SELECT  cp.id, cp.circ_lib, cn.record
  24     FROM  asset.copy cp
  25         JOIN asset.call_number cn ON (cn.id = cp.call_number)
  26         JOIN actor.org_unit a ON (cp.circ_lib = a.id)
  27         JOIN asset.copy_location cl ON (cp.location = cl.id)
  28         JOIN config.copy_status cs ON (cp.status = cs.id)
  29         JOIN biblio.record_entry b ON (cn.record = b.id)
  30     WHERE NOT cp.deleted
  31         AND NOT cn.deleted
  32         AND NOT b.deleted
  33         AND cs.opac_visible
  34         AND cl.opac_visible
  35         AND cp.opac_visible
  36         AND a.opac_visible;
  37
  38 $$ LANGUAGE SQL;
  39 COMMENT ON FUNCTION asset.refresh_opac_visible_copies_mat_view() IS $$
  40 Rebuild the copy OPAC visibility cache.  Useful during migrations.
  41 $$;
  42
  43 -- and actually populate the table
  44 SELECT asset.refresh_opac_visible_copies_mat_view();
  45
  46 CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$
  47 DECLARE
  48     add_query       TEXT;
  49     remove_query    TEXT;
  50     do_add          BOOLEAN := false;
  51     do_remove       BOOLEAN := false;
  52 BEGIN
  53     add_query := $$
  54             INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
  55                 SELECT  cp.id, cp.circ_lib, cn.record
  56                   FROM  asset.copy cp
  57                         JOIN asset.call_number cn ON (cn.id = cp.call_number)
  58                         JOIN actor.org_unit a ON (cp.circ_lib = a.id)
  59                         JOIN asset.copy_location cl ON (cp.location = cl.id)
  60                         JOIN config.copy_status cs ON (cp.status = cs.id)
  61                         JOIN biblio.record_entry b ON (cn.record = b.id)
  62                   WHERE NOT cp.deleted
  63                         AND NOT cn.deleted
  64                         AND NOT b.deleted
  65                         AND cs.opac_visible
  66                         AND cl.opac_visible
  67                         AND cp.opac_visible
  68                         AND a.opac_visible
  69     $$;
  70
  71     remove_query := $$ DELETE FROM asset.opac_visible_copies WHERE id IN ( SELECT id FROM asset.copy WHERE $$;
  72
  73     IF TG_OP = 'INSERT' THEN
  74
  75         IF TG_TABLE_NAME IN ('copy', 'unit') THEN
  76             add_query := add_query || 'AND cp.id = ' || NEW.id || ';';
  77             EXECUTE add_query;
  78         END IF;
  79
  80         RETURN NEW;
  81
  82     END IF;
  83
  84     -- handle items first, since with circulation activity
  85     -- their statuses change frequently
  86     IF TG_TABLE_NAME IN ('copy', 'unit') THEN
  87
  88         IF OLD.location    <> NEW.location OR
  89            OLD.call_number <> NEW.call_number OR
  90            OLD.status      <> NEW.status OR
  91            OLD.circ_lib    <> NEW.circ_lib THEN
  92             -- any of these could change visibility, but
  93             -- we'll save some queries and not try to calculate
  94             -- the change directly
  95             do_remove := true;
  96             do_add := true;
  97         ELSE
  98
  99             IF OLD.deleted <> NEW.deleted THEN
 100                 IF NEW.deleted THEN
 101                     do_remove := true;
 102                 ELSE
 103                     do_add := true;
 104                 END IF;
 105             END IF;
 106
 107             IF OLD.opac_visible <> NEW.opac_visible THEN
 108                 IF OLD.opac_visible THEN
 109                     do_remove := true;
 110                 ELSIF NOT do_remove THEN -- handle edge case where deleted item
 111                                         -- is also marked opac_visible
 112                     do_add := true;
 113                 END IF;
 114             END IF;
 115
 116         END IF;
 117
 118         IF do_remove THEN
 119             DELETE FROM asset.opac_visible_copies WHERE id = NEW.id;
 120         END IF;
 121         IF do_add THEN
 122             add_query := add_query || 'AND cp.id = ' || NEW.id || ';';
 123             EXECUTE add_query;
 124         END IF;
 125
 126         RETURN NEW;
 127
 128     END IF;
 129
 130     IF TG_TABLE_NAME IN ('call_number', 'record_entry') THEN -- these have a 'deleted' column
 131
 132         IF OLD.deleted AND NEW.deleted THEN -- do nothing
 133
 134             RETURN NEW;
 135
 136         ELSIF NEW.deleted THEN -- remove rows
 137
 138             IF TG_TABLE_NAME = 'call_number' THEN
 139                 DELETE FROM asset.opac_visible_copies WHERE id IN (SELECT id FROM asset.copy WHERE call_number = NEW.id);
 140             ELSIF TG_TABLE_NAME = 'record_entry' THEN
 141                 DELETE FROM asset.opac_visible_copies WHERE record = NEW.id;
 142             END IF;
 143
 144             RETURN NEW;
 145
 146         ELSIF OLD.deleted THEN -- add rows
 147
 148             IF TG_TABLE_NAME IN ('copy','unit') THEN
 149                 add_query := add_query || 'AND cp.id = ' || NEW.id || ';';
 150             ELSIF TG_TABLE_NAME = 'call_number' THEN
 151                 add_query := add_query || 'AND cp.call_number = ' || NEW.id || ';';
 152             ELSIF TG_TABLE_NAME = 'record_entry' THEN
 153                 add_query := add_query || 'AND cn.record = ' || NEW.id || ';';
 154             END IF;
 155
 156             EXECUTE add_query;
 157             RETURN NEW;
 158
 159         END IF;
 160
 161     END IF;
 162
 163     IF TG_TABLE_NAME = 'call_number' THEN
 164
 165         IF OLD.record <> NEW.record THEN
 166             -- call number is linked to different bib
 167             remove_query := remove_query || 'call_number = ' || NEW.id || ');';
 168             EXECUTE remove_query;
 169             add_query := add_query || 'AND cp.call_number = ' || NEW.id || ';';
 170             EXECUTE add_query;
 171         END IF;
 172
 173         RETURN NEW;
 174
 175     END IF;
 176
 177     IF TG_TABLE_NAME IN ('record_entry') THEN
 178         RETURN NEW; -- don't have 'opac_visible'
 179     END IF;
 180
 181     -- actor.org_unit, asset.copy_location, asset.copy_status
 182     IF NEW.opac_visible = OLD.opac_visible THEN -- do nothing
 183
 184         RETURN NEW;
 185
 186     ELSIF NEW.opac_visible THEN -- add rows
 187
 188         IF TG_TABLE_NAME = 'org_unit' THEN
 189             add_query := add_query || 'AND cp.circ_lib = ' || NEW.id || ';';
 190         ELSIF TG_TABLE_NAME = 'copy_location' THEN
 191             add_query := add_query || 'AND cp.location = ' || NEW.id || ';';
 192         ELSIF TG_TABLE_NAME = 'copy_status' THEN
 193             add_query := add_query || 'AND cp.status = ' || NEW.id || ';';
 194         END IF;
 195
 196         EXECUTE add_query;
 197
 198     ELSE -- delete rows
 199
 200         IF TG_TABLE_NAME = 'org_unit' THEN
 201             remove_query := 'DELETE FROM asset.opac_visible_copies WHERE circ_lib = ' || NEW.id || ';';
 202         ELSIF TG_TABLE_NAME = 'copy_location' THEN
 203             remove_query := remove_query || 'location = ' || NEW.id || ');';
 204         ELSIF TG_TABLE_NAME = 'copy_status' THEN
 205             remove_query := remove_query || 'status = ' || NEW.id || ');';
 206         END IF;
 207
 208         EXECUTE remove_query;
 209
 210     END IF;
 211
 212     RETURN NEW;
 213 END;
 214 $func$ LANGUAGE PLPGSQL;
 215 COMMENT ON FUNCTION asset.cache_copy_visibility() IS $$
 216 Trigger function to update the copy OPAC visiblity cache.
 217 $$;
 218 CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
 219 CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
 220 CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON asset.call_number FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
 221 CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON asset.copy_location FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
 222 CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
 223 CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON config.copy_status FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
 224 CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON actor.org_unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
 225
 226 CREATE OR REPLACE FUNCTION search.query_parser_fts (
 227
 228     param_search_ou INT,
 229     param_depth     INT,
 230     param_query     TEXT,
 231     param_statuses  INT[],
 232     param_locations INT[],
 233     param_offset    INT,
 234     param_check     INT,
 235     param_limit     INT,
 236     metarecord      BOOL,
 237     staff           BOOL
 238
 239 ) RETURNS SETOF search.search_result AS $func$
 240 DECLARE
 241
 242     current_res         search.search_result%ROWTYPE;
 243     search_org_list     INT[];
 244
 245     check_limit         INT;
 246     core_limit          INT;
 247     core_offset         INT;
 248     tmp_int             INT;
 249
 250     core_result         RECORD;
 251     core_cursor         REFCURSOR;
 252     core_rel_query      TEXT;
 253
 254     total_count         INT := 0;
 255     check_count         INT := 0;
 256     deleted_count       INT := 0;
 257     visible_count       INT := 0;
 258     excluded_count      INT := 0;
 259
 260 BEGIN
 261
 262     check_limit := COALESCE( param_check, 1000 );
 263     core_limit  := COALESCE( param_limit, 25000 );
 264     core_offset := COALESCE( param_offset, 0 );
 265
 266     -- core_skip_chk := COALESCE( param_skip_chk, 1 );
 267
 268     IF param_search_ou > 0 THEN
 269         IF param_depth IS NOT NULL THEN
 270             SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
 271         ELSE
 272             SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
 273         END IF;
 274     ELSIF param_search_ou < 0 THEN
 275         SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
 276     ELSIF param_search_ou = 0 THEN
 277         -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
 278     END IF;
 279
 280     OPEN core_cursor FOR EXECUTE param_query;
 281
 282     LOOP
 283
 284         FETCH core_cursor INTO core_result;
 285         EXIT WHEN NOT FOUND;
 286         EXIT WHEN total_count >= core_limit;
 287
 288         total_count := total_count + 1;
 289
 290         CONTINUE WHEN total_count NOT BETWEEN  core_offset + 1 AND check_limit + core_offset;
 291
 292         check_count := check_count + 1;
 293
 294         PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) );
 295         IF NOT FOUND THEN
 296             -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
 297             deleted_count := deleted_count + 1;
 298             CONTINUE;
 299         END IF;
 300
 301         PERFORM 1
 302           FROM  biblio.record_entry b
 303                 JOIN config.bib_source s ON (b.source = s.id)
 304           WHERE s.transcendant
 305                 AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) );
 306
 307         IF FOUND THEN
 308             -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
 309             visible_count := visible_count + 1;
 310
 311             current_res.id = core_result.id;
 312             current_res.rel = core_result.rel;
 313
 314             tmp_int := 1;
 315             IF metarecord THEN
 316                 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
 317             END IF;
 318
 319             IF tmp_int = 1 THEN
 320                 current_res.record = core_result.records[1];
 321             ELSE
 322                 current_res.record = NULL;
 323             END IF;
 324
 325             RETURN NEXT current_res;
 326
 327             CONTINUE;
 328         END IF;
 329
 330         PERFORM 1
 331           FROM  asset.call_number cn
 332                 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
 333                 JOIN asset.uri uri ON (map.uri = uri.id)
 334           WHERE NOT cn.deleted
 335                 AND cn.label = '##URI##'
 336                 AND uri.active
 337                 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
 338                 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
 339                 AND cn.owning_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
 340           LIMIT 1;
 341
 342         IF FOUND THEN
 343             -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
 344             visible_count := visible_count + 1;
 345
 346             current_res.id = core_result.id;
 347             current_res.rel = core_result.rel;
 348
 349             tmp_int := 1;
 350             IF metarecord THEN
 351                 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
 352             END IF;
 353
 354             IF tmp_int = 1 THEN
 355                 current_res.record = core_result.records[1];
 356             ELSE
 357                 current_res.record = NULL;
 358             END IF;
 359
 360             RETURN NEXT current_res;
 361
 362             CONTINUE;
 363         END IF;
 364
 365         IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
 366
 367             PERFORM 1
 368               FROM  asset.call_number cn
 369                     JOIN asset.copy cp ON (cp.call_number = cn.id)
 370               WHERE NOT cn.deleted
 371                     AND NOT cp.deleted
 372                     AND cp.status IN ( SELECT * FROM search.explode_array( param_statuses ) )
 373                     AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
 374                     AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
 375               LIMIT 1;
 376
 377             IF NOT FOUND THEN
 378                 -- RAISE NOTICE ' % were all status-excluded ... ', core_result.records;
 379                 excluded_count := excluded_count + 1;
 380                 CONTINUE;
 381             END IF;
 382
 383         END IF;
 384
 385         IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
 386
 387             PERFORM 1
 388               FROM  asset.call_number cn
 389                     JOIN asset.copy cp ON (cp.call_number = cn.id)
 390               WHERE NOT cn.deleted
 391                     AND NOT cp.deleted
 392                     AND cp.location IN ( SELECT * FROM search.explode_array( param_locations ) )
 393                     AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
 394                     AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
 395               LIMIT 1;
 396
 397             IF NOT FOUND THEN
 398                 -- RAISE NOTICE ' % were all copy_location-excluded ... ', core_result.records;
 399                 excluded_count := excluded_count + 1;
 400                 CONTINUE;
 401             END IF;
 402
 403         END IF;
 404
 405         IF staff IS NULL OR NOT staff THEN
 406
 407             PERFORM 1
 408               FROM  asset.opac_visible_copies
 409               WHERE circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
 410                     AND record IN ( SELECT * FROM search.explode_array( core_result.records ) )
 411               LIMIT 1;
 412
 413             IF NOT FOUND THEN
 414                 -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
 415                 excluded_count := excluded_count + 1;
 416                 CONTINUE;
 417             END IF;
 418
 419         ELSE
 420
 421             PERFORM 1
 422               FROM  asset.call_number cn
 423                     JOIN asset.copy cp ON (cp.call_number = cn.id)
 424                     JOIN actor.org_unit a ON (cp.circ_lib = a.id)
 425               WHERE NOT cn.deleted
 426                     AND NOT cp.deleted
 427                     AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
 428                     AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
 429               LIMIT 1;
 430
 431             IF NOT FOUND THEN
 432
 433                 PERFORM 1
 434                   FROM  asset.call_number cn
 435                   WHERE cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
 436                   LIMIT 1;
 437
 438                 IF FOUND THEN
 439                     -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
 440                     excluded_count := excluded_count + 1;
 441                     CONTINUE;
 442                 END IF;
 443
 444             END IF;
 445
 446         END IF;
 447
 448         visible_count := visible_count + 1;
 449
 450         current_res.id = core_result.id;
 451         current_res.rel = core_result.rel;
 452
 453         tmp_int := 1;
 454         IF metarecord THEN
 455             SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
 456         END IF;
 457
 458         IF tmp_int = 1 THEN
 459             current_res.record = core_result.records[1];
 460         ELSE
 461             current_res.record = NULL;
 462         END IF;
 463
 464         RETURN NEXT current_res;
 465
 466         IF visible_count % 1000 = 0 THEN
 467             -- RAISE NOTICE ' % visible so far ... ', visible_count;
 468         END IF;
 469
 470     END LOOP;
 471
 472     current_res.id = NULL;
 473     current_res.rel = NULL;
 474     current_res.record = NULL;
 475     current_res.total = total_count;
 476     current_res.checked = check_count;
 477     current_res.deleted = deleted_count;
 478     current_res.visible = visible_count;
 479     current_res.excluded = excluded_count;
 480
 481     CLOSE core_cursor;
 482
 483     RETURN NEXT current_res;
 484
 485 END;
 486 $func$ LANGUAGE PLPGSQL;
 487
 488 COMMIT;