Revision 3615
Added by Aaron Marcuse-Kubitza over 12 years ago
| schemas/vegbien.my.sql | ||
|---|---|---|
| 160 | 160 |
|
| 161 | 161 |
CREATE TABLE aggregateoccurrence ( |
| 162 | 162 |
aggregateoccurrence_id int(11) NOT NULL, |
| 163 |
datasource_id int(11), |
|
| 164 |
sourceaccessioncode text, |
|
| 163 | 165 |
taxonoccurrence_id int(11) NOT NULL, |
| 164 | 166 |
cover double precision, |
| 165 | 167 |
basalarea double precision, |
| 166 | 168 |
biomass double precision, |
| 167 | 169 |
inferencearea double precision, |
| 168 | 170 |
count int(11), |
| 169 |
sourceaccessioncode text, |
|
| 170 | 171 |
plantobservation_id int(11), |
| 171 | 172 |
stratum_id int(11), |
| 172 | 173 |
coverindex_id int(11), |
| ... | ... | |
| 637 | 638 |
|
| 638 | 639 |
CREATE TABLE location ( |
| 639 | 640 |
location_id int(11) NOT NULL, |
| 640 |
parent_id int(11), |
|
| 641 | 641 |
datasource_id int(11) NOT NULL, |
| 642 | 642 |
sourceaccessioncode text NOT NULL, |
| 643 |
parent_id int(11), |
|
| 643 | 644 |
centerlatitude double precision, |
| 644 | 645 |
centerlongitude double precision, |
| 645 | 646 |
locationaccuracy double precision, |
| ... | ... | |
| 771 | 772 |
|
| 772 | 773 |
CREATE TABLE locationevent ( |
| 773 | 774 |
locationevent_id int(11) NOT NULL, |
| 775 |
datasource_id int(11), |
|
| 776 |
sourceaccessioncode text, |
|
| 774 | 777 |
parent_id int(11), |
| 775 | 778 |
location_id int(11) NOT NULL, |
| 776 | 779 |
project_id int(11), |
| 777 | 780 |
authorlocationcode text, |
| 778 |
sourceaccessioncode text, |
|
| 779 | 781 |
previous_id int(11), |
| 780 | 782 |
dateaccuracy text, |
| 781 | 783 |
method_id int(11), |
| ... | ... | |
| 1557 | 1559 |
|
| 1558 | 1560 |
CREATE TABLE plantobservation ( |
| 1559 | 1561 |
plantobservation_id int(11) NOT NULL, |
| 1562 |
datasource_id int(11), |
|
| 1563 |
sourceaccessioncode text, |
|
| 1560 | 1564 |
overallheight double precision, |
| 1561 | 1565 |
overallheightaccuracy double precision, |
| 1562 | 1566 |
authorplantcode text, |
| 1563 | 1567 |
stemcount int(11), |
| 1564 |
sourceaccessioncode text, |
|
| 1565 | 1568 |
plant_id int(11), |
| 1566 | 1569 |
accessioncode text |
| 1567 | 1570 |
); |
| ... | ... | |
| 1658 | 1661 |
|
| 1659 | 1662 |
CREATE TABLE project ( |
| 1660 | 1663 |
project_id int(11) NOT NULL, |
| 1664 |
datasource_id int(11), |
|
| 1665 |
sourceaccessioncode text, |
|
| 1661 | 1666 |
projectname text NOT NULL, |
| 1662 | 1667 |
projectdescription text, |
| 1663 | 1668 |
startdate timestamp NULL, |
| 1664 | 1669 |
stopdate timestamp NULL, |
| 1665 | 1670 |
d_obscount int(11), |
| 1666 | 1671 |
d_lastlocationaddeddate timestamp NULL, |
| 1667 |
datasource_id int(11), |
|
| 1668 | 1672 |
accessioncode text |
| 1669 | 1673 |
); |
| 1670 | 1674 |
|
| ... | ... | |
| 2114 | 2118 |
|
| 2115 | 2119 |
CREATE TABLE taxonoccurrence ( |
| 2116 | 2120 |
taxonoccurrence_id int(11) NOT NULL, |
| 2121 |
datasource_id int(11), |
|
| 2122 |
sourceaccessioncode text, |
|
| 2117 | 2123 |
locationevent_id int(11), |
| 2118 |
sourceaccessioncode text, |
|
| 2119 | 2124 |
verbatimcollectorname text, |
| 2120 | 2125 |
growthform text, |
| 2121 | 2126 |
iscultivated int(1), |
| ... | ... | |
| 2225 | 2230 |
|
| 2226 | 2231 |
CREATE TABLE stemobservation ( |
| 2227 | 2232 |
stemobservation_id int(11) NOT NULL, |
| 2233 |
datasource_id int(11), |
|
| 2234 |
sourceaccessioncode text, |
|
| 2228 | 2235 |
plantobservation_id int(11) NOT NULL, |
| 2229 |
sourceaccessioncode text, |
|
| 2230 | 2236 |
authorstemcode text, |
| 2231 | 2237 |
xposition double precision, |
| 2232 | 2238 |
yposition double precision, |
| ... | ... | |
| 3591 | 3597 |
|
| 3592 | 3598 |
|
| 3593 | 3599 |
-- |
| 3600 |
-- Name: aggregateoccurrence_unique_datasource; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 3601 |
-- |
|
| 3602 |
|
|
| 3603 |
|
|
| 3604 |
|
|
| 3605 |
|
|
| 3606 |
-- |
|
| 3594 | 3607 |
-- Name: commclass_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 3595 | 3608 |
-- |
| 3596 | 3609 |
|
| ... | ... | |
| 3640 | 3653 |
|
| 3641 | 3654 |
|
| 3642 | 3655 |
-- |
| 3656 |
-- Name: location_unique_datasource; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 3657 |
-- |
|
| 3658 |
|
|
| 3659 |
CREATE UNIQUE INDEX location_unique_datasource ON location (datasource_id, sourceaccessioncode); |
|
| 3660 |
|
|
| 3661 |
|
|
| 3662 |
-- |
|
| 3643 | 3663 |
-- Name: locationevent_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 3644 | 3664 |
-- |
| 3645 | 3665 |
|
| ... | ... | |
| 3654 | 3674 |
|
| 3655 | 3675 |
|
| 3656 | 3676 |
-- |
| 3677 |
-- Name: locationevent_unique_datasource; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 3678 |
-- |
|
| 3679 |
|
|
| 3680 |
|
|
| 3681 |
|
|
| 3682 |
|
|
| 3683 |
-- |
|
| 3657 | 3684 |
-- Name: locationevent_unique_parent_authorcode; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 3658 | 3685 |
-- |
| 3659 | 3686 |
|
| ... | ... | |
| 3752 | 3779 |
|
| 3753 | 3780 |
|
| 3754 | 3781 |
-- |
| 3782 |
-- Name: plantobservation_unique_datasource; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 3783 |
-- |
|
| 3784 |
|
|
| 3785 |
|
|
| 3786 |
|
|
| 3787 |
|
|
| 3788 |
-- |
|
| 3755 | 3789 |
-- Name: plantstatus_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 3756 | 3790 |
-- |
| 3757 | 3791 |
|
| ... | ... | |
| 3766 | 3800 |
|
| 3767 | 3801 |
|
| 3768 | 3802 |
-- |
| 3803 |
-- Name: project_unique_datasource; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 3804 |
-- |
|
| 3805 |
|
|
| 3806 |
|
|
| 3807 |
|
|
| 3808 |
|
|
| 3809 |
-- |
|
| 3769 | 3810 |
-- Name: reference_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 3770 | 3811 |
-- |
| 3771 | 3812 |
|
| ... | ... | |
| 3815 | 3856 |
|
| 3816 | 3857 |
|
| 3817 | 3858 |
-- |
| 3859 |
-- Name: stemobservation_unique_datasource; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 3860 |
-- |
|
| 3861 |
|
|
| 3862 |
|
|
| 3863 |
|
|
| 3864 |
|
|
| 3865 |
-- |
|
| 3818 | 3866 |
-- Name: taxondetermination_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 3819 | 3867 |
-- |
| 3820 | 3868 |
|
| ... | ... | |
| 3836 | 3884 |
|
| 3837 | 3885 |
|
| 3838 | 3886 |
-- |
| 3887 |
-- Name: taxonoccurrence_unique_datasource; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 3888 |
-- |
|
| 3889 |
|
|
| 3890 |
|
|
| 3891 |
|
|
| 3892 |
|
|
| 3893 |
-- |
|
| 3839 | 3894 |
-- Name: userdefined_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 3840 | 3895 |
-- |
| 3841 | 3896 |
|
| schemas/vegbien.sql | ||
|---|---|---|
| 380 | 380 |
|
| 381 | 381 |
CREATE TABLE aggregateoccurrence ( |
| 382 | 382 |
aggregateoccurrence_id integer NOT NULL, |
| 383 |
datasource_id integer, |
|
| 384 |
sourceaccessioncode text, |
|
| 383 | 385 |
taxonoccurrence_id integer NOT NULL, |
| 384 | 386 |
cover double precision, |
| 385 | 387 |
basalarea double precision, |
| 386 | 388 |
biomass double precision, |
| 387 | 389 |
inferencearea double precision, |
| 388 | 390 |
count integer, |
| 389 |
sourceaccessioncode text, |
|
| 390 | 391 |
plantobservation_id integer, |
| 391 | 392 |
stratum_id integer, |
| 392 | 393 |
coverindex_id integer, |
| ... | ... | |
| 938 | 939 |
|
| 939 | 940 |
CREATE TABLE location ( |
| 940 | 941 |
location_id integer NOT NULL, |
| 941 |
parent_id integer, |
|
| 942 | 942 |
datasource_id integer NOT NULL, |
| 943 | 943 |
sourceaccessioncode text NOT NULL, |
| 944 |
parent_id integer, |
|
| 944 | 945 |
centerlatitude double precision, |
| 945 | 946 |
centerlongitude double precision, |
| 946 | 947 |
locationaccuracy double precision, |
| ... | ... | |
| 1082 | 1083 |
|
| 1083 | 1084 |
CREATE TABLE locationevent ( |
| 1084 | 1085 |
locationevent_id integer NOT NULL, |
| 1086 |
datasource_id integer, |
|
| 1087 |
sourceaccessioncode text, |
|
| 1085 | 1088 |
parent_id integer, |
| 1086 | 1089 |
location_id integer NOT NULL, |
| 1087 | 1090 |
project_id integer, |
| 1088 | 1091 |
authorlocationcode text, |
| 1089 |
sourceaccessioncode text, |
|
| 1090 | 1092 |
previous_id integer, |
| 1091 | 1093 |
dateaccuracy text, |
| 1092 | 1094 |
method_id integer, |
| ... | ... | |
| 1979 | 1981 |
|
| 1980 | 1982 |
CREATE TABLE plantobservation ( |
| 1981 | 1983 |
plantobservation_id integer NOT NULL, |
| 1984 |
datasource_id integer, |
|
| 1985 |
sourceaccessioncode text, |
|
| 1982 | 1986 |
overallheight double precision, |
| 1983 | 1987 |
overallheightaccuracy double precision, |
| 1984 | 1988 |
authorplantcode text, |
| 1985 | 1989 |
stemcount integer, |
| 1986 |
sourceaccessioncode text, |
|
| 1987 | 1990 |
plant_id integer, |
| 1988 | 1991 |
accessioncode text |
| 1989 | 1992 |
); |
| ... | ... | |
| 2095 | 2098 |
|
| 2096 | 2099 |
CREATE TABLE project ( |
| 2097 | 2100 |
project_id integer NOT NULL, |
| 2101 |
datasource_id integer, |
|
| 2102 |
sourceaccessioncode text, |
|
| 2098 | 2103 |
projectname text NOT NULL, |
| 2099 | 2104 |
projectdescription text, |
| 2100 | 2105 |
startdate timestamp with time zone, |
| 2101 | 2106 |
stopdate timestamp with time zone, |
| 2102 | 2107 |
d_obscount integer, |
| 2103 | 2108 |
d_lastlocationaddeddate timestamp with time zone, |
| 2104 |
datasource_id integer, |
|
| 2105 | 2109 |
accessioncode text |
| 2106 | 2110 |
); |
| 2107 | 2111 |
|
| ... | ... | |
| 2606 | 2610 |
|
| 2607 | 2611 |
CREATE TABLE taxonoccurrence ( |
| 2608 | 2612 |
taxonoccurrence_id integer NOT NULL, |
| 2613 |
datasource_id integer, |
|
| 2614 |
sourceaccessioncode text, |
|
| 2609 | 2615 |
locationevent_id integer, |
| 2610 |
sourceaccessioncode text, |
|
| 2611 | 2616 |
verbatimcollectorname text, |
| 2612 | 2617 |
growthform growthform, |
| 2613 | 2618 |
iscultivated boolean, |
| ... | ... | |
| 2719 | 2724 |
|
| 2720 | 2725 |
CREATE TABLE stemobservation ( |
| 2721 | 2726 |
stemobservation_id integer NOT NULL, |
| 2727 |
datasource_id integer, |
|
| 2728 |
sourceaccessioncode text, |
|
| 2722 | 2729 |
plantobservation_id integer NOT NULL, |
| 2723 |
sourceaccessioncode text, |
|
| 2724 | 2730 |
authorstemcode text, |
| 2725 | 2731 |
xposition double precision, |
| 2726 | 2732 |
yposition double precision, |
| ... | ... | |
| 4127 | 4133 |
|
| 4128 | 4134 |
|
| 4129 | 4135 |
-- |
| 4136 |
-- Name: aggregateoccurrence_unique_datasource; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4137 |
-- |
|
| 4138 |
|
|
| 4139 |
CREATE UNIQUE INDEX aggregateoccurrence_unique_datasource ON aggregateoccurrence USING btree ((COALESCE(datasource_id, 2147483647)), (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE ((datasource_id IS NOT NULL) AND (sourceaccessioncode IS NOT NULL)); |
|
| 4140 |
|
|
| 4141 |
|
|
| 4142 |
-- |
|
| 4130 | 4143 |
-- Name: commclass_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4131 | 4144 |
-- |
| 4132 | 4145 |
|
| ... | ... | |
| 4176 | 4189 |
|
| 4177 | 4190 |
|
| 4178 | 4191 |
-- |
| 4192 |
-- Name: location_unique_datasource; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4193 |
-- |
|
| 4194 |
|
|
| 4195 |
CREATE UNIQUE INDEX location_unique_datasource ON location USING btree (datasource_id, sourceaccessioncode); |
|
| 4196 |
|
|
| 4197 |
|
|
| 4198 |
-- |
|
| 4179 | 4199 |
-- Name: locationevent_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4180 | 4200 |
-- |
| 4181 | 4201 |
|
| ... | ... | |
| 4190 | 4210 |
|
| 4191 | 4211 |
|
| 4192 | 4212 |
-- |
| 4213 |
-- Name: locationevent_unique_datasource; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4214 |
-- |
|
| 4215 |
|
|
| 4216 |
CREATE UNIQUE INDEX locationevent_unique_datasource ON locationevent USING btree ((COALESCE(datasource_id, 2147483647)), (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE ((datasource_id IS NOT NULL) AND (sourceaccessioncode IS NOT NULL)); |
|
| 4217 |
|
|
| 4218 |
|
|
| 4219 |
-- |
|
| 4193 | 4220 |
-- Name: locationevent_unique_parent_authorcode; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4194 | 4221 |
-- |
| 4195 | 4222 |
|
| ... | ... | |
| 4288 | 4315 |
|
| 4289 | 4316 |
|
| 4290 | 4317 |
-- |
| 4318 |
-- Name: plantobservation_unique_datasource; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4319 |
-- |
|
| 4320 |
|
|
| 4321 |
CREATE UNIQUE INDEX plantobservation_unique_datasource ON plantobservation USING btree ((COALESCE(datasource_id, 2147483647)), (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE ((datasource_id IS NOT NULL) AND (sourceaccessioncode IS NOT NULL)); |
|
| 4322 |
|
|
| 4323 |
|
|
| 4324 |
-- |
|
| 4291 | 4325 |
-- Name: plantstatus_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4292 | 4326 |
-- |
| 4293 | 4327 |
|
| ... | ... | |
| 4302 | 4336 |
|
| 4303 | 4337 |
|
| 4304 | 4338 |
-- |
| 4339 |
-- Name: project_unique_datasource; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4340 |
-- |
|
| 4341 |
|
|
| 4342 |
CREATE UNIQUE INDEX project_unique_datasource ON project USING btree ((COALESCE(datasource_id, 2147483647)), (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE ((datasource_id IS NOT NULL) AND (sourceaccessioncode IS NOT NULL)); |
|
| 4343 |
|
|
| 4344 |
|
|
| 4345 |
-- |
|
| 4305 | 4346 |
-- Name: reference_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4306 | 4347 |
-- |
| 4307 | 4348 |
|
| ... | ... | |
| 4351 | 4392 |
|
| 4352 | 4393 |
|
| 4353 | 4394 |
-- |
| 4395 |
-- Name: stemobservation_unique_datasource; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4396 |
-- |
|
| 4397 |
|
|
| 4398 |
CREATE UNIQUE INDEX stemobservation_unique_datasource ON stemobservation USING btree ((COALESCE(datasource_id, 2147483647)), (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE ((datasource_id IS NOT NULL) AND (sourceaccessioncode IS NOT NULL)); |
|
| 4399 |
|
|
| 4400 |
|
|
| 4401 |
-- |
|
| 4354 | 4402 |
-- Name: taxondetermination_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4355 | 4403 |
-- |
| 4356 | 4404 |
|
| ... | ... | |
| 4372 | 4420 |
|
| 4373 | 4421 |
|
| 4374 | 4422 |
-- |
| 4423 |
-- Name: taxonoccurrence_unique_datasource; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4424 |
-- |
|
| 4425 |
|
|
| 4426 |
CREATE UNIQUE INDEX taxonoccurrence_unique_datasource ON taxonoccurrence USING btree ((COALESCE(datasource_id, 2147483647)), (COALESCE(sourceaccessioncode, '\\N'::text))) WHERE ((datasource_id IS NOT NULL) AND (sourceaccessioncode IS NOT NULL)); |
|
| 4427 |
|
|
| 4428 |
|
|
| 4429 |
-- |
|
| 4375 | 4430 |
-- Name: userdefined_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4376 | 4431 |
-- |
| 4377 | 4432 |
|
Also available in: Unified diff
schemas/vegbien.sql: Added datasource_id to all tables with a sourceaccessioncode (and corresponding *_unique_datasource constraint on these columns) so they can be directly looked up using just the input table's own fkey to parent. This will enable loading hierarchical (plots) data without "breadcrumbs", a huge benefit! Also added sourceaccessioncode wherever there was a datasource_id, to standardize on these names as being the columns that link directly to the input table rows.