Open-ILS/src/sql/Pg/upgrade/1321.schema.asset.copy_inventory.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('1321', :eg_version);
   4
   5 CREATE TABLE asset.copy_inventory (
   6     id                          SERIAL                      PRIMARY KEY,
   7     inventory_workstation       INTEGER                     REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
   8     inventory_date              TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
   9     copy                        BIGINT                      NOT NULL
  10 );
  11 CREATE INDEX copy_inventory_copy_idx ON asset.copy_inventory (copy);
  12 CREATE UNIQUE INDEX asset_copy_inventory_date_once_per_copy ON asset.copy_inventory (inventory_date, copy);
  13
  14 CREATE OR REPLACE FUNCTION evergreen.asset_copy_inventory_copy_inh_fkey() RETURNS TRIGGER AS $f$
  15 BEGIN
  16         PERFORM 1 FROM asset.copy WHERE id = NEW.copy;
  17         IF NOT FOUND THEN
  18                 RAISE foreign_key_violation USING MESSAGE = FORMAT(
  19                         $$Referenced asset.copy id not found, copy:%s$$, NEW.copy
  20                 );
  21         END IF;
  22         RETURN NEW;
  23 END;
  24 $f$ LANGUAGE PLPGSQL VOLATILE COST 50;
  25
  26 CREATE CONSTRAINT TRIGGER inherit_asset_copy_inventory_copy_fkey
  27         AFTER UPDATE OR INSERT ON asset.copy_inventory
  28         DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_copy_inventory_copy_inh_fkey();
  29
  30 CREATE OR REPLACE FUNCTION asset.copy_may_float_to_inventory_workstation() RETURNS TRIGGER AS $func$
  31 DECLARE
  32     copy asset.copy%ROWTYPE;
  33     workstation actor.workstation%ROWTYPE;
  34 BEGIN
  35     SELECT * INTO copy FROM asset.copy WHERE id = NEW.copy;
  36     IF FOUND THEN
  37         SELECT * INTO workstation FROM actor.workstation WHERE id = NEW.inventory_workstation;
  38         IF FOUND THEN
  39            IF copy.floating IS NULL THEN
  40               IF copy.circ_lib <> workstation.owning_lib THEN
  41                  RAISE EXCEPTION 'Inventory workstation owning lib (%) does not match copy circ lib (%).',
  42                        workstation.owning_lib, copy.circ_lib;
  43               END IF;
  44            ELSE
  45               IF NOT evergreen.can_float(copy.floating, copy.circ_lib, workstation.owning_lib) THEN
  46                  RAISE EXCEPTION 'Copy (%) cannot float to inventory workstation owning lib (%).',
  47                        copy.id, workstation.owning_lib;
  48               END IF;
  49            END IF;
  50         END IF;
  51     END IF;
  52     RETURN NEW;
  53 END;
  54 $func$ LANGUAGE PLPGSQL VOLATILE COST 50;
  55
  56 CREATE CONSTRAINT TRIGGER asset_copy_inventory_allowed_trig
  57         AFTER UPDATE OR INSERT ON asset.copy_inventory
  58         DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE asset.copy_may_float_to_inventory_workstation();
  59
  60 INSERT INTO asset.copy_inventory
  61 (inventory_workstation, inventory_date, copy)
  62 SELECT DISTINCT ON (inventory_date, copy) inventory_workstation, inventory_date, copy
  63 FROM asset.latest_inventory
  64 JOIN asset.copy acp ON acp.id = latest_inventory.copy
  65 JOIN actor.workstation ON workstation.id = latest_inventory.inventory_workstation
  66 WHERE acp.circ_lib = workstation.owning_lib
  67 UNION
  68 SELECT DISTINCT ON (inventory_date, copy) inventory_workstation, inventory_date, copy
  69 FROM asset.latest_inventory
  70 JOIN asset.copy acp ON acp.id = latest_inventory.copy
  71 JOIN actor.workstation ON workstation.id = latest_inventory.inventory_workstation
  72 WHERE acp.circ_lib <> workstation.owning_lib
  73 AND acp.floating IS NOT NULL
  74 AND evergreen.can_float(acp.floating, acp.circ_lib, workstation.owning_lib)
  75 ORDER by inventory_date;
  76
  77 DROP TABLE asset.latest_inventory;
  78
  79 CREATE VIEW asset.latest_inventory (id, inventory_workstation, inventory_date, copy) AS
  80 SELECT DISTINCT ON (copy) id, inventory_workstation, inventory_date, copy
  81 FROM asset.copy_inventory
  82 ORDER BY copy, inventory_date DESC;
  83
  84 DROP FUNCTION evergreen.asset_latest_inventory_copy_inh_fkey();
  85
  86 COMMIT;