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

--

CREATE FUNCTION taxon_update_ancestors() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

    -- Delete existing ancestors

    DELETE FROM taxon_ancestor WHERE taxon_id = new.taxon_id;

    IF new.parent_id IS NOT NULL THEN

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

        INSERT

        INTO taxon_ancestor

        (taxon_id, ancestor_id)

        SELECT

            new.taxon_id, ancestor_id

        FROM taxon_ancestor

        WHERE taxon_id = new.parent_id

        ;

    END IF;

    -- Add "ancestor" for this node

    /* This is useful for queries, because you don't have to separately test if

    the leaf node is the one you're looking for, in addition to that leaf node's

    ancestors. */

    INSERT

    INTO taxon_ancestor

    (taxon_id, ancestor_id)

    VALUES (new.taxon_id, new.taxon_id)

    ;

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

    -- recursively tell all descendants

    UPDATE taxon

    SET taxon_id = taxon_id -- need at least one SET statement

    -- Add COALESCE() to enable using taxon_unique index for lookup

    WHERE COALESCE(parent_id, 2147483647) = new.taxon_id

    ;

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

    ancestors when a node is deleted, because the taxon.taxon_parent_id

    foreign key is set to ON DELETE CASCADE, which just removes all the

    descendants anyway. */

    RETURN new;

END;

$$;

--

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

--

CREATE TABLE taxon (

    taxon_id integer NOT NULL,

    parent_id integer,

    rank taxonrank NOT NULL,

    verbatimrank text,

    taxonname text NOT NULL,

    author_id integer,

    authority_id integer,

    description text,

    accessioncode text

);

--

-- Name: TABLE taxon; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON TABLE taxon IS 'A formal, named taxon in the tree of life. Can be at any level in the taxonomic hierarchy. Note that the taxon name stores only one rank (e.g. family) of the full taxonomic name. The higher-level ranks are stored in the taxon''s chain of parent_id ancestors.

Equivalent to VegBank''s plantConcept and plantName tables, plus plantParent_ID and

plantLevel from plantStatus.';

--

-- Name: COLUMN taxon.parent_id; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxon.parent_id IS 'The parent taxon.';

--

-- Name: COLUMN taxon.rank; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxon.rank IS 'The taxon''s level in the taxonomic hierarchy, standardized to a closed list. Even if you specify a custom verbatimrank, you must also specify a closest-match rank from the taxonrank closed list.';

--

-- Name: COLUMN taxon.verbatimrank; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxon.verbatimrank IS 'The taxon''s verbatim level in the taxonomic hierarchy. Does not need to be in the taxonrank closed list.';

--

-- Name: COLUMN taxon.taxonname; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxon.taxonname IS 'The name of the taxon within its parent taxon. Contains only the lowest-rank portion of the taxonomic name. (The higher-level ranks are stored in the taxon''s chain of parent_id ancestors.)';

--

-- Name: COLUMN taxon.author_id; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxon.author_id IS 'The author of the taxon name.';

--

-- Name: COLUMN taxon.authority_id; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxon.authority_id IS 'The authority which defines the taxon name.';

--

-- Name: COLUMN taxon.description; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxon.description IS 'The authority''s description of the taxon.';

--

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

--

CREATE TABLE taxon_ancestor (

    taxon_id integer NOT NULL,

    ancestor_id integer NOT NULL

);

--

-- Name: taxon_taxon_id_seq; Type: SEQUENCE; Schema: public; Owner: -

--

CREATE SEQUENCE taxon_taxon_id_seq

    START WITH 1

    INCREMENT BY 1

    NO MINVALUE

    NO MAXVALUE

    CACHE 1;

--

-- Name: taxon_taxon_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

--

ALTER SEQUENCE taxon_taxon_id_seq OWNED BY taxon.taxon_id;

--

-- Name: taxon_id; Type: DEFAULT; Schema: public; Owner: -

--

ALTER TABLE taxon ALTER COLUMN taxon_id SET DEFAULT nextval('taxon_taxon_id_seq'::regclass);

--

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

--

ALTER TABLE ONLY taxon_ancestor

    ADD CONSTRAINT taxon_ancestor_pkey PRIMARY KEY (taxon_id, ancestor_id);

--

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

--

ALTER TABLE ONLY taxon

    ADD CONSTRAINT taxon_pkey PRIMARY KEY (taxon_id);

--

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

--

CREATE UNIQUE INDEX taxon_unique ON taxon USING btree ((COALESCE(parent_id, 2147483647)), taxonname, rank, (COALESCE(author_id, 2147483647)), (COALESCE(authority_id, 2147483647)));

--

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

--

CREATE TRIGGER taxon_update_ancestors AFTER INSERT OR UPDATE ON taxon FOR EACH ROW EXECUTE PROCEDURE taxon_update_ancestors();

--

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

--

ALTER TABLE ONLY taxon_ancestor

    ADD CONSTRAINT taxon_ancestor_ancestor_id_fkey FOREIGN KEY (ancestor_id) REFERENCES taxon(taxon_id) ON UPDATE CASCADE ON DELETE CASCADE;

--

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

--

ALTER TABLE ONLY taxon_ancestor

    ADD CONSTRAINT taxon_ancestor_taxon_id_fkey FOREIGN KEY (taxon_id) REFERENCES taxon(taxon_id) ON UPDATE CASCADE ON DELETE CASCADE;

--

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

--

ALTER TABLE ONLY taxon

    ADD CONSTRAINT taxon_author_id_fkey FOREIGN KEY (author_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;

--

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

--

ALTER TABLE ONLY taxon

    ADD CONSTRAINT taxon_authority_id_fkey FOREIGN KEY (authority_id) REFERENCES reference(reference_id) ON UPDATE CASCADE ON DELETE CASCADE;

--

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

--

ALTER TABLE ONLY taxon

    ADD CONSTRAINT taxon_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES taxon(taxon_id) ON UPDATE CASCADE ON DELETE CASCADE;

--