| 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 |
|
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