Bugzilla – Attachment 186476 Details for
Bug 40817
Holds charges should be accessible from Holds
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 40817: Update database for hold-account linking
Bug-40817-Update-database-for-hold-account-linking.patch (text/plain), 5.85 KB, created by
Martin Renvoize (ashimema)
on 2025-09-16 12:57:36 UTC
(
hide
)
Description:
Bug 40817: Update database for hold-account linking
Filename:
MIME Type:
Creator:
Martin Renvoize (ashimema)
Created:
2025-09-16 12:57:36 UTC
Size:
5.85 KB
patch
obsolete
>From 0ab48805b626e96dfe1f1997558177a48e7073ec Mon Sep 17 00:00:00 2001 >From: Martin Renvoize <martin.renvoize@openfifth.co.uk> >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
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 40817
: 186476 |
186477
|
186478
|
186479
|
186480
|
186481