Revision 903
Added by Aaron Marcuse-Kubitza about 13 years ago
| vegbien.sql | ||
|---|---|---|
| 1395 | 1395 |
ALTER SEQUENCE locationplace_locationplace_id_seq OWNED BY locationplace.locationplace_id; |
| 1396 | 1396 |
|
| 1397 | 1397 |
|
| 1398 |
SET default_with_oids = false; |
|
| 1399 |
|
|
| 1400 | 1398 |
-- |
| 1401 |
-- Name: morphospecies; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 1402 |
-- |
|
| 1403 |
|
|
| 1404 |
CREATE TABLE morphospecies ( |
|
| 1405 |
morphospecies_id integer NOT NULL, |
|
| 1406 |
locationevent_id integer NOT NULL, |
|
| 1407 |
name text NOT NULL |
|
| 1408 |
); |
|
| 1409 |
|
|
| 1410 |
|
|
| 1411 |
-- |
|
| 1412 |
-- Name: morphospecies_morphospecies_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
|
| 1413 |
-- |
|
| 1414 |
|
|
| 1415 |
CREATE SEQUENCE morphospecies_morphospecies_id_seq |
|
| 1416 |
START WITH 1 |
|
| 1417 |
INCREMENT BY 1 |
|
| 1418 |
NO MINVALUE |
|
| 1419 |
NO MAXVALUE |
|
| 1420 |
CACHE 1; |
|
| 1421 |
|
|
| 1422 |
|
|
| 1423 |
-- |
|
| 1424 |
-- Name: morphospecies_morphospecies_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - |
|
| 1425 |
-- |
|
| 1426 |
|
|
| 1427 |
ALTER SEQUENCE morphospecies_morphospecies_id_seq OWNED BY morphospecies.morphospecies_id; |
|
| 1428 |
|
|
| 1429 |
|
|
| 1430 |
SET default_with_oids = true; |
|
| 1431 |
|
|
| 1432 |
-- |
|
| 1433 | 1399 |
-- Name: namedplace; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 1434 | 1400 |
-- |
| 1435 | 1401 |
|
| ... | ... | |
| 1686 | 1652 |
plantdescription text, |
| 1687 | 1653 |
d_obscount integer, |
| 1688 | 1654 |
d_currentaccepted boolean, |
| 1689 |
accessioncode text |
|
| 1655 |
accessioncode text, |
|
| 1656 |
scope_id integer |
|
| 1690 | 1657 |
); |
| 1691 | 1658 |
|
| 1692 | 1659 |
|
| ... | ... | |
| 1709 | 1676 |
ALTER SEQUENCE plantconcept_plantconcept_id_seq OWNED BY plantconcept.plantconcept_id; |
| 1710 | 1677 |
|
| 1711 | 1678 |
|
| 1679 |
SET default_with_oids = false; |
|
| 1680 |
|
|
| 1712 | 1681 |
-- |
| 1682 |
-- Name: plantconceptscope; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 1683 |
-- |
|
| 1684 |
|
|
| 1685 |
CREATE TABLE plantconceptscope ( |
|
| 1686 |
plantconceptscope_id integer NOT NULL, |
|
| 1687 |
locationevent_id integer, |
|
| 1688 |
project_id integer |
|
| 1689 |
); |
|
| 1690 |
|
|
| 1691 |
|
|
| 1692 |
-- |
|
| 1693 |
-- Name: plantconceptscope_plantconceptscope_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
|
| 1694 |
-- |
|
| 1695 |
|
|
| 1696 |
CREATE SEQUENCE plantconceptscope_plantconceptscope_id_seq |
|
| 1697 |
START WITH 1 |
|
| 1698 |
INCREMENT BY 1 |
|
| 1699 |
NO MINVALUE |
|
| 1700 |
NO MAXVALUE |
|
| 1701 |
CACHE 1; |
|
| 1702 |
|
|
| 1703 |
|
|
| 1704 |
-- |
|
| 1705 |
-- Name: plantconceptscope_plantconceptscope_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - |
|
| 1706 |
-- |
|
| 1707 |
|
|
| 1708 |
ALTER SEQUENCE plantconceptscope_plantconceptscope_id_seq OWNED BY plantconceptscope.plantconceptscope_id; |
|
| 1709 |
|
|
| 1710 |
|
|
| 1711 |
SET default_with_oids = true; |
|
| 1712 |
|
|
| 1713 |
-- |
|
| 1713 | 1714 |
-- Name: plantcorrelation; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 1714 | 1715 |
-- |
| 1715 | 1716 |
|
| ... | ... | |
| 1853 | 1854 |
CREATE TABLE plantstatus ( |
| 1854 | 1855 |
plantstatus_id integer NOT NULL, |
| 1855 | 1856 |
plantconcept_id integer NOT NULL, |
| 1857 |
plantparent_id integer, |
|
| 1858 |
plantlevel text, |
|
| 1856 | 1859 |
reference_id integer, |
| 1857 | 1860 |
plantconceptstatus text DEFAULT 'undetermined'::text NOT NULL, |
| 1858 | 1861 |
plantpartycomments text, |
| 1859 | 1862 |
plantparentname text, |
| 1860 |
plantparentconcept_id integer, |
|
| 1861 |
plantparent_id integer, |
|
| 1862 |
plantlevel text, |
|
| 1863 | 1863 |
party_id integer, |
| 1864 | 1864 |
startdate timestamp with time zone, |
| 1865 | 1865 |
stopdate timestamp with time zone, |
| ... | ... | |
| 2741 | 2741 |
revisions boolean, |
| 2742 | 2742 |
determinationdate timestamp with time zone, |
| 2743 | 2743 |
emb_taxondetermination integer, |
| 2744 |
accessioncode text, |
|
| 2745 |
morphospecies_id integer |
|
| 2744 |
accessioncode text |
|
| 2746 | 2745 |
); |
| 2747 | 2746 |
|
| 2748 | 2747 |
|
| ... | ... | |
| 3621 | 3620 |
|
| 3622 | 3621 |
|
| 3623 | 3622 |
-- |
| 3624 |
-- Name: morphospecies_id; Type: DEFAULT; Schema: public; Owner: - |
|
| 3625 |
-- |
|
| 3626 |
|
|
| 3627 |
ALTER TABLE morphospecies ALTER COLUMN morphospecies_id SET DEFAULT nextval('morphospecies_morphospecies_id_seq'::regclass);
|
|
| 3628 |
|
|
| 3629 |
|
|
| 3630 |
-- |
|
| 3631 | 3623 |
-- Name: namedplace_id; Type: DEFAULT; Schema: public; Owner: - |
| 3632 | 3624 |
-- |
| 3633 | 3625 |
|
| ... | ... | |
| 3684 | 3676 |
|
| 3685 | 3677 |
|
| 3686 | 3678 |
-- |
| 3679 |
-- Name: plantconceptscope_id; Type: DEFAULT; Schema: public; Owner: - |
|
| 3680 |
-- |
|
| 3681 |
|
|
| 3682 |
ALTER TABLE plantconceptscope ALTER COLUMN plantconceptscope_id SET DEFAULT nextval('plantconceptscope_plantconceptscope_id_seq'::regclass);
|
|
| 3683 |
|
|
| 3684 |
|
|
| 3685 |
-- |
|
| 3687 | 3686 |
-- Name: plantcorrelation_id; Type: DEFAULT; Schema: public; Owner: - |
| 3688 | 3687 |
-- |
| 3689 | 3688 |
|
| ... | ... | |
| 4359 | 4358 |
|
| 4360 | 4359 |
|
| 4361 | 4360 |
-- |
| 4362 |
-- Name: morphospecies_keys; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 4363 |
-- |
|
| 4364 |
|
|
| 4365 |
ALTER TABLE ONLY morphospecies |
|
| 4366 |
ADD CONSTRAINT morphospecies_keys UNIQUE (locationevent_id, name); |
|
| 4367 |
|
|
| 4368 |
|
|
| 4369 |
-- |
|
| 4370 |
-- Name: morphospecies_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 4371 |
-- |
|
| 4372 |
|
|
| 4373 |
ALTER TABLE ONLY morphospecies |
|
| 4374 |
ADD CONSTRAINT morphospecies_pkey PRIMARY KEY (morphospecies_id); |
|
| 4375 |
|
|
| 4376 |
|
|
| 4377 |
-- |
|
| 4378 | 4361 |
-- Name: namedplace_keys; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 4379 | 4362 |
-- |
| 4380 | 4363 |
|
| ... | ... | |
| 4447 | 4430 |
|
| 4448 | 4431 |
|
| 4449 | 4432 |
-- |
| 4433 |
-- Name: plantconceptscope_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 4434 |
-- |
|
| 4435 |
|
|
| 4436 |
ALTER TABLE ONLY plantconceptscope |
|
| 4437 |
ADD CONSTRAINT plantconceptscope_pkey PRIMARY KEY (plantconceptscope_id); |
|
| 4438 |
|
|
| 4439 |
|
|
| 4440 |
-- |
|
| 4450 | 4441 |
-- Name: plantcorrelation_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 4451 | 4442 |
-- |
| 4452 | 4443 |
|
| ... | ... | |
| 5287 | 5278 |
|
| 5288 | 5279 |
|
| 5289 | 5280 |
-- |
| 5281 |
-- Name: fki_plantconcept_scope_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 5282 |
-- |
|
| 5283 |
|
|
| 5284 |
CREATE INDEX fki_plantconcept_scope_id ON plantconcept USING btree (scope_id); |
|
| 5285 |
|
|
| 5286 |
|
|
| 5287 |
-- |
|
| 5288 |
-- Name: fki_plantconceptscope_locationevent_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 5289 |
-- |
|
| 5290 |
|
|
| 5291 |
CREATE INDEX fki_plantconceptscope_locationevent_id ON plantconceptscope USING btree (locationevent_id); |
|
| 5292 |
|
|
| 5293 |
|
|
| 5294 |
-- |
|
| 5295 |
-- Name: fki_plantconceptscope_project_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 5296 |
-- |
|
| 5297 |
|
|
| 5298 |
CREATE INDEX fki_plantconceptscope_project_id ON plantconceptscope USING btree (project_id); |
|
| 5299 |
|
|
| 5300 |
|
|
| 5301 |
-- |
|
| 5290 | 5302 |
-- Name: fki_plantobservation_plant_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 5291 | 5303 |
-- |
| 5292 | 5304 |
|
| ... | ... | |
| 5336 | 5348 |
|
| 5337 | 5349 |
|
| 5338 | 5350 |
-- |
| 5339 |
-- Name: fki_taxondetermination_morphospecies_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 5340 |
-- |
|
| 5341 |
|
|
| 5342 |
CREATE INDEX fki_taxondetermination_morphospecies_id ON taxondetermination USING btree (morphospecies_id); |
|
| 5343 |
|
|
| 5344 |
|
|
| 5345 |
-- |
|
| 5346 | 5351 |
-- Name: graphic_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 5347 | 5352 |
-- |
| 5348 | 5353 |
|
| ... | ... | |
| 5626 | 5631 |
-- Name: plantconcept_keys; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 5627 | 5632 |
-- |
| 5628 | 5633 |
|
| 5629 |
CREATE UNIQUE INDEX plantconcept_keys ON plantconcept USING btree ((COALESCE(reference_id, 0)), plantname_id); |
|
| 5634 |
CREATE UNIQUE INDEX plantconcept_keys ON plantconcept USING btree ((COALESCE(scope_id, 0)), (COALESCE(reference_id, 0)), plantname_id);
|
|
| 5630 | 5635 |
|
| 5631 | 5636 |
|
| 5632 | 5637 |
-- |
| ... | ... | |
| 5644 | 5649 |
|
| 5645 | 5650 |
|
| 5646 | 5651 |
-- |
| 5652 |
-- Name: plantconceptscope_keys; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 5653 |
-- |
|
| 5654 |
|
|
| 5655 |
CREATE UNIQUE INDEX plantconceptscope_keys ON plantconceptscope USING btree ((COALESCE(locationevent_id, 0)), (COALESCE(project_id, 0))); |
|
| 5656 |
|
|
| 5657 |
|
|
| 5658 |
-- |
|
| 5647 | 5659 |
-- Name: plantcorrelation_plantconcept_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 5648 | 5660 |
-- |
| 5649 | 5661 |
|
| ... | ... | |
| 6564 | 6576 |
|
| 6565 | 6577 |
|
| 6566 | 6578 |
-- |
| 6567 |
-- Name: morphospecies_locationevent_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 6568 |
-- |
|
| 6569 |
|
|
| 6570 |
ALTER TABLE ONLY morphospecies |
|
| 6571 |
ADD CONSTRAINT morphospecies_locationevent_id FOREIGN KEY (locationevent_id) REFERENCES locationevent(locationevent_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 6572 |
|
|
| 6573 |
|
|
| 6574 |
-- |
|
| 6575 | 6579 |
-- Name: namedplace_reference_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 6576 | 6580 |
-- |
| 6577 | 6581 |
|
| ... | ... | |
| 6668 | 6672 |
|
| 6669 | 6673 |
|
| 6670 | 6674 |
-- |
| 6675 |
-- Name: plantconcept_scope_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 6676 |
-- |
|
| 6677 |
|
|
| 6678 |
ALTER TABLE ONLY plantconcept |
|
| 6679 |
ADD CONSTRAINT plantconcept_scope_id FOREIGN KEY (scope_id) REFERENCES plantconceptscope(plantconceptscope_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 6680 |
|
|
| 6681 |
|
|
| 6682 |
-- |
|
| 6683 |
-- Name: plantconceptscope_locationevent_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 6684 |
-- |
|
| 6685 |
|
|
| 6686 |
ALTER TABLE ONLY plantconceptscope |
|
| 6687 |
ADD CONSTRAINT plantconceptscope_locationevent_id FOREIGN KEY (locationevent_id) REFERENCES locationevent(locationevent_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 6688 |
|
|
| 6689 |
|
|
| 6690 |
-- |
|
| 6691 |
-- Name: plantconceptscope_project_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 6692 |
-- |
|
| 6693 |
|
|
| 6694 |
ALTER TABLE ONLY plantconceptscope |
|
| 6695 |
ADD CONSTRAINT plantconceptscope_project_id FOREIGN KEY (project_id) REFERENCES project(project_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 6696 |
|
|
| 6697 |
|
|
| 6698 |
-- |
|
| 6671 | 6699 |
-- Name: plantcorrelation_plantconcept_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 6672 | 6700 |
-- |
| 6673 | 6701 |
|
| ... | ... | |
| 6980 | 7008 |
|
| 6981 | 7009 |
|
| 6982 | 7010 |
-- |
| 6983 |
-- Name: taxondetermination_morphospecies_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 6984 |
-- |
|
| 6985 |
|
|
| 6986 |
ALTER TABLE ONLY taxondetermination |
|
| 6987 |
ADD CONSTRAINT taxondetermination_morphospecies_id FOREIGN KEY (morphospecies_id) REFERENCES morphospecies(morphospecies_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 6988 |
|
|
| 6989 |
|
|
| 6990 |
-- |
|
| 6991 | 7011 |
-- Name: taxondetermination_party_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 6992 | 7012 |
-- |
| 6993 | 7013 |
|
Also available in: Unified diff
vegbien.sql: Removed morphospecies, instead using plantconcept and new table plantconceptscope to represent scoped morphospecies. Reorganized plant* tables in ERD.