Revision 3615
Added by Aaron Marcuse-Kubitza over 12 years ago
| 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 |
|
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.