#!/usr/bin/perl -w

############################################################################
#                                                                          #
#  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;


############# Adjust usernames and passwords!!!!!!! #############
my $mysql = DBI->connect("DBI:mysql:database=rtdb", "<user>", "<pass>")
	or die $DBI::errstr;
my $pg    = DBI->connect("DBI:Pg:dbname=rtdb;host=localhost", 
                                 "<user>", "<pass>", {AutoCommit => 0})
	or die $DBI::errstr;

my @tables = (
	'ACL',
	'Attachments',
	'Attributes',
	'CachedGroupMembers',
	'CustomFieldValues',
	'CustomFields',
	'GroupMembers',
	'Groups',
	'Links',
	'ObjectCustomFieldValues',
	'ObjectCustomFields',
	'Principals',
	'Queues',
	'ScripActions',
	'ScripConditions',
	'Scrips',
	'Templates',
	'Tickets',
	'Transactions',
	'Users',
);

$| = 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) ) {
		$pgh->execute(@{$data->[$reg]}{@keys}) or die $pg->errstr;
	}
	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) ) {
			$pgh->execute(@{$data->[$reg]}{@keys}) or die $pg->errstr;
		}
	}
	$nr_regs = $mysql->selectall_arrayref(
	           	   qq|SELECT max(id) + 1 FROM $table|)->[0][0];
	$pg->do(qq|ALTER SEQUENCE "\L$table\E_id_seq" RESTART WITH $nr_regs|) or die $pg->errstr;
	$pg->commit();
}

$mysql->disconnect;
$pg->disconnect;