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

--CREATE SCHEMA "TNRS";


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

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'', 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: -
--

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;
$$;


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

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)
$_$;


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

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)
$_$;


--
-- Name: map_taxonomic_status(text, text); Type: FUNCTION; Schema: TNRS; Owner: -
--

CREATE FUNCTION map_taxonomic_status(taxonomic_status_verbatim text, scrubbed_unique_taxon_name text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT CASE WHEN $2 IS NOT NULL THEN 'accepted' ELSE lower($1) END
$_$;


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

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 = 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;
$$;


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

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: 40 min ("5363526 rows affected, 2329840 ms execution time")
VACUUM ANALYZE "TNRS".tnrs -- to remove previous rows
runtime: 1.5 min ("92633 ms")';


SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: tnrs; Type: TABLE; Schema: TNRS; Owner: -; 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,
    is_valid_match boolean NOT NULL,
    scrubbed_unique_taxon_name text
);


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

COMMENT ON TABLE tnrs IS 'IMPORTANT: when changing this table''s schema, you may need to re-run TNRS:
$ inputs/.TNRS/data.sql.run refresh';


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

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.is_valid_match AS taxon_scrub__is_valid_match, 
    tnrs.scrubbed_unique_taxon_name
   FROM tnrs;


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

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".taxon_scrub__is_valid_match, 
    "MatchedTaxon".scrubbed_unique_taxon_name
   FROM "MatchedTaxon"
  WHERE "MatchedTaxon".taxon_scrub__is_valid_match;


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

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


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

CREATE TABLE batch (
    id text NOT NULL,
    id_by_time text,
    time_submitted timestamp with time zone DEFAULT now(),
    client_version text
);


--
-- Name: batch_download_settings; Type: TABLE; Schema: TNRS; Owner: -; 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
);


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

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: -; Tablespace: 
--

CREATE TABLE client_version (
    id text NOT NULL,
    global_rev integer NOT NULL,
    "/lib/tnrs.py rev" integer,
    "/bin/tnrs_db rev" integer
);


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

COMMENT ON TABLE client_version IS 'contains svn revisions';


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

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

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

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

CREATE VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" AS
 SELECT tnrs."Name_submitted" AS scrubbed_unique_taxon_name, 
    tnrs."Name_matched_rank" AS scrubbed_taxon_rank, 
    tnrs."Name_matched_accepted_family" AS scrubbed_family, 
    tnrs."Genus_matched" AS scrubbed_genus, 
    tnrs."Specific_epithet_matched" AS scrubbed_specific_epithet, 
    tnrs."Infraspecific_rank" AS scrubbed_infraspecific_rank, 
    tnrs."Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet, 
    tnrs."Name_matched_author" AS scrubbed_author, 
    tnrs."Name_matched" AS scrubbed_taxon_name_no_author
   FROM tnrs;


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

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"."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".taxon_scrub__is_valid_match, 
    "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
   FROM ("ValidMatchedTaxon"
   LEFT JOIN "taxon_scrub.scrubbed_unique_taxon_name.*" USING (scrubbed_unique_taxon_name));


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

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


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

ALTER TABLE ONLY batch
    ADD CONSTRAINT batch_id_by_time_key UNIQUE (id_by_time);


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

ALTER TABLE ONLY batch
    ADD CONSTRAINT batch_pkey PRIMARY KEY (id);


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

ALTER TABLE ONLY client_version
    ADD CONSTRAINT client_version_pkey PRIMARY KEY (id);


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

ALTER TABLE ONLY tnrs
    ADD CONSTRAINT "tnrs_Name_submitted_key" UNIQUE ("Name_submitted");


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

ALTER TABLE ONLY tnrs
    ADD CONSTRAINT tnrs_pkey PRIMARY KEY (batch, "Name_number");


--
-- Name: batch_client_version_idx; Type: INDEX; Schema: TNRS; Owner: -; Tablespace: 
--

CREATE INDEX batch_client_version_idx ON batch USING btree (client_version);


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

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


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

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


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

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

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

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

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


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

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; Type: ACL; Schema: TNRS; Owner: -
--

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


--
-- PostgreSQL database dump complete
--