On Sun, Dec 06, 2009 at 05:21:52PM +0100, Emmanuel Lacour wrote: > On Sun, Dec 06, 2009 at 05:43:05PM +1000, David Hobley wrote: > > > > We have been trying to do this using this page, but we have never > > managed to get the binary attachments across successfully. If you have > > any luck with this, we are definitely interested in hearing about it. > > > > > attached the version I used to migrate our DB without any loss ... test > it ;) >
attached to _this_ message ...
#!/usr/bin/perl -w # Warning, quickly modified version to suit my needs # Emmanuel Lacour <[email protected]> ############################################################################ # # # mysql2pg.pl - Helps migration of RT database from MySQL to PostgreSQL # # Copyright (C) 2007 - Gilmar Santos Jr # # # # This program is free software; you can redistribute it and/or modify # # it under the terms of the GNU General Public License as published by # # the Free Software Foundation; either version 2 of the License. # # # # This program is distributed in the hope that it will be useful, # # but WITHOUT ANY WARRANTY; without even the implied warranty of # # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # # GNU General Public License for more details. # # # # You should have received a copy of the GNU General Public License # # along with this program; if not, write to the Free Software # # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, # # MA 02110-1301, USA # # # # Gilmar Santos Jr - [email protected] # # # ############################################################################ use strict; use DBI; use Unicode::MapUTF8 qw(to_utf8); use MIME::QuotedPrint; use Encode; use utf8; Encode::Guess->set_suspects( qw(iso8859-1) ); ############# Adjust usernames and passwords!!!!!!! ############# my $mysql = DBI->connect("DBI:mysql:database=rt", "rt", "xxxxxxxx") or die $DBI::errstr; $mysql->do('SET NAMES utf8;'); my $pg = DBI->connect("DBI:Pg:dbname=rt;host=localhost", "rt", "xxxxxxxx", {AutoCommit => 0}) or die $DBI::errstr; my @tables = ( 'FM_Classes', 'FM_Articles', 'FM_Topics', 'FM_ObjectTopics', 'Attachments', 'CustomFieldValues', 'ObjectCustomFieldValues', 'ObjectCustomFields', 'CustomFields', 'CachedGroupMembers', 'GroupMembers', 'Attributes', 'ACL', 'Links', 'Principals', 'Tickets', 'Transactions', 'Queues', 'ScripConditions', 'Groups', 'Scrips', 'Users', 'ScripActions', 'Templates', ); my $bad_data; $| = 1; foreach my $table (@tables) { print "\rConverting $table... "; my $nr_regs = $mysql->selectall_arrayref( qq|SELECT count(*) FROM $table;|)->[0][0]; print "($nr_regs lines)\n"; my $data = $mysql->selectall_arrayref( qq|SELECT * FROM $table LIMIT 10000 OFFSET 0|, { Slice => {} } ) or die $mysql->errstr; $pg->do(qq|DELETE FROM \L$table\E|) or die $pg->errstr; my @keys = keys %{$data->[0]}; my $pgh = $pg->prepare( "INSERT INTO \L$table\E(" . (join(", ", @keys)) . ") VALUES (" . (join(", ", map { "?" } @keys)) . ")" ) or die $pg->errstr; foreach my $reg (0 .. ($nr_regs - 1 > 9999 ? 9999 : $nr_regs - 1) ) { if ( $table eq "Attachments" ) { if ( ( $data->[$reg]{"Content"} ) && ( $data->[$reg]{"Content"} =~ /\x00/ ) ) { $data->[$reg]{"ContentEncoding"} = "base64"; Encode::_utf8_off($data->[$reg]{"Content"}); $data->[$reg]{"Content"} = MIME::Base64::encode_base64($data->[$reg]{"Content"}); } elsif ( ( $data->[$reg]{"ContentType"} ) && ( $data->[$reg]{"ContentType"} !~ /text\/plain/gi ) && ( $data->[$reg]{"Content"} ) && ( !Encode::is_utf8( $data->[$reg]{"Content"}, 1 ) ) ) { $data->[$reg]{"ContentEncoding"} = "quoted-printable"; Encode::_utf8_off($data->[$reg]{"Content"}); $data->[$reg]{'Content'} = MIME::QuotedPrint::encode($data->[$reg]{'Content'}); } elsif ( ( $data->[$reg]{"ContentType"} ) && ( $data->[$reg]{"ContentType"} eq 'text/plain' ) && ( $data->[$reg]{"Content"} ) ) { $bad_data = $data->[$reg]{'Content'}; if ( ! eval { decode( "UTF-8", $bad_data, Encode::FB_CROAK); } ) { warn "Bad UTF-8: ".$data->[$reg]{'id'}."\n"; $data->[$reg]{'Content'} = encode( "UTF-8", $data->[$reg]{'Content'} ); } } } if ( $table eq 'Transactions' ) { if ( ! $data->[$reg]{'OldReference'} || $data->[$reg]{'OldReference'} eq '' ) { $data->[$reg]{'OldReference'} = undef; } if ( ! $data->[$reg]{'NewReference'} || $data->[$reg]{'NewReference'} eq '' ) { $data->[$reg]{'NewReference'} = undef; } } if ( $table eq 'Tickets' ) { if ( ! $data->[$reg]{'Told'} || $data->[$reg]{'Told'} eq '' ) { $data->[$reg]{'Told'} = undef; } } my @converted = @{$data->[$reg...@keys}; if ( ! $pgh->execute(@converted) ) { use Data::Dumper; print STDERR Dumper($data->[$reg])."\n"; my $enc = Encode::Guess->guess($data->[$reg]{"Content"}); print STDERR $enc->name.$pg->err." - ".$pg->errstr."\n"; exit (1); } } for (my $i = 10000; $i < $nr_regs; $i += 10000) { print "\r ", sprintf('%10.2f', $i * 100 / $nr_regs), "\% done"; $data = $mysql->selectall_arrayref( qq|SELECT * FROM $table LIMIT 10000 OFFSET $i|, { Slice => {} } ) or die $mysql->errstr; foreach my $reg (0 .. ($nr_regs - $i > 9999 ? 9999 : $nr_regs - $i - 1) ) { if ( $table eq "Attachments" ) { if ( ( $data->[$reg]{"Content"} ) && ( $data->[$reg]{"Content"} =~ /\x00/ ) ) { $data->[$reg]{"ContentEncoding"} = "base64"; Encode::_utf8_off($data->[$reg]{"Content"}); $data->[$reg]{"Content"} = MIME::Base64::encode_base64($data->[$reg]{"Content"}); } elsif ( ( $data->[$reg]{"ContentType"} ) && ( $data->[$reg]{"ContentType"} !~ /text\/plain/gi ) && ( $data->[$reg]{"Content"} ) && ( !Encode::is_utf8( $data->[$reg]{"Content"}, 1 ) ) ) { $data->[$reg]{"ContentEncoding"} = "quoted-printable"; Encode::_utf8_off($data->[$reg]{"Content"}); $data->[$reg]{'Content'} = MIME::QuotedPrint::encode($data->[$reg]{'Content'}); } elsif ( ( $data->[$reg]{"ContentType"} ) && ( $data->[$reg]{"ContentType"} eq 'text/plain' ) && ( $data->[$reg]{"Content"} ) ) { $bad_data = $data->[$reg]{'Content'}; if ( ! eval { decode( "UTF-8", $bad_data, Encode::FB_CROAK); } ) { warn "Bad UTF-8: ".$data->[$reg]{'id'}."\n"; $data->[$reg]{'Content'} = encode( "UTF-8", $data->[$reg]{'Content'} ); } } } if ( $table eq 'Transactions' ) { if ( ! $data->[$reg]{'OldReference'} || $data->[$reg]{'OldReference'} eq '' ) { $data->[$reg]{'OldReference'} = undef; } if ( ! $data->[$reg]{'NewReference'} || $data->[$reg]{'NewReference'} eq '' ) { $data->[$reg]{'NewReference'} = undef; } } if ( $table eq 'Tickets' ) { if ( ! $data->[$reg]{'Told'} || $data->[$reg]{'Told'} eq '' ) { $data->[$reg]{'Told'} = undef; } } my @converted = @{$data->[$reg...@keys}; if ( ! $pgh->execute(@converted) ) { use Data::Dumper; print Dumper($data->[$reg])."\n"; print STDERR Encode::Guess->guess($data->[$reg]{"Content"}).$pg->err." - ".$pg->errstr; exit (1); } } } my $seq_name = $table.'_id_seq'; $seq_name =~ s/eq$// if ( $table =~ /^(ObjectCustomFieldValues|ObjectCustomFields)$/i ); $nr_regs = $mysql->selectall_arrayref( qq|SELECT max(id) + 1 FROM $table|)->[0][0]; $nr_regs = 1 unless ($nr_regs); $pg->do(qq|ALTER SEQUENCE "\L$seq_name\E" RESTART WITH $nr_regs|) or die $pg->errstr; $pg->commit(); } $mysql->disconnect; $pg->disconnect;
_______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [email protected] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
