Revision 5855
Added by Aaron Marcuse-Kubitza almost 12 years ago
| vegbien.my.sql | ||
|---|---|---|
| 229 | 229 |
|
| 230 | 230 |
|
| 231 | 231 |
-- |
| 232 |
-- Name: placename_update_ancestors(); Type: FUNCTION; Schema: public; Owner: -
|
|
| 232 |
-- Name: place_matched_place_id_self_ref(); Type: FUNCTION; Schema: public; Owner: -
|
|
| 233 | 233 |
-- |
| 234 | 234 |
|
| 235 | 235 |
|
| 236 | 236 |
|
| 237 | 237 |
|
| 238 | 238 |
-- |
| 239 |
-- Name: placepath_matched_placepath_id_self_ref(); Type: FUNCTION; Schema: public; Owner: -
|
|
| 239 |
-- Name: placename_update_ancestors(); Type: FUNCTION; Schema: public; Owner: -
|
|
| 240 | 240 |
-- |
| 241 | 241 |
|
| 242 | 242 |
|
| ... | ... | |
| 794 | 794 |
CREATE TABLE locationplace ( |
| 795 | 795 |
locationplace_id int(11) NOT NULL, |
| 796 | 796 |
location_id int(11) NOT NULL, |
| 797 |
placepath_id int(11) NOT NULL,
|
|
| 797 |
place_id int(11) NOT NULL, |
|
| 798 | 798 |
identifier_id int(11) |
| 799 | 799 |
); |
| 800 | 800 |
|
| ... | ... | |
| 1003 | 1003 |
|
| 1004 | 1004 |
|
| 1005 | 1005 |
-- |
| 1006 |
-- Name: placepath; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 1006 |
-- Name: place; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 1007 | 1007 |
-- |
| 1008 | 1008 |
|
| 1009 |
CREATE TABLE placepath (
|
|
| 1010 |
placepath_id int(11) NOT NULL,
|
|
| 1009 |
CREATE TABLE place ( |
|
| 1010 |
place_id int(11) NOT NULL, |
|
| 1011 | 1011 |
creator_id int(11) NOT NULL, |
| 1012 | 1012 |
placecode text, |
| 1013 |
matched_placepath_id int(11),
|
|
| 1013 |
matched_place_id int(11), |
|
| 1014 | 1014 |
placename_id int(11), |
| 1015 | 1015 |
continent text, |
| 1016 | 1016 |
country text, |
| ... | ... | |
| 1023 | 1023 |
|
| 1024 | 1024 |
|
| 1025 | 1025 |
-- |
| 1026 |
-- Name: TABLE placepath; Type: COMMENT; Schema: public; Owner: -
|
|
| 1026 |
-- Name: TABLE place; Type: COMMENT; Schema: public; Owner: - |
|
| 1027 | 1027 |
-- |
| 1028 | 1028 |
|
| 1029 | 1029 |
|
| 1030 | 1030 |
|
| 1031 | 1031 |
|
| 1032 | 1032 |
-- |
| 1033 |
-- Name: COLUMN placepath.matched_placepath_id; Type: COMMENT; Schema: public; Owner: -
|
|
| 1033 |
-- Name: COLUMN place.matched_place_id; Type: COMMENT; Schema: public; Owner: -
|
|
| 1034 | 1034 |
-- |
| 1035 | 1035 |
|
| 1036 | 1036 |
|
| 1037 | 1037 |
|
| 1038 | 1038 |
|
| 1039 | 1039 |
-- |
| 1040 |
-- Name: COLUMN placepath.otherranks; Type: COMMENT; Schema: public; Owner: -
|
|
| 1040 |
-- Name: COLUMN place.otherranks; Type: COMMENT; Schema: public; Owner: - |
|
| 1041 | 1041 |
-- |
| 1042 | 1042 |
|
| 1043 | 1043 |
|
| ... | ... | |
| 2011 | 2011 |
|
| 2012 | 2012 |
|
| 2013 | 2013 |
-- |
| 2014 |
-- Name: place_place_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
|
| 2015 |
-- |
|
| 2016 |
|
|
| 2017 |
|
|
| 2018 |
|
|
| 2019 |
|
|
| 2020 |
-- |
|
| 2021 |
-- Name: place_place_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - |
|
| 2022 |
-- |
|
| 2023 |
|
|
| 2024 |
|
|
| 2025 |
|
|
| 2026 |
|
|
| 2027 |
-- |
|
| 2014 | 2028 |
-- Name: placecorrelation; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 2015 | 2029 |
-- |
| 2016 | 2030 |
|
| ... | ... | |
| 2090 | 2104 |
|
| 2091 | 2105 |
|
| 2092 | 2106 |
-- |
| 2093 |
-- Name: placepath_placepath_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
|
| 2094 |
-- |
|
| 2095 |
|
|
| 2096 |
|
|
| 2097 |
|
|
| 2098 |
|
|
| 2099 |
-- |
|
| 2100 |
-- Name: placepath_placepath_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - |
|
| 2101 |
-- |
|
| 2102 |
|
|
| 2103 |
|
|
| 2104 |
|
|
| 2105 |
|
|
| 2106 |
-- |
|
| 2107 | 2107 |
-- Name: plant; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 2108 | 2108 |
-- |
| 2109 | 2109 |
|
| ... | ... | |
| 3102 | 3102 |
|
| 3103 | 3103 |
|
| 3104 | 3104 |
-- |
| 3105 |
-- Name: placecorrelation_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3105 |
-- Name: place_id; Type: DEFAULT; Schema: public; Owner: - |
|
| 3106 | 3106 |
-- |
| 3107 | 3107 |
|
| 3108 | 3108 |
|
| 3109 | 3109 |
|
| 3110 | 3110 |
|
| 3111 | 3111 |
-- |
| 3112 |
-- Name: placename_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3112 |
-- Name: placecorrelation_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3113 | 3113 |
-- |
| 3114 | 3114 |
|
| 3115 | 3115 |
|
| 3116 | 3116 |
|
| 3117 | 3117 |
|
| 3118 | 3118 |
-- |
| 3119 |
-- Name: placepath_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3119 |
-- Name: placename_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3120 | 3120 |
-- |
| 3121 | 3121 |
|
| 3122 | 3122 |
|
| ... | ... | |
| 3591 | 3591 |
|
| 3592 | 3592 |
|
| 3593 | 3593 |
-- |
| 3594 |
-- Name: place_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3595 |
-- |
|
| 3596 |
|
|
| 3597 |
ALTER TABLE place |
|
| 3598 |
ADD CONSTRAINT place_pkey PRIMARY KEY (place_id); |
|
| 3599 |
|
|
| 3600 |
|
|
| 3601 |
-- |
|
| 3594 | 3602 |
-- Name: placecorrelation_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 3595 | 3603 |
-- |
| 3596 | 3604 |
|
| ... | ... | |
| 3615 | 3623 |
|
| 3616 | 3624 |
|
| 3617 | 3625 |
-- |
| 3618 |
-- Name: placepath_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3619 |
-- |
|
| 3620 |
|
|
| 3621 |
ALTER TABLE placepath |
|
| 3622 |
ADD CONSTRAINT placepath_pkey PRIMARY KEY (placepath_id); |
|
| 3623 |
|
|
| 3624 |
|
|
| 3625 |
-- |
|
| 3626 | 3626 |
-- Name: plant_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 3627 | 3627 |
-- |
| 3628 | 3628 |
|
| ... | ... | |
| 4128 | 4128 |
|
| 4129 | 4129 |
|
| 4130 | 4130 |
-- |
| 4131 |
-- Name: placename_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4131 |
-- Name: place_unique_within_creator_by_code; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4132 | 4132 |
-- |
| 4133 | 4133 |
|
| 4134 |
CREATE UNIQUE INDEX placename_accessioncode_index ON placename (accessioncode); |
|
| 4135 | 4134 |
|
| 4136 | 4135 |
|
| 4136 |
|
|
| 4137 | 4137 |
-- |
| 4138 |
-- Name: placename_unique; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4138 |
-- Name: place_unique_within_creator_by_name; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4139 | 4139 |
-- |
| 4140 | 4140 |
|
| 4141 | 4141 |
|
| 4142 | 4142 |
|
| 4143 | 4143 |
|
| 4144 | 4144 |
-- |
| 4145 |
-- Name: placepath_unique_within_creator_by_code; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4145 |
-- Name: placename_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4146 | 4146 |
-- |
| 4147 | 4147 |
|
| 4148 |
CREATE UNIQUE INDEX placename_accessioncode_index ON placename (accessioncode); |
|
| 4148 | 4149 |
|
| 4149 | 4150 |
|
| 4150 |
|
|
| 4151 | 4151 |
-- |
| 4152 |
-- Name: placepath_unique_within_creator_by_name; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4152 |
-- Name: placename_unique; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4153 | 4153 |
-- |
| 4154 | 4154 |
|
| 4155 | 4155 |
|
| ... | ... | |
| 4380 | 4380 |
|
| 4381 | 4381 |
|
| 4382 | 4382 |
-- |
| 4383 |
-- Name: placename_update_ancestors; Type: TRIGGER; Schema: public; Owner: -
|
|
| 4383 |
-- Name: place_matched_place_id_self_ref; Type: TRIGGER; Schema: public; Owner: -
|
|
| 4384 | 4384 |
-- |
| 4385 | 4385 |
|
| 4386 | 4386 |
|
| 4387 | 4387 |
|
| 4388 | 4388 |
|
| 4389 | 4389 |
-- |
| 4390 |
-- Name: placepath_matched_placepath_id_self_ref; Type: TRIGGER; Schema: public; Owner: -
|
|
| 4390 |
-- Name: placename_update_ancestors; Type: TRIGGER; Schema: public; Owner: -
|
|
| 4391 | 4391 |
-- |
| 4392 | 4392 |
|
| 4393 | 4393 |
|
| ... | ... | |
| 4846 | 4846 |
|
| 4847 | 4847 |
|
| 4848 | 4848 |
-- |
| 4849 |
-- Name: locationplace_placepath_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4849 |
-- Name: locationplace_place_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 4850 | 4850 |
-- |
| 4851 | 4851 |
|
| 4852 | 4852 |
ALTER TABLE locationplace |
| 4853 |
ADD CONSTRAINT locationplace_placepath_id_fkey FOREIGN KEY (placepath_id) REFERENCES placepath(placepath_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4853 |
ADD CONSTRAINT locationplace_place_id_fkey FOREIGN KEY (place_id) REFERENCES place(place_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4854 | 4854 |
|
| 4855 | 4855 |
|
| 4856 | 4856 |
-- |
| ... | ... | |
| 4947 | 4947 |
|
| 4948 | 4948 |
|
| 4949 | 4949 |
-- |
| 4950 |
-- Name: place_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 4951 |
-- |
|
| 4952 |
|
|
| 4953 |
|
|
| 4954 |
|
|
| 4955 |
|
|
| 4956 |
-- |
|
| 4957 |
-- Name: place_matched_place_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 4958 |
-- |
|
| 4959 |
|
|
| 4960 |
ALTER TABLE place |
|
| 4961 |
ADD CONSTRAINT place_matched_place_id_fkey FOREIGN KEY (matched_place_id) REFERENCES place(place_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 4962 |
|
|
| 4963 |
|
|
| 4964 |
-- |
|
| 4965 |
-- Name: place_place_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 4966 |
-- |
|
| 4967 |
|
|
| 4968 |
ALTER TABLE place |
|
| 4969 |
ADD CONSTRAINT place_place_id_fkey FOREIGN KEY (placename_id) REFERENCES placename(placename_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 4970 |
|
|
| 4971 |
|
|
| 4972 |
-- |
|
| 4950 | 4973 |
-- Name: placecorrelation_childplace_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 4951 | 4974 |
-- |
| 4952 | 4975 |
|
| ... | ... | |
| 4987 | 5010 |
|
| 4988 | 5011 |
|
| 4989 | 5012 |
-- |
| 4990 |
-- Name: placepath_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 4991 |
-- |
|
| 4992 |
|
|
| 4993 |
|
|
| 4994 |
|
|
| 4995 |
|
|
| 4996 |
-- |
|
| 4997 |
-- Name: placepath_matched_placepath_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 4998 |
-- |
|
| 4999 |
|
|
| 5000 |
ALTER TABLE placepath |
|
| 5001 |
ADD CONSTRAINT placepath_matched_placepath_id_fkey FOREIGN KEY (matched_placepath_id) REFERENCES placepath(placepath_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5002 |
|
|
| 5003 |
|
|
| 5004 |
-- |
|
| 5005 |
-- Name: placepath_place_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5006 |
-- |
|
| 5007 |
|
|
| 5008 |
ALTER TABLE placepath |
|
| 5009 |
ADD CONSTRAINT placepath_place_id_fkey FOREIGN KEY (placename_id) REFERENCES placename(placename_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5010 |
|
|
| 5011 |
|
|
| 5012 |
-- |
|
| 5013 | 5013 |
-- Name: plantobservation_aggregateoccurrence_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5014 | 5014 |
-- |
| 5015 | 5015 |
|
Also available in: Unified diff
schemas/vegbien.sql: Renamed placepath to place since this contains primary information about the place, including the reference to the canonical place