Revision 8573
Added by Aaron Marcuse-Kubitza over 11 years ago
| schemas/VegCore/VegCore.my.sql | ||
|---|---|---|
| 73 | 73 |
-- Table `taxon_name` |
| 74 | 74 |
-- ----------------------------------------------------- |
| 75 | 75 |
CREATE TABLE IF NOT EXISTS `taxon_name` ( |
| 76 |
`name` TEXT NOT NULL , |
|
| 77 |
PRIMARY KEY (`name`) , |
|
| 78 |
CONSTRAINT `fk_taxon_name_taxon_string1` |
|
| 79 |
FOREIGN KEY (`name` ) |
|
| 80 |
REFERENCES `taxon_string` (`string` ) |
|
| 81 |
ON DELETE CASCADE |
|
| 82 |
ON UPDATE CASCADE) |
|
| 83 |
ENGINE = InnoDB |
|
| 84 |
DEFAULT CHARACTER SET = latin1 |
|
| 85 |
COLLATE = latin1_swedish_ci; |
|
| 86 |
|
|
| 87 |
|
|
| 88 |
-- ----------------------------------------------------- |
|
| 89 |
-- Table `taxon_concept` |
|
| 90 |
-- ----------------------------------------------------- |
|
| 91 |
CREATE TABLE IF NOT EXISTS `taxon_concept` ( |
|
| 92 | 76 |
`id` TEXT NOT NULL , |
| 93 |
`according_to` TEXT NOT NULL , |
|
| 94 |
`taxon_name` TEXT NOT NULL , |
|
| 95 |
`parent` TEXT NOT NULL , |
|
| 77 |
`unique_name` TEXT NOT NULL , |
|
| 78 |
`formal_name` TEXT NULL , |
|
| 79 |
`taxon_name` TEXT NULL , |
|
| 80 |
`author` TEXT NULL , |
|
| 96 | 81 |
`rank` TEXT NULL , |
| 97 |
`accepted_taxon` TEXT NULL , |
|
| 98 | 82 |
PRIMARY KEY (`id`) , |
| 99 |
INDEX `fk_taxon_taxon1` (`parent` ASC) , |
|
| 100 |
INDEX `fk_taxon_concept_source1` (`according_to` ASC) , |
|
| 101 |
INDEX `fk_taxon_concept_taxon_concept1` (`accepted_taxon` ASC) , |
|
| 102 |
INDEX `fk_taxon_concept_taxon_name1` (`taxon_name` ASC) , |
|
| 103 |
UNIQUE INDEX `taxon_concept_unique_name` (`according_to` ASC, `taxon_name` ASC) , |
|
| 104 |
CONSTRAINT `fk_taxon_record1` |
|
| 83 |
INDEX `fk_taxon_concept_taxon_string10` (`unique_name` ASC) , |
|
| 84 |
CONSTRAINT `fk_taxon_record10` |
|
| 105 | 85 |
FOREIGN KEY (`id` ) |
| 106 | 86 |
REFERENCES `record` (`id` ) |
| 107 | 87 |
ON DELETE CASCADE |
| 108 | 88 |
ON UPDATE CASCADE, |
| 109 |
CONSTRAINT `fk_taxon_taxon1` |
|
| 110 |
FOREIGN KEY (`parent` ) |
|
| 111 |
REFERENCES `taxon_concept` (`id` ) |
|
| 112 |
ON DELETE CASCADE |
|
| 113 |
ON UPDATE CASCADE, |
|
| 114 |
CONSTRAINT `fk_taxon_concept_source1` |
|
| 115 |
FOREIGN KEY (`according_to` ) |
|
| 116 |
REFERENCES `source` (`id` ) |
|
| 117 |
ON DELETE CASCADE |
|
| 118 |
ON UPDATE CASCADE, |
|
| 119 |
CONSTRAINT `fk_taxon_concept_taxon_concept1` |
|
| 120 |
FOREIGN KEY (`accepted_taxon` ) |
|
| 121 |
REFERENCES `taxon_concept` (`id` ) |
|
| 122 |
ON DELETE CASCADE |
|
| 123 |
ON UPDATE CASCADE, |
|
| 124 |
CONSTRAINT `fk_taxon_concept_taxon_name1` |
|
| 125 |
FOREIGN KEY (`taxon_name` ) |
|
| 126 |
REFERENCES `taxon_name` (`name` ) |
|
| 127 |
ON DELETE CASCADE |
|
| 128 |
ON UPDATE CASCADE, |
|
| 129 |
CONSTRAINT `fk_taxon_concept_taxon_string1` |
|
| 130 |
FOREIGN KEY (`taxon_name` ) |
|
| 89 |
CONSTRAINT `fk_taxon_concept_taxon_string10` |
|
| 90 |
FOREIGN KEY (`unique_name` ) |
|
| 131 | 91 |
REFERENCES `taxon_string` (`string` ) |
| 132 | 92 |
ON DELETE CASCADE |
| 133 | 93 |
ON UPDATE CASCADE) |
| ... | ... | |
| 137 | 97 |
|
| 138 | 98 |
|
| 139 | 99 |
-- ----------------------------------------------------- |
| 140 |
-- Table `taxon_path` |
|
| 141 |
-- ----------------------------------------------------- |
|
| 142 |
CREATE TABLE IF NOT EXISTS `taxon_path` ( |
|
| 143 |
`id` TEXT NOT NULL , |
|
| 144 |
`family` TEXT NULL , |
|
| 145 |
`genus` TEXT NULL , |
|
| 146 |
`specific_epithet` TEXT NULL , |
|
| 147 |
`ranks` SET('hstore') NULL ,
|
|
| 148 |
PRIMARY KEY (`id`) , |
|
| 149 |
CONSTRAINT `fk_taxon_path_taxon_concept1` |
|
| 150 |
FOREIGN KEY (`id` ) |
|
| 151 |
REFERENCES `taxon_concept` (`id` ) |
|
| 152 |
ON DELETE CASCADE |
|
| 153 |
ON UPDATE CASCADE) |
|
| 154 |
ENGINE = InnoDB |
|
| 155 |
DEFAULT CHARACTER SET = latin1 |
|
| 156 |
COLLATE = latin1_swedish_ci; |
|
| 157 |
|
|
| 158 |
|
|
| 159 |
-- ----------------------------------------------------- |
|
| 160 | 100 |
-- Table `parsed_taxon_assertion` |
| 161 | 101 |
-- ----------------------------------------------------- |
| 162 | 102 |
CREATE TABLE IF NOT EXISTS `parsed_taxon_assertion` ( |
| ... | ... | |
| 164 | 104 |
`matched_taxon` TEXT NULL , |
| 165 | 105 |
`match_score` FLOAT NULL , |
| 166 | 106 |
PRIMARY KEY (`id`) , |
| 167 |
INDEX `fk_parsed_taxon_assertion_taxon_path1` (`matched_taxon` ASC) ,
|
|
| 107 |
INDEX `fk_parsed_taxon_assertion_taxon_name1` (`matched_taxon` ASC) ,
|
|
| 168 | 108 |
CONSTRAINT `fk_matched_taxon_qualified_taxon10` |
| 169 | 109 |
FOREIGN KEY (`id` ) |
| 170 | 110 |
REFERENCES `taxon_assertion` (`id` ) |
| 171 | 111 |
ON DELETE CASCADE |
| 172 | 112 |
ON UPDATE CASCADE, |
| 173 |
CONSTRAINT `fk_parsed_taxon_assertion_taxon_path1`
|
|
| 113 |
CONSTRAINT `fk_parsed_taxon_assertion_taxon_name1`
|
|
| 174 | 114 |
FOREIGN KEY (`matched_taxon` ) |
| 175 |
REFERENCES `taxon_path` (`id` )
|
|
| 115 |
REFERENCES `taxon_name` (`id` )
|
|
| 176 | 116 |
ON DELETE CASCADE |
| 177 | 117 |
ON UPDATE CASCADE) |
| 178 | 118 |
ENGINE = InnoDB |
| ... | ... | |
| 208 | 148 |
`cf_aff` TEXT NULL , |
| 209 | 149 |
PRIMARY KEY (`id`) , |
| 210 | 150 |
INDEX `fk_taxon_assertion_taxon_string1` (`string` ASC) , |
| 211 |
INDEX `fk_taxon_assertion_taxon_concept1` (`taxon` ASC) ,
|
|
| 151 |
INDEX `fk_taxon_assertion_taxon_name1` (`taxon` ASC) ,
|
|
| 212 | 152 |
CONSTRAINT `fk_qualified_taxon_record1` |
| 213 | 153 |
FOREIGN KEY (`id` ) |
| 214 | 154 |
REFERENCES `record` (`id` ) |
| ... | ... | |
| 219 | 159 |
REFERENCES `taxon_string` (`string` ) |
| 220 | 160 |
ON DELETE CASCADE |
| 221 | 161 |
ON UPDATE CASCADE, |
| 222 |
CONSTRAINT `fk_taxon_assertion_taxon_concept1`
|
|
| 162 |
CONSTRAINT `fk_taxon_assertion_taxon_name1`
|
|
| 223 | 163 |
FOREIGN KEY (`taxon` ) |
| 224 |
REFERENCES `taxon_concept` (`id` )
|
|
| 164 |
REFERENCES `taxon_name` (`id` )
|
|
| 225 | 165 |
ON DELETE CASCADE |
| 226 | 166 |
ON UPDATE CASCADE) |
| 227 | 167 |
ENGINE = InnoDB |
| ... | ... | |
| 620 | 560 |
|
| 621 | 561 |
|
| 622 | 562 |
-- ----------------------------------------------------- |
| 563 |
-- Table `taxon_concept` |
|
| 564 |
-- ----------------------------------------------------- |
|
| 565 |
CREATE TABLE IF NOT EXISTS `taxon_concept` ( |
|
| 566 |
`id` TEXT NOT NULL , |
|
| 567 |
`according_to` TEXT NOT NULL , |
|
| 568 |
`parent` TEXT NOT NULL , |
|
| 569 |
`accepted_taxon` TEXT NULL , |
|
| 570 |
PRIMARY KEY (`id`) , |
|
| 571 |
INDEX `fk_taxon_taxon1` (`parent` ASC) , |
|
| 572 |
INDEX `fk_taxon_concept_source1` (`according_to` ASC) , |
|
| 573 |
INDEX `fk_taxon_concept_taxon_concept1` (`accepted_taxon` ASC) , |
|
| 574 |
UNIQUE INDEX `taxon_concept_unique_name` (`according_to` ASC) , |
|
| 575 |
CONSTRAINT `fk_taxon_taxon1` |
|
| 576 |
FOREIGN KEY (`parent` ) |
|
| 577 |
REFERENCES `taxon_concept` (`id` ) |
|
| 578 |
ON DELETE CASCADE |
|
| 579 |
ON UPDATE CASCADE, |
|
| 580 |
CONSTRAINT `fk_taxon_concept_source1` |
|
| 581 |
FOREIGN KEY (`according_to` ) |
|
| 582 |
REFERENCES `source` (`id` ) |
|
| 583 |
ON DELETE CASCADE |
|
| 584 |
ON UPDATE CASCADE, |
|
| 585 |
CONSTRAINT `fk_taxon_concept_taxon_concept1` |
|
| 586 |
FOREIGN KEY (`accepted_taxon` ) |
|
| 587 |
REFERENCES `taxon_concept` (`id` ) |
|
| 588 |
ON DELETE CASCADE |
|
| 589 |
ON UPDATE CASCADE, |
|
| 590 |
CONSTRAINT `fk_taxon_concept_taxon_name1` |
|
| 591 |
FOREIGN KEY (`id` ) |
|
| 592 |
REFERENCES `taxon_name` (`id` ) |
|
| 593 |
ON DELETE CASCADE |
|
| 594 |
ON UPDATE CASCADE) |
|
| 595 |
ENGINE = InnoDB |
|
| 596 |
DEFAULT CHARACTER SET = latin1 |
|
| 597 |
COLLATE = latin1_swedish_ci; |
|
| 598 |
|
|
| 599 |
|
|
| 600 |
-- ----------------------------------------------------- |
|
| 623 | 601 |
-- Table `community` |
| 624 | 602 |
-- ----------------------------------------------------- |
| 625 | 603 |
CREATE TABLE IF NOT EXISTS `community` ( |
| ... | ... | |
| 982 | 960 |
-- Table `TNRS taxonomic scrubbing steps` |
| 983 | 961 |
-- ----------------------------------------------------- |
| 984 | 962 |
CREATE TABLE IF NOT EXISTS `TNRS taxonomic scrubbing steps` ( |
| 985 |
) |
|
| 986 |
ENGINE = InnoDB; |
|
| 963 |
`id` TEXT NOT NULL , |
|
| 964 |
PRIMARY KEY (`id`) ) |
|
| 965 |
ENGINE = InnoDB |
|
| 966 |
DEFAULT CHARACTER SET = latin1 |
|
| 967 |
COLLATE = latin1_swedish_ci; |
|
| 987 | 968 |
|
| 988 | 969 |
|
| 989 | 970 |
-- ----------------------------------------------------- |
| ... | ... | |
| 1002 | 983 |
COLLATE = latin1_swedish_ci; |
| 1003 | 984 |
|
| 1004 | 985 |
|
| 986 |
-- ----------------------------------------------------- |
|
| 987 |
-- Table `taxon_path` |
|
| 988 |
-- ----------------------------------------------------- |
|
| 989 |
CREATE TABLE IF NOT EXISTS `taxon_path` ( |
|
| 990 |
`id` TEXT NOT NULL , |
|
| 991 |
`family` TEXT NULL , |
|
| 992 |
`genus` TEXT NULL , |
|
| 993 |
`specific_epithet` TEXT NULL , |
|
| 994 |
`ranks` SET('hstore') NULL ,
|
|
| 995 |
PRIMARY KEY (`id`) , |
|
| 996 |
CONSTRAINT `fk_taxon_path_taxon_name1` |
|
| 997 |
FOREIGN KEY (`id` ) |
|
| 998 |
REFERENCES `taxon_name` (`id` ) |
|
| 999 |
ON DELETE CASCADE |
|
| 1000 |
ON UPDATE CASCADE) |
|
| 1001 |
ENGINE = InnoDB |
|
| 1002 |
DEFAULT CHARACTER SET = latin1 |
|
| 1003 |
COLLATE = latin1_swedish_ci; |
|
| 1005 | 1004 |
|
| 1005 |
|
|
| 1006 |
|
|
| 1006 | 1007 |
SET SQL_MODE=@OLD_SQL_MODE; |
| 1007 | 1008 |
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; |
| 1008 | 1009 |
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; |
Also available in: Unified diff
schemas/VegCore/VegCore.ERD.mwb: split taxon_concept into taxon_name (containing the components of a name) and taxon_concept (containing the taxon's formal specification and relationships to other taxon_concepts). this avoids requiring a taxon_concept entry for names that are not true taxon concepts. taxon_name: added taxon name/author-related components. TNRS taxonomic scrubbing steps: added dummy id column to avoid sync problems with empty tables.