From 9e1f8b62fed4265ca9e4775dc77f4b4e670dc75e Mon Sep 17 00:00:00 2001
From: Rogan Hamby <rogan.hamby@gmail.com>
Date: Tue, 16 Apr 2019 12:49:07 -0400
Subject: [PATCH] adding fk constraint to hold_type on action.hold_request and
 reporter class

Signed-off-by: Rogan Hamby <rogan.hamby@gmail.com>
Signed-off-by: Chris Sharp <csharp@georgialibraries.org>
---
 Open-ILS/examples/fm_IDL.xml                  |  9 +++++++++
 Open-ILS/src/sql/Pg/002.schema.config.sql     | 17 +++++++++++++++++
 Open-ILS/src/sql/Pg/090.schema.action.sql     |  2 +-
 ....action_hold_request_fkey_to_hold_type.sql | 19 +++++++++++++++++++
 4 files changed, 46 insertions(+), 1 deletion(-)
 create mode 100644 Open-ILS/src/sql/Pg/upgrade/xxxx.schema.action_hold_request_fkey_to_hold_type.sql

diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml
index 7270ee3be6..c8db25c2b6 100644
--- a/Open-ILS/examples/fm_IDL.xml
+++ b/Open-ILS/examples/fm_IDL.xml
@@ -6356,6 +6356,7 @@ SELECT  usr,
 			<link field="current_shelf_lib" reltype="has_a" key="id" map="" class="aou"/>
 			<link field="sms_carrier" reltype="has_a" key="id" map="" class="csc"/>
 			<link field="acq_request" reltype="has_a" key="id" map="" class="aur"/>
+			<link field="hold_type" reltype="has_a" key="hold_type" map="" class="cht"/>
 		</links>
 		<permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
 			<actions>
@@ -12813,6 +12814,14 @@ SELECT  usr,
 		</links>
 	</class>
 
+	<class id="cht" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="config::hold_type" oils_persist:tablename="config.hold_type" reporter:label="Hold Type" oils_persist:readonly="true">
+    	<fields oils_persist:primary="id" oils_persist:sequence="config.hold_type_id_seq">
+        	<field name="id" reporter:selector="name" reporter:datatype="id"/>
+        	<field name="description"  reporter:datatype="text" oils_persist:i18n="true"/>
+        	<field name="hold_type"  reporter:datatype="text" oils_persist:i18n="true"/>
+    	</fields>
+	</class>
+
 	<!-- ********************************************************************************************************************* -->
 </IDL>
 
diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql
index b516ac60e8..d70e59d05a 100644
--- a/Open-ILS/src/sql/Pg/002.schema.config.sql
+++ b/Open-ILS/src/sql/Pg/002.schema.config.sql
@@ -1318,4 +1318,21 @@ CREATE TABLE config.copy_tag_type (
 CREATE INDEX config_copy_tag_type_owner_idx
     ON config.copy_tag_type (owner);
 
+CREATE TABLE config.hold_type (
+    id          SERIAL,
+    hold_type   TEXT UNIQUE,
+    description TEXT
+);
+
+INSERT INTO config.hold_type (hold_type,description) VALUES
+    ('C','Copy Hold'),
+    ('V','Volume Hold'),
+    ('T','Title Hold'),
+    ('M','Metarecord Hold'),
+    ('R','Recall Hold'),
+    ('F','Force Hold'),
+    ('I','Issuance Hold'),
+    ('P','Part Hold')
+;
+
 COMMIT;
diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql
index b2d2e185ac..900c1b36d3 100644
--- a/Open-ILS/src/sql/Pg/090.schema.action.sql
+++ b/Open-ILS/src/sql/Pg/090.schema.action.sql
@@ -442,7 +442,7 @@ CREATE TABLE action.hold_request (
 	selection_ou		INT				NOT NULL,
 	selection_depth		INT				NOT NULL DEFAULT 0,
 	pickup_lib		INT				NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
-	hold_type		TEXT				NOT NULL, -- CHECK (hold_type IN ('M','T','V','C')),  -- XXX constraint too constraining...
+	hold_type		TEXT				REFERENCES config.hold_type (hold_type) DEFERRABLE INITIALLY DEFERRED,
 	holdable_formats	TEXT,
 	phone_notify		TEXT,
 	email_notify		BOOL				NOT NULL DEFAULT FALSE,
diff --git a/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.action_hold_request_fkey_to_hold_type.sql b/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.action_hold_request_fkey_to_hold_type.sql
new file mode 100644
index 0000000000..8362f62397
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.action_hold_request_fkey_to_hold_type.sql
@@ -0,0 +1,19 @@
+CREATE TABLE config.hold_type (
+    id          SERIAL,
+    hold_type   TEXT UNIQUE,
+    description TEXT
+);
+
+INSERT INTO config.hold_type (hold_type,description) VALUES
+    ('C','Copy Hold'),
+    ('V','Volume Hold'),
+    ('T','Title Hold'),
+    ('M','Metarecord Hold'),
+    ('R','Recall Hold'),
+    ('F','Force Hold'),
+    ('I','Issuance Hold'),
+    ('P','Part Hold')
+;
+
+ALTER TABLE action.hold_request ADD CONSTRAINT hold_request_hold_type_fkey FOREIGN KEY (hold_type) REFERENCES config.hold_type(hold_type) DEFERRABLE INITIALLY DEFERRED;
+
-- 
2.43.2