From 4212d08bff944b9b84c292541d3d5d79e002a1f0 Mon Sep 17 00:00:00 2001 From: Aleisha Amohia 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 --- .../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 952cd9d296..0cfead5865 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2025,7 +2025,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', @@ -2048,10 +2049,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