-- Name: namedplace_update_ancestors(); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: plantname_update_ancestors(); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: namedplace_ancestor; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE namedplace_ancestor (

    namedplace_id int(11) NOT NULL,

    ancestor_id int(11) NOT NULL

);

--

-- Name: plantname_ancestor; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE plantname_ancestor (

    plantname_id int(11) NOT NULL,

    ancestor_id int(11) NOT NULL

);

--

-- Name: namedplace_ancestor_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 

--

ALTER TABLE namedplace_ancestor

    ADD CONSTRAINT namedplace_ancestor_pkey PRIMARY KEY (namedplace_id, ancestor_id);

--

-- Name: plantname_ancestor_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 

--

ALTER TABLE plantname_ancestor

    ADD CONSTRAINT plantname_ancestor_pkey PRIMARY KEY (plantname_id, ancestor_id);

--

-- Name: fki_namedplace_ancestor_ancestor_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 

--

CREATE INDEX fki_namedplace_ancestor_ancestor_id ON namedplace_ancestor  (ancestor_id);

--

-- Name: fki_namedplace_ancestor_namedplace_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 

--

CREATE INDEX fki_namedplace_ancestor_namedplace_id ON namedplace_ancestor  (namedplace_id);

--

-- Name: fki_plantname_ancestor_ancestor_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 

--

CREATE INDEX fki_plantname_ancestor_ancestor_id ON plantname_ancestor  (ancestor_id);

--

-- Name: fki_plantname_ancestor_plantname_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 

--

CREATE INDEX fki_plantname_ancestor_plantname_id ON plantname_ancestor  (plantname_id);

--

-- Name: namedplace_update_ancestors; Type: TRIGGER; Schema: public; Owner: -

--

--

-- Name: plantname_update_ancestors; Type: TRIGGER; Schema: public; Owner: -

--

--

-- Name: namedplace_ancestor_ancestor_id; Type: FK CONSTRAINT; Schema: public; Owner: -

--

ALTER TABLE namedplace_ancestor

    ADD CONSTRAINT namedplace_ancestor_ancestor_id FOREIGN KEY (ancestor_id) REFERENCES namedplace(namedplace_id) ON UPDATE CASCADE ON DELETE CASCADE;

--

-- Name: namedplace_ancestor_namedplace_id; Type: FK CONSTRAINT; Schema: public; Owner: -

--

ALTER TABLE namedplace_ancestor

    ADD CONSTRAINT namedplace_ancestor_namedplace_id FOREIGN KEY (namedplace_id) REFERENCES namedplace(namedplace_id) ON UPDATE CASCADE ON DELETE CASCADE;

--

-- Name: plantname_ancestor_ancestor_id; Type: FK CONSTRAINT; Schema: public; Owner: -

--

ALTER TABLE plantname_ancestor

    ADD CONSTRAINT plantname_ancestor_ancestor_id FOREIGN KEY (ancestor_id) REFERENCES plantname(plantname_id) ON UPDATE CASCADE ON DELETE CASCADE;

--

-- Name: plantname_ancestor_plantname_id; Type: FK CONSTRAINT; Schema: public; Owner: -

--

ALTER TABLE plantname_ancestor

    ADD CONSTRAINT plantname_ancestor_plantname_id FOREIGN KEY (plantname_id) REFERENCES plantname(plantname_id) ON UPDATE CASCADE ON DELETE CASCADE;

--

TRUNCATE namedplace_ancestor CASCADE;

TRUNCATE plantname_ancestor CASCADE;

-- Name: namedplace_update_ancestors(); Type: FUNCTION; Schema: public; Owner: -

--

CREATE FUNCTION namedplace_update_ancestors() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

    IF new.parent_id IS NOT NULL THEN

        -- Delete existing ancestors

        DELETE FROM namedplace_ancestor WHERE namedplace_id = new.namedplace_id;

        -- Copy parent's ancestors to this node's ancestors

        INSERT

        INTO namedplace_ancestor

        (namedplace_id, ancestor_id)

        SELECT

            new.namedplace_id, ancestor_id

        FROM namedplace_ancestor

        WHERE namedplace_id = new.parent_id

        ;

        -- Add ancestor for immediate parent

        INSERT

        INTO namedplace_ancestor

        (namedplace_id, ancestor_id)

        VALUES (new.namedplace_id, new.parent_id)

        ;

        -- Tell immediate children to update their ancestors lists, which will

        -- recursively tell all descendants

        UPDATE namedplace

        SET namedplace_id = namedplace.namedplace_id -- set to itself

        WHERE parent_id = namedplace_id -- the namedplace_id passed as param

        ;

        /*

        Note: We don't need an ON DELETE trigger to update the descendants'

        ancestors when a node is deleted, because the

        namedplace.namedplace_parent_id foreign key is set to ON DELETE CASCADE,

        which just removes all the descendants anyway.

        */

    END IF;

    RETURN new;

END;

$$;

--

-- Name: plantname_update_ancestors(); Type: FUNCTION; Schema: public; Owner: -

--

CREATE FUNCTION plantname_update_ancestors() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

    IF new.parent_id IS NOT NULL THEN

        -- Delete existing ancestors

        DELETE FROM plantname_ancestor WHERE plantname_id = new.plantname_id;

        -- Copy parent's ancestors to this node's ancestors

        INSERT

        INTO plantname_ancestor

        (plantname_id, ancestor_id)

        SELECT

            new.plantname_id, ancestor_id

        FROM plantname_ancestor

        WHERE plantname_id = new.parent_id

        ;

        -- Add ancestor for immediate parent

        INSERT

        INTO plantname_ancestor

        (plantname_id, ancestor_id)

        VALUES (new.plantname_id, new.parent_id)

        ;

        -- Tell immediate children to update their ancestors lists, which will

        -- recursively tell all descendants

        UPDATE plantname

        SET plantname_id = plantname.plantname_id -- set to itself

        WHERE parent_id = plantname_id -- the plantname_id passed as param

        ;

        /*

        Note: We don't need an ON DELETE trigger to update the descendants'

        ancestors when a node is deleted, because the

        plantname.plantname_parent_id foreign key is set to ON DELETE CASCADE,

        which just removes all the descendants anyway.

        */

    END IF;

    RETURN new;

END;

$$;

--

SET default_with_oids = false;

-- Name: namedplace_ancestor; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE namedplace_ancestor (

    namedplace_id integer NOT NULL,

    ancestor_id integer NOT NULL

);

--

SET default_with_oids = true;

SET default_with_oids = false;

-- Name: plantname_ancestor; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE plantname_ancestor (

    plantname_id integer NOT NULL,

    ancestor_id integer NOT NULL

);

--

-- Name: namedplace_ancestor_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 

--

ALTER TABLE ONLY namedplace_ancestor

    ADD CONSTRAINT namedplace_ancestor_pkey PRIMARY KEY (namedplace_id, ancestor_id);

--

-- Name: plantname_ancestor_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 

--

ALTER TABLE ONLY plantname_ancestor

    ADD CONSTRAINT plantname_ancestor_pkey PRIMARY KEY (plantname_id, ancestor_id);

--

-- Name: fki_namedplace_ancestor_ancestor_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 

--

CREATE INDEX fki_namedplace_ancestor_ancestor_id ON namedplace_ancestor USING btree (ancestor_id);

--

-- Name: fki_namedplace_ancestor_namedplace_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 

--

CREATE INDEX fki_namedplace_ancestor_namedplace_id ON namedplace_ancestor USING btree (namedplace_id);

--

-- Name: fki_plantname_ancestor_ancestor_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 

--

CREATE INDEX fki_plantname_ancestor_ancestor_id ON plantname_ancestor USING btree (ancestor_id);

--

-- Name: fki_plantname_ancestor_plantname_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 

--

CREATE INDEX fki_plantname_ancestor_plantname_id ON plantname_ancestor USING btree (plantname_id);

--

-- Name: namedplace_update_ancestors; Type: TRIGGER; Schema: public; Owner: -

--

CREATE TRIGGER namedplace_update_ancestors AFTER INSERT OR UPDATE ON namedplace FOR EACH ROW EXECUTE PROCEDURE namedplace_update_ancestors();

--

-- Name: plantname_update_ancestors; Type: TRIGGER; Schema: public; Owner: -

--

CREATE TRIGGER plantname_update_ancestors AFTER INSERT OR UPDATE ON plantname FOR EACH ROW EXECUTE PROCEDURE plantname_update_ancestors();

--

-- Name: namedplace_ancestor_ancestor_id; Type: FK CONSTRAINT; Schema: public; Owner: -

--

ALTER TABLE ONLY namedplace_ancestor

    ADD CONSTRAINT namedplace_ancestor_ancestor_id FOREIGN KEY (ancestor_id) REFERENCES namedplace(namedplace_id) ON UPDATE CASCADE ON DELETE CASCADE;

--

-- Name: namedplace_ancestor_namedplace_id; Type: FK CONSTRAINT; Schema: public; Owner: -

--

ALTER TABLE ONLY namedplace_ancestor

    ADD CONSTRAINT namedplace_ancestor_namedplace_id FOREIGN KEY (namedplace_id) REFERENCES namedplace(namedplace_id) ON UPDATE CASCADE ON DELETE CASCADE;

--

-- Name: plantname_ancestor_ancestor_id; Type: FK CONSTRAINT; Schema: public; Owner: -

--

ALTER TABLE ONLY plantname_ancestor

    ADD CONSTRAINT plantname_ancestor_ancestor_id FOREIGN KEY (ancestor_id) REFERENCES plantname(plantname_id) ON UPDATE CASCADE ON DELETE CASCADE;

--

-- Name: plantname_ancestor_plantname_id; Type: FK CONSTRAINT; Schema: public; Owner: -

--

ALTER TABLE ONLY plantname_ancestor

    ADD CONSTRAINT plantname_ancestor_plantname_id FOREIGN KEY (plantname_id) REFERENCES plantname(plantname_id) ON UPDATE CASCADE ON DELETE CASCADE;

--