#!/usr/bin/perl -w

# Warning, quickly modified version to suit my needs
# Emmanuel Lacour <elacour@easter-eggs.com>

############################################################################
#                                                                          #
#  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 - gilmarsantosjr@safernet.org.br                       #
#                                                                          #
############################################################################

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;
