-- Sample script 
-- This script shows how to update Trees.
-- 1. Update a tag
-- 2. Update species codes where a tree 
-- was identified as Species A and has now been corrected to Species B
-- where both A and B are already in the species list. 
-- This is NOT the process for taxonomic revision of all trees in a particular taxon
# Shameema Esufali June 2020

-- **********************************************************************************************
-- Set up the database.  Replace the test database "blue" with the last frozen 
-- version of your database. Eg USE current_yoursite;  SOURCE ../bci_current_20August2020.sql;
DROP DATABASE IF EXISTS blue;
CREATE DATABASE IF NOT EXISTS blue;
USE blue;
SOURCE ../sampledatabases/blue.sql;


-- You must run the code below in your database.  Once you have run it for one script it is 
-- not necessary to run it for others but it will not cause a problem if you do. 
-- Do not remove the commands below. Change the path to reflect your directory path.
# 1. Installs a procedure that creates DBHAttributes from codes separated by a semicolon.
# The following columns must exist in the table tempinserttrees. 
# codes varchar(20),
# dbhid int,
# 2. Installs Log Procedures. 
SOURCE Procedures/LogProcedures.sql;
SOURCE Procedures/Utilities.sql;

# Some older databases have the old and new columns as NOT NULL
# the statement below corrects the error. 
ALTER TABLE Log MODIFY old VARCHAR(2048), MODIFY new VARCHAR(2048);
SELECT @n1:=count(*) FROM Log;

# This table is required in the database. 
CREATE TABLE TempLogFeeder(
  ChangedTableID int(10),
  ChangedColumn varchar(64),
  ChangeReason VARCHAR(256)
  );
  
-- end database setup **********************************************************************  


# 1.  UPDATE a tag to a new tag.  A comment explaining the change is advisable. 
# Include the personnelid of the person who can explain the change in your datafile. 

-- Load the data 
CREATE TABLE tempchangetag (
 tag char(15),
 newtag char(15),
 changereason varchar(256),
 personnelid int
 );
 
 LOAD DATA LOCAL INFILE 
'../datafiles/changetag.csv'
INTO TABLE tempchangetag FIELDS OPTIONALLY ENCLOSED BY '"' terminated by ','IGNORE 1 LINES;

ALTER TABLE tempchangetag ADD COLUMN treeid int;
UPDATE tempchangetag a, Tree b SET a.treeid=b.treeid 
 WHERE TRIM(leading '0' from a.tag)=TRIM(leading '0' from b.tag);

 -- Log the changes
SET @personnelid=1, @action="U", @changedcolumn="Tag"; 
TRUNCATE TempLogFeeder;
INSERT INTO TempLogFeeder (ChangedTableID,ChangedColumn,ChangeReason) 
 SELECT  treeid,@changedcolumn,changereason FROM tempchangetag;

CALL logRecordSet(@personnelid,"Tree",@action) ; 

-- Update the tags
UPDATE Tree a, tempchangetag b
 SET  a.tag=b.newtag
 WHERE a.tag=b.tag;
 
-- Rebuild Views
# Replace the first parameter with your database name 
CALL ctfsweb_webuser.CreateViewTaxonomy("blue","'foo'");
CALL ctfsweb_webuser.CreateFullView( "blue","ViewFullTable");

# Clean up temp tables when you are done. (Un-comment this statement to execute it) 
# DROP TABLE tempchangetag;

# ******************************************************************************************************* 
# 2. UPDATE the speciesid on some trees.  A comment explaining the change is advisable. 
# Include the personnelid of the person who can explain the change in your datafile.   

-- Load the data
CREATE TABLE tempchangetreespecies (
 tag char(15),
 oldspcode char(15),
 newspcode char(15),
 changereason varchar(256),
 personnelid int
);

LOAD DATA LOCAL INFILE 
 '../datafiles/changetreespecies.csv'
 INTO TABLE tempchangetreespecies FIELDS OPTIONALLY ENCLOSED BY '"' terminated by ','IGNORE 1 LINES;

ALTER TABLE tempchangetreespecies ADD COLUMN treeid INT, ADD COLUMN oldspeciesid int(15), ADD COLUMN newspeciesid int(15);
UPDATE tempchangetreespecies a, Tree b SET a.treeid=b.treeid WHERE a.tag =b.tag;
SELECT "Updating species ids";
UPDATE tempchangetreespecies a, Species b SET a.oldspeciesid =b.speciesid WHERE a.oldspcode= b.mnemonic;
UPDATE tempchangetreespecies a, Species b SET a.newspeciesid =b.speciesid WHERE a.newspcode=b.mnemonic;

-- Log the changes
SET @personnelid=1, @action="U", @changedcolumn="SpeciesID";
TRUNCATE TempLogFeeder; 
INSERT INTO TempLogFeeder (ChangedTableID,ChangedColumn,ChangeReason) 
  SELECT  treeid,@changedcolumn,"reason for updating these records" 
  FROM tempchangetreespecies;

CALL logRecordSet(@personnelid,"Tree",@action) ; 

-- Update the trees 
UPDATE Tree a,tempchangetreespecies b
 SET a.speciesid=b.newspeciesid
 WHERE a.tag=b.tag;

# *******************************************************************************************************
# Rebuild Views. Replace the first parameter with your database name 
# *******************************************************************************************************

CALL ctfsweb_webuser.CreateViewTaxonomy("blue","'foo'");
CALL ctfsweb_webuser.CreateFullView( "blue","ViewFullTable");

# *******************************************************************************************************
# ***** FINALLY CLEAN UP THE DATABASE BY REMOVING ALL THE TEMP TABLES YOU CREATED
# *******************************************************************************************************



-- Do this when you are done
# DROP TABLE IF EXISTS 

# *****************************************************************************************************

