#!/usr/bin/perl -w -s

############################# migrate.pl ###############################

#								       #

#	This perl script creates the new 2011 db and migrates          #

#       from the old db by...                                          #

#	1. creates the new database using create.sql                   #

#	2. maps the data from the old db to the new db                 #

#	3. drops the excess data and schema                            #

#								       #

#       Usage: migrate.pl [options]                                    #

#       options:                                                       #

#         -olddb='<DATABASENAME>'    Name of the old STRI Database     #

#         -newdb='<DATABASENAME>'    Name of the new STRI Database     #

#         -user='<USERNAME>'         MySQL username                    #

#         -passwd='<PASSWORD>'       Password of the MySQL user        #

#         -cfile='<FILEPATH>'        Absolute path to create.sql       #

#         -dfile='<FILEPATH>'        Absolute path to drop.sql         #

#         -mysql='<FILEPATH>'        Absolute path to mysql            #

#         -mysqldump='<FILEPATH>'    Absolute path to mysqldump        #

#         -quiet                     Ignores errors                    #

#                                                                      #

########################################################################

############################## Developers ##############################

#								       #

#		Bradley University, Computer Science Dept.	       #

#		Smithsonian Tropical Research Institute		       #

#								       #

#			2011 Capstone Project			       #

#		Mario Campos, Michael Koeber, Zach Caschetta 	       #

#								       #

########################################################################

use strict;

use DBI;

use Fcntl;

our($olddb,$newdb,$user,$passwd,$cfile,$dfile,$mysql,$mysqldump,$quiet);

BEGIN {

    $olddb or print 'Enter the old database name:' and chomp($olddb=<>);

    $newdb or print 'Enter the new database name:' and chomp($newdb=<>);

    $user or print 'Enter the username:' and chomp($user=<>);

    $passwd or print 'Enter the password:' and chomp($passwd=<>);

    $cfile or print 'Enter the path of the sql create file:' and chomp($cfile=<>);

    $dfile or print 'Enter the path of the sql drop file:' and chomp($dfile=<>);

    $mysql or $mysql='mysql';

    $mysqldump or $mysqldump='mysqldump';

    sysopen FILE,'migrate.log',O_CREAT|O_EXCL|O_WRONLY,0755 or die $!;

}

# create new db

print "Creating `$newdb`...";

system "$mysql -u $user -p$passwd -e 'create database $newdb;'";

print "done!\n";

# dump old db

print "Dumping `$olddb`...";

system "$mysqldump --opt -u $user -p$passwd $olddb > $olddb";

print "done!\n";

# inserting data into new db

print "Undumping into `$newdb`...";

system "$mysql -u $user -p$passwd $newdb < $olddb";

print "done!\n";

# running create.sql

system "$mysql -v -u $user -p$passwd $newdb < $cfile";

# connect to MySQL

my $dbh = DBI->connect("DBI:mysql:database=$newdb;host=localhost",$user,$passwd);

#   create coordinates of plot points

my $sql = 'SELECT PlotID, Latitude, Longitude, Elevation FROM Site';

my $sth = $dbh->prepare("$sql");

$sth->execute;

my $tbl_ref = $sth->fetchall_hashref('PlotID');

foreach my $key (keys %$tbl_ref) {

    $sql = 'INSERT INTO Coordinates (PlotID,GX,GY';

    defined $tbl_ref->{$key}->{'Elevation'} and $sql.=',GZ';

    $sql.=") VALUES ($key,$tbl_ref->{$key}->{Longitude},$tbl_ref->{$key}->{Latitude}";

    defined $tbl_ref->{$key}->{'Elevation'} and $sql.=",$tbl_ref->{$key}->{'Elevation'}";

    $sql.=')';

    if($dbh->do($sql)) {

	print "$sql\n";

    } else {

	die "Error executing: $sql\n" unless defined $quiet;

	print FILE "Error executing: $sql\n";

    }

}

#   create coordinates of quadrat points

$sql = 'SELECT QuadratID, PlotID, StartX, StartY FROM Quadrat';

$sth = $dbh->prepare("$sql");

$sth->execute;

$tbl_ref = $sth->fetchall_hashref('QuadratID');

foreach my $key (keys %$tbl_ref) {

    $sql = "INSERT INTO Coordinates (QuadratID,PlotID,PX,PY) VALUES ($key,$tbl_ref->{$key}->{PlotID},$tbl_ref->{$key}->{StartX},$tbl_ref->{$key}->{StartY})";

    if($dbh->do($sql)) {

        print "$sql\n";

    } else {

        die "Error executing: $sql\n" unless defined $quiet;

        print FILE "Error executing: $sql\n";

    }

}

$sql = 'SELECT DISTINCT DimX, DimY, PlotID FROM Quadrat';

$sth = $dbh->prepare("$sql");

$sth->execute;

$tbl_ref = $sth->fetchall_hashref('PlotID');

foreach my $key (keys %$tbl_ref) {

    if(defined $tbl_ref->{$key}->{'DimX'} && defined $tbl_ref->{$key}->{'DimY'}) {

	$sql = "UPDATE Site SET QDimX=$tbl_ref->{$key}->{DimX}, QDimY=$tbl_ref->{$key}->{DimY} WHERE PlotID=$key";

	if($dbh->do($sql)) {

	    print "$sql\n";

	} else {

	    die "Error executing: $sql\n" unless defined $quiet;

	    print FILE "Error executing: $sql\n";

	}

    }

}

# LogTreeHistory

$sql = 'SELECT TreeID,ChangeDate,ChangeDescription,ChangeCodeID,QuadratID,PlotID,Tag,X,Y,SpeciesID,SubSpeciesID FROM LogTreeHistory ORDER BY ChangeDate';

$sth = $dbh->prepare("$sql");

$sth->execute;

my @row = ();

while( @row = $sth->fetchrow_array ) {

    $sql = "INSERT INTO Log (ChangedTable,PrimaryKey,";

    defined $row[1] and $sql .= 'ChangeDate,';

    defined $row[2] and $sql .= 'Description,';

    $sql .= "New) VALUES ('Tree',$row[0],";

    defined $row[1] and $sql .= "'$row[1]',";

    defined $row[2] and $sql .= "'$row[2]',";

    $sql .= "'ChangeCodeID=$row[3],QuadratID=$row[4],PlotID=$row[5],Tag=$row[6],X=$row[7],Y=$row[8],SpeciesID=$row[9],SubspeciesID=$row[10]')";

    if($dbh->do($sql)) {

	print "$sql\n";

    } else {

	die "Error executing: $sql\n" unless defined $quiet;

	print FILE "Error executing: $sql\n";

    }

}

# LogMeasurementHistory

$sql = 'SELECT MeasureID,CensusID,DateOfChange,DescriptionOfChange,StemID,TreeID,DBH,HOM,ExactDate FROM LogMeasurementHistory ORDER BY DateOfChange';

$sth = $dbh->prepare("$sql");

$sth->execute;

while( @row = $sth->fetchrow_array ) {

    $sql = "INSERT INTO Log (ChangedTable,PrimaryKey,";

    defined $row[2] and $sql .= 'ChangeDate,';

    defined $row[3] and $sql .= 'Description,';

    $sql .= "New) VALUES ('Measurement','MeasureID=$row[0],CensusID=$row[1]',";

    defined $row[2] and $sql .= "'$row[2]',";

    defined $row[3] and $sql .= "'$row[3]',";

    $sql .= "'StemID=$row[4],TreeID=$row[5],DBH=$row[6],HOM=$row[7],ExactDate=$row[8]')";

    if($dbh->do($sql)) {

	print "$sql\n";

    } else {

        die "Error executing: $sql\n" unless defined $quiet;

        print FILE "Error executing: $sql\n";

    }

}

# LogMAttrHistory

$sql = 'SELECT MeasureID,CensusID,TSMID,DateOfChange,DescriptionOfChange FROM LogMAttrHistory ORDER BY DateOfChange';

$sth = $dbh->prepare($sql);

$sth->execute;

while( @row = $sth->fetchrow_array ) {

    $sql = "INSERT INTO Log (ChangedTable,PrimaryKey,";

    defined $row[3] and $sql .= 'ChangeDate,';

    defined $row[4] and $sql .= 'Description,';

    $sql .= "New) VALUES ('MeasurementAttributes','MeasureID=$row[0],CensusID=$row[1],TSMID=$row[2]',";

    defined $row[3] and $sql .= "'$row[3]',";

    defined $row[4] and $sql .= "'$row[4]',";

    $sql .= "'MeasureID=$row[0],CensusID=$row[1],TSMID=$row[2]')";

    if($dbh->do($sql)) {

        print "$sql\n";

    } else {

        die "Error executing: $sql\n" unless defined $quiet;

        print FILE "Error executing: $sql\n";

    }

}

# LogTreeAttrHistory

$sql = 'SELECT CensusID,TreeID,TSMID,DateOfChange,DescriptionOfChange FROM LogTreeAttrHistory ORDER BY DateOfChange';

$sth = $dbh->prepare("$sql");

$sth->execute;

while( @row = $sth->fetchrow_array ) {

    $sql = "INSERT INTO Log (ChangedTable,PrimaryKey,";

    defined $row[3] and $sql .= 'ChangeDate,';

    defined $row[4] and $sql .= 'Description,';

    $sql .= "New) VALUES ('TreeAttributes','CensusID=$row[0],TreeID=$row[1],TSMID=$row[2]',";

    defined $row[3] and $sql .= "'$row[3]',";

    defined $row[4] and $sql .= "'$row[4]',";

    $sql .= "'CensusID=$row[0],TreeID=$row[1],TSMID=$row[2]')";

    if($dbh->do($sql)) {

        print "$sql\n";

    } else {

        die "Error executing: $sql\n" unless defined $quiet;

        print FILE "Error executing: $sql\n";

    }

}

# execute drop.sql

system "$mysql -v -u $user -p$passwd $newdb < $dfile" or die $!;

close FILE or die $!;