--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET lock_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;

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

COMMENT ON SCHEMA "TNRS" IS '
IMPORTANT: when changing this schema, you must also make the same changes on vegbiendev:
on vegbiendev:
# back up existing TNRS schema (in case of an accidental incorrect change):
$ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s")
$ svn up
$ svn di
# make the changes shown in the diff
## to change column types:
SELECT util.set_col_types(''"TNRS".tnrs_match'', ARRAY[
  (''col'', ''new_type'')
]::util.col_cast[]); -- runtime: 9 min ("531282 ms")
$ rm=1 inputs/.TNRS/schema.sql.run
# repeat until `svn di` shows no diff
# back up new TNRS schema:
$ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s")
';


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: map_taxonomic_status(text, text); Type: FUNCTION; Schema: TNRS; Owner: bien
--

CREATE FUNCTION map_taxonomic_status(taxonomic_status_verbatim text, accepted_taxon_name_no_author text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
/* from http://wiki.vegpath.org/2013-11-14_conference_call#taxonomic-fields:
"taxonomic_status should be accepted instead of synonym when an accepted name is
available (this is not always the case when a name is marked as a synonym)" */
SELECT CASE WHEN $2 IS NOT NULL THEN 'accepted' ELSE lower($1) END
$_$;


ALTER FUNCTION "TNRS".map_taxonomic_status(taxonomic_status_verbatim text, accepted_taxon_name_no_author text) OWNER TO bien;

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

CREATE FUNCTION taxon_name_is_safe(taxon_name text) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT NOT ($1 = ANY("TNRS".unsafe_taxon_names()))
$_$;


ALTER FUNCTION "TNRS".taxon_name_is_safe(taxon_name text) OWNER TO bien;

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

CREATE FUNCTION tnrs__batch_begin() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	PERFORM util.seq__reset('pg_temp.tnrs__match_num__seq', 0);
	RETURN NULL;
END;
$$;


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

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

CREATE FUNCTION tnrs__match_num__fill() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	IF new.match_num IS NULL THEN
		new.match_num = "TNRS".tnrs__match_num__next();
	END IF;
	RETURN new;
END;
$$;


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

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

CREATE FUNCTION tnrs__match_num__next() RETURNS bigint
    LANGUAGE sql
    AS $$
SELECT nextval('pg_temp.tnrs__match_num__seq');
$$;


ALTER FUNCTION "TNRS".tnrs__match_num__next() 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");
	matched_taxon_name_with_author text = NULLIF(concat_ws(' '
		, NULLIF(NULLIF(new."Name_matched_accepted_family", 'Unknown'),
			new."Name_matched")
		, NULLIF(new."Name_matched", 'No suitable matches found.')
		, new."Name_matched_author"
		), '');
	accepted_taxon_name_with_author text = NULLIF(concat_ws(' '
		, NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'),
			new."Accepted_name")
		, new."Accepted_name"
		, new."Accepted_name_author"
		), '');
BEGIN
	/* exclude homonyms because these are not valid matches (i.e. TNRS provides
	a name, but the name is not meaningful because it is not unambiguous). */
	new.is_valid_match = new."Taxonomic_status" != 'Invalid'
		AND COALESCE(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, false);
	new.scrubbed_unique_taxon_name = COALESCE(
		accepted_taxon_name_with_author, matched_taxon_name_with_author);
	
	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_match SET "Name_submitted" = "Name_submitted"
runtime: 30 min ("5564082 rows affected, 1918900 ms execution time")
VACUUM ANALYZE "TNRS".tnrs_match -- to remove previous rows
runtime: 1.5 min ("92633 ms")
';


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

CREATE FUNCTION unsafe_taxon_names() RETURNS text[]
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT ARRAY[
]::text[]
$$;


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

SET default_tablespace = '';

SET default_with_oids = false;

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

CREATE TABLE tnrs_match (
    batch text DEFAULT now() NOT NULL,
    match_num integer 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,
    is_valid_match boolean NOT NULL,
    scrubbed_unique_taxon_name text
);


ALTER TABLE "TNRS".tnrs_match OWNER TO bien;

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

COMMENT ON TABLE tnrs_match IS '
to remove columns or add columns at the end:
$ rm=1 inputs/.TNRS/data.sql.run
$ make schemas/remake

to add columns in the middle:
make the changes in inputs/.TNRS/schema.sql
$ inputs/.TNRS/data.sql.run refresh # re-run TNRS
$ make schemas/remake

to populate a new column:
ALTER TABLE "TNRS".tnrs_match DISABLE TRIGGER tnrs_populate_fields; --speeds up update
UPDATE "TNRS".tnrs_match SET "col" = value;
	-- runtime: 30 min ("5564201 rows affected, 1624829 ms execution time")
ALTER TABLE "TNRS".tnrs_match ENABLE TRIGGER tnrs_populate_fields;
VACUUM ANALYZE "TNRS".tnrs_match --remove previous rows; runtime: 1.5 min ("92633 ms")

to add a constraint: runtime: 3 min ("173620 ms")
';


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

CREATE VIEW "MatchedTaxon" AS
 SELECT s."*Name_matched.batch",
    s."concatenatedScientificName",
    s."matchedTaxonName",
    s."matchedTaxonRank",
    s."*Name_matched.Name_score",
    s."matchedScientificNameAuthorship",
    s."matchedScientificNameID",
    s."*Name_matched.Author_score",
    s."matchedFamilyConfidence_fraction",
    s."matchedFamily",
    s."matchedGenus",
    s."matchedGenusConfidence_fraction",
    s."matchedSpecificEpithet",
    s."matchedSpeciesConfidence_fraction",
    s."matchedInfraspecificEpithet",
    s."*Name_matched.Infraspecific_epithet_score",
    s."identificationQualifier",
    s."morphospeciesSuffix",
    s."taxonomicStatus",
    s.accepted_taxon_name_no_author,
    s.accepted_author,
    s.accepted_taxon_rank,
    s."acceptedScientificNameID",
    s.accepted_species_binomial,
    s.accepted_family,
    s."*Name_matched.Selected",
    s."*Name_matched.Source",
    s."*Name_matched.Warnings",
    s."*Name_matched.Accepted_name_lsid",
    s.taxon_scrub__is_valid_match,
    s.scrubbed_unique_taxon_name,
        CASE
            WHEN (s.accepted_taxon_rank = 'family'::text) THEN concat_ws(' '::text, s.accepted_family, s."morphospeciesSuffix")
            WHEN (s.accepted_taxon_rank = 'genus'::text) THEN concat_ws(' '::text, s.accepted_taxon_name_no_author, s."morphospeciesSuffix")
            ELSE s.accepted_species_binomial
        END AS accepted_morphospecies_binomial
   FROM ( SELECT tnrs_match.batch AS "*Name_matched.batch",
            tnrs_match."Name_submitted" AS "concatenatedScientificName",
            tnrs_match."Name_matched" AS "matchedTaxonName",
            tnrs_match."Name_matched_rank" AS "matchedTaxonRank",
            tnrs_match."Name_score" AS "*Name_matched.Name_score",
            tnrs_match."Name_matched_author" AS "matchedScientificNameAuthorship",
            tnrs_match."Name_matched_url" AS "matchedScientificNameID",
            tnrs_match."Author_score" AS "*Name_matched.Author_score",
            tnrs_match."Family_score" AS "matchedFamilyConfidence_fraction",
            COALESCE(tnrs_match."Name_matched_accepted_family", tnrs_match."Accepted_name_family") AS "matchedFamily",
            tnrs_match."Genus_matched" AS "matchedGenus",
            tnrs_match."Genus_score" AS "matchedGenusConfidence_fraction",
            tnrs_match."Specific_epithet_matched" AS "matchedSpecificEpithet",
            tnrs_match."Specific_epithet_score" AS "matchedSpeciesConfidence_fraction",
            tnrs_match."Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet",
            tnrs_match."Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score",
            tnrs_match."Annotations" AS "identificationQualifier",
            tnrs_match."Unmatched_terms" AS "morphospeciesSuffix",
            map_taxonomic_status(tnrs_match."Taxonomic_status", tnrs_match."Accepted_name") AS "taxonomicStatus",
            tnrs_match."Accepted_name" AS accepted_taxon_name_no_author,
            tnrs_match."Accepted_name_author" AS accepted_author,
            tnrs_match."Accepted_name_rank" AS accepted_taxon_rank,
            tnrs_match."Accepted_name_url" AS "acceptedScientificNameID",
            tnrs_match."Accepted_name_species" AS accepted_species_binomial,
            tnrs_match."Accepted_name_family" AS accepted_family,
            tnrs_match."Selected" AS "*Name_matched.Selected",
            tnrs_match."Source" AS "*Name_matched.Source",
            tnrs_match."Warnings" AS "*Name_matched.Warnings",
            tnrs_match."Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid",
            tnrs_match.is_valid_match AS taxon_scrub__is_valid_match,
            tnrs_match.scrubbed_unique_taxon_name
           FROM tnrs_match) s;


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

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

COMMENT ON VIEW "MatchedTaxon" IS '
to modify:
SELECT util.recreate_view(''"TNRS"."MatchedTaxon"'', $$
SELECT __
$$);
';


--
-- 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,
    CONSTRAINT nulls_mapped CHECK (true)
);


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: 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".accepted_taxon_name_no_author,
    "MatchedTaxon".accepted_author,
    "MatchedTaxon".accepted_taxon_rank,
    "MatchedTaxon"."acceptedScientificNameID",
    "MatchedTaxon".accepted_species_binomial,
    "MatchedTaxon".accepted_family,
    "MatchedTaxon"."*Name_matched.Selected",
    "MatchedTaxon"."*Name_matched.Source",
    "MatchedTaxon"."*Name_matched.Warnings",
    "MatchedTaxon"."*Name_matched.Accepted_name_lsid",
    "MatchedTaxon".taxon_scrub__is_valid_match,
    "MatchedTaxon".scrubbed_unique_taxon_name,
    "MatchedTaxon".accepted_morphospecies_binomial
   FROM "MatchedTaxon"
  WHERE "MatchedTaxon".taxon_scrub__is_valid_match;


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: 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: taxon_scrub.scrubbed_unique_taxon_name.*; Type: VIEW; Schema: TNRS; Owner: bien
--

CREATE VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" AS
 SELECT tnrs_match."Name_submitted" AS scrubbed_unique_taxon_name,
    tnrs_match."Name_matched_rank" AS scrubbed_taxon_rank,
    COALESCE(tnrs_match."Name_matched_accepted_family", tnrs_match."Family_matched") AS scrubbed_family,
    tnrs_match."Genus_matched" AS scrubbed_genus,
    tnrs_match."Specific_epithet_matched" AS scrubbed_specific_epithet,
    tnrs_match."Infraspecific_rank" AS scrubbed_infraspecific_rank,
    tnrs_match."Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet,
    tnrs_match."Name_matched_author" AS scrubbed_author,
    tnrs_match."Name_matched" AS scrubbed_taxon_name_no_author,
    (tnrs_match."Name_matched" || COALESCE((' '::text || tnrs_match."Name_matched_author"), ''::text)) AS scrubbed_taxon_name_with_author
   FROM tnrs_match;


ALTER TABLE "TNRS"."taxon_scrub.scrubbed_unique_taxon_name.*" OWNER TO bien;

--
-- Name: VIEW "taxon_scrub.scrubbed_unique_taxon_name.*"; Type: COMMENT; Schema: TNRS; Owner: bien
--

COMMENT ON VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" IS '
to modify:
SELECT util.recreate_view(''"TNRS"."taxon_scrub.scrubbed_unique_taxon_name.*"'', $$
SELECT __
$$);

scrubbed_family: Name_matched_accepted_family was missing from the TNRS results at one point, so Family_matched is used as a workaround to populate this. the workaround is for *accepted names only*, as no opinion names do not have an Accepted_name_family to prepend to the scrubbed name to parse.
';


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

CREATE VIEW taxon_scrub AS
 SELECT "ValidMatchedTaxon".scrubbed_unique_taxon_name,
    "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".accepted_taxon_name_no_author,
    "ValidMatchedTaxon".accepted_author,
    "ValidMatchedTaxon".accepted_taxon_rank,
    "ValidMatchedTaxon"."acceptedScientificNameID",
    "ValidMatchedTaxon".accepted_species_binomial,
    "ValidMatchedTaxon".accepted_family,
    "ValidMatchedTaxon"."*Name_matched.Selected",
    "ValidMatchedTaxon"."*Name_matched.Source",
    "ValidMatchedTaxon"."*Name_matched.Warnings",
    "ValidMatchedTaxon"."*Name_matched.Accepted_name_lsid",
    "ValidMatchedTaxon".taxon_scrub__is_valid_match,
    "ValidMatchedTaxon".accepted_morphospecies_binomial,
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank,
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family,
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus,
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet,
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_rank,
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_epithet,
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_author,
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author,
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_with_author,
        CASE
            WHEN ("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank = 'family'::text) THEN concat_ws(' '::text, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family, "ValidMatchedTaxon"."morphospeciesSuffix")
            WHEN ("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank = 'genus'::text) THEN concat_ws(' '::text, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author, "ValidMatchedTaxon"."morphospeciesSuffix")
            ELSE (("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus || ' '::text) || "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet)
        END AS scrubbed_morphospecies_binomial
   FROM ("ValidMatchedTaxon"
   LEFT JOIN "taxon_scrub.scrubbed_unique_taxon_name.*" USING (scrubbed_unique_taxon_name));


ALTER TABLE "TNRS".taxon_scrub OWNER TO bien;

--
-- Name: VIEW taxon_scrub; Type: COMMENT; Schema: TNRS; Owner: bien
--

COMMENT ON VIEW taxon_scrub IS '
to modify:
SELECT util.recreate_view(''"TNRS".taxon_scrub'', $$
SELECT __
$$);
';


--
-- 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;
1	aggregator	http://tnrs.iplantcollaborative.org/TNRSapp.html
\.


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

COPY batch (id, id_by_time, time_submitted, client_version) FROM stdin;
2014-06-25 16:26:37.716529-07	2014-06-25 16:26:37.716529-07	2014-06-25 16:26:37.716529-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_match; Type: TABLE DATA; Schema: TNRS; Owner: bien
--

COPY tnrs_match (batch, match_num, "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", is_valid_match, scrubbed_unique_taxon_name) FROM stdin;
2014-06-25 16:26:37.716529-07	0	0	Compositae indet. sp.1	0.900000000000000022	Compositae	family	1	\N	http://www.theplantlist.org/1.1/browse/A/Compositae/	\N	\N	Compositae	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	indet. sp.1	Accepted	Compositae	\N	family	http://www.theplantlist.org/1.1/browse/A/Compositae/	\N	\N	true	tpl	 [Ambiguous match] 	\N	t	Compositae
2014-06-25 16:26:37.716529-07	1	0	Compositae indet. sp.1	0.900000000000000022	Compositae	family	1	\N	\N	\N	\N	Compositae	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	indet. sp.1	Accepted	Compositae	\N	family	\N	\N	\N	false	tpl	 [Ambiguous match] 	\N	t	Compositae
2014-06-25 16:26:37.716529-07	2	0	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	false	tropicos	 	\N	t	Asteraceae Bercht. & J. Presl
2014-06-25 16:26:37.716529-07	3	1	Fabaceae Boyle#6500	0.839999999999999969	Fagaceae	family	0.939999999999999947	\N	;http://plants.usda.gov/java/nameSearch	\N	\N	Fagaceae	0.880000000000000004	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	Boyle#6500	Accepted	Fagaceae	\N	family	;http://plants.usda.gov/java/nameSearch	\N	\N	true	tpl;usda	 	\N	f	Fagaceae
2014-06-25 16:26:37.716529-07	4	1	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	t	Fabaceae Lindl.
2014-06-25 16:26:37.716529-07	5	1	Fabaceae Boyle#6500	0.839999999999999969	Fagaceae	family	0.939999999999999947	Dumort.	http://www.tropicos.org/Name/42000062	\N	\N	Fagaceae	0.880000000000000004	Fagaceae	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	Boyle#6500	Accepted	Fagaceae	Dumort.	family	http://www.tropicos.org/Name/42000062	\N	Fagaceae	false	tropicos	 	\N	f	Fagaceae Dumort.
2014-06-25 16:26:37.716529-07	6	1	Fabaceae Boyle#6500	0.67000000000000004	Ficaceae	family	0.770000000000000018	Bercht. & J. Presl	http://www.tropicos.org/Name/100353631	\N	\N	Ficaceae	0.75	Ficaceae	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	Boyle#6500	No opinion	\N	\N	\N	\N	\N	\N	false	tropicos	 	\N	f	Ficaceae Bercht. & J. Presl
2014-06-25 16:26:37.716529-07	7	1	Fabaceae Boyle#6500	0.67000000000000004	Fucaceae	family	0.770000000000000018	\N	http://www.tropicos.org/Name/100371040	\N	\N	Fucaceae	0.75	Fucaceae	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	Boyle#6500	No opinion	\N	\N	\N	\N	\N	\N	false	tropicos	 	\N	f	Fucaceae
2014-06-25 16:26:37.716529-07	8	1	Fabaceae Boyle#6500	0.900000000000000022	Fabaceae	family	1	\N	http://plants.usda.gov/java/nameSearch	\N	\N	Fabaceae	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	Boyle#6500	Accepted	Fabaceae	\N	family	http://plants.usda.gov/java/nameSearch	\N	\N	false	usda	 	\N	t	Fabaceae
2014-06-25 16:26:37.716529-07	9	2	Fabaceae Inga "fuzzy leaf"	0.900000000000000022	Inga	genus	1	\N	http://www.theplantlist.org/1.1/browse/A/Leguminosae/Inga/;http://plants.usda.gov/java/profile?symbol=INGA	\N	\N	Fabaceae	1	Fabaceae	Inga	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	"fuzzy leaf"	Accepted	Inga	\N	genus	http://www.theplantlist.org/1.1/browse/A/Leguminosae/Inga/;http://plants.usda.gov/java/profile?symbol=INGA	\N	Fabaceae	true	tpl;usda	 	\N	t	Fabaceae Inga
2014-06-25 16:26:37.716529-07	10	2	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	false	tropicos	 	\N	t	Fabaceae Inga Mill.
2014-06-25 16:26:37.716529-07	11	2	Fabaceae Inga "fuzzy leaf"	0.900000000000000022	Inga	genus	1	Scop.	http://www.tropicos.org/Name/50215121	\N	\N	Fabaceae	1	Fabaceae	Inga	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	"fuzzy leaf"	Illegitimate	\N	\N	\N	\N	\N	\N	false	tropicos	 	\N	t	Fabaceae Inga Scop.
2014-06-25 16:26:37.716529-07	12	3	Fabaceae Inga sp.3	0.900000000000000022	Inga	genus	1	\N	http://www.theplantlist.org/1.1/browse/A/Leguminosae/Inga/;http://plants.usda.gov/java/profile?symbol=INGA	\N	\N	Fabaceae	1	Fabaceae	Inga	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	sp.3	Accepted	Inga	\N	genus	http://www.theplantlist.org/1.1/browse/A/Leguminosae/Inga/;http://plants.usda.gov/java/profile?symbol=INGA	\N	Fabaceae	true	tpl;usda	 	\N	t	Fabaceae Inga
2014-06-25 16:26:37.716529-07	13	3	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	false	tropicos	 	\N	t	Fabaceae Inga Mill.
2014-06-25 16:26:37.716529-07	14	3	Fabaceae Inga sp.3	0.900000000000000022	Inga	genus	1	Scop.	http://www.tropicos.org/Name/50215121	\N	\N	Fabaceae	1	Fabaceae	Inga	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	sp.3	Illegitimate	\N	\N	\N	\N	\N	\N	false	tropicos	 	\N	t	Fabaceae Inga Scop.
2014-06-25 16:26:37.716529-07	15	4	Fabaceae unknown #2	0.839999999999999969	Fagaceae	family	0.939999999999999947	\N	;http://plants.usda.gov/java/nameSearch	\N	\N	Fagaceae	0.880000000000000004	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	unknown #2	Accepted	Fagaceae	\N	family	;http://plants.usda.gov/java/nameSearch	\N	\N	true	tpl;usda	 	\N	f	Fagaceae
2014-06-25 16:26:37.716529-07	16	4	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	t	Fabaceae Lindl.
2014-06-25 16:26:37.716529-07	17	4	Fabaceae unknown #2	0.839999999999999969	Fagaceae	family	0.939999999999999947	Dumort.	http://www.tropicos.org/Name/42000062	\N	\N	Fagaceae	0.880000000000000004	Fagaceae	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	unknown #2	Accepted	Fagaceae	Dumort.	family	http://www.tropicos.org/Name/42000062	\N	Fagaceae	false	tropicos	 	\N	f	Fagaceae Dumort.
2014-06-25 16:26:37.716529-07	18	4	Fabaceae unknown #2	0.67000000000000004	Ficaceae	family	0.770000000000000018	Bercht. & J. Presl	http://www.tropicos.org/Name/100353631	\N	\N	Ficaceae	0.75	Ficaceae	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	unknown #2	No opinion	\N	\N	\N	\N	\N	\N	false	tropicos	 	\N	f	Ficaceae Bercht. & J. Presl
2014-06-25 16:26:37.716529-07	19	4	Fabaceae unknown #2	0.67000000000000004	Fucaceae	family	0.770000000000000018	\N	http://www.tropicos.org/Name/100371040	\N	\N	Fucaceae	0.75	Fucaceae	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	unknown #2	No opinion	\N	\N	\N	\N	\N	\N	false	tropicos	 	\N	f	Fucaceae
2014-06-25 16:26:37.716529-07	20	4	Fabaceae unknown #2	0.900000000000000022	Fabaceae	family	1	\N	http://plants.usda.gov/java/nameSearch	\N	\N	Fabaceae	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	unknown #2	Accepted	Fabaceae	\N	family	http://plants.usda.gov/java/nameSearch	\N	\N	false	usda	 	\N	t	Fabaceae
2014-06-25 16:26:37.716529-07	21	5	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	f	\N
2014-06-25 16:26:37.716529-07	22	6	Poa annua	1	Poa annua	species	1	L.	http://www.theplantlist.org/tpl1.1/record/kew-435194;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.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN	Poa annua	Poaceae	true	tpl;tropicos;usda	 	\N	t	Poaceae Poa annua L.
2014-06-25 16:26:37.716529-07	23	6	Poa annua	1	Poa annua	species	1	Cham. & Schltdl.	http://www.theplantlist.org/tpl1.1/record/kew-435195	\N	\N	\N	\N	Poaceae	Poa	1	annua	1	\N	\N	\N	\N	\N	\N	\N	\N	Synonym	Poa infirma	Kunth	species	http://www.theplantlist.org/tpl1.1/record/kew-436189	Poa infirma	Poaceae	false	tpl	 	\N	t	Poaceae Poa infirma Kunth
2014-06-25 16:26:37.716529-07	24	7	Poa annua L.	1	Poa annua	species	1	L.	http://www.theplantlist.org/tpl1.1/record/kew-435194;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.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN	Poa annua	Poaceae	true	tpl;tropicos;usda	 	\N	t	Poaceae Poa annua L.
2014-06-25 16:26:37.716529-07	25	7	Poa annua L.	0.800000000000000044	Poa annua	species	1	Cham. & Schltdl.	http://www.theplantlist.org/tpl1.1/record/kew-435195	Cham. & Schltdl.	0	\N	\N	Poaceae	Poa	1	annua	1	\N	\N	\N	\N	\N	\N	\N	\N	Synonym	Poa infirma	Kunth	species	http://www.theplantlist.org/tpl1.1/record/kew-436189	Poa infirma	Poaceae	false	tpl	 	\N	t	Poaceae Poa infirma Kunth
2014-06-25 16:26:37.716529-07	26	8	Poa annua fo. lanuginosa	1	Poa annua fo. lanuginosa	fo.	1	Sennen	http://www.theplantlist.org/tpl1.1/record/tro-50267771	\N	\N	\N	\N	\N	Poa	1	annua	1	fo.	lanuginosa	1	\N	\N	\N	\N	\N	Synonym	Poa annua	L.	species	http://www.theplantlist.org/tpl1.1/record/kew-435194	Poa annua	Poaceae	true	tpl	 	\N	t	Poaceae Poa annua L.
2014-06-25 16:26:37.716529-07	27	8	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	false	tropicos	 	\N	t	Poaceae Poa annua var. annua
2014-06-25 16:26:37.716529-07	28	9	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	t	Poaceae Poa infirma Kunth
2014-06-25 16:26:37.716529-07	29	9	Poa annua ssp. exilis	0.959999999999999964	Poa annua var. exilis	variety	0.959999999999999964	Tomm. ex Freyn	http://www.tropicos.org/Name/25547854	\N	\N	\N	\N	Poaceae	Poa	1	annua	1	var.	exilis	0.699999999999999956	\N	\N	\N	\N	\N	Synonym	Poa infirma	Kunth	species	http://www.tropicos.org/Name/25514158	Poa infirma	Poaceae	false	tropicos	 	\N	t	Poaceae Poa infirma Kunth
2014-06-25 16:26:37.716529-07	30	10	Poa annua subsp. exilis	1	Poa annua subsp. exilis	subsp.	1	(Tomm. ex Freyn.) Asch. & Graebn.	http://www.theplantlist.org/tpl1.1/record/kew-435202	\N	\N	\N	\N	\N	Poa	1	annua	1	subsp.	exilis	1	\N	\N	\N	\N	\N	Synonym	Poa infirma	Kunth	species	http://www.theplantlist.org/tpl1.1/record/kew-436189	Poa infirma	Poaceae	true	tpl	 	\N	t	Poaceae Poa infirma Kunth
2014-06-25 16:26:37.716529-07	31	10	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	false	tropicos	 	\N	t	Poaceae Poa infirma Kunth
2014-06-25 16:26:37.716529-07	32	11	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	t	Poaceae Poa annua subvar. minima (Schur) Asch. & Graebn.
2014-06-25 16:26:37.716529-07	33	12	Poa annua var. eriolepis	1	Poa annua var. eriolepis	var.	1	̉ۡ.Desv.	http://www.theplantlist.org/tpl1.1/record/kew-435206	\N	\N	\N	\N	\N	Poa	1	annua	1	var.	eriolepis	1	\N	\N	\N	\N	\N	Synonym	Poa annua	L.	species	http://www.theplantlist.org/tpl1.1/record/kew-435194	Poa annua	Poaceae	true	tpl	 	\N	t	Poaceae Poa annua L.
2014-06-25 16:26:37.716529-07	34	12	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	false	tropicos	 	\N	t	Poaceae Poa annua L.
2014-06-25 16:26:37.716529-07	35	13	Silene scouleri Hook. subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea C.L. Hitchc. & Maguire	0.770000000000000018	Silene scouleri subsp. pringlei	subsp.	0.770000000000000018	(S. Watson) C.L. Hitchc. & Maguire	http://www.theplantlist.org/tpl1.1/record/tro-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	subsp.	http://www.theplantlist.org/tpl1.1/record/tro-6303627	Silene scouleri	Caryophyllaceae	true	tpl	 [Partial match] 	\N	t	Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire
2014-06-25 16:26:37.716529-07	36	13	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	false	tropicos	 [Partial match] 	\N	t	Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire
2014-06-25 16:26:37.716529-07	37	13	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://plants.usda.gov/java/profile?symbol=SISCP	\N	\N	\N	\N	Caryophyllaceae	Silene	1	scouleri	1	subsp.	pringlei	1	\N	\N	\N	\N	var. grisea	Accepted	Silene scouleri ssp. pringlei	(S. Watson) C.L. Hitchc. & Maguire	subspecies	http://plants.usda.gov/java/profile?symbol=SISCP	Silene scouleri	Caryophyllaceae	false	usda	 [Partial match] 	\N	t	Caryophyllaceae Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire
2014-06-25 16:26:37.716529-07	38	14	Silene scouleri subsp. pringlei var. grisea C.L. Hitchc. & Maguire	0.770000000000000018	Silene scouleri subsp. pringlei	subsp.	0.770000000000000018	(S. Watson) C.L. Hitchc. & Maguire	http://www.theplantlist.org/tpl1.1/record/tro-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	subsp.	http://www.theplantlist.org/tpl1.1/record/tro-6303627	Silene scouleri	Caryophyllaceae	true	tpl	 [Partial match] 	\N	t	Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire
2014-06-25 16:26:37.716529-07	39	14	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	false	tropicos	 [Partial match] 	\N	t	Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire
2014-06-25 16:26:37.716529-07	40	14	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://plants.usda.gov/java/profile?symbol=SISCP	\N	\N	\N	\N	Caryophyllaceae	Silene	1	scouleri	1	subsp.	pringlei	1	\N	\N	\N	\N	var. grisea	Accepted	Silene scouleri ssp. pringlei	(S. Watson) C.L. Hitchc. & Maguire	subspecies	http://plants.usda.gov/java/profile?symbol=SISCP	Silene scouleri	Caryophyllaceae	false	usda	 [Partial match] 	\N	t	Caryophyllaceae Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire
\.


--
-- 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_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: bien; Tablespace: 
--

ALTER TABLE ONLY tnrs_match
    ADD CONSTRAINT tnrs_pkey PRIMARY KEY (batch, match_num);


--
-- 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: batch_client_version_idx; Type: INDEX; Schema: TNRS; Owner: bien; Tablespace: 
--

CREATE INDEX batch_client_version_idx ON batch USING btree (client_version);


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

CREATE INDEX "tnrs_Name_submitted_idx" ON tnrs_match USING btree ("Name_submitted");


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

CREATE INDEX tnrs__valid_match ON tnrs_match USING btree ("Name_submitted") WHERE is_valid_match;


--
-- 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__batch_begin; Type: TRIGGER; Schema: TNRS; Owner: bien
--

CREATE TRIGGER tnrs__batch_begin BEFORE INSERT ON tnrs_match FOR EACH STATEMENT EXECUTE PROCEDURE tnrs__batch_begin();


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

CREATE TRIGGER tnrs__match_num__fill BEFORE INSERT ON tnrs_match FOR EACH ROW EXECUTE PROCEDURE tnrs__match_num__fill();


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

CREATE TRIGGER tnrs_populate_fields BEFORE INSERT OR UPDATE ON tnrs_match 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;


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

ALTER TABLE ONLY tnrs_match
    ADD CONSTRAINT tnrs_batch_fkey FOREIGN KEY (batch) REFERENCES batch(id) ON UPDATE CASCADE ON DELETE CASCADE;


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

REVOKE ALL ON SCHEMA "TNRS" FROM PUBLIC;
REVOKE ALL ON SCHEMA "TNRS" FROM bien;
GRANT ALL ON SCHEMA "TNRS" TO bien;
GRANT USAGE ON SCHEMA "TNRS" TO bien_read;


--
-- Name: tnrs_match; Type: ACL; Schema: TNRS; Owner: bien
--

REVOKE ALL ON TABLE tnrs_match FROM PUBLIC;
REVOKE ALL ON TABLE tnrs_match FROM bien;
GRANT ALL ON TABLE tnrs_match TO bien;
GRANT SELECT ON TABLE tnrs_match TO bien_read;


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

REVOKE ALL ON TABLE "MatchedTaxon" FROM PUBLIC;
REVOKE ALL ON TABLE "MatchedTaxon" FROM bien;
GRANT ALL ON TABLE "MatchedTaxon" TO bien;
GRANT SELECT ON TABLE "MatchedTaxon" TO bien_read;


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

REVOKE ALL ON TABLE "ValidMatchedTaxon" FROM PUBLIC;
REVOKE ALL ON TABLE "ValidMatchedTaxon" FROM bien;
GRANT ALL ON TABLE "ValidMatchedTaxon" TO bien;
GRANT SELECT ON TABLE "ValidMatchedTaxon" TO bien_read;


--
-- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: ACL; Schema: TNRS; Owner: bien
--

REVOKE ALL ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" FROM PUBLIC;
REVOKE ALL ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" FROM bien;
GRANT ALL ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" TO bien;
GRANT SELECT ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" TO bien_read;


--
-- Name: taxon_scrub; Type: ACL; Schema: TNRS; Owner: bien
--

REVOKE ALL ON TABLE taxon_scrub FROM PUBLIC;
REVOKE ALL ON TABLE taxon_scrub FROM bien;
GRANT ALL ON TABLE taxon_scrub TO bien;
GRANT SELECT ON TABLE taxon_scrub TO bien_read;


--
-- PostgreSQL database dump complete
--