LP#1777675: Change table names to Latest Inventory
[working/Evergreen.git] / 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;