add the mysql implementation to repo

[mobb]

Text:

/*
Navicat MySQL Data Transfer

Source Server : localhost
Source Server Version : 50513
Source Host : localhost:3306
Source Database : ecocom_dp

Target Server Type : MYSQL
Target Server Version : 50513
File Encoding : 65001

Date: 2017-07-11 16:23:12
*/

SET FOREIGN_KEY_CHECKS=0;

---

-- Table structure for dataset_summary

---

DROP TABLE IF EXISTS dataset_summary;
CREATE TABLE dataset_summary (
dataset_summary_id int(11) NOT NULL ,
original_dataset_id varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
length_of_survey_years int(11) NULL DEFAULT NULL ,
number_of_years_sampled int(11) NULL DEFAULT NULL ,
std_dev_interval_betwe_years float NULL DEFAULT NULL ,
max_num_taxa int(11) NULL DEFAULT NULL ,
geo_extent_bounding_box_m2 float NULL DEFAULT NULL ,
PRIMARY KEY (dataset_summary_id)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='summary statistics to evaluate the usefluness of dataset'

;

---

-- Table structure for event

---

DROP TABLE IF EXISTS event;
CREATE TABLE event (
unique_id int(11) NOT NULL ,
event_id int(11) NOT NULL ,
variable_name varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
value varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
PRIMARY KEY (unique_id)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='info about a sampling event, eg, conditions, weather'

;

---

-- Table structure for observation

---

DROP TABLE IF EXISTS observation;
CREATE TABLE observation (
observation_id int(11) NOT NULL ,
event_id int(11) NOT NULL ,
dataset_summary_id int(11) NOT NULL ,
sampling_location_id varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
observation_datetime datetime NOT NULL ,
taxon_id varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
variable_name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
value float NOT NULL ,
unit varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
PRIMARY KEY (observation_id),
FOREIGN KEY (event_id) REFERENCES event (event_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (sampling_location_id) REFERENCES sampling_location (sampling_location_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (dataset_summary_id) REFERENCES dataset_summary (dataset_summary_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (taxon_id) REFERENCES taxon (taxon_id) ON DELETE RESTRICT ON UPDATE RESTRICT
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='table holds all the primary obs, with links'

;

---

-- Table structure for sampling_location

---

DROP TABLE IF EXISTS sampling_location;
CREATE TABLE sampling_location (
sampling_location_id varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
sampling_location_name varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
latitude float NULL DEFAULT NULL ,
longitude float NULL DEFAULT NULL ,
parent_sampling_location_id int(11) NULL DEFAULT NULL ,
PRIMARY KEY (sampling_location_id)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

---

-- Table structure for sampling_location_ancillary

---

DROP TABLE IF EXISTS sampling_location_ancillary;
CREATE TABLE sampling_location_ancillary (
sampling_location_ancillary_id int(11) NOT NULL ,
sampling_location_id varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
datetime datetime NOT NULL ,
variable_name varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
value varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
unit varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (sampling_location_ancillary_id),
FOREIGN KEY (sampling_location_id) REFERENCES sampling_location (sampling_location_id) ON DELETE RESTRICT ON UPDATE RESTRICT
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='info at each location during sampling'

;

---

-- Table structure for taxon

---

DROP TABLE IF EXISTS taxon;
CREATE TABLE taxon (
taxon_id varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
taxon_rank varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
taxon_name varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
authority_system varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
authority_taxon_id varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (taxon_id)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='taxonomic data'

;

---

-- Indexes structure for table event

---

CREATE INDEX event_id_index ON event(event_id) USING BTREE ;

---

-- Indexes structure for table observation

---

CREATE INDEX location_fk ON observation(sampling_location_id) USING BTREE ;
CREATE INDEX summary_fk ON observation(dataset_summary_id) USING BTREE ;
CREATE INDEX taxon_fk ON observation(taxon_id) USING BTREE ;
CREATE INDEX event_fk ON observation(event_id) USING BTREE ;

---

-- Indexes structure for table sampling_location_ancillary

---

CREATE INDEX location_ancillary_fk ON sampling_location_ancillary(sampling_location_id) USING BTREE ;
margarets-Mac-mini:Downloads mob$

[clnsmth]

You still want to do this @mobb ?