Revision 5854
Added by Aaron Marcuse-Kubitza about 12 years ago
| schemas/vegbien.my.sql | ||
|---|---|---|
| 229 | 229 |
|
| 230 | 230 |
|
| 231 | 231 |
-- |
| 232 |
-- Name: place_update_ancestors(); Type: FUNCTION; Schema: public; Owner: - |
|
| 232 |
-- Name: placename_update_ancestors(); Type: FUNCTION; Schema: public; Owner: -
|
|
| 233 | 233 |
-- |
| 234 | 234 |
|
| 235 | 235 |
|
| ... | ... | |
| 1011 | 1011 |
creator_id int(11) NOT NULL, |
| 1012 | 1012 |
placecode text, |
| 1013 | 1013 |
matched_placepath_id int(11), |
| 1014 |
place_id int(11), |
|
| 1014 |
placename_id int(11),
|
|
| 1015 | 1015 |
continent text, |
| 1016 | 1016 |
country text, |
| 1017 | 1017 |
stateprovince text, |
| ... | ... | |
| 2011 | 2011 |
|
| 2012 | 2012 |
|
| 2013 | 2013 |
-- |
| 2014 |
-- Name: place; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 2014 |
-- Name: placecorrelation; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 2015 | 2015 |
-- |
| 2016 | 2016 |
|
| 2017 |
CREATE TABLE place ( |
|
| 2018 |
place_id int(11) NOT NULL, |
|
| 2019 |
parent_id int(11), |
|
| 2020 |
rank text NOT NULL, |
|
| 2021 |
placename text NOT NULL, |
|
| 2022 |
placecode text, |
|
| 2023 |
placedescription text, |
|
| 2024 |
accessioncode text |
|
| 2017 |
CREATE TABLE placecorrelation ( |
|
| 2018 |
placecorrelation_id int(11) NOT NULL, |
|
| 2019 |
parentplace_id int(11) NOT NULL, |
|
| 2020 |
childplace_id int(11) NOT NULL, |
|
| 2021 |
placeconvergence text NOT NULL |
|
| 2025 | 2022 |
); |
| 2026 | 2023 |
|
| 2027 | 2024 |
|
| 2028 | 2025 |
-- |
| 2029 |
-- Name: TABLE place; Type: COMMENT; Schema: public; Owner: -
|
|
| 2026 |
-- Name: placecorrelation_placecorrelation_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
|
| 2030 | 2027 |
-- |
| 2031 | 2028 |
|
| 2032 | 2029 |
|
| 2033 | 2030 |
|
| 2034 | 2031 |
|
| 2035 | 2032 |
-- |
| 2036 |
-- Name: place_ancestor; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 2033 |
-- Name: placecorrelation_placecorrelation_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
|
| 2037 | 2034 |
-- |
| 2038 | 2035 |
|
| 2039 |
CREATE TABLE place_ancestor ( |
|
| 2040 |
place_id int(11) NOT NULL, |
|
| 2041 |
ancestor_id int(11) NOT NULL |
|
| 2042 |
); |
|
| 2043 | 2036 |
|
| 2044 | 2037 |
|
| 2038 |
|
|
| 2045 | 2039 |
-- |
| 2046 |
-- Name: TABLE place_ancestor; Type: COMMENT; Schema: public; Owner: -
|
|
| 2040 |
-- Name: placename; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 2047 | 2041 |
-- |
| 2048 | 2042 |
|
| 2043 |
CREATE TABLE placename ( |
|
| 2044 |
placename_id int(11) NOT NULL, |
|
| 2045 |
parent_id int(11), |
|
| 2046 |
rank text NOT NULL, |
|
| 2047 |
placename text NOT NULL, |
|
| 2048 |
placecode text, |
|
| 2049 |
placedescription text, |
|
| 2050 |
accessioncode text |
|
| 2051 |
); |
|
| 2049 | 2052 |
|
| 2050 | 2053 |
|
| 2051 |
|
|
| 2052 | 2054 |
-- |
| 2053 |
-- Name: place_place_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
|
| 2055 |
-- Name: TABLE placename; Type: COMMENT; Schema: public; Owner: -
|
|
| 2054 | 2056 |
-- |
| 2055 | 2057 |
|
| 2056 | 2058 |
|
| 2057 | 2059 |
|
| 2058 | 2060 |
|
| 2059 | 2061 |
-- |
| 2060 |
-- Name: place_place_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
|
| 2062 |
-- Name: placename_ancestor; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 2061 | 2063 |
-- |
| 2062 | 2064 |
|
| 2065 |
CREATE TABLE placename_ancestor ( |
|
| 2066 |
placename_id int(11) NOT NULL, |
|
| 2067 |
ancestor_id int(11) NOT NULL |
|
| 2068 |
); |
|
| 2063 | 2069 |
|
| 2064 | 2070 |
|
| 2065 |
|
|
| 2066 | 2071 |
-- |
| 2067 |
-- Name: placecorrelation; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 2072 |
-- Name: TABLE placename_ancestor; Type: COMMENT; Schema: public; Owner: -
|
|
| 2068 | 2073 |
-- |
| 2069 | 2074 |
|
| 2070 |
CREATE TABLE placecorrelation ( |
|
| 2071 |
placecorrelation_id int(11) NOT NULL, |
|
| 2072 |
parentplace_id int(11) NOT NULL, |
|
| 2073 |
childplace_id int(11) NOT NULL, |
|
| 2074 |
placeconvergence text NOT NULL |
|
| 2075 |
); |
|
| 2076 | 2075 |
|
| 2077 | 2076 |
|
| 2077 |
|
|
| 2078 | 2078 |
-- |
| 2079 |
-- Name: placecorrelation_placecorrelation_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
|
| 2079 |
-- Name: placename_placename_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
|
| 2080 | 2080 |
-- |
| 2081 | 2081 |
|
| 2082 | 2082 |
|
| 2083 | 2083 |
|
| 2084 | 2084 |
|
| 2085 | 2085 |
-- |
| 2086 |
-- Name: placecorrelation_placecorrelation_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
|
| 2086 |
-- Name: placename_placename_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
|
| 2087 | 2087 |
-- |
| 2088 | 2088 |
|
| 2089 | 2089 |
|
| ... | ... | |
| 3102 | 3102 |
|
| 3103 | 3103 |
|
| 3104 | 3104 |
-- |
| 3105 |
-- Name: place_id; Type: DEFAULT; Schema: public; Owner: - |
|
| 3105 |
-- Name: placecorrelation_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3106 | 3106 |
-- |
| 3107 | 3107 |
|
| 3108 | 3108 |
|
| 3109 | 3109 |
|
| 3110 | 3110 |
|
| 3111 | 3111 |
-- |
| 3112 |
-- Name: placecorrelation_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3112 |
-- Name: placename_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3113 | 3113 |
-- |
| 3114 | 3114 |
|
| 3115 | 3115 |
|
| ... | ... | |
| 3591 | 3591 |
|
| 3592 | 3592 |
|
| 3593 | 3593 |
-- |
| 3594 |
-- Name: place_ancestor_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
| 3594 |
-- Name: placecorrelation_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
| 3595 | 3595 |
-- |
| 3596 | 3596 |
|
| 3597 |
ALTER TABLE place_ancestor
|
|
| 3598 |
ADD CONSTRAINT place_ancestor_pkey PRIMARY KEY (place_id, ancestor_id);
|
|
| 3597 |
ALTER TABLE placecorrelation
|
|
| 3598 |
ADD CONSTRAINT placecorrelation_pkey PRIMARY KEY (placecorrelation_id);
|
|
| 3599 | 3599 |
|
| 3600 | 3600 |
|
| 3601 | 3601 |
-- |
| 3602 |
-- Name: place_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3602 |
-- Name: placename_ancestor_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
| 3603 | 3603 |
-- |
| 3604 | 3604 |
|
| 3605 |
ALTER TABLE place |
|
| 3606 |
ADD CONSTRAINT place_pkey PRIMARY KEY (place_id);
|
|
| 3605 |
ALTER TABLE placename_ancestor
|
|
| 3606 |
ADD CONSTRAINT placename_ancestor_pkey PRIMARY KEY (placename_id, ancestor_id);
|
|
| 3607 | 3607 |
|
| 3608 | 3608 |
|
| 3609 | 3609 |
-- |
| 3610 |
-- Name: placecorrelation_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
| 3610 |
-- Name: placename_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
| 3611 | 3611 |
-- |
| 3612 | 3612 |
|
| 3613 |
ALTER TABLE placecorrelation
|
|
| 3614 |
ADD CONSTRAINT placecorrelation_pkey PRIMARY KEY (placecorrelation_id);
|
|
| 3613 |
ALTER TABLE placename
|
|
| 3614 |
ADD CONSTRAINT placename_pkey PRIMARY KEY (placename_id);
|
|
| 3615 | 3615 |
|
| 3616 | 3616 |
|
| 3617 | 3617 |
-- |
| ... | ... | |
| 4128 | 4128 |
|
| 4129 | 4129 |
|
| 4130 | 4130 |
-- |
| 4131 |
-- Name: place_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4131 |
-- Name: placename_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4132 | 4132 |
-- |
| 4133 | 4133 |
|
| 4134 |
CREATE UNIQUE INDEX place_accessioncode_index ON place (accessioncode);
|
|
| 4134 |
CREATE UNIQUE INDEX placename_accessioncode_index ON placename (accessioncode);
|
|
| 4135 | 4135 |
|
| 4136 | 4136 |
|
| 4137 | 4137 |
-- |
| 4138 |
-- Name: place_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4138 |
-- Name: placename_unique; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4139 | 4139 |
-- |
| 4140 | 4140 |
|
| 4141 | 4141 |
|
| ... | ... | |
| 4380 | 4380 |
|
| 4381 | 4381 |
|
| 4382 | 4382 |
-- |
| 4383 |
-- Name: place_update_ancestors; Type: TRIGGER; Schema: public; Owner: - |
|
| 4383 |
-- Name: placename_update_ancestors; Type: TRIGGER; Schema: public; Owner: -
|
|
| 4384 | 4384 |
-- |
| 4385 | 4385 |
|
| 4386 | 4386 |
|
| ... | ... | |
| 4947 | 4947 |
|
| 4948 | 4948 |
|
| 4949 | 4949 |
-- |
| 4950 |
-- Name: place_ancestor_ancestor_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4950 |
-- Name: placecorrelation_childplace_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4951 | 4951 |
-- |
| 4952 | 4952 |
|
| 4953 |
ALTER TABLE place_ancestor
|
|
| 4954 |
ADD CONSTRAINT place_ancestor_ancestor_id_fkey FOREIGN KEY (ancestor_id) REFERENCES place(place_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4953 |
ALTER TABLE placecorrelation
|
|
| 4954 |
ADD CONSTRAINT placecorrelation_childplace_id_fkey FOREIGN KEY (childplace_id) REFERENCES placename(placename_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4955 | 4955 |
|
| 4956 | 4956 |
|
| 4957 | 4957 |
-- |
| 4958 |
-- Name: place_ancestor_place_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4958 |
-- Name: placecorrelation_parentplace_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4959 | 4959 |
-- |
| 4960 | 4960 |
|
| 4961 |
ALTER TABLE place_ancestor
|
|
| 4962 |
ADD CONSTRAINT place_ancestor_place_id_fkey FOREIGN KEY (place_id) REFERENCES place(place_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4961 |
ALTER TABLE placecorrelation
|
|
| 4962 |
ADD CONSTRAINT placecorrelation_parentplace_id_fkey FOREIGN KEY (parentplace_id) REFERENCES placename(placename_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4963 | 4963 |
|
| 4964 | 4964 |
|
| 4965 | 4965 |
-- |
| 4966 |
-- Name: place_parent_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4966 |
-- Name: placename_ancestor_ancestor_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4967 | 4967 |
-- |
| 4968 | 4968 |
|
| 4969 |
ALTER TABLE place |
|
| 4970 |
ADD CONSTRAINT place_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES place(place_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4969 |
ALTER TABLE placename_ancestor
|
|
| 4970 |
ADD CONSTRAINT placename_ancestor_ancestor_id_fkey FOREIGN KEY (ancestor_id) REFERENCES placename(placename_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4971 | 4971 |
|
| 4972 | 4972 |
|
| 4973 | 4973 |
-- |
| 4974 |
-- Name: placecorrelation_childplace_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4974 |
-- Name: placename_ancestor_place_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4975 | 4975 |
-- |
| 4976 | 4976 |
|
| 4977 |
ALTER TABLE placecorrelation
|
|
| 4978 |
ADD CONSTRAINT placecorrelation_childplace_id_fkey FOREIGN KEY (childplace_id) REFERENCES place(place_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4977 |
ALTER TABLE placename_ancestor
|
|
| 4978 |
ADD CONSTRAINT placename_ancestor_place_id_fkey FOREIGN KEY (placename_id) REFERENCES placename(placename_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4979 | 4979 |
|
| 4980 | 4980 |
|
| 4981 | 4981 |
-- |
| 4982 |
-- Name: placecorrelation_parentplace_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4982 |
-- Name: placename_parent_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4983 | 4983 |
-- |
| 4984 | 4984 |
|
| 4985 |
ALTER TABLE placecorrelation
|
|
| 4986 |
ADD CONSTRAINT placecorrelation_parentplace_id_fkey FOREIGN KEY (parentplace_id) REFERENCES place(place_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4985 |
ALTER TABLE placename
|
|
| 4986 |
ADD CONSTRAINT placename_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES placename(placename_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4987 | 4987 |
|
| 4988 | 4988 |
|
| 4989 | 4989 |
-- |
| ... | ... | |
| 5006 | 5006 |
-- |
| 5007 | 5007 |
|
| 5008 | 5008 |
ALTER TABLE placepath |
| 5009 |
ADD CONSTRAINT placepath_place_id_fkey FOREIGN KEY (place_id) REFERENCES place(place_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5009 |
ADD CONSTRAINT placepath_place_id_fkey FOREIGN KEY (placename_id) REFERENCES placename(placename_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5010 | 5010 |
|
| 5011 | 5011 |
|
| 5012 | 5012 |
-- |
| schemas/vegbien.sql | ||
|---|---|---|
| 469 | 469 |
|
| 470 | 470 |
|
| 471 | 471 |
-- |
| 472 |
-- Name: place_update_ancestors(); Type: FUNCTION; Schema: public; Owner: - |
|
| 472 |
-- Name: placename_update_ancestors(); Type: FUNCTION; Schema: public; Owner: -
|
|
| 473 | 473 |
-- |
| 474 | 474 |
|
| 475 |
CREATE FUNCTION place_update_ancestors() RETURNS trigger |
|
| 475 |
CREATE FUNCTION placename_update_ancestors() RETURNS trigger
|
|
| 476 | 476 |
LANGUAGE plpgsql |
| 477 | 477 |
AS $$ |
| 478 | 478 |
BEGIN |
| 479 | 479 |
-- Delete existing ancestors |
| 480 |
DELETE FROM place_ancestor WHERE place_id = new.place_id;
|
|
| 480 |
DELETE FROM placename_ancestor WHERE placename_id = new.placename_id;
|
|
| 481 | 481 |
|
| 482 | 482 |
IF new.parent_id IS NOT NULL THEN |
| 483 | 483 |
-- Copy parent's ancestors to this node's ancestors |
| 484 | 484 |
INSERT |
| 485 |
INTO place_ancestor |
|
| 486 |
(place_id, ancestor_id) |
|
| 485 |
INTO placename_ancestor
|
|
| 486 |
(placename_id, ancestor_id)
|
|
| 487 | 487 |
SELECT |
| 488 |
new.place_id, ancestor_id |
|
| 489 |
FROM place_ancestor |
|
| 490 |
WHERE place_id = new.parent_id |
|
| 488 |
new.placename_id, ancestor_id
|
|
| 489 |
FROM placename_ancestor
|
|
| 490 |
WHERE placename_id = new.parent_id
|
|
| 491 | 491 |
; |
| 492 | 492 |
END IF; |
| 493 | 493 |
|
| ... | ... | |
| 496 | 496 |
the leaf node is the one you're looking for, in addition to that leaf node's |
| 497 | 497 |
ancestors. */ |
| 498 | 498 |
INSERT |
| 499 |
INTO place_ancestor |
|
| 500 |
(place_id, ancestor_id) |
|
| 501 |
VALUES (new.place_id, new.place_id)
|
|
| 499 |
INTO placename_ancestor
|
|
| 500 |
(placename_id, ancestor_id)
|
|
| 501 |
VALUES (new.placename_id, new.placename_id)
|
|
| 502 | 502 |
; |
| 503 | 503 |
|
| 504 | 504 |
-- Tell immediate children to update their ancestors lists, which will |
| 505 | 505 |
-- recursively tell all descendants |
| 506 |
UPDATE place |
|
| 507 |
SET place_id = place_id -- need at least one SET statement
|
|
| 508 |
-- Add COALESCE() to enable using place_unique index for lookup |
|
| 509 |
WHERE COALESCE(parent_id, 2147483647) = new.place_id |
|
| 510 |
AND place_id != new.place_id -- avoid infinite recursion
|
|
| 506 |
UPDATE placename
|
|
| 507 |
SET placename_id = placename_id -- need at least one SET statement
|
|
| 508 |
-- Add COALESCE() to enable using placename_unique index for lookup
|
|
| 509 |
WHERE COALESCE(parent_id, 2147483647) = new.placename_id
|
|
| 510 |
AND placename_id != new.placename_id -- avoid infinite recursion
|
|
| 511 | 511 |
; |
| 512 | 512 |
|
| 513 | 513 |
/* Note: We don't need an ON DELETE trigger to update the descendants' |
| 514 |
ancestors when a node is deleted, because the place.place_parent_id
|
|
| 514 |
ancestors when a node is deleted, because the placename.placename_parent_id
|
|
| 515 | 515 |
foreign key is set to ON DELETE CASCADE, which just removes all the |
| 516 | 516 |
descendants anyway. */ |
| 517 | 517 |
|
| ... | ... | |
| 1542 | 1542 |
creator_id integer NOT NULL, |
| 1543 | 1543 |
placecode text, |
| 1544 | 1544 |
matched_placepath_id integer, |
| 1545 |
place_id integer, |
|
| 1545 |
placename_id integer,
|
|
| 1546 | 1546 |
continent text, |
| 1547 | 1547 |
country text, |
| 1548 | 1548 |
stateprovince text, |
| ... | ... | |
| 2691 | 2691 |
|
| 2692 | 2692 |
|
| 2693 | 2693 |
-- |
| 2694 |
-- Name: place; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 2694 |
-- Name: placecorrelation; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 2695 | 2695 |
-- |
| 2696 | 2696 |
|
| 2697 |
CREATE TABLE place ( |
|
| 2698 |
place_id integer NOT NULL, |
|
| 2699 |
parent_id integer, |
|
| 2700 |
rank placerank NOT NULL, |
|
| 2701 |
placename text NOT NULL, |
|
| 2702 |
placecode text, |
|
| 2703 |
placedescription text, |
|
| 2704 |
accessioncode text |
|
| 2697 |
CREATE TABLE placecorrelation ( |
|
| 2698 |
placecorrelation_id integer NOT NULL, |
|
| 2699 |
parentplace_id integer NOT NULL, |
|
| 2700 |
childplace_id integer NOT NULL, |
|
| 2701 |
placeconvergence text NOT NULL |
|
| 2705 | 2702 |
); |
| 2706 | 2703 |
|
| 2707 | 2704 |
|
| 2708 | 2705 |
-- |
| 2709 |
-- Name: TABLE place; Type: COMMENT; Schema: public; Owner: -
|
|
| 2706 |
-- Name: placecorrelation_placecorrelation_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
|
| 2710 | 2707 |
-- |
| 2711 | 2708 |
|
| 2712 |
COMMENT ON TABLE place IS 'An official, named place. Can be at any level in the geographic hierarchy. Note that the placename stores only one rank (e.g. country) of the full path to the place. The higher-level ranks are stored in the place''s chain of parent_id ancestors. |
|
| 2709 |
CREATE SEQUENCE placecorrelation_placecorrelation_id_seq |
|
| 2710 |
START WITH 1 |
|
| 2711 |
INCREMENT BY 1 |
|
| 2712 |
NO MINVALUE |
|
| 2713 |
NO MAXVALUE |
|
| 2714 |
CACHE 1; |
|
| 2713 | 2715 |
|
| 2714 |
Equivalent to VegBank''s namedPlace table.'; |
|
| 2715 | 2716 |
|
| 2716 |
|
|
| 2717 | 2717 |
-- |
| 2718 |
-- Name: place_ancestor; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 2718 |
-- Name: placecorrelation_placecorrelation_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
|
| 2719 | 2719 |
-- |
| 2720 | 2720 |
|
| 2721 |
CREATE TABLE place_ancestor ( |
|
| 2722 |
place_id integer NOT NULL, |
|
| 2723 |
ancestor_id integer NOT NULL |
|
| 2724 |
); |
|
| 2721 |
ALTER SEQUENCE placecorrelation_placecorrelation_id_seq OWNED BY placecorrelation.placecorrelation_id; |
|
| 2725 | 2722 |
|
| 2726 | 2723 |
|
| 2727 | 2724 |
-- |
| 2728 |
-- Name: TABLE place_ancestor; Type: COMMENT; Schema: public; Owner: -
|
|
| 2725 |
-- Name: placename; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 2729 | 2726 |
-- |
| 2730 | 2727 |
|
| 2731 |
COMMENT ON TABLE place_ancestor IS 'place''s ancestor cross link table.'; |
|
| 2728 |
CREATE TABLE placename ( |
|
| 2729 |
placename_id integer NOT NULL, |
|
| 2730 |
parent_id integer, |
|
| 2731 |
rank placerank NOT NULL, |
|
| 2732 |
placename text NOT NULL, |
|
| 2733 |
placecode text, |
|
| 2734 |
placedescription text, |
|
| 2735 |
accessioncode text |
|
| 2736 |
); |
|
| 2732 | 2737 |
|
| 2733 | 2738 |
|
| 2734 | 2739 |
-- |
| 2735 |
-- Name: place_place_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
|
| 2740 |
-- Name: TABLE placename; Type: COMMENT; Schema: public; Owner: -
|
|
| 2736 | 2741 |
-- |
| 2737 | 2742 |
|
| 2738 |
CREATE SEQUENCE place_place_id_seq |
|
| 2739 |
START WITH 1 |
|
| 2740 |
INCREMENT BY 1 |
|
| 2741 |
NO MINVALUE |
|
| 2742 |
NO MAXVALUE |
|
| 2743 |
CACHE 1; |
|
| 2743 |
COMMENT ON TABLE placename IS 'An official, named placename. Can be at any level in the geographic hierarchy. Note that the placename stores only one rank (e.g. country) of the full path to the placename. The higher-level ranks are stored in the placename''s chain of parent_id ancestors. |
|
| 2744 | 2744 |
|
| 2745 |
Equivalent to VegBank''s namedPlace table.'; |
|
| 2745 | 2746 |
|
| 2747 |
|
|
| 2746 | 2748 |
-- |
| 2747 |
-- Name: place_place_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
|
| 2749 |
-- Name: placename_ancestor; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 2748 | 2750 |
-- |
| 2749 | 2751 |
|
| 2750 |
ALTER SEQUENCE place_place_id_seq OWNED BY place.place_id; |
|
| 2752 |
CREATE TABLE placename_ancestor ( |
|
| 2753 |
placename_id integer NOT NULL, |
|
| 2754 |
ancestor_id integer NOT NULL |
|
| 2755 |
); |
|
| 2751 | 2756 |
|
| 2752 | 2757 |
|
| 2753 | 2758 |
-- |
| 2754 |
-- Name: placecorrelation; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 2759 |
-- Name: TABLE placename_ancestor; Type: COMMENT; Schema: public; Owner: -
|
|
| 2755 | 2760 |
-- |
| 2756 | 2761 |
|
| 2757 |
CREATE TABLE placecorrelation ( |
|
| 2758 |
placecorrelation_id integer NOT NULL, |
|
| 2759 |
parentplace_id integer NOT NULL, |
|
| 2760 |
childplace_id integer NOT NULL, |
|
| 2761 |
placeconvergence text NOT NULL |
|
| 2762 |
); |
|
| 2762 |
COMMENT ON TABLE placename_ancestor IS 'placename''s ancestor cross link table.'; |
|
| 2763 | 2763 |
|
| 2764 | 2764 |
|
| 2765 | 2765 |
-- |
| 2766 |
-- Name: placecorrelation_placecorrelation_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
|
| 2766 |
-- Name: placename_placename_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
|
| 2767 | 2767 |
-- |
| 2768 | 2768 |
|
| 2769 |
CREATE SEQUENCE placecorrelation_placecorrelation_id_seq
|
|
| 2769 |
CREATE SEQUENCE placename_placename_id_seq
|
|
| 2770 | 2770 |
START WITH 1 |
| 2771 | 2771 |
INCREMENT BY 1 |
| 2772 | 2772 |
NO MINVALUE |
| ... | ... | |
| 2775 | 2775 |
|
| 2776 | 2776 |
|
| 2777 | 2777 |
-- |
| 2778 |
-- Name: placecorrelation_placecorrelation_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
|
| 2778 |
-- Name: placename_placename_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
|
| 2779 | 2779 |
-- |
| 2780 | 2780 |
|
| 2781 |
ALTER SEQUENCE placecorrelation_placecorrelation_id_seq OWNED BY placecorrelation.placecorrelation_id;
|
|
| 2781 |
ALTER SEQUENCE placename_placename_id_seq OWNED BY placename.placename_id;
|
|
| 2782 | 2782 |
|
| 2783 | 2783 |
|
| 2784 | 2784 |
-- |
| ... | ... | |
| 3948 | 3948 |
|
| 3949 | 3949 |
|
| 3950 | 3950 |
-- |
| 3951 |
-- Name: place_id; Type: DEFAULT; Schema: public; Owner: - |
|
| 3951 |
-- Name: placecorrelation_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3952 | 3952 |
-- |
| 3953 | 3953 |
|
| 3954 |
ALTER TABLE place ALTER COLUMN place_id SET DEFAULT nextval('place_place_id_seq'::regclass);
|
|
| 3954 |
ALTER TABLE placecorrelation ALTER COLUMN placecorrelation_id SET DEFAULT nextval('placecorrelation_placecorrelation_id_seq'::regclass);
|
|
| 3955 | 3955 |
|
| 3956 | 3956 |
|
| 3957 | 3957 |
-- |
| 3958 |
-- Name: placecorrelation_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3958 |
-- Name: placename_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3959 | 3959 |
-- |
| 3960 | 3960 |
|
| 3961 |
ALTER TABLE placecorrelation ALTER COLUMN placecorrelation_id SET DEFAULT nextval('placecorrelation_placecorrelation_id_seq'::regclass);
|
|
| 3961 |
ALTER TABLE placename ALTER COLUMN placename_id SET DEFAULT nextval('placename_placename_id_seq'::regclass);
|
|
| 3962 | 3962 |
|
| 3963 | 3963 |
|
| 3964 | 3964 |
-- |
| ... | ... | |
| 4437 | 4437 |
|
| 4438 | 4438 |
|
| 4439 | 4439 |
-- |
| 4440 |
-- Name: place_ancestor_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
| 4440 |
-- Name: placecorrelation_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
| 4441 | 4441 |
-- |
| 4442 | 4442 |
|
| 4443 |
ALTER TABLE ONLY place_ancestor
|
|
| 4444 |
ADD CONSTRAINT place_ancestor_pkey PRIMARY KEY (place_id, ancestor_id);
|
|
| 4443 |
ALTER TABLE ONLY placecorrelation
|
|
| 4444 |
ADD CONSTRAINT placecorrelation_pkey PRIMARY KEY (placecorrelation_id);
|
|
| 4445 | 4445 |
|
| 4446 | 4446 |
|
| 4447 | 4447 |
-- |
| 4448 |
-- Name: place_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 4448 |
-- Name: placename_ancestor_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
| 4449 | 4449 |
-- |
| 4450 | 4450 |
|
| 4451 |
ALTER TABLE ONLY place |
|
| 4452 |
ADD CONSTRAINT place_pkey PRIMARY KEY (place_id);
|
|
| 4451 |
ALTER TABLE ONLY placename_ancestor
|
|
| 4452 |
ADD CONSTRAINT placename_ancestor_pkey PRIMARY KEY (placename_id, ancestor_id);
|
|
| 4453 | 4453 |
|
| 4454 | 4454 |
|
| 4455 | 4455 |
-- |
| 4456 |
-- Name: placecorrelation_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
| 4456 |
-- Name: placename_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
| 4457 | 4457 |
-- |
| 4458 | 4458 |
|
| 4459 |
ALTER TABLE ONLY placecorrelation
|
|
| 4460 |
ADD CONSTRAINT placecorrelation_pkey PRIMARY KEY (placecorrelation_id);
|
|
| 4459 |
ALTER TABLE ONLY placename
|
|
| 4460 |
ADD CONSTRAINT placename_pkey PRIMARY KEY (placename_id);
|
|
| 4461 | 4461 |
|
| 4462 | 4462 |
|
| 4463 | 4463 |
-- |
| ... | ... | |
| 4974 | 4974 |
|
| 4975 | 4975 |
|
| 4976 | 4976 |
-- |
| 4977 |
-- Name: place_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4977 |
-- Name: placename_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4978 | 4978 |
-- |
| 4979 | 4979 |
|
| 4980 |
CREATE UNIQUE INDEX place_accessioncode_index ON place USING btree (accessioncode);
|
|
| 4980 |
CREATE UNIQUE INDEX placename_accessioncode_index ON placename USING btree (accessioncode);
|
|
| 4981 | 4981 |
|
| 4982 | 4982 |
|
| 4983 | 4983 |
-- |
| 4984 |
-- Name: place_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4984 |
-- Name: placename_unique; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4985 | 4985 |
-- |
| 4986 | 4986 |
|
| 4987 |
CREATE UNIQUE INDEX place_unique ON place USING btree ((COALESCE(parent_id, 2147483647)), placename, rank);
|
|
| 4987 |
CREATE UNIQUE INDEX placename_unique ON placename USING btree ((COALESCE(parent_id, 2147483647)), placename, rank);
|
|
| 4988 | 4988 |
|
| 4989 | 4989 |
|
| 4990 | 4990 |
-- |
| ... | ... | |
| 5226 | 5226 |
|
| 5227 | 5227 |
|
| 5228 | 5228 |
-- |
| 5229 |
-- Name: place_update_ancestors; Type: TRIGGER; Schema: public; Owner: - |
|
| 5229 |
-- Name: placename_update_ancestors; Type: TRIGGER; Schema: public; Owner: -
|
|
| 5230 | 5230 |
-- |
| 5231 | 5231 |
|
| 5232 |
CREATE CONSTRAINT TRIGGER place_update_ancestors AFTER INSERT OR UPDATE ON place DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE place_update_ancestors();
|
|
| 5232 |
CREATE CONSTRAINT TRIGGER placename_update_ancestors AFTER INSERT OR UPDATE ON placename DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE placename_update_ancestors();
|
|
| 5233 | 5233 |
|
| 5234 | 5234 |
|
| 5235 | 5235 |
-- |
| ... | ... | |
| 5816 | 5816 |
|
| 5817 | 5817 |
|
| 5818 | 5818 |
-- |
| 5819 |
-- Name: place_ancestor_ancestor_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5819 |
-- Name: placecorrelation_childplace_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5820 | 5820 |
-- |
| 5821 | 5821 |
|
| 5822 |
ALTER TABLE ONLY place_ancestor
|
|
| 5823 |
ADD CONSTRAINT place_ancestor_ancestor_id_fkey FOREIGN KEY (ancestor_id) REFERENCES place(place_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5822 |
ALTER TABLE ONLY placecorrelation
|
|
| 5823 |
ADD CONSTRAINT placecorrelation_childplace_id_fkey FOREIGN KEY (childplace_id) REFERENCES placename(placename_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5824 | 5824 |
|
| 5825 | 5825 |
|
| 5826 | 5826 |
-- |
| 5827 |
-- Name: place_ancestor_place_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5827 |
-- Name: placecorrelation_parentplace_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5828 | 5828 |
-- |
| 5829 | 5829 |
|
| 5830 |
ALTER TABLE ONLY place_ancestor
|
|
| 5831 |
ADD CONSTRAINT place_ancestor_place_id_fkey FOREIGN KEY (place_id) REFERENCES place(place_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5830 |
ALTER TABLE ONLY placecorrelation
|
|
| 5831 |
ADD CONSTRAINT placecorrelation_parentplace_id_fkey FOREIGN KEY (parentplace_id) REFERENCES placename(placename_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5832 | 5832 |
|
| 5833 | 5833 |
|
| 5834 | 5834 |
-- |
| 5835 |
-- Name: place_parent_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5835 |
-- Name: placename_ancestor_ancestor_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5836 | 5836 |
-- |
| 5837 | 5837 |
|
| 5838 |
ALTER TABLE ONLY place |
|
| 5839 |
ADD CONSTRAINT place_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES place(place_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5838 |
ALTER TABLE ONLY placename_ancestor
|
|
| 5839 |
ADD CONSTRAINT placename_ancestor_ancestor_id_fkey FOREIGN KEY (ancestor_id) REFERENCES placename(placename_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5840 | 5840 |
|
| 5841 | 5841 |
|
| 5842 | 5842 |
-- |
| 5843 |
-- Name: placecorrelation_childplace_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5843 |
-- Name: placename_ancestor_place_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5844 | 5844 |
-- |
| 5845 | 5845 |
|
| 5846 |
ALTER TABLE ONLY placecorrelation
|
|
| 5847 |
ADD CONSTRAINT placecorrelation_childplace_id_fkey FOREIGN KEY (childplace_id) REFERENCES place(place_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5846 |
ALTER TABLE ONLY placename_ancestor
|
|
| 5847 |
ADD CONSTRAINT placename_ancestor_place_id_fkey FOREIGN KEY (placename_id) REFERENCES placename(placename_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5848 | 5848 |
|
| 5849 | 5849 |
|
| 5850 | 5850 |
-- |
| 5851 |
-- Name: placecorrelation_parentplace_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5851 |
-- Name: placename_parent_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5852 | 5852 |
-- |
| 5853 | 5853 |
|
| 5854 |
ALTER TABLE ONLY placecorrelation
|
|
| 5855 |
ADD CONSTRAINT placecorrelation_parentplace_id_fkey FOREIGN KEY (parentplace_id) REFERENCES place(place_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5854 |
ALTER TABLE ONLY placename
|
|
| 5855 |
ADD CONSTRAINT placename_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES placename(placename_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5856 | 5856 |
|
| 5857 | 5857 |
|
| 5858 | 5858 |
-- |
| ... | ... | |
| 5876 | 5876 |
-- |
| 5877 | 5877 |
|
| 5878 | 5878 |
ALTER TABLE ONLY placepath |
| 5879 |
ADD CONSTRAINT placepath_place_id_fkey FOREIGN KEY (place_id) REFERENCES place(place_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5879 |
ADD CONSTRAINT placepath_place_id_fkey FOREIGN KEY (placename_id) REFERENCES placename(placename_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5880 | 5880 |
|
| 5881 | 5881 |
|
| 5882 | 5882 |
-- |
Also available in: Unified diff
schemas/vegbien.sql: Renamed place to placename since it refers just to a name for a place, without coordinates