Open-ILS/src/sql/Pg/upgrade/1080.schema.lp1724246_cache_copy_visibility.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('1080', :eg_version); -- miker/jboyer/gmcharlt
   4
   5 CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$
   6 DECLARE
   7     ocn     asset.call_number%ROWTYPE;
   8     ncn     asset.call_number%ROWTYPE;
   9     cid     BIGINT;
  10 BEGIN
  11
  12     IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN -- Only needs ON INSERT OR DELETE, so handle separately
  13         IF TG_OP = 'INSERT' THEN
  14             INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
  15                 NEW.peer_record,
  16                 NEW.target_copy,
  17                 asset.calculate_copy_visibility_attribute_set(NEW.target_copy)
  18             );
  19
  20             RETURN NEW;
  21         ELSIF TG_OP = 'DELETE' THEN
  22             DELETE FROM asset.copy_vis_attr_cache
  23               WHERE record = OLD.peer_record AND target_copy = OLD.target_copy;
  24
  25             RETURN OLD;
  26         END IF;
  27     END IF;
  28
  29     IF TG_OP = 'INSERT' THEN -- Handles ON INSERT. ON UPDATE is below.
  30         IF TG_TABLE_NAME IN ('copy', 'unit') THEN
  31             SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
  32             INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
  33                 ncn.record,
  34                 NEW.id,
  35                 asset.calculate_copy_visibility_attribute_set(NEW.id)
  36             );
  37         ELSIF TG_TABLE_NAME = 'record_entry' THEN
  38             NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id);
  39         END IF;
  40
  41         RETURN NEW;
  42     END IF;
  43
  44     -- handle items first, since with circulation activity
  45     -- their statuses change frequently
  46     IF TG_TABLE_NAME IN ('copy', 'unit') THEN -- This handles ON UPDATE OR DELETE. ON INSERT above
  47
  48         IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
  49             DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
  50             RETURN OLD;
  51         END IF;
  52
  53         SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
  54
  55         IF OLD.deleted <> NEW.deleted THEN
  56             IF NEW.deleted THEN
  57                 DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
  58             ELSE
  59                 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
  60                     ncn.record,
  61                     NEW.id,
  62                     asset.calculate_copy_visibility_attribute_set(NEW.id)
  63                 );
  64             END IF;
  65
  66             RETURN NEW;
  67         ELSIF OLD.call_number  <> NEW.call_number THEN
  68             SELECT * INTO ocn FROM asset.call_number cn WHERE id = OLD.call_number;
  69
  70             IF ncn.record <> ocn.record THEN
  71                 UPDATE  biblio.record_entry
  72                   SET   vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(ncn.record)
  73                   WHERE id = ocn.record;
  74
  75                 -- We have to use a record-specific WHERE clause
  76                 -- to avoid modifying the entries for peer-bib copies.
  77                 UPDATE  asset.copy_vis_attr_cache
  78                   SET   target_copy = NEW.id,
  79                         record = ncn.record
  80                   WHERE target_copy = OLD.id
  81                         AND record = ocn.record;
  82             END IF;
  83         END IF;
  84
  85         IF OLD.location     <> NEW.location OR
  86            OLD.status       <> NEW.status OR
  87            OLD.opac_visible <> NEW.opac_visible OR
  88            OLD.circ_lib     <> NEW.circ_lib
  89         THEN
  90             -- Any of these could change visibility, but
  91             -- we'll save some queries and not try to calculate
  92             -- the change directly.  We want to update peer-bib
  93             -- entries in this case, unlike above.
  94             UPDATE  asset.copy_vis_attr_cache
  95               SET   target_copy = NEW.id,
  96                     vis_attr_vector = asset.calculate_copy_visibility_attribute_set(NEW.id)
  97               WHERE target_copy = OLD.id;
  98
  99         END IF;
 100
 101     ELSIF TG_TABLE_NAME = 'call_number' THEN -- Only ON UPDATE. Copy handler will deal with ON INSERT OR DELETE.
 102
 103         IF OLD.record <> NEW.record THEN
 104             IF NEW.label = '##URI##' THEN
 105                 UPDATE  biblio.record_entry
 106                   SET   vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
 107                   WHERE id = OLD.record;
 108
 109                 UPDATE  biblio.record_entry
 110                   SET   vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record)
 111                   WHERE id = NEW.record;
 112             END IF;
 113
 114             UPDATE  asset.copy_vis_attr_cache
 115               SET   record = NEW.record,
 116                     vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
 117               WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
 118                     AND record = OLD.record;
 119
 120         ELSIF OLD.owning_lib <> NEW.owning_lib THEN
 121             UPDATE  asset.copy_vis_attr_cache
 122               SET   vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
 123               WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
 124                     AND record = NEW.record;
 125
 126             IF NEW.label = '##URI##' THEN
 127                 UPDATE  biblio.record_entry
 128                   SET   vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
 129                   WHERE id = OLD.record;
 130             END IF;
 131         END IF;
 132
 133     ELSIF TG_TABLE_NAME = 'record_entry' THEN -- Only handles ON UPDATE OR DELETE
 134
 135         IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
 136             DELETE FROM asset.copy_vis_attr_cache WHERE record = OLD.id;
 137             RETURN OLD;
 138         ELSIF OLD.source <> NEW.source THEN
 139             NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id);
 140         END IF;
 141
 142     END IF;
 143
 144     RETURN NEW;
 145 END;
 146 $func$ LANGUAGE PLPGSQL;
 147
 148 COMMIT;