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

(-)a/installer/data/mysql/db_revs/250600022.pl (-73 / +79 lines)
Lines 41-50 return { Link Here
41
                "Added 'local' transport type to file_transports table"
41
                "Added 'local' transport type to file_transports table"
42
            );
42
            );
43
43
44
            # Migrate then drop the old transport-related columns from vendor_edi_accounts
45
            my @columns_to_drop =
46
                qw(host username password upload_port download_port upload_directory download_directory transport);
47
48
            my $all_exist = @columns_to_drop == grep { column_exists( 'vendor_edi_accounts', "$_" ) } @columns_to_drop;
49
44
            # Migrate existing EDI transport configurations to file_transports
50
            # Migrate existing EDI transport configurations to file_transports
45
            my $migration_count  = 0;
51
            if ($all_exist) {
46
            my $edi_accounts_sth = $dbh->prepare(
52
                my $migration_count  = 0;
47
                q{
53
                my $edi_accounts_sth = $dbh->prepare(
54
                    q{
48
                    SELECT id, description, host, username, password, upload_port, download_port,
55
                    SELECT id, description, host, username, password, upload_port, download_port,
49
                           upload_directory, download_directory, transport
56
                           upload_directory, download_directory, transport
50
                    FROM vendor_edi_accounts
57
                    FROM vendor_edi_accounts
Lines 52-142 return { Link Here
52
                      AND host IS NOT NULL
59
                      AND host IS NOT NULL
53
                      AND host != ''
60
                      AND host != ''
54
                }
61
                }
55
            );
62
                );
56
            $edi_accounts_sth->execute();
63
                $edi_accounts_sth->execute();
57
64
58
            my $insert_transport_sth = $dbh->prepare(
65
                my $insert_transport_sth = $dbh->prepare(
59
                q{
66
                    q{
60
                    INSERT INTO file_transports (name, host, port, transport, user_name, password,
67
                    INSERT INTO file_transports (name, host, port, transport, user_name, password,
61
                                               upload_directory, download_directory, auth_mode, passive, debug)
68
                                               upload_directory, download_directory, auth_mode, passive, debug)
62
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'password', 1, 0)
69
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'password', 1, 0)
63
                }
70
                }
64
            );
71
                );
65
72
66
            my $update_edi_account_sth = $dbh->prepare(
73
                my $update_edi_account_sth = $dbh->prepare(
67
                q{
74
                    q{
68
                    UPDATE vendor_edi_accounts SET file_transport_id = ? WHERE id = ?
75
                    UPDATE vendor_edi_accounts SET file_transport_id = ? WHERE id = ?
69
                }
76
                }
70
            );
77
                );
71
78
72
            while ( my $row = $edi_accounts_sth->fetchrow_hashref ) {
79
                while ( my $row = $edi_accounts_sth->fetchrow_hashref ) {
73
80
74
                # Determine appropriate port (default to SFTP=22, FTP=21, Local=NULL)
81
                    # Determine appropriate port
75
                my $port = $row->{upload_port} || $row->{download_port};
82
                    # (default to SFTP=22, FTP=21, Local=NULL)
76
                unless ($port) {
83
                    my $port = $row->{upload_port} || $row->{download_port};
77
                    if ( uc( $row->{transport} ) eq 'SFTP' ) {
84
                    unless ($port) {
78
                        $port = 22;
85
                        if ( uc( $row->{transport} ) eq 'SFTP' ) {
79
                    } elsif ( uc( $row->{transport} ) eq 'FILE' ) {
86
                            $port = 22;
80
                        $port = undef;    # Local transport doesn't use ports
87
                        } elsif ( uc( $row->{transport} ) eq 'FILE' ) {
81
                    } else {
88
                            $port = undef;    # Local transport doesn't use ports
82
                        $port = 21;       # FTP default
89
                        } else {
90
                            $port = 21;       # FTP default
91
                        }
83
                    }
92
                    }
84
                }
85
93
86
                # Map transport type (normalize case and handle FILE -> local)
94
                    # Map transport type
87
                my $transport_type = lc( $row->{transport} );
95
                    # (normalize case and handle FILE -> local)
88
                $transport_type = 'local' if $transport_type eq 'file';
96
                    my $transport_type = lc( $row->{transport} );
89
                $transport_type = 'ftp'
97
                    $transport_type = 'local' if $transport_type eq 'file';
90
                    unless $transport_type =~ /^(sftp|local)$/;
98
                    $transport_type = 'ftp'
91
99
                        unless $transport_type =~ /^(sftp|local)$/;
92
                # Create transport name from EDI account description
100
93
                my $transport_name = sprintf( "EDI Transport for %s", $row->{description} );
101
                    # Create transport name from EDI account description
94
102
                    my $transport_name = sprintf( "EDI Transport for %s", $row->{description} );
95
                # Handle host for local transport
103
96
                my $host = $row->{host};
104
                    # Handle host for local transport
97
                $host = 'localhost' if $transport_type eq 'local' && !$host;
105
                    my $host = $row->{host};
98
106
                    $host = 'localhost' if $transport_type eq 'local' && !$host;
99
                # Insert new file transport
107
100
                $insert_transport_sth->execute(
108
                    # Insert new file transport
101
                    $transport_name,
109
                    $insert_transport_sth->execute(
102
                    $host,
110
                        $transport_name,
103
                    $port,
111
                        $host,
104
                    $transport_type,
112
                        $port,
105
                    $row->{username},
113
                        $transport_type,
106
                    $row->{password},    # Password is already encrypted in EDI accounts
114
                        $row->{username},
107
                    $row->{upload_directory},
115
                        $row->{password},    # Password is already encrypted in EDI accounts
108
                    $row->{download_directory}
116
                        $row->{upload_directory},
109
                );
117
                        $row->{download_directory}
110
118
                    );
111
                my $transport_id = $dbh->last_insert_id(
112
                    undef, undef, 'file_transports',
113
                    'file_transport_id'
114
                );
115
119
116
                # Update EDI account to reference the new transport
120
                    my $transport_id = $dbh->last_insert_id(
117
                $update_edi_account_sth->execute( $transport_id, $row->{id} );
121
                        undef, undef, 'file_transports',
122
                        'file_transport_id'
123
                    );
118
124
119
                $migration_count++;
125
                    # Update EDI account to reference the new transport
120
            }
126
                    $update_edi_account_sth->execute(
127
                        $transport_id,
128
                        $row->{id}
129
                    );
121
130
122
            if ( $migration_count > 0 ) {
131
                    $migration_count++;
123
                say_success(
132
                }
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
133
134
            # Drop the old transport-related columns from vendor_edi_accounts
134
                if ( $migration_count > 0 ) {
135
            my @columns_to_drop =
135
                    say_success(
136
                qw(host username password upload_port download_port upload_directory download_directory transport);
136
                        $out,
137
                        "Successfully migrated $migration_count EDI transport configurations to file_transports"
138
                    );
139
                } else {
140
                    say_info(
141
                        $out,
142
                        "No EDI transport configurations found to migrate"
143
                    );
144
                }
137
145
138
            for my $column (@columns_to_drop) {
146
                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");
147
                    $dbh->do("ALTER TABLE vendor_edi_accounts DROP COLUMN $column");
141
                    say_success(
148
                    say_success(
142
                        $out,
149
                        $out,
143
- 

Return to bug 38489