Bugzilla – Attachment 186436 Details for
Bug 40808
Consider a link table for accountlines to varying other Koha tables
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 40808: Add accountline_links table for polymorphic hold-fee linking
Bug-40808-Add-accountlinelinks-table-for-polymorph.patch (text/plain), 7.53 KB, created by
Martin Renvoize (ashimema)
on 2025-09-15 18:00:35 UTC
(
hide
)
Description:
Bug 40808: Add accountline_links table for polymorphic hold-fee linking
Filename:
MIME Type:
Creator:
Martin Renvoize (ashimema)
Created:
2025-09-15 18:00:35 UTC
Size:
7.53 KB
patch
obsolete
>From deec39b79fc6c2b592793aded7abfaee7e15a916 Mon Sep 17 00:00:00 2001 >From: Martin Renvoize <martin.renvoize@openfifth.co.uk> >Date: Mon, 15 Sep 2025 12:11:30 +0100 >Subject: [PATCH] Bug 40808: Add accountline_links table for polymorphic > hold-fee linking > >This patch introduces a new polymorphic linking system to replace the >growing number of nullable foreign key fields in the accountlines table. > >Database changes: >- New accountline_links table with optimized indexes >- Supports linking account lines to holds, checkouts, article requests, etc. >- Uses clean link_type naming (hold, checkout, article_request) >- Migrates existing issue_id and old_issue_id links during upgrade >- Includes database triggers for referential integrity > >Benefits: >- Eliminates nullable FK field bloat in accountlines >- Infinitely extensible for new entity types >- Enables one-to-many relationships (one fee can link to multiple entities) >- Better performance with covering indexes >--- > .../data/mysql/atomicupdate/bug_40808.pl | 110 ++++++++++++++++++ > installer/data/mysql/kohastructure.sql | 22 ++++ > 2 files changed, 132 insertions(+) > create mode 100755 installer/data/mysql/atomicupdate/bug_40808.pl > >diff --git a/installer/data/mysql/atomicupdate/bug_40808.pl b/installer/data/mysql/atomicupdate/bug_40808.pl >new file mode 100755 >index 00000000000..7f78c0afd83 >--- /dev/null >+++ b/installer/data/mysql/atomicupdate/bug_40808.pl >@@ -0,0 +1,110 @@ >+use Modern::Perl; >+use Koha::Installer::Output qw(say_warning say_failure say_success say_info); >+ >+return { >+ bug_number => "XXXXX", >+ description => "Add accountline_links table for polymorphic linking of accountlines to holds, issues, etc.", >+ up => sub { >+ my ($args) = @_; >+ my ( $dbh, $out ) = @$args{qw(dbh out)}; >+ >+ # Create accountline_links table >+ unless ( TableExists('accountline_links') ) { >+ $dbh->do( >+ q{ >+ CREATE TABLE `accountline_links` ( >+ `link_id` int(11) NOT NULL AUTO_INCREMENT, >+ `accountlines_id` int(11) NOT NULL COMMENT 'foreign key to accountlines.accountlines_id', >+ `link_type` varchar(32) NOT NULL COMMENT 'Type of linked entity: reserve, old_reserve, issue, old_issue, article_request, etc.', >+ `linked_id` int(11) NOT NULL COMMENT 'ID of the linked entity', >+ `created_on` timestamp NOT NULL DEFAULT current_timestamp(), >+ PRIMARY KEY (`link_id`), >+ UNIQUE KEY `accountline_link_unique` (`accountlines_id`, `link_type`, `linked_id`), >+ KEY `reverse_lookup` (`link_type`, `linked_id`, `accountlines_id`), >+ KEY `accountlines_id` (`accountlines_id`), >+ KEY `entity_lookup` (`link_type`, `linked_id`), >+ CONSTRAINT `accountline_links_ibfk_accountlines` >+ FOREIGN KEY (`accountlines_id`) REFERENCES `accountlines` (`accountlines_id`) >+ ON DELETE CASCADE ON UPDATE CASCADE >+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci >+ } >+ ); >+ say_success( $out, "Created accountline_links table" ); >+ } else { >+ say_info( $out, "accountline_links table already exists" ); >+ } >+ >+ # Migrate existing issue_id links >+ if ( column_exists( 'accountlines', 'issue_id' ) ) { >+ $dbh->do( >+ q{ >+ INSERT IGNORE INTO accountline_links (accountlines_id, link_type, linked_id) >+ SELECT accountlines_id, 'checkout', issue_id >+ FROM accountlines >+ WHERE issue_id IS NOT NULL >+ } >+ ); >+ say_success( $out, "Migrated existing issue_id links to accountline_links" ); >+ } >+ >+ # Migrate existing old_issue_id links >+ if ( column_exists( 'accountlines', 'old_issue_id' ) ) { >+ $dbh->do( >+ q{ >+ INSERT IGNORE INTO accountline_links (accountlines_id, link_type, linked_id) >+ SELECT accountlines_id, 'old_checkout', old_issue_id >+ FROM accountlines >+ WHERE old_issue_id IS NOT NULL >+ } >+ ); >+ say_success( $out, "Migrated existing old_issue_id links to accountline_links" ); >+ } >+ >+ # Add database triggers for maintaining referential integrity >+ # Cleanup links when reserves are deleted >+ $dbh->do( >+ q{ >+ CREATE TRIGGER accountline_links_cleanup_reserves >+ AFTER DELETE ON reserves >+ FOR EACH ROW >+ DELETE FROM accountline_links >+ WHERE link_type = 'hold' AND linked_id = OLD.reserve_id >+ } >+ ); >+ >+ $dbh->do( >+ q{ >+ CREATE TRIGGER accountline_links_cleanup_old_reserves >+ AFTER DELETE ON old_reserves >+ FOR EACH ROW >+ DELETE FROM accountline_links >+ WHERE link_type = 'old_hold' AND linked_id = OLD.reserve_id >+ } >+ ); >+ >+ # Cleanup links when issues are deleted >+ $dbh->do( >+ q{ >+ CREATE TRIGGER accountline_links_cleanup_issues >+ AFTER DELETE ON issues >+ FOR EACH ROW >+ DELETE FROM accountline_links >+ WHERE link_type = 'checkout' AND linked_id = OLD.issue_id >+ } >+ ); >+ >+ $dbh->do( >+ q{ >+ CREATE TRIGGER accountline_links_cleanup_old_issues >+ AFTER DELETE ON old_issues >+ FOR EACH ROW >+ DELETE FROM accountline_links >+ WHERE link_type = 'old_checkout' AND linked_id = OLD.issue_id >+ } >+ ); >+ >+ say_success( $out, "Created database triggers for referential integrity" ); >+ >+ say_success( $out, "Bug XXXXX: accountline_links polymorphic linking system implemented successfully" ); >+ }, >+}; >diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql >index 61cec429db6..ba9c6e8dd83 100644 >--- a/installer/data/mysql/kohastructure.sql >+++ b/installer/data/mysql/kohastructure.sql >@@ -160,6 +160,28 @@ CREATE TABLE `accountlines` ( > ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; > /*!40101 SET character_set_client = @saved_cs_client */; > >+-- >+-- Table structure for table `accountline_links` >+-- >+ >+DROP TABLE IF EXISTS `accountline_links`; >+/*!40101 SET @saved_cs_client = @@character_set_client */; >+/*!40101 SET character_set_client = utf8mb4 */; >+CREATE TABLE `accountline_links` ( >+ `link_id` int(11) NOT NULL AUTO_INCREMENT, >+ `accountlines_id` int(11) NOT NULL COMMENT 'foreign key to accountlines.accountlines_id', >+ `link_type` varchar(32) NOT NULL COMMENT 'Type of linked entity: hold, old_hold, checkout, old_checkout, article_request, etc.', >+ `linked_id` int(11) NOT NULL COMMENT 'ID of the linked entity', >+ `created_on` timestamp NOT NULL DEFAULT current_timestamp(), >+ PRIMARY KEY (`link_id`), >+ UNIQUE KEY `accountline_link_unique` (`accountlines_id`, `link_type`, `linked_id`), >+ KEY `reverse_lookup` (`link_type`, `linked_id`, `accountlines_id`), >+ KEY `accountlines_id` (`accountlines_id`), >+ KEY `entity_lookup` (`link_type`, `linked_id`), >+ CONSTRAINT `accountline_links_ibfk_accountlines` FOREIGN KEY (`accountlines_id`) REFERENCES `accountlines` (`accountlines_id`) ON DELETE CASCADE ON UPDATE CASCADE >+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; >+/*!40101 SET character_set_client = @saved_cs_client */; >+ > -- > -- Table structure for table `action_logs` > -- >-- >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 40808
:
186436
|
186437
|
186438
|
186439
|
186440
|
186441
|
186442
|
186443
|
186458
|
186459
|
186460
|
186461
|
186462
|
186463
|
186464
|
186465