/* ***************** 2011 STRI DB 'CREATE' MySQL Script *****************

 *									*

 *		Most of the 2011 db is based off of the 2007 db.	*

 *		This script creates the new tables of the 2011 db	*

 *		and creates the new attributes of existing tables	*

 *		Nothing is deleted in this script. The drop.sql		*

 *		script will remove excess data and schema.		*

 *									*

 ***********************************************************************/

/* ***************************** Developers *****************************

 *									*

 *		Bradley University, Computer Science Dept.		*

 *		Smithsonian Tropical Research Institute			*

 *									*

 *			2011 Capstone Project				*

 *		Mario Campos, Michael Koeber, Zach Caschetta 		*

 *									*	

 ***********************************************************************/

-- Quadrat TABLE

ALTER TABLE Tree DROP FOREIGN KEY `Tree_ibfk_1`;

ALTER TABLE CensusQuadrat DROP FOREIGN KEY `CensusQuadrat_ibfk_2`;

ALTER TABLE Quadrat DROP PRIMARY KEY;

ALTER TABLE Quadrat CHANGE COLUMN QuadratID QuadratID2 INT UNSIGNED;

ALTER TABLE Quadrat ADD COLUMN (Area FLOAT UNSIGNED, IsStandardShape ENUM('Y','N') NOT NULL);

ALTER TABLE Quadrat ADD COLUMN QuadratID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

-- CensusQuadrat TABLE

ALTER TABLE DataEntry DROP FOREIGN KEY `DataEntry_ibfk_1`;

ALTER TABLE DataCollection DROP FOREIGN KEY `DataCollection_ibfk_1`;

ALTER TABLE CensusQuadrat CHANGE COLUMN QuadratID QuadratID2 INT UNSIGNED;

ALTER TABLE CensusQuadrat ADD COLUMN QuadratID INT UNSIGNED NOT NULL;

UPDATE CensusQuadrat CQ, Quadrat Q SET CQ.QuadratID=Q.QuadratID WHERE CQ.QuadratID2=Q.QuadratID2 AND CQ.PlotID=Q.PlotID;

ALTER TABLE CensusQuadrat DROP PRIMARY KEY;

ALTER TABLE CensusQuadrat ADD COLUMN CensusQuadratID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

ALTER TABLE CensusQuadrat ADD FOREIGN KEY (`QuadratID`) REFERENCES `Quadrat` (`QuadratID`);

-- DataCollection TABLE

ALTER TABLE DataCollection DROP PRIMARY KEY;

ALTER TABLE DataCollection ADD COLUMN DataCollectionID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

ALTER TABLE DataCollection ADD COLUMN PersonnelRoleID INT UNSIGNED NOT NULL;

ALTER TABLE DataCollection CHANGE COLUMN QuadratID QuadratID2 INT UNSIGNED;

ALTER TABLE DataCollection ADD COLUMN QuadratID INT UNSIGNED NOT NULL;

UPDATE DataCollection D, Quadrat Q SET D.QuadratID=Q.QuadratID WHERE D.QuadratID2=Q.QuadratID2;

ALTER TABLE DataCollection ADD FOREIGN KEY (`QuadratID`) REFERENCES `Quadrat` (`QuadratID`);

ALTER TABLE DataCollection ADD FOREIGN KEY (`CensusID`)  REFERENCES `Census` (`CensusID`);

-- PersonnelRole TABLE

CREATE TABLE PersonnelRole (PersonnelRoleID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

       	     		    PersonnelID SMALLINT UNSIGNED NOT NULL,

			    RoleID SMALLINT UNSIGNED NOT NULL

			   )ENGINE=INNODB;

INSERT INTO PersonnelRole (PersonnelID,RoleID) SELECT PersonnelID, RoleID FROM DataCollection;

ALTER TABLE PersonnelRole ADD FOREIGN KEY (RoleID) REFERENCES RoleReference(RoleID);

ALTER TABLE PersonnelRole ADD FOREIGN KEY (PersonnelID) REFERENCES Personnel(PersonnelID);

UPDATE DataCollection D, PersonnelRole P SET D.PersonnelRoleID=P.PersonnelRoleID WHERE D.PersonnelID=P.PersonnelID AND D.RoleID=P.RoleID;

ALTER TABLE DataCollection DROP FOREIGN KEY `DataCollection_ibfk_2`;

ALTER TABLE DataCollection DROP FOREIGN KEY `DataCollection_ibfk_3`;

ALTER TABLE DataCollection ADD FOREIGN KEY (`PersonnelRoleID`) REFERENCES `PersonnelRole` (`PersonnelRoleID`);

-- Tree TABLE

ALTER TABLE Tree CHANGE COLUMN QuadratID QuadratID2 INT UNSIGNED;

ALTER TABLE Tree ADD COLUMN QuadratID INT UNSIGNED;

UPDATE Tree T, Quadrat Q SET T.QuadratID=Q.QuadratID WHERE T.QuadratID2=Q.QuadratID2;

-- Stem TABLE

ALTER TABLE Measurement DROP FOREIGN KEY `Measurement_ibfk_2`;

-- ALTER TABLE Remeasurement DROP FOREIGN KEY to stem --FINISH THIS LINE

ALTER TABLE Stem DROP PRIMARY KEY;

ALTER TABLE Stem ADD COLUMN (QuadratID INT UNSIGNED NOT NULL,

      	    	     	     StemNumber INT UNSIGNED NOT NULL,

			     Moved ENUM('Y','N') NOT NULL,

			     GX FLOAT,

			     GY FLOAT,

			     GZ FLOAT,

			     PX FLOAT,

			     PY FLOAT,

			     PZ FLOAT,

			     QX FLOAT,

			     QY FLOAT,

			     QZ FLOAT

			    );

UPDATE Stem S, Tree T SET S.QuadratID=T.QuadratID WHERE S.TreeID=T.TreeID;

UPDATE Stem S1, Stem S2 SET S1.StemNumber=S2.StemID WHERE S1.StemID=S2.StemID AND S1.TreeID=S2.TreeID;

UPDATE Stem S, Tree T SET S.QX=T.x, S.QY=T.y WHERE S.TreeID=T.TreeID;

ALTER TABLE Stem DROP COLUMN StemID;

ALTER TABLE Stem ADD COLUMN StemID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

-- Measurement(DBH) TABLE

ALTER TABLE LogMeasurementHistory DROP FOREIGN KEY `LogMeasurementHistory_ibfk_1`;

ALTER TABLE MeasurementAttributes DROP FOREIGN KEY `MeasurementAttributes_ibfk_1`;

ALTER TABLE Measurement DROP PRIMARY KEY;

ALTER TABLE Measurement ADD COLUMN DBHID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

ALTER TABLE Measurement ADD COLUMN Comments VARCHAR(128);

ALTER TABLE Measurement MODIFY COLUMN HOM CHAR(16);

UPDATE Measurement M, Stem S SET M.StemID=S.StemID WHERE M.StemID=S.StemNumber AND M.TreeID=S.TreeID;

ALTER TABLE Measurement ADD FOREIGN KEY (`StemID`) REFERENCES `Stem` (`StemID`);

-- MeasurementAttributes TABLE

ALTER TABLE MeasurementAttributes ADD COLUMN DBHID INT UNSIGNED;

UPDATE MeasurementAttributes MA, Measurement M SET MA.DBHID=M.DBHID WHERE MA.MeasureID=M.MeasureID;

ALTER TABLE MeasurementAttributes ADD FOREIGN KEY (`DBHID`) REFERENCES `Measurement` (`DBHID`);

ALTER TABLE LogMAttrHistory DROP FOREIGN KEY `LogMAttrHistory_ibfk_1`;

ALTER TABLE MeasurementAttributes DROP PRIMARY KEY;

ALTER TABLE MeasurementAttributes ADD COLUMN DBHAttID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

-- Remeasurement TABLE

ALTER TABLE RemeasAttribs DROP FOREIGN KEY `RemeasAttribs_ibfk_1`;

ALTER TABLE Remeasurement DROP PRIMARY KEY;

ALTER TABLE Remeasurement CHANGE COLUMN RemeasureID RemeasureID2 INT UNSIGNED;

ALTER TABLE Remeasurement ADD COLUMN RemeasureID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

UPDATE Remeasurement R, Stem S SET R.StemID=S.StemID WHERE R.StemID=S.StemNumber AND R.TreeID=S.TreeID;

ALTER TABLE Remeasurement ADD FOREIGN KEY (`StemID`) REFERENCES `Stem` (`StemID`);

-- RemeasAttribs TABLE

ALTER TABLE RemeasAttribs CHANGE COLUMN RemeasureID RemeasureID2 INT UNSIGNED;

ALTER TABLE RemeasAttribs ADD COLUMN RemeasureID INT UNSIGNED NOT NULL;

UPDATE RemeasAttribs RA, Remeasurement R SET RA.RemeasureID=R.RemeasureID WHERE RA.RemeasureID2=R.RemeasureID2;

ALTER TABLE RemeasAttribs DROP PRIMARY KEY;

ALTER TABLE RemeasAttribs ADD COLUMN RmAttID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

ALTER TABLE RemeasAttribs ADD FOREIGN KEY (`RemeasureID`) REFERENCES `Remeasurement` (`RemeasureID`);

-- TreeAttributes TABLE

ALTER TABLE LogTreeAttrHistory DROP FOREIGN KEY `LogTreeAttrHistory_ibfk_1`;

ALTER TABLE TreeAttributes DROP PRIMARY KEY;

ALTER TABLE TreeAttributes ADD COLUMN TAttID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

-- rename tables

ALTER TABLE Measurement RENAME TO DBH;

ALTER TABLE MeasurementAttributes RENAME TO DBHAttributes;

-- add new tables

CREATE TABLE IF NOT EXISTS FeatureTypes (

					   FeatureTypeID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

					   Type VARCHAR(32) NOT NULL

					)  ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS Features (

					FeatureID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

					FeatureTypeID INT UNSIGNED NOT NULL,

					Name VARCHAR(32) NOT NULL,

					ShortDescrip VARCHAR(32),

					LongDescrip VARCHAR(128),

					FOREIGN KEY (`FeatureTypeID`) REFERENCES `FeatureTypes` (`FeatureTypeID`)

				    )	ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS Coordinates (

					  CoorID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

					  FeatureID INT UNSIGNED,

					  PlotID INT UNSIGNED,

					  QuadratID INT UNSIGNED,

					  GX FLOAT(8),

					  GY FLOAT(8),

					  GZ FLOAT(8),

					  PX FLOAT(8),

					  PY FLOAT(8),

					  PZ FLOAT(8),

					  QX FLOAT(8),

					  QY FLOAT(8),

					  QZ FLOAT(8),

					  CoordinateNo INT UNSIGNED,

					  FOREIGN KEY (`FeatureID`) REFERENCES `Features` (`FeatureID`),

					  FOREIGN KEY (`PlotID`) REFERENCES `Site` (`PlotID`),

					  FOREIGN KEY (`QuadratID`) REFERENCES `Quadrat` (`QuadratID`)

				       )  ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS MeasurementType (

						MeasurementTypeID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

						UOM VARCHAR(32) NOT NULL,

						Type VARCHAR(256)

					   )	ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS Measurement (

					MeasureID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

					CensusID INT UNSIGNED NOT NULL,

					TreeID INT UNSIGNED NOT NULL,

					StemID INT UNSIGNED NOT NULL,

					MeasurementTypeID INT UNSIGNED NOT NULL,

					Measure VARCHAR(256) NOT NULL,

					ExactDate DATE NOT NULL,

					Comments VARCHAR(128),

					FOREIGN KEY (`CensusID`) REFERENCES `Census` (`CensusID`),

					FOREIGN KEY (`TreeID`) REFERENCES `Tree` (`TreeID`),

					FOREIGN KEY (`StemID`) REFERENCES `Stem` (`StemID`),

					FOREIGN KEY (`MeasurementTypeID`) REFERENCES `MeasurementType` (`MeasurementTypeID`)

				       )ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS MeasurementAttributes (

       	     	    	   			  MAttID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

						  MeasureID INT UNSIGNED NOT NULL,

						  CensusID INT UNSIGNED NOT NULL,

						  TSMID INT UNSIGNED NOT NULL,

						  FOREIGN KEY (`MeasureID`) REFERENCES `Measurement` (`MeasureID`),

						  FOREIGN KEY (`CensusID`) REFERENCES `Census` (`CensusID`),

						  FOREIGN KEY (`TSMID`) REFERENCES `TSMAttributes` (`TSMID`)

						 )ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS Log (

				LogID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

				PersonnelID SMALLINT UNSIGNED,

				ChangedTable VARCHAR(32) NOT NULL,

				PrimaryKey VARCHAR(32) NOT NULL,

				ChangedColumn VARCHAR(32) NOT NULL,

				ChangeDate DATE,

				ChangeTime TIMESTAMP,

				Description VARCHAR(256),

				Action ENUM('I','D','U') NOT NULL,

				Old VARCHAR(512) NOT NULL,

				New VARCHAR(512) NOT NULL,

				FOREIGN KEY (`PersonnelID`) REFERENCES `Personnel` (`PersonnelID`)

			       )ENGINE=INNODB;

-- modify existing tables to meet new schema's specs

ALTER TABLE Site ADD COLUMN (  Area INT UNSIGNED NOT NULL,

      	    	     	       QDimX INT UNSIGNED NOT NULL,

			       QDimY INT UNSIGNED NOT NULL,

			       GUOM VARCHAR(32) NOT NULL,

			       GZUOM VARCHAR(32) NOT NULL,

			       PUOM VARCHAR(32) NOT NULL,

			       QUOM VARCHAR(32) NOT NULL,

			       GCoorCollected VARCHAR(32),

			       PCoorCollected VARCHAR(32),

			       QCoorCollected VARCHAR(32)

			    ); 

ALTER TABLE Stem ADD COLUMN (	GX FLOAT(8),

				GY FLOAT(8),

				GZ FLOAT(8),

				PX FLOAT(8),

				PY FLOAT(8),

				PZ FLOAT(8),

				QX FLOAT(8),

				QY FLOAT(8),

				QZ FLOAT(8),

				Moved ENUM('Y','N') NOT NULL

			    );

ALTER TABLE Census MODIFY COLUMN PlotCensusNumber CHAR(16);

/* ************ MySQL Database and Schema Converting Script *************

 *									*

 *		This script first replicates the old databases		*

 *		schema into a new database. The new database's		*

 *		schema is then modified to meet the new schema		*

 *		specifications. Finally, the the data of the 		*

 *		old database is copied into the new database		*

 *		and converted, if necessary.				*

 *									*

 ***********************************************************************/

/* ***************************** Developers *****************************

 *									*

 *		Bradley University, Computer Science Dept.		*

 *		Smithsonian Tropical Research Institute			*

 *									*

 *			2011 Capstone Project				*

 *		Mario Campos, Michael Koeber, Zach Caschetta 		*

 *									*	

 ***********************************************************************/

-- drop unnecessary columns

ALTER TABLE Site DROP COLUMN Latitude;

ALTER TABLE Site DROP COLUMN Longitude;

ALTER TABLE Site DROP COLUMN Elevation;

ALTER TABLE Site DROP COLUMN ReferenceX;

ALTER TABLE Site DROP COLUMN ReferenceY;

ALTER TABLE Quadrat DROP COLUMN DimX;

ALTER TABLE Quadrat DROP COLUMN DimY;

ALTER TABLE Quadrat DROP COLUMN StartX;

ALTER TABLE Quadrat DROP COLUMN StartY;

ALTER TABLE Quadrat DROP COLUMN QuadratID2;

ALTER TABLE CensusQuadrat DROP COLUMN QuadratID2;

ALTER TABLE CensusQuadrat DROP COLUMN PlotID;

ALTER TABLE DataCollection DROP COLUMN PlotID;

ALTER TABLE DataCollection DROP COLUMN PersonnelID;

ALTER TABLE DataCollection DROP COLUMN RoleID;

ALTER TABLE DataCollection DROP COLUMN QuadratID2;

ALTER TABLE Tree DROP COLUMN QuadratID2;

ALTER TABLE Tree DROP COLUMN QuadratID;

ALTER TABLE Tree DROP COLUMN PlotID;

ALTER TABLE Tree DROP COLUMN x;

ALTER TABLE Tree DROP COLUMN y;

ALTER TABLE DBH DROP COLUMN TreeID;

ALTER TABLE DBH DROP COLUMN MeasureID;

ALTER TABLE DBHAttributes DROP COLUMN MeasureID;

ALTER TABLE Remeasurement DROP FOREIGN KEY `Remeasurement_ibfk_2`;

ALTER TABLE Remeasurement DROP COLUMN TreeID;

ALTER TABLE Remeasurement DROP COLUMN RemeasureID2;

ALTER TABLE RemeasAttribs DROP COLUMN RemeasureID2;

-- drop unnecessary tables

DROP TABLE IF EXISTS DFTemp,

     	      	     LogMAttrHistory,

		     LogTreeHistory,

		     DataEntry,

		     LogStemHistory,

		     LogMeasurementHistory,

		     StageTargetNew,

		     StageTarget,

		     StageSource,

		     StageTaxonomicChanges,

		     LogTreeAttrHistory;

#!/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 $!;