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

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