Revision 10493
Added by Aaron Marcuse-Kubitza almost 11 years ago
| VegCore.my.sql | ||
|---|---|---|
| 145 | 145 |
KEY `fk_event_party_list1_idx` (`participants`), |
| 146 | 146 |
CONSTRAINT `fk_event_party_list1` FOREIGN KEY (`participants`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 147 | 147 |
CONSTRAINT `fk_event1` FOREIGN KEY (`parent`) REFERENCES `event` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 148 |
CONSTRAINT `fk_event_place1` FOREIGN KEY (`place`) REFERENCES `place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 148 |
CONSTRAINT `fk_event_place1` FOREIGN KEY (`place`) REFERENCES `local_place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 149 | 149 |
CONSTRAINT `fk_event_record1` FOREIGN KEY (`id`) REFERENCES `record` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
| 150 | 150 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='"an action that occurs at a place and during a period of time" ("DwC":http://rs.tdwg.org/dwc/terms/#Event)';
|
| 151 | 151 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| ... | ... | |
| 253 | 253 |
/*!40000 ALTER TABLE `geopath_scrub` ENABLE KEYS */; |
| 254 | 254 |
|
| 255 | 255 |
-- |
| 256 |
-- Table structure for table `geoplace` |
|
| 257 |
-- |
|
| 258 |
|
|
| 259 |
/*!40101 SET @saved_cs_client = @@character_set_client */; |
|
| 260 |
/*!40101 SET character_set_client = utf8 */; |
|
| 261 |
CREATE TABLE `geoplace` ( |
|
| 262 |
`id` varbinary(767) NOT NULL, |
|
| 263 |
`geocoords` varbinary(767) DEFAULT NULL, |
|
| 264 |
`geopath` varbinary(767) DEFAULT NULL, |
|
| 265 |
PRIMARY KEY (`id`), |
|
| 266 |
UNIQUE KEY `validatable_place_unique` (`geopath`,`geocoords`), |
|
| 267 |
KEY `fk_geovalidation_place_path1_idx` (`geopath`), |
|
| 268 |
KEY `fk_geovalidation_coordinates1_idx` (`geocoords`), |
|
| 269 |
CONSTRAINT `fk_geovalidation_place_path10` FOREIGN KEY (`geopath`) REFERENCES `geopath` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 270 |
CONSTRAINT `fk_geovalidation_coordinates10` FOREIGN KEY (`geocoords`) REFERENCES `geocoords` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
|
| 271 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; |
|
| 272 |
/*!40101 SET character_set_client = @saved_cs_client */; |
|
| 273 |
|
|
| 274 |
-- |
|
| 275 |
-- Dumping data for table `geoplace` |
|
| 276 |
-- |
|
| 277 |
|
|
| 278 |
/*!40000 ALTER TABLE `geoplace` DISABLE KEYS */; |
|
| 279 |
/*!40000 ALTER TABLE `geoplace` ENABLE KEYS */; |
|
| 280 |
|
|
| 281 |
-- |
|
| 256 | 282 |
-- Table structure for table `georeferencing` |
| 257 | 283 |
-- |
| 258 | 284 |
|
| ... | ... | |
| 269 | 295 |
KEY `fk_georeferencing_party_list1_idx` (`georeferenced_by`), |
| 270 | 296 |
CONSTRAINT `fk_georeferencing_geoplace1` FOREIGN KEY (`georeferenced_place`) REFERENCES `geoplace` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 271 | 297 |
CONSTRAINT `fk_georeferencing_party_list1` FOREIGN KEY (`georeferenced_by`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 272 |
CONSTRAINT `fk_georef_place1` FOREIGN KEY (`input_place`) REFERENCES `place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 298 |
CONSTRAINT `fk_georef_place1` FOREIGN KEY (`input_place`) REFERENCES `local_place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 273 | 299 |
CONSTRAINT `fk_geovalidation_record100` FOREIGN KEY (`id`) REFERENCES `record` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
| 274 | 300 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; |
| 275 | 301 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| ... | ... | |
| 358 | 384 |
/*!40000 ALTER TABLE `individual_observation` ENABLE KEYS */; |
| 359 | 385 |
|
| 360 | 386 |
-- |
| 387 |
-- Table structure for table `local_place` |
|
| 388 |
-- |
|
| 389 |
|
|
| 390 |
/*!40101 SET @saved_cs_client = @@character_set_client */; |
|
| 391 |
/*!40101 SET character_set_client = utf8 */; |
|
| 392 |
CREATE TABLE `local_place` ( |
|
| 393 |
`id` varbinary(767) NOT NULL, |
|
| 394 |
`parent` varbinary(767) NOT NULL, |
|
| 395 |
`geoplace` varbinary(767) DEFAULT NULL, |
|
| 396 |
`locality` varbinary(767) DEFAULT NULL, |
|
| 397 |
`coords` set('hstore') COLLATE utf8_bin DEFAULT NULL COMMENT 'for verbatim coordinates, etc.',
|
|
| 398 |
PRIMARY KEY (`id`), |
|
| 399 |
KEY `fk_place1_idx` (`parent`), |
|
| 400 |
KEY `fk_place_geoplace1_idx` (`geoplace`), |
|
| 401 |
CONSTRAINT `fk_place_geoplace1` FOREIGN KEY (`geoplace`) REFERENCES `geoplace` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 402 |
CONSTRAINT `fk_place1` FOREIGN KEY (`parent`) REFERENCES `local_place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 403 |
CONSTRAINT `fk_place_record1` FOREIGN KEY (`id`) REFERENCES `record` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
|
| 404 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='"A spatial region" ("DwC":http://rs.tdwg.org/dwc/terms/#dcterms:Location) or point';
|
|
| 405 |
/*!40101 SET character_set_client = @saved_cs_client */; |
|
| 406 |
|
|
| 407 |
-- |
|
| 408 |
-- Dumping data for table `local_place` |
|
| 409 |
-- |
|
| 410 |
|
|
| 411 |
/*!40000 ALTER TABLE `local_place` DISABLE KEYS */; |
|
| 412 |
/*!40000 ALTER TABLE `local_place` ENABLE KEYS */; |
|
| 413 |
|
|
| 414 |
-- |
|
| 361 | 415 |
-- Table structure for table `method` |
| 362 | 416 |
-- |
| 363 | 417 |
|
| ... | ... | |
| 473 | 527 |
/*!40000 ALTER TABLE `party_list_entry` ENABLE KEYS */; |
| 474 | 528 |
|
| 475 | 529 |
-- |
| 476 |
-- Table structure for table `place` |
|
| 477 |
-- |
|
| 478 |
|
|
| 479 |
/*!40101 SET @saved_cs_client = @@character_set_client */; |
|
| 480 |
/*!40101 SET character_set_client = utf8 */; |
|
| 481 |
CREATE TABLE `place` ( |
|
| 482 |
`id` varbinary(767) NOT NULL, |
|
| 483 |
`parent` varbinary(767) NOT NULL, |
|
| 484 |
`geoplace` varbinary(767) DEFAULT NULL, |
|
| 485 |
`locality` varbinary(767) DEFAULT NULL, |
|
| 486 |
`coords` set('hstore') COLLATE utf8_bin DEFAULT NULL COMMENT 'for verbatim coordinates, etc.',
|
|
| 487 |
PRIMARY KEY (`id`), |
|
| 488 |
KEY `fk_place1_idx` (`parent`), |
|
| 489 |
KEY `fk_place_geoplace1_idx` (`geoplace`), |
|
| 490 |
CONSTRAINT `fk_place_geoplace1` FOREIGN KEY (`geoplace`) REFERENCES `geoplace` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 491 |
CONSTRAINT `fk_place1` FOREIGN KEY (`parent`) REFERENCES `place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 492 |
CONSTRAINT `fk_place_record1` FOREIGN KEY (`id`) REFERENCES `record` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
|
| 493 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='"A spatial region" ("DwC":http://rs.tdwg.org/dwc/terms/#dcterms:Location) or point';
|
|
| 494 |
/*!40101 SET character_set_client = @saved_cs_client */; |
|
| 495 |
|
|
| 496 |
-- |
|
| 497 |
-- Dumping data for table `place` |
|
| 498 |
-- |
|
| 499 |
|
|
| 500 |
/*!40000 ALTER TABLE `place` DISABLE KEYS */; |
|
| 501 |
/*!40000 ALTER TABLE `place` ENABLE KEYS */; |
|
| 502 |
|
|
| 503 |
-- |
|
| 504 | 530 |
-- Table structure for table `place_observation` |
| 505 | 531 |
-- |
| 506 | 532 |
|
| ... | ... | |
| 519 | 545 |
KEY `fk_place_observation_place1_idx` (`place`), |
| 520 | 546 |
KEY `fk_place_observation_geological_context1_idx` (`geological_context`), |
| 521 | 547 |
KEY `fk_place_observation_community1_idx` (`community`), |
| 522 |
CONSTRAINT `fk_place_observation_place1` FOREIGN KEY (`place`) REFERENCES `place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 548 |
CONSTRAINT `fk_place_observation_place1` FOREIGN KEY (`place`) REFERENCES `local_place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 523 | 549 |
CONSTRAINT `fk_place_observation_geological_context1` FOREIGN KEY (`geological_context`) REFERENCES `geological_context` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 524 | 550 |
CONSTRAINT `fk_place_observation_community1` FOREIGN KEY (`community`) REFERENCES `community` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 525 | 551 |
CONSTRAINT `fk_place_observation_sampling_event1` FOREIGN KEY (`id`) REFERENCES `sampling_event` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
| ... | ... | |
| 548 | 574 |
`footprint_geom_WKT` varbinary(767) DEFAULT NULL, |
| 549 | 575 |
`dimensions` set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
| 550 | 576 |
PRIMARY KEY (`id`), |
| 551 |
CONSTRAINT `fk_subplot_place1` FOREIGN KEY (`id`) REFERENCES `place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
|
| 577 |
CONSTRAINT `fk_subplot_place1` FOREIGN KEY (`id`) REFERENCES `local_place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 552 | 578 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; |
| 553 | 579 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 554 | 580 |
|
| ... | ... | |
| 783 | 809 |
KEY `fk_specimen_collection2_idx` (`current_collection`), |
| 784 | 810 |
KEY `fk_specimen_organization3_idx` (`owner_collection`), |
| 785 | 811 |
KEY `fk_specimen_party_list1_idx` (`specimenholder_institutions`), |
| 786 |
CONSTRAINT `fk_specimen_taxon_occurrence1` FOREIGN KEY (`id`) REFERENCES `reobservable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 787 |
CONSTRAINT `fk_specimen_collection1` FOREIGN KEY (`orig_collection`) REFERENCES `collection` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 812 |
CONSTRAINT `fk_specimen_organization3` FOREIGN KEY (`owner_collection`) REFERENCES `collection` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 813 |
CONSTRAINT `fk_specimen_taxon_observation1` FOREIGN KEY (`collection_event`) REFERENCES `taxon_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 814 |
CONSTRAINT `fk_specimen_individual1` FOREIGN KEY (`individual`) REFERENCES `individual` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 788 | 815 |
CONSTRAINT `fk_specimen_collection2` FOREIGN KEY (`current_collection`) REFERENCES `collection` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 789 |
CONSTRAINT `fk_specimen_individual1` FOREIGN KEY (`individual`) REFERENCES `individual` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 790 |
CONSTRAINT `fk_specimen_organization3` FOREIGN KEY (`owner_collection`) REFERENCES `collection` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 791 | 816 |
CONSTRAINT `fk_specimen_party_list1` FOREIGN KEY (`specimenholder_institutions`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 792 |
CONSTRAINT `fk_specimen_taxon_observation1` FOREIGN KEY (`collection_event`) REFERENCES `taxon_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
|
| 817 |
CONSTRAINT `fk_specimen_collection1` FOREIGN KEY (`orig_collection`) REFERENCES `collection` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 818 |
CONSTRAINT `fk_specimen_taxon_occurrence1` FOREIGN KEY (`id`) REFERENCES `reobservable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
|
| 793 | 819 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='A "part of a plant" ("Wikipedia":http://en.wikipedia.org/wiki/Specimen) which was collected from it';
|
| 794 | 820 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 795 | 821 |
|
| ... | ... | |
| 1014 | 1040 |
UNIQUE KEY `taxon_determination_unique` (`taxon_assertion`,`identified_by`), |
| 1015 | 1041 |
KEY `fk_taxon_occurrence_has_qualified_taxon1_idx` (`taxon_assertion`), |
| 1016 | 1042 |
KEY `fk_taxon_determination_party_list1_idx` (`identified_by`), |
| 1017 |
CONSTRAINT `fk_taxon_determination_taxon_observation1` FOREIGN KEY (`id`) REFERENCES `taxon_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 1043 |
CONSTRAINT `fk_taxon_occurrence_has_qualified_taxon1` FOREIGN KEY (`taxon_assertion`) REFERENCES `taxon_assertion` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 1018 | 1044 |
CONSTRAINT `fk_taxon_determination_party_list1` FOREIGN KEY (`identified_by`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 1019 |
CONSTRAINT `fk_taxon_occurrence_has_qualified_taxon1` FOREIGN KEY (`taxon_assertion`) REFERENCES `taxon_assertion` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 1045 |
CONSTRAINT `fk_taxon_determination_taxon_observation1` FOREIGN KEY (`id`) REFERENCES `taxon_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 1020 | 1046 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='An assertion that a [[VegCore#TaxonOccurrence|TaxonOccurrence]] is a particular [[VegCore#Taxon|Taxon]]'; |
| 1021 | 1047 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 1022 | 1048 |
|
Also available in: Unified diff
schemas/VegCore/VegCore.ERD.mwb: place: renamed to local_place to distinguish it from geoplace, which is not a subclass of place (it is a separate, global table, while local_place is source-specific). note that renames sometimes need to be done manually on vegbiendev, to avoid triggering a MySQL bug that blocks the new table from being created and requires the entire database to be recreated to clear the error.