Revision 1328
Added by Aaron Marcuse-Kubitza almost 13 years ago
| vegbien.sql | ||
|---|---|---|
| 1603 | 1603 |
|
| 1604 | 1604 |
CREATE TABLE plantconcept ( |
| 1605 | 1605 |
plantconcept_id integer NOT NULL, |
| 1606 |
parent_id integer, |
|
| 1607 |
scope_id integer, |
|
| 1606 | 1608 |
plantname_id integer NOT NULL, |
| 1607 |
reference_id integer, |
|
| 1608 | 1609 |
plantcode text, |
| 1609 | 1610 |
plantdescription text, |
| 1610 |
d_obscount integer, |
|
| 1611 |
d_currentaccepted boolean, |
|
| 1612 |
accessioncode text, |
|
| 1613 |
scope_id integer |
|
| 1611 |
accessioncode text |
|
| 1614 | 1612 |
); |
| 1615 | 1613 |
|
| 1616 | 1614 |
|
| ... | ... | |
| 1811 | 1809 |
plantstatus_id integer NOT NULL, |
| 1812 | 1810 |
plantconcept_id integer NOT NULL, |
| 1813 | 1811 |
party_id integer, |
| 1814 |
plantparent_id integer, |
|
| 1815 | 1812 |
plantconceptstatus text DEFAULT 'undetermined'::text NOT NULL, |
| 1816 | 1813 |
reference_id integer, |
| 1817 | 1814 |
plantpartycomments text, |
| 1818 |
plantparentname text, |
|
| 1819 | 1815 |
startdate timestamp with time zone, |
| 1820 | 1816 |
stopdate timestamp with time zone, |
| 1821 | 1817 |
accessioncode text |
| ... | ... | |
| 4343 | 4339 |
|
| 4344 | 4340 |
|
| 4345 | 4341 |
-- |
| 4342 |
-- Name: fki_plantconcept_parent_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4343 |
-- |
|
| 4344 |
|
|
| 4345 |
CREATE INDEX fki_plantconcept_parent_id ON plantconcept USING btree (parent_id); |
|
| 4346 |
|
|
| 4347 |
|
|
| 4348 |
-- |
|
| 4346 | 4349 |
-- Name: fki_plantconcept_scope_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4347 | 4350 |
-- |
| 4348 | 4351 |
|
| ... | ... | |
| 4714 | 4717 |
|
| 4715 | 4718 |
|
| 4716 | 4719 |
-- |
| 4717 |
-- Name: plantconcept_dobscount_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4718 |
-- |
|
| 4719 |
|
|
| 4720 |
CREATE INDEX plantconcept_dobscount_x ON plantconcept USING btree (d_obscount); |
|
| 4721 |
|
|
| 4722 |
|
|
| 4723 |
-- |
|
| 4724 | 4720 |
-- Name: plantconcept_plantname_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4725 | 4721 |
-- |
| 4726 | 4722 |
|
| ... | ... | |
| 4728 | 4724 |
|
| 4729 | 4725 |
|
| 4730 | 4726 |
-- |
| 4731 |
-- Name: plantconcept_reference_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4732 |
-- |
|
| 4733 |
|
|
| 4734 |
CREATE INDEX plantconcept_reference_id_x ON plantconcept USING btree (reference_id); |
|
| 4735 |
|
|
| 4736 |
|
|
| 4737 |
-- |
|
| 4738 | 4727 |
-- Name: plantconcept_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4739 | 4728 |
-- |
| 4740 | 4729 |
|
| 4741 |
CREATE UNIQUE INDEX plantconcept_unique ON plantconcept USING btree ((COALESCE(scope_id, 0)), (COALESCE(reference_id, 0)), plantname_id);
|
|
| 4730 |
CREATE UNIQUE INDEX plantconcept_unique ON plantconcept USING btree ((COALESCE(parent_id, 0)), (COALESCE(scope_id, 0)), plantname_id);
|
|
| 4742 | 4731 |
|
| 4743 | 4732 |
|
| 4744 | 4733 |
-- |
| ... | ... | |
| 4812 | 4801 |
|
| 4813 | 4802 |
|
| 4814 | 4803 |
-- |
| 4815 |
-- Name: plantstatus_plantparent_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4816 |
-- |
|
| 4817 |
|
|
| 4818 |
CREATE INDEX plantstatus_plantparent_id_x ON plantstatus USING btree (plantparent_id); |
|
| 4819 |
|
|
| 4820 |
|
|
| 4821 |
-- |
|
| 4822 | 4804 |
-- Name: plantstatus_reference_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4823 | 4805 |
-- |
| 4824 | 4806 |
|
| ... | ... | |
| 5658 | 5640 |
|
| 5659 | 5641 |
|
| 5660 | 5642 |
-- |
| 5661 |
-- Name: plantconcept_plantname_id; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5643 |
-- Name: plantconcept_parent_id; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5662 | 5644 |
-- |
| 5663 | 5645 |
|
| 5664 | 5646 |
ALTER TABLE ONLY plantconcept |
| 5665 |
ADD CONSTRAINT plantconcept_plantname_id FOREIGN KEY (plantname_id) REFERENCES plantname(plantname_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5647 |
ADD CONSTRAINT plantconcept_parent_id FOREIGN KEY (parent_id) REFERENCES plantconcept(plantconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5666 | 5648 |
|
| 5667 | 5649 |
|
| 5668 | 5650 |
-- |
| 5669 |
-- Name: plantconcept_reference_id; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5651 |
-- Name: plantconcept_plantname_id; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5670 | 5652 |
-- |
| 5671 | 5653 |
|
| 5672 | 5654 |
ALTER TABLE ONLY plantconcept |
| 5673 |
ADD CONSTRAINT plantconcept_reference_id FOREIGN KEY (reference_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5655 |
ADD CONSTRAINT plantconcept_plantname_id FOREIGN KEY (plantname_id) REFERENCES plantname(plantname_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5674 | 5656 |
|
| 5675 | 5657 |
|
| 5676 | 5658 |
-- |
| ... | ... | |
| 5762 | 5744 |
|
| 5763 | 5745 |
|
| 5764 | 5746 |
-- |
| 5765 |
-- Name: plantstatus_plantparent_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5766 |
-- |
|
| 5767 |
|
|
| 5768 |
ALTER TABLE ONLY plantstatus |
|
| 5769 |
ADD CONSTRAINT plantstatus_plantparent_id FOREIGN KEY (plantparent_id) REFERENCES plantconcept(plantconcept_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5770 |
|
|
| 5771 |
|
|
| 5772 |
-- |
|
| 5773 | 5747 |
-- Name: plantstatus_reference_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5774 | 5748 |
-- |
| 5775 | 5749 |
|
Also available in: Unified diff
vegbien.sql: Moved plantconcept parent_id from plantstatus to plantconcept. plantconcept: Removed datasource-specific fields to make it globally unique (one plantconcept for each assigned parent taxon of a plantname, of which there will usually be just one)