From f1fe22bc80f200157fe4a2f89d957a95e570282b Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Mon, 24 Apr 2017 12:40:37 -0400 Subject: [PATCH] LP#1152753: make it possible for serial units to be added to copy buckets This patch replaces the baseline asset.copy.id fkey constraints with ones that understand inheritance, and change all existing contstraints to do the same via upgrade script. To test ------- Create some serial units and verify that they can be added to a copy bucket. Signed-off-by: Mike Rylander Signed-off-by: Galen Charlton Signed-off-by: Kathy Lussier Signed-off-by: Dan Wells --- Open-ILS/src/sql/Pg/070.schema.container.sql | 24 +++++++++--- Open-ILS/src/sql/Pg/800.fkeys.sql | 34 ++++++++++++++++- .../XXXX.schema.inheritance-constraint-trigger.sql | 44 ++++++++++++++++++++++ 3 files changed, 94 insertions(+), 8 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.inheritance-constraint-trigger.sql diff --git a/Open-ILS/src/sql/Pg/070.schema.container.sql b/Open-ILS/src/sql/Pg/070.schema.container.sql index 32dfc6aae3..b3a305668c 100644 --- a/Open-ILS/src/sql/Pg/070.schema.container.sql +++ b/Open-ILS/src/sql/Pg/070.schema.container.sql @@ -56,17 +56,29 @@ CREATE TABLE container.copy_bucket_item ( ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, - target_copy INT NOT NULL - REFERENCES asset."copy" (id) - ON DELETE CASCADE - ON UPDATE CASCADE - DEFERRABLE - INITIALLY DEFERRED, + target_copy INT NOT NULL, pos INT, create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); CREATE INDEX copy_bucket_item_bucket_idx ON container.copy_bucket_item (bucket); +CREATE OR REPLACE FUNCTION evergreen.container_copy_bucket_item_target_copy_inh_fkey() RETURNS TRIGGER AS $f$ +BEGIN + PERFORM 1 FROM asset.copy WHERE id = NEW.target_copy; + IF NOT FOUND THEN + RAISE foreign_key_violation USING MESSAGE = FORMAT( + $$Referenced asset.copy id not found, target_copy:%s$$, NEW.target_copy + ); + END IF; + RETURN NEW; +END; +$f$ LANGUAGE PLPGSQL VOLATILE COST 50; + +CREATE CONSTRAINT TRIGGER inherit_copy_bucket_item_target_copy_fkey + AFTER UPDATE OR INSERT OR DELETE ON container.copy_bucket_item + DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.container_copy_bucket_item_target_copy_inh_fkey(); + + CREATE TABLE container.copy_bucket_item_note ( id SERIAL PRIMARY KEY, item INT NOT NULL REFERENCES container.copy_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, diff --git a/Open-ILS/src/sql/Pg/800.fkeys.sql b/Open-ILS/src/sql/Pg/800.fkeys.sql index 7d10125289..8ae7d2647d 100644 --- a/Open-ILS/src/sql/Pg/800.fkeys.sql +++ b/Open-ILS/src/sql/Pg/800.fkeys.sql @@ -108,10 +108,40 @@ ALTER TABLE serial.unit ADD CONSTRAINT serial_unit_call_number_fkey FOREIGN KEY ALTER TABLE serial.unit ADD CONSTRAINT serial_unit_creator_fkey FOREIGN KEY (creator) REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; ALTER TABLE serial.unit ADD CONSTRAINT serial_unit_editor_fkey FOREIGN KEY (editor) REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vandelay.import_item ADD CONSTRAINT imported_as_fkey FOREIGN KEY (imported_as) REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED; +CREATE OR REPLACE FUNCTION evergreen.vandelay_import_item_imported_as_inh_fkey() RETURNS TRIGGER AS $f$ +BEGIN + PERFORM 1 FROM asset.copy WHERE id = NEW.imported_as; + IF NOT FOUND THEN + RAISE foreign_key_violation USING MESSAGE = FORMAT( + $$Referenced asset.copy id not found, imported_as:%s$$, NEW.imported_as + ); + END IF; + RETURN NEW; +END; +$f$ LANGUAGE PLPGSQL VOLATILE COST 50; + +CREATE CONSTRAINT TRIGGER inherit_import_item_imported_as_fkey + AFTER UPDATE OR INSERT OR DELETE ON vandelay.import_item + DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.vandelay_import_item_imported_as_inh_fkey(); + ALTER TABLE vandelay.bib_queue ADD CONSTRAINT match_bucket_fkey FOREIGN KEY (match_bucket) REFERENCES container.biblio_record_entry_bucket(id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE asset.copy_note ADD CONSTRAINT asset_copy_note_copy_fkey FOREIGN KEY (owning_copy) REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED; +CREATE OR REPLACE FUNCTION evergreen.asset_copy_note_owning_copy_inh_fkey() RETURNS TRIGGER AS $f$ +BEGIN + PERFORM 1 FROM asset.copy WHERE id = NEW.owning_copy; + IF NOT FOUND THEN + RAISE foreign_key_violation USING MESSAGE = FORMAT( + $$Referenced asset.copy id not found, owning_copy:%s$$, NEW.owning_copy + ); + END IF; + RETURN NEW; +END; +$f$ LANGUAGE PLPGSQL VOLATILE COST 50; + +CREATE CONSTRAINT TRIGGER inherit_asset_copy_note_copy_fkey + AFTER UPDATE OR INSERT OR DELETE ON asset.copy_note + DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_copy_note_owning_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; ALTER TABLE asset.call_number ADD CONSTRAINT asset_call_number_owning_lib_fkey FOREIGN KEY (owning_lib) REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.inheritance-constraint-trigger.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.inheritance-constraint-trigger.sql new file mode 100644 index 0000000000..94f40007f0 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.inheritance-constraint-trigger.sql @@ -0,0 +1,44 @@ +BEGIN; + +DO $temp$ +DECLARE + r RECORD; +BEGIN + + FOR r IN SELECT t.table_schema AS sname, + t.table_name AS tname, + t.column_name AS colname, + t.constraint_name + FROM information_schema.referential_constraints ref + JOIN information_schema.key_column_usage t USING (constraint_schema,constraint_name) + WHERE ref.unique_constraint_schema = 'asset' + AND ref.unique_constraint_name = 'copy_pkey' + LOOP + + EXECUTE 'ALTER TABLE '||r.sname||'.'||r.tname||' DROP CONSTRAINT '||r.constraint_name||';'; + + EXECUTE ' + CREATE OR REPLACE FUNCTION evergreen.'||r.sname||'_'||r.tname||'_'||r.colname||'_inh_fkey() RETURNS TRIGGER AS $f$ + BEGIN + PERFORM 1 FROM asset.copy WHERE id = NEW.'||r.colname||'; + IF NOT FOUND THEN + RAISE foreign_key_violation USING MESSAGE = FORMAT( + $$Referenced asset.copy id not found, '||r.colname||':%s$$, NEW.'||r.colname||' + ); + END IF; + RETURN NEW; + END; + $f$ LANGUAGE PLPGSQL VOLATILE COST 50; + '; + + EXECUTE ' + CREATE CONSTRAINT TRIGGER inherit_'||r.constraint_name||' + AFTER UPDATE OR INSERT OR DELETE ON '||r.sname||'.'||r.tname||' + DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.'||r.sname||'_'||r.tname||'_'||r.colname||'_inh_fkey(); + '; + END LOOP; +END +$temp$; + +COMMIT; + -- 2.11.0