## Usage: 
## perl -s galaxy-My2Pg.pl <parameters>
## The parameters are mandatory. Here is the list:
## -odb=<original database name>
## -odbhost=<original database host>
## -odbuser=<original database user>
## -odbpwd=<original database password for user>
## -tdb=<target database name>
## -tdbhost=<target database host>
## -tdbuser=<target database user>
## -tdbpwd=<target database password for user>

#use strict; # Commented due to the bare pg_types in the bind_param() lines
use warnings;
use DBI qw(:sql_types);
use DBD::Pg qw(:pg_types);
use utf8;
use Encode;

checkparam($odb, $odbhost, $odbuser, $odbpwd, $tdb, $tdbhost, $tdbuser, $tdbpwd);

# Connecting to both databases

my $dbhm = DBI->connect("DBI:mysql:database=$odb;host=$odbhost","$odbuser","$odbpwd",{'RaiseError' => 1});
my $dbhp = DBI->connect("DBI:Pg:dbname=$tdb;host=$tdbhost", "$tdbuser", "$tdbpwd", {AutoCommit => 1, RaiseError => 1, PrintError => 1});

my $reqp;
my $reqm;

# Getting the tables list from the new database

my @tableslist;
$reqp = $dbhp->prepare("SELECT * FROM pg_tables WHERE tableowner = '$tdbuser';");
$reqp->execute;

while (my $refp = $reqp->fetchrow_hashref()) {
	push(@tableslist, $refp->{'tablename'});
}

# Now let's clean and fill the new database's tables:

my %fieldslist;
my %entry;
my @identifiers;

foreach my $table (@tableslist) {

	# Fetching the fields names of the current table
	$reqp = $dbhp->prepare("SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = 'public' AND table_name = '$table';");
	$reqp->execute;
	
	while (my $refp = $reqp->fetchrow_hashref()) {
		$fieldslist{$refp->{'column_name'}} = $refp->{'data_type'};
		push(@identifiers, $refp->{'column_name'}); # just to keep them in the right order
	}

	# Disabling the foreing keys constraints etc
	$reqp = $dbhp->prepare("ALTER TABLE $table DISABLE TRIGGER ALL;");
	$reqp->execute;
	
	# Cleaning the current table
	$reqp = $dbhp->prepare("DELETE FROM $table;");
	$reqp->execute;
	
	# Retrieving the entries for the given table
	$reqm = $dbhm->prepare("SELECT * FROM $table;");
	$reqm->execute;
	
	while (my $refm = $reqm->fetchrow_hashref()) {
		# Associating the fields names and values for each entry
		while ((my $field, my $type) = each %fieldslist) {
			$entry{$field} = $refm->{$field};
		}
		# Building the query
		my $values = ""; my @parameters;
		foreach my $field (@identifiers) {
			my $type = $fieldslist{$field};
			my $value = "?";
			if (!defined($entry{$field})) { $value = "NULL"; }
			elsif ($type eq "boolean") {
				if ($entry{$field} == "0") { $value = "false"; }
				else { $value = "true"; }
			}
			else { push(@parameters, [$entry{$field}, $type]); }
			$values .= "$value, ";
		}
		$values = substr($values, 0, length($values) - 2);
		my $textreqp = "INSERT INTO $table VALUES (".$values.");";
		print $textreqp."\n";
		$reqp = $dbhp->prepare($textreqp);
		my $count = 0;
		foreach my $element (@parameters) {
			# For each parameter which needs a placeholder (not boolean or null)
			# we bind the value to the appropriate placeholder
			$count++;
			my $param = @$element[0]; my $type = @$element[1];
			# managing binary data:
			if ($type eq "bytea") { $reqp->bind_param($count, $param, { pg_type => DBD::Pg::PG_BYTEA }); }
			# managing non-utf8 characters
			elsif ($type eq "character varying") { 
				my $correctedparam = encode("UTF-8", $param);
				$reqp->bind_param($count, $correctedparam, { pg_type => DBD::Pg::PG_VARCHAR }); 
			}
			# managing timestamps:
			elsif ($type eq "timestamp without time zone") { $reqp->bind_param($count, $param, { pg_type => DBD::Pg::PG_TIMESTAMP }); }
			else { $reqp->bind_param($count, $param); }
		}
		$reqp->execute;
	}
	
	# Resetting the sequence (if any) with the last inserted id        
	if (defined($refm->{'id'})) {
		$reqp = $dbhp->prepare("SELECT setval( '".$table."_id_seq', (SELECT MAX(id) FROM $table) +1 );");
		$reqp->execute;
	}
	
	# Enabling the foreing keys constraints back        
	$reqp = $dbhp->prepare("ALTER TABLE $table ENABLE TRIGGER ALL;");
	$reqp->execute;
	
	# Emptying the hashes before stepping in the next table
	%fieldslist = ();
	%entry = ();
	@identifiers = ();
}

# Disconnecting

$dbhm->disconnect();
$dbhp->disconnect();

sub checkparam {
	for(my $i = 0; $i < 8; $i++) {
		if (!defined $_[$i] || $_[$i] eq "") { die "Execution aborted: All arguments are mandatory, see comments for more info."; }
	}
}


