Revision 4934
Added by Aaron Marcuse-Kubitza about 12 years ago
| vegbien.sql | ||
|---|---|---|
| 1132 | 1132 |
forma text, |
| 1133 | 1133 |
cultivar text, |
| 1134 | 1134 |
otherranks rankedtaxonname[], |
| 1135 |
scope_id integer, |
|
| 1135 | 1136 |
accessioncode text |
| 1136 | 1137 |
); |
| 1137 | 1138 |
|
| ... | ... | |
| 1158 | 1159 |
|
| 1159 | 1160 |
|
| 1160 | 1161 |
-- |
| 1162 |
-- Name: COLUMN taxonpath.scope_id; Type: COMMENT; Schema: public; Owner: - |
|
| 1163 |
-- |
|
| 1164 |
|
|
| 1165 |
COMMENT ON COLUMN taxonpath.scope_id IS 'The morphospecies scope'; |
|
| 1166 |
|
|
| 1167 |
|
|
| 1168 |
-- |
|
| 1161 | 1169 |
-- Name: analytical_db_view; Type: VIEW; Schema: public; Owner: - |
| 1162 | 1170 |
-- |
| 1163 | 1171 |
|
| ... | ... | |
| 2910 | 2918 |
CREATE TABLE taxon ( |
| 2911 | 2919 |
taxon_id integer NOT NULL, |
| 2912 | 2920 |
parent_id integer, |
| 2913 |
scope_id integer, |
|
| 2914 | 2921 |
rank taxonrank NOT NULL, |
| 2915 | 2922 |
verbatimrank text, |
| 2916 | 2923 |
taxonname text NOT NULL, |
| ... | ... | |
| 4608 | 4615 |
-- Name: taxon_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4609 | 4616 |
-- |
| 4610 | 4617 |
|
| 4611 |
CREATE UNIQUE INDEX taxon_unique ON taxon USING btree ((COALESCE(parent_id, 2147483647)), taxonname, rank, (COALESCE(scope_id, 2147483647)), (COALESCE(author_id, 2147483647)), (COALESCE(authority_id, 2147483647)));
|
|
| 4618 |
CREATE UNIQUE INDEX taxon_unique ON taxon USING btree ((COALESCE(parent_id, 2147483647)), taxonname, rank, (COALESCE(author_id, 2147483647)), (COALESCE(authority_id, 2147483647))); |
|
| 4612 | 4619 |
|
| 4613 | 4620 |
|
| 4614 | 4621 |
-- |
| ... | ... | |
| 4671 | 4678 |
-- Name: taxonpath_unique_within_datasource_by_name; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4672 | 4679 |
-- |
| 4673 | 4680 |
|
| 4674 |
CREATE UNIQUE INDEX taxonpath_unique_within_datasource_by_name ON taxonpath USING btree (datasource_id, (COALESCE(taxon_id, 2147483647)), (COALESCE(scientificname, '\\N'::text)), (COALESCE(scientificnameauthor, '\\N'::text)), (COALESCE(scientificnamewithauthor, '\\N'::text)), (COALESCE(domain, '\\N'::text)), (COALESCE(kingdom, '\\N'::text)), (COALESCE(phylum, '\\N'::text)), (COALESCE(class, '\\N'::text)), (COALESCE("order", '\\N'::text)), (COALESCE(family, '\\N'::text)), (COALESCE(genus, '\\N'::text)), (COALESCE(species, '\\N'::text)), (COALESCE(subspecies, '\\N'::text)), (COALESCE(variety, '\\N'::text)), (COALESCE(forma, '\\N'::text)), (COALESCE(cultivar, '\\N'::text))) WHERE (plantcode IS NULL);
|
|
| 4681 |
CREATE UNIQUE INDEX taxonpath_unique_within_datasource_by_name ON taxonpath USING btree (datasource_id, (COALESCE(taxon_id, 2147483647)), (COALESCE(scientificname, '\\N'::text)), (COALESCE(scientificnameauthor, '\\N'::text)), (COALESCE(scientificnamewithauthor, '\\N'::text)), (COALESCE(domain, '\\N'::text)), (COALESCE(kingdom, '\\N'::text)), (COALESCE(phylum, '\\N'::text)), (COALESCE(class, '\\N'::text)), (COALESCE("order", '\\N'::text)), (COALESCE(family, '\\N'::text)), (COALESCE(genus, '\\N'::text)), (COALESCE(species, '\\N'::text)), (COALESCE(subspecies, '\\N'::text)), (COALESCE(variety, '\\N'::text)), (COALESCE(forma, '\\N'::text)), (COALESCE(cultivar, '\\N'::text)), (COALESCE(scope_id, 2147483647))) WHERE (plantcode IS NULL);
|
|
| 4675 | 4682 |
|
| 4676 | 4683 |
|
| 4677 | 4684 |
-- |
| ... | ... | |
| 5582 | 5589 |
|
| 5583 | 5590 |
|
| 5584 | 5591 |
-- |
| 5585 |
-- Name: taxon_scope_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5586 |
-- |
|
| 5587 |
|
|
| 5588 |
ALTER TABLE ONLY taxon |
|
| 5589 |
ADD CONSTRAINT taxon_scope_id_fkey FOREIGN KEY (scope_id) REFERENCES taxonscope(taxonscope_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5590 |
|
|
| 5591 |
|
|
| 5592 |
-- |
|
| 5593 | 5592 |
-- Name: taxonalt_taxondetermination_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5594 | 5593 |
-- |
| 5595 | 5594 |
|
| ... | ... | |
| 5662 | 5661 |
|
| 5663 | 5662 |
|
| 5664 | 5663 |
-- |
| 5664 |
-- Name: taxonpath_scope_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5665 |
-- |
|
| 5666 |
|
|
| 5667 |
ALTER TABLE ONLY taxonpath |
|
| 5668 |
ADD CONSTRAINT taxonpath_scope_id_fkey FOREIGN KEY (scope_id) REFERENCES taxonscope(taxonscope_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5669 |
|
|
| 5670 |
|
|
| 5671 |
-- |
|
| 5665 | 5672 |
-- Name: taxonpath_taxon_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5666 | 5673 |
-- |
| 5667 | 5674 |
|
Also available in: Unified diff
schemas/vegbien.sql: Scope taxonpath instead of taxon with taxonscope, because a morphospecies name is specific to a datasource entity, so it should go in the datasource-specific taxonpath table instead of the datasource-general taxon table