1 |
1549
|
aaronmk
|
/* ************ MySQL Database and Schema Converting Script *************
|
2 |
|
|
* *
|
3 |
|
|
* This script first replicates the old databases *
|
4 |
|
|
* schema into a new database. The new database's *
|
5 |
|
|
* schema is then modified to meet the new schema *
|
6 |
|
|
* specifications. Finally, the the data of the *
|
7 |
|
|
* old database is copied into the new database *
|
8 |
|
|
* and converted, if necessary. *
|
9 |
|
|
* *
|
10 |
|
|
***********************************************************************/
|
11 |
|
|
|
12 |
|
|
/* ***************************** Developers *****************************
|
13 |
|
|
* *
|
14 |
|
|
* Bradley University, Computer Science Dept. *
|
15 |
|
|
* Smithsonian Tropical Research Institute *
|
16 |
|
|
* *
|
17 |
|
|
* 2011 Capstone Project *
|
18 |
|
|
* Mario Campos, Michael Koeber, Zach Caschetta *
|
19 |
|
|
* *
|
20 |
|
|
***********************************************************************/
|
21 |
|
|
|
22 |
|
|
-- drop unnecessary columns
|
23 |
|
|
ALTER TABLE Site DROP COLUMN Latitude;
|
24 |
|
|
ALTER TABLE Site DROP COLUMN Longitude;
|
25 |
|
|
ALTER TABLE Site DROP COLUMN Elevation;
|
26 |
|
|
ALTER TABLE Site DROP COLUMN ReferenceX;
|
27 |
|
|
ALTER TABLE Site DROP COLUMN ReferenceY;
|
28 |
|
|
ALTER TABLE Quadrat DROP COLUMN DimX;
|
29 |
|
|
ALTER TABLE Quadrat DROP COLUMN DimY;
|
30 |
|
|
ALTER TABLE Quadrat DROP COLUMN StartX;
|
31 |
|
|
ALTER TABLE Quadrat DROP COLUMN StartY;
|
32 |
|
|
ALTER TABLE Quadrat DROP COLUMN QuadratID2;
|
33 |
|
|
ALTER TABLE CensusQuadrat DROP COLUMN QuadratID2;
|
34 |
|
|
ALTER TABLE CensusQuadrat DROP COLUMN PlotID;
|
35 |
|
|
ALTER TABLE DataCollection DROP COLUMN PlotID;
|
36 |
|
|
ALTER TABLE DataCollection DROP COLUMN PersonnelID;
|
37 |
|
|
ALTER TABLE DataCollection DROP COLUMN RoleID;
|
38 |
|
|
ALTER TABLE DataCollection DROP COLUMN QuadratID2;
|
39 |
|
|
ALTER TABLE Tree DROP COLUMN QuadratID2;
|
40 |
|
|
ALTER TABLE Tree DROP COLUMN QuadratID;
|
41 |
|
|
ALTER TABLE Tree DROP COLUMN PlotID;
|
42 |
|
|
ALTER TABLE Tree DROP COLUMN x;
|
43 |
|
|
ALTER TABLE Tree DROP COLUMN y;
|
44 |
|
|
ALTER TABLE DBH DROP COLUMN TreeID;
|
45 |
|
|
ALTER TABLE DBH DROP COLUMN MeasureID;
|
46 |
|
|
ALTER TABLE DBHAttributes DROP COLUMN MeasureID;
|
47 |
|
|
ALTER TABLE Remeasurement DROP FOREIGN KEY `Remeasurement_ibfk_2`;
|
48 |
|
|
ALTER TABLE Remeasurement DROP COLUMN TreeID;
|
49 |
|
|
ALTER TABLE Remeasurement DROP COLUMN RemeasureID2;
|
50 |
|
|
ALTER TABLE RemeasAttribs DROP COLUMN RemeasureID2;
|
51 |
|
|
|
52 |
|
|
-- drop unnecessary tables
|
53 |
|
|
DROP TABLE IF EXISTS DFTemp,
|
54 |
|
|
LogMAttrHistory,
|
55 |
|
|
LogTreeHistory,
|
56 |
|
|
DataEntry,
|
57 |
|
|
LogStemHistory,
|
58 |
|
|
LogMeasurementHistory,
|
59 |
|
|
StageTargetNew,
|
60 |
|
|
StageTarget,
|
61 |
|
|
StageSource,
|
62 |
|
|
StageTaxonomicChanges,
|
63 |
|
|
LogTreeAttrHistory;
|