From 25c2920a955b5c5b5343952e891a5f914ae7cbc0 Mon Sep 17 00:00:00 2001 From: Aleisha Amohia Date: Mon, 1 Mar 2021 15:25:13 +1300 Subject: [PATCH] Bug 15516: Database and installer updates Signed-off-by: Michal Denar Signed-off-by: Victor Grousset/tuxayo --- .../bug_15516_-_hold_group_table.perl | 22 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 18 ++++++++++++++- 2 files changed, 39 insertions(+), 1 deletion(-) create mode 100644 installer/data/mysql/atomicupdate/bug_15516_-_hold_group_table.perl diff --git a/installer/data/mysql/atomicupdate/bug_15516_-_hold_group_table.perl b/installer/data/mysql/atomicupdate/bug_15516_-_hold_group_table.perl new file mode 100644 index 0000000000..3338df73dd --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_15516_-_hold_group_table.perl @@ -0,0 +1,22 @@ +$DBversion = 'XXX'; +if( CheckVersion( $DBversion ) ) { + + unless( TableExists( 'hold_groups' ) ) { + $dbh->do(q{CREATE TABLE hold_groups ( + hold_group_id int unsigned NOT NULL AUTO_INCREMENT, + PRIMARY KEY (hold_group_id) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci}); + } + + unless( column_exists( 'reserves', 'hold_group_id' ) ) { + $dbh->do(q{ALTER TABLE reserves ADD COLUMN hold_group_id int unsigned NULL DEFAULT NULL AFTER non_priority}); + $dbh->do(q{ALTER TABLE reserves ADD CONSTRAINT reserves_ibfk_7 FOREIGN KEY (hold_group_id) REFERENCES hold_groups (hold_group_id) ON DELETE SET NULL ON UPDATE CASCADE}); + } + + unless( column_exists( 'old_reserves', 'hold_group_id') ) { + $dbh->do(q{ALTER TABLE old_reserves ADD COLUMN hold_group_id int unsigned NULL DEFAULT NULL AFTER non_priority}); + $dbh->do(q{ALTER TABLE old_reserves ADD CONSTRAINT old_reserves_ibfk_5 FOREIGN KEY (hold_group_id) REFERENCES hold_groups (hold_group_id) ON DELETE SET NULL ON UPDATE CASCADE}); + } + + NewVersion( $DBversion, 15516, "Adding hold_groups table"); +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index aa39f89043..056844cb52 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3851,6 +3851,18 @@ CREATE TABLE `old_issues` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; +-- +-- Table structure for table `hold_groups` +-- +DROP TABLE IF EXISTS `hold_groups`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE hold_groups ( + hold_group_id int unsigned NOT NULL AUTO_INCREMENT, + PRIMARY KEY (hold_group_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + -- -- Table structure for table `old_reserves` -- @@ -3882,6 +3894,7 @@ CREATE TABLE `old_reserves` ( `itemtype` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'If record level hold, the optional itemtype of the item the patron is requesting', `item_level_hold` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Is the hpld placed at item level', `non_priority` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Is this a non priority hold', + `hold_group_id` int unsigned NULL DEFAULT NULL COMMENT 'The id of a group of titles reservations fulfilled when one title is picked', PRIMARY KEY (`reserve_id`), KEY `old_reserves_borrowernumber` (`borrowernumber`), KEY `old_reserves_biblionumber` (`biblionumber`), @@ -3891,7 +3904,8 @@ CREATE TABLE `old_reserves` ( CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL, CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE SET NULL, CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL, - CONSTRAINT `old_reserves_ibfk_4` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE SET NULL ON UPDATE SET NULL + CONSTRAINT `old_reserves_ibfk_4` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE SET NULL ON UPDATE SET NULL, + CONSTRAINT `old_reserves_ibfk_5` FOREIGN KEY (`hold_group_id`) REFERENCES `hold_groups` (`hold_group_id`) ON DELETE SET NULL ON UPDATE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; @@ -4321,6 +4335,7 @@ CREATE TABLE `reserves` ( `itemtype` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'If record level hold, the optional itemtype of the item the patron is requesting', `item_level_hold` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Is the hpld placed at item level', `non_priority` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Is this a non priority hold', + `hold_group_id` int unsigned NULL DEFAULT NULL COMMENT 'The id of a group of titles reservations fulfilled when one title is picked', PRIMARY KEY (`reserve_id`), KEY `priorityfoundidx` (`priority`,`found`), KEY `borrowernumber` (`borrowernumber`), @@ -4335,6 +4350,7 @@ CREATE TABLE `reserves` ( CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `reserves_ibfk_5` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `reserves_ibfk_6` FOREIGN KEY (`desk_id`) REFERENCES `desks` (`desk_id`) ON DELETE SET NULL ON UPDATE CASCADE + CONSTRAINT `reserves_ibfk_7` FOREIGN KEY (`hold_group_id`) REFERENCES `hold_groups` (`hold_group_id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- 2.31.1