Revision 1329
Added by Aaron Marcuse-Kubitza over 12 years ago
| schemas/vegbien.my.sql | ||
|---|---|---|
| 1347 | 1347 |
CREATE TABLE plantconcept ( |
| 1348 | 1348 |
plantconcept_id int(11) NOT NULL, |
| 1349 | 1349 |
parent_id int(11), |
| 1350 |
scope_id int(11), |
|
| 1351 | 1350 |
plantname_id int(11) NOT NULL, |
| 1352 | 1351 |
plantcode text, |
| 1353 | 1352 |
plantdescription text, |
| ... | ... | |
| 1458 | 1457 |
|
| 1459 | 1458 |
CREATE TABLE plantname ( |
| 1460 | 1459 |
plantname_id int(11) NOT NULL, |
| 1460 |
scope_id int(11), |
|
| 1461 |
rank text NOT NULL, |
|
| 1461 | 1462 |
plantname text NOT NULL, |
| 1462 |
rank text NOT NULL
|
|
| 1463 |
accessioncode text
|
|
| 1463 | 1464 |
); |
| 1464 | 1465 |
|
| 1465 | 1466 |
|
| ... | ... | |
| 3934 | 3935 |
|
| 3935 | 3936 |
|
| 3936 | 3937 |
-- |
| 3937 |
-- Name: fki_plantconcept_scope_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 3938 |
-- |
|
| 3939 |
|
|
| 3940 |
CREATE INDEX fki_plantconcept_scope_id ON plantconcept (scope_id); |
|
| 3941 |
|
|
| 3942 |
|
|
| 3943 |
-- |
|
| 3944 | 3938 |
-- Name: fki_plantconceptscope_locationevent_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 3945 | 3939 |
-- |
| 3946 | 3940 |
|
| ... | ... | |
| 3962 | 3956 |
|
| 3963 | 3957 |
|
| 3964 | 3958 |
-- |
| 3959 |
-- Name: fki_plantname_scope_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 3960 |
-- |
|
| 3961 |
|
|
| 3962 |
CREATE INDEX fki_plantname_scope_id ON plantname (scope_id); |
|
| 3963 |
|
|
| 3964 |
|
|
| 3965 |
-- |
|
| 3965 | 3966 |
-- Name: fki_plantobservation_plant_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 3966 | 3967 |
-- |
| 3967 | 3968 |
|
| ... | ... | |
| 5226 | 5227 |
|
| 5227 | 5228 |
|
| 5228 | 5229 |
-- |
| 5229 |
-- Name: plantconcept_scope_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5230 |
-- |
|
| 5231 |
|
|
| 5232 |
ALTER TABLE plantconcept |
|
| 5233 |
ADD CONSTRAINT plantconcept_scope_id FOREIGN KEY (scope_id) REFERENCES plantconceptscope(plantconceptscope_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5234 |
|
|
| 5235 |
|
|
| 5236 |
-- |
|
| 5237 | 5230 |
-- Name: plantconceptscope_locationevent_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5238 | 5231 |
-- |
| 5239 | 5232 |
|
| ... | ... | |
| 5290 | 5283 |
|
| 5291 | 5284 |
|
| 5292 | 5285 |
-- |
| 5286 |
-- Name: plantname_scope_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5287 |
-- |
|
| 5288 |
|
|
| 5289 |
ALTER TABLE plantname |
|
| 5290 |
ADD CONSTRAINT plantname_scope_id FOREIGN KEY (scope_id) REFERENCES plantconceptscope(plantconceptscope_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5291 |
|
|
| 5292 |
|
|
| 5293 |
-- |
|
| 5293 | 5294 |
-- Name: plantobservation_plant_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5294 | 5295 |
-- |
| 5295 | 5296 |
|
| schemas/vegbien.sql | ||
|---|---|---|
| 1604 | 1604 |
CREATE TABLE plantconcept ( |
| 1605 | 1605 |
plantconcept_id integer NOT NULL, |
| 1606 | 1606 |
parent_id integer, |
| 1607 |
scope_id integer, |
|
| 1608 | 1607 |
plantname_id integer NOT NULL, |
| 1609 | 1608 |
plantcode text, |
| 1610 | 1609 |
plantdescription text, |
| ... | ... | |
| 1735 | 1734 |
|
| 1736 | 1735 |
CREATE TABLE plantname ( |
| 1737 | 1736 |
plantname_id integer NOT NULL, |
| 1737 |
scope_id integer, |
|
| 1738 |
rank taxonrank NOT NULL, |
|
| 1738 | 1739 |
plantname text NOT NULL, |
| 1739 |
rank taxonrank NOT NULL
|
|
| 1740 |
accessioncode text
|
|
| 1740 | 1741 |
); |
| 1741 | 1742 |
|
| 1742 | 1743 |
|
| ... | ... | |
| 4346 | 4347 |
|
| 4347 | 4348 |
|
| 4348 | 4349 |
-- |
| 4349 |
-- Name: fki_plantconcept_scope_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4350 |
-- |
|
| 4351 |
|
|
| 4352 |
CREATE INDEX fki_plantconcept_scope_id ON plantconcept USING btree (scope_id); |
|
| 4353 |
|
|
| 4354 |
|
|
| 4355 |
-- |
|
| 4356 | 4350 |
-- Name: fki_plantconceptscope_locationevent_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4357 | 4351 |
-- |
| 4358 | 4352 |
|
| ... | ... | |
| 4374 | 4368 |
|
| 4375 | 4369 |
|
| 4376 | 4370 |
-- |
| 4371 |
-- Name: fki_plantname_scope_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4372 |
-- |
|
| 4373 |
|
|
| 4374 |
CREATE INDEX fki_plantname_scope_id ON plantname USING btree (scope_id); |
|
| 4375 |
|
|
| 4376 |
|
|
| 4377 |
-- |
|
| 4377 | 4378 |
-- Name: fki_plantobservation_plant_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4378 | 4379 |
-- |
| 4379 | 4380 |
|
| ... | ... | |
| 4727 | 4728 |
-- Name: plantconcept_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4728 | 4729 |
-- |
| 4729 | 4730 |
|
| 4730 |
CREATE UNIQUE INDEX plantconcept_unique ON plantconcept USING btree ((COALESCE(parent_id, 0)), (COALESCE(scope_id, 0)), plantname_id);
|
|
| 4731 |
CREATE UNIQUE INDEX plantconcept_unique ON plantconcept USING btree ((COALESCE(parent_id, 0)), plantname_id); |
|
| 4731 | 4732 |
|
| 4732 | 4733 |
|
| 4733 | 4734 |
-- |
| ... | ... | |
| 5656 | 5657 |
|
| 5657 | 5658 |
|
| 5658 | 5659 |
-- |
| 5659 |
-- Name: plantconcept_scope_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5660 |
-- |
|
| 5661 |
|
|
| 5662 |
ALTER TABLE ONLY plantconcept |
|
| 5663 |
ADD CONSTRAINT plantconcept_scope_id FOREIGN KEY (scope_id) REFERENCES plantconceptscope(plantconceptscope_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5664 |
|
|
| 5665 |
|
|
| 5666 |
-- |
|
| 5667 | 5660 |
-- Name: plantconceptscope_locationevent_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5668 | 5661 |
-- |
| 5669 | 5662 |
|
| ... | ... | |
| 5720 | 5713 |
|
| 5721 | 5714 |
|
| 5722 | 5715 |
-- |
| 5716 |
-- Name: plantname_scope_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5717 |
-- |
|
| 5718 |
|
|
| 5719 |
ALTER TABLE ONLY plantname |
|
| 5720 |
ADD CONSTRAINT plantname_scope_id FOREIGN KEY (scope_id) REFERENCES plantconceptscope(plantconceptscope_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5721 |
|
|
| 5722 |
|
|
| 5723 |
-- |
|
| 5723 | 5724 |
-- Name: plantobservation_plant_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5724 | 5725 |
-- |
| 5725 | 5726 |
|
Also available in: Unified diff
vegbien.sql: Moved scope_id from plantconcept to plantname, since plantnames themselves are scoped, not just the plantconcepts that use them (e.g. "sp. 1" has different meanings in different scopes, so it should not be shared between scopes). plantname: Added accessioncode.