From 940e920307b037a1f07841e042d0937ad2ce4c8f Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Tue, 23 Sep 2025 17:25:33 +0100 Subject: [PATCH] Bug 38489: Add file_transport_id to vendor_edi_accounts and migrate to new transport system This patch adds database migration to move EDI transport configurations from vendor_edi_accounts to the new file_transports system introduced in Bug 39190. Changes: - Add file_transport_id column to vendor_edi_accounts with foreign key - Add 'local' transport type to file_transports enum - Remove old transport fields from vendor_edi_accounts in kohastructure - Migration script converts existing EDI configurations to file transports - Handles FILE -> local transport type conversion Sponsored-by: ByWater Solutions Signed-off-by: Hannah Dunne-Howrie --- .../data/mysql/atomicupdate/bug_38489.pl | 139 ++++++++++++++++++ installer/data/mysql/kohastructure.sql | 15 +- 2 files changed, 144 insertions(+), 10 deletions(-) create mode 100755 installer/data/mysql/atomicupdate/bug_38489.pl diff --git a/installer/data/mysql/atomicupdate/bug_38489.pl b/installer/data/mysql/atomicupdate/bug_38489.pl new file mode 100755 index 00000000000..e8890b2604a --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_38489.pl @@ -0,0 +1,139 @@ +use Modern::Perl; +use Koha::Installer::Output qw(say_warning say_failure say_success say_info); + +return { + bug_number => "38489", + description => "Migrate EDI transport configuration to use the new file_transports system", + up => sub { + my ($args) = @_; + my ( $dbh, $out ) = @$args{qw(dbh out)}; + + # Check if file_transports table exists (should exist from Bug 39190) + my $sth = $dbh->prepare("SHOW TABLES LIKE 'file_transports'"); + $sth->execute(); + if ( !$sth->fetchrow_array ) { + say_failure( $out, "file_transports table not found. Please ensure Bug 39190 is applied first." ); + return; + } + + # Add file_transport_id column to vendor_edi_accounts + if ( !column_exists( 'vendor_edi_accounts', 'file_transport_id' ) ) { + $dbh->do( + q{ + ALTER TABLE vendor_edi_accounts + ADD COLUMN file_transport_id int(11) DEFAULT NULL AFTER plugin, + ADD KEY `vendor_edi_accounts_file_transport_id` (`file_transport_id`), + ADD CONSTRAINT `vendor_edi_accounts_ibfk_file_transport` + FOREIGN KEY (`file_transport_id`) REFERENCES `file_transports` (`file_transport_id`) + ON DELETE SET NULL ON UPDATE CASCADE + } + ); + say_success( $out, "Added file_transport_id column to vendor_edi_accounts table" ); + } + + # Add 'local' to the transport enum in file_transports table + $dbh->do( + q{ + ALTER TABLE file_transports + MODIFY COLUMN transport ENUM('ftp','sftp','local') NOT NULL DEFAULT 'sftp' + } + ); + say_success( $out, "Added 'local' transport type to file_transports table" ); + + # Migrate existing EDI transport configurations to file_transports + my $migration_count = 0; + my $edi_accounts_sth = $dbh->prepare( + q{ + SELECT id, description, host, username, password, upload_port, download_port, + upload_directory, download_directory, transport + FROM vendor_edi_accounts + WHERE file_transport_id IS NULL + AND host IS NOT NULL + AND host != '' + } + ); + $edi_accounts_sth->execute(); + + my $insert_transport_sth = $dbh->prepare( + q{ + INSERT INTO file_transports (name, host, port, transport, user_name, password, + upload_directory, download_directory, auth_mode, passive, debug) + VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'password', 1, 0) + } + ); + + my $update_edi_account_sth = $dbh->prepare( + q{ + UPDATE vendor_edi_accounts SET file_transport_id = ? WHERE id = ? + } + ); + + while ( my $row = $edi_accounts_sth->fetchrow_hashref ) { + + # Determine appropriate port (default to SFTP=22, FTP=21, Local=NULL) + my $port = $row->{upload_port} || $row->{download_port}; + unless ($port) { + if ( uc( $row->{transport} ) eq 'SFTP' ) { + $port = 22; + } elsif ( uc( $row->{transport} ) eq 'FILE' ) { + $port = undef; # Local transport doesn't use ports + } else { + $port = 21; # FTP default + } + } + + # Map transport type (normalize case and handle FILE -> local) + my $transport_type = lc( $row->{transport} ); + $transport_type = 'local' if $transport_type eq 'file'; + $transport_type = 'ftp' unless $transport_type =~ /^(sftp|local)$/; + + # Create transport name from EDI account description + my $transport_name = sprintf( "EDI Transport for %s", $row->{description} ); + + # Handle host for local transport + my $host = $row->{host}; + $host = 'localhost' if $transport_type eq 'local' && !$host; + + # Insert new file transport + $insert_transport_sth->execute( + $transport_name, + $host, + $port, + $transport_type, + $row->{username}, + $row->{password}, # Password is already encrypted in EDI accounts + $row->{upload_directory}, + $row->{download_directory} + ); + + my $transport_id = $dbh->last_insert_id( undef, undef, 'file_transports', 'file_transport_id' ); + + # Update EDI account to reference the new transport + $update_edi_account_sth->execute( $transport_id, $row->{id} ); + + $migration_count++; + } + + if ( $migration_count > 0 ) { + say_success( + $out, + "Successfully migrated $migration_count EDI transport configurations to file_transports" + ); + } else { + say_info( $out, "No EDI transport configurations found to migrate" ); + } + + # Drop the old transport-related columns from vendor_edi_accounts + my @columns_to_drop = + qw(host username password upload_port download_port upload_directory download_directory transport); + + for my $column (@columns_to_drop) { + if ( column_exists( 'vendor_edi_accounts', $column ) ) { + $dbh->do("ALTER TABLE vendor_edi_accounts DROP COLUMN $column"); + say_success( $out, "Dropped column '$column' from vendor_edi_accounts table" ); + } + } + + say_success( $out, "EDI transport migration completed successfully" ); + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index e391dc68f5d..2496a95d6bf 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -6003,7 +6003,7 @@ CREATE TABLE `file_transports` ( `name` varchar(80) NOT NULL, `host` varchar(80) NOT NULL DEFAULT 'localhost', `port` int(11) NOT NULL DEFAULT 22, - `transport` enum('ftp','sftp') NOT NULL DEFAULT 'sftp', + `transport` enum('ftp','sftp','local') NOT NULL DEFAULT 'sftp', `passive` tinyint(1) NOT NULL DEFAULT 1, `user_name` varchar(80) DEFAULT NULL, `password` mediumtext DEFAULT NULL, @@ -6699,19 +6699,11 @@ DROP TABLE IF EXISTS `vendor_edi_accounts`; CREATE TABLE `vendor_edi_accounts` ( `id` int(11) NOT NULL AUTO_INCREMENT, `description` mediumtext NOT NULL, - `host` varchar(40) DEFAULT NULL, - `username` varchar(40) DEFAULT NULL, - `password` mediumtext DEFAULT NULL, - `upload_port` int(11) DEFAULT NULL, - `download_port` int(11) DEFAULT NULL, `last_activity` date DEFAULT NULL, `vendor_id` int(11) DEFAULT NULL, - `download_directory` mediumtext DEFAULT NULL, - `upload_directory` mediumtext DEFAULT NULL, `san` varchar(20) DEFAULT NULL, `standard` varchar(3) DEFAULT 'EUR', `id_code_qualifier` varchar(3) DEFAULT '14', - `transport` varchar(6) DEFAULT 'FTP', `quotes_enabled` tinyint(1) NOT NULL DEFAULT 0, `invoices_enabled` tinyint(1) NOT NULL DEFAULT 0, `orders_enabled` tinyint(1) NOT NULL DEFAULT 0, @@ -6719,11 +6711,14 @@ CREATE TABLE `vendor_edi_accounts` ( `auto_orders` tinyint(1) NOT NULL DEFAULT 0, `shipment_budget` int(11) DEFAULT NULL, `plugin` varchar(256) NOT NULL DEFAULT '', + `file_transport_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `vendorid` (`vendor_id`), KEY `shipmentbudget` (`shipment_budget`), + KEY `vendor_edi_accounts_file_transport_id` (`file_transport_id`), CONSTRAINT `vfk_shipment_budget` FOREIGN KEY (`shipment_budget`) REFERENCES `aqbudgets` (`budget_id`), - CONSTRAINT `vfk_vendor_id` FOREIGN KEY (`vendor_id`) REFERENCES `aqbooksellers` (`id`) + CONSTRAINT `vfk_vendor_id` FOREIGN KEY (`vendor_id`) REFERENCES `aqbooksellers` (`id`), + CONSTRAINT `vendor_edi_accounts_ibfk_file_transport` FOREIGN KEY (`file_transport_id`) REFERENCES `file_transports` (`file_transport_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