#  Database changes and conversion script for major release 2014
# Shameema Esufali Last updated Aug 21 2014

# ***************************************************************************
# 1.  MAKE ID LEVELS CONSISTENT ACROSS DATABASES
# ***************************************************************************
/* ID LEVELS defined by R. Condit's email of August 16th 2012
#  addition of 'superspecies' to accomodate data from Taiwan

>>> Yes, AAAA** is correctly assigned IDlevel='multiple'.
>>>
>>> If all the remaining are valid morphospecies, then the IDlevel should
>>> be 'subspecies', 'species', 'genus', 'family', or 'none'. It gives the
>>> level of taxonomy to which the identity is known. So to answer your
>>> questions,
>>>
>>>> If we know up to variety, IDlevel =  'subspecies'
>>>> If known up to species, IDlevel = 'species'
>>>> If known up to genus (but valid morphospecies), IDlevel = 'genus'
>>>> If known only up to family (but valid morphospecies), IDlevel = 'family'
>>>
>>> IDlevel='none' is for cases of valid morphospecies for which the
>>> family is not known. There are a few of those around.
>>>
>>> The idea is that any IDlevel='multiple' should be excluded from
>>> species level analyses because they represent a mixture of species. In
>>> many plots, there is only one such mnemonic, as you have at Palanan.
>>> But there are some plots with many different 'multiple' mnemonics,
>>> such as FICU** to mean cases where the genus is known to be Ficus, but
>>> the mnemonic includes a mixture of different Ficus which can't be
>>> distinguished.
*/

# This data has to be converted from existing data.  Script updateidlevels.sql 
# goes through all the current and stable databases and updates them to valid equivalents 
 ALTER TABLE `Species` 
  MODIFY COLUMN IDLEVEL 
  ENUM('subspecies','species','superspecies','genus','family','multiple','none','variety');


# ***************************************************************************
# 2 ADD LIFEFORM TO SPECIES 
# ***************************************************************************
# This update can be done without data and sites advised to add data at their convenience
 ALTER TABLE `Species` 
  ADD COLUMN `Lifeform` 
  ENUM('Emergent Tree', 'Tree', 'Midcanopy Tree', 'Understory Tree', 'Shrub','Herb', 'Liana');
 

/*
use information_schema;
select c.table_name,c.column_name,c.table_schema from INFORMATION_SCHEMA.COLUMNS c where c.table_name='Species' and c.column_name='IDLevel' and 
c.table_schema like 'current_%' or c.table_schema like 'stable_%'

*/
  

# ***************************************************************************
# 3 ADD LOCALNAME TO SPECIES TABLE
# ***************************************************************************
# This update can be done without data and sites advised to add data at their convenience.  
# In some instances the data is available in the original files sent to us and 
# we (SL and SE) can do the update ourselves.

  ALTER TABLE `Species` ADD COLUMN `LocalName` VARCHAR(128);
  
  
# ***************************************************************************
# 4 ADD STATUS COLUMN TO TSMATTRIBUTES 
# ***************************************************************************

# Definition of these statuses

# 'alive' - Tree is alive
# 'dead' - Tree is dead
# 'not found' - Tree was recorded in previous census and not reported alive or dead in 
# current  census.  Should this be derived rather than specified as a status of an attribute?  
#    There are two cases.  1. Field workers mark it as missing 2. The data is just not there
# 'broken below'  - Tree is alive but the stem is broken.   

# This update can be done without data and we must ensure that the statuses are filled in.  Clear
# guidelines for this must be agreed so that SL and SE can, in consultation with the sites update the # TSMAttributes table for each site.

--  ALTER TABLE `TSMAttributes`  
--  ADD COLUMN `Status` ENUM('alive,alive-not measured','dead','not found','broken below');
  ALTER TABLE `TSMAttributes`  
  ADD COLUMN `Status` ENUM('alive','alive-not measured','dead','not found','broken below');


# ***************************************************************************
# 5. RENAME TAX1temp TO ViewTaxonomy  
# ***************************************************************************
# Anudeep must change the script that creates this table.  Make the fields CHAR rather 
# than ENUM because this is a created view. 

  RENAME TABLE TAX1temp TO ViewTaxonomy;
  ALTER TABLE ViewTaxonomy ADD COLUMN `LocalName` VARCHAR(128);
  ALTER TABLE ViewTaxonomy ADD COLUMN FieldFamily CHAR(32);
  ALTER TABLE ViewTaxonomy ADD COLUMN `Lifeform` CHAR(32);
  ALTER TABLE ViewTaxonomy  MODIFY COLUMN IDLevel CHAR(32);
  

# ***************************************************************************  
# 6.  Remove CensusID from DBHAttributes, MeasurementAttributes and RemeasAttribs
# ***************************************************************************
/*
use INFORMATION_SCHEMA;
select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from KEY_COLUMN_USAGE
where TABLE_SCHEMA = "stable_fushan" and TABLE_NAME = "MeasurementAttributes" 
and referenced_column_name is not NULL;
*/
ALTER TABLE MeasurementAttributes
  DROP FOREIGN KEY MeasurementAttributes_ibfk_2;
DROP INDEX censusid ON MeasurementAttributes;
ALTER TABLE DBHAttributes DROP COLUMN censusid;
ALTER TABLE MeasurementAttributes DROP COLUMN censusid;
ALTER TABLE RemeasAttribs DROP COLUMN censusid;

# ***************************************************************************  
# 7.  Add columns to ViewFullTable
# ***************************************************************************

ALTER TABLE ViewFullTable
 ADD COLUMN HighHOM bool,
 ADD COLUMN MainStem bool,
 MODIFY COLUMN Status VARCHAR(25);


#  Find and change the coordinates to decimal Stem, Coordinates and ViewFullTable  (check?)
# Have Taiwan run the update.

ALTER TABLE Stem MODIFY COLUMN GX decimal(16,5), MODIFY COLUMN GY decimal(16,5),MODIFY COLUMN GZ decimal(16,5);
ALTER TABLE Stem MODIFY COLUMN PX decimal(16,5), MODIFY COLUMN PY decimal(16,5),MODIFY COLUMN PZ decimal(16,5);
ALTER TABLE Stem MODIFY COLUMN QX decimal(16,5), MODIFY COLUMN QY decimal(16,5),MODIFY COLUMN QZ decimal(16,5);

ALTER TABLE Coordinates MODIFY COLUMN GX decimal(16,5), MODIFY COLUMN GY decimal(16,5),MODIFY COLUMN GZ decimal(16,5);
ALTER TABLE Coordinates MODIFY COLUMN PX decimal(16,5), MODIFY COLUMN PY decimal(16,5),MODIFY COLUMN PZ decimal(16,5);
ALTER TABLE Coordinates MODIFY COLUMN QX decimal(16,5), MODIFY COLUMN QY decimal(16,5),MODIFY COLUMN QZ decimal(16,5);

ALTER TABLE ViewFullTable MODIFY COLUMN PX decimal(16,5), MODIFY COLUMN PY decimal(16,5);
ALTER TABLE Coordinates MODIFY COLUMN QX decimal(16,5), MODIFY COLUMN QY decimal(16,5);




  

 






