From dac6e59fd01ca3302dbb609eb04e400e8ac0a4bc Mon Sep 17 00:00:00 2001 From: Paul Derscheid Date: Mon, 18 Aug 2025 15:40:05 +0000 Subject: [PATCH] Bug 40665: Database update This patch updates the database schema to add booking_id foreign key columns to both issues and old_issues tables, enabling the linkage between checkouts and the bookings that generated them. Schema changes: - Add booking_id column to issues and old_issues tables - Add foreign key constraints referencing bookings table - Preserve referential integrity with appropriate CASCADE actions Signed-off-by: Martin Renvoize --- ..._40665-add_booking_id_issues_old_issues.pl | 55 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 8 ++- 2 files changed, 61 insertions(+), 2 deletions(-) create mode 100755 installer/data/mysql/atomicupdate/bug_40665-add_booking_id_issues_old_issues.pl diff --git a/installer/data/mysql/atomicupdate/bug_40665-add_booking_id_issues_old_issues.pl b/installer/data/mysql/atomicupdate/bug_40665-add_booking_id_issues_old_issues.pl new file mode 100755 index 00000000000..0e742951c57 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_40665-add_booking_id_issues_old_issues.pl @@ -0,0 +1,55 @@ +use Modern::Perl; +use Koha::Installer::Output qw(say_warning say_success say_info); + +return { + bug_number => "40665", + description => "Add booking_id to issues and old_issues tables to link checkouts to bookings", + up => sub { + my ($args) = @_; + my ( $dbh, $out ) = @$args{qw(dbh out)}; + + if ( !column_exists( 'issues', 'booking_id' ) ) { + $dbh->do( + q{ + ALTER TABLE issues + ADD COLUMN booking_id int(11) DEFAULT NULL + COMMENT 'foreign key linking this checkout to the booking it fulfills' + AFTER itemnumber + } + ); + + $dbh->do( + q{ + ALTER TABLE issues + ADD CONSTRAINT issues_booking_id_fk + FOREIGN KEY (booking_id) REFERENCES bookings(booking_id) + ON DELETE SET NULL ON UPDATE CASCADE + } + ); + + say_success( $out, "Added column 'issues.booking_id'" ); + } + + if ( !column_exists( 'old_issues', 'booking_id' ) ) { + $dbh->do( + q{ + ALTER TABLE old_issues + ADD COLUMN booking_id int(11) DEFAULT NULL + COMMENT 'foreign key linking this checkout to the booking it fulfilled' + AFTER itemnumber + } + ); + + $dbh->do( + q{ + ALTER TABLE old_issues + ADD CONSTRAINT old_issues_booking_id_fk + FOREIGN KEY (booking_id) REFERENCES bookings(booking_id) + ON DELETE SET NULL ON UPDATE CASCADE + } + ); + + say_success( $out, "Added column 'old_issues.booking_id'" ); + } + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 2ad9c12b004..3c2cf7e5cc5 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3976,6 +3976,7 @@ CREATE TABLE `issues` ( `borrowernumber` int(11) NOT 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) NOT NULL COMMENT 'foreign key, linking this to the items 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 'datetime the item is due (yyyy-mm-dd hh:mm::ss)', `branchcode` varchar(10) DEFAULT NULL COMMENT 'foreign key, linking to the branches table for the location the item was checked out', `returndate` datetime DEFAULT NULL COMMENT 'date the item was returned, will be NULL until moved to old_issues', @@ -4000,7 +4001,8 @@ CREATE TABLE `issues` ( KEY `issues_ibfk_borrowers_borrowernumber` (`issuer_id`), CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON UPDATE CASCADE, CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON UPDATE CASCADE, - CONSTRAINT `issues_ibfk_borrowers_borrowernumber` FOREIGN KEY (`issuer_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE + CONSTRAINT `issues_ibfk_borrowers_borrowernumber` FOREIGN KEY (`issuer_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `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; /*!40101 SET character_set_client = @saved_cs_client */; @@ -5032,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', + `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', `returndate` datetime DEFAULT NULL COMMENT 'date the item was returned', @@ -5055,7 +5058,8 @@ 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_borrowers_borrowernumber` FOREIGN KEY (`issuer_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE + 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; /*!40101 SET character_set_client = @saved_cs_client */; -- 2.51.0