From 8e947ceba93ae10bde35d15901db38359ed4773d Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Wed, 10 May 2023 14:50:14 +0200 Subject: [PATCH] Bug 35717: DB changes Signed-off-by: Nick Clemens --- installer/data/mysql/atomicupdate/bug_28844.pl | 18 ++++++++++++++++++ installer/data/mysql/kohastructure.sql | 4 +++- 2 files changed, 21 insertions(+), 1 deletion(-) create mode 100755 installer/data/mysql/atomicupdate/bug_28844.pl diff --git a/installer/data/mysql/atomicupdate/bug_28844.pl b/installer/data/mysql/atomicupdate/bug_28844.pl new file mode 100755 index 00000000000..a11b3f154be --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_28844.pl @@ -0,0 +1,18 @@ +use Modern::Perl; + +return { + bug_number => "28844", + description => "Link orders and suggestions", + up => sub { + my ($args) = @_; + my ($dbh, $out) = @$args{qw(dbh out)}; + unless ( column_exists('suggestions', 'ordernumber') ) { + $dbh->do(q{ + ALTER TABLE suggestions + ADD COLUMN `ordernumber` int(10) DEFAULT NULL COMMENT 'links suggestion to an acquisitions order (aqorders.ordernumber)' AFTER biblionumber, + ADD CONSTRAINT `suggestions_ordernumber` FOREIGN KEY (`ordernumber`) REFERENCES `aqorders` (`ordernumber`) ON DELETE SET NULL ON UPDATE CASCADE + }); + say $out "Added column 'suggestions.ordernumber'"; + } + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index e463e8d2f4f..b2c1345f621 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -6135,6 +6135,7 @@ CREATE TABLE `suggestions` ( `place` varchar(255) DEFAULT NULL COMMENT 'publication place of the suggested item', `isbn` varchar(30) DEFAULT NULL COMMENT 'isbn of the suggested item', `biblionumber` int(11) DEFAULT NULL COMMENT 'foreign key linking the suggestion to the biblio table after the suggestion has been ordered', + `ordernumber` int(10) DEFAULT NULL COMMENT 'foreign key linking the suggestion to the acquisitions order after the suggestion has been ordered', `reason` mediumtext DEFAULT NULL COMMENT 'reason for accepting or rejecting the suggestion', `patronreason` mediumtext DEFAULT NULL COMMENT 'reason for making the suggestion', `budgetid` int(11) DEFAULT NULL COMMENT 'foreign key linking the suggested budget to the aqbudgets table', @@ -6162,7 +6163,8 @@ CREATE TABLE `suggestions` ( CONSTRAINT `suggestions_ibfk_lastmodificationby` FOREIGN KEY (`lastmodificationby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `suggestions_ibfk_managedby` FOREIGN KEY (`managedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `suggestions_ibfk_rejectedby` FOREIGN KEY (`rejectedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, - CONSTRAINT `suggestions_ibfk_suggestedby` FOREIGN KEY (`suggestedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE + CONSTRAINT `suggestions_ibfk_suggestedby` FOREIGN KEY (`suggestedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `suggestions_ordernumber` FOREIGN KEY (`ordernumber`) REFERENCES `aqorders` (`ordernumber`) 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.39.2