#!/bin/bash

# Usage
# bash /file/path/ernest_mammal_lh_import mysqlusername

# Download the database file from Ecological Archives
wget http://esapubs.org/archive/ecol/E084/093/Mammal_lifehistories_v2.txt

# Create text file with table defintions and load data
# infile instructions to pass to mysql

cat >> mysqlcode.txt <<-EOA

-- create the database
DROP DATABASE IF EXISTS LIFEHIST_MAMMAL_ERNEST;
CREATE DATABASE LIFEHIST_MAMMAL_ERNEST;
USE LIFEHIST_MAMMAL_ERNEST;

CREATE TABLE mammal_lh (
	species_id INT(5) NOT NULL AUTO_INCREMENT,
	sporder CHAR(20),
	family CHAR(20),
	genus CHAR(20),
	species CHAR(20),
	mass DECIMAL(9,2),
	gestation_period DECIMAL(5,2),
	newborn_mass DECIMAL(9,2),
	wean_age DECIMAL(5,2),
	wean_mass DECIMAL(9,2),
	afr DECIMAL(5,2),
	max_lifespan DECIMAL(6,2),
	litter_size DECIMAL(5,2),
	litters_peryear DECIMAL(5,2),
	refs CHAR(30),
	PRIMARY KEY (species_id));

-- Load the data into the table
LOAD DATA LOCAL INFILE 'Mammal_lifehistories_v2.txt' 
	INTO TABLE mammal_lh FIELDS TERMINATED BY '\t'
    IGNORE 1 LINES
	(sporder, family, genus, species, mass,
	gestation_period, newborn_mass, wean_age,
	wean_mass, afr, max_lifespan,
	litter_size, litters_peryear, refs);

-- Remove extra lines at end of raw data file
DELETE FROM mammal_lh WHERE species_id > '1440';

EOA

# upload the file to mysql
mysql -u $1 -p <mysqlcode.txt

# delete raw data files
rm Mammal_lifehistories_v2.txt

echo 'Life history characteristics of placental non-volant mammals by S.K. Morgan Ernest successfully uploaded into MySQL. If you use this database in publications please cite it as follows: S.K. Morgan Ernest. 2003. Life history characteristics of placental non-volant mammals. Ecology 84:3402.'
exit 0