adding a foreign key check for selction_ou on action.hold_request

Signed-off-by: Jane Sandberg <js7389@princeton.edu>

adding a check in the upgrade script for a non-existent selection_ou and changing it to the request_lib

Signed-off-by: Jane Sandberg <js7389@princeton.edu>

diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql
index 69d6b03..7ce47d3 100644 (file)
--- a/Open-ILS/src/sql/Pg/090.schema.action.sql
+++ b/Open-ILS/src/sql/Pg/090.schema.action.sql
@@ -472,7 +472,7 @@ CREATE TABLE action.hold_request (
        request_lib             INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
        requestor               INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
        usr                     INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
-       selection_ou            INT                             NOT NULL,
+       selection_ou            INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
        selection_depth         INT                             NOT NULL DEFAULT 0,
        pickup_lib              INT                             NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
        hold_type               TEXT                            REFERENCES config.hold_type (hold_type) DEFERRABLE INITIALLY DEFERRED,

diff --git a/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.function-selection-ou-fkey.sql b/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.function-selection-ou-fkey.sql
new file mode 100644 (file)
index 0000000..2c8d2ee
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.function-selection-ou-fkey.sql
@@ -0,0 +1,15 @@
+BEGIN;
+
+SELECT evergreen.upgrade_deps_block_check('xxxx', :eg_version);
+
+UPDATE action.hold_request 
+SET selection_ou = request_lib
+WHERE id IN (
+    SELECT ahr.id FROM action.hold_request ahr
+    LEFT JOIN actor.org_unit aou ON aou.id = ahr.selection_ou
+    WHERE aou.id IS NULL
+);
+
+ALTER TABLE action.hold_request ADD CONSTRAINT hold_request_selection_ou_fkey FOREIGN KEY (selection_ou) REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED;
+
+COMMIT;