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

(-)a/installer/data/mysql/atomicupdate/bug_38489.pl (-108 / +129 lines)
Lines 9-139 return { Link Here
9
        my ( $dbh, $out ) = @$args{qw(dbh out)};
9
        my ( $dbh, $out ) = @$args{qw(dbh out)};
10
10
11
        # Check if file_transports table exists (should exist from Bug 39190)
11
        # Check if file_transports table exists (should exist from Bug 39190)
12
        my $sth = $dbh->prepare("SHOW TABLES LIKE 'file_transports'");
12
        if ( TableExists('file_transports') ) {
13
        $sth->execute();
13
14
        if ( !$sth->fetchrow_array ) {
14
            # Add file_transport_id column to vendor_edi_accounts
15
            say_failure( $out, "file_transports table not found. Please ensure Bug 39190 is applied first." );
15
            if ( !column_exists( 'vendor_edi_accounts', 'file_transport_id' ) ) {
16
            return;
16
                $dbh->do(
17
        }
17
                    q{
18
                        ALTER TABLE vendor_edi_accounts
19
                        ADD COLUMN file_transport_id int(11) DEFAULT NULL AFTER plugin,
20
                        ADD KEY `vendor_edi_accounts_file_transport_id` (`file_transport_id`),
21
                        ADD CONSTRAINT `vendor_edi_accounts_ibfk_file_transport`
22
                            FOREIGN KEY (`file_transport_id`) REFERENCES `file_transports` (`file_transport_id`)
23
                            ON DELETE SET NULL ON UPDATE CASCADE
24
                    }
25
                );
26
                say_success(
27
                    $out,
28
                    "Added file_transport_id column to vendor_edi_accounts table"
29
                );
30
            }
18
31
19
        # Add file_transport_id column to vendor_edi_accounts
32
            # Add 'local' to the transport enum in file_transports table
20
        if ( !column_exists( 'vendor_edi_accounts', 'file_transport_id' ) ) {
21
            $dbh->do(
33
            $dbh->do(
22
                q{
34
                q{
23
                ALTER TABLE vendor_edi_accounts
35
                    ALTER TABLE file_transports
24
                ADD COLUMN file_transport_id int(11) DEFAULT NULL AFTER plugin,
36
                    MODIFY COLUMN transport ENUM('ftp','sftp','local') NOT NULL DEFAULT 'sftp'
25
                ADD KEY `vendor_edi_accounts_file_transport_id` (`file_transport_id`),
37
                }
26
                ADD CONSTRAINT `vendor_edi_accounts_ibfk_file_transport`
38
            );
27
                    FOREIGN KEY (`file_transport_id`) REFERENCES `file_transports` (`file_transport_id`)
39
            say_success(
28
                    ON DELETE SET NULL ON UPDATE CASCADE
40
                $out,
29
            }
41
                "Added 'local' transport type to file_transports table"
30
            );
42
            );
31
            say_success( $out, "Added file_transport_id column to vendor_edi_accounts table" );
32
        }
33
43
34
        # Add 'local' to the transport enum in file_transports table
44
            # Migrate existing EDI transport configurations to file_transports
35
        $dbh->do(
45
            my $migration_count  = 0;
36
            q{
46
            my $edi_accounts_sth = $dbh->prepare(
37
            ALTER TABLE file_transports
47
                q{
38
            MODIFY COLUMN transport ENUM('ftp','sftp','local') NOT NULL DEFAULT 'sftp'
48
                    SELECT id, description, host, username, password, upload_port, download_port,
39
        }
49
                           upload_directory, download_directory, transport
40
        );
50
                    FROM vendor_edi_accounts
41
        say_success( $out, "Added 'local' transport type to file_transports table" );
51
                    WHERE file_transport_id IS NULL
42
52
                      AND host IS NOT NULL
43
        # Migrate existing EDI transport configurations to file_transports
53
                      AND host != ''
44
        my $migration_count  = 0;
54
                }
45
        my $edi_accounts_sth = $dbh->prepare(
55
            );
46
            q{
56
            $edi_accounts_sth->execute();
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
57
65
        my $update_edi_account_sth = $dbh->prepare(
58
            my $insert_transport_sth = $dbh->prepare(
66
            q{
59
                q{
67
            UPDATE vendor_edi_accounts SET file_transport_id = ? WHERE id = ?
60
                    INSERT INTO file_transports (name, host, port, transport, user_name, password,
68
        }
61
                                               upload_directory, download_directory, auth_mode, passive, debug)
69
        );
62
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'password', 1, 0)
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
                }
63
                }
83
            }
64
            );
84
65
85
            # Map transport type (normalize case and handle FILE -> local)
66
            my $update_edi_account_sth = $dbh->prepare(
86
            my $transport_type = lc( $row->{transport} );
67
                q{
87
            $transport_type = 'local' if $transport_type eq 'file';
68
                    UPDATE vendor_edi_accounts SET file_transport_id = ? WHERE id = ?
88
            $transport_type = 'ftp' unless $transport_type =~ /^(sftp|local)$/;
69
                }
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
            );
70
            );
108
71
109
            my $transport_id = $dbh->last_insert_id( undef, undef, 'file_transports', 'file_transport_id' );
72
            while ( my $row = $edi_accounts_sth->fetchrow_hashref ) {
73
74
                # Determine appropriate port (default to SFTP=22, FTP=21, Local=NULL)
75
                my $port = $row->{upload_port} || $row->{download_port};
76
                unless ($port) {
77
                    if ( uc( $row->{transport} ) eq 'SFTP' ) {
78
                        $port = 22;
79
                    } elsif ( uc( $row->{transport} ) eq 'FILE' ) {
80
                        $port = undef;    # Local transport doesn't use ports
81
                    } else {
82
                        $port = 21;       # FTP default
83
                    }
84
                }
110
85
111
            # Update EDI account to reference the new transport
86
                # Map transport type (normalize case and handle FILE -> local)
112
            $update_edi_account_sth->execute( $transport_id, $row->{id} );
87
                my $transport_type = lc( $row->{transport} );
88
                $transport_type = 'local' if $transport_type eq 'file';
89
                $transport_type = 'ftp'
90
                    unless $transport_type =~ /^(sftp|local)$/;
91
92
                # Create transport name from EDI account description
93
                my $transport_name = sprintf( "EDI Transport for %s", $row->{description} );
94
95
                # Handle host for local transport
96
                my $host = $row->{host};
97
                $host = 'localhost' if $transport_type eq 'local' && !$host;
98
99
                # Insert new file transport
100
                $insert_transport_sth->execute(
101
                    $transport_name,
102
                    $host,
103
                    $port,
104
                    $transport_type,
105
                    $row->{username},
106
                    $row->{password},    # Password is already encrypted in EDI accounts
107
                    $row->{upload_directory},
108
                    $row->{download_directory}
109
                );
110
111
                my $transport_id = $dbh->last_insert_id(
112
                    undef, undef, 'file_transports',
113
                    'file_transport_id'
114
                );
115
116
                # Update EDI account to reference the new transport
117
                $update_edi_account_sth->execute( $transport_id, $row->{id} );
118
119
                $migration_count++;
120
            }
113
121
114
            $migration_count++;
122
            if ( $migration_count > 0 ) {
115
        }
123
                say_success(
124
                    $out,
125
                    "Successfully migrated $migration_count EDI transport configurations to file_transports"
126
                );
127
            } else {
128
                say_info(
129
                    $out,
130
                    "No EDI transport configurations found to migrate"
131
                );
132
            }
133
134
            # Drop the old transport-related columns from vendor_edi_accounts
135
            my @columns_to_drop =
136
                qw(host username password upload_port download_port upload_directory download_directory transport);
137
138
            for my $column (@columns_to_drop) {
139
                if ( column_exists( 'vendor_edi_accounts', $column ) ) {
140
                    $dbh->do("ALTER TABLE vendor_edi_accounts DROP COLUMN $column");
141
                    say_success(
142
                        $out,
143
                        "Dropped column '$column' from vendor_edi_accounts table"
144
                    );
145
                }
146
            }
116
147
117
        if ( $migration_count > 0 ) {
118
            say_success(
148
            say_success(
119
                $out,
149
                $out,
120
                "Successfully migrated $migration_count EDI transport configurations to file_transports"
150
                "EDI transport migration completed successfully"
121
            );
151
            );
122
        } else {
152
        } else {
123
            say_info( $out, "No EDI transport configurations found to migrate" );
153
            say_failure(
124
        }
154
                $out,
125
155
                "file_transports table not found. Please ensure Bug 39190 is applied first."
126
        # Drop the old transport-related columns from vendor_edi_accounts
156
            );
127
        my @columns_to_drop =
157
            return;
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
        }
158
        }
136
159
137
        say_success( $out, "EDI transport migration completed successfully" );
138
    },
160
    },
139
};
161
};
140
- 

Return to bug 38489