View | Details | Raw Unified | Return to bug 38489
Collapse All | Expand All

(-)a/installer/data/mysql/atomicupdate/bug_38489.pl (+139 lines)
Line 0 Link Here
1
use Modern::Perl;
2
use Koha::Installer::Output qw(say_warning say_failure say_success say_info);
3
4
return {
5
    bug_number  => "38489",
6
    description => "Migrate EDI transport configuration to use the new file_transports system",
7
    up          => sub {
8
        my ($args) = @_;
9
        my ( $dbh, $out ) = @$args{qw(dbh out)};
10
11
        # Check if file_transports table exists (should exist from Bug 39190)
12
        my $sth = $dbh->prepare("SHOW TABLES LIKE 'file_transports'");
13
        $sth->execute();
14
        if ( !$sth->fetchrow_array ) {
15
            say_failure( $out, "file_transports table not found. Please ensure Bug 39190 is applied first." );
16
            return;
17
        }
18
19
        # Add file_transport_id column to vendor_edi_accounts
20
        if ( !column_exists( 'vendor_edi_accounts', 'file_transport_id' ) ) {
21
            $dbh->do(
22
                q{
23
                ALTER TABLE vendor_edi_accounts
24
                ADD COLUMN file_transport_id int(11) DEFAULT NULL AFTER plugin,
25
                ADD KEY `vendor_edi_accounts_file_transport_id` (`file_transport_id`),
26
                ADD CONSTRAINT `vendor_edi_accounts_ibfk_file_transport`
27
                    FOREIGN KEY (`file_transport_id`) REFERENCES `file_transports` (`file_transport_id`)
28
                    ON DELETE SET NULL ON UPDATE CASCADE
29
            }
30
            );
31
            say_success( $out, "Added file_transport_id column to vendor_edi_accounts table" );
32
        }
33
34
        # Add 'local' to the transport enum in file_transports table
35
        $dbh->do(
36
            q{
37
            ALTER TABLE file_transports
38
            MODIFY COLUMN transport ENUM('ftp','sftp','local') NOT NULL DEFAULT 'sftp'
39
        }
40
        );
41
        say_success( $out, "Added 'local' transport type to file_transports table" );
42
43
        # Migrate existing EDI transport configurations to file_transports
44
        my $migration_count  = 0;
45
        my $edi_accounts_sth = $dbh->prepare(
46
            q{
47
            SELECT id, description, host, username, password, upload_port, download_port,
48
                   upload_directory, download_directory, transport
49
            FROM vendor_edi_accounts
50
            WHERE file_transport_id IS NULL
51
              AND host IS NOT NULL
52
              AND host != ''
53
        }
54
        );
55
        $edi_accounts_sth->execute();
56
57
        my $insert_transport_sth = $dbh->prepare(
58
            q{
59
            INSERT INTO file_transports (name, host, port, transport, user_name, password,
60
                                       upload_directory, download_directory, auth_mode, passive, debug)
61
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'password', 1, 0)
62
        }
63
        );
64
65
        my $update_edi_account_sth = $dbh->prepare(
66
            q{
67
            UPDATE vendor_edi_accounts SET file_transport_id = ? WHERE id = ?
68
        }
69
        );
70
71
        while ( my $row = $edi_accounts_sth->fetchrow_hashref ) {
72
73
            # Determine appropriate port (default to SFTP=22, FTP=21, Local=NULL)
74
            my $port = $row->{upload_port} || $row->{download_port};
75
            unless ($port) {
76
                if ( uc( $row->{transport} ) eq 'SFTP' ) {
77
                    $port = 22;
78
                } elsif ( uc( $row->{transport} ) eq 'FILE' ) {
79
                    $port = undef;    # Local transport doesn't use ports
80
                } else {
81
                    $port = 21;       # FTP default
82
                }
83
            }
84
85
            # Map transport type (normalize case and handle FILE -> local)
86
            my $transport_type = lc( $row->{transport} );
87
            $transport_type = 'local' if $transport_type eq 'file';
88
            $transport_type = 'ftp' unless $transport_type =~ /^(sftp|local)$/;
89
90
            # Create transport name from EDI account description
91
            my $transport_name = sprintf( "EDI Transport for %s", $row->{description} );
92
93
            # Handle host for local transport
94
            my $host = $row->{host};
95
            $host = 'localhost' if $transport_type eq 'local' && !$host;
96
97
            # Insert new file transport
98
            $insert_transport_sth->execute(
99
                $transport_name,
100
                $host,
101
                $port,
102
                $transport_type,
103
                $row->{username},
104
                $row->{password},    # Password is already encrypted in EDI accounts
105
                $row->{upload_directory},
106
                $row->{download_directory}
107
            );
108
109
            my $transport_id = $dbh->last_insert_id( undef, undef, 'file_transports', 'file_transport_id' );
110
111
            # Update EDI account to reference the new transport
112
            $update_edi_account_sth->execute( $transport_id, $row->{id} );
113
114
            $migration_count++;
115
        }
116
117
        if ( $migration_count > 0 ) {
118
            say_success(
119
                $out,
120
                "Successfully migrated $migration_count EDI transport configurations to file_transports"
121
            );
122
        } else {
123
            say_info( $out, "No EDI transport configurations found to migrate" );
124
        }
125
126
        # Drop the old transport-related columns from vendor_edi_accounts
127
        my @columns_to_drop =
128
            qw(host username password upload_port download_port upload_directory download_directory transport);
129
130
        for my $column (@columns_to_drop) {
131
            if ( column_exists( 'vendor_edi_accounts', $column ) ) {
132
                $dbh->do("ALTER TABLE vendor_edi_accounts DROP COLUMN $column");
133
                say_success( $out, "Dropped column '$column' from vendor_edi_accounts table" );
134
            }
135
        }
136
137
        say_success( $out, "EDI transport migration completed successfully" );
138
    },
139
};
(-)a/installer/data/mysql/kohastructure.sql (-11 / +5 lines)
Lines 6003-6009 CREATE TABLE `file_transports` ( Link Here
6003
  `name` varchar(80) NOT NULL,
6003
  `name` varchar(80) NOT NULL,
6004
  `host` varchar(80) NOT NULL DEFAULT 'localhost',
6004
  `host` varchar(80) NOT NULL DEFAULT 'localhost',
6005
  `port` int(11) NOT NULL DEFAULT 22,
6005
  `port` int(11) NOT NULL DEFAULT 22,
6006
  `transport` enum('ftp','sftp') NOT NULL DEFAULT 'sftp',
6006
  `transport` enum('ftp','sftp','local') NOT NULL DEFAULT 'sftp',
6007
  `passive` tinyint(1) NOT NULL DEFAULT 1,
6007
  `passive` tinyint(1) NOT NULL DEFAULT 1,
6008
  `user_name` varchar(80) DEFAULT NULL,
6008
  `user_name` varchar(80) DEFAULT NULL,
6009
  `password` mediumtext DEFAULT NULL,
6009
  `password` mediumtext DEFAULT NULL,
Lines 6699-6717 DROP TABLE IF EXISTS `vendor_edi_accounts`; Link Here
6699
CREATE TABLE `vendor_edi_accounts` (
6699
CREATE TABLE `vendor_edi_accounts` (
6700
  `id` int(11) NOT NULL AUTO_INCREMENT,
6700
  `id` int(11) NOT NULL AUTO_INCREMENT,
6701
  `description` mediumtext NOT NULL,
6701
  `description` mediumtext NOT NULL,
6702
  `host` varchar(40) DEFAULT NULL,
6703
  `username` varchar(40) DEFAULT NULL,
6704
  `password` mediumtext DEFAULT NULL,
6705
  `upload_port` int(11) DEFAULT NULL,
6706
  `download_port` int(11) DEFAULT NULL,
6707
  `last_activity` date DEFAULT NULL,
6702
  `last_activity` date DEFAULT NULL,
6708
  `vendor_id` int(11) DEFAULT NULL,
6703
  `vendor_id` int(11) DEFAULT NULL,
6709
  `download_directory` mediumtext DEFAULT NULL,
6710
  `upload_directory` mediumtext DEFAULT NULL,
6711
  `san` varchar(20) DEFAULT NULL,
6704
  `san` varchar(20) DEFAULT NULL,
6712
  `standard` varchar(3) DEFAULT 'EUR',
6705
  `standard` varchar(3) DEFAULT 'EUR',
6713
  `id_code_qualifier` varchar(3) DEFAULT '14',
6706
  `id_code_qualifier` varchar(3) DEFAULT '14',
6714
  `transport` varchar(6) DEFAULT 'FTP',
6715
  `quotes_enabled` tinyint(1) NOT NULL DEFAULT 0,
6707
  `quotes_enabled` tinyint(1) NOT NULL DEFAULT 0,
6716
  `invoices_enabled` tinyint(1) NOT NULL DEFAULT 0,
6708
  `invoices_enabled` tinyint(1) NOT NULL DEFAULT 0,
6717
  `orders_enabled` tinyint(1) NOT NULL DEFAULT 0,
6709
  `orders_enabled` tinyint(1) NOT NULL DEFAULT 0,
Lines 6719-6729 CREATE TABLE `vendor_edi_accounts` ( Link Here
6719
  `auto_orders` tinyint(1) NOT NULL DEFAULT 0,
6711
  `auto_orders` tinyint(1) NOT NULL DEFAULT 0,
6720
  `shipment_budget` int(11) DEFAULT NULL,
6712
  `shipment_budget` int(11) DEFAULT NULL,
6721
  `plugin` varchar(256) NOT NULL DEFAULT '',
6713
  `plugin` varchar(256) NOT NULL DEFAULT '',
6714
  `file_transport_id` int(11) DEFAULT NULL,
6722
  PRIMARY KEY (`id`),
6715
  PRIMARY KEY (`id`),
6723
  KEY `vendorid` (`vendor_id`),
6716
  KEY `vendorid` (`vendor_id`),
6724
  KEY `shipmentbudget` (`shipment_budget`),
6717
  KEY `shipmentbudget` (`shipment_budget`),
6718
  KEY `vendor_edi_accounts_file_transport_id` (`file_transport_id`),
6725
  CONSTRAINT `vfk_shipment_budget` FOREIGN KEY (`shipment_budget`) REFERENCES `aqbudgets` (`budget_id`),
6719
  CONSTRAINT `vfk_shipment_budget` FOREIGN KEY (`shipment_budget`) REFERENCES `aqbudgets` (`budget_id`),
6726
  CONSTRAINT `vfk_vendor_id` FOREIGN KEY (`vendor_id`) REFERENCES `aqbooksellers` (`id`)
6720
  CONSTRAINT `vfk_vendor_id` FOREIGN KEY (`vendor_id`) REFERENCES `aqbooksellers` (`id`),
6721
  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
6727
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
6722
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
6728
/*!40101 SET character_set_client = @saved_cs_client */;
6723
/*!40101 SET character_set_client = @saved_cs_client */;
6729
6724
6730
- 

Return to bug 38489