From ec35ad583869f324f908fe43038487f7961f9c4b Mon Sep 17 00:00:00 2001
From: Aleisha Amohia <aleishaamohia@hotmail.com>
Date: Wed, 3 Feb 2021 10:01:15 +1300
Subject: [PATCH] Bug 14237: Database updates
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

This patch adds a biblionumber column to course_items, adds a
relationship between course_items.biblionumber and biblio.biblionumber,
and changes course_items.itemnumber to allow null values.

Signed-off-by: Christian Stelzenmüller <christian.stelzenmueller@bsz-bw.de>
---
 .../bug_14237-add_course_items.biblionumber_column.perl        | 10 ++++++++++
 installer/data/mysql/kohastructure.sql                         |  7 +++++--
 2 files changed, 15 insertions(+), 2 deletions(-)
 create mode 100644 installer/data/mysql/atomicupdate/bug_14237-add_course_items.biblionumber_column.perl

diff --git a/installer/data/mysql/atomicupdate/bug_14237-add_course_items.biblionumber_column.perl b/installer/data/mysql/atomicupdate/bug_14237-add_course_items.biblionumber_column.perl
new file mode 100644
index 0000000000..185c67a213
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_14237-add_course_items.biblionumber_column.perl
@@ -0,0 +1,10 @@
+$DBversion = 'XXX';
+if( CheckVersion( $DBversion ) ){
+    unless( column_exists( 'course_items', 'biblionumber') ) {
+        $dbh->do(q{ ALTER TABLE course_items ADD `biblionumber` int(11) NOT NULL AFTER `itemnumber` });
+        $dbh->do(q{ ALTER TABLE course_items ADD CONSTRAINT `fk_course_items_biblionumber` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE });
+        $dbh->do(q{ ALTER TABLE course_items CHANGE `itemnumber` `itemnumber` int(11) DEFAULT NULL });
+    }
+
+    NewVersion( $DBversion, 14237, ["Add course_items.biblionumber column", "Add fk_course_items_biblionumber constraint", "Change course_items.itemnumber to allow NULL values"] );
+}
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index ae247e67c8..74ac4d492f 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -2028,7 +2028,8 @@ DROP TABLE IF EXISTS `course_items`;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `course_items` (
   `ci_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'course item id',
-  `itemnumber` int(11) NOT NULL COMMENT 'items.itemnumber for the item on reserve',
+  `itemnumber` int(11) DEFAULT NULL COMMENT 'items.itemnumber for the item on reserve',
+  `biblionumber` int(11) NOT NULL COMMENT 'biblio.biblionumber for the bibliographic record on reserve',
   `itype` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'new itemtype for the item to have while on reserve (optional)',
   `itype_enabled` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'indicates if itype should be changed while on course reserve',
   `itype_storage` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'a place to store the itype when item is on course reserve',
@@ -2051,10 +2052,12 @@ CREATE TABLE `course_items` (
   KEY `holdingbranch` (`holdingbranch`),
   KEY `fk_course_items_homebranch` (`homebranch`),
   KEY `fk_course_items_homebranch_storage` (`homebranch_storage`),
+  KEY `fk_course_items_biblionumber` (`biblionumber`),
   CONSTRAINT `course_items_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT `course_items_ibfk_2` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT `fk_course_items_homebranch` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
-  CONSTRAINT `fk_course_items_homebranch_storage` FOREIGN KEY (`homebranch_storage`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
+  CONSTRAINT `fk_course_items_homebranch_storage` FOREIGN KEY (`homebranch_storage`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `fk_course_items_biblionumber` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
-- 
2.11.0