Revision 10493
Added by Aaron Marcuse-Kubitza almost 11 years ago
| VegCore.pg.sql | ||
|---|---|---|
| 147 | 147 |
/*KEY "fk_event_party_list1_idx" ("participants")*/CHECK (true),
|
| 148 | 148 |
/*CONSTRAINT "fk_event_party_list1" FOREIGN KEY ("participants") REFERENCES "party_list" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
| 149 | 149 |
/*CONSTRAINT "fk_event1" FOREIGN KEY ("parent") REFERENCES "event" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
| 150 |
/*CONSTRAINT "fk_event_place1" FOREIGN KEY ("place") REFERENCES "place" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 150 |
/*CONSTRAINT "fk_event_place1" FOREIGN KEY ("place") REFERENCES "local_place" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 151 | 151 |
/*CONSTRAINT "fk_event_record1" FOREIGN KEY ("id") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
| 152 | 152 |
) /*ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='"an action that occurs at a place and during a period of text/*time*/" ("DwC":http://rs.tdwg.org/dwc/terms/#Event)'*/;
|
| 153 | 153 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| ... | ... | |
| 255 | 255 |
/*!40000 ALTER TABLE "geopath_scrub" ENABLE KEYS */; |
| 256 | 256 |
|
| 257 | 257 |
-- |
| 258 |
-- Table structure for table "geoplace" |
|
| 259 |
-- |
|
| 260 |
|
|
| 261 |
/*!40101 SET @saved_cs_client = @@character_set_client */; |
|
| 262 |
/*!40101 SET character_set_client = utf8 */; |
|
| 263 |
CREATE TABLE "geoplace" ( |
|
| 264 |
"id" text NOT NULL, |
|
| 265 |
"geocoords" text DEFAULT NULL, |
|
| 266 |
"geopath" text DEFAULT NULL, |
|
| 267 |
PRIMARY KEY ("id"),
|
|
| 268 |
/*CONSTRAINT "validatable_place_unique" */UNIQUE ("geopath","geocoords"),
|
|
| 269 |
/*KEY "fk_geovalidation_place_path1_idx" ("geopath")*/CHECK (true),
|
|
| 270 |
/*KEY "fk_geovalidation_coordinates1_idx" ("geocoords")*/CHECK (true),
|
|
| 271 |
/*CONSTRAINT "fk_geovalidation_place_path10" FOREIGN KEY ("geopath") REFERENCES "geopath" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 272 |
/*CONSTRAINT "fk_geovalidation_coordinates10" FOREIGN KEY ("geocoords") REFERENCES "geocoords" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 273 |
) /*ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin*/; |
|
| 274 |
/*!40101 SET character_set_client = @saved_cs_client */; |
|
| 275 |
|
|
| 276 |
-- |
|
| 277 |
-- Dumping data for table "geoplace" |
|
| 278 |
-- |
|
| 279 |
|
|
| 280 |
/*!40000 ALTER TABLE "geoplace" DISABLE KEYS */; |
|
| 281 |
/*!40000 ALTER TABLE "geoplace" ENABLE KEYS */; |
|
| 282 |
|
|
| 283 |
-- |
|
| 258 | 284 |
-- Table structure for table "georeferencing" |
| 259 | 285 |
-- |
| 260 | 286 |
|
| ... | ... | |
| 271 | 297 |
/*KEY "fk_georeferencing_party_list1_idx" ("georeferenced_by")*/CHECK (true),
|
| 272 | 298 |
/*CONSTRAINT "fk_georeferencing_geoplace1" FOREIGN KEY ("georeferenced_place") REFERENCES "geoplace" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
| 273 | 299 |
/*CONSTRAINT "fk_georeferencing_party_list1" FOREIGN KEY ("georeferenced_by") REFERENCES "party_list" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
| 274 |
/*CONSTRAINT "fk_georef_place1" FOREIGN KEY ("input_place") REFERENCES "place" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 300 |
/*CONSTRAINT "fk_georef_place1" FOREIGN KEY ("input_place") REFERENCES "local_place" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 275 | 301 |
/*CONSTRAINT "fk_geovalidation_record100" FOREIGN KEY ("id") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
| 276 | 302 |
) /*ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin*/; |
| 277 | 303 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| ... | ... | |
| 360 | 386 |
/*!40000 ALTER TABLE "individual_observation" ENABLE KEYS */; |
| 361 | 387 |
|
| 362 | 388 |
-- |
| 389 |
-- Table structure for table "local_place" |
|
| 390 |
-- |
|
| 391 |
|
|
| 392 |
/*!40101 SET @saved_cs_client = @@character_set_client */; |
|
| 393 |
/*!40101 SET character_set_client = utf8 */; |
|
| 394 |
CREATE TABLE "local_place" ( |
|
| 395 |
"id" text NOT NULL, |
|
| 396 |
"parent" text NOT NULL, |
|
| 397 |
"geoplace" text DEFAULT NULL, |
|
| 398 |
"locality" text DEFAULT NULL, |
|
| 399 |
"coords" hstore DEFAULT NULL /*COMMENT 'for verbatim coordinates, etc.'*/, |
|
| 400 |
PRIMARY KEY ("id"),
|
|
| 401 |
/*KEY "fk_place1_idx" ("parent")*/CHECK (true),
|
|
| 402 |
/*KEY "fk_place_geoplace1_idx" ("geoplace")*/CHECK (true),
|
|
| 403 |
/*CONSTRAINT "fk_place_geoplace1" FOREIGN KEY ("geoplace") REFERENCES "geoplace" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 404 |
/*CONSTRAINT "fk_place1" FOREIGN KEY ("parent") REFERENCES "local_place" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 405 |
/*CONSTRAINT "fk_place_record1" FOREIGN KEY ("id") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 406 |
) /*ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='"A spatial region" ("DwC":http://rs.tdwg.org/dwc/terms/#dcterms:Location) or point'*/;
|
|
| 407 |
/*!40101 SET character_set_client = @saved_cs_client */; |
|
| 408 |
|
|
| 409 |
-- |
|
| 410 |
-- Dumping data for table "local_place" |
|
| 411 |
-- |
|
| 412 |
|
|
| 413 |
/*!40000 ALTER TABLE "local_place" DISABLE KEYS */; |
|
| 414 |
/*!40000 ALTER TABLE "local_place" ENABLE KEYS */; |
|
| 415 |
|
|
| 416 |
-- |
|
| 363 | 417 |
-- Table structure for table "method" |
| 364 | 418 |
-- |
| 365 | 419 |
|
| ... | ... | |
| 475 | 529 |
/*!40000 ALTER TABLE "party_list_entry" ENABLE KEYS */; |
| 476 | 530 |
|
| 477 | 531 |
-- |
| 478 |
-- Table structure for table "place" |
|
| 479 |
-- |
|
| 480 |
|
|
| 481 |
/*!40101 SET @saved_cs_client = @@character_set_client */; |
|
| 482 |
/*!40101 SET character_set_client = utf8 */; |
|
| 483 |
CREATE TABLE "place" ( |
|
| 484 |
"id" text NOT NULL, |
|
| 485 |
"parent" text NOT NULL, |
|
| 486 |
"geoplace" text DEFAULT NULL, |
|
| 487 |
"locality" text DEFAULT NULL, |
|
| 488 |
"coords" hstore DEFAULT NULL /*COMMENT 'for verbatim coordinates, etc.'*/, |
|
| 489 |
PRIMARY KEY ("id"),
|
|
| 490 |
/*KEY "fk_place1_idx" ("parent")*/CHECK (true),
|
|
| 491 |
/*KEY "fk_place_geoplace1_idx" ("geoplace")*/CHECK (true),
|
|
| 492 |
/*CONSTRAINT "fk_place_geoplace1" FOREIGN KEY ("geoplace") REFERENCES "geoplace" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 493 |
/*CONSTRAINT "fk_place1" FOREIGN KEY ("parent") REFERENCES "place" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 494 |
/*CONSTRAINT "fk_place_record1" FOREIGN KEY ("id") REFERENCES "record" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 495 |
) /*ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='"A spatial region" ("DwC":http://rs.tdwg.org/dwc/terms/#dcterms:Location) or point'*/;
|
|
| 496 |
/*!40101 SET character_set_client = @saved_cs_client */; |
|
| 497 |
|
|
| 498 |
-- |
|
| 499 |
-- Dumping data for table "place" |
|
| 500 |
-- |
|
| 501 |
|
|
| 502 |
/*!40000 ALTER TABLE "place" DISABLE KEYS */; |
|
| 503 |
/*!40000 ALTER TABLE "place" ENABLE KEYS */; |
|
| 504 |
|
|
| 505 |
-- |
|
| 506 | 532 |
-- Table structure for table "place_observation" |
| 507 | 533 |
-- |
| 508 | 534 |
|
| ... | ... | |
| 521 | 547 |
/*KEY "fk_place_observation_place1_idx" ("place")*/CHECK (true),
|
| 522 | 548 |
/*KEY "fk_place_observation_geological_context1_idx" ("geological_context")*/CHECK (true),
|
| 523 | 549 |
/*KEY "fk_place_observation_community1_idx" ("community")*/CHECK (true),
|
| 524 |
/*CONSTRAINT "fk_place_observation_place1" FOREIGN KEY ("place") REFERENCES "place" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 550 |
/*CONSTRAINT "fk_place_observation_place1" FOREIGN KEY ("place") REFERENCES "local_place" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 525 | 551 |
/*CONSTRAINT "fk_place_observation_geological_context1" FOREIGN KEY ("geological_context") REFERENCES "geological_context" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
| 526 | 552 |
/*CONSTRAINT "fk_place_observation_community1" FOREIGN KEY ("community") REFERENCES "community" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
| 527 | 553 |
/*CONSTRAINT "fk_place_observation_sampling_event1" FOREIGN KEY ("id") REFERENCES "sampling_event" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
| ... | ... | |
| 550 | 576 |
"footprint_geom_WKT" text DEFAULT NULL, |
| 551 | 577 |
"dimensions" hstore DEFAULT NULL, |
| 552 | 578 |
PRIMARY KEY ("id"),
|
| 553 |
/*CONSTRAINT "fk_subplot_place1" FOREIGN KEY ("id") REFERENCES "place" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 579 |
/*CONSTRAINT "fk_subplot_place1" FOREIGN KEY ("id") REFERENCES "local_place" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 554 | 580 |
) /*ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin*/; |
| 555 | 581 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 556 | 582 |
|
| ... | ... | |
| 785 | 811 |
/*KEY "fk_specimen_collection2_idx" ("current_collection")*/CHECK (true),
|
| 786 | 812 |
/*KEY "fk_specimen_organization3_idx" ("owner_collection")*/CHECK (true),
|
| 787 | 813 |
/*KEY "fk_specimen_party_list1_idx" ("specimenholder_institutions")*/CHECK (true),
|
| 788 |
/*CONSTRAINT "fk_specimen_taxon_occurrence1" FOREIGN KEY ("id") REFERENCES "reobservable" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 789 |
/*CONSTRAINT "fk_specimen_collection1" FOREIGN KEY ("orig_collection") REFERENCES "collection" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 814 |
/*CONSTRAINT "fk_specimen_organization3" FOREIGN KEY ("owner_collection") REFERENCES "collection" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 815 |
/*CONSTRAINT "fk_specimen_taxon_observation1" FOREIGN KEY ("collection_event") REFERENCES "taxon_observation" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 816 |
/*CONSTRAINT "fk_specimen_individual1" FOREIGN KEY ("individual") REFERENCES "individual" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 790 | 817 |
/*CONSTRAINT "fk_specimen_collection2" FOREIGN KEY ("current_collection") REFERENCES "collection" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
| 791 |
/*CONSTRAINT "fk_specimen_individual1" FOREIGN KEY ("individual") REFERENCES "individual" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 792 |
/*CONSTRAINT "fk_specimen_organization3" FOREIGN KEY ("owner_collection") REFERENCES "collection" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 793 | 818 |
/*CONSTRAINT "fk_specimen_party_list1" FOREIGN KEY ("specimenholder_institutions") REFERENCES "party_list" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
| 794 |
/*CONSTRAINT "fk_specimen_taxon_observation1" FOREIGN KEY ("collection_event") REFERENCES "taxon_observation" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 819 |
/*CONSTRAINT "fk_specimen_collection1" FOREIGN KEY ("orig_collection") REFERENCES "collection" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 820 |
/*CONSTRAINT "fk_specimen_taxon_occurrence1" FOREIGN KEY ("id") REFERENCES "reobservable" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 795 | 821 |
) /*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'*/;
|
| 796 | 822 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 797 | 823 |
|
| ... | ... | |
| 1016 | 1042 |
/*CONSTRAINT "taxon_determination_unique" */UNIQUE ("taxon_assertion","identified_by"),
|
| 1017 | 1043 |
/*KEY "fk_taxon_occurrence_has_qualified_taxon1_idx" ("taxon_assertion")*/CHECK (true),
|
| 1018 | 1044 |
/*KEY "fk_taxon_determination_party_list1_idx" ("identified_by")*/CHECK (true),
|
| 1019 |
/*CONSTRAINT "fk_taxon_determination_taxon_observation1" FOREIGN KEY ("id") REFERENCES "taxon_observation" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 1045 |
/*CONSTRAINT "fk_taxon_occurrence_has_qualified_taxon1" FOREIGN KEY ("taxon_assertion") REFERENCES "taxon_assertion" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
|
| 1020 | 1046 |
/*CONSTRAINT "fk_taxon_determination_party_list1" FOREIGN KEY ("identified_by") REFERENCES "party_list" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
|
| 1021 |
/*CONSTRAINT "fk_taxon_occurrence_has_qualified_taxon1" FOREIGN KEY ("taxon_assertion") REFERENCES "taxon_assertion" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 1047 |
/*CONSTRAINT "fk_taxon_determination_taxon_observation1" FOREIGN KEY ("id") REFERENCES "taxon_observation" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
|
|
| 1022 | 1048 |
) /*ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='An assertion that a [[VegCore#TaxonOccurrence|TaxonOccurrence]] is a particular [[VegCore#Taxon|Taxon]]'*/; |
| 1023 | 1049 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 1024 | 1050 |
|
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.