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

CREATE TABLE taxonconcept (

    taxonconcept_id int(11) NOT NULL,

    creator_id int(11) NOT NULL,

    sourceaccessioncode text,

    creationdate date,

    accepted_concept_id int(11),

    matched_concept_id int(11),

    matched_concept_fit_fraction double precision,

    parent_id int(11),

    taxonname text,

    rank text,

    verbatimrank text,

    identifyingtaxonomicname text,

    taxonomicname text,

    author text,

    taxonomicnamewithauthor text,

    family text,

    genus text,

    species text,

    description text,

    accessioncode text

);

--

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

--

-- Name: COLUMN taxonconcept.creator_id; Type: COMMENT; Schema: public; Owner: -

--

-- Name: COLUMN taxonconcept.sourceaccessioncode; Type: COMMENT; Schema: public; Owner: -

--

-- Name: COLUMN taxonconcept.creationdate; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.accepted_concept_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.matched_concept_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.matched_concept_fit_fraction; Type: COMMENT; Schema: public; Owner: -

--

--

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

--

--

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

--

--

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

--

--

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

--

--

-- Name: COLUMN taxonconcept.identifyingtaxonomicname; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.taxonomicname; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.author; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.taxonomicnamewithauthor; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.family; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.genus; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonconcept.species; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: taxonconcept_update_ancestors(taxonconcept, int(11)); Type: FUNCTION; Schema: public; Owner: -

--

--

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

--

--

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

--

--

-- Name: concat(text); Type: AGGREGATE; Schema: public; Owner: -

--

--

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

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

--

--

SET default_tablespace = '';

SET default_with_oids = false;

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

CREATE TABLE taxonconcept (

    taxonconcept_id integer NOT NULL,

    creator_id integer NOT NULL,

    sourceaccessioncode text,

    creationdate date,

    accepted_concept_id integer,

    matched_concept_id integer,

    matched_concept_fit_fraction double precision,

    parent_id integer,

    taxonname text,

    rank taxonrank,

    verbatimrank text,

    identifyingtaxonomicname text,

    taxonomicname text,

    author text,

    taxonomicnamewithauthor text,

    family text,

    genus text,

    species text,

    description text,

    accessioncode text,

    CONSTRAINT taxonconcept_matched_concept_fit_fraction_range CHECK (((matched_concept_fit_fraction >= (0)::double precision) AND (matched_concept_fit_fraction <= (1)::double precision))),

    CONSTRAINT taxonconcept_required_key CHECK (((((((sourceaccessioncode IS NOT NULL) OR (taxonname IS NOT NULL)) OR (identifyingtaxonomicname IS NOT NULL)) OR (taxonomicname IS NOT NULL)) OR (taxonomicnamewithauthor IS NOT NULL)) OR ((parent_id IS NOT NULL) AND ((creationdate IS NOT NULL) OR (author IS NOT NULL)))))

);

--

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

--

COMMENT ON TABLE taxonconcept IS 'A taxon concept defined by an entity. Can be at any level in the taxonomic hierarchy. Can be either verbatim or accepted.

"A taxon (plural: taxa) is a group of one (or more) populations of organism(s), which a taxonomist adjudges to be a unit" (http://en.wikipedia.org/wiki/Taxon)

Note that taxonname stores only one rank (e.g. family) of the full taxonomic name. The higher-level ranks are stored in the taxon concept''s chain of parent_id ancestors.

To include a taxon name at a rank with no explicit column, create a parent taxonconcept for it and point to it using parent_id. To include multiple such names, chain the taxonconcepts together using parent_id. Note that lower-level taxa should point to higher-level taxa.

Equivalent to VegBank''s plantConcept and plantName tables, plus plantParent_ID and plantLevel from plantStatus.';

--

-- Name: COLUMN taxonconcept.creator_id; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonconcept.creator_id IS 'The entity that created the taxon concept.';

--

-- Name: COLUMN taxonconcept.sourceaccessioncode; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonconcept.sourceaccessioncode IS 'The datasource''s identifier for the taxonconcept.';

--

-- Name: COLUMN taxonconcept.creationdate; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonconcept.creationdate IS 'The date the taxonconcept was created or defined. For a taxonconcept applied in a taxondetermination, this is the date the determination was made.';

--

-- Name: COLUMN taxonconcept.accepted_concept_id; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonconcept.accepted_concept_id IS 'The accepted synonym of the taxonconcept.';

--

-- Name: COLUMN taxonconcept.matched_concept_id; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonconcept.matched_concept_id IS 'The taxonconcept containing the closest match to this taxonconcept. taxonconcepts should be linked in a four-level hierarchy of datasource concept -> parsed concept -> matched concept -> accepted concept. A previously-accepted name''s concept should be further linked to the synonym that has replaced it.

To indicate a synonym between taxonconcepts of different sources, choose one taxonconcept to be authoritative and point the other taxonconcept to it using this field.

An accepted name should point to itself in this field. This will happen automatically by setting it to the special value 0.';

--

-- Name: COLUMN taxonconcept.matched_concept_fit_fraction; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonconcept.matched_concept_fit_fraction IS 'The closeness of fit of the matched_concept.';

--

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

--

COMMENT ON COLUMN taxonconcept.parent_id IS 'The parent taxonconcept. Note that while a taxon *name* may have multiple parents, a taxon *concept* has only one, based on the creator''s opinion of where that taxonconcept goes in the taxonomic hierarchy.';

--

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

--

COMMENT ON COLUMN taxonconcept.taxonname IS 'The name of the taxon within its parent taxon. This is the lowest-rank portion of this taxonconcept''s full taxonomic name, if it has one.

The morphospecies suffix goes in this field.';

--

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

--

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

--

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

--

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

--

-- Name: COLUMN taxonconcept.identifyingtaxonomicname; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonconcept.identifyingtaxonomicname IS 'The taxonomic name that uniquely identifies this taxonconcept. If set, the other fields will not be used in duplicate elimination.';

--

-- Name: COLUMN taxonconcept.taxonomicname; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonconcept.taxonomicname IS 'The concatenated taxonomic name which uniquely identifies this taxon. Does not include the author of the name.

Equivalent to Darwin Core''s scientificName.';

--

-- Name: COLUMN taxonconcept.author; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonconcept.author IS 'The author of the taxonomic name.

Equivalent to Darwin Core''s scientificNameAuthorship.';

--

-- Name: COLUMN taxonconcept.taxonomicnamewithauthor; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonconcept.taxonomicnamewithauthor IS 'The concatenated taxonomic name which uniquely identifies this taxon, plus the author of that name.

Equivalent to Darwin Core''s scientificName + scientificNameAuthorship.

Equivalent to "Name sec. x".';

--

-- Name: COLUMN taxonconcept.family; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonconcept.family IS 'The family of the taxonomic name. This is a cached field for easy querying; the identifying version of this field is stored in the chain of parent_id ancestors.';

--

-- Name: COLUMN taxonconcept.genus; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonconcept.genus IS 'The genus portion of the taxonomic name. This is a cached field for easy querying; the identifying version of this field is stored in the chain of parent_id ancestors.';

--

-- Name: COLUMN taxonconcept.species; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonconcept.species IS 'The species portion of the taxonomic name. This is a cached field for easy querying; the identifying version of this field is stored in the chain of parent_id ancestors.';

--

-- Name: taxonconcept_update_ancestors(taxonconcept, integer); Type: FUNCTION; Schema: public; Owner: -

--

CREATE FUNCTION taxonconcept_update_ancestors(new taxonconcept, old_parent_id integer DEFAULT NULL::integer) RETURNS void

    IF parent_id_ IS DISTINCT FROM old_parent_id THEN

        DECLARE

            -- These include the parent itself

            old_ancestors integer[] := (

                SELECT array_agg(ancestor_id) FROM taxonconcept_ancestor

                WHERE taxonconcept_id = old_parent_id

            );

            new_ancestors integer[] := (

                SELECT array_agg(ancestor_id) FROM taxonconcept_ancestor

                WHERE taxonconcept_id = parent_id_

            );

            descendant_id integer;

        BEGIN

            FOR descendant_id IN -- also includes self

                SELECT taxonconcept_id

                FROM taxonconcept_ancestor

                WHERE ancestor_id = new.taxonconcept_id

            LOOP

                -- Delete old parent's ancestors

                DELETE FROM taxonconcept_ancestor

                WHERE taxonconcept_id = descendant_id

                AND ancestor_id = ANY (old_ancestors)

                ;

                -- Add new parent's ancestors

                INSERT INTO taxonconcept_ancestor

                (taxonconcept_id, ancestor_id)

                SELECT descendant_id, unnest(new_ancestors)

                ;

            END LOOP;

        END;

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

    ancestors when a node is deleted, because the taxonconcept.parent_id foreign

    key is ON DELETE CASCADE, which just removes all the descendants anyway. */

END;

$$;

--

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

--

CREATE FUNCTION taxonconcept_update_ancestors_on_insert() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

    /* 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. */

    PERFORM taxonconcept_update_ancestors(new);

    RETURN new;

END;

$$;

--

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

--

CREATE FUNCTION taxonconcept_update_ancestors_on_update() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

    PERFORM taxonconcept_update_ancestors(new,

        COALESCE(old.matched_concept_id, old.parent_id));

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

CREATE TRIGGER taxonconcept_update_ancestors_on_insert AFTER INSERT ON taxonconcept FOR EACH ROW EXECUTE PROCEDURE taxonconcept_update_ancestors_on_insert();

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

--

CREATE TRIGGER taxonconcept_update_ancestors_on_update AFTER UPDATE ON taxonconcept FOR EACH ROW EXECUTE PROCEDURE taxonconcept_update_ancestors_on_update();

--