From 0ab48805b626e96dfe1f1997558177a48e7073ec Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Tue, 16 Sep 2025 13:45:14 +0100 Subject: [PATCH] Bug 40817: Update database for hold-account linking This patch adds database schema changes to support direct linking between holds and their associated account lines (fees/charges): * Add reserve_id and old_reserve_id columns to accountlines table * Add foreign key constraints with CASCADE/SET NULL behavior * Add indexes for performance on the new foreign key columns * Add atomic update script for safe database migration These schema changes enable: - Direct linking between current holds and their fees - Automatic migration of account line links when holds move to old_reserves - Proper referential integrity with cascading behavior --- .../data/mysql/atomicupdate/bug_40817.pl | 47 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 8 +++- 2 files changed, 54 insertions(+), 1 deletion(-) create mode 100755 installer/data/mysql/atomicupdate/bug_40817.pl diff --git a/installer/data/mysql/atomicupdate/bug_40817.pl b/installer/data/mysql/atomicupdate/bug_40817.pl new file mode 100755 index 00000000000..38576743459 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_40817.pl @@ -0,0 +1,47 @@ +use Modern::Perl; +use Koha::Installer::Output qw(say_warning say_failure say_success say_info); + +return { + bug_number => "40817", + description => "Add reserve_id and old_reserve_id fields to accountlines table for hold-fee linking", + up => sub { + my ($args) = @_; + my ( $dbh, $out ) = @$args{qw(dbh out)}; + + # Add reserve_id field to accountlines table + unless ( column_exists( 'accountlines', 'reserve_id' ) ) { + $dbh->do( + q{ + ALTER TABLE accountlines + ADD COLUMN reserve_id int(11) DEFAULT NULL AFTER old_issue_id, + ADD KEY `accountlines_ibfk_reserves` (`reserve_id`), + ADD CONSTRAINT `accountlines_ibfk_reserves` + FOREIGN KEY (`reserve_id`) REFERENCES `reserves` (`reserve_id`) + ON DELETE SET NULL ON UPDATE CASCADE + } + ); + say_success( $out, "Added reserve_id field to accountlines table" ); + } else { + say_info( $out, "reserve_id field already exists in accountlines table" ); + } + + # Add old_reserve_id field to accountlines table + unless ( column_exists( 'accountlines', 'old_reserve_id' ) ) { + $dbh->do( + q{ + ALTER TABLE accountlines + ADD COLUMN old_reserve_id int(11) DEFAULT NULL AFTER reserve_id, + ADD KEY `accountlines_ibfk_old_reserves` (`old_reserve_id`), + ADD CONSTRAINT `accountlines_ibfk_old_reserves` + FOREIGN KEY (`old_reserve_id`) REFERENCES `old_reserves` (`reserve_id`) + ON DELETE SET NULL ON UPDATE CASCADE + } + ); + say_success( $out, "Added old_reserve_id field to accountlines table" ); + } else { + say_info( $out, "old_reserve_id field already exists in accountlines table" ); + } + + say_success( $out, "Bug 40817: Hold-fee linking via direct foreign keys implemented successfully" ); + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 61cec429db6..dfba5bc392d 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -120,6 +120,8 @@ CREATE TABLE `accountlines` ( `accountlines_id` int(11) NOT NULL AUTO_INCREMENT, `issue_id` int(11) DEFAULT NULL, `old_issue_id` int(11) DEFAULT NULL, + `reserve_id` int(11) DEFAULT NULL, + `old_reserve_id` int(11) DEFAULT NULL, `borrowernumber` int(11) DEFAULT NULL, `itemnumber` int(11) DEFAULT NULL, `date` timestamp NULL DEFAULT NULL, @@ -148,6 +150,8 @@ CREATE TABLE `accountlines` ( KEY `accountlines_ibfk_registers` (`register_id`), KEY `accountlines_ibfk_old_issues` (`old_issue_id`), KEY `accountlines_ibfk_issues` (`issue_id`), + KEY `accountlines_ibfk_reserves` (`reserve_id`), + KEY `accountlines_ibfk_old_reserves` (`old_reserve_id`), CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE, @@ -156,7 +160,9 @@ CREATE TABLE `accountlines` ( CONSTRAINT `accountlines_ibfk_issues` FOREIGN KEY (`issue_id`) REFERENCES `issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `accountlines_ibfk_old_issues` FOREIGN KEY (`old_issue_id`) REFERENCES `old_issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE, - CONSTRAINT `accountlines_ibfk_registers` FOREIGN KEY (`register_id`) REFERENCES `cash_registers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE + CONSTRAINT `accountlines_ibfk_registers` FOREIGN KEY (`register_id`) REFERENCES `cash_registers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `accountlines_ibfk_reserves` FOREIGN KEY (`reserve_id`) REFERENCES `reserves` (`reserve_id`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `accountlines_ibfk_old_reserves` FOREIGN KEY (`old_reserve_id`) REFERENCES `old_reserves` (`reserve_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