Revision 903
Added by Aaron Marcuse-Kubitza about 13 years ago
| vegbien.my.sql | ||
|---|---|---|
| 1209 | 1209 |
|
| 1210 | 1210 |
|
| 1211 | 1211 |
|
| 1212 |
|
|
| 1213 |
|
|
| 1214 | 1212 |
-- |
| 1215 |
-- Name: morphospecies; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 1216 |
-- |
|
| 1217 |
|
|
| 1218 |
CREATE TABLE morphospecies ( |
|
| 1219 |
morphospecies_id int(11) NOT NULL, |
|
| 1220 |
locationevent_id int(11) NOT NULL, |
|
| 1221 |
name text NOT NULL |
|
| 1222 |
); |
|
| 1223 |
|
|
| 1224 |
|
|
| 1225 |
-- |
|
| 1226 |
-- Name: morphospecies_morphospecies_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
|
| 1227 |
-- |
|
| 1228 |
|
|
| 1229 |
|
|
| 1230 |
|
|
| 1231 |
|
|
| 1232 |
-- |
|
| 1233 |
-- Name: morphospecies_morphospecies_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - |
|
| 1234 |
-- |
|
| 1235 |
|
|
| 1236 |
|
|
| 1237 |
|
|
| 1238 |
|
|
| 1239 |
|
|
| 1240 |
|
|
| 1241 |
-- |
|
| 1242 | 1213 |
-- Name: namedplace; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 1243 | 1214 |
-- |
| 1244 | 1215 |
|
| ... | ... | |
| 1460 | 1431 |
plantdescription text, |
| 1461 | 1432 |
d_obscount int(11), |
| 1462 | 1433 |
d_currentaccepted int(1), |
| 1463 |
accessioncode text |
|
| 1434 |
accessioncode text, |
|
| 1435 |
scope_id int(11) |
|
| 1464 | 1436 |
); |
| 1465 | 1437 |
|
| 1466 | 1438 |
|
| ... | ... | |
| 1478 | 1450 |
|
| 1479 | 1451 |
|
| 1480 | 1452 |
|
| 1453 |
|
|
| 1454 |
|
|
| 1481 | 1455 |
-- |
| 1456 |
-- Name: plantconceptscope; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 1457 |
-- |
|
| 1458 |
|
|
| 1459 |
CREATE TABLE plantconceptscope ( |
|
| 1460 |
plantconceptscope_id int(11) NOT NULL, |
|
| 1461 |
locationevent_id int(11), |
|
| 1462 |
project_id int(11) |
|
| 1463 |
); |
|
| 1464 |
|
|
| 1465 |
|
|
| 1466 |
-- |
|
| 1467 |
-- Name: plantconceptscope_plantconceptscope_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
|
| 1468 |
-- |
|
| 1469 |
|
|
| 1470 |
|
|
| 1471 |
|
|
| 1472 |
|
|
| 1473 |
-- |
|
| 1474 |
-- Name: plantconceptscope_plantconceptscope_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - |
|
| 1475 |
-- |
|
| 1476 |
|
|
| 1477 |
|
|
| 1478 |
|
|
| 1479 |
|
|
| 1480 |
|
|
| 1481 |
|
|
| 1482 |
-- |
|
| 1482 | 1483 |
-- Name: plantcorrelation; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 1483 | 1484 |
-- |
| 1484 | 1485 |
|
| ... | ... | |
| 1602 | 1603 |
CREATE TABLE plantstatus ( |
| 1603 | 1604 |
plantstatus_id int(11) NOT NULL, |
| 1604 | 1605 |
plantconcept_id int(11) NOT NULL, |
| 1606 |
plantparent_id int(11), |
|
| 1607 |
plantlevel text, |
|
| 1605 | 1608 |
reference_id int(11), |
| 1606 | 1609 |
plantconceptstatus text DEFAULT 'undetermined', |
| 1607 | 1610 |
plantpartycomments text, |
| 1608 | 1611 |
plantparentname text, |
| 1609 |
plantparentconcept_id int(11), |
|
| 1610 |
plantparent_id int(11), |
|
| 1611 |
plantlevel text, |
|
| 1612 | 1612 |
party_id int(11), |
| 1613 | 1613 |
startdate timestamp NULL, |
| 1614 | 1614 |
stopdate timestamp NULL, |
| ... | ... | |
| 2375 | 2375 |
revisions int(1), |
| 2376 | 2376 |
determinationdate timestamp NULL, |
| 2377 | 2377 |
emb_taxondetermination int(11), |
| 2378 |
accessioncode text, |
|
| 2379 |
morphospecies_id int(11) |
|
| 2378 |
accessioncode text |
|
| 2380 | 2379 |
); |
| 2381 | 2380 |
|
| 2382 | 2381 |
|
| ... | ... | |
| 3165 | 3164 |
|
| 3166 | 3165 |
|
| 3167 | 3166 |
-- |
| 3168 |
-- Name: morphospecies_id; Type: DEFAULT; Schema: public; Owner: - |
|
| 3169 |
-- |
|
| 3170 |
|
|
| 3171 |
|
|
| 3172 |
|
|
| 3173 |
|
|
| 3174 |
-- |
|
| 3175 | 3167 |
-- Name: namedplace_id; Type: DEFAULT; Schema: public; Owner: - |
| 3176 | 3168 |
-- |
| 3177 | 3169 |
|
| ... | ... | |
| 3228 | 3220 |
|
| 3229 | 3221 |
|
| 3230 | 3222 |
-- |
| 3223 |
-- Name: plantconceptscope_id; Type: DEFAULT; Schema: public; Owner: - |
|
| 3224 |
-- |
|
| 3225 |
|
|
| 3226 |
|
|
| 3227 |
|
|
| 3228 |
|
|
| 3229 |
-- |
|
| 3231 | 3230 |
-- Name: plantcorrelation_id; Type: DEFAULT; Schema: public; Owner: - |
| 3232 | 3231 |
-- |
| 3233 | 3232 |
|
| ... | ... | |
| 3903 | 3902 |
|
| 3904 | 3903 |
|
| 3905 | 3904 |
-- |
| 3906 |
-- Name: morphospecies_keys; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3907 |
-- |
|
| 3908 |
|
|
| 3909 |
ALTER TABLE morphospecies |
|
| 3910 |
ADD CONSTRAINT morphospecies_keys UNIQUE (locationevent_id, name); |
|
| 3911 |
|
|
| 3912 |
|
|
| 3913 |
-- |
|
| 3914 |
-- Name: morphospecies_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3915 |
-- |
|
| 3916 |
|
|
| 3917 |
ALTER TABLE morphospecies |
|
| 3918 |
ADD CONSTRAINT morphospecies_pkey PRIMARY KEY (morphospecies_id); |
|
| 3919 |
|
|
| 3920 |
|
|
| 3921 |
-- |
|
| 3922 | 3905 |
-- Name: namedplace_keys; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 3923 | 3906 |
-- |
| 3924 | 3907 |
|
| ... | ... | |
| 3991 | 3974 |
|
| 3992 | 3975 |
|
| 3993 | 3976 |
-- |
| 3977 |
-- Name: plantconceptscope_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3978 |
-- |
|
| 3979 |
|
|
| 3980 |
ALTER TABLE plantconceptscope |
|
| 3981 |
ADD CONSTRAINT plantconceptscope_pkey PRIMARY KEY (plantconceptscope_id); |
|
| 3982 |
|
|
| 3983 |
|
|
| 3984 |
-- |
|
| 3994 | 3985 |
-- Name: plantcorrelation_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 3995 | 3986 |
-- |
| 3996 | 3987 |
|
| ... | ... | |
| 4831 | 4822 |
|
| 4832 | 4823 |
|
| 4833 | 4824 |
-- |
| 4825 |
-- Name: fki_plantconcept_scope_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4826 |
-- |
|
| 4827 |
|
|
| 4828 |
CREATE INDEX fki_plantconcept_scope_id ON plantconcept (scope_id); |
|
| 4829 |
|
|
| 4830 |
|
|
| 4831 |
-- |
|
| 4832 |
-- Name: fki_plantconceptscope_locationevent_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4833 |
-- |
|
| 4834 |
|
|
| 4835 |
CREATE INDEX fki_plantconceptscope_locationevent_id ON plantconceptscope (locationevent_id); |
|
| 4836 |
|
|
| 4837 |
|
|
| 4838 |
-- |
|
| 4839 |
-- Name: fki_plantconceptscope_project_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4840 |
-- |
|
| 4841 |
|
|
| 4842 |
CREATE INDEX fki_plantconceptscope_project_id ON plantconceptscope (project_id); |
|
| 4843 |
|
|
| 4844 |
|
|
| 4845 |
-- |
|
| 4834 | 4846 |
-- Name: fki_plantobservation_plant_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4835 | 4847 |
-- |
| 4836 | 4848 |
|
| ... | ... | |
| 4880 | 4892 |
|
| 4881 | 4893 |
|
| 4882 | 4894 |
-- |
| 4883 |
-- Name: fki_taxondetermination_morphospecies_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4884 |
-- |
|
| 4885 |
|
|
| 4886 |
CREATE INDEX fki_taxondetermination_morphospecies_id ON taxondetermination (morphospecies_id); |
|
| 4887 |
|
|
| 4888 |
|
|
| 4889 |
-- |
|
| 4890 | 4895 |
-- Name: graphic_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4891 | 4896 |
-- |
| 4892 | 4897 |
|
| ... | ... | |
| 5188 | 5193 |
|
| 5189 | 5194 |
|
| 5190 | 5195 |
-- |
| 5196 |
-- Name: plantconceptscope_keys; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 5197 |
-- |
|
| 5198 |
|
|
| 5199 |
|
|
| 5200 |
|
|
| 5201 |
|
|
| 5202 |
-- |
|
| 5191 | 5203 |
-- Name: plantcorrelation_plantconcept_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 5192 | 5204 |
-- |
| 5193 | 5205 |
|
| ... | ... | |
| 6096 | 6108 |
|
| 6097 | 6109 |
|
| 6098 | 6110 |
-- |
| 6099 |
-- Name: morphospecies_locationevent_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 6100 |
-- |
|
| 6101 |
|
|
| 6102 |
ALTER TABLE morphospecies |
|
| 6103 |
ADD CONSTRAINT morphospecies_locationevent_id FOREIGN KEY (locationevent_id) REFERENCES locationevent(locationevent_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 6104 |
|
|
| 6105 |
|
|
| 6106 |
-- |
|
| 6107 | 6111 |
-- Name: namedplace_reference_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 6108 | 6112 |
-- |
| 6109 | 6113 |
|
| ... | ... | |
| 6197 | 6201 |
|
| 6198 | 6202 |
|
| 6199 | 6203 |
-- |
| 6204 |
-- Name: plantconcept_scope_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 6205 |
-- |
|
| 6206 |
|
|
| 6207 |
ALTER TABLE plantconcept |
|
| 6208 |
ADD CONSTRAINT plantconcept_scope_id FOREIGN KEY (scope_id) REFERENCES plantconceptscope(plantconceptscope_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 6209 |
|
|
| 6210 |
|
|
| 6211 |
-- |
|
| 6212 |
-- Name: plantconceptscope_locationevent_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 6213 |
-- |
|
| 6214 |
|
|
| 6215 |
ALTER TABLE plantconceptscope |
|
| 6216 |
ADD CONSTRAINT plantconceptscope_locationevent_id FOREIGN KEY (locationevent_id) REFERENCES locationevent(locationevent_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 6217 |
|
|
| 6218 |
|
|
| 6219 |
-- |
|
| 6220 |
-- Name: plantconceptscope_project_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 6221 |
-- |
|
| 6222 |
|
|
| 6223 |
ALTER TABLE plantconceptscope |
|
| 6224 |
ADD CONSTRAINT plantconceptscope_project_id FOREIGN KEY (project_id) REFERENCES project(project_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 6225 |
|
|
| 6226 |
|
|
| 6227 |
-- |
|
| 6200 | 6228 |
-- Name: plantcorrelation_plantconcept_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 6201 | 6229 |
-- |
| 6202 | 6230 |
|
| ... | ... | |
| 6499 | 6527 |
|
| 6500 | 6528 |
|
| 6501 | 6529 |
-- |
| 6502 |
-- Name: taxondetermination_morphospecies_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 6503 |
-- |
|
| 6504 |
|
|
| 6505 |
ALTER TABLE taxondetermination |
|
| 6506 |
ADD CONSTRAINT taxondetermination_morphospecies_id FOREIGN KEY (morphospecies_id) REFERENCES morphospecies(morphospecies_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 6507 |
|
|
| 6508 |
|
|
| 6509 |
-- |
|
| 6510 | 6530 |
-- Name: taxondetermination_party_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 6511 | 6531 |
-- |
| 6512 | 6532 |
|
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.