### Build a database from 4 text tables holding measurements (maindata.txt), species, genera, and families. 

CREATE DATABASE Sample;
USE Sample;

### Create a temporary table to hold all the main data in a single table: multiple censuses per individual, with date and size at each census
CREATE TABLE Measurements
(
 measureID INT AUTO_INCREMENT PRIMARY KEY,
 individualID INT,
 species VARCHAR(10),
 individual INT,
 census INT,
 date DATE,
 size INT,
 KEY(species),
 KEY(individual),
 KEY(individualID)
) TYPE=InnoDB;

LOAD DATA LOCAL INFILE "/home/condit/meetings_workshops/MysqlIntro/maindata.txt"
INTO TABLE Measurements IGNORE 1 LINES (species,individual,census,date,size);

### Create a table to hold the full species data. It will temporarily hold genus and family names, as this is the way the
# text file holds the species data. A speciesID is created when filled using auto increment.
CREATE TABLE Species
(
 speciesID INT AUTO_INCREMENT PRIMARY KEY,
 spcode VARCHAR(10),
 genus VARCHAR(75),
 genusID INT,
 speciesname VARCHAR(75),
 family VARCHAR(75),
 KEY(spcode),
 KEY(genus),
 KEY(family),
 KEY(genusID)
) TYPE=Innodb;

LOAD DATA LOCAL INFILE "/home/condit/meetings_workshops/MysqlIntro/Species.txt"
INTO TABLE Species IGNORE 1 LINES (spcode,genus,speciesname,family);

### Create a table to hold all the genera. It will have family ID eventually. A genusID is created when filled using auto increment.
CREATE TABLE Genus
(
 genusID INT AUTO_INCREMENT PRIMARY KEY,
 genus VARCHAR(75),
 familyID INT,
 KEY(genus),
 KEY(familyID)
) TYPE=Innodb;

LOAD DATA LOCAL INFILE "/home/condit/meetings_workshops/MysqlIntro/Genus.txt"
INTO TABLE Genus IGNORE 1 LINES (genus);

### Create a table to hold all the families. FamilyID is created when filled by auto increment. 
CREATE TABLE Family
(
 familyID INT AUTO_INCREMENT PRIMARY KEY,
 family VARCHAR(75),
 KEY(family)
) Type Innodb;

LOAD DATA LOCAL INFILE "/home/condit/meetings_workshops/MysqlIntro/Family.txt"
INTO TABLE Family IGNORE 1 LINES (family);

### Finalize the Species table by getting the genusID to replace the genus name
# SELECT * FROM Species JOIN Genus USING(genus);
UPDATE Species JOIN Genus USING(genus) SET Species.GenusID=Genus.GenusID;
# SELECT * FROM Species JOIN Genus USING(genus);

### Finalize the Genus table by getting the family for each from the Species table and the familyID from the Family table
# SELECT * FROM Genus JOIN Species USING(genusID) ;
# SELECT * FROM Genus JOIN Species USING(genusID) JOIN Family ON Family.family=Species.family ;
UPDATE Genus JOIN Species USING(genusID) JOIN Family ON Family.family=Species.family
SET Genus.familyID=Family.familyID;

### Must create a table of individuals by extracting distinct individuals from the Measurement table.
CREATE TABLE Individuals
(
 individualID INT AUTO_INCREMENT PRIMARY KEY,
 speciesID INT,
 individual INT,
 species VARCHAR(10),
 KEY(individual),
 KEY(species),
 KEY(speciesID)
) Type=Innodb;

INSERT INTO Individuals (individual,species)
SELECT DISTINCT individual,species FROM Measurements;

### Insert the speciesID into the Individuals table
# SELECT * FROM Individuals JOIN Species ON Individuals.species=Species.spcode ;
UPDATE Individuals JOIN Species ON Individuals.species=Species.spcode
SET Individuals.speciesID=Species.speciesID;

### And insert the new IndividualID into the Measurements table
# SELECT * FROM Measurements JOIN Individuals USING(individual);
UPDATE Measurements JOIN Individuals USING(individual)
SET Measurements.individualID=Individuals.individualID;

### Finally, delete superfluous fields
ALTER TABLE Measurements DROP COLUMN species, DROP COLUMN individual;
ALTER TABLE Individuals DROP COLUMN species;
ALTER TABLE Species DROP COLUMN Genus, DROP COLUMN Family;


### Query to recreate the main table

# SELECT spcode, individual, census, date, size
# FROM Species JOIN Individuals USING(speciesID)
#  JOIN Measurements ON Measurements.individualID=Individuals.individualID
# ORDER BY census,spcode,individual;

# SELECT family, genus, speciesname, spcode, individual, census, date, size
# FROM Species JOIN Individuals USING(speciesID)
#  JOIN Measurements ON Measurements.individualID=Individuals.individualID
#  JOIN Genus ON Genus.genusID=Species.genusID
#  JOIN Family ON Genus.familyID=Family.familyID
# ORDER BY census,spcode,individual;



