Revision 8928
Added by Aaron Marcuse-Kubitza over 11 years ago
| schemas/VegCore/VegCore.my.sql | ||
|---|---|---|
| 1 |
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; |
|
| 2 |
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; |
|
| 3 |
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; |
|
| 1 |
-- MySQL dump 10.11 |
|
| 2 |
-- |
|
| 3 |
-- Host: vegbiendev.nceas.ucsb.edu Database: VegCore |
|
| 4 |
-- ------------------------------------------------------ |
|
| 5 |
-- Server version 5.5.31-0ubuntu0.12.04.1 |
|
| 6 |
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; |
|
| 7 |
/*!40103 SET TIME_ZONE='+00:00' */; |
|
| 8 |
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; |
|
| 9 |
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; |
|
| 10 |
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO,POSTGRESQL' */; |
|
| 11 |
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; |
|
| 4 | 12 |
|
| 5 |
CREATE SCHEMA IF NOT EXISTS `VegCore` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin ; |
|
| 6 |
USE `VegCore` ; |
|
| 13 |
-- |
|
| 14 |
-- Table structure for table "aggregate_observation" |
|
| 15 |
-- |
|
| 7 | 16 |
|
| 8 |
-- ----------------------------------------------------- |
|
| 9 |
-- Table `source` |
|
| 10 |
-- ----------------------------------------------------- |
|
| 11 |
DROP TABLE IF EXISTS `source` ; |
|
| 17 |
DROP TABLE IF EXISTS "aggregate_observation"; |
|
| 18 |
CREATE TABLE "aggregate_observation" ( |
|
| 19 |
"id" varbinary(767) NOT NULL, |
|
| 20 |
"taxon_concept" varbinary(767) NOT NULL, |
|
| 21 |
"traits" set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
| 22 |
PRIMARY KEY ("id"),
|
|
| 23 |
KEY "fk_aggregate_observation_taxon_name1_idx" ("taxon_concept"),
|
|
| 24 |
CONSTRAINT "fk_aggregate_observation_taxon_name1" FOREIGN KEY ("taxon_concept") REFERENCES "taxon_concept" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 25 |
CONSTRAINT "fk_aggregate_observation_taxon_occurrence1" FOREIGN KEY ("id") REFERENCES "taxon_observation" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 26 |
); |
|
| 12 | 27 |
|
| 13 |
CREATE TABLE IF NOT EXISTS `source` ( |
|
| 14 |
`id` VARBINARY(767) NOT NULL , |
|
| 15 |
`parent` VARBINARY(767) NOT NULL , |
|
| 16 |
`name` VARBINARY(767) NOT NULL , |
|
| 17 |
`first_publisher` VARBINARY(767) NULL DEFAULT NULL , |
|
| 18 |
`owner` VARBINARY(767) NULL DEFAULT NULL , |
|
| 19 |
`info` SET('hstore') NULL ,
|
|
| 20 |
PRIMARY KEY (`id`) , |
|
| 21 |
INDEX `fk_source1_idx` (`parent` ASC) , |
|
| 22 |
INDEX `fk_source_party1_idx` (`owner` ASC) , |
|
| 23 |
UNIQUE INDEX `source_unique` (`parent` ASC, `name` ASC) , |
|
| 24 |
INDEX `fk_source_party2_idx` (`first_publisher` ASC) , |
|
| 25 |
CONSTRAINT `fk_source1` |
|
| 26 |
FOREIGN KEY (`parent` ) |
|
| 27 |
REFERENCES `source` (`id` ) |
|
| 28 |
ON DELETE CASCADE |
|
| 29 |
ON UPDATE CASCADE, |
|
| 30 |
CONSTRAINT `fk_source_party1` |
|
| 31 |
FOREIGN KEY (`owner` ) |
|
| 32 |
REFERENCES `party` (`id` ) |
|
| 33 |
ON DELETE CASCADE |
|
| 34 |
ON UPDATE CASCADE, |
|
| 35 |
CONSTRAINT `fk_source_party2` |
|
| 36 |
FOREIGN KEY (`first_publisher` ) |
|
| 37 |
REFERENCES `party` (`id` ) |
|
| 38 |
ON DELETE CASCADE |
|
| 39 |
ON UPDATE CASCADE) |
|
| 40 |
ENGINE = InnoDB |
|
| 41 |
DEFAULT CHARACTER SET = utf8 |
|
| 42 |
COLLATE = utf8_bin |
|
| 43 |
COMMENT = 'a \"reference [...] cited within the database\" (\"VegBank\":http://vegbank.org/vegbank/views/dba_tabledescription_detail.jsp?view=detail&wparam=reference&entity=dba_tabledescription&where=where_tablename)'; |
|
| 28 |
-- |
|
| 29 |
-- Dumping data for table "aggregate_observation" |
|
| 30 |
-- |
|
| 44 | 31 |
|
| 32 |
/*!40000 ALTER TABLE "aggregate_observation" DISABLE KEYS */; |
|
| 33 |
/*!40000 ALTER TABLE "aggregate_observation" ENABLE KEYS */; |
|
| 45 | 34 |
|
| 46 |
-- ----------------------------------------------------- |
|
| 47 |
-- Table `record` |
|
| 48 |
-- ----------------------------------------------------- |
|
| 49 |
DROP TABLE IF EXISTS `record` ; |
|
| 35 |
-- |
|
| 36 |
-- Table structure for table "base_class" |
|
| 37 |
-- |
|
| 50 | 38 |
|
| 51 |
CREATE TABLE IF NOT EXISTS `record` ( |
|
| 52 |
`id` VARBINARY(767) NOT NULL , |
|
| 53 |
`source` VARBINARY(767) NOT NULL , |
|
| 54 |
`source_id_scope` VARBINARY(767) NULL , |
|
| 55 |
`source_record_id` VARBINARY(767) NULL DEFAULT NULL , |
|
| 56 |
`info` SET('hstore') NULL ,
|
|
| 57 |
PRIMARY KEY (`id`) , |
|
| 58 |
INDEX `fk_record_source1_idx` (`source` ASC) , |
|
| 59 |
UNIQUE INDEX `record_unique` (`source` ASC, `source_id_scope` ASC, `source_record_id` ASC) , |
|
| 60 |
CONSTRAINT `fk_record_source1` |
|
| 61 |
FOREIGN KEY (`source` ) |
|
| 62 |
REFERENCES `source` (`id` ) |
|
| 63 |
ON DELETE CASCADE |
|
| 64 |
ON UPDATE CASCADE) |
|
| 65 |
ENGINE = InnoDB |
|
| 66 |
DEFAULT CHARACTER SET = utf8 |
|
| 67 |
COLLATE = utf8_bin; |
|
| 39 |
DROP TABLE IF EXISTS "base_class"; |
|
| 40 |
CREATE TABLE "base_class" ( |
|
| 41 |
"id" varbinary(767) NOT NULL, |
|
| 42 |
"referenced_class" varbinary(767) NOT NULL, |
|
| 43 |
PRIMARY KEY ("id"),
|
|
| 44 |
KEY "fk_base_class_referenced_class1_idx" ("referenced_class"),
|
|
| 45 |
CONSTRAINT "fk_base_class_referenced_class1" FOREIGN KEY ("referenced_class") REFERENCES "referenced_class" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 46 |
CONSTRAINT "fk_example_record1" FOREIGN KEY ("id") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 47 |
); |
|
| 68 | 48 |
|
| 49 |
-- |
|
| 50 |
-- Dumping data for table "base_class" |
|
| 51 |
-- |
|
| 69 | 52 |
|
| 70 |
-- ----------------------------------------------------- |
|
| 71 |
-- Table `party` |
|
| 72 |
-- ----------------------------------------------------- |
|
| 73 |
DROP TABLE IF EXISTS `party` ; |
|
| 53 |
/*!40000 ALTER TABLE "base_class" DISABLE KEYS */; |
|
| 54 |
/*!40000 ALTER TABLE "base_class" ENABLE KEYS */; |
|
| 74 | 55 |
|
| 75 |
CREATE TABLE IF NOT EXISTS `party` ( |
|
| 76 |
`id` VARBINARY(767) NOT NULL , |
|
| 77 |
`info` SET('hstore') NULL ,
|
|
| 78 |
PRIMARY KEY (`id`) , |
|
| 79 |
CONSTRAINT `fk_collection_source10` |
|
| 80 |
FOREIGN KEY (`id` ) |
|
| 81 |
REFERENCES `record` (`id` ) |
|
| 82 |
ON DELETE CASCADE |
|
| 83 |
ON UPDATE CASCADE) |
|
| 84 |
ENGINE = InnoDB |
|
| 85 |
DEFAULT CHARACTER SET = utf8 |
|
| 86 |
COLLATE = utf8_bin; |
|
| 56 |
-- |
|
| 57 |
-- Table structure for table "collection" |
|
| 58 |
-- |
|
| 87 | 59 |
|
| 60 |
DROP TABLE IF EXISTS "collection"; |
|
| 61 |
CREATE TABLE "collection" ( |
|
| 62 |
"id" varbinary(767) NOT NULL, |
|
| 63 |
"institution" varbinary(767) NOT NULL, |
|
| 64 |
"name" varbinary(767) NOT NULL, |
|
| 65 |
PRIMARY KEY ("id"),
|
|
| 66 |
UNIQUE KEY "collection_unique" ("institution","name"),
|
|
| 67 |
KEY "fk_collection_organization1_idx" ("institution"),
|
|
| 68 |
KEY "fk_collection_source1_idx" ("id"),
|
|
| 69 |
CONSTRAINT "fk_collection_organization1" FOREIGN KEY ("institution") REFERENCES "organization" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 70 |
CONSTRAINT "fk_collection_source1" FOREIGN KEY ("id") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 71 |
); |
|
| 88 | 72 |
|
| 89 |
-- ----------------------------------------------------- |
|
| 90 |
-- Table `relationship` |
|
| 91 |
-- ----------------------------------------------------- |
|
| 92 |
DROP TABLE IF EXISTS `relationship` ; |
|
| 73 |
-- |
|
| 74 |
-- Dumping data for table "collection" |
|
| 75 |
-- |
|
| 93 | 76 |
|
| 94 |
CREATE TABLE IF NOT EXISTS `relationship` ( |
|
| 95 |
`id` VARBINARY(767) NOT NULL , |
|
| 96 |
`record` VARBINARY(767) NOT NULL , |
|
| 97 |
`related_record` VARBINARY(767) NOT NULL , |
|
| 98 |
`info` SET('hstore') NULL ,
|
|
| 99 |
PRIMARY KEY (`id`) , |
|
| 100 |
INDEX `fk_relationship_record1_idx` (`record` ASC) , |
|
| 101 |
INDEX `fk_relationship_related_record_idx` (`related_record` ASC) , |
|
| 102 |
CONSTRAINT `fk_relationship_record1` |
|
| 103 |
FOREIGN KEY (`id` ) |
|
| 104 |
REFERENCES `record` (`id` ) |
|
| 105 |
ON DELETE CASCADE |
|
| 106 |
ON UPDATE CASCADE, |
|
| 107 |
CONSTRAINT `fk_relationship_record2` |
|
| 108 |
FOREIGN KEY (`record` ) |
|
| 109 |
REFERENCES `record` (`id` ) |
|
| 110 |
ON DELETE CASCADE |
|
| 111 |
ON UPDATE CASCADE, |
|
| 112 |
CONSTRAINT `fk_relationship_related_record` |
|
| 113 |
FOREIGN KEY (`related_record` ) |
|
| 114 |
REFERENCES `record` (`id` ) |
|
| 115 |
ON DELETE CASCADE |
|
| 116 |
ON UPDATE CASCADE) |
|
| 117 |
ENGINE = InnoDB |
|
| 118 |
DEFAULT CHARACTER SET = utf8 |
|
| 119 |
COLLATE = utf8_bin |
|
| 120 |
COMMENT = '\"information pertaining to relationships between resources (instances of data records, such as Occurrences, Taxa, Locations, Events)\" (\"DwC\":http://rs.tdwg.org/dwc/terms/#ResourceRelationship)'; |
|
| 77 |
/*!40000 ALTER TABLE "collection" DISABLE KEYS */; |
|
| 78 |
/*!40000 ALTER TABLE "collection" ENABLE KEYS */; |
|
| 121 | 79 |
|
| 80 |
-- |
|
| 81 |
-- Table structure for table "community" |
|
| 82 |
-- |
|
| 122 | 83 |
|
| 123 |
-- ----------------------------------------------------- |
|
| 124 |
-- Table `organization` |
|
| 125 |
-- ----------------------------------------------------- |
|
| 126 |
DROP TABLE IF EXISTS `organization` ; |
|
| 84 |
DROP TABLE IF EXISTS "community"; |
|
| 85 |
CREATE TABLE "community" ( |
|
| 86 |
"id" varbinary(767) NOT NULL, |
|
| 87 |
"name" varbinary(767) NOT NULL, |
|
| 88 |
"info" set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
| 89 |
PRIMARY KEY ("id"),
|
|
| 90 |
CONSTRAINT "fk_community_record1" FOREIGN KEY ("id") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 91 |
); |
|
| 127 | 92 |
|
| 128 |
CREATE TABLE IF NOT EXISTS `organization` ( |
|
| 129 |
`id` VARBINARY(767) NOT NULL , |
|
| 130 |
`info` SET('hstore') NULL ,
|
|
| 131 |
PRIMARY KEY (`id`) , |
|
| 132 |
CONSTRAINT `fk_organization_party1` |
|
| 133 |
FOREIGN KEY (`id` ) |
|
| 134 |
REFERENCES `party` (`id` ) |
|
| 135 |
ON DELETE CASCADE |
|
| 136 |
ON UPDATE CASCADE) |
|
| 137 |
ENGINE = InnoDB |
|
| 138 |
DEFAULT CHARACTER SET = utf8 |
|
| 139 |
COLLATE = utf8_bin; |
|
| 93 |
-- |
|
| 94 |
-- Dumping data for table "community" |
|
| 95 |
-- |
|
| 140 | 96 |
|
| 97 |
/*!40000 ALTER TABLE "community" DISABLE KEYS */; |
|
| 98 |
/*!40000 ALTER TABLE "community" ENABLE KEYS */; |
|
| 141 | 99 |
|
| 142 |
-- ----------------------------------------------------- |
|
| 143 |
-- Table `collection` |
|
| 144 |
-- ----------------------------------------------------- |
|
| 145 |
DROP TABLE IF EXISTS `collection` ; |
|
| 100 |
-- |
|
| 101 |
-- Table structure for table "coordinates" |
|
| 102 |
-- |
|
| 146 | 103 |
|
| 147 |
CREATE TABLE IF NOT EXISTS `collection` ( |
|
| 148 |
`id` VARBINARY(767) NOT NULL , |
|
| 149 |
`institution` VARBINARY(767) NOT NULL , |
|
| 150 |
`name` VARBINARY(767) NOT NULL , |
|
| 151 |
INDEX `fk_collection_organization1_idx` (`institution` ASC) , |
|
| 152 |
UNIQUE INDEX `collection_unique` (`institution` ASC, `name` ASC) , |
|
| 153 |
INDEX `fk_collection_source1_idx` (`id` ASC) , |
|
| 154 |
PRIMARY KEY (`id`) , |
|
| 155 |
CONSTRAINT `fk_collection_source1` |
|
| 156 |
FOREIGN KEY (`id` ) |
|
| 157 |
REFERENCES `record` (`id` ) |
|
| 158 |
ON DELETE CASCADE |
|
| 159 |
ON UPDATE CASCADE, |
|
| 160 |
CONSTRAINT `fk_collection_organization1` |
|
| 161 |
FOREIGN KEY (`institution` ) |
|
| 162 |
REFERENCES `organization` (`id` ) |
|
| 163 |
ON DELETE CASCADE |
|
| 164 |
ON UPDATE CASCADE) |
|
| 165 |
ENGINE = InnoDB |
|
| 166 |
DEFAULT CHARACTER SET = utf8 |
|
| 167 |
COLLATE = utf8_bin |
|
| 168 |
COMMENT = '\"the collection within the institution where a specimen is held\" (\"Brad Boyle\":https://projects.nceas.ucsb.edu/nceas/attachments/download/621/vegbien_identifier_examples.xlsx#terms/collectionCode)'; |
|
| 104 |
DROP TABLE IF EXISTS "coordinates"; |
|
| 105 |
CREATE TABLE "coordinates" ( |
|
| 106 |
"id" varbinary(767) NOT NULL, |
|
| 107 |
"latitude_deg" varbinary(767) DEFAULT NULL, |
|
| 108 |
"longitude_deg" varbinary(767) DEFAULT NULL, |
|
| 109 |
PRIMARY KEY ("id")
|
|
| 110 |
); |
|
| 169 | 111 |
|
| 112 |
-- |
|
| 113 |
-- Dumping data for table "coordinates" |
|
| 114 |
-- |
|
| 170 | 115 |
|
| 171 |
-- ----------------------------------------------------- |
|
| 172 |
-- Table `taxon_assertion` |
|
| 173 |
-- ----------------------------------------------------- |
|
| 174 |
DROP TABLE IF EXISTS `taxon_assertion` ; |
|
| 116 |
/*!40000 ALTER TABLE "coordinates" DISABLE KEYS */; |
|
| 117 |
/*!40000 ALTER TABLE "coordinates" ENABLE KEYS */; |
|
| 175 | 118 |
|
| 176 |
CREATE TABLE IF NOT EXISTS `taxon_assertion` ( |
|
| 177 |
`id` VARBINARY(767) NOT NULL , |
|
| 178 |
`string` VARBINARY(767) NOT NULL , |
|
| 179 |
`taxon` VARBINARY(767) NULL DEFAULT NULL , |
|
| 180 |
`cf_aff` VARBINARY(767) NULL DEFAULT NULL , |
|
| 181 |
`annotations` SET('hstore') NULL ,
|
|
| 182 |
PRIMARY KEY (`id`) , |
|
| 183 |
INDEX `fk_taxon_assertion_taxon_string1_idx` (`string` ASC) , |
|
| 184 |
INDEX `fk_taxon_assertion_taxon_name1_idx` (`taxon` ASC) , |
|
| 185 |
CONSTRAINT `fk_qualified_taxon_record1` |
|
| 186 |
FOREIGN KEY (`id` ) |
|
| 187 |
REFERENCES `record` (`id` ) |
|
| 188 |
ON DELETE CASCADE |
|
| 189 |
ON UPDATE CASCADE, |
|
| 190 |
CONSTRAINT `fk_taxon_assertion_taxon_string1` |
|
| 191 |
FOREIGN KEY (`string` ) |
|
| 192 |
REFERENCES `taxon_string` (`string` ) |
|
| 193 |
ON DELETE CASCADE |
|
| 194 |
ON UPDATE CASCADE, |
|
| 195 |
CONSTRAINT `fk_taxon_assertion_taxon_name1` |
|
| 196 |
FOREIGN KEY (`taxon` ) |
|
| 197 |
REFERENCES `taxon_name` (`id` ) |
|
| 198 |
ON DELETE CASCADE |
|
| 199 |
ON UPDATE CASCADE) |
|
| 200 |
ENGINE = InnoDB |
|
| 201 |
DEFAULT CHARACTER SET = utf8 |
|
| 202 |
COLLATE = utf8_bin; |
|
| 119 |
-- |
|
| 120 |
-- Table structure for table "derived_class" |
|
| 121 |
-- |
|
| 203 | 122 |
|
| 123 |
DROP TABLE IF EXISTS "derived_class"; |
|
| 124 |
CREATE TABLE "derived_class" ( |
|
| 125 |
"id" varbinary(767) NOT NULL, |
|
| 126 |
PRIMARY KEY ("id"),
|
|
| 127 |
CONSTRAINT "fk_derived_class_base_class1" FOREIGN KEY ("id") REFERENCES "base_class" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 128 |
); |
|
| 204 | 129 |
|
| 205 |
-- ----------------------------------------------------- |
|
| 206 |
-- Table `taxon_concept` |
|
| 207 |
-- ----------------------------------------------------- |
|
| 208 |
DROP TABLE IF EXISTS `taxon_concept` ; |
|
| 130 |
-- |
|
| 131 |
-- Dumping data for table "derived_class" |
|
| 132 |
-- |
|
| 209 | 133 |
|
| 210 |
CREATE TABLE IF NOT EXISTS `taxon_concept` ( |
|
| 211 |
`id` VARBINARY(767) NOT NULL , |
|
| 212 |
`according_to` VARBINARY(767) NOT NULL , |
|
| 213 |
`parent` VARBINARY(767) NOT NULL , |
|
| 214 |
`accepted_taxon_concept` VARBINARY(767) NULL DEFAULT NULL , |
|
| 215 |
PRIMARY KEY (`id`) , |
|
| 216 |
INDEX `fk_taxon_taxon1_idx` (`parent` ASC) , |
|
| 217 |
INDEX `fk_taxon_concept_source1_idx` (`according_to` ASC) , |
|
| 218 |
INDEX `fk_taxon_concept_taxon_concept1_idx` (`accepted_taxon_concept` ASC) , |
|
| 219 |
UNIQUE INDEX `taxon_concept_unique_name` (`according_to` ASC) , |
|
| 220 |
CONSTRAINT `fk_taxon_taxon1` |
|
| 221 |
FOREIGN KEY (`parent` ) |
|
| 222 |
REFERENCES `taxon_concept` (`id` ) |
|
| 223 |
ON DELETE CASCADE |
|
| 224 |
ON UPDATE CASCADE, |
|
| 225 |
CONSTRAINT `fk_taxon_concept_source1` |
|
| 226 |
FOREIGN KEY (`according_to` ) |
|
| 227 |
REFERENCES `source` (`id` ) |
|
| 228 |
ON DELETE CASCADE |
|
| 229 |
ON UPDATE CASCADE, |
|
| 230 |
CONSTRAINT `fk_taxon_concept_taxon_concept1` |
|
| 231 |
FOREIGN KEY (`accepted_taxon_concept` ) |
|
| 232 |
REFERENCES `taxon_concept` (`id` ) |
|
| 233 |
ON DELETE CASCADE |
|
| 234 |
ON UPDATE CASCADE, |
|
| 235 |
CONSTRAINT `fk_taxon_concept_taxon_name1` |
|
| 236 |
FOREIGN KEY (`id` ) |
|
| 237 |
REFERENCES `taxon_name` (`id` ) |
|
| 238 |
ON DELETE CASCADE |
|
| 239 |
ON UPDATE CASCADE) |
|
| 240 |
ENGINE = InnoDB |
|
| 241 |
DEFAULT CHARACTER SET = utf8 |
|
| 242 |
COLLATE = utf8_bin |
|
| 243 |
COMMENT = 'A [[VegCore#Taxon|Taxon]] described by a specific [[VegCore#Reference|Reference]]'; |
|
| 134 |
/*!40000 ALTER TABLE "derived_class" DISABLE KEYS */; |
|
| 135 |
/*!40000 ALTER TABLE "derived_class" ENABLE KEYS */; |
|
| 244 | 136 |
|
| 137 |
-- |
|
| 138 |
-- Table structure for table "event" |
|
| 139 |
-- |
|
| 245 | 140 |
|
| 246 |
-- ----------------------------------------------------- |
|
| 247 |
-- Table `parsed_taxon_assertion` |
|
| 248 |
-- ----------------------------------------------------- |
|
| 249 |
DROP TABLE IF EXISTS `parsed_taxon_assertion` ; |
|
| 141 |
DROP TABLE IF EXISTS "event"; |
|
| 142 |
CREATE TABLE "event" ( |
|
| 143 |
"id" varbinary(767) NOT NULL, |
|
| 144 |
"parent" varbinary(767) NOT NULL, |
|
| 145 |
"name" varbinary(767) DEFAULT NULL, |
|
| 146 |
"date_range" varbinary(767) DEFAULT NULL, |
|
| 147 |
"place" varbinary(767) DEFAULT NULL, |
|
| 148 |
"method" varbinary(767) DEFAULT NULL, |
|
| 149 |
PRIMARY KEY ("id"),
|
|
| 150 |
KEY "fk_event_place1_idx" ("place"),
|
|
| 151 |
KEY "fk_event1_idx" ("parent"),
|
|
| 152 |
KEY "fk_event_method1_idx" ("method"),
|
|
| 153 |
CONSTRAINT "fk_event1" FOREIGN KEY ("parent") REFERENCES "event" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 154 |
CONSTRAINT "fk_event_method1" FOREIGN KEY ("method") REFERENCES "method" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 155 |
CONSTRAINT "fk_event_place1" FOREIGN KEY ("place") REFERENCES "place" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 156 |
CONSTRAINT "fk_event_record1" FOREIGN KEY ("id") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 157 |
); |
|
| 250 | 158 |
|
| 251 |
CREATE TABLE IF NOT EXISTS `parsed_taxon_assertion` ( |
|
| 252 |
`id` VARBINARY(767) NOT NULL , |
|
| 253 |
`matched_taxon_concept` VARBINARY(767) NULL DEFAULT NULL , |
|
| 254 |
`match_score` FLOAT NULL , |
|
| 255 |
`match_info` SET('hstore') NULL ,
|
|
| 256 |
PRIMARY KEY (`id`) , |
|
| 257 |
INDEX `fk_parsed_taxon_assertion_taxon_name1_idx` (`matched_taxon_concept` ASC) , |
|
| 258 |
CONSTRAINT `fk_matched_taxon_qualified_taxon10` |
|
| 259 |
FOREIGN KEY (`id` ) |
|
| 260 |
REFERENCES `taxon_assertion` (`id` ) |
|
| 261 |
ON DELETE CASCADE |
|
| 262 |
ON UPDATE CASCADE, |
|
| 263 |
CONSTRAINT `fk_parsed_taxon_assertion_taxon_name1` |
|
| 264 |
FOREIGN KEY (`matched_taxon_concept` ) |
|
| 265 |
REFERENCES `taxon_concept` (`id` ) |
|
| 266 |
ON DELETE CASCADE |
|
| 267 |
ON UPDATE CASCADE) |
|
| 268 |
ENGINE = InnoDB |
|
| 269 |
DEFAULT CHARACTER SET = utf8 |
|
| 270 |
COLLATE = utf8_bin; |
|
| 159 |
-- |
|
| 160 |
-- Dumping data for table "event" |
|
| 161 |
-- |
|
| 271 | 162 |
|
| 163 |
/*!40000 ALTER TABLE "event" DISABLE KEYS */; |
|
| 164 |
/*!40000 ALTER TABLE "event" ENABLE KEYS */; |
|
| 272 | 165 |
|
| 273 |
-- ----------------------------------------------------- |
|
| 274 |
-- Table `taxon_string` |
|
| 275 |
-- ----------------------------------------------------- |
|
| 276 |
DROP TABLE IF EXISTS `taxon_string` ; |
|
| 166 |
-- |
|
| 167 |
-- Table structure for table "event_participant" |
|
| 168 |
-- |
|
| 277 | 169 |
|
| 278 |
CREATE TABLE IF NOT EXISTS `taxon_string` ( |
|
| 279 |
`string` VARBINARY(767) NOT NULL , |
|
| 280 |
`parsed_taxon_assertion` VARBINARY(767) NULL DEFAULT NULL , |
|
| 281 |
PRIMARY KEY (`string`) , |
|
| 282 |
INDEX `fk_taxon_string_parsed_taxon_assertion1_idx` (`parsed_taxon_assertion` ASC) , |
|
| 283 |
CONSTRAINT `fk_taxon_string_parsed_taxon_assertion1` |
|
| 284 |
FOREIGN KEY (`parsed_taxon_assertion` ) |
|
| 285 |
REFERENCES `parsed_taxon_assertion` (`id` ) |
|
| 286 |
ON DELETE CASCADE |
|
| 287 |
ON UPDATE CASCADE) |
|
| 288 |
ENGINE = InnoDB |
|
| 289 |
DEFAULT CHARACTER SET = utf8 |
|
| 290 |
COLLATE = utf8_bin; |
|
| 170 |
DROP TABLE IF EXISTS "event_participant"; |
|
| 171 |
CREATE TABLE "event_participant" ( |
|
| 172 |
"event" varbinary(767) NOT NULL, |
|
| 173 |
"party" varbinary(767) NOT NULL, |
|
| 174 |
"sort_order" int(11) DEFAULT NULL, |
|
| 175 |
PRIMARY KEY ("event","party"),
|
|
| 176 |
KEY "fk_event_has_party_party1_idx" ("party"),
|
|
| 177 |
KEY "fk_event_has_party_event1_idx" ("event"),
|
|
| 178 |
CONSTRAINT "fk_event_has_party_event1" FOREIGN KEY ("event") REFERENCES "event" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 179 |
CONSTRAINT "fk_event_has_party_party1" FOREIGN KEY ("party") REFERENCES "party" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 180 |
); |
|
| 291 | 181 |
|
| 182 |
-- |
|
| 183 |
-- Dumping data for table "event_participant" |
|
| 184 |
-- |
|
| 292 | 185 |
|
| 293 |
-- ----------------------------------------------------- |
|
| 294 |
-- Table `taxon_name` |
|
| 295 |
-- ----------------------------------------------------- |
|
| 296 |
DROP TABLE IF EXISTS `taxon_name` ; |
|
| 186 |
/*!40000 ALTER TABLE "event_participant" DISABLE KEYS */; |
|
| 187 |
/*!40000 ALTER TABLE "event_participant" ENABLE KEYS */; |
|
| 297 | 188 |
|
| 298 |
CREATE TABLE IF NOT EXISTS `taxon_name` ( |
|
| 299 |
`id` VARBINARY(767) NOT NULL , |
|
| 300 |
`unique_name` VARBINARY(767) NOT NULL , |
|
| 301 |
`formal_name` VARBINARY(767) NULL DEFAULT NULL , |
|
| 302 |
`taxon_name` VARBINARY(767) NULL DEFAULT NULL , |
|
| 303 |
`author` VARBINARY(767) NULL DEFAULT NULL , |
|
| 304 |
`common_name` VARBINARY(767) NULL DEFAULT NULL , |
|
| 305 |
`rank` VARBINARY(767) NULL DEFAULT NULL , |
|
| 306 |
PRIMARY KEY (`id`) , |
|
| 307 |
INDEX `fk_taxon_concept_taxon_string10_idx` (`unique_name` ASC) , |
|
| 308 |
CONSTRAINT `fk_taxon_record10` |
|
| 309 |
FOREIGN KEY (`id` ) |
|
| 310 |
REFERENCES `record` (`id` ) |
|
| 311 |
ON DELETE CASCADE |
|
| 312 |
ON UPDATE CASCADE, |
|
| 313 |
CONSTRAINT `fk_taxon_concept_taxon_string10` |
|
| 314 |
FOREIGN KEY (`unique_name` ) |
|
| 315 |
REFERENCES `taxon_string` (`string` ) |
|
| 316 |
ON DELETE CASCADE |
|
| 317 |
ON UPDATE CASCADE) |
|
| 318 |
ENGINE = InnoDB |
|
| 319 |
DEFAULT CHARACTER SET = utf8 |
|
| 320 |
COLLATE = utf8_bin; |
|
| 189 |
-- |
|
| 190 |
-- Table structure for table "geological_context" |
|
| 191 |
-- |
|
| 321 | 192 |
|
| 193 |
DROP TABLE IF EXISTS "geological_context"; |
|
| 194 |
CREATE TABLE "geological_context" ( |
|
| 195 |
"id" varbinary(767) NOT NULL, |
|
| 196 |
"name" varbinary(767) NOT NULL, |
|
| 197 |
"info" set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
| 198 |
PRIMARY KEY ("id"),
|
|
| 199 |
CONSTRAINT "fk_geological_context_record1" FOREIGN KEY ("id") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 200 |
); |
|
| 322 | 201 |
|
| 323 |
-- ----------------------------------------------------- |
|
| 324 |
-- Table `coordinates` |
|
| 325 |
-- ----------------------------------------------------- |
|
| 326 |
DROP TABLE IF EXISTS `coordinates` ; |
|
| 202 |
-- |
|
| 203 |
-- Dumping data for table "geological_context" |
|
| 204 |
-- |
|
| 327 | 205 |
|
| 328 |
CREATE TABLE IF NOT EXISTS `coordinates` ( |
|
| 329 |
`id` VARBINARY(767) NOT NULL , |
|
| 330 |
`latitude_deg` VARBINARY(767) NULL DEFAULT NULL , |
|
| 331 |
`longitude_deg` VARBINARY(767) NULL DEFAULT NULL , |
|
| 332 |
PRIMARY KEY (`id`) ) |
|
| 333 |
ENGINE = InnoDB |
|
| 334 |
DEFAULT CHARACTER SET = utf8 |
|
| 335 |
COLLATE = utf8_bin |
|
| 336 |
COMMENT = 'A numerically-defined point'; |
|
| 206 |
/*!40000 ALTER TABLE "geological_context" DISABLE KEYS */; |
|
| 207 |
/*!40000 ALTER TABLE "geological_context" ENABLE KEYS */; |
|
| 337 | 208 |
|
| 209 |
-- |
|
| 210 |
-- Table structure for table "geovalidation" |
|
| 211 |
-- |
|
| 338 | 212 |
|
| 339 |
-- ----------------------------------------------------- |
|
| 340 |
-- Table `place_path` |
|
| 341 |
-- ----------------------------------------------------- |
|
| 342 |
DROP TABLE IF EXISTS `place_path` ; |
|
| 213 |
DROP TABLE IF EXISTS "geovalidation"; |
|
| 214 |
CREATE TABLE "geovalidation" ( |
|
| 215 |
"id" varbinary(767) NOT NULL, |
|
| 216 |
"geovalid" tinyint(1) NOT NULL, |
|
| 217 |
"lat_long_domain_valid" tinyint(1) NOT NULL, |
|
| 218 |
"lat_long_in_ranks" set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
| 219 |
PRIMARY KEY ("id"),
|
|
| 220 |
CONSTRAINT "fk_geovalidation_validatable_place1" FOREIGN KEY ("id") REFERENCES "validatable_place" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 221 |
); |
|
| 343 | 222 |
|
| 344 |
CREATE TABLE IF NOT EXISTS `place_path` ( |
|
| 345 |
`id` VARBINARY(767) NOT NULL , |
|
| 346 |
`continent` VARBINARY(767) NULL DEFAULT NULL , |
|
| 347 |
`country` VARBINARY(767) NULL DEFAULT NULL , |
|
| 348 |
`state_province` VARBINARY(767) NULL DEFAULT NULL , |
|
| 349 |
`county` VARBINARY(767) NULL DEFAULT NULL , |
|
| 350 |
`municipality` VARBINARY(767) NULL DEFAULT NULL , |
|
| 351 |
`ranks` SET('hstore') NULL ,
|
|
| 352 |
PRIMARY KEY (`id`) ) |
|
| 353 |
ENGINE = InnoDB |
|
| 354 |
DEFAULT CHARACTER SET = utf8 |
|
| 355 |
COLLATE = utf8_bin |
|
| 356 |
COMMENT = 'A named region'; |
|
| 223 |
-- |
|
| 224 |
-- Dumping data for table "geovalidation" |
|
| 225 |
-- |
|
| 357 | 226 |
|
| 227 |
/*!40000 ALTER TABLE "geovalidation" DISABLE KEYS */; |
|
| 228 |
/*!40000 ALTER TABLE "geovalidation" ENABLE KEYS */; |
|
| 358 | 229 |
|
| 359 |
-- ----------------------------------------------------- |
|
| 360 |
-- Table `place` |
|
| 361 |
-- ----------------------------------------------------- |
|
| 362 |
DROP TABLE IF EXISTS `place` ; |
|
| 230 |
-- |
|
| 231 |
-- Table structure for table "individual" |
|
| 232 |
-- |
|
| 363 | 233 |
|
| 364 |
CREATE TABLE IF NOT EXISTS `place` ( |
|
| 365 |
`id` VARBINARY(767) NOT NULL , |
|
| 366 |
`parent` VARBINARY(767) NOT NULL , |
|
| 367 |
`coordinates` VARBINARY(767) NULL DEFAULT NULL , |
|
| 368 |
`path` VARBINARY(767) NULL DEFAULT NULL , |
|
| 369 |
`locality` VARBINARY(767) NULL DEFAULT NULL , |
|
| 370 |
PRIMARY KEY (`id`) , |
|
| 371 |
INDEX `fk_place_coordinates1_idx` (`coordinates` ASC) , |
|
| 372 |
INDEX `fk_place1_idx` (`parent` ASC) , |
|
| 373 |
INDEX `fk_place_place_path1_idx` (`path` ASC) , |
|
| 374 |
CONSTRAINT `fk_place_record1` |
|
| 375 |
FOREIGN KEY (`id` ) |
|
| 376 |
REFERENCES `record` (`id` ) |
|
| 377 |
ON DELETE CASCADE |
|
| 378 |
ON UPDATE CASCADE, |
|
| 379 |
CONSTRAINT `fk_place_coordinates1` |
|
| 380 |
FOREIGN KEY (`coordinates` ) |
|
| 381 |
REFERENCES `coordinates` (`id` ) |
|
| 382 |
ON DELETE CASCADE |
|
| 383 |
ON UPDATE CASCADE, |
|
| 384 |
CONSTRAINT `fk_place1` |
|
| 385 |
FOREIGN KEY (`parent` ) |
|
| 386 |
REFERENCES `place` (`id` ) |
|
| 387 |
ON DELETE CASCADE |
|
| 388 |
ON UPDATE CASCADE, |
|
| 389 |
CONSTRAINT `fk_place_place_path1` |
|
| 390 |
FOREIGN KEY (`path` ) |
|
| 391 |
REFERENCES `place_path` (`id` ) |
|
| 392 |
ON DELETE CASCADE |
|
| 393 |
ON UPDATE CASCADE) |
|
| 394 |
ENGINE = InnoDB |
|
| 395 |
DEFAULT CHARACTER SET = utf8 |
|
| 396 |
COLLATE = utf8_bin |
|
| 397 |
COMMENT = '\"A spatial region\" (\"DwC\":http://rs.tdwg.org/dwc/terms/#dcterms:Location) or point'; |
|
| 234 |
DROP TABLE IF EXISTS "individual"; |
|
| 235 |
CREATE TABLE "individual" ( |
|
| 236 |
"id" varbinary(767) NOT NULL, |
|
| 237 |
"tag" varbinary(767) DEFAULT NULL, |
|
| 238 |
PRIMARY KEY ("id"),
|
|
| 239 |
CONSTRAINT "fk_individual_record1" FOREIGN KEY ("id") REFERENCES "taxon_occurrence" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 240 |
); |
|
| 398 | 241 |
|
| 242 |
-- |
|
| 243 |
-- Dumping data for table "individual" |
|
| 244 |
-- |
|
| 399 | 245 |
|
| 400 |
-- ----------------------------------------------------- |
|
| 401 |
-- Table `method` |
|
| 402 |
-- ----------------------------------------------------- |
|
| 403 |
DROP TABLE IF EXISTS `method` ; |
|
| 246 |
/*!40000 ALTER TABLE "individual" DISABLE KEYS */; |
|
| 247 |
/*!40000 ALTER TABLE "individual" ENABLE KEYS */; |
|
| 404 | 248 |
|
| 405 |
CREATE TABLE IF NOT EXISTS `method` ( |
|
| 406 |
`id` VARBINARY(767) NOT NULL , |
|
| 407 |
`parent` VARBINARY(767) NOT NULL , |
|
| 408 |
`info` SET('hstore') NULL ,
|
|
| 409 |
PRIMARY KEY (`id`) , |
|
| 410 |
INDEX `fk_method_method1_idx` (`parent` ASC) , |
|
| 411 |
CONSTRAINT `fk_method_record1` |
|
| 412 |
FOREIGN KEY (`id` ) |
|
| 413 |
REFERENCES `record` (`id` ) |
|
| 414 |
ON DELETE CASCADE |
|
| 415 |
ON UPDATE CASCADE, |
|
| 416 |
CONSTRAINT `fk_method_method1` |
|
| 417 |
FOREIGN KEY (`parent` ) |
|
| 418 |
REFERENCES `method` (`id` ) |
|
| 419 |
ON DELETE CASCADE |
|
| 420 |
ON UPDATE CASCADE) |
|
| 421 |
ENGINE = InnoDB |
|
| 422 |
DEFAULT CHARACTER SET = utf8 |
|
| 423 |
COLLATE = utf8_bin |
|
| 424 |
COMMENT = '\"A specific method definition followed in the creation of the dataset. Each method links to a protocol and literature citation reference. A protocol may have many method or steps.\" (\"VegX\":http://wiki.tdwg.org/twiki/pub/Vegetation/WebHome/VegX_Schema_1.5.3_proposed.zip#/veg.xsd#/methods/method)'; |
|
| 249 |
-- |
|
| 250 |
-- Table structure for table "individual_observation" |
|
| 251 |
-- |
|
| 425 | 252 |
|
| 253 |
DROP TABLE IF EXISTS "individual_observation"; |
|
| 254 |
CREATE TABLE "individual_observation" ( |
|
| 255 |
"id" varbinary(767) NOT NULL, |
|
| 256 |
"individual" varbinary(767) DEFAULT NULL, |
|
| 257 |
"code" varbinary(767) DEFAULT NULL, |
|
| 258 |
"traits" set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
| 259 |
PRIMARY KEY ("id"),
|
|
| 260 |
KEY "fk_individual_observation_individual1_idx" ("individual"),
|
|
| 261 |
CONSTRAINT "fk_individual_observation_individual1" FOREIGN KEY ("individual") REFERENCES "individual" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 262 |
CONSTRAINT "fk_individual_observation_taxon_occurrence1" FOREIGN KEY ("id") REFERENCES "taxon_observation" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 263 |
); |
|
| 426 | 264 |
|
| 427 |
-- ----------------------------------------------------- |
|
| 428 |
-- Table `event` |
|
| 429 |
-- ----------------------------------------------------- |
|
| 430 |
DROP TABLE IF EXISTS `event` ; |
|
| 265 |
-- |
|
| 266 |
-- Dumping data for table "individual_observation" |
|
| 267 |
-- |
|
| 431 | 268 |
|
| 432 |
CREATE TABLE IF NOT EXISTS `event` ( |
|
| 433 |
`id` VARBINARY(767) NOT NULL , |
|
| 434 |
`parent` VARBINARY(767) NOT NULL , |
|
| 435 |
`name` VARBINARY(767) NULL DEFAULT NULL , |
|
| 436 |
`date_range` VARBINARY(767) NULL DEFAULT NULL , |
|
| 437 |
`place` VARBINARY(767) NULL DEFAULT NULL , |
|
| 438 |
`method` VARBINARY(767) NULL DEFAULT NULL , |
|
| 439 |
PRIMARY KEY (`id`) , |
|
| 440 |
INDEX `fk_event_place1_idx` (`place` ASC) , |
|
| 441 |
INDEX `fk_event1_idx` (`parent` ASC) , |
|
| 442 |
INDEX `fk_event_method1_idx` (`method` ASC) , |
|
| 443 |
CONSTRAINT `fk_event_record1` |
|
| 444 |
FOREIGN KEY (`id` ) |
|
| 445 |
REFERENCES `record` (`id` ) |
|
| 446 |
ON DELETE CASCADE |
|
| 447 |
ON UPDATE CASCADE, |
|
| 448 |
CONSTRAINT `fk_event_place1` |
|
| 449 |
FOREIGN KEY (`place` ) |
|
| 450 |
REFERENCES `place` (`id` ) |
|
| 451 |
ON DELETE CASCADE |
|
| 452 |
ON UPDATE CASCADE, |
|
| 453 |
CONSTRAINT `fk_event1` |
|
| 454 |
FOREIGN KEY (`parent` ) |
|
| 455 |
REFERENCES `event` (`id` ) |
|
| 456 |
ON DELETE CASCADE |
|
| 457 |
ON UPDATE CASCADE, |
|
| 458 |
CONSTRAINT `fk_event_method1` |
|
| 459 |
FOREIGN KEY (`method` ) |
|
| 460 |
REFERENCES `method` (`id` ) |
|
| 461 |
ON DELETE CASCADE |
|
| 462 |
ON UPDATE CASCADE) |
|
| 463 |
ENGINE = InnoDB |
|
| 464 |
DEFAULT CHARACTER SET = utf8 |
|
| 465 |
COLLATE = utf8_bin |
|
| 466 |
COMMENT = '\"an action that occurs at a place and during a period of time\" (\"DwC\":http://rs.tdwg.org/dwc/terms/#Event)'; |
|
| 269 |
/*!40000 ALTER TABLE "individual_observation" DISABLE KEYS */; |
|
| 270 |
/*!40000 ALTER TABLE "individual_observation" ENABLE KEYS */; |
|
| 467 | 271 |
|
| 272 |
-- |
|
| 273 |
-- Table structure for table "method" |
|
| 274 |
-- |
|
| 468 | 275 |
|
| 469 |
-- ----------------------------------------------------- |
|
| 470 |
-- Table `specimen` |
|
| 471 |
-- ----------------------------------------------------- |
|
| 472 |
DROP TABLE IF EXISTS `specimen` ; |
|
| 276 |
DROP TABLE IF EXISTS "method"; |
|
| 277 |
CREATE TABLE "method" ( |
|
| 278 |
"id" varbinary(767) NOT NULL, |
|
| 279 |
"parent" varbinary(767) NOT NULL, |
|
| 280 |
"info" set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
| 281 |
PRIMARY KEY ("id"),
|
|
| 282 |
KEY "fk_method_method1_idx" ("parent"),
|
|
| 283 |
CONSTRAINT "fk_method_method1" FOREIGN KEY ("parent") REFERENCES "method" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 284 |
CONSTRAINT "fk_method_record1" FOREIGN KEY ("id") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 285 |
); |
|
| 473 | 286 |
|
| 474 |
CREATE TABLE IF NOT EXISTS `specimen` ( |
|
| 475 |
`id` VARBINARY(767) NOT NULL , |
|
| 476 |
`individual` VARBINARY(767) NULL , |
|
| 477 |
`code_in_individual` VARBINARY(767) NULL , |
|
| 478 |
`collection_event` VARBINARY(767) NULL , |
|
| 479 |
`orig_collection` VARBINARY(767) NULL DEFAULT NULL , |
|
| 480 |
`barcode` VARBINARY(767) NULL DEFAULT NULL , |
|
| 481 |
`accession_number` VARBINARY(767) NULL DEFAULT NULL , |
|
| 482 |
`current_collection` VARBINARY(767) NULL , |
|
| 483 |
`owner_collection` VARBINARY(767) NULL , |
|
| 484 |
PRIMARY KEY (`id`) , |
|
| 485 |
INDEX `fk_specimen_collection1_idx` (`orig_collection` ASC) , |
|
| 486 |
INDEX `fk_specimen_taxon_observation1_idx` (`collection_event` ASC) , |
|
| 487 |
INDEX `fk_specimen_individual1_idx` (`individual` ASC) , |
|
| 488 |
INDEX `fk_specimen_collection2_idx` (`current_collection` ASC) , |
|
| 489 |
INDEX `fk_specimen_organization3_idx` (`owner_collection` ASC) , |
|
| 490 |
UNIQUE INDEX `specimen_unique_in_individual` (`individual` ASC, `code_in_individual` ASC) , |
|
| 491 |
UNIQUE INDEX `specimen_unique_by_collection_event` (`collection_event` ASC) , |
|
| 492 |
UNIQUE INDEX `specimen_unique_in_collection_by_barcode` (`orig_collection` ASC, `barcode` ASC) , |
|
| 493 |
UNIQUE INDEX `specimen_unique_in_collection_by_accession_number` (`orig_collection` ASC, `accession_number` ASC) , |
|
| 494 |
CONSTRAINT `fk_specimen_taxon_occurrence1` |
|
| 495 |
FOREIGN KEY (`id` ) |
|
| 496 |
REFERENCES `taxon_occurrence` (`id` ) |
|
| 497 |
ON DELETE CASCADE |
|
| 498 |
ON UPDATE CASCADE, |
|
| 499 |
CONSTRAINT `fk_specimen_collection1` |
|
| 500 |
FOREIGN KEY (`orig_collection` ) |
|
| 501 |
REFERENCES `collection` (`id` ) |
|
| 502 |
ON DELETE CASCADE |
|
| 503 |
ON UPDATE CASCADE, |
|
| 504 |
CONSTRAINT `fk_specimen_organization3` |
|
| 505 |
FOREIGN KEY (`owner_collection` ) |
|
| 506 |
REFERENCES `collection` (`id` ) |
|
| 507 |
ON DELETE CASCADE |
|
| 508 |
ON UPDATE CASCADE, |
|
| 509 |
CONSTRAINT `fk_specimen_taxon_observation1` |
|
| 510 |
FOREIGN KEY (`collection_event` ) |
|
| 511 |
REFERENCES `taxon_observation` (`id` ) |
|
| 512 |
ON DELETE CASCADE |
|
| 513 |
ON UPDATE CASCADE, |
|
| 514 |
CONSTRAINT `fk_specimen_individual1` |
|
| 515 |
FOREIGN KEY (`individual` ) |
|
| 516 |
REFERENCES `individual` (`id` ) |
|
| 517 |
ON DELETE CASCADE |
|
| 518 |
ON UPDATE CASCADE, |
|
| 519 |
CONSTRAINT `fk_specimen_collection2` |
|
| 520 |
FOREIGN KEY (`current_collection` ) |
|
| 521 |
REFERENCES `collection` (`id` ) |
|
| 522 |
ON DELETE CASCADE |
|
| 523 |
ON UPDATE CASCADE) |
|
| 524 |
ENGINE = InnoDB |
|
| 525 |
DEFAULT CHARACTER SET = utf8 |
|
| 526 |
COLLATE = utf8_bin |
|
| 527 |
COMMENT = 'A \"part of a plant\" (\"Wikipedia\":http://en.wikipedia.org/wiki/Specimen) which was collected from it'; |
|
| 287 |
-- |
|
| 288 |
-- Dumping data for table "method" |
|
| 289 |
-- |
|
| 528 | 290 |
|
| 291 |
/*!40000 ALTER TABLE "method" DISABLE KEYS */; |
|
| 292 |
/*!40000 ALTER TABLE "method" ENABLE KEYS */; |
|
| 529 | 293 |
|
| 530 |
-- ----------------------------------------------------- |
|
| 531 |
-- Table `taxon_observation` |
|
| 532 |
-- ----------------------------------------------------- |
|
| 533 |
DROP TABLE IF EXISTS `taxon_observation` ; |
|
| 294 |
-- |
|
| 295 |
-- Table structure for table "organization" |
|
| 296 |
-- |
|
| 534 | 297 |
|
| 535 |
CREATE TABLE IF NOT EXISTS `taxon_observation` ( |
|
| 536 |
`id` VARBINARY(767) NOT NULL , |
|
| 537 |
`taxon_occurrence` VARBINARY(767) NOT NULL , |
|
| 538 |
`collector` VARBINARY(767) NULL , |
|
| 539 |
`collector_number` VARBINARY(767) NULL , |
|
| 540 |
`voucher` VARBINARY(767) NULL DEFAULT NULL , |
|
| 541 |
`growth_form` VARBINARY(767) NULL DEFAULT NULL , |
|
| 542 |
`cultivated` TINYINT(1) NULL , |
|
| 543 |
`traits` SET('hstore') NULL ,
|
|
| 544 |
PRIMARY KEY (`id`) , |
|
| 545 |
INDEX `fk_taxon_observation_taxon_occurrence2_idx` (`taxon_occurrence` ASC) , |
|
| 546 |
INDEX `fk_taxon_observation_specimen1_idx` (`voucher` ASC) , |
|
| 547 |
INDEX `fk_taxon_observation_party1_idx` (`collector` ASC) , |
|
| 548 |
CONSTRAINT `fk_taxon_observation_event1` |
|
| 549 |
FOREIGN KEY (`id` ) |
|
| 550 |
REFERENCES `event` (`id` ) |
|
| 551 |
ON DELETE CASCADE |
|
| 552 |
ON UPDATE CASCADE, |
|
| 553 |
CONSTRAINT `fk_taxon_observation_taxon_occurrence2` |
|
| 554 |
FOREIGN KEY (`taxon_occurrence` ) |
|
| 555 |
REFERENCES `taxon_occurrence` (`id` ) |
|
| 556 |
ON DELETE CASCADE |
|
| 557 |
ON UPDATE CASCADE, |
|
| 558 |
CONSTRAINT `fk_taxon_observation_specimen1` |
|
| 559 |
FOREIGN KEY (`voucher` ) |
|
| 560 |
REFERENCES `specimen` (`id` ) |
|
| 561 |
ON DELETE CASCADE |
|
| 562 |
ON UPDATE CASCADE, |
|
| 563 |
CONSTRAINT `fk_taxon_observation_party1` |
|
| 564 |
FOREIGN KEY (`collector` ) |
|
| 565 |
REFERENCES `party` (`id` ) |
|
| 566 |
ON DELETE CASCADE |
|
| 567 |
ON UPDATE CASCADE) |
|
| 568 |
ENGINE = InnoDB |
|
| 569 |
DEFAULT CHARACTER SET = utf8 |
|
| 570 |
COLLATE = utf8_bin; |
|
| 298 |
DROP TABLE IF EXISTS "organization"; |
|
| 299 |
CREATE TABLE "organization" ( |
|
| 300 |
"id" varbinary(767) NOT NULL, |
|
| 301 |
"info" set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
| 302 |
PRIMARY KEY ("id"),
|
|
| 303 |
CONSTRAINT "fk_organization_party1" FOREIGN KEY ("id") REFERENCES "party" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 304 |
); |
|
| 571 | 305 |
|
| 306 |
-- |
|
| 307 |
-- Dumping data for table "organization" |
|
| 308 |
-- |
|
| 572 | 309 |
|
| 573 |
-- ----------------------------------------------------- |
|
| 574 |
-- Table `taxon_determination` |
|
| 575 |
-- ----------------------------------------------------- |
|
| 576 |
DROP TABLE IF EXISTS `taxon_determination` ; |
|
| 310 |
/*!40000 ALTER TABLE "organization" DISABLE KEYS */; |
|
| 311 |
/*!40000 ALTER TABLE "organization" ENABLE KEYS */; |
|
| 577 | 312 |
|
| 578 |
CREATE TABLE IF NOT EXISTS `taxon_determination` ( |
|
| 579 |
`id` VARBINARY(767) NOT NULL , |
|
| 580 |
`taxon_assertion` VARBINARY(767) NOT NULL , |
|
| 581 |
`identified_by` VARBINARY(767) NULL DEFAULT NULL , |
|
| 582 |
`fit_info` SET('hstore') NULL ,
|
|
| 583 |
INDEX `fk_taxon_occurrence_has_qualified_taxon1_idx` (`taxon_assertion` ASC) , |
|
| 584 |
PRIMARY KEY (`id`) , |
|
| 585 |
INDEX `fk_taxon_determination_party1_idx` (`identified_by` ASC) , |
|
| 586 |
UNIQUE INDEX `taxon_determination_unique` (`taxon_assertion` ASC, `identified_by` ASC) , |
|
| 587 |
CONSTRAINT `fk_taxon_occurrence_has_qualified_taxon1` |
|
| 588 |
FOREIGN KEY (`taxon_assertion` ) |
|
| 589 |
REFERENCES `taxon_assertion` (`id` ) |
|
| 590 |
ON DELETE CASCADE |
|
| 591 |
ON UPDATE CASCADE, |
|
| 592 |
CONSTRAINT `fk_taxon_determination_record1` |
|
| 593 |
FOREIGN KEY (`id` ) |
|
| 594 |
REFERENCES `taxon_observation` (`id` ) |
|
| 595 |
ON DELETE CASCADE |
|
| 596 |
ON UPDATE CASCADE, |
|
| 597 |
CONSTRAINT `fk_taxon_determination_party1` |
|
| 598 |
FOREIGN KEY (`identified_by` ) |
|
| 599 |
REFERENCES `party` (`id` ) |
|
| 600 |
ON DELETE CASCADE |
|
| 601 |
ON UPDATE CASCADE) |
|
| 602 |
ENGINE = InnoDB |
|
| 603 |
DEFAULT CHARACTER SET = utf8 |
|
| 604 |
COLLATE = utf8_bin |
|
| 605 |
COMMENT = 'An assertion that a [[VegCore#TaxonOccurrence|TaxonOccurrence]] is a particular [[VegCore#Taxon|Taxon]]'; |
|
| 313 |
-- |
|
| 314 |
-- Table structure for table "parsed_taxon_assertion" |
|
| 315 |
-- |
|
| 606 | 316 |
|
| 317 |
DROP TABLE IF EXISTS "parsed_taxon_assertion"; |
|
| 318 |
CREATE TABLE "parsed_taxon_assertion" ( |
|
| 319 |
"id" varbinary(767) NOT NULL, |
|
| 320 |
"matched_taxon_concept" varbinary(767) DEFAULT NULL, |
|
| 321 |
"match_score" float DEFAULT NULL, |
|
| 322 |
"match_info" set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
| 323 |
PRIMARY KEY ("id"),
|
|
| 324 |
KEY "fk_parsed_taxon_assertion_taxon_name1_idx" ("matched_taxon_concept"),
|
|
| 325 |
CONSTRAINT "fk_matched_taxon_qualified_taxon10" FOREIGN KEY ("id") REFERENCES "taxon_assertion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 326 |
CONSTRAINT "fk_parsed_taxon_assertion_taxon_name1" FOREIGN KEY ("matched_taxon_concept") REFERENCES "taxon_concept" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 327 |
); |
|
| 607 | 328 |
|
| 608 |
-- ----------------------------------------------------- |
|
| 609 |
-- Table `taxon_occurrence` |
|
| 610 |
-- ----------------------------------------------------- |
|
| 611 |
DROP TABLE IF EXISTS `taxon_occurrence` ; |
|
| 329 |
-- |
|
| 330 |
-- Dumping data for table "parsed_taxon_assertion" |
|
| 331 |
-- |
|
| 612 | 332 |
|
| 613 |
CREATE TABLE IF NOT EXISTS `taxon_occurrence` ( |
|
| 614 |
`id` VARBINARY(767) NOT NULL , |
|
| 615 |
`current_determination` VARBINARY(767) NULL DEFAULT NULL , |
|
| 616 |
`original_determination` VARBINARY(767) NULL DEFAULT NULL , |
|
| 617 |
PRIMARY KEY (`id`) , |
|
| 618 |
INDEX `fk_taxon_occurrence_taxon_determination1_idx` (`original_determination` ASC) , |
|
| 619 |
INDEX `fk_taxon_occurrence_taxon_determination2_idx` (`current_determination` ASC) , |
|
| 620 |
CONSTRAINT `fk_taxon_occurrence_taxon_determination1` |
|
| 621 |
FOREIGN KEY (`original_determination` ) |
|
| 622 |
REFERENCES `taxon_determination` (`id` ) |
|
| 623 |
ON DELETE CASCADE |
|
| 624 |
ON UPDATE CASCADE, |
|
| 625 |
CONSTRAINT `fk_taxon_occurrence_taxon_determination2` |
|
| 626 |
FOREIGN KEY (`current_determination` ) |
|
| 627 |
REFERENCES `taxon_determination` (`id` ) |
|
| 628 |
ON DELETE CASCADE |
|
| 629 |
ON UPDATE CASCADE, |
|
| 630 |
CONSTRAINT `fk_taxon_occurrence_event1` |
|
| 631 |
FOREIGN KEY (`id` ) |
|
| 632 |
REFERENCES `record` (`id` ) |
|
| 633 |
ON DELETE CASCADE |
|
| 634 |
ON UPDATE CASCADE) |
|
| 635 |
ENGINE = InnoDB |
|
| 636 |
DEFAULT CHARACTER SET = utf8 |
|
| 637 |
COLLATE = utf8_bin |
|
| 638 |
COMMENT = 'Any entity on which [[VegCore#TaxonDetermination|TaxonDetermination]]s can be made'; |
|
| 333 |
/*!40000 ALTER TABLE "parsed_taxon_assertion" DISABLE KEYS */; |
|
| 334 |
/*!40000 ALTER TABLE "parsed_taxon_assertion" ENABLE KEYS */; |
|
| 639 | 335 |
|
| 336 |
-- |
|
| 337 |
-- Table structure for table "party" |
|
| 338 |
-- |
|
| 640 | 339 |
|
| 641 |
-- ----------------------------------------------------- |
|
| 642 |
-- Table `individual` |
|
| 643 |
-- ----------------------------------------------------- |
|
| 644 |
DROP TABLE IF EXISTS `individual` ; |
|
| 340 |
DROP TABLE IF EXISTS "party"; |
|
| 341 |
CREATE TABLE "party" ( |
|
| 342 |
"id" varbinary(767) NOT NULL, |
|
| 343 |
"info" set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
| 344 |
PRIMARY KEY ("id"),
|
|
| 345 |
CONSTRAINT "fk_collection_source10" FOREIGN KEY ("id") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 346 |
); |
|
| 645 | 347 |
|
| 646 |
CREATE TABLE IF NOT EXISTS `individual` ( |
|
| 647 |
`id` VARBINARY(767) NOT NULL , |
|
| 648 |
`tag` VARBINARY(767) NULL DEFAULT NULL , |
|
| 649 |
PRIMARY KEY (`id`) , |
|
| 650 |
CONSTRAINT `fk_individual_record1` |
|
| 651 |
FOREIGN KEY (`id` ) |
|
| 652 |
REFERENCES `taxon_occurrence` (`id` ) |
|
| 653 |
ON DELETE CASCADE |
|
| 654 |
ON UPDATE CASCADE) |
|
| 655 |
ENGINE = InnoDB |
|
| 656 |
DEFAULT CHARACTER SET = utf8 |
|
| 657 |
COLLATE = utf8_bin |
|
| 658 |
COMMENT = 'A distinct biological organism'; |
|
| 348 |
-- |
|
| 349 |
-- Dumping data for table "party" |
|
| 350 |
-- |
|
| 659 | 351 |
|
| 352 |
/*!40000 ALTER TABLE "party" DISABLE KEYS */; |
|
| 353 |
/*!40000 ALTER TABLE "party" ENABLE KEYS */; |
|
| 660 | 354 |
|
| 661 |
-- ----------------------------------------------------- |
|
| 662 |
-- Table `aggregate_observation` |
|
| 663 |
-- ----------------------------------------------------- |
|
| 664 |
DROP TABLE IF EXISTS `aggregate_observation` ; |
|
| 355 |
-- |
|
| 356 |
-- Table structure for table "place" |
|
| 357 |
-- |
|
| 665 | 358 |
|
| 666 |
CREATE TABLE IF NOT EXISTS `aggregate_observation` ( |
|
| 667 |
`id` VARBINARY(767) NOT NULL , |
|
| 668 |
`taxon_concept` VARBINARY(767) NOT NULL , |
|
| 669 |
`traits` SET('hstore') NULL ,
|
|
| 670 |
PRIMARY KEY (`id`) , |
|
| 671 |
INDEX `fk_aggregate_observation_taxon_name1_idx` (`taxon_concept` ASC) , |
|
| 672 |
CONSTRAINT `fk_aggregate_observation_taxon_occurrence1` |
|
| 673 |
FOREIGN KEY (`id` ) |
|
| 674 |
REFERENCES `taxon_observation` (`id` ) |
|
| 675 |
ON DELETE CASCADE |
|
| 676 |
ON UPDATE CASCADE, |
|
| 677 |
CONSTRAINT `fk_aggregate_observation_taxon_name1` |
|
| 678 |
FOREIGN KEY (`taxon_concept` ) |
|
| 679 |
REFERENCES `taxon_concept` (`id` ) |
|
| 680 |
ON DELETE CASCADE |
|
| 681 |
ON UPDATE CASCADE) |
|
| 682 |
ENGINE = InnoDB |
|
| 683 |
DEFAULT CHARACTER SET = utf8 |
|
| 684 |
COLLATE = utf8_bin |
|
| 685 |
COMMENT = '\"An observation applying to all occurrences of an organism based on an aggregation factor\" (\"VegX\":http://wiki.tdwg.org/twiki/pub/Vegetation/WebHome/VegX_Schema_1.5.3_proposed.zip#/veg.xsd#/aggregateOrganismObservations/aggregateOrganismObservation)'; |
|
| 359 |
DROP TABLE IF EXISTS "place"; |
|
| 360 |
CREATE TABLE "place" ( |
|
| 361 |
"id" varbinary(767) NOT NULL, |
|
| 362 |
"parent" varbinary(767) NOT NULL, |
|
| 363 |
"coordinates" varbinary(767) DEFAULT NULL, |
|
| 364 |
"path" varbinary(767) DEFAULT NULL, |
|
| 365 |
"locality" varbinary(767) DEFAULT NULL, |
|
| 366 |
PRIMARY KEY ("id"),
|
|
| 367 |
KEY "fk_place_coordinates1_idx" ("coordinates"),
|
|
| 368 |
KEY "fk_place1_idx" ("parent"),
|
|
| 369 |
KEY "fk_place_place_path1_idx" ("path"),
|
|
| 370 |
CONSTRAINT "fk_place1" FOREIGN KEY ("parent") REFERENCES "place" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 371 |
CONSTRAINT "fk_place_coordinates1" FOREIGN KEY ("coordinates") REFERENCES "coordinates" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 372 |
CONSTRAINT "fk_place_place_path1" FOREIGN KEY ("path") REFERENCES "place_path" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 373 |
CONSTRAINT "fk_place_record1" FOREIGN KEY ("id") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 374 |
); |
|
| 686 | 375 |
|
| 376 |
-- |
|
| 377 |
-- Dumping data for table "place" |
|
| 378 |
-- |
|
| 687 | 379 |
|
| 688 |
-- ----------------------------------------------------- |
|
| 689 |
-- Table `taxon_presence` |
|
| 690 |
-- ----------------------------------------------------- |
|
| 691 |
DROP TABLE IF EXISTS `taxon_presence` ; |
|
| 380 |
/*!40000 ALTER TABLE "place" DISABLE KEYS */; |
|
| 381 |
/*!40000 ALTER TABLE "place" ENABLE KEYS */; |
|
| 692 | 382 |
|
| 693 |
CREATE TABLE IF NOT EXISTS `taxon_presence` ( |
|
| 694 |
`id` VARBINARY(767) NOT NULL , |
|
| 695 |
`taxon_concept` VARBINARY(767) NOT NULL , |
|
| 696 |
`traits` SET('hstore') NULL ,
|
|
| 697 |
PRIMARY KEY (`id`) , |
|
| 698 |
INDEX `fk_taxon_presence_taxon_name1_idx` (`taxon_concept` ASC) , |
|
| 699 |
CONSTRAINT `fk_taxon_observation_taxon_occurrence1` |
|
| 700 |
FOREIGN KEY (`id` ) |
|
| 701 |
REFERENCES `taxon_observation` (`id` ) |
|
| 702 |
ON DELETE CASCADE |
|
| 703 |
ON UPDATE CASCADE, |
|
| 704 |
CONSTRAINT `fk_taxon_presence_taxon_name1` |
|
| 705 |
FOREIGN KEY (`taxon_concept` ) |
|
| 706 |
REFERENCES `taxon_concept` (`id` ) |
|
| 707 |
ON DELETE CASCADE |
|
| 708 |
ON UPDATE CASCADE) |
|
| 709 |
ENGINE = InnoDB |
|
| 710 |
DEFAULT CHARACTER SET = utf8 |
|
| 711 |
COLLATE = utf8_bin |
|
| 712 |
COMMENT = 'An observation of just a [[VegCore#Taxon|Taxon]]\'s _presence_'; |
|
| 383 |
-- |
|
| 384 |
-- Table structure for table "place_observation" |
|
| 385 |
-- |
|
| 713 | 386 |
|
| 387 |
DROP TABLE IF EXISTS "place_observation"; |
|
| 388 |
CREATE TABLE "place_observation" ( |
|
| 389 |
"id" varbinary(767) NOT NULL, |
|
| 390 |
"place" varbinary(767) NOT NULL, |
|
| 391 |
"elevation_m" double DEFAULT NULL, |
|
| 392 |
"slope_incline_deg" double DEFAULT NULL, |
|
| 393 |
"slope_direction_deg_N" double DEFAULT NULL, |
|
| 394 |
"geological_context" varbinary(767) DEFAULT NULL, |
|
| 395 |
"community" varbinary(767) DEFAULT NULL, |
|
| 396 |
"observations" set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
| 397 |
PRIMARY KEY ("id"),
|
|
| 398 |
KEY "fk_place_observation_place1_idx" ("place"),
|
|
| 399 |
KEY "fk_place_observation_geological_context1_idx" ("geological_context"),
|
|
| 400 |
KEY "fk_place_observation_community1_idx" ("community"),
|
|
| 401 |
KEY "fk_place_observation_event1_idx" ("id"),
|
|
| 402 |
CONSTRAINT "fk_place_observation_community1" FOREIGN KEY ("community") REFERENCES "community" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 403 |
CONSTRAINT "fk_place_observation_event1" FOREIGN KEY ("id") REFERENCES "event" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 404 |
CONSTRAINT "fk_place_observation_geological_context1" FOREIGN KEY ("geological_context") REFERENCES "geological_context" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 405 |
CONSTRAINT "fk_place_observation_place1" FOREIGN KEY ("place") REFERENCES "place" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 406 |
); |
|
| 714 | 407 |
|
| 715 |
-- ----------------------------------------------------- |
|
| 716 |
-- Table `community` |
|
| 717 |
-- ----------------------------------------------------- |
|
| 718 |
DROP TABLE IF EXISTS `community` ; |
|
| 408 |
-- |
|
| 409 |
-- Dumping data for table "place_observation" |
|
| 410 |
-- |
|
| 719 | 411 |
|
| 720 |
CREATE TABLE IF NOT EXISTS `community` ( |
|
| 721 |
`id` VARBINARY(767) NOT NULL , |
|
| 722 |
`name` VARBINARY(767) NOT NULL , |
|
| 723 |
`info` SET('hstore') NULL ,
|
|
| 724 |
PRIMARY KEY (`id`) , |
|
| 725 |
CONSTRAINT `fk_community_record1` |
|
| 726 |
FOREIGN KEY (`id` ) |
|
| 727 |
REFERENCES `record` (`id` ) |
|
| 728 |
ON DELETE CASCADE |
|
| 729 |
ON UPDATE CASCADE) |
|
| 730 |
ENGINE = InnoDB |
|
| 731 |
DEFAULT CHARACTER SET = utf8 |
|
| 732 |
COLLATE = utf8_bin |
|
| 733 |
COMMENT = '\"A community type is an abstract grouping of organisms that tend to co-occur on the landscape due to shared ecological requirements or preferences. May be designated by syntaxon or physiognomic types.\" ([[VegCore#VegX|VegX]])'; |
|
| 412 |
/*!40000 ALTER TABLE "place_observation" DISABLE KEYS */; |
|
| 413 |
/*!40000 ALTER TABLE "place_observation" ENABLE KEYS */; |
|
| 734 | 414 |
|
| 415 |
-- |
|
| 416 |
-- Table structure for table "place_path" |
|
| 417 |
-- |
|
| 735 | 418 |
|
| 736 |
-- ----------------------------------------------------- |
|
| 737 |
-- Table `geological_context` |
|
| 738 |
-- ----------------------------------------------------- |
|
| 739 |
DROP TABLE IF EXISTS `geological_context` ; |
|
| 419 |
DROP TABLE IF EXISTS "place_path"; |
|
| 420 |
CREATE TABLE "place_path" ( |
|
| 421 |
"id" varbinary(767) NOT NULL, |
|
| 422 |
"continent" varbinary(767) DEFAULT NULL, |
|
| 423 |
"country" varbinary(767) DEFAULT NULL, |
|
| 424 |
"state_province" varbinary(767) DEFAULT NULL, |
|
| 425 |
"county" varbinary(767) DEFAULT NULL, |
|
| 426 |
"municipality" varbinary(767) DEFAULT NULL, |
|
| 427 |
"ranks" set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
| 428 |
PRIMARY KEY ("id")
|
|
| 429 |
); |
|
| 740 | 430 |
|
| 741 |
CREATE TABLE IF NOT EXISTS `geological_context` ( |
|
| 742 |
`id` VARBINARY(767) NOT NULL , |
|
| 743 |
`name` VARBINARY(767) NOT NULL , |
|
| 744 |
`info` SET('hstore') NULL ,
|
|
| 745 |
PRIMARY KEY (`id`) , |
|
| 746 |
CONSTRAINT `fk_geological_context_record1` |
|
| 747 |
FOREIGN KEY (`id` ) |
|
| 748 |
REFERENCES `record` (`id` ) |
|
| 749 |
ON DELETE CASCADE |
|
| 750 |
ON UPDATE CASCADE) |
|
| 751 |
ENGINE = InnoDB |
|
| 752 |
DEFAULT CHARACTER SET = utf8 |
|
| 753 |
COLLATE = utf8_bin |
|
| 754 |
COMMENT = '\"information pertaining to a location within a geological context, such as stratigraphy\" (\"DwC\":http://rs.tdwg.org/dwc/terms/#GeologicalContext)'; |
|
| 431 |
-- |
|
| 432 |
-- Dumping data for table "place_path" |
|
| 433 |
-- |
|
| 755 | 434 |
|
| 435 |
/*!40000 ALTER TABLE "place_path" DISABLE KEYS */; |
|
| 436 |
/*!40000 ALTER TABLE "place_path" ENABLE KEYS */; |
|
| 756 | 437 |
|
| 757 |
-- ----------------------------------------------------- |
|
| 758 |
-- Table `place_observation` |
|
| 759 |
-- ----------------------------------------------------- |
|
| 760 |
DROP TABLE IF EXISTS `place_observation` ; |
|
| 438 |
-- |
|
| 439 |
-- Table structure for table "plot" |
|
| 440 |
-- |
|
| 761 | 441 |
|
| 762 |
CREATE TABLE IF NOT EXISTS `place_observation` ( |
|
| 763 |
`id` VARBINARY(767) NOT NULL , |
|
| 764 |
`place` VARBINARY(767) NOT NULL , |
|
| 765 |
`elevation_m` DOUBLE NULL , |
|
| 766 |
`slope_incline_deg` DOUBLE NULL , |
|
| 767 |
`slope_direction_deg_N` DOUBLE NULL , |
|
| 768 |
`geological_context` VARBINARY(767) NULL DEFAULT NULL , |
|
| 769 |
`community` VARBINARY(767) NULL DEFAULT NULL , |
|
| 770 |
`observations` SET('hstore') NULL ,
|
|
| 771 |
INDEX `fk_place_observation_place1_idx` (`place` ASC) , |
|
| 772 |
INDEX `fk_place_observation_geological_context1_idx` (`geological_context` ASC) , |
|
| 773 |
INDEX `fk_place_observation_community1_idx` (`community` ASC) , |
|
| 774 |
INDEX `fk_place_observation_event1_idx` (`id` ASC) , |
|
| 775 |
PRIMARY KEY (`id`) , |
|
| 776 |
CONSTRAINT `fk_place_observation_place1` |
|
| 777 |
FOREIGN KEY (`place` ) |
|
| 778 |
REFERENCES `place` (`id` ) |
|
| 779 |
ON DELETE CASCADE |
|
| 780 |
ON UPDATE CASCADE, |
|
| 781 |
CONSTRAINT `fk_place_observation_geological_context1` |
|
| 782 |
FOREIGN KEY (`geological_context` ) |
|
| 783 |
REFERENCES `geological_context` (`id` ) |
|
| 784 |
ON DELETE CASCADE |
|
| 785 |
ON UPDATE CASCADE, |
|
| 786 |
CONSTRAINT `fk_place_observation_community1` |
|
| 787 |
FOREIGN KEY (`community` ) |
|
| 788 |
REFERENCES `community` (`id` ) |
|
| 789 |
ON DELETE CASCADE |
|
| 790 |
ON UPDATE CASCADE, |
|
| 791 |
CONSTRAINT `fk_place_observation_event1` |
|
| 792 |
FOREIGN KEY (`id` ) |
|
| 793 |
REFERENCES `event` (`id` ) |
|
| 794 |
ON DELETE CASCADE |
|
| 795 |
ON UPDATE CASCADE) |
|
| 796 |
ENGINE = InnoDB |
|
| 797 |
DEFAULT CHARACTER SET = utf8 |
|
| 798 |
COLLATE = utf8_bin |
|
| 799 |
COMMENT = 'A sampling of a [[VegCore#Location|Location]]'; |
|
| 442 |
DROP TABLE IF EXISTS "plot"; |
|
| 443 |
CREATE TABLE "plot" ( |
|
| 444 |
"id" varbinary(767) NOT NULL, |
|
| 445 |
"name" varbinary(767) DEFAULT NULL, |
|
| 446 |
"area_m2" double DEFAULT NULL, |
|
| 447 |
"bounding_box" varbinary(767) DEFAULT NULL, |
|
| 448 |
PRIMARY KEY ("id"),
|
|
| 449 |
CONSTRAINT "fk_subplot_place1" FOREIGN KEY ("id") REFERENCES "place" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 450 |
); |
|
| 800 | 451 |
|
| 452 |
-- |
|
| 453 |
-- Dumping data for table "plot" |
|
| 454 |
-- |
|
| 801 | 455 |
|
| 802 |
-- ----------------------------------------------------- |
|
| 803 |
-- Table `soil_observation` |
|
| 804 |
-- ----------------------------------------------------- |
|
| 805 |
DROP TABLE IF EXISTS `soil_observation` ; |
|
| 456 |
/*!40000 ALTER TABLE "plot" DISABLE KEYS */; |
|
| 457 |
/*!40000 ALTER TABLE "plot" ENABLE KEYS */; |
|
| 806 | 458 |
|
| 807 |
CREATE TABLE IF NOT EXISTS `soil_observation` ( |
|
| 808 |
`id` VARBINARY(767) NOT NULL , |
|
| 809 |
`observations` SET('hstore') NULL ,
|
|
| 810 |
PRIMARY KEY (`id`) , |
|
| 811 |
CONSTRAINT `fk_soil_observation_place_observation1` |
|
| 812 |
FOREIGN KEY (`id` ) |
|
| 813 |
REFERENCES `place_observation` (`id` ) |
|
| 814 |
ON DELETE CASCADE |
|
| 815 |
ON UPDATE CASCADE) |
|
| 816 |
ENGINE = InnoDB |
|
| 817 |
DEFAULT CHARACTER SET = utf8 |
|
| 818 |
COLLATE = utf8_bin |
|
| 819 |
COMMENT = 'A sampling of a [[VegCore#Location|Location]]\'s soil'; |
|
| 459 |
-- |
|
| 460 |
-- Table structure for table "project" |
|
| 461 |
-- |
|
| 820 | 462 |
|
| 463 |
DROP TABLE IF EXISTS "project"; |
|
| 464 |
CREATE TABLE "project" ( |
|
| 465 |
"id" varbinary(767) NOT NULL, |
|
| 466 |
"name" varbinary(767) NOT NULL, |
|
| 467 |
"info" set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
| 468 |
PRIMARY KEY ("id"),
|
|
| 469 |
CONSTRAINT "fk_project_event1" FOREIGN KEY ("id") REFERENCES "event" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 470 |
); |
|
| 821 | 471 |
|
| 822 |
-- ----------------------------------------------------- |
|
| 823 |
-- Table `plot` |
|
| 824 |
-- ----------------------------------------------------- |
|
| 825 |
DROP TABLE IF EXISTS `plot` ; |
|
| 472 |
-- |
|
| 473 |
-- Dumping data for table "project" |
|
| 474 |
-- |
|
| 826 | 475 |
|
| 827 |
CREATE TABLE IF NOT EXISTS `plot` ( |
|
| 828 |
`id` VARBINARY(767) NOT NULL , |
|
| 829 |
`name` VARBINARY(767) NULL DEFAULT NULL , |
|
| 830 |
`area_m2` DOUBLE NULL , |
|
| 831 |
`bounding_box` VARBINARY(767) NULL DEFAULT NULL , |
|
| 832 |
PRIMARY KEY (`id`) , |
|
| 833 |
CONSTRAINT `fk_subplot_place1` |
|
| 834 |
FOREIGN KEY (`id` ) |
|
| 835 |
REFERENCES `place` (`id` ) |
|
| 836 |
ON DELETE CASCADE |
|
| 837 |
ON UPDATE CASCADE) |
|
| 838 |
ENGINE = InnoDB |
|
| 839 |
DEFAULT CHARACTER SET = utf8 |
|
| 840 |
COLLATE = utf8_bin; |
|
| 476 |
/*!40000 ALTER TABLE "project" DISABLE KEYS */; |
|
| 477 |
/*!40000 ALTER TABLE "project" ENABLE KEYS */; |
|
| 841 | 478 |
|
| 479 |
-- |
|
| 480 |
-- Table structure for table "record" |
|
| 481 |
-- |
|
| 842 | 482 |
|
| 843 |
-- ----------------------------------------------------- |
|
| 844 |
-- Table `subplot` |
|
| 845 |
-- ----------------------------------------------------- |
|
| 846 |
DROP TABLE IF EXISTS `subplot` ; |
|
| 483 |
DROP TABLE IF EXISTS "record"; |
|
| 484 |
CREATE TABLE "record" ( |
|
| 485 |
"id" varbinary(767) NOT NULL, |
|
| 486 |
"source" varbinary(767) NOT NULL, |
|
| 487 |
"source_id_scope" varbinary(767) DEFAULT NULL, |
|
| 488 |
"source_record_id" varbinary(767) DEFAULT NULL, |
|
| 489 |
"info" set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
| 490 |
PRIMARY KEY ("id"),
|
|
| 491 |
UNIQUE KEY "record_unique" ("source","source_id_scope","source_record_id"),
|
|
| 492 |
KEY "fk_record_source1_idx" ("source"),
|
|
| 493 |
CONSTRAINT "fk_record_source1" FOREIGN KEY ("source") REFERENCES "source" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 494 |
); |
|
| 847 | 495 |
|
| 848 |
CREATE TABLE IF NOT EXISTS `subplot` ( |
|
| 849 |
`id` VARBINARY(767) NOT NULL , |
|
| 850 |
`x_m` DOUBLE NULL , |
|
| 851 |
`y_m` DOUBLE NULL , |
|
| 852 |
PRIMARY KEY (`id`) , |
|
| 853 |
CONSTRAINT `fk_subplot_plot1` |
|
| 854 |
FOREIGN KEY (`id` ) |
|
| 855 |
REFERENCES `plot` (`id` ) |
|
| 856 |
ON DELETE CASCADE |
|
| 857 |
ON UPDATE CASCADE) |
|
| 858 |
ENGINE = InnoDB |
|
| 859 |
DEFAULT CHARACTER SET = utf8 |
|
| 860 |
COLLATE = utf8_bin |
|
| 861 |
COMMENT = '\"subplot, line, or any other subsample or subdivision of plot\" (\"SALVIAS\":http://salvias.net/Documents/salvias_data_dictionary.html#Plot_data/subplot)'; |
|
| 496 |
-- |
|
| 497 |
-- Dumping data for table "record" |
|
| 498 |
-- |
|
| 862 | 499 |
|
| 500 |
/*!40000 ALTER TABLE "record" DISABLE KEYS */; |
|
| 501 |
/*!40000 ALTER TABLE "record" ENABLE KEYS */; |
|
| 863 | 502 |
|
| 864 |
-- ----------------------------------------------------- |
|
| 865 |
-- Table `validatable_place` |
|
| 866 |
-- ----------------------------------------------------- |
|
| 867 |
DROP TABLE IF EXISTS `validatable_place` ; |
|
| 503 |
-- |
|
| 504 |
-- Table structure for table "referenced_class" |
|
| 505 |
-- |
|
| 868 | 506 |
|
| 869 |
CREATE TABLE IF NOT EXISTS `validatable_place` ( |
|
| 870 |
`id` VARBINARY(767) NOT NULL , |
|
| 871 |
`coordinates` VARBINARY(767) NOT NULL , |
|
| 872 |
`path` VARBINARY(767) NOT NULL , |
|
| 873 |
PRIMARY KEY (`id`) , |
|
| 874 |
INDEX `fk_geovalidation_place_path1_idx` (`path` ASC) , |
|
| 875 |
INDEX `fk_geovalidation_coordinates1_idx` (`coordinates` ASC) , |
|
| 876 |
UNIQUE INDEX `validatable_place_unique` (`path` ASC, `coordinates` ASC) , |
|
| 877 |
CONSTRAINT `fk_geovalidation_place_path1` |
|
| 878 |
FOREIGN KEY (`path` ) |
|
| 879 |
REFERENCES `place_path` (`id` ) |
|
| 880 |
ON DELETE CASCADE |
|
| 881 |
ON UPDATE CASCADE, |
|
| 882 |
CONSTRAINT `fk_geovalidation_coordinates1` |
|
| 883 |
FOREIGN KEY (`coordinates` ) |
|
| 884 |
REFERENCES `coordinates` (`id` ) |
|
| 885 |
ON DELETE CASCADE |
|
| 886 |
ON UPDATE CASCADE) |
|
| 887 |
ENGINE = InnoDB |
|
| 888 |
DEFAULT CHARACTER SET = utf8 |
|
| 889 |
COLLATE = utf8_bin; |
|
| 507 |
DROP TABLE IF EXISTS "referenced_class"; |
|
| 508 |
CREATE TABLE "referenced_class" ( |
|
| 509 |
"id" varbinary(767) NOT NULL, |
|
| 510 |
PRIMARY KEY ("id"),
|
|
| 511 |
CONSTRAINT "fk_example_record10" FOREIGN KEY ("id") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 512 |
); |
|
| 890 | 513 |
|
| 514 |
-- |
|
| 515 |
-- Dumping data for table "referenced_class" |
|
| 516 |
-- |
|
| 891 | 517 |
|
| 892 |
-- ----------------------------------------------------- |
|
| 893 |
-- Table `individual_observation` |
|
| 894 |
-- ----------------------------------------------------- |
|
| 895 |
DROP TABLE IF EXISTS `individual_observation` ; |
|
| 518 |
/*!40000 ALTER TABLE "referenced_class" DISABLE KEYS */; |
|
| 519 |
/*!40000 ALTER TABLE "referenced_class" ENABLE KEYS */; |
|
| 896 | 520 |
|
| 897 |
CREATE TABLE IF NOT EXISTS `individual_observation` ( |
|
| 898 |
`id` VARBINARY(767) NOT NULL , |
|
| 899 |
`individual` VARBINARY(767) NULL , |
|
| 900 |
`code` VARBINARY(767) NULL , |
|
| 901 |
`traits` SET('hstore') NULL ,
|
|
| 902 |
PRIMARY KEY (`id`) , |
|
| 903 |
INDEX `fk_individual_observation_individual1_idx` (`individual` ASC) , |
|
| 904 |
CONSTRAINT `fk_individual_observation_taxon_occurrence1` |
|
| 905 |
FOREIGN KEY (`id` ) |
|
| 906 |
REFERENCES `taxon_observation` (`id` ) |
|
| 907 |
ON DELETE CASCADE |
|
| 908 |
ON UPDATE CASCADE, |
|
| 909 |
CONSTRAINT `fk_individual_observation_individual1` |
|
| 910 |
FOREIGN KEY (`individual` ) |
|
| 911 |
REFERENCES `individual` (`id` ) |
|
| 912 |
ON DELETE CASCADE |
|
| 913 |
ON UPDATE CASCADE) |
|
| 914 |
ENGINE = InnoDB |
|
| 915 |
DEFAULT CHARACTER SET = utf8 |
|
| 916 |
COLLATE = utf8_bin |
|
| 917 |
COMMENT = 'An observation of an [[VegCore#Individual|Individual]]'; |
|
| 521 |
-- |
|
| 522 |
-- Table structure for table "relationship" |
|
| 523 |
-- |
|
| 918 | 524 |
|
| 525 |
DROP TABLE IF EXISTS "relationship"; |
|
| 526 |
CREATE TABLE "relationship" ( |
|
| 527 |
"id" varbinary(767) NOT NULL, |
|
| 528 |
"record" varbinary(767) NOT NULL, |
|
| 529 |
"related_record" varbinary(767) NOT NULL, |
|
| 530 |
"info" set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
| 531 |
PRIMARY KEY ("id"),
|
|
| 532 |
KEY "fk_relationship_record1_idx" ("record"),
|
|
| 533 |
KEY "fk_relationship_related_record_idx" ("related_record"),
|
|
| 534 |
CONSTRAINT "fk_relationship_record1" FOREIGN KEY ("id") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 535 |
CONSTRAINT "fk_relationship_record2" FOREIGN KEY ("record") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 536 |
CONSTRAINT "fk_relationship_related_record" FOREIGN KEY ("related_record") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 537 |
); |
|
| 919 | 538 |
|
| 920 |
-- ----------------------------------------------------- |
|
| 921 |
-- Table `stem` |
|
| 922 |
-- ----------------------------------------------------- |
|
| 923 |
DROP TABLE IF EXISTS `stem` ; |
|
| 539 |
-- |
|
| 540 |
-- Dumping data for table "relationship" |
|
| 541 |
-- |
|
| 924 | 542 |
|
| 925 |
CREATE TABLE IF NOT EXISTS `stem` ( |
|
| 926 |
`id` VARBINARY(767) NOT NULL , |
|
| 927 |
`individual` VARBINARY(767) NOT NULL , |
|
| 928 |
PRIMARY KEY (`id`) , |
|
| 929 |
INDEX `fk_stem_individual1_idx` (`individual` ASC) , |
|
| 930 |
CONSTRAINT `fk_stem_individual1` |
|
| 931 |
FOREIGN KEY (`individual` ) |
|
| 932 |
REFERENCES `individual` (`id` ) |
|
| 933 |
ON DELETE CASCADE |
|
| 934 |
ON UPDATE CASCADE, |
|
| 935 |
CONSTRAINT `fk_stem_individual2` |
|
| 936 |
FOREIGN KEY (`id` ) |
|
| 937 |
REFERENCES `individual` (`id` ) |
|
| 938 |
ON DELETE CASCADE |
|
| 939 |
ON UPDATE CASCADE) |
|
| 940 |
ENGINE = InnoDB |
|
| 941 |
DEFAULT CHARACTER SET = utf8 |
|
| 942 |
COLLATE = utf8_bin |
|
| 943 |
COMMENT = 'An \"individual tree stem\" (\"VegBank\":http://vegbank.org/vegbank/views/dba_tabledescription_detail.jsp?view=detail&wparam=stemlocation&entity=dba_tabledescription&where=where_tablename)'; |
|
| 543 |
/*!40000 ALTER TABLE "relationship" DISABLE KEYS */; |
|
| 544 |
/*!40000 ALTER TABLE "relationship" ENABLE KEYS */; |
|
| 944 | 545 |
|
| 546 |
-- |
|
| 547 |
-- Table structure for table "soil_observation" |
|
| 548 |
-- |
|
| 945 | 549 |
|
| 946 |
-- ----------------------------------------------------- |
|
| 947 |
-- Table `stem_observation` |
|
| 948 |
-- ----------------------------------------------------- |
|
| 949 |
DROP TABLE IF EXISTS `stem_observation` ; |
|
| 550 |
DROP TABLE IF EXISTS "soil_observation"; |
|
| 551 |
CREATE TABLE "soil_observation" ( |
|
| 552 |
"id" varbinary(767) NOT NULL, |
|
| 553 |
"observations" set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
| 554 |
PRIMARY KEY ("id"),
|
|
| 555 |
CONSTRAINT "fk_soil_observation_place_observation1" FOREIGN KEY ("id") REFERENCES "place_observation" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 556 |
); |
|
| 950 | 557 |
|
| 951 |
CREATE TABLE IF NOT EXISTS `stem_observation` ( |
|
| 952 |
`id` VARBINARY(767) NOT NULL , |
|
| 953 |
`individual_observation` VARBINARY(767) NOT NULL , |
|
| 954 |
`stem` VARBINARY(767) NULL , |
|
| 955 |
`traits` SET('hstore') NULL ,
|
|
| 956 |
PRIMARY KEY (`id`) , |
|
| 957 |
INDEX `fk_stem_observation_individual_observation1_idx` (`individual_observation` ASC) , |
|
| 958 |
INDEX `fk_stem_observation_stem1_idx` (`stem` ASC) , |
|
| 959 |
UNIQUE INDEX `stem_observation_unique` (`individual_observation` ASC, `stem` ASC) , |
|
| 960 |
CONSTRAINT `fk_stem_observation_individual_observation1` |
|
| 961 |
FOREIGN KEY (`individual_observation` ) |
|
| 962 |
REFERENCES `individual_observation` (`id` ) |
|
| 963 |
ON DELETE CASCADE |
|
| 964 |
ON UPDATE CASCADE, |
|
| 965 |
CONSTRAINT `fk_stem_observation_stem1` |
|
| 966 |
FOREIGN KEY (`stem` ) |
|
| 967 |
REFERENCES `stem` (`id` ) |
|
| 968 |
ON DELETE CASCADE |
|
| 969 |
ON UPDATE CASCADE, |
|
| 970 |
CONSTRAINT `fk_stem_observation_individual_observation2` |
|
| 971 |
FOREIGN KEY (`id` ) |
|
| 972 |
REFERENCES `individual_observation` (`id` ) |
|
| 973 |
ON DELETE CASCADE |
|
| 974 |
ON UPDATE CASCADE) |
|
| 975 |
ENGINE = InnoDB |
|
| 976 |
DEFAULT CHARACTER SET = utf8 |
|
| 977 |
COLLATE = utf8_bin |
|
| 978 |
COMMENT = 'An observation of a [[VegCore#Stem|Stem]]'; |
|
| 558 |
-- |
|
| 559 |
-- Dumping data for table "soil_observation" |
|
| 560 |
-- |
|
| 979 | 561 |
|
| 562 |
/*!40000 ALTER TABLE "soil_observation" DISABLE KEYS */; |
|
| 563 |
/*!40000 ALTER TABLE "soil_observation" ENABLE KEYS */; |
|
| 980 | 564 |
|
| 981 |
-- ----------------------------------------------------- |
|
| 982 |
-- Table `project` |
|
| 983 |
-- ----------------------------------------------------- |
|
| 984 |
DROP TABLE IF EXISTS `project` ; |
|
| 565 |
-- |
|
| 566 |
-- Table structure for table "source" |
|
| 567 |
-- |
|
| 985 | 568 |
|
| 986 |
CREATE TABLE IF NOT EXISTS `project` ( |
|
| 987 |
`id` VARBINARY(767) NOT NULL , |
|
| 988 |
`name` VARBINARY(767) NOT NULL , |
|
| 989 |
`info` SET('hstore') NULL ,
|
|
| 990 |
PRIMARY KEY (`id`) , |
|
| 991 |
CONSTRAINT `fk_project_event1` |
|
| 992 |
FOREIGN KEY (`id` ) |
|
| 993 |
REFERENCES `event` (`id` ) |
|
| 994 |
ON DELETE CASCADE |
|
| 995 |
ON UPDATE CASCADE) |
|
| 996 |
ENGINE = InnoDB |
|
| 997 |
DEFAULT CHARACTER SET = utf8 |
|
| 998 |
COLLATE = utf8_bin |
|
| 999 |
COMMENT = 'A \"project established to collect vegetation plot data. Each plot originates as part of a project.\" (\"VegBank\":http://vegbank.org/vegbank/views/dba_tabledescription_detail.jsp?view=detail&wparam=project&entity=dba_tabledescription&where=where_tablename)'; |
|
| 569 |
DROP TABLE IF EXISTS "source"; |
|
| 570 |
CREATE TABLE "source" ( |
|
| 571 |
"id" varbinary(767) NOT NULL, |
|
| 572 |
"parent" varbinary(767) NOT NULL, |
|
| 573 |
"name" varbinary(767) NOT NULL, |
|
| 574 |
"first_publisher" varbinary(767) DEFAULT NULL, |
|
| 575 |
"owner" varbinary(767) DEFAULT NULL, |
|
| 576 |
"info" set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
| 577 |
PRIMARY KEY ("id"),
|
|
| 578 |
UNIQUE KEY "source_unique" ("parent","name"),
|
|
| 579 |
KEY "fk_source1_idx" ("parent"),
|
|
| 580 |
KEY "fk_source_party2_idx" ("first_publisher"),
|
|
| 581 |
KEY "fk_source_party1_idx" ("owner"),
|
|
| 582 |
CONSTRAINT "fk_source1" FOREIGN KEY ("parent") REFERENCES "source" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 583 |
CONSTRAINT "fk_source_party1" FOREIGN KEY ("owner") REFERENCES "party" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 584 |
CONSTRAINT "fk_source_party2" FOREIGN KEY ("first_publisher") REFERENCES "party" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 585 |
); |
|
| 1000 | 586 |
|
| 587 |
-- |
|
| 588 |
-- Dumping data for table "source" |
|
| 589 |
-- |
|
| 1001 | 590 |
|
| 1002 |
-- ----------------------------------------------------- |
|
| 1003 |
-- Table `referenced_class` |
|
| 1004 |
-- ----------------------------------------------------- |
|
| 1005 |
DROP TABLE IF EXISTS `referenced_class` ; |
|
| 591 |
/*!40000 ALTER TABLE "source" DISABLE KEYS */; |
|
| 592 |
/*!40000 ALTER TABLE "source" ENABLE KEYS */; |
|
| 1006 | 593 |
|
| 1007 |
CREATE TABLE IF NOT EXISTS `referenced_class` ( |
|
| 1008 |
`id` VARBINARY(767) NOT NULL , |
|
Also available in: Unified diff
schemas/VegCore/VegCore.my.sql: regenerated using `VegCore.ERD.mwb.run export_mysql` (also part of the all target). note that this uses mysqldump, so the format and table order is different than it was for MySQL Workbench's custom export script.