From deec39b79fc6c2b592793aded7abfaee7e15a916 Mon Sep 17 00:00:00 2001 From: Martin Renvoize 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