Bugzilla – Attachment 186845 Details for
Bug 38489
EDI should be updated to use the new FTP/SFTP Servers management page
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 38489: Add file_transport_id to vendor_edi_accounts and migrate to new transport system
Bug-38489-Add-filetransportid-to-vendorediaccounts.patch (text/plain), 9.37 KB, created by
Martin Renvoize (ashimema)
on 2025-09-23 20:06:52 UTC
(
hide
)
Description:
Bug 38489: Add file_transport_id to vendor_edi_accounts and migrate to new transport system
Filename:
MIME Type:
Creator:
Martin Renvoize (ashimema)
Created:
2025-09-23 20:06:52 UTC
Size:
9.37 KB
patch
obsolete
>From 757ec37c658d5d6168436bb26d7f352cd91d6ca5 Mon Sep 17 00:00:00 2001 >From: Martin Renvoize <martin.renvoize@openfifth.co.uk> >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 >--- > .../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 948683dc43b..b99f456f1b5 100644 >--- a/installer/data/mysql/kohastructure.sql >+++ b/installer/data/mysql/kohastructure.sql >@@ -6000,7 +6000,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, >@@ -6696,19 +6696,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, >@@ -6716,11 +6708,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
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 38489
:
175204
|
175205
|
175206
|
175207
|
176118
|
176119
|
176161
|
185324
|
186819
|
186820
|
186845
|
186846
|
186847
|
186848
|
186849
|
186850
|
186851
|
186852
|
186854
|
187080
|
187081
|
187082
|
187083
|
187084
|
187085
|
187086
|
187087
|
187088
|
187089
|
187108
|
187147
|
187148
|
187149
|
187150
|
187151
|
187152
|
187153
|
187154
|
187155
|
187156
|
187157
|
187158
|
187159
|
187270
|
187271
|
187272
|
187273
|
187274
|
187275
|
187276
|
187277
|
187278
|
187279
|
187280
|
187281
|
187283
|
187284
|
187285
|
187286
|
187287
|
187288
|
187289
|
187290
|
187291
|
187292
|
187293
|
187294