Revision 5953
Added by Aaron Marcuse-Kubitza over 11 years ago
| vegbien.my.sql | ||
|---|---|---|
| 229 | 229 |
|
| 230 | 230 |
|
| 231 | 231 |
-- |
| 232 |
-- Name: party_creator_id_self_ref(); Type: FUNCTION; Schema: public; Owner: -
|
|
| 232 |
-- Name: party_reference_id_self_ref(); Type: FUNCTION; Schema: public; Owner: -
|
|
| 233 | 233 |
-- |
| 234 | 234 |
|
| 235 | 235 |
|
| ... | ... | |
| 315 | 315 |
|
| 316 | 316 |
CREATE TABLE taxonlabel ( |
| 317 | 317 |
taxonlabel_id int(11) NOT NULL, |
| 318 |
creator_id int(11) NOT NULL,
|
|
| 318 |
reference_id int(11) NOT NULL,
|
|
| 319 | 319 |
sourceaccessioncode text, |
| 320 | 320 |
creationdate date, |
| 321 | 321 |
taxonstatus text, |
| ... | ... | |
| 338 | 338 |
|
| 339 | 339 |
|
| 340 | 340 |
-- |
| 341 |
-- Name: COLUMN taxonlabel.creator_id; Type: COMMENT; Schema: public; Owner: -
|
|
| 341 |
-- Name: COLUMN taxonlabel.reference_id; Type: COMMENT; Schema: public; Owner: -
|
|
| 342 | 342 |
-- |
| 343 | 343 |
|
| 344 | 344 |
|
| ... | ... | |
| 482 | 482 |
|
| 483 | 483 |
CREATE TABLE aggregateoccurrence ( |
| 484 | 484 |
aggregateoccurrence_id int(11) NOT NULL, |
| 485 |
creator_id int(11) NOT NULL,
|
|
| 485 |
reference_id int(11) NOT NULL,
|
|
| 486 | 486 |
sourceaccessioncode text, |
| 487 | 487 |
taxonoccurrence_id int(11), |
| 488 | 488 |
collectiondate date, |
| ... | ... | |
| 619 | 619 |
|
| 620 | 620 |
CREATE TABLE coordinates ( |
| 621 | 621 |
coordinates_id int(11) NOT NULL, |
| 622 |
creator_id int(11) NOT NULL,
|
|
| 622 |
reference_id int(11) NOT NULL,
|
|
| 623 | 623 |
latitude_deg double precision NOT NULL, |
| 624 | 624 |
longitude_deg double precision NOT NULL, |
| 625 | 625 |
verbatimlatitude text, |
| ... | ... | |
| 650 | 650 |
|
| 651 | 651 |
CREATE TABLE location ( |
| 652 | 652 |
location_id int(11) NOT NULL, |
| 653 |
creator_id int(11) NOT NULL,
|
|
| 653 |
reference_id int(11) NOT NULL,
|
|
| 654 | 654 |
sourceaccessioncode text, |
| 655 | 655 |
parent_id int(11), |
| 656 | 656 |
authorlocationcode text, |
| ... | ... | |
| 708 | 708 |
|
| 709 | 709 |
CREATE TABLE locationevent ( |
| 710 | 710 |
locationevent_id int(11) NOT NULL, |
| 711 |
creator_id int(11) NOT NULL,
|
|
| 711 |
reference_id int(11) NOT NULL,
|
|
| 712 | 712 |
sourceaccessioncode text, |
| 713 | 713 |
parent_id int(11), |
| 714 | 714 |
location_id int(11), |
| ... | ... | |
| 984 | 984 |
|
| 985 | 985 |
CREATE TABLE party ( |
| 986 | 986 |
party_id int(11) NOT NULL, |
| 987 |
creator_id int(11) NOT NULL,
|
|
| 987 |
reference_id int(11) NOT NULL,
|
|
| 988 | 988 |
fullname text, |
| 989 | 989 |
salutation text, |
| 990 | 990 |
givenname text, |
| ... | ... | |
| 1007 | 1007 |
|
| 1008 | 1008 |
CREATE TABLE place ( |
| 1009 | 1009 |
place_id int(11) NOT NULL, |
| 1010 |
creator_id int(11) NOT NULL,
|
|
| 1010 |
reference_id int(11) NOT NULL,
|
|
| 1011 | 1011 |
placecode text, |
| 1012 | 1012 |
matched_place_id int(11), |
| 1013 | 1013 |
coordinates_id int(11), |
| ... | ... | |
| 1043 | 1043 |
|
| 1044 | 1044 |
CREATE TABLE plantobservation ( |
| 1045 | 1045 |
plantobservation_id int(11) NOT NULL, |
| 1046 |
creator_id int(11) NOT NULL,
|
|
| 1046 |
reference_id int(11) NOT NULL,
|
|
| 1047 | 1047 |
sourceaccessioncode text, |
| 1048 | 1048 |
aggregateoccurrence_id int(11), |
| 1049 | 1049 |
overallheight_m double precision, |
| ... | ... | |
| 1075 | 1075 |
|
| 1076 | 1076 |
CREATE TABLE specimenreplicate ( |
| 1077 | 1077 |
specimenreplicate_id int(11) NOT NULL, |
| 1078 |
creator_id int(11) NOT NULL,
|
|
| 1078 |
reference_id int(11) NOT NULL,
|
|
| 1079 | 1079 |
sourceaccessioncode text, |
| 1080 | 1080 |
plantobservation_id int(11), |
| 1081 | 1081 |
institution_id int(11), |
| ... | ... | |
| 1114 | 1114 |
|
| 1115 | 1115 |
CREATE TABLE stemobservation ( |
| 1116 | 1116 |
stemobservation_id int(11) NOT NULL, |
| 1117 |
creator_id int(11) NOT NULL,
|
|
| 1117 |
reference_id int(11) NOT NULL,
|
|
| 1118 | 1118 |
sourceaccessioncode text, |
| 1119 | 1119 |
plantobservation_id int(11) NOT NULL, |
| 1120 | 1120 |
authorstemcode text, |
| ... | ... | |
| 1187 | 1187 |
|
| 1188 | 1188 |
CREATE TABLE taxonoccurrence ( |
| 1189 | 1189 |
taxonoccurrence_id int(11) NOT NULL, |
| 1190 |
creator_id int(11) NOT NULL,
|
|
| 1190 |
reference_id int(11) NOT NULL,
|
|
| 1191 | 1191 |
sourceaccessioncode text, |
| 1192 | 1192 |
locationevent_id int(11), |
| 1193 | 1193 |
authortaxoncode text, |
| ... | ... | |
| 2144 | 2144 |
|
| 2145 | 2145 |
CREATE TABLE project ( |
| 2146 | 2146 |
project_id int(11) NOT NULL, |
| 2147 |
creator_id int(11) NOT NULL,
|
|
| 2147 |
reference_id int(11) NOT NULL,
|
|
| 2148 | 2148 |
sourceaccessioncode text, |
| 2149 | 2149 |
projectname text, |
| 2150 | 2150 |
projectdescription text, |
| ... | ... | |
| 3642 | 3642 |
-- |
| 3643 | 3643 |
|
| 3644 | 3644 |
ALTER TABLE project |
| 3645 |
ADD CONSTRAINT project_unique UNIQUE (creator_id, projectname);
|
|
| 3645 |
ADD CONSTRAINT project_unique UNIQUE (reference_id, projectname);
|
|
| 3646 | 3646 |
|
| 3647 | 3647 |
|
| 3648 | 3648 |
-- |
| ... | ... | |
| 3982 | 3982 |
-- Name: location_creator; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 3983 | 3983 |
-- |
| 3984 | 3984 |
|
| 3985 |
CREATE INDEX location_creator ON location (creator_id);
|
|
| 3985 |
CREATE INDEX location_creator ON location (reference_id);
|
|
| 3986 | 3986 |
|
| 3987 | 3987 |
|
| 3988 | 3988 |
-- |
| ... | ... | |
| 4357 | 4357 |
|
| 4358 | 4358 |
|
| 4359 | 4359 |
-- |
| 4360 |
-- Name: party_creator_id_self_ref; Type: TRIGGER; Schema: public; Owner: -
|
|
| 4360 |
-- Name: party_reference_id_self_ref; Type: TRIGGER; Schema: public; Owner: -
|
|
| 4361 | 4361 |
-- |
| 4362 | 4362 |
|
| 4363 | 4363 |
|
| ... | ... | |
| 4457 | 4457 |
|
| 4458 | 4458 |
|
| 4459 | 4459 |
-- |
| 4460 |
-- Name: aggregateoccurrence_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4460 |
-- Name: aggregateoccurrence_method_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4461 | 4461 |
-- |
| 4462 | 4462 |
|
| 4463 | 4463 |
|
| 4464 | 4464 |
|
| 4465 | 4465 |
|
| 4466 | 4466 |
-- |
| 4467 |
-- Name: aggregateoccurrence_method_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4467 |
-- Name: aggregateoccurrence_reference_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4468 | 4468 |
-- |
| 4469 | 4469 |
|
| 4470 | 4470 |
|
| ... | ... | |
| 4654 | 4654 |
|
| 4655 | 4655 |
|
| 4656 | 4656 |
-- |
| 4657 |
-- Name: coordinates_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4657 |
-- Name: coordinates_reference_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4658 | 4658 |
-- |
| 4659 | 4659 |
|
| 4660 | 4660 |
|
| ... | ... | |
| 4700 | 4700 |
|
| 4701 | 4701 |
|
| 4702 | 4702 |
-- |
| 4703 |
-- Name: location_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 4704 |
-- |
|
| 4705 |
|
|
| 4706 |
|
|
| 4707 |
|
|
| 4708 |
|
|
| 4709 |
-- |
|
| 4710 | 4703 |
-- Name: location_parent_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 4711 | 4704 |
-- |
| 4712 | 4705 |
|
| ... | ... | |
| 4715 | 4708 |
|
| 4716 | 4709 |
|
| 4717 | 4710 |
-- |
| 4718 |
-- Name: locationevent_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4711 |
-- Name: location_reference_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4719 | 4712 |
-- |
| 4720 | 4713 |
|
| 4721 | 4714 |
|
| ... | ... | |
| 4761 | 4754 |
|
| 4762 | 4755 |
|
| 4763 | 4756 |
-- |
| 4757 |
-- Name: locationevent_reference_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 4758 |
-- |
|
| 4759 |
|
|
| 4760 |
|
|
| 4761 |
|
|
| 4762 |
|
|
| 4763 |
-- |
|
| 4764 | 4764 |
-- Name: locationevent_soiltaxon_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 4765 | 4765 |
-- |
| 4766 | 4766 |
|
| ... | ... | |
| 4891 | 4891 |
|
| 4892 | 4892 |
|
| 4893 | 4893 |
-- |
| 4894 |
-- Name: party_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4894 |
-- Name: party_currentname_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4895 | 4895 |
-- |
| 4896 | 4896 |
|
| 4897 |
ALTER TABLE party |
|
| 4898 |
ADD CONSTRAINT party_currentname_id_fkey FOREIGN KEY (currentname_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 4897 | 4899 |
|
| 4898 | 4900 |
|
| 4899 |
|
|
| 4900 | 4901 |
-- |
| 4901 |
-- Name: party_currentname_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4902 |
-- Name: party_reference_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4902 | 4903 |
-- |
| 4903 | 4904 |
|
| 4904 |
ALTER TABLE party |
|
| 4905 |
ADD CONSTRAINT party_currentname_id_fkey FOREIGN KEY (currentname_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 4906 | 4905 |
|
| 4907 | 4906 |
|
| 4907 |
|
|
| 4908 | 4908 |
-- |
| 4909 | 4909 |
-- Name: partymember_childparty_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 4910 | 4910 |
-- |
| ... | ... | |
| 4930 | 4930 |
|
| 4931 | 4931 |
|
| 4932 | 4932 |
-- |
| 4933 |
-- Name: place_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 4934 |
-- |
|
| 4935 |
|
|
| 4936 |
|
|
| 4937 |
|
|
| 4938 |
|
|
| 4939 |
-- |
|
| 4940 | 4933 |
-- Name: place_matched_place_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 4941 | 4934 |
-- |
| 4942 | 4935 |
|
| ... | ... | |
| 4953 | 4946 |
|
| 4954 | 4947 |
|
| 4955 | 4948 |
-- |
| 4949 |
-- Name: place_reference_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 4950 |
-- |
|
| 4951 |
|
|
| 4952 |
|
|
| 4953 |
|
|
| 4954 |
|
|
| 4955 |
-- |
|
| 4956 | 4956 |
-- Name: placecorrelation_childplace_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 4957 | 4957 |
-- |
| 4958 | 4958 |
|
| ... | ... | |
| 5001 | 5001 |
|
| 5002 | 5002 |
|
| 5003 | 5003 |
-- |
| 5004 |
-- Name: plantobservation_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5004 |
-- Name: plantobservation_plant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5005 | 5005 |
-- |
| 5006 | 5006 |
|
| 5007 |
ALTER TABLE plantobservation |
|
| 5008 |
ADD CONSTRAINT plantobservation_plant_id_fkey FOREIGN KEY (plant_id) REFERENCES plant(plant_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5007 | 5009 |
|
| 5008 | 5010 |
|
| 5009 |
|
|
| 5010 | 5011 |
-- |
| 5011 |
-- Name: plantobservation_plant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5012 |
-- Name: plantobservation_reference_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5012 | 5013 |
-- |
| 5013 | 5014 |
|
| 5014 |
ALTER TABLE plantobservation |
|
| 5015 |
ADD CONSTRAINT plantobservation_plant_id_fkey FOREIGN KEY (plant_id) REFERENCES plant(plant_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5016 | 5015 |
|
| 5017 | 5016 |
|
| 5017 |
|
|
| 5018 | 5018 |
-- |
| 5019 |
-- Name: project_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5019 |
-- Name: project_reference_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5020 | 5020 |
-- |
| 5021 | 5021 |
|
| 5022 | 5022 |
|
| ... | ... | |
| 5102 | 5102 |
|
| 5103 | 5103 |
|
| 5104 | 5104 |
-- |
| 5105 |
-- Name: specimenreplicate_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5105 |
-- Name: specimenreplicate_institution_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5106 | 5106 |
-- |
| 5107 | 5107 |
|
| 5108 | 5108 |
|
| 5109 | 5109 |
|
| 5110 | 5110 |
|
| 5111 | 5111 |
-- |
| 5112 |
-- Name: specimenreplicate_institution_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5112 |
-- Name: specimenreplicate_plantobservation_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5113 | 5113 |
-- |
| 5114 | 5114 |
|
| 5115 |
ALTER TABLE specimenreplicate |
|
| 5116 |
ADD CONSTRAINT specimenreplicate_plantobservation_id_fkey FOREIGN KEY (plantobservation_id) REFERENCES plantobservation(plantobservation_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5115 | 5117 |
|
| 5116 | 5118 |
|
| 5117 |
|
|
| 5118 | 5119 |
-- |
| 5119 |
-- Name: specimenreplicate_plantobservation_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5120 |
-- Name: specimenreplicate_reference_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5120 | 5121 |
-- |
| 5121 | 5122 |
|
| 5122 |
ALTER TABLE specimenreplicate |
|
| 5123 |
ADD CONSTRAINT specimenreplicate_plantobservation_id_fkey FOREIGN KEY (plantobservation_id) REFERENCES plantobservation(plantobservation_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5124 | 5123 |
|
| 5125 | 5124 |
|
| 5125 |
|
|
| 5126 | 5126 |
-- |
| 5127 | 5127 |
-- Name: specimenreplicate_specimen_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5128 | 5128 |
-- |
| ... | ... | |
| 5132 | 5132 |
|
| 5133 | 5133 |
|
| 5134 | 5134 |
-- |
| 5135 |
-- Name: stemobservation_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5135 |
-- Name: stemobservation_plantobservation_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5136 | 5136 |
-- |
| 5137 | 5137 |
|
| 5138 |
ALTER TABLE stemobservation |
|
| 5139 |
ADD CONSTRAINT stemobservation_plantobservation_id_fkey FOREIGN KEY (plantobservation_id) REFERENCES plantobservation(plantobservation_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5138 | 5140 |
|
| 5139 | 5141 |
|
| 5140 |
|
|
| 5141 | 5142 |
-- |
| 5142 |
-- Name: stemobservation_plantobservation_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5143 |
-- Name: stemobservation_reference_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5143 | 5144 |
-- |
| 5144 | 5145 |
|
| 5145 |
ALTER TABLE stemobservation |
|
| 5146 |
ADD CONSTRAINT stemobservation_plantobservation_id_fkey FOREIGN KEY (plantobservation_id) REFERENCES plantobservation(plantobservation_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5147 | 5146 |
|
| 5148 | 5147 |
|
| 5148 |
|
|
| 5149 | 5149 |
-- |
| 5150 | 5150 |
-- Name: stratum_locationevent_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5151 | 5151 |
-- |
| ... | ... | |
| 5245 | 5245 |
|
| 5246 | 5246 |
|
| 5247 | 5247 |
-- |
| 5248 |
-- Name: taxonlabel_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5249 |
-- |
|
| 5250 |
|
|
| 5251 |
|
|
| 5252 |
|
|
| 5253 |
|
|
| 5254 |
-- |
|
| 5255 | 5248 |
-- Name: taxonlabel_matched_label_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5256 | 5249 |
-- |
| 5257 | 5250 |
|
| ... | ... | |
| 5268 | 5261 |
|
| 5269 | 5262 |
|
| 5270 | 5263 |
-- |
| 5264 |
-- Name: taxonlabel_reference_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5265 |
-- |
|
| 5266 |
|
|
| 5267 |
|
|
| 5268 |
|
|
| 5269 |
|
|
| 5270 |
-- |
|
| 5271 | 5271 |
-- Name: taxonlabel_relationship_ancestor_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5272 | 5272 |
-- |
| 5273 | 5273 |
|
| ... | ... | |
| 5300 | 5300 |
|
| 5301 | 5301 |
|
| 5302 | 5302 |
-- |
| 5303 |
-- Name: taxonoccurrence_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5303 |
-- Name: taxonoccurrence_locationevent_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5304 | 5304 |
-- |
| 5305 | 5305 |
|
| 5306 |
ALTER TABLE taxonoccurrence |
|
| 5307 |
ADD CONSTRAINT taxonoccurrence_locationevent_id_fkey FOREIGN KEY (locationevent_id) REFERENCES locationevent(locationevent_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5306 | 5308 |
|
| 5307 | 5309 |
|
| 5308 |
|
|
| 5309 | 5310 |
-- |
| 5310 |
-- Name: taxonoccurrence_locationevent_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5311 |
-- Name: taxonoccurrence_reference_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5311 | 5312 |
-- |
| 5312 | 5313 |
|
| 5313 |
ALTER TABLE taxonoccurrence |
|
| 5314 |
ADD CONSTRAINT taxonoccurrence_locationevent_id_fkey FOREIGN KEY (locationevent_id) REFERENCES locationevent(locationevent_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5315 | 5314 |
|
| 5316 | 5315 |
|
| 5316 |
|
|
| 5317 | 5317 |
-- |
| 5318 | 5318 |
-- Name: taxonstatus_party_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5319 | 5319 |
-- |
Also available in: Unified diff
mappings/VegCore-VegBIEN.csv: Renamed creator_ids to reference_id since they are now fkeys to reference