From c21c8f591a5140541b111acb0e69d020615c7bb6 Mon Sep 17 00:00:00 2001 From: Jason Stephenson Date: Sun, 24 Oct 2021 12:54:31 -0400 Subject: [PATCH] LP1883171 & LP1940663: Database & IDL updates for copy inventory table Add new asset.copy_inventory table with constraints: * Foreign Key on copy -> asset.copy.id * Unique index on inventory_date and copy * Require that copy is at home or may float to inventory_workstation org. Add IDL entry for asset.copy_inventory table (aci). Change asset.latest_inventory table into a view. Mark the asset.latest_inventory IDL entry (alci) read-only. Provide database upgrade script to make the database changes and move entries from the asset.latest_inventory table to the asset.copy_inventory table. Add pgtap schema tests to validate that the above tables, views, and constraints exist. Add pgtap live tests to validate that the table and view constraints work as intended. MERGE NOTE: IDL permissions on the new real table were updated to match previously modified permissions on the old real table. This work was sponsored by NOBLE. Signed-off-by: Jason Stephenson Signed-off-by: Michele Morgan Signed-off-by: Mike Rylander --- Open-ILS/examples/fm_IDL.xml | 30 +++++-- Open-ILS/src/sql/Pg/040.schema.asset.sql | 44 +++++++++- Open-ILS/src/sql/Pg/800.fkeys.sql | 8 +- .../src/sql/Pg/live_t/lp1883171-copy_inventory.pg | 98 ++++++++++++++++++++++ .../sql/Pg/t/lp1883171-copy_inventory-schema.pg | 30 +++++++ .../upgrade/XXXX.schema.asset.copy_inventory.sql | 86 +++++++++++++++++++ 6 files changed, 283 insertions(+), 13 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/live_t/lp1883171-copy_inventory.pg create mode 100644 Open-ILS/src/sql/Pg/t/lp1883171-copy_inventory-schema.pg create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.asset.copy_inventory.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index d3a22cf5b1..e6e4c6a6af 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -8020,6 +8020,7 @@ SELECT usr, + @@ -8047,6 +8048,7 @@ SELECT usr, + @@ -8069,8 +8071,29 @@ SELECT usr, - - + + + + + + + + + + + + + + + + + + + + + + + @@ -8082,10 +8105,7 @@ SELECT usr, - - - diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql index e2692381ca..8f5eb63d39 100644 --- a/Open-ILS/src/sql/Pg/040.schema.asset.sql +++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql @@ -120,13 +120,49 @@ CREATE TABLE asset.copy_part_map ( ); CREATE UNIQUE INDEX copy_part_map_cp_part_idx ON asset.copy_part_map (target_copy, part); -CREATE TABLE asset.latest_inventory ( +CREATE TABLE asset.copy_inventory ( id SERIAL PRIMARY KEY, inventory_workstation INTEGER REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED, - inventory_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), - copy BIGINT NOT NULL + inventory_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), + copy BIGINT NOT NULL ); -CREATE INDEX latest_inventory_copy_idx ON asset.latest_inventory (copy); +CREATE INDEX copy_inventory_copy_idx ON asset.copy_inventory (copy); +CREATE UNIQUE INDEX asset_copy_inventory_date_once_per_copy ON asset.copy_inventory (inventory_date, copy); + +CREATE OR REPLACE FUNCTION asset.copy_may_float_to_inventory_workstation() RETURNS TRIGGER AS $func$ +DECLARE + copy asset.copy%ROWTYPE; + workstation actor.workstation%ROWTYPE; +BEGIN + SELECT * INTO copy FROM asset.copy WHERE id = NEW.copy; + IF FOUND THEN + SELECT * INTO workstation FROM actor.workstation WHERE id = NEW.inventory_workstation; + IF FOUND THEN + IF copy.floating IS NULL THEN + IF copy.circ_lib <> workstation.owning_lib THEN + RAISE EXCEPTION 'Inventory workstation owning lib (%) does not match copy circ lib (%).', + workstation.owning_lib, copy.circ_lib; + END IF; + ELSE + IF NOT evergreen.can_float(copy.floating, copy.circ_lib, workstation.owning_lib) THEN + RAISE EXCEPTION 'Copy (%) cannot float to inventory workstation owning lib (%).', + copy.id, workstation.owning_lib; + END IF; + END IF; + END IF; + END IF; + RETURN NEW; +END; +$func$ LANGUAGE PLPGSQL VOLATILE COST 50; + +CREATE CONSTRAINT TRIGGER asset_copy_inventory_allowed_trig + AFTER UPDATE OR INSERT ON asset.copy_inventory + DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE asset.copy_may_float_to_inventory_workstation(); + +CREATE VIEW asset.latest_inventory (id, inventory_workstation, inventory_date, copy) AS +SELECT DISTINCT ON (copy) id, inventory_workstation, inventory_date, copy +FROM asset.copy_inventory +ORDER BY copy, inventory_date DESC; CREATE OR REPLACE FUNCTION asset.acp_status_changed() RETURNS TRIGGER AS $$ diff --git a/Open-ILS/src/sql/Pg/800.fkeys.sql b/Open-ILS/src/sql/Pg/800.fkeys.sql index 9fdb93305f..78045d20fb 100644 --- a/Open-ILS/src/sql/Pg/800.fkeys.sql +++ b/Open-ILS/src/sql/Pg/800.fkeys.sql @@ -174,7 +174,7 @@ BEGIN END; $f$ LANGUAGE PLPGSQL VOLATILE COST 50; -CREATE OR REPLACE FUNCTION evergreen.asset_latest_inventory_copy_inh_fkey() RETURNS TRIGGER AS $f$ +CREATE OR REPLACE FUNCTION evergreen.asset_copy_inventory_copy_inh_fkey() RETURNS TRIGGER AS $f$ BEGIN PERFORM 1 FROM asset.copy WHERE id = NEW.copy; IF NOT FOUND THEN @@ -194,9 +194,9 @@ CREATE CONSTRAINT TRIGGER inherit_asset_copy_tag_copy_map_copy_fkey AFTER UPDATE OR INSERT ON asset.copy_tag_copy_map DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_copy_tag_copy_map_copy_inh_fkey(); -CREATE CONSTRAINT TRIGGER inherit_asset_latest_inventory_copy_fkey - AFTER UPDATE OR INSERT ON asset.latest_inventory - DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_latest_inventory_copy_inh_fkey(); +CREATE CONSTRAINT TRIGGER inherit_asset_copy_inventory_copy_fkey + AFTER UPDATE OR INSERT ON asset.copy_inventory + DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_copy_inventory_copy_inh_fkey(); ALTER TABLE asset.copy_note ADD CONSTRAINT asset_copy_note_creator_fkey FOREIGN KEY (creator) REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; diff --git a/Open-ILS/src/sql/Pg/live_t/lp1883171-copy_inventory.pg b/Open-ILS/src/sql/Pg/live_t/lp1883171-copy_inventory.pg new file mode 100644 index 0000000000..896d02d812 --- /dev/null +++ b/Open-ILS/src/sql/Pg/live_t/lp1883171-copy_inventory.pg @@ -0,0 +1,98 @@ +BEGIN; + +SELECT plan(9); + +-- Workstations to use for tests: +\set BR1_ws_name 'BR1-lp1883171-pgtap-live_t' +\set BR4_ws_name 'BR4-lp1883171-pgtap-live_t' + +-- Fixed timestamp to check for duplicates: +\set fixed_ts '2021-10-24 11:52:33.604067-04' + +-- Copy from BR1: +\set BR1_copy_id 1 +-- Copy from BR4 +\set BR4_copy_id 801 +-- Copy from BR3 to test floating. +\set BR3_copy_id 701 + +-- A name for a floating group to test floating. +\set SYS2_floating_group_name 'SYS2 Float Group' + +-- Create workstations: +INSERT INTO actor.workstation +(name, owning_lib) +VALUES +(:'BR1_ws_name', 4), +(:'BR4_ws_name', 7); + +-- Create the floating group and define its members +INSERT INTO config.floating_group +(name) +VALUES (:'SYS2_floating_group_name'); + +INSERT INTO config.floating_group_member +(floating_group, org_unit, stop_depth) +SELECT id, 3, 1 +FROM config.floating_group +WHERE name = :'SYS2_floating_group_name'; + +-- Let the copy at BR3 float to SYS2 +UPDATE asset.copy +SET floating = floating_group.id +FROM config.floating_group +WHERE copy.id = :BR3_copy_id +AND floating_group.name = :'SYS2_floating_group_name'; + +PREPARE insert_null_all AS INSERT INTO asset.copy_inventory (inventory_workstation, inventory_date, copy) +VALUES (NULL, :'fixed_ts', :BR1_copy_id), +(NULL, :'fixed_ts', :BR3_copy_id), +(NULL, :'fixed_ts', :BR4_copy_id); +SELECT lives_ok('insert_null_all', 'NULL workstation should succeed'); + +PREPARE insert_br1_br1 AS INSERT INTO asset.copy_inventory(inventory_workstation, copy) +SELECT id, :BR1_copy_id +FROM actor.workstation +WHERE name = :'BR1_ws_name'; +SELECT lives_ok('insert_br1_br1', 'BR1 at BR1 should succeed'); + +PREPARE insert_br1_br4 AS INSERT INTO asset.copy_inventory (inventory_workstation, copy) +SELECT id, :BR1_copy_id +FROM actor.workstation +WHERE name = :'BR4_ws_name'; +SELECT throws_ok('insert_br1_br4'); + +PREPARE insert_br4_br1 AS INSERT INTO asset.copy_inventory (inventory_workstation, copy) +SELECT id, :BR4_copy_id +FROM actor.workstation +WHERE name = :'BR1_ws_name'; +SELECT throws_ok('insert_br4_br1'); + +PREPARE insert_br3_br1 AS INSERT INTO asset.copy_inventory (inventory_workstation, copy) +SELECT id, :BR3_copy_id +FROM actor.workstation +WHERE name = :'BR1_ws_name'; +SELECT throws_ok('insert_br3_br1'); + +PREPARE insert_br4_br4 AS INSERT INTO asset.copy_inventory(inventory_workstation, copy) +SELECT id, :BR4_copy_id +FROM actor.workstation +WHERE name = :'BR4_ws_name'; +SELECT lives_ok('insert_br4_br4', 'BR4 at BR4 should succeed'); + +PREPARE insert_br3_br4 AS INSERT INTO asset.copy_inventory(inventory_workstation, copy) +SELECT id, :BR3_copy_id +FROM actor.workstation +WHERE name = :'BR4_ws_name'; +SELECT lives_ok('insert_br3_br4', 'BR3 at BR4 should succeed'); + +PREPARE dup_insert_br3_br4 AS INSERT INTO asset.copy_inventory (inventory_workstation, inventory_date, copy) +SELECT id, :'fixed_ts', :BR3_copy_id +FROM actor.workstation +WHERE name = :'BR4_ws_name'; +SELECT throws_ok('dup_insert_br3_br4', 23505); + +SELECT throws_ok('insert_null_all', 23505); + +SELECT * FROM finish(); +ROLLBACK; diff --git a/Open-ILS/src/sql/Pg/t/lp1883171-copy_inventory-schema.pg b/Open-ILS/src/sql/Pg/t/lp1883171-copy_inventory-schema.pg new file mode 100644 index 0000000000..1a4073fdb8 --- /dev/null +++ b/Open-ILS/src/sql/Pg/t/lp1883171-copy_inventory-schema.pg @@ -0,0 +1,30 @@ +BEGIN; + +SELECT plan(11); + +SELECT has_table('asset'::name, 'copy_inventory'::name); + +SELECT has_index('asset'::name, 'copy_inventory'::name, 'copy_inventory_copy_idx'::name, 'copy'::name); + +SELECT has_index('asset'::name, 'copy_inventory'::name, 'asset_copy_inventory_date_once_per_copy'::name, + ARRAY[ 'inventory_date', 'copy' ]); + +SELECT has_function('evergreen'::name, 'asset_copy_inventory_copy_inh_fkey'::name); + +SELECT has_trigger('asset'::name, 'copy_inventory'::name, 'inherit_asset_copy_inventory_copy_fkey'::name); + +SELECT has_function('asset'::name, 'copy_may_float_to_inventory_workstation'::name); + +SELECT has_trigger('asset'::name, 'copy_inventory'::name, 'asset_copy_inventory_allowed_trig'::name); + +SELECT hasnt_function('evergreen'::name, 'asset_latest_inventory_copy_inh_fkey'::name); + +SELECT hasnt_trigger('asset'::name, 'latest_inventory'::name, 'inherit_asset_latest_inventory_copy_fkey'::name); + +SELECT hasnt_table('asset'::name, 'latest_inventory'::name); + +SELECT has_view('asset'::name, 'latest_inventory'::name, 'View latest_inventory should exist'); + +SELECT * from finish(); + +ROLLBACK; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.asset.copy_inventory.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.asset.copy_inventory.sql new file mode 100644 index 0000000000..ff6af4544a --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.asset.copy_inventory.sql @@ -0,0 +1,86 @@ +BEGIN; + +-- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE TABLE asset.copy_inventory ( + id SERIAL PRIMARY KEY, + inventory_workstation INTEGER REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED, + inventory_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), + copy BIGINT NOT NULL +); +CREATE INDEX copy_inventory_copy_idx ON asset.copy_inventory (copy); +CREATE UNIQUE INDEX asset_copy_inventory_date_once_per_copy ON asset.copy_inventory (inventory_date, copy); + +CREATE OR REPLACE FUNCTION evergreen.asset_copy_inventory_copy_inh_fkey() RETURNS TRIGGER AS $f$ +BEGIN + PERFORM 1 FROM asset.copy WHERE id = NEW.copy; + IF NOT FOUND THEN + RAISE foreign_key_violation USING MESSAGE = FORMAT( + $$Referenced asset.copy id not found, copy:%s$$, NEW.copy + ); + END IF; + RETURN NEW; +END; +$f$ LANGUAGE PLPGSQL VOLATILE COST 50; + +CREATE CONSTRAINT TRIGGER inherit_asset_copy_inventory_copy_fkey + AFTER UPDATE OR INSERT ON asset.copy_inventory + DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_copy_inventory_copy_inh_fkey(); + +CREATE OR REPLACE FUNCTION asset.copy_may_float_to_inventory_workstation() RETURNS TRIGGER AS $func$ +DECLARE + copy asset.copy%ROWTYPE; + workstation actor.workstation%ROWTYPE; +BEGIN + SELECT * INTO copy FROM asset.copy WHERE id = NEW.copy; + IF FOUND THEN + SELECT * INTO workstation FROM actor.workstation WHERE id = NEW.inventory_workstation; + IF FOUND THEN + IF copy.floating IS NULL THEN + IF copy.circ_lib <> workstation.owning_lib THEN + RAISE EXCEPTION 'Inventory workstation owning lib (%) does not match copy circ lib (%).', + workstation.owning_lib, copy.circ_lib; + END IF; + ELSE + IF NOT evergreen.can_float(copy.floating, copy.circ_lib, workstation.owning_lib) THEN + RAISE EXCEPTION 'Copy (%) cannot float to inventory workstation owning lib (%).', + copy.id, workstation.owning_lib; + END IF; + END IF; + END IF; + END IF; + RETURN NEW; +END; +$func$ LANGUAGE PLPGSQL VOLATILE COST 50; + +CREATE CONSTRAINT TRIGGER asset_copy_inventory_allowed_trig + AFTER UPDATE OR INSERT ON asset.copy_inventory + DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE asset.copy_may_float_to_inventory_workstation(); + +INSERT INTO asset.copy_inventory +(inventory_workstation, inventory_date, copy) +SELECT DISTINCT ON (inventory_date, copy) inventory_workstation, inventory_date, copy +FROM asset.latest_inventory +JOIN asset.copy acp ON acp.id = latest_inventory.copy +JOIN actor.workstation ON workstation.id = latest_inventory.inventory_workstation +WHERE acp.circ_lib = workstation.owning_lib +UNION +SELECT DISTINCT ON (inventory_date, copy) inventory_workstation, inventory_date, copy +FROM asset.latest_inventory +JOIN asset.copy acp ON acp.id = latest_inventory.copy +JOIN actor.workstation ON workstation.id = latest_inventory.inventory_workstation +WHERE acp.circ_lib <> workstation.owning_lib +AND acp.floating IS NOT NULL +AND evergreen.can_float(acp.floating, acp.circ_lib, workstation.owning_lib) +ORDER by inventory_date; + +DROP TABLE asset.latest_inventory; + +CREATE VIEW asset.latest_inventory (id, inventory_workstation, inventory_date, copy) AS +SELECT DISTINCT ON (copy) id, inventory_workstation, inventory_date, copy +FROM asset.copy_inventory +ORDER BY copy, inventory_date DESC; + +DROP FUNCTION evergreen.asset_latest_inventory_copy_inh_fkey(); + +COMMIT; -- 2.11.0