From 53085682191011be5750c17e29f05702588cf315 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Tue, 23 Sep 2025 12:30:47 +0200 Subject: [PATCH] Bug 40704: DB changes --- .../data/mysql/atomicupdate/bug_40704.pl | 45 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 2 + 2 files changed, 47 insertions(+) create mode 100755 installer/data/mysql/atomicupdate/bug_40704.pl diff --git a/installer/data/mysql/atomicupdate/bug_40704.pl b/installer/data/mysql/atomicupdate/bug_40704.pl new file mode 100755 index 00000000000..fdf834604bd --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_40704.pl @@ -0,0 +1,45 @@ +use Modern::Perl; +use Koha::Installer::Output qw(say_warning say_success say_info); + +return { + bug_number => "40704", + description => "Add column issues.deleted_itemnumber and old_issues.deleted_itemnumber", + up => sub { + my ($args) = @_; + my ( $dbh, $out ) = @$args{qw(dbh out)}; + + unless ( column_exists( 'issues', 'deleted_itemnumber' ) ) { + $dbh->do( + q{ + ALTER TABLE issues + ADD COLUMN `deleted_itemnumber` int(11) DEFAULT NULL COMMENT 'foreign key, linking this to the deleteditems table for the item that was checked out' AFTER itemnumber; + } + ); + } + unless ( foreign_key_exists( 'issues', 'issues_ibfk_deleteditems_itemnumber' ) ) { + $dbh->do( + q{ + ALTER TABLE issues + ADD CONSTRAINT `issues_ibfk_deleteditems_itemnumber` FOREIGN KEY (`deleted_itemnumber`) REFERENCES `deleteditems` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL + } + ); + } + + unless ( column_exists( 'old_issues', 'deleted_itemnumber' ) ) { + $dbh->do( + q{ + ALTER TABLE old_issues + ADD COLUMN `deleted_itemnumber` int(11) DEFAULT NULL COMMENT 'foreign key, linking this to the deleteditems table for the item that was checked out' AFTER itemnumber; + } + ); + } + unless ( foreign_key_exists( 'old_issues', 'old_issues_ibfk_deleteditems_itemnumber' ) ) { + $dbh->do( + q{ + ALTER TABLE old_issues + ADD CONSTRAINT `old_issues_ibfk_deleteditems_itemnumber` FOREIGN KEY (`deleted_itemnumber`) REFERENCES `deleteditems` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL + } + ); + } + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 948683dc43b..50916cea286 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -5034,6 +5034,7 @@ CREATE TABLE `old_issues` ( `borrowernumber` int(11) DEFAULT NULL COMMENT 'foreign key, linking this to the borrowers table for the patron this item was checked out to', `issuer_id` int(11) DEFAULT NULL COMMENT 'foreign key, linking this to the borrowers table for the user who checked out this item', `itemnumber` int(11) DEFAULT NULL COMMENT 'foreign key, linking this to the items table for the item that was checked out', + `deleted_itemnumber` int(11) DEFAULT NULL COMMENT 'foreign key, linking this to the deleteditems table for the item that was checked out', `booking_id` int(11) DEFAULT NULL COMMENT 'foreign key linking this checkout to the booking it fulfills', `date_due` datetime DEFAULT NULL COMMENT 'date the item is due (yyyy-mm-dd)', `branchcode` varchar(10) DEFAULT NULL COMMENT 'foreign key, linking to the branches table for the location the item was checked out', @@ -5058,6 +5059,7 @@ CREATE TABLE `old_issues` ( KEY `old_issues_ibfk_borrowers_borrowernumber` (`issuer_id`), CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL, CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL, + CONSTRAINT `old_issues_ibfk_deleteditems_itemnumber` FOREIGN KEY (`deleted_itemnumber`) REFERENCES `deleteditems` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL, CONSTRAINT `old_issues_ibfk_borrowers_borrowernumber` FOREIGN KEY (`issuer_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `old_issues_booking_id_fk` FOREIGN KEY (`booking_id`) REFERENCES `bookings` (`booking_id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 2.34.1