# Import names

# Export results

# Don't overwrite TNRS.backup, which contains the full TNRS cache

make backups/TNRS.sql-remake

mv -f backups/TNRS.sql "$selfDir/_scrub"

make backups/public.sql-remake

mv -f backups/public.sql "$selfDir/_scrub"

--

-- PostgreSQL database dump

--

SET statement_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = off;

SET check_function_bodies = false;

SET client_min_messages = warning;

SET escape_string_warning = off;

--

-- Name: TNRS; Type: SCHEMA; Schema: -; Owner: bien

--

CREATE SCHEMA "TNRS";

ALTER SCHEMA "TNRS" OWNER TO bien;

SET search_path = "TNRS", pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--

-- Name: tnrs; Type: TABLE; Schema: TNRS; Owner: bien; Tablespace: 

--

CREATE TABLE tnrs (

    "Name_number" text,

    "Name_submitted" text NOT NULL,

    "Overall_score" text,

    "Name_matched" text,

    "Name_matched_rank" text,

    "Name_score" text,

    "Name_matched_author" text,

    "Name_matched_url" text,

    "Author_matched" text,

    "Author_score" text,

    "Family_matched" text,

    "Family_score" text,

    "Name_matched_accepted_family" text,

    "Genus_matched" text,

    "Genus_score" text,

    "Specific_epithet_matched" text,

    "Specific_epithet_score" text,

    "Infraspecific_rank" text,

    "Infraspecific_epithet_matched" text,

    "Infraspecific_epithet_score" text,

    "Infraspecific_rank_2" text,

    "Infraspecific_epithet_2_matched" text,

    "Infraspecific_epithet_2_score" text,

    "Annotations" text,

    "Unmatched_terms" text,

    "Taxonomic_status" text,

    "Accepted_name" text,

    "Accepted_name_author" text,

    "Accepted_name_rank" text,

    "Accepted_name_url" text,

    "Accepted_name_species" text,

    "Accepted_name_family" text,

    "Selected" text,

    "Source" text,

    "Warnings" text,

    "Accepted_name_lsid" text

);

ALTER TABLE "TNRS".tnrs OWNER TO bien;

--

-- Name: TABLE tnrs; Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON TABLE tnrs IS 'tnrs_accepted_names sorts accepted names first (note that false sorts before true). Accepted names are defined as names that scrub to themselves.';

--

-- Data for Name: tnrs; Type: TABLE DATA; Schema: TNRS; Owner: bien

--

COPY tnrs ("Name_number", "Name_submitted", "Overall_score", "Name_matched", "Name_matched_rank", "Name_score", "Name_matched_author", "Name_matched_url", "Author_matched", "Author_score", "Family_matched", "Family_score", "Name_matched_accepted_family", "Genus_matched", "Genus_score", "Specific_epithet_matched", "Specific_epithet_score", "Infraspecific_rank", "Infraspecific_epithet_matched", "Infraspecific_epithet_score", "Infraspecific_rank_2", "Infraspecific_epithet_2_matched", "Infraspecific_epithet_2_score", "Annotations", "Unmatched_terms", "Taxonomic_status", "Accepted_name", "Accepted_name_author", "Accepted_name_rank", "Accepted_name_url", "Accepted_name_species", "Accepted_name_family", "Selected", "Source", "Warnings", "Accepted_name_lsid") FROM stdin;

1	Poa annua L.	1	Poa annua	species	1	L.	http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN	L.	1	\N	\N	Poaceae	Poa	1	annua	1	\N	\N	\N	\N	\N	\N	\N	\N	Accepted	Poa annua	L.	species	http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN	Poa annua	Poaceae	true	tropicos;usda	\N	\N

1	Poa annua var. annua	1	Poa annua var. annua	variety	1	\N	http://www.tropicos.org/Name/25517736	\N	\N	\N	\N	Poaceae	Poa	1	annua	1	var.	annua	1	\N	\N	\N	\N	\N	Accepted	Poa annua var. annua	\N	variety	http://www.tropicos.org/Name/25517736	Poa annua	Poaceae	true	tropicos	\N	\N

2	Poa infirma Kunth	1	Poa infirma	species	1	Kunth	http://www.tropicos.org/Name/25514158;http://plants.usda.gov/java/profile?symbol=POIN30	Kunth	1	\N	\N	Poaceae	Poa	1	infirma	1	\N	\N	\N	\N	\N	\N	\N	\N	Accepted	Poa infirma	Kunth	species	http://www.tropicos.org/Name/25514158;http://plants.usda.gov/java/profile?symbol=POIN30	Poa infirma	Poaceae	true	tropicos;usda	\N	\N

0	Asteraceae Bercht. & J. Presl	0.4	Asteraceae	family	0.5	\N	http://compositae.landcareresearch.co.nz/default.aspx;http://plants.usda.gov/java/nameSearch	\N	\N	Asteraceae	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	Bercht. & J. Presl	Accepted	Asteraceae	\N	family	http://compositae.landcareresearch.co.nz/default.aspx;http://plants.usda.gov/java/nameSearch	\N	\N	true	gcc;usda	[Partial match]	\N

14	Compositae indet. sp.1	0.9	Compositae	family	1	Giseke	http://www.tropicos.org/Name/50255940	\N	\N	Compositae	1	Compositae	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	indet. sp.1	Synonym	Asteraceae	Bercht. & J. Presl	family	http://www.tropicos.org/Name/50307371	\N	Asteraceae	true	tropicos	\N	\N

12	Fabaceae Boyle#6500	0.9	Fabaceae	family	1	Lindl.	http://www.tropicos.org/Name/42000184	\N	\N	Fabaceae	1	Fabaceae	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	Boyle#6500	Accepted	Fabaceae	Lindl.	family	http://www.tropicos.org/Name/42000184	\N	Fabaceae	true	tropicos	\N	\N

10	Fabaceae Inga "fuzzy leaf"	0.9	Inga	genus	1	Mill.	http://www.tropicos.org/Name/40031040	\N	\N	Fabaceae	1	Fabaceae	Inga	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	"fuzzy leaf"	Accepted	Inga	Mill.	genus	http://www.tropicos.org/Name/40031040	\N	Fabaceae	true	tropicos	\N	\N

9	Fabaceae Inga sp.3	0.9	Inga	genus	1	Mill.	http://www.tropicos.org/Name/40031040	\N	\N	Fabaceae	1	Fabaceae	Inga	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	sp.3	Accepted	Inga	Mill.	genus	http://www.tropicos.org/Name/40031040	\N	Fabaceae	true	tropicos	\N	\N

11	Fabaceae unknown #2	0.9	Fabaceae	family	1	Lindl.	http://www.tropicos.org/Name/42000184	\N	\N	Fabaceae	1	Fabaceae	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	unknown #2	Accepted	Fabaceae	Lindl.	family	http://www.tropicos.org/Name/42000184	\N	Fabaceae	true	tropicos	\N	\N

0	Poa annua	1	Poa annua	species	1	L.	http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN	\N	\N	\N	\N	Poaceae	Poa	1	annua	1	\N	\N	\N	\N	\N	\N	\N	\N	Accepted	Poa annua	L.	species	http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN	Poa annua	Poaceae	true	tropicos;usda	\N	\N

6	Poa annua fo. lanuginosa	1	Poa annua fo. lanuginosa	forma	1	Sennen	http://www.tropicos.org/Name/50267771	\N	\N	\N	\N	Poaceae	Poa	1	annua	1	fo.	lanuginosa	1	\N	\N	\N	\N	\N	Synonym	Poa annua var. annua	\N	variety	http://www.tropicos.org/Name/25517736	Poa annua	Poaceae	true	tropicos	\N	\N

4	Poa annua ssp. exilis	1	Poa annua subsp. exilis	subspecies	1	(Tomm. ex Freyn) Asch. & Graebn.	http://www.tropicos.org/Name/50063800	\N	\N	\N	\N	Poaceae	Poa	1	annua	1	subsp.	exilis	1	\N	\N	\N	\N	\N	Synonym	Poa infirma	Kunth	species	http://www.tropicos.org/Name/25514158	Poa infirma	Poaceae	true	tropicos	\N	\N

3	Poa annua subsp. exilis	1	Poa annua subsp. exilis	subspecies	1	(Tomm. ex Freyn) Asch. & Graebn.	http://www.tropicos.org/Name/50063800	\N	\N	\N	\N	Poaceae	Poa	1	annua	1	subsp.	exilis	1	\N	\N	\N	\N	\N	Synonym	Poa infirma	Kunth	species	http://www.tropicos.org/Name/25514158	Poa infirma	Poaceae	true	tropicos	\N	\N

5	Poa annua subvar. minima	1	Poa annua subvar. minima	subvariety	1	(Schur) Asch. & Graebn.	http://www.tropicos.org/Name/50158097	\N	\N	\N	\N	Poaceae	Poa	1	annua	1	subvar.	minima	1	\N	\N	\N	\N	\N	Accepted	Poa annua subvar. minima	(Schur) Asch. & Graebn.	subvariety	http://www.tropicos.org/Name/50158097	Poa annua	Poaceae	true	tropicos	\N	\N

2	Poa annua var. eriolepis	1	Poa annua var. eriolepis	variety	1	E. Desv.	http://www.tropicos.org/Name/50119145	\N	\N	\N	\N	Poaceae	Poa	1	annua	1	var.	eriolepis	1	\N	\N	\N	\N	\N	Synonym	Poa annua	L.	species	http://www.tropicos.org/Name/25509881	Poa annua	Poaceae	true	tropicos	\N	\N

8	Silene scouleri Hook. subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea C.L. Hitchc. & Maguire	0.77	Silene scouleri subsp. pringlei	subspecies	0.77	(S. Watson) C.L. Hitchc. & Maguire	http://www.tropicos.org/Name/6303627	\N	\N	\N	\N	Caryophyllaceae	Silene	1	scouleri	1	subsp.	pringlei	1	\N	\N	\N	\N	var. grisea	Accepted	Silene scouleri subsp. pringlei	(S. Watson) C.L. Hitchc. & Maguire	subspecies	http://www.tropicos.org/Name/6303627	Silene scouleri	Caryophyllaceae	true	tropicos	[Partial match]	\N

7	Silene scouleri subsp. pringlei var. grisea C.L. Hitchc. & Maguire	0.77	Silene scouleri subsp. pringlei	subspecies	0.77	(S. Watson) C.L. Hitchc. & Maguire	http://www.tropicos.org/Name/6303627	\N	\N	\N	\N	Caryophyllaceae	Silene	1	scouleri	1	subsp.	pringlei	1	\N	\N	\N	\N	var. grisea	Accepted	Silene scouleri subsp. pringlei	(S. Watson) C.L. Hitchc. & Maguire	subspecies	http://www.tropicos.org/Name/6303627	Silene scouleri	Caryophyllaceae	true	tropicos	[Partial match]	\N

13	Fam_indet. Boyle#6501	0	No suitable matches found.	\N	0	\N	\N	\N	0	\N	0	\N	\N	0	\N	0	\N	\N	0	\N	\N	0	\N	\N	\N	\N	\N	\N	\N	\N	\N	true	\N	\N	\N

3	No suitable matches found.	0	No suitable matches found.	\N	0	\N	\N	\N	0	\N	0	\N	\N	0	\N	0	\N	\N	0	\N	\N	0	\N	\N	\N	\N	\N	\N	\N	\N	\N	true	\N	\N	\N

\.

--

-- Name: tnrs_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: bien; Tablespace: 

--

ALTER TABLE ONLY tnrs

    ADD CONSTRAINT tnrs_pkey PRIMARY KEY ("Name_submitted");

--

-- Name: tnrs_accepted_names; Type: INDEX; Schema: TNRS; Owner: bien; Tablespace: 

--

CREATE INDEX tnrs_accepted_names ON tnrs USING btree (((NOT ("Name_submitted" = ("Accepted_name" || COALESCE((' '::text || "Accepted_name_author"), ''::text))))), "Name_submitted");

ALTER TABLE tnrs CLUSTER ON tnrs_accepted_names;

--

-- PostgreSQL database dump complete

--

--

-- PostgreSQL database dump

--

SET statement_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = off;

SET check_function_bodies = false;

SET client_min_messages = warning;

SET escape_string_warning = off;

SET search_path = public, pg_catalog;

--

-- Name: establishmentmeans_dwc; Type: TYPE; Schema: public; Owner: bien

--

CREATE TYPE establishmentmeans_dwc AS ENUM (

    'cultivated',

    'wild',

    'native',

    'exotic',

    'invasive',

    'escaped from captivity'

);

ALTER TYPE public.establishmentmeans_dwc OWNER TO bien;

--

-- Name: TYPE establishmentmeans_dwc; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON TYPE establishmentmeans_dwc IS 'See http://rs.tdwg.org/dwc/terms/#establishmentMeans';

--

-- Name: growthform; Type: TYPE; Schema: public; Owner: bien

--

CREATE TYPE growthform AS ENUM (

    'tree',

    'shrub',

    'liana',

    'vine',

    'herb',

    'hemiepiphyte',

    'epiphyte',

    'grass',

    'forb',

    'moss',

    'lichen',

    'fungus',

    'floating aquatic',

    'submerged aquatic'

);

ALTER TYPE public.growthform OWNER TO bien;

--

-- Name: occurrencestatus_dwc; Type: TYPE; Schema: public; Owner: bien

--

CREATE TYPE occurrencestatus_dwc AS ENUM (

    'present',

    'absent',

    'common',

    'irregular',

    'rare',

    'doubtful'

);

ALTER TYPE public.occurrencestatus_dwc OWNER TO bien;

--

-- Name: placerank; Type: TYPE; Schema: public; Owner: bien

--

CREATE TYPE placerank AS ENUM (

    'continent',

    'country',

    'stateProvince',

    'county',

    'municipality',

    'village',

    'site',

    'territory',

    'region',

    'waterBody',

    'island',

    'islandGroup'

);

ALTER TYPE public.placerank OWNER TO bien;

--

-- Name: TYPE placerank; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON TYPE placerank IS 'county = parish, canton

municipality = city

';

--

-- Name: rankedplacename; Type: TYPE; Schema: public; Owner: bien

--

CREATE TYPE rankedplacename AS (

	rank placerank,

	verbatimrank text,

	placename text

);

ALTER TYPE public.rankedplacename OWNER TO bien;

--

-- Name: taxonrank; Type: TYPE; Schema: public; Owner: bien

--

CREATE TYPE taxonrank AS ENUM (

    'unknown',

    'suprageneric',

    'domain',

    'superkingdom',

    'kingdom',

    'subkingdom',

    'infrakingdom',

    'superphylum',

    'phylum',

    'subphylum',

    'infraphylum',

    'superclass',

    'class',

    'subclass',

    'infraclass',

    'superorder',

    'order',

    'suborder',

    'infraorder',

    'family group',

    'superfamily',

    'family',

    'subfamily',

    'infrafamily',

    'family subdivision',

    'supertribe',

    'tribe',

    'subtribe',

    'infratribe',

    'genus group',

    'genus',

    'subgenus',

    'infragenus',

    'genus subdivision',

    'section',

    'subsection',

    'series',

    'subseries',

    'infrageneric',

    'species aggregate',

    'species group',

    'species',

    'subspecies',

    'subspecific aggregate',

    'below subspecies',

    'infraspecies',

    'biovariety',

    'pathovariety',

    'variety',

    'subvariety',

    'subsubvariety',

    'forma',

    'subforma',

    'subsubforma',

    'special forma',

    'infraspecific',

    'candidate',

    'cultivated plants',

    'cultivar',

    'convar',

    'grex',

    'cultivar group',

    'graft-chimaera',

    'denomination class'

);

ALTER TYPE public.taxonrank OWNER TO bien;

--

-- Name: TYPE taxonrank; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON TYPE taxonrank IS 'From <http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1263&entity=dba_fielddescription&params=1263>';

--

-- Name: rankedtaxonname; Type: TYPE; Schema: public; Owner: bien

--

CREATE TYPE rankedtaxonname AS (

	rank taxonrank,

	verbatimrank text,

	taxonname text

);

ALTER TYPE public.rankedtaxonname OWNER TO bien;

--

-- Name: role; Type: TYPE; Schema: public; Owner: bien

--

CREATE TYPE role AS ENUM (

    'unknown',

    'collector',

    'identifier',

    'computer',

    'contributor'

);

ALTER TYPE public.role OWNER TO bien;

--

-- Name: taxonclass; Type: TYPE; Schema: public; Owner: bien

--

CREATE TYPE taxonclass AS ENUM (

    'tree',

    'shrub',

    'liana',

    'vine',

    'herb',

    'hemiepiphyte',

    'epiphyte',

    'grass',

    'forb',

    'moss',

    'lichen',

    'fungus',

    'floating aquatic',

    'submerged aquatic',

    'cultivated',

    'wild',

    'native',

    'exotic',

    'invasive',

    'escaped from captivity',

    'dominant',

    'vascular',

    'woody'

);

ALTER TYPE public.taxonclass OWNER TO bien;

--

-- Name: TYPE taxonclass; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON TYPE taxonclass IS '= growthform + establishmentmeans_dwc + some others';

--

-- Name: _set_canon_taxonconcept(integer, integer, double precision); Type: FUNCTION; Schema: public; Owner: bien

--

CREATE FUNCTION _set_canon_taxonconcept(taxonconcept_id integer, canon_concept_id integer, canon_concept_fit_fraction double precision DEFAULT NULL::double precision) RETURNS integer

    LANGUAGE sql

    AS $_$

UPDATE taxonconcept SET

  canon_concept_id = $2

, canon_concept_fit_fraction = $3

WHERE taxonconcept_id = $1

RETURNING taxonconcept_id

$_$;

ALTER FUNCTION public._set_canon_taxonconcept(taxonconcept_id integer, canon_concept_id integer, canon_concept_fit_fraction double precision) OWNER TO bien;

--

-- Name: make_analytical_db(); Type: FUNCTION; Schema: public; Owner: bien

--

CREATE FUNCTION make_analytical_db() RETURNS void

    LANGUAGE sql

    AS $$CREATE TABLE analytical_db AS SELECT * FROM analytical_db_view$$;

ALTER FUNCTION public.make_analytical_db() OWNER TO bien;

--

-- Name: party_creator_id_self_ref(); Type: FUNCTION; Schema: public; Owner: bien

--

CREATE FUNCTION party_creator_id_self_ref() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

    IF new.party_id IS NULL THEN -- prepopulate party_id

        new.party_id = nextval('party_party_id_seq'::regclass);

    END IF;

    IF new.creator_id = 0 THEN -- make self-reference

        new.creator_id = new.party_id;

    END IF;

    RETURN new;

END;

$$;

ALTER FUNCTION public.party_creator_id_self_ref() OWNER TO bien;

--

-- Name: place_update_ancestors(); Type: FUNCTION; Schema: public; Owner: bien

--

CREATE FUNCTION place_update_ancestors() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

    -- Delete existing ancestors

    DELETE FROM place_ancestor WHERE place_id = new.place_id;

    IF new.parent_id IS NOT NULL THEN

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

        INSERT

        INTO place_ancestor

        (place_id, ancestor_id)

        SELECT

            new.place_id, ancestor_id

        FROM place_ancestor

        WHERE place_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 place_ancestor

    (place_id, ancestor_id)

    VALUES (new.place_id, new.place_id)

    ;

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

    -- recursively tell all descendants

    UPDATE place

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

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

    WHERE COALESCE(parent_id, 2147483647) = new.place_id

    ;

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

    ancestors when a node is deleted, because the place.place_parent_id

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

    descendants anyway. */

    RETURN new;

END;

$$;

ALTER FUNCTION public.place_update_ancestors() OWNER TO bien;

--

-- Name: placepath_canon_placepath_id_self_ref(); Type: FUNCTION; Schema: public; Owner: bien

--

CREATE FUNCTION placepath_canon_placepath_id_self_ref() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

    IF new.placepath_id IS NULL THEN -- prepopulate placepath_id

        new.placepath_id = nextval('placepath_placepath_id_seq'::regclass);

    END IF;

    IF new.canon_placepath_id = 0 THEN -- make self-reference

        new.canon_placepath_id = new.placepath_id;

    END IF;

    RETURN new;

END;

$$;

ALTER FUNCTION public.placepath_canon_placepath_id_self_ref() OWNER TO bien;

--

-- Name: plantobservation_aggregateoccurrence_count_1(); Type: FUNCTION; Schema: public; Owner: bien

--

CREATE FUNCTION plantobservation_aggregateoccurrence_count_1() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

    UPDATE aggregateoccurrence

    SET count = GREATEST(COALESCE(count, 0), 1) -- at least 1

    WHERE aggregateoccurrence_id = new.aggregateoccurrence_id

    ;

    RETURN new;

END;

$$;

ALTER FUNCTION public.plantobservation_aggregateoccurrence_count_1() OWNER TO bien;

--

-- Name: taxonconcept_canon_concept_id_self_ref(); Type: FUNCTION; Schema: public; Owner: bien

--

CREATE FUNCTION taxonconcept_canon_concept_id_self_ref() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

    IF new.taxonconcept_id IS NULL THEN -- prepopulate taxonconcept_id

        new.taxonconcept_id = nextval('taxonconcept_taxonconcept_id_seq'::regclass);

    END IF;

    IF new.canon_concept_id = 0 THEN -- make self-reference

        new.canon_concept_id = new.taxonconcept_id;

    END IF;

    RETURN new;

END;

$$;

ALTER FUNCTION public.taxonconcept_canon_concept_id_self_ref() OWNER TO bien;

--

-- Name: taxonconcept_canon_concept_min_fit(); Type: FUNCTION; Schema: public; Owner: bien

--

CREATE FUNCTION taxonconcept_canon_concept_min_fit() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

    IF new.canon_concept_id IS NOT NULL

        AND new.canon_concept_fit_fraction < 0.8 THEN -- insufficient match

        new.canon_concept_id = NULL;

        new.canon_concept_fit_fraction = NULL;

    END IF;

    RETURN new;

END;

$$;

ALTER FUNCTION public.taxonconcept_canon_concept_min_fit() OWNER TO bien;

--

-- Name: taxonconcept_update_ancestors(); Type: FUNCTION; Schema: public; Owner: bien

--

CREATE FUNCTION taxonconcept_update_ancestors() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

    -- Delete existing ancestors

    DELETE FROM taxonconcept_ancestor

    WHERE taxonconcept_id = new.taxonconcept_id;

    IF new.parent_id IS NOT NULL THEN

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

        INSERT

        INTO taxonconcept_ancestor

        (taxonconcept_id, ancestor_id)

        SELECT

            new.taxonconcept_id, ancestor_id

        FROM taxonconcept_ancestor

        WHERE taxonconcept_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 taxonconcept_ancestor

    (taxonconcept_id, ancestor_id)

    VALUES (new.taxonconcept_id, new.taxonconcept_id)

    ;

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

    -- recursively tell all descendants

    UPDATE taxonconcept

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

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

    WHERE COALESCE(parent_id, 2147483647) = new.taxonconcept_id

    ;

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

    RETURN new;

END;

$$;

ALTER FUNCTION public.taxonconcept_update_ancestors() OWNER TO bien;

--

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

--

CREATE AGGREGATE concat(text) (

    SFUNC = textcat,

    STYPE = text,

    INITCOND = ''

);

ALTER AGGREGATE public.concat(text) OWNER TO bien;

SET default_tablespace = '';

SET default_with_oids = false;

--

-- Name: address; Type: TABLE; Schema: public; Owner: bien; Tablespace: 

--

CREATE TABLE address (

    address_id integer NOT NULL,

    party_id integer NOT NULL,

    organization_id integer,

    orgposition text,

    email text,

    deliverypoint text,

    city text,

    administrativearea text,

    postalcode text,

    country text,

    currentflag boolean,

    addressstartdate timestamp with time zone

);

ALTER TABLE public.address OWNER TO bien;

--

-- Name: address_address_id_seq; Type: SEQUENCE; Schema: public; Owner: bien

--

CREATE SEQUENCE address_address_id_seq

    START WITH 1

    INCREMENT BY 1

    NO MINVALUE

    NO MAXVALUE

    CACHE 1;

ALTER TABLE public.address_address_id_seq OWNER TO bien;

--

-- Name: address_address_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: bien

--

ALTER SEQUENCE address_address_id_seq OWNED BY address.address_id;

--

-- Name: address_address_id_seq; Type: SEQUENCE SET; Schema: public; Owner: bien

--

SELECT pg_catalog.setval('address_address_id_seq', 1, false);

--

-- Name: aggregateoccurrence; Type: TABLE; Schema: public; Owner: bien; Tablespace: 

--

CREATE TABLE aggregateoccurrence (

    aggregateoccurrence_id integer NOT NULL,

    creator_id integer NOT NULL,

    sourceaccessioncode text,

    taxonoccurrence_id integer NOT NULL,

    collectiondate timestamp with time zone,

    cover_fraction double precision,

    linecover_m double precision,

    basalarea_m2 double precision,

    biomass_kg_m2 double precision,

    inferencearea_m2 double precision,

    count integer,

    stratum_id integer,

    coverindex_id integer,

    occurrencestatus_dwc occurrencestatus_dwc DEFAULT 'present'::occurrencestatus_dwc NOT NULL,

    method_id integer,

    notes text,

    accessioncode text

);

ALTER TABLE public.aggregateoccurrence OWNER TO bien;

--

-- Name: TABLE aggregateoccurrence; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON TABLE aggregateoccurrence IS 'Equivalent to VegBank''s taxonimportance table.';

--

-- Name: COLUMN aggregateoccurrence.linecover_m; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN aggregateoccurrence.linecover_m IS 'The distance in m along which this occurrence intercepts a line subplot.';

--

-- Name: COLUMN aggregateoccurrence.occurrencestatus_dwc; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN aggregateoccurrence.occurrencestatus_dwc IS 'The extent to which the taxon is present. See <http://code.google.com/p/darwincore/wiki/Occurrence#occurrenceStatus>.';

--

-- Name: aggregateoccurrence_aggregateoccurrence_id_seq; Type: SEQUENCE; Schema: public; Owner: bien

--

CREATE SEQUENCE aggregateoccurrence_aggregateoccurrence_id_seq

    START WITH 1

    INCREMENT BY 1

    NO MINVALUE

    NO MAXVALUE

    CACHE 1;

ALTER TABLE public.aggregateoccurrence_aggregateoccurrence_id_seq OWNER TO bien;

--

-- Name: aggregateoccurrence_aggregateoccurrence_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: bien

--

ALTER SEQUENCE aggregateoccurrence_aggregateoccurrence_id_seq OWNED BY aggregateoccurrence.aggregateoccurrence_id;

--

-- Name: aggregateoccurrence_aggregateoccurrence_id_seq; Type: SEQUENCE SET; Schema: public; Owner: bien

--

SELECT pg_catalog.setval('aggregateoccurrence_aggregateoccurrence_id_seq', 1, false);

--

-- Name: location; Type: TABLE; Schema: public; Owner: bien; Tablespace: 

--

CREATE TABLE location (

    location_id integer NOT NULL,

    creator_id integer NOT NULL,

    sourceaccessioncode text,

    parent_id integer,

    authorlocationcode text,

    confidentialitystatus integer DEFAULT 0 NOT NULL,

    confidentialityreason text,

    sublocationxposition_m double precision,

    sublocationyposition_m double precision,

    authorzone text,

    authordatum text,

    authorlocation text,

    locationnarrative text,

    azimuth double precision,

    shape text,

    area_m2 double precision,

    standsize text,

    placementmethod text,

    permanence boolean,

    layoutnarrative text,

    elevation_m double precision,

    elevationaccuracy_m double precision,

    elevationrange_m double precision,

    verbatimelevation text,

    slopeaspect_deg double precision,

    minslopeaspect_deg double precision,

    maxslopeaspect_deg double precision,

    slopegradient_fraction double precision,

    minslopegradient_fraction double precision,

    maxslopegradient_fraction double precision,

    topoposition text,

    landform text,

    surficialdeposits text,

    rocktype text,

    submitter_surname text,

    submitter_givenname text,

    submitter_email text,

    notespublic boolean,

    notesmgt boolean,

    revisions boolean,

    dateentered timestamp with time zone DEFAULT now(),

    locationrationalenarrative text,

    accessioncode text,

    CONSTRAINT location_required_key CHECK (((sourceaccessioncode IS NOT NULL) OR (authorlocationcode IS NOT NULL)))

);

ALTER TABLE public.location OWNER TO bien;

--

-- Name: TABLE location; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON TABLE location IS 'Equivalent to VegBank''s plot table.';

--

-- Name: locationcoords; Type: TABLE; Schema: public; Owner: bien; Tablespace: 

--

CREATE TABLE locationcoords (

    locationcoords_id integer NOT NULL,

    location_id integer NOT NULL,

    latitude_deg double precision,

    longitude_deg double precision,

    verbatimlatitude text,

    verbatimlongitude text,

    verbatimcoordinates text,

    footprintgeometry_dwc text,

    coordsaccuracy_deg double precision,

    identifier_id integer,

    determinationdate timestamp with time zone,

    isoriginal boolean DEFAULT false NOT NULL,

    iscurrent boolean DEFAULT false NOT NULL,

    calculated boolean

);

ALTER TABLE public.locationcoords OWNER TO bien;

--

-- Name: COLUMN locationcoords.footprintgeometry_dwc; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN locationcoords.footprintgeometry_dwc IS 'DwC''s footprintWKT field. "A Well-Known Text (WKT) representation of the shape (footprint, geometry) that defines the Location" (http://rs.tdwg.org/dwc/terms/#footprintWKT).';

--

-- Name: COLUMN locationcoords.coordsaccuracy_deg; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN locationcoords.coordsaccuracy_deg IS 'Accuracy of latitude/longitude or footprint geometry, in meters. This should generally be at least 1 m, which is the accuracy of the best GPSes.';

--

-- Name: locationevent; Type: TABLE; Schema: public; Owner: bien; Tablespace: 

--

CREATE TABLE locationevent (

    locationevent_id integer NOT NULL,

    creator_id integer NOT NULL,

    sourceaccessioncode text,

    parent_id integer,

    location_id integer,

    project_id integer,

    authoreventcode text,

    previous_id integer,

    dateaccuracy text,

    method_id integer,

    temperature_c double precision,

    precipitation_m double precision,

    autotaxoncover boolean,

    originaldata text,

    effortlevel text,

    floristicquality text,

    bryophytequality text,

    lichenquality text,

    locationeventnarrative text,

    landscapenarrative text,

    homogeneity text,

    phenologicaspect text,

    representativeness text,

    standmaturity text,

    successionalstatus text,

    basalarea double precision,

    hydrologicregime text,

    soilmoistureregime text,

    soildrainage text,

    watersalinity text,

    waterdepth_m double precision,

    shoredistance double precision,

    soildepth double precision,

    organicdepth double precision,

    soiltaxon_id integer,

    soiltaxonsrc text,

    percentbedrock double precision,

    percentrockgravel double precision,

    percentwood double precision,

    percentlitter double precision,

    percentbaresoil double precision,

    percentwater double precision,

    percentother double precision,

    nameother text,

    treeht double precision,

    shrubht double precision,

    fieldht double precision,

    nonvascularht double precision,

    submergedht double precision,

    treecover double precision,

    shrubcover double precision,

    fieldcover double precision,

    nonvascularcover double precision,

    floatingcover double precision,

    submergedcover double precision,

    dominantstratum text,

    growthform1type text,

    growthform2type text,

    growthform3type text,

    growthform1cover double precision,

    growthform2cover double precision,

    growthform3cover double precision,

    totalcover double precision,

    notespublic boolean,

    notesmgt boolean,

    revisions boolean,

    obsstartdate timestamp with time zone,

    obsenddate timestamp with time zone,

    dateentered timestamp with time zone DEFAULT now(),

    toptaxon1name text,

    toptaxon2name text,

    toptaxon3name text,

    toptaxon4name text,

    toptaxon5name text,

    numberoftaxa integer,

    accessioncode text,

    CONSTRAINT locationevent_required_key CHECK (((sourceaccessioncode IS NOT NULL) OR (location_id IS NOT NULL)))

);

ALTER TABLE public.locationevent OWNER TO bien;

--

-- Name: TABLE locationevent; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON TABLE locationevent IS 'Equivalent to VegBank''s observation table.';

--

-- Name: locationplace; Type: TABLE; Schema: public; Owner: bien; Tablespace: 

--

CREATE TABLE locationplace (

    locationplace_id integer NOT NULL,

    location_id integer NOT NULL,

    placepath_id integer NOT NULL,

    identifier_id integer

);

ALTER TABLE public.locationplace OWNER TO bien;

--

-- Name: TABLE locationplace; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON TABLE locationplace IS 'Equivalent to VegBank''s place table.';

--

-- Name: method; Type: TABLE; Schema: public; Owner: bien; Tablespace: 

--

CREATE TABLE method (

    method_id integer NOT NULL,

    reference_id integer,

    name text NOT NULL,

    description text,

    diameterheight_m double precision,

    mindiameter_m double precision,

    maxdiameter_m double precision,

    minheight_m double precision,

    maxheight_m double precision,

    observationtype text,

    observationmeasure text,

    covermethod_id integer,

    samplingfactor double precision DEFAULT 1 NOT NULL,

    coverbasis text,

    stemsamplemethod text,

    shape text,

    length_m double precision,

    width_m double precision,

    radius_m double precision,

    area_m2 double precision,

    samplearea_m2 double precision,

    subplotspacing_m double precision,

    subplotmethod_id integer,

    pointsperline integer,

    accessioncode text

);

ALTER TABLE public.method OWNER TO bien;

--

-- Name: TABLE method; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON TABLE method IS 'A method for sampling and aggregating plants. Replaces VegBank''s stratummethod and stratumtype tables.

Important: *All* length- or area-related measurements throughout VegBIEN must be converted to SI base units, e.g. cm -> m, ha -> m^2.**';

--

-- Name: COLUMN method.reference_id; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN method.reference_id IS 'Use the reference table (e.g. reference.url) to store a link to the original plain text description.';

--

-- Name: COLUMN method.name; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN method.name IS 'A short name for the set of methods used. Although there is no existing standard, many names are widely used, and could be useful for finding plots with similar methodology.';

--

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

--

COMMENT ON COLUMN method.description IS 'Additional metadata helpful for understanding how the data were collected during the observation event.';

--

-- Name: COLUMN method.diameterheight_m; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN method.diameterheight_m IS 'The height in m at which the diameter is measured. e.g. 1.37 m for breast height (DBH).';

--

-- Name: COLUMN method.mindiameter_m; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN method.mindiameter_m IS 'Lower diameter limit in m for inclusion of a tree.';

--

-- Name: COLUMN method.maxdiameter_m; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN method.maxdiameter_m IS 'Upper diameter limit in m for inclusion of a tree.';

--

-- Name: COLUMN method.minheight_m; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN method.minheight_m IS 'Lower height limit in m for inclusion of a tree.';

--

-- Name: COLUMN method.maxheight_m; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN method.maxheight_m IS 'Upper height limit in m for inclusion of a tree.';

--

-- Name: COLUMN method.observationtype; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN method.observationtype IS 'values: aggregate, individual, both';

--

-- Name: COLUMN method.observationmeasure; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN method.observationmeasure IS 'e.g. count, cover, presence, points-intercepted, distance-intercepted';

--

-- Name: COLUMN method.samplingfactor; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN method.samplingfactor IS 'Here, we could explicitly say that we are sampling a particular area by a different size representative sample area. Simply divide the number of plants connected to this record by this value to get the extrapolated (or interpolated) number of plants in the area in question. This explicitly notes a subsample or supersample.';

--

-- Name: COLUMN method.coverbasis; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN method.coverbasis IS 'Were cover values for the total taxon list collected from one contiguous area or dispersed subplots?

entire: Cover based on observation of an entire plot consisting of a single contiguous area of land. subplot-contiguous: Cover based on observation of a single contiguous area of land of less spatial extent than the entire plot.

subplot-regular: Cover based on observation of multiple subplots arranged in a regular pattern within the overall plot.

subplot-random: Cover based on observation of multiple randomly dispersed subplots within the overall plot.

subplot-haphazard: Cover based on observation of multiple subplots haphazardly arranged within the overall plot.

line-intercept: Cover based on length of line touching each species present.

point-intercept: Cover based on number of points for each species present.

';

--

-- Name: COLUMN method.stemsamplemethod; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN method.stemsamplemethod IS 'The method used to obtain basal area or tree stem data (e.g., full census, point quarter, random pairs, Bitterlich, other).

e.g.:

Full census

Point quarter

Random pairs

Bitterlich

Other

Subsample census';

--

-- Name: COLUMN method.shape; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN method.shape IS 'e.g. square, rectangle, circle, line, point, other';

--

-- Name: COLUMN method.samplearea_m2; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN method.samplearea_m2 IS 'The total surface area used for cover estimates and for which a complete species list is provided. If subplots were used, this would be the total area of the subplots without interstitial space.';

--

-- Name: COLUMN method.subplotspacing_m; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN method.subplotspacing_m IS 'Spacing in m between adjacent subplots, lines (line-intercept data), or points (point-intercept data).';

--

-- Name: COLUMN method.subplotmethod_id; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN method.subplotmethod_id IS 'Method to use for each subplot/line/point, which will specify subplot size, line length, etc.';

--

-- Name: COLUMN method.pointsperline; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN method.pointsperline IS 'The number of points sampled on each line subplot for point-intercept data.';

--

-- Name: party; Type: TABLE; Schema: public; Owner: bien; Tablespace: 

--

CREATE TABLE party (

    party_id integer NOT NULL,

    creator_id integer NOT NULL,

    salutation text,

    givenname text,

    middlename text,

    surname text,

    organizationname text,

    currentname_id integer,

    contactinstructions text,

    email text,

    partytype text,

    partypublic boolean DEFAULT true,

    d_obscount integer,

    accessioncode text,

    CONSTRAINT party_required_key CHECK (((organizationname IS NOT NULL) OR ((creator_id <> party_id) AND (surname IS NOT NULL))))

);

ALTER TABLE public.party OWNER TO bien;

--

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

--

COMMENT ON COLUMN party.creator_id IS 'A datasource should point to itself in this field. This will happen automatically by setting it to the special value 0.';

--

-- Name: placepath; Type: TABLE; Schema: public; Owner: bien; Tablespace: 

--

CREATE TABLE placepath (

    placepath_id integer NOT NULL,

    creator_id integer NOT NULL,

    placecode text,

    canon_placepath_id integer,

    place_id integer,

    continent text,

    country text,

    stateprovince text,

    county text,

    municipality text,

    site text,

    otherranks rankedplacename[],

    CONSTRAINT placepath_required_key CHECK (((((((placecode IS NOT NULL) OR (continent IS NOT NULL)) OR (country IS NOT NULL)) OR (stateprovince IS NOT NULL)) OR (county IS NOT NULL)) OR (otherranks IS NOT NULL)))

);

ALTER TABLE public.placepath OWNER TO bien;

--

-- Name: TABLE placepath; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON TABLE placepath IS 'The full path to a place. Can be either verbatim or accepted. For accepted names, points to the identified place.

To include a placename at a rank with no explicit column, add it to the otherranks array.';

--

-- Name: COLUMN placepath.canon_placepath_id; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN placepath.canon_placepath_id IS 'The placepath containing the accepted name of this verbatim place path.  placepaths should be linked in a two-level hierarchy of datasource name -> accepted name.

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

--

-- Name: COLUMN placepath.otherranks; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN placepath.otherranks IS 'Additional ranks which do not have a named column. Put ranks in path order, so that lower-level places come after higher-level places.';

--

-- Name: plantobservation; Type: TABLE; Schema: public; Owner: bien; Tablespace: 

--

CREATE TABLE plantobservation (

    plantobservation_id integer NOT NULL,

    creator_id integer NOT NULL,

    sourceaccessioncode text,

    aggregateoccurrence_id integer NOT NULL,

    overallheight_m double precision,

    overallheightaccuracy_m double precision,

    collectionnumber text,

    stemcount integer,

    plant_id integer,

    accessioncode text

);

ALTER TABLE public.plantobservation OWNER TO bien;

--

-- Name: TABLE plantobservation; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON TABLE plantobservation IS 'Equivalent to VegBank''s stemcount table.';

--

-- Name: COLUMN plantobservation.collectionnumber; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN plantobservation.collectionnumber IS 'The number of the organism within the data collection or event.';

--

-- Name: specimenreplicate; Type: TABLE; Schema: public; Owner: bien; Tablespace: 

--

CREATE TABLE specimenreplicate (

    specimenreplicate_id integer NOT NULL,

    creator_id integer NOT NULL,

    sourceaccessioncode text,

    plantobservation_id integer,

    institution_id integer,

    collectioncode_dwc text,

    catalognumber_dwc text,

    description text,

    specimen_id integer,

    accessioncode text,

    CONSTRAINT specimenreplicate_required_key CHECK ((((plantobservation_id IS NOT NULL) OR (sourceaccessioncode IS NOT NULL)) OR (catalognumber_dwc IS NOT NULL)))

);

ALTER TABLE public.specimenreplicate OWNER TO bien;

--

-- Name: TABLE specimenreplicate; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON TABLE specimenreplicate IS 'A herbarium''s replicate of a specimen. Contains Darwin Core specimen data.';

--

-- Name: COLUMN specimenreplicate.institution_id; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN specimenreplicate.institution_id IS 'The institution (such as a museum) that the specimenreplicate is from.';

--

-- Name: COLUMN specimenreplicate.collectioncode_dwc; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN specimenreplicate.collectioncode_dwc IS 'The code for the collection that the specimenreplicate is from.';

--

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

--

CREATE TABLE taxonconcept (

    taxonconcept_id integer NOT NULL,

    creator_id integer NOT NULL,

    creationdate timestamp with time zone,

    canon_concept_id integer,

    canon_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_canon_concept_fit_fraction_range CHECK (((canon_concept_fit_fraction >= (0)::double precision) AND (canon_concept_fit_fraction <= (1)::double precision))),

    CONSTRAINT taxonconcept_required_key CHECK ((((((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)))))

);

ALTER TABLE public.taxonconcept OWNER TO bien;

--

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

--

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: bien

--

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

--

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

--

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.canon_concept_id; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN taxonconcept.canon_concept_id IS 'The taxonconcept containing the accepted synonym of 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.canon_concept_fit_fraction; Type: COMMENT; Schema: public; Owner: bien

--

COMMENT ON COLUMN taxonconcept.canon_concept_fit_fraction IS 'The closeness of fit of the canon_concept.';

--

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

--

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: bien

--

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: bien

--

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: bien

--

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: bien

--

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: bien

--

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: bien

--

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

Equivalent to Darwin Core''s scientificNameAuthorship.';