/* ************ 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;