Revision 3199
Added by Aaron Marcuse-Kubitza over 12 years ago
| vegbien.my.sql | ||
|---|---|---|
| 103 | 103 |
|
| 104 | 104 |
|
| 105 | 105 |
-- |
| 106 |
-- Name: taxondetermination_taxonoccurrence_id_fkey(); Type: FUNCTION; Schema: public; Owner: - |
|
| 107 |
-- |
|
| 108 |
|
|
| 109 |
|
|
| 110 |
|
|
| 111 |
|
|
| 112 |
-- |
|
| 106 | 113 |
-- Name: concat(text); Type: AGGREGATE; Schema: public; Owner: - |
| 107 | 114 |
-- |
| 108 | 115 |
|
| ... | ... | |
| 2101 | 2108 |
|
| 2102 | 2109 |
|
| 2103 | 2110 |
-- |
| 2104 |
-- Name: specimenreplicate; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 2111 |
-- Name: taxonoccurrence; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 2105 | 2112 |
-- |
| 2106 | 2113 |
|
| 2107 |
CREATE TABLE specimenreplicate ( |
|
| 2108 |
specimenreplicate_id int(11) NOT NULL, |
|
| 2109 |
datasource_id int(11) NOT NULL, |
|
| 2110 |
collectioncode_dwc text, |
|
| 2111 |
catalognumber_dwc text, |
|
| 2112 |
collectiondate timestamp NULL, |
|
| 2113 |
institution_id int(11), |
|
| 2114 |
sourceaccessioncode text, |
|
| 2114 |
CREATE TABLE taxonoccurrence ( |
|
| 2115 | 2115 |
taxonoccurrence_id int(11) NOT NULL, |
| 2116 |
collectionnumber text, |
|
| 2117 |
specimen_id int(11), |
|
| 2118 |
description text, |
|
| 2116 |
locationevent_id int(11), |
|
| 2117 |
verbatimcollectorname text, |
|
| 2118 |
growthform text, |
|
| 2119 |
iscultivated int(1), |
|
| 2120 |
cultivatedbasis text, |
|
| 2121 |
isnative int(1), |
|
| 2119 | 2122 |
accessioncode text |
| 2120 | 2123 |
); |
| 2121 | 2124 |
|
| 2122 | 2125 |
|
| 2123 | 2126 |
-- |
| 2124 |
-- Name: TABLE specimenreplicate; Type: COMMENT; Schema: public; Owner: -
|
|
| 2127 |
-- Name: TABLE taxonoccurrence; Type: COMMENT; Schema: public; Owner: -
|
|
| 2125 | 2128 |
-- |
| 2126 | 2129 |
|
| 2127 | 2130 |
|
| 2128 | 2131 |
|
| 2129 | 2132 |
|
| 2130 | 2133 |
-- |
| 2131 |
-- Name: COLUMN specimenreplicate.collectioncode_dwc; Type: COMMENT; Schema: public; Owner: -
|
|
| 2134 |
-- Name: COLUMN taxonoccurrence.iscultivated; Type: COMMENT; Schema: public; Owner: -
|
|
| 2132 | 2135 |
-- |
| 2133 | 2136 |
|
| 2134 | 2137 |
|
| 2135 | 2138 |
|
| 2136 | 2139 |
|
| 2137 | 2140 |
-- |
| 2138 |
-- Name: COLUMN specimenreplicate.institution_id; Type: COMMENT; Schema: public; Owner: -
|
|
| 2141 |
-- Name: COLUMN taxonoccurrence.cultivatedbasis; Type: COMMENT; Schema: public; Owner: -
|
|
| 2139 | 2142 |
-- |
| 2140 | 2143 |
|
| 2141 | 2144 |
|
| 2142 | 2145 |
|
| 2143 | 2146 |
|
| 2144 | 2147 |
-- |
| 2145 |
-- Name: COLUMN specimenreplicate.collectionnumber; Type: COMMENT; Schema: public; Owner: -
|
|
| 2148 |
-- Name: COLUMN taxonoccurrence.isnative; Type: COMMENT; Schema: public; Owner: -
|
|
| 2146 | 2149 |
-- |
| 2147 | 2150 |
|
| 2148 | 2151 |
|
| 2149 | 2152 |
|
| 2150 | 2153 |
|
| 2151 | 2154 |
-- |
| 2152 |
-- Name: specimenreplicate_specimenreplicate_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
|
| 2155 |
-- Name: taxonoccurrence_taxonoccurrence_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
|
| 2153 | 2156 |
-- |
| 2154 | 2157 |
|
| 2155 | 2158 |
|
| 2156 | 2159 |
|
| 2157 | 2160 |
|
| 2158 | 2161 |
-- |
| 2159 |
-- Name: specimenreplicate_specimenreplicate_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
|
| 2162 |
-- Name: taxonoccurrence_taxonoccurrence_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
|
| 2160 | 2163 |
-- |
| 2161 | 2164 |
|
| 2162 | 2165 |
|
| ... | ... | |
| 2165 | 2168 |
|
| 2166 | 2169 |
|
| 2167 | 2170 |
-- |
| 2171 |
-- Name: specimenreplicate; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 2172 |
-- |
|
| 2173 |
|
|
| 2174 |
CREATE TABLE specimenreplicate ( |
|
| 2175 |
taxonoccurrence_id int(11) NOT NULL AUTO_INCREMENT, |
|
| 2176 |
datasource_id int(11) NOT NULL, |
|
| 2177 |
collectioncode_dwc text, |
|
| 2178 |
catalognumber_dwc text, |
|
| 2179 |
collectiondate timestamp NULL, |
|
| 2180 |
institution_id int(11), |
|
| 2181 |
sourceaccessioncode text, |
|
| 2182 |
collectionnumber text, |
|
| 2183 |
specimen_id int(11), |
|
| 2184 |
description text |
|
| 2185 |
) |
|
| 2186 |
; |
|
| 2187 |
ALTER TABLE specimenreplicate |
|
| 2188 |
ADD CONSTRAINT specimenreplicate_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id) REFERENCES taxonoccurrence(taxonoccurrence_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 2189 |
|
|
| 2190 |
|
|
| 2191 |
|
|
| 2192 |
-- |
|
| 2193 |
-- Name: TABLE specimenreplicate; Type: COMMENT; Schema: public; Owner: - |
|
| 2194 |
-- |
|
| 2195 |
|
|
| 2196 |
|
|
| 2197 |
|
|
| 2198 |
|
|
| 2199 |
-- |
|
| 2200 |
-- Name: COLUMN specimenreplicate.collectioncode_dwc; Type: COMMENT; Schema: public; Owner: - |
|
| 2201 |
-- |
|
| 2202 |
|
|
| 2203 |
|
|
| 2204 |
|
|
| 2205 |
|
|
| 2206 |
-- |
|
| 2207 |
-- Name: COLUMN specimenreplicate.institution_id; Type: COMMENT; Schema: public; Owner: - |
|
| 2208 |
-- |
|
| 2209 |
|
|
| 2210 |
|
|
| 2211 |
|
|
| 2212 |
|
|
| 2213 |
-- |
|
| 2214 |
-- Name: COLUMN specimenreplicate.collectionnumber; Type: COMMENT; Schema: public; Owner: - |
|
| 2215 |
-- |
|
| 2216 |
|
|
| 2217 |
|
|
| 2218 |
|
|
| 2219 |
|
|
| 2220 |
-- |
|
| 2168 | 2221 |
-- Name: stemobservation; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 2169 | 2222 |
-- |
| 2170 | 2223 |
|
| ... | ... | |
| 2343 | 2396 |
|
| 2344 | 2397 |
|
| 2345 | 2398 |
-- |
| 2346 |
-- Name: taxonoccurrence; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 2347 |
-- |
|
| 2348 |
|
|
| 2349 |
CREATE TABLE taxonoccurrence ( |
|
| 2350 |
taxonoccurrence_id int(11) NOT NULL, |
|
| 2351 |
locationevent_id int(11), |
|
| 2352 |
verbatimcollectorname text, |
|
| 2353 |
growthform text, |
|
| 2354 |
iscultivated int(1), |
|
| 2355 |
cultivatedbasis text, |
|
| 2356 |
isnative int(1), |
|
| 2357 |
accessioncode text |
|
| 2358 |
); |
|
| 2359 |
|
|
| 2360 |
|
|
| 2361 |
-- |
|
| 2362 |
-- Name: TABLE taxonoccurrence; Type: COMMENT; Schema: public; Owner: - |
|
| 2363 |
-- |
|
| 2364 |
|
|
| 2365 |
|
|
| 2366 |
|
|
| 2367 |
|
|
| 2368 |
-- |
|
| 2369 |
-- Name: COLUMN taxonoccurrence.iscultivated; Type: COMMENT; Schema: public; Owner: - |
|
| 2370 |
-- |
|
| 2371 |
|
|
| 2372 |
|
|
| 2373 |
|
|
| 2374 |
|
|
| 2375 |
-- |
|
| 2376 |
-- Name: COLUMN taxonoccurrence.cultivatedbasis; Type: COMMENT; Schema: public; Owner: - |
|
| 2377 |
-- |
|
| 2378 |
|
|
| 2379 |
|
|
| 2380 |
|
|
| 2381 |
|
|
| 2382 |
-- |
|
| 2383 |
-- Name: COLUMN taxonoccurrence.isnative; Type: COMMENT; Schema: public; Owner: - |
|
| 2384 |
-- |
|
| 2385 |
|
|
| 2386 |
|
|
| 2387 |
|
|
| 2388 |
|
|
| 2389 |
-- |
|
| 2390 |
-- Name: taxonoccurrence_taxonoccurrence_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
|
| 2391 |
-- |
|
| 2392 |
|
|
| 2393 |
|
|
| 2394 |
|
|
| 2395 |
|
|
| 2396 |
-- |
|
| 2397 |
-- Name: taxonoccurrence_taxonoccurrence_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - |
|
| 2398 |
-- |
|
| 2399 |
|
|
| 2400 |
|
|
| 2401 |
|
|
| 2402 |
|
|
| 2403 |
-- |
|
| 2404 | 2399 |
-- Name: telephone; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 2405 | 2400 |
-- |
| 2406 | 2401 |
|
| ... | ... | |
| 2857 | 2852 |
|
| 2858 | 2853 |
|
| 2859 | 2854 |
-- |
| 2860 |
-- Name: specimenreplicate_id; Type: DEFAULT; Schema: public; Owner: - |
|
| 2861 |
-- |
|
| 2862 |
|
|
| 2863 |
|
|
| 2864 |
|
|
| 2865 |
|
|
| 2866 |
-- |
|
| 2867 | 2855 |
-- Name: stemobservation_id; Type: DEFAULT; Schema: public; Owner: - |
| 2868 | 2856 |
-- |
| 2869 | 2857 |
|
| ... | ... | |
| 3490 | 3478 |
-- |
| 3491 | 3479 |
|
| 3492 | 3480 |
ALTER TABLE specimenreplicate |
| 3493 |
ADD CONSTRAINT specimenreplicate_pkey PRIMARY KEY (specimenreplicate_id);
|
|
| 3481 |
ADD CONSTRAINT specimenreplicate_pkey PRIMARY KEY (taxonoccurrence_id);
|
|
| 3494 | 3482 |
|
| 3495 | 3483 |
|
| 3496 | 3484 |
-- |
| ... | ... | |
| 4085 | 4073 |
|
| 4086 | 4074 |
|
| 4087 | 4075 |
-- |
| 4088 |
-- Name: fki_specimenreplicate_taxonoccurrence_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4089 |
-- |
|
| 4090 |
|
|
| 4091 |
CREATE INDEX fki_specimenreplicate_taxonoccurrence_id ON specimenreplicate (taxonoccurrence_id); |
|
| 4092 |
|
|
| 4093 |
|
|
| 4094 |
-- |
|
| 4095 | 4076 |
-- Name: fki_stemtag_plant_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4096 | 4077 |
-- |
| 4097 | 4078 |
|
| ... | ... | |
| 4834 | 4815 |
|
| 4835 | 4816 |
|
| 4836 | 4817 |
-- |
| 4818 |
-- Name: taxondetermination_taxonoccurrence_id_fkey; Type: TRIGGER; Schema: public; Owner: - |
|
| 4819 |
-- |
|
| 4820 |
|
|
| 4821 |
|
|
| 4822 |
|
|
| 4823 |
|
|
| 4824 |
-- |
|
| 4837 | 4825 |
-- Name: address_organization_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 4838 | 4826 |
-- |
| 4839 | 4827 |
|
| ... | ... | |
| 5612 | 5600 |
|
| 5613 | 5601 |
|
| 5614 | 5602 |
-- |
| 5615 |
-- Name: specimenreplicate_taxonoccurrence_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5616 |
-- |
|
| 5617 |
|
|
| 5618 |
ALTER TABLE specimenreplicate |
|
| 5619 |
ADD CONSTRAINT specimenreplicate_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id) REFERENCES taxonoccurrence(taxonoccurrence_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5620 |
|
|
| 5621 |
|
|
| 5622 |
-- |
|
| 5623 | 5603 |
-- Name: stemobservation_plantobservation_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5624 | 5604 |
-- |
| 5625 | 5605 |
|
| ... | ... | |
| 5690 | 5670 |
|
| 5691 | 5671 |
|
| 5692 | 5672 |
-- |
| 5693 |
-- Name: taxondetermination_taxonoccurrence_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5694 |
-- |
|
| 5695 |
|
|
| 5696 |
ALTER TABLE taxondetermination |
|
| 5697 |
ADD CONSTRAINT taxondetermination_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id) REFERENCES taxonoccurrence(taxonoccurrence_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5698 |
|
|
| 5699 |
|
|
| 5700 |
-- |
|
| 5701 | 5673 |
-- Name: taxonoccurrence_locationevent_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5702 | 5674 |
-- |
| 5703 | 5675 |
|
| ... | ... | |
| 5726 | 5698 |
-- |
| 5727 | 5699 |
|
| 5728 | 5700 |
ALTER TABLE voucher |
| 5729 |
ADD CONSTRAINT voucher_specimenreplicate_id FOREIGN KEY (specimenreplicate_id) REFERENCES specimenreplicate(specimenreplicate_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5701 |
ADD CONSTRAINT voucher_specimenreplicate_id FOREIGN KEY (specimenreplicate_id) REFERENCES specimenreplicate(taxonoccurrence_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5730 | 5702 |
|
| 5731 | 5703 |
|
| 5732 | 5704 |
-- |
Also available in: Unified diff
VegBIEN: Fixing import issue related to duplicate entries in tables with children, where when a new table entry duplicates an existing entry, the 1:1 tables of that table and those tables' children are not merged, causing them to become orphaned. It is described in detail at <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/Import_issues#Merging-duplicates-with-children>, including the rationale for this solution. Note that this is not a bug in column-based import, it applies to row-based import as well. This commit fixes the issue for specimenreplicate->taxonoccurrence.