Bugzilla – Attachment 185895 Details for
Bug 40665
Add booking_id field to issues to link checkouts to bookings that were fulfilled by them
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 40665: Database update
Bug-40665-Database-update.patch (text/plain), 6.85 KB, created by
Martin Renvoize (ashimema)
on 2025-08-29 10:32:13 UTC
(
hide
)
Description:
Bug 40665: Database update
Filename:
MIME Type:
Creator:
Martin Renvoize (ashimema)
Created:
2025-08-29 10:32:13 UTC
Size:
6.85 KB
patch
obsolete
>From dac6e59fd01ca3302dbb609eb04e400e8ac0a4bc Mon Sep 17 00:00:00 2001 >From: Paul Derscheid <paul.derscheid@lmscloud.de> >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 <martin.renvoize@openfifth.co.uk> >--- > ..._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
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 40665
:
185516
|
185517
|
185518
|
185519
|
185520
| 185895 |
185896
|
185897
|
185898
|
185899
|
186250
|
186369
|
187589