--

-- PostgreSQL database dump

--

SET statement_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SET check_function_bodies = false;

SET client_min_messages = warning;

--

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

--

CREATE SCHEMA "TNRS";

ALTER SCHEMA "TNRS" OWNER TO bien;

SET search_path = "TNRS", pg_catalog;

--

-- Name: batch__fill(); Type: FUNCTION; Schema: TNRS; Owner: bien

--

CREATE FUNCTION batch__fill() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

	new.id_by_time = new.time_submitted;

	new.id = COALESCE(new.id, new.id_by_time);

	RETURN new;

END;

$$;

ALTER FUNCTION "TNRS".batch__fill() OWNER TO bien;

--

-- Name: family_is_homonym(text); Type: FUNCTION; Schema: TNRS; Owner: bien

--

CREATE FUNCTION family_is_homonym(family text) RETURNS boolean

    LANGUAGE sql STABLE STRICT

    AS $_$

SELECT EXISTS(SELECT * FROM "IRMNG".family_homonym_epithet WHERE "taxonNameOrEpithet" = $1)

$_$;

ALTER FUNCTION "TNRS".family_is_homonym(family text) OWNER TO bien;

--

-- Name: genus_is_homonym(text); Type: FUNCTION; Schema: TNRS; Owner: bien

--

CREATE FUNCTION genus_is_homonym(genus text) RETURNS boolean

    LANGUAGE sql STABLE STRICT

    AS $_$

SELECT EXISTS(SELECT * FROM "IRMNG".genus_homonym_epithet WHERE "taxonNameOrEpithet" = $1)

$_$;

ALTER FUNCTION "TNRS".genus_is_homonym(genus text) OWNER TO bien;

--

-- Name: score_ok(double precision); Type: FUNCTION; Schema: TNRS; Owner: bien

--

CREATE FUNCTION score_ok(score double precision) RETURNS boolean

    LANGUAGE sql IMMUTABLE STRICT

    AS $_$

SELECT $1 >= 0.8

$_$;

ALTER FUNCTION "TNRS".score_ok(score double precision) OWNER TO bien;

--

-- Name: tnrs_populate_fields(); Type: FUNCTION; Schema: TNRS; Owner: bien

--

CREATE FUNCTION tnrs_populate_fields() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

DECLARE

    "Specific_epithet_is_plant" boolean :=

        (CASE

        WHEN   new."Infraspecific_epithet_matched"   IS NOT NULL

            OR new."Infraspecific_epithet_2_matched" IS NOT NULL

            OR new."Specific_epithet_score" >= 0.9 -- fuzzy match

            THEN true

        ELSE NULL -- ambiguous

        END);

    never_homonym boolean = COALESCE(new."Author_score" >= 0.6, false); -- author disambiguates

    family_is_homonym boolean = NOT never_homonym AND "TNRS".family_is_homonym(new."Family_matched");

    genus_is_homonym  boolean = NOT never_homonym AND "TNRS".genus_is_homonym( new."Genus_matched" );

BEGIN

    new."Accepted_scientific_name" = NULLIF(concat_ws(' '

        , NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'), new."Accepted_name")

        , new."Accepted_name"

        , new."Accepted_name_author"

    ), '');

    new."Max_score" = GREATEST(

          new."Overall_score"

        , new."Family_score"

        , new."Genus_score"

        , new."Specific_epithet_score"

    );

    new."Is_homonym" = COALESCE(genus_is_homonym, family_is_homonym);

    new."Is_plant" = (CASE

        WHEN new."Family_score" = 1 AND NOT family_is_homonym -- exact match

            THEN true

        ELSE -- consider genus

            (CASE

            WHEN new."Genus_score" =  1    -- exact match

                THEN

                (CASE

                WHEN NOT genus_is_homonym THEN true

                ELSE "Specific_epithet_is_plant"

                END)

            WHEN new."Genus_score" >= 0.85 -- fuzzy match

                THEN "Specific_epithet_is_plant"

            ELSE NULL -- ambiguous

            END)

        END);

    RETURN new;

END;

$$;

ALTER FUNCTION "TNRS".tnrs_populate_fields() OWNER TO bien;

--

-- Name: FUNCTION tnrs_populate_fields(); Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON FUNCTION tnrs_populate_fields() IS 'IMPORTANT: when changing this function, you must regenerate the derived cols:

UPDATE "TNRS".tnrs SET "Name_submitted" = "Name_submitted"

runtime: 25 min ("5221748 rows affected, 1524228 ms execution time")

VACUUM ANALYZE "TNRS".tnrs -- to remove previous rows

runtime: 1 min ("62350 ms")';

SET default_tablespace = '';

SET default_with_oids = false;

--

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

--

CREATE TABLE tnrs (

    batch text DEFAULT now() NOT NULL,

    "Name_number" integer NOT NULL,

    "Name_submitted" text NOT NULL,

    "Overall_score" double precision,

    "Name_matched" text,

    "Name_matched_rank" text,

    "Name_score" double precision,

    "Name_matched_author" text,

    "Name_matched_url" text,

    "Author_matched" text,

    "Author_score" double precision,

    "Family_matched" text,

    "Family_score" double precision,

    "Name_matched_accepted_family" text,

    "Genus_matched" text,

    "Genus_score" double precision,

    "Specific_epithet_matched" text,

    "Specific_epithet_score" double precision,

    "Infraspecific_rank" text,

    "Infraspecific_epithet_matched" text,

    "Infraspecific_epithet_score" double precision,

    "Infraspecific_rank_2" text,

    "Infraspecific_epithet_2_matched" text,

    "Infraspecific_epithet_2_score" double precision,

    "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,

    "Accepted_scientific_name" text,

    "Max_score" double precision,

    "Is_homonym" boolean,

    "Is_plant" boolean

);

ALTER TABLE "TNRS".tnrs OWNER TO bien;

--

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

--

COMMENT ON TABLE tnrs IS 'IMPORTANT: when changing this table''s schema, you must regenerate data.sql:

$ <this_file>/../test_taxonomic_names/test_scrub

you must also make the same changes on vegbiendev: e.g.

$ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s")

SELECT util.set_col_types(''"TNRS".tnrs'', ARRAY[

  (''col'', ''new_type'')

]::util.col_cast[]);

runtime: 9 min ("531282 ms")';

--

-- Name: AcceptedTaxon; Type: VIEW; Schema: TNRS; Owner: bien

--

CREATE VIEW "AcceptedTaxon" AS

    SELECT tnrs.batch AS "*Accepted_name.batch", tnrs."Name_submitted" AS "acceptedScientificName", tnrs."Genus_matched" AS "acceptedGenus", tnrs."Specific_epithet_matched" AS "acceptedSpecificEpithet", tnrs."Infraspecific_epithet_matched" AS "acceptedInfraspecificEpithet", tnrs."Taxonomic_status" AS "acceptedTaxonomicStatus", tnrs."Selected" AS "*Accepted_name.Selected", tnrs."Source" AS "*Accepted_name.Source", tnrs."Warnings" AS "*Accepted_name.Warnings", tnrs."Accepted_name_lsid" AS "*Accepted_name.Accepted_name_lsid" FROM tnrs;

ALTER TABLE "TNRS"."AcceptedTaxon" OWNER TO bien;

--

-- Name: MatchedTaxon; Type: VIEW; Schema: TNRS; Owner: bien

--

CREATE VIEW "MatchedTaxon" AS

    SELECT tnrs.batch AS "*Name_matched.batch", tnrs."Name_submitted" AS "concatenatedScientificName", tnrs."Name_matched" AS "matchedTaxonName", tnrs."Name_matched_rank" AS "matchedTaxonRank", tnrs."Name_score" AS "*Name_matched.Name_score", tnrs."Name_matched_author" AS "matchedScientificNameAuthorship", tnrs."Name_matched_url" AS "matchedScientificNameID", tnrs."Author_score" AS "*Name_matched.Author_score", tnrs."Family_score" AS "matchedFamilyConfidence_fraction", COALESCE(tnrs."Name_matched_accepted_family", tnrs."Accepted_name_family") AS "matchedFamily", tnrs."Genus_matched" AS "matchedGenus", tnrs."Genus_score" AS "matchedGenusConfidence_fraction", tnrs."Specific_epithet_matched" AS "matchedSpecificEpithet", tnrs."Specific_epithet_score" AS "matchedSpeciesConfidence_fraction", tnrs."Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet", tnrs."Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score", tnrs."Annotations" AS "identificationQualifier", tnrs."Unmatched_terms" AS "morphospeciesSuffix", tnrs."Taxonomic_status" AS "taxonomicStatus", tnrs."Accepted_name" AS "acceptedTaxonName", tnrs."Accepted_name_author" AS "acceptedScientificNameAuthorship", tnrs."Accepted_name_rank" AS "acceptedTaxonRank", tnrs."Accepted_name_url" AS "acceptedScientificNameID", tnrs."Accepted_name_species" AS "*Name_matched.Accepted_name_species", tnrs."Accepted_name_family" AS "acceptedFamily", tnrs."Selected" AS "*Name_matched.Selected", tnrs."Source" AS "*Name_matched.Source", tnrs."Warnings" AS "*Name_matched.Warnings", tnrs."Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid", tnrs."Accepted_scientific_name" AS "acceptedScientificName", tnrs."Max_score" AS "matchedTaxonConfidence_fraction" FROM tnrs;

ALTER TABLE "TNRS"."MatchedTaxon" OWNER TO bien;

--

-- Name: ValidMatchedTaxon; Type: VIEW; Schema: TNRS; Owner: bien

--

CREATE VIEW "ValidMatchedTaxon" AS

    SELECT "MatchedTaxon"."*Name_matched.batch", "MatchedTaxon"."concatenatedScientificName", "MatchedTaxon"."matchedTaxonName", "MatchedTaxon"."matchedTaxonRank", "MatchedTaxon"."*Name_matched.Name_score", "MatchedTaxon"."matchedScientificNameAuthorship", "MatchedTaxon"."matchedScientificNameID", "MatchedTaxon"."*Name_matched.Author_score", "MatchedTaxon"."matchedFamilyConfidence_fraction", "MatchedTaxon"."matchedFamily", "MatchedTaxon"."matchedGenus", "MatchedTaxon"."matchedGenusConfidence_fraction", "MatchedTaxon"."matchedSpecificEpithet", "MatchedTaxon"."matchedSpeciesConfidence_fraction", "MatchedTaxon"."matchedInfraspecificEpithet", "MatchedTaxon"."*Name_matched.Infraspecific_epithet_score", "MatchedTaxon"."identificationQualifier", "MatchedTaxon"."morphospeciesSuffix", "MatchedTaxon"."taxonomicStatus", "MatchedTaxon"."acceptedTaxonName", "MatchedTaxon"."acceptedScientificNameAuthorship", "MatchedTaxon"."acceptedTaxonRank", "MatchedTaxon"."acceptedScientificNameID", "MatchedTaxon"."*Name_matched.Accepted_name_species", "MatchedTaxon"."acceptedFamily", "MatchedTaxon"."*Name_matched.Selected", "MatchedTaxon"."*Name_matched.Source", "MatchedTaxon"."*Name_matched.Warnings", "MatchedTaxon"."*Name_matched.Accepted_name_lsid", "MatchedTaxon"."acceptedScientificName", "MatchedTaxon"."matchedTaxonConfidence_fraction" FROM "MatchedTaxon" WHERE score_ok("MatchedTaxon"."matchedTaxonConfidence_fraction");

ALTER TABLE "TNRS"."ValidMatchedTaxon" OWNER TO bien;

--

-- Name: VIEW "ValidMatchedTaxon"; Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON VIEW "ValidMatchedTaxon" IS 'to update, use * as the column list';

--

-- Name: ScrubbedTaxon; Type: VIEW; Schema: TNRS; Owner: bien

--

CREATE VIEW "ScrubbedTaxon" AS

    SELECT "ValidMatchedTaxon"."acceptedScientificName", "ValidMatchedTaxon"."*Name_matched.batch", "ValidMatchedTaxon"."concatenatedScientificName", "ValidMatchedTaxon"."matchedTaxonName", "ValidMatchedTaxon"."matchedTaxonRank", "ValidMatchedTaxon"."*Name_matched.Name_score", "ValidMatchedTaxon"."matchedScientificNameAuthorship", "ValidMatchedTaxon"."matchedScientificNameID", "ValidMatchedTaxon"."*Name_matched.Author_score", "ValidMatchedTaxon"."matchedFamilyConfidence_fraction", "ValidMatchedTaxon"."matchedFamily", "ValidMatchedTaxon"."matchedGenus", "ValidMatchedTaxon"."matchedGenusConfidence_fraction", "ValidMatchedTaxon"."matchedSpecificEpithet", "ValidMatchedTaxon"."matchedSpeciesConfidence_fraction", "ValidMatchedTaxon"."matchedInfraspecificEpithet", "ValidMatchedTaxon"."*Name_matched.Infraspecific_epithet_score", "ValidMatchedTaxon"."identificationQualifier", "ValidMatchedTaxon"."morphospeciesSuffix", "ValidMatchedTaxon"."taxonomicStatus", "ValidMatchedTaxon"."acceptedTaxonName", "ValidMatchedTaxon"."acceptedScientificNameAuthorship", "ValidMatchedTaxon"."acceptedTaxonRank", "ValidMatchedTaxon"."acceptedScientificNameID", "ValidMatchedTaxon"."*Name_matched.Accepted_name_species", "ValidMatchedTaxon"."acceptedFamily", "ValidMatchedTaxon"."*Name_matched.Selected", "ValidMatchedTaxon"."*Name_matched.Source", "ValidMatchedTaxon"."*Name_matched.Warnings", "ValidMatchedTaxon"."*Name_matched.Accepted_name_lsid", "ValidMatchedTaxon"."matchedTaxonConfidence_fraction", "AcceptedTaxon"."*Accepted_name.batch", "AcceptedTaxon"."acceptedGenus", "AcceptedTaxon"."acceptedSpecificEpithet", "AcceptedTaxon"."acceptedInfraspecificEpithet", "AcceptedTaxon"."acceptedTaxonomicStatus", "AcceptedTaxon"."*Accepted_name.Selected", "AcceptedTaxon"."*Accepted_name.Source", "AcceptedTaxon"."*Accepted_name.Warnings", "AcceptedTaxon"."*Accepted_name.Accepted_name_lsid" FROM ("ValidMatchedTaxon" NATURAL LEFT JOIN "AcceptedTaxon");

ALTER TABLE "TNRS"."ScrubbedTaxon" OWNER TO bien;

--

-- Name: VIEW "ScrubbedTaxon"; Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON VIEW "ScrubbedTaxon" IS 'to update, use * as the column list';

--

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

--

CREATE TABLE "Source" (

    "*row_num" integer NOT NULL,

    "sourceType" text DEFAULT 'aggregator'::text NOT NULL,

    "datasetURL" text DEFAULT 'http://tnrs.iplantcollaborative.org/TNRSapp.html'::text NOT NULL

);

ALTER TABLE "TNRS"."Source" OWNER TO bien;

--

-- Name: COLUMN "Source"."sourceType"; Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON COLUMN "Source"."sourceType" IS 'constant';

--

-- Name: COLUMN "Source"."datasetURL"; Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON COLUMN "Source"."datasetURL" IS 'constant';

--

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

--

CREATE TABLE batch (

    id text NOT NULL,

    id_by_time text,

    time_submitted timestamp with time zone DEFAULT now(),

    client_version text

);

ALTER TABLE "TNRS".batch OWNER TO bien;

--

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

--

CREATE TABLE batch_download_settings (

    id text NOT NULL,

    "E-mail" text,

    "Id" text,

    "Job type" text,

    "Contains Id" boolean,

    "Start time" text,

    "Finish time" text,

    "TNRS version" text,

    "Sources selected" text,

    "Match threshold" double precision,

    "Classification" text,

    "Allow partial matches?" boolean,

    "Sort by source" boolean,

    "Constrain by higher taxonomy" boolean

);

ALTER TABLE "TNRS".batch_download_settings OWNER TO bien;

--

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

--

COMMENT ON TABLE batch_download_settings IS 'stores data from http://tnrs.iplantcollaborative.org/TNRSapp.html > Submit List > results section > Download settings > settings.txt';

--

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

--

CREATE TABLE client_version (

    id text NOT NULL,

    global_rev integer NOT NULL,

    "/lib/tnrs.py rev" integer,

    "/bin/tnrs_db rev" integer

);

ALTER TABLE "TNRS".client_version OWNER TO bien;

--

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

--

COMMENT ON TABLE client_version IS 'contains svn revisions';

--

-- Name: COLUMN client_version.global_rev; Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON COLUMN client_version.global_rev IS 'from `svn info .` > Last Changed Rev';

--

-- Name: COLUMN client_version."/lib/tnrs.py rev"; Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON COLUMN client_version."/lib/tnrs.py rev" IS 'from `svn info lib/tnrs.py` > Last Changed Rev';

--

-- Name: COLUMN client_version."/bin/tnrs_db rev"; Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON COLUMN client_version."/bin/tnrs_db rev" IS 'from `svn info bin/tnrs_db` > Last Changed Rev';

--

-- Name: ~Source.map; Type: TABLE; Schema: TNRS; Owner: bien; Tablespace: 

--

CREATE TABLE "~Source.map" (

    "from" text NOT NULL,

    "to" text,

    filter text,

    notes text

);

ALTER TABLE "TNRS"."~Source.map" OWNER TO bien;

--

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

--

COPY "Source" ("*row_num", "sourceType", "datasetURL") FROM stdin;

\.

--

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

--

COPY batch (id, id_by_time, time_submitted, client_version) FROM stdin;

2013-08-27 22:02:36.042579-07	2013-08-27 22:02:36.042579-07	2013-08-27 22:02:36.042579-07	\N

2013-08-27 22:02:41.03229-07	2013-08-27 22:02:41.03229-07	2013-08-27 22:02:41.03229-07	\N

2013-08-27 22:02:43.024394-07	2013-08-27 22:02:43.024394-07	2013-08-27 22:02:43.024394-07	\N

\.

--

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

--

COPY batch_download_settings (id, "E-mail", "Id", "Job type", "Contains Id", "Start time", "Finish time", "TNRS version", "Sources selected", "Match threshold", "Classification", "Allow partial matches?", "Sort by source", "Constrain by higher taxonomy") FROM stdin;

\.

--

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

--

COPY client_version (id, global_rev, "/lib/tnrs.py rev", "/bin/tnrs_db rev") FROM stdin;

\.

--

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

--

COPY tnrs (batch, "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", "Accepted_scientific_name", "Max_score", "Is_homonym", "Is_plant") FROM stdin;

2013-08-27 22:02:36.042579-07	0	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	0	\N	\N

2013-08-27 22:02:36.042579-07	1	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	Poaceae Poa annua L.	1	f	t

2013-08-27 22:02:36.042579-07	2	Poa annua	1	Poa annua	species	1	L.	http://www.tropicos.org/Name/25509881	\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	Poa annua	Poaceae	true	tropicos	 	\N	Poaceae Poa annua L.	1	f	t

2013-08-27 22:02:36.042579-07	3	Silene scouleri Hook. subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea C.L. Hitchc. & Maguire	0.770000000000000018	Silene scouleri subsp. pringlei	subspecies	0.770000000000000018	(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	Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire	1	t	t

2013-08-27 22:02:36.042579-07	4	Fabaceae Boyle#6500	0.900000000000000022	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	Fabaceae Lindl.	1	f	t

2013-08-27 22:02:36.042579-07	5	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	Poaceae Poa infirma Kunth	1	f	t

2013-08-27 22:02:36.042579-07	6	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	Poaceae Poa infirma Kunth	1	f	t

2013-08-27 22:02:36.042579-07	7	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	Poaceae Poa annua subvar. minima (Schur) Asch. & Graebn.	1	f	t

2013-08-27 22:02:36.042579-07	8	Poa annua L.	1	Poa annua	species	1	L.	http://www.tropicos.org/Name/25509881	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	Poa annua	Poaceae	true	tropicos	 	\N	Poaceae Poa annua L.	1	f	t

2013-08-27 22:02:36.042579-07	9	Compositae indet. sp.1	0.900000000000000022	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	Asteraceae Bercht. & J. Presl	1	f	t

2013-08-27 22:02:36.042579-07	10	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	Poaceae Poa annua var. annua	1	f	t

2013-08-27 22:02:36.042579-07	11	Silene scouleri subsp. pringlei var. grisea C.L. Hitchc. & Maguire	0.770000000000000018	Silene scouleri subsp. pringlei	subspecies	0.770000000000000018	(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	Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire	1	t	t

2013-08-27 22:02:36.042579-07	12	Fabaceae Inga "fuzzy leaf"	0.900000000000000022	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	Fabaceae Inga Mill.	1	t	t

2013-08-27 22:02:36.042579-07	13	Fabaceae Inga sp.3	0.900000000000000022	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	Fabaceae Inga Mill.	1	t	t

2013-08-27 22:02:36.042579-07	14	Fabaceae unknown #2	0.900000000000000022	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	Fabaceae Lindl.	1	f	t

2013-08-27 22:02:41.03229-07	0	Poaceae Poa infirma Kunth	1	Poa infirma	species	1	Kunth	http://www.tropicos.org/Name/25514158	Kunth	1	Poaceae	1	Poaceae	Poa	1	infirma	1	\N	\N	\N	\N	\N	\N	\N	\N	Accepted	Poa infirma	Kunth	species	http://www.tropicos.org/Name/25514158	Poa infirma	Poaceae	true	tropicos	 	\N	Poaceae Poa infirma Kunth	1	f	t

2013-08-27 22:02:41.03229-07	1	Fabaceae Lindl.	0.5	Fabaceae	family	0.5	Lindl.	http://www.tropicos.org/Name/42000184	\N	\N	Fabaceae	1	Fabaceae	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	Lindl.	Accepted	Fabaceae	Lindl.	family	http://www.tropicos.org/Name/42000184	\N	Fabaceae	true	tropicos	 [Partial match] 	\N	Fabaceae Lindl.	1	f	t

2013-08-27 22:02:41.03229-07	2	Poaceae Poa annua subvar. minima (Schur) Asch. & Graebn.	1	Poa annua subvar. minima	subvariety	1	(Schur) Asch. & Graebn.	http://www.tropicos.org/Name/50158097	(Schur) Asch. & Graebn.	1	Poaceae	1	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	Poaceae Poa annua subvar. minima (Schur) Asch. & Graebn.	1	f	t

2013-08-27 22:02:41.03229-07	3	Fabaceae Inga Mill.	1	Inga	genus	1	Mill.	http://www.tropicos.org/Name/40031040	Mill.	1	Fabaceae	1	Fabaceae	Inga	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	Accepted	Inga	Mill.	genus	http://www.tropicos.org/Name/40031040	\N	Fabaceae	true	tropicos	 	\N	Fabaceae Inga Mill.	1	f	t

2013-08-27 22:02:41.03229-07	4	Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire	1	Silene scouleri subsp. pringlei	subspecies	1	(S. Watson) C.L. Hitchc. & Maguire	http://www.tropicos.org/Name/6303627	(S. Watson) C.L. Hitchc. & Maguire	1	Caryophyllaceae	1	Caryophyllaceae	Silene	1	scouleri	1	subsp.	pringlei	1	\N	\N	\N	\N	\N	Accepted	Silene scouleri subsp. pringlei	(S. Watson) C.L. Hitchc. & Maguire	subspecies	http://www.tropicos.org/Name/6303627	Silene scouleri	Caryophyllaceae	true	tropicos	 	\N	Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire	1	f	t

2013-08-27 22:02:41.03229-07	5	Poaceae Poa annua L.	1	Poa annua	species	1	L.	http://www.tropicos.org/Name/25509881	L.	1	Poaceae	1	Poaceae	Poa	1	annua	1	\N	\N	\N	\N	\N	\N	\N	\N	Accepted	Poa annua	L.	species	http://www.tropicos.org/Name/25509881	Poa annua	Poaceae	true	tropicos	 	\N	Poaceae Poa annua L.	1	f	t

2013-08-27 22:02:41.03229-07	6	Asteraceae Bercht. & J. Presl	0.400000000000000022	Asteraceae	family	0.5	\N	http://compositae.landcareresearch.co.nz/default.aspx	\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	\N	\N	true	gcc	 [Partial match] 	\N	Asteraceae	1	f	t

2013-08-27 22:02:41.03229-07	7	Poaceae Poa annua var. annua	1	Poa annua var. annua	variety	1	\N	http://www.tropicos.org/Name/25517736	\N	\N	Poaceae	1	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	Poaceae Poa annua var. annua	1	f	t

2013-08-27 22:02:43.024394-07	0	Asteraceae	1	Asteraceae	family	1	\N	http://compositae.landcareresearch.co.nz/default.aspx	\N	\N	Asteraceae	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	Accepted	Asteraceae	\N	family	http://compositae.landcareresearch.co.nz/default.aspx	\N	\N	true	gcc	 	\N	Asteraceae	1	f	t

\.

--

-- Data for Name: ~Source.map; Type: TABLE DATA; Schema: TNRS; Owner: bien

--

COPY "~Source.map" ("from", "to", filter, notes) FROM stdin;

row_num	*row_num	\N	\N

:aggregator	sourceType	\N	\N

:http://tnrs.iplantcollaborative.org/TNRSapp.html	datasetURL	\N	\N

\.

--

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

--

ALTER TABLE ONLY "Source"

    ADD CONSTRAINT "Source_pkey" PRIMARY KEY ("*row_num");

--

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

--

ALTER TABLE ONLY batch_download_settings

    ADD CONSTRAINT batch_download_settings_pkey PRIMARY KEY (id);

--

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

--

ALTER TABLE ONLY batch

    ADD CONSTRAINT batch_id_by_time_key UNIQUE (id_by_time);

--

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

--

ALTER TABLE ONLY batch

    ADD CONSTRAINT batch_pkey PRIMARY KEY (id);

--

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

--

ALTER TABLE ONLY client_version

    ADD CONSTRAINT client_version_pkey PRIMARY KEY (id);

--

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

--

ALTER TABLE ONLY tnrs

    ADD CONSTRAINT "tnrs_Name_submitted_key" UNIQUE ("Name_submitted");

--

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

--

ALTER TABLE ONLY tnrs

    ADD CONSTRAINT tnrs_pkey PRIMARY KEY (batch, "Name_number");

--

-- Name: ~Source.map_from_key; Type: CONSTRAINT; Schema: TNRS; Owner: bien; Tablespace: 

--

ALTER TABLE ONLY "~Source.map"

    ADD CONSTRAINT "~Source.map_from_key" UNIQUE ("from");

--

-- Name: ~Source.map_to_key; Type: CONSTRAINT; Schema: TNRS; Owner: bien; Tablespace: 

--

ALTER TABLE ONLY "~Source.map"

    ADD CONSTRAINT "~Source.map_to_key" UNIQUE ("to");

--

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

--

CREATE UNIQUE INDEX tnrs_score_ok ON tnrs USING btree ("Name_submitted") WHERE score_ok("Max_score");

--

-- Name: batch__fill; Type: TRIGGER; Schema: TNRS; Owner: bien

--

CREATE TRIGGER batch__fill BEFORE INSERT OR UPDATE ON batch FOR EACH ROW EXECUTE PROCEDURE batch__fill();

--

-- Name: map_filter_insert; Type: TRIGGER; Schema: TNRS; Owner: bien

--

CREATE TRIGGER map_filter_insert BEFORE INSERT ON "~Source.map" FOR EACH ROW EXECUTE PROCEDURE util.map_filter_insert();

ALTER TABLE "~Source.map" DISABLE TRIGGER map_filter_insert;

--

-- Name: tnrs_populate_fields; Type: TRIGGER; Schema: TNRS; Owner: bien

--

CREATE TRIGGER tnrs_populate_fields BEFORE INSERT OR UPDATE ON tnrs FOR EACH ROW EXECUTE PROCEDURE tnrs_populate_fields();

--

-- Name: batch_client_version_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: bien

--

ALTER TABLE ONLY batch

    ADD CONSTRAINT batch_client_version_fkey FOREIGN KEY (client_version) REFERENCES client_version(id) ON UPDATE CASCADE ON DELETE CASCADE;

--

-- Name: batch_download_settings_id_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: bien

--

ALTER TABLE ONLY batch_download_settings

    ADD CONSTRAINT batch_download_settings_id_fkey FOREIGN KEY (id) REFERENCES batch(id) ON UPDATE CASCADE ON DELETE CASCADE;