From 6479b57853d552d227849da063ae8511a33f001e Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Wed, 30 Mar 2016 17:49:26 -0400 Subject: [PATCH] LP#1564079 Checkout history handles serials Remove the target_copy foreign key constraint on action.usr_circ_history so that it does not prevent creation of rows from circulation's that link to serial.unit objects. Similar to action.circulation, the foreign key constraint is enforced via a evergreen.fake_fkey_tgr trigger instead. This modifies the 2.9.3-2.10.0-upgrade-db.sql and 0960 upgrade scripts, since without this change, the scripts may fail. A separate upgrade script is added to drop the constraint for those who have already successfully run the the 2.9.3-2.10.0-upgrade-db.sql script (i.e. those who don't use serials). For simplicity, the evergreen.fake_fkey_tgr trigger is only applied in this separate upgrade script (in addition to the base schema, of course). Signed-off-by: Bill Erickson Signed-off-by: Kathy Lussier --- Open-ILS/src/sql/Pg/090.schema.action.sql | 7 +++++-- .../src/sql/Pg/upgrade/0960.schema.decouple_co_history.sql | 3 +-- .../Pg/upgrade/XXXX.schema.fix_circ_history_copy_ref.sql | 14 ++++++++++++++ .../src/sql/Pg/version-upgrade/2.9.3-2.10.0-upgrade-db.sql | 3 +-- 4 files changed, 21 insertions(+), 6 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix_circ_history_copy_ref.sql diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index f203aa956f..74b2ca4fa1 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -1311,14 +1311,17 @@ CREATE TABLE action.usr_circ_history ( usr INTEGER NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED, xact_start TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), - target_copy BIGINT NOT NULL REFERENCES asset.copy(id) - DEFERRABLE INITIALLY DEFERRED, + target_copy BIGINT NOT NULL, -- asset.copy.id / serial.unit.id due_date TIMESTAMP WITH TIME ZONE NOT NULL, checkin_time TIMESTAMP WITH TIME ZONE, source_circ BIGINT REFERENCES action.circulation(id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED ); +CREATE TRIGGER action_usr_circ_history_target_copy_trig + AFTER INSERT OR UPDATE ON action.usr_circ_history + FOR EACH ROW EXECUTE PROCEDURE evergreen.fake_fkey_tgr('target_copy'); + CREATE OR REPLACE FUNCTION action.maintain_usr_circ_history() RETURNS TRIGGER AS $FUNK$ DECLARE diff --git a/Open-ILS/src/sql/Pg/upgrade/0960.schema.decouple_co_history.sql b/Open-ILS/src/sql/Pg/upgrade/0960.schema.decouple_co_history.sql index 5fc0fed9f2..59e13b7d61 100644 --- a/Open-ILS/src/sql/Pg/upgrade/0960.schema.decouple_co_history.sql +++ b/Open-ILS/src/sql/Pg/upgrade/0960.schema.decouple_co_history.sql @@ -11,8 +11,7 @@ CREATE TABLE action.usr_circ_history ( usr INTEGER NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED, xact_start TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), - target_copy BIGINT NOT NULL REFERENCES asset.copy(id) - DEFERRABLE INITIALLY DEFERRED, + target_copy BIGINT NOT NULL, due_date TIMESTAMP WITH TIME ZONE NOT NULL, checkin_time TIMESTAMP WITH TIME ZONE, source_circ BIGINT REFERENCES action.circulation(id) diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix_circ_history_copy_ref.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix_circ_history_copy_ref.sql new file mode 100644 index 0000000000..2acd1dd1d9 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix_circ_history_copy_ref.sql @@ -0,0 +1,14 @@ + +BEGIN; + +--SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +ALTER TABLE action.usr_circ_history + DROP CONSTRAINT IF EXISTS usr_circ_history_target_copy_fkey; + +CREATE TRIGGER action_usr_circ_history_target_copy_trig + AFTER INSERT OR UPDATE ON action.usr_circ_history + FOR EACH ROW EXECUTE PROCEDURE evergreen.fake_fkey_tgr('target_copy'); + +COMMIT; + diff --git a/Open-ILS/src/sql/Pg/version-upgrade/2.9.3-2.10.0-upgrade-db.sql b/Open-ILS/src/sql/Pg/version-upgrade/2.9.3-2.10.0-upgrade-db.sql index 0856094f8c..7440667420 100644 --- a/Open-ILS/src/sql/Pg/version-upgrade/2.9.3-2.10.0-upgrade-db.sql +++ b/Open-ILS/src/sql/Pg/version-upgrade/2.9.3-2.10.0-upgrade-db.sql @@ -1325,8 +1325,7 @@ CREATE TABLE action.usr_circ_history ( usr INTEGER NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED, xact_start TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), - target_copy BIGINT NOT NULL REFERENCES asset.copy(id) - DEFERRABLE INITIALLY DEFERRED, + target_copy BIGINT NOT NULL, due_date TIMESTAMP WITH TIME ZONE NOT NULL, checkin_time TIMESTAMP WITH TIME ZONE, source_circ BIGINT REFERENCES action.circulation(id) -- 2.11.0