Open-ILS/src/sql/Pg/upgrade/XXXX-create-inventory-workstation-and-date.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
   4
   5 CREATE TABLE asset.latest_inventory (
   6     id                          SERIAL                      PRIMARY KEY,
   7     inventory_workstation       INTEGER                     REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
   8     inventory_date              TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
   9     copy                        BIGINT                      NOT NULL
  10 );
  11 CREATE INDEX latest_inventory_copy_idx ON asset.latest_inventory (copy);
  12
  13 CREATE OR REPLACE FUNCTION evergreen.asset_latest_inventory_copy_inh_fkey() RETURNS TRIGGER AS $f$
  14 BEGIN
  15         PERFORM 1 FROM asset.copy WHERE id = NEW.copy;
  16         IF NOT FOUND THEN
  17                 RAISE foreign_key_violation USING MESSAGE = FORMAT(
  18                         $$Referenced asset.copy id not found, copy:%s$$, NEW.copy
  19                 );
  20         END IF;
  21         RETURN NEW;
  22 END;
  23 $f$ LANGUAGE PLPGSQL VOLATILE COST 50;
  24
  25 CREATE CONSTRAINT TRIGGER inherit_asset_latest_inventory_copy_fkey
  26         AFTER UPDATE OR INSERT ON asset.latest_inventory
  27         DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_latest_inventory_copy_inh_fkey();
  28
  29 INSERT into config.workstation_setting_type (name, grp, datatype, label)
  30 VALUES (
  31     'eg.circ.checkin.do_inventory_update', 'circ', 'bool',
  32     oils_i18n_gettext (
  33              'eg.circ.checkin.do_inventory_update',
  34              'Checkin: Update Inventory',
  35              'cwst', 'label'
  36     )
  37 );
  38
  39 COMMIT;