search

EDIorg / ecocomDP

A dataset design pattern and R package for ecological community data.
https://ediorg.github.io/ecocomDP/
Other
32 stars 13 forks source link

add the mysql implementation to repo #8

Open mobb opened 7 years ago

mobb commented 7 years ago

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 commented 3 years ago

You still want to do this @mobb ?